using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.util;
|
using SqlSugar;
|
|
namespace MES.Service.service.Warehouse;
|
|
public class MesInvItemBarcodesManager : Repository<MesInvItemBarcodes>
|
{
|
//当前类已经继承了 Repository 增、删、查、改的方法
|
|
//这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemBarcodesManager.cs
|
|
#region 生产领料条码验证
|
|
//生产领料条码验证合理性
|
private bool PdaProdPickItemCkBar(string cBarcode, string cDaa001)
|
{
|
try
|
{
|
// 1. 检查条码是否为空
|
if (string.IsNullOrEmpty(cBarcode)) throw new Exception("请输入条码！");
|
|
// 2. 检查条码是否存在于 MesInvItemBarcodes 表中
|
var tempNum = Db.Queryable<MesInvItemBarcodes>()
|
.Where(it => it.ItemBarcode == cBarcode)
|
.Count();
|
if (tempNum == 0) throw new Exception("条码不存在！");
|
|
// 3. 检查条码是否存在于 MesInvItemStocks 表中
|
tempNum = Db.Queryable<MesInvItemStocks>()
|
.Where(it => it.ItemBarcode == cBarcode)
|
.Count();
|
if (tempNum == 0) throw new Exception("库存中无此条码！");
|
|
// 4. 获取 MesInvItemBarcodes 的数据
|
var mesInvItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
|
.Where(it => it.ItemBarcode == cBarcode)
|
.First();
|
if (mesInvItemBarcodes == null) throw new Exception("条码不存在！");
|
|
// 5. 检查条码数量是否为 0
|
if (mesInvItemBarcodes.Quantity == 0)
|
throw new Exception("此条码数量为0！");
|
|
// 6. 检查条码状态
|
if (mesInvItemBarcodes.Barcodestatus == 1 ||
|
mesInvItemBarcodes.Hbdytm == 1)
|
throw new Exception("无效条码！");
|
|
// 7. 检查条码是否被冻结
|
if (mesInvItemBarcodes.Visable == 1) throw new Exception("条码冻结！");
|
|
// 8. 检查条码是否作废
|
if (mesInvItemBarcodes.Location == 2) throw new Exception("条码作废！");
|
|
// 9. 检查投料单是否需要此物料
|
tempNum = Db.Queryable<Womdab>()
|
.Where(it =>
|
it.Dab001 == cDaa001 && it.Dab003 ==
|
mesInvItemBarcodes.ItemId.ToString())
|
.Count();
|
if (tempNum == 0) throw new Exception("投料单不需要此物料！");
|
|
var sql =
|
"SELECT COUNT(1) FROM WOMDAB WHERE DAB001='" + cDaa001 +
|
"' AND DAB003='" + mesInvItemBarcodes.ItemId +
|
"' AND DAB017=(SELECT DEPOTS_CODE FROM MES_INV_ITEM_STOCKS WHERE ITEM_BARCODE='" +
|
cBarcode + "')";
|
|
// 10. 检查发料仓库是否正确
|
tempNum = Db.Ado.SqlQuerySingle<int>(sql);
|
if (tempNum == 0) throw new Exception("发料仓库有误！");
|
|
// 11. 成功，返回结果
|
return true;
|
}
|
catch (Exception ex)
|
{
|
// 捕获异常并重新抛出
|
throw new Exception($"报错: {ex.Message}");
|
}
|
}
|
|
public decimal? GetBarCodesQuan(WarehouseQuery query)
|
{
|
if (!PdaProdPickItemCkBar(query.barcode, query.daa001)) return null;
|
|
var invItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
|
.Where(s => s.ItemBarcode == query.barcode).First();
|
|
if (invItemBarcodes == null) throw new Exception("条码不存在！");
|
|
return invItemBarcodes.Quantity;
|
}
|
|
//生产领料保存
|
public string PrcRfPdaProdPickItem3(WarehouseQuery query)
|
{
|
var (factory, company) = UserUtil.GetFactory(query.userName);
|
var param = new ProdPickParams
|
{
|
CUser = query.userName,
|
CBarcode = query.barcode,
|
CDaa001 = query.daa001,
|
CNum = query.Num,
|
PiFactory = factory,
|
PiCompany = company
|
};
|
|
// 检查发料数量是否合法
|
if (param.CNum <= 0) throw new Exception("发料数量不能小于等于0！");
|
|
// 检查条码是否存在
|
param.CInvItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
|
.Where(it => it.ItemBarcode == param.CBarcode)
|
.First();
|
if (param.CInvItemBarcodes == null) throw new Exception("条码不存在！");
|
|
// 检查条码是否已入库
|
param.CInvItemStocks = Db.Queryable<MesInvItemStocks>()
|
.Where(it => it.ItemBarcode == param.CBarcode)
|
.First();
|
if (param.CInvItemStocks == null) throw new Exception("条码未入库！");
|
|
// 检查工单号是否存在
|
param.CWomdaa = Db.Queryable<Womdaa>()
|
.Where(it => it.Daa001 == param.CDaa001)
|
.First();
|
if (param.CWomdaa == null) throw new Exception("不存在此工单！");
|
|
// 检查发料数量是否超出条码数量
|
if (param.CNum > param.CInvItemBarcodes.Quantity)
|
throw new Exception("发料数量不能大于条码数量！");
|
|
// 检查条码合法性
|
if (!PdaProdPickItemCkBar(param.CBarcode, param.CDaa001)) return null;
|
|
// 计算剩余可发数量
|
var tempNum = CalculateRemainingQuantity(param.CDaa001,
|
param.CInvItemBarcodes.ItemId);
|
if (param.CNum > tempNum) throw new Exception("发料数量不能大于对应物料的未发料数量！");
|
|
// 工单部门名称
|
param.CDepart = Db.Queryable<SysDepartment>()
|
.Where(dep => dep.Id.ToString() == param.CWomdaa.Daa013)
|
.Select(dep => dep.Departmentname)
|
.First();
|
|
// 生成新条码
|
param.NewBarcode = GenerateNewBarcode(param.CBarcode);
|
|
UseTransaction(db =>
|
{
|
// 对出库单的操作
|
var (outId, outBill) = HandleInventoryOut(db, param);
|
param.COutId = outId;
|
param.OutBill = outBill;
|
|
// 更新条码数据
|
UpdateBarcodeData(db, param);
|
|
// 生成打印条码并插入到 mes_rf_prnbarcode
|
GenerateAndInsertPrintBarcode(db, param);
|
|
return 1;
|
});
|
|
return "001";
|
}
|
|
private void UpdateBarcodeData(SqlSugarScope db, ProdPickParams param)
|
{
|
// 更新条码数据
|
db.Updateable<MesInvItemBarcodes>()
|
.SetColumns(it => new MesInvItemBarcodes
|
{ Oldqty = it.Quantity, Quantity = it.Quantity - param.CNum })
|
.Where(it => it.ItemBarcode == param.CBarcode)
|
.ExecuteCommand();
|
|
// 插入新的条码记录
|
db.Insertable(new MesInvItemBarcodes
|
{
|
ItemNo = param.CInvItemBarcodes.ItemNo,
|
ItemId = param.CInvItemBarcodes.ItemId,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
TaskNo = param.CDaa001,
|
WorkNo = param.CWomdaa.Daa021,
|
SuppNo = param.CInvItemBarcodes.SuppNo,
|
Mblnr = param.CInvItemBarcodes.Mblnr,
|
Zeile = param.CInvItemBarcodes.Zeile,
|
Factory = param.CInvItemBarcodes.Factory,
|
Company = param.CInvItemBarcodes.Company,
|
EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
|
LineK3id = param.CInvItemBarcodes.LineK3id,
|
Quantity = param.CNum,
|
Oldqty = param.CNum,
|
ItemBarcode = param.NewBarcode,
|
Unit = param.CInvItemBarcodes.Unit,
|
LotDate = param.CInvItemBarcodes.LotDate,
|
Memo = "生产领料",
|
ItemSname = param.CInvItemBarcodes.ItemSname,
|
TrLotno = param.CInvItemBarcodes.TrLotno,
|
BillNo = param.CInvItemBarcodes.BillNo,
|
InsDate = param.CInvItemBarcodes.InsDate,
|
WorkLine = param.CInvItemBarcodes.WorkLine,
|
ComeFlg = 6,
|
OldItemBarcode = param.CBarcode
|
}).ExecuteCommand();
|
|
// 更新条码库存
|
db.Updateable<MesInvItemStocks>()
|
.SetColumns(it => new MesInvItemStocks
|
{ Quantity = it.Quantity - param.CNum })
|
.Where(it => it.ItemBarcode == param.CBarcode)
|
.ExecuteCommand();
|
|
// 插入出库条码明细表
|
db.Insertable(new MesInvItemOutCDetails
|
{
|
ItemOutId = param.COutId,
|
ItemNo = param.CInvItemBarcodes.ItemNo,
|
ItemId = param.CInvItemBarcodes.ItemId,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
DepotCode = param.CInvItemStocks.DepotsCode,
|
DepotSectionCode = param.CInvItemStocks.DepotSectionsCode,
|
TaskNo = param.CDaa001,
|
WorkNo = param.CWomdaa.Daa021,
|
SuppNo = param.CInvItemBarcodes.SuppNo,
|
PbillNo = param.OutBill,
|
Factory = param.PiFactory,
|
Company = param.PiCompany,
|
EbelnK3id = param.CInvItemStocks.EbelnK3id,
|
LineK3id = param.CInvItemStocks.LineK3id,
|
Quantity = param.CNum,
|
ItemBarcode = param.NewBarcode
|
}).ExecuteCommand();
|
|
// 插入到 MesInvBusiness2 表
|
db.Insertable(new MesInvBusiness2
|
{
|
Status = 1,
|
BillTypeId = 200,
|
ItemNo = param.CInvItemBarcodes.ItemNo,
|
ItemId = param.CInvItemBarcodes.ItemId,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
TaskNo = param.CDaa001,
|
WorkNo = param.CWomdaa.Daa021,
|
SuppNo = param.CInvItemBarcodes.SuppNo,
|
Factory = param.PiFactory,
|
Company = param.PiCompany,
|
EbelnK3id = param.CInvItemStocks.EbelnK3id,
|
LineK3id = param.CInvItemStocks.LineK3id,
|
Quantity = param.CNum,
|
ItemBarcode = param.CBarcode
|
}).ExecuteCommand();
|
}
|
|
private void GenerateAndInsertPrintBarcode(SqlSugarScope db,
|
ProdPickParams param)
|
{
|
string cSupName = null;
|
if (!string.IsNullOrEmpty(param.CInvItemBarcodes.SuppNo))
|
cSupName = db.Queryable<MesSupplier>()
|
.Where(it => it.SuppNo == param.CInvItemBarcodes.SuppNo)
|
.Select(it => it.SuppName)
|
.First();
|
|
var cId =
|
db.Ado.GetInt(
|
"SELECT F_GETSEQNEXTVALUE('MES_RF_PRNBARCODE') FROM dual");
|
|
// 插入打印条码记录
|
db.Insertable(new MesRfPrnbarcode
|
{
|
Id = cId,
|
Forder = "1",
|
Fno = "1",
|
CreateDate = DateTime.Now,
|
Strp1 = param.CBarcode,
|
Strp2 = cSupName ?? " ",
|
Strp3 = param.CInvItemBarcodes.ItemNo,
|
Strp4 = GetItemName(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp5 =
|
$"{param.CInvItemBarcodes.Quantity - param.CNum} {GetUnitName(param.CInvItemBarcodes.Unit)}",
|
Strp6 = DateTime.Now.ToString("yyyy-MM-dd"),
|
Strp7 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp8 = param.CBarcode,
|
Strp9 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp10 = param.CBarcode,
|
Strp11 = param.CBarcode,
|
Strp12 = param.CBarcode
|
}).ExecuteCommand();
|
|
db.Insertable(new MesRfPrnbarcode
|
{
|
Id = cId,
|
Forder = "1",
|
Fno = "1",
|
CreateDate = DateTime.Now,
|
Strp1 = param.NewBarcode,
|
Strp2 = cSupName ?? " ",
|
Strp3 = param.CInvItemBarcodes.ItemNo,
|
Strp4 = GetItemName(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp5 = $"{param.CNum} {GetUnitName(param.CInvItemBarcodes.Unit)}",
|
Strp6 = DateTime.Now.ToString("yyyy-MM-dd"),
|
Strp7 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp8 = param.NewBarcode,
|
Strp9 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
|
Strp10 = param.NewBarcode,
|
Strp11 = param.NewBarcode,
|
Strp12 = param.NewBarcode
|
}).ExecuteCommand();
|
}
|
|
private (int OutId, string OutBill) HandleInventoryOut(SqlSugarScope db,
|
ProdPickParams param)
|
{
|
// 处理出库单的插入或更新逻辑
|
var tempNum = db.Queryable<MesInvItemOuts>()
|
.Where(it => it.TaskNo == param.CDaa001 && it.Status == 0)
|
.Count();
|
|
var cOutId = 0;
|
string cOutBill;
|
|
if (tempNum == 0)
|
{
|
var sql =
|
"SELECT COUNT(1) FROM MES_INV_ITEM_OUTS WHERE TRUNC(CREATE_DATE)=TRUNC(SYSDATE);";
|
var todayCount = db.Ado.SqlQuerySingle<int>(sql);
|
|
cOutBill = $"P{DateTime.Now:yyyyMMdd}{todayCount + 1:D4}";
|
|
// 插入新的出库单
|
db.Insertable(new MesInvItemOuts
|
{
|
ItemOutNo = cOutBill,
|
TaskNo = param.CDaa001,
|
Status = 0,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
WorkNo = param.CWomdaa.Daa021,
|
OutType = "生产领料",
|
BoardItem = param.CWomdaa.Daa002,
|
PbillNo = param.CDaa001,
|
BillTypeId = 200,
|
TransactionNo = 201,
|
Company = param.PiCompany,
|
Factory = param.PiFactory,
|
Remark = "生产领料",
|
DepotCode = param.CInvItemStocks.DepotsCode,
|
OutDate = DateTime.Now,
|
OutPart = param.CDepart
|
}).ExecuteCommand();
|
}
|
else
|
{
|
// 获取现有出库单号
|
var outItem = db.Queryable<MesInvItemOuts>()
|
.Where(it => it.TaskNo == param.CDaa001 && it.Status == 0)
|
.Select(it => new { it.ItemOutNo, it.Id })
|
.First();
|
|
cOutBill = outItem.ItemOutNo;
|
cOutId = Convert.ToInt32(outItem.Id);
|
}
|
|
// 插入或更新从表
|
InsertOrUpdateOutItems(db, cOutId, param);
|
|
return (cOutId, cOutBill);
|
}
|
|
private void InsertOrUpdateOutItems(SqlSugarScope db, int cOutId,
|
ProdPickParams param)
|
{
|
var tempNum = db.Queryable<MesInvItemOutItems>()
|
.Where(it =>
|
it.ItemId == param.CInvItemBarcodes.ItemId &&
|
it.ItemOutId == cOutId)
|
.Count();
|
|
var cNumTemp = param.CNum;
|
|
var sql =
|
"SELECT B.Id, NVL(A.YF, 0) yfl, B.DAB006 - NVL(A.YF, 0) Qty FROM WOMDAB B LEFT JOIN (SELECT SUM(QUANTITY) YF, ITEM_DABID FROM MES_INV_ITEM_OUT_ITEMS GROUP BY ITEM_DABID) A ON A.ITEM_DABID = B.ID WHERE DAB003 = '" +
|
param.CInvItemBarcodes.ItemId + "' AND DAB001 = '" + param.CDaa001 +
|
"' ORDER BY TO_NUMBER(DAB002)";
|
|
var womdabList = db.Ado.SqlQuery<DabResult>(sql);
|
|
foreach (var item in womdabList)
|
{
|
if (item.Qty <= 0) continue;
|
var itemDabid = item.Id;
|
if (cNumTemp <= item.Qty)
|
{
|
if (tempNum == 0)
|
// 插入新的发料记录
|
db.Insertable(new MesInvItemOutItems
|
{
|
ItemOutId = cOutId,
|
Quantity = cNumTemp,
|
ItemDabid = itemDabid,
|
ItemId = param.CInvItemBarcodes.ItemId,
|
ItemNo = param.CInvItemBarcodes.ItemNo,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
Factory = param.PiFactory,
|
Company = param.PiCompany,
|
DepotCode = param.CInvItemStocks.DepotsCode,
|
TaskNo = param.CDaa001,
|
EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
|
LineK3id = param.CInvItemBarcodes.LineK3id,
|
PbillNo = param.CDaa001,
|
WorkNo = param.CInvItemStocks.WorkNo,
|
WorkLine = param.CInvItemStocks.WorkLine,
|
DepotSectionCode =
|
param.CInvItemStocks.DepotSectionsCode
|
}).ExecuteCommand();
|
else
|
// 更新现有发料记录的数量
|
db.Updateable<MesInvItemOutItems>()
|
.SetColumns(it => new MesInvItemOutItems
|
{ Quantity = it.Quantity + cNumTemp })
|
.Where(it =>
|
it.ItemOutId == cOutId &&
|
it.ItemDabid == itemDabid)
|
.ExecuteCommand();
|
|
// 更新 WOMDAB 表的发料数量
|
db.Updateable<Womdab>()
|
.SetColumns(it => new Womdab
|
{ Dab007 = item.Yfl + cNumTemp })
|
.Where(it => it.Id == itemDabid)
|
.ExecuteCommand();
|
|
break;
|
}
|
|
// 插入剩余的发料记录
|
db.Insertable(new MesInvItemOutItems
|
{
|
ItemOutId = cOutId,
|
Quantity = item.Qty,
|
ItemDabid = itemDabid,
|
ItemId = param.CInvItemBarcodes.ItemId,
|
ItemNo = param.CInvItemBarcodes.ItemNo,
|
CreateBy = param.CUser,
|
CreateDate = DateTime.Now,
|
Factory = param.PiFactory,
|
Company = param.PiCompany,
|
DepotCode = param.CInvItemStocks.DepotsCode,
|
TaskNo = param.CDaa001,
|
EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
|
LineK3id = param.CInvItemBarcodes.LineK3id,
|
PbillNo = param.CDaa001,
|
WorkNo = param.CInvItemStocks.WorkNo,
|
WorkLine = param.CInvItemStocks.WorkLine,
|
DepotSectionCode = param.CInvItemStocks.DepotSectionsCode
|
}).ExecuteCommand();
|
|
cNumTemp -= item.Qty;
|
|
// 更新 WOMDAB 表的发料数量
|
db.Updateable<Womdab>()
|
.SetColumns(it => new Womdab { Dab007 = it.Dab006 })
|
.Where(it => it.Id == itemDabid)
|
.ExecuteCommand();
|
}
|
}
|
|
private string GenerateNewBarcode(string oldBarcode)
|
{
|
// 生成新的条码逻辑
|
var count = Db.Queryable<MesInvItemBarcodes>()
|
.Where(b => b.OldItemBarcode == oldBarcode).Count();
|
return $"{oldBarcode}-{count + 1}";
|
}
|
|
private decimal? CalculateRemainingQuantity(
|
string cDaa001, decimal? itemId)
|
{
|
// 计算剩余可发数量
|
var womdab = Db.Queryable<Womdab, Womdaa>((b, a) => new JoinQueryInfos(
|
JoinType.Left, b.Pid == a.Id))
|
.Where((b, a) =>
|
a.Daa001 == cDaa001 && b.Dab003 == itemId.ToString())
|
.Select(b => new
|
{
|
TotalQuantity = SqlFunc.AggregateMax(b.Dab006),
|
IssuedQuantity = SqlFunc.AggregateMax(b.Dab007)
|
})
|
.First();
|
|
return womdab.TotalQuantity - womdab.IssuedQuantity;
|
}
|
|
private static string GetItemName(decimal? pId)
|
{
|
try
|
{
|
if (pId == null) return null;
|
|
// 查询物料名称
|
var itemName = Db.Queryable<MesItems>()
|
.Where(it => it.Id == pId)
|
.Select(it => it.ItemName)
|
.First();
|
|
return itemName;
|
}
|
catch (Exception)
|
{
|
throw new Exception("未找到物料名称，物料id号为" + pId);
|
}
|
}
|
|
private string GetUnitName(string? pId)
|
{
|
try
|
{
|
if (string.IsNullOrEmpty(pId)) return null;
|
|
// 查询单位名称是否存在
|
var tempNum = Db.Queryable<MesUnit>()
|
.Where(it => it.Id.ToString() == pId)
|
.Count();
|
|
string unitName;
|
|
if (tempNum != 0)
|
// 如果在 MES_UNIT 表中找到单位名称
|
unitName = Db.Queryable<MesUnit>()
|
.Where(it => it.Id.ToString() == pId)
|
.Select(it => it.Fname)
|
.First();
|
else
|
// 如果在 MES_UNIT 表中找不到，在 MES_ITEMS 表中查找相关联的单位名称
|
unitName = Db.Queryable<MesItems, MesUnit>((i, u) =>
|
new JoinQueryInfos(
|
JoinType.Left, i.ItemUnit == u.Id.ToString()))
|
.Where((i, u) => i.Id.ToString() == pId)
|
.Select((i, u) => u.Fname)
|
.First();
|
|
return unitName;
|
}
|
catch (Exception)
|
{
|
throw new Exception("单位名称未找到，物料id号为" + pId);
|
}
|
}
|
|
private string GetItemModel(decimal? pId)
|
{
|
try
|
{
|
if (pId == null) return null;
|
|
// 查询物料规格
|
var itemModel = Db.Queryable<MesItems>()
|
.Where(it => it.Id == pId)
|
.Select(it => it.ItemModel)
|
.First();
|
|
return itemModel;
|
}
|
catch (Exception)
|
{
|
throw new Exception("未找到物料型号，物料id号为" + pId);
|
}
|
}
|
|
#endregion
|
}
|
|
public class ProdPickParams
|
{
|
public string? CUser { get; set; }
|
public string? CBarcode { get; set; }
|
public string? CDaa001 { get; set; }
|
public decimal? CNum { get; set; }
|
public MesInvItemBarcodes? CInvItemBarcodes { get; set; }
|
public MesInvItemStocks? CInvItemStocks { get; set; }
|
public Womdaa? CWomdaa { get; set; }
|
public string? NewBarcode { get; set; }
|
public string? PiCompany { get; set; }
|
public string? PiFactory { get; set; }
|
public string? CDepart { get; set; }
|
public int? COutId { get; set; }
|
public string? OutBill { get; set; }
|
}
|
|
public class DabResult
|
{
|
public decimal? Id { get; set; }
|
public decimal? Yfl { get; set; }
|
public decimal? Qty { get; set; }
|
}
|
