IcsFromERPJob/ICSSoft.FromERP/ICSWarehouse.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 ICSWarehouse : 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 WorkPoint = dr["WorkPointCode"].ToString();
                    string Class = this.GetType().Name + WorkPoint;
                    erpName = string.Format(erpName, WorkPoint);
                    #region SQL
                    string sql = @"select  A.ID AS Serial ,
                                        A.Code AS StorageCode ,
                                        B.Name AS StorageName ,
                                        CAST(A.IsLot AS VARCHAR) AS BatchEnable,
                                        0 As Asset,
										0 AS Proxy,
										0 AS InCost,
                                        ISNULL(A.CreatedOn,A.ModifiedOn) AS MTIME,
										C.Code AS WorkPoint
                                        INTO #TempWarehouse
                                        FROM {1}.dbo.[CBO_Wh] A
                                        LEFT JOIN {1}.dbo.[CBO_Wh_Trl] AS B ON B.ID = A.ID AND B.SysMLFlag = 'zh-CN'
                                        LEFT JOIN {1}.dbo.Base_Organization C ON A.Org=C.ID
                                        WHERE C.Code='{0}' AND ISNULL(A.CreatedOn,A.ModifiedOn)>=@LastTime
                                        ORDER BY A.Code

                                        IF NOT EXISTS(SELECT * FROM #TempWarehouse)
                                        RETURN";
                    sql = ICSHelper.Time(Namespace, Class, WorkPoint, sql, "#TempWarehouse");

                    sql += @"--删除数据
                               -- DELETE ICSWarehouse WHERE WarehouseCode NOT IN (SELECT cWhCode FROM {1}.dbo.CBO_Wh)

                                SELECT  a.WarehouseCode INTO #tableICSWarehouse
                                FROM ICSWarehouse a With(NoLock)
                                LEFT JOIN {1}.dbo.[CBO_Wh] b With(NoLock) ON a.ID=b.ID
	                                WHERE a.WorkPoint='{0}' and b.ID IS NULL 

                                DELETE ICSWarehouse 
                                WHERE WorkPoint='{0}' and  WarehouseCode IN ( SELECT WarehouseCode from #tableICSWarehouse)";
                    Dictionary<string, string> values = new Dictionary<string, string>();
                    values.Add("WarehouseName", "a.StorageName");
                    values.Add("MTIME", "a.MTIME");
                    //更新存在数据
                    sql += ICSHelper.UpdateSQL("b", values)
                        + @" #TempWarehouse a
                            INNER JOIN ICSWarehouse b ON a.StorageCode=b.WarehouseCode  and a.WorkPoint=b.WorkPoint
                            WHERE a.WorkPoint='" + WorkPoint + "'";

                    values.Add("WarehouseCode", "a.StorageCode");
                    values.Add("Enable", "1");
                    values.Add("BatchEnable", "a.BatchEnable");
                    values.Add("ID", "a.Serial");
                    values.Add("Asset", "a.Asset");
                    values.Add("Proxy", "a.Proxy");
                    values.Add("InCost", "a.InCost");
                    values.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
                    values.Add("MUSERName", "'" + ConstWorkPoint.Musername + "'");
                    values.Add("WorkPoint", "'" + WorkPoint + "'");
                    //插入新增数据
                    sql += ICSHelper.InsertSQL("ICSWarehouse", values)
                        + @"  #TempWarehouse a
                            LEFT JOIN ICSWarehouse b ON a.StorageCode=b.WarehouseCode  and a.WorkPoint=b.WorkPoint
                            WHERE b.WarehouseCode IS NULL and a.WorkPoint='" + WorkPoint + @"' 
                            ";
                    sql += " DROP TABLE #TempWarehouse " +
                        " DROP TABLE #tableICSWarehouse";
                    sql = string.Format(sql, WorkPoint, erpName);
                    ICSHelper.ExecuteDate(conStr, sql);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}