X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=includes%2Fdb_pager.inc;h=affb9e09796e9cf046abdb0998817ed533eac076;hb=268a54dcfd08aff7113bc3afe8b784f36db2d0d4;hp=819b4757f5c7e3381b749f9bc3d7073b8ea187ec;hpb=711e760ecf671f3b43204c4276a96477bea6dea7;p=fa-stable.git diff --git a/includes/db_pager.inc b/includes/db_pager.inc index 819b4757..affb9e09 100644 --- a/includes/db_pager.inc +++ b/includes/db_pager.inc @@ -29,6 +29,7 @@ class db_pager { var $width; // table width (default '95%') var $header_fun; // additional row between title and body var $header_class; + var $row_fun; // Function for row preprocessing var $footer_fun; var $footer_class; var $data = array(); @@ -48,17 +49,24 @@ class db_pager { $from, $group, $order; - var $extra_where; + var $extra_where = array(); var $ready = false; // this var is false after change in sql before first // and before first query. - + var $inactive_ctrl = false; + var $main_tbl; // table and key field name for inactive ctrl and edit/delete links + var $key; // key field name + // db_pager constructor - // accepts $sql like 'SELECT ...[FROM ...][WHERE ...][GROUP ...][ORDER ...]' + // accepts $sql query either as: + // a. string in form 'SELECT field_list FROM table_joins [WHERE conditions [GROUP group_list [ORDER order_list]]]' + // - if WHERE keyword is used in table_joins, WHERE conditions is obligatory + // b. associative array using select, where, group and order keys ex : + // array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location') + // // $name is base name for pager controls - function db_pager($sql, $name, $page_len=0) + function __construct($sql, $name, $table = null, $page_len=0) { - global $table_style; $this->width = "95%"; if ($page_len == 0) $page_len = user_query_size(); $this->name = $name; @@ -66,34 +74,51 @@ class db_pager { $this->set_sql($sql); } // - // Parse base sql select query. + // Parse base sql select query or use an associative array. // function set_sql($sql) { + global $SysPrefs; + if ($sql != $this->sql) { $this->sql = $sql; $this->ready = false; - $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2); - if(count($parts) == 2) { - $sql = $parts[0]; - $this->order = $parts[1]; - } - $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2); - if(count($parts) == 2) { - $sql = $parts[0]; - $this->group = $parts[1]; - } - $parts = preg_split('/\sWHERE\s/si', $sql, 2); - if(count($parts) == 2) { - $sql = $parts[0]; - $this->where = $parts[1]; - } - $parts = preg_split('/\sFROM\s/si', $sql, 2); - if(count($parts) == 2) { - $sql = $parts[0]; - $this->from = $parts[1]; + + if(is_array($sql)) { + foreach(explode(' ', 'select from where group order') as $section) { + $this->$section = @$sql[$section]; + } + $this->select = "SELECT ".$this->select; + } + else { + // parse the query + $parts = preg_split('/\sFROM\s/si', $sql, 2); + if (count($parts) == 2) { + $this->select = $parts[0]; + $sql = $parts[1]; + } else { + if ($SysPrefs->go_debug) + display_error("Invalid sql input for db_pager"); + } + + $parts = preg_split('/\sWHERE(?!.*WHERE.*)\s/si', $sql, 2); // last occurence + if(count($parts) == 2) { + $this->from = $parts[0]; + $sql = $parts[1]; + + $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2); + if(count($parts) == 2) { + $sql = $parts[0]; + $this->order = $parts[1]; + } + $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2); + if(count($parts) == 2) { + $sql = $parts[0]; + $this->group = $parts[1]; + } + $this->where = $sql; + } } - $this->select = $sql; } } // @@ -127,7 +152,16 @@ class db_pager { // function sort_table($col) { + + $max_priority = 0; + foreach($this->columns as $id => $_col) { + if(!isset($_col['ord_priority'])) continue; + $max_priority = max($max_priority, $_col['ord_priority']); + }; + + $ord = $this->columns[$col]['ord']; + $this->columns[$col]['ord_priority'] = $max_priority+1; // set priority , higher than anything else $ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : ''); $this->columns[$col]['ord'] = $ord; $this->set_page(1); @@ -155,49 +189,53 @@ class db_pager { if ($result) { // setting field names for subsequent queries $c = 0; - // add result field names to column defs for - // col value retrieve and sort purposes - for ($c = $i = 0; $c < count($this->columns); $c++) { - if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) - $this->columns[$c]['name']= mysql_field_name($result, $i++); + // add result field names to column defs for + // col value retrieve and sort purposes + $cnt = min(db_num_fields($result), count($this->columns)); + for ($c = $i = 0; $c < $cnt; $c++) { + if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) { +// if (!@($this->columns[$c]['type']=='skip')) + $this->columns[$c]['name']= db_field_name($result, $i); + if (!@($this->columns[$c]['type']=='insert')) + $i++; + } } - - while ($row = db_fetch_assoc($result)) + while ($row = db_fetch_assoc($result)) { $this->data[] = $row; - + } } else - return false; + return false; return true; - } + } // // Calculates page numbers for html controls. // function set_page($to) { switch($to) { - case 'next': - $page = $this->curr_page+1; break; - case 'prev': - $page = $this->curr_page-1; break; - case 'last': - $page = $this->last_page; break; - default: - if (is_numeric($to)) { - $page = $to; break; - } - case 'first': - $page = 1; break; + case 'next': + $page = $this->curr_page+1; break; + case 'prev': + $page = $this->curr_page-1; break; + case 'last': + $page = $this->last_page; break; + default: + if (is_numeric($to)) { + $page = $to; break; + } + case 'first': + $page = 1; break; } - if ($page < 1) - $page = 1; - $max = $this->max_page; - if ($page > $max) - $page = $max; - $this->curr_page = $page; - $this->next_page = ($page < $max) ? $page+1 : null; - $this->prev_page = ($page > 1) ? ($page-1) : null; - $this->last_page = ($page < $max) ? $max : null; - $this->first_page = ($page != 1) ? 1: null; + if ($page < 1) + $page = 1; + $max = $this->max_page; + if ($page > $max) + $page = $max; + $this->curr_page = $page; + $this->next_page = ($page < $max) ? $page+1 : null; + $this->prev_page = ($page > 1) ? ($page-1) : null; + $this->last_page = ($page < $max) ? $max : null; + $this->first_page = ($page != 1) ? 1: null; } // // Set column definitions @@ -210,29 +248,31 @@ class db_pager { } foreach ($flds as $colnum=>$coldef) { if (is_string($colnum)) { // 'colname'=>params - $h = $colnum; - $c = $coldef; + $h = $colnum; + $c = $coldef; } else { // n=>params if (is_array($coldef)) { $h = ''; $c = $coldef; } else { - $h = $coldef; + $h = $coldef; $c = 'text'; } } if (is_string($c)) // params is simple column type - $c = array('type'=>$c); + $c = array('type'=>$c); if (!isset($c['type'])) - $c['type'] = 'text'; + $c['type'] = 'text'; switch($c['type']) { - case 'insert': - default: - $c['head'] = $h; break; - case 'skip': // skip the column (no header) - unset($c['head']); // paranoid code + case 'inactive': + $this->inactive_ctrl = true; + case 'insert': + default: + $c['head'] = $h; break; + case 'skip': // skip the column (no header) + unset($c['head']); break; } $this->columns[] = $c; } @@ -247,35 +287,42 @@ class db_pager { $select = $this->select; $from = $this->from; $where = $this->where; - $group = $this->group; + $group = $this->group; $order = $this->order; if(count($this->extra_where)) { $where .= ($where=='' ? '' : ' AND ') - .implode( $this->extra_where, ' AND '); + .implode(' AND ', $this->extra_where); } if ($where) $where = " WHERE ($where)"; - if ($count) { - $group = $group == '' ? "*" : "DISTINCT $group"; - - return "SELECT COUNT($group) FROM $from $where"; - } + if ($count) + return "SELECT COUNT(*) FROM ($this->sql) tmp_count"; $sql = "$select FROM $from $where"; if ($group) $sql.= " GROUP BY $group"; $ord = array(); + // sort order column by priority instead of table order. + $columns = array(); foreach ($this->columns as $col) { + if(isset($col['ord_priority'])) { + $columns[$col['ord_priority']] = $col; + } + } + krsort($columns); + + foreach ($columns as $col) { if (isset($col['ord'])) { if ( $col['ord'] != '' && isset($col['name'])) { $ord[] = $col['name'] .' '. $col['ord']; } } } - + if (count($ord)) { - $sql .= " ORDER BY " . implode($ord, ','); + $ord = array_map('db_escape_function', $ord); + $sql .= " ORDER BY " . implode(',', $ord); } else { if($order) $sql .= " ORDER BY $order"; // original base query order @@ -294,7 +341,7 @@ class db_pager { // function _init() { - global $go_debug; + global $SysPrefs; if ($this->ready == false ) { $sql = $this->_sql_gen(true); @@ -306,14 +353,14 @@ class db_pager { $this->max_page = $this->page_len ? ceil($this->rec_count/$this->page_len) : 0; - if ($go_debug) { // FIX - need column name parsing, but for now: + if ($SysPrefs->go_debug) { // FIX - need column name parsing, but for now: // check if field names are set explicite in col def // for all initially ordered columns foreach ($this->columns as $col) { if (isset($col['ord']) && $col['ord'] != '' && !isset($col['name'])) { display_warning("Result field names must be set - for all intially ordered db_pager columns."); + for all initially ordered db_pager columns."); } } } @@ -372,6 +419,41 @@ class db_pager { $this->footer_fun = $func; $this->footer_class = $footercl; } + // + // Setter for table editors with inactive cell control. + // + function set_inactive_ctrl($table, $key) { + $this->inactive_ctrl = array('table'=>$table, 'key'=>$key); + } + // + // Helper for display inactive control cells + // + function inactive_control_cell(&$row) + { + if ($this->inactive_ctrl) { + + global $Ajax; + + $key = $this->key ? + $this->key : $this->columns[0]['name']; // TODO - support for complex keys + $id = $row[$key]; + $table = $this->main_tbl; + $name = "Inactive". $id; + $value = $row['inactive'] ? 1:0; + + if (check_value('show_inactive')) { + if (isset($_POST['LInact'][$id]) && (get_post('_Inactive'.$id.'_update') || + get_post('Update')) && (check_value('Inactive'.$id) != $value)) { + update_record_status($id, !$value, $table, $key); + $value = !$value; + } + echo ''. checkbox(null, $name, $value, true, '') + . hidden("LInact[$id]", $value, false) . ''; + } + } else + return ''; + } + }; //----------------------------------------------------------------------------- // Creates new db_pager $_SESSION object on first page call. @@ -399,20 +481,22 @@ class db_pager { // during display, but can be used in format handlers for 'spec' and 'insert' // type columns. -function &new_db_pager($name, $sql, $coldef, $page_len = 0) { - - if ($_SERVER['REQUEST_METHOD'] == 'GET') - unset($_SESSION[$name]); // kill old pager if any exists on first page call +function &new_db_pager($name, $sql, $coldef, $table = null, $key = null, $page_len = 0) { + if (isset($_SESSION[$name]) && + ($_SERVER['REQUEST_METHOD'] == 'GET' || $_SESSION[$name]->sql != $sql)) { + unset($_SESSION[$name]); // kill pager if sql has changed + } if (!isset($_SESSION[$name])) { - $_SESSION[$name] =& new db_pager($sql, $name, $page_len); + $_SESSION[$name] = new db_pager($sql, $name, $table, $page_len); + $_SESSION[$name]->main_tbl = $table; + $_SESSION[$name]->key = $key; $_SESSION[$name]->set_sql($sql); $_SESSION[$name]->set_columns($coldef); } - $ret = &$_SESSION[$name]; + return $_SESSION[$name]; - return $ret; } // // Force pager initialization. @@ -422,4 +506,3 @@ function refresh_pager($name) if (isset($_SESSION[$name])) $_SESSION[$name]->ready = false; } -?> \ No newline at end of file