X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb_pager.inc;h=f9089e9ee5a0f2ba89f152762f0645f5dab020ad;hb=5e63c6ace55729bbb5ee3b060035a25a4426eb0a;hp=f06d650139c5ce8f4840227dfa798c8235c60e08;hpb=7e830126b96477e969fe3b48d9fc0e78f6c1fe00;p=fa-stable.git

diff --git a/includes/db_pager.inc b/includes/db_pager.inc
index f06d6501..f9089e9e 100644
--- a/includes/db_pager.inc
+++ b/includes/db_pager.inc
@@ -39,12 +39,15 @@ class db_pager {
 	    $prev_page, 
 	    $next_page,
 	    $first_page;
-
+	    
 	var $page_len,
 	    $rec_count;
-
-	var $order = array();
-
+	
+	var $select,
+		$where,
+	    $from,
+		$group,
+		$order;
 	var	$extra_where;
 	
 	var $ready = false; // this var is false after change in sql before first
@@ -54,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) 
 	{
@@ -65,14 +73,57 @@ 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)
 	{
+		global $SysPrefs;
+
 		if ($sql != $this->sql) {
 		    $this->sql = $sql;
 		    $this->ready = false;
+
+				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 ($SysPrefs->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;
+					}
+			}
 		}
+		//_vd($this->select);
+		//_vd($this->from);
+		//_vd($this->where);
+		//_vd($this->group);
+		//_vd($this->order);
 	}
 	//
 	//	Set additional constraint on record set
@@ -105,17 +156,20 @@ 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;
-	    $n = array_search($col, $this->order);
-	    if ($n !== false)
-			unset($this->order[$n]);
-		if ($ord != '')
-			array_unshift($this->order, $col); // store column number as first
 	    $this->set_page(1);
 	    $this->query();
-
 	    return true;
 	}
 	//
@@ -192,7 +246,7 @@ class db_pager {
 	//  $flds: array( fldname1, fldname2=>type,...)
 	function set_columns($flds)
 	{
-		$this->columns = $this->order = array();
+		$this->columns = array();
 		if (!is_array($flds)) {
 			$flds = array($flds);
 		}
@@ -224,9 +278,7 @@ class db_pager {
 				case 'skip':		// skip the column (no header)
 					unset($c['head']); break;
 	  		}
-			if (@$c['ord'])
-				array_push($this->order, count($this->columns));
-			$this->columns[] = $c;
+			$this->columns[] = $c;	
 		}
 	}
 	//
@@ -236,48 +288,68 @@ class db_pager {
 	//
 	function _sql_gen($count=false) 
 	{
+		$select = $this->select;
+		$from = $this->from;
+		$where = $this->where;
+		$group = $this->group;
+		$order = $this->order;
+
+		if(count($this->extra_where)) {
+		    $where .= ($where=='' ? '' : ' AND ')
+				.implode(' AND ', $this->extra_where);
+		}
+		if ($where) $where = " WHERE ($where)";
 
-		if (count($this->extra_where)) {
-		    $where = ' WHERE ('.implode(' AND ', $this->extra_where).')';
-		} else
-			$where = '';
-
-		if ($count)
-			return "SELECT COUNT(*) FROM ($this->sql) $where AS _dummyname";
+		if ($count) {
+			$group = $group == '' ? "*" : "DISTINCT $group";
 
+			return "SELECT COUNT($group) FROM $from $where";
+		}
 
+		$sql = "$select FROM $from $where";
+		if ($group) $sql.= " GROUP BY $group";
 	    $ord = array();
 
-		$sql = "SELECT * FROM ($this->sql) $where AS _dummyname";
+			// 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 ($this->order as $n) {
-	    	$col = $this->columns[$n];
+	    foreach ($columns as $col) {
 	       	if (isset($col['ord'])) {
 	        	if ( $col['ord'] != '' && isset($col['name'])) {
 		    	    $ord[] = $col['name'] .' '. $col['ord'];
 			    }
 			}
 	    }
+				
 	    if (count($ord)) {
-			$sql .= " ORDER BY " . implode($ord, ',');
+			$ord = array_map('db_escape_function', $ord);
+			$sql .= " ORDER BY " . implode(',', $ord);
+		} else {
+			if($order)
+				$sql .= " ORDER BY $order"; // original base query order
 		}
 
 	    $page_len = $this->page_len;
 	    $offset = ($this->curr_page - 1) * $page_len;
 
-
 	    $sql .= " LIMIT $offset, $page_len";
 
 		return $sql;
-
+		
 	}
 	//
 	//	Initialization after changing record set
 	//
 	function _init() 
 	{
-		global $go_debug;
-
+		global $SysPrefs;
+		
 	    if ($this->ready == false ) {
 			$sql = $this->_sql_gen(true);
 			$result = db_query($sql, 'Error reading record set');
@@ -287,8 +359,8 @@ class db_pager {
 			$this->rec_count = $row[0];
 	  		$this->max_page = $this->page_len ?
 				ceil($this->rec_count/$this->page_len) : 0;
-
-			if ($go_debug) { // FIX - need column name parsing, but for now:
+		
+			if ($SysPrefs->go_debug) { // FIX - need column name parsing, but for now:
 				// check if field names are set explicite in col def
 				// for all initially ordered columns
 			    foreach ($this->columns as $col) {
@@ -369,7 +441,7 @@ class db_pager {
 //			return inactive_control_cell($row[$this->inactive_ctrl['key']],
 //				 $row['inactive'], $this->inactive_ctrl['table'], 
 //				 $this->inactive_ctrl['key']);
-
+				 
 			global	$Ajax;
 
 			$key = $this->key ?
@@ -385,7 +457,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
@@ -444,4 +516,3 @@ function refresh_pager($name)
 	if (isset($_SESSION[$name]))
 		$_SESSION[$name]->ready = false;
 }
-?>
\ No newline at end of file