From 1d976f52878c9f776146502037377310921e5a21 Mon Sep 17 00:00:00 2001
From: 南骏 池 <chiffly@163.com>
Date: 星期四, 04 九月 2025 08:23:13 +0800
Subject: [PATCH] 1.携客云获取送货单优化
---
service/QC/IpqcService.cs | 286 +++++++++++++++++++++++++++++++++++++++-----------------
1 files changed, 199 insertions(+), 87 deletions(-)
diff --git a/service/QC/IpqcService.cs b/service/QC/IpqcService.cs
index 8964b18..d477004 100644
--- a/service/QC/IpqcService.cs
+++ b/service/QC/IpqcService.cs
@@ -7,6 +7,7 @@
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
+using System.Text;
using static Azure.Core.HttpHeader;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;
@@ -45,89 +46,57 @@
return (pageList, totalCount);
}
- public List<MesQaItemsDetectDetail5> GetItems(string? releaseNo,
+ public List<dynamic> GetItems(string? releaseNo,
string? id)
{
var parsedGuid = Guid.Empty;
- if (id != null)
- {
- var isValid = Guid.TryParse(id, out parsedGuid);
- if (!isValid)
- throw new ApplicationException("GUID杞崲閿欒");
- }
+ var sql = string.Format(@"SELECT [a].[guid] AS [Guid],
+ [a].[parent_guid] AS [ParentGuid],
+ [a].[release_no] AS [ReleaseNo],
+ [a].[check_qyt] AS [CheckQyt],
+ [a].[fac_level] AS [FacLevel],
+ [a].[fcheck_item] AS [FcheckItem],
+ [a].[fcheck_tool] AS [FcheckTool],
+ [a].[fdown_allow] AS [FdownAllow],
+ [a].[fcheck_level] AS [FcheckLevel],
+ [a].[fstand] AS [Fstand],
+ [a].[fup_allow] AS [FupAllow],
+ [a].[sample_size_no] AS [SampleSizeNo],
+ [a].[fspec_requ] AS [FspecRequ],
+ [a].[fre_qty] AS [FreQty],
+ N'1000' AS [Factory],
+ N'1000' AS [Company],
+ COUNT([b].[guid]) AS [FenterQty],
+ [a].[fcheck_resu] AS [FcheckResu],
+ [a].[forder] AS [Order],
+ [a].[ybsl] AS [Ybsl],
+ [a].stationName,
+ [a].IS_GWSCAN,
+ ISNULL((SELECT COUNT(1)
+ FROM MES_QA_ITEMS_DETECT_DETAIL13 C
+ WHERE C.parent_guid = A.guid), 0) AS [YbslIn]
+FROM [MES_QA_ITEMS_DETECT_DETAIL5] [a]
+ Left JOIN [MES_QA_ITEMS_DETECT_DETAIL12] [b] ON ([a].[guid] = [b].[parent_guid])
+WHERE ([a].[release_no] = N'{0}' AND IS_GWSCAN = 1)
+GROUP BY [a].[guid], [a].[parent_guid], [a].[release_no], [a].[fac_level], [a].[fcheck_item], [a].[fcheck_tool],
+ [a].[fdown_allow], [a].[fcheck_level], [a].[fstand], [a].[fup_allow], [a].[sample_size_no], [a].[fspec_requ],
+ [a].[fre_qty], [a].[check_qyt], [a].[fcheck_resu], [a].[forder], [a].[ybsl], [a].stationName,
+ [a].IS_GWSCAN
+ORDER BY [a].[forder] ASC", releaseNo);
- var Ybsl_In = Db.Queryable<MesQaItemsDetectDetail5>()
- .Where(a => a.ParentGuid == parsedGuid)
- .Count();
+ var Ybsl_In = Db.Ado.SqlQuery<dynamic>(sql).ToList();
- return Db.Queryable<MesQaItemsDetectDetail5, MesQaItemsDetectDetail12>(
- (a, b) =>
- new JoinQueryInfos(JoinType.Left, a.Guid == b.ParentGuid))
- .Where((a, b) => a.ReleaseNo == releaseNo)
- .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
- (a, b) => a.Guid == parsedGuid)
- .GroupBy((a, b) => new
- {
- a.Guid,
- a.ParentGuid,
- a.ReleaseNo,
- a.FacLevel,
- a.FcheckItem,
- a.FcheckTool,
- a.FdownAllow,
- a.FcheckLevel,
- a.Fstand,
- a.FupAllow,
- a.SampleSizeNo,
- a.FspecRequ,
- a.FreQty,
- a.CheckQyt,
- a.FcheckResu,
- a.Order,
- a.Ybsl,
- a.ISRZXX,
- a.IPQCRZXX,
- a.IPQCRZXXDate,
- a.IPQCRZXXNum,
- a.IPQCpscs,
- a.FcheckType,
- }).Select((a, b) => new MesQaItemsDetectDetail5
- {
- Guid = a.Guid,
- ParentGuid = a.ParentGuid,
- ReleaseNo = a.ReleaseNo,
- CheckQyt = a.CheckQyt,
- FacLevel = a.FacLevel,
- FcheckItem = a.FcheckItem,
- FcheckTool = a.FcheckTool,
- FdownAllow = a.FdownAllow,
- FcheckLevel = a.FcheckLevel,
- Fstand = a.Fstand,
- FupAllow = a.FupAllow,
- SampleSizeNo = a.SampleSizeNo,
- FspecRequ = a.FspecRequ,
- FreQty = a.FreQty,
- Factory = "1000",
- Company = "1000",
- FenterQty = SqlFunc.AggregateCount(b.Guid),
- FcheckResu = a.FcheckResu,
- Order = a.Order,
- Ybsl = a.Ybsl,
- YbslIn = Ybsl_In,
- ISRZXX = a.ISRZXX,
- IPQCRZXX = a.IPQCRZXX,
- IPQCRZXXDate = a.IPQCRZXXDate.ToString(),
- IPQCRZXXNum = a.IPQCRZXXNum,
- IPQCpscs = a.IPQCpscs,
- FcheckType =a.FcheckType,
- }).OrderBy(a => a.Order)
- .ToList();
+ return Ybsl_In;
}
public (List<VIpqcXj> item, int TotalCount) getPageXj(XJPageResult queryObj)
{
+
+
+
+ var totalCount = 0;
var parsedGuid = Guid.Empty;
if (!queryObj.id.IsNullOrEmpty())
{
@@ -135,24 +104,83 @@
if (!isValid)
throw new ApplicationException("GUID杞崲閿欒");
}
+ //var pageList = Db.Queryable<VIpqcXj>()
+ // .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
+ // a.ItemNo.Contains(queryObj.keyword) ||
+ // a.ItemName.Contains(queryObj.keyword) ||
+ // a.Daa001.Contains(queryObj.keyword) ||
+ // a.ReleaseNo.Contains(queryObj.keyword) ||
+ // a.DepartmentName.Contains(queryObj.keyword) ||
+ // a.xtName.Contains(queryObj.keyword)
+ // )
+ // .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
+ // a => a.Guid == parsedGuid.ToString())
+ // .Where(a => (a.FSubmit ?? 0) == 0)
+ // .OrderByDescending(a => a.CreateDate)
+ // .OrderBy(a => a.checkTimeSlot)
+ // .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
+
+ var sql = string.Format(@"SELECT [guid],
+ [fsubmit],
+ [MODIFY1_FLAG],
+ [LOT_NO],
+ [FNG_HANDLE],
+ [FVERSION],
+ [LOT_NO1],
+ [FCOVERT_QTY],
+ [FCHECK_RESU],
+ [RELEASE_NO],
+ [DAA001],
+ [FBATCH_QTY],
+ [ITEM_NO],
+ [FCHECK_BY],
+ [FNG_DESC],
+ [CREATE_BY],
+ [DAA013],
+ [sj_xt],
+ [departmentname],
+ [name],
+ [ITEM_NAME],
+ [remarks],
+ [DATE_01],
+ [ITEM_MODEL],
+ [CREATE_DATE],
+ [line_id],
+ [check_time_slot],
+ ROW_NUMBER() OVER (ORDER BY [CREATE_DATE] DESC, [check_time_slot] ASC) AS RowIndex
+FROM [V_IPQC_XJ]
+WHERE
+ ISNULL([fsubmit], 0) = 0
+ AND (
+ -- 澶勭悊22瀛楃鐨勬爣鍑嗘椂闂存 (e.g. '2025-07-29 08:00-10:00')
+ (
+ LEN([check_time_slot]) = 22
+ AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
+ AND CAST(GETDATE() AS time) BETWEEN
+ TRY_CAST(SUBSTRING([check_time_slot], 12, 5) + ':00' AS time)
+ AND TRY_CAST(SUBSTRING([check_time_slot], 18, 5) + ':00' AS time)
+ )
+ OR
+ -- 澶勭悊13瀛楃鐨勫姞鐝牸寮� (e.g. '2025-07-29 鍔犵彮')
+ (
+ LEN([check_time_slot]) = 13
+ AND [check_time_slot] LIKE '%鍔犵彮'
+ AND TRY_CONVERT(date, SUBSTRING([check_time_slot], 1, 10)) = CAST(GETDATE() AS date)
+ AND CAST(GETDATE() AS time) >= '17:00:00'
+ )
+ )
+ {0}",
+ parsedGuid != Guid.Empty ? "AND guid = @parsedGuid" : ""); // 鏂板GUID杩囨护鏉′欢
+
+
- var totalCount = 0;
- var pageList = Db.Queryable<VIpqcXj>()
- .WhereIF(!string.IsNullOrWhiteSpace(queryObj.keyword), a =>
- a.ItemNo.Contains(queryObj.keyword) ||
- a.ItemName.Contains(queryObj.keyword) ||
- a.Daa001.Contains(queryObj.keyword) ||
- a.ReleaseNo.Contains(queryObj.keyword) ||
- a.DepartmentName.Contains(queryObj.keyword) ||
- a.xtName.Contains(queryObj.keyword)
- )
- .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
- a => a.Guid == parsedGuid.ToString())
- .Where(a => (a.FSubmit ?? 0) == 0)
- .OrderByDescending(a => a.CreateDate)
- .ToPageList(queryObj.PageIndex, queryObj.Limit, ref totalCount);
+ var sqlParams = new List<SugarParameter> {
+ new("@parsedGuid", parsedGuid) // 鏂板鍙傛暟
+ };
+
+ var pageList = Db.Ado.SqlQuery<VIpqcXj>(sql,sqlParams).ToList();
return (pageList, totalCount);
}
@@ -337,4 +365,88 @@
return Db.Ado.ExecuteCommand(sql, sqlParams);
}
+
+
+ public dynamic GetIpqcXjDaa(dynamic unity)
+ {
+
+ var sqlParams = new List<SugarParameter> { };
+ sqlParams.Add(new("@xt", unity.xt));
+
+ var sql2 = new StringBuilder(@"
+ SELECT '['+DAA001+']['+DAA021+']' AS daaInfo,DAA001,DAA021
+ FROM WOMDAA
+ WHERE DAA015 = @xt AND daa018 NOT IN ('W:瀹屽伐', 'D:寰呭紑宸�')");
+
+ if (!string.IsNullOrWhiteSpace(unity.selectKey?.ToString()))
+ {
+ sqlParams.Add(new("@selectKey", unity.selectKey));
+ sql2.Append(@"
+ AND (DAA001 LIKE '%' + @selectKey + '%'
+ OR DAA021 LIKE '%' + @selectKey + '%')");
+ }
+
+ var XcslItem = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);
+
+ if (XcslItem == null)
+ {
+ throw new Exception("璇ユ潯浠朵笅鏃犲搴斿伐鍗曚俊鎭紝璇烽噸鏂拌緭鍏ワ紒");
+ }
+
+ return XcslItem;
+ }
+
+
+ public dynamic SelIpqcItemsByGw(dynamic query)
+ {
+ if (query == null) throw new ArgumentNullException(nameof(query));
+
+ if (string.IsNullOrEmpty(query.userAccount?.ToString()))
+ throw new ArgumentException("鐢ㄦ埛鍚嶄笉鍏佽涓虹┖", nameof(query.userAccount));
+
+ if (string.IsNullOrEmpty(query.inOrderGuid1?.ToString()))
+ throw new ArgumentException("妫�楠屽崟涓昏〃GUID涓嶅厑璁镐负绌�", nameof(query.inOrderGuid1));
+
+ var _strMsg = "";
+ var _status = -1;
+
+ using (var conn = new SqlConnection(DbHelperSQL.strConn))
+ using (var cmd = new SqlCommand("prc_selIpqcItemsByGw", conn))
+ {
+ try
+ {
+ conn.Open();
+ cmd.CommandType = CommandType.StoredProcedure;
+
+ SqlParameter[] parameters =
+ {
+ new("@pi_user", SqlDbType.NVarChar, 100) { Value = query.userAccount },
+ new("@pi_gw", SqlDbType.NVarChar, 100) { Value = query.gw },
+ new("@pi_QaGuid1", SqlDbType.NVarChar, 100) { Value = query.inOrderGuid1 },
+ new("@po_outMsg", SqlDbType.NVarChar, 2000) { Direction = ParameterDirection.Output },
+ new("@po_outStatus", SqlDbType.Int) { Direction = ParameterDirection.Output }
+ };
+
+ cmd.Parameters.AddRange(parameters);
+ cmd.ExecuteNonQuery();
+
+ _strMsg = parameters[3].Value?.ToString() ?? "";
+ _status = Convert.ToInt32(parameters[4].Value ?? -1);
+
+ if (_status <= 0) throw new Exception(_strMsg);
+
+ return new
+ {
+ message = _strMsg,
+ status = _status,
+ qaGuid = query.inOrderGuid1,
+ gw = query.gw
+ };
+ }
+ catch (Exception ex)
+ {
+ throw new Exception($"宸ヤ綅鎵弿澶辫触锛歿ex.Message}");
+ }
+ }
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3