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; }