/**
* Get SQL query data for the Report constructor
* @return Array
*/
public function getReportSQLData()
{
$_gQuery = $this->pSet->getSQLQuery();
$whereComponents = $this->getWhereComponents();
$gsqlWhereExpr = $this->getnoRecOnFirstPageWhereCondition();
$gsqlWhereExpr = combineSQLCriteria(array($gsqlWhereExpr, $whereComponents["commonWhere"], $this->getFiltersWhere()));
$_SESSION[$this->sessionPrefix . "_where"] = $gsqlWhereExpr;
$gsqlHavingExpr = combineSQLCriteria(array($whereComponents["commonHaving"], $this->getFiltersHaving()));
return array($_gQuery->HeadToSql(), $_gQuery->FromToSql(), $gsqlWhereExpr, $_gQuery->GroupByToSql(), $gsqlHavingExpr);
}
/**
* Get the lookup SQL Query string
*
* @param String parentValue
* @param String childVal
* @param Boolean doCategoryFilter (optional)
* @param Boolean doValueFilter (optional)
* @param Boolean addCategoryField (optional)
* @param Boolean doWhereFilter (optional)
* @param Boolean oneRecordMode (optional)
* @return String
*/
protected function getLookupSQL($parentVal, $childVal = "", $doCategoryFilter = true, $doValueFilter = false, $addCategoryField = false, $doWhereFilter = true, $oneRecordMode = false)
{
if ($this->lookupType != LT_LOOKUPTABLE && $this->lookupType != LT_QUERY) {
return "";
}
$pSet = $this->pageObject->pSetEdit;
$strCategoryFilter = $pSet->getCategoryFilter($this->field);
// build Order By clause
$strOrderBy = $pSet->getLookupOrderBy($this->field);
if ($this->lookupConnection->dbType == nDATABASE_MSSQLServer) {
$strUniqueOrderBy = $strOrderBy;
}
if (strlen($strOrderBy)) {
if ($this->lookupType == LT_QUERY) {
$strOrderBy = RunnerPage::_getFieldSQLDecrypt($strOrderBy, $this->lookupConnection, $this->lookupPSet, $this->ciphererDisplay);
} else {
$strOrderBy = $this->lookupConnection->addFieldWrappers($strOrderBy);
}
if ($pSet->isLookupDesc($this->field)) {
$strOrderBy .= ' DESC';
}
}
// build Where clause
$lookupWhere = $doWhereFilter ? $this->getLookupWhere() : "";
$categoryWhere = $doCategoryFilter ? $this->getCategoryWhere($parentVal, $strCategoryFilter) : "";
$childWhere = $doValueFilter ? $this->getChildWhere($childVal) : "";
$strWhere = combineSQLCriteria(array($lookupWhere, $categoryWhere, $childWhere));
if ($this->lookupConnection->dbType == nDATABASE_Oracle) {
if ($oneRecordMode) {
$strWhere = whereAdd($strWhere, "rownum < 2");
}
}
// build SQL string
if ($this->lookupType == LT_QUERY) {
$lookupQueryObj = $this->lookupPSet->getSQLQuery();
if ($this->customDisplay) {
$lookupQueryObj->AddCustomExpression($this->displayFieldName, $this->lookupPSet, $this->tName, $this->field);
}
$lookupQueryObj->ReplaceFieldsWithDummies($this->lookupPSet->getBinaryFieldsIndices());
$strWhere = whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $strWhere);
$orderByClause = strlen($strOrderBy) ? ' ORDER BY ' . $strOrderBy : null;
return $lookupQueryObj->toSql($strWhere, $orderByClause, null, $oneRecordMode);
}
$LookupSQL = "SELECT ";
if ($this->lookupConnection->dbType == nDATABASE_MSSQLServer || $this->lookupConnection->dbType == nDATABASE_Access) {
if ($oneRecordMode) {
$LookupSQL .= "top 1 ";
}
}
$bUnique = $pSet->isLookupUnique($this->field);
if ($bUnique && !$oneRecordMode) {
$LookupSQL .= "DISTINCT ";
}
$LookupSQL .= $this->lwLinkField;
if (!$this->linkAndDisplaySame) {
$LookupSQL .= "," . RunnerPage::sqlFormattedDisplayField($this->field, $this->lookupConnection, $pSet);
}
if ($addCategoryField && strlen($strCategoryFilter)) {
$LookupSQL .= "," . $this->lookupConnection->addFieldWrappers($strCategoryFilter);
}
if ($this->lookupConnection->dbType == nDATABASE_MSSQLServer) {
if ($strUniqueOrderBy && $bUnique && !$oneRecordMode) {
$LookupSQL .= "," . $this->lookupConnection->addFieldWrappers($strUniqueOrderBy);
}
}
$LookupSQL .= " FROM " . $this->lookupConnection->addTableWrappers($this->lookupTable);
if (strlen($strWhere)) {
$LookupSQL .= " WHERE " . $strWhere;
}
if (strlen($strOrderBy)) {
$LookupSQL .= " ORDER BY " . $this->lookupConnection->addTableWrappers($this->lookupTable) . "." . $strOrderBy;
}
if ($this->lookupConnection->dbType == nDATABASE_MySQL) {
if ($oneRecordMode) {
$LookupSQL .= " limit 0,1";
}
}
if ($this->lookupConnection->dbType == nDATABASE_PostgreSQL) {
if ($oneRecordMode) {
$LookupSQL .= " limit 1";
}
}
if ($this->lookupConnection->dbType == nDATABASE_DB2) {
if ($oneRecordMode) {
$LookupSQL .= " fetch first 1 rows only";
}
}
return $LookupSQL;
}
/**
* Get and array of WHERE and HAVING components
*/
static function sGetWhereComponents($query, $pSet, $searchObj, $controls, $connection, $masterTableSQLClause = "", $secSQL = false)
{
$whereComponents = array();
$whereComponents["commonWhere"] = combineSQLCriteria(array($query->WhereToSql(), $masterTableSQLClause, $secSQL !== false ? $secSQL : SecuritySQL("Search", $pSet->getTableName())));
$whereComponents["commonHaving"] = combineSQLCriteria(array($query->Having()->toSql($query)));
$nonaggregatedFields = $pSet->getListOfFieldsByExprType(false);
$aggregatedFields = $pSet->getListOfFieldsByExprType(true);
$searchObj->haveAgregateFields = count($agregateFields) > 0;
$whereComponents["searchWhere"] = $searchObj->getWhere($nonaggregatedFields, $controls);
$whereComponents["searchHaving"] = $searchObj->getWhere($aggregatedFields, $controls);
$whereComponents["joinFromPart"] = $searchObj->getCommonJoinFromParts($controls);
$whereComponents["searchUnionRequired"] = "or" === $searchObj->getCriteriaCombineType() && 0 != strlen($whereComponents["searchHaving"]) && 0 != strlen($whereComponents["searchWhere"]);
$searchObj->processFiltersWhere($connection);
$filters = $searchObj->filteredFields;
$whereComponents["filterWhere"] = array();
foreach ($nonaggregatedFields as $f) {
if (isset($filters[$f])) {
$whereComponents["filterWhere"][$f] = $filters[$f]["where"];
}
}
$whereComponents["filterHaving"] = array();
foreach ($aggregatedFields as $f) {
if (isset($filters[$f])) {
$whereComponents["filterHaving"][$f] = $filters[$f]["where"];
}
}
return $whereComponents;
}
/**
* 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;
}