From db8ff228ea3060ab41626074b98e9227b21a35d3 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 06 一月 2023 09:43:44 +0800
Subject: [PATCH] App智能分析生产看板接口修改
---
VueWebApi/DLL/DAL/AppAnalyticsDAL.cs | 66 ++++++++++++++++-----------------
1 files changed, 32 insertions(+), 34 deletions(-)
diff --git a/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs b/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
index e78eac8..bee0947 100644
--- a/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
+++ b/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
@@ -28,32 +28,26 @@
{
// --------------鐢熶骇鎬昏--------------
var sql = @"select
- sum(CASE AA.clmname WHEN '璁″垝鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
- sum(CASE AA.clmname WHEN '瀹屽伐鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'end_qty',
- sum(CASE AA.clmname WHEN '鍚堟牸浜у嚭' THEN AA.clmqty ELSE 0 END) as 'good_qty',
- sum(CASE AA.clmname WHEN '缂洪櫡浜у嚭' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
- from(
- select isnull(sum(A.plan_qty),0) as clmqty,'璁″垝鏁伴噺'as clmname from TK_Wrk_Man A
- where A.lm_date between @startopendate and @endclosedate
- union all
- select (isnull(sum(S.report_qty),0)+isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as endqty,'瀹屽伐鏁伴噺' as 瀹屽伐鏁伴噺 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
- where A.lm_date between @startopendate and @endclosedate
- union all
- select isnull(sum(S.report_qty),0) as report_qty,'鍚堟牸浜у嚭' as 鍚堟牸浜у嚭 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
- where A.lm_date between @startopendate and @endclosedate
- union all
- select (isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as ngqty,'缂洪櫡浜у嚭' as 缂洪櫡浜у嚭 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code
- where A.lm_date between @startopendate and @endclosedate
- ) as AA";
+ sum(CASE AA.clmname WHEN '璁″垝鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
+ sum(CASE AA.clmname WHEN '瀹屽伐鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'end_qty',
+ sum(CASE AA.clmname WHEN '鍚堟牸浜у嚭' THEN AA.clmqty ELSE 0 END) as 'good_qty',
+ sum(CASE AA.clmname WHEN '缂洪櫡浜у嚭' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
+ from(
+ select isnull(sum(A.plan_qty),0) as clmqty,'璁″垝鏁伴噺'as clmname from TK_Wrk_Man A
+ where A.lm_date between @startopendate and @endclosedate
+ union all
+ select (isnull(sum(P.good_qty),0)+isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as endqty,'瀹屽伐鏁伴噺' as 瀹屽伐鏁伴噺 from TK_Wrk_Man A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and P.isend='Y'
+ where A.lm_date between @startopendate and @endclosedate
+ union all
+ select isnull(sum(P.good_qty),0) as report_qty,'鍚堟牸浜у嚭' as 鍚堟牸浜у嚭 from TK_Wrk_Man A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and P.isend='Y'
+ where A.lm_date between @startopendate and @endclosedate
+ union all
+ select (isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as ngqty,'缂洪櫡浜у嚭' as 缂洪櫡浜у嚭 from TK_Wrk_Man A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code
+ where A.lm_date between @startopendate and @endclosedate
+ ) as AA";
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
var data0 = DapperHelper.selectdata(sql, dynamicParams);
@@ -73,19 +67,23 @@
switch (defecttype)
{
case "prt": //鎸変骇鍝佺粺璁�
- sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,M.partname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Man A
- inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.materiel_code=B.partnumber
- inner join TMateriel_Info M on A.materiel_code=M.partcode
- where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
- group by M.partname
- order by defect_qty desc";
+ sql2 = @"select top 5 isnull(sum(AA.plan_qty),0) as plan_qty,AA.partname,isnull(sum(AA.defect_qty),0) as defect_qty from (
+ select distinct A.plan_qty,M.partname,B.defect_qty,B.step_code from TK_Wrk_Man A
+ inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.materiel_code=B.partnumber
+ inner join TMateriel_Info M on A.materiel_code=M.partcode
+ where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
+ ) as AA
+ group by AA.step_code,AA.partname
+ order by defect_qty desc";
break;
case "stp"://鎸夊伐搴忕粺璁�
- sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,S.stepname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Step A
+ sql2 = @"select top 5 isnull(sum(AA.plan_qty),0) as plan_qty,AA.stepname,isnull(sum(AA.defect_qty),0) as defect_qty from(
+ select distinct A.plan_qty,S.stepname,B.defect_qty from TK_Wrk_Step A
inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.step_code=B.step_code
inner join TStep S on A.step_code=S.stepcode
where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
- group by S.stepname
+ ) as AA
+ group by AA.stepname
order by defect_qty desc";
break;
default:
--
Gitblit v1.9.3