X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb_pager.inc;h=affb9e09796e9cf046abdb0998817ed533eac076;hb=268a54dcfd08aff7113bc3afe8b784f36db2d0d4;hp=b099e3dd71ac0006f783f24335ed0973a8912563;hpb=264c09696d65efe4532c197f317162daf8c24f32;p=fa-stable.git
diff --git a/includes/db_pager.inc b/includes/db_pager.inc
index b099e3dd..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();
@@ -39,13 +40,16 @@ class db_pager {
$prev_page,
$next_page,
$first_page;
-
+
var $page_len,
$rec_count;
-
- var $order = array();
-
- var $extra_where;
+
+ var $select,
+ $where,
+ $from,
+ $group,
+ $order;
+ var $extra_where = array();
var $ready = false; // this var is false after change in sql before first
// and before first query.
@@ -54,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();
@@ -65,13 +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;
+
+ 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;
+ }
+ }
}
}
//
@@ -105,17 +152,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;
}
//
@@ -139,22 +189,22 @@ 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;
}
//
@@ -163,43 +213,43 @@ class db_pager {
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
// $flds: array( fldname1, fldname2=>type,...)
function set_columns($flds)
{
- $this->columns = $this->order = array();
+ $this->columns = array();
if (!is_array($flds)) {
$flds = array($flds);
}
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 = '';
@@ -210,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':
@@ -224,9 +274,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,31 +284,44 @@ class db_pager {
//
function _sql_gen($count=false)
{
-
- if (count($this->extra_where)) {
- $where = ' WHERE ('.implode(' AND ', $this->extra_where).')';
- } else
- $where = '';
+ $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)
- return "SELECT COUNT(*) FROM ($this->sql) $where AS _dummyname";
-
+ return "SELECT COUNT(*) FROM ($this->sql) tmp_count";
+ $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)) {
- $ord = array_map(function_exists('mysql_real_escape_string') ?
- 'mysql_real_escape_string': 'mysql_escape_string', $ord);
+ $ord = array_map('db_escape_function', $ord);
$sql .= " ORDER BY " . implode(',', $ord);
} else {
if($order)
@@ -270,19 +331,18 @@ class db_pager {
$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');
@@ -292,8 +352,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) {
@@ -371,10 +431,7 @@ 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;
$key = $this->key ?
@@ -390,7 +447,7 @@ class db_pager {
update_record_status($id, !$value, $table, $key);
$value = !$value;
}
- echo '| '. checkbox(null, $name, $value, true, '', "align='center'")
+ echo ' | '. checkbox(null, $name, $value, true, '')
. hidden("LInact[$id]", $value, false) . ' | ';
}
} else
@@ -449,4 +506,3 @@ function refresh_pager($name)
if (isset($_SESSION[$name]))
$_SESSION[$name]->ready = false;
}
-?>
\ No newline at end of file