/// </summary>
/// <param name="plant">廠區</param>
/// <param name="column">欄位名稱</param>
/// <param name="value">欄位內容</param>
/// <param name="erp_no">單號</param>
/// <param name="action">申請性質</param>
public void Add(string plant, string column, string value, string erp_no, string action)
{
Out2 o = new Out2();
o.Plant = plant;
o.Column = column;
o.Value = value;
o.ERP_NO = erp_no;
o.Action = action;
_list.Add(o);
}
private void Run(bool isOut)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb_head = new StringBuilder();
StringBuilder sb_head_tr = new StringBuilder();
StringBuilder sb_head_td = new StringBuilder();
StringBuilder sb_body_tr = new StringBuilder();
DBTransfer db = new DBTransfer();
//float count = db.GetCount(sb.ToString());
//lab_count.Text = "總筆數:" + count.ToString();
using (SmoothEnterprise.Database.DataSet EIP = new SmoothEnterprise.Database.DataSet(SmoothEnterprise.Database.DataSetType.OpenRead))
{
sb.Append("select a.* FROM EIPB.DBO.ERP_AXMI25 a left join ");
sb.Append("(select occa01 'occ01' ,max(updatetime) up_time FROM EIPB.DBO.ERP_AXMI25 where isState='Complete' group by occa01 )b ");
sb.Append(" on a.occa01=b.occ01 where a.updatetime=b.up_time ");
if (!string.IsNullOrEmpty(sDt))
{
sb.AppendFormat(" and convert(nvarchar(10),a.occaud15,121)>='{0}'", Convert.ToDateTime(sDt).ToString("yy/MM/dd"));
}
if (!string.IsNullOrEmpty(eDt))
{
sb.AppendFormat(" and convert(nvarchar(10),a.occaud15,121)<= '{0}' ", Convert.ToDateTime(eDt).ToString("yy/MM/dd"));
}
if (!string.IsNullOrEmpty(custem))
{
sb.AppendFormat(" and a.occa01 like '%{0}%' ", custem);
}
sb.Append(" order by a.occaud15 desc ");
Utility.log(sb.ToString());
EIP.Open(sb.ToString());
int i = 0;
while (!EIP.EOF)
{
outInfo = new Out2();
i++;
lab_current.Text = "目前巡查:" + i.ToString();
string rowno = EIP["rowno"].ToString();
string plant = EIP["PlantID"].ToString();
string occa01 = EIP["occa01"].ToString();
string action = EIP["occa00"].ToString().Split(':')[0]; // U:修改 只取第一個字元 U
string ERP_NO = EIP["occano"].ToString();
string status = EIP["isState"].ToString();
string custmer = EIP["occa01"].ToString();
string updatetime = EIP["updatetime"].ToString();
//每一次都要清空
Dictionary <string, string> dc_EIP = new Dictionary <string, string>();
//只有一筆資料
//收集EIP的資料
foreach (DataColumn col in EIP.Columns)
{
string Key = col.ColumnName.ToUpper();
string Val = EIP[Key].ToString();
dc_EIP.Add(Key, Val);
}
SmoothEnterprise.Database.DataSet ERP = new SmoothEnterprise.Database.DataSet(SmoothEnterprise.Database.DataSetType.OpenRead);
{
StringBuilder erp_sql = new StringBuilder();
erp_sql.AppendFormat(" SELECT * FROM IP185.{0}.dbo.OCC_FILE ", plant);
erp_sql.AppendFormat(" WHERE 1=1 AND OCC01 = '{0}'", occa01);
ERP.Open(erp_sql.ToString());
if (ERP.EOF && action == "I")
{
// 沒資料，以OCCA_FILE為主去比對網頁資料，然後新增
erp_sql = new StringBuilder();
erp_sql.AppendFormat(" SELECT * FROM IP185.{0}.dbo.OCCA_FILE ", plant);
erp_sql.AppendFormat(" WHERE occano = '{0}'", ERP_NO);
ERP.Open(erp_sql.ToString());
}
else
{
// 已新增，更新不同的欄位資料
action = "U";
}
if (!ERP.EOF)
{
//有資料
//只有一筆資料
//收集ERP的資料
Dictionary <string, string> dc_ERP = new Dictionary <string, string>();
foreach (DataColumn col in ERP.Columns)
{
string Key = col.ColumnName.ToUpper();
string Val = ERP[Key].ToString();
dc_ERP.Add(Key, Val);
}
StringBuilder sb_body_td = new StringBuilder();
string value = "";
foreach (KeyValuePair <string, string> item in dc_EIP)
{
value = "";
string col = item.Key;
string name = action == "I" ? col : col.Replace("OCCA", "OCC"); //欄位名稱大寫;
string td_ERP = "";
string td_EIP = "";
string bgcolor = " bgcolor='#ffffff'";
if (dc_ERP.ContainsKey(name)) //欄位是在ERP
{
string ERP_Val = dc_ERP[name].Trim();
string EIP_Val = dc_EIP[col].Trim();
if (col == "OCCA12") //創業日，只取日期
{
try
{
if (!string.IsNullOrEmpty(EIP_Val))
{
EIP_Val = Convert.ToDateTime(EIP_Val).ToShortDateString();
}
if (!string.IsNullOrEmpty(ERP_Val))
{
ERP_Val = Convert.ToDateTime(ERP_Val).ToShortDateString();
}
}
catch (Exception ex)
{
bgcolor = " bgcolor='#81c0c0'";
Error = Error + "創業日:" + ERP_NO + "," + EIP_Val + ERP_Val + "<br>";
}
}
if (col == "OCCA1004")
{
EIP_Val = "2";
}
if (string.IsNullOrEmpty(EIP_Val))
{
EIP_Val = "NULL";
}
if (string.IsNullOrEmpty(ERP_Val))
{
ERP_Val = "NULL";
}
//值不相等
bool isChange = true;
if (col == "OCCAUSER" || col == "OCCAGRUP" || col == "OCCAUD15" || col == "OCCA1004" || col == "OCCAACTI") //例外，不更新的欄位
{
isChange = false;
}
Tansfer(col, ref EIP_Val, ref ERP_Val);
if (EIP_Val.Contains(".000"))
{
long iEIP = (long)decimal.Parse(EIP_Val);
long iERP = ERP_Val == "NULL" ? 0 : (long)decimal.Parse(ERP_Val);
if (iEIP == iERP)
{
isChange = false;
}
}
if (!EIP_Val.Contains(ERP_Val) || EIP_Val != ERP_Val)
{
if (ERP_NO == "101-1840001")
{
//Utility.log(col + "," + EIP_Val + "," + ERP_Val);
}
if (isChange)//不更新欄位
{
diffect = true;
value = "<font color='#009393'> " + col + "</font><br><font color='red'> " + EIP_Val + "</font>" + "<br>" + "<font color='#484891'> " + ERP_Val + "</font>";
EIP_Val = EIP_Val == "NULL" ? string.Empty : EIP_Val;
outInfo.Add(plant, col, EIP_Val, ERP_NO, action);
}
}
}
if (!string.IsNullOrEmpty(value))
{
sb_body_td.AppendFormat("<td {1} >{0}</td>", value, bgcolor);
}
//string td=sb_td.ToString();
//sb_tr.AppendFormat("<tr>{0}</tr>", td);
}
if (!string.IsNullOrEmpty(sb_body_td.ToString()))
{
sb_head_tr.AppendFormat("<tr><td>{4}</td><td><a href='{7}/ERP_AXMI25/ERP_AXMI25View.aspx?ROWNO={0}' Target='_blank' >{1}</a></td><td>{5}</td><td>{8}</td><td>{9}</td><td>{6}*</td><td >{3}..</td>{2}</tr>", rowno, ERP_NO, sb_body_td.ToString(), status, i.ToString(), custmer, action, Utility.LocalUrl, plant, updatetime);
}
//sb_head.AppendFormat("<tr><td>{0}</td><td>{3}</td><td>{4}</td></tr>", i.ToString(), rowno, ERP_NO, status, sb_td.ToString());
}
}
//----------------輸出xml-------------------------
if (isOut)
{
if (outInfo.List.Count > 0 || action != "U")
{
OutXML("2", rowno, plant, action, ERP_NO);
}
}
EIP.MoveNext();
}
Show = "<table border='1'>" + sb_head_tr.ToString() + "</table>";
if (mail.ToUpper() == "Y")
{
StringBuilder body = new StringBuilder();
body.AppendFormat("Query Data form {0} to {1} <br><br><br>", sDt, eDt);
body.Append(Show);
string subject = string.Format(" {0} to {1} 客代異常報表 ", sDt, eDt);
mail = "[email protected],[email protected];[email protected],[email protected]";
//mail = "*****@*****.**";
Utility.SendMail(mail, "*****@*****.**", subject, body.ToString());
}
lab_count.Text = "總筆數:" + i.ToString();
}
}