Example #1
1
 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);
 }