IcsFromERPJob/ICSSoft.FromERP/ICSPMIssueDoc.cs

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


namespace ICSSoft.FromERP
{
    /// <summary>
    /// 委外领料单
    /// </summary>
    public class ICSPMIssueDoc : 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;
                #region SQL
                string sql = @"SELECT A1.DocNo AS VouchCode,
                                CAST(A2.LineNum AS NVARCHAR(50)) AS VouchRow,
                                A4.Code AS SubInvCode,
                                A2.IssueQty AS Quantity, 
                                A2.IssueQty AS DemandQuantity,
                                CASE WHEN A1.IssueDirection=0 THEN '领料' ELSE '退料' END AS MoveType,
                                A6.Code AS WHCode,
                                A8.Code AS ComUnitCode,
                                CAST(A2.ID AS NVARCHAR(50)) AS Did,
                                A1.BusinessDate AS VoucherDate,
                                CAST(A1.DocState AS NVARCHAR(50)) AS Status,
                                A1.BusinessDate AS MTIME,
                                A12.DocNo AS POCode,
                                A13.Code AS InvCode,
                                A15.Code AS ORDERNO,
                                A1.ID AS EATTRIBUTE,
                                CAST(A10.DocLineNo AS NVARCHAR(50)) AS PORow,
                                A16.Code AS WorkPoint,A18.Code,A2.LotNo AS cBatch
                                into #TempPick
                                FROM {0}.dbo.MaterialAppVouchs a 
                                        INNER JOIN {0}.dbo.MaterialAppVouch b ON a.ID = b.ID
                                        INNER JOIN {0}.dbo.OM_MODetails b ON a.MoDetailsID = b.MoDetailsID
                                        LEFT JOIN {0}.dbo.OM_MOMain c ON b.MOID = c.MOID
                                        LEFT JOIN {0}.dbo.Inventory e ON a.cInvCode = e.cInvCode
                                        WHERE  ISNULL(b.cHandler,'')<> ''
                                        AND ISNULL(b.dnverifytime,ISNULL(b.dnmodifytime, b.dnmaketime))>=@LastTime

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

                sql += @"--删除数据
                                DELETE ICSOutSourePick WHERE Did NOT IN (SELECT ID FROM {0}.dbo.[PM_IssueDocLine]) AND LEN(Did)>0
                                ";
                Dictionary<string, string> values = new Dictionary<string, string>();
                values.Add("VouchCode", "a.VouchCode");
                values.Add("VouchRow", "a.VouchRow");
                values.Add("POCode", "a.POCode");
                values.Add("InvCode", "a.InvCode");
                values.Add("SubInvCode", "a.SubInvCode");
                values.Add("Quantity", "a.Quantity");
                values.Add("DemandQuantity", "a.DemandQuantity");
                values.Add("WHCode", "a.WHCode");
                values.Add("ComUnitCode", "a.ComUnitCode");
                values.Add("Status", "a.Status");
                values.Add("VoucherDate", "a.VoucherDate");
                values.Add("MTIME", "a.MTIME");
                values.Add("EATTRIBUTE", "a.EATTRIBUTE");
                values.Add("PORow", "a.PORow");
                values.Add("WorkPoint", "a.WorkPoint");
                values.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
                values.Add("MUSERName", "'" + ConstWorkPoint.Musername + "'");
                values.Add("cBatch", "a.cBatch");
                values.Add("MoveType", "a.MoveType");
                values.Add("ORDERNO", "a.ORDERNO");
                //values.Add("VoucherNO", "a.VoucherNO");
                //values.Add("Reservation", "a.Reservation");
                //values.Add("ReservationRow", "a.ReservationRow");
                //更新存在数据
                sql += ICSHelper.UpdateSQL("b", values)
                    + @" #TempPick a
                            INNER JOIN ICSOutSourePick b ON a.Did=b.Did
                            ";

                values.Add("ID", "NEWID()");
                values.Add("HasQuantity", "'0'");
                values.Add("Sign", "'1'");
                values.Add("Did", "a.Did");
                //values.Add("WorkPoint", "'" + ConstWorkPoint.WorkPoint + "'");
                //插入新增数据
                sql += ICSHelper.InsertSQL("ICSOutSourePick", values)
                    + @"  #TempPick a
                            LEFT JOIN ICSOutSourePick b ON a.Did=b.Did
                            WHERE b.ID IS NULL
                            ";
                sql += "DROP TABLE #TempPick";
                sql = string.Format(sql, ICSHelper.GetConfigString()["ERPDB"]);
                ICSHelper.ExecuteDate(conStr, sql);
                #endregion
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}