How about now (Refresh) it doesn’t seem to mangle the real code too badly
// Fetches the first updated or newly added record from the 'ttRcvHead' collection
var recHead = ttRcvHead.FirstOrDefault(r => r.Added() || r.Updated());
// Extract relevant fields from the fetched receipt header
var company = recHead.Company;
var vendorNum = recHead.VendorNum;
var packSlip = recHead.PackSlip;
var purPoint = recHead.PurPoint;
// Query to fetch detailed receipt information by joining multiple related tables
var receiptInfo = (from rd in Db.RcvDtl
// Join the RcvHead, Vendor, POHeader, PODetail, etc. based on matching fields
join rh in Db.RcvHead on new { rd.Company, rd.VendorNum, rd.PackSlip, rd.PurPoint } equals new { rh.Company, rh.VendorNum, rh.PackSlip, rh.PurPoint }
join vendor in Db.Vendor on new { rd.Company, rd.VendorNum } equals new { vendor.Company, vendor.VendorNum }
join poDtl in Db.PODetail on new { rd.Company, rd.PONum, rd.POLine } equals new { poDtl.Company, PONum = poDtl.PONUM, poDtl.POLine }
join ph in Db.POHeader on new { rd.Company, rd.PONum } equals new { ph.Company, ph.PONum }
join buyer in Db.PurAgent on new { ph.Company, ph.BuyerID } equals new { buyer.Company, buyer.BuyerID }
join whses in Db.Warehse on new { rd.Company, WareHouseCode = rd.WareHouseCode } equals new { whses.Company, WareHouseCode = whses.WarehouseCode }
join whseBin in Db.WhseBin on new { rd.Company, WarehouseCode = rd.WareHouseCode, rd.BinNum } equals new { whseBin.Company, whseBin.WarehouseCode, whseBin.BinNum }
join partplant in Db.PartPlant on new { rd.Company, rd.PartNum, rh.Plant } equals new { partplant.Company, partplant.PartNum, partplant.Plant }
join plant in Db.Plant on new { partplant.Company, partplant.Plant } equals new { plant.Company, Plant = plant.Plant1 }
join userFile in Db.UserFile on partplant.PartManager_c equals userFile.DcdUserID into partManagerJoin
join poRelRel in Db.PORel on new { rd.Company, rd.PONum, rd.POLine, rd.PORelNum } equals new { poRelRel.Company, PONum = poRelRel.PONum, poRelRel.POLine, poRelRel.PORelNum }
from partManager in partManagerJoin.DefaultIfEmpty()
// Filter to include only relevant records for the given company, vendor number, pack slip, and purchase point
where rd.Company == company && rd.VendorNum == vendorNum && rd.PackSlip == packSlip && rd.PurPoint == purPoint && buyer.EMailAddress != ""
group new
{
Buyer = new { buyer.Name, buyer.EMailAddress },
PartManagerEmail = partManager != null ? partManager.EMailAddress : "",
Plant = plant.Plant1,
PlantName = plant.Name,
Vendor = vendor,
ReceiptDetails = new { ReceiptLines = rd, POLine = poDtl, POHeader = ph, Warehouse = whses, WhseBin = whseBin, PORel = poRelRel, TotReceived = poRelRel.ReceivedQty }
}
by new { Buyer = new { buyer.Name, buyer.EMailAddress }, PartManagerEmail = partManager != null ? partManager.EMailAddress : "", Plant = plant.Plant1, PlantName = plant.Name, Vendor = vendor }
into g
// Sort the result by PO number, PO line, and PO release number
orderby g.FirstOrDefault().ReceiptDetails.POLine.PONUM, g.FirstOrDefault().ReceiptDetails.POLine.POLine, g.FirstOrDefault().ReceiptDetails.PORel.PORelNum
select new
{
Buyer = g.Key.Buyer,
PartManagerEmail = g.Key.PartManagerEmail,
Plant = g.Key.Plant,
PlantName = g.Key.PlantName,
Vendor = g.Key.Vendor,
ReceiptDetails = g.Select(x => x.ReceiptDetails)
}).ToList();
// Get the email address for sending the email (default to a "NoReply" if no match is found)
string emailFrom = (from sysCo in Db.MailSetting where sysCo.Company == this.Session.CompanyID select sysCo.EmailFromAddr).DefaultIfEmpty("NoReply.EpicorLive@stephengould.com").FirstOrDefault();
// Get the company details based on the receipt header's company field
var co = (from c in Db.Company where c.Company1 == recHead.Company select c).FirstOrDefault();
// Loop through each group in the receipt information (organized by buyer and vendor)
foreach (var group in receiptInfo)
{
string emailTo = "";
string emailCc = "";
var buyer = group.Buyer;
var partManagerEmail = group.PartManagerEmail;
var poNumbers = group.ReceiptDetails.Select(x => x.POHeader.PONum).Distinct().ToList();
// Create email subject with all relevant PO numbers and plant name
string subject = $"PO Receipt Pack Slip:{recHead.PackSlip} PO(s): {String.Join(", ", poNumbers)} Plant: {group.PlantName} - Receipt Complete";
// Build the email body
StringBuilder body = new StringBuilder();
body.Append("<html><head><style>body{font-family:Verdana;font-size:12px;}table{max-width:100%;background-color:#ffffff;border-collapse:collapse;border-width:1px;border-color:#000000;border-style:solid;color:#000000;font-size:11px;}thead{background-color:#0e85b2;border-width:1px;border-color:#000000;border-style:solid;color:#fff;}td{border-width:1px;border-color:#000000;border-style:solid;padding:3px;}th{border-color: black;border-style: solid;border-width: 1px;padding-left: 10px;padding-right: 10px;}{</style></head><body>");
// Check if the company is live; if not, append test information
if (co.IsLive_c)
{
emailTo = buyer.EMailAddress;
emailCc = partManagerEmail;
}
else
{
emailTo = $"{callContextClient.CurrentUserId}@stephengould.com";
subject = $"(TEST EMAIL){subject}";
body.Append("<table><thead><tr><th colspan=2>TEST EMAIL</th></tr><tr><th colspan=2 style=\"background-color:#0e85b2\">Intended Recipients</th></tr></thead><tbody>");
body.Append($"<tr><td><b>TO</b></td><td>{buyer.EMailAddress}</td></tr>");
body.Append($"<tr><td><b>CC</b></td><td>{partManagerEmail}</td></tr>");
body.Append("</tbody></table>");
}
// Continue building the body with receipt details (company, supplier, POs, etc.)
body.Append($"<ul><li><b>Company: </b> {co.Company1} - {co.Name}</li><li><b>Site: </b>{group.PlantName}</li><li><b>Supplier Name: </b>{group.Vendor.Name}</li><li><b>PO(s): </b>{String.Join(", ", poNumbers)}</li></ul>");
// Build the receipt table with relevant columns like PO/Line/Rel, Part Number, Description, Warehouse, etc.
body.Append($"<table><thead><tr>");
body.Append("<th>PO / Line / Rel</th>");
body.Append("<th>Part Number</th>");
body.Append("<th>Part Desc</th>");
body.Append("<th>Warehouse</th>");
body.Append("<th>Bin</th>");
body.Append("<th>Receipt Qty</th>");
body.Append("<th>Order Qty</th>");
body.Append("<th>Total Received</th>");
body.Append("<th>Unit Cost</th>");
body.Append("<th>Extended Cost</th>");
body.Append("</tr></thead><tbody>");
// Track the total amounts for the POs
decimal overallGrandTotal = 0M;
decimal currentPOTotal = 0M;
int lastPONumber = 0;
// Loop through each receipt line and append details to the body
foreach (var item in group.ReceiptDetails)
{
int currentPONumber = item.ReceiptLines.PONum;
if (lastPONumber != 0 && lastPONumber != currentPONumber)
{
// Append subtotal for the previous PO
body.Append($"<tr><th colspan='9' style='text-align:right;'>Total for PO {lastPONumber}</th><th style='font-weight:normal; text-align:left; padding-left:3;'>{currentPOTotal.ToString("C")}</th></tr>");
currentPOTotal = 0; // Reset current PO total
}
// Append receipt line details (PO/Line/Rel, Part Number, etc.)
body.Append("<tr>");
body.Append($"<td>{item.ReceiptLines.PONum} / {item.ReceiptLines.POLine} / {item.ReceiptLines.PORelNum}</td>");
body.Append($"<td>{item.ReceiptLines.PartNum}</td>");
body.Append($"<td>{item.ReceiptLines.PartDescription}</td>");
body.Append($"<td>{item.Warehouse.Name}</td>");
body.Append($"<td>{item.WhseBin.Description}</td>");
body.Append($"<td>{item.ReceiptLines.VendorQty.ToString("0.#####")}</td>");
body.Append($"<td>{item.PORel.RelQty.ToString("0.#####")}</td>");
// Calculate total received and percentage difference
var totReceived = group.ReceiptDetails.Where(por => por.PORel.PONum == item.PORel.PONum && por.PORel.POLine == item.PORel.POLine).Select(por => por.PORel.ReceivedQty).Sum();
var relQty = item.PORel.RelQty;
var percentageDiff = Math.Abs(totReceived - relQty) / relQty;
body.Append($"<td>{totReceived.ToString("0.#####")}{(percentageDiff > 0.1m ? "⚠" : string.Empty)}</td>");
// Calculate and append unit cost and extended cost
body.Append($"<td>{item.ReceiptLines.VendorUnitCost.ToString("C")}</td>");
body.Append($"<td>{(item.ReceiptLines.VendorUnitCost * item.ReceiptLines.VendorQty / (item.ReceiptLines.CostPerCode == "M" ? 1000 : item.ReceiptLines.CostPerCode == "C" ? 100 : 1)).ToString("C")}</td>");
body.Append("</tr>");
// Track PO totals
decimal extendedCost = item.ReceiptLines.VendorUnitCost * item.ReceiptLines.VendorQty / (item.ReceiptLines.CostPerCode == "M" ? 1000 : item.ReceiptLines.CostPerCode == "C" ? 100 : 1);
currentPOTotal += extendedCost;
overallGrandTotal += extendedCost;
lastPONumber = currentPONumber;
}
// Append the total for the last PO and overall total
if (lastPONumber != 0)
{
body.Append($"<tr><th colspan='9' style='text-align:right;'>Total for PO {lastPONumber}</th><th style='font-weight:normal; text-align:left; padding-left:3;'>{currentPOTotal.ToString("C")}</th></tr>");
}
body.Append($"<tr><th colspan='9' style='text-align:right;padding-left:0;'>Overall Grand Total</th><th style='font-weight:normal; text-align:left;padding-left:3;'>{overallGrandTotal.ToString("C")}</th></tr>");
// Append additional notes and end the HTML body
body.Append($"</tbody></table><br/><br/><p><b>Note:</b> Any items showing the symbol ⚠, indicate that we received 10% over / under ask.</p> <p>Epicor Automated Message<br/><br/><img src=\"https://sgportal.stephengould.com/assets/stephen-gould.png\"/></body></html>");
// Send email using Ice.Mail.SmtpMail
var mailer = this.GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom(emailFrom);
message.SetTo(emailTo);
message.SetCC(emailCc);
#if DEBUG
message.SetBcc("STUFF AND THINGS"); // Add Bcc in debug mode
#endif
message.SetSubject(subject);
message.SetBody(body.ToString());
message.IsBodyHtml = true;
// Send the email
mailer.Send(message);
}
