<?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 $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.
// db_pager constructor
- // accepts $sql like 'SELECT .... FROM .... [WHERE ...] [GROUP ...]'
+ // accepts $sql like 'SELECT ...[FROM ...][WHERE ...][GROUP ...][ORDER ...]'
// $name is base name for pager controls
function db_pager($sql, $name, $page_len=0)
{
+ global $table_style;
+ $this->width = "95%";
if ($page_len == 0) $page_len = user_query_size();
$this->name = $name;
$this->page_len = $page_len;
if ($sql != $this->sql) {
$this->sql = $sql;
$this->ready = false;
- $select = $sql;
- $from = $where = $group = '';
+ $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) {
- $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];
- }
- }
+ $sql = $parts[0];
+ $this->from = $parts[1];
}
- $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");
-*/
+ $this->select = $sql;
}
}
//
// 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
// Change sort column direction
// in order asc->desc->none->asc
//
- function sort_table($col=null)
+ function sort_table($col)
{
$ord = $this->columns[$col]['ord'];
$ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
// add result field names to column defs for
// col value retrieve and sort purposes
for ($c = $i = 0; $c < count($this->columns); $c++) {
- if ($this->columns[$c]['type'] != 'insert')
+ if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert']))
$this->columns[$c]['name']= mysql_field_name($result, $i++);
}
$flds = array($flds);
}
foreach ($flds as $colnum=>$coldef) {
- if (!is_numeric($colnum)) { // 'colname'=>params
+ if (is_string($colnum)) { // 'colname'=>params
$h = $colnum;
$c = $coldef;
} else { // n=>params
$h = $coldef;
$c = 'text';
}
- $c = '';
}
- if (!is_array($c)) // params is simple column type
+ if (is_string($c)) // params is simple column type
$c = array('type'=>$c);
if (!isset($c['type']))
$from = $this->from;
$where = $this->where;
$group = $this->group;
+ $order = $this->order;
if(count($this->extra_where)) {
- $wherw .= ($where=='' ? '' : ' AND ')
+ $where .= ($where=='' ? '' : ' AND ')
.implode( $this->extra_where, ' AND ');
}
- if ($where) $where = " WHERE($where)";
+ if ($where) $where = " WHERE ($where)";
if ($count) {
$group = $group == '' ? "*" : "DISTINCT $group";
return "SELECT COUNT($group) FROM $from $where";
}
- $sql = "$select FROM $from $where GROUP BY $group";
+ $sql = "$select FROM $from $where";
+ if ($group) $sql.= " GROUP BY $group";
$ord = array();
- foreach( $this->columns as $col) {
+ foreach ($this->columns as $col) {
if (isset($col['ord'])) {
if ( $col['ord'] != '' && isset($col['name'])) {
$ord[] = $col['name'] .' '. $col['ord'];
}
}
- if (count($ord))
+ if (count($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 $go_debug;
+
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 ($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.");
+ }
+ }
+ }
$this->set_page(1);
$this->ready = true;
}
global $Ajax;
$page = find_submit($this->name.'_page_', false);
- $sort = find_submit($this->name.'_sort_', false);
+ $sort = find_submit($this->name.'_sort_', true);
if ($page) {
$this->change_page($page);
if ($page == 'next' && !$this->next_page ||
if ($page == 'prev' && !$this->prev_page ||
$page == 'first' && !$this->first_page)
set_focus($this->name.'_page_next');
- } elseif ($sort) {
+ } elseif ($sort != -1) {
$this->sort_table($sort);
} else
$this->query();
$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;
+ }
};
//-----------------------------------------------------------------------------
// Creates new db_pager $_SESSION object on first page call.
// $sql - base sql for data inquiry. Order of fields implies
// pager columns order.
// $coldef - array of column definitions. Example definitions
-// Text column with title 'User name':
+// Column with title 'User name' and default text format:
// 'User name'
// Skipped field from sql query. Data for the field is not displayed:
// 'dummy' => 'skip'
-// Column without title, formated with function func(). Field value
-// is passed as parameter:
-// array('type'=>'spec', 'fun'=>'func')
-// Inserted column with title 'Some', formated with function rowfun(). Row
-// values are passed as parameter array:
-// 'Some' => array('type'=>'insert', 'fun'=>'rowfun')
+// Column without title, data retrieved form row data with function func():
+// array('fun'=>'func')
+// Inserted column with title 'Some', formated with function rowfun().
+// formated as date:
+// 'Some' => array('type'=>'date, 'insert'=>true, 'fun'=>'rowfun')
// Column with name 'Another', formatted as date,
// sortable with ascending start order (available orders: asc,desc, '').
// 'Another' => array('type'=>'date', 'ord'=>'asc')
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);
+ if ($_SERVER['REQUEST_METHOD'] == 'GET')
+ unset($_SESSION[$name]); // kill old pager if any exists on first page call
- if (isset($name)) $_SESSION[$name] = &$ret;
+ if (!isset($_SESSION[$name])) {
+ $_SESSION[$name] =& new db_pager($sql, $name, $page_len);
+ $_SESSION[$name]->set_sql($sql);
+ $_SESSION[$name]->set_columns($coldef);
+ }
+
+ $ret = &$_SESSION[$name];
return $ret;
}
-
+//
+// Force pager initialization.
+//
+function refresh_pager($name)
+{
+ if (isset($_SESSION[$name]))
+ $_SESSION[$name]->ready = false;
+}
?>
\ No newline at end of file