Index: trunk/core/kernel/db/dblist.php
===================================================================
diff -u -r932 -r1339
--- trunk/core/kernel/db/dblist.php (.../dblist.php) (revision 932)
+++ trunk/core/kernel/db/dblist.php (.../dblist.php) (revision 1339)
@@ -1,5 +1,29 @@
null, FLT_NORMAL => null, FLT_SEARCH => null, FLT_VIEW => null);
+
+ /**
+ * Holds list HAVING filter object
+ *
+ * @var kMultipleFilter
+ * @access private
+ */
+ var $HavingFilter = Array(FLT_SYSTEM => null, FLT_NORMAL => null, FLT_SEARCH => null, FLT_VIEW => null);
+
+ /**
* Creates kDBList
*
* @return kDBList
*/
function kDBList() {
parent::kDBBase();
$this->OrderFields = Array();
+
+ $this->WhereFilter[FLT_SYSTEM] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
+ $this->WhereFilter[FLT_NORMAL] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
+
+ $this->WhereFilter[FLT_SEARCH] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
+ $this->WhereFilter[FLT_SEARCH]->setType(FLT_TYPE_OR);
+
+ $this->WhereFilter[FLT_VIEW] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
+
+ $this->HavingFilter[FLT_SYSTEM] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
+ $this->HavingFilter[FLT_NORMAL] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
+
+ $this->HavingFilter[FLT_SEARCH] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_OR);
+ $this->HavingFilter[FLT_SEARCH]->setType(FLT_TYPE_OR);
+
+ $this->HavingFilter[FLT_VIEW] =& $this->Application->makeClass('kMultipleFilter', FLT_TYPE_AND);
+
$this->PerPage = -1;
}
/**
- * Sets counted part of SELECT query used in grouped queries
- *
- * When using queries containing GROUP BY clause, the SELECT part may contain aggregate functions such as SUM(), COUNT() etc.
- * the part of the query with this functions should be set as 'counted SQL' for {@link dDBList::CountRecs()} to build the correct
- * count query.
- * Example:
- *
- * ...
- * $this->SetSelectSQL('SELECT product_id, SUM(amount) FROM sales');
- * $this->SetCounterSQL('SUM(amount)');
- * ...
- *
- *
- * {@link kDBList::CountRecs()} will replace the SELECT part with COUNT(*), SUM(amount) when counting records, which will give the accurate
- * number of records. The number of records is used in list pagination
- *
- * @access public
- * @param string
- * @return void
- */
- function SetCountedSQL($sql)
+ * Adds new or replaces old filter with same name
+ *
+ * @param string $name filter name (for internal use)
+ * @param string $clause where/having clause part (no OR/AND allowed)
+ * @param int $filter_type is filter having filter or where filter
+ * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW
+ * @access public
+ */
+ function addFilter($name, $clause, $filter_type = WHERE_FILTER, $filter_scope = FLT_SYSTEM)
{
- $this->CountedSQL = $sql;
+ $filter_name = ($filter_type == WHERE_FILTER) ? 'WhereFilter' : 'HavingFilter';
+
+ $filter =& $this->$filter_name;
+ $filter =& $filter[$filter_scope];
+ $filter->addFilter($name,$clause);
}
/**
+ * Removes specified filter from filters list
+ *
+ * @param string $name filter name (for internal use)
+ * @param int $filter_type is filter having filter or where filter
+ * @param int $filter_scope filter subtype: FLT_NORMAL,FLT_SYSTEM,FLT_SEARCH,FLT_VIEW
+ * @access public
+ */
+ function removeFilter($name, $filter_type = WHERE_FILTER, $filter_scope = FLT_SYSTEM)
+ {
+ $filter_name = ($filter_type == WHERE_FILTER) ? 'WhereFilter' : 'HavingFilter';
+
+ $filter =& $this->$filter_name;
+ $filter =& $filter[$filter_scope];
+ $filter->removeFilter($name);
+ }
+
+ /**
+ * Clear list filters
+ *
+ * @param bool $user clear user filters
+ * @param bool $system clear system filters
+ */
+ function clearFilters($user=true,$system=true,$search=true,$view=true)
+ {
+ if($system)
+ {
+ $this->WhereFilter[FLT_SYSTEM]->clearFilters();
+ $this->HavingFilter[FLT_SYSTEM]->clearFilters();
+ }
+ if($user)
+ {
+ $this->WhereFilter[FLT_NORMAL]->clearFilters();
+ $this->HavingFilter[FLT_NORMAL]->clearFilters();
+ }
+ if($search)
+ {
+ $this->WhereFilter[FLT_SEARCH]->clearFilters();
+ $this->HavingFilter[FLT_SEARCH]->clearFilters();
+ }
+ if($view)
+ {
+ $this->WhereFilter[FLT_VIEW]->clearFilters();
+ $this->HavingFilter[FLT_VIEW]->clearFilters();
+ }
+ }
+
+ /**
* Counts the total number of records base on the query resulted from {@link kDBList::GetSelectSQL()}
*
* The method modifies the query to substitude SELECT part (fields listing) with COUNT(*).
@@ -134,24 +245,25 @@
*/
function CountRecs()
{
- $q = $this->GetSelectSQL();
- $counted_sql = '';
- if ($this->hasCounted) {
- $counted_sql = $this->GetCountedSQL();
- $counted_sql = ", $counted_sql";
+ $sql = $this->GetSelectSQL(true,false);
+ $sql = $this->getCountSQL($sql);
+ $this->RecordsCount = (int)$this->Conn->GetOne($sql);
+
+ $sql = $this->GetSelectSQL(true,true);
+ $sql = $this->getCountSQL($sql);
+ $this->NoFilterCount = (int)$this->Conn->GetOne($sql);
+ }
+
+ function getCountSQL($sql)
+ {
+ if ( preg_match("/DISTINCT(.*?)FROM(?!_)/is",$sql,$regs ) )
+ {
+ return preg_replace("/^.*SELECT DISTINCT(.*?)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $sql);
}
-
- if ( preg_match("/DISTINCT(.*?)FROM(?!_)/is",$q,$regs ) )
- $q = preg_replace("/^.*SELECT DISTINCT(.*?)FROM(?!_)/is", "SELECT COUNT(DISTINCT ".$regs[1].") AS count FROM", $q);
else
- $q = preg_replace("/^.*SELECT(.*?)FROM(?!_)/is", "SELECT COUNT(*) AS count $counted_sql FROM ", $q);
-
- if ($this->DisplayQueries) {
- echo get_class($this)." Count SQL: $q
";
+ {
+ return preg_replace("/^.*SELECT(.*?)FROM(?!_)/is", "SELECT COUNT(*) AS count FROM ", $sql);
}
-
- $this->RecordsCount = (int)$this->Conn->GetOne($q);
- $this->hasCounted=true;
}
/**
@@ -174,7 +286,7 @@
$sql = $q.' '.$this->Conn->getLimitClause($this->Offset,$this->PerPage);
$this->Records = $this->Conn->Query($sql);
- $this->SelectedCount=count($this->Records);
+ $this->SelectedCount = count($this->Records);
if ($this->Records === false) {
//handle errors here
@@ -189,43 +301,107 @@
* @access public
* @return string
*/
- function GetSelectSQL()
+ function GetSelectSQL($for_counting=false,$system_filters_only=false)
{
- $q = parent::GetSelectSQL();
+ $q = parent::GetSelectSQL($this->SelectClause);
+ if(!$for_counting) $q = $this->addCalculatedFields($q);
- $where = $this->GetWhereClause();
- $having = $this->GetHavingClause();
+ $where = $this->GetWhereClause($for_counting,$system_filters_only);
+ $having = $this->GetHavingClause($for_counting,$system_filters_only);
$order = $this->GetOrderClause();
$group = $this->GetGroupClause();
if (!empty($where)) $q .= ' WHERE ' . $where;
- if (!empty($having)) $q .= ' HAVING ' . $having;
if (!empty($group)) $q .= ' GROUP BY ' . $group;
- if (!empty($order)) $q .= ' ORDER BY ' . $order;
+ if (!empty($having)) $q .= ' HAVING ' . $having;
+ if ( !$for_counting && !empty($order) ) $q .= ' ORDER BY ' . $order;
- return $q;
+ return str_replace('%1$s',$this->TableName,$q);
}
+ function extractCalculatedFields($clause)
+ {
+ if ( is_array($this->CalculatedFields) ) {
+ foreach($this->CalculatedFields as $field_name => $field_expression)
+ {
+ $clause = preg_replace('/`'.$field_name.'`/', $field_expression, $clause);
+ }
+ }
+ return $clause;
+ }
+
/**
* Returns WHERE clause of the query
*
* @access public
+ * @param bool $for_counting merge where filters with having filters + replace field names for having fields with their values
* @return string
*/
- function GetWhereClause()
+ function GetWhereClause($for_counting=false,$system_filters_only=false)
{
- return '';
+ $where =& $this->Application->makeClass('kMultipleFilter');
+
+ $where->addFilter('system_where', $this->WhereFilter[FLT_SYSTEM] );
+
+ if (!$system_filters_only) {
+ $where->addFilter('view_where', $this->WhereFilter[FLT_VIEW] );
+ $search_w = $this->WhereFilter[FLT_SEARCH]->getSQL();
+ if( $search_w || $for_counting ) // move search_having to search_where in case search_where isset or we are counting
+ {
+ $search_h = $this->extractCalculatedFields( $this->HavingFilter[FLT_SEARCH]->getSQL() );
+ $search_w = ($search_w && $search_h) ? $search_w.' OR '.$search_h : $search_w.$search_h;
+ $where->addFilter('search_where', $search_w );
+ }
+ }
+
+ if( $for_counting ) // add system_having and view_having to where
+ {
+ $where->addFilter('system_having', $this->extractCalculatedFields( $this->HavingFilter[FLT_SYSTEM]->getSQL() ) );
+ if (!$system_filters_only) $where->addFilter('view_having', $this->extractCalculatedFields( $this->HavingFilter[FLT_VIEW]->getSQL() ) );
+ }
+
+ return $where->getSQL();
}
/**
+ * Depricated method
+ *
+ * @param string $clause
+ * @todo REMOVE
+ */
+ function SetWhereClause($clause)
+ {
+ if( $this->Application->isDebugMode() )
+ {
+ global $debugger;
+ $debugger->appendTrace();
+ }
+ trigger_error('Depricated method kDBList->SetWhereClause. Use kDBList->addFilter instead.', E_USER_ERROR);
+ }
+
+ /**
* Returns HAVING clause of the query
*
- * @access public
+ * @param bool $for_counting don't return having filter in case if this is counting sql
* @return string
+ * @access public
*/
- function GetHavingClause()
+ function GetHavingClause($for_counting=false, $system_filters_only=false)
{
- return '';
+ if( $for_counting ) return '';
+
+ $having =& $this->Application->makeClass('kMultipleFilter');
+
+ $having->addFilter('system_having', $this->HavingFilter[FLT_SYSTEM] );
+ if (!$system_filters_only) {
+ $having->addFilter('view_having', $this->HavingFilter[FLT_VIEW] );
+ $search_w = $this->WhereFilter[FLT_SEARCH]->getSQL();
+ if (!$search_w) {
+ $having->addFilter('search_having', $this->HavingFilter[FLT_SEARCH] );
+ }
+ }
+
+ return $having->getSQL();
}
/**
@@ -275,30 +451,55 @@
{
$ret = '';
foreach ($this->OrderFields as $field) {
- $ret .= $field[0] . ' ' . $field[1] . ',';
+
+ $name = $field[0];
+ $ret .= isset($this->Fields[$name]) && !isset($this->VirtualFields[$name]) ? '`'.$this->TableName.'`.' : '';
+ $ret .= '`'.$field[0] . '` ' . $field[1] . ',';
}
$ret = rtrim($ret, ',');
return $ret;
}
+ function GetOrderField($pos=NULL)
+ {
+ if(!(isset($this->OrderFields[$pos]) && $this->OrderFields[$pos]) )
+ {
+ $pos = 0;
+ }
+ return isset($this->OrderFields[$pos][0]) ? $this->OrderFields[$pos][0] : '';
+ }
+
+ function GetOrderDirection($pos=NULL)
+ {
+ if(!$this->OrderFields[$pos])
+ $pos = 0;
+
+ return $this->OrderFields[$pos][1];
+ }
+
/**
- * Description
+ * Return unformatted field value
*
- * @access public
* @param string
- * @return void
+ * @return mixed
+ * @access public
*/
function GetDBField($name)
{
$row =& $this->getCurrentRecord();
return $row[$name];
}
- function GetField($name)
+ function HasField($name)
{
- return $this->GetDBField($name);
+ $row =& $this->getCurrentRecord();
+ return isset($row[$name]);
}
+ function GetFieldValues()
+ {
+ return $this->getCurrentRecord();
+ }
function &getCurrentRecord()
{
@@ -314,7 +515,7 @@
*/
function GoFirst()
{
- $this->CurrentIndex=0;
+ $this->CurrentIndex = 0;
}
/**
@@ -332,6 +533,18 @@
* Description
*
* @access public
+ * @return void
+ */
+ function GoPrev()
+ {
+ if ($this->CurrentIndex>0)
+ $this->CurrentIndex--;
+ }
+
+ /**
+ * Description
+ *
+ * @access public
* @return bool
*/
function EOL()
@@ -388,6 +601,41 @@
}
//$this->GoFirst();
}
+
+ /**
+ * Sets current item field value
+ * (doesn't apply formatting)
+ *
+ * @access public
+ * @param string $name Name of the field
+ * @param mixed $value Value to set the field to
+ * @return void
+ */
+ function SetDBField($name,$value)
+ {
+ $this->Records[$this->CurrentIndex][$name] = $value;
+ }
+
+ /**
+ * Apply where clause, that links this object ti it's parent item
+ *
+ * @param string $special
+ * @access public
+ */
+ function linkToParent($special)
+ {
+ $parent_prefix = $this->Application->getUnitOption($this->Prefix, 'ParentPrefix');
+ if($parent_prefix)
+ {
+ $parent_table_key = $this->Application->getUnitOption($this->Prefix, 'ParentTableKey');
+ $foreign_key_field = $this->Application->getUnitOption($this->Prefix, 'ForeignKey');
+
+ $parent_object =& $this->Application->recallObject($parent_prefix.'.'.$special);
+ $parent_id = $parent_object->GetDBField($parent_table_key);
+
+ $this->addFilter('parent_filter', $foreign_key_field.' = '.$parent_id); // only for list in this case
+ }
+ }
}
?>
\ No newline at end of file