/**
* Builds SQL query, for retrieve data from DB
*
*/
function buildSQL()
{
$this->gstrOrderBy = $this->gQuery->OrderByToSql();
$this->gsqlHead = $this->gQuery->HeadToSql();
$this->gsqlFrom = $this->gQuery->FromToSql();
$this->gsqlWhereExpr = $this->gQuery->WhereToSql();
$this->gsqlGroupBy = $this->gQuery->GroupByToSql();
$this->gsqlHaving = $this->gQuery->Having()->toSql($gQuery);
$agregateFields = $this->pSet->getListOfFieldsByExprType(true);
$this->searchClauseObj->haveAgregateFields = count($agregateFields) > 0;
$searchWhereClause = $this->searchClauseObj->getWhere($this->pSet->getListOfFieldsByExprType(false), $this->controls);
$searchHavingClause = $this->searchClauseObj->getWhere($agregateFields, $this->controls);
$this->strWhereClause = whereAdd($this->strWhereClause, $searchWhereClause);
$this->strHavingClause = whereAdd($this->strHavingClause, $searchHavingClause);
$strSecuritySql = SecuritySQL("Search", $this->tName);
$this->strWhereClause = whereAdd($this->strWhereClause, $strSecuritySql);
if ($this->noRecordsFirstPage && !count($_GET) && !count($_POST)) {
$this->strWhereClause = whereAdd($this->strWhereClause, "1=0");
}
//add where clause with foreign keys of current table and it's master table master keys
$where = $this->addWhereWithMasterTable();
$this->strWhereClause = whereAdd($this->strWhereClause, $where);
if ($this->dbType == nDATABASE_DB2) {
$this->gsqlHead .= ", ROW_NUMBER() over () as DB2_ROW_NUMBER ";
}
$this->strSearchCriteria = postvalue('criteria');
if ($this->searchClauseObj->isUsedSearchFor && !$this->searchClauseObj->isUsedFieldsForSearch) {
$this->strSearchCriteria = "or";
}
$strSQL = SQLQuery::gSQLWhere_having($this->gsqlHead, $this->gsqlFrom, $this->gsqlWhereExpr, $this->gsqlGroupBy, $this->gsqlHaving, $this->strWhereClause, $this->strHavingClause, $this->strSearchCriteria);
// order by
$strSQL .= " " . trim($this->strOrderBy);
// save SQL for use in "Export" and "Printer-friendly" pages
$_SESSION[$this->sessionPrefix . "_sql"] = $strSQL;
$_SESSION[$this->sessionPrefix . "_where"] = $this->strWhereClause;
$_SESSION[$this->sessionPrefix . "_having"] = $this->strHavingClause;
$_SESSION[$this->sessionPrefix . "_criteria"] = $this->strSearchCriteria;
$_SESSION[$this->sessionPrefix . "_order"] = $this->strOrderBy;
// select and display records
$this->addMasterDetailSubQuery();
$strSQLbak = $strSQL;
if ($this->eventExists("BeforeQueryList")) {
$tstrWhereClause = $this->strWhereClause;
$tstrOrderBy = $this->strOrderBy;
$this->eventsObject->BeforeQueryList($strSQL, $tstrWhereClause, $tstrOrderBy, $this);
$this->strWhereClause = $tstrWhereClause;
$this->strOrderBy = $tstrOrderBy;
}
// Rebuild SQL if needed
if ($strSQL != $strSQLbak) {
// changed $strSQL - old style
$this->numRowsFromSQL = GetRowCount($strSQL);
} else {
$strSQL = SQLQuery::gSQLWhere_having($this->gsqlHead, $this->gsqlFrom, $this->gsqlWhereExpr, $this->gsqlGroupBy, $this->gsqlHaving, $this->strWhereClause, $this->strHavingClause, $this->strSearchCriteria);
$strSQL .= " " . trim($this->strOrderBy);
$rowcount = false;
if ($this->eventExists("ListGetRowCount")) {
$rowcount = $this->eventsObject->ListGetRowCount($this->searchClauseObj, $this->masterTable, $this->masterKeysReq, null, $this);
}
if ($rowcount !== false) {
$this->numRowsFromSQL = $rowcount;
} else {
$this->numRowsFromSQL = SQLQuery::gSQLRowCount_int($this->gsqlHead, $this->gsqlFrom, $this->gsqlWhereExpr, $this->gsqlGroupBy, $this->gsqlHaving, $this->strWhereClause, $this->strHavingClause, $this->strSearchCriteria);
}
}
LogInfo($strSQL);
$this->querySQL = $strSQL;
}
$numrows = GetRowCount($strSQL, $pageObject->connection);
} else {
$strSQL = SQLQuery::gSQLWhere_having($gQuery->HeadToSql(), $gQuery->FromToSql() . $joinFromPart, $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $strWhereClause, $strHavingClause, $strSearchCriteria);
$strSQL .= " " . trim($strOrderBy);
$rowcount = false;
if ($eventObj->exists("ListGetRowCount")) {
$masterKeysReq = array();
for ($i = 0; $i < count($pageObject->detailKeysByM); $i++) {
$masterKeysReq[] = $_SESSION[$strTableName . "_masterkey" . ($i + 1)];
}
$rowcount = $eventObj->ListGetRowCount($pageObject->searchClauseObj, $_SESSION[$strTableName . "_mastertable"], $masterKeysReq, $selected_recs, $pageObject);
}
if ($rowcount !== false) {
$numrows = $rowcount;
} else {
$numrows = SQLQuery::gSQLRowCount_int($gQuery->HeadToSql(), $gQuery->FromToSql() . $joinFromPart, $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $strWhereClause, $strHavingClause, $pageObject->connection, $strSearchCriteria);
}
}
LogInfo($strSQL);
// Pagination:
$mypage = 1;
$nPageSize = 0;
if (@$_REQUEST["records"] == "page" && $numrows) {
$mypage = (int) @$_SESSION[$strTableName . "_pagenumber"];
$nPageSize = (int) @$_SESSION[$strTableName . "_pagesize"];
if (!$nPageSize) {
$nPageSize = $gSettings->getInitialPageSize();
}
if ($nPageSize < 0) {
$nPageSize = 0;
}
function gSQLRowCount($where, $having="", $searchCriteria="or")
{
return SQLQuery::gSQLRowCount_int($this->HeadToSql(), $this->FromToSql(), $this->WhereToSql(), $this->GroupByToSql()
, $this->Having()->toSql($this), $where, $having, $searchCriteria);
}
protected function calcRowCount()
{
global $gQuery;
$strSQLbak = $this->sqlParts["sql"];
$sql = $this->sqlParts["sql"];
$where = $this->sqlParts["where"];
$orderby = $this->sqlParts["orderby"];
if ($this->eventsObject->exists("BeforeQueryPrint")) {
$this->eventsObject->BeforeQueryPrint($sql, $where, $orderby, $this);
}
// Rebuild SQL if needed
if ($sql != $strSQLbak) {
// changed $strSQL - old style
$this->totalRowCount = GetRowCount($sql, $this->connection);
return;
}
// rebuild sql
$this->sqlParts["where"] = $where;
$this->sqlParts["orderby"] = $orderby;
$this->sqlParts["sql"] = SQLQuery::gSQLWhere_having($gQuery->HeadToSql(), $gQuery->FromToSql() . $this->sqlParts["join"], $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $this->sqlParts["where"], $this->sqlParts["having"], $this->sqlParts["searchCriteria"]);
$this->sqlParts["sql"] .= " " . trim($this->sqlParts["orderby"]);
if ($this->eventsObject->exists("ListGetRowCount")) {
$this->totalRowCount = $this->eventsObject->ListGetRowCount($this->searchClauseObj, $this->masterTable, $this->masterKeys, $this->selectedRecords, $this);
}
if ($this->totalRowCount === false) {
$this->totalRowCount = SQLQuery::gSQLRowCount_int($gQuery->HeadToSql(), $gQuery->FromToSql() . $this->sqlParts["join"], $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $this->sqlParts["where"], $this->sqlParts["having"], $this->connection, $this->sqlParts["searchCriteria"]);
}
}
/**
* Use for count details recs number, if subQueryes not supported, or keys have different types
*
* @param integer $i
* @param array $detailid
* @intellisense
*/
function countDetailsRecsNoSubQ($dInd, &$detailid)
{
global $tables_data, $masterTablesData, $detailsTablesData, $allDetailsTablesArr, $cman;
$dDataSourceTable = $this->allDetailsTablesArr[$dInd]['dDataSourceTable'];
$detPSet = $this->pSet->getTable($dDataSourceTable);
$detCipherer = new RunnerCipherer($dDataSourceTable, $detPSet);
$detConnection = $cman->byTable($dDataSourceTable);
$detailsQuery = $detPSet->getSQLQuery();
$dSqlWhere = $detailsQuery->WhereToSql();
$detailKeys = $detPSet->getDetailKeysByMasterTable($this->tName);
$securityClause = SecuritySQL("Search", $dDataSourceTable);
// add where
if (strlen($securityClause)) {
$dSqlWhere = whereAdd($dSqlWhere, $securityClause);
}
$masterwhere = "";
foreach ($this->masterKeysByD[$dInd] as $idx => $val) {
if ($masterwhere) {
$masterwhere .= " and ";
}
$mastervalue = $detCipherer->MakeDBValue($detailKeys[$idx], $detailid[$idx], "", true);
if ($mastervalue == "null") {
$masterwhere .= RunnerPage::_getFieldSQL($detailKeys[$idx], $detConnection, $detPSet) . " is NULL ";
} else {
$masterwhere .= RunnerPage::_getFieldSQLDecrypt($detailKeys[$idx], $detConnection, $detPSet, $detCipherer) . "=" . $mastervalue;
}
}
return SQLQuery::gSQLRowCount_int($detailsQuery->HeadToSql(), $detailsQuery->FromToSql(), $dSqlWhere, $detailsQuery->GroupByToSql(), $detailsQuery->Having()->toSql($detailsQuery), $masterwhere, "", $detConnection);
}
/**
* Use for count details recs number, if subQueryes not supported, or keys have different types
*
* @param integer $i
* @param array $detailid
* @intellisense
*/
function countDetailsRecsNoSubQ($dInd, &$detailid)
{
global $tables_data;
global $masterTablesData;
global $detailsTablesData;
global $allDetailsTablesArr;
$dDataSourceTable = $this->allDetailsTablesArr[$dInd]['dDataSourceTable'];
$masterPSet = $this->pSet->getTable($dDataSourceTable);
$detailsQuery = $masterPSet->getSQLQuery();
$dSqlWhere = $detailsQuery->WhereToSql();
$detailKeys = $masterPSet->getDetailKeysByMasterTable($this->tName);
$securityClause = SecuritySQL("Search", $dDataSourceTable);
// add where
if (strlen($securityClause)) {
$dSqlWhere = whereAdd($dSqlWhere, $securityClause);
}
$masterwhere = "";
foreach ($this->masterKeysByD[$dInd] as $idx => $val) {
if ($masterwhere) {
$masterwhere .= " and ";
}
$mastervalue = $this->cipherer->MakeDBValue($detailKeys[$idx], $detailid[$idx], "", $dDataSourceTable, true);
if ($mastervalue == "null") {
$masterwhere .= GetFullFieldNameForInsert($masterPSet, $detailKeys[$idx]) . " is NULL ";
} else {
$masterwhere .= GetFullFieldName($detailKeys[$idx], $dDataSourceTable, false) . "=" . $mastervalue;
}
}
return SQLQuery::gSQLRowCount_int($detailsQuery->HeadToSql(), $detailsQuery->FromToSql(), $dSqlWhere, $detailsQuery->GroupByToSql(), $detailsQuery->Having()->toSql($detailsQuery), $masterwhere, "");
}
/**
* Builds SQL query, for retrieve data from DB
*/
function buildSQL()
{
$this->gstrOrderBy = $this->gQuery->OrderByToSql();
$this->gsqlHead = $this->gQuery->HeadToSql();
if ($this->connection->dbType == nDATABASE_DB2) {
$this->gsqlHead .= ", ROW_NUMBER() over () as DB2_ROW_NUMBER ";
}
// add to gsqlHead subquery counting the number of details
$this->addMasterDetailSubQuery();
$this->gsqlFrom = $this->gQuery->FromToSql();
$this->gsqlWhereExpr = $this->gQuery->WhereToSql();
$this->gsqlGroupBy = $this->gQuery->GroupByToSql();
$this->gsqlHaving = $this->gQuery->Having()->toSql($this->gQuery);
$whereComponents = $this->getWhereComponents();
$searchWhere = $whereComponents["searchWhere"];
$searchHaving = $whereComponents["searchHaving"];
$joinFromPart = $whereComponents["joinFromPart"];
$filterWhere = $this->getFiltersWhere();
$filterHaving = $this->getFiltersHaving();
//$this->strWhereClause could contain some conditions for a lookup list page so It needs combining with commonWhere
$sqlWhere = combineSQLCriteria(array($whereComponents["commonWhere"], $filterWhere, $this->strWhereClause));
$sqlHaving = combineSQLCriteria(array($whereComponents["commonHaving"], $filterHaving));
$sqlFrom = $this->gsqlFrom . $joinFromPart;
$strSecuritySql = $this->SecuritySQL("Search", $this->tName);
// where clause with foreign keys of current table and it's master table master keys
$masterWhere = $this->getMasterTableSQLClause();
$this->strWhereClause = combineSQLCriteria(array($searchWhere, $strSecuritySql, $masterWhere, $filterWhere));
$this->strHavingClause = combineSQLCriteria(array($searchHaving, $filterHaving));
if ($this->mode != LIST_DETAILS && $this->noRecordsFirstPage && !$this->isRequiredSearchRunning()) {
$this->strWhereClause = whereAdd($this->strWhereClause, "1=0");
$sqlWhere = combineSQLCriteria(array($sqlWhere, "1=0"));
}
$searchCombineType = $whereComponents["searchUnionRequired"] ? "or" : "and";
$strSQL = SQLQuery::gSQLWhere_having($this->gsqlHead, $sqlFrom, $sqlWhere, $this->gsqlGroupBy, $sqlHaving, $searchWhere, $searchHaving, $searchCombineType);
$strSQL .= " " . trim($this->strOrderBy);
// save SQL parts to use in "Export" and "Printer-friendly" pages and to get prev\next records
$_SESSION[$this->sessionPrefix . "_sql"] = $strSQL;
$_SESSION[$this->sessionPrefix . "_where"] = $this->strWhereClause;
$_SESSION[$this->sessionPrefix . "_having"] = $this->strHavingClause;
$_SESSION[$this->sessionPrefix . "_criteria"] = $searchCombineType;
$_SESSION[$this->sessionPrefix . "_order"] = $this->strOrderBy;
$_SESSION[$this->sessionPrefix . "_joinFromPart"] = $joinFromPart;
$strSQLbak = $strSQL;
$whereModifiedInEvent = false;
if ($this->eventExists("BeforeQueryList")) {
$tstrWhereClause = $this->strWhereClause;
$tstrOrderBy = $this->strOrderBy;
$this->eventsObject->BeforeQueryList($strSQL, $tstrWhereClause, $tstrOrderBy, $this);
$whereModifiedInEvent = $tstrWhereClause != $this->strWhereClause;
$this->strWhereClause = $tstrWhereClause;
$this->strOrderBy = $tstrOrderBy;
// Rebuild SQL if needed
if ($strSQL != $strSQLbak) {
// changed $strSQL - old style
$this->numRowsFromSQL = GetRowCount($strSQL, $this->connection);
} else {
if ($whereModifiedInEvent || $this->strOrderBy != $tstrOrderBy) {
$strSQL = SQLQuery::gSQLWhere_having($this->gsqlHead, $sqlFrom, $this->gsqlWhereExpr, $this->gsqlGroupBy, $this->gsqlHaving, $this->strWhereClause, $this->strHavingClause, $searchCombineType);
$strSQL .= " " . trim($this->strOrderBy);
}
}
}
$rowcount = false;
if ($this->eventExists("ListGetRowCount")) {
$rowcount = $this->eventsObject->ListGetRowCount($this->searchClauseObj, $this->masterTable, $this->masterKeysReq, null, $this);
}
if ($rowcount !== false) {
$this->numRowsFromSQL = $rowcount;
} else {
if ($whereModifiedInEvent) {
$this->numRowsFromSQL = SQLQuery::gSQLRowCount_int($this->gsqlHead, $sqlFrom, $this->gsqlWhereExpr, $this->gsqlGroupBy, $this->gsqlHaving, $this->strWhereClause, $this->strHavingClause, $this->connection, $searchCombineType);
} else {
$this->numRowsFromSQL = SQLQuery::gSQLRowCount_int($this->gsqlHead, $sqlFrom, $sqlWhere, $this->gsqlGroupBy, $sqlHaving, $searchWhere, $searchHaving, $this->connection, $searchCombineType);
}
}
LogInfo($strSQL);
$this->querySQL = $strSQL;
}
