IcsFromERPJob/ICSSoft.FromERP/ICSICSMOIssue.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 ICSICSMOIssue : 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.cCode,as IssueCode,
                               b.irowno as Sequence,
                               c.MoCode as SourceCode,
                               d.SortSeq as SourceSequence,
                               b.cInvCode as InvCode,
                               b.iQuantity as Quantity
                               b.iNum as Amount,
                               a.cWhCode as WHCode,
                               a.bredvouch as Type,
                               a.iverifystate as Status,
                               a.cMaker as CreatePerson,
                               a.dnmaketime as  CreateDateTime,
                               a.id as IssueID,
                               b.AutoID as IssueDetailID ,
                               ISNULL(a.dnmodifytime, a.dnmaketime) AS MTIME
                                      ,  '{0}' as WorkPoint       
                               INTO  #TempRd
                               from rdrecord11 a
                               inner  join {1}.dbo.rdrecords11 b on a.ID=b.ID
                               left join {1}.dbo.mom_order c on a.iproorderid=c.MoId
                               left JOIN {1}.dbo.mom_orderdetail d ON c.MoId=d.MoId
                               and   ISNULL(a.cHandler , '')<>''  AND ISNULL(a.dnmodifytime, a.dnmaketime)>=@LastTime
                                      
                                     
                                        IF NOT EXISTS(SELECT * FROM #TempRd)
                                        RETURN";
                    sql = ICSHelper.Time(Namespace, Class, WorkPoint, sql, "#TempRd");

                    sql += @"--删除数据
                                --DELETE ICSMOIssue where ISNULL(IssueDetailID,'') NOT IN ( SELECT autoID FROM {1}.dbo.rdrecords11 ) 

SELECT  ISNULL(a.IssueDetailID,'') IssueDetailID INTO #tableICSMOIssue
FROM ICSMOIssue a With(NoLock)
LEFT JOIN {1}.dbo.[rdrecords11] b With(NoLock) ON ISNULL(a.IssueDetailID,'')=b.autoID
		WHERE a.WorkPoint='{0}' and b.autoID IS NULL     

	DELETE ICSMOIssue 
	WHERE WorkPoint='{0}' and  ISNULL(a.IssueDetailID,'') IN ( SELECT IssueDetailID from #tableICSMOIssue)

                                ";
                    sql += "\r\n";
                    Dictionary<string, string> values = new Dictionary<string, string>();

                    values.Add("TransferNO", "a.TransferNO");
                    values.Add("Sequence", "a.Sequence");
                    values.Add("FromWarehouseCode", "a.FromWarehouseCode");
                    values.Add("FromLocationCode", "a.FromLocationCode");
                    values.Add("ToWarehouseCode", "a.ToWarehouseCode");

                    values.Add("Status", "a.Status");
                    values.Add("InvCode", "a.InvCode");
                    values.Add("Quantity", "a.Quantity");
                    values.Add("Amount", "a.Amount");
                    values.Add("Type", "a.Type");
                    values.Add("Memo", "a.Memo");
                    values.Add("CreateDateTime", "a.CreateDateTime");
                    values.Add("CreatePerson", "a.CreatePerson");
                    values.Add("TransferID", "a.TransferID");
                    values.Add("TransferDetailID", "a.TransferDetailID");
                    values.Add("ExtensionID", "''");

                    values.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
                    values.Add("MUSERNAME", "'" + ConstWorkPoint.Musername + "'");
                    values.Add("WorkPoint", "'" + WorkPoint + "'");
                    values.Add("MTIME", "a.MTIME");


                    //更新存在数据
                    sql += "\r\n";
                    sql += ICSHelper.UpdateSQL("b", values)
                        + @" #TempRd a
                            INNER JOIN ICSMOIssue b ON a.IssueDetailID=b.IssueDetailID and a.WorkPoint=b.WorkPoint
                            WHERE a.WorkPoint='" + WorkPoint + "' ";
                    //values.Add("EATTRIBUTE3", "a.EATTRIBUTE3");
                    values.Add("ID", "newid()");
                    values.Add("TransferQuantity", "0");
                    //插入新增数据
                    sql += "\r\n";
                    sql += ICSHelper.InsertSQL("ICSMOIssue", values)
                        + @"  #TempRd a
                            LEFT JOIN ICSMOIssue b ON a.IssueDetailID=b.IssueDetailID and a.WorkPoint=b.WorkPoint
                            WHERE b.IssueDetailID IS NULL and a.WorkPoint='" + WorkPoint + "' ";
                    sql += " DROP TABLE #TempRd " +
                        " DROP TABLE #tableICSMOIssue";
                    sql = string.Format(sql, WorkPoint, erpName);
                    ICSHelper.ExecuteDate(conStr, sql);
                    #endregion
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
            }
        }
    }
}