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);
}
/**
* @param Array keys
* @param Boolean forLookup
* @param String _table
* @param String _field
* @param String pageType
* @return Array
*/
public function GetAddedDataLookupQuery($keys, $forLookup, $_table, $_field, $pageType)
{
$lookupMainSettings = getLookupMainTableSettings($this->tName, $_table, $_field, $pageType);
if(!$lookupMainSettings)
return array();
global $conn;
$LookupSQL = "";
$mainField = $_field;
$mainTable = $lookupMainSettings->getTableName();
$linkFieldName = $lookupMainSettings->getLinkField($mainField);
$dispfield = $lookupMainSettings->getDisplayField($mainField);
$nLookupType = $lookupMainSettings->getLookupType($mainField);
if($nLookupType == LT_QUERY)
{
if($lookupMainSettings->getCustomDisplay($mainField))
$this->pSet->getSQLQuery()->AddCustomExpression($dispfield, $this->pSet, $mainTable, $mainField);
$lookupQueryObj = $this->pSet->getSQLQuery()->CloneObject();
}
else
{
$LookupSQL = "select ";
$LookupSQL .= GetFullFieldName($linkFieldName, $this->tName, true);
if($linkFieldName != $dispfield)
$LookupSQL .= "," . $this->pSet->getLWDisplayField($mainField, true);
$LookupSQL.=" from ".AddTableWrappers($this->strOriginalTableName);
}
$data = 0;
$lookupIndexes = array("linkFieldIndex" => 0, "displayFieldIndex" => 0);
if(count($keys))
{
$where = KeyWhere($keys);
if($nLookupType == LT_QUERY)
$LookupSQL = $lookupQueryObj->toSql(whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $where));
else
$LookupSQL.=" where ".$where;
$lookupIndexes = GetLookupFieldsIndexes($lookupMainSettings, $mainField);
LogInfo($LookupSQL);
if($forLookup)
{
$rs=db_query($LookupSQL,$conn);
$data = $this->cipherer->DecryptFetchedArray($rs);
}
else if($LookupSQL)
{
$rs = db_query($LookupSQL,$conn);
$data = db_fetch_numarray($rs);
$data[$lookupIndexes["linkFieldIndex"]] = $this->cipherer->DecryptField($linkFieldName, $data[$lookupIndexes["linkFieldIndex"]]);
if($nLookupType == LT_QUERY)
$data[$lookupIndexes["displayFieldIndex"]] = $this->cipherer->DecryptField($dispfield, $data[$lookupIndexes["displayFieldIndex"]]);
}
}
return array($data, array("linkField" => $linkFieldName, "displayField" => $dispfield
, "linkFieldIndex" => $lookupIndexes["linkFieldIndex"], "displayFieldIndex" => $lookupIndexes["displayFieldIndex"]));
}
function db_gettablelist()
{
global $conn;
$ret=array();
$strSQL="select owner||'.'||table_name as name,'TABLE' as type from all_tables where owner not like '%SYS%'
union all
select owner||'.'||view_name as name,'VIEW' from all_views where owner not like '%SYS%'";
$rs=db_query($strSQL,$conn);
while($data=db_fetch_numarray($rs))
$ret[]=$data[0];
return $ret;
}
function db_connect() {
global $connstr, $postgreDbVersion;
$conn=pg_connect($connstr);
if(!$conn)
trigger_error("Unable to connect",E_USER_ERROR);
$postgreDbVersion = "8";
$ret = pg_query("SELECT version()");
$row = db_fetch_numarray($ret);
if($row)
{
if( preg_match("/^PostgreSQL\s(\d{1,2})\./", $row[0], $matches) )
$postgreDbVersion = $matches[1];
}
return $conn;
}
function db_gettablelist()
{
global $conn;
$ret=array();
$strSQL="select DATABASE() as dbname";
$rs=db_query($strSQL,$conn);
$data=db_fetch_array($rs);
if(!$data)
return $ret;
$dbname=$data["dbname"];
$query = db_query("SELECT VERSION() as mysql_version",$conn);
$data=db_fetch_array($query);
$server_info=0;
if($data)
$server_info=$data["mysql_version"];
if($server_info>=5)
{
$strSQL="SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '".$dbname."'";
$rs=db_query($strSQL,$conn);
while($data=db_fetch_array($rs))
$ret[]=$data["TABLE_NAME"];
$strSQL="SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '".$dbname."'";
$rs=db_query($strSQL,$conn);
while($data=db_fetch_array($rs))
if(!in_array($data["TABLE_NAME"],$ret))
$ret[]=$data["TABLE_NAME"];
sort($ret);
}
else
{
$strSQL="SHOW tables";
$rs=db_query($strSQL,$conn);
while($data=db_fetch_numarray($rs))
$ret[]=$data[0];
}
return $ret;
}
function CrossTableReport($rpt_array)
{
global $conn;
$this->xml_array = $rpt_array;
$arrdata = array();
$arravgsum = array();
$arravgcount = array();
$group_y = array();
$group_x = array();
$sort_y = array();
$grid_row = array();
$avgsumx = array();
$avgcountx = array();
$this->total_summary = 0;
$this->is_value_empty = true;
$this->table_type = $rpt_array["table_type"];
if (!$this->table_type) {
$this->table_type = "project";
}
if ($rpt_array["fromWizard"]) {
$this->fromWizard = true;
}
$this->TableName = $this->xml_array["tables"][0];
$this->pSet = new ProjectSettings(GetTableByShort($this->TableName), PAGE_REPORT);
$sum_x = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_x"];
$sum_y = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_y"];
$sum_total = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_total"];
if (postvalue("group_func") != "") {
$_SESSION[$this->TableName . "_group_func"] = postvalue("group_func");
}
if (postvalue("field") != "") {
$_SESSION[$this->TableName . "_field"] = postvalue("field");
}
if (postvalue("axis_x") != "") {
$_SESSION[$this->TableName . "_gr_x"] = postvalue("axis_x");
}
if (postvalue("axis_y") != "") {
$_SESSION[$this->TableName . "_gr_y"] = postvalue("axis_y");
}
if (postvalue("rname") != "") {
$_SESSION[$this->TableName . "_rname"] = postvalue("rname");
}
$crtableSQL = $this->getstrSQL();
$rs = db_query($crtableSQL, $conn);
while ($data = db_fetch_numarray($rs)) {
if (!in_array($data[1], $group_y)) {
$group_y[] = $data[1];
$sort_y[] = count($sort_y);
}
if (!in_array($data[2], $group_x)) {
$group_x[] = $data[2];
$this->col_summary["data"][count($group_x) - 1]["col_summary"] = " ";
$this->col_summary["data"][count($group_x) - 1]["id_col_summary"] = "total_x_" . (count($group_x) - 1);
}
for ($i = 0; $i < count($group_y); $i++) {
if ($group_y[$i] == $data[1]) {
$key_y = $i;
}
}
for ($i = 0; $i < count($group_x); $i++) {
if ($group_x[$i] == $data[2]) {
$key_x = $i;
$avgsumx[$key_x] = 0;
$avgcountx[$key_x] = 0;
}
}
if (!$this->is_value_empty) {
$arrdata[$key_y][$key_x] = $data[0];
$arravgsum[$key_y][$key_x] = $data[3];
$arravgcount[$key_y][$key_x] = $data[4];
} else {
$arrdata[$key_y][$key_x] = " ";
}
}
global $group_sort_y;
$group_sort_y = $group_y;
usort($sort_y, array("CrossTableReport", "sort_arr_y"));
$group_func = $_SESSION[$this->TableName . "_group_func"];
$idx_field = $_SESSION[$this->TableName . "_field"];
if (!$idx_field) {
$idx_field = 0;
}
if ($group_func == "") {
$arr_value = $this->getSelectedValue();
if (empty($arr_value)) {
$field = $_SESSION['webreports']['group_fields'][0]["name"];
$arr_value[] = $field;
} else {
$field = $arr_value[$idx_field];
}
$group_func = $this->getGroupFunction($field, "");
} else {
$arr_value = $this->getSelectedValue();
$field = $arr_value[$idx_field];
$isGroupFuncExists = false;
foreach ($this->xml_array["totals"] as $key => $value) {
if ($this->FullFieldName($value["name"], $value["table"]) == $field) {
if ($value["sum"] == true && $group_func == "sum") {
$isGroupFuncExists = true;
break;
}
if ($value["max"] == true && $group_func == "max") {
$isGroupFuncExists = true;
break;
}
if ($value["min"] == true && $group_func == "min") {
$isGroupFuncExists = true;
break;
}
if ($value["avg"] == true && $group_func == "avg") {
$isGroupFuncExists = true;
break;
}
}
}
if (!$isGroupFuncExists) {
$group_func = $this->getGroupFunction($arr_value[$idx_field], "");
}
}
foreach ($sort_y as $key_y) {
$value_y = $group_y[$key_y];
$this->rowinfo[$key_y]["row_summary"] = " ";
$this->rowinfo[$key_y]["group_y"] = $this->getDisplayValue($this->index_field_y, $value_y);
foreach ($group_x as $key_x => $value_x) {
if (array_key_exists($key_y, $arrdata)) {
if (array_key_exists($key_x, $arrdata[$key_y]) && !$this->is_value_empty && !is_null($arrdata[$key_y][$key_x])) {
if ($group_func == "avg") {
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["row_value"] = round($arrdata[$key_y][$key_x], 2);
} else {
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["row_value"] = $arrdata[$key_y][$key_x];
}
} else {
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["row_value"] = " ";
}
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["id_data"] = $key_y . "_" . $key_x;
}
}
$this->rowinfo[$key_y]["id_row_summary"] = "total_y_" . $key_y;
}
foreach ($group_x as $key_x => $value_x) {
if ($value_x != "") {
$this->group_header["data"][$key_x]["gr_value"] = $this->getDisplayValue($this->index_field_x, $value_x);
} else {
$this->group_header["data"][$key_x]["gr_value"] = " ";
}
}
$this->total_summary = " ";
foreach ($this->rowinfo as $key_y => $obj_y) {
$obj_x = $obj_y["row_record"]["data"];
foreach ($obj_x as $key_x => $value) {
if ($value["row_value"] !== " ") {
switch ($group_func) {
case "sum":
if (!is_null($value["row_value"])) {
$this->rowinfo[$key_y]["row_summary"] += $value["row_value"];
$this->col_summary["data"][$key_x]["col_summary"] += $value["row_value"];
$this->total_summary += $value["row_value"];
}
break;
case "min":
if (($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"] < $this->rowinfo[$key_y]["row_summary"]) && !is_null($value["row_value"])) {
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
}
if (($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"] > $value["row_value"]) && !is_null($value["row_value"])) {
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
}
if (($this->total_summary === " " || $this->total_summary > $value["row_value"]) && !is_null($value["row_value"])) {
$this->total_summary = $value["row_value"];
}
break;
case "max":
if ($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"] > $this->rowinfo[$key_y]["row_summary"]) {
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
}
if ($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"] < $value["row_value"]) {
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
}
if ($this->total_summary === " " || $this->total_summary < $value["row_value"]) {
$this->total_summary = $value["row_value"];
}
break;
case "avg":
$this->rowinfo[$key_y]["avgsumy"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["avgcounty"] += $arravgcount[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgsumx"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgcountx"] += $arravgcount[$key_y][$key_x];
break;
}
if ($sum_x == true && !$this->is_value_empty && !is_null($this->col_summary["data"][$key_x]["col_summary"])) {
if (is_numeric($this->col_summary["data"][$key_x]["col_summary"])) {
$this->col_summary["data"][$key_x]["col_summary"] = round($this->col_summary["data"][$key_x]["col_summary"], 2);
}
} else {
$this->col_summary["data"][$key_x]["col_summary"] = " ";
}
}
}
if ($sum_y == true && !$this->is_value_empty && !is_null($this->rowinfo[$key_y]["row_summary"])) {
if (is_numeric($this->rowinfo[$key_y]["row_summary"])) {
$this->rowinfo[$key_y]["row_summary"] = round($this->rowinfo[$key_y]["row_summary"], 2);
}
} else {
$this->rowinfo[$key_y]["row_summary"] = " ";
}
}
if ($group_func == "avg") {
$total_sum = 0;
$total_count = 0;
foreach ($this->rowinfo as $key_y => $valuey) {
if ($valuey["avgcounty"]) {
$this->rowinfo[$key_y]["row_summary"] = round($valuey["avgsumy"] / $valuey["avgcounty"], 2);
$total_sum += $valuey["avgsumy"];
$total_count += $valuey["avgcounty"];
}
foreach ($valuey["row_record"]["data"] as $key_x => $valuex) {
if ($valuex["avgcountx"]) {
$avgsumx[$key_x] += $valuex["avgsumx"];
$avgcountx[$key_x] += $valuex["avgcountx"];
$total_sum += $valuex["avgsumx"];
$total_count += $valuex["avgcountx"];
}
}
}
foreach ($avgsumx as $key => $value) {
if ($avgcountx[$key]) {
$this->col_summary["data"][$key]["col_summary"] = round($value / $avgcountx[$key], 2);
}
}
if ($total_count) {
$this->total_summary = $total_sum / $total_count;
}
}
if ($sum_total == true && !$this->is_value_empty) {
if (is_numeric($this->total_summary)) {
$this->total_summary = round($this->total_summary, 2);
}
} else {
$this->total_summary = " ";
}
$prefix = "";
if ($this->table_type != "db") {
if (!$this->fromWizard) {
$prefix = $this->xml_array["tables"][0] . "_";
}
$field = $arr_value[$idx_field];
} else {
$field = GoodFieldName($arr_value[$idx_field]);
}
if ($this->xml_array['totals'][$prefix . $field]['curr'] == true && count($this->rowinfo)) {
foreach ($this->rowinfo as $arrkey => $arrfield) {
foreach ($arrfield["row_record"]["data"] as $fieldkey => $fieldvalue) {
if (is_numeric($fieldvalue["row_value"])) {
$this->rowinfo[$arrkey]["row_record"]["data"][$fieldkey]["row_value"] = str_format_currency($fieldvalue["row_value"]);
}
}
if (is_numeric($arrfield["row_summary"])) {
$this->rowinfo[$arrkey]["row_summary"] = str_format_currency($arrfield["row_summary"]);
}
}
if (is_numeric($this->total_summary)) {
$this->total_summary = str_format_currency($this->total_summary);
}
foreach ($this->col_summary["data"] as $arrkey => $arrvalue) {
if (is_numeric($arrvalue["col_summary"])) {
$this->col_summary["data"][$arrkey]["col_summary"] = str_format_currency($arrvalue["col_summary"]);
}
}
}
}
function WRGetTableListAdmin($db_type)
{
global $conn;
$ret=array();
$rs=db_query("select ".AddFieldWrappers("tablename").",".AddFieldWrappers("group_name")." from ".AddTableWrappers("webreport_admin")." where ".AddFieldWrappers("db_type")."='".$db_type."'",$conn);
while($data = db_fetch_numarray( $rs ))
$ret[]=array("tablename"=>$data[0],"group"=>$data[1]);
return $ret;
}
// show readonly fields
$linkdata = "";
if (@$_POST["a"] == "added" && $inlineadd == ADD_ONTHEFLY) {
if (!$error_happened && $status != "DECLINED") {
$LookupSQL = "";
$linkfield = "";
$dispfield = "";
if ($LookupSQL) {
$LookupSQL .= " from " . AddTableWrappers($strOriginalTableName);
}
$data = 0;
if (count($keys) && $LookupSQL) {
$where = KeyWhere($keys);
$LookupSQL .= " where " . $where;
$rs = db_query($LookupSQL, $conn);
$data = db_fetch_numarray($rs);
}
if ($data) {
$respData = array($linkfield => @$data[0], $dispfield => @$data[1]);
} else {
$respData = array($linkfield => @$avalues[$linkfield], $dispfield => @$avalues[$dispfield]);
}
$returnJSON['success'] = true;
$returnJSON['keys'] = $keys;
$returnJSON['vals'] = $respData;
$returnJSON['fields'] = $showFields;
} else {
$returnJSON['success'] = false;
$returnJSON['message'] = $message;
}
echo "<textarea>" . htmlspecialchars(my_json_encode($returnJSON)) . "</textarea>";
function DoInsertRecordSQL($table, &$avalues, &$blobfields, $pageid, &$pageObject, &$cipherer)
{
global $error_happened, $conn, $inlineadd, $usermessage, $message, $failed_inline_add, $keys, $strTableName;
// make SQL string
$strSQL = "insert into " . AddTableWrappers($table) . " ";
$strFields = "(";
$strValues = "(";
$blobs = PrepareBlobs($avalues, $blobfields);
foreach ($avalues as $akey => $value) {
$strFields .= $pageObject->pSet->getTableField($akey) . ", ";
if (in_array($akey, $blobfields)) {
$strValues .= $value . ", ";
} else {
if (is_null($cipherer)) {
$strValues .= add_db_quotes($akey, $value) . ", ";
} else {
$strValues .= $cipherer->AddDBQuotes($akey, $value) . ", ";
}
}
}
if (substr($strFields, -2) == ", ") {
$strFields = substr($strFields, 0, strlen($strFields) - 2);
}
if (substr($strValues, -2) == ", ") {
$strValues = substr($strValues, 0, strlen($strValues) - 2);
}
$strSQL .= $strFields . ") values " . $strValues . ")";
if (!ExecuteUpdate($pageObject, $strSQL, $blobs, true)) {
return false;
}
if ($error_happened) {
return false;
}
$pageObject->ProcessFiles();
if ($inlineadd == ADD_INLINE) {
$status = "ADDED";
$message = "" . "Record was added" . "";
$IsSaved = true;
} else {
$message = "<<< " . "Record was added" . " >>>";
}
if ($usermessage != "") {
$message = $usermessage;
}
$auditObj = GetAuditObject($table);
if ($inlineadd == ADD_SIMPLE || $inlineadd == ADD_INLINE || $inlineadd == ADD_ONTHEFLY || $inlineadd == ADD_POPUP || $inlineadd == ADD_MASTER || tableEventExists("AfterAdd", $strTableName) || $auditObj) {
$failed_inline_add = false;
$keyfields = $pageObject->pSet->getTableKeys();
foreach ($keyfields as $k) {
if (array_key_exists($k, $avalues)) {
$keys[$k] = $avalues[$k];
} elseif ($pageObject->pSet->isAutoincField($k)) {
$lastrs = @db_query("SELECT lastval()", $conn);
if ($lastdata = db_fetch_numarray($lastrs)) {
$keys[$k] = $lastdata[0];
}
} else {
$failed_inline_add = true;
}
}
}
return true;
}
static function gSQLRowCount_int($sqlHead, $sqlFrom, $sqlWhere, $sqlGroupBy, $sqlHaving, $where, $having, $criteria="or")
{
global $conn;
global $bSubqueriesSupported;
$strWhere=whereAdd($sqlWhere,$where);
if(strlen($strWhere))
$strWhere=" where ".$strWhere." ";
if(strlen($sqlGroupBy))
{
if($bSubqueriesSupported)
{
$countstr = "select count(*) from (".SQLQuery::gSQLWhere_having($sqlHead,$sqlFrom,$sqlWhere,$sqlGroupBy, $sqlHaving,$where,$having,$criteria).") a";
}
else
{
$countstr = SQLQuery::gSQLWhere_having($sqlHead,$sqlFrom,$sqlWhere,$sqlGroupBy, $sqlHaving,$where,$having,$criteria);
return GetMySQL4RowCount($countstr);
}
}
else
{
$countstr = "select count(*) ".$sqlFrom.$strWhere;
}
$countrs = db_query($countstr, $conn);
$countdata = db_fetch_numarray($countrs);
return $countdata[0];
}
, $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);
$rs = db_query($strSQL, $conn);
// fill $response array with the field's suggest value
while( ($row = db_fetch_numarray($rs)) && 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
if (!preg_match($pattern_clear_tags, $val)) // if not html tags
$fieldControl->suggestValue("_".$val, $searchFor, $response, $row);
else
$response['_'.$val] = '_'.$val;
}
}
db_close($conn);
ksort($response, SORT_STRING);
if( $denyChecking )
{
$returnJSON = array("success" => false, "error" => "Duplicated values are allowed");
echo "<div>".printJSON($returnJSON)."</div>";
return;
}
$cipherer = new RunnerCipherer($tableName, $pSet);
if( $cipherer->isFieldEncrypted($fieldName) )
$value = $cipherer->MakeDBValue($fieldName, $value, $fieldControlType, "", true);
else
$value = make_db_value($fieldName, $value, $fieldControlType, "", $tableName);
$where = GetFullFieldName($fieldName, $tableName, false).( $value == "null" ? ' is ' : '=' ).$value;
$sql = "SELECT count(*) from ".AddTableWrappers( $pSet->getOriginalTableName() )." where ".$where;
$rs = db_query($sql, $conn);
if( !$rs || !($data = db_fetch_numarray($rs)) )
{
$returnJSON = array("success" => false, "error" => "Error: Wrong SQL query");
echo "<div>".printJSON($returnJSON)."</div>";
return;
}
$hasDuplicates = $data[0] ? true : false;
$returnJSON = array("success" => true, "hasDuplicates" => $hasDuplicates, "error"=>"");
echo "<div>".printJSON($returnJSON)."</div>";
return;
?>
function CrossTableReport($rpt_array, $strSQL)
{
global $conn;
$this->xml_array = $rpt_array;
if( $rpt_array["table_type"] )
$this->table_type = $rpt_array["table_type"];
if( $rpt_array["fromWizard"] )
$this->fromWizard = true;
$this->tableName = $this->xml_array["tables"][0];
$this->shortTableName = GetTableURL($this->tableName);
if( strlen($this->shortTableName) == 0 )
$this->shortTableName = $this->tableName;
$this->pSet = new ProjectSettings($this->tableName, PAGE_REPORT);
if( $this->fromWizard )
{
include_once getabspath("classes/controls/ViewControlsContainer.php");
$this->viewControls = new ViewControlsContainer($this->pSet, PAGE_REPORT);
$this->tableKeys = $this->pSet->getTableKeys();
}
$this->fillSessionVariables();
$this->dataField = $this->getDataField( $_SESSION[$this->shortTableName."_field"] );
if( !strlen($this->dataField) )
$this->dataField = $_SESSION['webreports']['group_fields'][0]["name"];
$this->initDataFieldSettings();
$this->initDataGroupFunction($_SESSION[$this->shortTableName."_group_func"]);
// assing index_field_x, index_field_y properties
$this->setAxisFieldsIndices();
$crtableSQL = $this->getstrSQL( $strSQL );
$rs = db_query($crtableSQL, $conn);
$group_y = array();
$group_x = array();
$sort_y = array();
$arrdata = array();
$arravgsum = array();
$arravgcount = array();
$avgsumx = array();
$avgcountx = array();
while($data = db_fetch_numarray($rs))
{
if( !in_array($data[1], $group_y) )
{
$group_y[] = $data[1];
$sort_y[] = count($sort_y);
}
if( !in_array($data[2], $group_x) )
{
$group_x[] = $data[2];
$this->col_summary["data"][ count($group_x) - 1 ]["col_summary"] = " ";
$this->col_summary["data"][ count($group_x) - 1 ]["id_col_summary"] = "total_x_".( count($group_x) - 1);
}
$key_y = array_search( $data[1], $group_y );
$key_x = array_search( $data[2], $group_x );
$avgsumx[ $key_x ] = 0;
$avgcountx[ $key_x ] = 0;
if( !$this->is_value_empty )
{
$arrdata[ $key_y ][ $key_x ] = $data[0];
$arravgsum[ $key_y ][ $key_x ] = $data[3];
$arravgcount[ $key_y ][ $key_x ] = $data[4];
}
else
$arrdata[ $key_y ][ $key_x ] = " ";
}
// sort y groups
global $group_sort_y;
$group_sort_y = $group_y;
SortForCrosstable($sort_y);
foreach($sort_y as $key_y)
{
$value_y = $group_y[ $key_y ];
$this->rowinfo[ $key_y ]["row_summary"] = " ";
$this->rowinfo[ $key_y ]["group_y"] = $this->getAxisDisplayValue($this->index_field_y, $value_y);
foreach($group_x as $key_x => $value_x)
{
if( array_key_exists($key_y, $arrdata) )
{
$rowValue = " ";
if( array_key_exists($key_x, $arrdata[ $key_y ]) && !$this->is_value_empty && !is_null($arrdata[ $key_y ][ $key_x ]) )
{
$rowValue = $arrdata[ $key_y ][ $key_x ];
if( $this->dataGroupFunction == "avg" )
$rowValue = round($rowValue, 2);
}
$this->rowinfo[ $key_y ]["row_record"]["data"][ $key_x ]["row_value"] = $rowValue;
$this->rowinfo[ $key_y ]["row_record"]["data"][ $key_x ]["id_data"] = $key_y."_".$key_x;
}
}
$this->rowinfo[ $key_y ]["id_row_summary"] = "total_y_".$key_y;
}
foreach($group_x as $key_x => $value_x)
{
if($value_x != "")
$this->group_header["data"][ $key_x ]["gr_value"] = $this->getAxisDisplayValue($this->index_field_x, $value_x);
else
$this->group_header["data"][ $key_x ]["gr_value"]=" ";
}
$sum_x = $this->xml_array["group_fields"][ count($this->xml_array["group_fields"]) - 1 ]["sum_x"];
$sum_y = $this->xml_array["group_fields"][ count($this->xml_array["group_fields"]) - 1 ]["sum_y"];
$sum_total = $this->xml_array["group_fields"][ count($this->xml_array["group_fields"]) - 1 ]["sum_total"];
$this->total_summary = " ";
foreach($this->rowinfo as $key_y => $obj_y)
{
$obj_x = $obj_y["row_record"]["data"];
foreach($obj_x as $key_x => $value)
{
if($value["row_value"] !== " ")
{
switch($this->dataGroupFunction)
{
case "sum":
if(!is_null($value["row_value"]))
{
$this->rowinfo[$key_y]["row_summary"] += $value["row_value"];
$this->col_summary["data"][$key_x]["col_summary"] += $value["row_value"];
$this->total_summary += $value["row_value"];
}
break;
case "min":
if(($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"]<$this->rowinfo[$key_y]["row_summary"]) && !is_null($value["row_value"]))
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
if(($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"]>$value["row_value"]) && !is_null($value["row_value"]))
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
if(($this->total_summary === " " || $this->total_summary>$value["row_value"]) && !is_null($value["row_value"]))
$this->total_summary = $value["row_value"];
break;
case "max":
if($this->rowinfo[$key_y]["row_summary"] === " " || $value["row_value"]>$this->rowinfo[$key_y]["row_summary"])
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
if($this->col_summary["data"][$key_x]["col_summary"] === " " || $this->col_summary["data"][$key_x]["col_summary"]<$value["row_value"])
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
if($this->total_summary === " " || $this->total_summary<$value["row_value"])
$this->total_summary = $value["row_value"];
break;
case "avg":
$this->rowinfo[$key_y]["avgsumy"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["avgcounty"] += $arravgcount[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgsumx"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgcountx"] += $arravgcount[$key_y][$key_x];
break;
}
if($sum_x == true && !$this->is_value_empty && !is_null($this->col_summary["data"][$key_x]["col_summary"]))
{
if(is_numeric($this->col_summary["data"][$key_x]["col_summary"]))
$this->col_summary["data"][$key_x]["col_summary"] = round($this->col_summary["data"][$key_x]["col_summary"],2);
}
else
$this->col_summary["data"][$key_x]["col_summary"] = " ";
}
}
if($sum_y == true && !$this->is_value_empty && !is_null($this->rowinfo[$key_y]["row_summary"]))
{
if(is_numeric($this->rowinfo[$key_y]["row_summary"]))
$this->rowinfo[$key_y]["row_summary"] = round($this->rowinfo[$key_y]["row_summary"],2);
}
else
$this->rowinfo[$key_y]["row_summary"] = " ";
}
if($this->dataGroupFunction == "avg")
{
$total_sum = 0;
$total_count = 0;
foreach($this->rowinfo as $key_y => $valuey)
{
if($valuey["avgcounty"])
{
$this->rowinfo[$key_y]["row_summary"] = round($valuey["avgsumy"]/$valuey["avgcounty"],2);
$total_sum += $valuey["avgsumy"];
$total_count += $valuey["avgcounty"];
}
foreach($valuey["row_record"]["data"] as $key_x => $valuex)
{
if($valuex["avgcountx"])
{
$avgsumx[$key_x] += $valuex["avgsumx"];
$avgcountx[$key_x] += $valuex["avgcountx"];
$total_sum += $valuex["avgsumx"];
$total_count += $valuex["avgcountx"];
}
}
}
foreach($avgsumx as $key => $value)
{
if($avgcountx[$key])
$this->col_summary["data"][$key]["col_summary"] = round($value/$avgcountx[$key],2);
}
if($total_count)
$this->total_summary = $total_sum/$total_count;
}
if( $sum_total != true || $this->is_value_empty )
$this->total_summary = " ";
elseif( is_numeric($this->total_summary) )
$this->total_summary = round($this->total_summary,2);
$this->updateRecordsDisplayedFields();
}
/**
* get count of rows from the query
* @intellisense
*/
function GetRowCount($strSQL)
{
global $conn;
$strSQL=str_replace(array("\r\n","\n","\t")," ",$strSQL);
$tstr = strtoupper($strSQL);
$ind1 = strpos($tstr,"SELECT ");
$ind2 = my_strrpos($tstr," FROM ");
$ind3 = my_strrpos($tstr," GROUP BY ");
if($ind3===false)
{
$ind3 = strpos($tstr," ORDER BY ");
if($ind3===false)
$ind3=strlen($strSQL);
}
$countstr=substr($strSQL,0,$ind1+6)." count(*) ".substr($strSQL,$ind2+1,$ind3-$ind2);
$countrs = db_query($countstr,$conn);
$countdata = db_fetch_numarray($countrs);
return $countdata[0];
}
/**
* Check if the field's value duplicates with any of database field's values
*
* @param {String} $fieldName
* @param {String | Number} $value
* @retrun {Boolean}
*/
function hasDuplicateValue($fieldName, $value)
{
global $conn;
if($this->cipherer->isFieldEncrypted($fieldName))
{
$value = $this->cipherer->MakeDBValue($fieldName, $value, "", "", true);
}
else
{
$value = add_db_quotes($fieldName, $value);
}
$where = GetFullFieldName($fieldName, $this->tName, false).'='.$value;
$sql = "SELECT count(*) from ".AddTableWrappers($this->pSet->getOriginalTableName())." where ".$where;
$rs = db_query($sql, $conn);
$data = db_fetch_numarray($rs);
if(!$data[0])
{
return false;
}
return true;
}
$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);
}
// prepare common vals
$where = whereAdd($where . $masterWhere, $strSecuritySql);
$distinct = "DISTINCT";
$sqlHead = "SELECT " . $distinct . " " . GetFullFieldName($f) . " ";
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, $gQuery->FromToSql(), $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $where, $having);
}
$strSQL = SQLQuery::gSQLWhere_having($sqlHead, $gQuery->FromToSql(), $gQuery->WhereToSql(), $gQuery->GroupByToSql(), $gQuery->Having()->toSql($gQuery), $where, $having);
$strSQL .= " ORDER BY 1 LIMIT 10 ";
$rs = db_query($strSQL, $conn);
$i = 0;
while (($row = db_fetch_numarray($rs)) && count($response) < 10) {
$val = $cipherer->DecryptField($f, $row[0]);
if (IsGuid($fType)) {
$val = substr($val, 1, -1);
}
$controls->getControl($f)->suggestValue($val, $searchFor, $response, $row);
}
}
}
db_close($conn);
ksort($response, SORT_STRING);
$suggestValues = array();
foreach ($response as $value => $realValue) {
$suggestValues[] = array("value" => $value, "realValue" => $realValue);
}
// all queries worked without errors, add success marker
/**
* Fill rights array
* Call it only after save new data, for get fresh data
*
*/
function getRights()
{
$trs = db_query("select GroupID,TableName,AccessMask from [ugrights] order by GroupID", $this->conn);
while ($tdata = db_fetch_numarray($trs)) {
if (!array_key_exists($tdata[1], $this->nonAdminTablesRightsArr)) {
continue;
}
if (!array_key_exists((int) $tdata[0], $this->nonAdminTablesRightsArr[$tdata[1]])) {
continue;
}
$this->nonAdminTablesRightsArr[$tdata[1]][$tdata[0]] = $tdata[2];
}
}
if ($lookupOrderBy) {
$LookupSQL .= " ORDER BY " . $lookupOrderBy;
}
}
}
}
if (strlen(GetLWWhere($f, $pageType, $strTableName))) {
$hasWhere = true;
}
break;
}
$lookupIndexes = GetLookupFieldsIndexes($gSettings, $lookupField);
$linkFieldIndex = $lookupIndexes["linkFieldIndex"];
$displayFieldIndex = $lookupIndexes["displayFieldIndex"];
$rs = db_query($LookupSQL, $conn);
while ($data = db_fetch_numarray($rs)) {
if ($LookupType == LT_QUERY && $gSettings->isLookupUnique($f)) {
if (!isset($uniqueArray)) {
$uniqueArray = array();
}
if (in_array($data[$displayFieldIndex], $uniqueArray)) {
continue;
}
$uniqueArray[] = $data[$displayFieldIndex];
}
$data[$linkFieldIndex] = $cipherer->DecryptField($f, $data[$linkFieldIndex]);
if ($LookupType == LT_QUERY) {
$data[$displayFieldIndex] = $cipherer->DecryptField($displayFieldName, $data[$displayFieldIndex]);
}
$response[] = $data[$linkFieldIndex];
$response[] = $data[$displayFieldIndex];
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];
if (IsGuid($fType)) {
$val = substr($val, 1, -1);
}
$pos = strpos($val, "\n");
if ($pos !== FALSE) {
$response[] = substr($val, 0, $pos);
} else {
$response[] = $val;
}
if ($i > 10) {
break;
}
}
function suggestValue($value, $searchFor, &$response, &$row)
{
if (!GetGlobalData("handleSearchSuggestInLookup", true)) {
parent::suggestValue($value, $searchFor, $response, $row);
return;
}
global $conn;
$lookupSQL = buildLookupSQL($this->lookupPageType, $this->field, $this->pageObject->tName, "", $value, false, true, false, true, true, true);
$this->fillLookupFieldsIndexes();
$rs_lookup = db_query($lookupSQL, $conn);
if ($data = db_fetch_numarray($rs_lookup)) {
if ($this->isDisplayFieldEncrypted) {
$lookup_value = $this->ciphererDisplay->DecryptField($this->lookupType == LT_QUERY ? $this->displayFieldName : $this->field, $data[$this->displayFieldIndex]);
} else {
$lookup_value = $data[$this->displayFieldIndex];
}
parent::suggestValue($lookup_value, $searchFor, $response, $row);
}
}
/**
* Fill rights array
* Call it only after save new data, for get fresh data
*
*/
function getRights()
{
$trs = db_query("select ,, from \"ugrights\" order by ", $this->conn);
while ($tdata = db_fetch_numarray($trs)) {
if (!array_key_exists($tdata[1], $this->nonAdminTablesRightsArr)) {
continue;
}
if (!array_key_exists((int) $tdata[0], $this->nonAdminTablesRightsArr[$tdata[1]])) {
continue;
}
$this->nonAdminTablesRightsArr[$tdata[1]][$tdata[0]] = $tdata[2];
}
}
function CrossTableReport($rpt_array)
{
global $conn;
$this->xml_array = $rpt_array;
$arrdata = array();
$arravgsum = array();
$arravgcount = array();
$group_y = array();
$group_x = array();
$sort_y = array();
$grid_row = array();
$this->total_summary = 0;
$this->is_value_empty = true;
$this->table_type = $rpt_array["table_type"];
if (!$this->table_type) {
$this->table_type = "project";
}
$this->TableName = $this->xml_array["tables"][0];
$sum_x = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_x"];
$sum_y = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_y"];
$sum_total = $this->xml_array["group_fields"][count($this->xml_array["group_fields"]) - 1]["sum_total"];
$crtableSQL = $this->getstrSQL();
$rs = db_query($crtableSQL, $conn);
while ($data = db_fetch_numarray($rs)) {
if (!in_array($data[1], $group_y)) {
$group_y[] = $data[1];
$sort_y[] = count($sort_y);
}
if (!in_array($data[2], $group_x)) {
$group_x[] = $data[2];
$this->col_summary["data"][count($group_x) - 1]["col_summary"] = " ";
$this->col_summary["data"][count($group_x) - 1]["id_col_summary"] = "total_x_" . (count($group_x) - 1);
}
for ($i = 0; $i < count($group_y); $i++) {
if ($group_y[$i] == $data[1]) {
$key_y = $i;
}
}
for ($i = 0; $i < count($group_x); $i++) {
if ($group_x[$i] == $data[2]) {
$key_x = $i;
$avgsumx[$key_x] = 0;
$avgcountx[$key_x] = 0;
}
}
if (!$this->is_value_empty) {
$arrdata[$key_y][$key_x] = $data[0];
$arravgsum[$key_y][$key_x] = $data[3];
$arravgcount[$key_y][$key_x] = $data[4];
} else {
$arrdata[$key_y][$key_x] = " ";
}
}
global $group_sort_y;
$group_sort_y = $group_y;
usort($sort_y, array("CrossTableReport", "sort_arr_y"));
foreach ($sort_y as $key_y) {
$value_y = $group_y[$key_y];
$this->rowinfo[$key_y]["row_summary"] = " ";
$this->rowinfo[$key_y]["group_y"] = $this->getDisplayValue($this->index_field_y, $value_y);
foreach ($group_x as $key_x => $value_x) {
if (array_key_exists($key_y, $arrdata)) {
if (array_key_exists($key_x, $arrdata[$key_y]) && !$this->is_value_empty) {
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["row_value"] = $arrdata[$key_y][$key_x];
} else {
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["row_value"] = " ";
}
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["id_data"] = $key_y . "_" . $key_x;
}
}
$this->rowinfo[$key_y]["id_row_summary"] = "total_y_" . $key_y;
}
foreach ($group_x as $key_x => $value_x) {
if ($value_x != "") {
$this->group_header["data"][$key_x]["gr_value"] = $this->getDisplayValue($this->index_field_x, $value_x);
} else {
$this->group_header["data"][$key_x]["gr_value"] = " ";
}
}
$group_func = postvalue("group_func");
if ($group_func == "") {
$arr_value = array();
$arr_value = $this->getSelectedValue();
$group_func = $this->getGroupFunction($arr_value[0], "");
}
$this->total_summary = " ";
foreach ($this->rowinfo as $key_y => $obj_y) {
$obj_x = $obj_y["row_record"]["data"];
foreach ($obj_x as $key_x => $value) {
if ($value["row_value"] != " ") {
switch ($group_func) {
case "sum":
$this->rowinfo[$key_y]["row_summary"] += $value["row_value"];
$this->col_summary["data"][$key_x]["col_summary"] += $value["row_value"];
$this->total_summary += $value["row_value"];
break;
case "min":
if ($this->rowinfo[$key_y]["row_summary"] == " " || $value["row_value"] < $this->rowinfo[$key_y]["row_summary"]) {
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
}
if ($this->col_summary["data"][$key_x]["col_summary"] == " " || $this->col_summary["data"][$key_x]["col_summary"] > $value["row_value"]) {
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
}
if ($this->total_summary == " " || $this->total_summary > $value["row_value"]) {
$this->total_summary = $value["row_value"];
}
break;
case "max":
if ($this->rowinfo[$key_y]["row_summary"] == " " || $value["row_value"] > $this->rowinfo[$key_y]["row_summary"]) {
$this->rowinfo[$key_y]["row_summary"] = $value["row_value"];
}
if ($this->col_summary["data"][$key_x]["col_summary"] == " " || $this->col_summary["data"][$key_x]["col_summary"] < $value["row_value"]) {
$this->col_summary["data"][$key_x]["col_summary"] = $value["row_value"];
}
if ($this->total_summary == " " || $this->total_summary < $value["row_value"]) {
$this->total_summary = $value["row_value"];
}
break;
case "avg":
$this->rowinfo[$key_y]["avgsumy"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["avgcounty"] += $arravgcount[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgsumx"] += $arravgsum[$key_y][$key_x];
$this->rowinfo[$key_y]["row_record"]["data"][$key_x]["avgcountx"] += $arravgcount[$key_y][$key_x];
break;
}
if ($sum_x == "true" && !$this->is_value_empty) {
$this->col_summary["data"][$key_x]["col_summary"] = round($this->col_summary["data"][$key_x]["col_summary"], 2);
} else {
$this->col_summary["data"][$key_x]["col_summary"] = "";
}
}
}
if ($sum_y == "true" && !$this->is_value_empty) {
$this->rowinfo[$key_y]["row_summary"] = round($this->rowinfo[$key_y]["row_summary"], 2);
} else {
$this->rowinfo[$key_y]["row_summary"] = "";
}
}
if ($group_func == "avg") {
$total_sum = 0;
$total_count = 0;
foreach ($this->rowinfo as $key_y => $valuey) {
if ($valuey["avgcounty"]) {
$this->rowinfo[$key_y]["row_summary"] = $valuey["avgsumy"] / $valuey["avgcounty"];
$total_sum += $valuey["avgsumy"];
$total_count += $valuey["avgcounty"];
}
foreach ($valuey["row_record"]["data"] as $key_x => $valuex) {
if ($valuex["avgcountx"]) {
$avgsumx[$key_x] += $valuex["avgsumx"];
$avgcountx[$key_x] += $valuex["avgcountx"];
$total_sum += $valuex["avgsumx"];
$total_count += $valuex["avgcountx"];
}
}
}
foreach ($avgsumx as $key => $value) {
if ($avgcountx[$key]) {
$this->col_summary["data"][$key]["col_summary"] = $value / $avgcountx[$key];
}
}
if ($total_count) {
$this->total_summary = $total_sum / $total_count;
}
}
if ($sum_total == "true" && !$this->is_value_empty) {
$this->total_summary = round($this->total_summary, 2);
} else {
$this->total_summary = "";
}
}
foreach ($keyToModify as $screen => $data){
foreach ($data as $key => $val){
$rpt_array[$xml_field][$screen][$key] = $val;
}
}
}
}
$rpt_array['miscellaneous']['print_friendly'] = ($rpt_array['miscellaneous']['print_friendly'] == "true") ? true : false;
// Load and assign styles
$sql_query = "SELECT " . AddFieldWrappers("report_style_id") . "," . AddFieldWrappers("type") . "," . AddFieldWrappers("field") . "," . AddFieldWrappers("group") . "," . AddFieldWrappers("style_str") . "," . AddFieldWrappers("uniq") . ", " . AddFieldWrappers("repname") . ", " . AddFieldWrappers("styletype") . " FROM " . AddTableWrappers("webreport_style") . " WHERE " . AddFieldWrappers("repname") . "=" . db_prepare_string(postvalue('rname')) . " ORDER BY " . AddFieldWrappers("report_style_id") . " ASC";
$rsReport = db_query($sql_query, $conn);
$styleStr = '';
while ($data = db_fetch_numarray($rsReport)){
if ($data[1] == 'table')
$styleStr .= "#legend td{" . $data[4] . "}\n";
else if (($data[2] == 0) && ($data[3] != 0))
$styleStr .= "#legend td.class" . $data[3] . "g" . "{" . $data[4] . "}\n";
else if (($data[2] != 0) && ($data[3] == 0))
$styleStr .= "#legend td.class" . $data[2] . "f" . "{" . $data[4] . "}\n";
else if ($data[5] == 0 && $data[2] != 0 && $data[3] != 0)
$styleStr .= "#legend td.class" . $data[3] . "g" . $data[2] . "f0u{" . $data[4] . "}\n";
else
$styleStr .= "#legend td.class" . $data[3] . "g" . $data[2] . "f" . $data[5] . "u" . "{" . $data[4] . "}\n";
}
$xt->assign("styleStr", $styleStr);
if ($editmode) {
/**
* Fill groups array from DB, call after save
*
*/
function fillGroups()
{
$this->groups[] = array(-1, "<" . "Admin" . ">");
$this->groupFullChecked[] = true;
$trs = db_query("select , from \"uggroups\" order by ", $this->conn);
while ($tdata = db_fetch_numarray($trs)) {
$this->groups[] = array($tdata[0], $tdata[1]);
$this->groupFullChecked[] = true;
}
}
function GetAddedDataLookupQuery($pageObject, $keys, $forLookup)
{
global $conn, $strTableName, $strOriginalTableName;
$LookupSQL = "";
$linkfield = "";
$dispfield = "";
$noBlobReplace = false;
$lookupFieldName = "";
if ($LookupSQL && $nLookupType != LT_QUERY) {
$LookupSQL .= " from " . AddTableWrappers($strOriginalTableName);
}
$data = 0;
$lookupIndexes = array("linkFieldIndex" => 0, "displayFieldIndex" => 0);
if (count($keys)) {
$where = KeyWhere($keys);
if ($nLookupType == LT_QUERY) {
$LookupSQL = $lookupQueryObj->toSql(whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $where));
} else {
$LookupSQL .= " where " . $where;
}
$lookupIndexes = GetLookupFieldsIndexes($lookupPSet, $lookupFieldName);
LogInfo($LookupSQL);
if ($forLookup) {
$rs = db_query($LookupSQL, $conn);
$data = $pageObject->cipherer->DecryptFetchedArray($rs);
} else {
if ($LookupSQL) {
$rs = db_query($LookupSQL, $conn);
$data = db_fetch_numarray($rs);
$data[$lookupIndexes["linkFieldIndex"]] = $pageObject->cipherer->DecryptField($linkFieldName, $data[$lookupIndexes["linkFieldIndex"]]);
if ($nLookupType == LT_QUERY) {
$data[$lookupIndexes["displayFieldIndex"]] = $pageObject->cipherer->DecryptField($dispfield, $data[$lookupIndexes["displayFieldIndex"]]);
}
}
}
}
return array($data, array("linkField" => $linkFieldName, "displayField" => $dispfield, "linkFieldIndex" => $lookupIndexes["linkFieldIndex"], "displayFieldIndex" => $lookupIndexes["displayFieldIndex"]));
}
public function showDBValue(&$data, $keylink)
{
global $conn, $strTableName;
$value = $data[$this->field];
if (!strlen($value)) {
return "";
}
$where = "";
$out = "";
$lookupvalue = $value;
$iquery = "field=" . htmlspecialchars(rawurlencode($this->field)) . $keylink;
$where = GetLWWhere($this->field, $this->container->pageType);
if ($this->pSet->multiSelect($this->field)) {
$arr = splitvalues($value);
$numeric = true;
$type = $this->pSet->getLWLinkFieldType($this->field);
if (!$type) {
foreach ($arr as $val) {
if (strlen($val) && !is_numeric($val)) {
$numeric = false;
break;
}
}
} else {
$numeric = !NeedQuotes($type);
}
$in = "";
foreach ($arr as $val) {
if ($numeric && !strlen($val)) {
continue;
}
if (strlen($in)) {
$in .= ",";
}
if ($numeric) {
$in .= $val + 0;
} else {
$in .= db_prepare_string($this->cipherer->EncryptField($this->nLookupType == LT_QUERY ? $this->linkFieldName : $this->field, $val));
}
}
if (strlen($in)) {
if ($this->nLookupType == LT_QUERY) {
$inWhere = GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " in (" . $in . ")";
if (strlen($where)) {
$inWhere .= " and (" . $where . ")";
}
$LookupSQL = $this->lookupQueryObj->toSql(whereAdd($this->lookupQueryObj->m_where->toSql($this->lookupQueryObj), $inWhere));
} else {
$LookupSQL = $this->LookupSQL . $this->pSet->getLWLinkField($this->field) . " in (" . $in . ")";
if (strlen($where)) {
$LookupSQL .= " and (" . $where . ")";
}
}
LogInfo($LookupSQL);
$rsLookup = db_query($LookupSQL, $conn);
$found = false;
$lookupArrTmp = array();
$lookupArr = array();
while ($lookuprow = db_fetch_numarray($rsLookup)) {
$lookupArrTmp[] = $lookuprow[$this->displayFieldIndex];
}
$lookupArr = array_unique($lookupArrTmp);
$localData = $data;
foreach ($lookupArr as $lookupvalue) {
if ($found) {
$out .= ",";
}
$found = true;
if ($this->pSet->getViewFormat($this->field) != "Custom") {
$localData[$this->field] = $lookupvalue;
}
$outVal = $this->localControlsContainer->showDBValue($this->field, $localData, $keylink, $lookupvalue);
$out .= $this->nLookupType == LT_QUERY || $this->linkAndDisplaySame ? $this->cipherer->DecryptField($this->nLookupType == LT_QUERY ? $this->displayFieldName : $this->field, $outVal) : $outVal;
}
return $out;
}
} else {
$found = false;
$strdata = $this->cipherer->MakeDBValue($this->nLookupType == LT_QUERY ? $this->linkFieldName : $this->field, $value, "", "", true);
if ($this->nLookupType == LT_QUERY) {
$strWhere = GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " = " . $strdata;
if (strlen($where)) {
$strWhere .= " and (" . $where . ")";
}
$LookupSQL = $this->lookupQueryObj->toSql(whereAdd($this->lookupQueryObj->m_where->toSql($this->lookupQueryObj), $strWhere));
} else {
$strWhere = $this->pSet->getLWLinkField($this->field) . " = " . $strdata;
if (strlen($where)) {
$strWhere .= " and (" . $where . ")";
}
$LookupSQL = $this->LookupSQL . $strWhere;
}
LogInfo($LookupSQL);
$rsLookup = db_query($LookupSQL, $conn);
if ($lookuprow = db_fetch_numarray($rsLookup)) {
$lookupvalue = $lookuprow[$this->displayFieldIndex];
$found = true;
}
}
if (!$out) {
if ($found && ($this->nLookupType == LT_QUERY || $this->linkAndDisplaySame)) {
$lookupvalue = $this->cipherer->DecryptField($this->nLookupType == LT_QUERY ? $this->displayFieldName : $this->field, $lookupvalue);
}
$localData = $data;
if ($this->pSet->getViewFormat($this->field) != "Custom") {
$localData[$this->field] = $lookupvalue;
}
$out = $this->localControlsContainer->showDBValue($this->field, $localData, $keylink, $lookupvalue);
}
return $out;
}
