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 populateQueryInformations($selectedcolumns_arr, &$join_array, &$columns_array) { global $inventory_entities, $inventory_entities_tables, $related_join_array; $this->selectedcolumns_arr = $selectedcolumns_arr; $primary_focus = CRMEntity::getInstance($this->report_obj->primarymodule); $primary_focus->modulename = $this->report_obj->primarymodule; $this->parimary_table_name = $primary_table_name = $primary_focus->table_name; $this->parimary_table_index = $primary_table_index = $primary_focus->table_index; foreach ($primary_focus->tab_name as $other_table) { $primary_join_array[$other_table] = $primary_focus->tab_name_index[$other_table]; } // realted modules array to tables -> if (isset($this->report_obj->relatedmodulesarray) && !empty($this->report_obj->relatedmodulesarray)) { foreach ($this->report_obj->relatedmodulesarray as $key => $rmod_tabid) { $rmod_arr = explode("x", $rmod_tabid); $r_module = vtlib_getModuleNameById($rmod_arr[0]); if (vtlib_isModuleActive($r_module)) { $related_focus = CRMEntity::getInstance($r_module); $related_focus->modulename = $this->report_obj->primarymodule; $realted_table_name = $related_focus->table_name; $realted_table_index = $related_focus->table_index; foreach ($related_focus->tab_name as $other_table) { $related_join_array[$r_module][$other_table] = $related_focus->tab_name_index[$other_table]; } } } } $adb = PEARDatabase::getInstance(); $join_array = $columns_array = array(); require_once "modules/ITS4YouReports/classes/UIFactory.php"; $debug_generation = false; //$debug_generation = true; if ($debug_generation) { ITS4YouReports::sshow($selectedcolumns_arr); } /* * * QUERY INFORMATIONS PREPARING START ** */ foreach ($selectedcolumns_arr as $key => $arr) { $fld_string = $arr["fieldcolname"]; $e_arr = explode(":", $fld_string); // $e_arr 0 1 2 used to define columnstotal array $columns_total_str = $e_arr[0] . ":" . $e_arr[1] . ":" . $e_arr[2]; $tablename = $e_arr[0]; $columnname = $e_arr[1]; $field_string = $e_arr[2]; $fieldname = $e_arr[3]; $last_key = count($e_arr) - 1; $field_uitype = $fieldid = $field_columnname = $column_tablename = ""; $field_module_arr = explode("_", $field_string); $field_module = $field_module_arr[0]; $field_module_id = getTabid($field_module_arr[0]); $as_prefix = $e_arr[$last_key]; if (is_numeric($as_prefix) || in_array(strtolower($as_prefix), array("inv", "mif"))) { $access_query_scope = "_" . strtolower($as_prefix) . "_{$field_module_id}"; if (!array_key_exists($field_module, $this->modules_to_access_query) || !in_array($access_query_tablename, $this->modules_to_access_query)) { $this->modules_to_access_query[$field_module] = $access_query_scope; } //$tablename = trim($tablename, $access_query_scope); } //error_reporting(63); //ini_set('display_errors', 1); $joined = false; $field_row = array(); global $inventory_entities; /* * * PRIMARY FIELDS !!!!! ** */ //$adb->setDebug(true); $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE tablename=? AND columnname=? AND tabid=?", array($tablename, $columnname, $field_module_id)), 0); //$adb->setDebug(false); if (!empty($field_row)) { $field_uitype = $field_row["uitype"]; } if ($debug_generation) { ITS4YouReports::sshow($tablename . " - " . $columnname); } if ($columnname != "converted" && array_key_exists($tablename, $primary_join_array) && $field_module_id == $this->report_obj->primarymoduleid && !empty($field_row)) { if ($debug_generation) { ITS4YouReports::sshow(1); } $fieldid = $field_row["fieldid"]; $params = array('fieldid' => "{$fieldid}", 'fieldtabid' => $field_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => $tablename, 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $primary_focus->table_index, 'fld_string' => $fld_string); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; /* * * PRIMARY INVENTORY FIELDS !!!!! ** */ } elseif ($tablename == "vtiger_inventoryproductrel") { if ($debug_generation) { ITS4YouReports::sshow(2); } $fieldid = $field_row["fieldid"]; $params = array('fieldid' => "", 'fieldtabid' => $field_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => $tablename, 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $primary_focus->table_index, 'fld_string' => $fld_string); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getInventoryJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } // primary joined, continue if ($joined) { continue; } if ($e_arr[$last_key] != "MIF" && in_array($columnname, $this->special_columns)) { if ($debug_generation) { ITS4YouReports::sshow(3); } $params = array('fieldid' => "", 'fieldtabid' => $this->report_obj->primarymoduleid, 'field_uitype' => "1", 'fieldname' => $columnname, 'columnname' => $columnname, 'tablename' => "", 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $primary_focus->table_index, 'fld_string' => $fld_string); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } if ($joined) { continue; } /* * * OTHER INVENTORY FIELDS !!!!! ** */ if ($e_arr[$last_key] == "INV") { if ($debug_generation) { ITS4YouReports::sshow(4); } $field_uitype = "INV"; $params = array('fieldid' => "", 'fieldtabid' => $field_module_id, 'fieldmodule' => $field_module, 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => $tablename, 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $primary_focus->table_index, 'fld_string' => $fld_string); $using_array["using"]["tablename"] = $primary_focus->table_name; $using_array["using"]["columnname"] = $primary_focus->table_index; $params["using_array"] = $using_array; $uifactory = new UIFactory($params); // going to UITypeINV $uifactory->getInventoryJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } // inventory joined, continue if ($joined) { continue; } /* * * MORE INFO FIELDS !!!!! ** */ if ($e_arr[$last_key] == "MIF") { if ($debug_generation) { ITS4YouReports::sshow(5); } $field_uitype = "MIF"; $params = array('fieldid' => "", 'fieldtabid' => $field_module_id, 'fieldmodule' => $field_module, 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => $tablename, 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $primary_focus->table_index, 'fld_string' => $fld_string); $using_array["using"]["tablename"] = $primary_focus->table_name; $using_array["using"]["columnname"] = $primary_focus->table_index; $params["using_array"] = $using_array; $uifactory = new UIFactory($params); // going to UITypeDefault $uifactory->getMoreInfoJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } // more info joined, continue if ($joined) { continue; } /* * * RELATED FIELDS !!!!! fields with FIELDID = realted Fields ** */ if (is_numeric($e_arr[$last_key]) && trim($tablename, $e_arr[$last_key]) != "vtiger_inventoryproductrel") { if ($debug_generation) { ITS4YouReports::sshow(6); } $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldid=?", array($e_arr[$last_key])), 0); $field_uitype = $field_row["uitype"]; $fieldid = $e_arr[$last_key]; /* * * @!! using array loaded in getJoinSQLbyFieldRelation !!@ ** */ // for module start if (is_numeric($e_arr[$last_key - 1])) { $formodule = $e_arr[$last_key - 1]; } else { $temp_fm = explode("_", $e_arr[2]); $formodule = getTabid($temp_fm[0]); } // for module end $params = array('fieldid' => $fieldid, 'fieldtabid' => $field_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => trim($tablename, "_" . $fieldid), 'table_index' => "", 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $field_row["tablename"], 'using_columnname' => $field_row["columnname"], 'formodule' => $formodule, 'fld_string' => $fld_string); $uifactory = new UIFactory($params); $uifactory->getJoinSQLbyFieldRelation($field_uitype, $join_array, $columns_array); $joined = true; } // related fields joined, continue if ($joined) { continue; } // related inventoryproductrel control /* * * RELATED INVENTORY FIELDS !!!!! ** */ $inv_array = explode("vtiger_inventoryproductrel", $tablename); if (isset($inv_array[1]) && is_numeric($inv_array[1])) { if ($debug_generation) { ITS4YouReports::sshow(7); } $r_id = $inv_array[1]; $field_res = $adb->pquery("SELECT uitype, tabid, tablename, columnname FROM vtiger_field WHERE fieldid=?", array($r_id)); $field_rows = $adb->num_rows($field_res); if ($field_rows > 0) { $field_row = $adb->fetchByAssoc($field_res, 0); // $field_uitype = $field_row["uitype"]; $field_uitype = "INV"; $forModuleArray = explode("_", $e_arr[2]); $r_module = $forModuleArray[0]; if (vtlib_isModuleActive($r_module)) { $r_tabid = getTabid($r_module); $field_tablename = $field_row["tablename"]; $field_columnname = $field_row["columnname"]; //$related_focus = CRMEntity::getInstance($r_module); //$related_focus->modulename = $r_module; $params = array('fieldid' => $r_id, 'fieldtabid' => $r_tabid, 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => trim($tablename, $r_id), 'table_index' => $primary_focus->table_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_focus->table_name, 'primary_table_index' => $primary_focus->table_index[$primary_focus->table_name], 'primary_tableid' => '', 'using_aliastablename' => $field_tablename, 'using_columnname' => $field_columnname, 'formodule' => $r_module, 'fld_string' => $fld_string); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getInventoryJoinSQL($field_uitype, $join_array, $columns_array); } } $joined = true; } if ($joined) { continue; } /* elseif (vtlib_getModuleNameById($inv_array[1])!="") { / ** // vazba pomocou more info $r_tabid=$inv_array[1]; $params = array("aliasid"=>$r_tabid, "columnname"=>$e_arr[3], "column_tablename"=>$e_arr[0], ); $uifactory = new UIFactory($params); $uifactory->getInventoryJoinSQL("RelatedInventory", $join_array, $columns_array); $joined = true;* / } // related inventory joined, continue if ($joined) { continue; } */ /* * * PRIMARY SPECIAL FIELDS (e.g. Invoice->AccountName etc.) !!!!! ** */ $f_field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $this->report_obj->primarymoduleid)), 0); if (is_array($f_field_row)) { if ($debug_generation) { ITS4YouReports::sshow(8); } $field_uitype = $f_field_row["uitype"]; $fieldid = $f_field_row["fieldid"]; $params = array('fieldid' => $fieldid, 'fieldtabid' => $f_field_row["tabid"], 'field_uitype' => $field_uitype, 'fieldname' => $fieldname, 'columnname' => $columnname, 'tablename' => $tablename, 'table_index' => $primary_focus->tab_name_index, 'report_primary_table' => $primary_focus->table_name, 'primary_table_name' => $primary_table_name, 'primary_table_index' => $primary_table_index, 'primary_tableid' => $this->report_obj->primarymoduleid, 'using_aliastablename' => $primary_focus->table_name, 'using_columnname' => $columnname, 'fld_string' => $fld_string); $using_array = getJoinInformation($params); $params["using_array"] = $using_array; $uifactory = new UIFactory($params); $uifactory->getJoinSQL($field_uitype, $join_array, $columns_array); $joined = true; } if ($joined) { continue; } if ($columnname == "crmid" && $tablename == "vtiger_crmentity") { if ($debug_generation) { ITS4YouReports::sshow(9); } if ($this->isentitytype == "1") { $fld_cond = $tablename . "." . $columnname; } else { $fld_cond = $primary_focus->table_name . "." . $primary_focus->tab_name_index[$primary_focus->table_name]; } $fld_alias = $columnname; if (strpos($field_string, "LBL_RECORDS") !== false && strpos($e_arr[3], "count") !== false) { $fld_alias .= "_r"; } $columns_array_value = " {$fld_cond} AS {$fld_alias}"; $columns_array[] = $columns_array_value; $columns_array[$fld_string]["fld_alias"] = $fld_alias; $columns_array[$fld_string]["fld_sql_str"] = $columns_array_value; $columns_array[$fld_string]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = ""; $columns_array[$fld_alias] = $fld_string; } if ($columnname == "converted" && $tablename == "vtiger_leaddetails") { if ($debug_generation) { ITS4YouReports::sshow(10); } if ($this->isentitytype == "1") { $fld_cond = $tablename . "." . $columnname; } else { $fld_cond = $primary_focus->table_name . "." . $primary_focus->tab_name_index[$primary_focus->table_name]; } $fld_alias = $columnname; if (strpos($e_arr[3], "count") !== false) { $fld_alias .= "_r"; } $columns_array_value = " {$fld_cond} AS {$fld_alias}"; $columns_array[] = $columns_array_value; $columns_array[$fld_string]["fld_alias"] = $fld_alias; $columns_array[$fld_string]["fld_sql_str"] = $columns_array_value; $columns_array[$fld_string]["fld_cond"] = $fld_cond; $columns_array["uitype_{$fld_alias}"] = ""; $columns_array[$fld_alias] = $fld_string; } } /* * * QUERY INFORMATIONS PREPARING END ** */ }
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; }