From dd651f9ce0156fc7db869da04e0c2004a1811c52 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 25 十月 2022 21:00:36 +0800
Subject: [PATCH] 安灯报表开发
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 303 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 303 insertions(+), 0 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index 1ff7101..3769371 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -992,5 +992,308 @@
return mes;
}
#endregion
+
+
+
+ #region[瀹夌伅鎶ヨ〃鏄庣粏]
+ public static ToMessage AnDonReportDefinitSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname,string responduser, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wkshp_code=@style ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (calltypecode != "" && calltypecode != null)
+ {
+ search += "and A.type=@calltypecode ";
+ dynamicParams.Add("@calltypecode", calltypecode);
+ }
+ if (calluser != "" && calluser != null)
+ {
+ search += "and A.start_user like '%'+@calluser+'%' ";
+ dynamicParams.Add("@calluser", calluser);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (responduser != "" && responduser != null)
+ {
+ search += "and A.resp_user like '%'+@responduser+'%' ";
+ dynamicParams.Add("@responduser", responduser);
+ }
+ if (callopendate != "" && callopendate != null)
+ {
+ search += "and A.start_date between @callopendate and @callclosedate ";
+ dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
+ dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and A.resp_date between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ 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,
+ A.start_user,A.start_date,A.resp_user,A.resp_date,
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '澶�'
+ + 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
+ 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
+ left join TAnDonType Y on A.type=Y.code
+ where T.description='W' " + search;
+ var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = data.ToList();
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[瀹夌伅鎶ヨ〃鏄庣粏瀵煎嚭]
+ public static ToMessage AnDonReportDefinitExcelSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname, string responduser, string respondopendate, string respondclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wkshp_code=@style ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (calltypecode != "" && calltypecode != null)
+ {
+ search += "and A.type=@calltypecode ";
+ dynamicParams.Add("@calltypecode", calltypecode);
+ }
+ if (calluser != "" && calluser != null)
+ {
+ search += "and A.start_user like '%'+@calluser+'%' ";
+ dynamicParams.Add("@calluser", calluser);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (responduser != "" && responduser != null)
+ {
+ search += "and A.resp_user like '%'+@responduser+'%' ";
+ dynamicParams.Add("@responduser", responduser);
+ }
+ if (callopendate != "" && callopendate != null)
+ {
+ search += "and A.start_date between @callopendate and @callclosedate ";
+ dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
+ dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and A.resp_date between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ 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 鍛煎彨绫诲瀷,
+ A.start_user as 鍛煎彨浜�,A.start_date as 鍛煎彨鏃堕棿,A.resp_user as 鍝嶅簲浜�,A.resp_date as 鍝嶅簲鏃堕棿,
+ CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '澶�'
+ + 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 鐘舵��
+ 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
+ left join TAnDonType Y on A.type=Y.code
+ where T.description='W' " + search;
+ DataTable data = DapperHelper.selectdata(sql, dynamicParams);
+ data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
+ string msg = DownLoad.DataTableToExcel(data, "瀹夌伅鏄庣粏鎶ヨ〃");
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = msg;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+
+ #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)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wkshp_code=@style ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (calltypecode != "" && calltypecode != null)
+ {
+ search += "and A.type=@calltypecode ";
+ dynamicParams.Add("@calltypecode", calltypecode);
+ }
+ if (callopendate != "" && callopendate != null)
+ {
+ search += "and A.start_date between @callopendate and @callclosedate ";
+ dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
+ dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and A.resp_date between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ 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 data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = data.ToList();
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[瀹夌伅鎶ヨ〃姹囨�诲鍑篯
+ public static ToMessage AnDonReportSumExcelSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wkshp_code=@style ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (calltypecode != "" && calltypecode != null)
+ {
+ search += "and A.type=@calltypecode ";
+ dynamicParams.Add("@calltypecode", calltypecode);
+ }
+ if (callopendate != "" && callopendate != null)
+ {
+ search += "and A.start_date between @callopendate and @callclosedate ";
+ dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
+ dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and A.resp_date between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ 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 ";
+ DataTable data = DapperHelper.selectdata(sql, dynamicParams);
+ data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
+ string msg = DownLoad.DataTableToExcel(data, "瀹夌伅姹囨�绘姤琛�");
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = msg;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
}
}
\ No newline at end of file
--
Gitblit v1.9.3