Exemplo n.º 1
0
        /// </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);
        }
Exemplo n.º 2
0
        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();
            }
        }