예제 #1
0
 public function getStJoinSQL(&$join_array, &$columns_array)
 {
     if (isset($this->params["fieldid"]) && $this->params["fieldid"] != "") {
         $fieldid_alias = $old_oth_fieldid = $mif_as = "";
         if ($this->params["fieldid"] != "") {
             $fieldid_alias = "_" . $this->params["fieldid"];
         }
         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"];
         $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 (!array_key_exists(" vtiger_quotes AS vtiger_quotes" . $fieldid_alias . " ", $join_array)) {
             $join_array[" vtiger_quotes AS vtiger_quotes" . $fieldid_alias . " "]["joincol"] = "vtiger_quotes" . $fieldid_alias . ".quoteid";
             $join_array[" vtiger_quotes AS vtiger_quotes" . $fieldid_alias . " "]["using"] = $tablename . $old_oth_fieldid . $mif_as . "." . $columnname;
         }
         if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " ", $join_array)) {
             $join_array[" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity" . $this->oth_as . $fieldid_alias . ".crmid";
             $join_array[" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " "]["using"] = "vtiger_quotes" . $fieldid_alias . ".quoteid AND vtiger_crmentity" . $this->oth_as . $fieldid_alias . ".deleted=0 ";
         }
     }
 }
예제 #2
0
 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);
 }
예제 #3
0
 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 />-------------");
 }
예제 #4
0
 public function getStJoinSQL(&$join_array, &$columns_array)
 {
     if (isset($this->params["fieldid"]) && $this->params["fieldid"] != "") {
         $fieldid_alias = $old_oth_fieldid = $mif_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"];
         $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 ($tablename != $this->params["primary_table_name"] && !array_key_exists(" {$tablename} AS {$tablename} ", $join_array)) {
             if ($tablename == "vtiger_cntactivityrel") {
                 $join_array[" {$tablename} AS {$tablename} "]["joincol"] = "{$tablename}.activityid";
                 $join_array[" {$tablename} AS {$tablename} "]["using"] = "vtiger_activity.activityid";
             } else {
                 if (!array_key_exists(" {$tablename} AS {$tablename}" . $old_oth_as . $old_oth_fieldid . $mif_as . " ", $join_array)) {
                     $join_array[" {$tablename} AS {$tablename}" . $old_oth_as . $old_oth_fieldid . $mif_as . " "]["joincol"] = "{$tablename}" . $old_oth_as . $old_oth_fieldid . $mif_as . "." . $this->params["table_index"][$tablename];
                     $join_array[" {$tablename} AS {$tablename}" . $old_oth_as . $old_oth_fieldid . $mif_as . " "]["using"] = $this->params["using_aliastablename"] . "." . $this->params["table_index"][$this->params["using_aliastablename"]];
                 }
             }
         }
         if (!array_key_exists(" vtiger_contactdetails AS vtiger_contactdetails" . $fieldid_alias . " ", $join_array)) {
             $join_array[" vtiger_contactdetails AS vtiger_contactdetails" . $fieldid_alias . " "]["joincol"] = "vtiger_contactdetails" . $fieldid_alias . ".contactid";
             $join_array[" vtiger_contactdetails AS vtiger_contactdetails" . $fieldid_alias . " "]["using"] = $tablename . $old_oth_as . $old_oth_fieldid . $mif_as . "." . $columnname;
         }
         if (!array_key_exists(" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " ", $join_array)) {
             $join_array[" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " "]["joincol"] = "vtiger_crmentity" . $this->oth_as . $fieldid_alias . ".crmid";
             $join_array[" vtiger_crmentity AS vtiger_crmentity" . $this->oth_as . $fieldid_alias . " "]["using"] = "vtiger_contactdetails" . $fieldid_alias . ".contactid AND vtiger_crmentity" . $this->oth_as . $fieldid_alias . ".deleted=0 ";
         }
     }
 }
예제 #5
0
 private function returnGroupDetailRecordsNew($group_value, $r_i, $selectedcolumns_arr, $currency_id = "")
 {
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::returnGroupDetailRecordsNew");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::returnGroupDetailRecordsNew");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::returnGroupDetailRecordsNew");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::returnGroupDetailRecordsNew");
     $return_html = "";
     $agi = 1;
     $pgi = 0;
     $g_con_name = "gi_con_{$pgi}";
     if ($this->report_obj->reportinformations["Group1"] != "none") {
         $detail_sql_arr = explode("GROUP BY", $this->detail_sql);
         $exploded_detail_sql_arr = explode("ORDER BY", $detail_sql_arr[1]);
         $detail_sql_arr[1] = $exploded_detail_sql_arr[1];
     } else {
         $detail_sql_arr = explode("ORDER BY", $this->detail_sql);
     }
     if (!empty($detail_sql_arr)) {
         $adb = PEARDatabase::getInstance();
         $where_pos = strpos($detail_sql_arr[0], "WHERE ");
         if ($where_pos === false) {
             $wstr = "WHERE";
         } else {
             $wstr = "AND";
         }
         if (isset($this->report_obj->reportinformations["timeline_columnstr{$agi}"]) && $this->report_obj->reportinformations["timeline_columnstr{$agi}"] != "" && $this->report_obj->reportinformations["timeline_columnstr{$agi}"] != "@vlv@") {
             $g_con_col_sql = $this->getTimeLineColumnSql($this->{$g_con_name}, $this->report_obj->reportinformations["timeline_columnstr{$agi}"]);
         } else {
             $g_con_col_sql = $this->{$g_con_name};
         }
         // quick fix campaing rel status
         if (strpos($detail_sql_arr[0], "campaignrelstatus") !== false || strpos($detail_sql_arr[0], "access_count") !== false) {
             if (isset($_REQUEST['advft_criteria']) && $_REQUEST['advft_criteria'] != "" || !empty($this->advf_col_array)) {
                 $wstr = "AND";
             } else {
                 $wstr = "WHERE";
             }
         }
         if ($group_value == "" || $group_value == "-") {
             $g_con_sql .= " {$wstr} ( " . $g_con_col_sql . " = '' OR " . $g_con_col_sql . " IS NULL )";
         } else {
             $g_con_sql .= " {$wstr} " . $g_con_col_sql . " = '{$group_value}' ";
         }
         if ($currency_id != "") {
             $g_con_sql .= " AND " . $this->currency_id_sql . " = '{$currency_id}' ";
         }
         global $default_charset;
         $g_con_sql = html_entity_decode($g_con_sql, ENT_QUOTES, $default_charset);
         $df_gsql = $detail_sql_arr[0];
         $detail_sql_arr[0] .= $g_con_sql;
         /* if ($this->report_obj->reportinformations["Group1"] != "none") {
            $df_gsql = implode("GROUP BY", $detail_sql_arr);
            } else { */
         $df_gsql = implode("ORDER BY", $detail_sql_arr);
         //}
         $columns_limit = "";
         if ($this->report_obj->reportinformations["Group1"] == "none" && isset($this->report_obj->reportinformations["columns_limit"]) && $this->report_obj->reportinformations["columns_limit"] != "" && $this->report_obj->reportinformations["columns_limit"] != "0") {
             $columns_limit = " LIMIT " . $this->report_obj->reportinformations["columns_limit"];
         }
         $df_gsql .= $columns_limit;
         //$adb->setDebug(true);
         $d_result = $adb->pquery($df_gsql, array());
         //$adb->setDebug(false);
         $d_num_rows = $adb->num_rows($d_result);
         $this->to_totals_array = $this->getToTotalsArray(true);
         if ($d_num_rows > 0) {
             $gr_i = 0;
             while ($detail_row = $adb->fetchByAssoc($d_result)) {
                 if (!isset($this->outputformat) || $this->outputformat == "HTML" || $outputformat == "CHARTS") {
                     $return_html .= "</tr>";
                     $return_html .= "<tr>";
                     foreach ($detail_row as $d_key => $d_value) {
                         $is_hid = strpos($d_key, "_hid");
                         if ($is_hid === false && !in_array($d_key, $this->skip_fields) && $this->report_obj->in_multiarray($this->detail_columns_array[$d_key], $this->detail_selectedcolumns_arr, "fieldcolname") === true) {
                             //$txt_align = $this->getFldAlignment($d_key, $d_value);
                             $fld_style_arr = $this->getFldStyle($d_key, $d_value);
                             $fld_style = $this->getFldStyleString($fld_style_arr);
                             $this->to_totals_res = $this->setToTotalsArray($d_num_rows, $this->to_totals_res, $d_key, $d_value, $this->to_totals_array, $currency_id);
                             $return_html .= "<td class='rpt4youGrpHead' style='background-color:#FFFFFF;{$fld_style}' nowrap >" . $this->getFldNumberFormat($d_key, $d_value, $currency_id) . "</td>";
                         }
                     }
                 } else {
                     foreach ($detail_row as $d_key => $d_value) {
                         $is_hid = strpos($d_key, "_hid");
                         if ($is_hid === false && !in_array($d_key, $this->skip_fields) && $this->report_obj->in_multiarray($this->detail_columns_array[$d_key], $this->detail_selectedcolumns_arr, "fieldcolname") === true) {
                             //$txt_align = $this->getFldAlignment($d_key, $d_value);
                             $this->to_totals_res = $this->setToTotalsArray($d_num_rows, $this->to_totals_res, $d_key, $d_value, $this->to_totals_array, $currency_id);
                             $return_html[$gr_i][] = $this->getFldNumberFormat($d_key, $d_value, $currency_id);
                         }
                     }
                     $gr_i++;
                 }
             }
         }
     }
     return $return_html;
 }
예제 #6
0
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;
}
예제 #7
0
 public function ImportReports4You($file_to_import = "", $debug = false)
 {
     //echo "<pre>";print_r($file_to_import);echo "</pre>";
     $adb = PEARDatabase::getInstance();
     //$debug = true;
     if ($debug) {
         $adb->setDebug(true);
     }
     if ($file_to_import != "" && file_exists($file_to_import) && substr($file_to_import, -4) == ".php") {
         $result = $adb->pquery("SELECT @reports4youid:=(IF(reports4youid IS NOT NULL,max(reports4youid)+1,1)) AS reports4youid FROM its4you_reports4you;", array());
         $row = $adb->fetchByAssoc($result);
         $report_id = $row["reports4youid"];
         $ReportSql = array();
         global $default_charset;
         require_once $file_to_import;
         $report_array_json = str_replace("@reportid", $report_id, $report_array_json);
         $ReportSql = Zend_Json::decode($report_array_json);
         if (isset($ReportSql["its4you_reports4you_settings"]) && !empty($ReportSql["its4you_reports4you_settings"])) {
             $its4you_reports4you_settings = vtlib_purify($ReportSql["its4you_reports4you_settings"]);
             $adb->pquery("INSERT INTO `its4you_reports4you_settings` (`reportid`, `owner`, `sharingtype`) VALUES (?,?,?)", array($its4you_reports4you_settings));
         }
         if (isset($ReportSql["its4you_reports4you_selectcolumn"]) && !empty($ReportSql["its4you_reports4you_selectcolumn"])) {
             $its4you_reports4you_selectcolumn = vtlib_purify($ReportSql["its4you_reports4you_selectcolumn"]);
             // $adb->pquery("INSERT INTO its4you_reports4you_selectcolumn (QUERYID,COLUMNINDEX,COLUMNNAME) VALUES (?,?,?)",array($selected_column));
             $sql = "INSERT INTO its4you_reports4you_selectcolumn (QUERYID,COLUMNINDEX,COLUMNNAME) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_selectcolumn as $c_key => $selected_column) {
                 $Qarray[] = '(?,?,?)';
                 $Values = array_merge($Values, $selected_column);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_summaries"]) && !empty($ReportSql["its4you_reports4you_summaries"])) {
             $its4you_reports4you_summaries = vtlib_purify($ReportSql["its4you_reports4you_summaries"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_summaries (reportsummaryid,summarytype,columnname) VALUES (?,?,?)",array($summaries_array));
             $sql = "INSERT INTO its4you_reports4you_summaries (reportsummaryid, summarytype, columnname) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_summaries as $s_key => $summaries_array) {
                 $Qarray[] = '(?,?,?)';
                 $Values = array_merge($Values, $summaries_array);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_summaries_orderby"]) && !empty($ReportSql["its4you_reports4you_summaries_orderby"])) {
             $its4you_reports4you_summaries_orderby = vtlib_purify($ReportSql["its4you_reports4you_summaries_orderby"]);
             $adb->pquery("INSERT INTO its4you_reports4you_summaries_orderby (reportid,columnindex,summaries_orderby,summaries_orderby_type) VALUES (?,?,?,?)", array($its4you_reports4you_summaries_orderby));
         }
         if (isset($ReportSql["its4you_reports4you_labels"]) && !empty($ReportSql["its4you_reports4you_labels"])) {
             $its4you_reports4you_labels = vtlib_purify($ReportSql["its4you_reports4you_labels"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_labels (reportid,type,columnname,columnlabel) VALUES (?,?,?,?)",array($labels_array));
             $sql = "INSERT INTO its4you_reports4you_labels (reportid,type,columnname,columnlabel) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_labels as $l_key => $labels_array) {
                 $Qarray[] = '(?,?,?,?)';
                 $Values = array_merge($Values, $labels_array);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you"]) && !empty($ReportSql["its4you_reports4you"])) {
             $its4you_reports4you = vtlib_purify($ReportSql["its4you_reports4you"]);
             /*
              $folder_result = $adb->pquery("SELECT IF(folderid IS NOT NULL, folderid, count(folderid)+1) AS folderid FROM `its4you_reports4you_folder` WHERE foldername=?", array($its4you_reports4you[3]));
              $folder_row = $adb->fetchByAssoc($folder_result);
              $its4you_reports4you[3] = $folder_row["folderid"];
             */
             $adb->pquery("INSERT INTO its4you_reports4you (reports4youid,reports4youname,description,folderid,reporttype,deleted,columns_limit,summaries_limit) VALUES (?,?,?,?,?,?,?,?)", array($its4you_reports4you));
         }
         if (isset($ReportSql["its4you_reports4you_summary"]) && !empty($ReportSql["its4you_reports4you_summary"])) {
             $its4you_reports4you_summary = vtlib_purify($ReportSql["its4you_reports4you_summary"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_summary (REPORTSUMMARYID,SUMMARYTYPE,COLUMNNAME) VALUES (?,?,?);",array($summary_array));
             $sql = "INSERT INTO its4you_reports4you_summary (REPORTSUMMARYID,SUMMARYTYPE,COLUMNNAME) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_summary as $sm_key => $summary_array) {
                 $Qarray[] = '(?,?,?)';
                 $Values = array_merge($Values, $summary_array);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_modules"]) && !empty($ReportSql["its4you_reports4you_modules"])) {
             $its4you_reports4you_modules = vtlib_purify($ReportSql["its4you_reports4you_modules"]);
             $adb->pquery("INSERT INTO its4you_reports4you_modules (REPORTMODULESID,PRIMARYMODULE,SECONDARYMODULES) VALUES (?,?,?)", array($its4you_reports4you_modules));
         }
         if (isset($ReportSql["its4you_reports4you_sortcol"]) && !empty($ReportSql["its4you_reports4you_sortcol"])) {
             $its4you_reports4you_sortcol = vtlib_purify($ReportSql["its4you_reports4you_sortcol"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_sortcol (sortcolid,reportid,columnname,sortorder,timeline_type,timeline_columnstr,timeline_columnfreq) VALUES (?,?,?,?,?,?,?)",array($its4you_reports4you_sortcol_arr));
             $sql = "INSERT INTO its4you_reports4you_sortcol (sortcolid,reportid,columnname,sortorder,timeline_type,timeline_columnstr,timeline_columnfreq) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_sortcol as $sortcol_i => $its4you_reports4you_sortcol_arr) {
                 $Qarray[] = '(?,?,?,?,?,?,?)';
                 if ($sortcol_i == "3") {
                     /* if($its4you_reports4you_sortcol_arr[$sortcol_i]=="ASC"){
                        $its4you_reports4you_sortcol_arr[$sortcol_i] = "Ascending";
                        }else{
                        $its4you_reports4you_sortcol_arr[$sortcol_i] = "Descending";
                        } */
                     $its4you_reports4you_sortcol_arr[4] = "rows";
                     $its4you_reports4you_sortcol_arr[5] = "";
                     $its4you_reports4you_sortcol_arr[6] = "";
                 }
                 $Values = array_merge($Values, $its4you_reports4you_sortcol_arr);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_relcriteria"]) && !empty($ReportSql["its4you_reports4you_relcriteria"])) {
             $its4you_reports4you_relcriteria = vtlib_purify($ReportSql["its4you_reports4you_relcriteria"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) VALUES (?,?,?,?,?,?,?)",array($its4you_reports4you_relcriteria_arr));
             $sql = "INSERT INTO its4you_reports4you_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_relcriteria as $its4you_reports4you_relcriteria_arr) {
                 $Qarray[] = '(?,?,?,?,?,?,?)';
                 foreach ($its4you_reports4you_relcriteria_arr as $rc_k => $rc_v) {
                     $its4you_reports4you_relcriteria_arr[$rc_k] = html_entity_decode($rc_v, ENT_QUOTES, $default_charset);
                 }
                 $Values = array_merge($Values, $its4you_reports4you_relcriteria_arr);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_relcriteria_grouping"]) && !empty($ReportSql["its4you_reports4you_relcriteria_grouping"])) {
             $its4you_reports4you_relcriteria_grouping = vtlib_purify($ReportSql["its4you_reports4you_relcriteria_grouping"]);
             //$adb->pquery("INSERT INTO its4you_reports4you_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) VALUES (?,?,?,?)",array($its4you_reports4you_relcriteria_grouping_arr));
             $sql = "INSERT INTO its4you_reports4you_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) VALUES ";
             $Qarray = array();
             $Values = array();
             foreach ($its4you_reports4you_relcriteria_grouping as $its4you_reports4you_relcriteria_grouping_arr) {
                 $Qarray[] = '(?,?,?,?)';
                 $Values = array_merge($Values, $its4you_reports4you_relcriteria_grouping_arr);
             }
             $adb->pquery($sql . implode(', ', $Qarray), $Values);
         }
         if (isset($ReportSql["its4you_reports4you_datefilter"]) && !empty($ReportSql["its4you_reports4you_datefilter"])) {
             $its4you_reports4you_datefilter = vtlib_purify($ReportSql["its4you_reports4you_datefilter"]);
             $adb->pquery("INSERT INTO its4you_reports4you_datefilter (DATEFILTERID,DATECOLUMNNAME,DATEFILTER,STARTDATE,ENDDATE) VALUES (?,?,?,?,?)", array($its4you_reports4you_datefilter));
         }
         if (isset($ReportSql["its4you_reports4you_charts"]) && !empty($ReportSql["its4you_reports4you_charts"])) {
             foreach ($ReportSql["its4you_reports4you_charts"] as $its4you_reports4you_charts) {
                 $adb->pquery("INSERT INTO its4you_reports4you_charts (reports4youid,charttype,dataseries,charttitle,chart_seq,x_group) VALUES (?,?,?,?,?,?)", array($its4you_reports4you_charts));
             }
         }
         $adb->pquery("INSERT INTO its4you_reports4you_selectquery (`queryid`, `startindex`, `numofobjects`) VALUES (?, '0', '0')", array($report_id));
         $adb->pquery("UPDATE its4you_reports4you_selectquery_seq SET id = ?", array($report_id));
         $new_imported_file = str_replace("reports/", "reports/imported/", $file_to_import);
         if (!copy($file_to_import, $new_imported_file)) {
             $return = vtranslate("LBL_COPPY_FAILED", $this->currentModule) . " {$file_to_import}" . vtranslate("LBL_DOTS", $this->currentModule) . "<br />";
         } else {
             $return = vtranslate("LBL_IMPORT_SUCCESS", $this->currentModule) . " {$new_imported_file}" . vtranslate("LBL_DOTS", $this->currentModule) . "<br />";
         }
         //$adb->setDebug(false);
     } else {
         $return = vtranslate("LBL_FILE_NOT_SUPPORTED", $this->currentModule) . vtranslate("LBL_DOTS", $this->currentModule) . "<br />";
     }
     if ($debug) {
         $adb->setDebug(false);
     }
     return $return;
 }
예제 #8
0
 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;
     }
 }
예제 #9
0
 function getEntityNameSQL($module, $fieldid_alias = "")
 {
     $entity_fields = "";
     $adb = PEARDatabase::getInstance();
     if (vtlib_isModuleActive($module)) {
         $uirel_tabid = getTabid($module);
         $uirel_focus = CRMEntity::getInstance($module);
         $uirel_table_name = $uirel_focus->table_name;
         $uirel_table_index = $uirel_focus->table_index;
         $table_alias = $uirel_table_name . $fieldid_alias;
         $entity_field_arr = $this->getEntityNameFields($module, $table_alias);
         $entity_fields = " WHEN {$table_alias}.{$uirel_table_index} IS NOT NULL THEN " . $entity_field_arr["fieldname"] . " ";
     }
     return $entity_fields;
 }
예제 #10
0
 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;
 }
예제 #11
0
 public function getMoreInfoJoinSQL(&$join_array, &$columns_array)
 {
     $adb = PEARDatabase::getInstance();
     $primary_obj = CRMEntity::getInstance($this->params["primarymodule"]);
     $secondary_obj = CRMEntity::getInstance($this->params["relatedmodule"]);
     if (method_exists($primary_obj, setRelationTables)) {
         $rel_array = $primary_obj->setRelationTables($this->params["relatedmodule"]);
     } else {
         $rel_array = array("vtiger_crmentityrel" => array("crmid", "relcrmid"), "" . $primary_obj->table_name . "" => "" . $primary_obj->table_index . "");
     }
     foreach ($rel_array as $key => $value) {
         $tables[] = $key;
         $fields[] = $value;
     }
     $j_table_name = $tables[0];
     $j_table_index = $fields[0][1];
     $j_table_usingindex = $fields[0][0];
     $s_table_name = $secondary_obj->table_name;
     $s_table_index = $secondary_obj->table_index;
     $join_array[$j_table_name]["joincol"] = $j_table_name . "." . $j_table_usingindex;
     $join_array[$s_table_name]["joincol"] = $s_table_name . "." . $j_table_index;
     $join_array[$s_table_name]["using"] = $j_table_name . "." . $j_table_usingindex;
     if ($this->params["column_tablename"] != $j_table_name) {
         $join_array[$this->params["column_tablename"]]["joincol"] = $this->params["column_tablename"] . "." . $secondary_obj->tab_name_index[$this->params["column_tablename"]];
         $join_array[$this->params["column_tablename"]]["using"] = $j_table_name . "." . $j_table_index;
     }
     $columns_array_value = $this->params["column_tablename"] . "." . $this->params["column_fieldname"];
     $fld_alias = $this->params["column_fieldname"];
     $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_alias;
     $columns_array["uitype_{$fld_alias}"] = $this->params["field_uitype"];
     $columns_array[$fld_alias] = $this->params["fld_string"];
 }