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