using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Quartz;
namespace ICSSoft.FromERP
{
///
/// 采购到货(退货单)
///
public class ICSDeliveryNotice : 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 A1.DocNO as DNCode,
CAST(A2.DocLineNo AS NVARCHAR(50)) as Sequence,
A1.Supplier_Code as VenCode ,
A3.Code as DepCode ,
CASE WHEN A1.ReceivementType=0 THEN '1' ELSE '2' END AS DNType,
A2.ItemInfo_ItemCode as InvCode,
CASE WHEN A1.ReceivementType=0 THEN (
Case When A2.SplitFlag=2 Then (SELECT ArriveQtyCU FROM {1}.dbo.PM_RcvLine
WHERE Receivement=A1.ID AND DocLineNo=A2.DocLineNo AND SplitFlag=1)
ELSE A2.ArriveQtyCU END) ELSE A2.RejectQtyTU END as Quantity ,
0 as Amount,
'' as Currency,
0 as UnitPrice,
A1.CreatedBy as CreatePerson,
A1.CreatedOn as CreateDateTime,
2 as Status,
ISNULL(A2.ModifiedOn ,A2.CreatedOn) as MTIME,
A1.ID as DNID,
isnull(A2.SrcDoc_SrcDoc_EntityID,'') as POID,
isnull(A2.SrcDoc_SrcDocLine_EntityID,'') as PODetailID,
cast(A2.ID as varchar(100)) as DNDetailID,
isnull(A7.Code,'') ProjectCode,isnull(A2.InvLotCode,'') cBatch,'' version ,'' brand,
'' as cFree1,
'' as cFree2,
'' as cFree3,
'' as cFree4,
'' as cFree5,
'' as cFree6,
'' as cFree7,
'' as cFree8,
'' as cFree9,
'' as cFree10,CAST('' AS NVARCHAR(500)) as Colspan,
A6.Code as WorkPoint,
A5.Code as WHCode
,A2.DescFlexSegments_PubDescSeg17
INTO #ICSDelive
FROM {1}.dbo.PM_Receivement A1
LEFT JOIN {1}.dbo.PM_RcvLine A2 ON A2.Receivement=A1.ID
LEFT JOIN {1}.dbo.CBO_Department A3 ON A2.RcvDept=A3.ID
LEFT JOIN {1}.dbo.CBO_Department_Trl A4 ON A4.ID = A3.ID AND A4.SysMLFlag='zh-CN'
LEFT JOIN {1}.dbo.CBO_Wh A5 ON A2.Wh=A5.ID
LEFT JOIN {1}.dbo.Base_Organization A6 ON A1.Org=A6.ID
LEFT JOIN {1}.dbo.CBO_Project A7 ON A7.ID=A2.Project
LEFT JOIN {1}.dbo.PM_RcvDocType A8 ON A1.RcvDocType=A8.ID
WHERE (A1.ReceivementType=0 OR A1.ReceivementType=1) AND A2.Status='3'
AND (A2.QCQCConclusion=-1 OR A2.QCQCConclusion=0 OR A2.QCQCConclusion=1 OR A2.QCQCConclusion=5
OR A2.QCQCConclusion=6 OR A2.QCQCConclusion=7 OR A2.QCQCConclusion=12)
and ISNULL(A2.ModifiedOn ,A2.CreatedOn)>=@LastTime AND A6.Code='{0}'
update #ICSDelive set Colspan=isNULL(ProjectCode,'')+'~'+isNULL(cBatch,'')+'~'+isNULL(Version,'')+'~'+isNULL(Brand,'')+'~'+isNULL(cFree1,'')+'~'+isnull(cFree2,'')+'~'+isnull(cFree3,'')+'~'+isnull(cFree4,'')+'~'+isnull(cFree5,'')+'~'+isnull(cFree6,'')+'~'+isnull(cFree7,'')+'~'+isnull(cFree8,'')+'~'+isnull(cFree9,'')+'~'+isnull(cFree10,'')
select distinct
Colspan,ProjectCode,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,WorkPoint,cBatch,version ,brand
INTO #TempExtension
from #ICSDelive
IF NOT EXISTS(SELECT * FROM #ICSDelive)
RETURN";
sql = ICSHelper.Time(Namespace, Class, WorkPoint, sql, "#ICSDelive");
Dictionary value = new Dictionary();
value.Add("ID", "NEWID()");
value.Add("BatchCode", "a.cBatch");
value.Add("version", "a.version");
value.Add("brand", "a.brand");
value.Add("ProjectCode", "a.ProjectCode");
value.Add("cFree1", "a.cFree1");
value.Add("cFree2", "a.cFree2");
value.Add("cFree3", "a.cFree3");
value.Add("cFree4", "a.cFree4");
value.Add("cFree5", "a.cFree5");
value.Add("cFree6", "a.cFree6");
value.Add("cFree7", "a.cFree7");
value.Add("cFree8", "a.cFree8");
value.Add("cFree9", "a.cFree9");
value.Add("cFree10", "a.cFree10");
value.Add("Colspan", "a.Colspan");
value.Add("MTIME", "GETDATE()");
value.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
value.Add("MUSERName", "'" + ConstWorkPoint.Musername + "'");
value.Add("WorkPoint", "'" + WorkPoint + "'");
//插入新增数据->ICSExtension表
sql += ICSHelper.InsertSQL("ICSExtension", value)
+ @"#TempExtension a
LEFT JOIN ICSExtension b ON a.ProjectCode+'~'+a.cBatch+'~'+a.Version+'~'+a.Brand+'~'+a.cFree1+'~'+a.cFree2+'~'+a.cFree3+'~'+a.cFree4+'~'+a.cFree5+'~'+a.cFree6+'~'+a.cFree7+'~'+a.cFree8+'~'+a.cFree9+'~'+a.cFree10=b.Colspan and a.WorkPoint=b.WorkPoint
WHERE b.Colspan IS NULL";
sql += @"--删除数据
--DELETE ICSDeliveryNotice WHERE DNDetailID NOT IN ( SELECT AutoID FROM {1}.dbo.PU_ArrivalVouchs)and ICSDeliveryNotice.EATTRIBUTE1='1' and ASNCode is NULL
SELECT a.DNDetailID INTO #tableICSDeliveryNotice
FROM ICSDeliveryNotice a With(NoLock)
LEFT JOIN {1}.dbo.[PM_RcvLine] b With(NoLock) ON a.DNDetailID=b.ID
WHERE a.WorkPoint='{0}' and b.ID IS NULL and a.EATTRIBUTE1='1' and a.ASNCode is NULL
DELETE ICSDeliveryNotice
WHERE WorkPoint='{0}' and DNDetailID IN ( SELECT DNDetailID from #tableICSDeliveryNotice)
";
Dictionary values = new Dictionary();
values.Add("DNCode", "a.DNCode");
values.Add("Sequence", "a.Sequence");
values.Add("VenCode", "a.VenCode");
values.Add("DepCode", "a.DepCode");
values.Add("DNType", "a.DNType");
values.Add("InvCode", "a.InvCode");
values.Add("Quantity", "abs(a.Quantity)");
values.Add("Amount", "a.Amount");//辅计量
values.Add("UnitPrice", "a.UnitPrice ");
values.Add("Currency", "a.Currency");
values.Add("Status", "a.Status");
values.Add("WHCode", "a.WHCode");
values.Add("CreatePerson", "a.CreatePerson");
values.Add("CreateDateTime", "a.CreateDateTime");
values.Add("POID", "a.POID");
values.Add("PODetailID", "a.PODetailID");
values.Add("DNDetailID", "a.DNDetailID");
values.Add("DNID", "a.DNID");
values.Add("ExtensionID", "c.ID");
values.Add("EATTRIBUTE1", "''");
//values.Add("EATTRIBUTE10", "a.DescFlexSegments_PubDescSeg14");//外部批次(测试)
values.Add("EATTRIBUTE10", "a.DescFlexSegments_PubDescSeg17");
//更新存在数据
sql += ICSHelper.UpdateSQL("ICSDeliveryNotice", values)
+ @" #ICSDelive a
INNER JOIN ICSDeliveryNotice b ON a.DNDetailID=b.DNDetailID and a.WorkPoint=b.WorkPoint
inner join ICSExtension c on a.Colspan=c.Colspan and a.WorkPoint=c.WorkPoint
WHERE a.WorkPoint='" + WorkPoint + "' ";
values.Add("MUSER", "'" + ConstWorkPoint.Muser + "'");
values.Add("MUSERName", "'" + ConstWorkPoint.Musername + "'");
values.Add("WorkPoint", "'" + WorkPoint + "'");
values.Add("MTIME", "a.MTIME");
values.Add("ID", "NEWID()");
values.Add("RCVQuantity", "0");
//values.Add("ErpAutoID", "a.ErpAutoID");
//values.Add("ErpID", "a.ErpID");
//values.Add("iFQuantity", "a.iFQuantity");
//插入新增数据
sql += ICSHelper.InsertSQL("ICSDeliveryNotice", values)
+ @" #ICSDelive a
LEFT JOIN ICSDeliveryNotice b ON a.DNDetailID=b.DNDetailID and a.WorkPoint=b.WorkPoint
inner join ICSExtension c on a.Colspan=c.Colspan and a.WorkPoint=c.WorkPoint
WHERE b.DNDetailID IS NULL and a.WorkPoint='" + WorkPoint + "' ";
sql += "DROP TABLE #ICSDelive ";
sql += " DROP TABLE #TempExtension " +
" DROP TABLE #tableICSDeliveryNotice";
sql = string.Format(sql, WorkPoint, erpName);
ICSHelper.ExecuteDate(conStr, sql);
#endregion
}
}
catch (Exception ex)
{
log.Error(ex.ToString());
}
}
}
}