void SendIPQCListStatic()
{
try
{
MyRecord.Say("-----------------开启定时发送4H不良判定统计表-------------------------");
string body = MyConvert.ZH_TW(@"
<HTML>
<BODY style=""FONT-SIZE: 9pt; FONT-FAMILY: PMingLiU"" leftMargin=5 topMargin=5 bgColor=#ece4f3 #ffffff>
<DIV><FONT size=3 face=PMingLiU>{3}ERP系统提示您：</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> 下述表格为过去4H（{0:MM.dd HH:mm}至{2:MM.dd HH:mm}）制程不良判定结果的统计。</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> (详细不良判定内容，请看附档。)</FONT></DIV>
<DIV>{1}
</DIV>
<DIV><FONT face=PMingLiU><FONT size=2></FONT> </DIV>
<DIV><FONT color=#0000ff size=4 face=PMingLiU><STRONG> 此郵件由ERP系統自動發送，请勿在此郵件上直接回復。</STRONG></FONT></DIV>
<DIV><FONT color=#800080 size=2><STRONG> </STRONG>
<FONT color=#000000 face=PMingLiU>{2:yy/MM/dd HH:mm}，由ERP系统伺服器（{4}）自动发送。<BR>
如自動發送功能有問題或者格式内容修改建議，請MailTo:<A href=""mailto:[email protected]"">JOHN</A><BR>
</FONT></FONT></DIV></FONT></BODY></HTML>
");
string bbr = @"
<FONT size=3 face=PMingLiU>
<TABLE style=""BORDER-COLLAPSE: collapse"" cellSpacing=0 cellPadding=0 width=""100%"" border=0>
<TBODY>
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
责任部门
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
前5大不良
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
平均不良率
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
不良数最高工序
</TD>
</TR>
{0}
</TBODY></TABLE>
</FONT>
";
string br = @"
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{0}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{1}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{2:#.###%}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{3}
</TD>
</TR>
";
string SQL = @"
SELECT [a].[ProcessID] AS [OccuredProcessCode], [a].[MachinID] AS [OccuredMachineCode], [a].[Numb1], [a].[Numb2], [a].[SampleNumb], [a].[AdjustNumb],
[a].[AccNoteRdsNo], [a].[RenoteRdsno], [a].[ProduceNo] AS [ProduceRdsNo], [a].[ProductCode], [b].[RejNumb], [b].[ScrapNumber],
[b].[AssignToDepartmentID], [b].[ProjCode], [b].[ItemID], [d].[name] AS [ProductName], [b].[Editor], [b].[EditDate], [c].[name] AS [ProjName],
[dp].[name] AS [AssignToDepartmentName], [dp].[FullSortID], [p].[name] AS [AssignToProcessName], [p1].[name] AS [OccuredProcessName],
[m].[name] AS [OccuredMachineName], [dp1].[name] AS [OccuredDepartmentName], [a].[RptDate],
(ISNULL([b].[RejNumb], 0) / (ISNULL([a].[Numb1], 0) + ISNULL([a].[Numb2], 0) + ISNULL([a].[SampleNumb], 0) + ISNULL([a].[AdjustNumb], 0))) AS [Yield]
FROM [dbo].[ProdDailyReport] [a]
INNER JOIN [dbo].[_PMC_IPQC_List] [b] ON [a].[_ID] = [b].[zbid]
INNER JOIN [dbo].[AllMaterialView] [d] ON [d].[code] = [a].[ProductCode]
INNER JOIN [dbo].[moProcedure] [p1] ON [p1].[code] = [a].[ProcessID]
INNER JOIN [dbo].[moMachine] [m] ON [m].[code] = [a].[MachinID]
INNER JOIN [dbo].[pbDept] [dp1] ON [dp1].[_ID] = [m].[DepartmentID]
LEFT OUTER JOIN [dbo].[_QC_Item] [c] ON [b].[ProjCode] = [c].[code]
LEFT OUTER JOIN [dbo].[pbDept] [dp] ON [dp].[_ID] = [b].[AssignToDepartmentID]
LEFT OUTER JOIN [dbo].[moProcedure] [p] ON [p].[code] = [b].[AssignToProcessCode]
WHERE [a].[QCCheckDate] BETWEEN DATEADD(HOUR, -4, GETDATE()) AND GETDATE()
ORDER BY [a].[RptDate], [a].[ProduceNo], [a].[AccNoteRdsNo]
";
DateTime NowTime = DateTime.Now;
string brs = "";
MyRecord.Say(string.Format("后台计算定时4H不良判定统计表，{0}", NowTime));
using (MyData.MyDataTable md = new MyData.MyDataTable(SQL))
{
string fname = string.Empty;
MyRecord.Say("创建SendMail。");
MyBase.SendMail sm = new MyBase.SendMail();
MyRecord.Say("加载邮件内容。");
MyRecord.Say("计算定时发送4H不良判定统计表");
if (md.MyRows.IsNotEmptySet())
{
var v = from a in md.MyRows
select new IPQCStaticItem(a);
List <IPQCStaticItem> lData = v.ToList();
var va = from a in lData
group a by a.AssignToDepartmentName into g
select new
{
DepartmentName = g.Key,
Top5 = string.Join("，", g.GroupBy(k => k.ProjName).Select(q => new { ProjName = q.Key, RejNumb = q.Sum(p => p.RejNumb) }).OrderByDescending(x => x.RejNumb).Take(5).Select(y => string.Format("{0}：{1}", y.ProjName, y.RejNumb))),
AvgYeild = g.Average(z => z.Yield),
TopProcessName = g.OrderBy(u => u.RejNumb).Take(1).FirstOrDefault().AssignToProcessName
};
foreach (var ri in va)
{
brs += string.Format(br, ri.DepartmentName, ri.Top5, ri.AvgYeild, ri.TopProcessName);
}
MyRecord.Say(string.Format("表格一共：{0}行，表格已经生成。", md.Rows.Count));
bbr = string.Format(bbr, brs);
}
else
{
bbr = @"<FONT size=5 face=PMingLiU color=Red> 过去4H没有做过不良判定。</FONT></DIV>";
}
sm.MailBodyText = MyConvert.ZH_TW(string.Format(body, NowTime.AddHours(-4), bbr, NowTime, MyBase.CompanyTitle, LocalInfo.GetLocalIp()));
sm.Subject = MyConvert.ZH_TW(string.Format("{1}{0:yy年MM月dd日HH时}_4H不良判定统计。", NowTime, MyBase.CompanyTitle));
MyRecord.Say("计算邮件附件内容。");
if (md.MyRows.IsNotEmptySet())
{
IPQCStaticItem xx;
var vAttachView = from a in md.MyRows
orderby a.Value("FullSortID")
select a;
string[] fields = new string[] { "ProduceRdsNo", "AccNoteRdsNo", "RptDate", "OccuredDepartmentName", "OccuredProcessName", "OccuredMachineName", "RenoteRdsno", "ProductCode", "ProductName", "Numb1", "Numb2", "SampleNumb", "AdjustNumb", "ProjName", "RejNumb", "Editor", "EditDate", "AssignToDepartmentName", "AssignToProcessName", "Yield" };
string[] captions = new string[] { "工单号", "完工单号", "完工日期", "发现部门", "发现工序", "发现机台", "不不良判定单号", "产品编号", "料号", "良品数", "不良数", "样品数", "过版纸数", "不良项目", "不良数", "判定人", "判定时间", "责任部门", "责任机台", "不良率" };
fname = ExportExcel.Export(sm, vAttachView, fields, captions, LCStr("不良判定表"));
}
;
MyConfig.MailAddress mAddress = MyConfig.GetMailAddress("IPQCListStatic");
MyRecord.Say(string.Format("MailTO:{0}\r\nMailCC:{1}", mAddress.MailTo, mAddress.MailCC));
sm.MailTo = mAddress.MailTo;
sm.MailCC = mAddress.MailCC;
//sm.MailTo = "*****@*****.**";
MyRecord.Say("发送邮件。");
sm.SendOut();
sm.mail.Dispose();
sm = null;
MyRecord.Say("邮件已经发送。");
if (File.Exists(fname))
{
File.Delete(fname);
}
}
MyRecord.Say("------------------发送完成----------------------------");
}
catch (Exception e)
{
MyRecord.Say(e);
}
}
void ECRECNProgressSendMail()
{
try
{
MyRecord.Say("------------------开始发送ECRECN跟踪表----------------------------");
MyRecord.Say("从数据库搜寻内容");
DateTime NowTime = DateTime.Now;
string body = @"
<HTML>
<BODY style=""FONT-SIZE: 9pt; FONT-FAMILY: PMingLiU"" leftMargin=5 topMargin=5 bgColor=#e6f3af #ffffff>
<DIV><FONT size=3 face=PMingLiU>{0}ERP系统提示您：</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> </FONT></DIV>
<DIV><FONT color=#ff0000 size=5 face=PMingLiU> <B>{2:yy/MM/dd} {3} </B></FONT></DIV>
{4}
<DIV><FONT face=PMingLiU><FONT size=2></FONT> </DIV>
<DIV><FONT color=#0000ff size=2 face=PMingLiU><STRONG> 此郵件由ERP系統自動發送，切勿在此郵件上直接回復。</STRONG></FONT></DIV>
<DIV><FONT color=#800080 size=2><STRONG> </STRONG>
<FONT color=#000000 face=PMingLiU>{1:yy/MM/dd HH:mm}，由ERP系统伺服器自动发送。<BR>
如自動發送功能有問題或者格式内容修改建議，請MailTo:<A href=""mailto:[email protected]"">JOHN</A><BR>
</FONT></FONT></DIV></FONT></BODY></HTML>
";
string brs = MyConvert.ZH_TW(@"
<DIV><FONT size=3 face=PMingLiU>
<TABLE style=""BORDER-COLLAPSE: collapse"" cellSpacing=0 cellPadding=0 width=""100%"" border=0>
<TBODY>
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
发起部门
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
客户
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
料号
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
ECR单号
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
建立
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
状态
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
IE
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
SR
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
生控
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
业务
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
工程
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
核准
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
ECN单号
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
ECN确认
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
ECN状态
</TD>
</TR>
{0}
</TBODY></TABLE></FONT>
</DIV>
");
string br = @"
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{0}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{1}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{2}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{3}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{4}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{5}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{6}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{7}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{8}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{9}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{10}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{11}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{12}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{13}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{14}
</TD>
</TR>
";
string mailBodyMainSentence = string.Empty;
MyRecord.Say("读取数据");
string SQL = @"
SELECT [a].[RdsNo] AS [ECR], [a].[InputDate] AS [ECRInputDate], [a].[RequestDepartmentName], [a].[RequestAuthor] AS [ECRAuthor],
[a].[RequestConfirmAuthor] AS [ECRConfirmAuthor], [a].[RequestDate] AS [ECRDate],
[a].[CheckDate] AS [ECRCheckDate],[a].[Checker] AS [ECRChecker],
[a].[ReviewAuthor1], [a].[ReviewAuthor2], [a].[ReviewAuthor3], [a].[ReviewAuthor4_1] AS [ReviewAuthor4], [a].[ReviewAuthor5], [a].[ReviewAuthor6],
[a].[ReviewAuthor7], [a].[ReviewAuthor8], [a].[ReviewDate1], [a].[ReviewDate2], [a].[ReviewDate3], [a].[ReviewDate4_1] AS [ReviewDate4], [a].[ReviewDate5],
[a].[ReviewDate6], [a].[ReviewDate7], [a].[ReviewDate8], (SELECT [Name]
FROM [_SY_Status] [RST]
WHERE [RST].[Type] = 'ECR'
AND [RST].[StatusID] = [a].[Status]) AS [ECRStatusName], [b].[RdsNo] AS [ECN],
[b].[RequestConfirmAuthor] AS [ECNConfirmAuthor], b.[RequestConfirmDate] AS [ECNConfirmDate], b.[CheckDate] AS [ECNCheckDate],b.Inputer as [ECNAuthor],
b.[Checker] AS [ECNChecker], (SELECT [Name]
FROM [_SY_Status] [RST]
WHERE [RST].[Type] = 'ECN'
AND [RST].[StatusID] = [b].[Status]) AS [ECNStatusName],
[a].[Code],[a].[MutiProd],[a].[ProdName],[a].[CustCode]
FROM [_PMC_EngineerChangeRequest] [a]
LEFT OUTER JOIN [_PMC_EngineerChangeNote] [b] ON [a].[ECN] = [b].[RdsNo]
WHERE [b].[CheckDate] IS NULL
AND [a].[Status] > -1
AND a.[InputDate] > '2018-06-01 00:00:00'
ORDER BY [a].[RdsNo];
";
MyRecord.Say("读取计算完成。");
MyRecord.Say("创建SendMail。");
MyBase.SendMail sm = new MyBase.SendMail();
string fname = string.Empty;
using (MyData.MyDataTable md = new MyData.MyDataTable(SQL))
{
if (md.MyRows.IsNotEmptySet())
{
string xbr = string.Empty;
foreach (var r in md.MyRows)
{
xbr += string.Format(br, r.Value("RequestDepartmentName"),
r.Value("CustCode"),
r.BooleanValue("MutiProd") ? r.Value("Code") : r.Value("ProdName"),
r.Value("ECR"),
string.Format("{0}{2}({1:yy/MM/dd HH:mm})", r.Value("ECRAuthor"), r.DateTimeValue("ECRInputDate"), r.Value("ECRConfirmAuthor").IsNotEmpty() ? string.Format(@"/{0}", r.Value("ECRConfirmAuthor")) : ""),
r.Value("ECRStatusName"),
r.DateTimeValue("ReviewDate1").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor1"), r.DateTimeValue("ReviewDate1")) : "",
r.DateTimeValue("ReviewDate2").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor2"), r.DateTimeValue("ReviewDate2")) : "",
r.DateTimeValue("ReviewDate3").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor3"), r.DateTimeValue("ReviewDate3")) : "",
r.DateTimeValue("ReviewDate5").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor5"), r.DateTimeValue("ReviewDate5")) : "",
r.DateTimeValue("ReviewDate6").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor6"), r.DateTimeValue("ReviewDate6")) : "",
r.DateTimeValue("ReviewDate8").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ReviewAuthor8"), r.DateTimeValue("ReviewDate8")) : "",
r.Value("ECN"),
r.DateTimeValue("ECNConfirmDate").IsNotEmpty() ? string.Format("{0}({1:yy/MM/dd HH:mm})", r.Value("ECNConfirmAuthor"), r.DateTimeValue("ECNConfirmDate")) : "",
r.Value("ECNStatusName")
);
}
brs = string.Format(brs, xbr);
MyRecord.Say("生成邮件内容。");
mailBodyMainSentence = "未结案ECR/ECN单进度。";
}
else
{
mailBodyMainSentence = "无未结案ECR/ECN单。";
brs = string.Empty;
}
}
MyRecord.Say("加载邮件内容。");
sm.MailBodyText = string.Format(body, MyBase.CompanyTitle, DateTime.Now, NowTime, mailBodyMainSentence, brs);
sm.Subject = MyConvert.ZH_TW(string.Format("{1}{0:yy年MM月dd日}未结ECR/ECN进度表", NowTime.Date, MyBase.CompanyTitle));
MyConfig.MailAddress mAddress = MyConfig.GetMailAddress("ECRECN");
MyRecord.Say(string.Format("MailTO:{0}\r\nMailCC:{1}", mAddress.MailTo, mAddress.MailCC));
sm.MailTo = mAddress.MailTo;
sm.MailCC = mAddress.MailCC;
//sm.MailTo = "*****@*****.**";
MyRecord.Say("发送邮件。");
sm.SendOut();
sm.mail.Dispose();
sm = null;
MyRecord.Say("已经发送。");
MyRecord.Say(@"------------------每日未结ECR\ECN-发送完成----------------------------");
}
catch (Exception ex)
{
MyRecord.Say(ex);
}
}
void DeliverPlanNumbSumSendMail()
{
try
{
MyRecord.Say("------------------开始定时发送7日出货计划量----------------------------");
MyRecord.Say("从数据库搜寻内容");
DateTime NowTime = DateTime.Now;
string body = @"
<HTML>
<BODY style=""FONT-SIZE: 9pt; FONT-FAMILY: PMingLiU"" leftMargin=5 topMargin=5 bgColor=#e6f3af #ffffff>
<DIV><FONT size=3 face=PMingLiU>{0}ERP系统提示您：</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> </FONT></DIV>
<DIV><FONT color=#ff0000 size=3 face=PMingLiU> <B>{2:yy/MM/dd} {3}（详情请见附档）</B></FONT></DIV>
{4}
<DIV><FONT face=PMingLiU><FONT size=2></FONT> </DIV>
<DIV><FONT color=#0000ff size=2 face=PMingLiU><STRONG> 此郵件由ERP系統自動發送，切勿在此郵件上直接回復。</STRONG></FONT></DIV>
<DIV><FONT color=#800080 size=2><STRONG> </STRONG>
<FONT color=#000000 face=PMingLiU>{1:yy/MM/dd HH:mm}，由ERP系统伺服器自动发送。<BR>
如自動發送功能有問題或者格式内容修改建議，請MailTo:<A href=""mailto:[email protected]"">JOHN</A><BR>
</FONT></FONT></DIV></FONT></BODY></HTML>
";
string gridTitle = @"
<DIV><FONT size=3 face=PMingLiU>
<TABLE style=""BORDER-COLLAPSE: collapse"" cellSpacing=0 cellPadding=0 width=""100%"" border=0>
<TBODY>
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
日期
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{0:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{1:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{2:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{3:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{4:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{5:yy/MM/dd}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{6:yy/MM/dd}
</TD>
</TR>
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
安排产量合计：
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{7}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{8}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{9}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{10}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{11}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{12}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{13}
</TD>
</TR>
</TBODY></TABLE></FONT>
</DIV>";
string mailBodyMainSentence = string.Empty;
string brs = string.Empty;
MyRecord.Say("读取数据");
string SQL = @"
Declare @NowDate DateTime
Set @NowDate=Convert(DateTime,Convert(VarChar(10),GetDate(),121),121)
Select ProdNo = IsNull(A.ProdNo,B.RdsNo),SendDate = IsNull(A.SendDate,B.pdeliver),DateAdd([MI],-485,IsNull(A.SendDate,B.pDeliver)) as DutySendDate,PNumb=isNull(A.PNumb,B.PNumb),
B.PNumb as ProduceNumber,B.Code,C.Name,C.Type,C.mTypeName as TypeName,C.CustCode
Into #T
from [_PMC_ActiveProduceNote_View] B Left Outer Join [_PMC_DeliverPlan_Sendlist] A ON B.[RdsNo] = A.PRODNO
Left Outer Join [AllMaterialView] C ON B.Code = C.Code
Select * from #T a Where a.SendDate Between @NowDate And DateAdd(""mi"",-1,DateAdd(""Day"",8,@NowDate))
Order by a.ProdNo
";
List <DeilveryPlanItem> DataSource = new List <DeilveryPlanItem>();
using (MyData.MyDataTable md = new MyData.MyDataTable(SQL))
{
if (md.MyRows.IsNotEmptySet())
{
var v = from a in md.MyRows
select new DeilveryPlanItem(a);
DataSource = v.ToList();
}
}
MyRecord.Say("读取计算完成。");
int xGridCount = DataSource.Count;
MyRecord.Say("创建SendMail。");
MyBase.SendMail sm = new MyBase.SendMail();
string fname = string.Empty;
if (xGridCount > 0)
{
string tmpFileName = Path.GetTempFileName();
MyRecord.Say(string.Format("tmpFileName = {0}", tmpFileName));
Regex rgx = new System.Text.RegularExpressions.Regex(@"(?<=tmp)(.+)(?=\.tmp)");
string tmpFileNameLast = rgx.Match(tmpFileName).Value;
MyRecord.Say(string.Format("tmpFileNameLast = {0}", tmpFileNameLast));
string dName = string.Format("{0}\\TMPExcel", Application.StartupPath);
if (!Directory.Exists(dName))
{
Directory.CreateDirectory(dName);
}
fname = string.Format("{0}\\{1}", dName, string.Format("NOTICE_{0:yyyyMMdd}_tmp{1}.xls", NowTime.Date, tmpFileNameLast));
MyRecord.Say(string.Format("fname = {0}", fname));
ExportToExcelDeliveryPlanNumber(fname, DataSource);
MyRecord.Say("已经保存了。");
sm.Attachments.Add(new System.Net.Mail.Attachment(fname));
MyRecord.Say("加载到附件");
var vSum = from a in DataSource
where a.DutySendDate.Date.Subtract(DateTime.Now.Date).TotalDays >= 0
group a by a.DutySendDate.Date into g
orderby g.Key ascending
select new
{
Date = g.Key,
Numb = string.Format("{0:#,##0.0}K", g.Sum(x => x.PNumb) / 1000.00)
};
DateTime[] dts = new DateTime[7]; string[] nbs = new string[7];
int dtsi = 0;
foreach (var item in vSum.Take(7))
{
dts[dtsi] = item.Date;
nbs[dtsi] = item.Numb;
dtsi++;
}
brs = string.Format(gridTitle, dts[0], dts[1], dts[2], dts[3], dts[4], dts[5], dts[6],
nbs[0], nbs[1], nbs[2], nbs[3], nbs[4], nbs[5], nbs[6]);
MyRecord.Say("生成邮件内容。");
mailBodyMainSentence = "未来7天出货计划安排。";
}
else
{
mailBodyMainSentence = "没有出货计划内容。";
brs = string.Empty;
}
MyRecord.Say("加载邮件内容。");
sm.MailBodyText = string.Format(body, MyBase.CompanyTitle, DateTime.Now, NowTime, mailBodyMainSentence, brs);
sm.Subject = MyConvert.ZH_TW(string.Format("{1}{0:yy年MM月dd日}出货计划量", NowTime.Date, MyBase.CompanyTitle));
//string mailto = ConfigurationManager.AppSettings["DeliveryPlanNumberMailTo"], mailcc = ConfigurationManager.AppSettings["DeliveryPlanNumberMailCC"];
//MyRecord.Say(string.Format("MailTO:{0}\r\nMailCC:{1}", mailto, mailcc));
//sm.MailTo = mailto;
//sm.MailCC = mailcc;
//sm.MailTo = "*****@*****.**";
MyConfig.MailAddress mAddress = MyConfig.GetMailAddress("DeliveryPlanNumber");
MyRecord.Say(string.Format("MailTO:{0}\r\nMailCC:{1}", mAddress.MailTo, mAddress.MailCC));
sm.MailTo = mAddress.MailTo;
sm.MailCC = mAddress.MailCC;
//sm.MailTo = "*****@*****.**";
MyRecord.Say("发送邮件。");
sm.SendOut();
sm.mail.Dispose();
sm = null;
MyRecord.Say("已经发送。");
MyRecord.Say("------------------7日出货计划量-发送完成----------------------------");
}
catch (Exception ex)
{
MyRecord.Say(ex);
}
}
void SendWorkspaceInspectionEmail()
{
try
{
MyRecord.Say("-----------------开启定时发送当日纪律稽核表-------------------------");
string body = MyConvert.ZH_TW(@"
<HTML>
<BODY style=""FONT-SIZE: 9pt; FONT-FAMILY: PMingLiU"" leftMargin=5 topMargin=5 bgColor=#ece4f3 #ffffff>
<DIV><FONT size=3 face=PMingLiU>{3}ERP系统提示您：</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> 请注意，以下内容为昨天（{0:yyyy年MM月dd日}）品保纪律稽核问题反馈。</FONT></DIV>
<DIV><FONT size=3 face=PMingLiU> (各部门纪律得分请看附档。)</FONT></DIV>
<DIV>{1}
</DIV>
<DIV><FONT face=PMingLiU><FONT size=2></FONT> </DIV>
<DIV><FONT color=#0000ff size=4 face=PMingLiU><STRONG> 此郵件由ERP系統自動發送，请勿在此郵件上直接回復。</STRONG></FONT></DIV>
<DIV><FONT color=#800080 size=2><STRONG> </STRONG>
<FONT color=#000000 face=PMingLiU>{2:yy/MM/dd HH:mm}，由ERP系统伺服器（{4}）自动发送。<BR>
如自動發送功能有問題或者格式内容修改建議，請MailTo:<A href=""mailto:[email protected]"">JOHN</A><BR>
</FONT></FONT></DIV></FONT></BODY></HTML>
");
string bbr = @"
<FONT size=3 face=PMingLiU>
<TABLE style=""BORDER-COLLAPSE: collapse"" cellSpacing=0 cellPadding=0 width=""100%"" border=0>
<TBODY>
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
白夜班
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
部门
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
工序
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
机台
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
缺失类别
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
纪律稽核不佳说明
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
责任人
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
责任主管
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
稽核人
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
稽核日期
</TD>
</TR>
{0}
</TBODY></TABLE>
</FONT>
";
string br = @"
<TR>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{0}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{1}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{2}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{3}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{4}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{5}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{6}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{7}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{8}
</TD>
<TD class=xl63 style=""BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"" align=center >
{9}
</TD>
</TR>
";
string SQL = @"
Select a.InspectDate,a.ClassType,
ClassTypeName =(Case When isNull(a.ClassType,0) = 1 Then '白班' Else '夜班' End),
DeptName = dp.name,dp.FullSortID,
ProcessName = (Select Name from moProcedure pp where pp.code = b.ProcessCode),
MachineName = (select Name From moMachine mm where mm.code = b.MachineCode),
b.MachineCode,b.ProcessCode,cm.ProjName,
a.InspectMan,b.InspectMemo,b.Remark2,b.Remark3
from [_PMC_KanbanKPI_CheckList] a Inner Join [_PMC_KanbanKPI_CheckList_List] b On a.[_id] = b.zbid
Inner Join [pbDept] dp On dp.Code = b.DepartmentCode
Inner Join [_PMC_Machine_CheckItemList] cm On cm.ItemCode = b.ItemCode
Where a.InspectDate = @DateBegin And b.ScoreSelectValue > 1
Order by a.InspectDate,a.ClassType,dp.FullSortID,b.ProcessCode,b.MachineCode
";
DateTime NowTime = DateTime.Now;
string brs = "";
DateTime dt1 = NowTime.DayOfWeek == DayOfWeek.Monday ? NowTime.AddDays(-2).Date : NowTime.AddDays(-1).Date;
MyData.MyDataParameter[] mps = new MyData.MyDataParameter[]
{
new MyData.MyDataParameter("@DateBegin", dt1, MyData.MyDataParameter.MyDataType.DateTime)
};
MyRecord.Say(string.Format("后台计算定时发送当日纪律稽核表，{0}", dt1));
using (MyData.MyDataTable md = new MyData.MyDataTable(SQL, mps))
{
string fname = string.Empty;
MyRecord.Say("创建SendMail。");
MyBase.SendMail sm = new MyBase.SendMail();
MyRecord.Say("加载邮件内容。");
MyRecord.Say("计算定时发送当日纪律稽核表");
if (md.MyRows.IsNotEmptySet())
{
foreach (var ri in md.MyRows)
{
brs += string.Format(br, ri.Value("ClassTypeName"),
ri.Value("DeptName"),
ri.Value("ProcessName"),
ri.Value("MachineName"),
ri.Value("ProjName"),
ri.Value("InspectMemo"),
ri.Value("Remark2"),
ri.Value("Remark3"),
ri.Value("InspectMan"),
string.Format("{0:yyyy/MM/dd}", ri.DateTimeValue("InspectDate"))
);
}
MyRecord.Say(string.Format("表格一共：{0}行，表格已经生成。", md.Rows.Count));
bbr = string.Format(bbr, brs);
}
else
{
bbr = @"<FONT size=5 face=PMingLiU color=Red> 没有发现纪律稽核不佳项。</FONT></DIV>";
}
sm.MailBodyText = MyConvert.ZH_TW(string.Format(body, dt1, bbr, NowTime, MyBase.CompanyTitle, LocalInfo.GetLocalIp()));
sm.Subject = MyConvert.ZH_TW(string.Format("{1}{0:yy年MM月dd日}_纪律稽核结果。", NowTime, MyBase.CompanyTitle));
string SQL2 = @"
Select a.InspectDate,a.ClassType,
ClassTypeName =(Case When isNull(a.ClassType,0) = 1 Then '白班' Else '夜班' End),
Score=(Sum(b.Score) / sum(cm.Result1Value)) * 100,DeptName = Max(dp.Name),dp.FullSortID,
Value3Time = Sum(Case When b.ScoreSelectValue = 3 Then 1 Else 0 End)
from [_PMC_KanbanKPI_CheckList] a Inner Join [_PMC_KanbanKPI_CheckList_List] b On a.[_id] = b.zbid
Inner Join [pbDept] dp On dp.Code = b.DepartmentCode
Inner Join [_PMC_Machine_CheckItemList] cm On cm.ItemCode = b.ItemCode
Where a.InspectDate = @DateBegin
Group by dp.FullSortID,b.DepartmentCode,a.InspectDate,a.ClassType
Order by a.InspectDate,a.ClassType,dp.FullSortID
";
MyRecord.Say("计算邮件附件内容。");
using (MyData.MyDataTable md2 = new MyData.MyDataTable(SQL2, mps))
{
var vAttachView = from a in md2.MyRows
orderby a.Value("ClassType"), a.Value("FullSortID")
select a;
string[] fields = new string[] { "ClassTypeName", "DeptName", "Score", "Value3Time" };
string[] captions = new string[] { "白夜班", "部门", "得分", "不佳数" };
fname = ExportExcel.Export(sm, vAttachView, fields, captions, LCStr("纪律稽核得分"));
};
//string mailto = ConfigurationManager.AppSettings["WorkspaceInspectionMailTo"], mailcc = ConfigurationManager.AppSettings["WorkspaceInspectionMailCC"];
//MyRecord.Say(string.Format("MailTO:{0} MailCC:{1}", mailto, mailcc));
//sm.MailTo = mailto; // "[email protected],[email protected],[email protected],[email protected]";
//sm.MailCC = mailcc; // "[email protected],[email protected],[email protected]";
////sm.MailTo = "*****@*****.**";
MyConfig.MailAddress mAddress = MyConfig.GetMailAddress("WorkspaceInspection");
MyRecord.Say(string.Format("MailTO:{0}\r\nMailCC:{1}", mAddress.MailTo, mAddress.MailCC));
sm.MailTo = mAddress.MailTo;
sm.MailCC = mAddress.MailCC;
//sm.MailTo = "*****@*****.**";
MyRecord.Say("发送邮件。");
sm.SendOut();
sm.mail.Dispose();
sm = null;
MyRecord.Say("邮件已经发送。");
if (File.Exists(fname))
{
File.Delete(fname);
}
}
MyRecord.Say("------------------发送完成----------------------------");
}
catch (Exception e)
{
MyRecord.Say(e);
}
}
