public void Handler()
{
#region 逻辑
///判断SRM系统供货计划同步天数
if (string.IsNullOrEmpty(srmSupplyPlanSyncDays))
{
throw new Exception("0x00000192");///系统配置中未发现SRM系统供货计划同步天数
}
int supplyPlanSyncDays = 0;
int.TryParse(srmSupplyPlanSyncDays, out supplyPlanSyncDays);
///以当日为起始，逐天进行数据对比，直至系统配置中设定的天数
///获取当前日期
DateTime dateTime = DateTime.Now;
for (int i = 0; i < supplyPlanSyncDays; i++)
{
string dateColumn = string.Empty;
dateColumn = dateTime.AddDays(i).ToString("yyyyMMdd");
///获取数据库中有效的日期列
dateColumn = new SupplyPlanBLL().GetDatabaseExistsDateColumn(dateColumn);
if (string.IsNullOrEmpty(dateColumn))
{
continue;
}
///获取日期④对应的供货计划数据
List <SrmPrevSupplyPlanInfo> supplyPlanInfos = new SrmPrevSupplyPlanBLL().GetListByDateColumn(dateColumn);
if (supplyPlanInfos.Count == 0)
{
continue;
}
///再获取日期④对应的上一版供货计划数据
List <SrmPrevSupplyPlanInfo> prevSupplyPlanInfos = new SrmPrevSupplyPlanBLL().GetList("[DELIVERY_DATE] = N'" + dateColumn + "'", string.Empty);
///数据库执行语句
StringBuilder sqlSb = new StringBuilder();
///对比两个数据集合中的差异数据行（以相同工厂③、供应商②、物料号①为比对维度，数量有变化的视为差异数据行，包括两个数据集合未能交集的部分也为差异数据行）
var query = (from s in supplyPlanInfos
join p in prevSupplyPlanInfos
on new { s.PartNo, s.SupplierNum, s.Plant, s.DeliveryDate }
equals new { p.PartNo, p.SupplierNum, p.Plant, p.DeliveryDate }
select new { s.PartNo, s.SupplierNum, s.Plant, s.DeliveryDate, s.RequireQty, pQty = p.RequireQty }).ToList();
///取出上一版供货计划删除的部分
List <SrmPrevSupplyPlanInfo> prevSupplyPlans = (from p in prevSupplyPlanInfos
where !(from q in query where p.PartNo == q.PartNo && p.SupplierNum == q.SupplierNum && p.Plant == q.Plant && p.DeliveryDate == q.DeliveryDate select q).Any()
select p).ToList();
///LOG_FID
Guid logFid = Guid.NewGuid();
foreach (var p in prevSupplyPlans)
{
///被删除的数据以需求数量⑤0表示
sqlSb.AppendFormat(@"insert into [LES].[TI_IFM_SRM_SUPPLY_PLAN]
([FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[DELIVERY_DATE],[REQUIRE_QTY],[CREATE_USER],[CREATE_DATE],[VALID_FLAG],[PROCESS_FLAG],[LOG_FID])
values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',0,'{4}',GETDATE(),1,{5},'{6}');"
, p.PartNo, p.SupplierNum, p.Plant, p.DeliveryDate, loginUser, (int)ProcessFlagConstants.Untreated, logFid);
}
///取出最新版供货计划新增的部分
List <SrmPrevSupplyPlanInfo> supplyPlans = (from p in supplyPlanInfos
where !(from q in query where p.PartNo == q.PartNo && p.SupplierNum == q.SupplierNum && p.Plant == q.Plant && p.DeliveryDate == q.DeliveryDate select q).Any()
select p).ToList();
foreach (var s in supplyPlans)
{
sqlSb.AppendFormat(@"insert into [LES].[TI_IFM_SRM_SUPPLY_PLAN]
([FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[DELIVERY_DATE],[REQUIRE_QTY],[CREATE_USER],[CREATE_DATE],[VALID_FLAG],[PROCESS_FLAG],[LOG_FID])
values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',{4},'{5}',GETDATE(),1,{6},'{7}');"
, s.PartNo, s.SupplierNum, s.Plant, s.DeliveryDate, s.RequireQty.GetValueOrDefault(), loginUser, (int)ProcessFlagConstants.Untreated, logFid);
}
///取出数据交集部分 数量有变化的差异行
var queryinfos = query.Where(d => d.RequireQty != d.pQty).ToList();
foreach (var q in queryinfos)
{
sqlSb.AppendFormat(@"insert into [LES].[TI_IFM_SRM_SUPPLY_PLAN]
([FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[DELIVERY_DATE],[REQUIRE_QTY],[CREATE_USER],[CREATE_DATE],[VALID_FLAG],[PROCESS_FLAG],[LOG_FID])
values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',{4},'{5}',GETDATE(),1,{6},'{7}');"
, q.PartNo, q.SupplierNum, q.Plant, q.DeliveryDate, q.RequireQty.GetValueOrDefault(), loginUser, (int)ProcessFlagConstants.Untreated, logFid);
}
if (sqlSb.Length > 0)
{
///调用数据发送
string targetSystem = "SRM";
string methodCode = "LES-SRM-002";
string keyValue = dateColumn;
sqlSb.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql(targetSystem, logFid, methodCode, keyValue, loginUser));
}
///删除上一版供货计划的数据
if (prevSupplyPlanInfos.Count > 0)
{
sqlSb.Append(@"delete from [LES].[TE_ATP_SRM_PREV_SUPPLY_PLAN] where [ID] in (" + string.Join(",", prevSupplyPlanInfos.Select(d => d.Id).ToArray()) + ");");
}
///更新TE供货计划为最新版
foreach (var s in supplyPlanInfos)
{
sqlSb.AppendFormat(@"insert into [LES].[TE_ATP_SRM_PREV_SUPPLY_PLAN]
([FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[DELIVERY_DATE],[REQUIRE_QTY],[CREATE_USER],[CREATE_DATE],[VALID_FLAG])
values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',{4},'{5}',GETDATE(),1);"
, s.PartNo, s.SupplierNum, s.Plant, s.DeliveryDate, s.RequireQty.GetValueOrDefault(), loginUser);
}
#region 执行
using (TransactionScope trans = new TransactionScope())
{
if (sqlSb.Length > 0)
{
BLL.LES.CommonBLL.ExecuteNonQueryBySql(sqlSb.ToString());
}
trans.Complete();
}
#endregion
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":" + dateColumn
+ "|D" + prevSupplyPlans.Count + "|A" + supplyPlans.Count + "|U" + queryinfos.Count);
}
#endregion
}
public void Handler()
{
///数据库执行语句
StringBuilder sqlText = new StringBuilder();
///获取状态⑦为10已创建的缺件表
List <LackOfMaterialInfo> lackOfMaterialInfos = lackOfMaterialBLL.GetListByPage("" +
"[STATUS] = " + (int)LackOfMaterialStatusConstants.WaitForCalculation + "",
"[ID]", 1, 1, out int dataCnt);
///没有需要计算的缺件表
if (lackOfMaterialInfos.Count == 0)
{
return;
}
LackOfMaterialInfo lackOfMaterialInfo = lackOfMaterialInfos.FirstOrDefault();
///准备开始计算时需要先将状态⑦更新为20处理中同时记录开始计算时间⑧
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.Calculating, lackOfMaterialInfo.Id, "", loginUser);
#region 缺件表中的检索条件暂时保留
///物料号②、供应商代码③、工厂代码④为空时表示全部，物料号②可能出现模糊条件需要用charindex进行条件判定
///开始日期⑤与结束日期⑥决定了获取供货计划的数据范围
///物料号更新为多选状态
string[] partNos = new string[] { };
if (!string.IsNullOrEmpty(lackOfMaterialInfo.PartNo))
{
partNos = lackOfMaterialInfo.PartNo.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
}
string partNoCondition = string.Empty;
if (partNos.Length > 0)
{
partNoCondition = "and [PART_NO] in ('" + string.Join("','", partNos) + "') ";
}
///
string supplierNumConidtion = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.SupplierNum))
{
supplierNumConidtion = "and [SUPPLIER_NUM] = N'" + lackOfMaterialInfo.SupplierNum + "' ";
}
///
string plantCondition = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.Plant))
{
plantCondition = "and [PLANT] = N'" + lackOfMaterialInfo.Plant + "' ";
}
///
string partPurchaserCondition = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.PartPurchaser))
{
partPurchaserCondition = "and CHARINDEX(N'" + lackOfMaterialInfo.PartPurchaser + "',[PART_PURCHASER]) > 0 ";
}
#endregion
/// 获取供货计划数据
List <string> dateColumns = new List <string>();
///获取缺件表计算的日期范围
DateTime cDateTime = lackOfMaterialInfo.StartDate.GetValueOrDefault();
while (cDateTime <= lackOfMaterialInfo.EndDate.GetValueOrDefault())
{
dateColumns.Add(cDateTime.ToString("yyyyMMdd"));
cDateTime = cDateTime.AddDays(1);
}
///获取数据库中有效的日期列
dateColumns = new SupplyPlanBLL().GetDatabaseExistsDateColumns(dateColumns);
///无效的日期参数
if (dateColumns.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "无效的日期参数", loginUser);
return;
}
///根据物料号②、供应商代码③、工厂代码④获取物料仓储信息，仅获取缺件检查标记为True的数据
#region 获取物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("[LACK_OF_INSPECTION_FLAG] = 1 ", string.Empty);
///没有物料仓储信息数据
if (partsStockInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "没有物料仓储信息数据", loginUser);
return;
}
#endregion
///根据物料仓储信息中需要做缺件检查的物料对物料条件重新定义
partNoCondition = "and [PART_NO] in ('" + string.Join("','", partsStockInfos.Select(d => d.PartNo).ToArray()) + "') ";
///
supplierNumConidtion = "and [SUPPLIER_NUM] in ('" + string.Join("','", partsStockInfos.Select(d => d.SupplierNum).ToArray()) + "') ";
///获取日期④对应的供货计划数据
List <SrmPrevSupplyPlanInfo> supplyPlanInfos = new List <SrmPrevSupplyPlanInfo>();
foreach (string dateColumn in dateColumns)
{
supplyPlanInfos.AddRange(new SrmPrevSupplyPlanBLL().GetListByDateColumn(dateColumn, partNoCondition + supplierNumConidtion + plantCondition));
}
///无供货计划
if (supplyPlanInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.CalculateFailed, lackOfMaterialInfo.Id, "无供货计划", loginUser);
return;
}
///分组
var supplyPlanInfosQuerys = from g in (from s in supplyPlanInfos
join p in partsStockInfos on new { s.PartNo, s.SupplierNum } equals new { p.PartNo, p.SupplierNum }
select s)
group g by new { g.PartNo, g.SupplierNum, g.Plant, g.PartPurchaser } into h
select new { h.Key.PartNo, h.Key.SupplierNum, h.Key.Plant, h.Key.PartPurchaser, RequireQty = h.Sum(x => x.RequireQty.GetValueOrDefault()) };
///获取库存数据
List <StocksInfo> stocksInfos = new StocksBLL().GetList(partNoCondition + supplierNumConidtion, string.Empty);
///获取物料号、供应商、工厂、仓库、存储区分组汇总的可用库存数量
var stocksInfosQuery = stocksInfos
.GroupBy(s => new { s.PartNo, s.SupplierNum, s.Plant })
.Select(p => new { p.Key.PartNo, p.Key.SupplierNum, p.Key.Plant, AvailbleStocks = p.Sum(x => x.AvailbleStocks.GetValueOrDefault()) }).ToList();
///以供货计划中开始日期至结束日期的需求数量合计 – 库存合计数量作为缺件数量⑤，当缺件数量 > 0 时，将缺件信息写入本次缺件表
///以上为计算逻辑内容，实际程序获取比对基础数据时需尽量减少与数据库的交互次数而改用LINQ方式进行计算，以提高程序计算效率
///一份缺件表计算完成后，将缺件明细一次性写入数据库同时更新缺件表状态⑦为30已处理，并记录计算结束时间⑨
foreach (var supplyPlanInfoQuery in supplyPlanInfosQuerys)
{
var stocksInfoQuery = stocksInfosQuery.FirstOrDefault(d =>
d.PartNo == supplyPlanInfoQuery.PartNo &&
d.Plant == supplyPlanInfoQuery.Plant &&
d.SupplierNum == supplyPlanInfoQuery.SupplierNum);
sqlText.AppendFormat("insert into [LES].[TT_ATP_LACK_OF_MATERIAL_DETAIL] (" +
"[FID],[LACK_ORDER_FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[LACK_QTY]," +
"[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PART_PURCHASER],[FEEDBACK_FLAG]) values (" +
"NEWID(),'{0}','{1}','{2}','{3}',{4}," +
"1,'{5}',GETDATE(),'{6}',{7});",
lackOfMaterialInfo.Fid, ///LACK_ORDER_FID,0
supplyPlanInfoQuery.PartNo, ///PART_NO,1
supplyPlanInfoQuery.SupplierNum, ///SUPPLIER_NUM,2
supplyPlanInfoQuery.Plant, ///PLANT,3
supplyPlanInfoQuery.RequireQty - (stocksInfoQuery == null ? 0 : stocksInfoQuery.AvailbleStocks), ///LACK_QTY,4
loginUser, ///CREATE_USER,5
supplyPlanInfoQuery.PartPurchaser, ///PART_PURCHASER,6
0 ///FEEDBACK_FLAG,7,反馈标记为了页面搜索方便把默认值填写为0
);
}
///更新缺件表状态
sqlText.AppendFormat("update [LES].[TT_ATP_LACK_OF_MATERIAL] set " +
"[STATUS] = {2}," +
"[EXECUTE_END_TIME] = GETDATE()," +
"[MODIFY_USER] = N'{0}'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = {1};",
loginUser,
lackOfMaterialInfo.Id,
(int)LackOfMaterialStatusConstants.Completed
);
#region 执行
using (TransactionScope trans = new TransactionScope())
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(sqlText.ToString());
trans.Complete();
}
#endregion
}
