>>>>>>>> $selectedcolumnstring = $_REQUEST["selectedColumnsString"]; //<<<<<<>>>>>>>> //<<<<<<>>>>>>>> $sort_by1 = decode_html(vtlib_purify($_REQUEST["Group1"])); $sort_order1 = vtlib_purify($_REQUEST["Sort1"]); $sort_by2 =decode_html(vtlib_purify($_REQUEST["Group2"])); $sort_order2 = vtlib_purify($_REQUEST["Sort2"]); $sort_by3 = decode_html(vtlib_purify($_REQUEST["Group3"])); $sort_order3 = vtlib_purify($_REQUEST["Sort3"]); //<<<<<<>>>>>> $groupTime1 = vtlib_purify($_REQUEST['groupbytime1']); $groupTime2 = vtlib_purify($_REQUEST['groupbytime2']); $groupTime3 = vtlib_purify($_REQUEST['groupbytime3']); //<<<<<<>>>>>> //<<<<<<>>>>>>>> $selectedcolumns = explode(";",$selectedcolumnstring); if(!in_array($sort_by1,$selectedcolumns)){ $selectedcolumns[] = $sort_by1; } if(!in_array($sort_by2,$selectedcolumns)){ $selectedcolumns[] = $sort_by2; } if(!in_array($sort_by3,$selectedcolumns)){ $selectedcolumns[] = $sort_by3; } //<<<<<<>>>>>>>> $pmodule = vtlib_purify($_REQUEST["primarymodule"]); $smodule = vtlib_purify($_REQUEST["secondarymodule"]); //<<<<<<>>>>>>>> //<<<<<<>>>>>>>> $reportname = vtlib_purify($_REQUEST["reportName"]); $reportdescription = vtlib_purify($_REQUEST["reportDesc"]); $reporttype = vtlib_purify($_REQUEST["reportType"]); $folderid = vtlib_purify($_REQUEST["folder"]); //<<<<<<>>>>>>>> //<<<<<<>>>>>>>> $stdDateFilterField = vtlib_purify($_REQUEST["stdDateFilterField"]); $stdDateFilter = vtlib_purify($_REQUEST["stdDateFilter"]); $startdate = $_REQUEST["startdate"]; $enddate = $_REQUEST["enddate"]; $dbCurrentDateTime = new DateTimeField(date('Y-m-d H:i:s')); if(!empty($startdate)) { $startDateTime = new DateTimeField($startdate.' '. $dbCurrentDateTime->getDisplayTime()); $startdate = $startDateTime->getDBInsertDateValue(); } if(!empty($enddate)) { $endDateTime = new DateTimeField($enddate.' '. $dbCurrentDateTime->getDisplayTime()); $enddate = $endDateTime->getDBInsertDateValue(); } //<<<<<<>>>>>>>> //<<<<<<>>>>>>>> $sharetype = vtlib_purify($_REQUEST["stdtypeFilter"]); $shared_entities = vtlib_purify($_REQUEST["selectedColumnsStr"]); //<<<<<<>>>>>>>> //<<<<<<>>>>>>>>> $allKeys = array_keys($_REQUEST); for ($i=0;$i>>>>>>>> //<<<<<<>>>>>>> $json = new Zend_Json(); $advft_criteria = $_REQUEST['advft_criteria']; $advft_criteria = $json->decode($advft_criteria); $advft_criteria_groups = $_REQUEST['advft_criteria_groups']; $advft_criteria_groups = $json->decode($advft_criteria_groups); //<<<<<<>>>>>>> //<<<<<<>>>>>>> $isReportScheduled = vtlib_purify($_REQUEST['isReportScheduled']); $selectedRecipients = vtlib_purify($_REQUEST['selectedRecipientsString']); $scheduledFormat = vtlib_purify($_REQUEST['scheduledReportFormat']); $scheduledInterval = vtlib_purify($_REQUEST['scheduledIntervalString']); //<<<<<<>>>>>>> if($reportid == "") { $genQueryId = $adb->getUniqueID("vtiger_selectquery"); if($genQueryId != "") { $iquerysql = "insert into vtiger_selectquery (QUERYID,STARTINDEX,NUMOFOBJECTS) values (?,?,?)"; $iquerysqlresult = $adb->pquery($iquerysql, array($genQueryId,0,0)); $log->info("Reports :: Save->Successfully saved vtiger_selectquery"); if($iquerysqlresult!=false) { //<<<>>>>>>> if(!empty($selectedcolumns)) { for($i=0 ;$ipquery($icolumnsql, array($genQueryId,$i,(decode_html($selectedcolumns[$i])))); } } } if($shared_entities != "") { if($sharetype == "Shared") { $selectedcolumn = explode(";",$shared_entities); for($i=0 ;$i< count($selectedcolumn) -1 ;$i++) { $temp = split("::",$selectedcolumn[$i]); $icolumnsql = "insert into vtiger_reportsharing (reportid,shareid,setype) values (?,?,?)"; $icolumnsqlresult = $adb->pquery($icolumnsql, array($genQueryId,$temp[1],$temp[0])); } } } $log->info("Reports :: Save->Successfully saved vtiger_selectcolumn"); //<<<>>>>>>> if($genQueryId != "") { $ireportsql = "insert into vtiger_report (REPORTID,FOLDERID,REPORTNAME,DESCRIPTION,REPORTTYPE,QUERYID,STATE,OWNER,SHARINGTYPE) values (?,?,?,?,?,?,?,?,?)"; $ireportparams = array($genQueryId, $folderid, $reportname, $reportdescription, $reporttype, $genQueryId,'CUSTOM',$current_user->id,$sharetype); $ireportresult = $adb->pquery($ireportsql, $ireportparams); $log->info("Reports :: Save->Successfully saved vtiger_report"); if($ireportresult!=false) { //<<<>>>>>> $ireportmodulesql = "insert into vtiger_reportmodules (REPORTMODULESID,PRIMARYMODULE,SECONDARYMODULES) values (?,?,?)"; $ireportmoduleresult = $adb->pquery($ireportmodulesql, array($genQueryId, $pmodule, $smodule)); $log->info("Reports :: Save->Successfully saved vtiger_reportmodules"); //<<<>>>>>> //<<<>>>>>> if($sort_by1 != "") { $sort_by1sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by1result = $adb->pquery($sort_by1sql, array(1, $genQueryId, $sort_by1, $sort_order1)); if(CustomReportUtils::IsDateField($sort_by1)){ $groupByTime1Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime1Res = $adb->pquery($groupByTime1Sql,array($genQueryId,1,$sort_by1,$groupTime1)); } } if($sort_by2 != "") { $sort_by2sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by2result = $adb->pquery($sort_by2sql, array(2,$genQueryId,$sort_by2,$sort_order2)); if(CustomReportUtils::IsDateField($sort_by2)){ $groupByTime2Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime2Res = $adb->pquery($groupByTime2Sql,array($genQueryId,2,$sort_by2,$groupTime2)); } } if($sort_by3 != "") { $sort_by3sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by3result = $adb->pquery($sort_by3sql, array(3,$genQueryId,$sort_by3,$sort_order3)); if(CustomReportUtils::IsDateField($sort_by3)){ $groupByTime3Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime3Res = $adb->pquery($groupByTime3Sql,array($genQueryId,3,$sort_by3,$groupTime3)); } } $log->info("Reports :: Save->Successfully saved vtiger_reportsortcol"); //<<<>>>>>> //<<<>>>>>> $ireportmodulesql = "insert into vtiger_reportdatefilter (DATEFILTERID,DATECOLUMNNAME,DATEFILTER,STARTDATE,ENDDATE) values (?,?,?,?,?)"; $ireportmoduleresult = $adb->pquery($ireportmodulesql, array($genQueryId, $stdDateFilterField, $stdDateFilter, $startdate, $enddate)); $log->info("Reports :: Save->Successfully saved vtiger_reportdatefilter"); //<<<>>>>>> //<<<>>>>>> for ($i=0;$ipquery($ireportsummarysql, array($genQueryId, $i, $columnstototal[$i])); } $log->info("Reports :: Save->Successfully saved vtiger_reportsummary"); //<<<>>>>>> //<<<>>>>>> 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') { if($field->getUIType() == '71') { $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;$xgetDBInsertDateTimeValue(); } 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($genQueryId, $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, $genQueryId, $group_condition_info["groupcondition"], $group_condition_info["conditionexpression"])); } $log->info("Reports :: Save->Successfully saved vtiger_relcriteria"); //<<<>>>>>> //<<<>>>>>> if($isReportScheduled == 'on' || $isReportScheduled == '1'){ $scheduleReportSql = 'INSERT INTO vtiger_scheduled_reports (reportid,recipients,schedule,format,next_trigger_time) VALUES (?,?,?,?,?)'; $adb->pquery($scheduleReportSql, array($genQueryId,$selectedRecipients,$scheduledInterval,$scheduledFormat,date("Y-m-d H:i:s"))); } //<<<>>>>>> }else { $errormessage = "Error Message
  • Error while inserting the record

" ; echo $errormessage; die; } } }else { $errormessage = "Error Message
  • Error while inserting the record

" ; echo $errormessage; die; } echo ''; } }else { if($reportid != "") { if(!empty($selectedcolumns)) { $idelcolumnsql = "delete from vtiger_selectcolumn where queryid=?"; $idelcolumnsqlresult = $adb->pquery($idelcolumnsql, array($reportid)); if($idelcolumnsqlresult != false) { for($i=0 ;$ipquery($icolumnsql, array($reportid,$i,(decode_html($selectedcolumns[$i])))); } } } } $delsharesqlresult = $adb->pquery("DELETE FROM vtiger_reportsharing WHERE reportid=?", array($reportid)); if($delsharesqlresult != false && $sharetype=="Shared" && $shared_entities!='') { $selectedcolumn = explode(";",$shared_entities); for($i=0 ;$i< count($selectedcolumn) -1 ;$i++) { $temp = split("::",$selectedcolumn[$i]); $icolumnsql = "INSERT INTO vtiger_reportsharing (reportid,shareid,setype) VALUES (?,?,?)"; $icolumnsqlresult = $adb->pquery($icolumnsql, array($reportid,$temp[1],$temp[0])); } } //<<<>>>>>> $ireportmodulesql = "UPDATE vtiger_reportmodules SET primarymodule=?,secondarymodules=? WHERE reportmodulesid=?"; $ireportmoduleresult = $adb->pquery($ireportmodulesql, array($pmodule, $smodule,$reportid)); $log->info("Reports :: Save->Successfully saved vtiger_reportmodules"); //<<<>>>>>> $ireportsql = "update vtiger_report set REPORTNAME=?, DESCRIPTION=?, REPORTTYPE=?, SHARINGTYPE=? where REPORTID=?"; $ireportparams = array($reportname, $reportdescription, $reporttype, $sharetype, $reportid); $ireportresult = $adb->pquery($ireportsql, $ireportparams); $log->info("Reports :: Save->Successfully saved vtiger_report"); $idelreportsortcolsql = "delete from vtiger_reportsortcol where reportid=?"; $idelreportsortcolsqlresult = $adb->pquery($idelreportsortcolsql, array($reportid)); $delReportGroupTimeSQL = "DELETE FROM vtiger_reportgroupbycolumn WHERE reportid=?"; $delReportGroupTimeRES = $adb->pquery($delReportGroupTimeSQL,array($reportid)); $log->info("Reports :: Save->Successfully deleted vtiger_reportsortcol"); if($idelreportsortcolsqlresult!=false) { //<<<>>>>>> if($sort_by1 != "") { $sort_by1sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by1result = $adb->pquery($sort_by1sql, array(1, $reportid, $sort_by1, $sort_order1)); if(CustomReportUtils::IsDateField($sort_by1)){ $groupByTime1Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime1Res = $adb->pquery($groupByTime1Sql,array($reportid,1,$sort_by1,$groupTime1)); } } if($sort_by2 != "") { $sort_by2sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by2result = $adb->pquery($sort_by2sql, array(2, $reportid, $sort_by2, $sort_order2)); if(CustomReportUtils::IsDateField($sort_by2)){ $groupByTime2Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime2Res = $adb->pquery($groupByTime2Sql,array($reportid,2,$sort_by2,$groupTime2)); } } if($sort_by3 != "") { $sort_by3sql = "insert into vtiger_reportsortcol (SORTCOLID,REPORTID,COLUMNNAME,SORTORDER) values (?,?,?,?)"; $sort_by3result = $adb->pquery($sort_by3sql, array(3, $reportid, $sort_by3, $sort_order3)); if(CustomReportUtils::IsDateField($sort_by3)){ $groupByTime3Sql = "INSERT INTO vtiger_reportgroupbycolumn(REPORTID,SORTID,SORTCOLNAME,DATEGROUPBYCRITERIA) values(?,?,?,?)"; $groupByTime3Res = $adb->pquery($groupByTime3Sql,array($reportid,3,$sort_by3,$groupTime3)); } } $log->info("Reports :: Save->Successfully saved vtiger_reportsortcol"); //<<<>>>>>> $idelreportdatefiltersql = "delete from vtiger_reportdatefilter where datefilterid=?"; $idelreportdatefiltersqlresult = $adb->pquery($idelreportdatefiltersql, array($reportid)); //<<<>>>>>> $ireportmodulesql = "insert into vtiger_reportdatefilter (DATEFILTERID,DATECOLUMNNAME,DATEFILTER,STARTDATE,ENDDATE) values (?,?,?,?,?)"; $ireportmoduleresult = $adb->pquery($ireportmodulesql, array($reportid, $stdDateFilterField, $stdDateFilter, $startdate, $enddate)); $log->info("Reports :: Save->Successfully saved vtiger_reportdatefilter"); //<<<>>>>>> //<<<>>>>>> $idelreportsummarysql = "delete from vtiger_reportsummary where reportsummaryid=?"; $idelreportsummarysqlresult = $adb->pquery($idelreportsummarysql, array($reportid)); for ($i=0;$ipquery($ireportsummarysql, array($reportid, $i, $columnstototal[$i])); } $log->info("Reports :: Save->Successfully saved vtiger_reportsummary"); //<<<>>>>>> //<<<>>>>>> $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)); 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;$xgetDBInsertDateTimeValue(); } 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"])); } $log->info("Reports :: Save->Successfully saved vtiger_relcriteria"); //<<<>>>>>> //<<<>>>>>> if($isReportScheduled == 'off' || $isReportScheduled == '0' || $isReportScheduled == '') { $deleteScheduledReportSql = "DELETE FROM vtiger_scheduled_reports WHERE reportid=?"; $adb->pquery($deleteScheduledReportSql, array($reportid)); } else{ $checkScheduledResult = $adb->pquery('SELECT 1 FROM vtiger_scheduled_reports WHERE reportid=?', array($reportid)); if($adb->num_rows($checkScheduledResult) > 0) { $scheduledReportSql = 'UPDATE vtiger_scheduled_reports SET recipients=?,schedule=?,format=? WHERE reportid=?'; $adb->pquery($scheduledReportSql, array($selectedRecipients,$scheduledInterval,$scheduledFormat,$reportid)); } else { $scheduleReportSql = 'INSERT INTO vtiger_scheduled_reports (reportid,recipients,schedule,format,next_trigger_time) VALUES (?,?,?,?,?)'; $adb->pquery($scheduleReportSql, array($reportid,$selectedRecipients,$scheduledInterval,$scheduledFormat,date("Y-m-d H:i:s"))); } } //<<<>>>>>> }else { $errormessage = "Error Message
  • Error while inserting the record

" ; echo $errormessage; die; } }else { $errormessage = "Error Message
  • Error while inserting the record

" ; echo $errormessage; die; } echo ''; } ?>