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
  20         var $data = array();
  21
  22         var $curr_page,
  23                 $max_page,
  24             $last_page,
  25             $prev_page,
  26             $next_page,
  27             $first_page;
  28
  29         var $page_len,
  30             $rec_count;
  31
  32         var $select,
  33                 $where,
  34             $from,
  35                 $group;
  36         var     $extra_where;
  37
  38         var $ready = false;
  39
  40         //  db_pager constructor
  41         //  accepts $sql like 'SELECT .... FROM .... [WHERE ...] [GROUP ...]'
  42         //      $name is base name for pager controls
  43         function db_pager($sql, $name, $page_len=0)
  44         {
  45                 if ($page_len == 0) $page_len = user_query_size();
  46                 $this->name = $name;
  47                 $this->page_len = $page_len;
  48                 $this->set_sql($sql);
  49         }
  50         //
  51         //      Parse base sql select query.
  52         //
  53         function set_sql($sql)
  54         {
  55                 if ($sql != $this->sql) {
  56                     $this->sql = $sql;
  57                     $this->ready = false;
  58                         $select = $sql;
  59                         $from = $where = $group = '';
  60                 $parts = preg_split('/\sFROM\s/si', $sql, 2);
  61                         if(count($parts) == 2) {
  62                                 $select = $parts[0];
  63                                 $from = $parts[1];
  64                         $parts = preg_split('/\sWHERE\s/si', $from, 2);
  65                                 if(count($parts) == 2) {
  66                                         $from = $parts[0];
  67                                         $where = $parts[1];
  68                                 $parts = preg_split('/\sGROUP\s*BY\s/si', $where, 2);
  69                                         if(count($parts) == 2) {
  70                                                 $where = $parts[0];
  71                                                 $group = $parts[1];
  72                                         }
  73                                 }
  74                         }
  75                         $this->select = $select;
  76                         $this->from = $from;
  77                         $this->where = $where;
  78                         $this->group = $group;
  79 /*
  80                 display_error("sql: $sql");
  81                 display_error($select);
  82                 display_error("FROM $from");
  83                 display_error("WHERE $where");
  84                 display_error("GROUP BY $group");
  85 */
  86                 }
  87         }
  88         //
  89         //      Set additional constraint on record set
  90         //
  91         function set_where($where)
  92         {
  93             if (!is_array($where))
  94                   $where = array($where);
  95
  96             if (count($where) != count($this->extra_where) ||
  97                         count(array_diff($this->extra_where, $where))) {
  98                                 $this->extra_where = $where;
  99                                 $this->ready = false;
 100             }
 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 ($this->columns[$c]['type'] != '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
 239                 if(count($this->extra_where)) {
 240                     $wherw .= ($where=='' ? '' : ' AND ')
 241                                 .implode( $this->extra_where, ' AND ');
 242                 }
 243                 if ($where) $where = " WHERE($where)";
 244
 245                 if ($count) {
 246                         $group = $group == '' ? "*" : "DISTINCT $group";
 247
 248                         return "SELECT COUNT($group) FROM $from $where";
 249                 }
 250
 251                 $sql = "$select FROM $from $where";
 252                 if($group) $sql.= " GROUP BY $group";
 253             $ord = array();
 254
 255             foreach( $this->columns as $col) {
 256                 if (isset($col['ord'])) {
 257                         if ( $col['ord'] != '' && isset($col['name'])) {
 258                             $ord[] = $col['name'] .' '. $col['ord'];
 259                             }
 260                         }
 261             }
 262
 263             if (count($ord))
 264                         $sql .= " ORDER BY " . implode($ord, ',');
 265
 266             $page_len = $this->page_len;
 267             $offset = ($this->curr_page - 1) * $page_len;
 268
 269             $sql .= " LIMIT $offset, $page_len";
 270
 271                 return $sql;
 272
 273         }
 274         //
 275         //      Initialization after changing record set
 276         //
 277         function _init()
 278         {
 279             if ($this->ready == false ) {
 280                         $sql = $this->_sql_gen(true);
 281                         $result = db_query($sql, 'Error reading record set');
 282                         if ($result == false)
 283                                 return false;
 284                         $row = db_fetch_row($result);
 285                         $this->rec_count = $row[0];
 286                         $this->max_page = ceil($this->rec_count/$this->page_len);
 287                         $this->set_page(1);
 288                         $this->ready = true;
 289             }
 290         return true;
 291         }
 292         //
 293         //      Set current page in response to user control.
 294         //
 295         function select_records()
 296         {
 297                 global $Ajax;
 298
 299                 $page = find_submit($this->name.'_page_', false);
 300                 $sort = find_submit($this->name.'_sort_', true);
 301                 if ($page) {
 302                         $this->change_page($page);
 303                         if ($page == 'next' && !$this->next_page ||
 304                                 $page == 'last' && !$this->last_page)
 305                                         set_focus($this->name.'_page_prev');
 306                         if ($page == 'prev' && !$this->prev_page ||
 307                                 $page == 'first' && !$this->first_page)
 308                                         set_focus($this->name.'_page_next');
 309                 } elseif ($sort != -1) {
 310                         $this->sort_table($sort);
 311                 } else
 312                         $this->query();
 313         }
 314         //
 315         //      Set check function to mark some rows.
 316         //
 317         function set_marker($func, $notice='', $markercl='overduebg', $msgclass='overduefg' )
 318         {
 319                 $this->marker = $func;
 320                 $this->marker_txt = $notice;
 321                 $this->marker_class = $markercl;
 322                 $this->notice_class = $msgclass;
 323         }
 324 };
 325 //-----------------------------------------------------------------------------
 326 //      Creates new db_pager $_SESSION object on first page call.
 327 //  Retrieves from $_SESSION var on subsequent $_POST calls
 328 //
 329 //  $name - base name for pager controls and $_SESSION object name
 330 //  $sql  - base sql for data inquiry. Order of fields implies
 331 //              pager columns order.
 332 //      $coldef - array of column definitions. Example definitions
 333 //              Text column with title 'User name':
 334 //                              'User name'
 335 //              Skipped field from sql query. Data for the field is not displayed:
 336 //                              'dummy' => 'skip'
 337 //              Column without title, formated with function func(). Field value
 338 // is passed as parameter:
 339 //                              array('type'=>'spec', 'fun'=>'func')
 340 //              Inserted column with title 'Some', formated with function rowfun(). Row
 341 //              values are passed as parameter array:
 342 //                              'Some' => array('type'=>'insert', 'fun'=>'rowfun')
 343 //              Column with name 'Another', formatted as date,
 344 // sortable with ascending start order (available orders: asc,desc, '').
 345 //                              'Another' => array('type'=>'date', 'ord'=>'asc')
 346 //
 347 //      All available column format types you will find in db_pager_view.inc file.
 348 //              If query result has more fields than count($coldef), rest of data is ignored
 349 //  during display, but can be used in format handlers for 'spec' and 'insert'
 350 //      type columns.
 351
 352 function &new_db_pager($name, $sql, $coldef, $page_len = 0)  {
 353
 354     if (isset($name) && isset($_SESSION[$name])) {
 355                  // kill old pager if any on first page call
 356             if ($_SERVER['REQUEST_METHOD'] == 'GET')
 357                         unset($_SESSION[$name]);
 358             else
 359                 return $_SESSION[$name];
 360     }
 361     $ret =& new db_pager($sql, $name, $page_len);
 362     $ret->set_columns($coldef);
 363
 364     if (isset($name)) $_SESSION[$name] = &$ret;
 365
 366     return $ret;
 367 }
 368
 369 ?>