$allSearchFields = GetTableData($strTableName, '.googleLikeFields', array());
}
// proccess fields and create sql
foreach ($allSearchFields as $f) {
$fType = GetFieldType($f, $strTableName);
// filter fields by type
if (!IsCharType($fType) && !IsNumberType($fType) && !IsGuid($fType) || IsTextType($fType)) {
continue;
}
// get suggest for field
if (($searchField == '' || $searchField == GoodFieldName($f)) && CheckFieldPermissions($f)) {
$where = "";
$having = "";
if (!$gQuery->IsAggrFuncField(GetFieldIndex($f) - 1)) {
$where = $searchClauseObj->getSuggestWhere($f, $fType, $suggestAllContent, $searchFor);
} elseif ($gQuery->IsAggrFuncField(GetFieldIndex($f) - 1)) {
$having = $searchClauseObj->getSuggestWhere($f, $fType, $suggestAllContent, $searchFor);
}
// prepare common vals
$sqlHead = "SELECT DISTINCT " . GetFullFieldName($f) . " ";
$oHaving = $gQuery->Having();
$sqlHaving = $oHaving->toSql($gQuery);
$sqlGroupBy = $gQuery->GroupByToSql();
$where = whereAdd($where, $strSecuritySql);
$strSQL = gSQLWhere_having($sqlHead, $gsqlFrom, $gsqlWhereExpr, $sqlGroupBy, $sqlHaving, $where, $having);
$strSQL .= " ORDER BY 1 ";
$rs = db_query($strSQL, $conn);
$i = 0;
while ($row = db_fetch_numarray($rs)) {
$i++;
$val = $row[0];
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 GetFieldByIndex($index, $table = "")
{
global $strTableName, $tables_data;
if (!$table) {
$table = $strTableName;
}
if (!array_key_exists($table, $tables_data)) {
return null;
}
foreach ($tables_data[$table] as $key => $value) {
if (!is_array($value) || !array_key_exists("Index", $value)) {
continue;
}
if ($value["Index"] == $index and GetFieldIndex($key)) {
return $key;
}
}
return null;
}
$returnJSON = array("success" => false, "error" => '');
echo my_json_encode($returnJSON);
return;
}
$id = postvalue("id");
$field = postvalue("field");
if (!CheckFieldPermissions($field)) {
$returnJSON = array("success" => false, "error" => 'Error: You have not permission for read this text');
echo my_json_encode($returnJSON);
return;
}
if (!$gQuery->HasGroupBy()) {
// Do not select any fields except current (full text) field.
// If query has 'group by' clause then other fields are used in it and we may not simply cut 'em off.
// Just don't do anything in that case.
$gQuery->RemoveAllFieldsExcept(GetFieldIndex($field));
}
$keysArr = GetTableData($strTableName, '.Keys', array());
$keys = array();
foreach ($keysArr as $ind => $k) {
$keys[$k] = postvalue("key" . ($ind + 1));
}
$where = KeyWhere($keys);
$secOpt = GetTableData($strTableName, '.nSecOptions', array());
if ($secOpt == ADVSECURITY_VIEW_OWN) {
$where = whereAdd($where, SecuritySQL("Search"));
}
$sql = gSQLWhere($where);
$rs = db_query($sql, $conn);
if (!$rs || !($data = db_fetch_array($rs))) {
$returnJSON = array("success" => false, "error" => 'Error: Wrong SQL query');
/**
* Builde order params
*/
function buildOrderParams()
{
//orderlinkattrs for fields
$this->orderLinksAttr();
$order_ind = -1;
//Array fields for sort
$this->arrFieldForSort = array();
//Array how fields sort
$this->arrHowFieldSort = array();
$arrImplicitSortFields = array();
$key = array();
//Session field for sort
if (@$_SESSION[$this->sessionPrefix . "_arrFieldForSort"]) {
$this->arrFieldForSort = $_SESSION[$this->sessionPrefix . "_arrFieldForSort"];
}
//Session how field sort
if (@$_SESSION[$this->sessionPrefix . "_arrHowFieldSort"]) {
$this->arrHowFieldSort = $_SESSION[$this->sessionPrefix . "_arrHowFieldSort"];
}
//Session key fields for sort
if (@$_SESSION[$this->sessionPrefix . "_key"]) {
$key = $_SESSION[$this->sessionPrefix . "_key"];
} else {
$tKeys = GetTableKeys($this->tName);
for ($i = 0; $i < count($tKeys); $i++) {
if (GetFieldIndex($tKeys[$i])) {
$key[] = GetFieldIndex($tKeys[$i]);
}
}
$_SESSION[$this->sessionPrefix . "_key"] = $key;
}
$lenkey = count($key);
if (!isset($_SESSION[$this->sessionPrefix . "_order"])) {
$this->arrFieldForSort = array();
$this->arrHowFieldSort = array();
if (count($this->gOrderIndexes)) {
for ($i = 0; $i < count($this->gOrderIndexes); $i++) {
$this->arrFieldForSort[] = $this->gOrderIndexes[$i][0];
$this->arrHowFieldSort[] = $this->gOrderIndexes[$i][1];
}
} elseif ($this->gstrOrderBy != '') {
$_SESSION[$this->sessionPrefix . "_noNextPrev"] = 1;
}
//add sorting on key fields
if (count($key) && $this->moveNext) {
for ($i = 0; $i < $lenkey; $i++) {
$idsearch = array_search($key[$i], $this->arrFieldForSort);
if ($idsearch === false) {
$this->arrFieldForSort[] = $key[$i];
$this->arrHowFieldSort[] = "ASC";
$arrImplicitSortFields[] = $key[$i];
}
}
}
}
$lenArr = count($this->arrFieldForSort);
//Sorting defined on the sheet
if (@$_SESSION[$this->sessionPrefix . "_orderby"]) {
$order_field = GetFieldByGoodFieldName(substr($_SESSION[$this->sessionPrefix . "_orderby"], 1));
$order_dir = substr($_SESSION[$this->sessionPrefix . "_orderby"], 0, 1);
$order_ind = GetFieldIndex($order_field);
if ($order_ind) {
if (!@$_REQUEST["a"] and !@$_REQUEST["goto"] and !@$_REQUEST["pagesize"]) {
if (@$_REQUEST["ctrl"]) {
$idsearch = array_search($order_ind, $this->arrFieldForSort);
if ($idsearch === false) {
if ($order_dir == "a") {
$this->arrFieldForSort[] = $order_ind;
$this->arrHowFieldSort[] = "ASC";
} else {
$this->arrFieldForSort[] = $order_ind;
$this->arrHowFieldSort[] = "DESC";
}
} else {
$this->arrHowFieldSort[$idsearch] = $order_dir == "a" ? "ASC" : "DESC";
}
} else {
$this->arrFieldForSort = array();
$this->arrHowFieldSort = array();
if (!empty($_SESSION[$this->sessionPrefix . "_orderNo"])) {
unset($_SESSION[$this->sessionPrefix . "_orderNo"]);
}
$_SESSION[$this->sessionPrefix . "_noNextPrev"] = 0;
if ($order_dir == "a") {
$this->arrFieldForSort[] = $order_ind;
$this->arrHowFieldSort[] = "ASC";
} else {
$this->arrFieldForSort[] = $order_ind;
$this->arrHowFieldSort[] = "DESC";
}
}
}
}
}
$lenArr = count($this->arrFieldForSort);
//Draw pictures of fields for sorting
$condition = true;
if (!count($this->arrFieldForSort)) {
$condition = false;
} elseif (!$this->arrFieldForSort[0]) {
$condition = false;
}
if ($condition) {
for ($i = 0; $i < $lenArr; $i++) {
$order_field = GetFieldByIndex($this->arrFieldForSort[$i]);
$order_dir = $this->arrHowFieldSort[$i] == "ASC" ? "a" : "d";
$idsearch = array_search($this->arrFieldForSort[$i], $arrImplicitSortFields);
if ($idsearch === false) {
$this->xt->assign_section(GoodFieldName($order_field) . "_fieldheader", "", "<img " . ($this->is508 == true ? "alt=\" \" " : "") . "src=\"images/" . ($order_dir == "a" ? "up" : "down") . ".gif\" border=0>");
}
// default ASC for key fields
if ($idsearch !== false && in_array(GetFieldIndex($order_field), $arrImplicitSortFields)) {
$orderlinkattrs = $this->setLinksAttr(GoodFieldName($order_field));
} else {
$orderlinkattrs = $this->setLinksAttr(GoodFieldName($order_field), $order_dir);
}
$this->xt->assign(GoodFieldName($order_field) . "_orderlinkattrs", $orderlinkattrs);
}
}
//Shape sorting line for a request
if ($lenArr > 0) {
for ($i = 0; $i < $lenArr; $i++) {
$this->strOrderBy .= GetFieldByIndex($this->arrFieldForSort[$i]) ? ($this->strOrderBy != "" ? ", " : " ORDER BY ") . $this->arrFieldForSort[$i] . " " . $this->arrHowFieldSort[$i] : "";
}
}
if ($_SESSION[$this->sessionPrefix . "_noNextPrev"] == 1) {
$this->strOrderBy = $this->gstrOrderBy;
}
}
function SQLStatement($sql, $order, $pagesize, $connection, &$searchClauseObj, &$params)
{
// copy properties to object
RunnerApply($this, $params);
$this->searchClauseObj = $searchClauseObj;
if (!is_array($sql)) {
die('Invalid sql parameter');
}
global $reportCaseSensitiveGroupFields;
$this->_originalSql = $sql;
$start = 0;
$fields = array();
for ($i = 0; $i < count($this->repGroupFields); $i++) {
for ($j = 0; $j < count($this->fieldsArr); $j++) {
if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) {
$add = array();
$add['name'] = $this->fieldsArr[$j]['name'];
if (IsNumberType(GetFieldType($this->fieldsArr[$j]['name'], $this->tName))) {
$add['type'] = 'numeric';
} elseif (IsCharType(GetFieldType($this->fieldsArr[$j]['name'], $this->tName))) {
$add['type'] = 'char';
$add['case_sensitive'] = $reportCaseSensitiveGroupFields;
} elseif (IsDateFieldType(GetFieldType($this->fieldsArr[$j]['name'], $this->tName))) {
$add['type'] = 'date';
} else {
$add['type'] = 'char';
}
$add['interval'] = $this->repGroupFields[$i]['groupInterval'];
$add['viewformat'] = $this->fieldsArr[$j]['viewFormat'];
$add['rowsinsummary'] = 1;
if ($this->fieldsArr[$j]['totalMax'] || $this->fieldsArr[$j]['totalMin'] || $this->fieldsArr[$j]['totalAvg'] || $this->fieldsArr[$j]['totalSum']) {
$add['rowsinsummary']++;
}
if ($this->repLayout == REPORT_STEPPED) {
$add['rowsinheader'] = 1;
} elseif ($this->repLayout == REPORT_BLOCK) {
$add['rowsinheader'] = 0;
} elseif ($this->repLayout == REPORT_OUTLINE || $this->repLayout == REPORT_ALIGN) {
if ($j == count($this->fieldsArr) - 1) {
$add['rowsinheader'] = 2;
} else {
$add['rowsinheader'] = 1;
}
} elseif ($this->repLayout == REPORT_TABULAR) {
$add['rowsinheader'] = 0;
}
$fields[] = $add;
}
}
}
$this->_hasGroups = count($fields) > 0;
foreach ($fields as $field) {
$f = create_reportfield($field['name'], $field['type'], $field['interval'], 'grp');
$start = $f->setStart($start);
if (isset($field['case_sensitive'])) {
$f->setCaseSensitive($field['case_sensitive']);
}
if (isset($field['rowsinsummary'])) {
$f->_rowsInSummary = $field['rowsinsummary'];
}
if (isset($field['rowsinheader'])) {
$f->_rowsInHeader = $field['rowsinheader'];
}
$f->_viewFormat = $field['viewformat'];
$this->_fields[] = $f;
}
// order
if ($order) {
$order_in = array();
$order_out = array();
$order_old = array();
foreach ($order as $o) {
$order_in[] = $o[2] . ' as ' . cached_ffn('originalorder' . $o[0]);
$order_out[] = cached_ffn('originalorder' . $o[0]) . ' ' . $o[1];
$groupField = false;
for ($i = 0; $i < count($this->repGroupFields); $i++) {
for ($j = 0; $j < count($this->fieldsArr); $j++) {
if ($this->repGroupFields[$i]['strGroupField'] == $this->fieldsArr[$j]['name']) {
$fieldIndex = GetFieldIndex($this->repGroupFields[$i]['strGroupField']);
if ($fieldIndex == $o[0]) {
$n = $this->repGroupFields[$i]['groupOrder'] - 1;
$this->_fields[$n]->_orderBy = $o[1];
$groupField = true;
}
}
}
}
// don't add group fields to the $order_old
if (!$groupField) {
$order_old[] = $o[2] . ' ' . $o[1];
}
}
$this->_order_in = join(', ', $order_in);
$this->_order_out = join(', ', $order_out);
$this->_order_old = join(', ', $order_old);
}
for ($i = 0; $i < count($this->fieldsArr); $i++) {
if ($this->fieldsArr[$i]['totalMax']) {
$this->_aggregates[] = 'MAX(' . cached_ffn($this->fieldsArr[$i]['name']) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MAX");
}
if ($this->fieldsArr[$i]['totalMin']) {
$this->_aggregates[] = 'MIN(' . cached_ffn($this->fieldsArr[$i]['name']) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "MIN");
}
if ($this->fieldsArr[$i]['totalAvg']) {
if (!IsDateFieldType(GetFieldType($this->fieldsArr[$i]['name'], $this->tName))) {
$this->_aggregates[] = 'AVG(' . cached_ffn($this->fieldsArr[$i]['name']) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "AVG");
$this->_aggregates[] = 'COUNT(' . cached_ffn($this->fieldsArr[$i]['name']) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "NAVG");
}
}
if ($this->fieldsArr[$i]['totalSum']) {
if (!IsDateFieldType(GetFieldType($this->fieldsArr[$i]['name'], $this->tName))) {
$this->_aggregates[] = 'SUM(' . cached_ffn($this->fieldsArr[$i]['name']) . ') as ' . cached_ffn($this->fieldsArr[$i]['name'] . "SUM");
}
}
}
$this->_reportSummary = $this->repPageSummary || $this->repGlobalSummary;
$this->_pagesize = $pagesize;
}
