From ce12a541680abb5604b7c90a3130c819848cb973 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 06 六月 2023 19:09:35 +0800
Subject: [PATCH] 1.生产进度报表开发 2.T+对接回调地址信息接口开发
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 200 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 200 insertions(+), 0 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index dfbf278..599a168 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -1007,5 +1007,205 @@
return mes;
}
#endregion
+
+
+ #region[鐢熶骇杩涘害鎶ヨ〃]
+ public static ToMessage ProductionScheduleReportSearch(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)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (status != "" && status != null)
+ {
+ switch (status)
+ {
+ case "START":
+ search += "and AA.status='START' ";
+ break;
+ case "CLOSED":
+ search += "and AA.status='CLOSED' ";
+ break;
+ default:
+ search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ break;
+ }
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ 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 AA.saleOrderCode,AA.m_po,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.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
+ group by E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
+ ) 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 status, string wocode, string routecode, string routename, string partcode, string partname, string partspec, string opendate, string closedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (status != "" && status != null)
+ {
+ switch (status)
+ {
+ case "START":
+ search += "and AA.status='START' ";
+ break;
+ case "CLOSED":
+ search += "and AA.status='CLOSED' ";
+ break;
+ default:
+ search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
+ break;
+ }
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ 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 AA.saleOrderCode as ERP婧愬崟鍙�,AA.m_po as ERP鐢熶骇璁㈠崟,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.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
+ group by E.saleOrderCode,W.m_po,m.wo_code,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
+ ) 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