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"]; $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); $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($this->pSet->getBinaryFieldsIndices()); $sql = "select * from (" . $query->toSql($where, "", $having) . ") 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 $sql_next .= " limit 1"; $sql_prev .= " limit 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); }
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); }