IcsFromERPJob/ICSSoft.FromERP/ICSPOArrive.cs

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


namespace ICSSoft.FromERP
{
    /// <summary>
    /// 采购（委外）退货
    /// </summary>
    public class ICSPOArrive : 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 CAST(a.Autoid AS NVARCHAR(50)) AS ERPAutoid,
                                        b.cCode AS cCode,
                                        CAST(a.irowno AS NVARCHAR(50)) AS irowno,
                                        b.cVenCode AS cVenCode,
                                        d.cVenName AS cVenName,
                                        b.cDepCode AS cDepCode,
                                        c.cDepName AS cDepName,
                                        b.cWhCode AS cWhCode,
                                        a.cInvCode AS cInvCode,
                                        -a.iQuantity AS iQuantity,
                                        ISNULL(b.dnmodifytime, b.dnmaketime) AS MTIME,
                                        ISNULL(a.cBatch,'') AS Batch,
                                        CAST(b.ID AS NVARCHAR(50)) AS ErpID,
                                        b.cOrderCode AS POCode,
                                        CAST(e.ivouchrowno AS NVARCHAR(50)) AS iPOsID,
                                        b.cBusType AS Free1 
                                         INTO #TempPOArrive
                                        FROM {0}.dbo.RdRecords01 a 
                                        INNER JOIN {0}.dbo.RdRecord01 b ON a.ID = b.ID
                                        LEFT JOIN {0}.dbo.Department c ON b.cDepCode = c.cDepCode
                                        LEFT JOIN {0}.dbo.Vendor d ON b.cVenCode = d.cVenCode
                                        LEFT JOIN {0}.dbo.PO_Podetails e ON a.iPOsID = e.ID
                                        WHERE ISNULL(b.dnmodifytime, b.dnmaketime)>=@LastTime AND b.cBusType IN ('普通采购','委外加工') AND a.iQuantity<0 AND ISNULL(b.cHandler, '')=''

                                        IF NOT EXISTS(SELECT * FROM #TempPOArrive)
                                        RETURN";
                sql = ICSHelper.Time(Namespace, Class, ConstWorkPoint.WorkPoint, sql, "#TempPOArrive");
                sql += "\r\n";
                sql += @"--删除数据
                                DELETE ICSPOArrive WHERE ERPAutoid NOT IN 
                            (SELECT Autoid FROM {0}.dbo.RdRecords01)  AND LEN(ERPAutoid)>0 and Free2='退'";
                Dictionary<string, string> values = new Dictionary<string, string>();
                values.Add("cCode", "a.cCode");
                values.Add("irowno", "a.irowno");
                values.Add("cVenCode", "a.cVenCode");
                values.Add("cVenName", "a.cVenName");
                values.Add("cDepCode", "a.cDepCode");
                values.Add("cDepName", "a.cDepName");
                values.Add("cWhCode", "a.cWhCode");
                values.Add("cInvCode", "a.cInvCode");
                values.Add("iQuantity", "a.iQuantity");
                values.Add("MTime", "a.MTime");
                values.Add("Batch", "a.Batch");
                values.Add("POCode", "a.POCode");
                values.Add("iPOsID", "a.iPOsID");
                values.Add("Free1", "a.Free1");
                values.Add("Free2", "'退'");
                //更新存在数据
                sql += ICSHelper.UpdateSQL("b", values)
                    + @" #TempPOArrive a
                            INNER JOIN ICSPOArrive b ON a.ERPAutoid=b.ERPAutoid
                            ";

                values.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
                //values.Add("MUSERName", "'" + ConstWorkPoint.Musername + "'");
                values.Add("WorkPoint", "'" + ConstWorkPoint.WorkPoint + "'");
                values.Add("ID", "NewID()");
                values.Add("ERPAutoid", "a.ERPAutoid");
                sql += "\r\n";
                //插入新增数据
                sql += ICSHelper.InsertSQL("ICSPOArrive", values)
                    + @"  #TempPOArrive a
                            LEFT JOIN ICSPOArrive b ON a.ERPAutoid=b.ERPAutoid
                            WHERE b.ERPAutoid IS NULL
                            ";
                sql += "DROP TABLE #TempPOArrive";
                sql = string.Format(sql, ICSHelper.GetConfigString()["ERPDB"]);
                ICSHelper.ExecuteDate(conStr, sql);
                #endregion
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}