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.
179 lines
8.9 KiB
179 lines
8.9 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Quartz;
|
|
|
|
using System.Data;
|
|
using System.Net.NetworkInformation;
|
|
|
|
namespace ICSSoft.FromERP
|
|
{
|
|
/// <summary>
|
|
/// 物料档案(来源U8)
|
|
/// </summary>
|
|
public class ICSInventoryFromU8 : 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 conERPStr = ICSHelper.GetERPConnectString();
|
|
string conStr = ICSHelper.GetConnectString();
|
|
string Namespace = this.GetType().Namespace;
|
|
//string Class = this.GetType().Name;
|
|
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);
|
|
}
|
|
}
|
|
DataTable dt = ICSHelper.GetERPDB(conStr);
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
|
|
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 NEWID() AS ID,
|
|
a.cInvCode AS ItemCode,
|
|
CASE WHEN A.bSelf ='1' THEN '1' WHEN A.bPurchase='1' THEN '1' when a.bProxyForeign='1' THEN '2'
|
|
else '' end ItemFormAttribute,
|
|
dbo.GetReplaceStr(cInvName) AS ItemName,
|
|
a.cEngineerFigNo ItemDrawingNo,
|
|
'' ItemDesc,
|
|
dbo.GetReplaceStr(isnull(cInvAddCode,'')+'|'+isnull(cInvStd,'')) AS ItemStd,
|
|
a.cInvAddCode INVPARSETYPE,
|
|
c.cComUnitName AS ItemUnit,
|
|
a.cInvCCode ItemMainCategoryCode,
|
|
b.cInvCName ClassName,
|
|
ISNULL(c.iChangRate,0) as ItemRate,
|
|
ISNULL(a.iMassDate,'') as EffectiveDays,
|
|
ISNULL(CONVERT(NVARCHAR(50),a.dModifyDate,21),'') MTime,
|
|
(case when isnull(a.fOutExcess,0)=0 then 0 else (case when isnull(a.fOutExcess,0)>5 then 3 else 2 end) end) 备料超额类型,
|
|
isnull(a.fOutExcess,0) 备料超额值
|
|
|
|
INTO #TempINV
|
|
FROM {1}.dbo.Inventory a with(nolock)
|
|
LEFT JOIN {1}.dbo.InventoryClass b with(nolock) ON a.cInvCCode = b.cInvCCode
|
|
LEFT JOIN {1}.dbo.ComputationUnit c with(nolock) ON a.cComUnitCode = c.cComunitCode
|
|
WHERE a.dEDate IS NULL AND a.dModifyDate>=@LastTime
|
|
ORDER BY cInvCode
|
|
|
|
|
|
IF NOT EXISTS(SELECT * FROM #TempINV)
|
|
RETURN";
|
|
sql = string.Format(sql, ErpId, erpName);
|
|
sql = ICSHelper.Time(Namespace, Class, TenantId, sql, "#TempINV");
|
|
|
|
sql += @"--删除数据
|
|
|
|
SELECT a.ItemCode INTO #tableICSINVENTORY
|
|
FROM ICSINVENTORY a With(NoLock)
|
|
LEFT JOIN {1}.dbo.[Inventory] b With(NoLock) ON a.ItemCode=b.cInvCode
|
|
WHERE b.cInvCode is null
|
|
and a.TenantId='{0}'
|
|
|
|
|
|
DELETE ICSINVENTORY
|
|
WHERE TenantId='{0}' and ItemCode IN ( SELECT ItemCode from #tableICSINVENTORY) ";
|
|
Dictionary<string, string> values = new Dictionary<string, string>();
|
|
values.Add("ItemCode", "a.ItemCode");//物料编号
|
|
values.Add("ItemName", "a.ItemName");//物料名称
|
|
values.Add("ItemDesc", "a.ItemDesc");//物料描述
|
|
values.Add("ItemStd", "a.ItemStd");//规格型号
|
|
values.Add("ItemAttribute", "a.ItemFormAttribute");//产品属性(0：采购，1：自制，2：委外，3虚拟）
|
|
values.Add("ItemAddCode", "a.INVPARSETYPE"); //产品代码(U8存货代码 )
|
|
values.Add("ItemUnit", "a.ItemUnit");//主计量单位
|
|
values.Add("ItemMainCategoryCode", "a.ItemMainCategoryCode");//大类编码
|
|
values.Add("ItemRate", "a.ItemRate");
|
|
values.Add("InvVersion", "''");
|
|
values.Add("InvBrand", "''");
|
|
values.Add("InvIQC", "0");//是否IQC检验
|
|
values.Add("InvFQC", "0");//是否FQC检验
|
|
values.Add("EffectiveEnable", "0");//
|
|
values.Add("EffectiveDays", "a.EffectiveDays");//
|
|
values.Add("BatchEnable", "1");//
|
|
values.Add("LotEnable", "0");//
|
|
values.Add("PrintEnable", "0");//
|
|
values.Add("OverType", "a.备料超额类型");//超领类型（1：比例，2：数量，3：不管控）
|
|
values.Add("OverValue", "a.备料超额值");
|
|
values.Add("LastModificationTime", "a.Mtime");//操作时间
|
|
values.Add("ItemType", "d.ItemType");//操作时间
|
|
values.Add("ItemContype", "d.ItemContype");//操作时间
|
|
values.Add("ItemControlType", "d.ItemControlType");//操作时间
|
|
values.Add("ItemDrawingNo", "b.ItemDrawingNo");//产品图号
|
|
|
|
|
|
//更新存在数据
|
|
sql += ICSHelper.UpdateSQL("b", values)
|
|
+ @" #TempINV a
|
|
INNER JOIN ICSINVENTORY b ON a.ItemCode=b.ItemCode
|
|
left join IcsMainCategoryCode d on b.ItemMainCategoryCode=d.ItemMainCategoryCode and b.TenantId=d.TenantId
|
|
WHERE b.TenantId='" + TenantId + "' ";
|
|
|
|
values.Add("CreationTime", "a.MTime");//操作人
|
|
values.Add("CreatorUserId", "'" + ConstWorkPoint.Muser + "'");//操作人
|
|
values.Add("CreatorUserName", "'" + ConstWorkPoint.Musername + "'");//操作人名称
|
|
values.Add("TenantId", "'" + TenantId + "'");//站点
|
|
//插入新增数据
|
|
sql += ICSHelper.InsertSQL("ICSINVENTORY", values)
|
|
+ @" #TempINV a
|
|
LEFT JOIN ICSINVENTORY b ON a.ItemCode=b.ItemCode and b.TenantId='{0}'
|
|
left join IcsMainCategoryCode d on b.ItemMainCategoryCode=d.ItemMainCategoryCode and b.TenantId=d.TenantId
|
|
WHERE 1=1 and b.ItemCode is null
|
|
";
|
|
sql += " DROP TABLE #TempINV " +
|
|
" DROP TABLE #tableICSINVENTORY ";
|
|
sql = string.Format(sql, TenantId, erpName, ErpId);
|
|
|
|
ICSHelper.ExecuteDate(conStr, sql);
|
|
#endregion
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
}
|
|
}
|
|
}
|
|
}
|