/// <summary>
/// 中间集合添加到数据库
/// </summary>
/// <param name="sapProductOrderInfos"></param>
/// <param name="logFid"></param>
/// <returns></returns>
public int InsertListToCentreTable(List <BFDASapProductOrderInfo> sapProductOrderInfos, Guid logFid, string logSql)
{
List <SapProductOrderInfo> productOrderInfos = ConversionToCentreList(sapProductOrderInfos);
StringBuilder @string = new StringBuilder(logSql);
foreach (var productOrderInfo in productOrderInfos)
{
///FID
productOrderInfo.Fid = Guid.NewGuid();
///LOG_FID
productOrderInfo.LogFid = logFid;
///PROCESS_FLAG
productOrderInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
///CREATE_USER
productOrderInfo.CreateUser = loginUser;
///
@string.AppendLine(SapProductOrderDAL.GetInsertSql(productOrderInfo));
}
///分布式事务 执行写入数据库操作.
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
return(productOrderInfos.Count);
}
/// <summary>
/// 更改单
/// </summary>
/// <param name="pullOrdersInfo"></param>
public string TransitionBreakpoint(PullOrdersInfo pullOrdersInfo, string loginUser)
{
if (pullOrdersInfo.ChangeFlag == (int)ChangeFlagConstants.Replaced)
{
return(string.Empty);
}
///根据生产订单号获取其物料清单，作为后续匹配更改单的源数据
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomDAL().GetList("and [ORDERFID]='" + pullOrdersInfo.Fid + "'", string.Empty);
if (pullOrderBomInfos.Count == 0)
{
return(string.Empty);
}
///根据生产订单对应的工厂、生产版本（生产线）获取状态为20.已发布且更改类型为20.过渡断点的更改单
///TT_BPM_BOM_CHANGE_ORDER 还需要校验过渡开始时间小于等于当前时间
List <BomChangeOrderInfo> bomChangeOrderInfos = new BomChangeOrderDAL().GetList("" +
" and [PLANT]=N'" + pullOrdersInfo.Werk + "'" +
" and [ASSEMBLY_LINE]=N'" + pullOrdersInfo.AssemblyLine + "' " +
" and [STATUS]=" + (int)BasicDataStatusConstants.Enable + "" +
" and [ORDER_TYPE]=" + (int)BreakPointOrderTypeConstants.TransitionBreakpoint + "" +
" and [TRANSITION_START_TIME] <=GETDATE()", string.Empty);
if (bomChangeOrderInfos.Count == 0)
{
return(string.Empty);
}
///过滤其中旧物料库存数量大于等于旧物料已消耗数量的物料数据
List <BomChangeOrderDetailInfo> bomChangeOrderDetailInfos = new BomChangeOrderDetailDAL().GetList("" +
"and [ORDER_FID] in ('" + string.Join("','", bomChangeOrderInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty);
if (bomChangeOrderDetailInfos.Count == 0)
{
return(string.Empty);
}
bomChangeOrderDetailInfos = bomChangeOrderDetailInfos.Where(d => d.OldPartStockQty >= d.OldPartConsumedQty).ToList();
if (bomChangeOrderDetailInfos.Count == 0)
{
return(string.Empty);
}
///将物料清单与过渡更改单物料根据新物料号、工位过滤数据
var replacedParts = (from p in pullOrderBomInfos
join b in bomChangeOrderDetailInfos
on new { PartNo = p.Zcomno, Location = p.Zloc } equals new { PartNo = b.NewPartNo, b.Location }
select new
{
p.Id,
p.SupplierNum,
b.NewPartNo,
b.OldPartNo,
b.NewPartQty,
b.Location,
OldPartQty = 1 //b.OldPartQty TODO:旧物料
}).ToList();
///在更新生产订单物料清单信息（新换旧）
if (replacedParts.Count == 0)
{
return(string.Empty);
}
StringBuilder @string = new StringBuilder();
foreach (var parts in replacedParts)
{
@string.AppendFormat("update [LES].[TT_BAS_PULL_ORDER_BOM] set " +
"[ZCOMNO]=N'" + parts.OldPartNo + "'," +
"[ZQTY]=" + parts.OldPartQty + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "'" +
"where [ID]=" + parts.Id + "");
///同时生成物料替换记录
PorderBomRepleaceRecordInfo porderBomRepleaceRecordInfo = new PorderBomRepleaceRecordInfo();
///FID
porderBomRepleaceRecordInfo.Fid = Guid.NewGuid();
///VALID_FLAG
porderBomRepleaceRecordInfo.ValidFlag = true;
///CREATE_DATE
porderBomRepleaceRecordInfo.CreateDate = DateTime.Now;
///CREATE_USER
porderBomRepleaceRecordInfo.CreateUser = loginUser;
///生产订单号
porderBomRepleaceRecordInfo.OrderNo = pullOrdersInfo.OrderNo;
///旧物料号
porderBomRepleaceRecordInfo.OldPartNo = parts.OldPartNo;
///新物料号
porderBomRepleaceRecordInfo.NewPartNo = parts.NewPartNo;
///旧供应商
porderBomRepleaceRecordInfo.OldSupplierNum = parts.SupplierNum;
///新供应商
porderBomRepleaceRecordInfo.NewSupplierNum = parts.SupplierNum;
///旧工位
porderBomRepleaceRecordInfo.OldLocation = parts.Location;
///新工位
porderBomRepleaceRecordInfo.NewLocation = parts.Location;
///旧物料用量
porderBomRepleaceRecordInfo.OldPartQty = parts.OldPartQty;
///新物料用量
porderBomRepleaceRecordInfo.NewPartQty = parts.NewPartQty;
///替换时间
porderBomRepleaceRecordInfo.RepleaceTime = DateTime.Now;
///状态 TODO:暂时没有想好干嘛的
porderBomRepleaceRecordInfo.Status = 10;
///同时生成物料替换记录
@string.AppendFormat(PorderBomRepleaceRecordDAL.GetInsertSql(porderBomRepleaceRecordInfo));
}
if (@string.Length > 0)
{
///在本更改单所有物料替换完成时还需生成一条状态为逆处理的生产订单中间表记录
SapProductOrderInfo sapProductOrderInfo = SapProductOrderDAL.CreateSapProductOrderInfo(loginUser);
///订单号
sapProductOrderInfo.Aufnr = pullOrdersInfo.OrderNo;
///处理状态
sapProductOrderInfo.ProcessFlag = (int)ProcessFlagConstants.ConverseProgress;
///版本号
sapProductOrderInfo.Verid = pullOrdersInfo.Version.ToString();
@string.AppendFormat(SapProductOrderDAL.GetInsertSql(sapProductOrderInfo));
}
return(@string.ToString());
}
/// <summary>
/// 替换条件
/// </summary>
/// <param name="pullOrdersInfo"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public string ReplacementCriteria(PullOrdersInfo pullOrdersInfo, string loginUser)
{
///车辆过点时根据替换条件更新物料清单中的物料号、供应商、工位、用量信息，同时产生替换记录
if (pullOrdersInfo.ChangeFlag == (int)ChangeFlagConstants.Replaced)
{
return(string.Empty);
}
///根据生产订单号获取其物料清单，作为后续匹配更改单的源数据
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomDAL().GetList("" +
"and [ORDERFID]='" + pullOrdersInfo.Fid + "'", string.Empty);
if (pullOrderBomInfos.Count == 0)
{
return(string.Empty);
}
///TT_BPM_BOM_REPLEACE_CONDITION 替换条件信息
List <BomRepleaceConditionInfo> bomRepleaceConditionInfos = new BomRepleaceConditionDAL().GetList("" +
" and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "" +
" and [OLD_PART_NO] in ('" + string.Join("','", pullOrderBomInfos.Select(d => d.Zcomno).ToArray()) + "')" +
" and GETDATE() between [EXECUTE_START_TIME] and [EXECUTE_END_TIME]", string.Empty);
if (bomRepleaceConditionInfos.Count == 0)
{
return(string.Empty);
}
///车型信息 TT_BPM_BOM_REPLEACE_CONDITION_VEHICLE
List <BomRepleaceConditionVehicleInfo> bomRepleaceConditionVehicleInfos = new BomRepleaceConditionVehicleBLL().GetList("" +
" and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "" +
" and [CONDITION_FID] in ('" + string.Join("','", bomRepleaceConditionInfos.Select(d => d.Fid).ToArray()) + "')" +
" and [PART_NO]=N'" + pullOrdersInfo.PartNo + "'" +
" and [MODEL_YEAR]=N'" + pullOrdersInfo.ModelYear + "'" +
" and [FARBAU]=N'" + pullOrdersInfo.Farbau + "'" +
" and [PNR_STRING]=N'" + pullOrdersInfo.PnrString + "'" +
" and [ZCOLORI]=N'" + pullOrdersInfo.Zcolori + "'", string.Empty);
StringBuilder @string = new StringBuilder();
foreach (BomRepleaceConditionInfo bomRepleaceConditionInfo in bomRepleaceConditionInfos)
{
///物料号
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d => d.Zcomno == bomRepleaceConditionInfo.OldPartNo).ToList();
if (pullOrderBoms.Count == 0)
{
continue;
}
///工位
if (!string.IsNullOrEmpty(bomRepleaceConditionInfo.OldLocation))
{
pullOrderBoms = pullOrderBoms.Where(d => d.Zloc == bomRepleaceConditionInfo.OldLocation).ToList();
}
///供应商
if (!string.IsNullOrEmpty(bomRepleaceConditionInfo.OldSupplierNum))
{
pullOrderBoms = pullOrderBoms.Where(d => d.SupplierNum == bomRepleaceConditionInfo.OldSupplierNum).ToList();
}
if (pullOrderBoms.Count == 0)
{
continue;
}
///车型
BomRepleaceConditionVehicleInfo bomRepleaceConditionVehicleInfo = bomRepleaceConditionVehicleInfos.FirstOrDefault(d => d.ConditionFid == bomRepleaceConditionInfo.Fid);
foreach (PullOrderBomInfo pullOrderBom in pullOrderBoms)
{
///更新物料清单
@string.AppendFormat("update [LES].[TT_BAS_PULL_ORDER_BOM] set " +
"[ZCOMNO]=N'" + bomRepleaceConditionInfo.NewPartNo + "',");
if (bomRepleaceConditionInfo.OldPartQty.GetValueOrDefault() != 0)
{
@string.AppendFormat("[ZQTY]=" + bomRepleaceConditionInfo.NewPartQty.GetValueOrDefault() + ",");
}
if (!string.IsNullOrEmpty(bomRepleaceConditionInfo.OldLocation))
{
@string.AppendFormat("[ZLOC]=N'" + bomRepleaceConditionInfo.NewLocation + "',");
}
if (!string.IsNullOrEmpty(bomRepleaceConditionInfo.OldSupplierNum))
{
@string.AppendFormat("[SUPPLIER_NUM]=N'" + bomRepleaceConditionInfo.NewSupplierNum + "',");
}
@string.AppendFormat("[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "'" +
"where [ID]=" + pullOrderBom.Id + "");
///旧物料多的情况
if (pullOrderBom.Zqty > bomRepleaceConditionInfo.NewPartQty)
{
PullOrderBomInfo info = new PullOrderBomInfo();
///FID,
info.Fid = Guid.NewGuid();
///ORDERFID,订单外键
info.Orderfid = pullOrdersInfo.Fid;
///ZORDNO,订单号
info.Zordno = pullOrdersInfo.OrderNo;
///ZKWERK,工厂
info.Zkwerk = pullOrderBom.Zkwerk;
///ZBOMID,MBOM项目号
info.Zbomid = pullOrderBom.Zbomid;
///ZCOMNO,零件号
info.Zcomno = bomRepleaceConditionInfo.NewPartNo;
///ZCOMDS,零件描述
info.Zcomds = pullOrderBom.Zcomds;
///ZVIN,ZVIN
info.Zvin = pullOrderBom.Zvin;
///ZQTY,数量
info.Zqty = pullOrderBom.Zqty.GetValueOrDefault() - Convert.ToInt32(bomRepleaceConditionInfo.NewPartQty.GetValueOrDefault());
///ZDATE,计划下线日期
info.Zdate = pullOrderBom.Zdate;
///ZLOC,工位
info.Zloc = bomRepleaceConditionInfo.OldLocation == null ? pullOrderBom.Zloc : bomRepleaceConditionInfo.NewLocation;
///ZST,操作状态
info.Zst = pullOrderBom.Zst;
///ZMEMO,备注
info.Zmemo = pullOrderBom.Zmemo;
///ZMEINS,单位
info.Zmeins = pullOrderBom.Zmeins;
///SUPPLIER_NUM,供应商
info.SupplierNum = bomRepleaceConditionInfo.OldSupplierNum == null ? pullOrderBom.SupplierNum : bomRepleaceConditionInfo.NewSupplierNum;
///PLATFORM,平台
info.Platform = pullOrderBom.Platform;
///VALID_FLAG,
info.ValidFlag = true;
///CREATE_USER,COMMON_CREATE_USER
info.CreateUser = loginUser;
///CREATE_DATE,COMMON_CREATE_DATE
info.CreateDate = DateTime.Now;
///MODIFY_USER,COMMON_UPDATE_USER
info.ModifyUser = null;
///MODIFY_DATE,COMMON_UPDATE_DATE
info.ModifyDate = null;
@string.AppendFormat(PullOrderBomDAL.GetInsertSql(info));
}
///同时生成物料替换记录
PorderBomRepleaceRecordInfo porderBomRepleaceRecordInfo = new PorderBomRepleaceRecordInfo();
///FID
porderBomRepleaceRecordInfo.Fid = Guid.NewGuid();
///VALID_FLAG
porderBomRepleaceRecordInfo.ValidFlag = true;
///CREATE_DATE
porderBomRepleaceRecordInfo.CreateDate = DateTime.Now;
///CREATE_USER
porderBomRepleaceRecordInfo.CreateUser = loginUser;
///生产订单号
porderBomRepleaceRecordInfo.OrderNo = pullOrdersInfo.OrderNo;
///旧物料号
porderBomRepleaceRecordInfo.OldPartNo = bomRepleaceConditionInfo.OldPartNo;
///新物料号
porderBomRepleaceRecordInfo.NewPartNo = bomRepleaceConditionInfo.NewPartNo;
///旧供应商
porderBomRepleaceRecordInfo.OldSupplierNum = bomRepleaceConditionInfo.OldSupplierNum;
///新供应商
porderBomRepleaceRecordInfo.NewSupplierNum = bomRepleaceConditionInfo.NewSupplierNum;
///旧工位
porderBomRepleaceRecordInfo.OldLocation = bomRepleaceConditionInfo.OldLocation;
///新工位
porderBomRepleaceRecordInfo.NewLocation = bomRepleaceConditionInfo.NewLocation;
///旧物料版本
porderBomRepleaceRecordInfo.OldPartVersion = bomRepleaceConditionInfo.OldPartVersion;
///新物料版本
porderBomRepleaceRecordInfo.NewPartVersion = bomRepleaceConditionInfo.NewPartVersion;
///旧物料用量
porderBomRepleaceRecordInfo.OldPartQty = bomRepleaceConditionInfo.OldPartQty;
///新物料用量
porderBomRepleaceRecordInfo.NewPartQty = bomRepleaceConditionInfo.NewPartQty;
///替换时间
porderBomRepleaceRecordInfo.RepleaceTime = DateTime.Now;
///状态 TODO:暂时没有想好干嘛的
porderBomRepleaceRecordInfo.Status = 10;
///同时生成物料替换记录
@string.AppendFormat(PorderBomRepleaceRecordDAL.GetInsertSql(porderBomRepleaceRecordInfo));
}
//车型不为空
if (bomRepleaceConditionVehicleInfo != null && @string.Length > 0)
{
@string.AppendFormat("update [LES].[TT_BPM_BOM_REPLEACE_CONDITION_VEHICLE] set " +
"[REPLEACED_VEHICLE_QTY]=isnull([REPLEACED_VEHICLE_QTY],0)+" + pullOrderBoms.Count + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "'" +
"where [ID]=" + bomRepleaceConditionVehicleInfo.Id + "");
}
}
if (@string.Length > 0 && bomRepleaceConditionInfos.Where(d => d.NewPartNo != d.OldPartNo || d.OldSupplierNum != d.NewSupplierNum || d.NewPartQty != d.OldPartQty).ToList().Count > 0)
{
///在本更改单所有物料替换完成时还需生成一条状态为逆处理的生产订单中间表记录
SapProductOrderInfo sapProductOrderInfo = SapProductOrderDAL.CreateSapProductOrderInfo(loginUser);
///订单号
sapProductOrderInfo.Aufnr = pullOrdersInfo.OrderNo;
///处理状态
sapProductOrderInfo.ProcessFlag = (int)ProcessFlagConstants.ConverseProgress;
///版本号
sapProductOrderInfo.Verid = pullOrdersInfo.Version.ToString();
@string.AppendFormat(SapProductOrderDAL.GetInsertSql(sapProductOrderInfo));
}
return(@string.ToString());
}