From 65a9633ebcf5cf4c0871d1c857a743ec7694c157 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 30 十月 2024 17:29:43 +0800
Subject: [PATCH] 1.生产入库功能接口 2.生产入库单报表接口
---
VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs | 340 ++++++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 313 insertions(+), 27 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
index ba515a2..cf232b8 100644
--- a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
@@ -5,6 +5,8 @@
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
+using VueWebCoreApi.Models;
+using VueWebCoreApi.Models.InventoryModel;
using VueWebCoreApi.Tools;
namespace VueWebCoreApi.DLL.DAL
@@ -20,7 +22,7 @@
#region[鐢熶骇杩涘害鎶ヨ〃]
- public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status,string socode, string wocode, 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 socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -118,7 +120,7 @@
#endregion
#region[鐢熶骇杩涘害鎶ヨ〃瀵煎嚭]
- public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status,string socode, string wocode,string partcode, string partname, string partspec, string opendate, string closedate)
+ public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status, string socode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -222,7 +224,7 @@
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍鏌ヨ]
- public static ToMessage GroupSalaryReportSearch(string compute,string wkshopcode,string socode, 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 compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -233,7 +235,7 @@
search += "and P.isend=@isend ";
dynamicParams.Add("@isend", "Y");
}
- if (wkshopcode != "" && wkshopcode != null)
+ if (wkshopcode != "" && wkshopcode != null)
{
search += "and K.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
@@ -353,7 +355,7 @@
#endregion
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage GroupSalaryReportExcelSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
+ public static ToMessage GroupSalaryReportExcelSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -455,7 +457,7 @@
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃]
- public static ToMessage PeopleSalaryReportSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec,string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -578,7 +580,7 @@
#endregion
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage PeopleSalaryReportSearch(string compute,string wkshopcode,string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
+ public static ToMessage PeopleSalaryReportSearch(string compute, string wkshopcode, string socode, string wocode, string partcode, string partname, string partspec, string stepcode, string stepname, string reportname, string reportopendate, string reportclosedate)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -1382,11 +1384,6 @@
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; //鎬绘潯鏁�
@@ -1468,11 +1465,6 @@
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; //鎬绘潯鏁�
@@ -1542,11 +1534,6 @@
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; //鎬绘潯鏁�
@@ -1610,11 +1597,6 @@
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; //鎬绘潯鏁�
@@ -1645,5 +1627,309 @@
return mes;
}
#endregion
+
+
+ #region[鍏ュ簱璁板綍]
+ public static ToMessage StorageRecordSearch(string hbillno, string sono, string mono, string wocode, string partcode, string partname, string stockcode, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (hbillno != "" && hbillno != null)
+ {
+ search += "and AA.hbillno like '%'+@hbillno+'%' ";
+ dynamicParams.Add("@hbillno", hbillno);
+ }
+ if (sono != "" && sono != null)
+ {
+ search += "and AA.salecode like '%'+@sono+'%' ";
+ dynamicParams.Add("@sono", sono);
+ }
+ if (mono != "" && mono != null)
+ {
+ search += "and AA.mo_no like '%'+@mono+'%' ";
+ dynamicParams.Add("@mono", mono);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wocode like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (stockcode != "" && stockcode != null)
+ {
+ search += "and AA.stockcode=@stockcode ";
+ dynamicParams.Add("@stockcode", stockcode);
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and AA.hbdate between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select * from(
+ select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
+ B.qty,B.inbarcode,R.step_code,E.stepname,R.style,A.create_user,A.create_date
+ from TK_WMS_Inwh_Main A
+ inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
+ inner join TK_Wrk_Record R on B.inbarcode=R.inbarcode
+ left join TK_Wrk_Man M on B.wocode=M.wo_code
+ left join TMateriel_Info P on B.partcode=P.partcode
+ left join TSecStck S on B.stockcode=S.code
+ left join TStep E on R.step_code=E.stepcode
+ left join TOrganization T on M.wkshp_code=T.torg_code
+ union all
+ select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
+ B.qty,B.inbarcode,O.step_code,E.stepname,O.style,A.create_user,A.create_date
+ from TK_WMS_Inwh_Main A
+ inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
+ inner join TK_Wrk_OutRecord O on B.inbarcode=O.inbarcode
+ left join TK_Wrk_Man M on B.wocode=M.wo_code
+ left join TMateriel_Info P on B.partcode=P.partcode
+ left join TSecStck S on B.stockcode=S.code
+ left join TStep E on O.step_code=E.stepcode
+ left join TOrganization T on M.wkshp_code=T.torg_code
+ ) as AA where 1=1 " + 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 DeleteStorageRecord(List<StorageRecord> json, User us)
+ {
+ var sql = "";
+ List<object> list = new List<object>();
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ // 浣跨敤LINQ鐨刲ambda琛ㄨ揪寮忔牴鎹甴billno鍒嗙粍骞剁粺璁℃瘡涓粍鐨勬潯鏁�
+ var groupedRecords = json.GroupBy(r => r.hbillno).Select(g => new{hbillno = g.Key,count = g.Count()}).ToList();
+ for (int i = 0; i < groupedRecords.Count; i++)
+ {
+ //鏌ヨ鍏ュ簱鍗曞瓙琛ㄧ殑鏁版嵁鏉℃暟
+ sql = @"select * from TK_WMS_Inwh_Sub where hbillno=@hbillno";
+ dynamicParams.Add("@hbillno", groupedRecords[i].hbillno);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ //瀛愯〃鏈夊搴斿叆搴撳崟鍙锋暟鎹笖鏁版嵁鏉℃暟绛変簬鎻愪氦鐨勫叆搴撴潯鏁�
+ if (data.Rows.Count > 0 && data.Rows.Count == groupedRecords[i].count)
+ {
+ //鍒犻櫎鍏ュ簱鍗曚富琛�
+ sql = @"delete TK_WMS_Inwh_Main where hbillno=@hbillno";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ hbillno = groupedRecords[i].hbillno.ToString()
+ }
+ });
+ }
+ }
+
+ for (int i = 0; i < json.Count; i++)
+ {
+ //鍒犻櫎鍏ュ簱鍗曞瓙琛�
+ sql = @"delete TK_WMS_Inwh_Sub where hbillno=@hbillno and inbarcode=@inbarcode";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ hbillno = json[i].hbillno.ToString(),
+ inbarcode = json[i].inbarcode.ToString()
+ }
+ });
+ if (json[i].style == "B")
+ {
+ //鑷埗鎶ュ伐璁板綍琛ㄥ叆搴撴暟閲�
+ sql = @"update TK_Wrk_Record set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ qty = json[i].qty.ToString(),
+ inbarcode = json[i].inbarcode.ToString()
+ }
+ });
+ }
+ if (json[i].style == "S")
+ {
+ //澶栧崗鏀舵枡璁板綍琛ㄥ叆搴撴暟閲�
+ sql = @"update TK_Wrk_OutRecord set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ qty = json[i].qty.ToString(),
+ inbarcode = json[i].inbarcode.ToString()
+ }
+ });
+ }
+ //娓呴櫎宸ュ簭浠诲姟琛ㄧ疮璁″叆搴撴暟閲�
+ sql = @"update TK_Wrk_Step set inhouseqty=inhouseqty-@qty where wo_code=@wocode and step_code=@stepcode and isend='Y'";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ qty = json[i].qty.ToString(),
+ wocode = json[i].wocode.ToString(),
+ stepcode = json[i].step_code.ToString()
+ }
+ });
+ }
+ bool aa = DapperHelper.DoTransaction(list);
+ LogHelper.WriteLogData(aa.ToString());
+ if (aa)
+ {
+ //鍐欏叆鎿嶄綔璁板綍琛�
+ LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗叆搴撳崟:"+ string.Join(",", json.Select(r => $"{r.hbillno},{r.inbarcode}")), us.usertype);
+ mes.code = "200";
+ mes.count = 0;
+ mes.message = "鍏ュ簱鍗曞垹闄ゆ垚鍔�!";
+ mes.data = null;
+ }
+ 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 StorageRecordExcelSearch(string hbillno, string sono, string mono, string wocode, string partcode, string partname, string stockcode, string respondopendate, string respondclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (hbillno != "" && hbillno != null)
+ {
+ search += "and AA.hbillno like '%'+@hbillno+'%' ";
+ dynamicParams.Add("@hbillno", hbillno);
+ }
+ if (sono != "" && sono != null)
+ {
+ search += "and AA.salecode like '%'+@sono+'%' ";
+ dynamicParams.Add("@sono", sono);
+ }
+ if (mono != "" && mono != null)
+ {
+ search += "and AA.mo_no like '%'+@mono+'%' ";
+ dynamicParams.Add("@mono", mono);
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and AA.wocode like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and AA.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and AA.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (stockcode != "" && stockcode != null)
+ {
+ search += "and AA.stockcode=@stockcode ";
+ dynamicParams.Add("@stockcode", stockcode);
+ }
+ if (respondopendate != "" && respondopendate != null)
+ {
+ search += "and A.hbdate between @respondopendate and @respondclosedate ";
+ dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
+ dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.hbillno as 鍏ュ簱鍗曞彿,AA.hbdate as 鍗曟嵁鏃ユ湡,AA.salecode as 閿�鍞崟鍙�,AA.mo_no as 璁㈠崟鍙�,AA.wocode as 宸ュ崟鍙�,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,
+ AA.partspec as 浜у搧瑙勬牸,AA.wkshp_code as 杞﹂棿缂栫爜,AA.torg_name as 杞﹂棿鍚嶇О,AA.stockcode as 浠撳簱缂栫爜,AA.stockname as 浠撳簱鍚嶇О, AA.qty as 鍏ュ簱鏁伴噺,
+ AA.step_code as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,AA.create_user as 鍒涘缓浜哄憳,AA.create_date as 鍒涘缓鏃堕棿
+ from(
+ select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name,B.stockcode,S.name as stockname,
+ B.qty,B.inbarcode,R.step_code,E.stepname,R.style,A.create_user,A.create_date
+ from TK_WMS_Inwh_Main A
+ inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
+ inner join TK_Wrk_Record R on B.inbarcode=R.inbarcode
+ left join TK_Wrk_Man M on B.wocode=M.wo_code
+ left join TMateriel_Info P on B.partcode=P.partcode
+ left join TSecStck S on B.stockcode=S.code
+ left join TStep E on R.step_code=E.stepcode
+ left join TOrganization T on M.wkshp_code=T.torg_code
+ union all
+ select A.hbillno,A.hbdate,B.salecode,B.mo_no,B.wocode,B.partcode,P.partname,P.partspec,M.wkshp_code,T.torg_name as wkshp_name,B.stockcode,S.name as stockname,
+ B.qty,B.inbarcode,O.step_code,E.stepname,O.style,A.create_user,A.create_date
+ from TK_WMS_Inwh_Main A
+ inner join TK_WMS_Inwh_Sub B on A.hbillno=B.hbillno
+ inner join TK_Wrk_OutRecord O on B.inbarcode=O.inbarcode
+ left join TK_Wrk_Man M on B.wocode=M.wo_code
+ left join TMateriel_Info P on B.partcode=P.partcode
+ left join TSecStck S on B.stockcode=S.code
+ left join TStep E on O.step_code=E.stepcode
+ left join TOrganization T on M.wkshp_code=T.torg_code
+ ) as AA where 1=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
}
}
--
Gitblit v1.9.3