}
else {
$("td[id=row" + i + "]").css("background-color","#F4F7FB");
}
}
});
'."\r\n";
$b_includes .= JumpTo();
if (count(GetUserGroups()) < 2 || $_SESSION['webreports']['settings']['status'] != "public" ) {
$b_includes .= '$("td[id=row9]").hide();'."\r\n";
}
if (is_wr_project() || is_wr_custom()) {
$b_includes .= '$("td[id=row1], td[id=row2]").hide();'."\n";
}
if($wr_is_standalone)
$b_includes .= '$("td[id=row11]").hide();'."\n";
if($_SESSION['webreports']['group_fields'][count($_SESSION['webreports']['group_fields'])-1]["cross_table"]=="true")
$b_includes .= '$("#row5,#row6").hide();'."\r\n";
$rLinks = "var rlinks = {};\r\n";
for($ri = 0; $ri < 10; $ri++)
$rLinks.= "rlinks['".$ri."'] = '".GetTableLink("webreport".$ri)."';\r\n";
$b_includes .= '
$("a#a_addsql").fancybox({
$arr_page_order_fields=array();
$arr_page_order_fields["data"]=array();
$arr_not_group_fields=array();
for ( $i=0; $i < count($xml_array['group_fields'])-1; $i++ ) {
$aGroupFields[] = $xml_array['tables'][0]."_".$xml_array['group_fields'][$i]['name'];
}
$aTotFields=array();
foreach ( $inspect_fields as $fld ) {
if(is_wr_db())
$fld_type=WRGetFieldType($fld['table'].".".$fld['name']);
elseif(is_wr_custom())
$fld_type=WRCustomGetFieldType($fld['table'],$fld['name']);
else
$fld_type=GetFieldType($fld['name'],$fld['table']);
if ( !IsBinaryType($fld_type)) {
$aTotFields[] = $fld['table']."_".$fld['name'];
}
}
$ngFieldNames = array_diff( $aTotFields, $aGroupFields );
$arr_alias = array();
foreach ( $aGroupFields as $gr_name ) {
foreach ( $inspect_fields as $fld ) {
function GetGroupDisplay($field, $value) {
global $locale_info, $rpt_array, $tbl, $fields_type;
for ($i = 0; $i < count($rpt_array['group_fields']) - 1; $i++){
$arr = $rpt_array['group_fields'][$i];
foreach ($rpt_array['totals'] as $fld){
if ($arr['name'] == fldname($fld)) {
if (!is_wr_custom())
$ftype = WRGetFieldType($fld['table'] . "." . $fld['name']);
else
$ftype = $fields_type[$fld['name']];
if ($field == $arr['name']) {
if ($arr['int_type'] == 0) {
$prefix = "";
if (!is_wr_db())
$prefix = $rpt_array["tables"][0] . "_";
else
$field = GoodFieldName($field);
if ($rpt_array['totals'][$prefix . $field]['curr'] == "true")
return str_format_currency($value);
else
return $value;
} elseif (IsNumberType($ftype)) {
$start = $value - ($value % $arr['int_type']);
$end = $start + $arr['int_type'];
$prefix = "";
if (!is_wr_db())
$prefix = $rpt_array["tables"][0] . "_";
else
$field = GoodFieldName($field);
if ($rpt_array['totals'][$prefix . $field]['curr'] == "true")
return str_format_currency($start) . " - " . str_format_currency($end);
else
return $start . " - " . $end;
} elseif (IsCharType($ftype)) {
return substr($value, 0, $arr['int_type']);
} elseif (IsDateFieldType($ftype)) {
$tm = db2time($value);
if (!count($tm))
return "";
if ($arr['int_type'] == 1) { // DATE_INTERVAL_YEAR
return $tm[0];
} elseif ($arr['int_type'] == 2) { // DATE_INTERVAL_QUARTER
return $tm[0] . "/Q" . floor(($tm[1] - 1) / 4 + 1);
} elseif ($arr['int_type'] == 3) { // DATE_INTERVAL_MONTH
return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0];
} elseif ($arr['int_type'] == 4) { // DATE_INTERVAL_WEEK
$start = getweekstart($tm);
$end = adddays($start, 6);
return format_shortdate($start) . " - " . format_shortdate($end);
} elseif ($arr['int_type'] == 5) { // DATE_INTERVAL_DAY
return format_shortdate($tm);
} elseif ($arr['int_type'] == 6) { // DATE_INTERVAL_HOUR
$tm[4] = 0;
$tm[5] = 0;
return str_format_datetime($tm);
} elseif ($arr['int_type'] == 7) { // DATE_INTERVAL_MINUTE
$tm[5] = 0;
return str_format_datetime($tm);
} else {
return str_format_datetime($tm);
}
}
}
}
}
}
}
function update_report_totals()
{
$root =& $_SESSION["webreports"];
// ensure all fields in reports are listed in the tables
$tables = getReportTablesList();
if (is_wr_custom()) {
$fields = WRGetFieldsList('');
}
$arr_unset = array();
foreach ($root["totals"] as $idx => $fld) {
if (array_search($fld["table"], $tables) !== false || is_null($fld["table"]) && is_wr_custom()) {
if (!is_wr_custom()) {
$fields = WRGetFieldsList($fld["table"]);
}
if (array_search($fld["name"], $fields) !== false) {
continue;
}
}
// remove $total if found
$arr_unset[] = $idx;
}
foreach ($arr_unset as $idx => $fld) {
unset($root["totals"][$fld]);
}
// ensure all fields appear in the totals
$all_fields = array();
foreach ($tables as $t) {
$fields = WRGetFieldsList($t);
foreach ($fields as $f) {
if (is_wr_db()) {
$all_fields[] = $t . "." . $f;
} else {
$all_fields[] = $f;
}
}
}
// ensure all series fields appear in the totals
foreach ($all_fields as $fieldItem) {
$f = $fieldItem;
$table = "";
$fld = "";
if (is_wr_db()) {
WRSplitFieldName($f, $table, $fld);
} else {
$table = $tables[0];
$fld = $f;
$f = $table . "_" . $f;
}
if (array_key_exists(GoodFieldName($f), $root["totals"])) {
continue;
}
$pSet = new ProjectSettings($table, PAGE_LIST);
$root['totals'][GoodFieldName($f)] = array();
$root['totals'][GoodFieldName($f)]["name"] = $fld;
$root['totals'][GoodFieldName($f)]["table"] = $table;
$root['totals'][GoodFieldName($f)]["label"] = $pSet->label($fld);
$root['totals'][GoodFieldName($f)]["show"] = "true";
$root['totals'][GoodFieldName($f)]["min"] = "false";
$root['totals'][GoodFieldName($f)]["max"] = "false";
$root['totals'][GoodFieldName($f)]["sum"] = "false";
$root['totals'][GoodFieldName($f)]["avg"] = "false";
$root['totals'][GoodFieldName($f)]["curr"] = "false";
$root['totals'][GoodFieldName($f)]["search"] = "";
$root['totals'][GoodFieldName($f)]["view_format"] = GetGenericViewFormat($table, $fld);
$root['totals'][GoodFieldName($f)]["edit_format"] = GetGenericEditFormat($table, $fld);
$root['totals'][GoodFieldName($f)]["display_field"] = $pSet->getDisplayField($fld);
$root['totals'][GoodFieldName($f)]["linkfield"] = $pSet->getLinkField($fld);
$root['totals'][GoodFieldName($f)]["show_thumbnail"] = $pSet->showThumbnail($fld);
$root['totals'][GoodFieldName($f)]["need_encode"] = $pSet->NeedEncode($fld);
$root['totals'][GoodFieldName($f)]["thumbnail"] = $pSet->getStrThumbnail($fld);
$root['totals'][GoodFieldName($f)]["listformatobj_imgwidth"] = $pSet->getImageWidth($fld);
$root['totals'][GoodFieldName($f)]["listformatobj_imgheight"] = $pSet->getImageHeight($fld);
$root['totals'][GoodFieldName($f)]["hlprefix"] = $pSet->getLinkPrefix($fld);
$root['totals'][GoodFieldName($f)]["listformatobj_filename"] = $pSet->getFilenameField($fld);
$root['totals'][GoodFieldName($f)]["lookupobj_lookuptype"] = $pSet->getLookupType($fld);
$root['totals'][GoodFieldName($f)]["editformatobj_lookupobj_customdispaly"] = $pSet->getDisplayField($fld);
$root['totals'][GoodFieldName($f)]["editformatobj_lookupobj_table"] = $pSet->getLookupTable($fld);
$root['totals'][GoodFieldName($f)]["editformatobj_lookupobj_where"] = GetLWWhere($fld, PAGE_REPORT);
}
$_SESSION["webreports"] = $root;
}
function save_sql($type)
{
global $cman;
$sql_query = "";
$sql_where = "";
$sql_order_by = "";
$sql_order_by_preview = "";
$sql_group_by = "";
$root =& $_SESSION[$type];
$_connection = $cman->getForWebReports();
if (is_wr_project()) {
$_connection = $cman->byTable($root['tables'][0]);
}
switch ($type) {
case "webreports":
if (!empty($root['sort_fields'])) {
$sql_order_by .= " \nORDER BY ";
foreach ($root['sort_fields'] as $arr) {
if (is_wr_project()) {
$sql_order_by .= $_connection->AddFieldWrappers($arr['name']);
} else {
$table = "";
$field = "";
WRSplitFieldName($arr['name'], $table, $field);
if ($table) {
$sql_order_by .= $_connection->AddTableWrappers($table) . "." . $_connection->AddFieldWrappers($field);
} else {
$sql_order_by .= $_connection->AddFieldWrappers($field);
}
}
$sql_order_by .= $arr["desc"] == "true" ? " DESC, " : " ASC, ";
}
$sql_order_by = substr($sql_order_by, 0, -2);
}
if (is_wr_custom()) {
$arr = getCustomSQLbyName($root["tables"][0]);
$sql_query = $arr[2];
$sql_query_preview = $arr[2];
$sql_where = "";
$sql_group_by = "";
break;
}
$arr_fields_all = array();
if (!empty($root['totals'])) {
foreach ($root['totals'] as $fld) {
if ($fld["show"] == "true") {
$alias = " as " . $_connection->AddFieldWrappers(GoodFieldName($fld["table"] . "." . $fld["name"]));
$arr_fields_all[] = $_connection->AddTableWrappers($fld["table"]) . "." . $_connection->AddFieldWrappers($fld["name"]) . $alias;
if (!IsBinaryType(WRGetFieldType($fld["table"] . "." . $fld["name"]))) {
$arr_fields_nb[] = $_connection->AddTableWrappers($fld["table"]) . "." . $_connection->AddFieldWrappers($fld["name"]) . $alias;
}
}
}
} else {
$table_name = $root['tables'][0];
$arr_fields = WRGetFieldsList($table_name);
for ($j = 0; $j < count($arr_fields); $j++) {
$arr_fields_all[] = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($arr_fields[$j]);
if (!IsBinaryType(WRGetFieldType($table_name . "." . $arr_fields[$j]))) {
$arr_fields_nb[] = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($arr_fields[$j]);
}
}
}
$sql_query = "";
// $sql_query .= " \nFROM " . AddTableWrappers($root['tables'][0]);
$sql_query .= " \n" . make_from_clause($type);
/*
if ( !empty( $root['table_relations'] ) ){
$arr_relations = array_slice(explode("@END@", $root['table_relations']["relations"]), 0, -1);
foreach ($arr_relations as $rel) {
$arr_parts = explode("@SEP@", $rel);
$sql_query .= $arr_parts[0];
}
}
*/
$sql_query_preview = "SELECT\n" . implode(", \n", $arr_fields_nb) . $sql_query;
$sql_query = "SELECT\n" . implode(", \n", $arr_fields_all) . $sql_query;
if (!empty($root['where_condition'])) {
$sql_where .= " \nWHERE ";
foreach ($root['where_condition'] as $arr) {
WRSplitFieldName($arr['field_opt'], $t, $f);
$fld_name = $_connection->AddTableWrappers($t) . "." . $_connection->AddFieldWrappers($f);
$sql_where .= "( " . $fld_name . $arr['filter_value'];
$sql_where .= $arr['first_or_value'] == "" ? "" : " OR " . $fld_name . $arr['first_or_value'];
$sql_where .= $arr['second_or_value'] == "" ? "" : " OR " . $fld_name . $arr['second_or_value'];
$sql_where .= $arr['third_or_value'] == "" ? "" : " OR " . $fld_name . $arr['third_or_value'];
$sql_where .= " ) AND ";
}
$sql_where = substr($sql_where, 0, -5);
}
break;
case "webcharts":
if (is_wr_custom()) {
$arr = getCustomSQLbyName($root["tables"][0]);
$sql_query = $arr[2];
$sql_query_preview = $arr[2];
$sql_where = "";
$sql_order_by = "";
$sql_order_by_preview = "";
$sql_group_by = "";
break;
}
$table_name = $root['tables'][0];
$arr_join_tables = getChartTablesList();
foreach ($arr_join_tables as $tbl) {
$arr_fields_join = WRGetFieldsList($tbl);
for ($j = 0; $j < count($arr_fields_join); $j++) {
$arr_fields[] = $arr_fields_join[$j];
$arr_fields_all[] = $_connection->AddTableWrappers($tbl) . "." . $_connection->AddFieldWrappers($arr_fields_join[$j]);
$arr_label[$_connection->AddTableWrappers($tbl) . "." . $_connection->AddTableWrappers($arr_fields_join[$j])] = $tbl . "_" . $arr_fields_join[$j];
}
}
$sql_query = "SELECT\n";
$sql_query_preview = "SELECT\n";
//
if (!empty($root['parameters'])) {
if (is_groupby_chart()) {
foreach ($root['parameters'] as $idx => $arr) {
if ($arr["name"] == "") {
continue;
}
$fld = $_connection->AddTableWrappers($arr["table"]) . "." . $_connection->AddFieldWrappers($arr["name"]);
if ($arr["agr_func"] != "") {
$fld = $arr["agr_func"] . "(" . $fld . ")";
if ($idx < count($arr_fields) - 1 && $arr["label"]) {
// adding alias to Data series field
$fld .= " AS " . $_connection->AddFieldWrappers($arr["agr_func"] . "_" . $arr_label[$_connection->AddTableWrappers($arr["table"]) . "." . $_connection->AddFieldWrappers($arr["name"])]);
} else {
if ($arr["agr_func"] != "GROUP BY") {
// adding alias to Label field
$fld .= " AS " . $_connection->AddFieldWrappers($arr["agr_func"] . "_" . $arr_label[$_connection->AddTableWrappers($arr["table"]) . "." . $_connection->AddFieldWrappers($arr["name"])]);
}
}
} else {
$fld .= " AS " . $_connection->AddFieldWrappers($arr_label[$_connection->AddTableWrappers($arr["table"]) . "." . $_connection->AddFieldWrappers($arr["name"])]);
}
$sql_query .= $fld . ", \n";
}
for ($i = 0; $i < count($root['group_by_condition']) - 1; $i++) {
$arr = $root['group_by_condition'][$i];
if ($arr["field_opt"] == "" || $arr["group_by_value"] == -1) {
continue;
}
$ttable = "";
$tfield = "";
WRSplitFieldName($arr["field_opt"], $ttable, $tfield);
$fld = $_connection->AddTableWrappers($ttable) . "." . $_connection->AddFieldWrappers($tfield);
if ($arr["group_by_value"] != -1 && $arr["group_by_value"] != "GROUP BY") {
$fld = $arr["group_by_value"] . "(" . $fld . ")" . " AS " . $_connection->AddFieldWrappers($arr["group_by_value"] . "_" . $arr_label[$fld]);
} else {
if ($arr["group_by_value"] == "GROUP BY") {
$fld .= " AS " . $_connection->AddFieldWrappers($arr_label[$arr_fields_all[$i]]);
}
}
$sql_query_preview .= $fld . ", \n";
}
} else {
for ($j = 0; $j < count($arr_fields_all); $j++) {
if (!IsBinaryType(WRGetFieldType($arr_fields_all[$j]))) {
$sql_query .= $arr_fields_all[$j] . " AS " . $_connection->AddFieldWrappers($arr_label[$arr_fields_all[$j]]) . ", \n";
$sql_query_preview .= $arr_fields_all[$j] . " AS " . $_connection->AddFieldWrappers($arr_label[$arr_fields_all[$j]]) . ", \n";
}
}
}
$sql_query = substr($sql_query, 0, -3);
$sql_query_preview = substr($sql_query_preview, 0, -3);
}
$sql_query .= " \n" . make_from_clause($type);
$sql_query_preview .= " \n" . make_from_clause($type);
//
$sql_where = "";
if (!empty($root['group_by_condition'][0])) {
for ($i = 0; $i < count($root['group_by_condition']) - 1; $i++) {
$arr = $root['group_by_condition'][$i];
$fld = array(0 => "", 1 => "");
WRSplitFieldName($arr['field_opt'], $t, $f);
$fld_name = $_connection->AddTableWrappers($t) . "." . $_connection->AddFieldWrappers($f);
if ($arr['filter_value'] == "") {
continue;
}
if (strlen($sql_where)) {
$sql_where .= " AND ";
}
$sql_where .= "(" . $fld_name . $arr['filter_value'];
$sql_where .= $arr['first_or_value'] == "" ? "" : " OR " . $fld_name . $arr['first_or_value'];
$sql_where .= $arr['second_or_value'] == "" ? "" : " OR " . $fld_name . $arr['second_or_value'];
$sql_where .= $arr['third_or_value'] == "" ? "" : " OR " . $fld_name . $arr['third_or_value'];
$sql_where .= ")";
}
if (strlen($sql_where)) {
$sql_where = " WHERE " . $sql_where;
}
$group_by_clause = "";
$having_clause = "";
if (is_groupby_chart()) {
for ($i = 0; $i < count($root['group_by_condition']) - 1; $i++) {
$arr = $root['group_by_condition'][$i];
$table_name = "";
$field_name = "";
WRSplitFieldName($arr['field_opt'], $table_name, $field_name);
$fld_name = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($field_name);
if ($arr['group_by_value'] != "-1") {
if ($arr['group_by_value'] == "GROUP BY") {
$group_by_clause .= $fld_name . ", ";
}
if (!empty($arr['having_value'])) {
if ($arr["group_by_value"] != "GROUP BY" && $arr["group_by_value"] != "-1") {
$fld_name = $arr["group_by_value"] . "(" . $fld_name . ")";
}
$having_clause .= $fld_name . " " . $arr['having_value'] . " AND ";
}
}
}
if ($group_by_clause != "") {
$group_by_clause = "\nGROUP BY " . substr($group_by_clause, 0, -2);
}
if ($having_clause != "") {
$having_clause = "\nHAVING " . substr($having_clause, 0, -5);
}
$sql_group_by = $group_by_clause . $having_clause;
}
// calc order by clause
$arr_order = array();
for ($i = 0; $i < count($root['group_by_condition']) - 1; $i++) {
$arr = $root['group_by_condition'][$i];
if ($arr["sort_dir"] == "-1" || is_groupby_chart() && $arr['group_by_value'] == -1) {
continue;
}
WRSplitFieldName($arr['field_opt'], $table_name, $field_name);
if (is_groupby_chart() && $arr['group_by_value'] != -1) {
$fld_name = $arr["group_by_value"] . "(" . $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($field_name) . ")";
$fld_name_preview = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($field_name);
} else {
$fld_name_preview = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($field_name);
$fld_name = $_connection->AddTableWrappers($table_name) . "." . $_connection->AddFieldWrappers($field_name);
}
$arr_order[(int) $arr["sort_order"]] = array("field" => $fld_name, "field_preview" => $fld_name_preview, "dir" => $arr["sort_dir"]);
}
if (count($arr_order)) {
$arr_sortorders = array_keys($arr_order);
sort($arr_sortorders);
foreach ($arr_sortorders as $i) {
if (strlen($sql_order_by_preview)) {
$sql_order_by_preview .= ", ";
}
if (strlen($sql_order_by)) {
$sql_order_by .= ", ";
}
$sql_order_by_preview .= $arr_order[$i]["field_preview"] . " " . $arr_order[$i]["dir"];
$sql_order_by .= $arr_order[$i]["field"] . " " . $arr_order[$i]["dir"];
}
$sql_order_by_preview = "\nORDER BY " . $sql_order_by_preview;
$sql_order_by = "\nORDER BY " . $sql_order_by;
}
}
//$sql_query_preview = $sql_query;
break;
default:
break;
}
$_SESSION[$type]['sql'] = $sql_query;
$_SESSION[$type]['sql_preview'] = $sql_query_preview;
$_SESSION[$type]['where'] = $sql_where;
$_SESSION[$type]['order_by'] = $sql_order_by;
$_SESSION[$type]['order_by_preview'] = $sql_order_by_preview;
$_SESSION[$type]['group_by'] = $sql_group_by;
$_SESSION["object_sql"] = $sql_query;
}
<input type="text" id="editformatobj_lookupobj_customdispaly_' . $cnt . '" name="editformatobj_lookupobj_customdispaly_' . $cnt . '" value="' . $pSet->getLWDisplayField( $arr['field'] ) . '" >
<input type="text" id="editformatobj_lookupobj_table_' . $cnt . '" name="editformatobj_lookupobj_table_' . $cnt . '" value="' . $pSet->getLookupTable( $arr['field'] ) . '" >
<input type="text" id="editformatobj_lookupobj_where_' . $cnt . '" name="editformatobj_lookupobj_where_' . $cnt . '" value="' . GetLWWhere($arr['field'],PAGE_REPORT) . '" >
</td>
</tr>';
}
$arr = $_SESSION['webreports']['totals'];
if ( !empty( $arr ) ) {
$b_includes .= '<script type="text/javascript">
$(document).ready(function(){'."\n";
foreach ($arr as $id => $totals) {
if(!is_wr_custom())
$b_includes .= "$('tr[id=".$id."]', '#trt').find('td').eq(1).text('" . jsreplace($totals["table"].".".$totals["name"]) . "');"."\n";
$b_includes .= "$('tr[id=".$id."]', '#trt').find('td').eq(2).find('input').val('" . jsreplace($totals["label"]) . "');"."\n";
$total_name=$totals["name"];
if(is_wr_db())
$total_name=$totals["table"].".".$totals["name"];
if ( !in_array($total_name, $aSelGroupFields) || $is_crosstable_report=="true") {
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(3).find("input").attr("checked",' . ($totals["show"] == "true" ? "true" : "false") . ');'."\n";
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(4).find("input").attr("checked",' . ($totals["min"] == "true" ? "true" : "false") . ');'."\n";
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(5).find("input").attr("checked",' . ($totals["max"] == "true" ? "true" : "false") . ');'."\n";
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(6).find("input").attr("checked",' . ($totals["sum"] == "true" ? "true" : "false") . ');'."\n";
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(7).find("input").attr("checked",' . ($totals["avg"] == "true" ? "true" : "false") . ');'."\n";
$b_includes .= '$("tr[id='.$id.']", "#trt").find("td").eq(8).find("input").attr("checked",' . ($totals["curr"] == "true" ? "true" : "false") . ');'."\n";
}
}
$b_includes .= '
