function saveReport() { $summaryNames = array("sum" => "مجموع", "count" => "تعداد", "avg" => "میانگین", "max" => "ماکزیمم", "min" => "مینیمم"); //-------------------- Add or Edit report header --------------------------- $rptobj = new rp_reports(); $rptobj->report_title = $_POST["report_title"]; $rptobj->conditions = stripslashes($_POST["conditions"]); $rptobj->refer_page = $_POST["refer_page"]; if (empty($_POST["report_id"])) { $result = $rptobj->Add(); $rptobj->report_id = rp_reports::LastID(); } else { $rptobj->report_id = $_POST["report_id"]; $result = $rptobj->Edit(); } if (!$result) { print_r(ExceptionHandler::PopAllExceptions()); die; } //------------------------ insert all used columns ------------------------- PdoDataAccess::runquery("delete from rp_report_columns where report_id=" . $rptobj->report_id . " AND used_type in('group','separation','filter','order')"); $variables = array("groupColumns", "separationColumns", "filterColumns", "orderColumns"); $query = "insert into rp_report_columns(report_id,parent_path,column_id,used_type) values"; foreach ($variables as $var) { $st = preg_split('/,/', $_POST[$var]); for ($i = 0; $i < count($st) - 1; $i++) { $id = $st[$i]; $parent_path = $id; $tmp = preg_split('/_/', $id); $column_id = $tmp[count($tmp) - 1]; $query .= "(" . $rptobj->report_id . ",'" . $parent_path . "'," . $column_id . ",'" . str_replace("Columns", "", $var) . "'),"; } } $st = array(); preg_match_all("|\\[[^\\]]+(.*)+\\]|U", $_POST["conditions"], $st, PREG_PATTERN_ORDER); if ($st && $st[0]) { for ($i = 0; $i < count($st[0]); $i++) { $tmp = preg_replace("/\\[|\\]/", "", $st[0][$i]); $parent_path = $tmp; $tmp = preg_split('/_/', $parent_path); $column_id = $tmp[count($tmp) - 1]; $query .= "(" . $rptobj->report_id . ",'" . $parent_path . "'," . $column_id . ",'condition'),"; } } $query = substr($query, 0, strlen($query) - 1); $query .= " ON DUPLICATE KEY UPDATE row_id=row_id"; PdoDataAccess::runquery($query); //-------------- make All tables name/value Collection --------------------- $temp = PdoDataAccess::runquery("select * from rp_tables order by table_id"); $allMasters = ""; for ($i = 0; $i < count($temp); $i++) { $allMasters[$temp[$i]["table_id"]] = $temp[$i]; } //---------------------- extract all used relations ----------------------- $columns = PdoDataAccess::runquery("\r\n\t\tselect rc.*,c.*,rt.join_text from rp_report_columns rc\r\n\t\t\tjoin rp_columns c using(column_id)\r\n\t\t\tleft join rp_tables as rt on(rt.table_id=c.basic_info_table)\r\n\t\twhere report_id=" . $rptobj->report_id . " and used_type<>'formula'"); global $ALIAS_counter; $ALIAS_counter = 0; $tables = array(); $allRelations = array(); $EXTRA_QUERY_FROM = ""; for ($i = 0; $i < count($columns); $i++) { $tmp = preg_split('/_/', $columns[$i]["parent_path"]); $parent = ""; $cur_column_alias = ""; for ($j = 0; $j < count($tmp) - 1; $j++) { if (array_search($tmp[$j], $allRelations) === false) { array_push($allRelations, $tmp[$j]); } if (isset($tables[$tmp[$j]])) { for ($k = 0; $k < count($tables[$tmp[$j]]["parents"]); $k++) { if ($tables[$tmp[$j]]["parents"][$k][0] == $parent) { $cur_column_alias = $tables[$tmp[$j]]["parents"][$k][1]; break; } } if ($k < count($tables[$tmp[$j]]["parents"])) { $parent .= $parent == "" ? $tmp[$j] : "_" . $tmp[$j]; continue; } $tables[$tmp[$j]]["parents"][] = array($parent, $ALIAS_counter++); $cur_column_alias = $ALIAS_counter - 1; } else { $tables[$tmp[$j]] = array("parents" => array(array($parent, $ALIAS_counter++))); $cur_column_alias = $ALIAS_counter - 1; } $parent .= $parent == "" ? $tmp[$j] : "_" . $tmp[$j]; } //............................. $parent_path = $columns[$i]["parent_path"]; $tmp = preg_split('/_/', $parent_path); $column_id = $columns[$i]["column_id"]; $alias = "tbl" . $cur_column_alias; $field = $alias . "." . $columns[$i]["field_name"]; $base_field = $alias . "." . $columns[$i]["field_name"]; if ($columns[$i]["basic_type_id"] != "" && $columns[$i]["basic_type_id"] != "0") { $EXTRA_QUERY_FROM .= "\n left join Basic_Info as tbl" . $ALIAS_counter . " on(tbl{$ALIAS_counter}.TypeID=" . $columns[$i]["basic_type_id"] . " AND tbl{$ALIAS_counter}.InfoID=" . $alias . "." . $columns[$i]["field_name"] . ")"; $field = "tbl" . $ALIAS_counter . ".Title"; $ALIAS_counter++; } if ($columns[$i]["basic_info_table"] != "" && $columns[$i]["basic_info_table"] != "0") { $result = AddInfoTable($allMasters, $allMasters[$columns[$i]["basic_info_table"]], $alias, $columns[$i]["field_name"]); $EXTRA_QUERY_FROM .= "\n" . $result["from"]; $field = addslashes($result["field"]); } PdoDataAccess::runquery("update rp_report_columns\r\n\t\t\tset field = '" . $field . "', base_field = '" . $base_field . "'\r\n\t\t\twhere row_id=" . $columns[$i]["row_id"]); } //--------------------------- make from clause ----------------------------- $relations = PdoDataAccess::runquery("\r\n\t\tselect relation_id,t1.table_name as parent_table_name,t2.table_name as table_name, join_text\r\n\t\tfrom rp_relations r\r\n\t\t\tleft join rp_base_tables t1 on(r.parent_table_id=t1.table_id)\r\n\t\t\tleft join rp_base_tables t2 on(r.table_id=t2.table_id)\r\n\t\twhere relation_id in(" . implode(",", $allRelations) . ") order by relation_id"); $QUERY_FROM = "\n from " . $relations[0]["table_name"] . " as tbl" . $tables[$relations[0]["relation_id"]]["parents"][0][1]; for ($i = 1; $i < count($relations); $i++) { $relation_id = $relations[$i]["relation_id"]; for ($j = 0; $j < count($tables[$relation_id]["parents"]); $j++) { $on = $relations[$i]["join_text"]; $on = str_replace("ALIAS2", "tbl" . $tables[$relation_id]["parents"][$j][1], $on); $parent = preg_split('/_/', $tables[$relation_id]["parents"][$j][0]); $last_parent = $parent[count($parent) - 1]; unset($parent[count($parent) - 1]); $path_parent = implode("_", $parent); for ($k = 0; $k < count($tables[$last_parent]["parents"]); $k++) { if ($path_parent == $tables[$last_parent]["parents"][$k][0]) { $on = str_replace("ALIAS1", "tbl" . $tables[$last_parent]["parents"][$k][1], $on); break; } } $QUERY_FROM .= "\n left join " . $relations[$i]["table_name"] . " as tbl" . $tables[$relation_id]["parents"][$j][1] . " on(" . $on . ")"; } } //-------------------------------------------------------------------------- $rptobj->query = $QUERY_FROM . $EXTRA_QUERY_FROM; $result = $rptobj->Edit(); if ($result) { echo Response::createObjectiveResponse(true, $rptobj->report_id); } else { print_r(ExceptionHandler::PopAllExceptions()); } die; //--------------------- extract all used relations ------------------------- $columns = PdoDataAccess::runquery("\r\n\t\tselect rc.*,c.*,rt.join_text from rp_report_columns rc\r\n\t\t\tjoin rp_columns c using(column_id)\r\n\t\t\tleft join rp_tables as rt on(rt.table_id=c.basic_info_table)\r\n\t\twhere report_id=" . $rptobj->report_id . " and used_type<>'formula'"); $allRelations = array(); $EXTRA_QUERY_FROM = ""; global $ALIAS_counter; $ALIAS_counter = 0; $aliases = array(); for ($i = 0; $i < count($columns); $i++) { $tmp = preg_split('/_/', $columns[$i]["parent_path"]); for ($j = 0; $j < count($tmp) - 1; $j++) { if (array_search($tmp[$j], $allRelations) === false) { array_push($allRelations, $tmp[$j]); } } //............................. $parent_path = $columns[$i]["parent_path"]; $tmp = preg_split('/_/', $parent_path); $column_id = $columns[$i]["column_id"]; $relation_id = $tmp[count($tmp) - 2]; $aliases[$relation_id] = "tbl" . $ALIAS_counter++; $alias = $aliases[$relation_id]; $field = $alias . "." . $columns[$i]["field_name"]; $base_field = $alias . "." . $columns[$i]["field_name"]; if ($columns[$i]["basic_type_id"] != "" && $columns[$i]["basic_type_id"] != "0") { $EXTRA_QUERY_FROM .= "\n left join Basic_Info as tbl" . $ALIAS_counter . " on(tbl{$ALIAS_counter}.TypeID=" . $columns[$i]["basic_type_id"] . " AND tbl{$ALIAS_counter}.InfoID=" . $alias . "." . $columns[$i]["field_name"] . ")"; $field = "tbl" . $ALIAS_counter . ".Title"; $ALIAS_counter++; } if ($columns[$i]["basic_info_table"] != "" && $columns[$i]["basic_info_table"] != "0") { $result = AddInfoTable($allMasters, $allMasters[$columns[$i]["basic_info_table"]], $alias, $columns[$i]["field_name"]); $EXTRA_QUERY_FROM .= "\n" . $result["from"]; $field = addslashes($result["field"]); } PdoDataAccess::runquery("update rp_report_columns \r\n\t\t\tset field = '" . $field . "', base_field = '" . $base_field . "'\r\n\t\t\twhere row_id=" . $columns[$i]["row_id"]); } array_multisort($allRelations); //--------------------------- make from clause ----------------------------- $relations = PdoDataAccess::runquery("\r\n\t\tselect relation_id,t1.table_name as parent_table_name,t2.table_name as table_name, join_text\r\n\t\tfrom rp_relations r\r\n\t\t\tleft join rp_base_tables t1 on(r.parent_table_id=t1.table_id)\r\n\t\t\tleft join rp_base_tables t2 on(r.table_id=t2.table_id)\r\n\t\twhere relation_id in(" . implode(",", $allRelations) . ") order by relation_id"); //$QUERY_FROM = "\n from " . $relations[0]["table_name"] . " as tbl" . $relations[0]["relation_id"]; $QUERY_FROM = "\n from " . $relations[0]["table_name"] . " as " . $aliases[$relations[0]["relation_id"]]; //$aliases = array(); //$aliases[$relations[0]["table_name"]] = "tbl" . $relations[0]["relation_id"]; $aliases[$relations[0]["table_name"]] = $aliases[$relations[0]["relation_id"]]; for ($i = 1; $i < count($relations); $i++) { $on = $relations[$i]["join_text"]; $on = str_replace("ALIAS1", $aliases[$relations[$i]["parent_table_name"]], $on); //$on = str_replace("ALIAS2", "tbl" . $relations[$i]["relation_id"], $on); $on = str_replace("ALIAS2", $aliases[$relations[0]["relation_id"]], $on); //$QUERY_FROM .= "\n left join " . $relations[$i]["table_name"] . " as tbl" . $relations[$i]["relation_id"] . " on(" . $on . ")"; $QUERY_FROM .= "\n left join " . $relations[$i]["table_name"] . " as " . $aliases[$relations[0]["relation_id"]] . " on(" . $on . ")"; //$aliases[$relations[$i]["table_name"]] = "tbl" . $relations[$i]["relation_id"]; } //-------------------------------------------------------------------------- $rptobj->query = $QUERY_FROM . $EXTRA_QUERY_FROM; $result = $rptobj->Edit(); if ($result) { echo Response::createObjectiveResponse(true, $rptobj->report_id); } else { print_r(ExceptionHandler::PopAllExceptions()); } die; }