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