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