protected function getSelect($table, $what = "*", QueryBuilderWhere $where = NULL, $limit = array(), $groupBy = array(), $orderBy = array())
{
if (strlen($table) < 1) {
throw new DbControlException("Ilegal parameter table. Must be NOT-NULL string.");
}
if (strlen($what) < 1) {
throw new DbControlException("Ilegal parameter what. Must be NOT-NULL string.");
}
if (!is_array($limit)) {
throw new DbControlException("Ilegal parameter limit. Must be array.");
}
if (!is_array($groupBy)) {
throw new DbControlException("Ilegal parameter groupBy. Must be array.");
}
if (!is_array($orderBy)) {
throw new DbControlException("Ilegal parameter groupBy. Must be array.");
}
if ($where instanceof QueryBuilderWhere) {
$whereString = $this->getWhereStringByObject($where);
}
// groupByString
$groupByString = "";
if (count($groupBy) < 1) {
$groupByString = "";
} else {
foreach ($groupBy as $columnName) {
$groupByString .= strlen($groupByString) > 0 ? ", " : "";
$groupByString .= reset(self::getQuotesColumnName()) . "{$columnName}" . end(self::getQuotesColumnName()) . "";
}
$groupByString = "GROUP BY {$groupByString}";
}
// orderByString
$orderByString = $this->getOrderByStringByArray($orderBy);
$orderByStringReverse = $this->getOrderByStringByArray($orderBy, true);
// limitString
$limitString = "";
switch (count($limit)) {
case 0:
case 2:
NULL;
break;
case 1:
$limit = array(0, current($limit));
break;
default:
throw new DbControlException("Ilegal parameter limit!");
}
$outTmp = "{$what} FROM " . reset(self::getQuotesTableName()) . "{$table}" . end(self::getQuotesTableName()) . " {$whereString} {$groupByString} {$orderByString}";
// LIMIT clause emulation for MSSQL
/**
* FOR INSTANCE EMULATION OF:
* - SELECT emp_id,lname,fname FROM employee LIMIT 20,10
* select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc
) as newtbl order by lname desc
) as newtbl2 order by lname asc
*/
if (count($limit) > 0) {
$limitOffset = reset($limit);
$limitLimit = end($limit);
/* check table count */
$sqlCountNoLimit = "SELECT COUNT(*) AS count FROM " . reset(self::getQuotesTableName()) . "{$table}" . end(self::getQuotesTableName()) . " {$whereString} {$groupByString}";
$dbControl = new DbControl($this->task);
$countNoLimit = $dbControl->initiateQuery($sqlCountNoLimit)->count;
$limitLimit = $limitOffset + $limitLimit > $countNoLimit ? $limitOffset > $countNoLimit ? 0 : $countNoLimit - $limitOffset : $limitLimit;
$out = "SELECT * FROM (\n\t\t\t\t\t\t\tSELECT TOP " . $limitLimit . " {$what} FROM (" . "SELECT TOP " . ($limitOffset + $limitLimit) . " {$outTmp}\n\t\t\t\t\t\t\t) AS order_tmptable1 {$orderByStringReverse}" . ") AS order_tmptable2 {$orderByString}";
} else {
$out = "SELECT {$outTmp}";
}
return $out;
}
