From 291558a5ddd495195047a23a27b44ce5b0bbca5a Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 15 十一月 2024 11:06:00 +0800
Subject: [PATCH] 1.T8入库记录(查询、删除) 2.T+入库记录(查询、删除) 3.生产入库记录查询增加部门查询条件 4.看板视图根据配置文件区分T8还是T+
---
VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs | 348 +++++++++++++++++++++++++++++++++++++++++++++++----------
1 files changed, 286 insertions(+), 62 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
index e269956..26843c3 100644
--- a/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs
@@ -19,6 +19,7 @@
public static string strProcName = ""; //瀹氫箟鍏ㄥ眬sql鍙橀噺
public static List<SqlParameter> listStr = new List<SqlParameter>(); //瀹氫箟鍏ㄥ眬鍙傛暟闆嗗悎
public static SqlParameter[] parameters; //瀹氫箟鍏ㄥ眬SqlParameter鍙傛暟鏁扮粍
+ public static string ErpPath = AppSetting.GetAppSetting("ErpPath"); //鑾峰彇ERP閰嶇疆
#region[鐢熶骇杩涘害鎶ヨ〃]
@@ -1629,8 +1630,8 @@
#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)
+ #region[T8鍏ュ簱璁板綍琛ㄥご]
+ public static ToMessage StorageRecordMainSearch(string hbillno, string username, string hbdateopendate, string hbdateclosedate, string userdateopendate, string userdateclosedate, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
@@ -1638,76 +1639,130 @@
{
if (hbillno != "" && hbillno != null)
{
- search += "and AA.hbillno like '%'+@hbillno+'%' ";
+ search += "and hbillno like '%'+@hbillno+'%' ";
dynamicParams.Add("@hbillno", hbillno);
}
- if (sono != "" && sono != null)
+ if (username != "" && username != null)
{
- search += "and AA.salecode like '%'+@sono+'%' ";
- dynamicParams.Add("@sono", sono);
+ search += "and create_user like '%'+@username+'%' ";
+ dynamicParams.Add("@username", username);
}
- if (mono != "" && mono != null)
+ if (hbdateopendate != "" && hbdateopendate != null)
{
- search += "and AA.mo_no like '%'+@mono+'%' ";
- dynamicParams.Add("@mono", mono);
+ search += "and hbdate between @hbdateopendate and @hbdateclosedate ";
+ dynamicParams.Add("@hbdateopendate", hbdateopendate + " 00:00:00");
+ dynamicParams.Add("@hbdateclosedate", hbdateclosedate + " 23:59:59");
}
- if (wocode != "" && wocode != null)
+ if (userdateopendate != "" && userdateopendate != 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 += "and create_date between @userdateopendate and @userdateclosedate ";
+ dynamicParams.Add("@userdateopendate", userdateopendate + " 00:00:00");
+ dynamicParams.Add("@userdateclosedate", userdateclosedate + " 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 sql = @"select (case when docu_typecode='WI' then '浜ф垚鍝佸叆搴�' end) as hbilltype,hbillno,hbdate,create_user,create_date
+ from TK_WMS_Inwh_Main 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[T8鍏ュ簱璁板綍琛ㄤ綋]
+ public static ToMessage StorageRecordSubSearch(string hbillno)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ dynamicParams.Add("@hbillno", hbillno);
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select * from(
+ select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,R.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
+ E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
+ S.qty,S.salecode,S.style
+ from TK_WMS_Inwh_Sub S
+ left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
+ left join TK_Wrk_Record R on S.inbarcode=R.inbarcode
+ left join TStep T on R.step_code=T.stepcode
+ left join TMateriel_Info M on S.partcode=M.partcode
+ left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
+ where S.style='B' and S.hbillno=@hbillno
+ union all
+ select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,O.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
+ E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
+ S.qty,S.salecode,S.style
+ from TK_WMS_Inwh_Sub S
+ left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
+ left join TK_Wrk_OutRecord O on S.inbarcode=O.inbarcode
+ left join TStep T on O.step_code=T.stepcode
+ left join TMateriel_Info M on S.partcode=M.partcode
+ left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
+ where S.style='S' and S.hbillno=@hbillno
+ ) as AA" + search;
+ DataTable data = DapperHelper.selectdata(sql, dynamicParams);
+ mes.code = "200";
+ mes.message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[T8鍏ュ簱璁板綍鍒犻櫎]
+ public static ToMessage StorageRecordDelete(DataTable dt, User us)
+ {
+ var sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //瀛樺偍杩囩▼鍚�
+ sql = @"h_p_IFCLD_T8DeleteInProductOrder";
+ dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: dt);
+ // 娣诲姞杈撳嚭鍙傛暟
+ dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
+ dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
+ bool a = DapperHelper.IsProcedure(sql, dynamicParams);
+ // 鑾峰彇杈撳嚭鍙傛暟鐨勫��
+ var statusCode = dynamicParams.Get<int>("@StatusCode");
+ var message = dynamicParams.Get<string>("@Message");
+ if (a)
+ {
+ mes.code = statusCode.ToString();
+ mes.count = 0;
+ mes.message = message;
+ mes.data = null;
+ }
+ else
+ {
+ mes.code = statusCode.ToString();
+ mes.count = 0;
+ mes.message = message;
+ mes.data = null;
+ }
}
catch (Exception e)
{
@@ -1729,7 +1784,7 @@
try
{
// 浣跨敤LINQ鐨刲ambda琛ㄨ揪寮忔牴鎹甴billno鍒嗙粍骞剁粺璁℃瘡涓粍鐨勬潯鏁�
- var groupedRecords = json.GroupBy(r => r.hbillno).Select(g => new{hbillno = g.Key,count = g.Count()}).ToList();
+ 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++)
{
//鏌ヨ鍏ュ簱鍗曞瓙琛ㄧ殑鏁版嵁鏉℃暟
@@ -1765,15 +1820,27 @@
inbarcode = json[i].inbarcode.ToString()
}
});
- if (json[i].style == "B")
+ if (json[i].style == "B")
{
- //鑷埗鎶ュ伐璁板綍琛ㄥ叆搴撴暟閲�
- sql = @"update TK_Wrk_Record set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
+ //鍒犻櫎鎶ュ伐璁板綍涓昏〃
+ //sql = @"delete from TK_Wrk_Record where id=@repoid and inbarcode=@inbarcode";
+ //list.Add(new
+ //{
+ // str = sql,
+ // parm = new
+ // {
+ // repoid = json[i].repoid.ToString(),
+ // inbarcode = json[i].inbarcode.ToString()
+ // }
+ //});
+ //淇敼鑷埗鎶ュ伐鍏ュ簱鏁伴噺
+ sql = @"update TK_Wrk_Record set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode and id=@repoid";
list.Add(new
{
str = sql,
parm = new
{
+ repoid = json[i].repoid.ToString(),
qty = json[i].qty.ToString(),
inbarcode = json[i].inbarcode.ToString()
}
@@ -1781,7 +1848,18 @@
}
if (json[i].style == "S")
{
- //澶栧崗鏀舵枡璁板綍琛ㄥ叆搴撴暟閲�
+ //鍒犻櫎澶栧崗鏀舵枡璁板綍涓昏〃
+ //sql = @"delete from TK_Wrk_OutRecord where id=@repoid and inbarcode=@inbarcode";
+ //list.Add(new
+ //{
+ // str = sql,
+ // parm = new
+ // {
+ // repoid = json[i].repoid.ToString(),
+ // inbarcode = json[i].inbarcode.ToString()
+ // }
+ //});
+ //淇敼澶栧崗鏀舵枡璁板綍琛ㄥ叆搴撴暟閲�
sql = @"update TK_Wrk_OutRecord set inhouseqty=inhouseqty-@qty where inbarcode=@inbarcode";
list.Add(new
{
@@ -1811,7 +1889,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗叆搴撳崟:"+ string.Join(",", json.Select(r => $"{r.hbillno},{r.inbarcode}")), us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗叆搴撳崟:" + string.Join(",", json.Select(r => $"{r.hbillno},{r.inbarcode}")), us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鍏ュ簱鍗曞垹闄ゆ垚鍔�!";
@@ -1931,5 +2009,151 @@
return mes;
}
#endregion
+
+
+ #region[T+鍏ュ簱璁板綍琛ㄥご]
+ public static ToMessage InStorageRecordMainSearch(string hbillno, string username, string hbdateopendate, string hbdateclosedate, string userdateopendate, string userdateclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (hbillno != "" && hbillno != null)
+ {
+ search += "and hbillno like '%'+@hbillno+'%' ";
+ dynamicParams.Add("@hbillno", hbillno);
+ }
+ if (username != "" && username != null)
+ {
+ search += "and create_user like '%'+@username+'%' ";
+ dynamicParams.Add("@username", username);
+ }
+ if (hbdateopendate != "" && hbdateopendate != null)
+ {
+ search += "and hbdate between @hbdateopendate and @hbdateclosedate ";
+ dynamicParams.Add("@hbdateopendate", hbdateopendate + " 00:00:00");
+ dynamicParams.Add("@hbdateclosedate", hbdateclosedate + " 23:59:59");
+ }
+ if (userdateopendate != "" && userdateopendate != null)
+ {
+ search += "and create_date between @userdateopendate and @userdateclosedate ";
+ dynamicParams.Add("@userdateopendate", userdateopendate + " 00:00:00");
+ dynamicParams.Add("@userdateclosedate", userdateclosedate + " 23:59:59");
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select (case when docu_typecode='69' then '浜ф垚鍝佸叆搴�' end) as hbilltype,hbillno,hbdate,create_user,create_date
+ from TK_WMS_Inwh_Main 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[T+鍏ュ簱璁板綍琛ㄤ綋]
+ public static ToMessage InStorageRecordSubSearch(string hbillno)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ dynamicParams.Add("@hbillno", hbillno);
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select * from(
+ select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,R.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
+ E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
+ S.qty,S.salecode,S.style
+ from TK_WMS_Inwh_Sub S
+ left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
+ left join TK_Wrk_Record R on S.inbarcode=R.inbarcode
+ left join TStep T on R.step_code=T.stepcode
+ left join TMateriel_Info M on S.partcode=M.partcode
+ left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
+ where S.style='B' and S.hbillno=@hbillno
+ union all
+ select S.hbillno,S.rownumber,S.inbarcode,S.mo_id,S.mo_no,E.sbid,S.wocode,O.step_code,T.stepname,E.materiel_id,S.partcode,M.partname,M.partspec,
+ E.unitid,S.unitcode,E.unitname,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,K.name as stockname,
+ S.qty,S.salecode,S.style
+ from TK_WMS_Inwh_Sub S
+ left join TKimp_Ewo E on S.mo_no=E.wo and S.mo_id=E.woid and S.partcode=E.materiel_code
+ left join TK_Wrk_OutRecord O on S.inbarcode=O.inbarcode
+ left join TStep T on O.step_code=T.stepcode
+ left join TMateriel_Info M on S.partcode=M.partcode
+ left join TSecStck K on COALESCE(M.idwarehouse, E.stck_code)=K.code
+ where S.style='S' and S.hbillno=@hbillno
+ ) as AA" + search;
+ DataTable data = DapperHelper.selectdata(sql, dynamicParams);
+ mes.code = "200";
+ mes.message = "鏌ヨ鎴愬姛!";
+ mes.count = total;
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[T+鍏ュ簱璁板綍鍒犻櫎]
+ public static ToMessage DeleteInStorageRecord(DataTable dt, User us)
+ {
+ var sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //瀛樺偍杩囩▼鍚�
+ sql = @"h_p_IFCLD_TCloudDeleteInProductOrder";
+ dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: dt);
+ // 娣诲姞杈撳嚭鍙傛暟
+ dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
+ dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
+ bool a = DapperHelper.IsProcedure(sql, dynamicParams);
+ // 鑾峰彇杈撳嚭鍙傛暟鐨勫��
+ var statusCode = dynamicParams.Get<int>("@StatusCode");
+ var message = dynamicParams.Get<string>("@Message");
+ if (a)
+ {
+ mes.code = statusCode.ToString();
+ mes.count = 0;
+ mes.message = message;
+ mes.data = null;
+ }
+ else
+ {
+ mes.code = statusCode.ToString();
+ mes.count = 0;
+ mes.message = message;
+ mes.data = null;
+ }
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
}
}
--
Gitblit v1.9.3