$mypage = (int) @$_SESSION[$strTableName . "_pagenumber"];
$nPageSize = (int) @$_SESSION[$strTableName . "_pagesize"];
if (!$nPageSize) {
$nPageSize = GetTableData($strTableName, ".pageSize", 0);
}
if ($nPageSize < 0) {
$nPageSize = 0;
}
if ($nPageSize > 0) {
if ($numrows <= ($mypage - 1) * $nPageSize) {
$mypage = ceil($numrows / $nPageSize);
}
if (!$mypage) {
$mypage = 1;
}
$strSQL = AddTop($strSQL, $mypage * $nPageSize);
}
}
$listarray = false;
if ($eventObj->exists("ListQuery")) {
$listarray = $eventObj->ListQuery($pageObject->searchClauseObj, $_SESSION[$strTableName . "_arrFieldForSort"], $_SESSION[$strTableName . "_arrHowFieldSort"], $_SESSION[$strTableName . "_mastertable"], $masterKeysReq, $selected_recs, $nPageSize, $mypage);
}
if ($listarray !== false) {
$rs = $listarray;
} elseif ($nPageSize > 0) {
$rs = db_query($strSQL, $conn);
db_pageseek($rs, $nPageSize, $mypage);
} else {
$rs = db_query($strSQL, $conn);
}
if (!ini_get("safe_mode")) {
/**
* 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 = "";
$arrFieldForSort = array();
$arrHowFieldSort = array();
$query = GetQueryObject($this->tName);
$where = $_SESSION[$this->sessionPrefix . "_where"];
if (!strlen($where)) {
$where = SecuritySQL($securityMode);
}
$having = $_SESSION[$this->sessionPrefix . "_having"];
$orderindexes = GetTableData($this->tName, ".orderindexes", array());
$strOrderBy = GetTableData($this->tName, ".strOrderBy", '');
// calc indexes of the key fields
$keyIndexes = array();
$tKeys = GetTableKeys($this->tName);
foreach ($tKeys as $k) {
$ki = GetFieldIndex($k, $this->tName);
if ($ki) {
$keyIndexes[] = $ki;
}
}
if (!count($keyIndexes)) {
$_SESSION[$this->sessionPrefix . "_noNextPrev"] = 1;
return;
}
// get current ordering info
if (@$_SESSION[$this->sessionPrefix . "_arrFieldForSort"] && @$_SESSION[$this->sessionPrefix . "_arrHowFieldSort"]) {
$arrFieldForSort = $_SESSION[$this->sessionPrefix . "_arrFieldForSort"];
$arrHowFieldSort = $_SESSION[$this->sessionPrefix . "_arrHowFieldSort"];
} else {
// get default order
if (count($orderindexes)) {
for ($i = 0; $i < count($orderindexes); $i++) {
$arrFieldForSort[] = $orderindexes[$i][0];
$arrHowFieldSort[] = $orderindexes[$i][1];
}
} elseif ($strOrderBy != '') {
$_SESSION[$this->sessionPrefix . "_noNextPrev"] = 1;
return;
}
// add key fields
for ($i = 0; $i < count($keyIndexes); $i++) {
if (array_search($keyIndexes[$i], $arrFieldForSort) === false) {
$arrFieldForSort[] = $keyIndexes[$i];
$arrHowFieldSort[] = "ASC";
}
}
$_SESSION[$this->sessionPrefix . "_arrFieldForSort"] = $arrFieldForSort;
$_SESSION[$this->sessionPrefix . "_arrHowFieldSort"] = $arrHowFieldSort;
}
if (!count($arrFieldForSort)) {
$_SESSION[$this->sessionPrefix . "_noNextPrev"] = 1;
return;
}
// make next & prev ORDER BY strings
for ($i = 0; $i < count($arrFieldForSort); $i++) {
if (!GetFieldByIndex($arrFieldForSort[$i], $this->tName)) {
continue;
}
if ($order_next == "") {
$order_next = " ORDER BY ";
$order_prev = " ORDER BY ";
} else {
$order_next .= ",";
$order_prev .= ",";
}
$order_next .= $arrFieldForSort[$i] . " " . $arrHowFieldSort[$i];
$order_prev .= $arrFieldForSort[$i] . " " . ($arrHowFieldSort[$i] == "DESC" ? "ASC" : "DESC");
}
// make next & prev where expressions
$tail = "";
for ($i = 0; $i < count($arrFieldForSort); $i++) {
$fieldName = GetFieldByIndex($arrFieldForSort[$i], $this->tName);
if (!$fieldName) {
continue;
}
if (!$query->HasGroupBy()) {
$fullName = GetFullFieldName($fieldName, $this->tName);
} else {
$fullName = $fieldName;
}
$asc = $arrHowFieldSort[$i] == "ASC";
if (!is_null($data[$fieldName])) {
// current field value is not null
$value = make_db_value($fieldName, $data[$fieldName], "", "", $this->tName);
$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($arrFieldForSort) - 1) {
$nextExpr .= " or " . $fullName . "=" . $value;
$prevExpr .= " or " . $fullName . "=" . $value;
}
} else {
// current field value is null
if ($asc) {
$nextExpr = $fullName . " IS NOT NULL";
} else {
$prevExpr = $fullName . " IS NOT NULL";
}
if ($i < count($arrFieldForSort) - 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(GetBinaryFieldsIndices($this->tName));
$sql_next = $query->toSql($where_next, $order_next);
$sql_prev = $query->toSql($where_prev, $order_prev);
} else {
$oWhere = $query->Where();
$oHaving = $query->Having();
$where = whereAdd($where, $oWhere->toSql($query));
$having = whereAdd($having, $oHaving->toSql($query));
$query->ReplaceFieldsWithDummies(GetBinaryFieldsIndices($this->tName));
$sql = "select * from (" . $query->toSql($where, "", $having) . ") prevnextquery";
$sql_next = $sql . " WHERE " . $where_next . $order_next;
$sql_prev = $sql . " WHERE " . $where_prev . $order_prev;
}
// add record count options
$sql_next = AddTop($sql_next, 1);
$sql_prev = AddTop($sql_prev, 1);
$res_next = db_query($sql_next, $conn);
if ($res_next) {
if ($row_next = db_fetch_array($res_next)) {
foreach ($tKeys as $i => $k) {
$next[$i + 1] = $row_next[$k];
}
}
db_closequery($res_next);
}
$res_prev = db_query($sql_prev, $conn);
if ($row_prev = db_fetch_array($res_prev)) {
foreach ($tKeys as $i => $k) {
$prev[$i + 1] = $row_prev[$k];
}
}
db_closequery($res_prev);
}
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);
}
