修改自动数据库链接，修改周期报表默认时间，修改年度报表显示不全问题。

yyk

2024-08-29 2cf0aaaca070d1423ab8412431690befef39fa89

 Wms/WMS.BLL/DataServer/StockServer.cs

@@ -2,14 +2,19 @@
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.Intrinsics.X86;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using Model.ModelDto;
using Model.ModelDto.DataDto;
using Model.ModelDto.SysDto;
using Model.ModelVm.DataVm;
using Model.ModelVm.SysVm;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.Entity.SysEntity;
@@ -29,94 +34,144 @@
        #region 库存明细

        /// <summary>
        /// 查询库存总量
        /// 查询库存统计
        /// </summary>
        /// <param name="selectType">查询类型 0：物料信息 1：批次信息 2：质检信息 3：货主信息</param>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="ownerNo">货主编码</param>
        /// <param name="ownerName">货主名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetDataStockList(string skuNo, string skuName, string ownerNo, string ownerName, string lotNo)
        public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo, 
            string ownerName, string lotNo, string status, string inspectStatus)
        {
            string str = "select stock.SkuNo,stock.SkuName,stock.LotNo,stock.LotText,stock.Standard,stock.Qty," +
                "stock.LockQty,stock.FrozenQty,stock.OwnerNo,stock.OwnerName,(mate.Weight * stock.Qty) WeightSum " +
                "from DataStock stock " +
                "left join SysMaterials mate on stock.SkuNo = mate.SkuNo " +
                "Where stock.IsDel = @isdel and mate.IsDel = 0 ";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and stock.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and stock.SkuName like @skuname";
            }
            //判断货主编码是否为空
            if (!string.IsNullOrEmpty(ownerNo))
            {
                str += " and stock.OwnerNo like @ownerNo";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(ownerName))
            {
                str += " and stock.OwnerName like @ownerName";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and stock.LotNo like @lotNo";
            }
            //排序
            str += " order by stock.SkuNo";
            List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料编码
                skuname = "%" + skuName + "%", //物料名称
                ownerNo = "%" + ownerNo + "%", //货主编码
                ownerName = "%" + ownerName + "%", //货主名称
                lotNo = "%" + lotNo + "%", //批次号
            });
            Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
            .AndIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
            .AndIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
            .AndIF(!string.IsNullOrEmpty(ownerNo), a => a.OwnerNo.Contains(ownerNo))
            .AndIF(!string.IsNullOrEmpty(ownerName), a => a.OwnerName.Contains(ownerName))
            .AndIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
            .AndIF(!string.IsNullOrEmpty(status), a => a.Status == status)
            .AndIF(!string.IsNullOrEmpty(inspectStatus), a => a.InspectStatus == inspectStatus)
            .And(a => a.IsDel == "0")
            .ToExpression();//注意 这一句 不能少

            //库存总量
            List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();

            foreach (var item in StockList)
            var data = Db.Queryable<DataStockDetail>().Where(item);

            List<MateDataStockDto> data2;
            switch (selectType) 
            {
                StockListDto.Add(item);
                ////判断库存总量是否拥有物料
                //if (StockListDto.Count > 0)
                //{
                //    int i = 0;
                //    //foreach循环库存总量
                //    foreach (var dto in StockListDto)
                //    {
                //        //判断物料和批次是否相同
                //        if (dto.SkuNo == item.SkuNo && dto.LotNo == item.LotNo)
                //        {
                //            dto.Qty =  (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString();
                //            dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString();
                //            dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString();
                //            dto.ResidueQty= (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString();
                //            break;
                //        }
                //        i += 1;
                //        //判断已有相同物料
                //        if (i == StockListDto.Count)
                //        {
                //            StockListDto.Add(item);
                //            break;
                //        }
                //    }
                //}
                //else
                //{
                //    StockListDto.Add(item);
                //    continue;
                //}
                // 按物料统计
                case "0":
                     data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard })
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
                            SkuName = a.SkuName, //物料名称
                            Standard = a.Standard, //规格
                            //LotNo = a.LotNo, //批次
                            //LotText = a.LotText, //批次描述
                            //OwnerNo = a.OwnerNo, //货主编码
                            //OwnerName = a.OwnerName, //货主名称
                            Status = status, //库存状态
                            InspectStatus = inspectStatus, //质检状态
                            Qty =  SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
                            LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)    
                        }).ToListAsync();
                    break;

                // 按批次统计
                case "1":
                     data2 = await data.GroupBy(a => new { a.LotNo, a.LotText, a.SkuNo, a.SkuName, a.Standard })
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
                            SkuName = a.SkuName, //物料名称
                            Standard = a.Standard, //规格
                            LotNo = a.LotNo, //批次
                            LotText = a.LotText, //批次描述
                            //OwnerNo = a.OwnerNo, //货主编码
                            //OwnerName = a.OwnerName, //货主名称
                            Status = status, //库存状态
                            InspectStatus = inspectStatus, //质检状态
                            Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
                            LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)
                        }).ToListAsync();
                    break;

                // 按质检状态统计
                case "2":
                    data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard,a.InspectStatus})
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
                            SkuName = a.SkuName, //物料名称
                            Standard = a.Standard, //规格
                            //LotNo = a.LotNo, //批次
                            //LotText = a.LotText, //批次描述
                            //OwnerNo = a.OwnerNo, //货主编码
                            //OwnerName = a.OwnerName, //货主名称
                            Status = status, //库存状态
                            InspectStatus = a.InspectStatus, //质检状态
                            Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
                            LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
                        }).ToListAsync();
                    break; 

                // 按货主统计
                case "3":
                    data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.OwnerNo, a.OwnerName })
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
                            SkuName = a.SkuName, //物料名称
                            Standard = a.Standard, //规格
                            //LotNo = a.LotNo, //批次
                            //LotText = a.LotText, //批次描述
                            OwnerNo = a.OwnerNo, //货主编码
                            OwnerName = a.OwnerName, //货主名称
                            Status = status, //库存状态
                            InspectStatus = inspectStatus, //质检状态
                            Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
                            LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
                        }).ToListAsync();
                     break;

                // 默认统计（物料 批次 质检 库存状态 货主)
                default:
                    data2 = await data.GroupBy(a => new {  a.SkuNo, a.SkuName, a.Standard ,a.LotNo, a.LotText, a.OwnerNo, a.OwnerName , a.InspectStatus })
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
                            SkuName = a.SkuName, //物料名称
                            Standard = a.Standard, //规格
                            LotNo = a.LotNo, //批次
                            LotText = a.LotText, //批次描述
                            OwnerNo = a.OwnerNo, //货主编码
                            OwnerName = a.OwnerName, //货主名称
                            Status = status, //库存状态
                            InspectStatus = a.InspectStatus, //质检状态
                            Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
                            LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
                        }).ToListAsync();
                    break;
            }

            return StockListDto;
            return data2;
        }

        /// <summary>
@@ -512,7 +567,8 @@
        {
            try
            {
                string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a inner join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
                string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a " +
                    "left join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";

                //判断开始时间是否为空
                if (!string.IsNullOrWhiteSpace(StartTime))
@@ -837,9 +893,9 @@
                        //判断物料是否相同
                        if (dto.SkuNo == item.SkuNo)
                        {
                            dto.Qty = (Convert.ToInt32(dto.Qty) + Convert.ToInt32(item.Qty)).ToString();
                            dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString();
                            dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString();
                            dto.Qty = Convert.ToInt32(dto.Qty) + Convert.ToInt32(item.Qty);
                            dto.FrozenQty = Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty);
                            dto.LockQty = Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty);
                            dto.ResidueQty = (Convert.ToInt32(dto.ResidueQty) + Convert.ToInt32(item.ResidueQty)).ToString();
                            break;
                        }