From 2e7e9a549a71301b97353b072afc4e09e75bd09c Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 22 三月 2024 10:17:56 +0800
Subject: [PATCH] 1.生产进度报表参数删减 2.新工单派发查找同产品历史最新工序信息
---
VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs | 116 +++++++++++++++++++++++++++++++---------------------------
1 files changed, 62 insertions(+), 54 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
index 162caa9..aa7f995 100644
--- a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
@@ -20,24 +20,29 @@
#region[鐢熶骇杩涘害鎶ヨ〃]
- public static ToMessage ProductionScheduleReportSearch(string status, string wkshopcode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
if (status != "" && status != null)
{
switch (status)
{
case "START":
- search += "and AA.status='START' ";
+ search += "and AA.status='鎵ц涓�' ";
break;
case "CLOSED":
- search += "and AA.status='CLOSED' ";
+ search += "and AA.status='宸插畬鎴�' ";
break;
default:
- search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ search += "and AA.status='鏈紑濮�' ";
break;
}
}
@@ -45,16 +50,6 @@
{
search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
- }
- if (wkshopcode != "" && wkshopcode != null)
- {
- search += "and AA.wkshp_code=@wkshopcode ";
- dynamicParams.Add("@wkshopcode", wkshopcode);
- }
- if (partcode != "" && partcode != null)
- {
- search += "and AA.partcode like '%'+@partcode+'%' ";
- dynamicParams.Add("@partcode", partcode);
}
if (partcode != "" && partcode != null)
{
@@ -84,22 +79,27 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.saleOrderCode,AA.m_po,AA.wo_code,AA.wkshp_code,AA.wkshp_name,
- (case when AA.status='START' then '鎵ц涓�' when AA.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end) as status,
- AA.lm_date,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name from
- (
- select E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name as wkshp_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty,
- (select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
- inner join TStep S on n.step_code=S.stepcode
- where n.wo_code=m.wo_code for xml path('')) as concat_name
- from TK_Wrk_Step m
- inner join TK_Wrk_Man W on m.wo_code=W.wo_code
- left join TKimp_Ewo E on W.m_po=E.wo
- inner join TMateriel_Info P on W.materiel_code=p.partcode
- left join TOrganization F on W.wkshp_code=F.torg_code
- group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
- ) AA
- where " + search;
+ var sql = @"select top 100 percent AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.status,AA.lm_date,
+ AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name
+ from (
+ select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,
+ P.partcode,P.partname,P.partspec,W.plan_qty,
+ (
+ select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+','
+ from TK_Wrk_Step n
+ inner join TStep S on n.step_code=S.stepcode
+ where n.wo_code=W.wo_code for xml path('')
+ ) as concat_name
+ from TK_Wrk_Man W
+ left join (
+ select wo,saleOrderCode from TKimp_Ewo
+ ) as E on W.m_po=E.wo
+ left join TMateriel_Info P on W.materiel_code=p.partcode
+ left join TOrganization F on W.wkshp_code=F.torg_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,W.plan_qty
+ ) as AA where " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
mes.message = "鏌ヨ鎴愬姛!";
@@ -118,24 +118,29 @@
#endregion
#region[鐢熶骇杩涘害鎶ヨ〃瀵煎嚭]
- public static ToMessage ProductionScheduleReportExcelSearch(string status, string wkshopcode, string wocode,string partcode, string partname, string partspec, string opendate, string closedate)
+ public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status, string wocode, string partcode, string partname, string partspec, string opendate, string closedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
if (status != "" && status != null)
{
switch (status)
{
case "START":
- search += "and AA.status='START' ";
+ search += "and AA.status='鎵ц涓�' ";
break;
case "CLOSED":
- search += "and AA.status='CLOSED' ";
+ search += "and AA.status='宸插畬鎴�' ";
break;
default:
- search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ search += "and AA.status='鏈紑濮�' ";
break;
}
}
@@ -143,11 +148,6 @@
{
search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
- }
- if (wkshopcode != "" && wkshopcode != null)
- {
- search += "and AA.wkshp_code=@wkshopcode ";
- dynamicParams.Add("@wkshopcode", wkshopcode);
}
if (partcode != "" && partcode != null)
{
@@ -177,21 +177,29 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.saleOrderCode as ERP婧愬崟鍙�,AA.m_po as ERP鐢熶骇璁㈠崟,AA.wo_code as 鐢熶骇宸ュ崟鍙�,AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,
- (case when AA.status='START' then '鎵ц涓�' when AA.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end) as 宸ュ崟鐘舵��,
- AA.lm_date as 鍗曟嵁鏃ユ湡,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.plan_qty as 浠诲姟鏁伴噺,left(AA.concat_name,len(concat_name)-1) as 鐢熶骇杩涘害 from
- (
- select E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.wkshp_code,F.torg_name as wkshp_name,P.partcode,P.partname,P.partspec,m.plan_qty,
- (select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
- inner join TStep S on n.step_code=S.stepcode
- where n.wo_code=m.wo_code for xml path('')) as concat_name
- from TK_Wrk_Step m
- inner join TK_Wrk_Man W on m.wo_code=W.wo_code
- left join TKimp_Ewo E on W.m_po=E.wo
- inner join TMateriel_Info P on W.materiel_code=p.partcode
- left join TOrganization F on W.wkshp_code=F.torg_code
- group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
- ) AA
+
+ var sql = @"select top 100 percent AA.saleOrderCode as 閿�鍞鍗曞彿,AA.m_po as 鐢熶骇璁㈠崟鍙�,AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 鐢熶骇宸ュ崟鍙�,
+ AA.status as 宸ュ崟鐘舵��,AA.lm_date as 鍗曟嵁鏃ユ湡,AA.route_code as 宸ヨ壓璺嚎缂栧彿,AA.route_name as 宸ヨ壓璺嚎鍚嶇О,
+ AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.plan_qty as 浠诲姟鏁伴噺,left(AA.concat_name,len(concat_name)-1) as 鐢熶骇杩涘害
+ from (
+ select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,
+ P.partcode,P.partname,P.partspec,W.plan_qty,
+ (
+ select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+','
+ from TK_Wrk_Step n
+ inner join TStep S on n.step_code=S.stepcode
+ where n.wo_code=W.wo_code for xml path('')
+ ) as concat_name
+ from TK_Wrk_Man W
+ left join (
+ select wo,saleOrderCode from TKimp_Ewo
+ ) as E on W.m_po=E.wo
+ left join TMateriel_Info P on W.materiel_code=p.partcode
+ left join TOrganization F on W.wkshp_code=F.torg_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,W.plan_qty
+ ) as AA
where " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
--
Gitblit v1.9.3