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.
167 lines
8.5 KiB
167 lines
8.5 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Quartz;
|
|
|
|
using System.Data;
|
|
|
|
namespace ICSSoft.FromERP
|
|
{
|
|
/// <summary>
|
|
/// 物料档案(凯尔克)
|
|
/// </summary>
|
|
public class ICSInventory : 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;
|
|
|
|
DataTable dt = ICSHelper.GetERPDB(conStr);
|
|
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;
|
|
erpName = string.Format(erpName, TenantId);
|
|
|
|
#region SQL
|
|
string sql = @" select A.ID AS ID,
|
|
A.Code AS ItemCode
|
|
,a.ItemFormAttribute
|
|
,dbo.GetReplaceStr(A.Name) AS ItemName,
|
|
a.DescFlexField_PrivateDescSeg2 ItemDrawingNo
|
|
,b.Description ItemDesc,
|
|
dbo.GetReplaceStr(A.SPECS) AS ItemStd,
|
|
D.Name AS ItemUnit,
|
|
G.Code as ItemMainCategoryCode,
|
|
H.Name as ClassName,
|
|
1 as ItemRate,
|
|
1 as EffectiveDays,
|
|
A.ModifiedOn as MTime
|
|
,I.id as Org
|
|
,Case When Q.IsSueOverType=0 THEN 1 WHEN Q.IsSueOverType=1 THEN 2 WHEN Q.IsSueOverType=2 THEN 3 ELSE 0 END AS 备料超额类型
|
|
,Case When Q.IsSueOverType=0 Then Q.StandardMaterialScale When Q.IsSueOverType=1 Then Q.StandardMaterialQty
|
|
ELSE 0 END AS 备料超额值
|
|
INTO #TempINV
|
|
from {1}.dbo.[CBO_ItemMaster] AS A
|
|
LEFT JOIN {1}.dbo.[CBO_ItemMaster_Trl] AS B ON ( A.[ID] = B.[ID] AND B.SysMLFlag='zh-CN')
|
|
LEFT JOIN {1}.dbo.[Base_UOM] AS C ON ( A.[CostUOM] = C.[ID] )
|
|
LEFT JOIN {1}.dbo.[Base_UOM_Trl] AS D ON ( C.[ID] = D.[ID] AND D.SysMLFlag='zh-CN')
|
|
LEFT JOIN {1}.dbo.[Base_UOM] AS E ON ( A.[InventoryUOM] = E.[ID] )
|
|
LEFT JOIN {1}.dbo.[Base_UOM_Trl] AS F ON ( E.[ID] = F.[ID] AND F.SysMLFlag='zh-CN')
|
|
LEFT JOIN {1}.dbo.[CBO_Category] AS G ON ( A.[MainItemCategory] = G.[ID] )
|
|
LEFT JOIN {1}.dbo.[CBO_Category_Trl] AS H ON ( G.[ID] = H.[ID] AND H.SysMLFlag='zh-CN')
|
|
LEFT JOIN {1}.dbo.[Base_Organization] AS I ON ( A.[Org] = I.[ID] )
|
|
LEFT JOIN {1}.dbo.[Base_Organization_Trl] AS J ON ( I.[ID] = J.[ID] AND J.SysMLFlag='zh-CN')
|
|
LEFT JOIN {1}.dbo.CBO_InventoryInfo L on A.id=L.ItemMaster
|
|
LEFT JOIN {1}.dbo.CBO_Wh M on L.warehouse=M.id and A.org=M.org
|
|
LEFT JOIN {1}.dbo.CBO_Category_Trl P ON P.ID=A.MainItemCategory
|
|
LEFT JOIN {1}.dbo.CBO_MfgInfo Q ON Q.ItemMaster=A.ID
|
|
where A.ModifiedOn>=@LastTime AND I.id={0}
|
|
ORDER BY A.Code
|
|
|
|
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.[CBO_ItemMaster] b With(NoLock) ON a.ItemCode=b.code
|
|
WHERE b.code is null
|
|
and a.TenantId='{0}'
|
|
AND b.Org={2}
|
|
|
|
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");//规格型号
|
|
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");//大类编码
|
|
values.Add("InvFQC", "0");//大类编码
|
|
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.备料超额类型");
|
|
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", "a.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 + "' and a.Org={2} ";
|
|
|
|
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
|
|
and a.Org={2} ";
|
|
sql += " DROP TABLE #TempINV " +
|
|
" DROP TABLE #tableICSINVENTORY ";
|
|
sql = string.Format(sql, TenantId, erpName, ErpId);
|
|
// return;
|
|
log.Info(sql);
|
|
// return;
|
|
ICSHelper.ExecuteDate(conStr, sql);
|
|
#endregion
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
}
|
|
}
|
|
}
|
|
}
|