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