/**
* Returns the complete SQL statement and the values to apply
*
* @return array ($sql, $vals)
*/
public function getSQL()
{
// FROM needs commas only for tables, not joins
$from = '';
foreach ($this->from as $alias => $statement) {
if ($this->type[$alias] == 'table' && $from) {
$from .= ",\n";
} else {
$from .= "\n";
}
$from .= $statement;
}
// prepare aliases for the select columns
$selects = array();
foreach ($this->select as $alias => $select) {
$selects[] = "{$select} AS {$alias}";
}
$sql = ' SELECT ' . join(",\n", $selects) . "\n" . ' FROM ' . $from . "\n" . ' WHERE ' . $this->where->toSQL() . "\n";
if ($this->groupby) {
$sql .= 'GROUP BY ' . join(",\n", $this->groupby) . "\n";
}
if ($this->orderby) {
$sql .= 'ORDER BY ' . join(",\n", $this->orderby) . "\n";
}
return $this->fixPlaceholders($sql);
}
/**
* returns where clause as string generated from QueryBuilderWhere instance
* @param QueryBuilderWhere $where
* @param bool $isSub - whether is it generated for sub where or not (true only for recursive call, so do not care)
* @return string
*/
protected function getWhereStringByObject(QueryBuilderWhere $where, $isSub = false)
{
// whereString
$whereString = "";
/* FULLTEXT WHERE */
if ($where instanceof QueryBuilderWhereFullText) {
$columnsString = "";
$phrasesString = "";
foreach ($where->getColumns() as $columnName) {
$columnsString .= strlen($columnsString) > 0 ? "," : "";
$columnsString .= reset(self::getQuotesColumnName()) . $columnName . end(self::getQuotesColumnName());
}
foreach ($where->getPhrases() as $phrase) {
$phrase = preg_replace('/[-!"#$%&\'()*+,./:;<=>?@[\\\\]^_`{|}~]/', '', $phrase);
//$phrase = ereg_replace("[[:punct:]]", "", $phrase);
if (strlen(trim($phrase)) < 1) {
continue;
}
$phrase = $this->escapeString($phrase);
$phrasesString .= strlen($phrasesString) > 0 ? " " : "";
$phrasesString .= '"' . $phrase . '"';
}
$whereString = "MATCH (" . $columnsString . ")\n\t\t\t\t\t\t\t\tAGAINST ('{$phrasesString}')";
} else {
if (count($where->getConditions()) < 1) {
NULL;
} else {
foreach ($where->getConditions() as $condition) {
$columnName = $condition["column"];
$glue = $condition["glue"] > 0 ? "OR" : "AND";
switch ($condition["comparison"]) {
case -3:
$comparisonValue = strtoupper($this->getValueWrapped($condition["value"])) == "NULL" ? " IS NOT NULL" : " != " . $this->getValueWrapped($condition["value"]) . "";
break;
case -2:
$comparisonValue = " < " . $this->getValueWrapped($condition["value"]) . "";
break;
case -1:
$comparisonValue = " <= " . $this->getValueWrapped($condition["value"]) . "";
break;
case 0:
$comparisonValue = strtoupper($this->getValueWrapped($condition["value"])) == "NULL" ? " IS NULL" : " = " . $this->getValueWrapped($condition["value"]) . "";
break;
case 1:
$comparisonValue = " >= " . $this->getValueWrapped($condition["value"]) . "";
break;
case 2:
$comparisonValue = " > " . $this->getValueWrapped($condition["value"]) . "";
break;
case 3:
$comparisonValue = " LIKE " . $this->getValueWrapped("%" . $condition["value"] . "") . "";
break;
case 4:
$comparisonValue = " LIKE " . $this->getValueWrapped("%" . $condition["value"] . "%") . "";
break;
case 5:
$comparisonValue = " LIKE " . $this->getValueWrapped("" . $condition["value"] . "%") . "";
break;
default:
throw new DbControlException("Ilegal comparison '" . $condition["comparison"] . "'!");
}
$whereString .= strlen($whereString) > 0 ? " {$glue} " : "";
$whereString .= reset(self::getQuotesColumnName()) . $columnName . end(self::getQuotesColumnName()) . $comparisonValue;
}
}
}
foreach ($where->getWheres() as $subWhereSet) {
$subWhere = $subWhereSet["where"];
$subWhereGlue = $subWhereSet["glue"] > 0 ? "OR" : "AND";
if (strlen($subWhereString = $this->getWhereStringByObject($subWhere, true)) < 1) {
continue;
}
$whereString = strlen($whereString) > 0 ? "({$whereString}) {$subWhereGlue} " : "";
$whereString .= "({$subWhereString})";
}
if (!$isSub && strlen($whereString) > 0) {
$whereString = "WHERE {$whereString}";
}
return $whereString;
}
/**
* returns prior set where instance
* @return QueryBuilderWhere
*/
protected function getWhere()
{
try {
if ($this->where instanceof QueryBuilderWhere) {
return $this->where;
}
$itemType = $this->getClassVar("itemType");
$where = new QueryBuilderWhere();
$passwordColNames = eval("return {$itemType}::\$passwordColNames;");
@reset($this->filter);
$fcur = @current($this->filter);
if (!empty($fcur) || (int) $fcur === 0) {
if ($this->filter !== false) {
foreach ((array) $this->filter as $column => $value) {
// password columns
if (in_array($column, $passwordColNames)) {
$value = md5($value);
}
$where->addConditionColumn($column, $value);
}
}
}
// add whereAdd addition
if ($this->whereAdd instanceof QueryBuilderWhere) {
$where->addWhere($this->whereAdd);
}
return $this->where = $where;
} catch (Exception $e) {
throw $e;
}
}
/**
* getter na vsechny XT roles v systemu, krome roli, ktere jsou vyhrazene jako superrole
* @param bool $superAdmin - with superadmin roles
* @return XTRolesRecords
* @throws Exception
*/
public static function getXTRoles($superAdmin = false)
{
try {
if ($superAdmin) {
$where = NULL;
} else {
//$where = $superAdmin ? NULL : "name != '". self::XT_ROLE_NAME_SUPERADMIN ."'";
$where = new QueryBuilderWhere();
$where->addConditionColumn("name", self::XT_ROLE_NAME_SUPERADMIN, -3);
$where->addConditionColumn($pidColName, "<<NULL>>", 0, 1);
}
return new XTRolesRecords(false, array("id" => 0), false, $where);
} catch (Exception $e) {
throw $e;
}
}
/**
* return descendants count
* @return int
* @throws Exception
*/
public function getDescendantsCount()
{
try {
$tableName = $this->getClassVar("tableName");
$idColName = $this->getClassVar("idColName");
$treeColNames = $this->getClassVar("treeColNames");
$lftColName = $treeColNames[0];
$rgtColName = $treeColNames[1];
$bidColName = $treeColNames[3];
$myLft = $this->params[$lftColName];
$myRgt = $this->params[$rgtColName];
$myBid = $this->params[$bidColName];
$where = new QueryBuilderWhere();
$where->addConditionColumn($lftColName, $myLft, 2);
$where->addConditionColumn($rgtColName, $myRgt, -2);
$where->addConditionColumn($bidColName, $myBid);
$sql = $this->getQueryBuilder()->getSelectCount($tableName, $where);
// var_dump(__CLASS__ ."::". __LINE__ .": ". $this->getQueryBuilder()->getSelectCount($tableName, $where));
$result = $this->getDb()->initiateQuery($this->getQueryBuilder()->getSelectCount($tableName, $where));
return (int) $result->count;
} catch (Exception $e) {
throw $e;
}
}
