Given that I used a Template provided by the Community, I guess it should not be a problem to re-share it.
// Zero Cost Job Part Email
//
// 12/06/17 HK: Initial Implementation, since this is considered a temporary, lets keep it simple
//
// Initialize Actions
Func<string, string> GetCompanyAddressAction = (CompanyID) => {
var Company_Row =
(from sc in Db.SysCompany.With(LockHint.NoLock)
where sc.Company == CompanyID
select new { sc.EmailFromAddr, sc.EmailFromLabel }).FirstOrDefault();
if (Company_Row != null) {
return string.Format(@"""{0}"" <{1}>", Company_Row.EmailFromLabel.Trim(), Company_Row.EmailFromAddr.Trim());
}
return string.Empty;
};
// Initialize Variables
decimal TotalStdCost = 0;
// Initialize Mail Variables
string EmailTO = "chrissconnwhereareyou@aol.com";
//string EmailTO = "youcanAddMultiples@cc.me; orccMe@whitehouse.org";
string EmailCC = "";
string EmailBCC = ""; // If blank itll be just ignored on bottom
string EmailSubject = "";
string EmailBody = "";
// Get JobHead
var ttJobHead_Row = ttJobHead.FirstOrDefault();
// Get Demand Link Details
var JobProd_Row =
(from jp in Db.JobProd.With(LockHint.NoLock)
join jh in Db.JobHead.With(LockHint.NoLock)
on new { jp.Company, JN = jp.TargetJobNum } equals new { jh.Company, JN = jh.JobNum }
where
jp.Company == ttJobHead_Row.Company && jp.JobNum == ttJobHead_Row.JobNum && jp.PartNum == ttJobHead_Row.PartNum
select new {
jp.Company, jp.Plant, RecordType = "JobProd", jp.TargetJobNum, jp.TargetAssemblySeq, jp.TargetMtlSeq,
DemandPartNum = jh.PartNum
}
).FirstOrDefault();
if (ttJobHead_Row != null)
{
// Set Subject
EmailSubject = "[ Alert ] Missing Std Cost - Job #: " + ttJobHead_Row.JobNum + " Released in Company " + ttJobHead_Row.Company;
// Start Email Body
EmailBody += string.Format("Job # <b>{0}</b> was released in Company <b>{1}</b> on <b>{2}</b> by <b>{3}</b>:",
ttJobHead_Row.JobNum, ttJobHead_Row.Company, DateTime.Today.ToString("d"), callContextClient.CurrentUserId);
EmailBody += "<BR><BR>";
EmailBody +=
@"<table width='100%' align='center' border='1' cellspacing='1' cellpadding='1' style='font-family: Arial; font-size: 10pt;' bordercolor='#CCCCCC'>"
+ "<tr>"
+ "<th>Demand Job / Part</th>"
+ "<th>Job / Asm / Mtl</th>"
+ "<th>Part Type</th>"
+ "<th>Part</th>"
+ "<th>Prod Group</th>"
+ "<th>Class</th>"
+ "<th>Qty</th>"
+ "<th>Std Cost</th>"
+ "</tr>";
// Union JobAsmbl and JobMtl since they have common columns needed for Details Query
var JobParts = (
from ja in Db.JobAsmbl.With(LockHint.NoLock)
where ja.Company == ttJobHead_Row.Company && ja.JobNum == ttJobHead_Row.JobNum
select new {
ja.Company, ja.Plant, RecordType = "JobAsmbl", Seq = ja.AssemblySeq, ParentSeq = ja.Parent, ja.JobNum, ja.PartNum, ja.Description, ja.IUM, ja.RequiredQty
}
).Union(
from jm in Db.JobMtl.With(LockHint.NoLock)
where jm.Company == ttJobHead_Row.Company && jm.JobNum == ttJobHead_Row.JobNum
select new {
jm.Company, jm.Plant, RecordType = "JobMtl", Seq = jm.MtlSeq, ParentSeq = jm.AssemblySeq, jm.JobNum, jm.PartNum, jm.Description, jm.IUM, jm.RequiredQty
}
);
// Get The Rest of Details for JobParts
var JobParts_Rows =
(from jp_rows in JobParts
join pl in Db.Plant.With(LockHint.NoLock)
on new { jp_rows.Company, PL = jp_rows.Plant } equals new { pl.Company, PL = pl.Plant1 }
join pc in Db.PartCost.With(LockHint.NoLock)
on new { pl.Company, CID = pl.PlantCostID, PartNum = jp_rows.PartNum } equals new { pc.Company, CID = pc.CostID, PartNum = pc.PartNum }
/* Join on PartNum to get ClassID */
join p in Db.Part.With(LockHint.NoLock)
on new { jp_rows.Company, jp_rows.PartNum } equals new { p.Company, p.PartNum }
/* Left Join on PartClass, just incase it does not exist - To Get Description */
join pcl in Db.PartClass.With(LockHint.NoLock)
on new { p.Company, p.ClassID } equals new { pcl.Company, pcl.ClassID }
into pclj
from pcl in pclj.DefaultIfEmpty()
/* Left Join on ProdGrup, just incase it does not exist */
join prdc in Db.ProdGrup.With(LockHint.NoLock)
on new { p.Company, p.ProdCode } equals new { prdc.Company, prdc.ProdCode }
into prdcj
from prdc in prdcj.DefaultIfEmpty()
select new {
jp_rows.Company, jp_rows.Plant, jp_rows.RecordType, jp_rows.Seq, jp_rows.ParentSeq, jp_rows.JobNum, jp_rows.PartNum, jp_rows.Description, jp_rows.IUM, jp_rows.RequiredQty,
p.ClassID, ClassDescription = pcl != null ? pcl.Description : "",
p.ProdCode, ProdGroupDesc = prdc != null ? prdc.Description : "",
TotalStdCost = (pc.StdLaborCost + pc.StdBurdenCost + pc.StdMaterialCost + pc.StdSubContCost + pc.StdMtlBurCost)
}).ToList();
Ice.Diagnostics.Log.WriteEntry("[ ZeroCostJobPart Notification ] - Building Lines...");
foreach (var JobPart_Row in JobParts_Rows)
{
TotalStdCost = Math.Round(JobPart_Row.TotalStdCost, 3);
EmailBody +=
"<tr style='" + (TotalStdCost == 0 && JobPart_Row.ClassID != "REFD" ? "color: red;" : "") + "'>"
+ @"<td align='center'>" + (JobProd_Row == null ? " - " : JobProd_Row.TargetJobNum + " / " + JobProd_Row.DemandPartNum) + "</td>"
+ @"<td align='center'>" + JobPart_Row.JobNum + " / " + JobPart_Row.ParentSeq + " / " + JobPart_Row.Seq + "</td>"
+ @"<td align='center'>" + JobPart_Row.RecordType + "</td>"
+ @"<td align='center'><b>" + JobPart_Row.PartNum + @"</b><br><span style='font-size: 8pt'>" + JobPart_Row.Description + "</span></td>"
+ @"<td align='center'><b>" + JobPart_Row.ProdCode + @"</b><br><span style='font-size: 8pt'>" + JobPart_Row.ProdGroupDesc + "</span></td>"
+ @"<td align='center'><b>" + JobPart_Row.ClassID + @"</b><br><span style='font-size: 8pt'>" + JobPart_Row.ClassDescription + "</span></td>"
+ @"<td align='center'>" + Math.Round(JobPart_Row.RequiredQty, 2) + " " + JobPart_Row.IUM + "</td>"
+ @"<td align='center'>" + Math.Round(JobPart_Row.TotalStdCost, 2) + "</td>"
+ "</tr>";
}
EmailBody += "</table>";
Ice.Diagnostics.Log.WriteEntry("[ ZeroCostJobPart Notification ] - Sending Email...");
// Send Email
var mailer = this.GetMailer(async:true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom( GetCompanyAddressAction(ttJobHead_Row.Company) );
message.SetTo(EmailTO);
message.SetCC(EmailCC);
message.SetBcc(EmailBCC);
message.SetSubject(EmailSubject);
message.SetBody(EmailBody);
message.IsBodyHtml = true;
mailer.Send(message);
Ice.Diagnostics.Log.WriteEntry("[ ZeroCostJobPart Notification ] - Email Sent!");
}
♂
