From fd6838164120ff30f76780f6478dcb0e04983ee9 Mon Sep 17 00:00:00 2001
From: 啊鑫 <t2856754968@163.com>
Date: 星期六, 10 八月 2024 15:10:15 +0800
Subject: [PATCH] 采购退料，优化代码
---
MES.Service/service/Warehouse/MesInvItemOutsManager.cs | 349 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 341 insertions(+), 8 deletions(-)
diff --git a/MES.Service/service/Warehouse/MesInvItemOutsManager.cs b/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
index db0ca42..d120804 100644
--- a/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
+++ b/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
@@ -1,7 +1,10 @@
-锘縰sing MES.Service.DB;
+锘縰sing 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;
@@ -10,16 +13,190 @@
//褰撳墠绫诲凡缁忕户鎵夸簡 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 + "搴撳瓨涓棤姝ゆ潯鐮侊紝璇锋牳瀵癸紒");
+
+ //楠岃瘉鍑哄簱鍗昺es_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.Status == 1)
- .SetColumns(s => s.CheckUser == query.userName)
+ .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)
+ {
+ return Db.Updateable<MesInvItemOuts>()
+ .SetColumns(s => s.Status == entity.status)
+ .SetColumns(s => s.CheckUser == entity.userName)
.SetColumns(s => s.CheckDate == DateTime.Now)
- .Where(s => s.Id == query.id).ExecuteCommand() > 0;
+ .Where(s => s.Id == entity.id).ExecuteCommand() > 0;
}
@@ -218,6 +395,8 @@
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,
@@ -240,6 +419,35 @@
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
@@ -280,8 +488,11 @@
return results;
}
- public List<InventoryItem> GetSumItem(WarehouseQuery query)
+ 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,
@@ -300,11 +511,25 @@
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);
+ GROUP BY DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME)",
+ mesInvItemOuts.DepotCode,
+ mesInvItemOuts.Id);
var results = Db.Ado.SqlQuery<InventoryItem>(sql);
- return results;
+
+ 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)
@@ -369,4 +594,112 @@
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;
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3