<?php
+/**********************************************************************
+ Copyright (C) FrontAccounting, LLC.
+ Released under the terms of the GNU General Public License, GPL,
+ as published by the Free Software Foundation, either version 3
+ of the License, or (at your option) any later version.
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
+ See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
+***********************************************************************/
//
// Controler part of database table pager with column sort.
// To display actual html object call display_db_pager($name) inside
var $marker_txt;
var $marker_class;
var $notice_class;
-
+ 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();
var $curr_page,
var $select,
$where,
$from,
- $group;
+ $group,
+ $order;
var $extra_where;
- var $ready = false;
-
+ var $ready = false; // this var is false after change in sql before first
+ // and before first query.
+ var $inactive_ctrl = false;
+ var $main_tbl; // table and key field name for inactive ctrl and edit/delete links
+ var $key; // key field name
+
// db_pager constructor
- // accepts $sql like 'SELECT .... FROM .... [WHERE ...] [GROUP ...]'
+ // 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, $page_len=0)
+ function db_pager($sql, $name, $table = null, $page_len=0)
{
+ $this->width = "95%";
if ($page_len == 0) $page_len = user_query_size();
$this->name = $name;
$this->page_len = $page_len;
$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;
- $select = $sql;
- $from = $where = $group = '';
- $parts = preg_split('/\sFROM\s/si', $sql, 2);
- if(count($parts) == 2) {
- $select = $parts[0];
- $from = $parts[1];
- $parts = preg_split('/\sWHERE\s/si', $from, 2);
- if(count($parts) == 2) {
- $from = $parts[0];
- $where = $parts[1];
- $parts = preg_split('/\sGROUP\s*BY\s/si', $where, 2);
- if(count($parts) == 2) {
- $where = $parts[0];
- $group = $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 = $select;
- $this->from = $from;
- $this->where = $where;
- $this->group = $group;
-/*
- display_error("sql: $sql");
- display_error($select);
- display_error("FROM $from");
- display_error("WHERE $where");
- display_error("GROUP BY $group");
-*/
}
}
//
// Set additional constraint on record set
//
- function set_where($where)
+ function set_where($where = null)
{
- if (!is_array($where))
- $where = array($where);
+ if ($where) {
+ if (!is_array($where))
+ $where = array($where);
- if (count($where) != count($this->extra_where) ||
- count(array_diff($this->extra_where, $where))) {
- $this->extra_where = $where;
- $this->ready = false;
- }
+ if (count($where) == count($this->extra_where) &&
+ !count(array_diff($this->extra_where, $where)))
+ return;
+ }
+ $this->extra_where = $where;
+ $this->ready = false;
}
//
// Set query result page
//
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);
if ($result) {
// setting field names for subsequent queries
$c = 0;
- // add result field names to column defs for
- // col value retrieve and sort purposes
- for ($c = $i = 0; $c < count($this->columns); $c++) {
- if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert']))
- $this->columns[$c]['name']= mysql_field_name($result, $i++);
+ // 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++;
+ }
}
-
- while ($row = db_fetch_assoc($result))
+ 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
}
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 = '';
$c = $coldef;
} else {
- $h = $coldef;
+ $h = $coldef;
$c = 'text';
}
}
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 'insert':
- default:
- $c['head'] = $h; break;
- case 'skip': // skip the column (no header)
- unset($c['head']); // paranoid code
+ case 'inactive':
+ $this->inactive_ctrl = true;
+ case 'insert':
+ default:
+ $c['head'] = $h; break;
+ case 'skip': // skip the column (no header)
+ unset($c['head']); break;
}
$this->columns[] = $c;
}
$select = $this->select;
$from = $this->from;
$where = $this->where;
- $group = $this->group;
+ $group = $this->group;
+ $order = $this->order;
if(count($this->extra_where)) {
- $wherw .= ($where=='' ? '' : ' AND ')
- .implode( $this->extra_where, ' AND ');
+ $where .= ($where=='' ? '' : ' AND ')
+ .implode(' AND ', $this->extra_where);
}
- if ($where) $where = " WHERE($where)";
-
- if ($count) {
- $group = $group == '' ? "*" : "DISTINCT $group";
+ if ($where) $where = " WHERE ($where)";
- 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";
+ if ($group) $sql.= " GROUP BY $group";
$ord = array();
- foreach( $this->columns as $col) {
+ // 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, ',');
+
+ if (count($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;
//
function _init()
{
+ global $SysPrefs;
+
if ($this->ready == false ) {
$sql = $this->_sql_gen(true);
$result = db_query($sql, 'Error reading record set');
return false;
$row = db_fetch_row($result);
$this->rec_count = $row[0];
- $this->max_page = ceil($this->rec_count/$this->page_len);
+ $this->max_page = $this->page_len ?
+ ceil($this->rec_count/$this->page_len) : 0;
+
+ 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 initially ordered db_pager columns.");
+ }
+ }
+ }
$this->set_page(1);
$this->ready = true;
}
$this->marker_class = $markercl;
$this->notice_class = $msgclass;
}
+ //
+ // Set handler to display additional row between titles and pager body.
+ // Return array of column contents.
+ //
+ function set_header($func, $headercl='inquirybg')
+ {
+ $this->header_fun = $func;
+ $this->header_class = $headercl;
+ }
+ //
+ // Set handler to display additional row between pager body and navibar.
+ // Return array of column contents.
+ //
+ function set_footer($func, $footercl='inquirybg')
+ {
+ $this->footer_fun = $func;
+ $this->footer_class = $footercl;
+ }
+ //
+ // Setter for table editors with inactive cell control.
+ //
+ function set_inactive_ctrl($table, $key) {
+ $this->inactive_ctrl = array('table'=>$table, 'key'=>$key);
+ }
+ //
+ // Helper for display inactive control cells
+ //
+ function inactive_control_cell(&$row)
+ {
+ if ($this->inactive_ctrl) {
+
+ global $Ajax;
+
+ $key = $this->key ?
+ $this->key : $this->columns[0]['name']; // TODO - support for complex keys
+ $id = $row[$key];
+ $table = $this->main_tbl;
+ $name = "Inactive". $id;
+ $value = $row['inactive'] ? 1:0;
+
+ if (check_value('show_inactive')) {
+ if (isset($_POST['LInact'][$id]) && (get_post('_Inactive'.$id.'_update') ||
+ get_post('Update')) && (check_value('Inactive'.$id) != $value)) {
+ update_record_status($id, !$value, $table, $key);
+ $value = !$value;
+ }
+ echo '<td align="center">'. checkbox(null, $name, $value, true, '')
+ . hidden("LInact[$id]", $value, false) . '</td>';
+ }
+ } else
+ return '';
+ }
+
};
//-----------------------------------------------------------------------------
// Creates new db_pager $_SESSION object on first page call.
// during display, but can be used in format handlers for 'spec' and 'insert'
// type columns.
-function &new_db_pager($name, $sql, $coldef, $page_len = 0) {
-
- if (isset($name) && isset($_SESSION[$name])) {
- // kill old pager if any on first page call
- if ($_SERVER['REQUEST_METHOD'] == 'GET')
- unset($_SESSION[$name]);
- else
- return $_SESSION[$name];
- }
- $ret =& new db_pager($sql, $name, $page_len);
- $ret->set_columns($coldef);
+function &new_db_pager($name, $sql, $coldef, $table = null, $key = null, $page_len = 0) {
- if (isset($name)) $_SESSION[$name] = &$ret;
+ if (isset($_SESSION[$name]) &&
+ ($_SERVER['REQUEST_METHOD'] == 'GET' || $_SESSION[$name]->sql != $sql)) {
+ unset($_SESSION[$name]); // kill pager if sql has changed
+ }
+ if (!isset($_SESSION[$name])) {
+ $_SESSION[$name] = new db_pager($sql, $name, $table, $page_len);
+ $_SESSION[$name]->main_tbl = $table;
+ $_SESSION[$name]->key = $key;
+ $_SESSION[$name]->set_sql($sql);
+ $_SESSION[$name]->set_columns($coldef);
+ }
+
+ return $_SESSION[$name];
- return $ret;
}
-
-?>
\ No newline at end of file
+//
+// Force pager initialization.
+//
+function refresh_pager($name)
+{
+ if (isset($_SESSION[$name]))
+ $_SESSION[$name]->ready = false;
+}
projects / fa-stable.git / blobdiff