using System; using SqlSugar; using Infrastructure.Attribute; using ZR.Model; using ZR.Repository; using System.Linq; using Infrastructure; using System.Collections.Generic; using ZR.Model.System; using Org.BouncyCastle.Math.EC.Rfc7748; using ZR.Service.Business.IBusinessService.Balance; using ZR.Model.Dto.Balance; using ZR.Model.Models.BaseSet; using ZR.Model.Models.Balance; using Mapster; using ZR.Model.Models.InStore; using ZR.Model.Models.Statistics; namespace ZR.Service.Business.Balance { /// /// 库存管理/库存明细表Service业务层处理 /// /// @author admin /// @date 2023-04-27 /// [AppService(ServiceType = typeof(IStkBalanceDtlService), ServiceLifetime = LifeTime.Transient)] public class StkBalanceDtlService : BaseService, IStkBalanceDtlService { #region 业务逻辑代码 /// /// 查询库存管理/库存明细表列表 /// /// /// public PagedInfo GetList(StkBalanceDtlQueryDto parm) { //开始拼装查询条件 var predicate = Expressionable.Create(); if (parm.BeginTime != null && parm.EndTime != null) { parm.BeginTime = parm.BeginTime.Value.ToLocalTime(); parm.EndTime = parm.EndTime.Value.ToLocalTime(); predicate.And(d => d.CreateTime >= parm.BeginTime && d.CreateTime <= parm.EndTime); } predicate.AndIF(parm.RegionId > 0, d => d.RegionId == parm.RegionId); //predicate.AndIF(parm.BinId > 0, d => d.BinId == parm.BinId); //predicate.AndIF(!string.IsNullOrEmpty(parm.BinCode), d => d.BinCode.Contains(parm.BinCode)); predicate.AndIF(!string.IsNullOrEmpty(parm.BomNo), d => d.BomNo.Contains(parm.BomNo)); predicate.AndIF(!string.IsNullOrEmpty(parm.DeliverNo), d => d.DeliverNo.Contains(parm.DeliverNo)); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialCode), d => d.MaterialCode.Contains(parm.MaterialCode)); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialName), d => d.MaterialName.Contains(parm.MaterialName)); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialSpec), d => d.MaterialSpec.Contains(parm.MaterialSpec)); predicate.AndIF(parm.Status != null, d => d.Status == parm.Status); predicate.AndIF(parm.KeyFlag != null, d => d.KeyFlag == parm.KeyFlag); predicate.AndIF(!string.IsNullOrEmpty(parm.BnSnCode), d => d.MaterialSpec.Contains(parm.BnSnCode)); //先判断搜索条件中有没有bincode if (!string.IsNullOrEmpty(parm.BinCode)) { //把basbin表检索到list List bins = Context.Queryable().ToList(); //查找包含搜索条件的basbin数据 var newBasBins = bins.FindAll(b => b.BinCode.Contains(parm.BinCode)); //提取符合条件的basbin,并返回其id long[] BasBinIds = newBasBins.Select(x => x.BinId).ToArray(); //添加转化完成的检索条件,即符合basbinId的数据 predicate.And(d => BasBinIds.Contains(d.BinId)); } //搜索条件查询语法参考Sqlsugar var response = Queryable() .LeftJoin((d, region) => d.RegionId == region.RegionId) .LeftJoin((d, region, bin) => d.BinId == bin.BinId) .Where(predicate.ToExpression()) .Select((d, region, bin) => new StkBalanceDtl { RegionId = d.RegionId.SelectAll(), BinId = d.BinId.SelectAll(), RegionName = region.RegionName, RegionCode = region.RegionCode, BinCode = bin.BinCode, }) .ToPage(parm); return response; } /// /// 查询库存管理/库存统计列表 /// /// /// public PagedInfo StatList(StkBalanceDtlQueryDto parm) { //开始拼装查询条件 var predicate = Expressionable.Create(); predicate.AndIF(parm.RegionId > 0, d => d.RegionId == parm.RegionId); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialCode), d => d.MaterialCode.Contains(parm.MaterialCode)); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialName), d => d.MaterialName.Contains(parm.MaterialName)); predicate.AndIF(!string.IsNullOrEmpty(parm.MaterialSpec), d => d.MaterialSpec.Contains(parm.MaterialSpec)); predicate.AndIF(parm.KeyFlag != null, d => d.KeyFlag == parm.KeyFlag); predicate.AndIF(!string.IsNullOrEmpty(parm.BnSnCode), d => d.MaterialSpec.Contains(parm.BnSnCode)); //搜索条件查询语法参考Sqlsugar var response = Queryable() .GroupBy(d => new { d.MaterialCode }) .LeftJoin((d, region) => d.RegionId == region.RegionId) .Where(predicate.ToExpression()) .Select((d, region) => new StkBalanceDtl { RegionId = d.RegionId.SelectAll(), RegionName = region.RegionName, SumQty = SqlFunc.AggregateSum(d.BalanceQty), }) .ToPage(parm); return response; } public List GetKeyStatistics() { //开始拼装查询条件 var predicate = Expressionable.Create(); //predicate.And(d => d.CreateTime >= BeginTime && d.CreateTime <= EndTime); //搜索条件查询语法参考Sqlsugar var response = Queryable() .GroupBy(d => new { d.KeyFlag }) .Where(predicate.ToExpression()) .Select(d => new GroupByKey { KeyFlag = d.KeyFlag, SumQty = SqlFunc.AggregateSum(d.BalanceQty), }) .ToList(); return response; } public List GetCategoryStatistics() { //开始拼装查询条件 var predicate = Expressionable.Create(); //predicate.And(d => d.CreateTime >= BeginTime && d.CreateTime <= EndTime); //搜索条件查询语法参考Sqlsugar var response = Queryable() .GroupBy(d => new { d.Category }) .Where(predicate.ToExpression()) .Select(d => new GroupByCategory { Category = d.Category, SumQty = SqlFunc.AggregateSum(d.BalanceQty), }) .ToList(); return response; } /// /// 添加库存管理/库存明细表 /// /// /// public int AddStkBalanceDtl(StkBalanceDtl model) { return Add(model, true); } /// /// 修改库存管理/库存明细表 /// /// /// public int UpdateStkBalanceDtl(StkBalanceDtl model) { //var response = Update(w => w.BalanceId == model.BalanceId, it => new StkBalanceDtl() //{ // RegionId = model.RegionId, // BinId = model.BinId, // DeliverNo = model.DeliverNo, // BomNo = model.BomNo, // MaterialCode = model.MaterialCode, // MaterialName = model.MaterialName, // MaterialSpec = model.MaterialSpec, // Category = model.Category, // KeyFlag = model.KeyFlag, // BnSnCode = model.BnSnCode, // BalanceQty = model.BalanceQty, // Status = model.Status, // UpdateBy = model.UpdateBy, // UpdateTime = model.UpdateTime, // Remark = model.Remark, //}); //return response; return Update(model, true); } /// /// 清空库存管理/库存明细表 /// /// public void TruncateStkBalanceDtl() { Truncate(); } #endregion #region 2023 0524 孙亚龙 新增 public List GetBalanceDtlList(GetOutOperateBalanceQueryDto parm) { /* * 库存查找规则: * 1、先按照 bom号、物料号、Bn\Sn码 的条件查找库存 * 2、如果存在指定bom号的库存数据,则直接返回 * 3、如果不存在指定bom号的库存数据,则需要按照物料号、Bn\Sn码 的条件查找库存,然后把结果返回 * */ #region 按照 bom号、物料号、Bn\Sn码 的条件查找库存 //开始拼装查询条件 var predicate = Expressionable.Create(); predicate.AndIF(parm != null && !string.IsNullOrEmpty(parm.MaterialCode), x => x.MaterialCode == parm.MaterialCode || x.MaterialSpec == parm.MaterialCode); predicate.AndIF(parm != null && !string.IsNullOrEmpty(parm.BomNo), x => x.BomNo == parm.BomNo); predicate.AndIF(parm != null && !string.IsNullOrEmpty(parm.BnSnCode), x => x.BnSnCode == parm.BnSnCode); predicate.AndIF(parm.IsForAssemble, x => x.Status == 88); predicate.AndIF(!parm.IsForAssemble, x => x.Status == 55); //predicate.And(x => x.BalanceQty >= parm.Qty); //搜索条件查询语法参考Sqlsugar var response = Queryable() .Where(predicate.ToExpression()) .OrderBy(x => x.BalanceId) .OrderBy(x => x.BalanceQty) .ToList(); if (response.Count > 0) { return response.Adapt>(); } #endregion 按照 bom号、物料号、Bn\Sn码 的条件查找库存 #region 按照物料号、Bn\Sn码 的条件查找库存 var predicate1 = Expressionable.Create(); predicate1.AndIF(parm != null && !string.IsNullOrEmpty(parm.MaterialCode), x => x.MaterialCode == parm.MaterialCode || x.MaterialSpec == parm.MaterialCode); predicate1.AndIF(parm != null && !string.IsNullOrEmpty(parm.BnSnCode), x => x.BnSnCode == parm.BnSnCode); predicate1.AndIF(parm.IsForAssemble, x => x.Status == 88); predicate1.AndIF(!parm.IsForAssemble, x => x.Status == 55); response = Queryable() .Where(predicate1.ToExpression()) .OrderBy(x => x.BalanceId) .OrderBy(x => x.BalanceQty) .ToList(); return response.Adapt>(); #endregion 按照物料号、Bn\Sn码 的条件查找库存 } #endregion } }