using NewPdaSqlServer.DB;
|
using NewPdaSqlServer.Dto.service;
|
using NewPdaSqlServer.entity;
|
using NewPdaSqlServer.util;
|
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)
|
.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)
|
.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;
|
|
if (nflag != 0) return true;
|
|
return false;
|
}
|
|
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 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_OUTS_" + 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 = "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;
|
}
|
}
|
