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 MesInvItemMovesManager : Repository<MesInvItemMoves>
|
{
|
//当前类已经继承了 Repository 增、删、查、改的方法
|
|
//这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemMovesManager.cs
|
|
public MovesDto ScanInBarcode(WarehouseQuery query)
|
{
|
if (string.IsNullOrEmpty(query.sectionCode))
|
throw new Exception("请扫库位条码！");
|
|
var pBillTypeId = 300;
|
var pTransctionNo = 301;
|
|
var depotQuery = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
|
new JoinQueryInfos(
|
JoinType.Inner, a.Zuid.ToString() == b.Zuid))
|
.Where((a, b) => a.DepotSectionCode == query.sectionCode)
|
.Select((a, b) => new { b.DepotCode })
|
.First();
|
|
if (depotQuery == null)
|
throw new Exception($"002[库位编码 {query.sectionCode} 不存在，请确认！");
|
|
var cDepotCode = depotQuery.DepotCode;
|
|
// 查询调拔入库信息
|
var itemMoveQuery = Db
|
.Queryable<MesInvItemMoves, MesInvItemMovesCDetails>((a, b) =>
|
new JoinQueryInfos(
|
JoinType.Inner, a.Id == b.ItemMoveId))
|
.Where((a, b) => b.ItemBarcode == query.barcode &&
|
a.BillTypeId == pBillTypeId &&
|
a.TransactionNo == pTransctionNo &&
|
a.Status == 1 && SqlFunc.IsNull(b.MoveOk, 0) != 1)
|
.Select((a, b) => new { a.Id, a.BillNo, a.InvDepotsCode })
|
.First();
|
|
if (itemMoveQuery == null) throw new Exception("条码未做调拔出库扫码，请核对！");
|
|
var cBillNo = itemMoveQuery.BillNo;
|
var iDepotCode = itemMoveQuery.InvDepotsCode;
|
|
// 验证库区与仓库
|
var depotValidationQuery = Db.Queryable<MesDepotSections, MesDepots>(
|
(a, b) => new JoinQueryInfos(
|
JoinType.Inner, a.Zuid.ToString() == b.Zuid))
|
.Where((a, b) => a.DepotSectionCode == query.sectionCode &&
|
b.DepotCode == iDepotCode)
|
.Select((a, b) => new { b.DepotCode })
|
.First();
|
|
if (depotValidationQuery == null)
|
throw new Exception($"002[库位编码 {query.sectionCode} 不存在，请确认！");
|
|
if (iDepotCode != depotValidationQuery.DepotCode)
|
throw new Exception("实际仓库与申请调入仓库不符，请核对！");
|
|
var barcodeCount = Db.Queryable<MesInvItemMovesCDetails>()
|
.Where(b =>
|
b.ItemBarcode == query.barcode &&
|
b.ItemMoveId == itemMoveQuery.Id && b.MoveOk == 1)
|
.Count();
|
|
if (barcodeCount > 0) throw new Exception("条码调拔已完成，请核对！");
|
|
var barcodeInfo = Db.Queryable<MesInvItemBarcodes>()
|
.Where(t => t.ItemBarcode == query.barcode)
|
.First();
|
|
if (barcodeInfo == null) throw new Exception("条码不存在，请核对！");
|
|
var isAudit = UseTransaction(db =>
|
{
|
// 更新业务、库存和条码数据
|
db.Updateable<MesInvBusiness2>()
|
.SetColumns(b => new MesInvBusiness2
|
{
|
ToInvDepotsCode = cDepotCode,
|
ToInvDepotSectionsCode = query.sectionCode
|
})
|
.Where(b =>
|
b.BillNo == cBillNo && b.BillTypeId == pBillTypeId &&
|
b.TransactionCode == pTransctionNo.ToString() &&
|
b.ItemBarcode == query.barcode)
|
.ExecuteCommand();
|
|
db.Updateable<MesInvItemStocks>()
|
.SetColumns(b => new MesInvItemStocks
|
{
|
DepotsCode = cDepotCode,
|
DepotSectionsCode = query.sectionCode
|
})
|
.Where(b => b.ItemBarcode == query.barcode)
|
.ExecuteCommand();
|
|
db.Updateable<MesInvItemMovesCDetails>()
|
.SetColumns(b => new MesInvItemMovesCDetails
|
{
|
MoveOk = 1,
|
InvDepotsCode = cDepotCode,
|
InvDepotSectionsCode = query.sectionCode
|
})
|
.Where(b =>
|
b.ItemBarcode == query.barcode &&
|
b.ItemMoveId == itemMoveQuery.Id)
|
.ExecuteCommand();
|
|
// 检查是否所有条码已扫码移库
|
var totalQuantity = db.Queryable<MesInvItemOutItems>()
|
.Where(a => a.ItemOutId == itemMoveQuery.Id)
|
.Sum(a => SqlFunc.IsNull(a.Quantity, 0));
|
|
var scannedQuantity = db.Queryable<MesInvItemMovesCDetails>()
|
.Where(a => a.ItemMoveId == itemMoveQuery.Id && a.MoveOk == 1)
|
.Sum(a => SqlFunc.IsNull(a.Quantity, 0));
|
|
if (totalQuantity == scannedQuantity) return 1;
|
|
return 0;
|
});
|
|
var dto = new MovesDto();
|
dto.InAudit = isAudit == 1;
|
dto.BarcodesInfo = barcodeInfo;
|
return dto;
|
}
|
|
|
public List<MesInvItemMoves> GetBillNo(WarehouseQuery query)
|
{
|
query.Factory = "1000";
|
query.Company = "1000";
|
|
return Db.Queryable<MesInvItemMoves>()
|
.Where(it =>
|
SqlFunc.IsNull(it.Status, 0) == 1 &&
|
SqlFunc.IsNull(it.Ts, 0) != 1)
|
.Where(it =>
|
it.Factory == query.Factory && it.Company == query.Company)
|
.OrderBy(it => it.Id, OrderByType.Desc).ToList();
|
}
|
|
public List<MesInvItemMoves> GetPage(WarehouseQuery query)
|
{
|
return Db.Queryable<MesInvItemMoves>()
|
.WhereIF(!string.IsNullOrEmpty(query.billNo),
|
s => s.BillNo == query.billNo)
|
.OrderByDescending(s => s.Id)
|
.ToPageList(query.PageIndex, query.Limit);
|
}
|
|
public MovesDto GetItems(WarehouseQuery query)
|
{
|
var dto = new MovesDto();
|
|
dto.BarcodeList = GetItemMovesCDetails(query);
|
dto.OutItems = GetItemOutItems(query);
|
|
return dto;
|
}
|
|
private List<MesInvItemOutItems> GetItemOutItems(WarehouseQuery query)
|
{
|
var sql = string.Format(
|
"SELECT C.ITEM_NO, C.QUANTITY, C.REMARK, C.REMARK, S.ITEM_NAME, S.ITEM_MODEL, U.FNAME ITEM_UNIT, D.QUANTITY_OK, NVL(ST.QUANTITY, 0) WLKC FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_ID = S.ID LEFT JOIN MES_UNIT U ON U.ID = S.ITEM_UNIT LEFT JOIN MES_DEPOTS P ON P.DEPOT_CODE = C.DEPOT_CODE LEFT JOIN MES_DEPOT_SECTIONS E ON E.DEPOT_SECTION_CODE = C.DEPOT_SECTION_CODE LEFT JOIN (SELECT ITEM_MOVE_ID, ITEM_NO, SUM(QUANTITY) QUANTITY_OK FROM MES_INV_ITEM_MOVES_C_DETAILS GROUP BY ITEM_MOVE_ID, ITEM_NO) D ON D.ITEM_MOVE_ID = C.ITEM_OUT_ID AND D.ITEM_NO = C.ITEM_NO LEFT JOIN MES_INV_ITEM_MOVES A ON A.ID = C.ITEM_OUT_ID left join (SELECT ITEM_ID, SUM(QUANTITY) QUANTITY, DEPOTS_CODE FROM MES_INV_ITEM_STOCKS GROUP BY ITEM_ID, DEPOTS_CODE) ST ON C.ITEM_ID = ST.ITEM_ID AND ST.DEPOTS_CODE = A.FROM_DEPOTS_CODE where C.ITEM_OUT_ID = {0}",
|
query.id);
|
|
return Db.Ado.SqlQuery<MesInvItemOutItems>(sql);
|
}
|
|
private List<MesInvItemMovesCDetails> GetItemMovesCDetails(
|
WarehouseQuery query)
|
{
|
return Db.Queryable<MesInvItemMovesCDetails, MesItems, MesSupplier>(
|
(b, c, f) => new JoinQueryInfos(
|
JoinType.Left,
|
b.ItemNo == c.ItemNo && b.Company == c.Company &&
|
b.Factory == c.Factory,
|
JoinType.Left, b.SuppNo == f.SuppNo
|
))
|
.Where((b, c, f) => b.ItemMoveId == query.id)
|
.Select((b, c, f) => new MesInvItemMovesCDetails
|
{
|
FromDepotSectionsCode = b.FromDepotSectionsCode,
|
BoardStyle = b.BoardStyle,
|
FromDepotsCode = b.FromDepotsCode,
|
InvDepotSectionsCode = b.InvDepotSectionsCode,
|
InvDepotsCode = b.InvDepotsCode,
|
Quantity = b.Quantity,
|
LotNo = b.LotNo,
|
ItemNo = b.ItemNo,
|
ItemBarcode = b.ItemBarcode,
|
ItemName = c.ItemName,
|
ItemModel = c.ItemModel,
|
ItemUnit = c.ItemUnit,
|
SuppName = f.SuppName,
|
MoveOk =
|
SqlFunc.IsNull(b.MoveOk, 0) // 使用 SqlFunc.IsNull 实现 NVL 的效果
|
}).ToList();
|
}
|
|
public OutItemDto ScanMoveBarcode(WarehouseQuery query)
|
{
|
var BillTypeId = 300;
|
var TransctionNo = 301;
|
query.Factory = "1000";
|
query.Company = "1000";
|
|
if (string.IsNullOrEmpty(query.billNo)) throw new Exception("请选单据号！");
|
|
var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
|
.Where(s => s.ItemBarcode == query.barcode &&
|
s.Quantity > 0 && s.DepotsCode != null).First();
|
if (mesInvItemStocks == null)
|
throw new Exception("库存中无此条码，请核对！" + query.barcode);
|
|
var mesInvItemMoves = Db.Queryable<MesInvItemMoves>()
|
.Where(d => d.TransactionNo == TransctionNo
|
&& SqlFunc.IsNull(d.Status, 0) == 1).First();
|
|
if (mesInvItemMoves == null)
|
throw new Exception("出库单 " + query.billNo + "不存在，请确认！");
|
|
var count = Db.Queryable<MesInvItemMoves, MesInvItemMovesCDetails>(
|
(a, b) =>
|
new JoinQueryInfos(JoinType.Inner, a.Id == b.ItemMoveId))
|
.Where((a, b) => b.ItemBarcode == query.barcode
|
&& a.TransactionNo == TransctionNo &&
|
SqlFunc.IsNull(a.Status, 0) == 0).Count();
|
if (count > 0) throw new Exception("条码重复扫描，请核对！");
|
|
count = Db.Queryable<MesInvItemMoves, MesInvItemMovesCDetails>((a, b) =>
|
new JoinQueryInfos(JoinType.Inner, a.Id == b.ItemMoveId))
|
.Where((a, b) => b.ItemBarcode == query.barcode
|
&& a.TransactionNo == TransctionNo &&
|
SqlFunc.IsNull(b.MoveOk, 0) == 0).Count();
|
|
if (count > 0) throw new Exception("条码重复扫描，请核对！");
|
|
if (mesInvItemMoves.FromDepotsCode != mesInvItemStocks.DepotsCode)
|
throw new Exception("条码现存仓库与申请出货仓库不一致，请核对！");
|
|
var quantity = Db.Queryable<MesInvItemOutItems>()
|
.Where(b => b.ItemNo == mesInvItemStocks.ItemNo
|
&& b.ItemOutId == mesInvItemMoves.Id)
|
.Sum(b => b.Quantity);
|
|
if (quantity == null)
|
throw new Exception("扫码物料非本次申请物料，请核对！" + query.barcode);
|
|
var sum = Db.Queryable<MesInvItemMovesCDetails>()
|
.Where(b => b.ItemNo == mesInvItemStocks.ItemNo
|
&& b.ItemMoveId == mesInvItemMoves.Id)
|
.Sum(b => b.Quantity) ?? 0;
|
|
var sumqty = sum + (mesInvItemStocks.Quantity ?? 0);
|
|
if (sumqty > quantity)
|
throw new Exception(
|
"已扫条码数量或本次扫码数量：" + sumqty + " 大于申请数量：" + quantity + "，请核对！");
|
|
UseTransaction(db =>
|
{
|
SaveMesInvItemMovesCDetails(db, query, mesInvItemMoves,
|
mesInvItemStocks);
|
|
// Insert into mes_inv_business2
|
SaveMesInvBusiness2(db, query, BillTypeId, TransctionNo,
|
mesInvItemStocks, mesInvItemMoves);
|
|
if (TransctionNo == 303)
|
db.Updateable<MesInvItemStocks>()
|
.SetColumns(s =>
|
s.DepotSectionsCode == mesInvItemMoves.InvDepotsCode)
|
.SetColumns(s =>
|
s.DepotsCode == mesInvItemMoves.InvDepotsCode)
|
.Where(s => s.Id == mesInvItemStocks.Id)
|
.ExecuteCommand();
|
else
|
db.Updateable<MesInvItemStocks>()
|
.SetColumns(s => s.DepotSectionsCode == null)
|
.SetColumns(s => s.DepotsCode == null)
|
.Where(s => s.Id == mesInvItemStocks.Id)
|
.ExecuteCommand();
|
|
var scanOutShowDb = ScanOutShowDb(query);
|
if (CollectionUtil.IsNullOrEmpty(scanOutShowDb))
|
db.Updateable<MesInvItemMoves>()
|
.SetColumns(s => s.Status == 1)
|
.SetColumns(s => s.Checkdate == DateTime.Now)
|
.SetColumns(s => s.Checkuser == query.userName)
|
.Where(s => s.BillNo == query.billNo).ExecuteCommand();
|
|
return 1;
|
});
|
|
var itemDto = new OutItemDto();
|
//itemDto.SumItem = scanOutShowDb;
|
|
if (TransctionNo == 201) itemDto.Quantity = mesInvItemStocks.Quantity;
|
|
itemDto.ItemNo = mesInvItemStocks.ItemNo;
|
|
return itemDto;
|
}
|
|
private void SaveMesInvBusiness2(SqlSugarScope Db, WarehouseQuery query,
|
int BillTypeId,
|
int TransctionNo, MesInvItemStocks mesInvItemStocks,
|
MesInvItemMoves mesInvItemMoves)
|
{
|
var executeCommand = Db.Insertable(new MesInvBusiness2
|
{
|
Status = 1,
|
BillTypeId = BillTypeId,
|
TransactionCode = TransctionNo.ToString(),
|
BusinessType = 1,
|
ItemBarcode = query.barcode,
|
ItemNo = mesInvItemStocks.ItemNo,
|
LotNo = mesInvItemStocks.LotNo,
|
EpFlag = 1,
|
Quantity = mesInvItemStocks.Quantity,
|
FromInvDepotsCode = mesInvItemStocks.DepotsCode,
|
FromInvDepotSectionsCode = mesInvItemStocks.DepotSectionsCode,
|
ToInvDepotsCode = TransctionNo == 303
|
? mesInvItemMoves.InvDepotsCode
|
: null,
|
ToInvDepotSectionsCode = TransctionNo == 303
|
? mesInvItemMoves.InvDepotsCode
|
: null,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
Factory = query.Factory,
|
Company = query.Company,
|
TaskNo = mesInvItemStocks.TaskNo,
|
BillNo = query.billNo,
|
WorkNo = mesInvItemStocks.WorkNo,
|
WorkLine = mesInvItemStocks.WorkLine,
|
SuppNo = mesInvItemStocks.SuppNo
|
}).ExecuteCommand();
|
|
if (executeCommand <= 0) throw new Exception("写入MesInvBusiness2表失败");
|
}
|
|
private void SaveMesInvItemMovesCDetails(SqlSugarScope Db,
|
WarehouseQuery query,
|
MesInvItemMoves mesInvItemMoves, MesInvItemStocks mesInvItemStocks)
|
{
|
var executeCommand = Db.Insertable(new MesInvItemMovesCDetails
|
{
|
ItemMoveId = mesInvItemMoves.Id,
|
ItemBarcode = query.barcode,
|
CItemCode = mesInvItemStocks.CItemCode,
|
ItemNo = mesInvItemStocks.ItemNo,
|
LotNo = mesInvItemStocks.LotNo,
|
Quantity = mesInvItemStocks.Quantity,
|
EpFlag = mesInvItemStocks.EpFlag,
|
CreateBy = query.userName,
|
CreateDate = DateTime.Now,
|
LastupdateBy = query.userName,
|
LastupdateDate = DateTime.Now,
|
CustNo = mesInvItemStocks.CustomerNo,
|
TaskNo = mesInvItemStocks.TaskNo,
|
FromDepotsCode = mesInvItemStocks.DepotsCode,
|
FromDepotSectionsCode = mesInvItemStocks.DepotSectionsCode,
|
Factory = mesInvItemStocks.Factory,
|
Company = mesInvItemStocks.Company,
|
IqcStatus = mesInvItemStocks.IqcStatus,
|
Fcar = mesInvItemStocks.Fcar,
|
IndepDate = mesInvItemStocks.IndepDate,
|
VisableSubmit = mesInvItemStocks.VisableSubmit,
|
VisableSubmitBy = mesInvItemStocks.VisableSubmitBy,
|
VisableSubmitDate = mesInvItemStocks.VisableSubmitDate,
|
BoardStyle = mesInvItemStocks.BoardStyle,
|
WorkNo = mesInvItemStocks.WorkNo,
|
WorkLine = mesInvItemStocks.WorkLine,
|
SuppNo = mesInvItemStocks.SuppNo
|
}).ExecuteCommand();
|
|
if (executeCommand <= 0)
|
throw new Exception("写入MesInvItemMovesCDetails表失败");
|
}
|
|
//scan_out_show_DB
|
private List<InventoryItem> ScanOutShowDb(WarehouseQuery query)
|
{
|
var BillTypeId = 300;
|
var TransctionNo = 301;
|
query.Factory = "1000";
|
query.Company = "1000";
|
|
if (string.IsNullOrEmpty(query.billNo)) throw new Exception("请选单据号！");
|
|
var sql = string.Format(
|
"select f_get_sections_code({0}, {1}, b.item_no) sections_code , s.erp_item_no , S.ITEM_MODEL , to_char(nvl(b.quantity, 0) - nvl(quantity_ok, 0), 'FM9999999990.00') flist from mes_inv_item_moves a join mes_inv_item_out_items b on b.item_out_id = a.id join mes_items s on b.item_no = s.item_no left join (select ITEM_MOVE_ID, item_no, sum(quantity) quantity_ok from MES_INV_ITEM_MOVES_C_DETAILS group by ITEM_MOVE_ID, item_no) c on c.ITEM_MOVE_ID = a.id and b.item_no = c.item_no where 1 = 1 and a.bill_type_id = {2} and a.transaction_no = {3} and a.bill_no = {4} and nvl(b.quantity, 0) - nvl(quantity_ok, 0) > 0 and rownum < 500 AND A.STATUS = 1 order by f_get_sections_code({0}, {1}, b.item_no), s.erp_item_no ",
|
query.Factory, query.Company, BillTypeId, TransctionNo,
|
query.billNo);
|
|
var results = Db.Ado.SqlQuery<InventoryItem>(sql);
|
|
return results;
|
}
|
|
public bool Audit(WarehouseQuery query)
|
{
|
return Db.Updateable<MesInvItemMoves>()
|
.SetColumns(a => new MesInvItemMoves { Ts = 1 })
|
.Where(a => a.Id == query.id)
|
.ExecuteCommand() > 0;
|
}
|
|
public MessageCenter SaveMessageCenter(WarehouseQuery query)
|
{
|
var message = MesToErpParam(query);
|
|
var executeReturnIdentity =
|
Db.Insertable(message).ExecuteReturnIdentity();
|
if (executeReturnIdentity > 0)
|
{
|
message.Id = executeReturnIdentity;
|
message.Pid = executeReturnIdentity;
|
return message;
|
}
|
|
throw new Exception("获取数据失败");
|
}
|
|
public MessageCenter MesToErpParam(WarehouseQuery query)
|
{
|
var erpParameters = "";
|
var title = "";
|
var tableName = "MES_INV_ITEM_MOVES_" + query.Type;
|
if ("A".Equals(query.Type))
|
{
|
erpParameters = GetErpParameters(query.billNo);
|
title = "调拨入库单" + query.billNo + "审核";
|
}
|
else if ("B".Equals(query.Type))
|
{
|
erpParameters = GetDeApprovePam(query.id);
|
title = "调拨入库单" + query.billNo + "反审核";
|
}
|
|
var ErpUrl = AppsettingsUtility.Settings.ProductionErpUrl;
|
var message = new MessageCenter
|
{
|
TableName = tableName,
|
Url = ErpUrl,
|
Status = 1,
|
CreateBy = query.userName,
|
Route = query.billNo,
|
Title = title,
|
PageName = "Allocation/Add?id=" + query.id +
|
"&billNo=" + query.billNo,
|
CreateDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
|
Method = "POST",
|
Seq = 1,
|
Data = erpParameters,
|
IsMessage = 0,
|
ContentType = "application/x-www-form-urlencoded"
|
};
|
return message;
|
}
|
|
private string GetErpParameters(string? billNo)
|
{
|
// var invItemIns = Db.Queryable<MesInvItemOuts>()
|
// .Single(x => x.ItemOutNo == billNo);
|
//
|
// //调用function函数
|
// var sql =
|
// $"SELECT F_GENERATE_DATA_INSERTED('{billNo}') FROM DUAL;";
|
// var jsonString = Db.Ado.SqlQuerySingle<string>(sql);
|
//
|
// var encodedUrl = "taskname=CGTL&mesid=" + invItemIns.Id +
|
// "&optype=create&datajson=" + jsonString;
|
//
|
// return encodedUrl;
|
return null;
|
}
|
|
private string GetDeApprovePam(decimal? id)
|
{
|
// var sid = (int)id;
|
// var encodedUrl = "taskname=CGTL&mesid=" + sid +
|
// "&optype=delete&datajson={}";
|
//
|
// return encodedUrl;
|
return null;
|
}
|
}
|
