using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using SqlSugar;
|
|
namespace MES.Service.service.Warehouse;
|
|
public class MesInvItemOutsManager : Repository<MesInvItemOuts>
|
{
|
//当前类已经继承了 Repository 增、删、查、改的方法
|
//这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemOutsManager.cs
|
|
public bool Audit(WarehouseQuery query)
|
{
|
// 审核前校验
|
Validate(query);
|
//审核
|
return Db.Updateable<MesInvItemOuts>()
|
.SetColumns(s => s.Status == 1)
|
.SetColumns(s => s.CheckUser == query.userName)
|
.SetColumns(s => s.CheckDate == DateTime.Now)
|
.Where(s => s.Id == query.id).ExecuteCommand() > 0;
|
}
|
|
|
public 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.Id)
|
.ToList();
|
|
var sql = string.Empty;
|
var P_Msg = string.Empty;
|
|
foreach (var item in itemOutItems)
|
{
|
// 判断申请数量是否大于剩余可退数量
|
sql = string.Format(
|
@"SELECT NVL(s1.RK_QTY, 0) - NVL(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.ID = {0}", item.Id);
|
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.Id &&
|
s1.RkNo == item.RkNo)
|
.Count();
|
|
if (cf_Num > 1)
|
{
|
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.PbillNo)
|
.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)
|
{
|
P_Msg =
|
$"子表仓库编码：{j.DepotCode} 与主表仓库编码：{mesInvItemOuts.DepotCode}，不一致，请检查";
|
throw new Exception(P_Msg);
|
}
|
}
|
}
|
|
public OutItemDto SaveCombination(OutItemDto dto)
|
{
|
var mesInvItemOuts = dto.Form;
|
mesInvItemOuts.Status = 0;
|
mesInvItemOuts.BoardFlag = 0;
|
mesInvItemOuts.Sapstatus = 0;
|
mesInvItemOuts.OutStatus = 0;
|
mesInvItemOuts.ItemFlag = 0;
|
mesInvItemOuts.IsVisual = 1;
|
mesInvItemOuts.TransactionNo = 203;
|
mesInvItemOuts.Factory = "1000";
|
mesInvItemOuts.Company = "1000";
|
|
//返回ID
|
var id = Db.Insertable(mesInvItemOuts)
|
.ExecuteReturnIdentity();
|
|
if (id <= 0) throw new Exception("插入错误");
|
|
mesInvItemOuts.Id = id;
|
dto.SaveItems.ForEach(s =>
|
{
|
s.ItemOutId = id;
|
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 = id;
|
dto.SelectItems = GetItems(query);
|
|
return dto;
|
}
|
|
public List<MesInvItemOuts> GetPage(WarehouseQuery query)
|
{
|
return
|
Db.Queryable<MesInvItemOuts, MesDepots, MesSupplier, SysUser,
|
SysUser>((a, e, i, u1, u3) =>
|
new JoinQueryInfos(
|
JoinType.Left,
|
a.DepotCode == e.DepotCode
|
&& e.Factory == a.Factory
|
&& e.Company == a.Company,
|
JoinType.Left,
|
a.SuppNo == i.SuppNo,
|
JoinType.Left,
|
a.CreateBy == u1.Fcode,
|
JoinType.Left,
|
a.CheckUser == u3.Fcode))
|
.WhereIF(query.id > 0,
|
(a, e, i, u1, u3) => a.Id == query.id)
|
.Select((a, e, i, u1, u3) => new MesInvItemOuts
|
{
|
Id = a.Id,
|
DepotCode = a.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 = a.SuppNo,
|
DepotName = e.DepotName,
|
SuppName = i.SuppName,
|
CreateByFname = u1.Fname,
|
CheckUserFname = u3.Fname,
|
CreateDate = a.CreateDate,
|
CreateBy = a.CreateBy,
|
Status = a.Status,
|
ItemOutNo = a.ItemOutNo
|
}).ToPageList(query.PageIndex, query.Limit);
|
}
|
|
//MESInvItemOutItems
|
public List<MESInvItemOutItems> GetItems(WarehouseQuery query)
|
{
|
var where = "and 1=1";
|
if (query.id != null) where = "and ITEM_OUT_ID = '" + query.id + "'";
|
|
var sql = string.Format(@"select * from (
|
SELECT C.REMARK, C.PBILL_NO, C.ITEM_OUT_ID, C.RK_QTY,
|
C.TL_QTY, C.WORK_LINE, C.QUANTITY, C.ITEM_NO,
|
NVL(s1.RK_QTY, 0) - NVL(KT_QTY, 0) KT_QTY,
|
R.DEPOT_SECTION_CODE, S.ITEM_MODEL, S.ITEM_NAME,
|
K.CGB014, Q.STAFF_NAME, R.DEPOT_CODE
|
FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_NO = S.ITEM_NO
|
LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
|
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_details
|
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
|
LEFT JOIN MES_ROH_IN_DATA K
|
ON K.BILL_NO = C.PBILL_NO AND C.WORK_LINE = K.PURCHASE_ORDER_LINE_NUMBER
|
left join MES_ROH_IN H on H.BILL_NO = K.BILL_NO
|
left join MES_STAFF Q on H.PURCHASER = Q.STAFF_NAME
|
LEFT JOIN MES_SUPPLIER T ON H.SUPPLIER = T.ID
|
LEFT JOIN (select *
|
from (SELECT row_number() over (partition by DEPOT_CODE, ITEM_NO, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE, ITEM_NO,
|
EBELN FROM mes_inv_item_in_c_details
|
where EBELN is not null)
|
where rn = 1 and ROWNUM = 1) R ON R.ITEM_NO = C.ITEM_NO) where 1=1 {0}",
|
where);
|
var results = Db.Ado.SqlQuery<MESInvItemOutItems>(sql);
|
return results;
|
}
|
|
public List<InventoryItem> GetSumItem(WarehouseQuery query)
|
{
|
var sql = string.Format(@"SELECT DEPOT ,ITEM_NO,ITEM_MODEL,
|
ITEM_NAME,QTY FROM (SELECT SUM(QTY) QTY, DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME
|
FROM (SELECT f_get_section_code2('1000', '1000', b.ITEM_NO,'{0}') DEPOT,
|
B.ITEM_NO, A.ITEM_MODEL,A.ITEM_NAME,to_char(nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0),'FM9999999990.00') QTY
|
FROM MES_INV_ITEM_OUT_ITEMS B LEFT JOIN mes_ITEMS A
|
ON B.ITEM_NO = A.ITEM_NO LEFT JOIN (SELECT ITEM_OUT_ID,
|
ITEM_NO,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
|
FROM MES_INV_ITEM_OUT_C_DETAILS
|
WHERE ITEM_OUT_ID = {1}
|
GROUP BY ITEM_OUT_ID,ITEM_NO,PBILL_NO,RK_NO) D
|
ON D.ITEM_OUT_ID = B.ITEM_OUT_ID
|
AND D.ITEM_NO = B.ITEM_NO
|
AND D.PBILL_NO = B.PBILL_NO
|
AND D.RK_NO = B.RK_NO
|
WHERE B.ITEM_OUT_ID = {1}
|
AND nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0) > 0
|
ORDER BY f_get_section_code2('1000','1000',b.ITEM_NO,'{0}'),
|
A.ITEM_MODEL,A.ITEM_NAME)
|
GROUP BY DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME)", query.DepotCode,
|
query.id);
|
|
var results = Db.Ado.SqlQuery<InventoryItem>(sql);
|
return results;
|
}
|
|
public List<InventoryItemDetail> SelectMaterials(WarehouseQuery query)
|
{
|
var sql = string.Format(@"select B.WORK_NO, b.work_line,
|
b.item_no, s2.item_name, s2.item_model,
|
sum(b.quantity) sum_Quantity,
|
b.unit, b.supp_no,
|
nvl(s1.quantity, 0) quantity,
|
sum(b.quantity) - nvl(U.KT_QTY, 0) Refundable_Quantity,
|
B.cbill_no, B.task_no, A.BILL_NO,
|
to_char(b.CREATE_DATE, 'yyyy-mm-dd') as CREATE_DATE
|
from mes_inv_item_in_c_details b
|
left join mes_inv_item_ins a on b.item_in_id = a.id
|
LEFT JOIN (select s.item_no, s.work_line, s.Work_no,
|
sum(s.quantity) quantity, S.RK_NO
|
from mes_inv_item_out_c_details s
|
group by s.item_no, s.work_line, s.work_no, S.RK_NO) s1
|
ON NVL(B.work_LINE, '0') = NVL(s1.work_LINE, '0')
|
and B.work_no = s1.work_no
|
and B.item_no = s1.item_no
|
AND B.BILL_NO = S1.RK_NO
|
LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
|
FROM MES_INV_ITEM_OUT_ITEMS
|
GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
|
ON U.ITEM_NO = B.ITEM_NO
|
AND U.WORK_LINE = B.WORK_LINE
|
AND U.PBILL_NO = B.WORK_NO AND U.RK_NO = B.BILL_NO
|
left join mes_items s2 on b.item_no = s2.item_no
|
left join purdha da on da.dha001 = b.cbill_no
|
WHERE A.BILL_NO LIKE 'Q%'
|
AND A.CBILL_NO like 'L%'
|
group by b.item_no, b.work_line, b.supp_no, b.remark, s1.quantity, b.unit,
|
s2.item_name, s2.item_model, da.dha003, B.WORK_NO, B.cbill_no, B.task_no,
|
to_char(b.CREATE_DATE, 'yyyy-mm-dd'), A.BILL_NO, U.KT_QTY, A.CBILL_NO
|
having b.remark = '采购入库'
|
and sum(b.quantity) - nvl(U.KT_QTY, 0) > 0
|
and b.supp_no = '{0}'
|
order by to_char(b.CREATE_DATE, 'yyyy-mm-dd')",
|
query.SuppNo);
|
|
var results = Db.Ado.SqlQuery<InventoryItemDetail>(sql);
|
return results;
|
}
|
|
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;
|
}
|
}
|
