function generateAdvFilterSql($advfilterlist)
{
$adb = PearDatabase::getInstance();
$advfiltersql = "";
$customView = new CustomView();
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advfilterlist as $groupindex => $groupinfo) {
$groupcondition = $groupinfo['condition'];
$groupcolumns = $groupinfo['columns'];
if (count($groupcolumns) > 0) {
$advfiltergroupsql = "";
foreach ($groupcolumns as $columnindex => $columninfo) {
$fieldcolname = $columninfo["columnname"];
$comparator = $columninfo["comparator"];
$value = $columninfo["value"];
$columncondition = $columninfo["column_condition"];
$advcolsql = array();
if ($fieldcolname != "" && $comparator != "") {
if (in_array($comparator, $dateSpecificConditions)) {
if ($fieldcolname != 'none') {
$selectedFields = explode(':', $fieldcolname);
if ($selectedFields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$selectedFields[0] = 'vtiger_crmentity';
}
if ($comparator != 'custom') {
list($startDate, $endDate) = $this->getStandarFiltersStartAndEndDate($comparator);
} else {
list($startDateTime, $endDateTime) = explode(',', $value);
list($startDate, $startTime) = explode(' ', $startDateTime);
list($endDate, $endTime) = explode(' ', $endDateTime);
}
$type = $selectedFields[4];
if ($startDate != '0000-00-00' && $endDate != '0000-00-00' && $startDate != '' && $endDate != '') {
$startDateTime = new DateTimeField($startDate . ' ' . date('H:i:s'));
$userStartDate = $startDateTime->getDisplayDate();
if ($type == 'DT') {
$userStartDate = $userStartDate . ' 00:00:00';
}
$startDateTime = getValidDBInsertDateTimeValue($userStartDate);
$endDateTime = new DateTimeField($endDate . ' ' . date('H:i:s'));
$userEndDate = $endDateTime->getDisplayDate();
if ($type == 'DT') {
$userEndDate = $userEndDate . ' 23:59:59';
}
$endDateTime = getValidDBInsertDateTimeValue($userEndDate);
if ($selectedFields[1] == 'birthday') {
$tableColumnSql = 'DATE_FORMAT(' . $selectedFields[0] . '.' . $selectedFields[1] . ', "%m%d")';
$startDateTime = "DATE_FORMAT('{$startDateTime}', '%m%d')";
$endDateTime = "DATE_FORMAT('{$endDateTime}', '%m%d')";
} else {
if ($selectedFields[0] == 'vtiger_activity' && $selectedFields[1] == 'date_start') {
$tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)';
} else {
$tableColumnSql = $selectedFields[0] . '.' . $selectedFields[1];
}
$startDateTime = "'{$startDateTime}'";
$endDateTime = "'{$endDateTime}'";
}
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN {$startDateTime} AND {$endDateTime}";
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedFields[0]);
}
}
continue;
}
$selectedFields = explode(":", $fieldcolname);
$tempComparators = array('e', 'n', 'bw', 'a', 'b');
if ($selectedFields[4] == 'DT' && in_array($comparator, $tempComparators)) {
if ($selectedFields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$selectedFields[0] = 'vtiger_crmentity';
}
if ($selectedFields[0] == 'vtiger_activity' && $selectedFields[1] == 'date_start') {
$tableColumnSql = 'CAST((CONCAT(date_start, " ", time_start)) AS DATETIME)';
} else {
$tableColumnSql = $selectedFields[0] . '.' . $selectedFields[1];
}
if ($value != null && $value != '') {
if ($comparator == 'e' || $comparator == 'n') {
$dateTimeComponents = explode(' ', $value);
$dateTime = new DateTime($dateTimeComponents[0] . ' ' . '00:00:00');
$date1 = $dateTime->format('Y-m-d H:i:s');
$dateTime->modify("+1 days");
$date2 = $dateTime->format('Y-m-d H:i:s');
$tempDate = strtotime($date2) - 1;
$date2 = date('Y-m-d H:i:s', $tempDate);
$start = getValidDBInsertDateTimeValue($date1);
$end = getValidDBInsertDateTimeValue($date2);
$start = "'{$start}'";
$end = "'{$end}'";
if ($comparator == 'e') {
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN {$start} AND {$end}";
} else {
$advfiltergroupsql .= "{$tableColumnSql} NOT BETWEEN {$start} AND {$end}";
}
} else {
if ($comparator == 'bw') {
$values = explode(',', $value);
$startDateTime = explode(' ', $values[0]);
$endDateTime = explode(' ', $values[1]);
$startDateTime = new DateTimeField($startDateTime[0] . ' ' . date('H:i:s'));
$userStartDate = $startDateTime->getDisplayDate();
$userStartDate = $userStartDate . ' 00:00:00';
$start = getValidDBInsertDateTimeValue($userStartDate);
$endDateTime = new DateTimeField($endDateTime[0] . ' ' . date('H:i:s'));
$userEndDate = $endDateTime->getDisplayDate();
$userEndDate = $userEndDate . ' 23:59:59';
$end = getValidDBInsertDateTimeValue($userEndDate);
$advfiltergroupsql .= "{$tableColumnSql} BETWEEN '{$start}' AND '{$end}'";
} else {
if ($comparator == 'a' || $comparator == 'b') {
$value = explode(' ', $value);
$dateTime = new DateTime($value[0]);
if ($comparator == 'a') {
$modifiedDate = $dateTime->modify('+1 days');
$nextday = $modifiedDate->format('Y-m-d H:i:s');
$temp = strtotime($nextday) - 1;
$date = date('Y-m-d H:i:s', $temp);
$value = getValidDBInsertDateTimeValue($date);
$advfiltergroupsql .= "{$tableColumnSql} > '{$value}'";
} else {
$prevday = $dateTime->format('Y-m-d H:i:s');
$temp = strtotime($prevday) - 1;
$date = date('Y-m-d H:i:s', $temp);
$value = getValidDBInsertDateTimeValue($date);
$advfiltergroupsql .= "{$tableColumnSql} < '{$value}'";
}
}
}
}
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedFields[0]);
}
continue;
}
$selectedfields = explode(":", $fieldcolname);
$moduleFieldLabel = $selectedfields[2];
list($moduleName, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => $selectedfields[0] . ".first_name", 'last_name' => $selectedfields[0] . ".last_name"), 'Users');
// Added to handle the crmentity table name for Primary module
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$selectedfields[0] = "vtiger_crmentity";
}
//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";
}
}
if (in_array($comparator, $dateSpecificConditions)) {
$customView = new CustomView($moduleName);
$columninfo['stdfilter'] = $columninfo['comparator'];
$valueComponents = explode(',', $columninfo['value']);
if ($comparator == 'custom') {
if ($selectedfields[4] == 'DT') {
$startDateTimeComponents = explode(' ', $valueComponents[0]);
$endDateTimeComponents = explode(' ', $valueComponents[1]);
$columninfo['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]);
$columninfo['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]);
} else {
$columninfo['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$columninfo['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($columninfo);
$startDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['startdate']);
$endDate = DateTimeField::convertToDBFormat($dateFilterResolvedList['enddate']);
$columninfo['value'] = $value = implode(',', array($startDate, $endDate));
$comparator = 'bw';
}
$valuearray = explode(",", trim($value));
$datatype = isset($selectedfields[4]) ? $selectedfields[4] : "";
if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
$advcolumnsql = "";
for ($n = 0; $n < count($valuearray); $n++) {
if (($selectedfields[0] == "vtiger_users" . $this->primarymodule || $selectedfields[0] == "vtiger_users" . $this->secondarymodule) && $selectedfields[1] == 'user_name') {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$advcolsql[] = " (trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype) . " or vtiger_groups" . $module_from_tablename . ".groupname " . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype) . ")";
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
} elseif ($selectedfields[1] == 'status') {
//when you use comma seperated values.
if ($selectedfields[2] == 'Calendar_Status') {
$advcolsql[] = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
if ($selectedfields[2] == 'HelpDesk_Status') {
$advcolsql[] = "vtiger_troubletickets.status" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
if ($selectedfields[2] == 'Faq_Status') {
$advcolsql[] = "vtiger_faq.status" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
}
}
} elseif ($selectedfields[1] == 'description') {
//when you use comma seperated values.
if ($selectedfields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$advcolsql[] = "vtiger_crmentity.description" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$advcolsql[] = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} elseif ($selectedfields[1] == 'modifiedby') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_lastModifiedBy' . $module_from_tablename;
} else {
$tableName = 'vtiger_lastModifiedBy' . $this->primarymodule;
}
$advcolsql[] = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$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 . ") ";
} elseif ($selectedfields[1] == 'user_name') {
if ($selectedfields[0] == "vtiger_users" . $this->primarymodule) {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$fieldvalue = " trim(case when (" . $selectedfields[0] . ".last_name NOT LIKE '') then " . $concatSql . " else vtiger_groups" . $module_from_tablename . ".groupname end) " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
} else {
$secondaryModules = explode(':', $this->secondarymodule);
$firstSecondaryModule = "vtiger_users" . $secondaryModules[0];
$secondSecondaryModule = "vtiger_users" . $secondaryModules[1];
if ($firstSecondaryModule && $firstSecondaryModule == $selectedfields[0] || $secondSecondaryModule && $secondSecondaryModule == $selectedfields[0]) {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$moduleInstance = CRMEntity::getInstance($module_from_tablename);
$fieldvalue = " trim(case when (" . $selectedfields[0] . ".last_name NOT LIKE '') then " . $concatSql . " else vtiger_groups" . $module_from_tablename . ".groupname end) " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_groups" . $module_from_tablename);
$this->queryPlanner->addTable($moduleInstance->table_name);
}
}
} elseif ($comparator == 'bw' && count($valuearray) == 2) {
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "(" . "vtiger_crmentity." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
}
} elseif ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "vtiger_crmentity." . $selectedfields[1] . " " . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$fieldvalue = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[1] == 'modifiedby') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_lastModifiedBy' . $module_from_tablename;
} else {
$tableName = 'vtiger_lastModifiedBy' . $this->primarymodule;
}
$this->queryPlanner->addTable($tableName);
$fieldvalue = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[1] == 'smcreatorid') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_createdby' . $module_from_tablename;
} else {
$tableName = 'vtiger_createdby' . $this->primarymodule;
}
if ($moduleName == 'ModComments') {
$tableName = 'vtiger_users' . $moduleName;
}
$this->queryPlanner->addTable($tableName);
$fieldvalue = 'trim(' . getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . ')' . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[0] == "vtiger_activity" && ($selectedfields[1] == 'status' || $selectedfields[1] == 'eventstatus')) {
// for "Is Empty" condition we need to check with "value NOT NULL" OR "value = ''" conditions
if ($comparator == 'y') {
$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status\n else vtiger_activity.eventstatus end) IS NULL OR (case when (vtiger_activity.status not like '')\n then vtiger_activity.status else vtiger_activity.eventstatus end) = ''";
} else {
$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status\n else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($value), $datatype);
}
} else {
if ($comparator == 'ny') {
if ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NOT NULL AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '' AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '0')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NOT NULL AND " . $selectedfields[0] . "." . $selectedfields[1] . " != '')";
}
} elseif ($comparator == 'y' || $comparator == 'e' && (trim($value) == "NULL" || trim($value) == '')) {
if ($selectedfields[0] == 'vtiger_inventoryproductrel') {
$selectedfields[0] = 'vtiger_inventoryproductrel' . $moduleName;
}
if ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '' OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '0')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '')";
}
} elseif ($selectedfields[0] == 'vtiger_inventoryproductrel') {
if ($selectedfields[1] == 'productid') {
$fieldvalue = "vtiger_products{$moduleName}.productname " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_products{$moduleName}");
} else {
if ($selectedfields[1] == 'serviceid') {
$fieldvalue = "vtiger_service{$moduleName}.servicename " . $this->getAdvComparator($comparator, trim($value), $datatype);
$this->queryPlanner->addTable("vtiger_service{$moduleName}");
} else {
//for inventory module table should be follwed by the module name
$selectedfields[0] = 'vtiger_inventoryproductrel' . $moduleName;
$fieldvalue = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, $value, $datatype);
}
}
} elseif ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
$comparatorValue = $this->getAdvComparator($comparator, trim($value), $datatype, $fieldSqlColumns[0]);
$fieldSqls = array();
foreach ($fieldSqlColumns as $columnSql) {
$fieldSqls[] = $columnSql . $comparatorValue;
}
$fieldvalue = ' (' . implode(' OR ', $fieldSqls) . ') ';
} else {
$fieldvalue = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($value), $datatype);
}
}
$advfiltergroupsql .= $fieldvalue;
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
$this->queryPlanner->addTable($selectedfields[0]);
}
}
if (trim($advfiltergroupsql) != "") {
$advfiltergroupsql = "( {$advfiltergroupsql} ) ";
if (!empty($groupcondition)) {
$advfiltergroupsql .= ' ' . $groupcondition . ' ';
}
$advfiltersql .= $advfiltergroupsql;
}
}
}
if (trim($advfiltersql) != "") {
$advfiltersql = '(' . $advfiltersql . ')';
}
return $advfiltersql;
}
function generateAdvFilterSql($advfilterlist)
{
global $adb;
$advfiltersql = "";
foreach ($advfilterlist as $groupindex => $groupinfo) {
$groupcondition = $groupinfo['condition'];
$groupcolumns = $groupinfo['columns'];
if (count($groupcolumns) > 0) {
$advfiltergroupsql = "";
foreach ($groupcolumns as $columnindex => $columninfo) {
$fieldcolname = $columninfo["columnname"];
$comparator = $columninfo["comparator"];
$value = $columninfo["value"];
$columncondition = $columninfo["column_condition"];
if ($fieldcolname != "" && $comparator != "") {
$selectedfields = explode(":", $fieldcolname);
$moduleFieldLabel = $selectedfields[2];
list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel);
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => $selectedfields[0] . ".first_name", 'last_name' => $selectedfields[0] . ".last_name"), 'Users');
// Added to handle the crmentity table name for Primary module
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$selectedfields[0] = "vtiger_crmentity";
}
//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] : "";
$secondarymodules = explode(':', $this->secondarymodule);
array_walk($secondarymodules, function (&$val) {
$val = 'vtiger_users' . $val;
});
if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
$advcolumnsql = "";
for ($n = 0; $n < count($valuearray); $n++) {
if (($selectedfields[0] == 'vtiger_users' . $this->primarymodule || in_array($selectedfields[0], $secondarymodules)) && $selectedfields[1] == 'user_name') {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$advcolsql[] = " trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype) . " or vtiger_groups" . $module_from_tablename . ".groupname " . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} elseif ($selectedfields[1] == 'status') {
//when you use comma seperated values.
if ($selectedfields[2] == 'Calendar_Status') {
$advcolsql[] = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} elseif ($selectedfields[2] == 'HelpDesk_Status') {
$advcolsql[] = "vtiger_troubletickets.status" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
} elseif ($selectedfields[1] == 'description') {
//when you use comma seperated values.
if ($selectedfields[0] == 'vtiger_crmentity' . $this->primarymodule) {
$advcolsql[] = "vtiger_crmentity.description" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$advcolsql[] = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
}
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$advcolsql[] = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($valuearray[$n]), $datatype);
} else {
$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 . ") ";
} elseif (($selectedfields[0] == 'vtiger_users' . $this->primarymodule || in_array($selectedfields[0], $secondarymodules)) && $selectedfields[1] == 'user_name') {
$module_from_tablename = str_replace("vtiger_users", "", $selectedfields[0]);
$fieldvalue = " trim(case when (" . $selectedfields[0] . ".last_name NOT LIKE '') then " . $concatSql . " else vtiger_groups" . $module_from_tablename . ".groupname end) " . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($comparator == 'bw' && count($valuearray) == 2) {
if ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "(" . "vtiger_crmentity." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
} else {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " between '" . trim($valuearray[0]) . "' and '" . trim($valuearray[1]) . "')";
}
} elseif ($selectedfields[0] == "vtiger_crmentity" . $this->primarymodule) {
$fieldvalue = "vtiger_crmentity." . $selectedfields[1] . " " . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[2] == 'Quotes_Inventory_Manager') {
$fieldvalue = "trim({$concatSql})" . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[1] == 'modifiedby') {
$module_from_tablename = str_replace("vtiger_crmentity", "", $selectedfields[0]);
if ($module_from_tablename != '') {
$tableName = 'vtiger_lastModifiedBy' . $module_from_tablename;
} else {
$tableName = 'vtiger_lastModifiedBy' . $this->primarymodule;
}
$fieldvalue = getSqlForNameInDisplayFormat(array('last_name' => "{$tableName}.last_name", 'first_name' => "{$tableName}.first_name"), 'Users') . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($selectedfields[0] == "vtiger_activity" && $selectedfields[1] == 'status') {
$fieldvalue = "(case when (vtiger_activity.status not like '') then vtiger_activity.status else vtiger_activity.eventstatus end)" . $this->getAdvComparator($comparator, trim($value), $datatype);
} elseif ($comparator == 'e' && (trim($value) == "NULL" || trim($value) == '')) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '')";
} elseif ($comparator == 'e' && $datatype == 'D' && (trim($value) == "--\$" || trim($value) == '$')) {
$fieldvalue = "(" . $selectedfields[0] . "." . $selectedfields[1] . " IS NULL OR " . $selectedfields[0] . "." . $selectedfields[1] . " = '')";
} elseif (substr($selectedfields[0], 0, 26) == 'vtiger_inventoryproductrel' && ($selectedfields[1] == 'productid' || $selectedfields[1] == 'serviceid' || $selectedfields[1] == 'discount')) {
$invmod = in_array($this->primarymodule, getInventoryModules()) ? $this->primarymodule : $this->secondarymodule;
if ($selectedfields[1] == 'productid') {
$fieldvalue = "vtiger_products{$invmod}.productname " . $this->getAdvComparator($comparator, trim($value), $datatype);
} else {
if ($selectedfields[1] == 'serviceid') {
$fieldvalue = "vtiger_service{$invmod}.servicename " . $this->getAdvComparator($comparator, trim($value), $datatype);
} else {
if ($selectedfields[1] == 'discount') {
$fieldvalue = "(vtiger_inventoryproductrel{$invmod}.discount_amount " . $this->getAdvComparator($comparator, trim($value), $datatype) . "\n\t\t\t\t\t\t\t\t\tOR ROUND((vtiger_inventoryproductrel{$invmod}.listprice * vtiger_inventoryproductrel{$invmod}.quantity * (vtiger_inventoryproductrel{$invmod}.discount_percent/100)),3) " . $this->getAdvComparator($comparator, trim($value), $datatype) . ") ";
}
}
}
} elseif ($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) {
$comparatorValue = $this->getAdvComparator($comparator, trim($value), $datatype);
$fieldSqls = array();
$fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo);
foreach ($fieldSqlColumns as $columnSql) {
$fieldSqls[] = $columnSql . $comparatorValue;
}
$fieldvalue = ' (' . implode(' OR ', $fieldSqls) . ') ';
} else {
$fieldvalue = $selectedfields[0] . "." . $selectedfields[1] . $this->getAdvComparator($comparator, trim($value), $datatype);
}
$advfiltergroupsql .= $fieldvalue;
if (!empty($columncondition)) {
$advfiltergroupsql .= ' ' . $columncondition . ' ';
}
}
}
if (trim($advfiltergroupsql) != "") {
$advfiltergroupsql = "( {$advfiltergroupsql} ) ";
if (!empty($groupcondition)) {
$advfiltergroupsql .= ' ' . $groupcondition . ' ';
}
$advfiltersql .= $advfiltergroupsql;
}
}
}
if (trim($advfiltersql) != "") {
$advfiltersql = '(' . $advfiltersql . ')';
}
return $advfiltersql;
}
