Mailshot a Epicor BAQ Report to Dynamic Email Address

adminEpicor Developement

I recently had a requirement to mailshot a price list out to customers.

In order to do this I developed a BAQ report for the actual price list with CustID as a parameter.

I then created a standard data directive on the UD12 table with a custom code widget that executed the code below.

I then created a mailing list in Excel that included the CustID and the email address:

Image

I then uploaded the spreadsheet via DMT and hey presto two emails appeared in my inbox.

In case your wondering, the solution uses the AutoPrint functionality.


/*
Author: Andrew Clements - Coba Consulting Limited
Version 1.0
Date: Initial Release 24/04/2020
Description: Autoprints report based on CustID in Key1 and email address in Character01

*/
var reportParamTS = new Ice.Tablesets.BAQReportTableset();
var reportParamTable = reportParamTS.BAQReportParam;
var reportParamRow = new Ice.Tablesets.BAQReportParamRow();
reportParamTable.Add(reportParamRow);

int rowIndex = 0;

Ice.Lib.Trace.AutoPrintTrace rootAutoPrintTracer = new Ice.Lib.Trace.AutoPrintTrace();
rootAutoPrintTracer.AddRootTraceMessage("Processing AutoPrint action for Report 'BAQ_RPT_96', Style 'Standard - SSRS'");
rootAutoPrintTracer.AddToTraceHandler();

rootAutoPrintTracer.AddSettingTraceMessage("PrintSchedule", "Immediate");
rootAutoPrintTracer.AddSettingTraceMessage("PrintAction", "EmailFax");

Ice.Lib.Trace.AutoPrintTrace rowSpecificAutoPrintTracer = null;
foreach (var primaryTableRow in this.ttUD12.Where(r => (!r.Unchanged() || (r.Unchanged() && !ttUD12.Any(r1 => r1.SysRowID == r.SysRowID && r1.RowMod != r.RowMod)))))
{
rowSpecificAutoPrintTracer  = new Ice.Lib.Trace.AutoPrintTrace(rootAutoPrintTracer);
rowSpecificAutoPrintTracer.AddRootTraceMessage(null, "ProcessRow");
rowSpecificAutoPrintTracer.AddRootToParent();

rowSpecificAutoPrintTracer.AddRowTraceMessage(rowIndex, primaryTableRow.SysRowID);

var prompts = new Dictionary<string, object="">();
var filters = new Dictionary<string, ienumerable<object="">&gt;();
var email = primaryTableRow.Character01;</string,></string,>

prompts.Add("CustID", primaryTableRow.Key1);
reportParamRow.Summary = Convert.ToBoolean("False");
reportParamRow.Check01 = Convert.ToBoolean("False");
reportParamRow.Check02 = Convert.ToBoolean("False");
reportParamRow.Check03 = Convert.ToBoolean("False");
reportParamRow.Check04 = Convert.ToBoolean("False");
reportParamRow.Check05 = Convert.ToBoolean("False");
reportParamRow.Number01 = Convert.ToDecimal("0");
reportParamRow.Number02 = Convert.ToDecimal("0");
reportParamRow.Number03 = Convert.ToDecimal("0");
reportParamRow.Number04 = Convert.ToDecimal("0");
reportParamRow.Number05 = Convert.ToDecimal("0");
reportParamRow.ReportID = Convert.ToString("BAQ_RPT_96");
reportParamRow.Check06 = Convert.ToBoolean("False");
reportParamRow.Check07 = Convert.ToBoolean("False");
reportParamRow.Check08 = Convert.ToBoolean("False");
reportParamRow.Check09 = Convert.ToBoolean("False");
reportParamRow.Check10 = Convert.ToBoolean("False");
reportParamRow.ArchiveCode = Convert.ToInt32("0");
reportParamRow.DateFormat = Convert.ToString("dd/mm/yyyy");
reportParamRow.NumericFormat = Convert.ToString(",.");
reportParamRow.PrintReportParameters = Convert.ToBoolean("False");
reportParamRow.DesignMode = Convert.ToBoolean("False");
var autoPrintHandler = new Ice.Lib.AutoPrintHandler(Db);
reportParamRow.Filter1 = autoPrintHandler.BuildBAQReportCriteriaDocumentAndUpdateReportParameters("BAQ_RPT_96", reportParamRow, prompts, filters);
reportParamRow.AutoAction =  "SSRSPrint" ;
reportParamRow.ReportStyleNum = 1 ;
reportParamRow.WorkstationID = Session.TaskClientID?? string.Empty;
if (reportParamTable.Columns.Contains("ReportID"))
{
reportParamRow["ReportID"] = "BAQ_RPT_96" ;
}

reportParamRow.TaskNote = "";
reportParamRow.SSRSRenderFormat = "PDF";

if (reportParamTS.BAQReportParam.Columns.Contains("EmailTo"))
{
reportParamRow.FaxTo = "";
reportParamRow.FaxNumber = "";
reportParamRow.FaxSubject = "Price List";
reportParamRow.EMailTo = email;
reportParamRow.EMailCC = "";
reportParamRow.EMailBCC = "";
reportParamRow.EMailBody = "Dear Sir/ Madam, Please find attached the updated  price list.
Yours faithfully .......";
reportParamRow.AttachmentType = "PDF";
}

rowSpecificAutoPrintTracer.AddLinqRowTraceMessage(reportParamRow, "ReportParameter");

if (String.IsNullOrEmpty ("Ice.Contracts.BAQReportSvcContract"))
{
Epicor.Hosting.Trace.ServerLog.WriteTraceMessage(Epicor.Hosting.Trace.WellKnownTraceFlags.BpmTrace, "BPM.AutoPrint", () =&gt;"Report class name is empty" );
throw new Ice.Common.BusinessObjectException("Report class name is empty");
}

var reportService = Ice.Assemblies.ServiceRenderer.GetService<ice.contracts.baqreportsvccontract>(Db, true);
if (reportService == null)
{
throw new Ice.Common.BusinessObjectException("Report service 'Ice.Contracts.BAQReportSvcContract' not available");
}</ice.contracts.baqreportsvccontract>

// Run immediately
try
{
reportParamRow.AgentID = ""; /* Clear the Agent fields when running direct */
reportParamRow.AgentSchedNum = 0;
reportParamRow.AgentTaskNum = 0;

var autoPrintSubmit = new Ice.Lib.AutoPrint.SubmitImmediateAutoPrintReport(Db);
autoPrintSubmit.SubmitImmediateReport(reportParamRow, reportService);

rowSpecificAutoPrintTracer.AddTraceMessage("Report submitted", "ImmediateMode");
}
catch (Exception ex)
{
Epicor.Hosting.Trace.ServerLog.WriteTraceMessage(
Epicor.Hosting.Trace.WellKnownTraceFlags.BpmTrace,
"BPM.AutoPrint",
() =&gt; "SubmitImmediateReport failed. Ex: {0}" + ex.ToString());

throw new Ice.Common.BusinessObjectException("Failed to run autoprint report. Error: " + ex.ToString());
}

rowIndex++;
}

You need the following references

and the following 'Usings'