/// <summary>
/// JisPartBoxInfo -> JisCounterInfo
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
public static void GetJisCounterInfo(JisPartBoxInfo jisPartBoxInfo, ref JisCounterInfo jisCounterInfo)
{
if (jisPartBoxInfo == null)
{
return;
}
///PART_BOX_FID
jisCounterInfo.PartBoxFid = jisPartBoxInfo.Fid;
///PART_BOX_CODE
jisCounterInfo.PartBoxCode = jisPartBoxInfo.PartBoxCode;
///PLANT
jisCounterInfo.Plant = jisPartBoxInfo.Plant;
///PLANT_ZONE
jisCounterInfo.PlantZone = jisPartBoxInfo.PlantZone;
///WORKSHOP
jisCounterInfo.Workshop = jisPartBoxInfo.Workshop;
///ASSEMBLY_LINE
jisCounterInfo.AssemblyLine = jisPartBoxInfo.AssemblyLine;
///WORKSHOP_SECTION
jisCounterInfo.WorkshopSection = jisPartBoxInfo.WorkshopSection;
///LOCATION
jisCounterInfo.Location = jisPartBoxInfo.Location;
///SUPPLIER_NUM
jisCounterInfo.SupplierNum = jisPartBoxInfo.SupplierNum;
///ACCUMULATIVE_TYPE
jisCounterInfo.AccumulativeType = jisPartBoxInfo.AccumulativeType;
///ACCUMULATIVE_QTY
jisCounterInfo.AccumulativeQty = jisPartBoxInfo.AccumulativeQty;
///PACKAGE_MODEL
jisCounterInfo.PackageModel = jisPartBoxInfo.PackageModel;
///COMMENTS
jisCounterInfo.Comments = jisPartBoxInfo.Comments;
}
/// <summary>
/// LogicDeleteInfo
/// </summary>
/// <param name="id">主键</param>
/// <param name="loginUser">用户</param>
/// <returns></returns>
public bool LogicDeleteInfo(long id, string loginUser)
{
JisPartBoxInfo info = dal.GetInfo(id);
if (info == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (info.Status != (int)BasicDataStatusConstants.Created)
{
throw new Exception("MC:0x00000415");///已创建状态才可进行删除
}
return(dal.LogicDelete(id, loginUser) > 0 ? true : false);
}
/// <summary>
/// JisPartBoxInfo - > jisCounterInfo
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
private JisCounterInfo CreateJisCounterInfo(JisPartBoxInfo jisPartBoxInfo)
{
///创建计数器
JisCounterInfo jisCounterInfo = JisCounterBLL.CreateJisCounterInfo(loginUser);
///以物料拉动信息填充计数器
JisCounterBLL.GetJisCounterInfo(jisPartBoxInfo, ref jisCounterInfo);
///
jisCounterInfo.Id = new JisCounterBLL().InsertInfo(jisCounterInfo);
if (jisCounterInfo.Id == 0)
{
throw new Exception("MC:0x00000454");///排序计数器创建失败
}
return(jisCounterInfo);
}
/// <summary>
///
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
/// <param name="vehiclePointStatusInfo"></param>
/// <param name="maintainInhouseLogisticStandardInfo"></param>
/// <param name="currentQty"></param>
/// <returns></returns>
private string UpdateJisCounter(JisPartBoxInfo jisPartBoxInfo, JisCounterInfo jisCounterInfo, VehiclePointStatusInfo vehiclePointStatusInfo, MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo, decimal currentQty)
{
///创建计数器日志
JisCounterLogInfo jisCounterLogInfo = JisCounterLogBLL.CreateJisCounterLogInfo(jisCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以车辆过点信息填充计数器日志
JisCounterLogBLL.GetJisCounterLogInfo(vehiclePointStatusInfo, ref jisCounterLogInfo);
///以零件类信息填充计数器日志
JisCounterLogBLL.GetJisCounterLogInfo(jisPartBoxInfo, ref jisCounterLogInfo);
///以物料拉动信息填充计数器日志
JisCounterLogBLL.GetJisCounterLogInfo(maintainInhouseLogisticStandardInfo, ref jisCounterLogInfo);
///PART_QTY
jisCounterLogInfo.PartQty = currentQty;
///
return(JisCounterLogDAL.GetInsertSql(jisCounterLogInfo));
}
/// <summary>
///
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
/// <param name="vehiclePointStatusInfo"></param>
/// <param name="maintainInhouseLogisticStandardInfo"></param>
/// <param name="currentQty"></param>
/// <param name="jisCounterStatus"></param>
/// <returns></returns>
private string UpdateJisCounter(JisPartBoxInfo jisPartBoxInfo, JisCounterInfo jisCounterInfo, decimal currentQty, int jisCounterStatus)
{
return("update [LES].[TT_MPM_JIS_COUNTER] set " +
"[PART_BOX_CODE] = N'" + jisPartBoxInfo.PartBoxCode + "'," +
"[PLANT] = N'" + jisPartBoxInfo.Plant + "'," +
"[PLANT_ZONE] = N'" + jisPartBoxInfo.PlantZone + "'," +
"[WORKSHOP] = N'" + jisPartBoxInfo.Workshop + "'," +
"[ASSEMBLY_LINE] = N'" + jisPartBoxInfo.AssemblyLine + "'," +
"[WORKSHOP_SECTION] = N'" + jisPartBoxInfo.WorkshopSection + "'," +
"[LOCATION] = N'" + jisPartBoxInfo.Location + "'," +
"[SUPPLIER_NUM] = N'" + jisPartBoxInfo.SupplierNum + "'," +
"[ACCUMULATIVE_TYPE] = " + jisPartBoxInfo.AccumulativeType.GetValueOrDefault() + "," +
"[ACCUMULATIVE_QTY] = " + jisPartBoxInfo.AccumulativeQty.GetValueOrDefault() + "," +
"[PACKAGE_MODEL] = N'" + jisPartBoxInfo.PackageModel + "'," +
"[CURRENT_QTY] = isnull([CURRENT_QTY],0) + " + currentQty + "," +
"[STATUS] = " + jisCounterStatus + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "' where " +
"[ID] = " + jisCounterInfo.Id + ";");
}
/// <summary>
/// UpdateInfo
/// </summary>
/// <param name="fields">更新字段</param>
/// <returns></returns>
public bool UpdateInfo(string fields, long id)
{
JisPartBoxInfo info = dal.GetInfo(id);
if (info == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (info.Status != (int)BasicDataStatusConstants.Created)
{
throw new Exception("MC:0x00000441");///已创建状态才可进行修改
}
string partBoxName = CommonBLL.GetFieldValue(fields, "PART_BOX_NAME");
int partBoxNameCnt = dal.GetCounts(" [PART_BOX_NAME] = N'" + partBoxName + "' and [ID] <> " + id + "");
if (partBoxNameCnt > 0)
{
throw new Exception("Err_:MC:0x00000303");///零件类名称重复
}
return(dal.UpdateInfo(fields, id) > 0 ? true : false);
}
/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info">对象</param>
/// <returns></returns>
public long InsertInfo(JisPartBoxInfo info)
{
return(dal.Add(info));
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <JisPartBoxInfo> jisPartBoxes = CommonDAL.DatatableConvertToList <JisPartBoxInfo>(dataTable).ToList();
if (jisPartBoxes.Count == 0 || jisPartBoxes == null)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <JisPartBoxInfo> jisParts = new JisPartBoxDAL().GetList(" [PART_BOX_CODE] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
List <JisPartBoxInfo> boxInfos = new JisPartBoxDAL().GetList(" [PART_BOX_NAME] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxName).ToList().ToArray()) + "')", "");
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var item in jisPartBoxes)
{
JisPartBoxInfo info = jisParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode);
if (info == null)
{
if (string.IsNullOrEmpty(item.PartBoxCode) ||
string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) where [PART_BOX_CODE] = N'" + item.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_JIS_PART_BOX] ("
+ "[FID],"
+ insertFieldString
+ "[STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID(),"///FID
+ insertValueString
+ (int)BasicDataStatusConstants.Created + ","
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
jisParts.Add(item);
continue;
}
if (info.Status == (int)BasicDataStatusConstants.Created)
{
if (string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName && d.Id != info.Id);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_MPM_JIS_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + info.Id + ";";
}
}
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// Jis拉动
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
/// <param name="jisCounterLogs"></param>
/// <param name="maintainInhouseLogistics"></param>
/// <param name="JisPullGuid"></param>
/// <param name="orderCode"></param>
/// <param name="pullOrdersInfos"></param>
/// <param name="isJis"></param>
/// <returns></returns>
public string JisPull(JisPartBoxInfo jisPartBoxInfo, JisCounterInfo jisCounterInfo, List <JisCounterLogInfo> jisCounterLogs, List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogistics, List <PullOrdersInfo> pullOrdersInfos, bool isJis)
{
StringBuilder stringBuilder = new StringBuilder();
///拉动单Guid
Guid JisPullGuid = Guid.NewGuid();
///拉动单号
string orderCode = new SeqDefineBLL().GetCurrentCode("JIS_PULL_ORDER_CODE");
///单据衔接
MaterialPullingOrderInfo materialPullingOrderInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///jisPartBoxInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetJisMaterialPullingOrderInfo(jisPartBoxInfo, ref materialPullingOrderInfo);
///orderCode -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetorderCodeMaterialPullingOrderInfo(orderCode, ref materialPullingOrderInfo);
foreach (JisCounterLogInfo jisCounterLog in jisCounterLogs)
{
///物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogistics.Where(d => d.PartNo == jisCounterLog.PartNo).FirstOrDefault();
if (maintainInhouseLogisticStandardInfo == null)
{
continue;
}
///生产订单信息
PullOrdersInfo pullOrdersInfo = pullOrdersInfos.FirstOrDefault(d => d.OrderNo == jisCounterLog.SourceData);
if (pullOrdersInfo == null)
{
continue;
}
///仓储衔接明细表
MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///maintainInhouseLogisticStandardInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMainMaterialPullingOrderDetailInfo(maintainInhouseLogisticStandardInfo, jisCounterLog.PartQty.GetValueOrDefault(), ref detailInfo);
///jisPartBoxInfo-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetJisMaterialPullingOrderDetailInfo(jisPartBoxInfo, ref detailInfo);
///orderCode-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetOrderCodeMaterialPullingOrderDetailInfo(orderCode, ref detailInfo);
///pullOrdersInfo-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetPullOrderMaterialPullingOrderDetailInfo(pullOrdersInfo, ref detailInfo);
///Add
materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Add(detailInfo);
if (isJis)
{
///排序拉动明细
stringBuilder.AppendFormat(CreateJisPullOrderDetailSql(JisPullGuid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
}
else
{
///物料成套拉动明细
stringBuilder.AppendFormat(CreateCompletePullOrderDetailSql(JisPullGuid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
}
if (stringBuilder.Length == 0)
{
continue;
}
}
if (isJis)
{
///开始车号
materialPullingOrderInfo.StartVehicheNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Min(d => d.DayVehicheSeqNo.GetValueOrDefault());
///结束车号
materialPullingOrderInfo.EndVehicheNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Max(d => d.DayVehicheSeqNo.GetValueOrDefault());
///排序拉动主表
stringBuilder.AppendFormat(CreateJisPullOrderSql(JisPullGuid, materialPullingOrderInfo));
}
else
{
///车辆编号
materialPullingOrderInfo.VehicheModelNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Select(d => d.VehicheModelNo).FirstOrDefault();
///生产订单号
materialPullingOrderInfo.OrderNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Select(d => d.OrderNo).FirstOrDefault();
///物料成套拉动主表
stringBuilder.AppendFormat(CreateCompletePullOrderSql(JisPullGuid, materialPullingOrderInfo));
}
///仓储衔接
stringBuilder.AppendFormat(MaterialPullingCommonBLL.Handler(materialPullingOrderInfo, loginUser));
return(stringBuilder.ToString());
}
