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