You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
182 lines
8.4 KiB
182 lines
8.4 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Quartz;
|
|
|
|
namespace ICSSoft.FromERP
|
|
{
|
|
/// <summary>
|
|
/// 产品Bom(来源U8)
|
|
/// </summary>
|
|
public class IcsSBomFromU8 : 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);
|
|
|
|
|
|
|
|
string erpNames = ICSHelper.GetConfigString()["Workpoint_ERPDB"];
|
|
List<string> DBS = erpNames.Split(',').ToList();
|
|
List<WorkpointERPDBLink> ListDB = new List<WorkpointERPDBLink>();
|
|
foreach (string DB in DBS)
|
|
{
|
|
if (DB.Split('|') != null && DB.Split('|').Count() > 1)
|
|
{
|
|
WorkpointERPDBLink wd = new WorkpointERPDBLink();
|
|
wd.Workpoint = DB.Split('|')[0];
|
|
wd.ERPDB = DB.Split('|')[1];
|
|
ListDB.Add(wd);
|
|
}
|
|
}
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
// string erpName = ICSHelper.GetConfigString()["ERPDB"];
|
|
string TenantId = dr["TenantId"].ToString();
|
|
string TenantCode = dr["TenantCode"].ToString();
|
|
string ErpId = dr["ErpID"].ToString(); //erpID
|
|
string Class = this.GetType().Name + TenantCode;
|
|
|
|
WorkpointERPDBLink dblink = ListDB.FindLast(a => a.Workpoint == TenantCode);
|
|
if (dblink == null)
|
|
{
|
|
return;
|
|
}
|
|
string erpName = dblink.ERPDB;
|
|
|
|
// erpName = string.Format(erpName, TenantId);
|
|
#region SQL
|
|
string sql = @"
|
|
|
|
SELECT DISTINCT
|
|
c.InvCode as 料品编码,
|
|
f.InvCode as 子阶料编码,
|
|
g2.cInvName as 子阶料名称,
|
|
'' 子阶料描述,
|
|
'' 项目号,
|
|
'' 工程变更号,
|
|
d.BaseQtyD 子阶料计量数量,
|
|
'' 子阶料物料版本,
|
|
d.EffBegDate 生效日期,
|
|
d.EffEndDate 失效日期,
|
|
d.SortSeq 次序,
|
|
d.OpSeq 工序号,
|
|
a.Version 母料版本号 ,
|
|
ISNULL(a.ModifyDate, a.CreateDate) as MTime
|
|
,d.OpComponentId as ErpDetailID
|
|
into #TempBOM
|
|
FROM {1}.dbo.bom_bom a With(NoLock) -- BOM资料
|
|
INNER JOIN {1}.dbo.bom_parent b With(NoLock) on a.BomId = b.BomId --BOM母件资料
|
|
INNER JOIN {1}.dbo.bas_part c With(NoLock) on b.ParentId = c.PartId --物料表
|
|
INNER JOIN {1}.dbo.bom_opcomponent d With(NoLock) on a.BomId=d.BomId --BOM子件资料
|
|
INNER JOIN {1}.dbo.bas_part f With(NoLock) on d.ComponentId = f.PartId
|
|
left join {1}.dbo.inventory g With(NoLock) on c.InvCode=g.cInvCode --存货档案
|
|
left join {1}.dbo.inventory g2 With(NoLock) on f.InvCode=g2.cInvCode --存货档案
|
|
|
|
WHERE ISNULL(a.ModifyDate, a.CreateDate)>=@LastTime
|
|
|
|
IF NOT EXISTS(SELECT * FROM #TempBOM)
|
|
RETURN";
|
|
|
|
sql = string.Format(sql, ErpId, erpName);
|
|
sql = ICSHelper.Time(Namespace, Class, TenantId, sql, "#TempBOM");
|
|
sql += "\r\n";
|
|
sql += @"--删除数据
|
|
|
|
|
|
|
|
SELECT a.ErpId INTO #tableICSSBOM
|
|
FROM ICSSBOM a With(NoLock)
|
|
left join {1}.dbo.bom_opcomponent d With(NoLock) on a.ErpId=d.OpComponentId
|
|
WHERE d.OpComponentId is null
|
|
and a.TenantId='{0}'
|
|
|
|
|
|
DELETE ICSSBOM
|
|
WHERE TenantId='{0}' and ErpId IN ( SELECT ErpId from #tableICSSBOM)
|
|
";
|
|
Dictionary<string, string> values = new Dictionary<string, string>();
|
|
values.Add("SBOMVER", "a.母料版本号");
|
|
values.Add("ITEMCODE", "a.料品编码");
|
|
values.Add("SBITEMCODE", "a.子阶料编码");
|
|
values.Add("SBItemName", "a.子阶料名称");
|
|
values.Add("SBSITEMCODE", "a.子阶料编码");
|
|
values.Add("SBITEMQTY", "a.子阶料计量数量");
|
|
values.Add("SEQ", "1");
|
|
values.Add("SBItemECN", "a.工程变更号");
|
|
values.Add("SBItemStatus", "'Y'");
|
|
values.Add("SBItemEfftime", "a.生效日期");
|
|
values.Add("SBItemInvtime", "a.失效日期");
|
|
values.Add("SBItemProject", "a.项目号");
|
|
values.Add("SBItemSeq", "a.次序");
|
|
values.Add("Location", "a.工序号");
|
|
values.Add("LastModificationTime", "a.Mtime");//操作时间
|
|
values.Add("LastModifierUserId", "'" + ConstWorkPoint.Muser + "'");//操作人
|
|
values.Add("LastModifierUserName", "'" + ConstWorkPoint.Musername + "'");//操作人名称
|
|
values.Add("SBItemContype", "c.ItemContype");
|
|
//更新存在数据
|
|
sql += ICSHelper.UpdateSQL("b", values)
|
|
+ @" #TempBOM a
|
|
INNER JOIN ICSSBOM b ON a.ErpDetailID=b.ErpId
|
|
left join IcsInventory c on b.SBItemCode=c.ItemCode and b.TenantId=c.TenantId
|
|
left join IcsMainCategoryCode d on c.ItemMainCategoryCode=d.ItemMainCategoryCode and c.TenantId=d.TenantId
|
|
WHERE b.TenantId='{0}' ";
|
|
|
|
values.Add("ErpId", "convert(nvarchar(100), a.ErpDetailID)");
|
|
values.Add("CreationTime", "a.MTime");//操作人
|
|
values.Add("CreatorUserId", "'" + ConstWorkPoint.Muser + "'");//操作人
|
|
values.Add("CreatorUserName", "'" + ConstWorkPoint.Musername + "'");//操作人名称
|
|
values.Add("TenantId", "'" + TenantId + "'");//站点
|
|
sql += "\r\n";
|
|
//插入新增数据
|
|
sql += ICSHelper.InsertSQL("ICSSBOM", values)
|
|
+ @" #TempBOM a
|
|
LEFT JOIN ICSSBOM b ON a.ErpDetailID=b.ErpId and b.TenantId='{0}'
|
|
left join IcsInventory c on a.子阶料编码=c.ItemCode and c.TenantId='{0}'
|
|
left join IcsMainCategoryCode d on c.ItemMainCategoryCode=d.ItemMainCategoryCode and d.TenantId='{0}'
|
|
WHERE 1=1 and b.id is null
|
|
";
|
|
sql += " DROP TABLE #TempBOM " +
|
|
"DROP TABLE #tableICSSBOM";
|
|
sql = string.Format(sql, TenantId, erpName, ErpId);
|
|
// return;
|
|
ICSHelper.ExecuteDate(conStr, sql);
|
|
#endregion
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
}
|