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