function getReqAdvFilterSql($reportid, $join_array) { $adb = PEARDatabase::getInstance(); $conditions = array(); $advfiltersql = ""; // ITS4YOU-CR SlOl 28. 3. 2014 10:34:19 to get select options array // $ITS4YouReports = new ITS4YouReports($record); $ITS4YouReports = $this->report_obj; // ADV FILTER START $ITS4YouReports->getPriModuleColumnsList($ITS4YouReports->primarymodule); if (!empty($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule])) { foreach ($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule] as $key => $value) { $secondarymodules[] = $value["id"]; } $secondary_modules_str = implode(":", $secondarymodules); } $ITS4YouReports->getSecModuleColumnsList($secondary_modules_str); $sel_fields = $ITS4YouReports->adv_sel_fields; $conditions_rc = $this->getReqConditions($sel_fields); // ITS4YOU-END 28. 3. 2014 10:34:37 $last_gid = $l_groupcondition = ""; $std_filter_columns = $this->report_obj->getStdFilterColumns(); foreach ($conditions_rc as $groupid => $conditions) { $d_i = 1; $d_num_rows = count($conditions); foreach ($conditions as $key => $d_conditions) { $advfiltergroupsql = ""; $groupid = $d_conditions["groupid"]; if ($last_gid == "") { $last_gid = $groupid; } $dkey = $d_conditions["dkey"]; $from_req = $d_conditions["from_req"]; $column_condition = ""; $fieldcolname = $d_conditions["fcol{$dkey}"]; $comparator = $d_conditions["fop{$dkey}"]; $value = $d_conditions["fval{$dkey}"]; // ? fval_ if (isset($d_conditions["fcon{$dkey}"])) { $column_condition = $d_conditions["fcon{$dkey}"]; } if (isset($d_conditions["gpcon{$dkey}"])) { $groupcondition = $d_conditions["gpcon{$dkey}"]; } if (isset($d_conditions["f_type{$dkey}"])) { $f_type = $d_conditions["f_type{$dkey}"]; } if ($fieldcolname != "" && ($comparator == "isn" || $comparator == "isnn")) { if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $fld_cond = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $fld_arr = explode(":", $fieldcolname); $fld_cond = $fld_arr[0] . "." . $fld_arr[1]; } if ($comparator == "isn") { $advfiltergroupsql .= " (" . $fld_cond . " IS NULL OR " . $fld_cond . " = '') "; } else { $advfiltergroupsql .= " (" . $fld_cond . " IS NOT NULL AND " . $fld_cond . " != '') "; } } elseif ($fieldcolname != "" && $comparator != "" && $f_type != "S" && !in_array($fieldcolname, $std_filter_columns)) { $selectedfields = explode(":", $fieldcolname); //Added to handle yes or no for checkbox field in reports advance filters. -shahul if ($selectedfields[4] == 'C') { if (strcasecmp(trim($value), "yes") == 0) { $value = "1"; } if (strcasecmp(trim($value), "no") == 0) { $value = "0"; } } $valuearray = explode(", ", trim($value)); $datatype = isset($selectedfields[4]) ? $selectedfields[4] : ""; // ADV FILTER CONDITIONS START if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') { $advcolumnsql = ""; for ($n = 0; $n < count($valuearray); $n++) { $advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype); } //If negative logic filter ('not equal to', 'does not contain') is used, 'and' condition should be applied instead of 'or' if ($comparator == 'n' || $comparator == 'k') { $advcolumnsql = implode(" and ", $advcolsql); } else { $advcolumnsql = implode(" or ", $advcolsql); } $fieldvalue = " (" . $advcolumnsql . ") "; } else { // ITS4YOU-UP SlOl 9. 1. 2014 13:44:18 $last_key = count($selectedfields) - 1; $exploded_name = explode("_", $selectedfields[2]); $field_module_id = getTabid($exploded_name[0]); if (is_numeric($selectedfields[$last_key])) { $tablename = $selectedfields[0]; $fieldname = $selectedfields[3]; $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0); $selected_field_col_table = trim($selectedfields[0], $selectedfields[$last_key]); if (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $field_row["fieldid"] . " ", $join_array)) { $selected_field_col = $selected_field_col_table . "_" . $field_row["fieldid"] . "." . $selectedfields[1]; } elseif (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $selectedfields[$last_key] . " ", $join_array)) { $selected_field_col = $selected_field_col_table . "_" . $selectedfields[$last_key] . "." . $selectedfields[1]; } } elseif (isset($this->columns_array[$fieldcolname]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } elseif ($selectedfields[$last_key] == "MIF") { $alias = "mif_" . $field_module_id; $selected_field_col = trim($selectedfields[0], "MIF") . $alias . "." . $selectedfields[1]; } else { $tablename = $selectedfields[0]; $fieldname = $selectedfields[3]; $field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0); if ($tablename == "vtiger_inventoryproductrel") { $field_uitype = "INV"; $params = array("fieldid" => $selectedfields[$last_key]); $uifactory = new UIFactory($field_row); $selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields); } elseif (!empty($field_row)) { $field_uitype = $field_row["uitype"]; //$uifactory = new UIFactory($field_row); //$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields); if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $table_alias = $tablename . "_{$field_uitype}"; $column_alias = $fieldname; $selected_field_col = $table_alias . "." . $column_alias; } } elseif (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $selected_field_col = $selectedfields[0] . "." . $selectedfields[1]; } } // ITS4YOU-END 9. 1. 2014 13:44:22 $fieldvalue = $selected_field_col . $this->getAdvComparator($comparator, trim($value), $datatype); } // ADV FILTER CONDITIONS END $advfiltergroupsql .= $fieldvalue; } elseif ($f_type == "S") { $fld_arr = explode(":", $fieldcolname); if ($fld_arr[3] == "assigned_user_id" && strpos($value, "Current User") !== false) { $value = trim($value, "('"); $value = trim($value, "')"); $valuearray = explode("', '", $value); $valuearray = $this->getConditionCurrentUserName($valuearray); $value = "('" . implode("', '", $valuearray) . "')"; } $advfiltergroupsql .= $this->getSelFieldsWhereSQL($fieldcolname, $comparator, $value, $fld_arr); /*if ($comparator == "n") { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " NOT IN " . $value; } else { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " IN " . $value; }*/ } elseif (in_array($fieldcolname, $std_filter_columns)) { if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { $fld_cond = $this->columns_array[$fieldcolname]["fld_cond"]; } else { $fld_arr = explode(":", $fieldcolname); $fld_cond = $fld_arr[0] . "." . $fld_arr[1]; } $advfiltergroupsql .= $fld_cond . " " . $this->getStdComparator($comparator, trim($value)); } else { $column_info = explode(":", $fieldcolname); $temp_val = explode(", ", $value); if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $value != '') && !in_array($fieldcolname, $std_filter_columns)) { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if (trim($temp_val[$x]) != '') { $date = new DateTimeField(trim($temp_val[$x])); if ($column_info[4] == 'D') { $val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x])); } elseif ($column_info[4] == 'DT') { $val[$x] = $date->getDBInsertDateTimeValue(); } else { $val[$x] = $date->getDBInsertTimeValue(); } } } $value = implode(", ", $val); } if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") { if (in_array($fieldcolname, $std_filter_columns)) { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getStdComparator($comparator, trim($value)); } else { $advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getAdvComparator($comparator, trim($value)); } } else { $this->adv_fcol[] = $fieldcolname; // $selectedfields = explode(":", $fieldcolname); if (in_array($fieldcolname, $std_filter_columns)) { $advfiltergroupsql .= $fieldcolname . " " . $this->getStdComparator($comparator, trim($value)); } else { $advfiltergroupsql .= $fieldcolname . " " . $this->getAdvComparator($comparator, trim($value)); } } } if (trim($advfiltergroupsql) != "") { $advfiltergroupsql = "( {$advfiltergroupsql} ) "; if ($column_condition != NULL && $column_condition != '' && $d_i < $d_num_rows) { $advfiltergroupsql .= ' ' . $column_condition . ' '; } if ($l_groupcondition != NULL && $l_groupcondition != '' && $groupid != $last_gid) { $advfiltergroupsql = ' ' . $l_groupcondition . ' ' . $advfiltergroupsql; $l_groupcondition = $groupcondition; $last_gid = $groupid; } else { $l_groupcondition = $groupcondition; $last_gid = $groupid; } $advfiltersql .= $advfiltergroupsql; } $d_i++; } if (trim($advfiltersql) != "") { $advfiltersql = '(' . $advfiltersql . ')'; } } // Save the information return $advfiltersql; }