function updateAdvancedCriteria($reportid, $advft_criteria, $advft_criteria_groups)
{
global $adb, $log;
$idelrelcriteriasql = "delete from vtiger_relcriteria where queryid=?";
$idelrelcriteriasqlresult = $adb->pquery($idelrelcriteriasql, array($reportid));
$idelrelcriteriagroupsql = "delete from vtiger_relcriteria_grouping where queryid=?";
$idelrelcriteriagroupsqlresult = $adb->pquery($idelrelcriteriagroupsql, array($reportid));
if (empty($advft_criteria)) {
return;
}
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') {
// Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
if ($field->getUIType() == '72') {
$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 (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToUserFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
$irelcriteriasql = "insert into vtiger_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) values (?,?,?,?,?,?,?)";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($reportid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advft_criteria_groups[$adv_filter_groupid]["conditionexpression"])) {
$groupConditionExpression = $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $column_index . ' ' . $adv_filter_column_condition;
$advft_criteria_groups[$adv_filter_groupid]["conditionexpression"] = $groupConditionExpression;
}
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (empty($group_condition_info)) {
continue;
}
if (empty($group_condition_info["conditionexpression"])) {
continue;
}
// Case when the group doesn't have any column criteria
$irelcriteriagroupsql = "insert into vtiger_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) values (?,?,?,?)";
$irelcriteriagroupresult = $adb->pquery($irelcriteriagroupsql, array($group_index, $reportid, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"]));
}
}
/**
* Function to save the custom view record
*/
public function save()
{
$db = PearDatabase::getInstance();
$currentUserModel = Users_Record_Model::getCurrentUserModel();
$cvId = $this->getId();
$moduleModel = $this->getModule();
$moduleName = $moduleModel->get('name');
$viewName = $this->get('viewname');
$setDefault = $this->get('setdefault');
$setMetrics = $this->get('setmetrics');
$status = $this->get('status');
if ($status == self::CV_STATUS_PENDING) {
if ($currentUserModel->isAdminUser()) {
$status = self::CV_STATUS_PUBLIC;
}
}
if (!$cvId) {
$cvId = $db->getUniqueID("vtiger_customview");
$this->set('cvid', $cvId);
$sql = 'INSERT INTO vtiger_customview(cvid, viewname, setdefault, setmetrics, entitytype, status, userid) VALUES (?,?,?,?,?,?,?)';
$params = array($cvId, $viewName, $setDefault, $setMetrics, $moduleName, $status, $currentUserModel->getId());
$db->pquery($sql, $params);
} else {
$sql = 'UPDATE vtiger_customview SET viewname=?, setdefault=?, setmetrics=?, status=? WHERE cvid=?';
$params = array($viewName, $setDefault, $setMetrics, $status, $cvId);
$db->pquery($sql, $params);
$db->pquery('DELETE FROM vtiger_cvcolumnlist WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvstdfilter WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvadvfilter WHERE cvid = ?', array($cvId));
$db->pquery('DELETE FROM vtiger_cvadvfilter_grouping WHERE cvid = ?', array($cvId));
}
if ($setDefault == 1) {
$query = 'SELECT 1 FROM vtiger_user_module_preferences WHERE userid = ? AND tabid = ?';
$queryParams = array($currentUserModel->getId(), $moduleModel->getId());
$queryResult = $db->pquery($query, $queryParams);
if ($db->num_rows($queryResult) > 0) {
$updateSql = 'UPDATE vtiger_user_module_preferences SET default_cvid = ? WHERE userid = ? AND tabid = ?';
$updateParams = array($cvId, $currentUserModel->getId(), $moduleModel->getId());
$db->pquery($updateSql, $updateParams);
} else {
$insertSql = 'INSERT INTO vtiger_user_module_preferences(userid, tabid, default_cvid) VALUES (?,?,?)';
$insertParams = array($currentUserModel->getId(), $moduleModel->getId(), $cvId);
$db->pquery($insertSql, $insertParams);
}
} else {
$deleteSql = 'DELETE FROM vtiger_user_module_preferences WHERE userid = ? AND tabid = ? AND default_cvid = ?';
$deleteParams = array($currentUserModel->getId(), $moduleModel->getId(), $cvId);
$db->pquery($deleteSql, $deleteParams);
}
$selectedColumnsList = $this->get('columnslist');
if (!empty($selectedColumnsList)) {
$noOfColumns = count($selectedColumnsList);
for ($i = 0; $i < $noOfColumns; $i++) {
$columnSql = 'INSERT INTO vtiger_cvcolumnlist (cvid, columnindex, columnname) VALUES (?,?,?)';
$columnParams = array($cvId, $i, $selectedColumnsList[$i]);
$db->pquery($columnSql, $columnParams);
}
} else {
//no fields were sent so add default All filter columns
$defaultModuleFilter = $db->pquery('SELECT cvid FROM vtiger_customview WHERE setdefault = 1 AND entitytype = ?', array($moduleName));
$defaultViewId = $db->query_result($defaultModuleFilter, 0, 'cvid');
//User Specific filterId
if (empty($defaultViewId)) {
$userDefaultModuleFilter = $db->pquery('SELECT default_cvid FROM vtiger_user_module_preferences WHERE
userid = ? AND tabid = ?', array($currentUserModel->id, $moduleModel->getId()));
$defaultViewId = $db->query_result($userDefaultModuleFilter, 0, 'default_cvid');
}
//First filterid of module
if (empty($defaultViewId)) {
$firstDefaultFilter = $db->pquery('SELECT cvid FROM vtiger_customview WHERE entitytype = ?', array($moduleName));
$defaultViewId = $db->query_result($firstDefaultFilter, 0, 'cvid');
}
// Get the defaults filters columnlist
$columnSql = "INSERT INTO vtiger_cvcolumnlist (cvid, columnindex, columnname)\n\t\t\t\t\t\t\tSELECT ?, columnindex, columnname FROM vtiger_cvcolumnlist WHERE cvid = ?";
$db->pquery($columnSql, array($cvId, $defaultViewId));
}
$stdFilterList = $this->get('stdfilterlist');
if (!empty($stdFilterList) && !empty($stdFilterList['columnname'])) {
$stdFilterSql = 'INSERT INTO vtiger_cvstdfilter(cvid,columnname,stdfilter,startdate,enddate) VALUES (?,?,?,?,?)';
$stdFilterParams = array($cvId, $stdFilterList['columnname'], $stdFilterList['stdfilter'], $db->formatDate($stdFilterList['startdate'], true), $db->formatDate($stdFilterList['enddate'], true));
$db->pquery($stdFilterSql, $stdFilterParams);
}
$advFilterList = $this->get('advfilterlist');
if (!empty($advFilterList)) {
foreach ($advFilterList as $groupIndex => $groupInfo) {
if (empty($groupInfo)) {
continue;
}
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
foreach ($groupColumns as $columnIndex => $columnCondition) {
if (empty($columnCondition)) {
continue;
}
$advFilterColumn = $columnCondition['columnname'];
$advFilterComparator = $columnCondition['comparator'];
$advFitlerValue = $columnCondition['value'];
$advFilterColumnCondition = $columnCondition['column_condition'];
$columnInfo = explode(":", $advFilterColumn);
$fieldName = $columnInfo[2];
$fieldModel = $moduleModel->getField($fieldName);
//Required if Events module fields are selected for the condition
if (!$fieldModel) {
$modulename = $moduleModel->get('name');
if ($modulename == 'Calendar') {
$eventModuleModel = Vtiger_Module_model::getInstance('Events');
$fieldModel = $eventModuleModel->getField($fieldName);
}
}
$fieldType = $fieldModel->getFieldDataType();
if ($fieldType == 'currency') {
if ($fieldModel->get('uitype') == '72') {
// Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save
$advFitlerValue = CurrencyField::convertToDBFormat($advFitlerValue, null, true);
} else {
$advFitlerValue = CurrencyField::convertToDBFormat($advFitlerValue);
}
}
$temp_val = explode(",", $advFitlerValue);
if (($fieldType == 'date' || $fieldType == 'time' && $fieldName != 'time_start' && $fieldName != 'time_end' || $fieldType == 'datetime') && ($fieldType != '' && $advFitlerValue != '')) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
//if date and time given then we have to convert the date and
//leave the time as it is, if date only given then temp_time
//value will be empty
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($fieldType == 'date') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($fieldType == 'datetime') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$advFitlerValue = implode(",", $val);
}
$advCriteriaSql = 'INSERT INTO vtiger_cvadvfilter(cvid,columnindex,columnname,comparator,value,groupid,column_condition)
values (?,?,?,?,?,?,?)';
$advCriteriaParams = array($cvId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFitlerValue, $groupIndex, $advFilterColumnCondition);
$db->pquery($advCriteriaSql, $advCriteriaParams);
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advFilterList[$groupIndex]["conditionexpression"])) {
$groupConditionExpression = $advFilterList[$groupIndex]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition;
$advFilterList[$groupIndex]["conditionexpression"] = $groupConditionExpression;
}
$groupConditionExpression = $advFilterList[$groupIndex]["conditionexpression"];
if (empty($groupConditionExpression)) {
continue;
}
// Case when the group doesn't have any column criteria
$advGroupSql = 'INSERT INTO vtiger_cvadvfilter_grouping(groupid,cvid,group_condition,condition_expression) VALUES (?,?,?,?)';
$advGroupParams = array($groupIndex, $cvId, $groupCondition, $groupConditionExpression);
$db->pquery($advGroupSql, $advGroupParams);
}
}
}
/** 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();
$advfiltersql = '';
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') {
// Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
if ($field->getUIType() == '72') {
$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);
}
$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;
}
}
$temp_val = explode(",", $adv_filter_value);
if (($fieldType == 'date' || $fieldType == 'time' && $fieldName != 'time_start' && $fieldName != 'time_end' || $fieldType == 'datetime') && ($fieldType != '' && $adv_filter_value != '')) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
//if date and time given then we have to convert the date and
//leave the time as it is, if date only given then temp_time
//value will be empty
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($fieldType == 'date') {
$val[$x] = DateTimeField::convertToUserFormat(trim($temp_val[$x]));
} elseif ($fieldType == 'datetime') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
$irelcriteriasql = "INSERT INTO vtiger_cvadvfilter(cvid,columnindex,columnname,comparator,value,groupid,column_condition) values (?,?,?,?,?,?,?)";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($genCVid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advft_criteria_groups[$adv_filter_groupid]["conditionexpression"])) {
$groupConditionExpression = $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $column_index . ' ' . $adv_filter_column_condition;
$advft_criteria_groups[$adv_filter_groupid]["conditionexpression"] = $groupConditionExpression;
}
/** 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;
}
/**
* Function saves Reports Filter information
*/
function saveAdvancedFilters()
{
$db = PearDatabase::getInstance();
$reportId = $this->getId();
$advancedFilter = $this->get('advancedFilter');
if (!empty($advancedFilter)) {
$db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId));
$db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId));
foreach ($advancedFilter as $groupIndex => $groupInfo) {
if (empty($groupInfo)) {
continue;
}
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
foreach ($groupColumns as $columnIndex => $columnCondition) {
if (empty($columnCondition)) {
continue;
}
$advFilterColumn = $columnCondition["columnname"];
$advFilterComparator = $columnCondition["comparator"];
$advFilterValue = $columnCondition["value"];
$advFilterColumnCondition = $columnCondition["column_condition"];
$columnInfo = explode(":", $advFilterColumn);
$moduleFieldLabel = $columnInfo[2];
list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($db, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency') {
if ($field->getUIType() == '72') {
// Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true);
} else {
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue);
}
}
$tempVal = explode(",", $advFilterValue);
if (($columnInfo[4] == 'D' || $columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end' || $columnInfo[4] == 'DT') && ($columnInfo[4] != '' && $advFilterValue != '')) {
$val = array();
for ($i = 0; $i < count($tempVal); $i++) {
if (trim($tempVal[$i]) != '') {
$date = new DateTimeField(trim($tempVal[$i]));
if ($columnInfo[4] == 'D') {
$val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i]));
} elseif ($columnInfo[4] == 'DT') {
/**
* While generating query to retrieve report, for date time fields we are only taking
* date field and appending '00:00:00' for correct results depending on time zone.
* If you save the time also here by converting to db format, while showing in edit
* view it was changing the date selected.
*/
$values = explode(' ', $tempVal[$i]);
$date = new DateTimeField($values[0]);
$val[$i] = $date->getDBInsertDateValue();
} else {
$val[$i] = $date->getDBInsertTimeValue();
}
}
}
$advFilterValue = implode(",", $val);
}
$db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value,
groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition));
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advancedFilter[$groupIndex]["conditionexpression"])) {
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition;
$advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression;
}
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
if (empty($groupConditionExpression)) {
continue;
}
// Case when the group doesn't have any column criteria
$db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)", array($groupIndex, $reportId, $groupCondition, $groupConditionExpression));
}
}
}
}
}
$focus->column_fields["subject"] = $_REQUEST["task_subject"];
$focus->column_fields["time_start"] = $_REQUEST["task_time_start"];
if ($_REQUEST['task_assigntype'] == 'U') {
$focus->column_fields['assigned_user_id'] = $_REQUEST['task_assigned_user_id'];
} elseif ($_REQUEST['task_assigntype'] == 'T') {
$focus->column_fields['assigned_user_id'] = $_REQUEST['task_assigned_group_id'];
}
$date = new DateTimeField($_REQUEST['task_date_start'] . ' ' . $_REQUEST['task_time_start']);
$endDate = new DateTimeField($_REQUEST['task_due_date'] . ' ' . $_REQUEST['task_time_start']);
$focus->column_fields["taskstatus"] = $_REQUEST["taskstatus"];
$focus->column_fields["date_start"] = $date->getDBInsertDateValue();
$focus->column_fields["time_start"] = $date->getDBInsertTimeValue();
$focus->column_fields["due_date"] = $endDate->getDBInsertDateValue();
$focus->column_fields["time_end"] = $endDate->getDBInsertTimeValue();
$focus->column_fields["taskpriority"] = $_REQUEST["taskpriority"];
$focus->column_fields["parent_id"] = $_REQUEST["task_parent_id"];
$focus->column_fields["contact_id"] = $_REQUEST["task_contact_id"];
$focus->column_fields["description"] = $_REQUEST["task_description"];
if (isset($_REQUEST['task_sendnotification']) && $_REQUEST['task_sendnotification'] != null) {
$focus->column_fields["sendnotification"] = $_REQUEST["task_sendnotification"];
}
$focus->save($tab_type);
function getRequestedToData()
{
$mail_data = array();
$mail_data['user_id'] = $_REQUEST["task_assigned_user_id"];
$mail_data['subject'] = $_REQUEST['task_subject'];
$mail_data['status'] = $_REQUEST['activity_mode'] == 'Task' ? $_REQUEST['taskstatus'] : $_REQUEST['eventstatus'];
$mail_data['activity_mode'] = $_REQUEST['activity_mode'];
/**
* Function to process the activity list query
* @param string $query - query string
* return array $response - activity lists
*/
function process_list_query1($query)
{
global $log;
$log->debug("Entering process_list_query1(" . $query . ") method ...");
$result =& $this->db->query($query, true, "Error retrieving {$this->object_name} list: ");
$list = array();
$rows_found = $this->db->getRowCount($result);
if ($rows_found != 0) {
$task = array();
for ($index = 0, $row = $this->db->fetchByAssoc($result, $index); $row && $index < $rows_found; $index++, $row = $this->db->fetchByAssoc($result, $index)) {
foreach ($this->range_fields as $columnName) {
if (isset($row[$columnName])) {
if ($columnName == 'time_start') {
$startDate = new DateTimeField($row['date_start'] . ' ' . $row[$columnName]);
$task[$columnName] = $startDate->getDBInsertTimeValue();
} else {
$task[$columnName] = $row[$columnName];
}
} else {
$task[$columnName] = "";
}
}
$task[contact_name] = return_name($row, 'cfn', 'cln');
$list[] = $task;
}
}
$response = array();
$response['list'] = $list;
$response['row_count'] = $rows_found;
$response['next_offset'] = $next_offset;
$response['previous_offset'] = $previous_offset;
$log->debug("Exiting process_list_query1 method ...");
return $response;
}
function getActivityMailInfo($return_id, $status, $activity_type)
{
$mail_data = array();
global $adb;
$qry = "select * from vtiger_activity where activityid=?";
$ary_res = $adb->pquery($qry, array($return_id));
$send_notification = $adb->query_result($ary_res, 0, "sendnotification");
$subject = $adb->query_result($ary_res, 0, "subject");
$priority = $adb->query_result($ary_res, 0, "priority");
$st_date = $adb->query_result($ary_res, 0, "date_start");
$st_time = $adb->query_result($ary_res, 0, "time_start");
$end_date = $adb->query_result($ary_res, 0, "due_date");
$end_time = $adb->query_result($ary_res, 0, "time_end");
$location = $adb->query_result($ary_res, 0, "location");
if (!empty($st_time)) {
$date = new DateTimeField($st_date . ' ' . $st_time);
$st_date = $date->getDisplayDate();
$st_time = $date->getDisplayTime();
}
if (!empty($end_time)) {
$date = new DateTimeField($end_date . ' ' . $end_time);
$end_date = $date->getDisplayDate();
$end_time = $date->getDisplayTime();
}
$owner_qry = "select smownerid from vtiger_crmentity where crmid=?";
$res = $adb->pquery($owner_qry, array($return_id));
$owner_id = $adb->query_result($res, 0, "smownerid");
$usr_res = $adb->pquery("select count(*) as count from vtiger_users where id=?", array($owner_id));
if ($adb->query_result($usr_res, 0, 'count') > 0) {
$assignType = "U";
$usr_id = $owner_id;
} else {
$assignType = "T";
$group_qry = "select groupname from vtiger_groups where groupid=?";
$grp_res = $adb->pquery($group_qry, array($owner_id));
$grp_name = $adb->query_result($grp_res, 0, "groupname");
}
$desc_qry = "select description from vtiger_crmentity where crmid=?";
$des_res = $adb->pquery($desc_qry, array($return_id));
$description = $adb->query_result($des_res, 0, "description");
$rel_qry = "select case vtiger_crmentity.setype when 'Leads' then vtiger_leaddetails.lastname when 'Accounts' then vtiger_account.accountname when 'Potentials' then vtiger_potential.potentialname when 'Quotes' then vtiger_quotes.subject when 'PurchaseOrder' then vtiger_purchaseorder.subject when 'SalesOrder' then vtiger_salesorder.subject when 'Invoice' then vtiger_invoice.subject when 'Campaigns' then vtiger_campaign.campaignname when 'HelpDesk' then vtiger_troubletickets.title end as relname from vtiger_seactivityrel inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_seactivityrel.crmid left join vtiger_leaddetails on vtiger_leaddetails.leadid = vtiger_seactivityrel.crmid left join vtiger_account on vtiger_account.accountid=vtiger_seactivityrel.crmid left join vtiger_potential on vtiger_potential.potentialid=vtiger_seactivityrel.crmid left join vtiger_quotes on vtiger_quotes.quoteid= vtiger_seactivityrel.crmid left join vtiger_purchaseorder on vtiger_purchaseorder.purchaseorderid = vtiger_seactivityrel.crmid left join vtiger_salesorder on vtiger_salesorder.salesorderid = vtiger_seactivityrel.crmid left join vtiger_invoice on vtiger_invoice.invoiceid = vtiger_seactivityrel.crmid left join vtiger_campaign on vtiger_campaign.campaignid = vtiger_seactivityrel.crmid left join vtiger_troubletickets on vtiger_troubletickets.ticketid = vtiger_seactivityrel.crmid where vtiger_seactivityrel.activityid=?";
$rel_res = $adb->pquery($rel_qry, array($return_id));
$rel_name = $adb->query_result($rel_res, 0, "relname");
$relatedContacts = getActivityRelatedContacts($return_id);
$mail_data['mode'] = "edit";
$mail_data['activity_mode'] = $activity_type;
$mail_data['sendnotification'] = $send_notification;
$mail_data['user_id'] = $usr_id;
$mail_data['subject'] = $subject;
$mail_data['status'] = $status;
$mail_data['taskpriority'] = $priority;
$mail_data['relatedto'] = $rel_name;
$mail_data['contact_name'] = implode(',', $relatedContacts);
$mail_data['description'] = $description;
$mail_data['assign_type'] = $assignType;
$mail_data['group_name'] = $grp_name;
$value = getaddEventPopupTime($st_time, $end_time, '24');
$start_hour = $value['starthour'] . ':' . $value['startmin'] . '' . $value['startfmt'];
if ($activity_type != 'Task') {
$end_hour = $value['endhour'] . ':' . $value['endmin'] . '' . $value['endfmt'];
}
$date = new DateTimeField($st_date . " " . $start_hour);
$endDate = new DateTimeField($end_date . " " . $end_hour);
$mail_data['st_date_time'] = $date->getDBInsertDateTimeValue();
$mail_data['end_date_time'] = $endDate->getDBInsertDateValue() . ' ' . $endDate->getDBInsertTimeValue();
$mail_data['location'] = $location;
return $mail_data;
}
function getReqAdvFilterSql($reportid, $join_array)
{
$adb = PEARDatabase::getInstance();
$conditions = array();
$advfiltersql = "";
// ITS4YOU-CR SlOl 28. 3. 2014 10:34:19 to get select options array
// $ITS4YouReports = new ITS4YouReports($record);
$ITS4YouReports = $this->report_obj;
// ADV FILTER START
$ITS4YouReports->getPriModuleColumnsList($ITS4YouReports->primarymodule);
if (!empty($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule])) {
foreach ($ITS4YouReports->related_modules[$ITS4YouReports->primarymodule] as $key => $value) {
$secondarymodules[] = $value["id"];
}
$secondary_modules_str = implode(":", $secondarymodules);
}
$ITS4YouReports->getSecModuleColumnsList($secondary_modules_str);
$sel_fields = $ITS4YouReports->adv_sel_fields;
$conditions_rc = $this->getReqConditions($sel_fields);
// ITS4YOU-END 28. 3. 2014 10:34:37
$last_gid = $l_groupcondition = "";
$std_filter_columns = $this->report_obj->getStdFilterColumns();
foreach ($conditions_rc as $groupid => $conditions) {
$d_i = 1;
$d_num_rows = count($conditions);
foreach ($conditions as $key => $d_conditions) {
$advfiltergroupsql = "";
$groupid = $d_conditions["groupid"];
if ($last_gid == "") {
$last_gid = $groupid;
}
$dkey = $d_conditions["dkey"];
$from_req = $d_conditions["from_req"];
$column_condition = "";
$fieldcolname = $d_conditions["fcol{$dkey}"];
$comparator = $d_conditions["fop{$dkey}"];
$value = $d_conditions["fval{$dkey}"];
// ? fval_
if (isset($d_conditions["fcon{$dkey}"])) {
$column_condition = $d_conditions["fcon{$dkey}"];
}
if (isset($d_conditions["gpcon{$dkey}"])) {
$groupcondition = $d_conditions["gpcon{$dkey}"];
}
if (isset($d_conditions["f_type{$dkey}"])) {
$f_type = $d_conditions["f_type{$dkey}"];
}
if ($fieldcolname != "" && ($comparator == "isn" || $comparator == "isnn")) {
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$fld_cond = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$fld_arr = explode(":", $fieldcolname);
$fld_cond = $fld_arr[0] . "." . $fld_arr[1];
}
if ($comparator == "isn") {
$advfiltergroupsql .= " (" . $fld_cond . " IS NULL OR " . $fld_cond . " = '') ";
} else {
$advfiltergroupsql .= " (" . $fld_cond . " IS NOT NULL AND " . $fld_cond . " != '') ";
}
} elseif ($fieldcolname != "" && $comparator != "" && $f_type != "S" && !in_array($fieldcolname, $std_filter_columns)) {
$selectedfields = explode(":", $fieldcolname);
//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] : "";
// ADV FILTER CONDITIONS START
if (isset($valuearray) && count($valuearray) > 1 && $comparator != 'bw') {
$advcolumnsql = "";
for ($n = 0; $n < count($valuearray); $n++) {
$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 . ") ";
} else {
// ITS4YOU-UP SlOl 9. 1. 2014 13:44:18
$last_key = count($selectedfields) - 1;
$exploded_name = explode("_", $selectedfields[2]);
$field_module_id = getTabid($exploded_name[0]);
if (is_numeric($selectedfields[$last_key])) {
$tablename = $selectedfields[0];
$fieldname = $selectedfields[3];
$field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0);
$selected_field_col_table = trim($selectedfields[0], $selectedfields[$last_key]);
if (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $field_row["fieldid"] . " ", $join_array)) {
$selected_field_col = $selected_field_col_table . "_" . $field_row["fieldid"] . "." . $selectedfields[1];
} elseif (array_key_exists(" {$selected_field_col_table} AS " . $selected_field_col_table . "_" . $selectedfields[$last_key] . " ", $join_array)) {
$selected_field_col = $selected_field_col_table . "_" . $selectedfields[$last_key] . "." . $selectedfields[1];
}
} elseif (isset($this->columns_array[$fieldcolname]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} elseif ($selectedfields[$last_key] == "MIF") {
$alias = "mif_" . $field_module_id;
$selected_field_col = trim($selectedfields[0], "MIF") . $alias . "." . $selectedfields[1];
} else {
$tablename = $selectedfields[0];
$fieldname = $selectedfields[3];
$field_row = $adb->fetchByAssoc($adb->pquery("SELECT * FROM vtiger_field WHERE fieldname=? AND tabid=?", array($fieldname, $field_module_id)), 0);
if ($tablename == "vtiger_inventoryproductrel") {
$field_uitype = "INV";
$params = array("fieldid" => $selectedfields[$last_key]);
$uifactory = new UIFactory($field_row);
$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields);
} elseif (!empty($field_row)) {
$field_uitype = $field_row["uitype"];
//$uifactory = new UIFactory($field_row);
//$selected_field_col = $uifactory->getSelectedFieldCol($field_uitype, $selectedfields);
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$table_alias = $tablename . "_{$field_uitype}";
$column_alias = $fieldname;
$selected_field_col = $table_alias . "." . $column_alias;
}
} elseif (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$selected_field_col = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$selected_field_col = $selectedfields[0] . "." . $selectedfields[1];
}
}
// ITS4YOU-END 9. 1. 2014 13:44:22
$fieldvalue = $selected_field_col . $this->getAdvComparator($comparator, trim($value), $datatype);
}
// ADV FILTER CONDITIONS END
$advfiltergroupsql .= $fieldvalue;
} elseif ($f_type == "S") {
$fld_arr = explode(":", $fieldcolname);
if ($fld_arr[3] == "assigned_user_id" && strpos($value, "Current User") !== false) {
$value = trim($value, "('");
$value = trim($value, "')");
$valuearray = explode("', '", $value);
$valuearray = $this->getConditionCurrentUserName($valuearray);
$value = "('" . implode("', '", $valuearray) . "')";
}
$advfiltergroupsql .= $this->getSelFieldsWhereSQL($fieldcolname, $comparator, $value, $fld_arr);
/*if ($comparator == "n") {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " NOT IN " . $value;
} else {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " IN " . $value;
}*/
} elseif (in_array($fieldcolname, $std_filter_columns)) {
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
$fld_cond = $this->columns_array[$fieldcolname]["fld_cond"];
} else {
$fld_arr = explode(":", $fieldcolname);
$fld_cond = $fld_arr[0] . "." . $fld_arr[1];
}
$advfiltergroupsql .= $fld_cond . " " . $this->getStdComparator($comparator, trim($value));
} else {
$column_info = explode(":", $fieldcolname);
$temp_val = explode(", ", $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] != '' && $value != '') && !in_array($fieldcolname, $std_filter_columns)) {
$val = array();
for ($x = 0; $x < count($temp_val); $x++) {
if (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$value = implode(", ", $val);
}
if (isset($this->columns_array[$fieldcolname]["fld_cond"]) && $this->columns_array[$fieldcolname]["fld_cond"] != "") {
if (in_array($fieldcolname, $std_filter_columns)) {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getStdComparator($comparator, trim($value));
} else {
$advfiltergroupsql .= $this->columns_array[$fieldcolname]["fld_cond"] . " " . $this->getAdvComparator($comparator, trim($value));
}
} else {
$this->adv_fcol[] = $fieldcolname;
// $selectedfields = explode(":", $fieldcolname);
if (in_array($fieldcolname, $std_filter_columns)) {
$advfiltergroupsql .= $fieldcolname . " " . $this->getStdComparator($comparator, trim($value));
} else {
$advfiltergroupsql .= $fieldcolname . " " . $this->getAdvComparator($comparator, trim($value));
}
}
}
if (trim($advfiltergroupsql) != "") {
$advfiltergroupsql = "( {$advfiltergroupsql} ) ";
if ($column_condition != NULL && $column_condition != '' && $d_i < $d_num_rows) {
$advfiltergroupsql .= ' ' . $column_condition . ' ';
}
if ($l_groupcondition != NULL && $l_groupcondition != '' && $groupid != $last_gid) {
$advfiltergroupsql = ' ' . $l_groupcondition . ' ' . $advfiltergroupsql;
$l_groupcondition = $groupcondition;
$last_gid = $groupid;
} else {
$l_groupcondition = $groupcondition;
$last_gid = $groupid;
}
$advfiltersql .= $advfiltergroupsql;
}
$d_i++;
}
if (trim($advfiltersql) != "") {
$advfiltersql = '(' . $advfiltersql . ')';
}
}
// Save the information
return $advfiltersql;
}
/**
* Function saves Reports Filter information
*/
function saveAdvancedFilters($advft_criteria = array(), $advft_criteria_groups = array(), $std_filter_columns = array(), $export_sql = false)
{
$adb = PearDatabase::getInstance();
$reportid = $this->getId();
$d_adv_criteria = "DELETE FROM its4you_reports4you_relcriteria WHERE queryid = ?";
$d_adv_criteria_result = $adb->pquery($d_adv_criteria, array($reportid));
if (!empty($std_filter_columns)) {
global $default_charset;
foreach ($std_filter_columns as $std_key => $std_value) {
$std_filter_columns[$std_key] = html_entity_decode($std_value, ENT_QUOTES, $default_charset);
}
}
if (!empty($advft_criteria) && !empty($advft_criteria_groups)) {
$default_charset = vglobal("default_charset");
foreach ($advft_criteria as $column_index => $column_condition) {
if (empty($column_condition)) {
continue;
}
$adv_filter_comparator = $column_condition["comparator"];
if (in_array($column_condition["columnname"], $std_filter_columns)) {
$adv_filter_column = $column_condition["columnname"];
} else {
$adv_filter_column = $column_condition["columnname"];
}
$adv_filter_value = $column_condition["value"];
$adv_filter_column_condition = $column_condition["column_condition"];
$adv_filter_groupid = $column_condition["groupid"];
if (in_array($adv_filter_column, $std_filter_columns)) {
$temp_val = explode("<;@STDV@;>", html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset));
$val[0] = DateTimeField::convertToDBFormat(trim($temp_val[0]));
$val[1] = DateTimeField::convertToDBFormat(trim($temp_val[1]));
$adv_filter_value = implode("<;@STDV@;>", $val);
// $adv_filter_value = html_entity_decode($adv_filter_value, ENT_QUOTES, $default_charset);
} else {
$column_info = explode(":", $adv_filter_column);
/// $temp_val = explode(",",$adv_filter_value);
$temp_val = $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 (trim($temp_val[$x]) != '') {
$date = new DateTimeField(trim($temp_val[$x]));
if ($column_info[4] == 'D') {
$val[$x] = DateTimeField::convertToDBFormat(trim($temp_val[$x]));
} elseif ($column_info[4] == 'DT') {
$val[$x] = $date->getDBInsertDateTimeValue();
} else {
$val[$x] = $date->getDBInsertTimeValue();
}
}
}
$adv_filter_value = implode(",", $val);
}
}
if (is_array($adv_filter_value)) {
$adv_filter_value = implode(",", $adv_filter_value);
}
$irelcriteriasql = "INSERT INTO its4you_reports4you_relcriteria(QUERYID,COLUMNINDEX,COLUMNNAME,COMPARATOR,VALUE,GROUPID,COLUMN_CONDITION) VALUES (?,?,?,?,?,?,?)";
$export_sql === true ? $adb->setDebug(true) : "";
$irelcriteriaresult = $adb->pquery($irelcriteriasql, array($reportid, $column_index, $adv_filter_column, $adv_filter_comparator, $adv_filter_value, $adv_filter_groupid, $adv_filter_column_condition));
$export_sql === true ? $adb->setDebug(false) : "";
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advft_criteria_groups[$adv_filter_groupid]["conditionexpression"])) {
$groupConditionExpression = $advft_criteria_groups[$adv_filter_groupid]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $column_index . ' ' . $adv_filter_column_condition;
$advft_criteria_groups[$adv_filter_groupid]["conditionexpression"] = $groupConditionExpression;
}
$d_adv_criteria_grouping = "DELETE FROM its4you_reports4you_relcriteria_grouping WHERE queryid = ?";
$export_sql === true ? $adb->setDebug(true) : "";
$d_adv_criteria_grouping_result = $adb->pquery($d_adv_criteria_grouping, array($reportid));
$export_sql === true ? $adb->setDebug(false) : "";
foreach ($advft_criteria_groups as $group_index => $group_condition_info) {
if (!isset($group_condition_info) || empty($group_condition_info)) {
continue;
}
$irelcriteriagroupsql = "INSERT INTO its4you_reports4you_relcriteria_grouping(GROUPID,QUERYID,GROUP_CONDITION,CONDITION_EXPRESSION) VALUES (?,?,?,?)";
$export_sql === true ? $adb->setDebug(true) : "";
$irelcriteriagroupresult = $adb->pquery($irelcriteriagroupsql, array($group_index, $reportid, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"]));
$export_sql === true ? $adb->setDebug(false) : "";
}
}
}
/**
* Function saves Reports Filter information
*/
function saveAdvancedFilters()
{
$db = PearDatabase::getInstance();
$reportId = $this->getId();
$advancedFilter = $this->get('advancedFilter');
if (!empty($advancedFilter)) {
$db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId));
$db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId));
foreach ($advancedFilter as $groupIndex => $groupInfo) {
if (empty($groupInfo)) {
continue;
}
$groupColumns = $groupInfo['columns'];
$groupCondition = $groupInfo['condition'];
foreach ($groupColumns as $columnIndex => $columnCondition) {
if (empty($columnCondition)) {
continue;
}
$advFilterColumn = $columnCondition["columnname"];
$advFilterComparator = $columnCondition["comparator"];
$advFilterValue = $columnCondition["value"];
$advFilterColumnCondition = $columnCondition["column_condition"];
$columnInfo = explode(":", $advFilterColumn);
$moduleFieldLabel = $columnInfo[2];
list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($db, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if ($fieldType == 'currency') {
if ($field->getUIType() == '71') {
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true);
} else {
$advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue);
}
}
$tempVal = explode(",", $advFilterValue);
if (($columnInfo[4] == 'D' || $columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end' || $columnInfo[4] == 'DT') && ($columnInfo[4] != '' && $advFilterValue != '')) {
$val = array();
for ($i = 0; $i < count($tempVal); $i++) {
if (trim($tempVal[$i]) != '') {
$date = new DateTimeField(trim($tempVal[$i]));
if ($columnInfo[4] == 'D') {
$val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i]));
} elseif ($columnInfo[4] == 'DT') {
$val[$i] = $date->getDBInsertDateTimeValue();
} else {
$val[$i] = $date->getDBInsertTimeValue();
}
}
}
$advFilterValue = implode(",", $val);
}
$db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value,
groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition));
// Update the condition expression for the group to which the condition column belongs
$groupConditionExpression = '';
if (!empty($advancedFilter[$groupIndex]["conditionexpression"])) {
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
}
$groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition;
$advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression;
}
$groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"];
if (empty($groupConditionExpression)) {
continue;
}
// Case when the group doesn't have any column criteria
$db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)", array($groupIndex, $reportId, $groupCondition, $groupConditionExpression));
}
}
}
