From dccbfeb549d2dd3b11e16c4073977de24f3e20f7 Mon Sep 17 00:00:00 2001
From: fcx <2246384483@qq.com>
Date: 星期二, 25 十一月 2025 14:40:11 +0800
Subject: [PATCH] 平板端上方添加一个搜索框提供查询
---
StandardPda/MES.Service/service/QC/ProductionLineService.cs | 509 ++++++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 404 insertions(+), 105 deletions(-)
diff --git a/StandardPda/MES.Service/service/QC/ProductionLineService.cs b/StandardPda/MES.Service/service/QC/ProductionLineService.cs
index e3c1202..cfb78ee 100644
--- a/StandardPda/MES.Service/service/QC/ProductionLineService.cs
+++ b/StandardPda/MES.Service/service/QC/ProductionLineService.cs
@@ -81,52 +81,171 @@
// 绗竴姝ワ細鏌ヨ宸ュ崟鍩虹鏁版嵁
// 绗竴姝ワ細鏌ヨ宸ュ崟鍩虹鏁版嵁
+ // var workOrderSql = @"
+ // SELECT
+ // A.ID,
+ // A.DAA001,
+ // item.ITEM_NO AS ItemNo,
+ // A.DAA003,
+ // A.DAA004,
+ // A.DAA008,
+ // A.DAA018,
+ // A.DAA019 AS DaA019,
+ // A.DAA020 AS Line,
+
+ // A.PID AS Pbaid,
+ // T.PROJECT_CODE AS ProjecT_CODE,
+ // u.FNAME AS FName,
+ // 0 AS Urgent,
+ // 0 AS IsFirst,
+ // CAST(NULL AS VARCHAR2(50)) AS BillNo,
+ // CAST(NULL AS VARCHAR2(50)) AS Result,
+ // CAST(BE1.FNAME AS VARCHAR2(50)) AS StatusUser,
+ // CAST(NULL AS DATE) AS CreateTime,
+ // CAST(NULL AS NUMBER) AS InspectionId
+ // FROM WOMDAA A
+ // LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
+ // LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
+ // LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
+ //left join MES_ITEMS item on item.ITEM_ID=c.CAA006
+ // LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
+ // LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
+ // LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
+ // WHERE A.DAA018 LIKE '%宸插紑宸�%'
+ // AND A.FSTATUS = 1
+ // AND A.F_TYPE = 0
+ // AND CASE
+ // WHEN C.CAA023 = '0' THEN '寮�绔�'
+ // WHEN C.CAA023 = '1' THEN '宸叉牳鍑�'
+ // WHEN C.CAA023 = '2' THEN '寮�宸�'
+ // WHEN C.CAA023 = '3' THEN '瀹屽伐'
+ // WHEN C.CAA023 = '4' THEN '鏍稿噯涓�'
+ // END LIKE '%寮�宸�%'";
+
+ // // 娣诲姞鎼滅储鏉′欢
+ // if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
+ // {
+ // workOrderSql += @"
+ // AND (
+ // A.DAA001 LIKE :SearchValue
+ // OR T.PROJECT_CODE LIKE '%' || :SearchValue || '%' -- 椤圭洰(鏉ヨ嚜MES_PROJECT琛�)
+ // OR A.DAA001 LIKE '%' || :SearchValue || '%' -- 宸ュ崟鍙�(鏉ヨ嚜WOMDAA琛�)
+ // OR item.ITEM_NO LIKE '%' || :SearchValue || '%' -- 鐗╂枡鍙�(鏉ヨ嚜MES_ITEMS琛�)
+ // OR item.ITEM_NAME LIKE '%' || :SearchValue || '%' -- 鐗╂枡鍚嶇О(鏉ヨ嚜MES_ITEMS琛�)
+ // )";
+ // }
+
+
+
var workOrderSql = @"
- SELECT
- A.ID,
- A.DAA001,
- item.ITEM_NO AS ItemNo,
- A.DAA003,
- A.DAA004,
- A.DAA008,
- A.DAA018,
- A.DAA019 AS DaA019,
- A.DAA020 AS Line,
-
- A.PID AS Pbaid,
- T.PROJECT_CODE AS ProjecT_CODE,
- u.FNAME AS FName,
- 0 AS Urgent,
- 0 AS IsFirst,
- CAST(NULL AS VARCHAR2(50)) AS BillNo,
- CAST(NULL AS VARCHAR2(50)) AS Result,
- CAST(BE1.FNAME AS VARCHAR2(50)) AS StatusUser,
- CAST(NULL AS DATE) AS CreateTime,
- CAST(NULL AS NUMBER) AS InspectionId
- FROM WOMDAA A
- LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
- LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
- LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
- left join MES_ITEMS item on item.ITEM_ID=c.CAA006
- LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
- LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
- LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
- WHERE A.DAA018 LIKE '%宸插紑宸�%'
- AND A.FSTATUS = 1
- AND A.F_TYPE = 0
- AND CASE
- WHEN C.CAA023 = '0' THEN '寮�绔�'
- WHEN C.CAA023 = '1' THEN '宸叉牳鍑�'
- WHEN C.CAA023 = '2' THEN '寮�宸�'
- WHEN C.CAA023 = '3' THEN '瀹屽伐'
- WHEN C.CAA023 = '4' THEN '鏍稿噯涓�'
- END LIKE '%寮�宸�%'";
+
+SELECT
+
+ A.ID,
+
+ A.DAA001,
+
+ item.ITEM_NO AS ItemNo,
+
+ A.DAA003,
+
+ A.DAA004,
+
+ A.DAA008,
+
+ A.DAA018,
+
+ A.DAA019 AS DaA019,
+
+ A.DAA020 AS Line,
+
+
+
+ A.PID AS Pbaid,
+
+ T.PROJECT_CODE AS ProjecT_CODE,
+
+ u.FNAME AS FName,
+
+ 0 AS Urgent,
+
+ 0 AS IsFirst,
+
+ CAST(NULL AS VARCHAR2(50)) AS BillNo,
+
+ CAST(NULL AS VARCHAR2(50)) AS Result,
+
+ CAST(BE1.FNAME AS VARCHAR2(50)) AS StatusUser,
+
+ CAST(NULL AS DATE) AS CreateTime,
+
+ CAST(NULL AS NUMBER) AS InspectionId
+
+FROM WOMDAA A
+
+LEFT JOIN WOMCAA c ON c.CAA001 = A.DAA021
+
+LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
+
+LEFT JOIN MES_UNIT u ON TO_CHAR(u.ID) = TO_CHAR(A.DAA005)
+
+LEFT JOIN MES_ITEMS item ON item.ITEM_ID = c.CAA006
+
+LEFT JOIN MES_LINE ml ON ml.LINE_NO = A.DAA020
+
+LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
+
+LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
+
+LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
+
+WHERE A.DAA018 LIKE '%宸插紑宸�%'
+
+ AND A.FSTATUS = 1
+
+ AND A.F_TYPE = 0
+
+ AND CASE
+
+ WHEN C.CAA023 = '0' THEN '寮�绔�'
+
+ WHEN C.CAA023 = '1' THEN '宸叉牳鍑�'
+
+ WHEN C.CAA023 = '2' THEN '寮�宸�'
+
+ WHEN C.CAA023 = '3' THEN '瀹屽伐'
+
+ WHEN C.CAA023 = '4' THEN '鏍稿噯涓�'
+
+ END LIKE '%寮�宸�%'";
// 娣诲姞鎼滅储鏉′欢
- if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
+
+ if (!string.IsNullOrEmpty(queryObj.SearchValue))
{
- workOrderSql += " AND (A.DAA001 LIKE :SearchValue OR A.DAA002 LIKE :SearchValue)";
+ switch (queryObj.SelectedIndex)
+ {
+ case "2": // 宸ュ崟鍙�
+ workOrderSql += " AND A.DAA001 LIKE '%' || :SearchValue || '%'";
+ break;
+ case "3": // 鐗╂枡鍙�
+ workOrderSql += " AND item.ITEM_NO LIKE '%' || :SearchValue || '%'";
+ break;
+ case "4": // 鐗╂枡鍚嶇О
+ workOrderSql += " AND item.ITEM_NAME LIKE '%' || :SearchValue || '%'";
+ break;
+ case "0": // 椤圭洰缂栫爜
+ workOrderSql += " AND T.PROJECT_CODE LIKE '%' || :SearchValue || '%'";
+ break;
+ case "1": // 绾夸綋
+ workOrderSql += " AND A.DAA020 LIKE '%' || :SearchValue || '%'";
+ break;
+ default:
+ return (new List<ProductionLinePageResult>(), 0);
+ }
}
+ // 鏍规嵁 selectedIndex 娣诲姞棰濆鐨勬悳绱㈡潯浠�
+
var workOrders = db.SqlQueryable<ProductionLinePageResult>(workOrderSql)
.AddParameters(new { SearchValue = $"%{queryObj.SearchValue}%" })
@@ -257,81 +376,261 @@
{
var totalCount = 0;
+ // var sql = @"
+ // SELECT
+ // A.ID AS InspectionId,
+ // A.BILL_NO AS BillNo,
+ // A.PBAID AS Pbaid,
+ // DA.DAA001,
+ // item.ITEM_NO AS ItemNo,
+ // DA.DAA003,
+ // DA.DAA004,
+ // DA.DAA008,
+ // DA.DAA018,
+ // DA.DAA019 AS DaA019,
+ // DA.DAA020 AS Line,
+ // A.CREATE_TIME AS CreateTime,
+ // NVL(BE3.FNAME, BE1.FNAME) AS StatusUser,
+ // A.FSUBMIT,
+ // A.CREATE_BY,
+ // 0 AS Urgent,
+ // 0 AS IsFirst,
+ // T.PROJECT_CODE AS ProjecT_CODE,
+ // CASE
+ // WHEN 妫�楠岀粨鏋� IS NULL THEN '鏈畬鎴�'
+ // ELSE 妫�楠岀粨鏋�
+ // END AS Result
+ // FROM QS_ITEM_IPI_REQ A
+ // LEFT JOIN WOMDAA DA ON DA.ID = A.PBAID
+ // LEFT JOIN WOMCAA c ON c.CAA001 = DA.DAA021
+ // LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
+ // left join MES_ITEMS item on item.ITEM_ID=c.CAA006
+ //--鍙樻洿鍚�
+ //LEFT JOIN INSPECTOR_CHANGE_SJ_LOG chlog
+ // ON chlog.INSPECTION_NO = a.BILL_NO AND chlog.IS_VALID = 'Y'
+ // LEFT JOIN SYS_USER BE3 ON chlog.INSPECTOR = BE3.FCODE
+ //--鍙樻洿鍓�
+ // LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
+ // LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
+ // LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
+ // LEFT JOIN (
+ // SELECT
+ // PID,
+ // CASE
+ // WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
+ // WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '鍚堟牸'
+ // ELSE '涓嶅悎鏍�'
+ // END AS 妫�楠岀粨鏋�
+ // FROM (
+ // SELECT
+ // A.PID,
+ // CASE
+ // WHEN (A.IS_PASS IS NULL OR B.瀹屾垚鐘舵�� IS NULL) THEN -1
+ // WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 1) THEN 1
+ // WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 0) THEN 0
+ // ELSE -1
+ // END AS PASSED
+ // FROM QS_ITEM_IPI_ITEM A
+ // LEFT JOIN (
+ // SELECT
+ // PID,
+ // CASE
+ // WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
+ // WHEN (COUNT(CASE WHEN FSTAND = '鈭�' THEN 1 END) = COUNT(1)) THEN 1
+ // ELSE 0
+ // END AS 瀹屾垚鐘舵��
+ // FROM QS_ITEM_IPI_ITEM_DETAIL
+ // GROUP BY PID
+ // ) B ON A.ID = B.PID
+ // )
+ // GROUP BY PID
+ // ) RES ON RES.PID = A.ID
+ // WHERE A.ID IS NOT NULL";
+
+ // // 娣诲姞鎼滅储鏉′欢
+ // if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
+ // {
+ // sql += sql += @"
+ // AND (
+ // A.BILL_NO LIKE :SearchValue
+ // OR DA.DAA001 LIKE :SearchValue
+ // OR DA.DAA002 LIKE :SearchValue
+ // OR T.PROJECT_CODE LIKE '%' || :SearchValue || '%' -- 椤圭洰(鏉ヨ嚜MES_PROJECT琛�)
+ // OR ml.LINE_NAME LIKE '%' || :SearchValue || '%' -- 绾夸綋(鏉ヨ嚜MES_LINE琛�)
+ // OR A.DAA001 LIKE '%' || :SearchValue || '%' -- 宸ュ崟鍙�(鏉ヨ嚜WOMDAA琛�)
+ // OR item.ITEM_NO LIKE '%' || :SearchValue || '%' -- 鐗╂枡鍙�(鏉ヨ嚜MES_ITEMS琛�)
+ // OR item.ITEM_NAME LIKE '%' || :SearchValue || '%' -- 鐗╂枡鍚嶇О(鏉ヨ嚜MES_ITEMS琛�)
+ // )";
+
+ // }
+
+
var sql = @"
+
+ SELECT
+
+ A.ID AS InspectionId,
+
+ A.BILL_NO AS BillNo,
+
+ A.PBAID AS Pbaid,
+
+ DA.DAA001,
+
+ item.ITEM_NO AS ItemNo,
+
+ DA.DAA003,
+
+ DA.DAA004,
+
+ DA.DAA008,
+
+ DA.DAA018,
+
+ DA.DAA019 AS DaA019,
+
+ DA.DAA020 AS Line,
+
+ A.CREATE_TIME AS CreateTime,
+
+ NVL(BE3.FNAME, BE1.FNAME) AS StatusUser,
+
+ A.FSUBMIT,
+
+ A.CREATE_BY,
+
+ 0 AS Urgent,
+
+ 0 AS IsFirst,
+
+ T.PROJECT_CODE AS ProjecT_CODE,
+
+ CASE
+
+ WHEN 妫�楠岀粨鏋� IS NULL THEN '鏈畬鎴�'
+
+ ELSE 妫�楠岀粨鏋�
+
+ END AS Result
+
+ FROM QS_ITEM_IPI_REQ A
+
+ LEFT JOIN WOMDAA DA ON DA.ID = A.PBAID
+
+ LEFT JOIN WOMCAA c ON c.CAA001 = DA.DAA021
+
+ LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
+
+ LEFT JOIN MES_ITEMS item ON item.ITEM_ID = c.CAA006
+
+ LEFT JOIN MES_LINE ml ON ml.LINE_NO = DA.DAA020
+
+ --鍙樻洿鍚�
+
+ LEFT JOIN INSPECTOR_CHANGE_SJ_LOG chlog
+
+ ON chlog.INSPECTION_NO = a.BILL_NO AND chlog.IS_VALID = 'Y'
+
+ LEFT JOIN SYS_USER BE3 ON chlog.INSPECTOR = BE3.FCODE
+
+ --鍙樻洿鍓�
+
+ LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
+
+ LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
+
+ LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
+
+ LEFT JOIN (
+
+ SELECT
+
+ PID,
+
+ CASE
+
+ WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
+
+ WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '鍚堟牸'
+
+ ELSE '涓嶅悎鏍�'
+
+ END AS 妫�楠岀粨鏋�
+
+ FROM (
+
SELECT
- A.ID AS InspectionId,
- A.BILL_NO AS BillNo,
- A.PBAID AS Pbaid,
- DA.DAA001,
- item.ITEM_NO AS ItemNo,
- DA.DAA003,
- DA.DAA004,
- DA.DAA008,
- DA.DAA018,
- DA.DAA019 AS DaA019,
- DA.DAA020 AS Line,
- A.CREATE_TIME AS CreateTime,
- NVL(BE3.FNAME, BE1.FNAME) AS StatusUser,
- A.FSUBMIT,
- A.CREATE_BY,
- 0 AS Urgent,
- 0 AS IsFirst,
- T.PROJECT_CODE AS ProjecT_CODE,
+
+ A.PID,
+
CASE
- WHEN 妫�楠岀粨鏋� IS NULL THEN '鏈畬鎴�'
- ELSE 妫�楠岀粨鏋�
- END AS Result
- FROM QS_ITEM_IPI_REQ A
- LEFT JOIN WOMDAA DA ON DA.ID = A.PBAID
- LEFT JOIN WOMCAA c ON c.CAA001 = DA.DAA021
- LEFT JOIN MES_PROJECT T ON C.PROJECT = T.ID
- left join MES_ITEMS item on item.ITEM_ID=c.CAA006
---鍙樻洿鍚�
-LEFT JOIN INSPECTOR_CHANGE_SJ_LOG chlog
- ON chlog.INSPECTION_NO = a.BILL_NO AND chlog.IS_VALID = 'Y'
- LEFT JOIN SYS_USER BE3 ON chlog.INSPECTOR = BE3.FCODE
---鍙樻洿鍓�
- LEFT JOIN IQC_BEFORE_SJ BE ON BE.ITEM_ID = item.ITEM_ID
- LEFT JOIN IQC_BEFORE_FROM_SJ BE2 ON BE.PID = BE2.ID
- LEFT JOIN SYS_USER BE1 ON BE2.SID = BE1.FID
+
+ WHEN (A.IS_PASS IS NULL OR B.瀹屾垚鐘舵�� IS NULL) THEN -1
+
+ WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 1) THEN 1
+
+ WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 0) THEN 0
+
+ ELSE -1
+
+ END AS PASSED
+
+ FROM QS_ITEM_IPI_ITEM A
+
LEFT JOIN (
+
SELECT
+
PID,
+
CASE
- WHEN (COUNT(CASE WHEN PASSED = -1 THEN 1 END) > 0) THEN NULL
- WHEN (COUNT(CASE WHEN PASSED = 1 THEN 1 END) = COUNT(*)) THEN '鍚堟牸'
- ELSE '涓嶅悎鏍�'
- END AS 妫�楠岀粨鏋�
- FROM (
- SELECT
- A.PID,
- CASE
- WHEN (A.IS_PASS IS NULL OR B.瀹屾垚鐘舵�� IS NULL) THEN -1
- WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 1) THEN 1
- WHEN (NVL(A.IS_PASS, -1) = NVL(B.瀹屾垚鐘舵��, -1) AND A.IS_PASS = 0) THEN 0
- ELSE -1
- END AS PASSED
- FROM QS_ITEM_IPI_ITEM A
- LEFT JOIN (
- SELECT
- PID,
- CASE
- WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
- WHEN (COUNT(CASE WHEN FSTAND = '鈭�' THEN 1 END) = COUNT(1)) THEN 1
- ELSE 0
- END AS 瀹屾垚鐘舵��
- FROM QS_ITEM_IPI_ITEM_DETAIL
- GROUP BY PID
- ) B ON A.ID = B.PID
- )
+
+ WHEN (COUNT(CASE WHEN FSTAND IS NULL THEN 1 END) > 0) THEN NULL
+
+ WHEN (COUNT(CASE WHEN FSTAND = '鈭�' THEN 1 END) = COUNT(1)) THEN 1
+
+ ELSE 0
+
+ END AS 瀹屾垚鐘舵��
+
+ FROM QS_ITEM_IPI_ITEM_DETAIL
+
GROUP BY PID
- ) RES ON RES.PID = A.ID
- WHERE A.ID IS NOT NULL";
+
+ ) B ON A.ID = B.PID
+
+ )
+
+ GROUP BY PID
+
+ ) RES ON RES.PID = A.ID
+
+ WHERE A.ID IS NOT NULL";
// 娣诲姞鎼滅储鏉′欢
- if (StringUtil.IsNotNullOrEmpty(queryObj.SearchValue))
+
+ if (!string.IsNullOrEmpty(queryObj.SearchValue))
{
- sql += " AND (A.BILL_NO LIKE :SearchValue OR DA.DAA001 LIKE :SearchValue OR DA.DAA002 LIKE :SearchValue)";
+ switch (queryObj.SelectedIndex)
+ {
+ case "2": // 宸ュ崟鍙�
+ sql += " AND DA.DAA001 LIKE '%' || :SearchValue || '%'";
+ break;
+ case "3": // 鐗╂枡鍙�
+ sql += " AND item.ITEM_NO LIKE '%' || :SearchValue || '%'";
+ break;
+ case "4": // 鐗╂枡鍚嶇О
+ sql += " AND item.ITEM_NAME LIKE '%' || :SearchValue || '%'";
+ break;
+ case "0": // 椤圭洰缂栫爜
+ sql += " AND T.PROJECT_CODE LIKE '%' || :SearchValue || '%'";
+ break;
+ case "1": // 绾夸綋
+ sql += " AND DA.DAA020 LIKE '%' || :SearchValue || '%'";
+ break;
+ default:
+ return (new List<ProductionLinePageResult>(), 0);
+ }
}
sql += " ORDER BY A.CREATE_TIME DESC";
--
Gitblit v1.9.3