Fixed problem with counting number of rows for some SQL queries displayed in db pager.

[fa-stable.git] / includes / db_pager.inc

diff --git a/includes/db_pager.inc b/includes/db_pager.inc
index cf67659a004203b8407815eac60760c47acb2b79..1940236d0ed64338d6bed92ffc2c130fc33f18af 100644 (file)
--- a/includes/db_pager.inc
+++ b/includes/db_pager.inc
@@ -61,7 +61,6 @@ class db_pager {
        //      $name is base name for pager controls
        function db_pager($sql, $name, $table = null, $page_len=0) 
        {
-               global $table_style;
                $this->width = "95%";
                if ($page_len == 0) $page_len = user_query_size();
                $this->name = $name;
@@ -70,33 +69,47 @@ class db_pager {
        }
        //
        //      Parse base sql select query.
-       //
+       //      or use an associative array.
+       //  Usefull is the query can't be split correctly (using subquery for example).
+       //  The associative array shouldn't contain the keyword itself.
+       //  ex :
+       //  array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location')
        function set_sql($sql)
        {
                if ($sql != $this->sql) {
                    $this->sql = $sql;
                    $this->ready = false;
-               $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
-                       if(count($parts) == 2) {
-                               $sql = $parts[0];
-                               $this->order = $parts[1];
-                       }
-               $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
-                       if(count($parts) == 2) {
-                               $sql = $parts[0];
-                               $this->group = $parts[1];
-                       }
-               $parts = preg_split('/\sWHERE\s/si', $sql, 2);
-                       if(count($parts) == 2) {
-                               $sql = $parts[0];
-                               $this->where = $parts[1];
-                       }
-               $parts = preg_split('/\sFROM\s/si', $sql, 2);
-                       if(count($parts) == 2) {
-                               $sql = $parts[0];
-                               $this->from = $parts[1];
+
+                               if(is_array($sql)) {
+                                       foreach(explode(' ', 'select from where group order') as $section) {
+                                               $this->$section = @$sql[$section];
+                                       }
+                                       $this->select = "SELECT ".$this->select;
+                               }
+                               else {
+                                       // parse the query
+                                               $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
+                                       if(count($parts) == 2) {
+                                               $sql = $parts[0];
+                                               $this->order = $parts[1];
+                                       }
+                                               $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
+                                       if(count($parts) == 2) {
+                                               $sql = $parts[0];
+                                               $this->group = $parts[1];
+                                       }
+                                               $parts = preg_split('/\sWHERE\s/si', $sql, 2);
+                                       if(count($parts) == 2) {
+                                               $sql = $parts[0];
+                                               $this->where = $parts[1];
+                                       }
+                                               $parts = preg_split('/\sFROM\s/si', $sql, 2);
+                                       if(count($parts) == 2) {
+                                               $sql = $parts[0];
+                                               $this->from = $parts[1];
+                                       }
+                                       $this->select = $sql;
                        }
-                       $this->select = $sql;
                }
        }
        //
@@ -130,7 +143,16 @@ class db_pager {
        //
        function sort_table($col) 
        {
+
+                       $max_priority = 0;
+                       foreach($this->columns as $id => $_col) {
+                       if(!isset($_col['ord_priority'])) continue;
+                               $max_priority = max($max_priority, $_col['ord_priority']);
+                       };
+
+
            $ord = $this->columns[$col]['ord'];
+               $this->columns[$col]['ord_priority']  = $max_priority+1; // set priority , higher than anything else
            $ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
            $this->columns[$col]['ord'] = $ord;
            $this->set_page(1);
@@ -160,11 +182,11 @@ class db_pager {
                        $c = 0;
                  // add result field names to column defs for 
                  // col value retrieve and sort purposes 
-                  $cnt = min(mysql_num_fields($result), count($this->columns));
+                  $cnt = min(db_num_fields($result), count($this->columns));
                        for ($c = $i = 0; $c < $cnt; $c++) {
                                if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) {
 //                                     if (!@($this->columns[$c]['type']=='skip'))
-                                               $this->columns[$c]['name']= mysql_field_name($result, $i);
+                                               $this->columns[$c]['name']= db_field_name($result, $i);
                                        if (!@($this->columns[$c]['type']=='insert'))
                                        $i++;
                                        }
@@ -261,21 +283,30 @@ class db_pager {
 
                if(count($this->extra_where)) {
                    $where .= ($where=='' ? '' : ' AND ')
-                               .implode( $this->extra_where, ' AND ');
+                               .implode(' AND ', $this->extra_where);
                }
                if ($where) $where = " WHERE ($where)";
 
                if ($count) {
                        $group = $group == '' ? "*" : "DISTINCT $group";
 
-                       return "SELECT COUNT($group) FROM $from $where";
+                       return "SELECT COUNT(0) FROM (SELECT $group FROM $from $where) row_count";
                }
 
                $sql = "$select FROM $from $where";
                if ($group) $sql.= " GROUP BY $group";
            $ord = array();
 
+                       // sort order column by priority instead of table order.
+                       $columns = array();
            foreach ($this->columns as $col) {
+                                       if(isset($col['ord_priority'])) {
+                                               $columns[$col['ord_priority']] = $col;
+                                       }
+                       }
+                       krsort($columns);
+
+           foreach ($columns as $col) {
                if (isset($col['ord'])) {
                        if ( $col['ord'] != '' && isset($col['name'])) {
                            $ord[] = $col['name'] .' '. $col['ord'];
@@ -284,7 +315,9 @@ class db_pager {
            }
                                
            if (count($ord)) {
-                       $sql .= " ORDER BY " . implode($ord, ',');
+                       $ord = array_map(function_exists('mysql_real_escape_string') ? 
+                               'mysql_real_escape_string': 'mysql_escape_string', $ord);
+                       $sql .= " ORDER BY " . implode(',', $ord);
                } else {
                        if($order)
                                $sql .= " ORDER BY $order"; // original base query order
@@ -412,7 +445,7 @@ class db_pager {
                                        update_record_status($id, !$value, $table, $key);
                                        $value = !$value;
                                }
-                               echo '<td align="center">'. checkbox(null, $name, $value, true, '', "align='center'")
+                               echo '<td align="center">'. checkbox(null, $name, $value, true, '')
                                . hidden("LInact[$id]", $value, false) . '</td>';       
                        }
                } else
@@ -471,4 +504,4 @@ function refresh_pager($name)
        if (isset($_SESSION[$name]))
                $_SESSION[$name]->ready = false;
 }
-?>
\ No newline at end of file
+?>