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