/// <summary>
///
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(KanbanPartBoxInfo info)
{
///零件类代码①与零件类名称②各自校验在对应表字段内容不能重复
int cnt = dal.GetCounts(string.Format("[PART_BOX_CODE] = N'{0}'", info.PartBoxCode));
if (cnt > 0)
{
throw new Exception("Err_:MC:0x00000302");
}
cnt = dal.GetCounts(string.Format("[PART_BOX_NAME] = N'{0}'", info.PartBoxName));
if (cnt > 0)
{
throw new Exception("Err_:MC:0x00000303");
}
///来源存储区⑤与目标存储区⑦不能为同一存储区
if (info.SourceZoneNo == info.TargetZoneNo)
{
throw new Exception("Err_:MC:0x00000304");
}
///保存时更新状态为已创建
info.Status = (int)BasicDataStatusConstants.Created;
return(dal.Add(info));
}
/// <summary>
/// 看板生成拉动单
/// </summary>
/// <param name="cardNos"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool CreateKanbanPullOrder(List <String> cardNos, string loginUser, bool isFilterLoginUser = false)
{
if (cardNos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
#region 初始化SQL脚本
///主表字符串
string sqlKanbanPullOrder = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER] (
FID,
ORDER_CODE,
PART_BOX_CODE,
PART_BOX_NAME,
ROUTE_CODE,
ROUTE_NAME,
SOURCE_ZONE_NO,
SOURCE_WM_NO,
TARGET_ZONE_NO,
TARGET_WM_NO,
PICK_UP_FINISH_TIME,
PICK_UP_USER,
DELIVERY_FINISH_TIME,
DELIVERY_USER,
PRINT_CNT,
PRINT_TIME,
PRINT_USER,
STATUS,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
///明细表字符串
string sqlKanbanPullOrderDetail = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER_DETAIL] (
FID,
ORDER_FID,
ORDER_CODE,
ORDER_STATUS,
CARD_NO,
PART_NO,
PART_NAME,
SUPPLIER_CODE,
SUPPLIER_NAME,
PART_QTY,
PACKAGE_CODE,
PACKAGE_QTY,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
#endregion
///获取所有看板卡信息
List <KanbanCardInfo> kanbanCardInfos = GetKanBanCardsListIsByLoginUser(cardNos, loginUser, isFilterLoginUser);
if (kanbanCardInfos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
if (cardNos.Count != kanbanCardInfos.Count)
{
throw new Exception("MC:0x00000257");///所提交的看板卡状态必须为已启用且不能为已扫描
}
///根据看板卡信息中的零件类代码②对物料需求进行分组
var groupbyPartboxcode = kanbanCardInfos.GroupBy(ml => new { ml.PartBoxCode }).Select(w => w.Key).ToList();
#region 根据零件类获取看板拉动物料拉动对应拉动零件
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos
= new MaintainInhouseLogisticStandardBLL().GetList(
"[INHOUSE_PART_CLASS] in ('"
+ string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))
+ "') and [STATUS] = " + (int)BasicDataStatusConstants.Enable
+ " AND [INHOUSE_SYSTEM_MODE]=N'"
+ (int)PullModeConstants.Kanban
+ "'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
throw new Exception("0x00000233");///没有已启用的物料拉动信息
}
#endregion
#region 获取所有看板零件类以分组方式去重
List <KanbanPartBoxInfo> kanbanPartBoxInfos = new KanbanPartBoxDAL().GetList(
string.Format("[STATUS] = "
+ (int)BasicDataStatusConstants.Enable
+ " and [PART_BOX_CODE] in ('{0}') ", string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))), string.Empty);
if (kanbanPartBoxInfos.Count == 0)
{
throw new Exception("MC:3x00000014");///看板零件类数据错误
}
#endregion
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("update [LES].[TM_MPM_KANBAN_CARD] " +
"set [USED_STATUS] = " + (int)KanbanCardUseStatusConstants.Scaned + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] in (" + string.Join(",", kanbanCardInfos.Select(d => d.Id).ToArray()) + ");");
#region 遍历看板卡信息
foreach (var partboxcode in groupbyPartboxcode)
{
///获取看板零件类信息
KanbanPartBoxInfo kanbanPartBoxInfo = kanbanPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == partboxcode.PartBoxCode);
///TT_MPM_KANBAN_PULL_ORDER.FID
Guid pullOrderFid = Guid.NewGuid();
///看板拉动单号②依据序列号规则中的KANBAN_PULL_ORDER_CODE进行生成，明细中拉动单号③相同于主表
string orderCode = new SeqDefineDAL().GetCurrentCode("KANBAN_PULL_ORDER_CODE", "KB");///TODO:看板拉动单号生成
///看板拉动单外键①为NEW.GUID，同时作为看板拉动单明细的主表外键②，明细中外键①为NEW.GUID
///物流路线代码⑤名称⑥、来源仓库⑧存储区⑦、目标仓库⑩存储区⑨从零件类代码③对应的看板零件类TM_MPM_KANBAN_PART_BOX信息中获取
///创建时间获取数据库服务器时间，创建人取自于参数操作用户
///创建后看板拉动单状态⑱为20.已发布，数据有效标记⑲为true，看板拉动单其它字段在生成时留空
#region TT_MPM_KANBAN_PULL_ORDER
string sqlKanbanPullOrderValue = "N'" + pullOrderFid.ToString() + "'" ///FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxCode + "'" ///PART_BOX_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxName + "'" ///PART_BOX_NAME
+ ",N'" + kanbanPartBoxInfo.RouteCode + "'" ///ROUTE_CODE
+ ",N'" + kanbanPartBoxInfo.RouteName + "'" ///ROUTE_NAME
+ ",N'" + kanbanPartBoxInfo.SourceZoneNo + "'" ///SOURCE_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.SourceWmNo + "'" ///SOURCE_WM_NO
+ ",N'" + kanbanPartBoxInfo.TargetZoneNo + "'" ///TARGET_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.TargetWmNo + "'" ///TARGET_WM_NO
+ ",NULL" ///PICK_UP_FINISH_TIME
+ ",NULL" ///PICK_UP_USER
+ ",NULL" ///DELIVERY_FINISH_TIME
+ ",NULL" ///DELIVERY_USER
+ ",NULL" ///PRINT_CNT
+ ",NULL" ///PRINT_TIME
+ ",NULL" ///PRINT_USER
+ "," + (int)PullOrderStatusConstants.Released ///STATUS
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrder, sqlKanbanPullOrderValue));
List <KanbanCardInfo> kanbanCards = kanbanCardInfos.Where(d => d.PartBoxCode == partboxcode.PartBoxCode).ToList();
#region 根据看板卡信息获取所有零件物料拉动信息
var maintainInhouseLogisticStandardInfosparts = from a in maintainInhouseLogisticStandardInfos
join b in kanbanCards.Where(w => w.PartBoxCode == partboxcode.PartBoxCode).GroupBy(w => new { w.PartBoxCode, w.PartNo }).Select(w => new { w.Key.PartBoxCode, w.Key.PartNo })
on new { a.InhousePartClass, a.PartNo } equals new { InhousePartClass = b.PartBoxCode, b.PartNo }
select a;
#endregion
foreach (var kanbanCard in kanbanCards)
{
///看板卡号①=④、物料号④=⑤、物料名称⑤=⑥、物料数量⑧=⑨、包装型号⑨=⑩
///包装数量⑪默认为1，数据有效标记⑫为true
///若看板卡信息中指定了供应商代码⑥名称⑦，则看板拉动单明细中供应商代码⑦名称⑧从看板卡信息中获取
#region TT_MPM_KANBAN_PULL_ORDER_DETAIL
string sqlKanbanPullOrderDetailValue = "NEWID()" ///FID
+ ",N'" + pullOrderFid.ToString() + "'" ///ORDER_FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ "," + (int)PullOrderStatusConstants.Released ///ORDER_STATUS
+ ",N'" + kanbanCard.CardNo + "'" ///CARD_NO
+ ",N'" + kanbanCard.PartNo + "'" ///PART_NO
+ ",N'" + kanbanCard.PartName + "'" ///PART_NAME
+ ",N'" + kanbanCard.SupplierCode + "'" ///SUPPLIER_CODE
+ ",N'" + kanbanCard.SupplierName + "'" ///SUPPLIER_NAME
+ "," + kanbanCard.PartQty.GetValueOrDefault() ///PART_QTY
+ ",N'" + kanbanCard.PackageCode + "'" ///PACKAGE_CODE
+ ",1" ///PACKAGE_QTY
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrderDetail, sqlKanbanPullOrderDetailValue));
}
#region 单据衔接
MaterialPullingOrderInfo mpOrder = new MaterialPullingOrderInfo();
mpOrder.OrderNo = orderCode;
mpOrder.PartBoxCode = kanbanPartBoxInfo.PartBoxCode; ///零件类2
mpOrder.PartBoxName = kanbanPartBoxInfo.PartBoxName; ///零件类名称3
mpOrder.Plant = string.Empty; ///工厂4
mpOrder.Workshop = string.Empty; ///车间5
mpOrder.AssemblyLine = string.Empty; ///流水线6
mpOrder.SupplierNum = string.Empty; ///供应商代码7
mpOrder.SupplierName = string.Empty; ///供应商名称
mpOrder.SourceZoneNo = kanbanPartBoxInfo.SourceZoneNo; ///来源存储区8
mpOrder.SourceWmNo = kanbanPartBoxInfo.SourceWmNo; ///来源仓库9
mpOrder.TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo; ///目标存储区10
mpOrder.TargetWmNo = kanbanPartBoxInfo.TargetWmNo; ///目标仓库11
mpOrder.TargetDock = string.Empty; ///道口12
mpOrder.PlanShippingTime = DateTime.Now.AddMinutes(kanbanPartBoxInfo.PickUpTime.GetValueOrDefault()); ///建议交货时间
mpOrder.PlanDeliveryTime = mpOrder.PlanShippingTime.GetValueOrDefault().AddMinutes(kanbanPartBoxInfo.DeliveryTime.GetValueOrDefault()); ///预计到厂时间14 = 建议交货时间 – 送货时间
mpOrder.PublishTime = DateTime.Now;
mpOrder.AsnFlag = false;
mpOrder.OrderType = 0;///TODO:对于看板拉动单应该的值，如果后续用到则需要定义枚举
mpOrder.MaterialPullingOrderDetailInfos = (from m in maintainInhouseLogisticStandardInfosparts
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
//Uom = m.PartUnits,///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = kanbanCards.Where(w => w.PartNo == m.PartNo).Count(), ///需求包装数量9
RequirePartQty = (kanbanCards.Where(w => w.PartNo == m.PartNo).Sum(w => w.PartQty) ?? 0), ///需求物料数量10
TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo,
TargetWmNo = kanbanPartBoxInfo.TargetWmNo
}).ToList();
///执行单据衔接
stringBuilder.AppendLine(MaterialPullingCommonBLL.Handler(mpOrder, loginUser));
#endregion
}
#endregion
///数据保存时使用SQL拼接多条insert语句方式一次提交执行，执行失败需要同步返回至客户端
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
trans.Complete();
}
return(true);
}
/// <summary>
/// 批量创建看板卡
/// </summary>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool SynchronizationKanBanCardInfos(string loginUser)
{
#region 准备数据源
StringBuilder sqlBuilder = new StringBuilder();
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("[STATUS] = " + (int)BasicDataStatusConstants.Enable + "and [INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Kanban + "'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count() == 0)
{
throw new Exception("MC:0x00000256");///不存在可同步的物料看板拉动信息
}
///已生成的看板卡，除了作废的卡片之外
List <KanbanCardInfo> kanbanCardInfos = dal.GetList("[STATUS] <> " + (int)BasicDataStatusConstants.Disabled, string.Empty);
///已生成的看板卡统计信息
var groupKanbanCardInfos = kanbanCardInfos.GroupBy(w => new { w.PartNo, w.PartBoxCode }).Select(w => new { w.Key.PartBoxCode, w.Key.PartNo, SumCount = w.Count() }).ToList();
///看板零件类
List <KanbanPartBoxInfo> kanbanPartBoxInfos = new KanbanPartBoxDAL().GetList(string.Format("[PART_BOX_CODE] in ('{0}') and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "",
string.Join("','", maintainInhouseLogisticStandardInfos.GroupBy(w => new { w.InhousePartClass }).Select(w => w.Key.InhousePartClass).ToArray())), string.Empty);
///供应商信息
List <SupplierInfo> supplierinfos = new SupplierDAL().GetList(string.Format("[SUPPLIER_NUM] IN ('{0}')", maintainInhouseLogisticStandardInfos.GroupBy(w => new { w.SupplierNum }).Select(w => w.Key.SupplierNum).ToArray()), string.Empty);
#endregion
#region SQL脚本
string sqlstr = @"insert into [LES].[TM_MPM_KANBAN_CARD] (
FID,
CARD_NO,
PART_BOX_CODE,
PART_BOX_NAME,
PART_NO,
PART_NAME,
SUPPLIER_CODE,
SUPPLIER_NAME,
PART_QTY,
PACKAGE_CODE,
STATUS,
PRINT_CNT,
PRINT_TIME,
PRINT_USER,
USED_STATUS,
SCANNED_USER,
SCANNED_DATE,
VALID_FLAG,
CREATE_DATE,
CREATE_USER,
MODIFY_DATE,
MODIFY_USER
) values ({0});" ;
#endregion
#region 编列数据源生成脚本
foreach (var maintainInhouseLogisticStandardInfo in maintainInhouseLogisticStandardInfos)
{
///计算出还可创建多少张看板卡
int createCardCount = 0;
var groupKanbanCardInfo = groupKanbanCardInfos.FirstOrDefault(w =>
w.PartNo == maintainInhouseLogisticStandardInfo.PartNo &&
w.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
if (groupKanbanCardInfo == null)
{
createCardCount = maintainInhouseLogisticStandardInfo.KanbanCircleCnt.GetValueOrDefault();
}
else
{
createCardCount = maintainInhouseLogisticStandardInfo.KanbanCircleCnt.GetValueOrDefault() - groupKanbanCardInfo.SumCount;
}
///如果没有可创建的卡片则继续下一个
if (createCardCount == 0)
{
continue;
}
///获取看板零件类信息，且零件类必须处于已启用状态
KanbanPartBoxInfo kanbanPartBoxInfo = kanbanPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
if (kanbanPartBoxInfo == null)
{
continue;
}
SupplierInfo supplierInfo = supplierinfos.FirstOrDefault(d => d.SupplierNum == maintainInhouseLogisticStandardInfo.SupplierNum);
for (int i = 0; i < createCardCount; i++)
{
string sqlKanbanPullOrderDetailValue = "NEWID()"///FID
+ ",N'" + new SeqDefineDAL().GetCurrentCode("KANBAN_CARD_NO", null) + "'"
+ ",N'HOSPITAL'"
//+ ",N'" + kanbanPartBoxInfo.PartBoxCode + "'"
+ ",N'" + kanbanPartBoxInfo.PartBoxName + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.PartNo + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.PartCname + "'"
+ ",N'" + maintainInhouseLogisticStandardInfo.SupplierNum + "'"
+ ",N'" + (supplierInfo == null ? string.Empty : supplierInfo.SupplierName) + "'"///供应商名称
+ "," + maintainInhouseLogisticStandardInfo.InboundPackage.GetValueOrDefault()
+ ",N'" + maintainInhouseLogisticStandardInfo.InboundPackageModel + "'"
+ "," + (int)BasicDataStatusConstants.Created
+ ",NULL"
+ ",NULL"
+ ",NULL"
+ ",10"
+ ",NULL"
+ ",NULL"
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'" ///CREATE_USER
+ ",NULL"
+ ",NULL";
sqlBuilder.AppendLine(string.Format(sqlstr, sqlKanbanPullOrderDetailValue));
}
}
#endregion
if (sqlBuilder.Length == 0)
{
throw new Exception("MC:0x00000249");///不存在需要生成的看板卡
}
///批量创建看板卡时是否同时启用
string batchCreateKanbanCardEnableAtSametime = new ConfigDAL().GetValueByCode("BATCH_CREATE_KANBAN_CARD_ENABLE_AT_SAMETIME");
if (batchCreateKanbanCardEnableAtSametime.ToLower() == "true")
{
sqlBuilder.AppendLine("update [LES].[TM_MPM_KANBAN_CARD] "
+ "set [STATUS] = " + (int)BasicDataStatusConstants.Enable + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' "
+ "where [STATUS] = " + (int)BasicDataStatusConstants.Created + ";");
}
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(sqlBuilder.ToString());
trans.Complete();
}
return(true);
}
