From 0cc1531f55cfa7927abec8624ddce3db45201ee7 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 24 十月 2023 17:24:43 +0800
Subject: [PATCH] 1.班组工资报表功能接口、人员工资明细报表功能接口 2.委外报表功能接口、不良明细报表功能接口、维修明细报表功能接口 3.模具清单查看履历接口、模具上机过滤已上模设备接口 4.模具点检记录报表功能接口、模具保养记录报表功能接口 5.模具维修记录报表功能接口
---
VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs | 1024 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 1,024 insertions(+), 0 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
index a961364..5872621 100644
--- a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
@@ -221,5 +221,1029 @@
return mes;
}
#endregion
+
+
+ #region[鐝粍宸ヨ祫鎶ヨ〃璁板綍鏌ヨ]
+ public static ToMessage GroupSalaryReportSearch(string compute, 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)
+ {
+ 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+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and M.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and M.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and M.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and T.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (groupcode != "" && groupcode != null)
+ {
+ search += "and G.usergroupcode=@groupcode ";
+ dynamicParams.Add("@groupcode", groupcode);
+ }
+ if (username != "" && username != null)
+ {
+ search += "and U.username like '%'+@username+'%' ";
+ dynamicParams.Add("@username", username);
+ }
+ if (operopendate != "" && operopendate != null)
+ {
+ search += "and B.report_date between @operopendate and @operclosedate ";
+ dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
+ dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //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.usergroupcode,G.usergroupname,A.good_qty,
+ isnull(P.stepprice,0) as unprice,A.good_qty*isnull(P.stepprice,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
+ left join TK_Wrk_Man K on A.wo_code=K.wo_code
+ left join TGroup G on B.usergroup_code=G.usergroupcode
+ 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 B.usergroup_code<>'' and A.style='B' " + 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 GroupSalaryReportSearchUser(string id)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鑾峰彇鎶ュ伐浜哄憳
+ sql = @"select S.report_person as usercode,
+ STUFF((SELECT ',' + U.username
+ FROM TUser U
+ WHERE CHARINDEX(',' + U.usercode + ',', ',' + S.report_person + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS username
+ from TK_Wrk_RecordSub S
+ where m_id=@id";
+ dynamicParams.Add("@id", id);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
+ public static ToMessage GroupSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
+ {
+ 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+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and M.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and M.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and M.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and T.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (groupcode != "" && groupcode != null)
+ {
+ search += "and G.usergroupcode=@groupcode ";
+ dynamicParams.Add("@groupcode", groupcode);
+ }
+ if (username != "" && username != null)
+ {
+ search += "and U.username like '%'+@username+'%' ";
+ dynamicParams.Add("@username", username);
+ }
+ if (operopendate != "" && operopendate != null)
+ {
+ search += "and B.report_date between @operopendate and @operclosedate ";
+ dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
+ dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ 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.usergroupcode as 鐝粍缂栫爜,G.usergroupname as 鐝粍鍚嶇О,
+ A.good_qty as 鎶ュ伐鏁伴噺,isnull(P.stepprice,0) as 宸ュ簭鍗曚环,A.good_qty*isnull(P.stepprice,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
+ left join TK_Wrk_Man K on A.wo_code=K.wo_code
+ left join TGroup G on B.usergroup_code=G.usergroupcode
+ 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 B.usergroup_code<>'' and A.style='B' " + 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 PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec,string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, 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 AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepcode != "" && stepcode != null)
+ {
+ search += "and AA.step_code like '%'+@stepcode+'%' ";
+ dynamicParams.Add("@stepcode", stepcode);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (reportname != "" && reportname != null)
+ {
+ search += "and AA.username like '%'+@reportname+'%' ";
+ dynamicParams.Add("@reportname", reportname);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and AA.report_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select * from(
+ select A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
+ A.task_qty,B.report_qty,isnull(A.step_price,0) as step_price,(B.report_qty*isnull(A.step_price,0)) as moneys,
+ B.report_person as usercode,
+ STUFF((SELECT ',' + U.username
+ FROM TUser U
+ WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS username,
+ B.report_date
+ from TK_Wrk_Record A
+ inner join TK_Wrk_RecordSub B on A.id=B.m_id
+ left join TK_Wrk_Man M on A.wo_code=M.wo_code
+ left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
+ left join TStep S on A.step_code=S.stepcode
+ left join TMateriel_Info P on A.materiel_code=P.partcode
+ where A.style='B' and B.style='B' and A.verify='Y' and B.usergroup_code=''
+ union all
+ select A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
+ M.plan_qty as task_qty,B.sqty as report_qty,isnull(A.step_price,0) as step_price,(B.sqty*isnull(A.step_price,0)) as moneys,
+ B.in_person as usercode,
+ STUFF((SELECT ',' + U.username
+ FROM TUser U
+ WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS username,
+ B.in_time as report_date
+ from TK_Wrk_OutRecord A
+ inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
+ left join TK_Wrk_Man M on A.wo_code = M.wo_code
+ left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
+ left join TStep S on A.step_code = S.stepcode
+ left join TMateriel_Info P on A.materiel_code = P.partcode
+ where A.style = 'S' and B.style = 'S' and A.verify='Y'
+ ) as AA
+ where " + 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 PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
+ {
+ 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 AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepcode != "" && stepcode != null)
+ {
+ search += "and AA.step_code like '%'+@stepcode+'%' ";
+ dynamicParams.Add("@stepcode", stepcode);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (reportname != "" && reportname != null)
+ {
+ search += "and AA.username like '%'+@reportname+'%' ";
+ dynamicParams.Add("@reportname", reportname);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and AA.report_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wo_code as '宸ュ崟鍙�',AA.partcode as '浜у搧缂栫爜',AA.partname '浜у搧鍚嶇О',AA.partspec as '瑙勬牸鍨嬪彿',AA.step_seq as '宸ュ簭搴忓彿',
+ AA.step_code as '宸ュ簭缂栫爜',AA.stepname as '宸ュ簭鍚嶇О',(case when AA.steptype='Z' then '鑷埗' else '澶栧崗' end) as '宸ュ簭绫诲瀷',AA.task_qty as '浠诲姟鏁伴噺',AA.report_qty as '鎶ュ伐鏁伴噺',
+ AA.step_price as '宸ュ簭鍗曚环',AA.moneys as '璁′欢宸ヨ祫',AA.usercode as '鎶ュ伐浜哄憳缂栧彿',AA.username as '鎶ュ伐浜哄憳鍚嶇О',AA.report_date as '鎶ュ伐鏃堕棿'
+ from(
+ select A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
+ A.task_qty,B.report_qty,isnull(A.step_price,0) as step_price,(B.report_qty*isnull(A.step_price,0)) as moneys,
+ B.report_person as usercode,
+ STUFF((SELECT ',' + U.username
+ FROM TUser U
+ WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS username,
+ B.report_date
+ from TK_Wrk_Record A
+ inner join TK_Wrk_RecordSub B on A.id=B.m_id
+ left join TK_Wrk_Man M on A.wo_code=M.wo_code
+ left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
+ left join TStep S on A.step_code=S.stepcode
+ left join TMateriel_Info P on A.materiel_code=P.partcode
+ where A.style='B' and B.style='B' and A.verify='Y' and B.usergroup_code=''
+ union all
+ select A.wo_code,A.materiel_code as partcode,P.partname,P.partspec,A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,K.isend,
+ M.plan_qty as task_qty,B.sqty as report_qty,isnull(A.step_price,0) as step_price,(B.sqty*isnull(A.step_price,0)) as moneys,
+ B.in_person as usercode,
+ STUFF((SELECT ',' + U.username
+ FROM TUser U
+ WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS username,
+ B.in_time as report_date
+ from TK_Wrk_OutRecord A
+ inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
+ left join TK_Wrk_Man M on A.wo_code = M.wo_code
+ left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
+ left join TStep S on A.step_code = S.stepcode
+ left join TMateriel_Info P on A.materiel_code = P.partcode
+ where A.style = 'S' and B.style = 'S' and A.verify='Y'
+ ) as AA
+ where " + 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 OutSourceReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and A.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and M.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and M.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and M.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and S.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (suppername != "" && suppername != null)
+ {
+ search += "and C.name like '%'+@suppername+'%' ";
+ dynamicParams.Add("@suppername", suppername);
+ }
+ if (type != "" && type != null)
+ {
+ search += "and A.style=@type ";
+ dynamicParams.Add("@type", type);
+ switch (type)
+ {
+ case "F": //鍙戞枡
+ if (receivopendate != "" && receivopendate != null)
+ {
+ search += "and B.out_time between @receivopendate and @receivclosedate ";
+ dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00");
+ dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59");
+ }
+ break;
+ case "S": //鏀舵枡
+ if (receivopendate != "" && receivopendate != null)
+ {
+ search += "and B.in_time between @receivopendate and @receivclosedate ";
+ dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00");
+ dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59");
+ }
+ break;
+ default:
+ break;
+ }
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,C.code,C.name,(case when A.style='F' then '鍙戞枡' when A.style='S' then '鏀舵枡' end) as style,
+ A.fqty,A.sqty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,U.username as out_person,B.out_time,T.username as in_person,B.in_time
+ from TK_Wrk_OutRecord A
+ inner join TK_Wrk_OutRecordSub B on A.id=B.m_id
+ left join TMateriel_Info M on A.materiel_code=M.partcode
+ left join TStep S on A.step_code=S.stepcode
+ left join TCustomer C on A.wx_code=C.code
+ left join TUser U on B.out_person=U.usercode
+ left join TUser T on B.in_person=T.usercode
+ where " + 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 OutSourceReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and A.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and M.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and M.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and M.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and S.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (suppername != "" && suppername != null)
+ {
+ search += "and C.name like '%'+@suppername+'%' ";
+ dynamicParams.Add("@suppername", suppername);
+ }
+ if (type != "" && type != null)
+ {
+ search += "and A.style=@type ";
+ dynamicParams.Add("@type", type);
+ switch (type)
+ {
+ case "F": //鍙戞枡
+ if (receivopendate != "" && receivopendate != null)
+ {
+ search += "and B.out_time between @receivopendate and @receivclosedate ";
+ dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00");
+ dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59");
+ }
+ break;
+ case "S": //鏀舵枡
+ if (receivopendate != "" && receivopendate != null)
+ {
+ search += "and B.in_time between @receivopendate and @receivclosedate ";
+ dynamicParams.Add("@receivopendate", receivopendate + " 00:00:00");
+ dynamicParams.Add("@receivclosedate", receivclosedate + " 23:59:59");
+ }
+ break;
+ default:
+ break;
+ }
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.wo_code as '宸ュ崟缂栧彿',M.partcode as '浜у搧缂栫爜',M.partname as '浜у搧鍚嶇О',M.partspec as '浜у搧瑙勬牸',S.stepcode as '宸ュ簭缂栫爜',S.stepname as '宸ュ簭鍚嶇О',
+ C.code as '澶栧崗渚涙柟缂栫爜',C.name as '澶栧崗渚涙柟鍚嶇О',(case when A.style='F' then '鍙戞枡' when A.style='S' then '鏀舵枡' end) as '鎿嶄綔绫诲瀷',
+ A.fqty as '鍙戞枡鏁伴噺',A.sqty as '鏀舵枡鏁伴噺',A.ng_qty as '涓嶈壇鏁伴噺',A.laborbad_qty as '宸ュ簾鏁伴噺',A.materielbad_qty as '鏂欏簾鏁伴噺',U.username as '鍙戞枡浜哄憳',B.out_time as '鍙戞枡鏃堕棿',
+ T.username as '鏀舵枡浜哄憳',B.in_time as '鏀舵枡鏃堕棿'
+ from TK_Wrk_OutRecord A
+ inner join TK_Wrk_OutRecordSub B on A.id=B.m_id
+ left join TMateriel_Info M on A.materiel_code=M.partcode
+ left join TStep S on A.step_code=S.stepcode
+ left join TCustomer C on A.wx_code=C.code
+ left join TUser U on B.out_person=U.usercode
+ left join TUser T on B.in_person=T.usercode
+ where " + 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 DefectDetailsReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (defectcode != "" && defectcode != null)
+ {
+ search += "and AA.defect_code like '%'+@defectcode+'%' ";
+ dynamicParams.Add("@defectcode", defectcode);
+ }
+ if (defectname != "" && defectname != null)
+ {
+ search += "and AA.defect_name like '%'+@defectname+'%' ";
+ dynamicParams.Add("@defectname", defectname);
+ }
+ if (reportname != "" && reportname != null)
+ {
+ search += "and AA.lm_user like '%'+@reportname+'%' ";
+ dynamicParams.Add("@reportname", reportname);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and AA.lm_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select * from(
+ select A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + F.name
+ FROM TDefect F
+ WHERE CHARINDEX(',' + F.code + ',', ',' + A.defect_code + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS defect_name,
+ A.style,U.username as lm_user,A.lm_date
+ from CSR_WorkRecord_Defect A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
+ left join TMateriel_Info M on A.partnumber=M.partcode
+ left join TStep T on A.step_code=T.stepcode
+ left join TUser U on A.lm_user=U.usercode
+ ) as AA
+ where " + 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 DefectDetailsReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (defectcode != "" && defectcode != null)
+ {
+ search += "and AA.defect_code like '%'+@defectcode+'%' ";
+ dynamicParams.Add("@defectcode", defectcode);
+ }
+ if (defectname != "" && defectname != null)
+ {
+ search += "and AA.defect_name like '%'+@defectname+'%' ";
+ dynamicParams.Add("@defectname", defectname);
+ }
+ if (reportname != "" && reportname != null)
+ {
+ search += "and AA.lm_user like '%'+@reportname+'%' ";
+ dynamicParams.Add("@reportname", reportname);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and AA.lm_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,
+ AA.stepname as 宸ュ簭鍚嶇О,AA.plan_qty as 浠诲姟鏁伴噺,AA.defect_qty as 涓嶈壇鏁伴噺,AA.defect_code as 缂洪櫡浠ｇ爜,AA.defect_name as 缂洪櫡鍚嶇О,
+ (case when AA.style='B' then '鎶ュ伐' when AA.style='S' then '鏀舵枡' end) as '鎿嶄綔绫诲瀷',AA.lm_user as 鎿嶄綔浜哄憳,AA.lm_date as 鎿嶄綔鏃堕棿
+ from(
+ select A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + F.name
+ FROM TDefect F
+ WHERE CHARINDEX(',' + F.code + ',', ',' + A.defect_code + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS defect_name,
+ A.style,U.username as lm_user,A.lm_date
+ from CSR_WorkRecord_Defect A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
+ left join TMateriel_Info M on A.partnumber=M.partcode
+ left join TStep T on A.step_code=T.stepcode
+ left join TUser U on A.lm_user=U.usercode
+ ) as AA
+ where " + 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 MaintenanceDetailsReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (style != "" && style != null)
+ {
+ search += "and AA.style=@style ";
+ dynamicParams.Add("@style", style);
+ }
+ if (defectname != "" && defectname != null)
+ {
+ search += "and AA.defect_name like '%'+@defectname+'%' ";
+ dynamicParams.Add("@defectname", defectname);
+ }
+ if (repairname != "" && repairname != null)
+ {
+ search += "and AA.lm_user like '%'+@repairname+'%' ";
+ dynamicParams.Add("@repairname", repairname);
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and AA.lm_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,(case when AA.style='B' then '鎶ュ伐' when AA.style='S' then '鏀舵枡' end) as style,
+ AA.plan_qty,AA.repair_qty,AA.laborbad_qty,AA.materielbad_qty,AA.defect_code,AA.defect_name,AA.lm_user,AA.lm_date
+ from(
+ select F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.laborbad_qty,F.materielbad_qty,F.defect_code,
+ defect_name = STUFF(( SELECT ',' + T.name
+ FROM TDefect as T
+ where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
+ ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
+ FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
+ from CSR_WorkRecord_DefectHandle as F
+ inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code
+ left join TMateriel_Info M on F.partnumber=M.partcode
+ left join TStep S on F.step_code=S.stepcode
+ left join TUser U on F.lm_user=U.usercode
+ ) as AA
+ where " + 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 MaintenanceDetailsReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and AA.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and AA.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (style != "" && style != null)
+ {
+ search += "and AA.style=@style ";
+ dynamicParams.Add("@style", style);
+ }
+ if (defectname != "" && defectname != null)
+ {
+ search += "and AA.defect_name like '%'+@defectname+'%' ";
+ dynamicParams.Add("@defectname", defectname);
+ }
+ if (repairname != "" && repairname != null)
+ {
+ search += "and AA.lm_user like '%'+@repairname+'%' ";
+ dynamicParams.Add("@repairname", repairname);
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and AA.lm_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,
+ (case when AA.style='B' then '鎶ュ伐' when AA.style='S' then '鏀舵枡' end) as 鎿嶄綔绫诲瀷,
+ AA.plan_qty as 浠诲姟鏁伴噺,AA.repair_qty as 缁翠慨鏁伴噺,AA.laborbad_qty as 宸ュ簾鏁伴噺,AA.materielbad_qty as 鏂欏簾鏁伴噺,AA.defect_name as 缂洪櫡鍚嶇О,AA.lm_user as 缁翠慨浜哄憳,AA.lm_date as 缁翠慨鏃堕棿
+ from(
+ select F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.laborbad_qty,F.materielbad_qty,F.defect_code,
+ defect_name = STUFF(( SELECT ',' + T.name
+ FROM TDefect as T
+ where PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')>0
+ ORDER BY PATINDEX('%,' + RTRIM(T.code) + ',%',',' + F.defect_code + ',')
+ FOR XML PATH('')), 1, 1,''),U.username as lm_user,F.lm_date
+ from CSR_WorkRecord_DefectHandle as F
+ inner join TK_Wrk_Step P on F.wo_code=P.wo_code and F.step_code=P.step_code
+ left join TMateriel_Info M on F.partnumber=M.partcode
+ left join TStep S on F.step_code=S.stepcode
+ left join TUser U on F.lm_user=U.usercode
+ ) as AA
+ where " + 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
}
}
--
Gitblit v1.9.3