IcsFromERPJob/ICSSoft.FromERP/ICSMOBOM.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Quartz;


namespace ICSSoft.FromERP
{
    /// <summary>
    /// 工单备料表(锐腾)
    /// </summary>
    public class ICSMoBom : IJob
    {
        private static object key = new object();
        private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public void Execute(IJobExecutionContext context)
        {
            try
            {
                lock (key)
                {
                    log.Info("开始……………………………………………………………………");
                    Execute();
                    log.Info("结束……………………………………………………………………");
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }

        public void Execute()
        {
            try
            {
                string conStr = ICSHelper.GetConnectString();
                string Namespace = this.GetType().Namespace;
                //string Class = this.GetType().Name;
                DataTable dt = ICSHelper.GetERPDB(conStr);
                foreach (DataRow dr in dt.Rows)
                {
                    string erpName = ICSHelper.GetConfigString()["ERPDB"];
                    string TenantId = dr["TenantId"].ToString();//mes 组织
                    string TenantCode = dr["TenantCode"].ToString();
                    string ErpId = dr["ErpID"].ToString();      //erpID
                    string Class = this.GetType().Name + TenantCode;
                    erpName = string.Format(erpName, TenantId);
                    #region SQL
                    string sql = @"SELECT top 2000  A1.DocNo AS MOCODE ,
                                        A5.Code AS ITEMCODE,
                                        1 AS SEQ,
                                        A4.Code AS MOBITEMCODE,
                                        CAST(A3.ID AS NVARCHAR(50)) AS MOBITEMECN,
                                        A4.Name AS MOBITEMNAME,
                                        A4.SPECS AS MOBITEMDESC,
                                        A1.DocState AS MOBITEMSTATUS,
                                        GETDATE()  AS MOBITEMEFFTIME,
                                        GETDATE()  AS MOBITEMINVTIME,
                                        A3.ActualReqQty  AS MOBITEMQTY,
                                        A3.IssueStyle  AS MOBITEMLOCATION,
                                        c.code  AS MOFAC,
                                        A6.Code AS MOBOMITEMUOM,
                                        CAST(A3.DocLineNO  AS NVARCHAR(50)) AS MOBOMLINE,
                                        A12.Code as WorkPoint,
                                        ISNULL(A3.ModifiedOn,A3.CreatedOn) AS MTIME 
                                        into  #TempPick
                                        FROM {0}.dbo.MO_MO A1
                                        INNER JOIN {0}.dbo.MO_MOPickList A3 ON A3.MO=A1.ID
                                        LEFT JOIN {0}.dbo.CBO_ItemMaster A4 ON A3.ItemMaster=A4.ID
                                        LEFT JOIN {0}.dbo.CBO_ItemMaster A5 ON A1.ItemMaster=A5.ID
                                        LEFT JOIN {0}.dbo.Base_UOM A6 ON A3.IssueUOM=A6.ID
                                        LEFT JOIN {0}.dbo.[Base_Organization] as A12 on (A1.[Org] = A12.[ID])  
                                        LEFT JOIN {0}.dbo.CBO_Wh c on A3.SupplyWh=c.id and A1.org=c.org
                                        WHERE   A3.IsPhantomPart!=1
                                        and ISNULL(A3.ModifiedOn,A3.CreatedOn)>=@LastTime
                                        and a12.id='{1}'  order  by ISNULL(A3.ModifiedOn,A3.CreatedOn) 

                                        IF NOT EXISTS(SELECT * FROM #TempPick)
                                        RETURN";
                    sql = string.Format(sql, erpName, ErpId);
                    sql = ICSHelper.Time(Namespace, Class, TenantId, sql, "#TempPick");
                    Dictionary<string, string> value = new Dictionary<string, string>();


                    sql += "\r\n";
                    sql += @"--删除数据
                                DELETE ICSMOBOM WHERE Default1 NOT IN (SELECT ID FROM {0}.dbo.MO_MOPickList)  AND  ICSMOBOM.TenantId='{1}'";
                    Dictionary<string, string> values = new Dictionary<string, string>();
                    values.Add("MOCODE", "a.MOCODE");
                    values.Add("ITEMCODE", "a.ITEMCODE");
                    values.Add("MOBITEMCODE", "a.MOBITEMCODE");
                    values.Add("MOBITEMNAME", "a.MOBITEMNAME");
                    values.Add("MoBItemSpec", "a.MOBITEMDESC");
                    values.Add("MOBITEMSTATUS", "a.MOBITEMSTATUS");
                    values.Add("MOBITEMEFFTIME", "a.MOBITEMEFFTIME");
                    values.Add("MOBITEMINVTIME", "a.MOBITEMEFFTIME");
                    values.Add("MOBITEMQTY", "a.MOBITEMQTY");
                    values.Add("MOBOMITEMUOM", "a.MOBOMITEMUOM");
                    values.Add("MOBOMLINE", "a.MOBOMLINE");
                    values.Add("MOBITEMLOCATION", "a.MOBITEMLOCATION");
                    values.Add("LastModifierUserId", "'" + ConstWorkPoint.Muser + "'");
                    values.Add("LastModifierUserName", "'" + ConstWorkPoint.Musername + "'");
                    values.Add("LastModificationTime", "a.Mtime");//操作时间
                    values.Add("MOFAC", "a.MOFAC");
                    //更新存在数据
                    sql += ICSHelper.UpdateSQL("b", values)
                        + @" #TempPick a
                            INNER JOIN ICSMOBOM b ON a.MOBITEMECN=b.Default1 AND    B.TenantId='{1}'
                            ";

                    values.Add("Default1", "a.MOBITEMECN");
                    values.Add("CreationTime", "a.MTime");//操作人
                    values.Add("CreatorUserId", "'" + ConstWorkPoint.Muser + "'");//操作人
                    values.Add("CreatorUserName", "'" + ConstWorkPoint.Musername + "'");//操作人名称
                    values.Add("TenantId", "'" + TenantId + "'");//站点

                    sql += "\r\n";
                    //插入新增数据
                    sql += ICSHelper.InsertSQL("ICSMOBOM", values)
                        + @"  #TempPick a
                            LEFT JOIN ICSMOBOM b ON a.MOBITEMECN=b.Default1 AND  B.TenantId='{1}'
                            WHERE b.Default1 IS NULL 
                            ";
                    sql += "DROP TABLE #TempPick";
                    sql = string.Format(sql, erpName, TenantId);
                    //return;
                    ICSHelper.ExecuteDate(conStr, sql);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}