From 4f57c3e602eef9ec929a2315183fa28b21cd0987 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 29 三月 2023 10:51:33 +0800
Subject: [PATCH] 订单下达获取工单语句修改、生产工单手工创建获取单据号接口
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 207 ++++++++++++++++++++++++++++++++++-----------------
1 files changed, 137 insertions(+), 70 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index 3769371..fd0601e 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -221,12 +221,17 @@
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍鏌ヨ]
- public static ToMessage GroupSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage GroupSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and P.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
if (wocode != "" && wocode != null)
{
search += "and A.wo_code like '%'+@wocode+'%' ";
@@ -264,29 +269,36 @@
}
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");
+ }
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and A.step_code not in @s1";
+ dynamicParams.Add("@s1", s1);
}
if (search == "")
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //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'
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TK_Wrk_Man K on A.wo_code=K.wo_code
left join TGroup G on B.usergroup_code=G.group_code
- left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
+ left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
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 A.lm_user=U.usercode
- where " + search;
+ where G.group_code<>'' " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
@@ -333,12 +345,17 @@
#endregion
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage GroupSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
+ public static ToMessage GroupSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and P.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
if (wocode != "" && wocode != null)
{
search += "and A.wo_code like '%'+@wocode+'%' ";
@@ -376,31 +393,37 @@
}
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");
+ }
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and A.step_code not in @s1";
+ dynamicParams.Add("@s1", s1);
}
if (search == "")
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //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 鐝粍鍚嶇О,
- 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'
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TK_Wrk_Man K on A.wo_code=K.wo_code
left join TGroup G on B.usergroup_code=G.group_code
- left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
+ left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
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 A.lm_user=U.usercode
- where " + search;
+ where G.group_code<>'' " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
string msg = DownLoad.DataTableToExcel(data, "鐝粍璁′欢宸ヨ祫鎶ヨ〃");
@@ -422,54 +445,64 @@
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃]
- public static ToMessage PeopleSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and AA.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
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");
}
-
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and AA.stepcode not in @s1";
+ dynamicParams.Add("@s1", s1);
+ }
if (search == "")
{
search = "and 1=1 ";
@@ -477,16 +510,23 @@
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.ratio,AA.isend,
+ AA.moneys as usermoney,AA.username,AA.report_date
+ 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.ratio,P.isend,
+ (B.report_qty*isnull(S.unprice,0))*(B.ratio/100) as moneys,U.username,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'
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TK_Wrk_Man K on A.wo_code=K.wo_code
left join TGroup G on B.usergroup_code=G.group_code
- left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
+ left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
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";
@@ -506,54 +546,64 @@
#endregion
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage PeopleSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate)
+ public static ToMessage PeopleSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and AA.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
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");
}
-
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and AA.stepcode not in @s1";
+ dynamicParams.Add("@s1", s1);
+ }
if (search == "")
{
search = "and 1=1 ";
@@ -561,18 +611,23 @@
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.isend as 鏄惁鏈亾宸ュ簭,
+ AA.task_qty as 浠诲姟鏁伴噺,AA.group_code as 鐢熶骇鐝粍缂栫爜,AA.group_name as 鐢熶骇鐝粍鍚嶇О,AA.report_qty as 鎶ュ伐鏁伴噺,AA.unprice as 宸ュ簭鍗曚环,AA.ratio as 鍒嗛厤姣斾緥,
+ AA.moneys as 璁′欢宸ヨ祫,AA.username as 鎶ュ伐浜哄憳,AA.report_date 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.ratio,P.isend,
+ (B.report_qty*isnull(S.unprice,0))*(B.ratio/100) as moneys,U.username,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'
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TK_Wrk_Man K on A.wo_code=K.wo_code
left join TGroup G on B.usergroup_code=G.group_code
- left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
+ left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code
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 +1059,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 +1104,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 +1113,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 +1145,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 +1190,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 +1199,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 +1226,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 +1234,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 +1264,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 +1294,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 +1302,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 +1332,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