using NewPdaSqlServer.DB;
|
using NewPdaSqlServer.Dto.service;
|
using NewPdaSqlServer.entity;
|
using NewPdaSqlServer.util;
|
using Newtonsoft.Json;
|
using SqlSugar;
|
|
namespace NewPdaSqlServer.service.Warehouse;
|
|
public class MesInvItemOutsManager : Repository<MesInvItemOuts>
|
{
|
public List<MesInvItemOuts> GetPage(WarehouseQuery query)
|
{
|
var parsedGuid = Guid.Empty;
|
if (!string.IsNullOrEmpty(query.id))
|
{
|
var isValid = Guid.TryParse(query.id, out parsedGuid);
|
if (!isValid)
|
throw new ApplicationException("GUID转换错误");
|
}
|
|
return
|
Db.Queryable<MesInvItemOuts, MesDepots, MesSupplier, SysUser,
|
SysUser>((a, e, i, u1, u3) =>
|
new JoinQueryInfos(
|
JoinType.Left,
|
a.DepotId == e.DepotId,
|
JoinType.Left,
|
a.SuppId == i.Id,
|
JoinType.Left,
|
a.CreateBy == u1.Account,
|
JoinType.Left,
|
a.CheckUser == u3.Account))
|
.WhereIF(UtilityHelper.CheckGuid(parsedGuid),
|
(a, e, i, u1, u3) => a.Guid == parsedGuid)
|
.WhereIF(!string.IsNullOrEmpty(query.billNo),
|
(a, e, i, u1, u3) => a.ItemOutNo == query.billNo)
|
.Select((a, e, i, u1, u3) => new MesInvItemOuts
|
{
|
Guid = a.Guid,
|
DepotCode = e.DepotCode,
|
Fmrmode = a.Fmrmode,
|
CheckDate = a.CheckDate,
|
CheckUser = a.CheckUser,
|
States = a.States,
|
Reason = a.Reason,
|
Remark = a.Remark,
|
Nflag = a.Nflag,
|
FType = a.FType,
|
SuppNo = i.SuppNo,
|
DepotName = e.DepotName,
|
SuppName = i.SuppName,
|
CreateByFname = u1.Account,
|
CheckUserFname = u3.Account,
|
CreateDate = a.CreateDate,
|
CreateBy = a.CreateBy,
|
Status = a.Status,
|
ItemOutNo = a.ItemOutNo
|
}).ToPageList(query.PageIndex, query.Limit);
|
}
|
|
public List<MesInvItemOutItems> GetItems(WarehouseQuery query)
|
{
|
var parsedGuid = Guid.Empty;
|
if (string.IsNullOrEmpty(query.id)) return [];
|
|
var isValid = Guid.TryParse(query.id, out parsedGuid);
|
if (!isValid)
|
throw new ApplicationException("GUID转换错误");
|
|
var mesInvItemOutItemsList = Db.Queryable<MesInvItemOutItems, MesItems>(
|
(c, s) => new object[]
|
{
|
JoinType.Inner, c.ItemId == s.Id
|
}).Where((c, s) => c.ItemOutId == parsedGuid)
|
.Select<MesInvItemOutItems>((c, s) => new MesInvItemOutItems
|
{
|
Guid = c.Guid,
|
TlQty = c.TlQty,
|
WorkLine = c.WorkLine,
|
PbillNo = c.PbillNo,
|
LineK3id = c.LineK3id,
|
DepotCode = c.DepotCode,
|
Status = c.Status,
|
Remark = c.Remark,
|
EbelnK3id = c.EbelnK3id,
|
Quantity = c.Quantity,
|
ItemNo = s.ItemNo,
|
ItemName = s.ItemName,
|
ItemModel = s.ItemModel,
|
ItemId = c.ItemId,
|
RkNo = c.RkNo,
|
WorkNo = c.WorkNo
|
}).ToList();
|
|
foreach (var mesInvItemOutItemse in mesInvItemOutItemsList)
|
{
|
var u = Db.Queryable<MesInvItemOutItems>()
|
.GroupBy(it => new
|
{ it.ItemId, it.PbillNo, it.WorkLine, it.RkNo })
|
.Where(it => it.ItemId == mesInvItemOutItemse.ItemId
|
// && it.PbillNo == mesInvItemOutItemse.PbillNo
|
// && it.WorkLine == mesInvItemOutItemse.WorkLine
|
&& it.RkNo == mesInvItemOutItemse.RkNo)
|
.Select(it => new
|
{
|
KtQty = SqlFunc.AggregateSum(it.Quantity ?? 0)
|
}).First();
|
|
var s1 = Db.Queryable<MesInvItemInCItems>()
|
.GroupBy(it => new
|
{ it.ItemId, it.Ebeln, it.WorkLine, it.BillNo })
|
.Where(it => it.ItemId == mesInvItemOutItemse.ItemId
|
&& it.CbillNo == mesInvItemOutItemse.PbillNo
|
// && it.WorkLine == mesInvItemOutItemse.WorkLine
|
// && it.BillNo == mesInvItemOutItemse.RkNo
|
)
|
.Select(it => new
|
{
|
RkQty = SqlFunc.AggregateSum(it.Quantity ?? 0)
|
}).First();
|
|
mesInvItemOutItemse.KtQty = s1.RkQty - u.KtQty;
|
}
|
|
return mesInvItemOutItemsList;
|
}
|
|
public OutItemDto SaveCombination(OutItemDto dto)
|
{
|
var mesInvItemOuts = dto.Form;
|
mesInvItemOuts.Status = 0;
|
mesInvItemOuts.BoardFlag = false;
|
mesInvItemOuts.Sapstatus = false;
|
mesInvItemOuts.OutStatus = false;
|
mesInvItemOuts.ItemFlag = false;
|
mesInvItemOuts.IsVisual = true;
|
mesInvItemOuts.TransactionNo = 203;
|
mesInvItemOuts.Factory = "1000";
|
mesInvItemOuts.Company = "1000";
|
|
var newGuid = Guid.NewGuid();
|
|
mesInvItemOuts.Guid = newGuid;
|
|
//返回ID
|
var command = Db.Insertable(mesInvItemOuts)
|
.IgnoreColumns(true).ExecuteCommand();
|
|
if (command <= 0) throw new Exception("插入错误");
|
|
dto.SaveItems.ForEach(s =>
|
{
|
s.ItemOutId = newGuid;
|
s.Factory = "1000";
|
s.Company = "1000";
|
s.Status = 0;
|
s.DepotCode = mesInvItemOuts.DepotCode;
|
s.FType = mesInvItemOuts.FType;
|
s.CreateBy = mesInvItemOuts.CreateBy;
|
s.CreateDate = mesInvItemOuts.CreateDate;
|
});
|
|
var insertable = Db.Insertable(dto.SaveItems)
|
.IgnoreColumns(true).ExecuteCommand();
|
|
if (insertable < 0) throw new Exception("插入错误");
|
|
var query = new WarehouseQuery();
|
query.id = newGuid.ToString();
|
dto.SelectItems = GetItems(query);
|
|
return dto;
|
}
|
|
|
public List<CgtlView> SelectMaterials(WarehouseQuery query)
|
{
|
return Db.Queryable<CgtlView>()
|
.Where(s => s.SuppId == query.SuppId)
|
.ToList();
|
}
|
|
private void Validate(WarehouseQuery query)
|
{
|
// 校验单号是否正确
|
var mesInvItemOuts = Db.Queryable<MesInvItemOuts>()
|
.Where(o => o.ItemOutNo == query.billNo)
|
.Single();
|
|
if (mesInvItemOuts == null)
|
throw new Exception("没找到" + query.billNo + " 对应的采购退货单，请检查");
|
|
// 校验子表数据
|
var itemOutItems = Db.Queryable<MesInvItemOutItems>()
|
.Where(o => o.ItemOutId == mesInvItemOuts.Guid)
|
.ToList();
|
|
var sql = string.Empty;
|
var P_Msg = string.Empty;
|
|
foreach (var item in itemOutItems)
|
{
|
// 判断申请数量是否大于剩余可退数量
|
sql = string.Format(
|
@"SELECT isnull(s1.RK_QTY, 0) - isnull(KT_QTY, 0) KT_QTY
|
FROM MES_INV_ITEM_OUT_ITEMS C
|
LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY,
|
ITEM_NO,
|
PBILL_NO,
|
RK_NO,
|
WORK_LINE
|
FROM MES_INV_ITEM_OUT_ITEMS
|
GROUP BY ITEM_NO, PBILL_NO, WORK_LINE,RK_NO) U
|
ON U.ITEM_NO = C.ITEM_NO
|
AND U.WORK_LINE = C.WORK_LINE
|
AND U.PBILL_NO = C.PBILL_NO
|
AND U.RK_NO = C.RK_NO
|
LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY,
|
ITEM_NO,
|
EBELN,
|
WORK_LINE,BILL_NO
|
FROM MES_INV_ITEM_IN_C_ITEMS
|
GROUP BY ITEM_NO, EBELN, WORK_LINE,BILL_NO) s1
|
ON S1.ITEM_NO = C.ITEM_NO
|
AND S1.WORK_LINE = C.WORK_LINE
|
AND S1.EBELN = C.PBILL_NO
|
AND S1.BILL_NO = C.RK_NO
|
WHERE C.guid = '{0}'", item.Guid);
|
var sy_Qty = Db.Ado.SqlQuery<decimal>(sql);
|
|
if (sy_Qty != null && sy_Qty[0] < 0)
|
{
|
P_Msg =
|
$"采购订单：{item.PbillNo}，行：{item.WorkLine}，物料：{item.ItemNo} 申请数量大于剩余可收数量";
|
throw new Exception(P_Msg);
|
}
|
|
// 判断行是否重复
|
var cf_Num = Db.Queryable<MesInvItemOutItems>()
|
.Where(s1 => s1.ItemNo == item.ItemNo &&
|
s1.WorkLine == item.WorkLine &&
|
s1.PbillNo == item.PbillNo &&
|
s1.ItemOutId == mesInvItemOuts.Guid &&
|
s1.RkNo == item.RkNo)
|
.Count();
|
|
if (cf_Num <= 1) continue;
|
P_Msg =
|
$"采购订单：{item.PbillNo}，行：{item.WorkLine}，物料：{item.ItemNo}重复请删除其中一条数据";
|
throw new Exception(P_Msg);
|
}
|
|
// 校验主表内容是否被删掉
|
// 主表仓库编码
|
if (string.IsNullOrEmpty(mesInvItemOuts.DepotCode))
|
{
|
P_Msg = "仓库编码为空，请检查";
|
throw new Exception(P_Msg);
|
}
|
|
var supplierExists = Db.Queryable<MesSupplier>()
|
.Any(supplier => supplier.SuppNo == mesInvItemOuts.SuppNo);
|
|
if (!supplierExists)
|
{
|
P_Msg = $"供应商编码 {mesInvItemOuts.SuppNo} 在系统中不存在，请检查";
|
throw new Exception(P_Msg);
|
}
|
|
// 子表行与主表符合性校验
|
foreach (var j in itemOutItems)
|
{
|
// 校验子表对应采购订单的供应商编码
|
var c_SuppNo = Db.Queryable<MesRohIn, MesSupplier>(
|
(roh, supp) => new JoinQueryInfos(
|
JoinType.Left,
|
roh.Supplier == supp.Id.ToString()
|
))
|
.Where(roh => roh.BillNo == j.WorkNo)
|
.Select((roh, supp) => supp.SuppNo)
|
.Single();
|
|
if (c_SuppNo == null)
|
{
|
P_Msg = $"子表行供应商编码 {c_SuppNo} 在系统中未找到";
|
throw new Exception(P_Msg);
|
}
|
|
if (mesInvItemOuts.SuppNo != c_SuppNo)
|
{
|
P_Msg =
|
$"子表采购订单行的供应商编码 {c_SuppNo} 与主表供应商编码 {mesInvItemOuts.SuppNo} 不一致，请检查";
|
throw new Exception(P_Msg);
|
}
|
|
// 校验子表委外采购类型和主表类型是否一致
|
if (j.FType != mesInvItemOuts.FType)
|
{
|
P_Msg =
|
$"子表委外类型：{j.FType}，与主表委外类型：{mesInvItemOuts.FType}，不一致，请检查";
|
throw new Exception(P_Msg);
|
}
|
|
// 检查子表仓库是否和主表仓库一致
|
if (j.DepotCode == mesInvItemOuts.DepotCode) continue;
|
P_Msg =
|
$"子表仓库编码：{j.DepotCode} 与主表仓库编码：{mesInvItemOuts.DepotCode}，不一致，请检查";
|
throw new Exception(P_Msg);
|
}
|
}
|
|
public bool DeApproveBefore(WarehouseQuery query)
|
{
|
var mesInvItemOuts = base.GetById(query.id);
|
if (mesInvItemOuts == null) throw new Exception("出库单不存在");
|
|
var nflag = mesInvItemOuts.Nflag ?? 0;
|
|
return nflag != 0;
|
}
|
|
public bool Audit(WarehouseQuery query)
|
{
|
// 审核前校验
|
Validate(query);
|
query.status = 1;
|
//审核
|
return Update(query);
|
}
|
|
private bool Update(WarehouseQuery entity)
|
{
|
var parsedGuid = Guid.Empty;
|
if (!string.IsNullOrEmpty(entity.id))
|
{
|
var isValid = Guid.TryParse(entity.id, out parsedGuid);
|
if (!isValid)
|
throw new ApplicationException("GUID转换错误");
|
}
|
|
if (!UtilityHelper.CheckGuid(parsedGuid))
|
{
|
throw new ApplicationException("更新错误，数据是不合法的");
|
}
|
|
var userName = entity.userName ?? "system";
|
return Db.Updateable<MesInvItemOuts>()
|
.SetColumns(s => s.Status == entity.status)
|
.SetColumns(s => s.CheckUser == userName)
|
.SetColumns(s => s.CheckDate == DateTime.Now)
|
.Where(s => s.Guid == parsedGuid).ExecuteCommand() > 0;
|
}
|
|
|
public string GetMaxItemOutNo()
|
{
|
var date = DateTime.Now.ToString("yyyyMMdd");
|
var par = "NTL" + date;
|
var sql =
|
$"select max(ITEM_OUT_NO) from MES_INV_ITEM_OUTS where ITEM_OUT_NO like '{par}%'";
|
var maxBillNo = Db.Ado.SqlQuerySingle<string>(sql);
|
|
var number = "0001";
|
if (maxBillNo != null)
|
{
|
maxBillNo = maxBillNo.Substring(11);
|
var no = Convert.ToInt32(maxBillNo);
|
no++;
|
number = no.ToString().PadLeft(4, '0');
|
}
|
|
return "NTL" + date + number;
|
}
|
|
public MessageCenter SaveMessageCenter(WarehouseQuery query)
|
{
|
var message = MesToErpParam(query);
|
|
var newGuid = Guid.NewGuid();
|
message.Guid = newGuid;
|
message.Pid = newGuid;
|
var executeReturnIdentity =
|
Db.Insertable(message).IgnoreColumns(true).ExecuteCommand();
|
if (executeReturnIdentity > 0)
|
{
|
return message;
|
}
|
|
throw new Exception("获取数据失败");
|
}
|
|
public List<MesInvItemOuts> GetItemOutNo()
|
{
|
var data5 = Db.Queryable<MesInvItemOuts>().Where(it =>
|
it.BillTypeId == 200 && it.TransactionNo == 203 &&
|
it.Status == 1)
|
.ToPageList(1, 20);
|
|
var ItemOutNos = data5.FindAll(a => a.Nflag == null || a.Nflag == 0)
|
.ToList();
|
|
return ItemOutNos;
|
}
|
|
public OutItemDto GetSumItem(WarehouseQuery query)
|
{
|
var mesInvItemOuts = base.GetSingle(it => it.ItemOutNo == query.billNo);
|
if (mesInvItemOuts == null) throw new Exception("采购退货单不存在");
|
|
|
var dto = new OutItemDto();
|
dto.SumItem = GetItems(query);
|
|
var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
|
.Where(a => a.ItemBarcode == query.barcode).Single();
|
|
if (mesInvItemStocks == null) return dto;
|
|
var mesItems = Db.Queryable<MesItems>()
|
.Where(s => s.Id == mesInvItemStocks.ItemId).Single();
|
|
dto.ItemNo = mesItems.ItemNo;
|
dto.Quantity = mesInvItemStocks.Quantity;
|
|
return dto;
|
}
|
|
public List<MesInvItemOutCDetails> GetScanBarcode(WarehouseQuery query)
|
{
|
//string转guid
|
var parsedGuid = Guid.Empty;
|
if (string.IsNullOrEmpty(query.id)) return [];
|
|
var isValid = Guid.TryParse(query.id, out parsedGuid);
|
if (!isValid)
|
throw new ApplicationException("GUID转换错误");
|
|
var mesInvItemOutCDetailsList = Db
|
.Queryable<MesInvItemOutCDetails, MesInvItemBarcodes, MesItems,
|
MesDepots>(
|
(b, bar, c, d) => new
|
JoinQueryInfos(
|
JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
|
JoinType.Left, b.ItemId == c.Id,
|
JoinType.Left, b.DepotId == d.DepotId
|
))
|
.Where((b, bar, c, d) => b.ItemOutId == parsedGuid)
|
.Select((b, bar, c, d) => new MesInvItemOutCDetails
|
{
|
Guid = b.Guid,
|
Quantity = b.Quantity,
|
DepotSectionCode = b.DepotSectionCode,
|
DepotCode = b.DepotCode,
|
ItemNo = b.ItemNo,
|
ItemBarcode = b.ItemBarcode,
|
ItemName = c.ItemName,
|
ItemModel = c.ItemModel,
|
DepotName = d.DepotName,
|
Unit = bar.Unit
|
}).ToList();
|
|
return mesInvItemOutCDetailsList;
|
}
|
|
public MessageCenter MesToErpParam(WarehouseQuery query)
|
{
|
var erpParameters = "";
|
var title = "";
|
var tableName = "MES_INV_ITEM_OUTS_" + query.Type;
|
if ("A".Equals(query.Type))
|
{
|
erpParameters = GetErpParameters(query.id);
|
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 = "Warehouse/PurchaseReturn/Add?id=" + query.id +
|
"&itemOutNo=" + 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? id)
|
{
|
var guid = Guid.Empty;
|
if (string.IsNullOrEmpty(id)) return "";
|
|
var isValid = Guid.TryParse(id, out guid);
|
if (!isValid)
|
throw new ApplicationException("GUID转换错误");
|
|
var materials = Db
|
.Queryable<MesInvItemOuts, MesInvItemOutItems,
|
MesItems, MesUnit, MesInvItemArnDetail, MesDepots>(
|
(a, b, c, d, e, f) => new JoinQueryInfos(
|
JoinType.Left, a.Guid == b.ItemOutId,
|
JoinType.Left, b.ItemId == c.Id,
|
JoinType.Left, d.Id.ToString() == c.ItemUnit,
|
JoinType.Left,
|
e.CbillNo == b.WorkNo && e.ItemId == b.ItemId,
|
JoinType.Left, f.DepotId == a.DepotId
|
)).Where((a, b, c, d, e, f) => b.Guid == guid)
|
.Select<Material>((a, b, c, d, e, f) => new Material
|
{
|
FMaterialId = c.ItemNo,
|
FRMREALQTY = b.Quantity.ToString(),
|
FStockId = f.DepotCode,
|
FUnitID = d.Fnumber,
|
FLot = b.WorkNo,
|
F_MES_ENTRYID = b.Guid,
|
FsrcEntryId = e.Guid.ToString(),
|
}).ToList();
|
|
|
var mm = Db.Queryable<MesInvItemOuts>()
|
.Where(a => a.Guid == guid).First();
|
|
var jsonEntries = materials.Select(d => new
|
{
|
FMaterialId = d.FMaterialId,
|
FRMREALQTY = d.FRMREALQTY,
|
FStockId = d.FStockId,
|
FUnitID = d.FUnitID,
|
FLot = d.FLot,
|
F_MES_ENTRYID = d.F_MES_ENTRYID.ToString(),
|
FsrcEntryId = d.FsrcEntryId,
|
}).ToList();
|
var fdate = DateTime.Now.ToString("yyyy-MM-dd");
|
var jsonString = JsonConvert.SerializeObject(jsonEntries);
|
var encodedUrl = "taskname=CGTL&mesid=" + guid +
|
"&optype=create&datajson={\"F_MES_ID\":\"" + guid
|
+ "\",\"FDate\":\"" + fdate
|
+ "\",\"FSRCBillTypeId\":\"" + "采购入库单"
|
+ "\",\"FDESCRIPTION\":\"" + mm.Remark
|
+ "\",\"FMRMODE\":\"" + mm.Fmrmode
|
+ "\",\"cgtlentry\":" + jsonString
|
+ "}";
|
return encodedUrl;
|
}
|
|
private string GetDeApprovePam(string? id)
|
{
|
var encodedUrl = "taskname=CGTL&mesid=" + id +
|
"&optype=delete&datajson={}";
|
|
return encodedUrl;
|
}
|
|
public bool ScanCode(WarehouseQuery query)
|
{
|
decimal billTypeId = 200;
|
decimal transactionNo = 203;
|
|
var count = Db.Queryable<MesInvItemOutCDetails>()
|
.Where(a =>
|
a.ItemBarcode == query.barcode && a.TaskNo == query.billNo)
|
.Count();
|
|
if (count > 0) throw new Exception("条码重复扫描，请核对！");
|
|
//验证条码
|
var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
|
.Where(a => a.ItemBarcode == query.barcode
|
&& a.Quantity > 0).Single();
|
|
if (mesInvItemStocks == null)
|
throw new Exception(query.barcode + "库存中无此条码，请核对！");
|
|
//验证出库单mes_inv_item_outs
|
var mesInvItemOuts = Db.Queryable<MesInvItemOuts>()
|
.Where(d => d.ItemOutNo == query.billNo
|
&& d.BillTypeId == billTypeId &&
|
d.TransactionNo == transactionNo).Single();
|
|
//出库单的校验
|
if (mesInvItemOuts == null)
|
throw new Exception("出库单 " + query.billNo + " 不存在，请确认！");
|
|
//未审核的不允许继续
|
if (mesInvItemOuts.Status == null || mesInvItemOuts.Status == 0)
|
throw new Exception("出库单 " + query.billNo + "未审核");
|
|
//已推送的不允许继续
|
if (mesInvItemOuts.Nflag == 1)
|
throw new Exception("出库单 " + query.billNo + "已经推送，无法重复推送");
|
|
var depotCodeOut = mesInvItemOuts.DepotCode ?? "0";
|
var depotsCodeStock = mesInvItemStocks.DepotsCode ?? "0";
|
|
if (!depotCodeOut.Equals(depotsCodeStock))
|
{
|
// 创建错误消息
|
var errorMessage =
|
$"002[条码仓库{depotsCodeStock}与申请仓库{depotCodeOut}不一致，请核对！";
|
|
throw new Exception(errorMessage);
|
}
|
|
var StocksNum = mesInvItemStocks.Quantity;
|
|
var sql = string.Format(
|
@"select isnull(SUM(S.QUANTITY),0) - isnull(SUM(S.TL_QTY),0)
|
from MES_INV_ITEM_OUT_ITEMS S
|
LEFT JOIN mes_inv_item_outs d
|
ON S.ITEM_OUT_ID = D.GUID
|
where d.item_out_no = '{0}'
|
and d.bill_type_id = {1}
|
and d.transaction_no = {2}
|
AND S.item_ID = '{3}'
|
and isnull(d.status, 0) = 1
|
and isnull(d.nflag, 0) = 0", query.billNo, billTypeId,
|
transactionNo,
|
mesInvItemStocks.ItemId);
|
|
var cqty = Db.Ado.SqlQuerySingle<decimal>(sql);
|
|
if (cqty == null)
|
throw new Exception("物料" + mesInvItemStocks.ItemNo +
|
"的辅助属性与条码不一致！请检查");
|
|
if (cqty < StocksNum) throw new Exception("请确认发料数量");
|
|
//调用存储过程
|
var prcRfPdaBarcodeCgth = PrcRfPdaBarcodeCgth(query);
|
if (!prcRfPdaBarcodeCgth)
|
{
|
throw new Exception("执行失败");
|
}
|
|
// 变量用于保存查询结果
|
decimal? C_SQ_QTY = 0;
|
decimal? C_OK_QTY = 0;
|
var C_COUNT = 0;
|
|
sql = string.Format(@"
|
SELECT SUM(C.QUANTITY) AS SQ_QTY, SUM(D.QUANTITY_OK) AS OK_QTY
|
FROM MES_INV_ITEM_OUT_ITEMS C
|
LEFT JOIN (SELECT ITEM_OUT_ID, ITEM_ID, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
|
FROM MES_INV_ITEM_OUT_C_DETAILS
|
GROUP BY ITEM_OUT_ID, ITEM_ID, PBILL_NO, RK_NO) D
|
ON D.ITEM_OUT_ID = C.ITEM_OUT_ID
|
AND D.ITEM_ID = C.ITEM_ID
|
AND isnull(C.PBILL_NO, '0') = isnull(D.PBILL_NO, '0')
|
WHERE C.ITEM_OUT_ID = '{0}'",
|
mesInvItemOuts.Guid);
|
|
var queryResult = Db.Ado.SqlQuerySingle<dynamic>(sql);
|
|
C_SQ_QTY = queryResult?.SQ_QTY ?? 0;
|
C_OK_QTY = queryResult?.OK_QTY ?? 0;
|
|
// 判断逻辑
|
if (C_SQ_QTY < C_OK_QTY) throw new Exception("扫描数量超过申请数量,请重新扫描！");
|
|
return C_SQ_QTY == C_OK_QTY;
|
}
|
|
private bool PrcRfPdaBarcodeCgth(WarehouseQuery query)
|
{
|
var c_user = query.userName;
|
var p_bill_no = query.billNo;
|
var p_item_barcode = query.barcode;
|
var p_bill_type_id = 200;
|
var p_transaction_no = 203;
|
int c_num;
|
decimal C_STOCKNUM, C_SQTY, C_OKQTY, C_CQTY;
|
|
var commit = UseTransaction(db =>
|
{
|
if (string.IsNullOrEmpty(p_bill_no))
|
{
|
throw new Exception("请选取单据号！");
|
}
|
|
// Retrieve mes_inv_item_outs
|
var c_mes_inv_item_outs = db.Queryable<MesInvItemOuts>()
|
.Where(d => d.ItemOutNo == p_bill_no)
|
.First();
|
|
if (c_mes_inv_item_outs == null)
|
{
|
throw new Exception("请选择或扫描正确的采购退货单！");
|
}
|
|
if (c_mes_inv_item_outs.Status == 0)
|
{
|
throw new Exception("采购退货申请单未审核！");
|
}
|
|
if (c_mes_inv_item_outs.Nflag == 1)
|
{
|
throw new Exception("采购退货单已完结推送ERP,不能操作！");
|
}
|
|
// Check for duplicate barcode scan
|
c_num = db.Queryable<MesInvItemOutCDetails>()
|
.Where(b =>
|
b.ItemBarcode == p_item_barcode &&
|
b.ItemOutId == c_mes_inv_item_outs.Guid)
|
.Count();
|
|
if (c_num > 0)
|
{
|
throw new Exception("此条码已采购退货扫码完成！");
|
}
|
|
// Query item stock info
|
var c_mes_inv_item_stocks = db.Queryable<MesInvItemStocks>()
|
.Where(t => t.ItemBarcode == p_item_barcode && t.Quantity > 0)
|
.First();
|
|
if (c_mes_inv_item_stocks == null)
|
{
|
throw new Exception($"库存中无此条码，请核对！{p_item_barcode}");
|
}
|
|
// Validate depot codes
|
if (c_mes_inv_item_outs.DepotCode !=
|
c_mes_inv_item_stocks.DepotsCode)
|
{
|
throw new Exception(
|
$"此条码属于仓库 {c_mes_inv_item_stocks.DepotsCode} 与采购退货申请仓库 {c_mes_inv_item_outs.DepotCode} 不一致，请核对！");
|
}
|
|
C_STOCKNUM = c_mes_inv_item_stocks.Quantity.Value;
|
|
// Calculate the total and completed quantities for the item
|
var quantities = db.Queryable<MesInvItemOutItems, MesInvItemOuts>(
|
(s, d) => new object[]
|
{
|
JoinType.Left, s.ItemOutId == d.Guid,
|
})
|
.Where((s, d) =>
|
d.ItemOutNo == p_bill_no &&
|
s.ItemId == c_mes_inv_item_stocks.ItemId
|
&& d.Status == 1
|
&& (d.Nflag == 0 || d.Nflag == null))
|
.Select((s, d) => new
|
{
|
TotalQuantity = SqlFunc.AggregateSum(s.Quantity),
|
CompletedQuantity = SqlFunc.AggregateSum(s.TlQty)
|
})
|
.First();
|
|
if (quantities == null)
|
{
|
throw new Exception("采购退料单 '" + p_bill_no + "'未审核");
|
}
|
|
C_SQTY = quantities.TotalQuantity ?? 0;
|
C_OKQTY = quantities.CompletedQuantity ?? 0;
|
C_CQTY = C_SQTY - C_OKQTY;
|
|
if (C_CQTY < C_STOCKNUM) return 1;
|
|
// Handle barcode reduction
|
decimal p_number2 = C_STOCKNUM;
|
var itemOutItems = db.Queryable<MesInvItemOutItems>()
|
.Where(s =>
|
s.ItemOutId == c_mes_inv_item_outs.Guid &&
|
s.ItemId == c_mes_inv_item_stocks.ItemId)
|
.ToList();
|
|
itemOutItems = itemOutItems
|
.Where(s => s.Quantity - (s.TlQty ?? 0) > 0).ToList();
|
|
foreach (var CMI in itemOutItems)
|
{
|
if (p_number2 == 0)
|
break;
|
|
var c_mes_inv_item_arn_detail = db
|
.Queryable<MesInvItemArnDetail>()
|
.Where(t =>
|
t.CbillNo == CMI.PbillNo
|
&& t.Ebeln == CMI.WorkNo &&
|
//t.WorkLine == CMI.WorkLine &&
|
t.ItemId == CMI.ItemId)
|
.First();
|
|
if (c_mes_inv_item_arn_detail == null)
|
{
|
throw new Exception("未找到采购退料来源单据");
|
}
|
|
if (CMI.Quantity - CMI.TlQty <= p_number2)
|
{
|
db.Updateable<MesInvItemOutItems>()
|
.SetColumns(it => new MesInvItemOutItems
|
{ TlQty = (int)it.Quantity, Status = 1 })
|
.Where(it => it.Guid == CMI.Guid)
|
.ExecuteCommand();
|
|
p_number2 -= CMI.Quantity ?? 0 - CMI.TlQty ?? 0;
|
|
db.Insertable(new MesInvItemOutCDetails
|
{
|
ItemOutId = c_mes_inv_item_outs.Guid,
|
ItemBarcode = p_item_barcode,
|
ItemNo = c_mes_inv_item_stocks.ItemNo,
|
LotNo = c_mes_inv_item_stocks.LotNo,
|
Quantity = CMI.Quantity - CMI.TlQty,
|
ForceOutFlag = 0,
|
CreateBy = c_user,
|
CreateDate = DateTime.Now,
|
LastupdateBy = c_user,
|
LastupdateDate = DateTime.Now,
|
DepotCode = c_mes_inv_item_stocks.DepotsCode,
|
DepotSectionCode =
|
c_mes_inv_item_stocks.DepotSectionsCode,
|
WorkNo = CMI.WorkNo,
|
WorkLine = CMI.WorkLine,
|
SuppNo = c_mes_inv_item_outs.SuppNo,
|
PbillNo = CMI.PbillNo,
|
ItemId = c_mes_inv_item_stocks.ItemId,
|
EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
|
LineK3id = c_mes_inv_item_stocks.LineK3id,
|
DepotId = c_mes_inv_item_outs.DepotId
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
db.Insertable(new MesInvBusiness2
|
{
|
Status = 1,
|
BillTypeId = p_bill_type_id,
|
TransactionCode = p_transaction_no.ToString(),
|
BusinessType = 1,
|
ItemBarcode = p_item_barcode,
|
ItemNo = c_mes_inv_item_stocks.ItemNo,
|
LotNo = c_mes_inv_item_stocks.LotNo,
|
EpFlag = true,
|
Quantity = CMI.Quantity - CMI.TlQty,
|
FromInvDepotsCode = c_mes_inv_item_stocks.DepotsCode,
|
FromInvDepotSectionsCode =
|
c_mes_inv_item_stocks.DepotSectionsCode,
|
ToInvDepotsCode = null,
|
ToInvDepotSectionsCode = null,
|
Description = "采购退货",
|
CreateBy = c_user,
|
CreateDate = DateTime.Now,
|
LastupdateBy = c_user,
|
LastupdateDate = DateTime.Now,
|
TaskNo = CMI.WorkNo,
|
ItemId = c_mes_inv_item_stocks.ItemId,
|
EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
|
LineK3id = c_mes_inv_item_stocks.LineK3id,
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
if (p_number2 == 0)
|
{
|
db.Deleteable<MesInvItemStocks>()
|
.Where(s => s.Guid == c_mes_inv_item_outs.Guid)
|
.ExecuteCommand();
|
}
|
}
|
else
|
{
|
var mesInvItemOutItems = db.Queryable<MesInvItemOutItems>()
|
.Where(s => s.Guid == CMI.Guid).First();
|
|
db.Updateable<MesInvItemOutItems>()
|
.SetColumns(i =>
|
i.TlQty == p_number2 +
|
(mesInvItemOutItems.TlQty ?? 0))
|
.Where(i => i.Guid == CMI.Guid)
|
.ExecuteCommand();
|
|
db.Insertable(new MesInvItemOutCDetails
|
{
|
ItemOutId = c_mes_inv_item_outs.Guid,
|
ItemBarcode = p_item_barcode,
|
ItemNo = c_mes_inv_item_stocks.ItemNo,
|
LotNo = c_mes_inv_item_stocks.LotNo,
|
Quantity = p_number2,
|
ForceOutFlag = 0,
|
CreateBy = c_user,
|
CreateDate = DateTime.Now,
|
LastupdateBy = c_user,
|
LastupdateDate = DateTime.Now,
|
DepotCode = c_mes_inv_item_stocks.DepotsCode,
|
DepotSectionCode =
|
c_mes_inv_item_stocks.DepotSectionsCode,
|
WorkNo = CMI.WorkNo,
|
WorkLine = CMI.WorkLine,
|
SuppNo = c_mes_inv_item_outs.SuppNo,
|
ItemId = c_mes_inv_item_stocks.ItemId,
|
EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
|
LineK3id = c_mes_inv_item_stocks.LineK3id,
|
DepotId = c_mes_inv_item_outs.DepotId
|
}).IgnoreColumns(true).ExecuteCommand();
|
|
db.Insertable(new MesInvBusiness2
|
{
|
Status = 1,
|
BillTypeId = p_bill_type_id,
|
TransactionCode = p_transaction_no.ToString(),
|
BusinessType = 1,
|
ItemBarcode = p_item_barcode,
|
ItemNo = c_mes_inv_item_stocks.ItemNo,
|
LotNo = c_mes_inv_item_stocks.LotNo,
|
EpFlag = true,
|
Quantity = p_number2,
|
FromInvDepotsCode = c_mes_inv_item_stocks.DepotsCode,
|
FromInvDepotSectionsCode =
|
c_mes_inv_item_stocks.DepotSectionsCode,
|
Description = "采购退货",
|
CreateBy = c_user,
|
CreateDate = DateTime.Now,
|
LastupdateBy = c_user,
|
LastupdateDate = DateTime.Now,
|
TaskNo = CMI.PbillNo,
|
BillNo = p_bill_no,
|
WorkNo = CMI.WorkNo,
|
WorkLine = CMI.WorkLine,
|
SuppNo = c_mes_inv_item_outs.SuppNo,
|
SuppId = c_mes_inv_item_outs.SuppId.ToString(),
|
ItemId = c_mes_inv_item_stocks.ItemId,
|
EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
|
LineK3id = c_mes_inv_item_stocks.LineK3id,
|
}).IgnoreColumns(true).ExecuteCommand();
|
p_number2 = 0;
|
}
|
}
|
|
if (p_number2 > 0)
|
{
|
throw new Exception("物料数量将超采购退料申请单数量,无法出库");
|
}
|
|
return 1;
|
});
|
|
return commit > 0;
|
}
|
}
|
