X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb_pager.inc;h=affb9e09796e9cf046abdb0998817ed533eac076;hb=7e6e0807990447d2977b970c3a0fd28dc9250194;hp=1629c47a000aeb7a9fd90a1033a1258a644afb83;hpb=c128e08aea1e33a74bf918578013998a4d8b5c36;p=fa-stable.git

diff --git a/includes/db_pager.inc b/includes/db_pager.inc
index 1629c47a..affb9e09 100644
--- a/includes/db_pager.inc
+++ b/includes/db_pager.inc
@@ -29,6 +29,7 @@ class db_pager {
 	var $width;			// table width (default '95%')
 	var $header_fun;	// additional row between title and body
 	var $header_class;
+    var $row_fun;       // Function for row preprocessing
 	var $footer_fun;
 	var $footer_class;
 	var $data = array();
@@ -48,7 +49,7 @@ class db_pager {
 	    $from,
 		$group,
 		$order;
-	var	$extra_where;
+	var	$extra_where = array();
 	
 	var $ready = false; // this var is false after change in sql before first
 						// and before first query.
@@ -57,9 +58,14 @@ 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) 
+	function __construct($sql, $name, $table = null, $page_len=0) 
 	{
 		$this->width = "95%";
 		if ($page_len == 0) $page_len = user_query_size();
@@ -68,34 +74,51 @@ 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;
-    		$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 ($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;
+					}
 			}
-			$this->select = $sql;
 		}
 	}
 	//
@@ -129,7 +152,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);
@@ -157,53 +189,53 @@ class db_pager {
 	    if ($result) {
 		// setting field names for subsequent queries
 			$c = 0;
-		  // add result field names to column defs for 
-		  // col value retrieve and sort purposes 
-		   $cnt = min(db_num_fields($result), count($this->columns));
+		  	// add result field names to column defs for 
+		  	// col value retrieve and sort purposes 
+		   	$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']= db_field_name($result, $i);
 					if (!@($this->columns[$c]['type']=='insert'))
-			 		$i++;
-			 		}
+			 			$i++;
+			 	}
 		  	}
 		  	while ($row = db_fetch_assoc($result)) {
 				$this->data[] = $row;
 		  	}
 		} else 
-		  return false;
+		  	return false;
 		return true;
-	}	    
+	}
 	//
 	//	Calculates page numbers for html controls.
 	//
 	function set_page($to) 
 	{
 	    switch($to) {
-		case 'next':
-		    $page = $this->curr_page+1; break;
-		case 'prev':
-		    $page = $this->curr_page-1; break;
-		case 'last':
-		    $page = $this->last_page; break;
-		default:
-		    if (is_numeric($to)) {
-			 $page = $to; break;
-		    }
-		case 'first':
-		    $page = 1; break;
+			case 'next':
+			    $page = $this->curr_page+1; break;
+			case 'prev':
+			    $page = $this->curr_page-1; break;
+			case 'last':
+			    $page = $this->last_page; break;
+			default:
+			    if (is_numeric($to)) {
+				 	$page = $to; break;
+			    }
+			case 'first':
+			    $page = 1; break;
 	    }
-	  if ($page < 1) 
-	    $page = 1;
-	  $max = $this->max_page;
-	  if ($page > $max) 
-	    $page = $max;
-	  $this->curr_page = $page;
-	  $this->next_page = ($page < $max) ? $page+1 : null;
-	  $this->prev_page = ($page > 1) ? ($page-1) : null;
-	  $this->last_page = ($page < $max) ? $max : null;
-	  $this->first_page = ($page != 1) ? 1: null;
+	  	if ($page < 1) 
+	    	$page = 1;
+	  	$max = $this->max_page;
+	  	if ($page > $max) 
+	    	$page = $max;
+	  	$this->curr_page = $page;
+	  	$this->next_page = ($page < $max) ? $page+1 : null;
+	  	$this->prev_page = ($page > 1) ? ($page-1) : null;
+	  	$this->last_page = ($page < $max) ? $max : null;
+	  	$this->first_page = ($page != 1) ? 1: null;
 	}
 	//
 	//	Set column definitions
@@ -216,8 +248,8 @@ class db_pager {
 		}
 		foreach ($flds as $colnum=>$coldef) {
 			if (is_string($colnum)) {	// 'colname'=>params
-			  $h = $colnum;
-			  $c = $coldef;
+			  	$h = $colnum;
+			  	$c = $coldef;
 			} else {			//  n=>params
 				if (is_array($coldef)) {
 					$h = '';
@@ -228,10 +260,10 @@ class db_pager {
 				}
 			}
 			if (is_string($c)) 			// params is simple column type
-			  $c = array('type'=>$c);
+			  	$c = array('type'=>$c);
 
 			if (!isset($c['type']))
-			  $c['type'] = 'text';
+			  	$c['type'] = 'text';
 
 			switch($c['type']) {
 				case 'inactive': 
@@ -260,30 +292,37 @@ 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";
-		}
+		if ($count)
+			return "SELECT COUNT(*) FROM ($this->sql) tmp_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'];
 			    }
 			}
 	    }
-				
+
 	    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
@@ -302,7 +341,7 @@ class db_pager {
 	//
 	function _init() 
 	{
-		global $go_debug;
+		global $SysPrefs;
 		
 	    if ($this->ready == false ) {
 			$sql = $this->_sql_gen(true);
@@ -314,14 +353,14 @@ class db_pager {
 	  		$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) {
 		    	   	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.");
 			    	}
 				}
 	    	}
@@ -392,9 +431,6 @@ class db_pager {
 	function inactive_control_cell(&$row)
 	{
 		if ($this->inactive_ctrl) {
-//			return inactive_control_cell($row[$this->inactive_ctrl['key']],
-//				 $row['inactive'], $this->inactive_ctrl['table'], 
-//				 $this->inactive_ctrl['key']);
 				 
 			global	$Ajax;
 
@@ -411,7 +447,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
@@ -470,4 +506,3 @@ function refresh_pager($name)
 	if (isset($_SESSION[$name]))
 		$_SESSION[$name]->ready = false;
 }
-?>
\ No newline at end of file