From 6ec09f73c6515297294d24cdec4c815927450607 Mon Sep 17 00:00:00 2001
From: 南骏 池 <chiffly@163.com>
Date: 星期四, 05 六月 2025 09:51:19 +0800
Subject: [PATCH] 1.其他入库sql执行优化
---
service/Warehouse/InventoryManager.cs | 241 +++++++++++++++++++++++++++++++++++++++--------
1 files changed, 198 insertions(+), 43 deletions(-)
diff --git a/service/Warehouse/InventoryManager.cs b/service/Warehouse/InventoryManager.cs
index 4107f50..7fab709 100644
--- a/service/Warehouse/InventoryManager.cs
+++ b/service/Warehouse/InventoryManager.cs
@@ -1,8 +1,14 @@
锘縰sing NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
+using NewPdaSqlServer.service.@base;
using NewPdaSqlServer.util;
using SqlSugar;
+using System.Text;
+using System.Data;
+using System.Data.SqlClient;
+using System.Dynamic;
+using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
namespace NewPdaSqlServer.service.Warehouse;
@@ -16,9 +22,7 @@
// 妫�鏌ュ簱浣嶄唬鐮佹槸鍚︿负绌�
if (string.IsNullOrEmpty(p_section_code))
- {
throw new Exception("002[璇锋壂搴撲綅鏉＄爜锛�");
- }
// 鏌ヨ搴撳尯搴撲綅淇℃伅
var depotSection = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
@@ -32,9 +36,7 @@
// 濡傛灉搴撲綅浠ｇ爜涓嶅瓨鍦紝鍒欒繑鍥為敊璇俊鎭�
if (depotSection == null)
- {
throw new Exception("搴撲綅缂栫爜 " + p_section_code + " 涓嶅瓨鍦紝璇风‘璁わ紒");
- }
// 杩斿洖鎴愬姛淇℃伅
return "浠撳簱锛�" + depotSection.DepotName + " 搴撲綅锛�" +
@@ -51,10 +53,8 @@
var p_transction_no = 601;
// 妫�鏌ュ簱浣嶄唬鐮佹槸鍚︿负绌�
- if (string.IsNullOrEmpty(p_section_code))
- {
- throw new Exception("002[璇锋壂搴撲綅鏉＄爜锛�");
- }
+ // if (string.IsNullOrEmpty(p_section_code))
+ // throw new Exception("002[璇锋壂搴撲綅鏉＄爜锛�");
// 鏌ヨ搴撳尯搴撲綅淇℃伅
var depotSection = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
@@ -63,52 +63,56 @@
))
.Where((a, b) => a.DepotSectionCode == p_section_code)
.Select((a, b) => new
- { a.DepotSectionName, b.DepotCode, b.DepotName, b.DepotId })
+ { a.DepotSectionName, b.DepotCode, b.DepotName, b.DepotId,b.FSubsidiary })
.Single();
// 濡傛灉搴撲綅浠ｇ爜涓嶅瓨鍦紝鍒欒繑鍥為敊璇俊鎭�
- if (depotSection == null)
- {
- throw new Exception("搴撲綅缂栫爜 " + p_section_code + " 涓嶅瓨鍦紝璇风‘璁わ紒");
- }
+ // if (depotSection == null)
+ // throw new Exception("搴撲綅缂栫爜 " + p_section_code + " 涓嶅瓨鍦紝璇风‘璁わ紒");
// 妫�鏌ユ槸鍚﹀凡缁忔敹璐�
- var c_num = Db.Queryable<MesInvItemIns, MesInvItemInCDetails>((a, b) =>
- new JoinQueryInfos(
- JoinType.Inner, a.Guid == b.ItemInId
- ))
- .Where((a, b) => b.ItemBarcode == p_item_barcode)
- .Count();
+ // var c_num = Db.Queryable<MesInvItemIns, MesInvItemInCDetails>((a, b) =>
+ // new JoinQueryInfos(
+ // JoinType.Inner, a.Guid == b.ItemInId
+ // ))
+ // .Where((a, b) => b.ItemBarcode == p_item_barcode)
+ // .Count();
- if (c_num > 0)
- {
- throw new Exception("002[鏉＄爜閲嶅鎵弿锛岃鏍稿锛�");
- }
+ // if (c_num > 0) throw new Exception("002[鏉＄爜閲嶅鎵弿锛岃鏍稿锛�");
// 鏌ヨ鏉＄爜淇℃伅
- c_num = Db.Queryable<MesInvItemStocks>()
- .Where(t => t.ItemBarcode == p_item_barcode)
- .Count();
+ // c_num = Db.Queryable<MesInvItemStocks>()
+ // .Where(t => t.ItemBarcode == p_item_barcode)
+ // .Count();
- if (c_num > 0)
- {
- throw new Exception("002[鏉＄爜宸插湪搴撳瓨涓紝璇锋牳瀵癸紒");
- }
+ // if (c_num > 0) throw new Exception("002[鏉＄爜宸插湪搴撳瓨涓紝璇锋牳瀵癸紒");
// 鏌ヨ鏉＄爜璇︾粏淇℃伅
var c_mes_inv_item_barcodes = Db.Queryable<MesInvItemBarcodes>()
.Where(t => t.ItemBarcode == p_item_barcode)
.Single();
- if (c_mes_inv_item_barcodes == null)
- {
- throw new Exception("002[鏉＄爜涓嶅瓨鍦紝璇锋牳瀵癸紒");
- }
+ // if (c_mes_inv_item_barcodes == null)
+ // throw new Exception("002[鏉＄爜涓嶅瓨鍦紝璇锋牳瀵癸紒");
- if (c_mes_inv_item_barcodes.ComeFlg != 0)
- {
+ if (c_mes_inv_item_barcodes.Memo != "鍒濇湡鐗╂枡")
throw new Exception("002[鏉＄爜涓嶆槸鏈熷垵鏉＄爜锛屾棤娉曠敤鏈熷垵鍏ュ簱锛�");
- }
+
+ var wmsManager = new WmsBaseMangeer();
+ // 鏂板鍏ュ簱鏍￠獙锛堣皟鐢ㄥ瓨鍌ㄨ繃绋嬶級
+ var checkResult = wmsManager.pdaInvJY(
+ db: Db,
+ edtUserNo: query.userName,
+ barcode: p_item_barcode,
+ sectionCode: p_section_code,
+ stockId: depotSection.DepotId.ToString(),
+ stockOrgId: depotSection.FSubsidiary,
+ billNo: "", // 鏍规嵁瀹為檯鍗曟嵁鍙蜂紶鍊�
+ transactionNo: "601" // 浜嬪姟绫诲瀷涓庡瓨鍌ㄨ繃绋嬪尮閰�
+ );
+
+ if ( Convert.ToInt32(checkResult.result) < 1)
+ throw new Exception($"鍏ュ簱鏍￠獙澶辫触锛歿checkResult.strMsg}");
UseTransaction(db =>
{
@@ -144,9 +148,16 @@
BillNo = billNo,
SuppId = c_mes_inv_item_barcodes.SuppId,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
- UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag == null ? null : c_mes_inv_item_barcodes.UrgentFlag.Value ? "1" : "0",
+ UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag == null
+ ?
+ null
+ : c_mes_inv_item_barcodes.UrgentFlag.Value
+ ? "1"
+ : "0",
CbillNo = c_mes_inv_item_barcodes.BillNo,
- Fstatus = 0
+ Fstatus = 0,
+ ReceiveOrgId = depotSection.FSubsidiary,
+ InType = "鏈熷垵鍏ュ簱",
};
db.Insertable(newMesInvItemIns)
.IgnoreColumns(true)
@@ -186,7 +197,8 @@
UrgentFlag = c_mes_inv_item_barcodes.UrgentFlag,
BoardStyle = c_mes_inv_item_barcodes.BoardStyle,
TaskNo = c_mes_inv_item_barcodes.TaskNo,
- ItemId = c_mes_inv_item_barcodes.ItemId
+ ItemId = c_mes_inv_item_barcodes.ItemId,
+ ReceiveOrgId = depotSection.FSubsidiary,
}).IgnoreColumns(true).ExecuteCommand();
@@ -226,8 +238,11 @@
ItemNo = c_mes_inv_item_barcodes.ItemNo,
LotNo = c_mes_inv_item_barcodes.LotNo,
Quantity = c_mes_inv_item_barcodes.Quantity,
- EpFlag = c_mes_inv_item_barcodes.EpFlag == null ? null : (byte)(c_mes_inv_item_barcodes.EpFlag.Value ? 1 : 0),
+ EpFlag = c_mes_inv_item_barcodes.EpFlag == null
+ ? null
+ : (byte)(c_mes_inv_item_barcodes.EpFlag.Value ? 1 : 0),
DepotsCode = depotSection.DepotCode,
+ DepotId = depotSection.DepotId,
DepotsId = depotSection.DepotId,
DepotSectionsCode = p_section_code,
CheckDate = c_mes_inv_item_barcodes.CreateDate,
@@ -237,7 +252,9 @@
WorkLine = c_mes_inv_item_barcodes.WorkLine,
// ItemUnit = c_mes_inv_item_barcodes.Unit,
SuppNo = c_mes_inv_item_barcodes.SuppNo,
- ItemId = c_mes_inv_item_barcodes.ItemId
+ ItemId = c_mes_inv_item_barcodes.ItemId,
+ IndepUserCode = c_user,
+ StockOrgId = depotSection.FSubsidiary,
// Fsubsidiary = c_organize
}).IgnoreColumns(true).ExecuteCommand();
@@ -245,7 +262,7 @@
});
var mesItems = Db.Queryable<MesItems>()
- .Where(s=>s.Id == c_mes_inv_item_barcodes.ItemId)
+ .Where(s => s.Id == c_mes_inv_item_barcodes.ItemId)
.First();
query.itemNo = mesItems.ItemNo;
@@ -256,4 +273,142 @@
// 杩斿洖鎴愬姛淇℃伅
return query;
}
+
+
+ public dynamic GetDepoptsInfo(dynamic unity)
+ {
+
+ //// 浣跨敤鍙傛暟鍖栨煡璇㈤槻姝QL娉ㄥ叆
+ var sqlParams = new List<SugarParameter> { new("@sectionCode", unity.sectionCode) };
+
+ var sql2 = @" SELECT TOP 1 B.depot_code + '('+B.depot_name+')' depotsInfo,
+ B.FSubsidiary,
+ C.FNumber + '('+C.NAME+')' orgInfo
+ FROM MES_DEPOT_SECTIONS A
+ LEFT JOIN
+ MES_DEPOTS B ON A.depot_guid = B.Guid
+ LEFT JOIN SYS_ORGANIZATION C ON C.FID = B.FSubsidiary
+ WHERE a.depot_section_code = @sectionCode;";
+
+ var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2, sqlParams).FirstOrDefault();
+
+ // 妫�鏌ョ粨鏋滄湁鏁堟��
+ if (XcslItem == null)
+ {
+ throw new Exception($"搴撲綅 [{unity.sectionCode}] 涓嶅瓨鍦紝鎴栨墍灞炰粨搴撶姸鎬佸紓甯革紙鍙兘琚鐢ㄦ垨鍒犻櫎锛夈��");
+ }
+
+ return XcslItem; // 杩斿洖绗竴琛屾暟鎹紝濡傛灉娌℃湁鍒欒繑鍥� null
+ }
+
+
+
+ public dynamic GetItemsList(dynamic unity)
+ {
+ var sqlParams = new List<SugarParameter> { new("@orgId", unity.orgId) };
+
+ var sql2 = new StringBuilder(@"
+ SELECT TOP 20 item_id, item_no, item_name, item_model,item_no+'---'+item_name AS wlInfo
+ FROM MES_ITEMS
+ WHERE FSubsidiary = @orgId");
+
+ if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
+ {
+ sqlParams.Add(new("@selectKey", unity.selectKey));
+ sql2.Append(@"
+ AND (item_no LIKE '%' + @selectKey + '%'
+ OR item_name LIKE '%' + @selectKey + '%'
+ OR item_model LIKE '%' + @selectKey + '%')");
+ }
+
+ var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);
+
+ if (XcslItem == null)
+ {
+ throw new Exception("璇ユ潯浠朵笅鏃犲搴旂墿鏂欎俊鎭紝璇烽噸鏂拌緭鍏ワ紒");
+ }
+
+ return XcslItem;
+ }
+
+ public List<dynamic> ExecuteBeginBar(dynamic query)
+ {
+ if (query == null)
+ throw new ArgumentNullException(nameof(query), "鍙傛暟瀵硅薄涓嶈兘涓簄ull");
+
+ // 澧炲己鍙傛暟鏍￠獙
+ if (string.IsNullOrEmpty(query.userName?.ToString()))
+ throw new ArgumentException("鐢ㄦ埛璐﹀彿涓嶈兘涓虹┖", nameof(query.userName));
+
+ if (string.IsNullOrEmpty(query.itemid?.ToString()))
+ throw new ArgumentException("鐗╂枡ID涓嶈兘涓虹┖", nameof(query.itemid));
+
+ var resultList = new List<dynamic>();
+
+ using (var conn = new SqlConnection(DbHelperSQL.strConn))
+ using (var cmd = new SqlCommand("rpt_BeginBar", conn))
+ {
+ cmd.CommandType = CommandType.StoredProcedure;
+ cmd.Parameters.AddRange(new[]
+ {
+ new SqlParameter("@inUser", SqlDbType.NVarChar, 50) { Value = query.userName },
+ new SqlParameter("@inItemId", SqlDbType.NVarChar, 50) { Value = query.itemid },
+ new SqlParameter("@isDesign", SqlDbType.Int) { Value = 0 },
+ new SqlParameter("@in4", SqlDbType.NVarChar, 20) { Value = query.in4 ?? DBNull.Value },
+ new SqlParameter("@in5", SqlDbType.NVarChar, 20) { Value = query.num ?? DBNull.Value }
+ });
+
+ try
+ {
+ conn.Open();
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ var barcode = reader.GetString(0);
+
+ // 鏋勫缓绗﹀悎鏂规硶绛惧悕鐨勫弬鏁板璞�
+ var scanQuery = new WarehouseQuery
+ {
+ barcode = barcode,
+ DepotCode = query.sectionCode, // 浠庡師濮媞uery鑾峰彇搴撲綅缂栫爜
+ userName = query.userName // 浠庡師濮媞uery鑾峰彇鐢ㄦ埛璐﹀彿
+ };
+
+ try
+ {
+ // 璋冪敤姝ｅ紡绛惧悕鏂规硶
+ ScanBarcode(scanQuery);
+ var printData = GetPrintBar(barcode);
+ resultList.Add(printData);
+ }
+ catch (Exception ex)
+ {
+ throw new Exception($"鏈熷垵鏉＄爜鎵撳嵃鍏ュ簱瀛樺湪寮傚父{ex.Message}锛岃閲嶆柊鎵撳嵃锛�");
+ }
+ }
+ }
+ return resultList;
+ }
+ catch (Exception ex)
+ {
+ throw new Exception($"鐢熸垚鏈熷垵鏉＄爜澶辫触锛歿ex.Message}");
+ }
+ }
+ }
+
+ public dynamic GetPrintBar(string barcode)
+ {
+ var sqlParams = new List<SugarParameter> { new("@barcode", barcode) };
+
+ var sql1 = @" SELECT TOP 1 C.ITEM_NO,C.item_name,C.item_model,B.OLDQTY as QUANTITY,B.CREATE_DATE, '鏈熷垵鏉＄爜' AS BarType,A.ITEM_BARCODE,GETDATE() as print_date
+ FROM MES_INV_ITEM_STOCKS A
+ LEFT JOIN MES_INV_ITEM_BARCODES B ON A.ITEM_BARCODE = B.ITEM_BARCODE
+ LEFT JOIN MES_ITEMS C ON C.item_id = B.ITEM_ID
+ WHERE B.ITEM_BARCODE = @barcode";
+
+ var XcslItem = Db.Ado.SqlQuery<dynamic>(sql1, sqlParams).First();
+
+ return XcslItem;
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3