/** * 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; }