using System.Data;
|
using MES.Service.DB;
|
using MES.Service.Dto.service;
|
using MES.Service.Modes;
|
using MES.Service.util;
|
using SqlSugar;
|
using DbType = System.Data.DbType;
|
|
namespace MES.Service.service.Warehouse;
|
|
public class MesInvItemOutsManager : Repository<MesInvItemOuts>
|
{
|
//当前类已经继承了 Repository 增、删、查、改的方法
|
//这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemOutsManager.cs
|
|
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 nvl(SUM(S.QUANTITY),0) - nvl(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.ID
|
where d.item_out_no = '{0}'
|
and d.bill_type_id = {1}
|
and d.transaction_no = {2}
|
AND S.item_no = '{3}'
|
and nvl(d.status, 0) = 1
|
and nvl(d.nflag, 0) = 0", query.billNo, billTypeId,
|
transactionNo,
|
mesInvItemStocks.ItemNo);
|
|
var cqty = Db.Ado.SqlQuerySingle<decimal>(sql);
|
|
if (cqty == null)
|
throw new Exception("物料" + mesInvItemStocks.ItemNo +
|
"的辅助属性与条码不一致！请检查");
|
|
if (cqty < StocksNum) throw new Exception("请确认发料数量");
|
|
//调用存储过程
|
try
|
{
|
// 定义输出参数
|
var outputResult = new SugarParameter("C_RESULT", null,
|
DbType.Int32, ParameterDirection.Output,
|
4000);
|
|
var outputMessage = new SugarParameter("MSG", null, DbType.String,
|
ParameterDirection.Output, 4000);
|
|
// 定义输入参数
|
var parameters = new List<SugarParameter>
|
{
|
new("p_item_barcode", query.barcode,
|
DbType.String, ParameterDirection.Input),
|
new("p_bill_no", query.billNo, DbType.String,
|
ParameterDirection.Input),
|
new("pi_factory", "1000", DbType.String,
|
ParameterDirection.Input),
|
new("pi_company", "1000", DbType.String,
|
ParameterDirection.Input),
|
new("c_user", query.userName, DbType.String,
|
ParameterDirection.Input),
|
outputResult,
|
outputMessage
|
};
|
|
// 使用 SqlSugar 执行存储过程
|
Db.Ado.ExecuteCommand(
|
"BEGIN MES_CG_UPDATE_BARCODES(:p_item_barcode, :p_bill_no, :pi_factory, :pi_company, :c_user, :C_RESULT, :MSG); END;",
|
parameters.ToArray());
|
|
// 获取输出参数的值
|
var resultValue = outputResult.Value?.ToString();
|
var messageValue = outputMessage.Value?.ToString();
|
|
if ("1".Equals(resultValue)) throw new Exception("条码扣除失败");
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
|
// 变量用于保存查询结果
|
decimal? C_SQ_QTY = 0;
|
decimal? C_OK_QTY = 0;
|
var c_result = string.Empty;
|
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_NO, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
|
FROM MES_INV_ITEM_OUT_C_DETAILS
|
GROUP BY ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO
|
) D
|
ON D.ITEM_OUT_ID = C.ITEM_OUT_ID
|
AND D.ITEM_NO = C.ITEM_NO
|
AND D.RK_NO = C.RK_NO
|
AND NVL(C.PBILL_NO, '0') = NVL(D.PBILL_NO, '0')
|
WHERE C.ITEM_OUT_ID = {0}", mesInvItemOuts.Id);
|
|
var queryResult = Db.Ado.SqlQuerySingle<dynamic>(sql);
|
|
C_SQ_QTY = queryResult?.SQ_QTY;
|
C_OK_QTY = queryResult?.OK_QTY;
|
|
// 判断逻辑
|
if (C_SQ_QTY < C_OK_QTY) throw new Exception("扫描数量超过申请数量,请重新扫描！");
|
|
if (C_SQ_QTY == C_OK_QTY) return true;
|
|
return false;
|
}
|
|
|
public bool Audit(WarehouseQuery query)
|
{
|
// 审核前校验
|
Validate(query);
|
query.status = 1;
|
//审核
|
return Update(query);
|
}
|
|
public bool SetNFlag(WarehouseQuery query)
|
{
|
return Db.Updateable<MesInvItemOuts>()
|
.SetColumns(s => s.Nflag == 1)
|
.Where(s => s.ItemOutNo == query.billNo)
|
.ExecuteCommand() > 0;
|
}
|
|
public bool DeApprove(WarehouseQuery query)
|
{
|
query.date = null;
|
query.status = 0;
|
return Update(query);
|
}
|
|
private bool Update(WarehouseQuery entity)
|
{
|
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.Id == entity.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)
|
.WhereIF(!string.IsNullOrEmpty(query.billNo),
|
(a, e, i, u1, u3) => a.ItemOutNo == query.billNo)
|
.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);
|
}
|
|
public List<MesInvItemOutCDetails> GetScanBarcode(WarehouseQuery query)
|
{
|
return Db.Queryable<MesInvItemOutCDetails, MesInvItemBarcodes, MesItems,
|
MesDepots>(
|
(b, bar, c, d) => new
|
JoinQueryInfos(
|
JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
|
JoinType.Left, b.ItemNo == c.ItemNo &&
|
b.Company == c.Company &&
|
b.Factory == c.Factory,
|
JoinType.Left, b.DepotCode == d.DepotCode &&
|
b.Company == d.Company &&
|
b.Factory == d.Factory
|
))
|
.Where((b, bar, c, d) => b.ItemOutId == query.id)
|
.Select((b, bar, c, d) => new MesInvItemOutCDetails
|
{
|
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();
|
}
|
|
//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 OutItemDto GetSumItem(WarehouseQuery query)
|
{
|
var mesInvItemOuts = base.GetSingle(it => it.ItemOutNo == query.billNo);
|
if (mesInvItemOuts == null) throw new Exception("采购退货单不存在");
|
|
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)",
|
mesInvItemOuts.DepotCode,
|
mesInvItemOuts.Id);
|
|
var results = Db.Ado.SqlQuery<InventoryItem>(sql);
|
|
var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
|
.Where(a => a.ItemBarcode == query.barcode).Single();
|
|
var dto = new OutItemDto();
|
dto.SumItem = results;
|
|
if (mesInvItemStocks != null)
|
{
|
dto.ItemNo = mesInvItemStocks.ItemNo;
|
dto.Quantity = mesInvItemStocks.Quantity;
|
}
|
|
return dto;
|
}
|
|
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;
|
}
|
|
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;
|
}
|
|
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;
|
}
|
|
private string GetDeApprovePam(decimal? id)
|
{
|
var sid = (int)id;
|
var encodedUrl = "taskname=CGTL&mesid=" + sid +
|
"&optype=delete&datajson={}";
|
|
return encodedUrl;
|
}
|
|
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;
|
}
|
|
public List<string> GetItemOutNo()
|
{
|
var data5 = Db.Queryable<MesInvItemOuts>().Where(it =>
|
it.BillTypeId == 200 && it.TransactionNo == 203 &&
|
it.Status == 1)
|
.OrderBy(it => it.Id, OrderByType.Desc)
|
.ToPageList(1, 20);
|
|
var ItemOutNos = data5.FindAll(a => a.Nflag == null || a.Nflag == 0)
|
.Select(s => s.ItemOutNo).ToList();
|
//
|
// var ItemOutNos = data5
|
// .Select(s => s.ItemOutNo).ToList();
|
|
return ItemOutNos;
|
}
|
}
|
