From 8aaabf94d4472e5aa85de2617b60ec40885bd1fe Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 11 十月 2024 11:14:15 +0800
Subject: [PATCH] 新增MES工单列表导出功能
---
VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs | 109 +++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 106 insertions(+), 3 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs b/VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs
index 35851a4..d655dde 100644
--- a/VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/WorkOrderDAL.cs
@@ -1101,6 +1101,109 @@
}
#endregion
+ #region[MES宸ュ崟瀵煎嚭]
+ public static ToMessage MesOrderExcelSearch(string mesorderstus, string wkshopcode, string mesordercode, string sourceorder, string saleordercode, string ordertype, string partcode, string partname, string partspec, string creatuser, string createdate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (mesorderstus != "" && mesorderstus != null)
+ {
+ search += "and A.status=@mesorderstus ";
+ dynamicParams.Add("@mesorderstus", mesorderstus);
+ }
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wkshp_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (mesordercode != "" && mesordercode != null)
+ {
+ search += "and A.wo_code like '%'+@mesordercode+'%' ";
+ dynamicParams.Add("@mesordercode", mesordercode);
+ }
+ if (sourceorder != "" && sourceorder != null)
+ {
+ search += "and A.m_po like '%'+@sourceorder+'%' ";
+ dynamicParams.Add("@sourceorder", sourceorder);
+ }
+ if (saleordercode != "" && saleordercode != null)
+ {
+ search += "and W.saleOrderCode like '%'+@saleordercode+'%' ";
+ dynamicParams.Add("@saleordercode", saleordercode);
+ }
+ if (ordertype != "" && ordertype != null)
+ {
+ search += "and A.wotype like '%'+@ordertype+'%' ";
+ dynamicParams.Add("@ordertype", ordertype);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and A.materiel_code like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and B.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and B.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (createdate != "" && createdate != null)
+ {
+ search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
+ dynamicParams.Add("@createdate", createdate);
+ }
+ if (creatuser != "" && creatuser != null)
+ {
+ search += "and U.username like '%'+@creatuser+'%' ";
+ dynamicParams.Add("@creatuser", creatuser);
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.injectnumber as '娉ㄥ鏈哄彿',A.customername as '瀹㈡埛鍚嶇О',A.materiel_code as '浜у搧缂栫爜',B.partname as '浜у搧鍚嶇О',B.partspec as '浜у搧瑙勬牸',A.colour as '棰滆壊',A.plan_qty as '宸ュ崟鏁�',W.saleOrderCode as '閿�鍞鍗�',A.clerkuser as '閿�鍞笟鍔″憳',
+ (case when A.status='NEW' then '鏂板伐鍗�'
+ when A.status='ALLO' then '宸叉帓鍙�'
+ when A.status='START' then '宸插紑宸�'
+ when A.status='CLOSED' then '宸插畬宸�'
+ end) as '宸ュ崟鐘舵��',
+ (case when A.wotype='PO' then '鏍囧噯宸ュ崟' else '鎶ュ簾琛ュ崟' end) as '鍗曟嵁绫诲瀷',A.wo_code as '宸ュ崟缂栧彿',A.m_po as '婧愬崟鍗曞彿',C.torg_name as '鐢熶骇杞﹂棿',A.saleOrderDeliveryDate as '棰勮浜や粯鏃ユ湡',U.username as '鍒涘缓浜哄憳',A.lm_date as '鍒涘缓鏃堕棿'
+ from TK_Wrk_Man A
+ left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
+ left join TMateriel_Info B on A.materiel_code=B.partcode
+ left join TOrganization C on A.wkshp_code=C.torg_code
+ left join TSecStck D on A.stck_code=D.code
+ left join TUser U on A.lm_user=U.usercode
+ left join TOrganization L on C.parent_id=L.id
+ where A.is_delete<>'1' " + 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[MES宸ュ崟娲惧彂鎻愪氦]
public static ToMessage MesOrderDistribution(string[] wocodelist, User us)
@@ -1388,7 +1491,7 @@
}
//鏍规嵁鏉′欢鏌ヨ宸ュ崟宸ュ簭浠诲姟(鑷埗宸ュ簭)
sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
- S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date
+ S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
from TK_Wrk_Step A
left join TK_Wrk_Man B on A.wo_code=B.wo_code
left join TMateriel_Info M on B.materiel_code=M.partcode
@@ -1492,7 +1595,7 @@
sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,
(select isnull(sum(fqty),0) as fqty from TK_Wrk_OutRecord where wo_code=A.wo_code and step_code=A.step_code and style='F') as fqty,
- A.laborbad_qty,A.materielbad_qty,A.plan_startdate,A.plan_enddate,B.lm_date
+ A.laborbad_qty,A.materielbad_qty,A.plan_startdate,A.plan_enddate,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
from TK_Wrk_Step A
left join TK_Wrk_Man B on A.wo_code=B.wo_code
left join TMateriel_Info M on B.materiel_code=M.partcode
@@ -1593,7 +1696,7 @@
}
//鏍规嵁鏉′欢鏌ヨ宸ュ崟宸ュ簭浠诲姟(鑷埗宸ュ簭)
sql = @"select A.id,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isend,
- S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date
+ S.stepcode,S.stepname,S.descr,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,B.injectnumber,B.customercode,B.customername,B.colour
from TK_Wrk_Step A
left join TK_Wrk_Man B on A.wo_code=B.wo_code
left join TMateriel_Info M on B.materiel_code=M.partcode
--
Gitblit v1.9.3