From 1ab89ba15fd521d83e809f52d6e50133814c042a Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 15 三月 2024 15:25:41 +0800
Subject: [PATCH] 1.订单、工单增加字段(销售业务员) 2.优化生产进度报表查询语句
---
VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs | 136 +++++++++++++++++++++++++++++----------------
1 files changed, 88 insertions(+), 48 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
index 162caa9..a938209 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 saleordercode, string routecode, string routename, 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;
}
}
@@ -46,15 +51,20 @@
search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
- if (wkshopcode != "" && wkshopcode != null)
+ if (saleordercode != "" && saleordercode != null)
{
- search += "and AA.wkshp_code=@wkshopcode ";
- dynamicParams.Add("@wkshopcode", wkshopcode);
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
}
- if (partcode != "" && partcode != null)
+ if (routecode != "" && routecode != null)
{
- search += "and AA.partcode like '%'+@partcode+'%' ";
- dynamicParams.Add("@partcode", partcode);
+ search += "and AA.route_code like '%'+@routecode+'%' ";
+ dynamicParams.Add("@routecode", routecode);
+ }
+ if (routename != "" && routename != null)
+ {
+ search += "and AA.route_name like '%'+@routename+'%' ";
+ dynamicParams.Add("@routename", routename);
}
if (partcode != "" && partcode != null)
{
@@ -84,22 +94,28 @@
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.route_code,AA.route_name,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.org_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,W.route_code,R.name as route_name,
+ 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 TFlw_Rout R on W.route_code=R.code
+ left join TOrganization F on W.wkshp_code=F.org_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,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 +134,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 saleordercode, string routecode, string routename, 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;
}
}
@@ -144,10 +165,20 @@
search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
- if (wkshopcode != "" && wkshopcode != null)
+ if (saleordercode != "" && saleordercode != null)
{
- search += "and AA.wkshp_code=@wkshopcode ";
- dynamicParams.Add("@wkshopcode", wkshopcode);
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ if (routecode != "" && routecode != null)
+ {
+ search += "and AA.route_code like '%'+@routecode+'%' ";
+ dynamicParams.Add("@routecode", routecode);
+ }
+ if (routename != "" && routename != null)
+ {
+ search += "and AA.route_name like '%'+@routename+'%' ";
+ dynamicParams.Add("@routename", routename);
}
if (partcode != "" && partcode != null)
{
@@ -177,21 +208,30 @@
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.org_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,W.route_code,R.name as route_name,
+ 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 TFlw_Rout R on W.route_code=R.code
+ left join TOrganization F on W.wkshp_code=F.org_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,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