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; }
public function parseAdvFilterList($advFilterList, $glue = '') { if (!empty($glue)) { $this->addConditionGlue($glue); } $customView = new CustomView($this->module); $dateSpecificConditions = $customView->getStdFilterConditions(); foreach ($advFilterList as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $this->startGroup(''); foreach ($filtercolumns as $index => $filter) { $nameComponents = explode(':', $filter['columnname']); // For Events "End Date & Time" field datatype should be DT. But, db will give D for due_date field if ($nameComponents[2] == 'due_date' && $nameComponents[3] == 'Events_End_Date_&_Time') { $nameComponents[4] = 'DT'; } if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') { $name = $this->getSQLColumn('id'); } else { $name = $nameComponents[2]; } if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) { $filter['stdfilter'] = $filter['comparator']; $valueComponents = explode(',', $filter['value']); if ($filter['comparator'] == 'custom') { if ($nameComponents[4] == 'DT') { $startDateTimeComponents = explode(' ', $valueComponents[0]); $endDateTimeComponents = explode(' ', $valueComponents[1]); $filter['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]); } else { $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); } } $dateFilterResolvedList = $customView->resolveDateFilterValue($filter); // If datatype is DT then we should append time also if ($nameComponents[4] == 'DT') { $startdate = explode(' ', $dateFilterResolvedList['startdate']); if ($startdate[1] == '') { $startdate[1] = '00:00:00'; } $dateFilterResolvedList['startdate'] = $startdate[0] . ' ' . $startdate[1]; $enddate = explode(' ', $dateFilterResolvedList['enddate']); if ($enddate[1] == '') { $enddate[1] = '23:59:59'; } $dateFilterResolvedList['enddate'] = $enddate[0] . ' ' . $enddate[1]; } $value = array(); $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['startdate']); $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false); $this->addCondition($name, $value, 'BETWEEN'); } else { if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'e' || $filter['comparator'] == 'n')) { $filter['stdfilter'] = $filter['comparator']; $dateTimeComponents = explode(' ', $filter['value']); $filter['startdate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]); $startDate = $this->fixDateTimeValue($name, $filter['startdate']); $endDate = $this->fixDateTimeValue($name, $filter['enddate'], false); $value = array(); $start = explode(' ', $startDate); if ($start[1] == "") { $startDate = $start[0] . ' ' . '00:00:00'; } $end = explode(' ', $endDate); if ($end[1] == "") { $endDate = $end[0] . ' ' . '23:59:59'; } $value[] = $startDate; $value[] = $endDate; if ($filter['comparator'] == 'n') { $this->addCondition($name, $value, 'NOTEQUAL'); } else { $this->addCondition($name, $value, 'BETWEEN'); } } else { if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'a' || $filter['comparator'] == 'b')) { $dateTime = explode(' ', $filter['value']); $date = DateTimeField::convertToDBFormat($dateTime[0]); $value = array(); $value[] = $this->fixDateTimeValue($name, $date, false); // Still fixDateTimeValue returns only date value, we need to append time because it is DT type for ($i = 0; $i < count($value); $i++) { $values = explode(' ', $value[$i]); if ($values[1] == '') { $values[1] = '00:00:00'; } $value[$i] = $values[0] . ' ' . $values[1]; } $this->addCondition($name, $value, $filter['comparator']); } else { $this->addCondition($name, $filter['value'], $filter['comparator']); } } } $columncondition = $filter['column_condition']; if (!empty($columncondition)) { $this->addConditionGlue($columncondition); } } $this->endGroup(); $groupConditionGlue = $groupcolumns['condition']; if (!empty($groupConditionGlue)) { $this->addConditionGlue($groupConditionGlue); } } } }
/** * Function to display the Search Results * @param Vtiger_Request $request */ function showSearchResults(Vtiger_Request $request) { $db = PearDatabase::getInstance(); $viewer = $this->getViewer($request); $moduleName = $request->getModule(); $advFilterList = $request->get('advfilterlist'); //used to show the save modify filter option $isAdvanceSearch = false; $matchingRecords = array(); if (is_array($advFilterList) && count($advFilterList) > 0) { $isAdvanceSearch = true; $user = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($moduleName, $user); $queryGenerator->setFields(['id']); vimport('~modules/CustomView/CustomView.php'); $customView = new CustomView($moduleName); $dateSpecificConditions = $customView->getStdFilterConditions(); foreach ($advFilterList as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $queryGenerator->startGroup(''); foreach ($filtercolumns as $index => $filter) { $nameComponents = explode(':', $filter['columnname']); if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') { $name = $queryGenerator->getSQLColumn('id'); } else { $name = $nameComponents[2]; } if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) { $filter['stdfilter'] = $filter['comparator']; $valueComponents = explode(',', $filter['value']); if ($filter['comparator'] == 'custom') { $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); } $dateFilterResolvedList = $customView->resolveDateFilterValue($filter); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false); $queryGenerator->addCondition($name, $value, 'BETWEEN'); } else { $queryGenerator->addCondition($name, $filter['value'], $filter['comparator']); } $columncondition = $filter['column_condition']; if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) { $queryGenerator->addConditionGlue($columncondition); } } $queryGenerator->endGroup(); $groupConditionGlue = $groupcolumns['condition']; if (!empty($groupConditionGlue)) { $queryGenerator->addConditionGlue($groupConditionGlue); } } } $query = $queryGenerator->getQuery(); //Remove the ordering for now to improve the speed //$query .= ' ORDER BY createdtime DESC'; $result = $db->query($query); while ($row = $db->fetch_array($result)) { $recordInstance = Vtiger_Record_Model::getInstanceById(current($row)); $moduleName = $recordInstance->getModuleName(); $recordInstance->set('permitted', true); $matchingRecords[$moduleName][current($row)] = $recordInstance; } $viewer->assign('SEARCH_MODULE', $moduleName); } else { $searchKey = $request->get('value'); $searchModule = false; if ($request->get('searchModule')) { $searchModule = $request->get('searchModule'); } $viewer->assign('SEARCH_KEY', $searchKey); $viewer->assign('SEARCH_MODULE', $searchModule); $matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule, $request->get('limit')); } $recordsList = $matchingRecordsList = []; if ($matchingRecords[$moduleName]) { $matchingRecordsList[$moduleName] = $matchingRecords[$moduleName]; } foreach ($matchingRecords as $module => $recordModelsList) { $matchingRecordsList[$module] = $recordModelsList; } if ($request->get('html') == 'true') { $viewer->assign('MODULE', $moduleName); $viewer->assign('MATCHING_RECORDS', $matchingRecordsList); $viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch); echo $viewer->view('UnifiedSearchResults.tpl', '', true); } else { foreach ($matchingRecordsList as $module => $modules) { foreach ($modules as $recordID => $recordModel) { $label = decode_html($recordModel->getName()); $label .= ' (' . Vtiger_Functions::getOwnerRecordLabel($recordModel->get('smownerid')) . ')'; if (!$recordModel->get('permitted')) { $label .= ' <span class="glyphicon glyphicon-warning-sign" aria-hidden="true"></span>'; } $recordsList[] = ['id' => $recordID, 'module' => $module, 'category' => vtranslate($module, $module), 'label' => $label, 'permitted' => $recordModel->get('permitted')]; } } $response = new Vtiger_Response(); $response->setResult($recordsList); $response->emit(); } }
/** * Function to display the Search Results * @param Vtiger_Request $request */ function showSearchResults(Vtiger_Request $request) { $db = PearDatabase::getInstance(); $viewer = $this->getViewer($request); $moduleName = $request->getModule(); $advFilterList = $request->get('advfilterlist'); //used to show the save modify filter option $isAdvanceSearch = false; $matchingRecords = array(); if (is_array($advFilterList) && count($advFilterList) > 0) { $isAdvanceSearch = true; $user = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($moduleName, $user); $queryGenerator->setFields(array('id')); vimport('~~/modules/CustomView/CustomView.php'); $customView = new CustomView($moduleName); $dateSpecificConditions = $customView->getStdFilterConditions(); foreach ($advFilterList as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $queryGenerator->startGroup(''); foreach ($filtercolumns as $index => $filter) { $nameComponents = explode(':', $filter['columnname']); if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') { $name = $queryGenerator->getSQLColumn('id'); } else { $name = $nameComponents[2]; } if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) { $filter['stdfilter'] = $filter['comparator']; $valueComponents = explode(',', $filter['value']); if ($filter['comparator'] == 'custom') { $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); } $dateFilterResolvedList = $customView->resolveDateFilterValue($filter); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false); $queryGenerator->addCondition($name, $value, 'BETWEEN'); } else { $queryGenerator->addCondition($name, $filter['value'], $filter['comparator']); } $columncondition = $filter['column_condition']; if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) { $queryGenerator->addConditionGlue($columncondition); } } $queryGenerator->endGroup(); $groupConditionGlue = $groupcolumns['condition']; if (!empty($groupConditionGlue)) { $queryGenerator->addConditionGlue($groupConditionGlue); } } } $query = $queryGenerator->getQuery(); //Remove the ordering for now to improve the speed //$query .= ' ORDER BY createdtime DESC'; $result = $db->pquery($query, array()); $rows = $db->num_rows($result); for ($i = 0; $i < $rows; ++$i) { $row = $db->query_result_rowdata($result, $i); $recordInstance = Vtiger_Record_Model::getInstanceById($row[0]); $moduleName = $recordInstance->getModuleName(); $matchingRecords[$moduleName][$row[0]] = $recordInstance; } $viewer->assign('SEARCH_MODULE', $moduleName); } else { $searchKey = $request->get('value'); $searchModule = false; if ($request->get('searchModule')) { $searchModule = $request->get('searchModule'); } $viewer->assign('SEARCH_KEY', $searchKey); $viewer->assign('SEARCH_MODULE', $searchModule); $matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule); } $matchingRecordsList = array(); if ($matchingRecords[$moduleName]) { $matchingRecordsList[$moduleName] = $matchingRecords[$moduleName]; } foreach ($matchingRecords as $module => $recordModelsList) { $matchingRecordsList[$module] = $recordModelsList; } $viewer->assign('MODULE', $moduleName); $viewer->assign('MATCHING_RECORDS', $matchingRecordsList); $viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch); echo $viewer->view('UnifiedSearchResults.tpl', '', true); }
public function parseAdvFilterList($advFilterList, $glue = '') { if (!empty($glue)) { $this->addConditionGlue($glue); } $customView = new CustomView($this->module); $dateSpecificConditions = $customView->getStdFilterConditions(); foreach ($advFilterList as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $this->startGroup(''); foreach ($filtercolumns as $index => $filter) { $nameComponents = explode(':', $filter['columnname']); if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') { $name = $this->getSQLColumn('id'); } else { $name = $nameComponents[2]; } if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) { $filter['stdfilter'] = $filter['comparator']; $valueComponents = explode(',', $filter['value']); if ($filter['comparator'] == 'custom') { if ($nameComponents[4] == 'DT') { $startDateTimeComponents = explode(' ', $valueComponents[0]); $endDateTimeComponents = explode(' ', $valueComponents[1]); $filter['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]); } else { $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); } } $dateFilterResolvedList = $customView->resolveDateFilterValue($filter); $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['startdate']); $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false); $this->addCondition($name, $value, 'BETWEEN'); } else { if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'e' || $filter['comparator'] == 'n')) { $filter['stdfilter'] = $filter['comparator']; $dateTimeComponents = explode(' ', $filter['value']); $filter['startdate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]); $dateTimeFilterResolvedList = $customView->resolveDateFilterValue($filter); $value[] = $this->fixDateTimeValue($name, $dateTimeFilterResolvedList['startdate']); $value[] = $this->fixDateTimeValue($name, $dateTimeFilterResolvedList['enddate'], false); if ($filter['comparator'] == 'n') { $this->addCondition($name, $value, 'NOTEQUAL'); } else { $this->addCondition($name, $value, 'BETWEEN'); } } else { if ($nameComponents[4] == 'DT' && $filter['comparator'] == 'a') { $dateTime = explode(' ', $filter['value']); $value[] = $this->fixDateTimeValue($name, $dateTime[0], false); $this->addCondition($name, $value, $filter['comparator']); } else { $this->addCondition($name, $filter['value'], $filter['comparator']); } } } $columncondition = $filter['column_condition']; if (!empty($columncondition)) { $this->addConditionGlue($columncondition); } } $this->endGroup(); $groupConditionGlue = $groupcolumns['condition']; if (!empty($groupConditionGlue)) { $this->addConditionGlue($groupConditionGlue); } } } }