Merged changes from master branch up to current state.
[fa-stable.git] / includes / db_pager.inc
1 <?php
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 ***********************************************************************/
12 //
13 //      Controler part of database table pager with column sort.
14 //      To display actual html object call display_db_pager($name) inside
15 //  any form.
16 //
17 //      View definition you will find in the following file:
18 include_once($path_to_root."/includes/ui/db_pager_view.inc");
19
20 class db_pager {
21         var $sql;
22         var $name;
23         var $columns;           // column definitions (head, type, order)
24
25         var $marker;            // marker check function
26         var $marker_txt;        
27         var $marker_class;
28         var $notice_class;
29         var $width;                     // table width (default '95%')
30         var $header_fun;        // additional row between title and body
31         var $header_class;
32         var $footer_fun;
33         var $footer_class;
34         var $data = array();
35
36         var $curr_page,
37                 $max_page,
38             $last_page, 
39             $prev_page, 
40             $next_page,
41             $first_page;
42             
43         var $page_len,
44             $rec_count;
45         
46         var $select,
47                 $where,
48             $from,
49                 $group,
50                 $order;
51         var     $extra_where;
52         
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
58         
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')
65         //
66         //      $name is base name for pager controls
67         function db_pager($sql, $name, $table = null, $page_len=0) 
68         {
69                 $this->width = "95%";
70                 if ($page_len == 0) $page_len = user_query_size();
71                 $this->name = $name;
72                 $this->page_len = $page_len;
73                 $this->set_sql($sql);
74         }
75         //
76         //      Parse base sql select query     or use an associative array.
77         //
78         function set_sql($sql)
79         {
80                 global $SysPrefs;
81
82                 if ($sql != $this->sql) {
83                     $this->sql = $sql;
84                     $this->ready = false;
85
86                                 if(is_array($sql)) {
87                                         foreach(explode(' ', 'select from where group order') as $section) {
88                                                 $this->$section = @$sql[$section];
89                                         }
90                                         $this->select = "SELECT ".$this->select;
91                                 }
92                                 else {
93                                         // parse the query
94                                         $parts = preg_split('/\sFROM\s/si', $sql, 2);
95                                         if (count($parts) == 2) {
96                                                 $this->select = $parts[0];
97                                                 $sql = $parts[1];
98                                         } else {
99                                                 if ($SysPrefs->go_debug)
100                                                         display_error("Invalid sql input for db_pager");
101                                         }
102
103                                         $parts = preg_split('/\sWHERE(?!.*WHERE.*)\s/si', $sql, 2); // last occurence
104                                         if(count($parts) == 2) {
105                                                 $this->from = $parts[0];
106                                                 $sql = $parts[1];
107
108                                                 $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
109                                                 if(count($parts) == 2) {
110                                                         $sql = $parts[0];
111                                                         $this->order = $parts[1];
112                                                 }
113                                                 $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
114                                                 if(count($parts) == 2) {
115                                                         $sql = $parts[0];
116                                                         $this->group = $parts[1];
117                                                 }
118                                                 $this->where = $sql;
119                                         }
120                         }
121                 }
122                 //_vd($this->select);
123                 //_vd($this->from);
124                 //_vd($this->where);
125                 //_vd($this->group);
126                 //_vd($this->order);
127         }
128         //
129         //      Set additional constraint on record set
130         //
131         function set_where($where = null)
132         {
133                 if ($where) {
134                 if (!is_array($where))
135                           $where = array($where);
136
137                     if (count($where) == count($this->extra_where) &&
138                                 !count(array_diff($this->extra_where, $where)))
139                                  return;
140                 }
141                 $this->extra_where = $where;
142                 $this->ready = false;
143         }
144         //
145         //      Set query result page
146         //
147         function change_page($page=null) 
148         {
149             $this->set_page($page);
150             $this->query();
151             return true;
152         }
153         //
154         //      Change sort column direction 
155         //      in order asc->desc->none->asc
156         //
157         function sort_table($col) 
158         {
159
160                         $max_priority = 0;
161                         foreach($this->columns as $id => $_col) {
162                         if(!isset($_col['ord_priority'])) continue;
163                                 $max_priority = max($max_priority, $_col['ord_priority']);
164                         };
165
166
167             $ord = $this->columns[$col]['ord'];
168                 $this->columns[$col]['ord_priority']  = $max_priority+1; // set priority , higher than anything else
169             $ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
170             $this->columns[$col]['ord'] = $ord;
171             $this->set_page(1);
172             $this->query();
173             return true;
174         }
175         //
176         // Query database
177         //
178         function query() 
179         {
180                 global $Ajax;
181
182                 $Ajax->activate("_{$this->name}_span");
183             $this->data = array();
184             if (!$this->_init()) 
185                   return false;
186
187             if ($this->rec_count == 0) return true;
188
189             $sql = $this->_sql_gen(false);
190
191             $result = db_query($sql, 'Error browsing database: '.$sql );
192
193             if ($result) {
194                 // setting field names for subsequent queries
195                         $c = 0;
196                   // add result field names to column defs for 
197                   // col value retrieve and sort purposes 
198                    $cnt = min(db_num_fields($result), count($this->columns));
199                         for ($c = $i = 0; $c < $cnt; $c++) {
200                                 if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) {
201 //                                      if (!@($this->columns[$c]['type']=='skip'))
202                                                 $this->columns[$c]['name']= db_field_name($result, $i);
203                                         if (!@($this->columns[$c]['type']=='insert'))
204                                         $i++;
205                                         }
206                         }
207                         while ($row = db_fetch_assoc($result)) {
208                                 $this->data[] = $row;
209                         }
210                 } else 
211                   return false;
212                 return true;
213         }
214         //
215         //      Calculates page numbers for html controls.
216         //
217         function set_page($to) 
218         {
219             switch($to) {
220                 case 'next':
221                     $page = $this->curr_page+1; break;
222                 case 'prev':
223                     $page = $this->curr_page-1; break;
224                 case 'last':
225                     $page = $this->last_page; break;
226                 default:
227                     if (is_numeric($to)) {
228                          $page = $to; break;
229                     }
230                 case 'first':
231                     $page = 1; break;
232             }
233           if ($page < 1) 
234             $page = 1;
235           $max = $this->max_page;
236           if ($page > $max) 
237             $page = $max;
238           $this->curr_page = $page;
239           $this->next_page = ($page < $max) ? $page+1 : null;
240           $this->prev_page = ($page > 1) ? ($page-1) : null;
241           $this->last_page = ($page < $max) ? $max : null;
242           $this->first_page = ($page != 1) ? 1: null;
243         }
244         //
245         //      Set column definitions
246         //  $flds: array( fldname1, fldname2=>type,...)
247         function set_columns($flds)
248         {
249                 $this->columns = array();
250                 if (!is_array($flds)) {
251                         $flds = array($flds);
252                 }
253                 foreach ($flds as $colnum=>$coldef) {
254                         if (is_string($colnum)) {       // 'colname'=>params
255                           $h = $colnum;
256                           $c = $coldef;
257                         } else {                        //  n=>params
258                                 if (is_array($coldef)) {
259                                         $h = '';
260                                         $c = $coldef;
261                                 } else {
262                                         $h = $coldef;
263                                         $c = 'text';
264                                 }
265                         }
266                         if (is_string($c))                      // params is simple column type
267                           $c = array('type'=>$c);
268
269                         if (!isset($c['type']))
270                           $c['type'] = 'text';
271
272                         switch($c['type']) {
273                                 case 'inactive': 
274                                         $this->inactive_ctrl = true;
275                                 case 'insert':
276                                 default:
277                                         $c['head'] = $h; break;
278                                 case 'skip':            // skip the column (no header)
279                                         unset($c['head']); break;
280                         }
281                         $this->columns[] = $c;  
282                 }
283         }
284         //
285         // Generate db query from base sql
286         // $count==false - for current page data retrieval 
287         // $count==true  - for total records count
288         //
289         function _sql_gen($count=false) 
290         {
291                 $select = $this->select;
292                 $from = $this->from;
293                 $where = $this->where;
294                 $group = $this->group;
295                 $order = $this->order;
296
297                 if(count($this->extra_where)) {
298                     $where .= ($where=='' ? '' : ' AND ')
299                                 .implode(' AND ', $this->extra_where);
300                 }
301                 if ($where) $where = " WHERE ($where)";
302
303                 if ($count)
304                         return "SELECT COUNT(*) FROM ($this->sql) tmp_count";
305
306                 $sql = "$select FROM $from $where";
307                 if ($group) $sql.= " GROUP BY $group";
308             $ord = array();
309
310                         // sort order column by priority instead of table order.
311                         $columns = array();
312             foreach ($this->columns as $col) {
313                                         if(isset($col['ord_priority'])) {
314                                                 $columns[$col['ord_priority']] = $col;
315                                         }
316                 }
317                 krsort($columns);
318
319             foreach ($columns as $col) {
320                 if (isset($col['ord'])) {
321                         if ( $col['ord'] != '' && isset($col['name'])) {
322                             $ord[] = $col['name'] .' '. $col['ord'];
323                             }
324                         }
325             }
326
327             if (count($ord)) {
328                         $ord = array_map('db_escape_function', $ord);
329                         $sql .= " ORDER BY " . implode(',', $ord);
330                 } else {
331                         if($order)
332                                 $sql .= " ORDER BY $order"; // original base query order
333                 }
334
335             $page_len = $this->page_len;
336             $offset = ($this->curr_page - 1) * $page_len;
337
338             $sql .= " LIMIT $offset, $page_len";
339
340                 return $sql;
341                 
342         }
343         //
344         //      Initialization after changing record set
345         //
346         function _init() 
347         {
348                 global $SysPrefs;
349                 
350             if ($this->ready == false ) {
351                         $sql = $this->_sql_gen(true);
352                         $result = db_query($sql, 'Error reading record set');
353                         if ($result == false) 
354                                 return false;
355                         $row = db_fetch_row($result);
356                         $this->rec_count = $row[0];
357                         $this->max_page = $this->page_len ?
358                                 ceil($this->rec_count/$this->page_len) : 0;
359                 
360                         if ($SysPrefs->go_debug) { // FIX - need column name parsing, but for now:
361                                 // check if field names are set explicite in col def
362                                 // for all initially ordered columns
363                             foreach ($this->columns as $col) {
364                                 if (isset($col['ord']) && $col['ord'] != '' 
365                                                 &&  !isset($col['name'])) {
366                                                         display_warning("Result field names must be set
367                                                                 for all initially ordered db_pager columns.");
368                                 }
369                                 }
370                 }
371                         $this->set_page(1);
372                         $this->ready = true;
373             }
374         return true;
375         }
376         //
377         //      Set current page in response to user control.
378         //
379         function select_records() 
380         {
381                 global $Ajax;
382                 
383                 $page = find_submit($this->name.'_page_', false);
384                 $sort = find_submit($this->name.'_sort_', true);
385                 if ($page) {
386                         $this->change_page($page);
387                         if ($page == 'next' && !$this->next_page ||
388                                 $page == 'last' && !$this->last_page)
389                                         set_focus($this->name.'_page_prev');
390                         if ($page == 'prev' && !$this->prev_page ||
391                                 $page == 'first' && !$this->first_page)
392                                         set_focus($this->name.'_page_next');
393                 } elseif ($sort != -1) {
394                         $this->sort_table($sort);
395                 } else
396                         $this->query();
397         }
398         //
399         //      Set check function to mark some rows.
400         //      
401         function set_marker($func, $notice='', $markercl='overduebg', $msgclass='overduefg' )
402         {
403                 $this->marker = $func;
404                 $this->marker_txt = $notice;
405                 $this->marker_class = $markercl;
406                 $this->notice_class = $msgclass;
407         }
408         //
409         //      Set handler to display additional row between titles and pager body.
410         //      Return array of column contents.
411         //
412         function set_header($func, $headercl='inquirybg')
413         {
414                 $this->header_fun = $func;
415                 $this->header_class = $headercl;
416         }
417         //
418         //      Set handler to display additional row between pager body and navibar.
419         //      Return array of column contents.
420         //
421         function set_footer($func, $footercl='inquirybg')
422         {
423                 $this->footer_fun = $func;
424                 $this->footer_class = $footercl;
425         }
426         //
427         //      Setter for table editors with inactive cell control.
428         //
429         function set_inactive_ctrl($table, $key) {
430                 $this->inactive_ctrl = array('table'=>$table, 'key'=>$key);
431         }
432         //
433         //      Helper for display inactive control cells
434         //
435         function inactive_control_cell(&$row)
436         {
437                 if ($this->inactive_ctrl) {
438 //                      return inactive_control_cell($row[$this->inactive_ctrl['key']],
439 //                               $row['inactive'], $this->inactive_ctrl['table'], 
440 //                               $this->inactive_ctrl['key']);
441                                  
442                         global  $Ajax;
443
444                         $key = $this->key ?
445                                 $this->key : $this->columns[0]['name'];         // TODO - support for complex keys
446                         $id = $row[$key];
447                         $table = $this->main_tbl;
448                         $name = "Inactive". $id;
449                         $value = $row['inactive'] ? 1:0;
450
451                         if (check_value('show_inactive')) {
452                                 if (isset($_POST['LInact'][$id]) && (get_post('_Inactive'.$id.'_update') || 
453                                         get_post('Update')) && (check_value('Inactive'.$id) != $value)) {
454                                         update_record_status($id, !$value, $table, $key);
455                                         $value = !$value;
456                                 }
457                                 echo '<td align="center">'. checkbox(null, $name, $value, true, '')
458                                 . hidden("LInact[$id]", $value, false) . '</td>';       
459                         }
460                 } else
461                         return '';
462         }
463
464 };
465 //-----------------------------------------------------------------------------
466 //      Creates new db_pager $_SESSION object on first page call.
467 //  Retrieves from $_SESSION var on subsequent $_POST calls
468 //
469 //  $name - base name for pager controls and $_SESSION object name
470 //  $sql  - base sql for data inquiry. Order of fields implies
471 //              pager columns order.
472 //      $coldef - array of column definitions. Example definitions
473 //              Column with title 'User name' and default text format:
474 //                              'User name'
475 //              Skipped field from sql query. Data for the field is not displayed:
476 //                              'dummy' => 'skip'
477 //              Column without title, data retrieved form row data with function func():
478 //                              array('fun'=>'func')
479 //              Inserted column with title 'Some', formated with function rowfun().
480 //      formated as date:
481 //                              'Some' => array('type'=>'date, 'insert'=>true, 'fun'=>'rowfun')
482 //              Column with name 'Another', formatted as date, 
483 // sortable with ascending start order (available orders: asc,desc, '').
484 //                              'Another' => array('type'=>'date', 'ord'=>'asc')
485 //
486 //      All available column format types you will find in db_pager_view.inc file.
487 //              If query result has more fields than count($coldef), rest of data is ignored
488 //  during display, but can be used in format handlers for 'spec' and 'insert' 
489 //      type columns.
490
491 function &new_db_pager($name, $sql, $coldef, $table = null, $key = null, $page_len = 0)  {
492
493     if (isset($_SESSION[$name]) &&
494                  ($_SERVER['REQUEST_METHOD'] == 'GET' || $_SESSION[$name]->sql != $sql)) {
495                 unset($_SESSION[$name]); // kill pager if sql has changed
496         }
497         if (!isset($_SESSION[$name])) {
498             $_SESSION[$name] = new db_pager($sql, $name, $table, $page_len);
499                 $_SESSION[$name]->main_tbl = $table;
500                 $_SESSION[$name]->key = $key;
501                 $_SESSION[$name]->set_sql($sql);
502                 $_SESSION[$name]->set_columns($coldef);
503         }
504         
505         return  $_SESSION[$name];
506
507 }
508 //
509 //      Force pager initialization.
510 //
511 function refresh_pager($name)
512 {
513         if (isset($_SESSION[$name]))
514                 $_SESSION[$name]->ready = false;
515 }