From bec134656a5a8057435d77f5f8154928fb2625f9 Mon Sep 17 00:00:00 2001
From: bklLiudl <673013083@qq.com>
Date: 星期三, 17 七月 2024 15:57:44 +0800
Subject: [PATCH] 更改sql写法
---
Wms/WMS.BLL/BllAsnServer/BllBoxInfoServer.cs | 108 +++++++++++++++++++++++++++++++++++++++--------------
1 files changed, 79 insertions(+), 29 deletions(-)
diff --git a/Wms/WMS.BLL/BllAsnServer/BllBoxInfoServer.cs b/Wms/WMS.BLL/BllAsnServer/BllBoxInfoServer.cs
index 5abbc7b..6d276f2 100644
--- a/Wms/WMS.BLL/BllAsnServer/BllBoxInfoServer.cs
+++ b/Wms/WMS.BLL/BllAsnServer/BllBoxInfoServer.cs
@@ -71,19 +71,34 @@
}
// 楠岃瘉鏄惁閲嶅 鏀�/琚嬬爜
- sqlString += $@"select count(id) from BllBoxInfo where BoxNo3 = '{model.BoxNo3}' and IsDel = 0 ";
- int rowCount = Db.Ado.GetInt(sqlString);
- if (rowCount > 0)
+ //sqlString += $@"select count(id) from BllBoxInfo where BoxNo3 = '{model.BoxNo3}' and IsDel = 0 ";
+ //int rowCount = Db.Ado.GetInt(sqlString);
+ var box1 = Db.Queryable<BllBoxInfo>().First(m => m.BoxNo3 == model.BoxNo3 && m.IsDel == "0");
+ if (box1 != null)
{
strMessage = "-1:閲嶅鏁版嵁!";
return strMessage;
}
// 鑾峰彇鐗╂枡淇℃伅
- sqlString = "select tb1.SkuNo,tb1.SkuName,tb1.Warranty, ";
- sqlString += "case tb2.level when 5 then tb2.L4Num when 4 then tb2.L3Num when 3 then tb2.L2Num else 0 end as FullQty ";
- sqlString += "from SysMaterials as tb1 left join SysPackag as tb2 on tb1.PackagNo = tb2.PackagNo ";
- sqlString += $"where SkuNo = '{model.SkuNo}' and tb1.IsDel = '0';";
- var materialModel = Db.Ado.SqlQuery<BoxInfoVm>(sqlString);
+ //sqlString = "select tb1.SkuNo,tb1.SkuName,tb1.Warranty, ";
+ //sqlString += "case tb2.level when 5 then tb2.L4Num when 4 then tb2.L3Num when 3 then tb2.L2Num else 0 end as FullQty ";
+ //sqlString += "from SysMaterials as tb1 left join SysPackag as tb2 on tb1.PackagNo = tb2.PackagNo ";
+ //sqlString += $"where SkuNo = '{model.SkuNo}' and tb1.IsDel = '0';";
+ //var materialModel = Db.Ado.SqlQuery<BoxInfoVm>(sqlString);
+
+ var materialModel = Db.Queryable<SysMaterials>()
+ .LeftJoin<SysPackag>((tb1, tb2) => tb1.PackagNo == tb2.PackagNo)
+ .Where(tb1 => tb1.SkuNo == model.SkuNo && tb1.IsDel == "0")
+ .Select((tb1, tb2) => new BoxInfoVm
+ {
+ SkuNo = tb1.SkuNo,
+ SkuName = tb1.SkuName,
+ Warranty = tb1.Warranty,
+ FullQty = SqlFunc.IF(tb2.Level == 5).Return(tb2.L4Num)
+ .ElseIF(tb2.Level == 4).Return(tb2.L3Num)
+ .ElseIF(tb2.Level == 3).Return(tb2.L2Num).End(0)
+ }).ToList();
+
if (materialModel.Count <= 0)
{
strMessage = "-1:璇ョ墿鏂欎俊鎭笉瀛樺湪!";
@@ -108,9 +123,16 @@
}
#region 鍖呰淇℃伅
- string str = $"select SUM(Qty) Qty from BllBoxInfo where IsDel = '0' and BoxNo = '{model.BoxNo}'";
- //鑾峰彇绠辩爜淇℃伅
- var box = Db.Ado.SqlQuerySingle<BoxInfoVm>(str);
+ //string str = $"select SUM(Qty) Qty from BllBoxInfo where IsDel = '0' and BoxNo = '{model.BoxNo}'";
+ ////鑾峰彇绠辩爜淇℃伅
+ //var box = Db.Ado.SqlQuerySingle<BoxInfoVm>(str);
+
+ var boxs = Db.Queryable<BllBoxInfo>().Where(m => m.IsDel == "0" && m.BoxNo == model.BoxNo).Select(m => new BoxInfoVm
+ {
+ Qty = SqlFunc.AggregateSum(m.Qty)
+ });
+ var box = boxs.First();
+
if (box.Qty == null)
{
box.Qty = 0;
@@ -162,13 +184,37 @@
#endregion
// 鎻掑叆淇℃伅
- sqlString = "Insert into BllBoxInfo (BoxNo,BoxNo2,BoxNo3,SkuNo,SkuName,LotNo,LotText,";
- sqlString += "Qty,FullQty,ProductionTime,ExpirationTime,InspectMark,InspectStatus,BitBoxMark,Origin,Status,CreateUser) values ( ";
- sqlString += $"'{model.BoxNo}','{model.BoxNo2}','{model.BoxNo3}','{model.SkuNo}', ";
- sqlString += $"'{materialModel[0].SkuName}','{model.LotNo}','{model.LotText}','{model.Qty}','{materialModel[0].FullQty}',";
- sqlString += $"'{model.ProductionTime}','{model.ExpirationTime}','{model.InspectMark}','{sku.IsInspect}','{model.BitBoxMark}','{model.Origin}','0','{model.CreateUser}');";
+ //sqlString = "Insert into BllBoxInfo (BoxNo,BoxNo2,BoxNo3,SkuNo,SkuName,LotNo,LotText,";
+ //sqlString += "Qty,FullQty,ProductionTime,ExpirationTime,InspectMark,InspectStatus,BitBoxMark,Origin,Status,CreateUser) values ( ";
+ //sqlString += $"'{model.BoxNo}','{model.BoxNo2}','{model.BoxNo3}','{model.SkuNo}', ";
+ //sqlString += $"'{materialModel[0].SkuName}','{model.LotNo}','{model.LotText}','{model.Qty}','{materialModel[0].FullQty}',";
+ //sqlString += $"'{model.ProductionTime}','{model.ExpirationTime}','{model.InspectMark}','{sku.IsInspect}','{model.BitBoxMark}" +
+ // $"','{model.Origin}','0','{model.CreateUser}');";
+ //rowCount = Db.Ado.ExecuteCommand(sqlString);
- rowCount = Db.Ado.ExecuteCommand(sqlString);
+
+ BllBoxInfo newboxModel = new BllBoxInfo()
+ {
+ BoxNo=model.BoxNo,
+ BoxNo2 = model.BoxNo2,
+ BoxNo3 = model.BoxNo3,
+ SkuNo = model.SkuNo,
+ SkuName = materialModel[0].SkuName,
+ LotNo = model.LotNo,
+ LotText = model.LotText,
+ Qty = (decimal)model.Qty,
+ FullQty = materialModel[0].FullQty,
+ ProductionTime = DateTime.Parse(model.ProductionTime),
+ ExpirationTime = model.ExpirationTime,
+ InspectMark = model.InspectMark,
+ InspectStatus = sku.IsInspect,
+ BitBoxMark = model.BitBoxMark,
+ Origin = model.Origin,
+ Status = "0",
+ CreateUser = (int)model.CreateUser
+ };
+
+ var rowCount = Db.Insertable(newboxModel).ExecuteCommand();
if (rowCount <= 0)
{
strMessage = "-1:娣诲姞澶辫触!";
@@ -203,15 +249,23 @@
//寮�鍚簨鍔�
Db.Ado.BeginTran();
// 鍒犻櫎鏄庣粏鍗�
- sqlString += $"UPDATE BllBoxInfo SET IsDel = '1',";
- sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
- sqlString += $"WHERE Id = '{model.Id}' and Status = '0';";
+ //sqlString += $"UPDATE BllBoxInfo SET IsDel = '1',";
+ //sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
+ //sqlString += $"WHERE Id = '{model.Id}' and Status = '0';";
- int rowCount = Db.Ado.ExecuteCommand(sqlString);
- if (rowCount <= 0)
+ //int rowCount = Db.Ado.ExecuteCommand(sqlString);
+
+ var boxModel = Db.Queryable<BllBoxInfo>().First(m => m.Id == model.Id && m.Status == "0");
+
+ if (boxInfo.Status != "0")
{
return "-1:鐘舵�佸凡鍙樻洿鏃犳硶鍒犻櫎锛�";
}
+
+ boxInfo.IsDel = "1";
+ boxInfo.UpdateTime = DateTime.Now;
+ boxInfo.UpdateUser = model.CreateUser;
+
var boxInfoList = Db.Queryable<BllBoxInfo>().Where(m => m.IsDel == "0" && m.BoxNo == boxInfo.BoxNo && m.Id != model.Id).ToList();
if (boxInfoList.Count != 0)
{
@@ -221,15 +275,11 @@
{
continue;
}
-
- var sql = $"update BllBoxInfo SET BitBoxMark = '1' Where id = {item.Id}";
- int rowCount2 = Db.Ado.ExecuteCommand(sql);
- if (rowCount2 <= 0)
- {
- throw new Exception("-1:鐘舵�佸凡鍙樻洿鏃犳硶鍒犻櫎锛�");
- }
+ item.BitBoxMark = "1";
}
}
+ Db.Updateable(boxInfo).ExecuteCommand();
+ Db.Updateable(boxInfoList).ExecuteCommand();
Db.Ado.CommitTran();
new OperationASNServer().AddLogOperationAsn("鍏ュ簱浣滀笟", "绠辨敮褰曞叆", boxInfo.BoxNo3, "鍒犻櫎", $"鍒犻櫎浜嗙鐮侊細{boxInfo.BoxNo}銆佽拷婧爜锛歿boxInfo.BoxNo3}鐨勭鏀墿鏂欎俊鎭�", Convert.ToInt32(model.CreateUser));
--
Gitblit v1.8.0