/** to get the Advanced filter for the given customview Id * @param $cvid :: Type Integer * @returns $advfilterlist Array */ function getAdvFilterByCvid($cvid) { global $adb, $log, $default_charset, $current_user, $currentModule, $mod_strings; $advft_criteria = array(); $sql = 'SELECT * FROM vtiger_cvadvfilter_grouping WHERE cvid = ? ORDER BY groupid'; $groupsresult = $adb->pquery($sql, array($cvid)); $i = 1; $j = 0; while ($relcriteriagroup = $adb->fetch_array($groupsresult)) { $groupId = $relcriteriagroup["groupid"]; $groupCondition = $relcriteriagroup["group_condition"]; $ssql = 'select vtiger_cvadvfilter.* from vtiger_customview inner join vtiger_cvadvfilter on vtiger_cvadvfilter.cvid = vtiger_customview.cvid left join vtiger_cvadvfilter_grouping on vtiger_cvadvfilter.cvid = vtiger_cvadvfilter_grouping.cvid and vtiger_cvadvfilter.groupid = vtiger_cvadvfilter_grouping.groupid'; $ssql .= " where vtiger_customview.cvid = ? AND vtiger_cvadvfilter.groupid = ? order by vtiger_cvadvfilter.columnindex"; $result = $adb->pquery($ssql, array($cvid, $groupId)); $noOfColumns = $adb->num_rows($result); if ($noOfColumns <= 0) { continue; } while ($relcriteriarow = $adb->fetch_array($result)) { $columnIndex = $relcriteriarow["columnindex"]; $criteria = array(); $criteria['columnname'] = html_entity_decode($relcriteriarow["columnname"], ENT_QUOTES, $default_charset); $criteria['comparator'] = $relcriteriarow["comparator"]; $advfilterval = html_entity_decode($relcriteriarow["value"], ENT_QUOTES, $default_charset); $col = explode(":", $relcriteriarow["columnname"]); $uitype_value = getUItypeByFieldName($this->customviewmodule, $col[2]); if ($uitype_value == '15' || $uitype_value == '16' || $uitype_value == '33') { if (!isValueInPicklist($advfilterval, $col[2])) { $advfilterval = getTranslationKeyFromTranslatedValue($this->customviewmodule, $advfilterval); } } $temp_val = explode(",", $relcriteriarow["value"]); if ($col[4] == 'D' || $col[4] == 'T' && $col[1] != 'time_start' && $col[1] != 'time_end' || $col[4] == 'DT') { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if ($col[4] == 'D') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayDate(); } elseif ($col[4] == 'DT') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayDateTimeValue(); } else { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayTime(); } } $advfilterval = implode(",", $val); } if (($col[1] == 'smownerid' || $col[1] == 'smcreatorid' || $col[1] == 'modifiedby') && $advfilterval == 'current_user' && $_REQUEST['action'] != 'CustomView' && empty($_REQUEST['record'])) { $advfilterval = trim($current_user->first_name . ' ' . $current_user->last_name); } $criteria['value'] = $advfilterval; $criteria['column_condition'] = $relcriteriarow["column_condition"]; $advft_criteria[$i]['columns'][$j] = $criteria; $advft_criteria[$i]['condition'] = $groupCondition; $j++; } if (!empty($advft_criteria[$i]['columns'][$j - 1]['column_condition'])) { $advft_criteria[$i]['columns'][$j - 1]['column_condition'] = ''; } $i++; } // Clear the condition (and/or) for last group, if any. if (!empty($advft_criteria[$i - 1]['condition'])) { $advft_criteria[$i - 1]['condition'] = ''; } return $advft_criteria; }
/** Function to get the RunTime Advanced filter conditions * @ param $advft_criteria : Type Array * @ param $advft_criteria_groups : Type Array * This function returns $advfiltersql */ function RunTimeAdvFilter($advft_criteria, $advft_criteria_groups) { $adb = PearDatabase::getInstance(); $advfilterlist = array(); if (!empty($advft_criteria)) { foreach ($advft_criteria as $column_index => $column_condition) { if (empty($column_condition)) { continue; } $adv_filter_column = $column_condition["columnname"]; $adv_filter_comparator = $column_condition["comparator"]; $adv_filter_value = $column_condition["value"]; $adv_filter_column_condition = $column_condition["columncondition"]; $adv_filter_groupid = $column_condition["groupid"]; $column_info = explode(":", $adv_filter_column); $moduleFieldLabel = $column_info[2]; $fieldName = $column_info[3]; list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($adb, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency' or $fieldType == 'double') { $flduitype = $fieldInfo['uitype']; if ($flduitype == '72' or $flduitype == 9 or $flduitype == 7) { $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true); } else { $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value); } } $temp_val = explode(",", $adv_filter_value); if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $adv_filter_value != '')) { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if ($column_info[4] == 'D') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDBInsertDateValue(); } elseif ($column_info[4] == 'DT') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDBInsertDateTimeValue(); } else { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDBInsertTimeValue(); } } $adv_filter_value = implode(",", $val); } if ($fieldType == 'picklist' || $fieldType == 'multipicklist') { if (!isValueInPicklist($adv_filter_value, $fieldName)) { $adv_filter_value = getTranslationKeyFromTranslatedValue($module, $adv_filter_value); } } $criteria = array(); $criteria['columnname'] = $adv_filter_column; $criteria['comparator'] = $adv_filter_comparator; $criteria['value'] = $adv_filter_value; $criteria['column_condition'] = $adv_filter_column_condition; $advfilterlist[$adv_filter_groupid]['columns'][] = $criteria; } foreach ($advft_criteria_groups as $group_index => $group_condition_info) { if (empty($group_condition_info)) { continue; } if (empty($advfilterlist[$group_index])) { continue; } $advfilterlist[$group_index]['condition'] = $group_condition_info["groupcondition"]; $noOfGroupColumns = count($advfilterlist[$group_index]['columns']); if (!empty($advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'])) { $advfilterlist[$group_index]['columns'][$noOfGroupColumns - 1]['column_condition'] = ''; } } $noOfGroups = count($advfilterlist); if (!empty($advfilterlist[$noOfGroups]['condition'])) { $advfilterlist[$noOfGroups]['condition'] = ''; } $advfiltersql = $this->generateAdvFilterSql($advfilterlist); } return $advfiltersql; }
public function addUserSearchConditions($input) { global $log, $default_charset; if ($input['searchtype'] == 'advance') { $json = new Zend_Json(); $advft_criteria = empty($input['advft_criteria']) ? $_REQUEST['advft_criteria'] : $input['advft_criteria']; if (!empty($advft_criteria)) { $advft_criteria = $json->decode($advft_criteria); } $advft_criteria_groups = empty($input['advft_criteria_groups']) ? $_REQUEST['advft_criteria_groups'] : $input['advft_criteria_groups']; if (!empty($advft_criteria_groups)) { $advft_criteria_groups = $json->decode($advft_criteria_groups); } if (empty($advft_criteria) || count($advft_criteria) <= 0) { return; } $advfilterlist = getAdvancedSearchCriteriaList($advft_criteria, $advft_criteria_groups, $this->getModule()); if (empty($advfilterlist) || count($advfilterlist) <= 0) { return; } if ($this->conditionInstanceCount > 0) { $this->startGroup(self::$AND); } else { $this->startGroup(''); } foreach ($advfilterlist as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $this->startGroup(''); foreach ($filtercolumns as $index => $filter) { $name = explode(':', $filter['columnname']); if (empty($name[2]) && $name[1] == 'crmid' && $name[0] == 'vtiger_crmentity') { $name = $this->getSQLColumn('id'); } else { $name = $name[2]; } $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); } } } $this->endGroup(); } elseif ($input['type'] == 'dbrd') { if ($this->conditionInstanceCount > 0) { $this->startGroup(self::$AND); } else { $this->startGroup(''); } $allConditionsList = $this->getDashBoardConditionList(); $conditionList = $allConditionsList['conditions']; $relatedConditionList = $allConditionsList['relatedConditions']; $noOfConditions = count($conditionList); $noOfRelatedConditions = count($relatedConditionList); foreach ($conditionList as $index => $conditionInfo) { $this->addCondition($conditionInfo['fieldname'], $conditionInfo['value'], $conditionInfo['operator']); if ($index < $noOfConditions - 1 || $noOfRelatedConditions > 0) { $this->addConditionGlue(self::$AND); } } foreach ($relatedConditionList as $index => $conditionInfo) { $this->addRelatedModuleCondition($conditionInfo['relatedModule'], $conditionInfo['conditionModule'], $conditionInfo['finalValue'], $conditionInfo['SQLOperator']); if ($index < $noOfRelatedConditions - 1) { $this->addConditionGlue(self::$AND); } } $this->endGroup(); } else { if (isset($input['search_field']) && $input['search_field'] != "") { $fieldName = vtlib_purify($input['search_field']); } else { return; } if ($this->conditionInstanceCount > 0) { $this->startGroup(self::$AND); } else { $this->startGroup(''); } $moduleFields = $this->getModuleFields(); $field = $moduleFields[$fieldName]; $type = $field->getFieldDataType(); if (isset($input['search_text']) && $input['search_text'] != "") { // search other characters like "|, ?, ?" by jagi $value = $input['search_text']; $stringConvert = function_exists(iconv) ? @iconv("UTF-8", $default_charset, $value) : $value; if (!$this->isStringType($type)) { $value = trim($stringConvert); } if ($type == 'picklist') { global $currentModule; if (!isValueInPicklist($value, $field->getFieldName())) { $value = getTranslationKeyFromTranslatedValue($currentModule, $value); } } if ($type == 'currency') { // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion if ($field->getUIType() == '72') { $value = CurrencyField::convertToDBFormat($value, null, true); } else { $currencyField = new CurrencyField($value); if ($this->getModule() == 'Potentials' && $fieldName == 'amount') { $currencyField->setNumberofDecimals(2); } $value = $currencyField->getDBInsertedValue(); } } } if (!empty($input['operator'])) { $operator = $input['operator']; } elseif (trim(strtolower($value)) == 'null') { $operator = 'e'; } else { if (!$this->isNumericType($type) && !$this->isDateType($type)) { $operator = 'c'; } else { $operator = 'h'; } } $this->addCondition($fieldName, $value, $operator); $this->endGroup(); } }