Improved db_pager query parsing (fixed error in Sales Quotation Inquiry)

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

diff --git a/includes/db_pager.inc b/includes/db_pager.inc
index 8684281dc0406fdfa56e6fcfd08fb86b362b8210..6ad1755a752ff707d7e6b2218d5256383649bf7c 100644 (file)
--- a/includes/db_pager.inc
+++ b/includes/db_pager.inc
@@ -57,7 +57,12 @@ class db_pager {
        var $key;       // key field name
        
        //  db_pager constructor
-       //  accepts $sql like 'SELECT ...[FROM ...][WHERE ...][GROUP ...][ORDER ...]'
+       //  accepts $sql query either as:
+       //  a. string in form 'SELECT field_list FROM table_joins [WHERE conditions [GROUP group_list [ORDER order_list]]]'
+       //              - if WHERE keyword is used in table_joins, WHERE conditions is obligatory
+       //  b. associative array using select, where, group and order keys ex :
+       //      array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location')
+       //
        //      $name is base name for pager controls
        function db_pager($sql, $name, $table = null, $page_len=0) 
        {
@@ -68,34 +73,49 @@ class db_pager {
                $this->set_sql($sql);
        }
        //
-       //      Parse base sql select query.
+       //      Parse base sql select query     or use an associative array.
        //
        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('/\sFROM\s/si', $sql, 2);
+                                       if (count($parts) == 2) {
+                                               $this->select = $parts[0];
+                                               $sql = $parts[1];
+                                       } else {
+                                               if ($go_debug)
+                                                       display_error("Invalid sql input for db_pager");
+                                       }
+
+                                       $parts = preg_split('/\sWHERE(?!.*WHERE.*)\s/si', $sql, 2); // last occurence
+                                       if(count($parts) == 2) {
+                                               $this->from = $parts[0];
+                                               $sql = $parts[1];
+
+                                               $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];
+                                               }
+                                               $this->where = $sql;
+                                       }
                        }
-                       $this->select = $sql;
                }
        }
        //
@@ -129,7 +149,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);
@@ -274,7 +303,16 @@ class db_pager {
                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'];
@@ -323,7 +361,7 @@ class db_pager {
                                if (isset($col['ord']) && $col['ord'] != '' 
                                                &&  !isset($col['name'])) {
                                                        display_warning("Result field names must be set
-                                                               for all intially ordered db_pager columns.");
+                                                               for all initially ordered db_pager columns.");
                                }
                                }
                }
@@ -472,4 +510,4 @@ function refresh_pager($name)
        if (isset($_SESSION[$name]))
                $_SESSION[$name]->ready = false;
 }
-?>
\ No newline at end of file
+?>