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