From c975ff6253fef1e619daa8e82670a8f9e13f721d Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 18 十一月 2022 09:23:56 +0800
Subject: [PATCH] 设备保养项目导入接口修改
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 131 ++++++++++++++++++++++++++-----------------
1 files changed, 79 insertions(+), 52 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index 3769371..919ea7b 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -264,7 +264,7 @@
}
if (operopendate != "" && operopendate != null)
{
- search += "and A.lm_date between @operopendate and @operclosedate ";
+ search += "and B.report_date between @operopendate and @operclosedate ";
dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
}
@@ -276,7 +276,8 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date
+ var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
+ isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,B.report_date
from TK_Wrk_Record A
inner join TK_Wrk_RecordSub B on A.id=B.m_id
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -376,7 +377,7 @@
}
if (operopendate != "" && operopendate != null)
{
- search += "and A.lm_date between @operopendate and @operclosedate ";
+ search += "and B.report_date between @operopendate and @operclosedate ";
dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
}
@@ -390,7 +391,7 @@
var total = 0; //鎬绘潯鏁�
var sql = @"select distinct A.wo_code as 宸ュ崟缂栧彿,M.partcode as 浜у搧缂栫爜,M.partname as 浜у搧鍚嶇О,M.partspec as 浜у搧瑙勬牸,
T.stepcode as 宸ュ簭缂栫爜,T.stepname as 宸ュ簭鍚嶇О,A.task_qty as 浠诲姟鏁伴噺,G.group_code as 鐝粍缂栫爜,G.group_name as 鐝粍鍚嶇О,
- A.good_qty as 鎶ュ伐鏁伴噺,isnull(S.unprice,0) as 宸ュ簭鍗曚环,A.good_qty*isnull(S.unprice,0) as 璁′欢宸ヨ祫,U.username as 鎿嶄綔浜哄憳,A.lm_date as 鎿嶄綔鏃堕棿
+ A.good_qty as 鎶ュ伐鏁伴噺,isnull(S.unprice,0) as 宸ュ簭鍗曚环,A.good_qty*isnull(S.unprice,0) as 璁′欢宸ヨ祫,U.username as 鎿嶄綔浜哄憳,A.lm_date as 鎿嶄綔鏃堕棿,B.report_date as 鎶ュ伐鏃堕棿
from TK_Wrk_Record A
inner join TK_Wrk_RecordSub B on A.id=B.m_id
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -430,42 +431,42 @@
{
if (wocode != "" && wocode != null)
{
- search += "and A.wo_code like '%'+@wocode+'%' ";
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
if (partcode != "" && partcode != null)
{
- search += "and M.partcode like '%'+@partcode+'%' ";
+ search += "and AA.partcode like '%'+@partcode+'%' ";
dynamicParams.Add("@partcode", partcode);
}
if (partname != "" && partname != null)
{
- search += "and M.partname like '%'+@partname+'%' ";
+ search += "and AA.partname like '%'+@partname+'%' ";
dynamicParams.Add("@partname", partname);
}
if (partspec != "" && partspec != null)
{
- search += "and M.partspec like '%'+@partspec+'%' ";
+ search += "and AA.partspec like '%'+@partspec+'%' ";
dynamicParams.Add("@partspec", partspec);
}
if (stepname != "" && stepname != null)
{
- search += "and T.stepname like '%'+@stepname+'%' ";
+ search += "and AA.stepname like '%'+@stepname+'%' ";
dynamicParams.Add("@stepname", stepname);
}
if (groupcode != "" && groupcode != null)
{
- search += "and G.group_code=@groupcode ";
+ search += "and AA.group_code=@groupcode ";
dynamicParams.Add("@groupcode", groupcode);
}
if (reportname != "" && reportname != null)
{
- search += "and U.username like '%'+@reportname+'%' ";
+ search += "and AA.username like '%'+@reportname+'%' ";
dynamicParams.Add("@reportname", reportname);
}
if (reportopendate != "" && reportopendate != null)
{
- search += "and B.report_date between @reportopendate and @reportclosedate ";
+ search += "and AA.report_date between @reportopendate and @reportclosedate ";
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
@@ -477,7 +478,14 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.report_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,B.report_date
+ var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
+ AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,
+ AA.moneys/colum as usermoney,AA.username,AA.report_date,colum
+ from (
+ select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
+ A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
+ B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
+ (select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
from TK_Wrk_Record A
inner join TK_Wrk_RecordSub B on A.id=B.m_id
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -487,6 +495,7 @@
left join TMateriel_Info M on A.materiel_code=M.partcode
left join TStep T on A.step_code=T.stepcode
left join TUser U on B.report_person=U.usercode
+ ) as AA
where " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
@@ -514,42 +523,42 @@
{
if (wocode != "" && wocode != null)
{
- search += "and A.wo_code like '%'+@wocode+'%' ";
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
if (partcode != "" && partcode != null)
{
- search += "and M.partcode like '%'+@partcode+'%' ";
+ search += "and AA.partcode like '%'+@partcode+'%' ";
dynamicParams.Add("@partcode", partcode);
}
if (partname != "" && partname != null)
{
- search += "and M.partname like '%'+@partname+'%' ";
+ search += "and AA.partname like '%'+@partname+'%' ";
dynamicParams.Add("@partname", partname);
}
if (partspec != "" && partspec != null)
{
- search += "and M.partspec like '%'+@partspec+'%' ";
+ search += "and AA.partspec like '%'+@partspec+'%' ";
dynamicParams.Add("@partspec", partspec);
}
if (stepname != "" && stepname != null)
{
- search += "and T.stepname like '%'+@stepname+'%' ";
+ search += "and AA.stepname like '%'+@stepname+'%' ";
dynamicParams.Add("@stepname", stepname);
}
if (groupcode != "" && groupcode != null)
{
- search += "and G.group_code=@groupcode ";
+ search += "and AA.group_code=@groupcode ";
dynamicParams.Add("@groupcode", groupcode);
}
if (reportname != "" && reportname != null)
{
- search += "and U.username like '%'+@reportname+'%' ";
+ search += "and AA.username like '%'+@reportname+'%' ";
dynamicParams.Add("@reportname", reportname);
}
if (reportopendate != "" && reportopendate != null)
{
- search += "and B.report_date between @reportopendate and @reportclosedate ";
+ search += "and AA.report_date between @reportopendate and @reportclosedate ";
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
@@ -561,9 +570,14 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct A.wo_code as 宸ュ崟缂栧彿,M.partcode as 浜у搧缂栫爜,M.partname as 浜у搧鍚嶇О,M.partspec as 浜у搧瑙勬牸,T.stepcode as 宸ュ簭缂栫爜,T.stepname as 宸ュ簭鍚嶇О,
- A.task_qty as 浠诲姟鏁伴噺,G.group_code as 鐢熶骇鐝粍缂栫爜,G.group_name as 鐢熶骇鐝粍鍚嶇О,B.report_qty as 鎶ュ伐鏁伴噺,isnull(S.unprice,0) as 宸ュ簭鍗曚环,
- B.report_qty*isnull(S.unprice,0) as 璁′欢宸ヨ祫,U.username as 鎶ュ伐浜哄憳,B.report_date as 鎶ュ伐鏃堕棿
+ var sql = @"select AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,
+ AA.task_qty as 浠诲姟鏁伴噺,AA.group_code as 鐢熶骇鐝粍缂栫爜,AA.group_name as 鐢熶骇鐝粍鍚嶇О,AA.report_qty as 鎶ュ伐鏁伴噺,AA.unprice as 宸ュ簭鍗曚环,
+ AA.moneys/colum as 璁′欢宸ヨ祫,AA.username as 鎶ュ伐浜哄憳,AA.report_date as 鎶ュ伐鏃堕棿,colum as 鐝粍鎶ュ伐浜烘暟
+ from (
+ select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
+ A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
+ B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
+ (select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
from TK_Wrk_Record A
inner join TK_Wrk_RecordSub B on A.id=B.m_id
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -573,6 +587,7 @@
left join TMateriel_Info M on A.materiel_code=M.partcode
left join TStep T on A.step_code=T.stepcode
left join TUser U on B.report_person=U.usercode
+ ) as AA
where " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
@@ -1004,7 +1019,7 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and A.wkshp_code=@style ";
+ search += "and A.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
}
if (calltypecode != "" && calltypecode != null)
@@ -1049,7 +1064,7 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"select A.wkshp_code,T.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as typename,
@@ -1058,7 +1073,7 @@
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '灏忔椂'
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '鍒�'
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '绉�' AS respondcont,
- (case when A.resp_user is null then '寰呭搷搴�' else '浠ュ搷搴�' end) as status
+ (case when A.resp_user is null then '寰呭搷搴�' else '宸插搷搴�' end) as status
from TAnDon_Task_Info A
left join TOrganization T on A.wkshp_code=T.org_code
left join TEqpInfo E on A.eqp_code=E.code
@@ -1090,7 +1105,7 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and A.wkshp_code=@style ";
+ search += "and A.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
}
if (calltypecode != "" && calltypecode != null)
@@ -1135,7 +1150,7 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"select A.wkshp_code as 杞﹂棿缂栫爜,T.org_name as 杞﹂棿鍚嶇О,A.eqp_code as 璁惧缂栫爜,E.name as 璁惧鍚嶇О,Y.name as 鍛煎彨绫诲瀷,
@@ -1144,7 +1159,7 @@
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '灏忔椂'
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '鍒�'
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '绉�' AS 鍝嶅簲鏃堕暱,
- (case when A.resp_user is null then '寰呭搷搴�' else '浠ュ搷搴�' end) as 鐘舵��
+ (case when A.resp_user is null then '寰呭搷搴�' else '宸插搷搴�' end) as 鐘舵��
from TAnDon_Task_Info A
left join TOrganization T on A.wkshp_code=T.org_code
left join TEqpInfo E on A.eqp_code=E.code
@@ -1171,7 +1186,7 @@
#region[瀹夌伅鎶ヨ〃姹囨�籡
- public static ToMessage AnDonReportSumSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage AnDonReportSumSearch(string wkshopcode,string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -1179,8 +1194,13 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and A.wkshp_code=@style ";
+ search += "and A.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code=@eqpcode ";
+ dynamicParams.Add("@eqpcode", eqpcode);
}
if (calltypecode != "" && calltypecode != null)
{
@@ -1204,17 +1224,18 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select T.org_name as wkshp_name,Y.name as calltypename,
- (select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
- (select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
- (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as repondtime
- from TAnDon_Task_Info A
- left join TOrganization T on A.wkshp_code=T.org_code
- left join TAnDonType Y on A.type=Y.code
- where T.description='W' "+search+" group by A.wkshp_code,T.org_name,A.type,Y.name ";
+ var sql = @"select top 100 percent T.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as calltypename,
+ (select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
+ (select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
+ (select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) as repondtime
+ from TAnDon_Task_Info A
+ left join TOrganization T on A.wkshp_code=T.org_code
+ left join TAnDonType Y on A.type=Y.code
+ left join TEqpInfo E on A.eqp_code=E.code
+ where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
@@ -1233,7 +1254,7 @@
#endregion
#region[瀹夌伅鎶ヨ〃姹囨�诲鍑篯
- public static ToMessage AnDonReportSumExcelSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
+ public static ToMessage AnDonReportSumExcelSearch(string wkshopcode,string eqpcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -1241,8 +1262,13 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and A.wkshp_code=@style ";
+ search += "and A.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code=@eqpcode ";
+ dynamicParams.Add("@eqpcode", eqpcode);
}
if (calltypecode != "" && calltypecode != null)
{
@@ -1266,17 +1292,18 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select T.org_name as 鐢熶骇杞﹂棿,Y.name as 鍛煎彨绫诲瀷,
- (select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as 鍛煎彨娆℃暟,
- (select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as 鍝嶅簲娆℃暟,
- (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as 鍝嶅簲鏃堕暱(鍒�)
- from TAnDon_Task_Info A
- left join TOrganization T on A.wkshp_code=T.org_code
- left join TAnDonType Y on A.type=Y.code
- where T.description='W' " + search + " group by A.wkshp_code,T.org_name,A.type,Y.name ";
+ var sql = @"select top 100 percent T.org_name as 鐢熶骇杞﹂棿,E.name as 璁惧鍚嶇О,Y.name as 鍛煎彨绫诲瀷,
+ (select count(*) callcount from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.eqp_code=A.eqp_code and AA.type=A.type) as 鎬诲懠鍙鏁�,
+ (select count(*) repondcount from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.eqp_code=A.eqp_code and BB.type=A.type and BB.status='CLOSED') as 鎬诲搷搴旀鏁�,
+ (select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) 鎬诲搷搴旀椂闀�
+ from TAnDon_Task_Info A
+ left join TOrganization T on A.wkshp_code=T.org_code
+ left join TAnDonType Y on A.type=Y.code
+ left join TEqpInfo E on A.eqp_code=E.code
+ where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
string msg = DownLoad.DataTableToExcel(data, "瀹夌伅姹囨�绘姤琛�");
--
Gitblit v1.9.3