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