From 11f8321434ede074d8f6e902b0049a56bcff3bd8 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期一, 06 三月 2023 16:34:10 +0800
Subject: [PATCH] 1.节拍工价新增、编辑加工价类型字段区分自制、外协 2.工序编辑，工序类型切换保存时删除对应节拍工价表 3.工单派发时判断区分自制、外协工序 4.计件工资报表增加剔除工序参数
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 448 +++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 408 insertions(+), 40 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index 1ff7101..9dc3f89 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,35 @@
}
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 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 +344,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 +392,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.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 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 +443,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 +508,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 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 +544,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 +609,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 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鐨勫悕绉�
@@ -992,5 +1045,320 @@
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=@wkshopcode ";
+ 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=@wkshopcode ";
+ 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 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 = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ 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)
+ {
+ 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 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 = "鏌ヨ鎴愬姛!";
+ 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 eqpcode, 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=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code=@eqpcode ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ 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 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, "瀹夌伅姹囨�绘姤琛�");
+ 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