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