IcsFromERPJob/ICSSoft.FromERP/ICSReplenishapply.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 ICSReplenishapply : 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  CAST(a.ApplyId as VARCHAR(50)) AS PODetailID ,
                                        a.DocCode AS VouchCode ,
                                        CAST(b.ApplyDId as VARCHAR(50)) AS Did ,
                                        CAST(b.SortSeq as VARCHAR(50)) AS VouchRow ,
                                        e.MoCode AS MOCode ,
                                        c.SortSeq AS MOROW ,
                                        c.InvCode AS InvCode ,
                                        d.InvCode AS SubInvCode ,
                                        a.DocDate AS VoucherDate ,
                                        CASE WHEN f.bCutMantissa='1' AND f.bKCCutMantissa='1' THEN CEILING(b.ApplyQty) ELSE b.ApplyQty END  AS Quantity ,
                                        a.MDeptCode AS DepCode,
                                        '' AS WHCode ,
                                        a.CreateUser AS MUSER ,
                                        '' AS MUSERName ,
                                        ISNULL(a.RelsTime,ISNULL(a.ModifyTime, a.CreateTime)) AS MTIME ,
                                        '补料' AS VouchType ,
                                        '' AS ORDERNO ,
                                        '' AS cBatch
                                        INTO  #TempReplenishapply
                                        FROM {1}.dbo.mom_replenishapply a
                                        INNER JOIN {1}.dbo.mom_replenishapplydtl b ON a.ApplyId=b.ApplyId
                                        INNER JOIN {1}.dbo.mom_orderdetail c ON b.MoDId=c.MoDId
                                        INNER JOIN {1}.dbo.mom_moallocate d ON b.MoAllocateId=d.AllocateId
                                        INNER JOIN {1}.dbo.mom_order e ON c.MoId=e.MoId
                                        LEFT JOIN {1}.dbo.Inventory f ON d.InvCode = f.cInvCode
                                        WHERE  ISNULL (a.RelsUser,'') <> '' AND ISNULL(a.RelsTime,ISNULL(a.ModifyTime, a.CreateTime))>=@LastTime

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

                    sql += @"--删除数据
                               -- DELETE ICSReplenishapply WHERE Did NOT IN (SELECT ApplyDId FROM {1}.dbo.mom_replenishapplydtl ) AND LEN(Did)>0

SELECT  a.Did INTO #tableICSReplenishapply
FROM ICSReplenishapply a With(NoLock)
LEFT JOIN {1}.dbo.[mom_replenishapplydtl] b With(NoLock) ON a.Did=b.ApplyDId
		WHERE a.WorkPoint='{0}' and b.ApplyDId IS NULL     AND LEN(a.Did)>0

	DELETE ICSReplenishapply 
	WHERE WorkPoint='{0}' and  Did IN ( SELECT Did from #tableICSReplenishapply)

                                ";
                    Dictionary<string, string> values = new Dictionary<string, string>();
                    values.Add("VouchCode", "a.VouchCode");
                    values.Add("VouchRow", "a.VouchRow");
                    values.Add("MOCode", "a.MOCode");
                    values.Add("MOROW", "a.MOROW");
                    values.Add("InvCode", "a.InvCode");
                    values.Add("SubInvCode", "a.SubInvCode");
                    values.Add("Quantity", "a.Quantity");
                    values.Add("WHCode", "a.WHCode");
                    values.Add("VouchType", "a.VouchType");
                    values.Add("VoucherDate", "a.VoucherDate");
                    values.Add("MTIME", "a.MTIME");
                    values.Add("PODetailID", "a.PODetailID");

                    values.Add("MUSER", "a.MUSER");
                    values.Add("MUSERName", "a.MUSERName");

                    values.Add("ORDERNO", "a.ORDERNO");
                    values.Add("cBatch", "a.cBatch");
                    values.Add("DepCode", "a.DepCode");
                    //更新存在数据
                    sql += ICSHelper.UpdateSQL("b", values)
                        + @" #TempReplenishapply a
                            INNER JOIN ICSReplenishapply b ON a.Did=b.Did and a.WorkPoint=b.WorkPoint
                            WHERE a.WorkPoint='" + WorkPoint + "' ";

                    values.Add("ID", "NEWID()");
                    values.Add("HasQuantity", "'0'");
                    values.Add("Did", "a.Did");
                    values.Add("WorkPoint", "'" + WorkPoint + "'");

                    //插入新增数据
                    sql += ICSHelper.InsertSQL("ICSReplenishapply", values)
                        + @"  #TempReplenishapply a
                            LEFT JOIN ICSReplenishapply b ON a.Did=b.Did and a.WorkPoint=b.WorkPoint
                            WHERE b.ID IS NULL and a.WorkPoint='" + WorkPoint + @"' 
                            ";
                    sql += " DROP TABLE #TempReplenishapply " +
                        " DROP TABLE #tableICSReplenishapply";
                    sql = string.Format(sql, WorkPoint, erpName);
                    ICSHelper.ExecuteDate(conStr, sql);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}