$xml = new xml(); $chrt_strXML = ""; if (checkTableName(postvalue("chartname"), titCHART)) { include_once "include/" . postvalue("chartname") . "_variables.php"; $chrt_strXML = GetChartXML(postvalue("chartname")); $chrt_array = $xml->xml_to_array($chrt_strXML); $_SESSION["webobject"]["table_type"] = "project"; $_SESSION["object_sql"] = ""; } $webchart = false; if (!$chrt_strXML) { $sessPrefix = "webchart" . postvalue('cname'); $chrt_strXML = LoadSelectedChart(postvalue('cname')); $webchart = true; $chrt_array = $xml->xml_to_array($chrt_strXML); if (is_wr_project()) { include_once "include/" . $chrt_array['settings']['short_table_name'] . "_variables.php"; } } $param = array(); $param["webchart"] = $webchart; $param["showDetails"] = postvalue('showDetails'); $param["chartPreview"] = postvalue('chartPreview'); $param["hasFlash"] = postvalue('hasFlash') == "true"; $param["dashChart"] = postvalue('dashChart'); $param["pageId"] = postvalue('pageId'); if ($param["dashChart"]) { $param["dashTName"] = postvalue('dashTName'); $param["dashElementName"] = postvalue('dashElName'); } if ($webchart) {
$tbl=$tbl_name["sqlname"]; $isStorProc=$tbl_name["isStorProc"]; } else { $tbl=$tbl_name; $isStorProc=false; } if(!$isStorProc) { $selected = ""; if ( !empty( $_SESSION['webreports']['tables'] ) ){ if ( in_array( $tbl, $_SESSION['webreports']['tables'] ) ) $selected = "selected"; } $tables .= '<option ' . $selected . ' value="' . runner_htmlspecialchars($tbl) . '">'.(!is_wr_project() ? $tbl : getCaptionTable($tbl)) . (is_wr_project() ? (getCaptionTable($tbl)!=$tbl ? " (".$tbl.")" : "" ) : "").'</option>'."\r\n"; } } if (@$_SESSION['webreports']['settings']['title'] != "") { $xt->assign("report_title",", "."Title".": ".@$_SESSION['webreports']['settings']['title']); } else { $xt->assign("report_title",""); } $xt->assign("tables", $tables); if(!count($arr_tables_db) && !count($arr_tables_project) || !count($arr_tables_custom) && !count($arr_tables_project) || !count($arr_tables_custom) && !count($arr_tables_db)) $xt->assign("radio_style","style='display:none';"); if(count($arr_tables_db)) $xt->assign("view_radio_db",true);
function WRCustomGetFieldType($table, $field) { global $conn; if (is_wr_project()) { $pSet = new ProjectSettings($_SESSION['webreports']['tables'][0]); $type = $pSet->getFieldType($field); if ($type) { return $type; } } if (is_wr_db()) { global $wr_is_standalone; if (!$wr_is_standalone) { global $dal; if ($dal->Table($table)) { return $dal->GetFieldType($table, $field); } } return dbinfoFieldsType($table, $field); } if (is_wr_custom()) { $res = ""; $sql = $_SESSION["object_sql"]; if ($sql) { //The default connection is temporary used #9875 $connection = getDefaultConnection(); $arr = $connection->getFieldsList($sql); foreach ($arr as $val) { if ($val["fieldname"] == $field) { $res = $val["type"]; } } } return $res; } }
$reportName = ""; if (postvalue('rname')) $reportName = runner_htmlspecialchars(postvalue('rname')); else $reportName = runner_htmlspecialchars(@$_SESSION['webreports']['settings']['name']); $xt->assign("reportName", $reportName); if (postvalue("q")) $xt->assign("searchClause", "&q=" . postvalue("q")); $xt->assign("reportNamejs", jsreplace(postvalue('rname'))); $xt->assign("reportTitle", runner_htmlspecialchars($rpt_array['settings']['title'])); $xt->assign("shortTableName", runner_htmlspecialchars($rpt_array['short_table_name'])); $xt->assign("shortTableNamejs", jsreplace($rpt_array['short_table_name'])); $searchType = (!is_wr_project()) ? GetTableLink("dsearch") : GetTableLink(runner_htmlspecialchars($rpt_array['short_table_name']), "search"); $xt->assign("searchHref", "href='" . $searchType . "?rname=" . $reportName . "'"); $xt->assign("ext", "php"); if (postvalue("param") == "preview") $preview = "_preview"; switch ($rpt_array['miscellaneous']['type']){ case "stepped" : $templatefile = GetTemplateName("", "dreport" . $preview); break; case "block" : $templatefile = GetTemplateName("", "dreport1" . $preview); break; case "outline" :
} else { $("td[id=row" + i + "]").css("background-color","#F4F7FB"); } } }); '."\r\n"; $b_includes .= JumpTo(); if (count(GetUserGroups()) < 2 || isset( $_SESSION['webcharts']['settings']['status'] ) && $_SESSION['webcharts']['settings']['status'] == "private" ) { $b_includes .= '$("td[id=row7]").hide();'."\r\n"; } if (is_wr_project() || is_wr_custom()) { $b_includes .= '$("td[id=row1], td[id=row2]").hide();'."\r\n"; } if($wr_is_standalone) $b_includes .= '$("td[id=row9]").hide();'."\n"; if ($_SESSION['webcharts']['settings']['title'] == "") { $b_includes .= ' for (var i=2; i<=7; i++){ $("td[id=row" + i + "]").hide(); }; '."\r\n"; } $rLinks = "var rlinks = {};\r\n"; for($ri = 0; $ri < 10; $ri++)
function make_from_clause($type) { global $cman; $accessMode = GetDatabaseType() == nDATABASE_Access; $root =& $_SESSION[$type]; $_connection = $cman->getForWebReports(); if (is_wr_project()) { $_connection = $cman->byTable($root['tables'][0]); } $ret = $_connection->AddTableWrappers($root['tables'][0]); $fullouter = ""; $firstJoin = true; if (is_array($root["table_relations"]["relat"])) { foreach ($root["table_relations"]["relat"] as $r) { if (trim($r["rel_type"]) == "FULL OUTER JOIN") { $fullouter .= "\n," . $_connection->AddTableWrappers($r["right_table"]); continue; } if ($accessMode && !$firstJoin) { $ret = "(" . $ret . ")"; } $firstJoin = false; $ret .= "\n" . $r["rel_type"] . " " . $_connection->AddTableWrappers($r["right_table"]) . " ON "; $joinon = ""; foreach ($r["left_fields"] as $i => $f) { if (strlen($joinon)) { $joinon .= " AND "; } $joinon .= $_connection->AddTableWrappers($r["left_table"]) . "." . $_connection->AddFieldWrappers($r["left_fields"][$i]); $joinon .= "="; $joinon .= $_connection->AddTableWrappers($r["right_table"]) . "." . $_connection->AddFieldWrappers($r["right_fields"][$i]); } $ret .= $joinon; } } return "FROM " . $ret . $fullouter; }
if($("div.center_div").find("object").length==0) $("#ExpPDF").hide(); $(div).appendTo("div.center_div"); } } }); </script>'; if ($_SESSION["back_to_menu"]) { $xt->assign("back_to_menu", true); } else { $xt->assign("back_to_menu", false); } $xt->assign("wr_pagestylepath", $wr_pagestylepath); $xt->assign("chart_block", true); $xt->assign("chart_constructor", $show_dchart); $xt->assign("load_flash_player", $load_flash_player); if (!IsStoredProcedure($chrt_array['sql'])) { $xt->assign("testAdvSearch", testAdvSearch($chrt_array['tables'][0])); } else { $xt->assign("testAdvSearch", false); } $chart_name_js = jsreplace(postvalue('cname')); $xt->assign("chart_name_js", $chart_name_js); $xt->assign("chart_title", runner_htmlspecialchars($chrt_array['title'])); $xt->assign("short_table_name", runner_htmlspecialchars($chrt_array['settings']['short_table_name'])); $xt->assign("short_table_name_js", jsreplace($chrt_array['settings']['short_table_name'])); $xt->assign("ext", "php"); $searchType = !is_wr_project() ? GetTableLink("dsearch") : GetTableLink(runner_htmlspecialchars($chrt_array['settings']['short_table_name']), "search"); $xt->assign("searchHref", "href='" . $searchType . "?cname=" . $chart_name_js . "'"); $templatefile = GetTemplateName("", "dchart"); $xt->display($templatefile);
function save_sql($type) { $sql_query = ""; $sql_where = ""; $sql_order_by = ""; $sql_order_by_preview=""; $sql_group_by = ""; $root=&$_SESSION[$type]; 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 .= AddFieldWrappers($arr['name']); else { $table=""; $field=""; WRSplitFieldName($arr['name'],$table,$field); if($table) $sql_order_by .= AddTableWrappers($table).".".AddFieldWrappers($field); else $sql_order_by .= 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 " . AddFieldWrappers(GoodFieldName($fld["table"].".".$fld["name"])); $arr_fields_all[] = AddTableWrappers($fld["table"]).".".AddFieldWrappers($fld["name"]).$alias; if(!IsBinaryType(WRGetFieldType($fld["table"].".".$fld["name"]))) $arr_fields_nb[]=AddTableWrappers($fld["table"]).".".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[] = AddTableWrappers($table_name).".".AddFieldWrappers($arr_fields[$j]); if(!IsBinaryType(WRGetFieldType($table_name.".".$arr_fields[$j]))) $arr_fields_nb[]=AddTableWrappers($table_name).".".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 = AddTableWrappers($t).".".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[] = AddTableWrappers($tbl).".".AddFieldWrappers($arr_fields_join[$j]); $arr_label[AddTableWrappers($tbl).".".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=AddTableWrappers($arr["table"]).".".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 ".AddFieldWrappers($arr["agr_func"]."_".$arr_label[AddTableWrappers($arr["table"]).".".AddFieldWrappers($arr["name"])]); } else if($arr["agr_func"]!="GROUP BY") { // adding alias to Label field $fld.=" AS ".AddFieldWrappers($arr["agr_func"]."_".$arr_label[AddTableWrappers($arr["table"]).".".AddFieldWrappers($arr["name"])]); } } else $fld.=" AS ".AddFieldWrappers($arr_label[AddTableWrappers($arr["table"]).".".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=AddTableWrappers($ttable).".".AddFieldWrappers($tfield); if($arr["group_by_value"]!=-1 && $arr["group_by_value"]!="GROUP BY") { $fld = $arr["group_by_value"]."(".$fld.")"." AS ".AddFieldWrappers($arr["group_by_value"]."_".$arr_label[$fld]); } else if($arr["group_by_value"]=="GROUP BY") $fld.=" AS ".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 ".AddFieldWrappers($arr_label[$arr_fields_all[$j]]).", \n"; $sql_query_preview.= $arr_fields_all[$j]." AS ".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 = AddTableWrappers($t).".".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 = AddTableWrappers($table_name).".".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"]."(".AddTableWrappers($table_name).".".AddFieldWrappers($field_name).")"; $fld_name_preview = AddTableWrappers($table_name).".".AddFieldWrappers($field_name); } else { $fld_name_preview = AddTableWrappers($table_name).".".AddFieldWrappers($field_name); $fld_name = AddTableWrappers($table_name).".".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; }
function WRCustomGetFieldType($table,$field) { global $conn; if(is_wr_project()) { $pSet = new ProjectSettings($_SESSION['webreports']['tables'][0]); $type = $pSet->getFieldType($field); if($type) return $type; } if(is_wr_db()) { global $wr_is_standalone; if(!$wr_is_standalone) { global $dal; if($dal->Table($table)) return $dal->GetFieldType($table,$field); } return dbinfoFieldsType($table,$field); } if(is_wr_custom()) { $res=""; $sql=$_SESSION["object_sql"]; if($sql) { $arr=db_getfieldslist($sql); foreach($arr as $val) if($val["fieldname"]==$field) $res=$val["type"]; } return $res; } }