myConn.Open(); string cmdstr = "select UserName,Mail,TEL,A.EmployeeID from OLAP.dbo.SMS_Users A,OLAP.dbo.SMS_Roles B where A.EmployeeID = B.EmployeeID and B.InUse ='1' and B.RolesName= 'MOD_FGStock' and A.Mail <>'' order by A.Sort ASC"; //string cmdstr = "select UserName,Mail,TEL,SBU from OLAP.dbo.SMS_Users A,OLAP.dbo.SMS_Roles B where A.EmployeeID = B.EmployeeID and A.UserName='李少宇' and B.InUse ='1' and B.RolesName= 'MOD_FGStock' and A.Mail <>'' order by A.Sort ASC"; SqlDataAdapter da = new SqlDataAdapter(cmdstr,myConn); DataTable dt = new DataTable(); da.Fill(dt); try { string MailContent = this.MailContent(); for(int i =0;i<dt.Rows.Count;i++) { this.writeLog(dt.Rows[i]["Mail"].ToString().Trim()); string Mail = dt.Rows[i]["Mail"].ToString().Trim(); this.OnSend(Mail,MailContent); } } catch(Exception ex) { //this.OnSend("[email protected]","BI_BEOL_FGStock服務發送Mail服務失敗,失敗原因:"+ex.ToString()); this.OnSend("[email protected]","BI_MOD_FGStock服務發送Mail服務失敗,失敗原因:"+ex.ToString()); this.writeLog("Failed"); }
myConn.Close(); myConn.Dispose();
} #endregion #region Get Re private string GetRe() { string Re = ""; Re += "<b>In-house Unrestricted :</b>Stock can be used<br>"; Re += "<p><b>In-house Restricted :</b>Stock should be reviewed"; return Re; } #endregion #region 得到英文簡寫月份 private string GetMonth() { System.DateTime date = DateTime.Now; string month = ""; //用來得到簡寫的英文月份 if(System.DateTime.Now.Month!=5) { month = date.ToString("MMM", DateTimeFormatInfo.InvariantInfo)+"."; } else { month = date.ToString("MMM", DateTimeFormatInfo.InvariantInfo); } return month; } #endregion
//表尾 string tableEnd =""; // string re = "<b>Input :</b> The total quantity passed the 'Asy Panel' workstation.<br><b>Output :</b> The total quantiy passed the 'Packing' workstation."; // re += "<br><b>外包:</b> The input quantity of F/G return from outsource vendor before shipping.<br><b>Shiping :</b> The total quantity passed the 'Shipping' workstation."; // // // string tableBottom = "<tr><td colspan = 10 bgcolor = \"#D2FFE0\">"+re+"</td></tr>"; tableEnd = "</table>";
string mailEnd = "Please contact MIS if have any question.<br><br>Innolux MIS Div.<br>BIS Sec.<br>EXT:65139<br>"; string mailBody = ""; if(dt.Rows.Count!=0) { mailBody = mailHeader+tableHeader+tableBody+tableEnd+"<br><br>"+mailEnd+"<br><br><br><br>"; } else { mailBody = "<<This mail is deliverd by system, please don't reply.>><br><br>Dear Sirs,<br><br> "; mailBody += "There is no any Finished Goods quantity for "+System.DateTime.Now.AddDays(-1).ToString("M/d/yyyy").Trim()+"."; mailBody += "<br> For the details, please visit <a href=\"http://BIS\">http://BIS</a>"; mailBody += " ( Manual Path : ---> 06.Production Review ---> SFC Daily Production Result ) <br><br>"; mailBody += mailEnd; } return mailBody; } #endregion #region private DataTable GetRowsCount() { string cmdstr =" select sizeid,count(*) cout from "; cmdstr += " ( "; cmdstr += " select distinct sizeid,versionid,b.gradeid from innolux.dbo.fact_pipeline a left join innolux.dbo.Dim_PipeLine_Grade b on a.inlpnid=b.innoluxpn "; cmdstr += " where STATIONID='MOD BU' and convert(float,left(sizeid,CHARINDEX('\"',SIZEID)-1))>=14.1 "; cmdstr += " ) a "; cmdstr += " group by sizeid "; cmdstr += " union "; cmdstr += " select 'ztotal' sizeid,1 cout "; string constr = "server=INLLHBI01;database=Innolux;uid=biadm;pwd=bidwh;"; SqlConnection myConn = new SqlConnection(constr); myConn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmdstr,myConn); DataTable dt = new DataTable();
using System.Data;
using System.Data.SqlClient;
using System.Web.Mail;
using System.Globalization;namespace BI_MOD_FGStock
{
/// <summary>
/// Class1 的摘要描述。
/// </summary>
public class BI_MOD_FGStock:AutoJob.AutoJobRun
{
public BI_MOD_FGStock()
{
//
// TODO: 在此加入建構函式的程式碼
//
}
public override void Start(object o)
{
// if(System.DateTime.Now.ToString("HH:mm").Trim()=="07:50")
// {
this.writeLog(" ");
this.writeLog(" ");
this.writeLog(this.GetType().FullName + " Job Start");
this.writeLog(" ");
this.SendAction();
this.writeLog("Done");
// }
}
#region 發送Mail
public void OnSend(string mailTo,string mesgBody)
{
MailMessage mesg = new MailMessage();
mesg.From = "[email protected]";
mesg.To=mailTo;
//mesg.Bcc ="[email protected]"; Innolux Monitor Invoice Quantity for Jun. 2006 (10:00 AM 6/5/2006)
mesg.Subject ="Innolux MOD Finished Goods Stock Quantity For "+System.DateTime.Now.AddDays(-1).ToString("M/d/yyyy")+" (07:00 AM) "; //2006/05/30 7:00 AM)";
mesg.BodyFormat=MailFormat.Html;
mesg.Body = mesgBody; //MesgBody
SmtpMail.SmtpServer="10.133.130.62";
SmtpMail.Send(mesg);
}
#endregion
#region Action
private void SendAction()
{
string ConnString = "server=INLLHBI01;database=OLAP;uid=biadm;pwd=bidwh;";
SqlConnection myConn = new SqlConnection(ConnString);
myConn.Open();
string cmdstr = "select UserName,Mail,TEL,A.EmployeeID from OLAP.dbo.SMS_Users A,OLAP.dbo.SMS_Roles B where A.EmployeeID = B.EmployeeID and B.InUse ='1' and B.RolesName= 'MOD_FGStock' and A.Mail <>'' order by A.Sort ASC";
//string cmdstr = "select UserName,Mail,TEL,SBU from OLAP.dbo.SMS_Users A,OLAP.dbo.SMS_Roles B where A.EmployeeID = B.EmployeeID and A.UserName='李少宇' and B.InUse ='1' and B.RolesName= 'MOD_FGStock' and A.Mail <>'' order by A.Sort ASC";
SqlDataAdapter da = new SqlDataAdapter(cmdstr,myConn);
DataTable dt = new DataTable();
da.Fill(dt);
try
{
string MailContent = this.MailContent();
for(int i =0;i<dt.Rows.Count;i++)
{
this.writeLog(dt.Rows[i]["Mail"].ToString().Trim());
string Mail = dt.Rows[i]["Mail"].ToString().Trim();
this.OnSend(Mail,MailContent);
}
}
catch(Exception ex)
{
//this.OnSend("[email protected]","BI_BEOL_FGStock服務發送Mail服務失敗,失敗原因:"+ex.ToString());
this.OnSend("[email protected]","BI_MOD_FGStock服務發送Mail服務失敗,失敗原因:"+ex.ToString());
this.writeLog("Failed");
}
myConn.Close();
myConn.Dispose();
}
#endregion #region Get Re
private string GetRe()
{
string Re = "";
Re += "<b>In-house Unrestricted :</b>Stock can be used<br>";
Re += "<p><b>In-house Restricted :</b>Stock should be reviewed";
return Re;
}
#endregion #region 得到英文簡寫月份 private string GetMonth()
{
System.DateTime date = DateTime.Now; string month = ""; //用來得到簡寫的英文月份
if(System.DateTime.Now.Month!=5)
{
month = date.ToString("MMM", DateTimeFormatInfo.InvariantInfo)+".";
}
else
{
month = date.ToString("MMM", DateTimeFormatInfo.InvariantInfo);
}
return month;
} #endregion
{ string mailHeader = "<<This mail is deliverd by system, please don't reply.>><br><br>Dear Sirs,<br><br> The following is the Module Finished Goods Stock report for "+System.DateTime.Now.AddDays(-1).ToString("M/d/yyyy").Trim()+"<br><br>";
mailHeader += " For the details, please visit <a href=\"http://BIS\">http://BIS</a> ( Manual Path : --->06.A計劃 ---> 2.1. A計畫每日pipeline庫存(Cut-time 07:30AM) ) <br><br>";
string tableHeader = "";
tableHeader += "<table width=\"500\" cellspacing=\"0\" rules=\"all\" border=\"1\" id=\"DataGrid1\"> "
+ "<tr bgcolor=\"#98E7B1\" valign=\"Middle\" align=\"Center\"> "
+ "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" valign=\"Middle\" align=\"Center\" width=\"50\">Size</th>"
// + "<th colspan=\"1\" rowspan=\"2\" bgcolor=\"#98E7B1\" valign=\"Middle\" align=\"Center\" width=\"85\">Customer</th>"
+ "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" >Version</th> "
+ "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" >Grade</th> "
+ "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" valign=\"Middle\" align=\"Center\" width=\"50\">Qty</th>"
// + "</tr><tr bgcolor=\"#98E7B1\" valign=\"Middle\" align=\"Center\"> "
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"75\">In-house Restricted</th>"
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"75\">In-house Unrestricted</th>"
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"75\">總計</th>"
//
//
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"100\">In-house Restricted</th>"
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"100\">In-house Unrestricted</th>"
// + "<th colspan=\"1\" rowspan=\"1\" bgcolor=\"#98E7B1\" width=\"95\">總計</th>"
+ "</tr>";
DataTable dt = this.GetData();
DataTable dtcount = this.GetRowsCount();
//表体
string tableBody = ""; string size = "";
int rowSpan = 0;
double qty=0;
bool issame=false;
for(int i =0;i<dt.Rows.Count;i++)
{
if(size!=dt.Rows[i]["sizeid"].ToString())
{
size = dt.Rows[i]["sizeid"].ToString();
DataRow[] dr = dtcount.Select("sizeid='"+size+"'");
rowSpan = Convert.ToInt16(dr[0]["cout"].ToString());
issame=true;
}
else
{
rowSpan=1;
issame=false;
}
if(dt.Rows[i]["VersionID"].ToString()!="zsubtotal" && dt.Rows[i]["VersionID"].ToString()!="ztotal")
{
if(issame)
{
//SizeID
tableBody += "<tr><td align=\"Center\" valign=\"Middle\" rowspan=\""+rowSpan+"\">"+dt.Rows[i]["Sizeid"].ToString()+"</td>";
}
//Version
tableBody += "<td align=\"Center\" valign=\"Middle\" rowspan=\"1\">"+dt.Rows[i]["Versionid"].ToString()+"</td>"; //Version
tableBody += "<td align=\"Center\" valign=\"Middle\" rowspan=\"1\">"+dt.Rows[i]["Gradeid"].ToString()+"</td>";
qty = Convert.ToDouble(dt.Rows[i]["Qty"].ToString());
tableBody += "<td align=\"right\" valign=\"Middle\">"+string.Format("{0:#,##0}",qty)+"</td>"; tableBody += "</tr>";
}
else
{
if(dt.Rows[i]["VersionID"].ToString()=="zsubtotal")
{
//Sizeid
tableBody += "<tr bgcolor=\"#FFFF80\"> <td align=\"Center\" valign=\"Middle\" colspan=\"3\">Sub Total</td>";
qty = Convert.ToDouble(dt.Rows[i]["Qty"].ToString());
tableBody += "<td align=\"right\" valign=\"Middle\">"+string.Format("{0:#,##0}",qty)+"</td>"; tableBody += "</tr>";
}
else
{
//SBU
tableBody += "<tr bgcolor=\"#FBBF84\"> <td align=\"Center\" valign=\"Middle\" colspan=\"3\">Total</td>";
qty = Convert.ToDouble(dt.Rows[i]["Qty"].ToString());
tableBody += "<td align=\"right\" valign=\"Middle\">"+string.Format("{0:#,##0}",qty)+"</td>"; tableBody += "</tr>";
}
}
}
//表尾
string tableEnd ="";
// string re = "<b>Input :</b> The total quantity passed the 'Asy Panel' workstation.<br><b>Output :</b> The total quantiy passed the 'Packing' workstation.";
// re += "<br><b>外包:</b> The input quantity of F/G return from outsource vendor before shipping.<br><b>Shiping :</b> The total quantity passed the 'Shipping' workstation.";
//
//
// string tableBottom = "<tr><td colspan = 10 bgcolor = \"#D2FFE0\">"+re+"</td></tr>";
tableEnd = "</table>";
string mailEnd = "Please contact MIS if have any question.<br><br>Innolux MIS Div.<br>BIS Sec.<br>EXT:65139<br>"; string mailBody = "";
if(dt.Rows.Count!=0)
{
mailBody = mailHeader+tableHeader+tableBody+tableEnd+"<br><br>"+mailEnd+"<br><br><br><br>";
}
else
{
mailBody = "<<This mail is deliverd by system, please don't reply.>><br><br>Dear Sirs,<br><br> ";
mailBody += "There is no any Finished Goods quantity for "+System.DateTime.Now.AddDays(-1).ToString("M/d/yyyy").Trim()+".";
mailBody += "<br> For the details, please visit <a href=\"http://BIS\">http://BIS</a>";
mailBody += " ( Manual Path : ---> 06.Production Review ---> SFC Daily Production Result ) <br><br>";
mailBody += mailEnd;
}
return mailBody;
}
#endregion #region
private DataTable GetRowsCount()
{
string cmdstr =" select sizeid,count(*) cout from ";
cmdstr += " ( ";
cmdstr += " select distinct sizeid,versionid,b.gradeid from innolux.dbo.fact_pipeline a left join innolux.dbo.Dim_PipeLine_Grade b on a.inlpnid=b.innoluxpn ";
cmdstr += " where STATIONID='MOD BU' and convert(float,left(sizeid,CHARINDEX('\"',SIZEID)-1))>=14.1 ";
cmdstr += " ) a ";
cmdstr += " group by sizeid ";
cmdstr += " union ";
cmdstr += " select 'ztotal' sizeid,1 cout "; string constr = "server=INLLHBI01;database=Innolux;uid=biadm;pwd=bidwh;";
SqlConnection myConn = new SqlConnection(constr); myConn.Open(); SqlDataAdapter da = new SqlDataAdapter(cmdstr,myConn);
DataTable dt = new DataTable();
da.Fill(dt);
myConn.Close();
myConn.Dispose();
return dt; }