/** * Form the SQL query string to get then the filter's data */ protected function buildSQL() { $dbfName = $this->getDbFieldName($this->fName); $sqlHead = "SELECT MIN(" . $dbfName . ") as " . $this->connection->addFieldWrappers("sliderMin") . ", MAX(" . $dbfName . ") as " . $this->connection->addFieldWrappers("sliderMax"); $whereComponents = $this->whereComponents; $gQuery = $this->pSet->getSQLQuery(); $sqlFrom = $gQuery->FromToSql() . $whereComponents["joinFromPart"]; $sqlWhere = $this->getCombinedFilterWhere(); $sqlGroupBy = "GROUP BY " . $dbfName; $sqlHaving = $this->getCombinedFilterHaving(); $notNullWhere = $dbfName . " is not NULL"; if ($this->connection->dbType != nDATABASE_Oracle) { if (IsCharType($this->fieldType)) { $notNullWhere = $dbfName . "<>'' and " . $notNullWhere; } } $sqlWhere = whereAdd($sqlWhere, $notNullWhere); $searchCombineType = $whereComponents["searchUnionRequired"] ? "or" : "and"; $this->strSQL = SQLQuery::gSQLWhere_having($sqlHead, $sqlFrom, $sqlWhere, "", "", $whereComponents["searchWhere"], $whereComponents["searchHaving"], $strSearchCriteria); }
$where = whereAdd($where . $masterWhere, $strSecuritySql); $clausesData = $fieldControl->getSelectColumnsAndJoinFromPart($searchFor, $searchOpt, true); $selectColumns = $clausesData["selectColumns"]; $fromClause = $gQuery->FromToSql() . $clausesData["joinFromPart"]; $distinct = "DISTINCT"; if ($_connection->dbType == nDATABASE_MSSQLServer || $_connection->dbType == nDATABASE_Access) { if (IsTextType($fType)) { $distinct = ""; } } $sqlHead = "SELECT " . $distinct . " " . $selectColumns . " as _srchfld_"; if ($gQuery->HasGroupBy()) { $strSQL = $gQuery->gSQLWhere_having_fromQuery("", $where, $having); $strSQL = "SELECT DISTINCT st." . $_connection->addFieldWrappers($f) . " from (" . $strSQL . ") st"; } else { $strSQL = SQLQuery::gSQLWhere_having($sqlHead, $fromClause, $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $where, $having); } if ($_connection->dbType == nDATABASE_MySQL || $_connection->dbType == nDATABASE_PostgreSQL) { $strSQL .= " LIMIT " . $numberOfSuggests; } elseif ($_connection->dbType == nDATABASE_MSSQLServer || $_connection->dbType == nDATABASE_Access) { $strSQL = "select top " . $numberOfSuggests . " * from (" . $strSQL . ") st"; } elseif ($_connection->dbType == nDATABASE_Oracle) { $strSQL = AddRowNumber($strSQL, $numberOfSuggests); } $qResult = $_connection->query($strSQL); // fill $response array with the field's suggest value while (($row = $qResult->fetchNumeric()) && count($response) < $numberOfSuggests) { $val = $cipherer->DecryptField($f, $row[0]); if (IsGuid($fType)) { $val = substr($val, 1, -1); }
// order by $strOrderBy = $_SESSION[$strTableName . "_order"]; if (!$strOrderBy) { $strOrderBy = $gstrOrderBy; } $strSQL .= " " . trim($strOrderBy); $strSQLbak = $strSQL; if ($eventObj->exists("BeforeQueryExport")) { $eventObj->BeforeQueryExport($strSQL, $strWhereClause, $strOrderBy, $pageObject); } // Rebuild SQL if needed if ($strSQL != $strSQLbak) { // changed $strSQL - old style $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); } }
/** * Read current values from the database * * @return {array} array of current record data */ function getCurrentRecordInternal() { if (!is_null($this->data)) return $this->data; global $gstrOrderBy, $conn; $strWhereClause = ''; $strHavingClause = ''; if(!$this->all) { $strWhereClause = KeyWhere($this->keys); if($this->pSet->getAdvancedSecurityType()!=ADVSECURITY_ALL) $strWhereClause = whereAdd($strWhereClause, SecuritySQL("Search")); $strSQL = $this->gQuery->gSQLWhere($strWhereClause); } else { if ($_SESSION[$this->tName."_SelectedSQL"]!="" && @$_REQUEST["records"]=="") { $strSQL = $_SESSION[$this->tName."_SelectedSQL"]; $strWhereClause=@$_SESSION[$this->tName."_SelectedWhere"]; } else { $strWhereClause = @$_SESSION[$this->tName."_where"]; $strHavingClause = @$_SESSION[$this->tName."_having"]; $strSearchCriteria = @$_SESSION[$this->tName."_criteria"]; $joinFromPart = @$_SESSION[$this->tName."_joinFromPart"]; if($this->pSet->getAdvancedSecurityType()==ADVSECURITY_VIEW_OWN && $strWhereClause=="") $strWhereClause = whereAdd($strWhereClause, SecuritySQL("Search")); //$strSQL = $this->gQuery->gSQLWhere($strWhereClause, $strHavingClause, $strSearchCriteria); $strSQL = SQLQuery::gSQLWhere_having($this->gQuery->HeadToSql(), $this->gQuery->FromToSql().$joinFromPart, $this->gQuery->WhereToSql(), $this->gQuery->GroupByToSql(), $this->gQuery->Having()->toSql($this->gQuery), $strWhereClause, $strHavingClause, $strSearchCriteria); } // order by $strOrderBy = $_SESSION[$this->tName."_order"]; if(!$strOrderBy) $strOrderBy = $gstrOrderBy; $strSQL.=" ".trim($strOrderBy); } $strSQLbak = $strSQL; if($this->eventsObject->exists("BeforeQueryView")) $this->eventsObject->BeforeQueryView($strSQL, $strWhereClause, $this); if($strSQLbak == $strSQL) { //$strSQL = $this->gQuery->gSQLWhere($strWhereClause, $strHavingClause); $strSQL = SQLQuery::gSQLWhere_having($this->gQuery->HeadToSql(), $this->gQuery->FromToSql().$joinFromPart, $this->gQuery->WhereToSql(), $this->gQuery->GroupByToSql(), $this->gQuery->Having()->toSql($this->gQuery), $strWhereClause, $strHavingClause, $strSearchCriteria); if($this->all) { $numrows = $this->gQuery->gSQLRowCount($strWhereClause, $strHavingClause, $strSearchCriteria); $strSQL.=" ".trim($strOrderBy); } } else { // changed $strSQL - old style if($this->all) $numrows = GetRowCount($strSQL); } if(!$this->all) { LogInfo($strSQL); $rs = db_query($strSQL, $conn); } else { // Pagination: $nPageSize = 0; if(@$_REQUEST["records"]=="page" && $numrows) { $mypage = (integer)@$_SESSION[$this->tName."_pagenumber"]; $nPageSize = (integer)@$_SESSION[$this->tName."_pagesize"]; if($numrows <= ($mypage-1)*$nPageSize) $mypage = ceil($numrows/$nPageSize); if(!$nPageSize) $nPageSize = $gPageSize; if(!$mypage) $mypage = 1; $strSQL.=" limit ".(($mypage-1)*$nPageSize).",".$nPageSize; } $rs = db_query($strSQL,$conn); } $this->data = $this->cipherer->DecryptFetchedArray($rs); if(sizeof($this->data) && $this->eventsObject->exists("ProcessValuesView")) $this->eventsObject->ProcessValuesView($this->data, $this); return $this->data; }
/** * 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; }
static function gSQLRowCount_int($sqlHead, $sqlFrom, $sqlWhere, $sqlGroupBy, $sqlHaving, $where, $having, $criteria="or") { global $conn; global $bSubqueriesSupported; $strWhere=whereAdd($sqlWhere,$where); if(strlen($strWhere)) $strWhere=" where ".$strWhere." "; if(strlen($sqlGroupBy)) { if($bSubqueriesSupported) { $countstr = "select count(*) from (".SQLQuery::gSQLWhere_having($sqlHead,$sqlFrom,$sqlWhere,$sqlGroupBy, $sqlHaving,$where,$having,$criteria).") a"; } else { $countstr = SQLQuery::gSQLWhere_having($sqlHead,$sqlFrom,$sqlWhere,$sqlGroupBy, $sqlHaving,$where,$having,$criteria); return GetMySQL4RowCount($countstr); } } else { $countstr = "select count(*) ".$sqlFrom.$strWhere; } $countrs = db_query($countstr, $conn); $countdata = db_fetch_numarray($countrs); return $countdata[0]; }
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"]); } }
/** * Form the SQL query string to get then the filter's data */ protected function buildSQL() { $dbfName = $this->getDbFieldName($this->fName); $sqlHead = "SELECT " . $dbfName; if ($dbfName != $this->connection->addFieldWrappers($this->fName)) { $sqlHead .= " as " . $this->connection->addFieldWrappers($this->fName); } $sqlHead .= $this->getExtraSelectColumns(); if ($this->useTotals) { $sqlHead .= ", " . $this->getTotals(); } $whereComponents = $this->whereComponents; $gQuery = $this->pSet->getSQLQuery(); $sqlFrom = $gQuery->FromToSql() . $whereComponents["joinFromPart"]; $sqlGroupBy = "GROUP BY " . $this->getGroupByColumns($dbfName); $sqlHaving = $this->getCombinedFilterHaving(); $notNullWhere = $this->getNotNullWhere($dbfName); $sqlWhere = whereAdd($notNullWhere, $this->getCombinedFilterWhere($this->hasDependent)); $searchCombineType = $whereComponents["searchUnionRequired"] ? "or" : "and"; $this->strSQL = SQLQuery::gSQLWhere_having($sqlHead, $sqlFrom, $sqlWhere, $sqlGroupBy, $sqlHaving, $whereComponents["searchWhere"], $whereComponents["searchHaving"], $searchCombineType); // add ORDER BY to sort the result records if ($this->sortingType != SORT_BY_DISP_VALUE) { $sortFieldName = $dbfName; if ($this->sortingType == SORT_BY_GR_VALUE && $this->useTotals) { $sortFieldName = $this->connection->addFieldWrappers($this->fName . "TOTAL"); } $this->strSQL .= ' ORDER BY ' . $sortFieldName . ($this->isDescendingSortOrder ? ' DESC' : ' ASC'); } }
/** * @param String sqlHead * @param String sqlFrom * @param String sqlWhere * @param String sqlGroupBy * @param String sqlHaving * @param String where * @param String having * @param Connection connection * @param String criteria (optional) */ static function gSQLRowCount_int($sqlHead, $sqlFrom, $sqlWhere, $sqlGroupBy, $sqlHaving, $where, $having, $connection, $criteria = "or") { $strWhere = whereAdd($sqlWhere, $where); if (strlen($strWhere)) { $strWhere = " where " . $strWhere . " "; } $useAsSubquery = strlen($sqlGroupBy) > 0; if (!$useAsSubquery) { $sql = $sqlFrom . $strWhere; } else { $sql = SQLQuery::gSQLWhere_having($sqlHead, $sqlFrom, $sqlWhere, $sqlGroupBy, $sqlHaving, $where, $having, $criteria); } return $connection->getFetchedRowsNumber($sql, $useAsSubquery); }
/** * 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; }
/** * Form the SQL query string to get then the filter's data */ protected function buildSQL() { $sqlHead = "SELECT ".$this->getTotals(); $whereComponents = $this->whereComponents; $gQuery = $this->pSet->getSQLQuery(); $sqlFrom = $gQuery->FromToSql().$whereComponents["joinFromPart"]; $sqlWhere = $this->getCombinedFilterWhere(); $sqlHaving = $this->getCombinedFilterHaving(); $searchCombineType = $whereComponents["searchUnionRequired"] ? "or" : "and"; $this->strSQL = SQLQuery::gSQLWhere_having($sqlHead, $sqlFrom, $sqlWhere, "", $sqlHaving, $whereComponents["searchWhere"], $whereComponents["searchHaving"], $searchCombineType); }