includes/db_pager.inc

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