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