protected function initReportQuery()
{
$tag = "ReportDrafter: initReportQuery()";
Log::debug("$tag");
// convenience pointers
$reportBP = $this->reportBlueprint;
$params = $this->params;
/*
// Build a query
*/
$queryString = $reportBP->getQuery();
// replace {variables} with $variables (from $params["data"])
if( array_key_exists("data", $params) )
{
// separate $params["data"] into parallel arrays
$data = $params["data"];
$find = array();
$replace = array();
foreach($data as $f=>$r)
{
$find[] = "{" . $f . "}";
$replace[] = $r;
}
$queryString = str_replace($find, $replace, $queryString);
}
// parse query string from blueprint
$query = SQLSelectQuery::parseSQL($queryString);
// where
if(array_key_exists("where", $this->params))
{
$query->where($this->params["where"]);
}
// process filters
// TODO
// order by
if(array_key_exists("order", $this->params))
{
$order = $this->params["order"];
if(is_array($order))
{
foreach($order as $o)
{
$query->orderBy($o);
}
}
else
{
$query->orderBy($order);
}
}
// LIMIT (PAGING)
if(array_key_exists("page", $this->params))
{
// Init page number
$page = 1;
if( (is_int($this->params["page"])) && ($this->params["page"]>=1) ) $page = $this->params["page"];
// Init page limit
$page_limit = 20; // default
if( (array_key_exists("page_limit", $this->params)) && (is_int((int) $this->params["page_limit"])) && ($this->params["page_limit"]>=1) ) $page_limit = $this->params["page_limit"];
else $this->params["page_limit"] = $page_limit; // init default for use by javascript
// Calculate OFFSET (First row offset=0)
$page_offset = ($page-1) * $page_limit;
// Count the total number of rows that this query will produce (without LIMITs)
$sql = new DatabaseQuery($query->toString());
try
{
$sql->doQuery();
$num_rows = $sql->get_num_rows();
Log::debug("$tag: num_rows = $num_rows");
// Save "count" to params (for use by javascript)
$this->params["count"] = (int) $num_rows;
}
catch(Exception $e)
{
Log::error("$tag: Caught: " . $e->getMessage());
}
// Add LIMIT clause to query
$query->limit("$page_offset, $page_limit");
}
$this->query = $query;
} // END: protected function initReportQuery()
<?php
require_once "ClassLoader.php";
echo "<h1>Testing SQLSelectQuery</h1>";
$sqlSelectQuery = SQLSelectQuery::parseSQL("SELECT id, login, passwd AS Secret FROM Member WHERE status='Active' AND id>'2' ORDER BY login ASC LIMIT 5");
echo $sqlSelectQuery->toString();
echo "<br/><br/>";
$sql = new SQLSelectQuery();
$sql->select("id");
$sql->select("member.name as name");
$sql->select("count(*) as num");
$sql->from("access");
$sql->leftjoin("member", "access.member_id", "member.id");
$sql->where("access.type='GET'");
$sql->having("num > 1");
$sql->groupBy("member.name");
$sql->orderBy("num DESC");
$sql->limit("25");
echo $sql->toString();
