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 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); } }