/** Function to get the advanced filter columns for the reportid
* This function accepts the $reportid
* This function returns $columnslist Array: $columnname => $tablename:$columnname:$fieldlabel:$fieldname:$typeofdata=>$tablename.$columnname filtercriteria,
* $tablename1:$columnname1:$fieldlabel1:$fieldname1:$typeofdata1=>$tablename1.$columnname1 filtercriteria,
* |
* $tablenamen:$columnnamen:$fieldlabeln:$fieldnamen:$typeofdatan=>$tablenamen.$columnnamen filtercriteria
*/
function getAdvFilterList($reportid)
{
global $adb, $log;
$advft_criteria = array();
$sql = 'SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid';
$groupsresult = $adb->pquery($sql, array($reportid));
$i = 1;
$j = 0;
while ($relcriteriagroup = $adb->fetch_array($groupsresult)) {
$groupId = $relcriteriagroup["groupid"];
$groupCondition = $relcriteriagroup["group_condition"];
$ssql = 'select vtiger_relcriteria.* from vtiger_report
inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid
left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid
and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid';
$ssql .= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ? order by vtiger_relcriteria.columnindex";
$result = $adb->pquery($ssql, array($reportid, $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"]);
$criteria['comparator'] = $relcriteriarow["comparator"];
$advfilterval = $relcriteriarow["value"];
$col = explode(":", $relcriteriarow["columnname"]);
list($module, $void) = explode('_', $col[2], 2);
$uitype_value = getUItypeByFieldName($module, $col[3]);
if ($uitype_value == '15' || $uitype_value == '16' || $uitype_value == '33') {
if (!isValueInPicklist($advfilterval, $col[3])) {
$advfilterval = getTranslationKeyFromTranslatedValue($module, $advfilterval);
}
}
$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;
}
/** 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;
}
