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 | 220 +++++++++++++++++++++++++++++++++++++-----------------
1 files changed, 151 insertions(+), 69 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index ea4717b..b76adae 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -20,7 +20,7 @@
#region[濮斿鎶ヨ〃璁板綍鏌ヨ]
- public static ToMessage OutSourceReportSearch(string wkshopcode, 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 = "";
@@ -35,6 +35,11 @@
{
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)
{
@@ -96,7 +101,7 @@
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
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
+ 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
@@ -105,6 +110,7 @@
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);
@@ -125,7 +131,7 @@
#endregion
#region[濮斿鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage OutSourceReportExcelSearch(string wkshopcode, 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 = "";
@@ -133,13 +139,18 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and K.wkshp_code=@wkshopcode ";
+ 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)
{
@@ -200,7 +211,7 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select W.wkshp_code as 杞﹂棿缂栫爜,F.org_name as 杞﹂棿鍚嶇О,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 '鏀舵枡鏃堕棿'
@@ -212,6 +223,7 @@
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);
@@ -235,7 +247,7 @@
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍鏌ヨ]
- public static ToMessage GroupSalaryReportSearch(string wkshopcode, string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, string rejectstepcode, int startNum, int endNum, string prop, string order)
+ 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 = "";
@@ -255,6 +267,11 @@
{
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)
{
@@ -305,12 +322,13 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct A.id,K.wkshp_code,F.org_name as wkshp_name,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,
+ 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
@@ -364,7 +382,7 @@
#endregion
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage GroupSalaryReportExcelSearch(string wkshopcode, string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode)
+ 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 = "";
@@ -384,6 +402,11 @@
{
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)
{
@@ -434,13 +457,14 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct K.wkshp_code as 杞﹂棿缂栫爜,F.org_name as 杞﹂棿鍚嶇О,A.wo_code as 宸ュ崟缂栧彿,M.partcode as 浜у搧缂栫爜,M.partname as 浜у搧鍚嶇О,M.partspec as 浜у搧瑙勬牸,
+ 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
@@ -469,7 +493,7 @@
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃]
- public static ToMessage PeopleSalaryReportSearch(string wkshopcode, string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
+ 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 = "";
@@ -489,6 +513,11 @@
{
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)
{
@@ -539,17 +568,18 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
+ 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, A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
+ 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
@@ -576,7 +606,7 @@
#endregion
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage PeopleSalaryReportExcelSearch(string wkshopcode, string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate,string rejectstepcode)
+ 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 = "";
@@ -596,6 +626,11 @@
{
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)
{
@@ -646,17 +681,18 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,AA.isend as 鏄惁鏈亾宸ュ簭,
+ 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,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
+ 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
@@ -686,7 +722,7 @@
#region[涓嶈壇鏄庣粏鎶ヨ〃]
- public static ToMessage DefectDetailsReportSearch(string wkshopcode, 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)
+ 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 = "";
@@ -701,6 +737,11 @@
{
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)
{
@@ -752,7 +793,7 @@
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"select * from(
- select W.wkshp_code,L.org_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ 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
@@ -765,6 +806,7 @@
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;
@@ -786,7 +828,7 @@
#endregion
#region[涓嶈壇鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage DefectDetailsReportExcelSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string defectcode, string defectname, string reportname, string reportopendate, string reportclosedate)
+ 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 = "";
@@ -801,6 +843,11 @@
{
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)
{
@@ -851,11 +898,11 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name 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_code as 缂洪櫡浠ｇ爜,AA.defect_name as 缂洪櫡鍚嶇О,
+ 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,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ 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
@@ -868,6 +915,7 @@
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;
@@ -922,7 +970,7 @@
#region[缁翠慨鏄庣粏鎶ヨ〃]
- public static ToMessage MaintenanceDetailsReportSearch(string wkshopcode, 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)
+ 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 = "";
@@ -937,6 +985,11 @@
{
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)
{
@@ -987,10 +1040,10 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wkshp_code,AA.wkshp_name,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,
+ 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,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,
+ 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
@@ -1002,6 +1055,7 @@
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;
@@ -1023,7 +1077,7 @@
#endregion
#region[缁翠慨鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage MaintenanceDetailsReportExcelSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, string stepname, string style, string defectname, string repairname, string repairopendate, string repairclosedate)
+ 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 = "";
@@ -1038,6 +1092,11 @@
{
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)
{
@@ -1088,11 +1147,11 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,
+ 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,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,
+ 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
@@ -1104,6 +1163,7 @@
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;
@@ -1561,7 +1621,7 @@
#region[鐢熶骇杩涘害鎶ヨ〃]
- public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string wocode, string routecode, string routename, string partcode, string partname, string partspec,string opendate,string closedate, int startNum, int endNum, string prop, string order)
+ 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 = "";
@@ -1577,13 +1637,13 @@
switch (status)
{
case "START":
- search += "and AA.status='START' ";
+ search += "and AA.status='鎵ц涓�' ";
break;
case "CLOSED":
- search += "and AA.status='CLOSED' ";
+ search += "and AA.status='宸插畬鎴�' ";
break;
default:
- search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ search += "and AA.status='鏈紑濮�' ";
break;
}
}
@@ -1591,6 +1651,11 @@
{
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)
{
@@ -1630,23 +1695,28 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,
- (case when AA.status='START' then '鎵ц涓�' when AA.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end) as 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,m.wo_code,W.wkshp_code,F.org_name as wkshp_name,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.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=m.wo_code for xml path('')) as concat_name
- from TK_Wrk_Step m
- inner join TK_Wrk_Man W on m.wo_code=W.wo_code
- left join TKimp_Ewo E on W.m_po=E.wo
- inner join TMateriel_Info P on W.materiel_code=p.partcode
- inner 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,m.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
- ) AA
- where " + search;
+ 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 = "鏌ヨ鎴愬姛!";
@@ -1665,7 +1735,7 @@
#endregion
#region[鐢熶骇杩涘害鎶ヨ〃瀵煎嚭]
- public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status, string wocode, string routecode, string routename, string partcode, string partname, string partspec,string opendate,string closedate)
+ 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 = "";
@@ -1681,13 +1751,13 @@
switch (status)
{
case "START":
- search += "and AA.status='START' ";
+ search += "and AA.status='鎵ц涓�' ";
break;
case "CLOSED":
- search += "and AA.status='CLOSED' ";
+ search += "and AA.status='宸插畬鎴�' ";
break;
default:
- search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ search += "and AA.status='鏈紑濮�' ";
break;
}
}
@@ -1695,6 +1765,11 @@
{
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)
{
@@ -1734,23 +1809,30 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.saleOrderCode as ERP婧愬崟鍙�,AA.m_po as ERP鐢熶骇璁㈠崟,AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 鐢熶骇宸ュ崟鍙�,
- (case when AA.status='START' then '鎵ц涓�' when AA.status='CLOSED' then '宸插畬鎴�' else '鏈紑濮�' end) 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,m.wo_code,W.wkshp_code,F.org_name as wkshp_name,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.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=m.wo_code for xml path('')) as concat_name
- from TK_Wrk_Step m
- inner join TK_Wrk_Man W on m.wo_code=W.wo_code
- left join TKimp_Ewo E on W.m_po=E.wo
- inner join TMateriel_Info P on W.materiel_code=p.partcode
- inner 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,m.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
- ) AA
+
+ 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鐨勫悕绉�
--
Gitblit v1.9.3