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 buildSQL()
{
$this->buildLookupWhereClause();
if ($this->dispFieldAlias) {
$this->gsqlHead .= ", " . $this->dispField . " ";
$this->gsqlHead .= "as " . AddFieldWrappers($this->dispFieldAlias) . " ";
}
parent::buildSQL();
}
function InsertRecord($arr, $recInd)
{
global $goodlines, $conn, $error_message, $keys_present, $keys, $strOriginalTableName, $strTableName, $eventObj, $locale_info, $auditObj;
$ret = 1;
$rawvalues = array();
foreach ($arr as $key => $val) {
$rawvalues[$key] = $val;
$type = GetFieldType($key);
if (!NeedQuotes($type)) {
$value = (string) $val;
$value = str_replace(",", ".", $value);
if (strlen($value) > 0) {
$value = str_replace($locale_info["LOCALE_SCURRENCY"], "", $value);
$arr[$key] = 0 + $value;
} else {
$arr[$key] = NULL;
}
}
}
$retval = true;
if ($eventObj->exists('BeforeInsert')) {
$retval = $eventObj->BeforeInsert($rawvalues, $arr);
}
if ($retval) {
$fields = array_keys($arr);
foreach ($fields as $key => $val) {
$fields_list[$key] = AddFieldWrappers(GetFullFieldName($val));
}
$values_list = "";
foreach ($arr as $key => $val) {
if (!is_null($arr[$key])) {
$values_list .= add_db_quotes($key, $val) . ", ";
} else {
$values_list .= "NULL, ";
}
}
if (strlen($values_list) > 0) {
$values_list = substr($values_list, 0, strlen($values_list) - 2);
}
$sql = "insert into " . AddTableWrappers($strOriginalTableName) . " (" . implode(",", $fields_list) . ") values (" . $values_list . ")";
if (db_exec_import($sql, $conn)) {
$goodlines++;
if ($auditObj) {
$aKeys = GetKeysArray($arr, true);
$auditObj->LogAdd($strTableName, $arr, $aKeys);
}
} else {
$temp_error_message = "<b>Error:</b> in the line: " . implode(",", $arr) . ' <a linkType="debugOpener" recId="' . $recInd . '" href="" onclick="importMore(' . $recInd . ');">More info</a><br>';
$temp_error_message .= '<div id="importDebugInfoTable' . $recInd . '" cellpadding="3" cellspacing="1" align="center" style="display: none;"><p class="error">SQL query: ' . $sql . '; </p><p class="error">DB error: ' . db_error($conn) . ';</p></div>';
$temp_error_message .= "<br><br>";
// we'll try to update the record
if ($keys_present) {
$sql = "update " . AddTableWrappers($strOriginalTableName) . " set ";
$sqlset = "";
$where = " where ";
foreach ($fields as $k => $val) {
if (!in_array(AddFieldWrappers($fields[$k]), $keys)) {
if (!is_null($arr[$val])) {
$sqlset .= $fields_list[$k] . "=" . add_db_quotes($val, $arr[$val]) . ", ";
} else {
$sqlset .= $fields_list[$k] . "=NULL, ";
}
} else {
$where .= $fields_list[$k] . "=" . add_db_quotes($val, $arr[$val]) . " and ";
}
}
if (strlen($sqlset) > 0) {
$sql .= substr($sqlset, 0, strlen($sqlset) - 2);
}
$where = substr($where, 0, strlen($where) - 5);
$sql .= " " . $where;
$rstmp = db_query("select * from " . AddTableWrappers($strOriginalTableName) . " " . $where, $conn);
$data = db_fetch_array($rstmp);
if ($data) {
if ($auditObj) {
foreach ($data as $key => $val) {
$auditOldValues[$key] = $val;
}
}
if (db_exec_import($sql, $conn)) {
// update successfull
$goodlines++;
if ($auditObj) {
$aKeys = GetKeysArray($arr);
$auditObj->LogEdit($strTableName, $arr, $auditOldValues, $aKeys);
}
} else {
echo 'not updated';
// update not successfull
$error_message .= $temp_error_message;
$ret = 0;
}
} else {
$error_message .= $temp_error_message;
$ret = 0;
}
} else {
$error_message .= $temp_error_message;
}
}
return $ret;
}
}
$strSQLbak = $strSQL;
if (is_wr_db()) {
$strSQL = $rpt_array['sql'] . $rpt_array['where'] . $rpt_array['order_by'];
} elseif (is_wr_project()) {
$strSQL = $gQuery->gSQLWhere($strWhereClause);
$strSQL .= " " . trim($strOrderBy);
} elseif (is_wr_custom()) {
if (GetDatabaseType() != 1) {
$sqlquery = $rpt_array['sql'];
if (GetDatabaseType() == 2) { //MSSQLServer
$pos = strrpos(strtoupper($sqlquery), "ORDER BY");
if ($pos)
$sqlquery = substr($sqlquery, 0, $pos);
}
$strSQL = "select * from (" . $sqlquery . ") as " . AddFieldWrappers("t") . " " . $rpt_array['where'] . $rpt_array['order_by'];
} else
$strSQL = "select * from (" . $rpt_array['sql'] . ")" . $rpt_array['where'] . $rpt_array['order_by'];
}
$grid_row = array();
if ($cross_table != "true") {
$groupno = 0;
if (!$_SESSION[$sessPrefix . "_pagenumber"]) {
$_SESSION[$sessPrefix . "_pagenumber"] = 1;
}
if (!$_SESSION[$sessPrefix . "_pagesize"]) {
if ((count($rpt_array['group_fields']) - 1 ) > 0) {
$_SESSION[$sessPrefix . "_pagesize"] = 5;
} else {
$_SESSION[$sessPrefix . "_pagesize"] = 20;
function LoginAccess()
{
if ($this->attLogin > 0 && $this->timeLogin > 0) {
$rstmp = $this->TableObj->Query(AddFieldWrappers("ip") . "='" . $_SERVER["REMOTE_ADDR"] . "' and " . AddFieldWrappers("action") . "='access'", AddFieldWrappers("id") . " asc");
$i = 0;
while ($data = db_fetch_array($rstmp)) {
if (secondsPassedFrom($data["datetime"]) / 60 <= $this->timeLogin) {
if ($i == 0) {
$firstAccess = $data["datetime"];
}
$i += 1;
}
}
if ($i >= $this->attLogin) {
return ceil($this->timeLogin - secondsPassedFrom($firstAccess) / 60);
} else {
return false;
}
} else {
return false;
}
}
function AddTableWrappers($strName)
{
return AddFieldWrappers($strName);
}
function buildLookupSQL($pageType, $field, $table, $parentVal, $childVal = "", $doCategoryFilter = true, $doValueFilter = false, $addCategoryField = false, $doWhereFilter = true, $oneRecordMode = false, $doValueFilterByLinkField = false)
{
global $strTableName;
if (!strlen($table)) {
$table = $strTableName;
}
$pSet = new ProjectSettings($table, $pageType);
// read settings
$nLookupType = $pSet->getLookupType($field);
if ($nLookupType != LT_LOOKUPTABLE && $nLookupType != LT_QUERY) {
return "";
}
$lookupTable = $pSet->getLookupTable($field);
$displayFieldName = $pSet->getDisplayField($field);
$linkFieldName = $pSet->getLinkField($field);
$linkAndDisplaySame = $displayFieldName == $linkFieldName;
$bUnique = $pSet->isLookupUnique($field);
$strLookupWhere = GetLWWhere($field, $pageType, $table);
$strOrderBy = $pSet->getLookupOrderBy($field);
if (strlen($strOrderBy)) {
$strOrderBy = GetFullFieldName($strOrderBy, $lookupTable);
if ($pSet->isLookupDesc($field)) {
$strOrderBy .= ' DESC';
}
}
$bDesc = $pSet->isLookupDesc($field);
$strCategoryFilter = $pSet->getCategoryFilter($field);
if ($nLookupType == LT_QUERY) {
$lookupPSet = new ProjectSettings($lookupTable, $pageType);
$cipherer = new RunnerCipherer($lookupTable, $lookupPSet);
} else {
$cipherer = new RunnerCipherer($table, $pSet);
}
if ($doCategoryFilter) {
if ($nLookupType == LT_QUERY) {
$parentVal = $cipherer->MakeDBValue($strCategoryFilter, $parentVal, "", $lookupTable, true);
} else {
$parentVal = make_db_value($pSet->getCategoryControl($field), $parentVal, '', '', $table);
}
}
if ($doValueFilter) {
if ($pageType != PAGE_SEARCH || $doValueFilterByLinkField) {
if ($nLookupType == LT_QUERY) {
$childWhereField = $pSet->getLWLinkField($field, false);
} else {
$childWhereField = $pSet->getLWLinkField($field, true);
}
} else {
if ($nLookupType == LT_QUERY) {
$childWhereField = $pSet->getLWDisplayField($field, false);
} else {
$childWhereField = $pSet->getLWDisplayField($field, true);
}
}
if ($nLookupType == LT_QUERY) {
$childVal = $cipherer->MakeDBValue($childWhereField, $childVal, "", $lookupTable, true);
} else {
if ($linkAndDisplaySame) {
$childVal = make_db_value($field, $childVal, '', '', $table);
} else {
$childVal = add_db_quotes($field, $childVal, $table, 200);
}
}
}
// build Where clause
$categoryWhere = "";
$childWhere = "";
if ($pSet->useCategory($field) && $doCategoryFilter) {
$condition = "=" . $parentVal;
if ($parentVal === "null") {
$condition = " is null";
}
if ($nLookupType == LT_QUERY) {
$categoryWhere = $cipherer->GetFieldName(AddFieldWrappers($strCategoryFilter), $strCategoryFilter) . $condition;
} else {
$categoryWhere = AddFieldWrappers($strCategoryFilter) . $condition;
}
}
if ($doValueFilter) {
$condition = "=" . $childVal;
if ($childVal === "null") {
$condition = " is null";
}
if ($nLookupType == LT_QUERY) {
if ($pageType != PAGE_SEARCH || $pSet->lookupControlType($field) == LCT_LIST || $doValueFilterByLinkField) {
$childWhere = GetFullFieldName($pSet->getLinkField($field), $lookupTable, false) . $condition;
} else {
if (!$pSet->getCustomDisplay($field)) {
$childWhere = $cipherer->GetFieldName($lookupPSet->getFullNameField($displayFieldName), $field) . $condition;
} else {
$childWhere = $pSet->getDisplayField($field) . $condition;
}
}
} else {
if ($pageType != PAGE_SEARCH || $doValueFilterByLinkField) {
$childWhere = $pSet->getLWLinkField($field, true) . $condition;
} else {
$childWhere = $pSet->getLWDisplayField($field, true) . $condition;
}
}
}
$strWhere = "";
if ($doWhereFilter && strlen($strLookupWhere)) {
$strWhere = "(" . $strLookupWhere . ")";
}
if (strlen($categoryWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $categoryWhere;
}
if (strlen($childWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $childWhere;
}
// build SQL string
if ($nLookupType == LT_QUERY) {
$lookupQueryObj = $lookupPSet->getSQLQuery();
if ($pSet->getCustomDisplay($field)) {
$lookupQueryObj->AddCustomExpression($displayFieldName, $lookupPSet, $table, $field);
}
$lookupQueryObj->ReplaceFieldsWithDummies($lookupPSet->getBinaryFieldsIndices());
$strWhere = whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $strWhere);
$LookupSQL = $lookupQueryObj->toSql($strWhere, strlen($strOrderBy) ? ' ORDER BY ' . $strOrderBy : null, null, $oneRecordMode);
} else {
$LookupSQL = "SELECT ";
if ($bUnique && !$oneRecordMode) {
$LookupSQL .= "DISTINCT ";
}
$LookupSQL .= $pSet->getLWLinkField($field);
if (!$linkAndDisplaySame) {
$LookupSQL .= "," . $pSet->getLWDisplayField($field);
}
if ($addCategoryField && strlen($strCategoryFilter)) {
$LookupSQL .= "," . AddFieldWrappers($strCategoryFilter);
}
$LookupSQL .= " FROM " . AddTableWrappers($lookupTable);
if (strlen($strWhere)) {
$LookupSQL .= " WHERE " . $strWhere;
}
// order by clause
if (strlen($strOrderBy)) {
$LookupSQL .= " ORDER BY " . AddTableWrappers($lookupTable) . "." . $strOrderBy;
}
if ($oneRecordMode) {
$LookupSQL .= " limit 1";
}
}
return $LookupSQL;
}
function toSql($query, $first)
{
$ret = '';
if(is_a($this->m_table, "SQLTable"))
{
$ret .= $this->m_table->toSql($query);
}
else
{
if($this->m_table)
{
if(is_a($this->m_table, 'SQLQuery'))
{
return $this->m_sql;
}
else
{
$ret .= '(' . $this->m_table->toSql($query) . ')';
}
}
}
if($this->m_alias != '')
{
$ret .= ' ' . AddFieldWrappers($this->m_alias);
}
if($this->m_link == 'SQLL_MAIN')
{
return $ret;
}
switch($this->m_link)
{
case 'SQLL_INNERJOIN':
$ret = ' INNER JOIN ' . $ret;
break;
case 'SQLL_NATURALJOIN':
$ret = ' NATURAL JOIN ' . $ret;
break;
case 'SQLL_LEFTJOIN':
$ret = ' LEFT OUTER JOIN ' . $ret;
break;
case 'SQLL_RIGHTJOIN':
$ret = ' RIGHT OUTER JOIN ' . $ret;
break;
case 'SQLL_FULLOUTERJOIN':
$ret = ' FULL OUTER JOIN ' . $ret;
break;
case 'SQLL_CROSSJOIN':
$ret = (!$first ? ',' : '') . $ret;
break;
}
$joinStr = $this->m_joinon->toSql($query);
if($joinStr != '')
{
$ret .= ' ON ' . $joinStr;
}
return $ret;
}
if(!$show)
exit();
// security - end
// construct sql
$keys = DBGetTableKeys($table);
if(!count($keys))
exit();
$strkeywhere = "";
foreach($keys as $idx=>$k)
{
if(strlen($strkeywhere))
$strkeywhere.=" and ";
$strkeywhere.=AddTableWrappers($table).".".AddFieldWrappers($k)."=";
$type=WRGetFieldType($table.".".$k);
if(NeedQuotes($type))
$strkeywhere.=db_prepare_string(postvalue("key".($idx+1)));
else
{
$value=postvalue("key".($idx+1));
$strvalue = (string)$value;
$strvalue = str_replace(",",".",$strvalue);
if(is_numeric($strvalue))
$value=$strvalue;
else
$value=0;
$strkeywhere.=$value;
}
}
function buildLookupSQL($field, $table, $parentVal, $childVal = "", $doCategoryFilter = true, $doValueFilter = false, $addCategoryField = false, $doWhereFilter = true, $oneRecordMode = false)
{
global $strTableName;
if (!strlen($table)) {
$table = $strTableName;
}
// read settings
$nLookupType = GetFieldData($table, $field, "LookupType", LT_LISTOFVALUES);
if ($nLookupType != LT_LOOKUPTABLE) {
return "";
}
$bUnique = GetFieldData($table, $field, "LookupUnique", false);
$strLookupWhere = LookupWhere($field, $table);
$strOrderBy = GetFieldData($table, $field, "LookupOrderBy", "");
$bDesc = GetFieldData($table, $field, "LookupDesc", false);
$strCategoryFilter = GetFieldData($table, $field, "CategoryFilter", "");
if ($doCategoryFilter) {
$parentVal = make_db_value(CategoryControl($field, $table), $parentVal);
}
if ($doValueFilter) {
$childVal = make_db_value($field, $childVal);
}
// build SQL string
$LookupSQL = "SELECT ";
if ($oneRecordMode) {
$LookupSQL .= "top 1 ";
}
if ($bUnique) {
$LookupSQL .= "DISTINCT ";
}
$LookupSQL .= GetLWLinkField($field, $table);
$LookupSQL .= "," . GetLWDisplayField($field, $table);
if ($addCategoryField && strlen($strCategoryFilter)) {
$LookupSQL .= "," . AddFieldWrappers($strCategoryFilter);
}
$LookupSQL .= " FROM " . AddTableWrappers(GetLookupTable($field, $table));
// build Where clause
$categoryWhere = "";
$childWhere = "";
if (UseCategory($field, $table) && $doCategoryFilter) {
$condition = "=" . $parentVal;
if ($childVal === "null") {
$condition = " is null";
}
$categoryWhere = AddFieldWrappers($strCategoryFilter) . $condition;
}
if ($doValueFilter) {
$condition = "=" . $childVal;
if ($childVal === "null") {
$condition = " is null";
}
$childWhere = AddFieldWrappers(GetLWLinkField($field, $table)) . $condition;
}
$strWhere = "";
if ($doWhereFilter && strlen($strLookupWhere)) {
$strWhere = "(" . $strLookupWhere . ")";
}
if (strlen($categoryWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $categoryWhere;
}
if (strlen($childWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $childWhere;
}
if (strlen($strWhere)) {
$LookupSQL .= " WHERE " . $strWhere;
}
// order by clause
if (strlen($strOrderBy)) {
$LookupSQL .= " ORDER BY " . AddTableWrappers(GetLookupTable($field, $table)) . "." . AddFieldWrappers($strOrderBy);
if ($bDesc) {
$LookupSQL .= " DESC";
}
}
return $LookupSQL;
}
/**
* Get an SQL expression retriving the encrypted field's value
* Please note, when changing this function you should make appropriate changes in wizard method (dynamic permissions --> add new user) #8923
* @param {string} field
* @param {string} alias
* @param {string} addAs
* @return {string}
*/
function GetEncryptedFieldName($field, $alias = null, $addAs = false)
{
$result = "";
$result = "cast(DES_DECRYPT(unhex(%s), '%s') as char)";
if($result == "")
return $field;
$result = mysprintf($result, array($field, $this->key));
return $addAs ? $result." as ".AddFieldWrappers($alias != null ? $alias : $field) : $result;
}
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
if ($this->lookupType == LT_LISTOFVALUES) {
return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest);
}
$baseResult = $this->baseSQLWhere($strSearchOption);
if ($baseResult === false) {
return "";
}
if ($baseResult != "") {
return $baseResult;
}
$displayFieldType = $this->type;
if ($this->lookupType == LT_QUERY) {
$displayFieldType = $this->lookupPSet->getFieldType($this->field);
$this->btexttype = IsTextType($displayFieldType);
}
if ($this->multiselect) {
$SearchFor = splitvalues($SearchFor);
} else {
$SearchFor = array($SearchFor);
}
$ret = "";
if ($this->linkAndDisplaySame) {
$gstrField = GetFullFieldName($this->field, "", false);
} else {
$gstrField = GetFullFieldName($this->displayFieldName, $this->lookupTable, false);
}
if ($this->customDisplay) {
$gstrField = $this->lwDisplayFieldWrapped;
} else {
if (!$this->linkAndDisplaySame && $this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype && !$this->ciphererDisplay->isFieldPHPEncrypted($this->displayFieldName)) {
$gstrField = $this->lookupPSet->isEnableUpper(GetFullFieldName($this->displayFieldName, $this->lookupTable, false));
}
}
foreach ($SearchFor as $value) {
if (!($value == "null" || $value == "Null" || $value == "")) {
if (strlen(trim($ret))) {
$ret .= " or ";
}
if (!$this->multiselect) {
if ($strSearchOption == "Starts with") {
$value .= '%';
}
if ($isSuggest || $strSearchOption == "Contains") {
$value = '%' . $value . '%';
}
if ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between" || $strSearchOption == "Equals" && $this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) {
$value = $this->escapeSearchValForMySQL($value);
if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) {
$value = $this->lookupPSet->isEnableUpper(db_prepare_string($value));
} else {
$value = db_prepare_string($value);
}
} else {
if ($strSearchOption == "Equals") {
$value = make_db_value($this->field, $value);
}
}
}
if ($strSearchOption == "Equals") {
if (!($value == "null" || $value == "Null")) {
if ($this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) {
$condition = $gstrField . '=' . $value;
} else {
$condition = GetFullFieldName($this->field, "", false) . '=' . $value;
}
}
} else {
if ($strSearchOption == "Starts with" || $strSearchOption == "Contains" && !$this->multiselect) {
$condition = $gstrField . " " . $this->like . " " . $value;
} else {
if ($strSearchOption == "More than") {
$condition = $gstrField . " > " . $value;
} else {
if ($strSearchOption == "Less than") {
$condition = $gstrField . "<" . $value;
} else {
if ($strSearchOption == "Equal or more than") {
$condition = $gstrField . ">=" . $value1;
} else {
if ($strSearchOption == "Equal or less than") {
$condition = $gstrField . "<=" . $value1;
} else {
if ($strSearchOption == "Between") {
if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) {
$value2 = $this->lookupPSet->isEnableUpper(db_prepare_string($SearchFor2));
} else {
$value2 = db_prepare_string($SearchFor2);
}
$condition = $gstrField . ">=" . $value . " and ";
if (IsDateFieldType($this->type)) {
$timeArr = db2time($SearchFor2);
// for dates without time, add one day
if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) {
$timeArr = adddays($timeArr, 1);
$SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
$SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->pageObject->tName);
$condition .= $gstrField . "<" . $SearchFor2;
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
if (strpos($value, ",") !== false || strpos($value, '"') !== false) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
$value = $this->escapeSearchValForMySQL($value);
//for search by multiply Lookup wizard field
$ret .= GetFullFieldName($this->field, "", false) . " = " . db_prepare_string($value);
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value . ",%");
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value);
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string($value . ",%");
}
}
}
}
}
}
}
if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) {
if ($this->linkAndDisplaySame || $strSearchOption == "Equals" && $this->LCType != LCT_AJAX) {
$ret .= " " . $condition;
} else {
if ($this->lookupType == LT_QUERY) {
$lookupQueryObj = $this->lookupPSet->getSQLQuery();
$ret .= " EXISTS (" . $lookupQueryObj->toSql($condition . " and " . GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field), '', null, false) . ")";
} else {
$ret .= " EXISTS (SELECT 1 as fld from " . AddTableWrappers($this->lookupTable) . " where " . $condition . " and " . $this->lwLinkField . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field) . ")";
}
}
}
}
}
if (strlen(trim($ret))) {
$ret = "(" . $ret . ")";
} else {
$ret = trim($ret);
}
return $ret;
}
if ($strLookupWhere) {
$strLookupWhere = " (" . $strLookupWhere . ") AND ";
}
if ($LookupType == LT_QUERY) {
if ($gSettings->getCustomDisplay($f)) {
$strLookupWhere .= $displayFieldName;
} else {
$strLookupWhere .= GetFullFieldName($displayFieldName, $lookupTable, false);
}
} else {
$strLookupWhere .= $cipherer->GetFieldName($lwDisplayField, $f);
}
$strLookupWhere .= $cipherer->GetLikeClause($LookupType == LT_QUERY ? $displayFieldName : $f, $value);
if ($gSettings->useCategory($f) && (postvalue("category") != '' || postvalue('editMode') != MODE_SEARCH)) {
$cvalue = make_db_value($gSettings->getCategoryControl($f), postvalue("category"));
$strLookupWhere .= " AND " . AddFieldWrappers($gSettings->getCategoryFilter($f)) . "=" . $cvalue;
}
$lookupOrderBy = $gSettings->getLookupOrderBy($f);
if (strlen($lookupOrderBy)) {
$lookupOrderBy = GetFullFieldName($lookupOrderBy, $lookupTable);
if ($gSettings->isLookupDesc($f)) {
$lookupOrderBy .= ' DESC';
}
}
if ($LookupType == LT_QUERY) {
$LookupSQL = $lookupQueryObj->toSql(whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $strLookupWhere), strlen($lookupOrderBy) ? ' ORDER BY ' . $lookupOrderBy : null);
} else {
$LookupSQL = $LookupSQLTable . " where " . $strLookupWhere;
if (!$gSettings->isLookupUnique($f) || nDATABASE_Access != 4) {
if ($lookupOrderBy) {
$LookupSQL .= " ORDER BY " . $lookupOrderBy;
function Chart(&$ch_array, $param)
{
global $field_labels;
if ($this->webchart) {
$this->chrt_array = Convert_Old_Chart($ch_array);
} else {
$this->chrt_array = $ch_array;
}
$this->pSet = new ProjectSettings($this->chrt_array['tables'][0]);
$this->chrt_array["appearance"]["autoupdate"] = false;
$this->numRecordsToShow = $this->chrt_array['appearance']['maxbarscroll'];
if ($this->numRecordsToShow < 1) {
$this->numRecordsToShow = 1;
}
$this->table_type = $this->chrt_array["table_type"];
if (!$this->table_type) {
$this->table_type = "project";
}
$this->webchart = $param["webchart"];
$this->cname = $param["cname"];
$this->sessionPrefix = $this->chrt_array['tables'][0];
$this->gstrOrderBy = $param["gstrOrderBy"];
$TableName = GoodFieldName($this->chrt_array['tables'][0]);
$this->cipherer = new RunnerCipherer($this->chrt_array['tables'][0]);
$this->header = $this->chrt_array['appearance']['head'];
$this->footer = $this->chrt_array['appearance']['foot'];
$this->y_axis_label = $this->chrt_array['appearance']['y_axis_label'];
for ($i = 0; $i < count($this->chrt_array['parameters']) - 1; $i++) {
if (isset($this->chrt_array['parameters'][$i]['currencyFormat'])) {
$this->arrFormatCurrency[] = $this->chrt_array['parameters'][$i]['currencyFormat'];
} else {
if ($this->chrt_array['appearance']['scur'] == "false") {
$this->arrFormatCurrency[] = "";
} else {
$this->arrFormatCurrency[] = $this->chrt_array['appearance']['scur'];
}
}
if (isset($this->chrt_array['parameters'][$i]['decimalFormat'])) {
$this->arrFormatDecimal[] = $this->chrt_array['parameters'][$i]['decimalFormat'];
} else {
$this->arrFormatDecimal[] = $this->chrt_array['appearance']['dec'];
}
$this->arrFormatCustomer[] = $this->chrt_array['parameters'][$i]['customFormat'];
$this->arrFormatCustomerStr[] = $this->chrt_array['parameters'][$i]['customFormatStr'];
if ($this->chrt_array["chart_type"]["type"] == "ohlc" || $this->chrt_array["chart_type"]["type"] == "candlestick") {
$this->arrOHLC_open[] = $this->chrt_array['parameters'][$i]['ohlcOpen'];
$this->arrOHLC_high[] = $this->chrt_array['parameters'][$i]['ohlcHigh'];
$this->arrOHLC_low[] = $this->chrt_array['parameters'][$i]['ohlcLow'];
$this->arrOHLC_close[] = $this->chrt_array['parameters'][$i]['ohlcClose'];
$this->arrOHLC_color[] = "#" . $this->chrt_array['parameters'][$i]['ohlcColor'];
if ($this->chrt_array["chart_type"]["type"] == "candlestick") {
$this->arrOHLC_candle[] = "#" . $this->chrt_array['parameters'][$i]['ohlcCandleColor'];
}
} elseif ($this->chrt_array['parameters'][$i]['name'] != "") {
if ($this->table_type != "db") {
$this->arrDataSeries[] = $this->chrt_array['parameters'][$i]['agr_func'] ? $this->chrt_array['parameters'][$i]['label'] : $this->chrt_array['parameters'][$i]['name'];
} else {
$this->arrDataSeries[] = $this->chrt_array['parameters'][$i]['agr_func'] ? $this->chrt_array['parameters'][$i]['table'] . "_" . $this->chrt_array['parameters'][$i]['label'] : $this->chrt_array['parameters'][$i]['table'] . "_" . $this->chrt_array['parameters'][$i]['name'];
}
if (isset($this->chrt_array['appearance']['scolor' . ($i + 1) . '1'])) {
$this->arrDataColor[] = $this->chrt_array['appearance']['scolor' . ($i + 1) . '1'];
} else {
$this->arrDataColor[] = $this->chrt_array['parameters'][$i]['series_color'];
}
if ($this->chrt_array["chart_type"]["type"] == "bubble") {
$this->arrDataSize[] = $this->chrt_array['parameters'][$i]['size'];
}
if ($this->chrt_array["chart_type"]["type"] == "gauge") {
for ($k = 0; is_array($this->chrt_array["parameters"][$i]["gaugeColorZone"]) && $k < count($this->chrt_array["parameters"][$i]["gaugeColorZone"]); $k++) {
$beginColor = (double) @$this->chrt_array["parameters"][$i]["gaugeColorZone"][$k]["gaugeBeginColor"];
$endColor = (double) @$this->chrt_array["parameters"][$i]["gaugeColorZone"][$k]["gaugeEndColor"];
$gColor = "#" . @$this->chrt_array["parameters"][$i]["gaugeColorZone"][$k]["gaugeColor"];
$this->arrGaugeColor[count($this->arrDataSeries) - 1][] = array($beginColor, $endColor, $gColor);
}
}
}
if ($this->table_type == "project" && !$this->webchart) {
$this->arrDataLabels[] = $this->chart_xmlencode(GetFieldLabel(GoodFieldName($TableName), GoodFieldName($this->chrt_array['parameters'][$i]['name'])));
} else {
if (!$this->chart_xmlencode($this->chrt_array['parameters'][$i]['label'])) {
$this->arrDataLabels[] = $this->chart_xmlencode($this->chrt_array['parameters'][$i]['name']);
} else {
$this->arrDataLabels[] = $this->chart_xmlencode($this->chrt_array['parameters'][$i]['label']);
}
}
}
if ($this->chrt_array["chart_type"]["type"] != "gauge") {
if ($this->table_type != "db") {
$this->strLabel = $this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['name'];
} else {
$this->strLabel = $this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['table'] . "_" . $this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['name'];
}
for ($j = 0; $j < count($this->chrt_array['fields']); $j++) {
if ($this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['name'] == $this->chrt_array['fields'][$j]['name']) {
if ($this->table_type == "project") {
$this->label2 = $this->chart_xmlencode(GetFieldLabel($TableName, GoodFieldName($this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['name'])));
} else {
$this->label2 = $this->chart_xmlencode($this->chrt_array['parameters'][count($this->chrt_array['parameters']) - 1]['name']);
}
}
}
}
if ($this->chrt_array["chart_type"]["type"] != "ohlc" && $this->chrt_array["chart_type"]["type"] != "candlestick") {
foreach ($this->arrDataColor as $ind => $val) {
if ($ind == 0) {
$this->arrAxesColor = "#000000";
} else {
$this->arrAxesColor = "#" . $this->arrDataColor[$ind];
}
}
} else {
foreach ($this->arrOHLC_color as $ind => $val) {
if ($ind == 0) {
$this->arrAxesColor = "#000000";
} else {
$this->arrAxesColor = "#" . $this->arrOHLC_color[$ind];
}
}
}
// prepare search params
$gQuery = $this->pSet->getSQLQuery();
$strWhereClause = "";
$searchHavingClause = "";
$strSearchCriteria = "and";
global $strTableName;
// search where for basic charts
if (!$this->webchart) {
if (isset($_SESSION[$this->sessionPrefix . '_advsearch'])) {
$searchClauseObj = unserialize($_SESSION[$this->sessionPrefix . '_advsearch']);
include_once getabspath('classes/controls/EditControlsContainer.php');
$editControls = new EditControlsContainer(null, $this->pSet, PAGE_REPORT, $this->cipherer);
$strWhereClause = $searchClauseObj->getWhere($this->pSet->getListOfFieldsByExprType(false), $editControls);
$searchHavingClause = $searchClauseObj->getWhere($this->pSet->getListOfFieldsByExprType(true), $editControls);
$strSearchCriteria = @$_SESSION[$strTableName . "_criteria"];
if ($searchClauseObj->isUsedSearchFor && !$searchClauseObj->isUsedFieldsForSearch) {
$strSearchCriteria = "or";
}
}
} else {
if ($this->table_type != "project") {
$strTableName = "webchart" . $this->cname;
}
$strWhereClause = CalcSearchParam($this->table_type != "project");
}
if ($strWhereClause) {
$this->chrt_array['where'] .= $this->chrt_array['where'] ? " AND (" . $strWhereClause . ")" : " WHERE (" . $strWhereClause . ")";
}
if ($this->table_type == "project") {
if (SecuritySQL("Search")) {
$strWhereClause = whereAdd($strWhereClause, SecuritySQL("Search"));
}
$this->strSQL = $gQuery->gSQLWhere($strWhereClause, $searchHavingClause, $strSearchCriteria);
$strOrderBy = $this->gstrOrderBy;
$this->strSQL .= " " . $strOrderBy;
$strSQLbak = $this->strSQL;
if (tableEventExists("BeforeQueryChart", $strTableName)) {
$tstrSQL = $this->strSQL;
$eventObj = getEventObject($strTableName);
$eventObj->BeforeQueryChart($tstrSQL, $strWhereClause, $strOrderBy);
$this->strSQL = $tstrSQL;
}
if ($strSQLbak == $this->strSQL) {
$this->strSQL = $gQuery->gSQLWhere($strWhereClause, $searchHavingClause, $strSearchCriteria);
$this->strSQL .= " " . $strOrderBy;
}
}
if ($this->cname && $this->table_type == "db") {
$this->strSQL = $this->chrt_array['sql'] . $this->chrt_array['where'] . $this->chrt_array['group_by'] . $this->chrt_array['order_by'];
} elseif ($this->cname && $this->table_type == "custom") {
if (!IsStoredProcedure($this->chrt_array['sql'])) {
$sql_query = $this->chrt_array['sql'];
if (GetDatabaseType() == 2) {
$pos = strrpos(strtoupper($sql_query), "ORDER BY");
if ($pos) {
$sql_query = substr($sql_query, 0, $pos);
}
}
if (GetDatabaseType() != 1) {
//Oracle
$this->strSQL = "select * from (" . $sql_query . ") as " . AddFieldWrappers("custom_query") . $this->chrt_array['where'];
} else {
$this->strSQL = "select * from (" . $sql_query . ")" . $this->chrt_array['where'];
}
} else {
$this->strSQL = $this->chrt_array['sql'];
}
}
if (tableEventExists("UpdateChartSettings", $strTableName)) {
$eventObj = getEventObject($strTableName);
$eventObj->UpdateChartSettings($this);
}
}
{
$_SESSION["MyURL"]=$_SERVER["SCRIPT_NAME"]."?".$_SERVER["QUERY_STRING"];
redirectToLogin();
}
// check user permissions
if(!CheckTablePermissions($strTableName, "I"))
{
HeaderRedirect("menu");
}
$cipherer = new RunnerCipherer($strTableName);
$keys = array();
// keys array
$keys[] = AddFieldWrappers("id");
$keys_present=1;
$total_records=0;
$goodlines = 0;
// Create audit object
$auditObj = GetAuditObject($strTableName);
function getFieldNamesByHeaders($fields, $strOriginalTableName, $ext)
{
global $strTableName, $conn, $gSettings;
// check fields in column headers
// check that we have labes in column headers
$fieldsNotFoundArr = array();
$fNamesArr = array();
else
{
$_SESSION["webobject"]["table_type"]="custom";
$page = (postvalue('type') == "webcharts") ? GetTableLink("webchart0") : GetTableLink("webreport0");
$sql_query_display=$_SESSION["customSQL"];
$sql_query=$_SESSION["customSQL"];
if(postvalue("sql")=="add")
{
$sname="Query";
$prefix=0;
while(true)
{
if($prefix>0)
$sname="Query_".$prefix;
$rs=db_query("select count(*) from ".AddTableWrappers("webreport_sql")." where ".AddFieldWrappers("sqlname")."=".db_prepare_string($sname),$conn);
$data = db_fetch_numarray($rs);
if($data[0]>0)
$prefix++;
else
break;
}
$_SESSION["idSQL"]="";
$_SESSION["nameSQL"]=$sname;
$_SESSION["customSQL"]="";
$sql_query_display="";
$sql_query="";
}
elseif(postvalue("sql")=="makesql")
{
$sql_query_display=postvalue("output");
function cached_ffn($field, $forGroupedField = false)
{
global $cache_fullfieldname, $strTableName;
if (!isset($cache_fullfieldname[$field])) {
// commented for bug 6660. Correct fix needed
//
if (!$wr_is_standalone && !$forGroupedField) {
$res = GetFullFieldName($field, $strTableName, false);
} else {
$res = AddFieldWrappers($field);
}
$cache_fullfieldname[$field] = $res;
return $res;
} else {
return $cache_fullfieldname[$field];
}
}
</form>
</body>
</html>
";
}
elseif($_REQUEST["step"]=="upgrade")
{
include("include/dbcommon.php");
$conn=db_connect();
$rs=db_query("select ".AddFieldWrappers("version")." from ".AddTableWrappers("webreport_settings"),$conn);
if($data=db_fetch_numarray($rs))
$version=floatval($data[0]);
else
$version=1;
Upgrade($version);
db_exec("update ".AddTableWrappers("webreport_settings")." set ".AddFieldWrappers("version")."='".$wr_version."'",$conn);
echo "
<html lang=\"en\">
<head>
<meta http-equiv=\"X-UA-Compatible\" content=\"IE=Edge\">
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">
<link REL=\"stylesheet\" href=\"styles/default.css\" type=\"text/css\">
<link REL=\"stylesheet\" href=\"styles/".$wr_pagestylepath."/style.css\" type=\"text/css\">
<link REL=\"stylesheet\" href=\"pagestyles/login2.css\" type=\"text/css\">
</head>
<body class=\"".$wr_pagestylepath." page-login2 function-login\" >
<form name=frmAdmin method=post action=\"login.php\">
<input type=hidden name=btnSubmit value=\"Login\">
<input type=hidden name=username value=\"admin\">
<input type=hidden name=password value=\"".postvalue("admpass")."\">
<?php
ini_set("display_errors", "1");
ini_set("display_startup_errors", "1");
include("include/dbcommon.php");
header("Expires: Thu, 01 Jan 1970 00:00:01 GMT");
$strTableName = "";
include("include/reportfunctions.php");
if (@$_REQUEST["cname"]) {
$cname = @$_REQUEST["cname"];
$sql_query = "SELECT " . AddFieldWrappers("rpt_id") . " FROM " . AddTableWrappers("webreports") . " WHERE " . AddFieldWrappers("rpt_name") . "='" . $cname . "' and " . AddFieldWrappers("rpt_type") . "='chart'";
$rs = db_query($sql_query, $conn);
if (!$data = db_fetch_numarray($rs))
header("location: " . GetTableLink("webreport"));
else
Reload_Chart(postvalue("cname"));
}
//$conn=db_connect();
$chrt_array = getChartArray(postvalue("cname"));
if (is_wr_project())
include("include/" . $chrt_array['settings']['short_table_name'] . "_variables.php");
$sessPrefix = "webchart" . postvalue("cname");
/*
/Trecho comentado por Helbert Samuel em 05/03/2015
function getCharTypeInterval($field, $int_type)
{
$field = AddFieldWrappers($this->CrossGoodFieldName($field));
switch (GetDatabaseType()) {
case 0:
return array("left(" . $field . "," . $int_type . ")", "left(" . $field . "," . $int_type . ")");
//MySQL
break;
case 1:
return array("substr(" . $field . ",1," . $int_type . ")", "substr(" . $field . ",1," . $int_type . ")");
//Oracle
break;
case 2:
return array("left(" . $field . "," . $int_type . ")", "left(" . $field . "," . $int_type . ")");
//MSSSQL
break;
case 3:
return array("left(" . $field . "," . $int_type . ")", "left(" . $field . "," . $int_type . ")");
//MS Access
break;
case 4:
return array("substring(" . $field . " from 1 for " . $int_type . ")", "substring(" . $field . " from 1 for " . $int_type . ")");
//Postgree
break;
case 5:
return array("substring(" . $field . " from 1 for " . $int_type . ")", "substring(" . $field . " from 1 for " . $int_type . ")");
//Informix
break;
case 6:
return array("substr(" . $field . ",1," . $int_type . ")", "substr(" . $field . ",1," . $int_type . ")");
//SQLite3
break;
case 7:
return array("substr(" . $field . ",1," . $int_type . ")", "substr(" . $field . ",1," . $int_type . ")");
//DB2
break;
}
}
function UnlockAdmin($strtable, $keys, $startEdit)
{
$skeys = "";
foreach ($keys as $ind => $val) {
if (strlen($skeys)) {
$skeys .= "&";
}
$skeys .= rawurlencode($val);
}
$sdate = now();
if ($startEdit) {
// add a record - lock
$this->TableObj->startdatetime = $sdate;
$this->TableObj->confirmdatetime = $sdate;
$this->TableObj->sessionid = session_id();
$this->TableObj->table = $strtable;
$this->TableObj->keys = $skeys;
$this->TableObj->userid = $this->UserID;
$this->TableObj->action = 1;
$this->TableObj->Add();
}
// delete all other locking records
$rstmp = CustomQuery("delete from " . AddTableWrappers($this->lockTableName) . " where " . AddFieldWrappers("table") . "=" . db_prepare_string($strtable) . " and " . AddFieldWrappers("keys") . "=" . db_prepare_string($skeys) . " and " . AddFieldWrappers("action") . "=1 and " . AddFieldWrappers("sessionid") . "<>'" . session_id() . "' ");
// inform other users that their locking were removed by locking
$rstmp = CustomQuery("delete from " . AddTableWrappers($this->lockTableName) . " where " . AddFieldWrappers("startdatetime") . "<'" . format_datetime_custom(adddays(db2time(now()), -2), "yyyy-MM-dd HH:mm:ss") . "' and " . AddFieldWrappers("action") . "=2");
$this->TableObj->startdatetime = $sdate;
$this->TableObj->confirmdatetime = $sdate;
$this->TableObj->sessionid = session_id();
$this->TableObj->table = $strtable;
$this->TableObj->keys = $skeys;
$this->TableObj->userid = $this->UserID;
$this->TableObj->action = 2;
$this->TableObj->Add();
}
$fEditFormat = GetFieldData($strTableName, $f, 'EditFormat', '');
if ($fEditFormat != EDIT_FORMAT_LOOKUP_WIZARD || GoodFieldName($f) != $field) {
continue;
}
$LookupType = GetFieldData($strTableName, $f, 'LookupType', '');
if ($LookupType == LT_LOOKUPTABLE) {
$LookupSQL = "SELECT ";
if (GetFieldData($strTableName, $f, 'LookupUnique', false)) {
$LookupSQL .= "DISTINCT ";
}
$LookupSQL .= GetLWLinkField($f, $strTableName, true);
$LookupSQL .= "," . GetLWDisplayField($f, $strTableName, true);
$LookupSQL .= " FROM " . AddTableWrappers(GetFieldData($strTableName, $f, 'LookupTable', '')) . " ";
$LookupSQL .= " WHERE " . GetLWLinkField($f, $strTableName, true) . "=" . $lookupValue . " AND ";
$LookupSQL .= GetLWDisplayField($f, $strTableName, true) . " LIKE " . db_prepare_string($value . "%");
if (GetFieldData($strTableName, $f, 'UseCategory', false)) {
$cvalue = make_db_value(GetFieldData($strTableName, $f, 'CategoryControl', ''), postvalue("category"));
$LookupSQL .= " AND " . AddFieldWrappers(GetFieldData($strTableName, $f, 'CategoryFilter', '')) . "=" . $cvalue;
}
}
}
$rs2 = db_query($LookupSQL, $conn);
if ($data = db_fetch_numarray($rs2)) {
$response[] = $data[0];
$response[] = $data[1];
}
}
}
}
$respObj = array('success' => true, 'data' => array_slice($response, 0, 40));
echo my_json_encode($respObj);
/**
* Adds sub query for counting details recs number
*
*/
function addMasterDetailSubQuery()
{
// add count of child records to SQL
if ($this->subQueriesSupp && $this->subQueriesSupAccess && !$this->theSameFieldsType) {
for ($i = 0; $i < count($this->allDetailsTablesArr); $i++) {
if ($this->allDetailsTablesArr[$i]['dispChildCount'] || $this->allDetailsTablesArr[$i]['hideChild']) {
$origTName = $this->allDetailsTablesArr[$i]['dOriginalTable'];
$dataSourceTName = $this->allDetailsTablesArr[$i]['dDataSourceTable'];
$shortTName = $this->allDetailsTablesArr[$i]['dShortTable'];
$detailsSettings = $this->pSet->getTable($dataSourceTName);
$detailsQuery = $detailsSettings->getSQLQuery();
$detailsSqlWhere = $detailsQuery->WhereToSql();
$masterWhere = "";
foreach ($this->masterKeysByD[$i] as $idx => $val) {
if ($masterWhere) {
$masterWhere .= " and ";
}
$masterWhere .= $this->cipherer->GetFieldName(AddTableWrappers("subQuery_cnt") . "." . AddFieldWrappers($this->detailKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]) . "=" . $this->cipherer->GetFieldName(AddTableWrappers($this->origTName) . "." . AddFieldWrappers($this->masterKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]);
}
// add a key field to the select list
$subQ = "";
foreach ($this->detailKeysByD[$i] as $k) {
if (strlen($subQ)) {
$subQ .= ",";
}
$subQ .= GetFullFieldNameForInsert($this->pSet, $k);
}
$subQ = "SELECT " . $subQ . " " . $detailsQuery->FromToSql();
// add security where clause for sub query
$securityClause = SecuritySQL("Search", $dataSourceTName);
if (strlen($securityClause)) {
$subQ .= " WHERE " . whereAdd($detailsSqlWhere, $securityClause);
} elseif (strlen($detailsSqlWhere)) {
$subQ .= " WHERE " . whereAdd("", $detailsSqlWhere);
}
// add detail table query tail
$subQ .= " " . $detailsQuery->TailToSql();
$countsql = "SELECT count(*) FROM (" . $subQ . ") " . AddTableWrappers("subQuery_cnt") . " WHERE " . $masterWhere;
$this->gsqlHead .= ",(" . $countsql . ") as " . AddFieldWrappers($dataSourceTName . "_cnt") . " ";
}
}
}
}
$clausesData = $fieldControl->getSelectColumnsAndJoinFromPart( $searchFor, $searchOpt, true );
$selectColumns = $clausesData["selectColumns"];
$fromClause = $gQuery->FromToSql().$clausesData["joinFromPart"];
$distinct = "DISTINCT";
if(GetDatabaseType() == 2 || GetDatabaseType() == 3)
{
if( IsTextType($fType) )
$distinct = "";
}
$sqlHead = "SELECT ".$distinct." ".$selectColumns." ";
if($gQuery->HasGroupBy())
{
$strSQL = $gQuery->gSQLWhere_having_fromQuery("", $where, $having);
$strSQL = "SELECT DISTINCT st.".AddFieldWrappers($f)." from (".$strSQL.") st";
}
else
{
$strSQL = SQLQuery::gSQLWhere_having($sqlHead, $fromClause, $gQuery->WhereToSql(), $gQuery->GroupByToSql()
, $gQuery->Having()->toSql($gQuery), $where, $having);
}
if(GetDatabaseType() == 0 || GetDatabaseType() == 4)
$strSQL.= " LIMIT ".$numberOfSuggests;
elseif(GetDatabaseType() == 2 || GetDatabaseType() == 3)
$strSQL = "select top ".$numberOfSuggests." * from (".$strSQL.") st";
elseif(GetDatabaseType() == 1)
$strSQL = AddRowNumber($strSQL, $numberOfSuggests);
elseif(GetDatabaseType() == -1)
$strSQL = AddLimitFirebird($strSQL, 0, $numberOfSuggests);
}
if (@$_POST["a"] == "added" && ($inlineadd == ADD_INLINE || $inlineadd == ADD_MASTER || $inlineadd == ADD_POPUP)) {
//Preparation view values
// get current values and show edit controls
$dispFieldAlias = "";
$data = 0;
if (count($keys)) {
$where = KeyWhere($keys);
$sqlHead = $gQuery->HeadToSql();
$sqlGroupBy = $gQuery->GroupByToSql();
$oHaving = $gQuery->Having();
$sqlHaving = $oHaving->toSql($gQuery);
$dispFieldAlias = postvalue('dispFieldAlias');
$dispField = postvalue('dispField');
if ($dispFieldAlias) {
$sqlHead .= ", " . $dispField . " as " . AddFieldWrappers($dispFieldAlias) . " ";
}
$strSQL = gSQLWhere_having($sqlHead, $gsqlFrom, $gsqlWhereExpr, $sqlGroupBy, $sqlHaving, $where, '');
LogInfo($strSQL);
$rs = db_query($strSQL, $conn);
$data = db_fetch_array($rs);
}
if (!$data) {
$data = $avalues;
$HaveData = false;
}
//check if correct values added
$keylink = "";
$keylink .= "&key1=" . htmlspecialchars(rawurlencode(@$data["Bill ID"]));
////////////////////////////////////////////
// Bill ID -
$arr=array();
$arr=array(0,"",postvalue("output"));
$customSQL=$arr[2];
$_SESSION["customSQL"]=$customSQL;
$_SESSION["idSQL"]=$arr[0];
$_SESSION["nameSQL"]=$arr[1];
$_SESSION["object_sql"]=$customSQL;
echo $customSQL;
exit();
}
if(postvalue("name")=="getcustomsql")
{
$arr=array();
$arr=WRgetCurrentCustomSQL(postvalue("output"));
$customSQL=$arr[2];
$_SESSION["customSQL"]=$customSQL;
$_SESSION["idSQL"]=$arr[0];
$_SESSION["nameSQL"]=$arr[1];
$_SESSION["object_sql"]=$customSQL;
echo $customSQL;
exit();
}
$arr = my_json_decode(DecodeUTF8(postvalue("output")));
db_exec("delete from ".AddTableWrappers("webreport_admin"),$conn);
foreach($arr as $val)
{
db_exec("insert into ".AddTableWrappers("webreport_admin")." (".AddFieldWrappers("tablename").",".AddFieldWrappers("db_type").",".AddFieldWrappers("group_name").") values (".db_prepare_string($val["table"]).",'".$val["db_type"]."',".db_prepare_string($val["group"]).")",$conn);
}
echo "OK";
?>
else
$value=(0+$value);
}
$sWhere.=" or ".GetFullFieldName($cEmailField,"webreport_users",false)."=".$value.")";
if($tosearch && $globalEvents->exists("BeforeRemindPassword"))
$tosearch = $globalEvents->BeforeRemindPassword($strUsernameEmail,$strUsernameEmail, $pageObject);
if($tosearch)
{
$selectClause = "select ".GetFullFieldName($cUserNameField,"webreport_users",false)." as ".AddFieldWrappers($cUserNameField)
.",".GetFullFieldName($cPasswordField,"webreport_users",false)." as ".AddFieldWrappers($cPasswordField);
// prevent aliases mixing
if( $cUserNameField != $cEmailField )
$strSQL.= ",".GetFullFieldName($cEmailField,"webreport_users",false)." as ".AddFieldWrappers($cEmailField);
$strSQL = $selectClause." from ".AddTableWrappers("webreport_users")." where ".$sWhere;
$rs = db_query($strSQL, $conn);
$data = $cipherer->DecryptFetchedArray($rs);
if($data)
{
$password=$data[$cPasswordField];
$strUsername = $data[$cUserNameField];
$url = GetSiteUrl();
$url.= $_SERVER["SCRIPT_NAME"];
$url2 = str_replace("remind.","login.",$url)."?username=".$data[$cUserNameField];
function FetchByID()
{
global $conn, $dal_info;
$tableinfo =& $dal_info[$this->m_TableName];
$dal_where = "";
foreach ($tableinfo as $fieldname => $fld) {
$command = 'if(isset($this->' . $fld['varname'] . ')) { ';
$command .= '$this->Value[\'' . escapesq($fieldname) . '\'] = $this->' . $fld['varname'] . ';';
$command .= ' }';
eval($command);
foreach ($this->Param as $field => $value) {
if (strtoupper($field) != strtoupper($fieldname)) {
continue;
}
$dal_where .= AddFieldWrappers($fieldname) . "=" . $this->PrepareValue($value, $fld["type"]) . " and ";
break;
}
}
// cleanup
$this->Reset();
// construct and run SQL
if ($dal_where) {
$dal_where = " where " . substr($dal_where, 0, -5);
}
$dalSQL = "select * from " . AddTableWrappers($this->m_TableName) . $dal_where;
$rs = db_query($dalSQL, $conn);
return $rs;
}
function getTableField($field)
{
$result = $this->getFieldData($field, "strField");
if($result != "")
return AddFieldWrappers($result);
else
return $this->getFullNameField($field);
}
function cached_ffn($field)
{
global $cache_fullfieldname;
if (!isset($cache_fullfieldname[$field])) {
$res = AddFieldWrappers($field);
$cache_fullfieldname[$field] = $res;
return $res;
} else {
return $cache_fullfieldname[$field];
}
}
