public function getJoinSQLbyFieldRelation(&$join_array, &$columns_array) { $related_focus = CRMEntity::getInstance($this->relModuleName); $params_fieldname = $this->params["fieldname"]; // first join to vtiger module table $this->params["fieldname"] = $related_focus->tab_name_index[$this->params["tablename"]]; $this->getStJoinSQL($join_array, $columns_array); $r_tabid = getTabid($this->relModuleName); $adb = PEARDatabase::getInstance(); $uirel_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tabid = ? AND fieldname = ?", array($r_tabid, $params_fieldname)), 0); $related_table_name = $related_focus->table_name; $related_table_index = $related_focus->table_index; foreach ($related_focus->tab_name as $other_table) { $related_join_array[$other_table] = $related_focus->tab_name_index[$other_table]; } $field_uitype = $uirel_row["uitype"]; $fieldid = $this->params["fieldid"]; $oth_as = ""; if ($uirel_row["tablename"] == "vtiger_crmentity") { $oth_as = $this->oth_as; $related_table_name = $uirel_row["tablename"]; $related_table_index = $uirel_row["columnname"]; } $using_aliastablename = $related_table_name . $oth_as . $fieldid; $using_columnname = $related_table_index; $params = array('fieldid' => $uirel_row["fieldid"], 'fieldtabid' => $uirel_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $uirel_row["fieldname"], 'columnname' => $uirel_row["columnname"], 'tablename' => $uirel_row["tablename"], 'table_index' => $related_join_array, 'report_primary_table' => $this->params["report_primary_table"], 'primary_table_name' => $related_focus->table_name, 'primary_table_index' => $related_focus->table_index, 'primary_tableid' => $r_tabid, 'using_aliastablename' => $using_array["u_tablename"], 'using_columnname' => $using_array["u_tableindex"], 'old_oth_as' => $oth_as, 'old_oth_fieldid' => $fieldid, 'fld_string' => $this->params["fld_string"]); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); //show("<font color='green'>fielduitype".$field_uitype."_IN_P_".$field_uitype,$related_join_array,$params["using_array"],"</font>"); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); }
public function getJoinSQL(&$join_array, &$columns_array) { $this->getStJoinSQL($join_array, $columns_array); $fieldid_alias = ""; if ($this->params["fieldid"] != "") { $fieldid_alias = "_" . $this->params["fieldid"]; } $oth_as = ""; if ($this->params["tablename"] == "vtiger_crmentity") { $oth_as = $this->oth_as; } $join_tablename = trim($this->params["using_array"]["join"]["tablename"], $fieldid_alias); $join_columnname = $this->params["using_array"]["join"]["columnname"]; $adb = PEARDatabase::getInstance(); $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tablename=? AND columnname=?", array($this->params["tablename"], $this->params["columnname"])), 0); if ($field_row["fieldid"] != "" && $field_row["tablename"] != "vtiger_crmentity") { $fieldid_alias = "_" . $field_row["fieldid"]; } $join_tablename_alias = $this->params["join_tablename_alias"] = $join_tablename . $oth_as . $fieldid_alias; $join_alias = " " . $join_tablename . " AS " . $join_tablename_alias . " "; if ($this->params["primary_table_name"] != $join_tablename_alias && isset($this->params["using_array"]) && !empty($this->params["using_array"]["using"]) && !array_key_exists($join_alias, $join_array) && !in_array($this->params["old_oth_fieldid"], array("inv"))) { $using_tablename = $this->params["using_array"]["using"]["tablename"]; $using_columnname = $this->params["using_array"]["using"]["columnname"]; if ($join_tablename != $this->params["primary_table_name"] && $using_tablename != "" && $using_columnname != "") { $join_array[$join_alias]["joincol"] = $join_tablename_alias . "." . $join_columnname; $join_array[$join_alias]["using"] = $using_tablename . "." . $using_columnname; } //show("Join array uitype80 getJoinSQL",$join_array); } if ($this->params["columnname"] == "salesorderid") { $this->params["join_tablename_alias"] = "vtiger_salesorder" . $fieldid_alias; $uifactory = new UIFactory($this->params); $test_display = $uifactory->getDisplaySQL($this->relModuleName, $join_array, $columns_array); $columns_array_value = $test_display["display"]; $fld_alias = $test_display["fld_string"]; $fld_hrefid = $test_display["hrefid"]; $fld_cond = $test_display["fld_cond"]; } else { $fld_cond = $join_tablename_alias . "." . $this->params["columnname"]; $columns_array_value = $fld_cond . " AS " . $this->params["columnname"] . $fieldid_alias; $fld_alias = $this->params["columnname"] . $fieldid_alias; $fld_hrefid = ""; } $columns_array[] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; $columns_array[$this->params["fld_string"]]["fld_alias_hid"] = $fld_alias . "_hid"; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; if ($fld_hrefid != "") { $columns_array[] = $fld_hrefid; } //show("Columns array 80 getJoinSQL",$columns_array,"END JOIN<br />-------------"); }
public function getJoinSQL(&$join_array, &$columns_array) { $this->getStJoinSQL($join_array, $columns_array); $oth_as = $old_oth_fieldid = ""; if ($this->params["tablename"] == "vtiger_crmentity") { $oth_as = $this->oth_as; } if ($this->params["old_oth_fieldid"] != "") { if ($this->params["old_oth_fieldid"] == "mif") { $mif_as = "_" . $this->params["fieldtabid"]; } $old_oth_fieldid = "_" . $this->params["old_oth_fieldid"]; } $fieldid_alias = ""; if ($this->params["fieldid"] != "") { $fieldid_alias = "_" . $this->params["fieldid"]; } $join_tablename_alias = $this->params["join_tablename_alias"] = $this->params["tablename"] . $oth_as . $fieldid_alias; $join_alias = " " . $this->params["tablename"] . " AS " . $join_tablename_alias . " "; $mif_join_alias = " " . $this->params["tablename"] . " AS " . $this->params["tablename"] . $old_oth_fieldid . $mif_as . " "; if ($this->params["primary_table_name"] != $join_tablename_alias && !array_key_exists($mif_join_alias, $join_array) && !array_key_exists($join_alias, $join_array) && !in_array($this->params["old_oth_fieldid"], array("inv"))) { if ($this->params["tablename"] != $this->params["report_primary_table"] && $this->params["tablename"] != $this->params["primary_table_name"]) { $join_array[$join_alias]["joincol"] = $join_tablename_alias . "." . $this->params["table_index"][$this->params["tablename"]]; if ($this->params["using_aliastablename"] != "" && $this->params["using_columnname"] != "") { $join_array[$join_alias]["using"] = $this->params["using_aliastablename"] . "." . $this->params["using_columnname"]; } } } if (in_array($this->params["columnname"], $this->id_cols_array)) { $join_tablename_alias = $this->params["join_tablename_alias"] = "vtiger_account" . $fieldid_alias; $uifactory = new UIFactory($this->params); $test_display = $uifactory->getDisplaySQL($this->relModuleName, $join_array, $columns_array); $fld_cond = $test_display["fld_cond"]; $columns_array_value = $test_display["display"]; $fld_alias = $test_display["fld_string"]; $fld_hrefid = $test_display["hrefid"]; } else { $fld_cond = $join_tablename_alias . "." . $this->params["columnname"] . $fieldid_alias; $columns_array_value = $fld_cond . " AS " . $this->params["columnname"] . $fieldid_alias; $fld_alias = $this->params["columnname"] . $fieldid_alias; $fld_hrefid = ""; } $columns_array[] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; $columns_array[$this->params["fld_string"]]["fld_alias_hid"] = $fld_alias . "_hid"; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; if ($fld_hrefid != "") { $columns_array[] = $fld_hrefid; } }
function getReqAdvFilterSql($reportid, $join_array) { $adb = PEARDatabase::getInstance(); $conditions = array(); $advfiltersql = ""; // ITS4YOU-CR SlOl 28. 3. 2014 10:34:19 to get select options array // $ITS4YouReports = new ITS4YouReports($record); $ITS4YouReports = $this->report_obj; // ADV FILTER START $ITS4YouReports->getPriModuleColumnsList($ITS4YouReports->primarymodule); if (!empty($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule])) { foreach ($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule] as $key => $value) { $secondarymodules[] = $value["id"]; } $secondary_modules_str = implode(":", $secondarymodules); } $ITS4YouReports->getSecModuleColumnsList($secondary_modules_str); $sel_fields = $ITS4YouReports->adv_sel_fields; $conditions_rc = $this->getReqConditions($sel_fields); // ITS4YOU-END 28. 3. 2014 10:34:37 $last_gid = $l_groupcondition = ""; $std_filter_columns = $this->report_obj->getStdFilterColumns(); foreach ($conditions_rc as $groupid => $conditions) { $d_i = 1; $d_num_rows = count($conditions); foreach ($conditions as $key => $d_conditions) { $advfiltergroupsql = ""; $groupid = $d_conditions["groupid"]; if ($last_gid == "") { $last_gid = $groupid; } $dkey = $d_conditions["dkey"]; $from_req = $d_conditions["from_req"]; $column_condition = ""; $fieldcolname = $d_conditions["fcol{$dkey}"]; $comparator = $d_conditions["fop{$dkey}"]; $value = $d_conditions["fval{$dkey}"]; // ? fval_ if (isset($d_conditions["fcon{$dkey}"])) { $column_condition = $d_conditions["fcon{$dkey}"]; } if (isset($d_conditions["gpcon{$dkey}"])) { $groupcondition = $d_conditions["gpcon{$dkey}"]; } if (isset($d_conditions["f_type{$dkey}"])) { $f_type = $d_conditions["f_type{$dkey}"]; } if ($fieldcolname != "" && ($comparator == "isn" || $comparator == "isnn")) { if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $fld_cond = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $fld_arr = explode(":", $fieldcolname); $fld_cond = $fld_arr[0] . "." . $fld_arr[1]; } if ($comparator == "isn") { $advfiltergroupsql .= " (" . $fld_cond . " IS NULL OR " . $fld_cond . " = '') "; } else { $advfiltergroupsql .= " (" . $fld_cond . " IS NOT NULL AND " . $fld_cond . " != '') "; } } elseif ($fieldcolname != "" && $comparator != "" && $f_type != "S" && !in_array($fieldcolname, $std_filter_columns)) { $selectedfields = explode(":", $fieldcolname); //Added to handle yes or no for checkbox field in reports advance filters. -shahul if ($selectedfields[4] == 'C') { if (strcasecmp(trim($value), "yes") == 0) { $value = "1"; } if (strcasecmp(trim($value), "no") == 0) { $value = "0"; } } $valuearray = explode(", ", trim($value)); $datatype = isset($selectedfields[4]) ? $selectedfields[4] : ""; // ADV FILTER CONDITIONS START if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') { $advcolumnsql = ""; for ($n = 0; $n < count($valuearray); $n++) { $advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype); } //If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or' if ($comparator == 'n' || $comparator == 'k') { $advcolumnsql = implode(" and ", $advcolsql); } else { $advcolumnsql = implode(" or ", $advcolsql); } $fieldvalue = " (" . $advcolumnsql . ") "; } else { // ITS4YOU-UP SlOl 9. 1. 2014 13:44:18 $last_key = count($selectedfields) - 1; $exploded_name = explode("_", $selectedfields[2]); $field_module_id = getTabid($exploded_name[0]); if (is_numeric($selectedfields[$last_key])) { $tablename = $selectedfields[0]; $fieldname = $selectedfields[3]; $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0); $selected_field_col_table = trim($selectedfields[0], $selectedfields[$last_key]); if (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $field_row["fieldid"] . " ", $join_array)) { $selected_field_col = $selected_field_col_table . "_" . $field_row["fieldid"] . "." . $selectedfields[1]; } elseif (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $selectedfields[$last_key] . " ", $join_array)) { $selected_field_col = $selected_field_col_table . "_" . $selectedfields[$last_key] . "." . $selectedfields[1]; } } elseif (isset($this->columns_array[$fieldcolname]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } elseif ($selectedfields[$last_key] == "MIF") { $alias = "mif_" . $field_module_id; $selected_field_col = trim($selectedfields[0], "MIF") . $alias . "." . $selectedfields[1]; } else { $tablename = $selectedfields[0]; $fieldname = $selectedfields[3]; $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0); if ($tablename == "vtiger_inventoryproductrel") { $field_uitype = "INV"; $params = array("fieldid" => $selectedfields[$last_key]); $uifactory = new UIFactory($field_row); $selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields); } elseif (!empty($field_row)) { $field_uitype = $field_row["uitype"]; //$uifactory = new UIFactory($field_row); //$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields); if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $table_alias = $tablename . "_{$field_uitype}"; $column_alias = $fieldname; $selected_field_col = $table_alias . "." . $column_alias; } } elseif (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $selected_field_col = $selectedfields[0] . "." . $selectedfields[1]; } } // ITS4YOU-END 9. 1. 2014 13:44:22 $fieldvalue = $selected_field_col . $this->getAdvComparator($comparator, trim($value), $datatype); } // ADV FILTER CONDITIONS END $advfiltergroupsql .= $fieldvalue; } elseif ($f_type == "S") { $fld_arr = explode(":", $fieldcolname); if ($fld_arr[3] == "assigned_user_id" && strpos($value, "Current User") !== false) { $value = trim($value, "('"); $value = trim($value, "')"); $valuearray = explode("', '", $value); $valuearray = $this->getConditionCurrentUserName($valuearray); $value = "('" . implode("', '", $valuearray) . "')"; } $advfiltergroupsql .= $this->getSelFieldsWhereSQL($fieldcolname, $comparator, $value, $fld_arr); /*if ($comparator == "n") { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " NOT IN " . $value; } else { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " IN " . $value; }*/ } elseif (in_array($fieldcolname, $std_filter_columns)) { if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $fld_cond = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $fld_arr = explode(":", $fieldcolname); $fld_cond = $fld_arr[0] . "." . $fld_arr[1]; } $advfiltergroupsql .= $fld_cond . " " . $this->getStdComparator($comparator, trim($value)); } else { $column_info = explode(":", $fieldcolname); $temp_val = explode(", ", $value); if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $value != '') && !in_array($fieldcolname, $std_filter_columns)) { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if (trim($temp_val[$x]) != '') { $date = new DateTimeField(trim($temp_val[$x])); if ($column_info[4] == 'D') { $val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x])); } elseif ($column_info[4] == 'DT') { $val[$x] = $date->getDBInsertDateTimeValue(); } else { $val[$x] = $date->getDBInsertTimeValue(); } } } $value = implode(", ", $val); } if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { if (in_array($fieldcolname, $std_filter_columns)) { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getStdComparator($comparator, trim($value)); } else { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getAdvComparator($comparator, trim($value)); } } else { $this->adv_fcol[] = $fieldcolname; // $selectedfields = explode(":", $fieldcolname); if (in_array($fieldcolname, $std_filter_columns)) { $advfiltergroupsql .= $fieldcolname . " " . $this->getStdComparator($comparator, trim($value)); } else { $advfiltergroupsql .= $fieldcolname . " " . $this->getAdvComparator($comparator, trim($value)); } } } if (trim($advfiltergroupsql) != "") { $advfiltergroupsql = "( {$advfiltergroupsql} ) "; if ($column_condition != NULL && $column_condition != '' && $d_i < $d_num_rows) { $advfiltergroupsql .= ' ' . $column_condition . ' '; } if ($l_groupcondition != NULL && $l_groupcondition != '' && $groupid != $last_gid) { $advfiltergroupsql = ' ' . $l_groupcondition . ' ' . $advfiltergroupsql; $l_groupcondition = $groupcondition; $last_gid = $groupid; } else { $l_groupcondition = $groupcondition; $last_gid = $groupid; } $advfiltersql .= $advfiltergroupsql; } $d_i++; } if (trim($advfiltersql) != "") { $advfiltersql = '(' . $advfiltersql . ')'; } } // Save the information return $advfiltersql; }
function getJoinInformation($params) { $fieldid_alias = $old_fieldid = $old_oth_as = $mif_alias = ""; if ($params["fieldid"] != "") { $fieldid_alias = "_" . $params["fieldid"]; } if ($params["old_oth_as"] != "") { $old_oth_as = $params["old_oth_as"]; } if ($params["old_oth_fieldid"] != "") { $old_fieldid = "_" . $params["old_oth_fieldid"]; } if (isset($params["join_type"]) && $params["join_type"] == "MIF") { if ($params["fieldtabid"] != "") { $mif_alias .= "_" . $params["fieldtabid"]; } } // ITS4YOU-UP SlOl 12/14/2013 6:09:39 PM vtiger_cntactivityrel UPDATE if ($params["tablename"] == "vtiger_cntactivityrel") { $params["tablename"] = "vtiger_contactdetails"; $old_fieldid = ""; $mif_alias = $fieldid_alias; } // ITS4YOU-END 12/14/2013 6:09:41 PM $using["join"]["tablename"] = $params["tablename"] . $old_oth_as . $old_fieldid . $mif_alias; $using["join"]["columnname"] = $params["columnname"]; $uifactory = new UIFactory($params); $uifactory->getModulesByUitype($params["field_uitype"], $params["tablename"], $params["columnname"]); // $using_module_arr = getModulesByUitype($params["field_uitype"],$params["tablename"],$params["columnname"]); if (in_array($params["field_uitype"], array("117"))) { if ($using["join"]["tablename"] != $params["using_aliastablename"]) { $using["using"]["tablename"] = $params["using_aliastablename"]; $using["using"]["columnname"] = $params["using_columnname"]; } } elseif (isset($params["join_type"]) && $params["join_type"] == "MIF" && empty($using_module_arr)) { $using["join"]["tablename"] = $params["tablename"] . $old_oth_as; $using["join"]["columnname"] = $params["table_index"][$params["tablename"]]; if ($using["join"]["tablename"] != $params["using_aliastablename"]) { $using["using"]["tablename"] = $params["using_aliastablename"]; $using["using"]["columnname"] = $params["using_columnname"]; } } elseif ($params["tablename"] != "vtiger_crmentity" || in_array($params["field_uitype"], array("52", "53"))) { $adb = PEARDatabase::getInstance(); $sp_join_row = $adb->fetchByAssoc($adb->pquery("SELECT uitype,tablename,columnname FROM vtiger_field WHERE fieldid = ?", array($params["fieldid"])), 0); if (is_array($params["table_index"]) && isset($params["table_index"][$params["tablename"]]) && $params["tablename"] != $params["primary_table_name"]) { // show("UIP N1"); $using["join"]["tablename"] = $params["tablename"] . $old_oth_as . $old_fieldid . $mif_alias; $using["join"]["columnname"] = $params["table_index"][$params["tablename"]]; $using["using"]["tablename"] = $params["primary_table_name"] . $old_oth_as . $old_fieldid . $mif_alias; $using["using"]["columnname"] = $params["table_index"][$params["primary_table_name"]]; } elseif ($params["tablename"] == "vtiger_inventoryproductrel") { $using["join"]["tablename"] = $params["tablename"] . $fieldid_alias . $mif_alias; $using["join"]["columnname"] = "id"; $using["using"]["tablename"] = $params["primary_table_name"] . $old_fieldid . $mif_alias; $using["using"]["columnname"] = $params["table_index"][$params["primary_table_name"]]; // show("UIP N2"); } elseif (!empty($using_module_arr)) { $gmodule = $using_module_arr[0]; if (vtlib_isModuleActive($gmodule)) { $ui_focus = CRMEntity::getInstance($gmodule); $ui_focus->modulename = $gmodule; if (is_array($params["table_index"])) { if (isset($ui_focus->table_name) && $ui_focus->tab_name_index[$ui_focus->table_name] != "" && $params["tablename"] != $ui_focus->table_name) { // show("UIP N3"); $using["using"]["tablename"] = $params["tablename"] . $old_fieldid . $mif_alias; $using["using"]["columnname"] = $params["columnname"]; $using["join"]["tablename"] = $ui_focus->table_name . $fieldid_alias; $using["join"]["columnname"] = $ui_focus->tab_name_index[$ui_focus->table_name]; } } else { // show("UIP N4"); // SPECIAL FIELDS JOINING $using["using"]["tablename"] = $sp_join_row["tablename"] . $old_oth_as . $old_fieldid . $mif_alias; $using["using"]["columnname"] = $sp_join_row["columnname"]; $using["join"]["tablename"] = $ui_focus->table_name . $fieldid_alias . $mif_alias; $using["join"]["columnname"] = $ui_focus->tab_name_index[$ui_focus->table_name]; } } } elseif ($params["tablename"] != $params["primary_table_name"] && is_array($params["table_index"]) && array_key_exists($params["tablename"], $params["table_index"])) { // show("UIP N5"); $using["using"]["tablename"] = $params["primary_table_name"] . $old_fieldid . $mif_alias; $using["using"]["columnname"] = $params["table_index"]; $using["join"]["tablename"] = $params["tablename"] . $old_fieldid; $using["join"]["columnname"] = $params["table_index"][$params["tablename"]]; } /* elseif($params["tablename"]!=$params["primary_table_name"] && $params["using_aliastablename"]!="" && $params["using_columnname"]!=""){ $using["using"]["tablename"]=$params["using_aliastablename"]; $using["using"]["columnname"]=$params["using_columnname"]; } */ } return $using; }
public function getJoinSQL(&$join_array, &$columns_array) { if (isset($this->params["fieldid"]) && $this->params["fieldid"] != "") { $fieldid_alias = $old_oth_fieldid = $mif_as = $old_oth_as = ""; if ($this->params["fieldid"] != "") { $fieldid_alias = "_" . $this->params["fieldid"]; } if ($this->params["old_oth_as"] != "") { $old_oth_as = $this->params["old_oth_as"]; } if ($this->params["old_oth_fieldid"] != "") { if ($this->params["old_oth_fieldid"] == "mif") { $mif_as = "_" . $this->params["fieldtabid"]; } $old_oth_fieldid = "_" . $this->params["old_oth_fieldid"]; } $fieldid = $this->params["fieldid"]; // ITS4YOU-CR SlOl | 25.8.2015 11:33 $fieldid = $this->params["fieldid"]; $adb = PEARDatabase::getInstance(); $stjoin_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldid = ? ", array($fieldid)), 0); $tablename = $stjoin_row["tablename"]; $columnname = $stjoin_row["columnname"]; if ($this->params["primary_table_name"] != $tablename && $tablename != "vtiger_crmentity") { if (!array_key_exists(" {$tablename} AS {$tablename}" . $fieldid_alias . " ", $join_array) && !in_array($this->params["old_oth_fieldid"], array("inv"))) { $primary_index = $this->params["table_index"][$this->params["primary_table_name"]]; $rel_index = $this->params["table_index"][$tablename]; $join_array[" {$tablename} AS {$tablename}" . " "]["joincol"] = $this->params["primary_table_name"] . "." . $primary_index; $join_array[" {$tablename} AS {$tablename}" . " "]["using"] = $tablename . "." . $rel_index; } } // ITS4YOU-END // ITS4YOU-UP SlOl | 25.8.2015 11:33 if (in_array($this->params["columnname"], $this->id_cols_array) == true) { $join_col = "vtiger_users" . $fieldid_alias . ".id"; $using_col = " vtiger_crmentity{$old_oth_as}" . $old_oth_fieldid . $mif_as . ".smownerid "; } else { $join_col = "vtiger_users" . $fieldid_alias . ".id"; $using_col = $this->params["tablename"] . "." . $this->params["columnname"]; } if (!array_key_exists(" vtiger_users AS vtiger_users" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_users AS vtiger_users" . $fieldid_alias . " "]["joincol"] = $join_col; $join_array[" vtiger_users AS vtiger_users" . $fieldid_alias . " "]["using"] = $using_col; } if (in_array($this->params["columnname"], $this->id_cols_array) == true) { $join_col = "vtiger_groups" . $fieldid_alias . ".groupid"; $using_col = " vtiger_crmentity{$old_oth_as}" . $old_oth_fieldid . $mif_as . ".smownerid "; } else { $join_col = "vtiger_groups" . $fieldid_alias . ".groupid"; $using_col = $this->params["tablename"] . "." . $this->params["columnname"]; } if (!array_key_exists(" vtiger_groups AS vtiger_groups" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_groups AS vtiger_groups" . $fieldid_alias . " "]["joincol"] = $join_col; $join_array[" vtiger_groups AS vtiger_groups" . $fieldid_alias . " "]["using"] = $using_col; } // ITS4YOU-END } $this->params["join_tablename_alias"] = "vtiger_users" . $fieldid_alias; $uifactory = new UIFactory($this->params); $test_display = $uifactory->getDisplaySQL($this->relModuleName, $join_array, $columns_array); $columns_array_value = $test_display["display"]; $fld_alias = $test_display["fld_string"]; $fld_cond = $test_display["fld_cond"]; $columns_array[] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; // $columns_array[$this->params["fld_string"]]["fld_alias_hid"]=$fld_alias."_hid"; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; if ($fld_hrefid != "") { $columns_array[] = $fld_hrefid; } }
public function getJoinSQLbyFieldRelation(&$join_array, &$columns_array) { require_once 'modules/ITS4YouReports/ITS4YouReports.php'; // show("UI10params",$this->params); $formodule = $this->params["formodule"]; $modulename = vtlib_getModuleNameById($formodule); $related_focus = CRMEntity::getInstance($modulename); $params_fieldname = $this->params["fieldname"]; // first join to vtiger module table $this->params["fieldname"] = $related_focus->tab_name_index[$this->params["tablename"]]; $this->getStJoinSQL($join_array, $columns_array); $r_tabid = getTabid($modulename); $uirel_row = array(); if (!in_array($this->params["columnname"], ITS4YouReports::$intentory_fields)) { $adb = PEARDatabase::getInstance(); $uirel_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tabid = ? AND fieldname = ?", array($r_tabid, $params_fieldname)), 0); } $related_table_name = $related_focus->table_name; $related_table_index = $related_focus->table_index; foreach ($related_focus->tab_name as $other_table) { $related_join_array[$other_table] = $related_focus->tab_name_index[$other_table]; } $field_uitype = $uirel_row["uitype"]; $fieldid = $this->params["fieldid"]; $oth_as = "_{$formodule}"; if ($uirel_row["tablename"] == "vtiger_crmentity") { $oth_as = "_10_{$formodule}"; $related_table_name = $uirel_row["tablename"]; $related_table_index = $uirel_row["columnname"]; } if (empty($uirel_row)) { $uirel_row["fieldid"] = $this->params["fieldid"]; $uirel_row["tabid"] = $r_tabid; $uirel_row["fieldname"] = $this->params["fieldname"]; $uirel_row["columnname"] = $this->params["columnname"]; $uirel_row["tablename"] = $related_table_name; } // stjoin content start if (isset($this->params["fieldid"]) && $this->params["fieldid"] != "") { $fieldid_alias = $old_oth_fieldid = ""; if ($this->params["fieldid"] != "") { $fieldid_alias = "_" . $this->params["fieldid"]; } if ($this->params["old_oth_fieldid"] != "") { $old_oth_fieldid = "_" . $this->params["old_oth_fieldid"]; } $fieldid = $this->params["fieldid"]; $adb = PEARDatabase::getInstance(); $stjoin_row = $adb->fetchByAssoc($adb->pquery("SELECT tablename, columnname FROM vtiger_field WHERE fieldid = ? ", array($fieldid)), 0); $tablename = $stjoin_row["tablename"]; $columnname = $stjoin_row["columnname"]; if ($tablename != $this->params["primary_table_name"]) { $primary_focus = CRMEntity::getInstance(vtlib_getModuleNameById($this->params["primary_tableid"])); if (array_key_exists($tablename, $primary_focus->tab_name_index)) { if (!array_key_exists(" " . $tablename . " AS " . $tablename . " ", $join_array)) { $join_array[" " . $tablename . " AS " . $tablename . " "]["joincol"] = $tablename . "." . $primary_focus->tab_name_index[$tablename]; $join_array[" " . $tablename . " AS " . $tablename . " "]["using"] = $primary_focus->table_name . "." . $primary_focus->table_index; } } } //ITS4YouReports::sshow($join_array); } if (!array_key_exists(" " . $related_focus->table_name . " AS " . $related_focus->table_name . $oth_as . $fieldid_alias . " ", $join_array)) { $join_array[" " . $related_focus->table_name . " AS " . $related_focus->table_name . $oth_as . $fieldid_alias . " "]["joincol"] = $related_focus->table_name . $oth_as . $fieldid_alias . "." . $related_focus->table_index; $join_array[" " . $related_focus->table_name . " AS " . $related_focus->table_name . $oth_as . $fieldid_alias . " "]["using"] = $tablename . $old_oth_fieldid . "." . $columnname; } if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity" . $oth_as . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity" . $oth_as . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity" . $oth_as . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity" . $oth_as . $fieldid_alias . " "]["using"] = $related_focus->table_name . $oth_as . $fieldid_alias . "." . $related_focus->table_index . " AND vtiger_crmentity" . $oth_as . $fieldid_alias . ".deleted=0 "; } // stjoin content end //ITS4YouReports::sshow($related_table_name); //ITS4YouReports::sshow($join_array); $using_aliastablename = $related_table_name . $oth_as . $fieldid_alias; $using_columnname = $related_table_index; $params = array('fieldid' => $uirel_row["fieldid"], 'fieldtabid' => $oth_as . $uirel_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $uirel_row["fieldname"], 'columnname' => $uirel_row["columnname"], 'tablename' => $uirel_row["tablename"], 'table_index' => $related_join_array, 'report_primary_table' => $this->params["report_primary_table"], 'primary_table_name' => $related_focus->table_name, 'primary_table_index' => $related_focus->table_index, 'primary_tableid' => $r_tabid, 'using_aliastablename' => $using_aliastablename, 'using_columnname' => $using_columnname, 'old_oth_as' => $oth_as, 'old_oth_fieldid' => $fieldid, 'fld_string' => $this->params["fld_string"], 'formodule' => $formodule); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); // show("<font color='green'>fielduitype10_IN_JO_".$field_uitype,$using_array,$this->params,$join_array,"fielduitype10_IN_JO_PO","</font>"); if (in_array($this->params["columnname"], ITS4YouReports::$intentory_fields)) { $uifactory->getInventoryJoinSQL($field_uitype, $join_array, $columns_array); } else { $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); } }
public function getRelationTables(&$join_array, &$columns_array) { $adb = PEARDatabase::getInstance(); $module = vtlib_getModuleNameById($this->params["primary_tableid"]); $primary_obj = CRMEntity::getInstance($module); $secmodule = vtlib_getModuleNameById($this->params["fieldtabid"]); $secondary_obj = CRMEntity::getInstance($secmodule); $related_join_array = $secondary_obj->tab_name_index; $field_tablename = trim($this->params["tablename"], "_MIF"); $fieldid_alias = ""; if ($this->params["fieldtabid"] != "") { $fieldid_alias = "_" . $this->params["fieldtabid"]; } /* $ui10_query = $adb->pquery("SELECT vtiger_field.fieldid AS fieldid, vtiger_field.tabid AS tabid,vtiger_field.tablename AS tablename, vtiger_field.columnname AS columnname FROM vtiger_field INNER JOIN vtiger_fieldmodulerel ON vtiger_fieldmodulerel.fieldid = vtiger_field.fieldid WHERE vtiger_fieldmodulerel.module=? AND vtiger_fieldmodulerel.relmodule=?",array($module,$secmodule)); if($adb->num_rows($ui10_query)>0){ $ui10_tablename = $adb->query_result($ui10_query,0,'tablename'); $ui10_columnname = $adb->query_result($ui10_query,0,'columnname'); $ui10_tabid = $adb->query_result($ui10_query,0,'tabid'); $ui10_fieldid = $adb->query_result($ui10_query,0,'fieldid'); if($primary_obj->table_name == $ui10_tablename){ $reltables = array($ui10_tablename=>array("".$primary_obj->table_index."","$ui10_columnname")); } else if($secondary_obj->table_name == $ui10_tablename){ $reltables = array($ui10_tablename=>array("$ui10_columnname","".$secondary_obj->table_index.""),"".$primary_obj->table_name."" => "".$primary_obj->table_index.""); } else { if(isset($secondary_obj->tab_name_index[$ui10_tablename])){ $rel_field = $secondary_obj->tab_name_index[$ui10_tablename]; $reltables = array($ui10_tablename=>array("$ui10_columnname","$rel_field"),"".$primary_obj->table_name."" => "".$primary_obj->table_index.""); } else { $rel_field = $primary_obj->tab_name_index[$ui10_tablename]; $reltables = array($ui10_tablename=>array("$rel_field","$ui10_columnname"),"".$primary_obj->table_name."" => "".$primary_obj->table_index.""); } } }else { */ if (method_exists($primary_obj, setRelationTables)) { $reltables = $primary_obj->setRelationTables($secmodule); } else { $reltables = ''; } global $current_user; if ($current_user->id == "1") { /* ITS4YouReports::sshow(" $tablename AS $tablename".$old_oth_as.$old_oth_fieldid . $mif_as." "); ITS4YouReports::sshow("$tablename".$old_oth_as.$old_oth_fieldid . $mif_as."." . $this->params["table_index"][$tablename]); ITS4YouReports::sshow($this->params["using_aliastablename"] . "." . $this->params["table_index"][$this->params["using_aliastablename"]]); */ //get_dependents_list //get_related_list //ITS4YouReports::sshow($reltables); } //} // ITS4YOU-UP SlOl 4. 12. 2014 13:56:43 if ($this->params["fieldmodule"] == "ModComments") { $tab = array("vtiger_modcomments" => array("related_to", $primary_obj->table_index), "" . $primary_obj->table_name . "" => "" . $primary_obj->table_index . ""); // ITS4YOU-END 4. 12. 2014 13:56:45 } elseif (in_array($this->params["columnname"], array("campaignrelstatus", "access_count"))) { $tab = array(); } elseif (is_array($reltables) && !empty($reltables)) { $tab = $reltables; } else { $tab = array("vtiger_crmentityrel" => array("crmid", "relcrmid"), "" . $primary_obj->table_name . "" => "" . $primary_obj->table_index . ""); } if (!empty($tab)) { foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $table_name = $secondary_obj->table_name; $column_name = $secondary_obj->table_index; $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $condvalue_t = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue_t = $tables[1] . "." . $fields[1]; $condvalue_t_table = $tables[1]; $condvalue_t_column = $fields[1]; $condition_t = $pritablename . "_mif" . $fieldid_alias . ".{$prifieldname}"; //ITS4YouReports::sshow("MIF_EJ1"); } else { $condvalue_t = $table_name . "." . $column_name; $condvalue_t_table = $table_name; $condvalue_t_column = $column_name; $condition_t = $pritablename . "_mif" . $fieldid_alias . ".{$secfieldname}"; //ITS4YouReports::sshow("MIF_EJ2"); } if ($pritablename == "vtiger_crmentityrel") { // crmentityrel join $join_array[" " . $pritablename . " AS " . $pritablename . "_mif" . $fieldid_alias . " "]["joincol"] = ""; $join_array[" " . $pritablename . " AS " . $pritablename . "_mif" . $fieldid_alias . " "]["using"] = "(" . $primary_obj->table_name . "." . $primary_obj->table_index . " = " . $pritablename . "_mif" . $fieldid_alias . ".crmid AND module='{$module}' AND relmodule='{$secmodule}' ) OR (" . $primary_obj->table_name . "." . $primary_obj->table_index . " = " . $pritablename . "_mif" . $fieldid_alias . ".relcrmid AND module='{$secmodule}' AND relmodule='{$module}')"; // secondary obj table join $join_array[" " . $table_name . " AS " . $table_name . "_mif" . $fieldid_alias . " "]["joincol"] = ""; $join_array[" " . $table_name . " AS " . $table_name . "_mif" . $fieldid_alias . " "]["using"] = "(" . $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->table_index . " = " . $pritablename . "_mif" . $fieldid_alias . ".crmid AND module='{$secmodule}' AND relmodule='{$module}') OR (" . $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->table_index . " = " . $pritablename . "_mif" . $fieldid_alias . ".relcrmid AND module='{$module}' AND relmodule='{$secmodule}')"; // secondary obj crmentiry deleted=0 join for primary tables if ($table_name == $secondary_obj->table_name) { if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity_mif" . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["using"] = $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$table_name] . " AND vtiger_crmentity_mif" . $fieldid_alias . ".deleted=0 "; } } if ($field_tablename != $secondary_obj->table_name && isset($secondary_obj->tab_name_index[$field_tablename])) { if (!array_key_exists(" {$field_tablename} AS {$field_tablename}" . "_mif" . $fieldid_alias . " ", $join_array)) { $join_array[" {$field_tablename} AS {$field_tablename}" . "_mif" . $fieldid_alias . " "]["joincol"] = $field_tablename . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$field_tablename]; $join_array[" {$field_tablename} AS {$field_tablename}" . "_mif" . $fieldid_alias . " "]["using"] = $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$table_name]; } } } else { $join_array[" " . $pritablename . " AS " . $pritablename . "_mif" . $fieldid_alias . " "]["joincol"] = $condition_t; $join_array[" " . $pritablename . " AS " . $pritablename . "_mif" . $fieldid_alias . " "]["using"] = $condvalue_t; if ($pritablename == $secondary_obj->table_name) { if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity_mif" . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["using"] = $pritablename . "_mif" . $fieldid_alias . ".{$column_name} AND vtiger_crmentity_mif" . $fieldid_alias . ".deleted=0 "; } } if ($pritablename != $table_name) { $join_array[" " . $table_name . " AS " . $table_name . "_mif" . $fieldid_alias . " "]["joincol"] = $table_name . "_mif" . $fieldid_alias . "." . $column_name; $join_array[" " . $table_name . " AS " . $table_name . "_mif" . $fieldid_alias . " "]["using"] = $pritablename . "_mif" . $fieldid_alias . "." . $secfieldname; } if ($table_name == $secondary_obj->table_name) { if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity_mif" . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity_mif" . $fieldid_alias . " "]["using"] = $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$table_name] . " AND vtiger_crmentity_mif" . $fieldid_alias . ".deleted=0 "; } } if ($field_tablename != $table_name) { if (!array_key_exists(" {$field_tablename} AS " . $field_tablename . "_mif" . $fieldid_alias . " ", $join_array)) { $join_array[" {$field_tablename} AS " . $field_tablename . "_mif" . $fieldid_alias . " "]["joincol"] = $field_tablename . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$field_tablename]; $join_array[" {$field_tablename} AS " . $field_tablename . "_mif" . $fieldid_alias . " "]["using"] = $table_name . "_mif" . $fieldid_alias . "." . $secondary_obj->tab_name_index[$table_name]; } } } } $oth_as = "mif"; if ($this->params["columnname"] == "access_count") { $rel_tabid = $this->params["fieldtabid"]; if (!array_key_exists(" vtiger_seactivityrel AS vtiger_seactivityrel_mif_{$rel_tabid}" . "ac ", $join_array)) { $join_array[" vtiger_seactivityrel AS vtiger_seactivityrel_mif_{$rel_tabid}" . "ac "]["joincol"] = "vtiger_seactivityrel_mif_{$rel_tabid}" . "ac.crmid"; $join_array[" vtiger_seactivityrel AS vtiger_seactivityrel_mif_{$rel_tabid}" . "ac "]["using"] = "vtiger_crmentity.crmid"; } if (!array_key_exists(" vtiger_activity AS vtiger_activity_mif_{$rel_tabid}" . "ac ", $join_array)) { $join_array[" vtiger_activity AS vtiger_activity_mif_{$rel_tabid}" . "ac "]["joincol"] = "vtiger_activity_mif_{$rel_tabid}" . "ac.activityid"; $join_array[" vtiger_activity AS vtiger_activity_mif_{$rel_tabid}" . "ac "]["using"] = "vtiger_seactivityrel_mif_{$rel_tabid}" . "ac.activityid"; } if (!array_key_exists(" vtiger_email_track AS vtiger_email_track_mif_{$rel_tabid} ", $join_array)) { $join_array[" vtiger_email_track AS vtiger_email_track_mif_{$rel_tabid} "]["joincol"] = "vtiger_email_track_mif_{$rel_tabid}.mailid"; $join_array[" vtiger_email_track AS vtiger_email_track_mif_{$rel_tabid} "]["using"] = "vtiger_seactivityrel_mif_{$rel_tabid}" . "ac.activityid"; } if (array_key_exists(" vtiger_email_track AS vtiger_email_track_mif_{$rel_tabid} ", $join_array)) { $access_count_sql .= " IF(vtiger_email_track_mif_{$rel_tabid}.access_count IS NOT NULL, vtiger_email_track_mif_{$rel_tabid}.access_count,0) "; } $fld_alias = "access_count_mif_" . $this->params["fieldtabid"]; if ($access_count_sql != "") { $fld_cond = " {$access_count_sql} "; $access_count_col_sql = "{$fld_cond} AS {$fld_alias} "; } else { $access_count_col_sql = " 0 AS {$fld_alias} "; } // if(!in_array($access_count_col_sql,$columns_array) && ($this->params["fieldmodule"]=="Accounts")){ if (!in_array($access_count_col_sql, $columns_array)) { $columns_array[] = $access_count_col_sql; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $access_count_col_sql; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; } } elseif ($this->params["columnname"] == "campaignrelstatus") { $campaignstatus_sql = ""; if ($this->params["fieldmodule"] == "Contacts") { if (!array_key_exists(" vtiger_campaigncontrel AS vtiger_campaigncontrel_mif_4 ", $join_array)) { $join_array[" vtiger_campaigncontrel AS vtiger_campaigncontrel_mif_4 "]["joincol"] = "vtiger_campaigncontrel_mif_4.campaignid"; $join_array[" vtiger_campaigncontrel AS vtiger_campaigncontrel_mif_4 "]["using"] = "vtiger_campaign.campaignid"; } if (array_key_exists(" vtiger_campaigncontrel AS vtiger_campaigncontrel_mif_4 ", $join_array)) { $campaignstatus_sql .= " IF(vtiger_campaigncontrel_mif_4.campaignrelstatusid IS NOT NULL, (SELECT campaignrelstatus FROM vtiger_campaignrelstatus WHERE vtiger_campaignrelstatus.campaignrelstatusid = vtiger_campaigncontrel_mif_4.campaignrelstatusid),NULL) "; } } if ($this->params["fieldmodule"] == "Accounts") { if (!array_key_exists(" vtiger_campaignaccountrel AS vtiger_campaignaccountrel_mif_6 ", $join_array)) { $join_array[" vtiger_campaignaccountrel AS vtiger_campaignaccountrel_mif_6 "]["joincol"] = "vtiger_campaignaccountrel_mif_6.campaignid"; $join_array[" vtiger_campaignaccountrel AS vtiger_campaignaccountrel_mif_6 "]["using"] = "vtiger_campaign.campaignid"; } if (array_key_exists(" vtiger_campaignaccountrel AS vtiger_campaignaccountrel_mif_6 ", $join_array)) { $campaignstatus_sql .= " IF(vtiger_campaignaccountrel_mif_6.campaignrelstatusid IS NOT NULL, (SELECT campaignrelstatus FROM vtiger_campaignrelstatus WHERE vtiger_campaignrelstatus.campaignrelstatusid = vtiger_campaignaccountrel_mif_6.campaignrelstatusid),NULL) "; } } if ($this->params["fieldmodule"] == "Leads") { if (!array_key_exists(" vtiger_campaignleadrel AS vtiger_campaignleadrel_mif_7 ", $join_array)) { $join_array[" vtiger_campaignleadrel AS vtiger_campaignleadrel_mif_7 "]["joincol"] = "vtiger_campaignleadrel_mif_7.campaignid"; $join_array[" vtiger_campaignleadrel AS vtiger_campaignleadrel_mif_7 "]["using"] = "vtiger_campaign.campaignid"; } if (array_key_exists(" vtiger_campaignleadrel AS vtiger_campaignleadrel_mif_7 ", $join_array)) { $campaignstatus_sql .= " IF(vtiger_campaignleadrel_mif_7.campaignrelstatusid IS NOT NULL, (SELECT campaignrelstatus FROM vtiger_campaignrelstatus WHERE vtiger_campaignrelstatus.campaignrelstatusid = vtiger_campaignleadrel_mif_7.campaignrelstatusid),NULL) "; } } $fld_alias = "campaignrelstatus_mif_" . $this->params["fieldtabid"]; if ($campaignstatus_sql != "") { $fld_cond = " {$campaignstatus_sql} "; $campaignstatus_col_sql = "{$fld_cond} AS {$fld_alias} "; } else { $campaignstatus_col_sql = " NULL AS {$fld_alias} "; } // if(!in_array($campaignstatus_col_sql,$columns_array) && ($this->params["fieldmodule"]=="Accounts")){ if (!in_array($campaignstatus_col_sql, $columns_array)) { $columns_array[] = $campaignstatus_col_sql; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $campaignstatus_col_sql; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; } } elseif (in_array($this->params["fieldname"], ITS4YouReports::$intentory_fields)) { $field_uitype = "INV"; $params = array('fieldid' => $oth_as . $fieldid_alias, 'fieldtabid' => $this->params["fieldtabid"], 'fieldmodule' => $secmodule, 'field_uitype' => $field_uitype, 'fieldname' => $this->params["fieldname"], 'columnname' => $this->params["columnname"], 'tablename' => $this->params["table_name"], 'table_index' => $secondary_obj->tab_name_index, 'report_primary_table' => $primary_obj->table_name, 'primary_table_name' => $secondary_obj->table_name, 'primary_table_index' => $secondary_obj->table_index, 'primary_tableid' => $this->params["primary_tableid"], 'using_aliastablename' => $secondary_obj->table_name, 'using_columnname' => $secondary_obj->table_index, 'old_oth_as' => "", 'old_oth_fieldid' => $oth_as, 'join_type' => "MIF", 'fld_string' => $this->params["fld_string"]); $using_array["using"]["tablename"] = $pritablename . "_mif" . $fieldid_alias; $using_array["using"]["columnname"] = $primary_obj->tab_name_index[$pritablename]; $params["using_array"] = $using_array; $uifactory = new UIFactory($params); // going to UITypeINV $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; // show("INV mif joining 0","_".$oth_as.$fieldid_alias,$pritablename,$sec); $uifactory->getInventoryJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } else { $uirel_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tabid = ? AND fieldname = ?", array($this->params["fieldtabid"], $this->params["fieldname"])), 0); $field_uitype = $uirel_row["uitype"]; /* COMMENTED FOR A REASON OF MOD COMMENTS PROBLEM RELATIONS $params = Array('fieldid' => $uirel_row["fieldid"], 'fieldtabid' => $uirel_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $uirel_row["fieldname"], 'columnname' => $uirel_row["columnname"], 'tablename' => $uirel_row["tablename"], 'table_index' => $related_join_array, 'report_primary_table' => $this->params["report_primary_table"], 'primary_table_name' => $secondary_obj->table_name, 'primary_table_index' => $secondary_obj->table_index, 'primary_tableid' => $r_tabid, 'using_aliastablename' => $table_name . "_mif" . $fieldid_alias, 'using_columnname' => $secondary_obj->tab_name_index[$table_name], 'old_oth_as' => "", 'old_oth_fieldid' => $oth_as, 'join_type' => "MIF", 'fld_string' => $this->params["fld_string"], ); */ $params = array('fieldid' => $uirel_row["fieldid"], 'fieldtabid' => $uirel_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $uirel_row["fieldname"], 'columnname' => $uirel_row["columnname"], 'tablename' => $uirel_row["tablename"], 'table_index' => $related_join_array, 'report_primary_table' => $this->params["report_primary_table"], 'primary_table_name' => $primary_obj->table_name, 'primary_table_index' => $primary_obj->table_index, 'primary_tableid' => $r_tabid, 'using_aliastablename' => $table_name . "_mif" . $fieldid_alias, 'using_columnname' => $primary_obj->tab_name_index[$table_name], 'old_oth_as' => "_mif" . $fieldid_alias, 'old_oth_fieldid' => "", 'join_type' => "MIF", 'fld_string' => $this->params["fld_string"]); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); } return $tab; }
public function getMoreInfoJoinSQL(&$join_array, &$columns_array) { $uifactory = new UIFactory($this->params); $rel_array = $uifactory->getRelationTables($join_array, $columns_array); }
public function getInventoryJoinSQL(&$join_array, &$columns_array) { $fieldid_alias = $mif_as = ""; if ($this->params["fieldid"] != "") { $fieldid_alias = "_" . $this->params["fieldid"]; } $related_tablename_as = ""; if (isset($this->params["formodule"]) && $this->params["formodule"] != "") { $related_focus = CRMEntity::getInstance($this->params["formodule"]); $related_tablename = $related_focus->table_name; $related_columnname = $related_focus->table_index; $related_tablename_as = "{$related_tablename}" . $fieldid_alias; if (!array_key_exists(" {$related_tablename} AS {$related_tablename}" . $fieldid_alias . " ", $join_array)) { if (!in_array($related_tablename, $columns_array["relatedCurrencyTables"])) { $columns_array["relatedCurrencyTables"][] = array("table" => $related_tablename, "fieldid_alias" => $fieldid_alias); } $join_array[" {$related_tablename} AS {$related_tablename}" . $fieldid_alias . " "]["joincol"] = "{$related_tablename}" . $fieldid_alias . ".{$related_columnname}"; $join_array[" {$related_tablename} AS {$related_tablename}" . $fieldid_alias . " "]["using"] = $this->params["using_aliastablename"] . "." . $this->params["using_columnname"]; } } $join_array[" vtiger_inventoryproductrel AS vtiger_inventoryproductrel" . $fieldid_alias . " "]["joincol"] = "vtiger_inventoryproductrel" . $fieldid_alias . ".id"; if (isset($this->params["using_array"]) && !empty($this->params["using_array"]["using"])) { //$using_tablename = $this->params["using_array"]["using"]["tablename"]; //$using_columnname = $this->params["using_array"]["using"]["columnname"]; $using_tablename = $this->params["using_aliastablename"]; $using_columnname = $this->params["using_columnname"]; if ($using_tablename != "" && $using_columnname != "") { $join_array[" vtiger_inventoryproductrel AS vtiger_inventoryproductrel" . $fieldid_alias . " "]["using"] = $using_tablename . "." . $using_columnname; } } if (!array_key_exists(" vtiger_products AS vtiger_products_inv" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_products AS vtiger_products_inv" . $fieldid_alias . " "]["joincol"] = "vtiger_products_inv" . $fieldid_alias . ".productid"; $join_array[" vtiger_products AS vtiger_products_inv" . $fieldid_alias . " "]["using"] = "vtiger_inventoryproductrel" . $fieldid_alias . ".productid"; } if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity_products_inv" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity_products_inv" . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity_products_inv" . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity_products_inv" . $fieldid_alias . " "]["using"] = "vtiger_products_inv" . $fieldid_alias . ".productid AND vtiger_crmentity_products_inv" . $fieldid_alias . ".deleted=0 "; } if (!array_key_exists(" vtiger_service AS vtiger_service_inv" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_service AS vtiger_service_inv" . $fieldid_alias . " "]["joincol"] = "vtiger_service_inv" . $fieldid_alias . ".serviceid"; $join_array[" vtiger_service AS vtiger_service_inv" . $fieldid_alias . " "]["using"] = "vtiger_inventoryproductrel" . $fieldid_alias . ".productid"; } if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity_services_inv" . $fieldid_alias . " ", $join_array)) { $join_array[" vtiger_crmentity AS vtiger_crmentity_services_inv" . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity_services_inv" . $fieldid_alias . ".crmid"; $join_array[" vtiger_crmentity AS vtiger_crmentity_services_inv" . $fieldid_alias . " "]["using"] = "vtiger_service_inv" . $fieldid_alias . ".serviceid AND vtiger_crmentity_services_inv" . $fieldid_alias . ".deleted=0 "; } $column_tablename = "vtiger_inventoryproductrel" . $fieldid_alias; if ($this->params["columnname"] != "" && !in_array($this->params["columnname"], ITS4YouReports::$intentory_fields)) { $adb = PEARDatabase::getInstance(); $uirel_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tabid = ? AND fieldname = ?", array($this->params["fieldtabid"], $this->params["fieldname"])), 0); $related_focus = CRMEntity::getInstance($this->params["fieldmodule"]); $related_table_name = $related_focus->table_name; $related_table_index = $related_focus->table_index; $other_table_as = $old_oth_fieldid = $old_oth_fieldid_as = ""; foreach ($related_focus->tab_name as $other_table) { if ($other_table == "vtiger_crmentity") { $other_table_as = $other_table . "_" . strtolower($this->params["fieldmodule"]) . "_inv"; } else { $other_table_as = $other_table . "_inv"; } $related_join_array[$other_table_as] = $related_focus->tab_name_index[$other_table]; } $field_uitype = $uirel_row["uitype"]; $column_tablename = $uirel_row["tablename"]; if ($column_tablename == "vtiger_crmentity") { $old_oth_fieldid = strtolower($this->params["fieldmodule"]) . "_inv"; $old_oth_fieldid_as = "_" . $old_oth_fieldid; $column_tablename_as = $column_tablename . $old_oth_fieldid_as; } else { $old_oth_fieldid = "inv"; $old_oth_fieldid_as = "_" . $old_oth_fieldid; $column_tablename_as = $column_tablename . $old_oth_fieldid_as; } $params = array('fieldid' => $uirel_row["fieldid"], 'fieldtabid' => $uirel_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $uirel_row["fieldname"], 'columnname' => $uirel_row["columnname"], 'tablename' => $uirel_row["tablename"], 'table_index' => $related_join_array, 'report_primary_table' => $this->params["report_primary_table"], 'primary_table_name' => $related_focus->table_name, 'primary_table_index' => $related_focus->table_index, 'primary_tableid' => "", 'old_oth_fieldid' => $old_oth_fieldid, 'fld_string' => $this->params["fld_string"]); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); } elseif (in_array($this->params["columnname"], ITS4YouReports::$intentory_fields)) { $column_tablename = "vtiger_inventoryproductrel"; if ($this->params["columnname"] != "") { $column_tablename_alias = $this->params["tablename"]; if ($column_tablename_alias == "vtiger_crmentity") { $column_tablename_alias = $column_tablename . "_" . strtolower($this->params["fieldmodule"]) . "_inv"; } $fld_cond = $this->getInventoryColumnFldCond($this->params["columnname"], $column_tablename_alias, $fieldid_alias, $related_tablename_as); $columns_array_value = $this->getColumnsArrayValue($fld_cond, $fieldid_alias); $fld_alias = $this->params["columnname"] . $fieldid_alias; if (!in_array(" vtiger_inventoryproductrel" . $fieldid_alias . ".lineitem_id AS lineitem_id" . $fieldid_alias . " ", $columns_array)) { $columns_array[] = " vtiger_inventoryproductrel" . $fieldid_alias . ".lineitem_id AS lineitem_id" . $fieldid_alias . " "; } if ($using_tablename != "" && $using_columnname != "") { $columns_array[] = " {$using_tablename}.{$using_columnname} AS record_id" . $fieldid_alias . " "; } $columns_array[] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_alias"] = $fld_alias; $columns_array[$this->params["fld_string"]]["fld_sql_str"] = $columns_array_value; $columns_array[$this->params["fld_string"]]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"]; $columns_array[$fld_alias] = $this->params["fld_string"]; } } return ""; }