PHP SQLQuery::gSQLRowCount_int方法代码示例

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