From 9c634fd767aec36ef97c3a814bf7a29c67d20ee1 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 29 三月 2024 10:57:35 +0800
Subject: [PATCH] 修改单据重复显示问题，增加工单源单id关联
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 1583 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 1,566 insertions(+), 17 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index e735e3d..b76adae 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -20,16 +20,26 @@
#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)
+ public static ToMessage OutSourceReportSearch(string wkshopcode, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and W.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
if (wocode != "" && wocode != null)
{
search += "and A.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and E.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
}
if (partcode != "" && partcode != null)
{
@@ -90,8 +100,8 @@
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.bad_qty,U.username as out_person,B.out_time,T.username as in_person,B.in_time
+ var sql = @"select W.wkshp_code,F.org_name as wkshp_name,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.bad_qty,U.username as out_person,B.out_time,T.username as in_person,B.in_time,W.m_po,E.saleOrderCode
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
@@ -99,6 +109,9 @@
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
+ left join TK_Wrk_Man W on A.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization F on W.wkshp_code=F.org_code
where " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
@@ -118,16 +131,26 @@
#endregion
#region[濮斿鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage OutSourceReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate)
+ public static ToMessage OutSourceReportExcelSearch(string wkshopcode, string wocode,string saleordercode, string partcode, string partname, string partspec, string stepname, string suppername, string type, string receivopendate, string receivclosedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and W.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
if (wocode != "" && wocode != null)
{
search += "and A.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and E.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
}
if (partcode != "" && partcode != null)
{
@@ -188,7 +211,7 @@
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 '宸ュ簭鍚嶇О',
+ var sql = @"select W.wkshp_code as 杞﹂棿缂栫爜,F.org_name as 杞﹂棿鍚嶇О,E.saleOrderCode as 閿�鍞鍗曞彿,W.m_po as 璁㈠崟鍙�,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.bad_qty as '鎶ュ簾鏁伴噺',U.username as '鍙戞枡浜哄憳',B.out_time as '鍙戞枡鏃堕棿',
T.username as '鏀舵枡浜哄憳',B.in_time as '鏀舵枡鏃堕棿'
@@ -199,10 +222,13 @@
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
+ left join TK_Wrk_Man W on A.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization F on W.wkshp_code=F.org_code
where " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
- string msg = DownLoad.DataTableToExcel(data, "濮斿鎶ュ伐璁板綍");
+ string msg = DownLoad.DataTableToExcel(data, "濮斿鎶ュ伐璁板綍鎶ヨ〃");
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
mes.count = total;
@@ -221,16 +247,31 @@
#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 wkshopcode, string compute, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and K.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ 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 (saleordercode != "" && saleordercode != null)
+ {
+ search += "and E.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
}
if (partcode != "" && partcode != null)
{
@@ -264,9 +305,484 @@
}
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鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select distinct A.id,K.wkshp_code,F.org_name as wkshp_name,E.saleOrderCode,K.m_po,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
+ left join TK_Wrk_Man K on A.wo_code=K.wo_code
+ left join TKimp_Ewo E on K.m_po=E.wo and K.materiel_code=E.materiel_code
+ left join TGroup G on B.usergroup_code=G.group_code
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
+ left join TOrganization F on K.wkshp_code=F.org_code
+ where G.group_code<>'' " + 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 U.usercode,U.username from TK_Wrk_RecordSub A
+ inner join TUser U on A.report_person=U.usercode
+ 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 wkshopcode, string compute, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and K.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ 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 (saleordercode != "" && saleordercode != null)
+ {
+ search += "and E.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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.group_code=@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 (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鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select distinct K.wkshp_code as 杞﹂棿缂栫爜,F.org_name as 杞﹂棿鍚嶇О,E.saleOrderCode as 閿�鍞鍗曞彿,K.m_po as 璁㈠崟鍙�,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 鎿嶄綔鏃堕棿,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 TKimp_Ewo E on K.m_po=E.wo and K.materiel_code=E.materiel_code
+ left join TGroup G on B.usergroup_code=G.group_code
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
+ left join TOrganization F on K.wkshp_code=F.org_code
+ where G.group_code<>'' " + 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 wkshopcode, string compute, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ 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 (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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 (groupcode != "" && groupcode != null)
+ {
+ search += "and AA.group_code=@groupcode ";
+ dynamicParams.Add("@groupcode", groupcode);
+ }
+ 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 (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 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wkshp_code,AA.wkshp_name,AA.saleOrderCode,AA.m_po,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 K.wkshp_code,F.org_name as wkshp_name,W.saleOrderCode,K.m_po,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
+ left join TK_Wrk_Man K on A.wo_code=K.wo_code
+ left join TKimp_Ewo W on K.m_po=W.wo and K.materiel_code=W.materiel_code
+ left join TGroup G on B.usergroup_code=G.group_code
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
+ left join TOrganization F on K.wkshp_code=F.org_code
+ ) 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 PeopleSalaryReportExcelSearch(string wkshopcode, string compute, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ 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 (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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 (groupcode != "" && groupcode != null)
+ {
+ search += "and AA.group_code=@groupcode ";
+ dynamicParams.Add("@groupcode", groupcode);
+ }
+ 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 (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 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.saleOrderCode as 閿�鍞鍗曞彿,AA.m_po as 鐢熶骇璁㈠崟鍙�,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 K.wkshp_code,F.org_name as wkshp_name,W.saleOrderCode,K.m_po,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
+ left join TK_Wrk_Man K on A.wo_code=K.wo_code
+ left join TKimp_Ewo W on K.m_po=W.wo and K.materiel_code=W.materiel_code
+ left join TGroup G on B.usergroup_code=G.group_code
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
+ left join TOrganization F on K.wkshp_code=F.org_code
+ ) 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 DefectDetailsReportSearch(string wkshopcode, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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.username 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 == "")
@@ -276,16 +792,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,A.good_qty,isnull(S.unprice,0) as unprice,U.username as lm_user,A.lm_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'
- 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
+ var sql = @"select * from(
+ select W.wkshp_code,L.org_name as wkshp_name,E.saleOrderCode,W.m_po,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,A.defect_pendqty,A.bad_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + P.name
+ FROM TDefect P
+ WHERE CHARINDEX(',' + P.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
+ left join TUser U on A.lm_user=U.usercode
+ left join TK_Wrk_Man W on A.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization L on W.wkshp_code=L.org_code
+ ) as AA
where " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
@@ -303,5 +826,1031 @@
return mes;
}
#endregion
+
+ #region[涓嶈壇鏄庣粏鎶ヨ〃瀵煎嚭]
+ public static ToMessage DefectDetailsReportExcelSearch(string wkshopcode, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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.code like '%'+@defectcode+'%' ";
+ dynamicParams.Add("@defectcode", defectcode);
+ }
+ if (defectname != "" && defectname != null)
+ {
+ search += "and AA.name like '%'+@defectname+'%' ";
+ dynamicParams.Add("@defectname", defectname);
+ }
+ if (reportname != "" && reportname != null)
+ {
+ search += "and AA.username 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.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.saleOrderCode as 閿�鍞鍗曞彿,AA.m_po as 鐢熶骇璁㈠崟鍙�,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_pendqty as 涓嶈壇寰呭鐞嗘暟閲�,AA.bad_qty as 鎶ュ簾鏁伴噺,AA.defect_code as 缂洪櫡浠ｇ爜,AA.defect_name as 缂洪櫡鍚嶇О,
+ (case when AA.style='B' then '鎶ュ伐' when AA.style='S' then '鏀舵枡' end) as '鎿嶄綔绫诲瀷',AA.username as 鎿嶄綔浜哄憳,AA.lm_date as 鎿嶄綔鏃堕棿
+ from(
+ select W.wkshp_code,L.org_name as wkshp_name,E.saleOrderCode,W.m_po,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,A.defect_pendqty,A.bad_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + P.name
+ FROM TDefect P
+ WHERE CHARINDEX(',' + P.code + ',', ',' + A.defect_code + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS defect_name,
+ A.style,U.username,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
+ left join TK_Wrk_Man W on A.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization L on W.wkshp_code=L.org_code
+ ) 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 QuaneryDefectReportSearch()
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鑾峰彇鎶ュ伐涓嶈壇鍒嗙粍缁熻鏁版嵁
+ sql = @"select AA.cont,AA.name from (
+ select count(*) cont,F.name from CSR_WorkRecord_Defect A
+ left join TDefect F on A.defect_code=F.code
+ group by F.code,F.name
+ ) as AA order by AA.cont desc";
+ var data = DapperHelper.selecttable(sql);
+ 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 MaintenanceDetailsReportSearch(string wkshopcode, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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.wkshp_code,AA.wkshp_name,AA.saleOrderCode,AA.m_po,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.bad_qty,AA.defect_code,AA.defect_name,AA.lm_user,AA.lm_date
+ from(
+ select W.wkshp_code,L.org_name as wkshp_name,W.m_po,E.saleOrderCode,F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.bad_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
+ left join TK_Wrk_Man W on F.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization L on W.wkshp_code=L.org_code
+ ) 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 wkshopcode, string wocode,string saleordercode, 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 (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ 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.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.saleOrderCode as 閿�鍞鍗曞彿,AA.m_po as 璁㈠崟缂栧彿,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.bad_qty as 鎶ュ簾鏁伴噺,AA.defect_name as 缂洪櫡鍚嶇О,AA.lm_user as 缁翠慨浜哄憳,AA.lm_date as 缁翠慨鏃堕棿
+ from(
+ select W.wkshp_code,L.org_name as wkshp_name,E.saleOrderCode,W.m_po,F.wo_code,M.partcode,M.partname,M.partspec,S.stepcode,S.stepname,F.style,P.plan_qty,F.repair_qty,F.bad_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
+ left join TK_Wrk_Man W on F.wo_code=W.wo_code
+ left join TKimp_Ewo E on W.m_po=E.wo and W.materiel_code=E.materiel_code
+ left join TOrganization L on W.wkshp_code=L.org_code
+ ) 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 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
+
+
+ #region[鐗╂祦妫�楠岃褰曟煡璇
+ public static ToMessage LogisticsCheckSearch(string checktypecode, string partcode, string partname, string partspec, string labcode, string customercode, string batchno, string checkuser, string opencheckdate, string closecheckdate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (checktypecode != "" && checktypecode != null)
+ {
+ search += "and A.check_type=@checktypecode ";
+ dynamicParams.Add("@checktypecode", checktypecode);
+ }
+ 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 (labcode != "" && labcode != null)
+ {
+ search += "and A.hbarcode like '%'+@labcode+'%' ";
+ dynamicParams.Add("@labcode", labcode);
+ }
+ if (customercode != "" && customercode != null)
+ {
+ search += "and A.customer_code=@customercode ";
+ dynamicParams.Add("@customercode", customercode);
+ }
+ if (batchno != "" && batchno != null)
+ {
+ search += "and R.hbatchno like '%'+@batchno+'%' ";
+ dynamicParams.Add("@batchno", labcode);
+ }
+ if (checkuser != "" && checkuser != null)
+ {
+ search += "and U.username like '%'+@checkuser+'%' ";
+ dynamicParams.Add("@checkuser", checkuser);
+ }
+ if (opencheckdate != "" && opencheckdate != null)
+ {
+ search += "and A.lm_date between @opencheckdate and @closecheckdate ";
+ dynamicParams.Add("@opencheckdate", opencheckdate + " 00:00:00");
+ dynamicParams.Add("@closecheckdate", closecheckdate + " 23:59:59");
+ }
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.id,A.check_type,A.check_typename,A.sampmethod,M.partcode,M.partname,M.partspec,R.hbatchno,
+ A.hbarcode,A.customer_code,C.name as customer_name,U.usercode,U.username,A.lm_date,A.check_result
+ from TStepCheckRecord A
+ left join TMateriel_Info M on A.partcode=M.partcode
+ left join T_BarCodeBill R on A.hbarcode=R.hbarcode
+ left join TCustomer C on A.customer_code=C.code
+ left join TUser U on A.check_user=U.usercode
+ where A.check_type in('InCheck','OutCheck') " + 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 LogisticsCheckSubSearch(string checkid)
+ {
+ var sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //瀛樺偍杩囩▼鍚�
+ sql = @"h_p_IFCLD_WuLiuCheckSubSelect";
+ dynamicParams.Add("@checkid", checkid);
+ DataTable dt = DapperHelper.selectProcedure(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ mes.code = "200";
+ mes.count = 0;
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.data = dt;
+ }
+ else
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = "鏃犳楠屾槑缁嗘暟鎹�!";
+ mes.data = null;
+ }
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+
+ #region[鐢熶骇杩涘害鎶ヨ〃]
+ public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string wocode,string saleordercode, string routecode, string routename, string partcode, string partname, string partspec,string opendate,string closedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (status != "" && status != null)
+ {
+ switch (status)
+ {
+ case "START":
+ search += "and AA.status='鎵ц涓�' ";
+ break;
+ case "CLOSED":
+ search += "and AA.status='宸插畬鎴�' ";
+ break;
+ default:
+ search += "and AA.status='鏈紑濮�' ";
+ break;
+ }
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ if (routecode != "" && routecode != null)
+ {
+ search += "and AA.route_code like '%'+@routecode+'%' ";
+ dynamicParams.Add("@routecode", routecode);
+ }
+ if (routename != "" && routename != null)
+ {
+ search += "and AA.route_name like '%'+@routename+'%' ";
+ dynamicParams.Add("@routename", routename);
+ }
+ 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 (opendate != "" && opendate != null)
+ {
+ search += "and AA.lm_date between @opendate and @closedate ";
+ dynamicParams.Add("@opendate", opendate + " 00:00:00");
+ dynamicParams.Add("@closedate", closedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select top 100 percent AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.status,AA.lm_date,
+ AA.route_code,AA.route_name,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name
+ from (
+ select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,W.route_code,R.name as route_name,
+ P.partcode,P.partname,P.partspec,W.plan_qty,
+ (
+ select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+','
+ from TK_Wrk_Step n
+ inner join TStep S on n.step_code=S.stepcode
+ where n.wo_code=W.wo_code for xml path('')
+ ) as concat_name
+ from TK_Wrk_Man W
+ left join (
+ select wo,saleOrderCode from TKimp_Ewo
+ ) as E on W.m_po=E.wo
+ left join TMateriel_Info P on W.materiel_code=p.partcode
+ left join TFlw_Rout R on W.route_code=R.code
+ left join TOrganization F on W.wkshp_code=F.org_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,W.plan_qty
+ ) 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 ProductionScheduleReportExcelSearch(string wkshopcode, string status, string wocode,string saleordercode, string routecode, string routename, string partcode, string partname, string partspec,string opendate,string closedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and AA.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (status != "" && status != null)
+ {
+ switch (status)
+ {
+ case "START":
+ search += "and AA.status='鎵ц涓�' ";
+ break;
+ case "CLOSED":
+ search += "and AA.status='宸插畬鎴�' ";
+ break;
+ default:
+ search += "and AA.status='鏈紑濮�' ";
+ break;
+ }
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ if (routecode != "" && routecode != null)
+ {
+ search += "and AA.route_code like '%'+@routecode+'%' ";
+ dynamicParams.Add("@routecode", routecode);
+ }
+ if (routename != "" && routename != null)
+ {
+ search += "and AA.route_name like '%'+@routename+'%' ";
+ dynamicParams.Add("@routename", routename);
+ }
+ 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 (opendate != "" && opendate != null)
+ {
+ search += "and AA.lm_date between @opendate and @closedate ";
+ dynamicParams.Add("@opendate", opendate + " 00:00:00");
+ dynamicParams.Add("@closedate", closedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+
+ var sql = @"select top 100 percent AA.saleOrderCode as 閿�鍞鍗曞彿,AA.m_po as 鐢熶骇璁㈠崟鍙�,AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 鐢熶骇宸ュ崟鍙�,
+ AA.status as 宸ュ崟鐘舵��,AA.lm_date as 鍗曟嵁鏃ユ湡,AA.route_code as 宸ヨ壓璺嚎缂栧彿,AA.route_name as 宸ヨ壓璺嚎鍚嶇О,
+ AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.plan_qty as 浠诲姟鏁伴噺,left(AA.concat_name,len(concat_name)-1) as 鐢熶骇杩涘害
+ from (
+ select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name as wkshp_name,
+ case when W.status='START' then '鎵ц涓�' when W.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end as status,
+ W.lm_date,W.route_code,R.name as route_name,
+ P.partcode,P.partname,P.partspec,W.plan_qty,
+ (
+ select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+','
+ from TK_Wrk_Step n
+ inner join TStep S on n.step_code=S.stepcode
+ where n.wo_code=W.wo_code for xml path('')
+ ) as concat_name
+ from TK_Wrk_Man W
+ left join (
+ select wo,saleOrderCode from TKimp_Ewo
+ ) as E on W.m_po=E.wo
+ left join TMateriel_Info P on W.materiel_code=p.partcode
+ left join TFlw_Rout R on W.route_code=R.code
+ left join TOrganization F on W.wkshp_code=F.org_code
+ group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,W.plan_qty
+ ) 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
}
}
\ No newline at end of file
--
Gitblit v1.9.3