$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);
}
// "_" is added to conver number type to string
$fieldControl->suggestValue("_" . $val, $searchFor, $response, $row);
}
}
$_connection->close();
ksort($response, SORT_STRING);
foreach ($response as $value => $realValue) {
db_pageseek($rs,$PageSize,$mypage);
}
elseif(GetDatabaseType() == 3)
{
if($numrows)
{
$strSQL = AddTop($strSQL, $mypage*$PageSize);
}
$rs = db_query($strSQL,$conn);
db_pageseek($rs,$PageSize,$mypage);
}
elseif(GetDatabaseType() == 1)
{
if($numrows && 1)
{
$strSQL = AddRowNumber($strSQL, $mypage*$PageSize);
}
$rs = db_query($strSQL,$conn);
db_pageseek($rs,$PageSize,$mypage);
}
elseif(GetDatabaseType() == 4)
{
if($numrows)
{
$maxrecs=$PageSize;
$strSQL.=" limit ".$PageSize." offset ".(($mypage-1)*$PageSize);
}
$rs = db_query($strSQL,$conn);
}
elseif(GetDatabaseType() == -1)
{
/**
* Seeks recs, depending on page number etc.
*
* @param string $strSQL
*/
function seekPageInRecSet($strSQL)
{
$listarray = false;
if ($this->eventExists("ListQuery")) {
$arrFieldForSort = array();
$arrHowFieldSort = array();
for ($i = 0; $i < count($this->orderClause->fieldsList); $i++) {
$arrFieldForSort[] = $this->orderClause->fieldsList[$i]->fieldIndex;
$arrHowFieldSort[] = $this->orderClause->fieldsList[$i]->orderDirection;
}
$listarray = $this->eventsObject->ListQuery($this->searchClauseObj, $arrFieldForSort, $arrHowFieldSort, $this->masterTable, $this->masterKeysReq, null, $this->pageSize, $this->myPage, $this);
}
if ($listarray !== false) {
$this->recSet = $listarray;
} else {
if ($this->dbType == nDATABASE_MySQL) {
if ($this->maxPages > 1) {
$strSQL .= " limit " . ($this->myPage - 1) * $this->pageSize . "," . $this->pageSize;
}
$this->recSet = db_query($strSQL, $this->conn);
} elseif ($this->dbType == nDATABASE_MSSQLServer) {
if ($this->maxPages > 1) {
$strSQL = AddTop($strSQL, $this->myPage * $this->pageSize);
}
$this->recSet = db_query($strSQL, $this->conn);
db_pageseek($this->recSet, $this->pageSize, $this->myPage);
} elseif ($this->dbType == nDATABASE_Access) {
if ($this->maxPages > 1) {
$strSQL = AddTop($strSQL, $this->myPage * $this->pageSize);
}
$this->recSet = db_query($strSQL, $this->conn);
db_pageseek($this->recSet, $this->pageSize, $this->myPage);
} elseif ($this->dbType == nDATABASE_Oracle) {
if ($this->maxPages > 1) {
$strSQL = AddRowNumber($strSQL, $this->myPage * $this->pageSize);
}
$this->recSet = db_query($strSQL, $this->conn);
db_pageseek($this->recSet, $this->pageSize, $this->myPage);
} elseif ($this->dbType == nDATABASE_PostgreSQL) {
if ($this->maxPages > 1) {
$maxrecs = $this->pageSize;
$strSQL .= " limit " . $this->pageSize . " offset " . ($this->myPage - 1) * $this->pageSize;
}
$this->recSet = db_query($strSQL, $this->conn);
} elseif ($this->dbType == nDATABASE_DB2) {
if ($this->maxPages > 1) {
$strSQL = "with DB2_QUERY as (" . $strSQL . ") select * from DB2_QUERY where DB2_ROW_NUMBER between " . (($this->myPage - 1) * $this->pageSize + 1) . " and " . $this->myPage * $this->pageSize;
}
$this->recSet = db_query($strSQL, $this->conn);
} elseif ($this->dbType == nDATABASE_Informix) {
if ($this->maxPages > 1) {
$strSQL = AddTopIfx($strSQL, $this->myPage * $this->pageSize);
}
$this->recSet = db_query($strSQL, $this->conn);
db_pageseek($this->recSet, $this->pageSize, $this->myPage);
} elseif ($this->dbType == nDATABASE_SQLite3) {
if ($this->maxPages > 1) {
$strSQL .= " limit " . ($this->myPage - 1) * $this->pageSize . "," . $this->pageSize;
}
$this->recSet = db_query($strSQL, $this->conn);
} else {
$this->recSet = db_query($strSQL, $this->conn);
db_pageseek($this->recSet, $this->pageSize, $this->myPage);
}
}
}
/**
* @param String sql
* @param Number pageStart
* @param Number pageSize
* @param Boolean applyLimit
*/
public function queryPage($strSQL, $pageStart, $pageSize, $applyLimit)
{
if ($this->dbType == nDATABASE_MySQL) {
if ($applyLimit) {
$strSQL .= " limit " . ($pageStart - 1) * $pageSize . "," . $pageSize;
}
return $this->query($strSQL)->getQueryHandle();
}
if ($this->dbType == nDATABASE_MSSQLServer || $this->dbType == nDATABASE_Access) {
if ($applyLimit) {
$strSQL = AddTop($strSQL, $pageStart * $pageSize);
}
$qResult = $this->query($strSQL);
$qResult->seekPage($pageSize, $pageStart);
return $qResult->getQueryHandle();
}
if ($this->dbType == nDATABASE_Oracle) {
if ($applyLimit) {
$strSQL = AddRowNumber($strSQL, $pageStart * $pageSize);
}
$qResult = $this->query($strSQL);
$qResult->seekPage($pageSize, $pageStart);
return $qResult->getQueryHandle();
}
if ($this->dbType == nDATABASE_PostgreSQL) {
if ($applyLimit) {
$strSQL .= " limit " . $pageSize . " offset " . ($pageStart - 1) * $pageSize;
}
return $this->query($strSQL)->getQueryHandle();
}
if ($this->dbType == nDATABASE_DB2) {
if ($applyLimit) {
$strSQL = "with DB2_QUERY as (" . $strSQL . ") select * from DB2_QUERY where DB2_ROW_NUMBER between " . (($pageStart - 1) * $pageSize + 1) . " and " . $pageStart * $pageSize;
}
return $this->query($strSQL)->getQueryHandle();
}
if ($this->dbType == nDATABASE_Informix) {
if ($applyLimit) {
$strSQL = AddTopIfx($strSQL, $pageStart * $pageSize);
}
$qResult = $this->query($strSQL);
$qResult->seekPage($pageSize, $pageStart);
return $qResult->getQueryHandle();
}
if ($this->dbType == nDATABASE_SQLite3) {
if ($applyLimit) {
$strSQL .= " limit " . ($pageStart - 1) * $pageSize . "," . $pageSize;
}
return $this->query($strSQL)->getQueryHandle();
}
$qResult = $this->query($strSQL);
$qResult->seekPage($pageSize, $pageStart);
return $qResult->getQueryHandle();
}
/**
* Apply a limit to an SQL-query
* @param String sql
* @param Number N
* @param Number dbType
* @return String
*/
function applyDBrecordLimit($sql, $N, $dbType)
{
if (!strlen($dbType)) {
return $sql;
}
if ($dbType == nDATABASE_MySQL || $dbType == nDATABASE_PostgreSQL || $dbType == nDATABASE_SQLite3) {
return $sql . " LIMIT " . $N;
}
if ($dbType == nDATABASE_Oracle) {
return AddRowNumber($sql, $N);
}
if ($dbType == nDATABASE_MSSQLServer || $dbType == nDATABASE_Access) {
return AddTop($sql, $N);
}
if ($dbType == nDATABASE_Informix) {
return AddTopIfx($sql, $N);
}
if ($dbType == nDATABASE_DB2) {
return AddTopDB2($sql, $N);
}
return $sql;
}
function getNextPrevRecordKeys(&$data,$securityMode,&$next,&$prev)
{
global $conn;
$next=array();
$prev=array();
if(@$_SESSION[$this->sessionPrefix."_noNextPrev"])
return;
$prevExpr = "";
$nextExpr = "";
$where_next="";
$where_prev="";
$order_next="";
$order_prev="";
require_once(getabspath('classes/orderclause.php'));
$orderClause = new OrderClause($this);
$orderClause->init();
$query = $this->pSet->getQueryObject();
$where = $_SESSION[$this->sessionPrefix."_where"];
if(!strlen($where))
$where = SecuritySQL($securityMode);
$having = $_SESSION[$this->sessionPrefix."_having"];
$joinFromPart = $_SESSION[$this->sessionPrefix."_joinFromPart"];
$tKeys = $this->pSet->getTableKeys();
if(!count($orderClause->fieldsList))
{
$_SESSION[$this->sessionPrefix."_noNextPrev"] = 1;
return;
}
// make next & prev ORDER BY strings
for($i = 0; $i < count($orderClause->fieldsList); $i++)
{
$field = $orderClause->fieldsList[$i];
if(!$this->pSet->GetFieldByIndex($field->fieldIndex))
continue;
if($order_next == "")
{
$order_next = " ORDER BY ";
$order_prev = " ORDER BY ";
}
else
{
$order_next .= ",";
$order_prev .= ",";
}
$order_next .= $field->fieldIndex." ".$field->orderDirection;
$order_prev .= $field->fieldIndex." ".($field->orderDirection == "DESC" ? "ASC" : "DESC");
}
// make next & prev where expressions
$tail="";
for($i = 0; $i < count($orderClause->fieldsList); $i++)
{
$field = $orderClause->fieldsList[$i];
$fieldName = $this->pSet->GetFieldByIndex($field->fieldIndex);
if(!$fieldName)
continue;
if(!$query->HasGroupBy())
$fullName = GetFullFieldName($fieldName, $this->tName, false);
else
$fullName = AddFieldWrappers($fieldName);
$asc = ($field->orderDirection == "ASC");
if(!is_null($data[$fieldName]))
{
// current field value is not null
$value = $this->cipherer->MakeDBValue($fieldName, $data[$fieldName], "", "", true);
$nextop = ($asc ? ">" : "<");
$prevop = ($asc ? "<" : ">");
$nextExpr = $fullName.$nextop.$value;
$prevExpr = $fullName.$prevop.$value;
if($nextop=="<")
$nextExpr .= " or ".$fullName." IS NULL";
else
$prevExpr .= " or ".$fullName." IS NULL";
if($i < count($orderClause->fieldsList) - 1)
{
$nextExpr .= " or ".$fullName."=".$value;
$prevExpr .= " or ".$fullName."=".$value;
}
}
else
{
$nextExpr = "";
$prevExpr = "";
// current field value is null
if($asc)
$nextExpr = $fullName." IS NOT NULL";
else
$prevExpr = $fullName." IS NOT NULL";
if($i < count($orderClause->fieldsList) - 1)
{
if($nextExpr != "")
$nextExpr.=" or ";
$nextExpr .= $fullName." IS NULL";
if($prevExpr != "")
$prevExpr.=" or ";
$prevExpr .= $fullName." IS NULL";
}
}
if($nextExpr == "")
$nextExpr = " 1=0 ";
if($prevExpr == "")
$prevExpr = " 1=0 ";
// append expression to where clause
if($i>0)
{
$where_next .= " AND ";
$where_prev .= " AND ";
}
$where_next .= "(".$nextExpr;
$where_prev .= "(".$prevExpr;
$tail .=")";
}
$where_next = $where_next.$tail;
$where_prev = $where_prev.$tail;
if($where_next=="" or $order_next=="" or $where_prev=="" or $order_prev=="")
{
$_SESSION[$this->sessionPrefix."_noNextPrev"] = 1;
return;
}
// make the resulting query
if($query === null)
return;
if(!$query->HasGroupBy())
{
$oWhere = $query->Where();
$where = whereAdd($where,$oWhere->toSql($query));
$where_next = whereAdd($where_next,$where);
$where_prev = whereAdd($where_prev,$where);
$query->ReplaceFieldsWithDummies($this->pSet->getBinaryFieldsIndices());
$sql_next = $query->toSql($where_next, $order_next, null, false, $joinFromPart);
$sql_prev = $query->toSql($where_prev, $order_prev, null, false, $joinFromPart);
}
else
{
$oWhere = $query->Where();
$oHaving = $query->Having();
$where = whereAdd($where,$oWhere->toSql($query));
$having = whereAdd($having,$oHaving->toSql($query));
$query->ReplaceFieldsWithDummies($this->pSet->getBinaryFieldsIndices());
$sql = "select * from (".$query->toSql($where, "", $having, false, $joinFromPart).") prevnextquery";
$sql_next = $sql." WHERE ".$where_next.$order_next;
$sql_prev = $sql." WHERE ".$where_prev.$order_prev;
}
if(GetGlobalData("returnToActualListPage", false))
{
if($prevExpr == " 1=0 ")
$_SESSION[$this->sessionPrefix."_pagenumber"] = 1;
else{
$pageSQL = "select count(*) from (".$sql_prev.") tcount";
$pageRes = db_query($pageSQL, $conn);
$pageRow = db_fetch_numarray($pageRes);
$currentRow = $pageRow[0];
if($this->pageSize > 0)
$pageSize = $this->pageSize;
else
$pageSize = $this->pSet->getInitialPageSize();
$this->myPage = floor($currentRow / $pageSize) + 1;
$_SESSION[$this->sessionPrefix."_pagenumber"] = $this->myPage;
}
}
// add record count options
if(GetDatabaseType() == 0 || GetDatabaseType() == 4 || GetDatabaseType() == -1) {
$sql_next.=" limit 1";
$sql_prev.=" limit 1";
}elseif(GetDatabaseType() == 2 || GetDatabaseType() == 3) {
$sql_next=AddTop($sql_next, 1);
$sql_prev=AddTop($sql_prev, 1);
}elseif(GetDatabaseType() == 1) {
$sql_next=AddRowNumber($sql_next, 1);
$sql_prev=AddRowNumber($sql_prev, 1);
}
$res_next = db_query($sql_next,$conn);
if($res_next)
{
if($row_next = $this->cipherer->DecryptFetchedArray($res_next))
{
foreach($tKeys as $i=>$k)
{
$next[$i] = $row_next[$k];
}
}
db_closequery($res_next);
}
$res_prev = db_query($sql_prev,$conn);
if($row_prev = $this->cipherer->DecryptFetchedArray($res_prev))
{
foreach($tKeys as $i=>$k)
{
$prev[$i] = $row_prev[$k];
}
}
db_closequery($res_prev);
}