using Masuit.Tools;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.entity.Base;
using NewPdaSqlServer.util;
using SqlSugar;
using System.Security.Cryptography;
namespace NewPdaSqlServer.service.Warehouse;
///
/// MES物料表管理类
///
public class MesItemTblManager : Repository
{
///
/// 获取生产退料单号列表
///
/// 退料单号列表
public List GetSCTLBillNo(WarehouseQuery query)
{
var list = Db.Queryable()
.Where(s => (s.Tbl013 ?? 0) == 1 // 审核通过
&& (s.Tbl020 ?? 0) == 0
&& s.Tbl008 == query.Type)
.Select(s => s.BillNo).ToList();
return list;
}
///
/// 根据单号获取MES物料表明细
///
/// 仓库查询参数
/// 物料明细列表
public ProductionPickDto GetMesItemTblDetailByBillNo(
WarehouseQuery query)
{
// 关联查询物料表、物料明细表和物料基础信息表
var mesItemTblDetails = Db
.Queryable(
(a, b, c) => new JoinQueryInfos(
JoinType.Left,
a.Id == b.Tlmid,
JoinType.Left,
b.Tld009 == c.Id)
).Where((a, b, c) => a.BillNo == query.billNo && (a.Tbl013 ?? 0) == 1)
.Select((a, b, c) => new ItemDetailModel
{
FQty = b.Tld005 ?? 0, // 待退数量
SQty = b.Tld006 ?? 0, // 已退数量
DSQty = (b.Tld005 ?? 0) - (b.Tld006 ?? 0),
ItemNo = c.ItemNo, // 物料编号
ItemName = c.ItemName, // 物料名称
ItemModel = c.ItemModel // 物料型号
}).ToList();
if (mesItemTblDetails.Count<1)
throw new Exception("该申请单【" + query.billNo + "】 明细不存在,请确认!");
var DS_list = mesItemTblDetails.Where(s => s.DSQty > 0).ToList();
var YS_list = mesItemTblDetails.Where(s => s.SQty > 0).ToList();
var itemTblDetails = new ProductionPickDto
{
items = DS_list,
Ysitems = YS_list
// yisao = mesInvItemOutCDetailsList
};
return itemTblDetails;
}
///
/// 生产退料扫描库位
///
/// 仓库查询参数
/// 库位信息
public WarehouseQuery SctlScanDepots(WarehouseQuery query)
{
if (query.DepotCode.IsNullOrEmpty())
throw new NullReferenceException("请扫库位条码");
// 关联查询库位分区和库位信息
var warehouseQuery = Db.Queryable((a, b) =>
new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid))
.Where((a, b) => a.DepotSectionCode == query.DepotCode)
.Select((a, b) => new WarehouseQuery
{
DepotSectionName = a.DepotSectionName,
DepotName = b.DepotName,
DepotCode = b.DepotCode
}).First();
if (warehouseQuery == null)
throw new Exception("库位编码" + query.DepotCode + " 不存在,请确认!");
return warehouseQuery;
}
///
/// 生产退料扫描条码
///
/// 仓库查询参数
/// 处理结果
public WarehouseQuery SctlScanBarcode(WarehouseQuery query)
{
var p_item_barcode = query.barcode; // 物料条码
var p_bill_no = query.billNo; // 单据号
var p_section_code = query.DepotCode; // 库位编码
var c_user = query.userName; // 用户名
var p_bill_type_id = 100; // 单据类型ID
var p_transction_no = 104; // 交易编号
// 验证库位条码
if (p_section_code.IsNullOrEmpty()) throw new Exception("请扫库位条码!");
// 获取库位信息
var c_depot_code = Db.Queryable((a, b) =>
new JoinQueryInfos(JoinType.Inner, a.DepotGuid == b.Guid))
.Where((a, b) => a.DepotSectionCode == p_section_code)
.Select((a, b) => b.DepotId).First();
if (!c_depot_code.HasValue)
throw new Exception("库位编码" + p_section_code + " 不存在,请确认!");
// 获取库位分区信息
var mesDepotSections = Db.Queryable()
.Where(a => a.DepotSectionCode == p_section_code).First();
if (mesDepotSections == null)
throw new Exception("库位编码" + p_section_code + " 不存在,请确认!");
// 获取库位基础信息
var c_mes_depots = Db.Queryable()
.Where(b => b.Guid == mesDepotSections.DepotGuid).First();
if (c_mes_depots == null)
throw new Exception("库位编码" + p_section_code + " 不存在,请确认!");
// 检查条码是否已入库
var c_num = Db.Queryable((a, b) =>
new JoinQueryInfos(JoinType.Inner, a.Guid == b.ItemInId))
.Where((a, b) => b.ItemBarcode == p_item_barcode
&& a.BillTypeId == p_bill_type_id &&
a.TransctionNo == p_transction_no.ToString())
.Count();
if (c_num > 0) throw new Exception("此条码已扫入库,勿重复扫描!");
c_num = Db.Queryable()
.Where(t => t.ItemBarcode == p_item_barcode).Count();
if (c_num > 0) throw new Exception("此条码已扫入库,勿重复扫描!");
// 获取条码信息
var c_mes_inv_item_barcodes = Db.Queryable()
.Where(t => t.ItemBarcode == p_item_barcode).First();
if (c_mes_inv_item_barcodes == null)
throw new Exception("此条码不属于该退料单,请核对!");
if (c_mes_inv_item_barcodes.Memo != "生产退料")
throw new Exception("此条码不是生产退料条码,不可使用生产退料模块!");
// 使用 String.Equals 方法进行字符串比较,指定比较规则为忽略大小写,提高比较的灵活性
if (!string.Equals(c_mes_inv_item_barcodes.BillNo, p_bill_no, StringComparison.OrdinalIgnoreCase))
throw new Exception($"该条码对应的申请号【{c_mes_inv_item_barcodes.BillNo}】与 当前申请单号【{p_bill_no}】不一致,请核对!");
// 获取退料单信息
var C_MES_ITEM_TBL = Db.Queryable()
.Where(a => a.BillNo == p_bill_no
&& (a.Tbl013 ?? 0) == 1).First();
// 获取退料单信息
var tbWOMDAA = Db.Queryable()
.Where(a => a.Guid.ToString() == C_MES_ITEM_TBL.Tbl002).First();
if (C_MES_ITEM_TBL == null) throw new Exception("申请单已撤回,无法扫码!");
if (C_MES_ITEM_TBL.Tbl020 == 1) throw new Exception("扫码完成,申请单已完结!");
// 获取退料单明细
var C_MES_ITEM_TBL_DETAIL = Db.Queryable()
.Where(a => a.Tlid == c_mes_inv_item_barcodes.AboutGuid)
.First();
if (C_MES_ITEM_TBL_DETAIL == null)
throw new Exception("条码不属于该申请单明细,无法扫码!");
var CABerpid = Db.Queryable().Where(womdab => womdab.Guid.ToString() == C_MES_ITEM_TBL_DETAIL.Tld013).Select(womdab => womdab.Erpid).First();
var ownerId = Db.Queryable().Where(womcab => womcab.Erpid == CABerpid.Value).Select(womcab => womcab.OwnerId).First();
if (ownerId == null) throw new Exception("用料清单货主信息不存在,无法扫码,请联系管理员!");
var owner_type = "";
if (Db.Queryable().Any(x => x.Fid == ownerId))
{
owner_type = "BD_OwnerOrg";
}
else
{
// 第二层判断:检查 MES_CUSTOMER
if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(ownerId)))
{
owner_type = "BD_Customer";
}
else
{
// 第三层判断:检查 MES_SUPPLIER
if (Db.Queryable().Any(x => x.Id == Convert.ToInt32(ownerId)))
{
owner_type = "BD_Supplier";
}
else
{
throw new Exception("入库失败,用料清单货主信息存在问题,请联系管理员解决!");
}
}
}
var c_quantity = c_mes_inv_item_barcodes.Quantity;
var c_bill_no = "";
var c_id = Guid.Empty;
// 使用事务处理数据更新
UseTransaction(db =>
{
// 查询入库单
var mesInvItemIns = db.Queryable()
.Where(d =>
d.Status == 0 && d.TransctionNo ==
p_transction_no.ToString()
&& d.TaskNo == c_mes_inv_item_barcodes.BillNo
&& d.DepotsId == c_depot_code
&& d.InsDate.Value.ToString("yyyyMMdd") ==
DateTime.Now.ToString("yyyyMMdd")).First();
var totalResult = 0;
// 如果入库单不存在则创建新的入库单
if (mesInvItemIns == null)
{
c_bill_no = BillNo.GetBillNo("SCTL(生产退料)");
c_id = Guid.NewGuid();
totalResult += db.Insertable(new MesInvItemIns
{
Guid = c_id,
BillNo = c_bill_no,
BillTypeId = p_bill_type_id,
InsDate = DateTime.Now,
DepotsId = c_depot_code,
UserNoBack = c_user,
Reason = C_MES_ITEM_TBL.Tbl005,
Remark = C_MES_ITEM_TBL.Tbl006,
//InsDate = DateTime.Now,
DepotsCode = c_mes_depots.DepotCode,
TaskNo = c_mes_inv_item_barcodes.BillNo,
//DepotsId = c_depot_code,
TransctionNo = p_transction_no.ToString(),
CreateBy = c_user,
CreateDate = DateTime.Now,
LastupdateBy = c_user,
LastupdateDate = DateTime.Now,
CbillNo = tbWOMDAA.Daa001,
InType = "生产退料",
ReceiveOrgId = c_mes_depots.FSubsidiary,
Fstatus = 0,
Status = 0,
WorkNo = tbWOMDAA.Daa021
}).IgnoreColumns(true).ExecuteCommand();
}
else
{
c_id = mesInvItemIns.Guid;
c_bill_no = mesInvItemIns.BillNo;
}
// 检查是否为合并打印条码
var hbdy = c_mes_inv_item_barcodes.Hbdy ?? 0;
if (hbdy == 1) throw new Exception("不支持合并打印的条码:" + p_item_barcode);
// 检查是否存在于 MES_INV_ITEM_IN_C_ITEMS 表
var existingCount = db.Queryable()
.Where(it =>
it.ItemInId == c_id &&
it.ItemId == C_MES_ITEM_TBL_DETAIL.Tld009 &&
it.DepotId == c_depot_code.ToString())
.Count();
if (existingCount == 0)
// 不存在时插入新记录
db.Insertable(new MesInvItemInCItems
{
ItemInId = c_id,
Quantity = c_mes_inv_item_barcodes.Quantity,
CreateBy = c_user,
CreateDate = DateTime.Now,
ItemNo = c_mes_inv_item_barcodes.ItemNo,
//DepotCode = mesDepost.DepotCode,
ItemSname = c_mes_inv_item_barcodes.ItemSname,
Unit = c_mes_inv_item_barcodes.Unit,
Ebeln = c_mes_inv_item_barcodes.WorkNo,
BillNo = c_bill_no,
WorkNo = c_mes_inv_item_barcodes.WorkNo,
EbelnLineNo = c_mes_inv_item_barcodes.WorkLine,
CbillNo = c_mes_inv_item_barcodes.BillNo,
WorkLine = c_mes_inv_item_barcodes.WorkLine,
SuppId = c_mes_inv_item_barcodes.SuppId,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
Remark = c_mes_inv_item_barcodes.Memo,
EbelnK3id = c_mes_inv_item_barcodes.EbelnK3id,
LineK3id = c_mes_inv_item_barcodes.LineK3id,
ItemId = c_mes_inv_item_barcodes.ItemId,
DepotCode = c_mes_depots.DepotCode,
DepotId = c_depot_code.ToString(),
itemDabid = C_MES_ITEM_TBL_DETAIL.Tlid.ToString()
}).IgnoreColumns(true).ExecuteCommand();
else
// 存在时更新数量
db.Updateable()
.SetColumns(it => new MesInvItemInCItems
{
Quantity = SqlFunc.IsNull(it.Quantity, 0) + c_mes_inv_item_barcodes.Quantity // 确保 Quantity 不为 null
})
.Where(it =>
it.ItemInId == c_id &&
it.ItemId == C_MES_ITEM_TBL_DETAIL.Tld009 &&
it.DepotId == c_depot_code.ToString())
//.IgnoreColumns(true) // 保留 IgnoreColumns
.ExecuteCommand();
// 插入 mes_inv_item_in_c_details 表
totalResult += db.Insertable(new MesInvItemInCDetails
{
ItemInId = c_id,
BillNo = c_bill_no,
ItemBarcode = p_item_barcode,
Quantity = c_mes_inv_item_barcodes.Quantity,
BarcodeFlag = true,
EpFlag = true,
WorkType = 1,
ItemNo = c_mes_inv_item_barcodes.ItemNo,
//LotNo = cgddDetails.BatchNumber,--批号,退料不设置批号
SuppId = c_mes_inv_item_barcodes.SuppId,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
//DepotCode = mesDepost.DepotCode,
DepotSectionCode = p_section_code,
ItemSname = c_mes_inv_item_barcodes.ItemSname,
Unit = c_mes_inv_item_barcodes.Unit,
CreateBy = c_user,
CreateDate = DateTime.Now,
LastupdateBy = c_user,
LastupdateDate = DateTime.Now,
Remark = c_mes_inv_item_barcodes.Memo,
Ebeln = c_mes_inv_item_barcodes.Mblnr,
EbelnLineNo = c_mes_inv_item_barcodes.Zeile,
WorkNo = c_mes_inv_item_barcodes.WorkNo,
WorkLine = c_mes_inv_item_barcodes.WorkLine,
CbillNo = c_mes_inv_item_barcodes.BillNo,
UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag,
BoardStyle = c_mes_inv_item_barcodes.BoardStyle,
TaskNo = c_mes_inv_item_barcodes.TaskNo,
EbelnK3id = c_mes_inv_item_barcodes.EbelnK3id,
LineK3id = c_mes_inv_item_barcodes.LineK3id,
ItemId = c_mes_inv_item_barcodes.ItemId,
Ischeck = true,
//CheckDate = C_MES_ITEM_TBL_DETAIL.CheckDate,
//CheckRes = C_MES_ITEM_TBL_DETAIL.CheckRes,
//CheckStates = C_MES_ITEM_TBL_DETAIL.CheckStates,--检验信息
ReceiveOrgId = c_mes_depots.FSubsidiary,
DepotCode = c_mes_depots.DepotCode,
DepotId = Convert.ToInt64(c_depot_code)
}).IgnoreColumns(true).ExecuteCommand();
// 插入业务记录
totalResult += db.Insertable(new MesInvBusiness2
{
Guid = Guid.NewGuid(),
Status = 1,
BillTypeId = p_bill_type_id,
TransactionCode = p_transction_no.ToString(),
BusinessType = 1,
ItemBarcode = p_item_barcode,
ItemNo = c_mes_inv_item_barcodes.ItemNo,
LotNo = c_mes_inv_item_barcodes.LotNo,
EpFlag = true,
Quantity = c_mes_inv_item_barcodes.Quantity,
ToInvDepotsCode = c_mes_depots.DepotCode,
InvDepotId = c_depot_code,
ToInvDepotSectionsCode = p_section_code,
Description = "生产退料",
CreateBy = c_user,
CreateDate = DateTime.Now,
LastupdateBy = c_user,
LastupdateDate = DateTime.Now,
TaskNo = c_mes_inv_item_barcodes.TaskNo,
BillNo = c_bill_no,
WorkNo = c_mes_inv_item_barcodes.WorkNo,
WorkLine = c_mes_inv_item_barcodes.WorkLine,
SuppId = c_mes_inv_item_barcodes.SuppId,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
ItemId = c_mes_inv_item_barcodes.ItemId
}).IgnoreColumns(true).ExecuteCommand();
// 插入库存记录
totalResult += db.Insertable(new MesInvItemStocks
{
Guid = Guid.NewGuid(),
TaskNo = c_mes_inv_item_barcodes.TaskNo,
ItemBarcode = p_item_barcode,
ItemNo = c_mes_inv_item_barcodes.ItemNo,
LotNo = c_mes_inv_item_barcodes.LotNo,
Quantity = c_mes_inv_item_barcodes.Quantity,
//EpFlag = c_mes_inv_item_barcodes.EpFlag.Value
// ? (byte)1
// : (byte)0,
DepotId = c_mes_depots.DepotId,
DepotsCode = c_mes_depots.DepotCode,
DepotSectionsCode = p_section_code,
CheckDate = c_mes_inv_item_barcodes.CreateDate,
IndepDate = DateTime.Now,
BoardStyle = c_mes_inv_item_barcodes.BoardStyle,
WorkNo = c_mes_inv_item_barcodes.WorkNo,
WorkLine = c_mes_inv_item_barcodes.WorkLine,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
ItemId = c_mes_inv_item_barcodes.ItemId,
BillNo = c_mes_inv_item_barcodes.BillNo,
//DepotId = Convert.ToInt32(c_depot_code),
OwnerId = ownerId,
OwnerType = owner_type,
StockOrgId = c_mes_depots.FSubsidiary,
IndepUserCode = c_user
}).IgnoreColumns(true).ExecuteCommand();
// 根据退料类型(良品退料、来料不良退料、作业不良退料)更新相关数据
if (C_MES_ITEM_TBL.Tbl005 is "良品退料" or "来料不良退料")
{
// 良品退料 - 更新工单表(WOMDAB)相关数量
if (C_MES_ITEM_TBL.Tbl005 == "良品退料")
totalResult += Db.Updateable()
.SetColumns(it => new Womdab
{
Dab007 = it.Dab007 -
c_mes_inv_item_barcodes.Quantity, // 减少工单数量
Dab022 = (it.Dab022 ?? 0) +
c_mes_inv_item_barcodes.Quantity, // 增加退料数量
LpTl = (it.LpTl ?? 0) +
(int)c_mes_inv_item_barcodes
.Quantity, // 增加良品退料数量
Dab020 = (it.Dab020 ?? 0) -
c_mes_inv_item_barcodes.Quantity // 减少已发料数量
})
.Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
&& it.Dab002 == c_mes_inv_item_barcodes
.WorkLine
&& it.Dab003 == c_mes_inv_item_barcodes
.ItemId.ToString())
.ExecuteCommand();
// 来料不良退料 - 更新工单表(WOMDAB)相关数量
else if (C_MES_ITEM_TBL.Tbl005 == "来料不良退料")
totalResult += Db.Updateable()
.SetColumns(it => new Womdab
{
Dab007 = it.Dab007 -
c_mes_inv_item_barcodes.Quantity, // 减少工单数量
Dab022 = (it.Dab022 ?? 0) +
c_mes_inv_item_barcodes.Quantity, // 增加退料数量
LlBl = (it.LlBl ?? 0) +
(int)c_mes_inv_item_barcodes
.Quantity, // 增加来料不良数量
Dab020 = (it.Dab020 ?? 0) -
c_mes_inv_item_barcodes.Quantity // 减少已发料数量
})
.Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
&& it.Dab002 == c_mes_inv_item_barcodes
.WorkLine
&& it.Dab003 == c_mes_inv_item_barcodes
.ItemId.ToString())
.ExecuteCommand();
// 更新退料单明细表已退数量
totalResult += Db.Updateable()
.SetColumns(it => new MesItemTblDetail
{
Tld006 = (it.Tld006 ?? 0) +
(int)c_mes_inv_item_barcodes.Quantity // 增加已退数量
})
.Where(it => it.Tlmid == C_MES_ITEM_TBL.Id &&
it.Tlid == c_mes_inv_item_barcodes.AboutGuid)
.ExecuteCommand();
}
// 作业不良退料 - 更新工单表和退料单明细表
else if (C_MES_ITEM_TBL.Tbl005 == "作业不良退料")
{
totalResult += Db.Updateable()
.SetColumns(it => new Womdab
{
Dab022 = (it.Dab022 ?? 0) +
c_mes_inv_item_barcodes.Quantity, // 增加退料数量
ZyBl = (it.ZyBl ?? 0) +
(int)c_mes_inv_item_barcodes
.Quantity, // 增加作业不良数量
Dab020 = (it.Dab020 ?? 0) -
c_mes_inv_item_barcodes.Quantity // 减少已发料数量
})
.Where(it => it.Dab001 == c_mes_inv_item_barcodes.WorkNo
&& it.Dab002 ==
c_mes_inv_item_barcodes.WorkLine
&& it.Dab003 == c_mes_inv_item_barcodes.ItemId
.ToString())
.ExecuteCommand();
// 更新退料单明细表已退数量
totalResult += Db.Updateable()
.SetColumns(it => new MesItemTblDetail
{
Tld006 = (it.Tld006 ?? 0) +
(int)c_mes_inv_item_barcodes.Quantity // 增加已退数量
})
.Where(it => it.Tlmid == C_MES_ITEM_TBL.Id &&
it.Tlid == c_mes_inv_item_barcodes.AboutGuid)
.ExecuteCommand();
}
// 如果待退数量等于本次退料数量,则更新明细完成状态
if ((C_MES_ITEM_TBL_DETAIL.Tld005 ?? 0) -
(C_MES_ITEM_TBL_DETAIL.Tld006 ?? 0) ==
c_mes_inv_item_barcodes.Quantity)
totalResult += Db.Updateable()
.SetColumns(it => new MesItemTblDetail
{ Tld008 = 1 }) // 设置完成标志
.Where(it => it.Tlid == C_MES_ITEM_TBL_DETAIL.Tlid)
.ExecuteCommand();
// 检查退料单是否所有明细都已完成
var remainingCount = Db.Queryable(
(a, b) =>
new JoinQueryInfos(JoinType.Left, a.Id == b.Tlmid))
.Where((a, b) =>
a.BillNo == p_bill_no &&
(b.Tld005 ?? 0) - (b.Tld006 ?? 0) > 0)
.Count();
// 如果所有明细都已完成,则更新退料单状态为已完成
if (remainingCount < 1)
totalResult += Db.Updateable()
.SetColumns(it => it.Tbl020 == 1) // 设置完成标志
.Where(it => it.BillNo == p_bill_no)
.ExecuteCommand();
// 检查必要的插入操作是否都成功执行
var minimumExpectedOperations = 3; // 至少需要执行的插入操作数
if (totalResult < minimumExpectedOperations)
throw new Exception(
$"关键数据插入失败,预期至少{minimumExpectedOperations}个操作,实际执行{totalResult}个操作");
// 创建 插入日志
var logService = new LogService();
var LogMsg = "[PDA]生产退料。条码【" + query.barcode + "】 退料单号【" + c_bill_no + "】";
logService.CreateLog(db, query.userName, tbWOMDAA.Guid.ToString(), "WOMDAA", LogMsg, tbWOMDAA.Daa001);
return totalResult;
});
query.itemNo = c_mes_inv_item_barcodes.ItemNo;
query.Num = c_quantity.Value;
return query;
}
}