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/ProcurePlanServer.cs | 145 +++++++++++++++++++++---------------------------
1 files changed, 64 insertions(+), 81 deletions(-)
diff --git a/Wms/WMS.BLL/BllAsnServer/ProcurePlanServer.cs b/Wms/WMS.BLL/BllAsnServer/ProcurePlanServer.cs
index a446852..3e0f48b 100644
--- a/Wms/WMS.BLL/BllAsnServer/ProcurePlanServer.cs
+++ b/Wms/WMS.BLL/BllAsnServer/ProcurePlanServer.cs
@@ -5,6 +5,7 @@
using System;
using System.Collections.Generic;
using System.Text;
+using System.Threading.Tasks;
using WMS.BLL.LogServer;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
@@ -30,52 +31,34 @@
/// <param name="model"></param>
/// <param name="count"></param>
/// <returns></returns>
- public List<ProcurePlanNoticeDto> GetProcurePlanNoticeList(ProcurePlanNoticeVm model, out int count)
+ public async Task<List<ProcurePlanNoticeDto>> GetProcurePlanNoticeList(ProcurePlanNoticeVm model, RefAsync<int> count)
{
- string sqlString = string.Empty;
- string sqlCount = string.Empty;
- string sqlPub = string.Empty;
- try
- {
- sqlCount += "SELECT DISTINCT COUNT(tb1.ID) FROM BllProcurePlanNotice AS tb1 ";
- sqlString += "SELECT DISTINCT tb1.*,tb3.RealName as CreateUserName,tb4.RealName as UpdateUserName FROM BllProcurePlanNotice AS tb1 ";
- sqlPub += "LEFT JOIN BllProcurePlanNoticeDetail AS tb2 ON tb1.Id = tb2.ParentId ";
- sqlPub += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
- sqlPub += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";
- sqlPub += $"WHERE tb1.IsDel = '0' ";
- sqlPub += $"AND tb2.SkuNo LIKE '%{model.SkuNo}%' AND tb2.SkuName LIKE '%{model.SkuName}%' ";
- sqlPub += $"AND tb2.CustomerName LIKE '%{model.CustomerName}%' ";
- if (!string.IsNullOrEmpty(model.Status))
- {
- sqlPub += $"AND tb1.Status = '{model.Status}' ";
- }
- if (!string.IsNullOrEmpty(model.StartTime))
- {
- sqlPub += $"AND tb1.CreateTime >= '{model.StartTime}' ";
- }
- if (!string.IsNullOrEmpty(model.EndTime))
- {
- sqlPub += $"AND tb1.CreateTime <= '{model.EndTime}' ";
- }
- sqlCount += sqlPub;
- sqlPub += " order by tb1.Id desc ";
- if (model.Page == 0)
- {
- model.Page = 1;
- }
- sqlString += sqlPub + $" offset {((model.Page - 1) * model.Limit)} rows fetch next {model.Limit} rows only;";
-
- var com = new Common();
- count = com.GetRowCount(sqlCount);
-
- var modelList = Db.Ado.SqlQuery<ProcurePlanNoticeDto>(sqlString);
-
- return modelList;
- }
- catch (Exception ex)
- {
- throw ex;
- }
+ var modelList = await Db.Queryable<BllProcurePlanNotice>()
+ .LeftJoin<BllProcurePlanNoticeDetail>((tb1, tb2) => tb1.Id == tb2.ParentId)
+ .LeftJoin<SysUserInfor>((tb1, tb2, tb3) => tb1.CreateUser == tb3.Id)
+ .LeftJoin<SysUserInfor>((tb1, tb2, tb3, tb4) => tb1.UpdateUser == tb4.Id)
+ .WhereIF(!string.IsNullOrWhiteSpace(model.Status), tb1 => tb1.Status == model.Status)
+ .WhereIF(!string.IsNullOrWhiteSpace(model.StartTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(model.StartTime))
+ .WhereIF(!string.IsNullOrWhiteSpace(model.EndTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(model.EndTime).AddDays(1))
+ .WhereIF(!string.IsNullOrWhiteSpace(model.SkuName), (tb1,tb2) => tb2.SkuName.Contains(model.SkuName))
+ .WhereIF(!string.IsNullOrWhiteSpace(model.SkuNo), (tb1,tb2) => tb2.SkuNo.Contains(model.SkuNo))
+ .Where(tb1 => tb1.IsDel == "0")
+ .OrderBy(tb1=>tb1.Status)
+ .OrderByDescending(tb1 => tb1.CreateTime)
+ .Distinct()
+ .Select((tb1, tb2, tb3, tb4) => new ProcurePlanNoticeDto() {
+ Id = tb1.Id,
+ Status = tb1.Status,
+ OrderCode = tb1.OrderCode,
+ UserName = tb1.UserName,
+ CompleteTime = tb1.CompleteTime,
+ CreateTime = tb1.CreateTime,
+ CreateUserName = tb3.RealName,
+ UpdateTime = tb1.UpdateTime.ToString(),
+ UpdateUserName = tb4.RealName
+ }).ToPageListAsync(model.Page, model.Limit, count);
+
+ return modelList;
}
/// <summary>
/// 鑾峰彇閲囪喘鍗曟槑缁嗕俊鎭�
@@ -83,44 +66,43 @@
/// <param name="model"></param>
/// <param name="count"></param>
/// <returns></returns>
- public List<ProcurePlanNoticeDetailDto> GetProcurePlanNoticeDetailList(ProcurePlanNoticeDetailVm model, out int count)
+ public async Task<List<ProcurePlanNoticeDetailDto>> GetProcurePlanNoticeDetailList(ProcurePlanNoticeDetailVm model, RefAsync<int> count)
{
- string sqlString = string.Empty;
- string sqlCount = string.Empty;
- int rowCount = 1;
- try
- {
- if (model.Page == 0)
+ var modelList = await Db.Queryable<BllProcurePlanNoticeDetail, SysUserInfor, SysUserInfor, SysMaterials,
+ SysUnit, SysPackag>((tb1, tb2, tb3, tb4, tb5, tb6) => new JoinQueryInfos(
+ JoinType.Left, tb1.CreateUser == tb2.Id,
+ JoinType.Left, tb1.UpdateUser == tb3.Id,
+ JoinType.Left, tb1.SkuNo == tb4.SkuNo,
+ JoinType.Left, tb4.UnitNo == tb5.UnitNo,
+ JoinType.Left, tb1.PackagNo == tb6.PackagNo))
+ .Where(tb1 => tb1.ParentId == model.ParentId && tb1.IsDel == "0")
+ .OrderByDescending(tb1 => tb1.SkuNo)
+ .Distinct()
+ .Select((tb1, tb2, tb3, tb4, tb5, tb6) => new ProcurePlanNoticeDetailDto()
{
- model.Page = 1;
- }
- sqlCount += $"SELECT COUNT(ID) FROM BllProcurePlanNoticeDetail where ParentId = '{model.ParentId}' and IsDel = '0';";
- var com = new Common();
- count = com.GetRowCount(sqlCount);
- if (count != 0)
- {
- rowCount = count;
- }
+ Id = tb1.Id,
+ ParentId = tb1.ParentId.ToString(),
+ OrderDetailCode = tb1.OrderDetailCode,
+ SkuNo = tb1.SkuNo,
+ SkuName = tb1.SkuName,
+ Standard = tb1.Standard,
+ Qty = tb1.Qty,
+ CompleteQty = tb1.CompleteQty,
+ PackagNo = tb1.PackagNo,
+ PackagName = tb6.PackagName,
+ UnitName = tb5.UnitName,
+ Status = tb1.Status,
+ CustomerNo = tb1.CustomerNo,
+ CustomerName = tb1.CustomerName,
+ CompleteTime = tb1.CompleteTime,
+ CreateTime = tb1.CreateTime,
+ CreateUserName = tb2.RealName,
+ UpdateTime = tb1.UpdateTime.ToString(),
+ UpdateUserName = tb3.RealName
- sqlString += "SELECT DISTINCT tb1.*,tb3.RealName as CreateUserName, ";
- sqlString += "tb4.RealName as UpdateUserName,isnull(tb6.UnitName,tb5.UnitNo) as UnitName,tb7.PackagName ";
- sqlString += "FROM BllProcurePlanNoticeDetail AS tb1 ";
- sqlString += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
- sqlString += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";
- sqlString += "LEFT JOIN SysMaterials AS tb5 on tb1.SkuNo = tb5.SkuNo ";
- sqlString += "LEFT JOIN SysUnit AS tb6 on tb5.UnitNo = tb6.UnitNo ";
- sqlString += "LEFT JOIN SysPackag AS tb7 on tb1.PackagNo = tb7.PackagNo ";
- sqlString += $"WHERE tb1.ParentId = '{model.ParentId}' AND tb1.IsDel = '0' order by tb1.SkuNo desc ";
- sqlString += $"offset {((model.Page - 1) * model.Limit)} rows fetch next {rowCount} rows only;";
+ }).ToPageListAsync(model.Page, model.Limit, count);
- var modelList = Db.Ado.SqlQuery<ProcurePlanNoticeDetailDto>(sqlString);
-
- return modelList;
- }
- catch (Exception ex)
- {
- throw ex;
- }
+ return modelList;
}
/// <summary>
/// 閫氳繃閲囪喘鍗曠敓鎴愬叆搴撳崟鎹�
@@ -359,6 +341,7 @@
CustomerNo = CustomerModel.CustomerNo,
CustomerName = CustomerModel.CustomerName,
OrderCode = model.OrderCode,
+ UserName = model.Username,
CreateUser = 0
};
// 鎻掑叆鍏ュ簱鎬昏〃淇℃伅
@@ -372,7 +355,7 @@
Standard = skuModel.Standard,
LotNo = "",
LotText = "",
- Qty = 0,
+ Qty = (decimal)asnDetailModel.Qty,
FactQty=0,
CompleteQty=0,
PackagNo = skuModel.PackagNo,
@@ -383,7 +366,7 @@
OrderDetailCode = asnDetailModel.OrderDetailCode,
CreateUser = 0
};
- // 鎻掑叆鍏ュ簱鎬昏〃淇℃伅
+ // 鎻掑叆鍏ュ簱鏄庣粏琛ㄤ俊鎭�
Db.Insertable(arrDetailModel).ExecuteCommand();
#endregion
--
Gitblit v1.8.0