OrderFields = Array();
$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)
{
$this->CountedSQL = $sql;
}
/**
* 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(*).
* Special care should be applied when working with lists based on grouped queries, all aggregate function fields
* like SUM(), AVERAGE() etc. should be added to CountedSQL by using {@link kDBList::SetCountedSQL()}
*
* @access public
* @param string
* @return void
*/
function CountRecs()
{
$q = $this->GetSelectSQL();
$counted_sql = '';
if ($this->hasCounted) {
$counted_sql = $this->GetCountedSQL();
$counted_sql = ", $counted_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
";
}
$this->RecordsCount = (int)$this->Conn->GetOne($q);
$this->hasCounted=true;
}
/**
* Queries the database with SQL resulted from {@link kDBList::GetSelectSQL()} and stores result in {@link kDBList::SelectRS}
*
* All the sorting, pagination, filtration of the list should be set prior to calling Query().
*
* @access public
* @param string
* @return void
*/
function Query()
{
$q = $this->GetSelectSQL();
if ($this->DisplayQueries) {
echo get_class($this)." Query SQL: $q LIMIT ".$this->PerPage." OFFSET ".$this->Offset." Page: ".$this->Page."
";
}
//$rs = $this->Conn->SelectLimit($q, $this->PerPage, $this->Offset);
$sql = $q.' '.$this->Conn->getLimitClause($this->Offset,$this->PerPage);
$this->Records = $this->Conn->Query($sql);
$this->SelectedCount=count($this->Records);
if ($this->Records === false) {
//handle errors here
return false;
}
return true;
}
/**
* Builds full select query except for LIMIT clause
*
* @access public
* @return string
*/
function GetSelectSQL()
{
$q = parent::GetSelectSQL();
$where = $this->GetWhereClause();
$having = $this->GetHavingClause();
$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;
return $q;
}
/**
* Returns WHERE clause of the query
*
* @access public
* @return string
*/
function GetWhereClause()
{
return '';
}
/**
* Returns HAVING clause of the query
*
* @access public
* @return string
*/
function GetHavingClause()
{
return '';
}
/**
* Returns GROUP BY clause of the query
*
* @access public
* @return string
*/
function GetGroupClause()
{
return '';
}
/**
* Adds order field to ORDER BY clause
*
* @access public
* @param string $field Field name
* @param string $direction Direction of ordering (asc|desc)
* @return void
*/
function AddOrderField($field, $direction)
{
$this->OrderFields[] = Array($field, $direction);
}
/**
* Removes all order fields
*
* @access public
* @return void
*/
function ClearOrderFields()
{
$this->OrderFields = Array();
}
/**
* Returns ORDER BY Clause of the query
*
* The method builds order by clause by iterating {@link kDBList::OrderFields} array and concatenating it.
*
* @access public
* @return string
*/
function GetOrderClause()
{
$ret = '';
foreach ($this->OrderFields as $field) {
$ret .= $field[0] . ' ' . $field[1] . ',';
}
$ret = rtrim($ret, ',');
return $ret;
}
/**
* Description
*
* @access public
* @param string
* @return void
*/
function GetDBField($name)
{
$row =& $this->getCurrentRecord();
return $row[$name];
}
function GetField($name)
{
return $this->GetDBField($name);
}
function &getCurrentRecord()
{
return $this->Records[$this->CurrentIndex];
}
/**
* Description
*
* @access public
* @param string
* @return void
*/
function GoFirst()
{
$this->CurrentIndex=0;
}
/**
* Description
*
* @access public
* @return void
*/
function GoNext()
{
$this->CurrentIndex++;
}
/**
* Description
*
* @access public
* @return bool
*/
function EOL()
{
return ($this->CurrentIndex >= $this->SelectedCount);
}
/**
* Description
*
* @access public
* @param string
* @return void
*/
function GetTotalPages()
{
if ($this->PerPage == -1) return 1;
$this->TotalPages = (($this->RecordsCount - ($this->RecordsCount % $this->PerPage)) / $this->PerPage) // integer part of division
+ (($this->RecordsCount % $this->PerPage) != 0); // adds 1 if there is a reminder
return $this->TotalPages;
}
/**
* Sets number of records to query per page
*
* @access public
* @param int $per_page Number of records to display per page
* @return void
*/
function SetPerPage($per_page)
{
$this->PerPage = $per_page;
}
/**
* Description
*
* @access public
* @param int $page
* @return void
*/
function SetPage($page)
{
if ($this->PerPage == -1) {
$this->Page = 1;
return;
}
if ($page < 1) $page = 1;
$this->Offset = ($page-1)*$this->PerPage;
if ($this->Offset > $this->RecordsCount)
$this->SetPage(1);
else {
$this->Page = $page;
}
//$this->GoFirst();
}
}
?>