$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)
		{
Example #3
0
 /**
  * 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);
         }
     }
 }
Example #4
0
 /**
  * @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);
	}