kenken999/php
0
1<?php2ini_set("display_errors","1");3ini_set("display_startup_errors","1");4include("include/dbcommon.php");5header("Expires: Thu, 01 Jan 1970 00:00:01 GMT");6 7include("include/reportfunctions.php");8 9// CSRF protection10if( !isPostRequest() )11 return;12 13if( !Security::getUserName() )14{15 $_SESSION["MyURL"]=$_SERVER["SCRIPT_NAME"]."?".$_SERVER["QUERY_STRING"];16 header("Location: ".GetTableLink("login", "", "message=expired"));17 return;18}19 20if ( isset( $_POST['str_xml'] ))21{22 $arr = my_json_decode(postvalue('str_xml'));23 if($arr["table_type"])24 {25 $_SESSION["webobject"]["table_type"]=$arr["table_type"];26 }27}28 29$xml = new xml();30 31if(isset( $_POST['save'] ))32 $save_name=$_SESSION["webobject"]["name"];33 34if($_POST["web"])35 $root=&$_SESSION[$_POST['web']];36 37 38if ( isset( $_POST['str_xml'] ) && isset( $_POST['web'] ) && !isset( $_POST['save'] ) )39{40 41 42 $arr = my_json_decode(DecodeUTF8(postvalue('str_xml')));43 $parameterCount = $arr["parameters"] 44 ? count( $arr["parameters"] )45 : 0;46 if( $parameterCount < 2 && $_POST['web']=='webcharts' && $_POST['name']=='parameters')47 {48 echo "You must select at least one series";49 return;50 }51 52 $root=&$_SESSION[$_POST['web']];53 54 if ( $_POST['web'] == "webreports" )55 {56 Check_Crosstable_Group($arr,isset( $_POST['save']));57 $is_crosstable=$root["group_fields"][pre8count($root["group_fields"])-1]["cross_table"];58 Check_Crosstable_Totals($arr,$is_crosstable);59 }60 61 $rt = @$root['tables'][0];62 $ttype = @$root['table_type'];63 64 foreach ($arr as $key => $val) 65 {66 $root[$key] = $val;67 }68 69 if(is_wr_project()) 70 include("include/" . GetTableURL($root['tables'][0]) . "_variables.php");71 if ( $_POST['web'] == "webcharts" )72 {73 if(!is_wr_project() && (array_key_exists("table_relations", $arr) || array_key_exists("group_by_condition", $arr)))74 {75 update_chart_group_by_condition();76 update_chart_parameters();77 }78 }79 80 if ( $_POST['web'] == "webreports" )81 {82 if(!is_wr_project() && array_key_exists("table_relations", $arr))83 {84 update_report_group_fields();85 update_report_totals();86 update_report_sort_fields();87 Check_Crosstable_Group($arr,isset( $_POST['save']));88 }89 if(array_key_exists("group_fields", $arr) || array_key_exists("sort_fields", $arr))90 {91 update_report_sort_fields();92 }93 }94 95 if (array_key_exists("tables", $arr)) 96 {97 if(is_wr_custom())98 {99 $arr=getCustomSQLbyName($root["tables"][0]);100 $sqlcontent=$arr[2];101 global $cman;102 $conn = $cman->getForWebReports();103 $qResult=$conn->query($sqlcontent);104 $rs = $qResult->fetchAssoc();105 if(!$rs)106 {107 echo $errstr;108 exit();109 }110 else111 {112 $_SESSION["customSQL"]=$sqlcontent;113 $_SESSION["idSQL"]=$arr[0];114 $_SESSION["nameSQL"]=$arr[1];115 $_SESSION["object_sql"]=$sqlcontent;116 }117 }118 if ( !isset($root['settings']) ) 119 {120 if ( $_POST['web'] == "webreports" )121 {122 comlete_report_session_default_values();123 save_sql("webreports");124 $str_xml = $xml->array_to_xml( $root );125 wrSaveEntity( WR_REPORT, $root['settings']['name'],$root['settings']['name'], $root['settings']['title'], $root['settings']['status'], $str_xml, false);126 }127 elseif ( $_POST['web'] == "webcharts" )128 {129 comlete_chart_session_default_values();130 save_sql("webcharts");131 $str_xml = $xml->array_to_xml( $root );132 wrSaveEntity( WR_CHART, $root['settings']['name'],$root['settings']['name'], $root['settings']['title'], $root['settings']['status'], $str_xml, false );133 }134 }135 elseif ($root['tables'][0] != $rt || $root['table_type']!=$ttype)136 {137 if($_POST['web']=="webreports")138 {139 unset($root['totals']);140 unset($root['group_fields']);141 unset($root['sort_fields']);142 unset($root['table_relations']);143 unset($root['where_condition']);144 comlete_report_session_default_values(true);145 save_sql("webreports");146 }147 elseif ( $_POST['web'] == "webcharts" )148 {149 unset($root['table_relations']);150 unset($root['group_by']); 151 unset($root['parameters']);152 unset($root['appearance']);153 unset($root['type']);154 comlete_chart_session_default_values(true);155 save_sql("webcharts");156 }157 }158 }159 else160 {161 if($_POST['web']=="webreports")162 save_sql("webreports");163 if($_POST['web']=="webcharts")164 save_sql("webcharts");165 }166 echo "OK";167}168elseif ( isset( $_POST['str_xml'] ) && isset( $_POST['web'] ) && isset( $_POST['save'] ) )169{170 $arr = my_json_decode(DecodeUTF8(postvalue('str_xml')));171 172 $parameterCount = $arr["parameters"] 173 ? count( $arr["parameters"] )174 : 0;175 if( $parameterCount < 2 && $_POST['web']=='webcharts' && $_POST['name']=='parameters')176 {177 echo "You must select at least one series";178 return;179 }180 181 if ( $_POST['web'] == "webreports" )182 {183 Check_Crosstable_Group($arr,isset( $_POST['save']));184 $is_crosstable=$root["group_fields"][pre8count($root["group_fields"])-1]["cross_table"];185 Check_Crosstable_Totals($arr,$is_crosstable);186 }187 $saveas=false;188 if(isset($_POST['saveas']))189 $saveas=true;190 191 foreach ($arr as $key => $val) 192 {193 $root[$key] = $val;194 } 195 if(is_wr_project()) 196 include("include/" . GetTableURL($root['tables'][0]) . "_variables.php");197 if ( $_POST['web'] == "webreports" )198 {199 $root['owner'] = Security::getUserName();200 $root['table_name'] = $root['tables'][0];201 $root['short_table_name'] = GetTableURL( $root['tables'][0] );202 if($_POST['save']==1)203 $_SESSION['webreports']['tmp_active'] = "";204 if(!is_wr_project() && array_key_exists("table_relations", $arr))205 {206 update_report_group_fields();207 update_report_totals();208 update_report_sort_fields();209 Check_Crosstable_Group($arr,isset( $_POST['save']));210 }211 if(array_key_exists("group_fields", $arr) || array_key_exists("sort_fields", $arr))212 {213 update_report_sort_fields();214 }215 216 save_sql("webreports");217 218 $str_xml = $xml->array_to_xml( $root );219 wrSaveEntity( WR_REPORT, $save_name, $root['settings']['name'], $root['settings']['title'], $root['settings']['status'], $str_xml, $saveas );220 }221 elseif ( $_POST['web'] == "webcharts" )222 {223 $root['settings']['owner'] = Security::getUserName();224 $root['settings']['table_name'] = $root['tables'][0];225 $root['settings']['short_table_name'] = GetTableURL( $root['tables'][0] );226 if($_POST['save']==1)227 $_SESSION['webcharts']['tmp_active'] = "";228 if(!is_wr_project() && (array_key_exists("table_relations", $arr) || array_key_exists("group_by_condition", $arr)))229 {230 update_chart_group_by_condition();231 update_chart_parameters();232 }233 save_sql("webcharts");234 235 $str_xml = $xml->array_to_xml( $root );236 wrSaveEntity( WR_CHART, $save_name, $root['settings']['name'], $root['settings']['title'], $root['settings']['status'], $str_xml, $saveas );237 }238 239 echo "OK";240}241elseif ( isset( $_POST['del'] ))242{243 $ename = postvalue('name');244 if( pre8count( GetUserGroups() ) > 1 ) {245 $rpt = wrGetEntityRecord( $ename, $_POST['web'] == "webreports" ? WR_REPORT : WR_CHART );246 247 $entity = wrGetContent( $rpt["rpt_content"] );248 $permissions = wrGetEntityPermissions( $entity );249 250 if ( $rpt["rpt_owner"] != Security::getUserName() || $rpt["rpt_owner"] == "" || $permissions["view"] == 0 ) {251 if ( $_POST['web'] == "webreports" )252 echo "<p>"."You don't have permissions to delete this report"."</p>";253 else254 echo "<p>"."You don't have permissions to delete this chart"."</p>";255 exit();256 }257 }258 wrDeleteEntity( $ename, $_POST['web'] == "webreports" ? WR_REPORT : WR_CHART );259 echo "OK";260}261 262function comlete_report_session_default_values($isedit="") {263 $root=&$_SESSION["webreports"];264 $table = $root['tables'][0];265 $arr_fields = WRGetNBFieldsList($table);266 $arr_fields_all = WRGetFieldsList($table);267 268 $gfield=$arr_fields[0];269 if(is_wr_db())270 $gfield=$table.".".$arr_fields[0];271 $garrfield = array(272 "name" => $gfield,273 "int_type" => "0",274 "ss" => "true",275 "group_order" => "1",276 "color1" => "FF0000",277 "color2" => "CC0000"278 );279 $garrSummary = array();280 $garrSummary["name"] = "Summary";281 $garrSummary["crosstable"] = "false";282 $garrSummary["sps"] = "true";283 $garrSummary["sds"] = "true";284 $garrSummary["sgs"] = "true";285 $garrSummary["sum_x"] = "true";286 $garrSummary["sum_y"] = "true";287 $garrSummary["sum_total"] = "true";288 289 $root['group_fields'] = array($garrfield,$garrSummary);290 $root['totals'] = array();291 292 $pSet = new ProjectSettings($table);293 foreach ($arr_fields_all as $fld) {294 $root['totals'][GoodFieldName($table.".".$fld)] = array();295 $root['totals'][GoodFieldName($table.".".$fld)]["name"] = $fld;296 $root['totals'][GoodFieldName($table.".".$fld)]["table"] = $table;297 $root['totals'][GoodFieldName($table.".".$fld)]["label"] = $pSet->label($fld);298 $root['totals'][GoodFieldName($table.".".$fld)]["show"] = "true";299 $root['totals'][GoodFieldName($table.".".$fld)]["min"] = "false";300 $root['totals'][GoodFieldName($table.".".$fld)]["max"] = "false";301 $root['totals'][GoodFieldName($table.".".$fld)]["sum"] = "false";302 $root['totals'][GoodFieldName($table.".".$fld)]["avg"] = "false";303 $root['totals'][GoodFieldName($table.".".$fld)]["curr"] = "false";304 $root['totals'][GoodFieldName($table.".".$fld)]["search"] = "";305 $root['totals'][GoodFieldName($table.".".$fld)]["view_format"] = GetGenericViewFormat($table, $fld);306 $root['totals'][GoodFieldName($table.".".$fld)]["edit_format"] = GetGenericEditFormat($table, $fld);307 $root['totals'][GoodFieldName($table.".".$fld)]["display_field"] = $pSet->getDisplayField($fld);308 if(is_wr_project()) 309 $root['totals'][GoodFieldName($table.".".$fld)]["linkfield"] = $pSet->getLinkField($fld);310 else311 $root['totals'][GoodFieldName($table.".".$fld)]["linkfield"] = "";312 313 $root['totals'][GoodFieldName($table.".".$fld)]["show_thumbnail"] = $pSet->showThumbnail($fld);314 $root['totals'][GoodFieldName($table.".".$fld)]["need_encode"] = $pSet->NeedEncode($fld);315 $root['totals'][GoodFieldName($table.".".$fld)]["thumbnail"] = $pSet->getStrThumbnail($fld);316 $root['totals'][GoodFieldName($table.".".$fld)]["listformatobj_imgwidth"] = $pSet->getImageWidth($fld);317 $root['totals'][GoodFieldName($table.".".$fld)]["listformatobj_imgheight"] = $pSet->getImageHeight($fld);318 $root['totals'][GoodFieldName($table.".".$fld)]["hlprefix"] = $pSet->getLinkPrefix($fld);319 $root['totals'][GoodFieldName($table.".".$fld)]["listformatobj_filename"] = $pSet->getFilenameField($fld);320 $root['totals'][GoodFieldName($table.".".$fld)]["lookupobj_lookuptype"] = $pSet->getLookupType($fld);321 $root['totals'][GoodFieldName($table.".".$fld)]["editformatobj_lookupobj_customdispaly"] = $pSet->getDisplayField($fld);322 $root['totals'][GoodFieldName($table.".".$fld)]["editformatobj_lookupobj_table"] = $pSet->getLookupTable($fld);323 $root['totals'][GoodFieldName($table.".".$fld)]["editformatobj_lookupobj_where"] = prepareLookupWhere( $fld, $pSet );324 }325 326 $root['sort_fields'] = array(327 array(328 "name" => $gfield,329 "desc" => "false"330 )331 );332 333 if(!$isedit)334 {335 $root['miscellaneous'] = array(336 "type" => "stepped",337 "print_friendly" => "true",338 "lines_num" => "30"339 );340 341 $root['settings'] = array(342 "name" => GoodFieldName($root['tables'][0]).'_'.CheckLastID( WR_REPORT ),343 "title" => $root['tables'][0].' Report '.CheckLastID( WR_REPORT ),344 "status" => "private"345 );346 $_SESSION["webobject"]["name"]= GoodFieldName($root['tables'][0]).'_'.CheckLastID( WR_REPORT );347 $root['owner'] = Security::getUserName();348 $_SESSION['webreports']['tmp_active'] = "x";349 }350 $root['table_name'] = $root['tables'][0];351 $root['short_table_name'] = GetTableURL($root['tables'][0]);352}353 354function comlete_chart_session_default_values($isedit="") {355 $root=&$_SESSION["webcharts"];356 $table = $root['tables'][0];357 $arr_fields = WRGetNBFieldsList($table);358 $arr_fields_all = WRGetFieldsList($table);359 360 $root['chart_type'] = array(361 "type" => "2d_column"362 );363 364 $arr_data_series=array();365 $arr_label_series=array();366 get_chart_series_fields($arr_data_series,$arr_label_series);367 $datafield=array("field"=>$arr_fields[0],"label"=>WRChartLabel($arr_fields[0]));368 $labelfield=$datafield;369 if(pre8count($arr_label_series))370 {371 $labelfield = $arr_label_series[0];372 $ttable="";373 $tfield="";374 WRSplitFieldName($labelfield["field"],$ttable,$tfield);375 $labelfield["field"]=$tfield;376 }377 if(pre8count($arr_data_series))378 {379 $datafield = $arr_data_series[0];380 $ttable="";381 $tfield="";382 WRSplitFieldName($datafield["field"],$ttable,$tfield);383 $datafield["field"]=$tfield;384 }385 $root['parameters'] = array(386 array(387 "name" => $datafield["field"],388 "ohlcOpen" => $datafield["field"],389 "ohlcClose" => $datafield["field"],390 "ohlcHigh" => $datafield["field"],391 "ohlcLow" => $datafield["field"],392 "table" => $table,393 "agr_func" => "",394 "label" => $datafield["label"]395 ),396 array(397 "name" => $labelfield["field"],398 "table" => $table,399 "agr_func" => "",400 "label" => "undefined"401 )402 );403 404 $root['fields'] = array();405 406 foreach ($arr_fields_all as $fld) {407 $root['fields'][] = array(408 "name" => $fld,409 "label" => WRChartLabel($fld),410 "search" => ""411 );412 }413 414 $root['appearance'] = array();415 $root['appearance']["series_color"] = "FF0000";416 $root['appearance']["color51"] = "";417 $root['appearance']["color52"] = "";418 $root['appearance']["color61"] = "";419 $root['appearance']["color62"] = "";420 $root['appearance']["color71"] = "";421 $root['appearance']["color72"] = "";422 $root['appearance']["color81"] = "";423 $root['appearance']["color82"] = "";424 $root['appearance']["color91"] = "";425 $root['appearance']["color92"] = "";426 $root['appearance']["color101"] = "";427 $root['appearance']["color102"] = "";428 $root['appearance']["color111"] = "";429 $root['appearance']["color112"] = "";430 $root['appearance']["color121"] = "";431 $root['appearance']["color122"] = "";432 $root['appearance']["color131"] = "";433 $root['appearance']["color132"] = "";434 $root['appearance']["color141"] = "";435 $root['appearance']["color142"] = "";436 $root['appearance']["slegend"] = "true";437 $root['appearance']["sgrid"] = "true";438 $root['appearance']["sname"] = "true";439 $root['appearance']["sval"] = "true";440 $root['appearance']["sanim"] = "true";441 $root['appearance']["scur"] = "false";442 $root['appearance']["sstacked"] = "false";443 $root['appearance']["saxes"] = "false";444 $root['appearance']["slog"] = "false";445 $root['appearance']["dec"] = "2";446 $root['appearance']["head"] = $root['tables'][0].' Chart '.CheckLastID( WR_CHART );447 $root['appearance']["foot"] = $root['tables'][0].' Chart '.CheckLastID( WR_CHART );448 $root['appearance']["aqua"] = "0";449 $root['appearance']["cview"] = "0";450 $root['appearance']["is3d"] = "false";451 $root['appearance']["isstacked"] = "false";452 $root['appearance']["cscroll"] = "true";453 $root['appearance']["autoupdate"] = "false";454 $root['appearance']["maxbarscroll"] = "10";455 $root['appearance']["update_interval"] = "5";456 $root['appearance']["accumulstyle"] = "0";457 $root['appearance']["accumulinvert"] = "false";458 $root['appearance']["linestyle"] = "0";459 $root['appearance']["gaugestyle"] = "0";460 461 if(!$isedit)462 {463 $root['settings'] = array(464 "name" => GoodFieldName($root['tables'][0]).'_'.CheckLastID( WR_CHART ),465 "title" => $root['tables'][0].' Chart '.CheckLastID( WR_CHART ),466 "status" => "private",467 "owner" => Security::getUserName(),468 "table_name" => $root['tables'][0],469 "short_table_name" => GetTableURL($root['tables'][0])470 );471 $_SESSION["webobject"]["name"]= GoodFieldName($root['tables'][0]).'_'.CheckLastID( WR_CHART );472 $root['owner'] = Security::getUserName();473 $_SESSION['webcharts']['tmp_active'] = "x";474 }475 else476 {477 $root['settings'] = array(478 "name" => $_SESSION['webcharts']['settings']['name'],479 "title" => $_SESSION['webcharts']['settings']['title'],480 "status" => $_SESSION['webcharts']['settings']['status'],481 "owner" => $_SESSION['webcharts']['settings']['owner'],482 "table_name" => $root['tables'][0],483 "short_table_name" => GetTableURL($root['tables'][0])484 );485 }486 487 $root['table_name'] = $root['tables'][0];488 $root['short_table_name'] = GetTableURL($root['tables'][0]);489 490}491 492function save_sql($type) {493 global $cman;494 $sql_query = "";495 $sql_where = "";496 $sql_order_by = "";497 $sql_order_by_preview="";498 $sql_group_by = "";499 $root=&$_SESSION[$type];500 $customLabels = array();501 $_connection = $cman->getForWebReports();502 if(is_wr_project())503 $_connection = $cman->byTable( $root['tables'][0] );504 switch ($type)505 {506 case "webreports" :507 508 if(!empty($root['sort_fields']))509 {510 $sql_order_by .= " \nORDER BY ";511 foreach ( $root['sort_fields'] as $arr ) 512 {513 if(is_wr_project())514 $sql_order_by .= $_connection->addFieldWrappers($arr['name']);515 else516 {517 $table="";518 $field="";519 WRSplitFieldName($arr['name'],$table,$field);520 if($table)521 $sql_order_by .= $_connection->addTableWrappers($table).".".$_connection->addFieldWrappers($field);522 else523 $sql_order_by .= $_connection->addFieldWrappers($field);524 }525 $sql_order_by .= ($arr["desc"] == "true") ? " DESC, " : " ASC, ";526 }527 $sql_order_by = substr( $sql_order_by, 0, -2);528 }529 530 if(is_wr_custom())531 {532 $arr=getCustomSQLbyName($root["tables"][0]);533 $sql_query=$arr[2];534 $sql_query_preview=$arr[2];535 $sql_where="";536 $sql_group_by="";537 break;538 }539 $arr_fields_all = array();540 if ( !empty( $root['totals'] ) )541 {542 $fldnum =1;543 foreach ( $root['totals'] as $fld ) 544 {545 if ($fld["show"] == "true") 546 {547 $alias = " as " . $_connection->addFieldWrappers("f".$fldnum);548 $arr_fields_all[] = $_connection->addTableWrappers($fld["table"]).".".$_connection->addFieldWrappers($fld["name"]).$alias;549 if(!IsBinaryType(WRGetFieldType($fld["table"].".".$fld["name"])))550 $arr_fields_nb[] = $_connection->addTableWrappers($fld["table"]).".".$_connection->addFieldWrappers($fld["name"]).$alias;551 }552 $fldnum++;553 }554 }555 else 556 {557 $table_name = $root['tables'][0];558 $arr_fields = WRGetFieldsList($table_name);559 for ($j=0; $j < pre8count($arr_fields); $j++) 560 {561 $arr_fields_all[] = $_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($arr_fields[$j]);562 if(!IsBinaryType(WRGetFieldType($table_name.".".$arr_fields[$j])))563 $arr_fields_nb[]=$_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($arr_fields[$j]);564 }565 }566 $sql_query ="";567 $sql_query .= " \n".make_from_clause($type); 568/* 569 if ( !empty( $root['table_relations'] ) ){570 $arr_relations = array_slice(explode("@END@", $root['table_relations']["relations"]), 0, -1);571 foreach ($arr_relations as $rel) {572 $arr_parts = explode("@SEP@", $rel);573 $sql_query .= $arr_parts[0];574 }575 }576*/ 577 $sql_query_preview="SELECT\n".implode(", \n", $arr_fields_nb).$sql_query;578 $sql_query = "SELECT\n".implode(", \n", $arr_fields_all).$sql_query;579 580 if ( !empty( $root['where_condition'] ) ){581 $sql_where .= " \nWHERE ";582 foreach ( $root['where_condition'] as $arr ) {583 WRSplitFieldName($arr['field_opt'],$t,$f);584 $fld_name =$_connection->addTableWrappers($t).".".$_connection->addFieldWrappers($f);585 $sql_where .= "( " . $fld_name . $arr['filter_value'];586 $sql_where .= ( $arr['first_or_value'] == "" ) ? "" : " OR " . $fld_name . $arr['first_or_value'];587 $sql_where .= ( $arr['second_or_value'] == "" ) ? "" : " OR " . $fld_name . $arr['second_or_value'];588 $sql_where .= ( $arr['third_or_value'] == "" ) ? "" : " OR " . $fld_name . $arr['third_or_value'];589 $sql_where .= " ) AND ";590 }591 $sql_where = substr($sql_where,0,-5);592 }593 594 595 break;596 597 case "webcharts" :598 if(is_wr_custom())599 {600 $arr=getCustomSQLbyName($root["tables"][0]);601 $sql_query=$arr[2];602 $sql_query_preview=$arr[2];603 $sql_where="";604 $sql_order_by="";605 $sql_order_by_preview="";606 $sql_group_by="";607 break;608 }609 610 $table_name = $root['tables'][0];611 $arr_join_tables=getChartTablesList();612 $numfield = 1;613 $arr_label=array();614 foreach($arr_join_tables as $tbl)615 {616 $arr_fields_join = WRGetFieldsList($tbl);617 for ($j=0; $j < pre8count($arr_fields_join); $j++) {618 $arr_fields[] = $arr_fields_join[$j];619 $arr_fields_all[] = $_connection->addTableWrappers($tbl).".".$_connection->addFieldWrappers($arr_fields_join[$j]);620 $arr_label[$_connection->addTableWrappers($tbl).".".$_connection->addFieldWrappers($arr_fields_join[$j])] = "f".$numfield;621 $customLabels[$tbl."_".$arr_fields_join[$j]] = "f".$numfield;622 $numfield++;623 } 624 }625 $sql_query = "SELECT\n";626 $sql_query_preview = "SELECT\n";627 //628 if ( !empty( $root['parameters'] ) )629 {630 if(is_groupby_chart())631 {632 foreach ( $root['parameters'] as $idx=>$arr ) 633 {634 if ( $arr["name"] == "" ) 635 continue;636 $fld=$_connection->addTableWrappers($arr["table"]).".".$_connection->addFieldWrappers($arr["name"]);637 if($arr["agr_func"]!="")638 {639 640 $fld = $arr["agr_func"]."(".$fld.")";641 if($idx<pre8count($arr_fields)-1 && $arr["label"])642 {643 // adding alias to Data series field644 $fld.=" AS ".$_connection->addFieldWrappers( $arr_label[$_connection->addTableWrappers($arr["table"]).".".$_connection->addFieldWrappers($arr["name"])] );645 }646 else if($arr["agr_func"]!="GROUP BY")647 {648 // adding alias to Label field649 $fld.=" AS ".$_connection->addFieldWrappers( $arr_label[$_connection->addTableWrappers($arr["table"]).".".$_connection->addFieldWrappers($arr["name"])] );650 }651 652 }653 else 654 $fld.=" AS ".$_connection->addFieldWrappers($arr_label[$_connection->addTableWrappers($arr["table"]).".".$_connection->addFieldWrappers($arr["name"])]);655 $sql_query .= $fld . ", \n";656 }657 for ($i=0; $i < pre8count($root['group_by_condition'])-1; $i++)658 {659 $arr = $root['group_by_condition'][$i];660 if ( $arr["field_opt"] == "" || $arr["group_by_value"]==-1) 661 continue;662 $ttable="";663 $tfield="";664 WRSplitFieldName($arr["field_opt"],$ttable,$tfield);665 $fld=$_connection->addTableWrappers($ttable).".".$_connection->addFieldWrappers($tfield);666 if($arr["group_by_value"]!=-1 && $arr["group_by_value"]!="GROUP BY")667 {668 669 $fld = $arr["group_by_value"]."(".$fld.")"." AS ".$_connection->addFieldWrappers($arr["group_by_value"]."_".$arr_label[$fld]);670 671 }672 else if($arr["group_by_value"]=="GROUP BY")673 $fld.=" AS ".$_connection->addFieldWrappers($arr_label[$arr_fields_all[$i]]);674 675 $sql_query_preview .= $fld . ", \n";676 }677 }678 else679 {680 for ($j=0; $j < pre8count($arr_fields_all); $j++) 681 {682 if(!IsBinaryType(WRGetFieldType($arr_fields_all[$j])))683 {684 $sql_query .= $arr_fields_all[$j]." AS ".$_connection->addFieldWrappers($arr_label[$arr_fields_all[$j]]).", \n";685 $sql_query_preview.= $arr_fields_all[$j]." AS ".$_connection->addFieldWrappers($arr_label[$arr_fields_all[$j]]).", \n";686 }687 }688 }689 $sql_query = substr($sql_query,0,-3); 690 $sql_query_preview = substr($sql_query_preview,0,-3); 691 }692 693 $sql_query .= " \n".make_from_clause($type); 694 $sql_query_preview .= " \n".make_from_clause($type); 695 696 //697 $sql_where="";698 if ( !empty( $root['group_by_condition'][0] ) )699 {700 for ($i=0; $i < pre8count($root['group_by_condition'])-1; $i++) {701 $arr = $root['group_by_condition'][$i];702 $fld = array(0=>"",1=>"");703 WRSplitFieldName($arr['field_opt'],$t,$f);704 $fld_name = $_connection->addTableWrappers($t).".".$_connection->addFieldWrappers($f);705 if ($arr['filter_value'] == "" ) {706 continue;707 }708 if(strlen($sql_where))709 $sql_where.=" AND ";710 $sql_where .= "(" . $fld_name . $arr['filter_value'];711 $sql_where .= ( $arr['first_or_value'] == "" ) ? "" : " OR " . $fld_name . ($arr['first_or_value']);712 $sql_where .= ( $arr['second_or_value'] == "" ) ? "" : " OR " . $fld_name . ($arr['second_or_value']);713 $sql_where .= ( $arr['third_or_value'] == "" ) ? "" : " OR " . $fld_name . ($arr['third_or_value']);714 $sql_where .= ")";715 }716 if(strlen($sql_where))717 $sql_where=" WHERE ".$sql_where;718 719 720 $group_by_clause = "";721 $having_clause = "";722 if(is_groupby_chart())723 {724 for ($i=0; $i < pre8count($root['group_by_condition'])-1; $i++) {725 $arr = $root['group_by_condition'][$i];726 $table_name="";727 $field_name="";728 WRSplitFieldName($arr['field_opt'],$table_name,$field_name);729 $fld_name = $_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($field_name);730 if ( $arr['group_by_value'] != "-1" ) 731 {732 if ( $arr['group_by_value'] == "GROUP BY" )733 {734 $group_by_clause .= $fld_name. ", ";735 }736 if ( !empty( $arr['having_value'] ) )737 {738 if($arr["group_by_value"]!="GROUP BY" && $arr["group_by_value"]!="-1")739 $fld_name = $arr["group_by_value"]."(".$fld_name.")";740 $having_clause .= $fld_name." ".$arr['having_value'] . " AND ";741 } 742 }743 }744 745 if ( $group_by_clause != "" ) 746 {747 $group_by_clause = "\nGROUP BY " . substr($group_by_clause, 0, -2);748 }749 if ( $having_clause != "" ) 750 {751 $having_clause = "\nHAVING " . substr($having_clause, 0, -5);752 } 753 $sql_group_by = $group_by_clause . $having_clause;754 }755 756// calc order by clause757 $arr_order=array();758 for ($i=0; $i < pre8count($root['group_by_condition'])-1; $i++) 759 {760 $arr = $root['group_by_condition'][$i];761 if($arr["sort_dir"]=="-1" || is_groupby_chart() && $arr['group_by_value']==-1)762 continue;763 WRSplitFieldName($arr['field_opt'],$table_name,$field_name);764 if(is_groupby_chart() && $arr['group_by_value']!=-1)765 {766 $fld_name = $arr["group_by_value"]."(".$_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($field_name).")";767 $fld_name_preview = $_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($field_name);768 }769 else770 {771 772 $fld_name_preview = $_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($field_name);773 $fld_name = $_connection->addTableWrappers($table_name).".".$_connection->addFieldWrappers($field_name);774 }775 $arr_order[(int)($arr["sort_order"])]=array("field"=>$fld_name,"field_preview"=>$fld_name_preview,"dir"=>$arr["sort_dir"]);776 }777 778 if(pre8count($arr_order))779 {780 $arr_sortorders = array_keys($arr_order);781 sort($arr_sortorders);782 foreach($arr_sortorders as $i)783 {784 if(strlen($sql_order_by_preview))785 $sql_order_by_preview.=", ";786 if(strlen($sql_order_by))787 $sql_order_by.=", ";788 $sql_order_by_preview.= $arr_order[$i]["field_preview"]." ".$arr_order[$i]["dir"];789 $sql_order_by.= $arr_order[$i]["field"]." ".$arr_order[$i]["dir"];790 }791 $sql_order_by_preview = "\nORDER BY ".$sql_order_by_preview;792 $sql_order_by = "\nORDER BY ".$sql_order_by;793 }794 }795 //$sql_query_preview = $sql_query;796 break;797 default : 798 break; 799 }800 $_SESSION[$type]['customLabels'] = $customLabels;801 $_SESSION[$type]['sql'] = $sql_query;802 $_SESSION[$type]['sql_preview'] = $sql_query_preview;803 $_SESSION[$type]['where'] = $sql_where;804 $_SESSION[$type]['order_by'] = $sql_order_by;805 $_SESSION[$type]['order_by_preview'] = $sql_order_by_preview;806 $_SESSION[$type]['group_by'] = $sql_group_by;807 $_SESSION["object_sql"]=$sql_query;808}809 810// update chart fields (group by) when table is changed811function update_chart_group_by_condition()812{813 $root=&$_SESSION["webcharts"];814// get tables list815 $arr_join_tables=getChartTablesList();816 817 $groupby_found=false;818 if(!pre8count($root["group_by_condition"]))819 {820 $root["group_by_toggle"]=false;821 return;822 }823 $arr_unset = Array();824 825 foreach($root["group_by_condition"] as $idx=>$arr)826 {827 if($idx==="group_by_toggle")828 continue;829// check if the field appears in the tables830 $table="";831 $field="";832 WRSplitFieldName($arr["field_opt"],$table,$field);833 $appear=false;834 foreach($arr_join_tables as $tbl)835 {836 if($tbl!=$table)837 continue;838 $fields=WRGetFieldsList($tbl);839 foreach($fields as $f)840 {841 if($field==$f)842 {843 $appear=true;844 break;845 }846 }847 }848 if(!$appear)849 {850// remove field851 $arr_unset[]=$idx;852 } 853 elseif($arr["group_by_value"]=="GROUP BY")854 $groupby_found=true;855 }856 857 foreach($arr_unset as $idx=>$fld)858 {859 unset($root["group_by_condition"][$fld]);860 }861 862 863 864// compact group_by_condition array865 $keys = array_keys($root["group_by_condition"]);866 $group_by_condition = array();867 $i=0;868 foreach($keys as $k)869 {870 if(is_numeric($k))871 $group_by_condition[$i++]=$root["group_by_condition"][$k];872 else873 $group_by_condition[$k]=$root["group_by_condition"][$k];874 }875 $root["group_by_condition"] = $group_by_condition;876// uncheck group by toggle if needed877 if(!$groupby_found)878 {879 $root["group_by_condition"]["group_by_toggle"]="false";880 foreach($root["group_by_condition"] as $idx=>$arr)881 {882 if($idx==="group_by_toggle")883 continue;884 $root["group_by_condition"][$idx]["group_by_value"]="";885 }886 }887}888 889function update_chart_parameters()890{891 // check if chart parameters are valid892 $root=&$_SESSION["webcharts"];893 $params_saved=0;894 $arr_join_tables=getChartTablesList();895 if( !$root["parameters"] )896 return;897 foreach($root["parameters"] as $idx=>$arr)898 {899 $appear=false;900 if(is_groupby_chart())901 {902// check if the parameter appear in the group_by_condition903 for($i=0;$i<pre8count($root["group_by_condition"])-1;$i++)904 {905 if($root["group_by_condition"][$i]["field_opt"]!=$arr["table"].".".$arr["name"])906 continue;907 if($arr["agr_func"]==$root["group_by_condition"][$i]["group_by_value"] || !$arr["agr_func"] && $root["group_by_condition"][$i]["group_by_value"]=="GROUP BY")908 {909 $appear=true;910 break;911 }912 }913 }914 else915 {916 $root["parameters"][$idx]["agr_func"]="";917 // check if the field appear in the list of tables918 foreach($arr_join_tables as $tbl)919 {920 if($tbl!=$arr["table"])921 continue;922 $fields=WRGetFieldsList($tbl);923 foreach($fields as $f)924 {925 if($f==$arr["name"])926 {927 $appear=true;928 break;929 }930 }931 if($appear)932 break;933 }934 }935 if($appear)936 {937 $params_saved++;938 continue;939 }940// clean up parameter941 $root["parameters"][$idx]["name"]="";942 $root["parameters"][$idx]["table"]="";943 $root["parameters"][$idx]["agr_func"]="";944 $root["parameters"][$idx]["label"]="";945 }946 if(!$root["parameters"][0]["name"])947 {948// add first default parameter949 set_default_chart_parameter(0,false,true);950 if(!$root["parameters"][0]["name"])951 set_default_chart_parameter(0,true,true);952 953 }954 if(!$root["parameters"][pre8count($root["parameters"])-1]["name"])955 {956// add first default parameter957 set_default_chart_parameter(pre8count($root["parameters"])-1,true,false);958 }959}960 961function set_default_chart_parameter($idx,$labelMode,$addLabel)962{963 $root=&$_SESSION["webcharts"];964 $arr_join_tables=getChartTablesList();965 if(is_groupby_chart())966 {967 for($i=0;$i<pre8count($root["group_by_condition"])-1;$i++)968 {969 if(!$root["group_by_condition"][$i]["group_by_value"])970 continue;971 $type=WRGetFieldType($root["group_by_condition"][$i]["field_opt"]);972 $grvalue=$root["group_by_condition"][$i]["group_by_value"];973 if(!$labelMode && (IsNumberType($type) || $grvalue!="GROUP BY") || $labelMode)974 {975 $table="";976 $field="";977 WRSplitFieldName($root["group_by_condition"][$i]["field_opt"],$table,$field);978 $root["parameters"][$idx]["name"]=$field;979 $root["parameters"][$idx]["table"]=$table;980 if($grvalue!="GROUP BY")981 $root["parameters"][$idx]["agr_func"]=$grvalue;982 if($addLabel)983 $root["parameters"][$idx]["label"]=$field;984 break;985 }986 }987 }988 else989 {990 foreach($arr_join_tables as $tbl)991 {992 if(!$labelMode)993 $fields=GetNumberFieldsList($tbl);994 else995 $fields=WRGetNBFieldsList($tbl);996 if( $fields )997 {998 $root["parameters"][$idx]["name"]=$fields[0];999 $root["parameters"][$idx]["table"]=$tbl;1000 $root["parameters"][$idx]["agr_func"]="";1001 if($addLabel)1002 $root["parameters"][$idx]["label"]=WRChartLabel($tbl.".".$fields[0]);1003 break;1004 }1005 }1006 }1007}1008 1009function update_report_group_fields()1010{1011// ensure all group fields are listed in the tables 1012 $root=&$_SESSION["webreports"];1013// ensure all fields in reports are listed in the tables 1014 $tables = getReportTablesList();1015 $changed=false;1016 $arr_unset = Array();1017 foreach($root["group_fields"] as $idx=>$fld)1018 {1019 $table="";1020 $field="";1021 if($fld["name"] == "Summary")1022 continue;1023 1024 if(is_wr_db())1025 WRSplitFieldName($fld["name"],$table,$field);1026 else1027 {1028 $field=$fld["name"];1029 $table=$root['tables'][0];1030 }1031 if(array_search($table,$tables)!==false)1032 {1033 $fields=WRGetFieldsList($table);1034 if(array_search($field,$fields)!==false)1035 continue;1036 }1037// remove $total if found1038 $arr_unset[]=$idx;1039 $changed=true;1040 }1041 1042 1043 foreach($arr_unset as $idx=>$fld)1044 {1045 unset($root["group_fields"][$fld]);1046 }1047 1048 1049 if(!$changed)1050 return;1051// alter array indexes1052 $j=0;1053 $newarr=array();1054 $keys=array_keys($root["group_fields"]);1055 foreach($keys as $idx)1056 {1057 $newarr[$j]=$root["group_fields"][$idx];1058 $j++;1059 }1060 $root["group_fields"] = $newarr;1061}1062 1063function update_report_sort_fields()1064{1065// ensure all group fields are listed in the tables 1066 $root=&$_SESSION["webreports"];1067 if(!$root["sort_fields"])1068 return;1069// ensure all fields in reports are listed in the tables 1070 $tables=getReportTablesList();1071 $changed=false;1072 $arr_unset = Array();1073 foreach($root["sort_fields"] as $idx=>$fld)1074 {1075 $table="";1076 $field="";1077 1078 if(is_wr_db())1079 WRSplitFieldName($fld["name"],$table,$field);1080 else1081 {1082 $field=$fld["name"];1083 $table=$root['tables'][0];1084 }1085 if(array_search($table,$tables)!==false)1086 {1087 $fields=WRGetFieldsList($table);1088 if(array_search($field,$fields)!==false)1089 continue;1090 }1091// remove $total if found1092 $arr_unset[]=$idx;1093 }1094 foreach($arr_unset as $idx=>$fld)1095 {1096 unset($root["sort_fields"][$fld]);1097 }1098 1099 1100 1101// make new array1102 1103 $newarr=array();1104// add group fields 1105 foreach($root["group_fields"] as $fld)1106 {1107 if($fld["name"]=="Summary")1108 continue;1109 $newarr[]=array("name"=>$fld["name"],"desc"=>"false");1110 }1111// add the rest of fields1112 $keys=array_keys($root["sort_fields"]);1113 $j=pre8count($newarr);1114 foreach($keys as $idx)1115 {1116 $found=false;1117 foreach($newarr as $nfld)1118 {1119 if($nfld["name"]==$root["sort_fields"][$idx]["name"])1120 {1121 $found=true;1122 break;1123 }1124 }1125 if($found)1126 continue;1127 $newarr[$j]=$root["sort_fields"][$idx];1128 $j++;1129 }1130 $root["sort_fields"] = $newarr;1131}1132 1133 1134function make_from_clause($type)1135{1136 global $cman;1137 $accessMode = GetDatabaseType() == nDATABASE_Access;1138 $root=&$_SESSION[$type];1139 $_connection = $cman->getForWebReports();1140 if(is_wr_project())1141 $_connection = $cman->byTable( $root['tables'][0] );1142 $ret=$_connection->addTableWrappers($root['tables'][0]);1143 $fullouter="";1144 $firstJoin=true;1145 if(is_array($root["table_relations"]["relat"]))1146 {1147 foreach($root["table_relations"]["relat"] as $r)1148 {1149 if(trim($r["rel_type"])=="FULL OUTER JOIN")1150 {1151 $fullouter.="\n,".$_connection->addTableWrappers($r["right_table"]);1152 continue;1153 }1154 if($accessMode && !$firstJoin)1155 {1156 $ret="(".$ret.")";1157 }1158 $firstJoin=false;1159 $ret.="\n".$r["rel_type"]." ".$_connection->addTableWrappers($r["right_table"])." ON ";1160 $joinon="";1161 foreach($r["left_fields"] as $i=>$f)1162 {1163 if(strlen($joinon))1164 $joinon.=" AND ";1165 $joinon.=$_connection->addTableWrappers($r["left_table"]).".".$_connection->addFieldWrappers($r["left_fields"][$i]);1166 $joinon.="=";1167 $joinon.=$_connection->addTableWrappers($r["right_table"]).".".$_connection->addFieldWrappers($r["right_fields"][$i]);1168 }1169 $ret.=$joinon;1170 }1171 }1172 return "FROM ".$ret.$fullouter;1173}1174function Check_Crosstable_Group($arr,$is_save)1175{1176 if(pre8count($arr["group_fields"])==0 && $is_save)1177 $arr["group_fields"]=$_SESSION["webreports"]["group_fields"];1178 if($arr["group_fields"][pre8count($arr["group_fields"])-1]["cross_table"]=="true")1179 {1180 if(pre8count($arr["group_fields"])-1<2 && $is_save)1181 {1182 echo "You must select at least one group fields";1183 exit();1184 }1185 $count_x=0;1186 $count_y=0;1187 for($i=0;$i<pre8count($arr["group_fields"])-1;$i++)1188 {1189 if($arr["group_fields"][$i]["group_type"]=="x" || $arr["group_fields"][$i]["group_type"]=="all")1190 $count_x++;1191 if($arr["group_fields"][$i]["group_type"]=="y" || $arr["group_fields"][$i]["group_type"]=="all")1192 $count_y++;1193 }1194 if($count_x==0 || $count_y==0)1195 {1196 echo "You must select at least one axis";1197 exit();1198 }1199 }1200}