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.
354 lines
19 KiB
354 lines
19 KiB
using log4net.Core;
|
|
using Newtonsoft.Json;
|
|
using Quartz;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Security.Policy;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Xml.Linq;
|
|
|
|
namespace ICSSoft.FromERP
|
|
{
|
|
public class ICSAddStdWorkHourFromMES : 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 async void Execute()
|
|
{
|
|
try
|
|
{
|
|
Configuration config = GetConfig();
|
|
string url = config.ConnectionStrings.ConnectionStrings["APIAddStdWorkHour"].ConnectionString.ToString();
|
|
// 若变更环境 除了修改接口外，还需要替换ERP.U9DB u9数据库
|
|
log.Info("获取创建定额工时接口 " + url);
|
|
if (string.IsNullOrEmpty(url))
|
|
{
|
|
return;
|
|
}
|
|
string conStr = ICSHelper.GetConnectString();
|
|
string Namespace = this.GetType().Namespace;
|
|
List<string> AErrorCode = new List<string>();
|
|
List<string> BErrorCode = new List<string>();
|
|
List<string> CErrorCode = new List<string>();
|
|
|
|
// 业务逻辑 从u9那边料品关联工时表 找到没有工时数据的料品 再向mes这边根据料号找到数据 去匹配条件 计算出工时 给u9
|
|
// 二 分别抓取整机、本体部、驱动部未统计的料号 本体部、驱动部逆向去找型号
|
|
// 准备请求接口的数据集合
|
|
var AddList = new List<AddStdWorkHourItem>();
|
|
#region 未统计的整机料号
|
|
string sqls = @"select a.code,mf.Id as MFId,mfvb.ValueModel,mfvb.NominalDiameter,ISNULL(SpecialRequirement_ValveBodySpec,'') as SpecialRequirement from ERP.U9DB.dbo.CBO_ItemMaster a
|
|
join (select ROW_NUMBER() over(partition by ManufacturingSerial order by Id desc) as MFIdFlag,Id,ItemModel,ManufacturingSerial,TenantId from IcsManufacturingHead ) mf
|
|
on mf.MFIdFlag = 1 and a.code = mf.ManufacturingSerial --and mf.TenantId = ''
|
|
join IcsMfValveBodySpec mfvb on mf.Id = mfvb.MFId
|
|
left join ERP.U9DB.dbo.CA_StdWorkingHours b
|
|
on a.Id = b.ItemMaster
|
|
LEFT OUTER JOIN ERP.U9DB.dbo.Base_Organization AS oo
|
|
ON oo.ID = a.Org
|
|
where oo.Code = '01' and b.id is null and a.code like 'KA%' and mfvb.ValueModel is not null";
|
|
DataTable dt = ICSHelper.ExecuteTable(conStr, sqls);
|
|
|
|
if (dt != null)
|
|
{
|
|
log.Info("查询到整机待统计数量：" + dt.Rows.Count + "。");
|
|
//准备整机条件 口径+A拼接
|
|
string argsqls = @"select ItemModel,Type,NominalDiameter+'A' as NominalDiameter,Coefficient,ManHour,AdditiveManHour,ISNULL(SpecialRequirement,'') as SpecialRequirement
|
|
from IcsProductionDurationStatistics
|
|
where Type = 0 --and TenantId = ''";
|
|
DataTable argdt = ICSHelper.ExecuteTable(conStr, argsqls);
|
|
if (argdt != null)
|
|
{
|
|
//log.Info("整机循环开始！");
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
// 整机条件过滤 阀门型号、口径、特殊要求
|
|
var ValueModel = dr["ValueModel"].ToString().Length > 5 ? dr["ValueModel"].ToString().Substring(0, 5) : dr["ValueModel"].ToString(); // 截取前五位数字符
|
|
DataRow[] infoArgRow = argdt?.Select("ItemModel='" + ValueModel
|
|
// + "' and SpecialRequirement = '" + dr["SpecialRequirement"].ToString() //特殊条件都为空 或者有特殊条件
|
|
+ "' and NominalDiameter = '" + dr["NominalDiameter"].ToString()
|
|
+ "'");
|
|
if (infoArgRow.Length > 0)
|
|
{
|
|
var Manhour = infoArgRow[0]["ManHour"].ToDecimal(); //本体基础工时
|
|
var AdditiveManHour = infoArgRow[0]["AdditiveManHour"].ToDecimal();//追加工时
|
|
var Coefficient = infoArgRow[0]["Coefficient"].ToDecimal();//系数
|
|
AddList.Add(new AddStdWorkHourItem()
|
|
{
|
|
ORGCode = "01",//u9 Base_Organization表 的 code 字段 组织编码
|
|
WorkHour = ((Manhour + AdditiveManHour) * Coefficient) / 60, // 整机计算逻辑:(本体基础工时型号 + 追加工时) * 系数
|
|
CreatedByCode = "",
|
|
DepartmentCode = "080502",//部门
|
|
ItemCode = dr["code"].ToString()
|
|
});
|
|
}
|
|
else
|
|
{
|
|
//记录未匹配到条件 统计失败的料号
|
|
AErrorCode.Add(dr["code"].ToString()+ "(阀门型号:" + dr["ValueModel"].ToString() + ",特殊规格:" + dr["SpecialRequirement"].ToString()+ ",口径:" + dr["NominalDiameter"].ToString() + ");");
|
|
}
|
|
}
|
|
//log.Info("整机循环结束！");
|
|
}
|
|
else
|
|
{
|
|
log.Info("整机待统计数量：" + dt.Rows.Count + "。但未查询到整机条件配置，已跳过生成。");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
log.Info("查询到整机待统计数量：0。");
|
|
}
|
|
#endregion
|
|
|
|
#region 未统计的本体部
|
|
string vbssqls = @"select b.MFId,a.code,mfvb.ValueModel,mfvb.NominalDiameter,ISNULL(SpecialRequirement_ValveBodySpec,'') as SpecialRequirement from ERP.U9DB.dbo.CBO_ItemMaster a
|
|
join (select ROW_NUMBER() over(partition by PartItemCode order by MFId desc) as Flag,PartItemCode,MFId from ICSPartItem where PartItemName = '阀本体部') b -- and TenantId = ''
|
|
on a.code = b.PartItemCode and b.Flag = 1
|
|
join IcsMfValveBodySpec mfvb on b.MFId = mfvb.MFId
|
|
left join ERP.U9DB.dbo.CA_StdWorkingHours c on a.Id = c.ItemMaster
|
|
LEFT OUTER JOIN ERP.U9DB.dbo.Base_Organization AS oo ON oo.ID = a.Org
|
|
where oo.Code = '01' and c.id is null and a.code like 'A2001%'";
|
|
DataTable vbsdt = ICSHelper.ExecuteTable(conStr, vbssqls);
|
|
if (vbsdt != null)
|
|
{
|
|
log.Info("查询到本体部待统计数量：" + vbsdt.Rows.Count + "。");
|
|
//准备本体部条件 ItemModel型号 驱动部类型时此字段为驱动型号 口径+A拼接
|
|
string vbsargsqls = @"select ItemModel,Type,NominalDiameter+'A' as NominalDiameter,Coefficient,ManHour,AdditiveManHour,ISNULL(SpecialRequirement,'') as SpecialRequirement
|
|
from IcsProductionDurationStatistics
|
|
where Type = 1 --and TenantId = ''";
|
|
DataTable vbsargdt = ICSHelper.ExecuteTable(conStr, vbsargsqls);
|
|
if (vbsargdt != null)
|
|
{
|
|
//log.Info("本体部循环开始！");
|
|
foreach (DataRow dr in vbsdt.Rows)
|
|
{
|
|
// 本体部条件过滤 阀门型号、口径、特殊条件
|
|
var ValueModel = dr["ValueModel"].ToString().Length > 5 ? dr["ValueModel"].ToString().Substring(0, 5) : dr["ValueModel"].ToString(); // 截取前五位数字符
|
|
//log.Info(ValueModel);
|
|
DataRow[] infoVbsRow = vbsargdt?.Select("ItemModel='" + ValueModel
|
|
// + "' and SpecialRequirement = '" + dr["SpecialRequirement"].ToString() //特殊条件都为空 或者有特殊条件
|
|
+ "' and NominalDiameter = '" + dr["NominalDiameter"].ToString()
|
|
+ "'");
|
|
if (infoVbsRow.Length > 0)
|
|
{
|
|
var Manhour = infoVbsRow[0]["ManHour"].ToDecimal(); //本体基础工时
|
|
var AdditiveManHour = infoVbsRow[0]["AdditiveManHour"].ToDecimal();//追加工时
|
|
var Coefficient = infoVbsRow[0]["Coefficient"].ToDecimal();//系数
|
|
AddList.Add(new AddStdWorkHourItem()
|
|
{
|
|
ORGCode = "01",//u9 Base_Organization表 的 code 字段 组织编码
|
|
WorkHour = (Manhour + AdditiveManHour) * Coefficient / 60, // 本体部计算逻辑:(本体基础工时型号 + 追加工时) * 系数
|
|
CreatedByCode = "",
|
|
DepartmentCode = "080502",//部门
|
|
ItemCode = dr["code"].ToString()
|
|
});
|
|
}
|
|
else
|
|
{
|
|
//记录未匹配到条件 统计失败的料号
|
|
BErrorCode.Add(dr["code"].ToString() + "(阀门型号:" + dr["ValueModel"].ToString() + ",特殊规格:" + dr["SpecialRequirement"].ToString() + ",口径:" + dr["NominalDiameter"].ToString() + ");");
|
|
}
|
|
}
|
|
//log.Info("本体部循环结束！");
|
|
}
|
|
else
|
|
{
|
|
log.Info("本体部待统计数量：" + vbsdt.Rows.Count + "。但未查询到本体部条件配置，已跳过生成。");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
log.Info("查询到本体部待统计数量：0。");
|
|
}
|
|
#endregion
|
|
#region 未统计的驱动部
|
|
string dssqls = @"select d.DriveModel,case when ISNULL(d.HandwheelMechanism,'NON') = 'NON' then 1 else 0 end as HandwheelMechanism,b.MFId,a.code
|
|
from ERP.U9DB.dbo.CBO_ItemMaster a
|
|
join (select ROW_NUMBER() over(partition by PartItemCode order by MFId desc) as Flag,PartItemCode,MFId from ICSPartItem where PartItemName = '驱动部') b
|
|
on a.code = b.PartItemCode and b.Flag = 1
|
|
join IcsMfDriverSpec d on b.MFId = d.MFId
|
|
left join ERP.U9DB.dbo.CA_StdWorkingHours c on a.Id = c.ItemMaster
|
|
LEFT OUTER JOIN ERP.U9DB.dbo.Base_Organization AS oo ON oo.ID = a.Org
|
|
where oo.Code = '01' and c.id is null and a.code like 'A2002%'";
|
|
DataTable dsdt = ICSHelper.ExecuteTable(conStr, dssqls);
|
|
if (dsdt != null)
|
|
{
|
|
log.Info("查询到驱动部待统计数量：" + dsdt.Rows.Count + "。");
|
|
//准备驱动部条件 ItemModel型号 驱动部类型时此字段为驱动型号
|
|
string dsArgsqls = @"select ItemModel,Type,Coefficient,ManHour,AdditiveManHour,HandwheelMechanism
|
|
from IcsProductionDurationStatistics
|
|
where Type = 2 --and TenantId = ''";
|
|
DataTable dsArgdt = ICSHelper.ExecuteTable(conStr, dsArgsqls);
|
|
if (dsArgdt != null)
|
|
{
|
|
//log.Info("驱动部循环开始！");
|
|
foreach (DataRow dr in dsdt.Rows)
|
|
{
|
|
// 驱动部条件过滤 驱动型号、是否有手轮机构
|
|
var DriveModel = dr["DriveModel"].ToString().Length > 5 ? dr["DriveModel"].ToString().Substring(0, 5) : dr["DriveModel"].ToString(); // 截取前五位数字符
|
|
//log.Info(DriveModel);
|
|
DataRow[] infoDsArgRow = dsArgdt?.Select("ItemModel='" + DriveModel
|
|
+ "' and HandwheelMechanism = '" + dr["HandwheelMechanism"].ToString()
|
|
+ "'");
|
|
if (infoDsArgRow.Length > 0)
|
|
{
|
|
var Manhour = infoDsArgRow[0]["ManHour"].ToDecimal(); //本体基础工时
|
|
var AdditiveManHour = infoDsArgRow[0]["AdditiveManHour"].ToDecimal();//追加工时
|
|
var Coefficient = infoDsArgRow[0]["Coefficient"].ToDecimal();//系数
|
|
AddList.Add(new AddStdWorkHourItem()
|
|
{
|
|
ORGCode = "01",//u9 Base_Organization表 的 code 字段 组织编码
|
|
WorkHour = (Manhour + AdditiveManHour) * Coefficient / 60, // 驱动部计算逻辑:(本体基础工时型号 + 追加工时) * 系数
|
|
CreatedByCode = "",
|
|
DepartmentCode = "080502",//部门
|
|
ItemCode = dr["code"].ToString()
|
|
});
|
|
}
|
|
else
|
|
{
|
|
//记录未匹配到条件 统计失败的料号
|
|
CErrorCode.Add(dr["code"].ToString() + "(驱动型号:" + dr["DriveModel"].ToString() + ",是否有手轮机构:" + dr["HandwheelMechanism"].ToString() + ");");
|
|
}
|
|
}
|
|
//log.Info("驱动部循环结束！");
|
|
}
|
|
else
|
|
{
|
|
log.Info("驱动部待统计数量：" + dsdt.Rows.Count + "。但未查询到驱动部条件配置，已跳过生成。");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
log.Info("查询到驱动部待统计数量：0。");
|
|
}
|
|
#endregion
|
|
|
|
if (AErrorCode.Any())
|
|
{
|
|
log.Info("未匹配到统计条件的整机料号：" + string.Join(",", AErrorCode) + "。");
|
|
}
|
|
if (BErrorCode.Any())
|
|
{
|
|
log.Info("未匹配到统计条件的本体部料号：" + string.Join(",", BErrorCode) + "。");
|
|
}
|
|
if (CErrorCode.Any())
|
|
{
|
|
log.Info("未匹配到统计条件的驱动部料号：" + string.Join(",", CErrorCode) + "。");
|
|
}
|
|
log.Info("创建定额工时Req：" + JsonConvert.SerializeObject(AddList));
|
|
if (AddList.Any())
|
|
{
|
|
|
|
var response = await HttpHelper.HttpClientPost<AddStdWorkHourReq>(url, JsonConvert.SerializeObject(AddList));
|
|
//返回成功后 用流水号填写 表头产品名称、产品编码、u9料号
|
|
if (response != null)
|
|
{
|
|
if (response.IsOK)
|
|
{
|
|
log.Info("请求成功。" + response.Message);
|
|
}
|
|
else
|
|
{
|
|
log.Info("请求失败。" + response.Message);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
log.Info("未拿到响应，请求失败。");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
log.Info("无需请求。");
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
}
|
|
}
|
|
public static Configuration GetConfig()
|
|
{
|
|
Assembly assembly = Assembly.GetCallingAssembly();
|
|
string path = string.Format("{0}.config", assembly.Location);
|
|
if (!File.Exists(path))
|
|
{
|
|
throw new FileNotFoundException(path + "路径下的文件未找到！");
|
|
}
|
|
try
|
|
{
|
|
ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
|
|
configFile.ExeConfigFilename = path;
|
|
Configuration config = ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
|
|
return config;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 创建定额工时接口入参
|
|
/// </summary>
|
|
public class AddStdWorkHourItem
|
|
{
|
|
/// <summary>
|
|
/// 组织编码
|
|
/// </summary>
|
|
public string ORGCode { get; set; }
|
|
/// <summary>
|
|
/// 料品编码
|
|
/// </summary>
|
|
public string ItemCode { get; set; }
|
|
/// <summary>
|
|
/// 创建人编码
|
|
/// </summary>
|
|
public string CreatedByCode { get; set; }
|
|
/// <summary>
|
|
/// 工时（单位：小时）
|
|
/// </summary>
|
|
public decimal WorkHour { get; set; }
|
|
/// <summary>
|
|
/// 部门编码
|
|
/// </summary>
|
|
public string DepartmentCode { get; set; }
|
|
}
|
|
/// <summary>
|
|
/// 响应
|
|
/// </summary>
|
|
public class AddStdWorkHourReq
|
|
{
|
|
/// <summary>
|
|
/// true：成功；false：失败
|
|
/// </summary>
|
|
public bool IsOK { get; set; }
|
|
/// <summary>
|
|
/// 消息
|
|
/// </summary>
|
|
public string Message { get; set; }
|
|
}
|
|
}
|