2 /**********************************************************************
3 Copyright (C) FrontAccounting, LLC.
4 Released under the terms of the GNU General Public License, GPL,
5 as published by the Free Software Foundation, either version 3
6 of the License, or (at your option) any later version.
7 This program is distributed in the hope that it will be useful,
8 but WITHOUT ANY WARRANTY; without even the implied warranty of
9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
11 ***********************************************************************/
13 // Controler part of database table pager with column sort.
14 // To display actual html object call display_db_pager($name) inside
17 // View definition you will find in the following file:
18 include_once($path_to_root."/includes/ui/db_pager_view.inc");
23 var $columns; // column definitions (head, type, order)
25 var $marker; // marker check function
29 var $width; // table width (default '95%')
30 var $header_fun; // additional row between title and body
53 var $ready = false; // this var is false after change in sql before first
54 // and before first query.
55 var $inactive_ctrl = false;
56 var $main_tbl; // table and key field name for inactive ctrl and edit/delete links
57 var $key; // key field name
59 // db_pager constructor
60 // accepts $sql query either as:
61 // a. string in form 'SELECT field_list FROM table_joins [WHERE conditions [GROUP group_list [ORDER order_list]]]'
62 // - if WHERE keyword is used in table_joins, WHERE conditions is obligatory
63 // b. associative array using select, where, group and order keys ex :
64 // array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location')
66 // $name is base name for pager controls
67 function db_pager($sql, $name, $table = null, $page_len=0)
70 if ($page_len == 0) $page_len = user_query_size();
72 $this->page_len = $page_len;
76 // Parse base sql select query or use an associative array.
78 function set_sql($sql)
80 if ($sql != $this->sql) {
85 foreach(explode(' ', 'select from where group order') as $section) {
86 $this->$section = @$sql[$section];
88 $this->select = "SELECT ".$this->select;
92 $parts = preg_split('/\sFROM\s/si', $sql, 2);
93 if (count($parts) == 2) {
94 $this->select = $parts[0];
98 display_error("Invalid sql input for db_pager");
101 $parts = preg_split('/\sWHERE(?!.*WHERE.*)\s/si', $sql, 2); // last occurence
102 if(count($parts) == 2) {
103 $this->from = $parts[0];
106 $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
107 if(count($parts) == 2) {
109 $this->order = $parts[1];
111 $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
112 if(count($parts) == 2) {
114 $this->group = $parts[1];
120 //_vd($this->select);
127 // Set additional constraint on record set
129 function set_where($where = null)
132 if (!is_array($where))
133 $where = array($where);
135 if (count($where) == count($this->extra_where) &&
136 !count(array_diff($this->extra_where, $where)))
139 $this->extra_where = $where;
140 $this->ready = false;
143 // Set query result page
145 function change_page($page=null)
147 $this->set_page($page);
152 // Change sort column direction
153 // in order asc->desc->none->asc
155 function sort_table($col)
159 foreach($this->columns as $id => $_col) {
160 if(!isset($_col['ord_priority'])) continue;
161 $max_priority = max($max_priority, $_col['ord_priority']);
165 $ord = $this->columns[$col]['ord'];
166 $this->columns[$col]['ord_priority'] = $max_priority+1; // set priority , higher than anything else
167 $ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
168 $this->columns[$col]['ord'] = $ord;
180 $Ajax->activate("_{$this->name}_span");
181 $this->data = array();
185 if ($this->rec_count == 0) return true;
187 $sql = $this->_sql_gen(false);
189 $result = db_query($sql, 'Error browsing database: '.$sql );
192 // setting field names for subsequent queries
194 // add result field names to column defs for
195 // col value retrieve and sort purposes
196 $cnt = min(db_num_fields($result), count($this->columns));
197 for ($c = $i = 0; $c < $cnt; $c++) {
198 if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) {
199 // if (!@($this->columns[$c]['type']=='skip'))
200 $this->columns[$c]['name']= db_field_name($result, $i);
201 if (!@($this->columns[$c]['type']=='insert'))
205 while ($row = db_fetch_assoc($result)) {
206 $this->data[] = $row;
213 // Calculates page numbers for html controls.
215 function set_page($to)
219 $page = $this->curr_page+1; break;
221 $page = $this->curr_page-1; break;
223 $page = $this->last_page; break;
225 if (is_numeric($to)) {
233 $max = $this->max_page;
236 $this->curr_page = $page;
237 $this->next_page = ($page < $max) ? $page+1 : null;
238 $this->prev_page = ($page > 1) ? ($page-1) : null;
239 $this->last_page = ($page < $max) ? $max : null;
240 $this->first_page = ($page != 1) ? 1: null;
243 // Set column definitions
244 // $flds: array( fldname1, fldname2=>type,...)
245 function set_columns($flds)
247 $this->columns = array();
248 if (!is_array($flds)) {
249 $flds = array($flds);
251 foreach ($flds as $colnum=>$coldef) {
252 if (is_string($colnum)) { // 'colname'=>params
255 } else { // n=>params
256 if (is_array($coldef)) {
264 if (is_string($c)) // params is simple column type
265 $c = array('type'=>$c);
267 if (!isset($c['type']))
272 $this->inactive_ctrl = true;
275 $c['head'] = $h; break;
276 case 'skip': // skip the column (no header)
277 unset($c['head']); break;
279 $this->columns[] = $c;
283 // Generate db query from base sql
284 // $count==false - for current page data retrieval
285 // $count==true - for total records count
287 function _sql_gen($count=false)
289 $select = $this->select;
291 $where = $this->where;
292 $group = $this->group;
293 $order = $this->order;
295 if(count($this->extra_where)) {
296 $where .= ($where=='' ? '' : ' AND ')
297 .implode(' AND ', $this->extra_where);
299 if ($where) $where = " WHERE ($where)";
302 $group = $group == '' ? "*" : "DISTINCT $group";
304 return "SELECT COUNT($group) FROM $from $where";
307 $sql = "$select FROM $from $where";
308 if ($group) $sql.= " GROUP BY $group";
311 // sort order column by priority instead of table order.
313 foreach ($this->columns as $col) {
314 if(isset($col['ord_priority'])) {
315 $columns[$col['ord_priority']] = $col;
320 foreach ($columns as $col) {
321 if (isset($col['ord'])) {
322 if ( $col['ord'] != '' && isset($col['name'])) {
323 $ord[] = $col['name'] .' '. $col['ord'];
329 $ord = array_map(function_exists('mysql_real_escape_string') ?
330 'mysql_real_escape_string': 'mysql_escape_string', $ord);
331 $sql .= " ORDER BY " . implode(',', $ord);
334 $sql .= " ORDER BY $order"; // original base query order
337 $page_len = $this->page_len;
338 $offset = ($this->curr_page - 1) * $page_len;
340 $sql .= " LIMIT $offset, $page_len";
346 // Initialization after changing record set
352 if ($this->ready == false ) {
353 $sql = $this->_sql_gen(true);
354 $result = db_query($sql, 'Error reading record set');
355 if ($result == false)
357 $row = db_fetch_row($result);
358 $this->rec_count = $row[0];
359 $this->max_page = $this->page_len ?
360 ceil($this->rec_count/$this->page_len) : 0;
362 if ($go_debug) { // FIX - need column name parsing, but for now:
363 // check if field names are set explicite in col def
364 // for all initially ordered columns
365 foreach ($this->columns as $col) {
366 if (isset($col['ord']) && $col['ord'] != ''
367 && !isset($col['name'])) {
368 display_warning("Result field names must be set
369 for all initially ordered db_pager columns.");
379 // Set current page in response to user control.
381 function select_records()
385 $page = find_submit($this->name.'_page_', false);
386 $sort = find_submit($this->name.'_sort_', true);
388 $this->change_page($page);
389 if ($page == 'next' && !$this->next_page ||
390 $page == 'last' && !$this->last_page)
391 set_focus($this->name.'_page_prev');
392 if ($page == 'prev' && !$this->prev_page ||
393 $page == 'first' && !$this->first_page)
394 set_focus($this->name.'_page_next');
395 } elseif ($sort != -1) {
396 $this->sort_table($sort);
401 // Set check function to mark some rows.
403 function set_marker($func, $notice='', $markercl='overduebg', $msgclass='overduefg' )
405 $this->marker = $func;
406 $this->marker_txt = $notice;
407 $this->marker_class = $markercl;
408 $this->notice_class = $msgclass;
411 // Set handler to display additional row between titles and pager body.
412 // Return array of column contents.
414 function set_header($func, $headercl='inquirybg')
416 $this->header_fun = $func;
417 $this->header_class = $headercl;
420 // Set handler to display additional row between pager body and navibar.
421 // Return array of column contents.
423 function set_footer($func, $footercl='inquirybg')
425 $this->footer_fun = $func;
426 $this->footer_class = $footercl;
429 // Setter for table editors with inactive cell control.
431 function set_inactive_ctrl($table, $key) {
432 $this->inactive_ctrl = array('table'=>$table, 'key'=>$key);
435 // Helper for display inactive control cells
437 function inactive_control_cell(&$row)
439 if ($this->inactive_ctrl) {
440 // return inactive_control_cell($row[$this->inactive_ctrl['key']],
441 // $row['inactive'], $this->inactive_ctrl['table'],
442 // $this->inactive_ctrl['key']);
447 $this->key : $this->columns[0]['name']; // TODO - support for complex keys
449 $table = $this->main_tbl;
450 $name = "Inactive". $id;
451 $value = $row['inactive'] ? 1:0;
453 if (check_value('show_inactive')) {
454 if (isset($_POST['LInact'][$id]) && (get_post('_Inactive'.$id.'_update') ||
455 get_post('Update')) && (check_value('Inactive'.$id) != $value)) {
456 update_record_status($id, !$value, $table, $key);
459 echo '<td align="center">'. checkbox(null, $name, $value, true, '')
460 . hidden("LInact[$id]", $value, false) . '</td>';
467 //-----------------------------------------------------------------------------
468 // Creates new db_pager $_SESSION object on first page call.
469 // Retrieves from $_SESSION var on subsequent $_POST calls
471 // $name - base name for pager controls and $_SESSION object name
472 // $sql - base sql for data inquiry. Order of fields implies
473 // pager columns order.
474 // $coldef - array of column definitions. Example definitions
475 // Column with title 'User name' and default text format:
477 // Skipped field from sql query. Data for the field is not displayed:
479 // Column without title, data retrieved form row data with function func():
480 // array('fun'=>'func')
481 // Inserted column with title 'Some', formated with function rowfun().
483 // 'Some' => array('type'=>'date, 'insert'=>true, 'fun'=>'rowfun')
484 // Column with name 'Another', formatted as date,
485 // sortable with ascending start order (available orders: asc,desc, '').
486 // 'Another' => array('type'=>'date', 'ord'=>'asc')
488 // All available column format types you will find in db_pager_view.inc file.
489 // If query result has more fields than count($coldef), rest of data is ignored
490 // during display, but can be used in format handlers for 'spec' and 'insert'
493 function &new_db_pager($name, $sql, $coldef, $table = null, $key = null, $page_len = 0) {
495 if (isset($_SESSION[$name]) &&
496 ($_SERVER['REQUEST_METHOD'] == 'GET' || $_SESSION[$name]->sql != $sql)) {
497 unset($_SESSION[$name]); // kill pager if sql has changed
499 if (!isset($_SESSION[$name])) {
500 $_SESSION[$name] = new db_pager($sql, $name, $table, $page_len);
501 $_SESSION[$name]->main_tbl = $table;
502 $_SESSION[$name]->key = $key;
503 $_SESSION[$name]->set_sql($sql);
504 $_SESSION[$name]->set_columns($coldef);
507 return $_SESSION[$name];
511 // Force pager initialization.
513 function refresh_pager($name)
515 if (isset($_SESSION[$name]))
516 $_SESSION[$name]->ready = false;