includes/db_pager.inc

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