首页库存和仓库

admin

11 小时以前 daf065f5894352e5b739c66fb6fa12d8d028d5c3

 Wms/WMS.BLL/DataServer/StockServer.cs

@@ -1,8 +1,10 @@
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Net.WebSockets;
using System.Runtime.Intrinsics.X86;
using System.Security.Claims;
using System.Text;
@@ -14,6 +16,7 @@
using Model.ModelVm.SysVm;
using MySqlConnector;
using SqlSugar;
using Talk.Extensions;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.BllQualityEntity;
@@ -28,9 +31,152 @@
    public class StockServer : DbHelper<DataStock>, IStockServer
    {
        private static readonly SqlSugarScope Db = DataContext.Db;

        public StockServer() : base(Db)
        {

        }


        /// <summary>
        /// 获取库存物料类型分布
        /// </summary>
        /// <returns></returns>
        public IEnumerable<IdNameNumRateDto> GetGoodsTypeFb()
        {
            string stockNumSql = $@"select a.Type,sum(b.num) as num 
   from SysMaterials a
   inner join (select SkuNo,sum(Qty) as num from DataStockDetail where IsDel='0' group by SkuNo) b on b.SkuNo=a.SkuNo
   where a.IsDel='0'
   group by a.Type";
            DataTable stockNumDt = Db.Ado.GetDataTable(stockNumSql);
            var stockNumIE = stockNumDt.Rows.Cast<DataRow>().Select(e => new
            {
                typeName = e["Type"] + "",
                num = e["num"] + ""
            });

            //物料类型
            string goodsTypeSql = $@"select DictNo,DictName,0.00 as num
   from SysDictionary
   where IsDel='0' and ParentNo='MaterialType'
   order by Id";
            DataTable goodsTypeDt = Db.Ado.GetDataTable(goodsTypeSql);

            IEnumerable<IdNameNumRateDto> result = goodsTypeDt.Rows.Cast<DataRow>().Select(e => new IdNameNumRateDto
            {
                TypeId = e["DictNo"] + "",
                TypeName = e["DictName"] + "",
                TypeNum = Convert.ToDouble((stockNumIE.FirstOrDefault(f => f.typeName == e["DictNo"] + "") == null) ? "0.00" : stockNumIE.First(f => f.typeName == e["DictNo"] + "").num),
            });
            return result;
        }


        /// <summary>
        /// 获取库存质量状态分布
        /// </summary>
        /// <returns></returns>
        public IEnumerable<IdNameNumRateDto> GetQualityStatusFb()
        {
            string stockNumSql = $@"select InspectStatus,sum(Qty) as num
   from DataStockDetail
   where IsDel='0' and InspectStatus is not null
   group by InspectStatus";
            DataTable stockNumDt = Db.Ado.GetDataTable(stockNumSql);
            object totalObj = stockNumDt.Compute("sum(num)", "");

            var stockNumIE = stockNumDt.Rows.Cast<DataRow>().Select(e => new
            {
                typeName = e["InspectStatus"] + "",
                num = e["num"] + ""
            });

            //物料类型
            string inspectStatusSql = $@"select DictNo,DictName,0.00 as num,'0%' as rate
   from SysDictionary
   where IsDel='0' and ParentNo='InspectStatus'
   order by Id";
            DataTable goodsTypeDt = Db.Ado.GetDataTable(inspectStatusSql);

            foreach (DataRow dr in goodsTypeDt.Rows)
            {
                var stockNum = stockNumIE.FirstOrDefault(e => e.typeName == dr["DictNo"] + "");

                if (stockNum != null)
                {
                    dr["num"] = stockNum.num;
                    dr["rate"] = (stockNum.num.ToDouble() / Convert.ToDouble(totalObj) * 100.00).ToString("0.00") + "%";
                }
            }

            IEnumerable<IdNameNumRateDto> result = goodsTypeDt.Rows.Cast<DataRow>().Select(e => new IdNameNumRateDto
            {
                TypeId = e["DictNo"] + "",
                TypeName = e["DictName"] + "",
                TypeNum = Convert.ToDouble(e["num"]),
                TypeRate = e["rate"] + ""
            });
            return result;
        }


        /// <summary>
        /// 获取仓库状况总
        /// </summary>
        /// <returns></returns>
        public WarehouseStateDto GetWareHouseState()
        {
            //库区总数
            string houseNumSql = $@"select count(Id) as num
   from SysWareHouse
   where IsDel='0'";
            int houseNum = Db.Ado.GetInt(houseNumSql);

            //库位总数
            string stockNumSql = $@"select count(Id) as num
   from SysStorageLocat
   where IsDel='0'";
            int stockNum = Db.Ado.GetInt(stockNumSql);

            //已使用库位
            string stockUseNumSql = $@"select count(Id) as num
   from SysStorageLocat
   where IsDel='0' and Status='1'";
            int stockUseNum = Db.Ado.GetInt(stockUseNumSql);

            WarehouseStateDto result = new WarehouseStateDto()
            {
                WarehouseNum = houseNum + "",
                StockAllNum = stockNum + "",
                StockUseNum = stockUseNum + "",
                StockUseRate = ((double)stockUseNum / stockNum * 100.00).ToString("0.00") + "%"
            };
            return result;
        }


        /// <summary>
        /// 获取仓库详情
        /// </summary>
        /// <returns></returns>
        public IEnumerable<WarehouseUseDto> GetWareHouseStateInfo()
        {
            string stockUseSql = $@"select a.Id,a.WareHouseNo,a.WareHouseName,b.num as stockNum,c.num as useNum
   from SysWareHouse a
   left join (select WareHouseNo,count(Id) as num from SysStorageLocat where IsDel='0' group by WareHouseNo) b on b.WareHouseNo=a.WareHouseNo
   left join (select WareHouseNo,count(Id) as num from SysStorageLocat where IsDel='0' and Status='1' group by WareHouseNo) c on c.WareHouseNo=a.WareHouseNo
   where a.IsDel='0'
   order by a.WareHouseNo";
            DataTable stockUseDt = Db.Ado.GetDataTable(stockUseSql);

            IEnumerable<WarehouseUseDto> result = stockUseDt.Rows.Cast<DataRow>().Select(e => new WarehouseUseDto
            {
                WarehouseName = e["WareHouseName"] + "",
                StockAllNum = e["stockNum"] + "",
                StockUseNum = e["useNum"] + ""
            });
            return result;
        }


@@ -47,9 +193,11 @@
        /// <param name="lotNo">批次</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <param name="wareHouseNo">所属仓库</param>
        /// <param name="areaNo">所属区域</param>
        /// <returns></returns>
        public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo, 
            string ownerName, string lotNo, string status, string inspectStatus)
        public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo,
            string ownerName, string lotNo, string status, string inspectStatus, string wareHouseNo, string areaNo)
        {
            Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
            .AndIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
@@ -59,6 +207,8 @@
            .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)
            .AndIF(!string.IsNullOrEmpty(wareHouseNo), a => a.WareHouseNo == wareHouseNo)
            .AndIF(!string.IsNullOrEmpty(areaNo), a => a.AreaNo == areaNo)
            .And(a => a.IsDel == "0")
            .ToExpression();//注意 这一句 不能少

@@ -66,53 +216,53 @@
            var data = Db.Queryable<DataStockDetail>().Where(item);

            List<MateDataStockDto> data2;
            switch (selectType) 
            switch (selectType)
            {
                // 按物料统计
                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();
                    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();
                    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})
                    data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.InspectStatus })
                        .Select(a => new MateDataStockDto()
                        {
                            SkuNo = a.SkuNo, //物料编码
@@ -129,7 +279,7 @@
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
                        }).ToListAsync();
                    break; 
                    break;

                // 按货主统计
                case "3":
@@ -150,11 +300,11 @@
                            FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
                            InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
                        }).ToListAsync();
                     break;
                    break;

                // 默认统计（物料 批次 质检 库存状态 货主)
                default:
                    data2 = await data.GroupBy(a => new {  a.SkuNo, a.SkuName, a.Standard ,a.LotNo, a.LotText, a.OwnerNo, a.OwnerName , a.InspectStatus })
                    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, //物料编码
@@ -344,9 +494,9 @@
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo=a.LotNo,
                    SkuNo=a.SkuNo,
                    SkuName=a.SkuName,
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = SqlFunc.AggregateSumNoNull(a.Qty),
                    RFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
                    RCompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
@@ -431,22 +581,22 @@
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo,string startTime, string endTime)
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo, string startTime, string endTime)
        {
            // 获取入库单明细信息
            var asnList = Db.Queryable<BllPalletBind,BllArrivalNoticeDetail>
                ((tb1,tb2)=>new JoinQueryInfos(
            var asnList = Db.Queryable<BllPalletBind, BllArrivalNoticeDetail>
                ((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.ASNDetailNo == tb2.Id
                ))
                .Where((tb1,tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CompleteTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CompleteTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1,tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
                //.GroupBy((tb1,tb2) => new { tb2.LotNo, tb2.SkuNo, tb2.SkuName })
                .Select((tb1,tb2) => new TotalRecordDto()
                .Select((tb1, tb2) => new TotalRecordDto()
                {
                    ASNNo = tb2.ASNNo,
                    LotNo = tb1.LotNo,
@@ -459,7 +609,7 @@
                    CompleteTime = tb2.CreateTime,
                    LotText = tb1.LotText,
                    SupplierLot = tb2.SupplierLot,
                    

                    SONo = "",
                    CTaskNo = "",
                    CCreateTime = null,
@@ -492,13 +642,13 @@
                     RQty = 0,
                     RFactQty = 0,
                     RCompleteQty = 0,
                     CompleteTime = tb2.CreateTime,
                     CompleteTime = null,
                     LotText = tb1.LotText,
                     SupplierLot = tb2.SupplierLot,

                     SONo = tb2.SONo,
                     CTaskNo = tb1.TaskNo,
                     CCreateTime = tb1.UpdateTime,
                     CCreateTime = tb1.CreateTime,
                     CQty = tb1.Qty,
                     CFactQty = tb1.Qty,
                     CAllotQty = tb1.Qty,
@@ -525,7 +675,7 @@
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CompleteTime = a.CreateTime,
                    CompleteTime = null,
                    LotText = "",
                    SupplierLot = "",

@@ -866,75 +1016,37 @@
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="InspectStatus">质检状态</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName)
        public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName, string InspectStatus)
        {
            string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,(mate.Weight * stock.Qty) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = @isdel";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and stock.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and stock.SkuName like @skuname";
            }
            //排序
            str += " order by stock.SkuNo";
            List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料编码
                skuname = "%" + skuName + "%" //物料名称
            });

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

            foreach (var item in StockList)
            {
                //判断库存总量是否拥有物料
                if (StockListDto.Count > 0)
            // 获取入库单明细信息 GetDataStockListDaoChu
            var asnList = Db.Queryable<DataStockDetail>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo)).Distinct()
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName)).Distinct()
                .WhereIF(!string.IsNullOrEmpty(InspectStatus), a => a.InspectStatus == InspectStatus)
                .Select(a => new MateDataStockDto()
                {
                    int i = 0;
                    //foreach循环库存总量
                    foreach (var dto in StockListDto)
                    {
                        //判断物料是否相同
                        if (dto.SkuNo == item.SkuNo)
                        {
                            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;
                        }
                        i += 1;
                        //判断已有相同物料
                        if (i == StockListDto.Count)
                        {
                            StockListDto.Add(item);
                            break;
                        }
                    }
                }
                else
                {
                    StockListDto.Add(item);
                    continue;
                }
            }
            foreach (var item in StockListDto)
            {
                //物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
                if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
                {
                    item.SkuNo = $"'{item.SkuNo}";
                }
            }
                    SkuNo = a.SkuNo,//物料编码
                    SkuName = a.SkuName,//物料名称
                    Standard = a.Standard,//物料规格
                    LotNo = a.LotNo,//批次号
                    OwnerNo = a.OwnerNo,//货主编号
                    OwnerName = a.OwnerName,//货主名称
                    Status = ((a.Status == "0") ? "待分配" : ((a.Status == "1") ? "部分分配" : ((a.Status == "2") ? "盘点锁定" : ((a.Status == "3") ? "移库锁定" : "异常锁定")))),//库存状态
                    InspectStatus = ((a.InspectStatus == "0") ? "待质检" : (a.InspectStatus == "1" ? "检验合格" : "不合格")),//质检状态
                    Qty = SqlFunc.AggregateSum(Convert.ToInt32((a.Qty))),//库存数量
                    LockQty = SqlFunc.AggregateSum(Convert.ToInt32(a.LockQty)),//锁定数量
                    FrozenQty = SqlFunc.AggregateSum(Convert.ToInt32(a.FrozenQty)),//冻结数量
                    InspectQty = SqlFunc.AggregateSum(Convert.ToInt32(a.InspectQty))//可抽检数量
                })
                .Distinct()
                .GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.LotNo, a.Status, a.InspectStatus, a.OwnerNo, a.OwnerName, a.Qty, a.LockQty, a.FrozenQty, a.InspectQty })
                .ToList();

            return StockListDto;
            Console.WriteLine("结果：" + asnList);
            return asnList;
        }

        /// <summary>
@@ -944,127 +1056,114 @@
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="locatNo">储位地址</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <param name="ownerNo">货物编号</param>
        /// <param name="ownerName">货物名称</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, 
            string palletNo, string status, string inspectStatus, string startTime, string endTime)
        public List<StockDetailDto> GetDataStockList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string status, string inspectStatus, string ownerNo, string ownerName, string startTime, string endTime)
        {
            string str = "select detail.*,house.WareHouseName as WareHouseName,roadway.RoadwayName as RoadwayName  from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo Where detail.IsDel = @isdel";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and detail.SkuNo = @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and detail.SkuName = @skuname";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and detail.LotNo = @lotno";
            }
            //判断储位地址是否为空
            if (!string.IsNullOrEmpty(locatNo))
            {
                str += " and detail.LocatNo = @locatno";
            }
            //判断托盘条码是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and detail.PalletNo = @palletno";
            }
            //判断库存状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and detail.Status = @status";
            }
            //判断质检状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and detail.InspectStatus = @inspectstatus";
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                str += $" and detail.CompleteTime >= '{startTime}'";
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
                str += $" and detail.CompleteTime < '{endTime}'";
            }
            //排序
            str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
            List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = skuNo, //物料编码
                skuname = skuName, //物料名称
                lotno = lotNo, //批次
                locatno = locatNo, //储位地址
                palletno = palletNo, //托盘条码
                status = status, //库存状态
                inspectstatus = inspectStatus //质检状态
            });
            foreach (var item in stockDetailsList)
            {
                //储位地址，加上单引号是防止导出到excel自动把前面的0给去掉
                if (!string.IsNullOrEmpty(item.LocatNo) && item.LocatNo.Substring(0, 1) == "0")
            // 获取明细信息
            var asnList = Db.Queryable<DataStockDetail, DataStock>
                ((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.Id == tb2.Id
                ))
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
                .WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
                .WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
                //.GroupBy((tb1,tb2) => new { tb2.LotNo, tb2.SkuNo, tb2.SkuName })
                .Select((tb1, tb2) => new StockDetailDto()
                {
                    item.LocatNo = $"'{item.LocatNo}";
                }
                //物料编码
                if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
                {
                    item.SkuNo = $"'{item.SkuNo}";
                }
                //库存状态
                switch (item.Status)
                {
                    case "0":
                        item.Status = "待分配";
                        break;
                    case "1":
                        item.Status = "部分分配";
                        break;
                    case "2":
                        item.Status = "已分配";
                        break;
                    case "3":
                        item.Status = "盘点锁定";
                        break;
                    case "4":
                        item.Status = "移库锁定";
                        break;
                    default:
                        break;
                }
                //质检状态
                switch (item.InspectStatus)
                {
                    case "0":
                        item.InspectStatus = "待检验";
                        break;
                    case "1":
                        item.InspectStatus = "检验合格";
                        break;
                    case "2":
                        item.InspectStatus = "不合格";
                        break;
                    case "4":
                        item.InspectStatus = "放置期";
                        break;
                    default:
                        break;
                }
                    Status = tb1.Status,
                    InspectStatus = tb1.InspectStatus,
                    LotNo = tb1.LotNo,
                    SkuNo = tb2.SkuNo,
                    SkuName = tb2.SkuName,
                    OwnerNo = tb2.OwnerNo,
                    OwnerName = tb2.OwnerName,
                    CompleteTime = tb2.CreateTime,
                });

            }
            return stockDetailsList;
            // 获取出库单明细信息
            var soList = Db.Queryable<DataStockDetail, DataStock>
                (((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.Id == tb2.Id
                )))
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
                .WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
                .WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
                 //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                 .Select((tb1, tb2) => new StockDetailDto()
                 {
                     Status = tb1.Status,
                     InspectStatus = tb1.InspectStatus,
                     LotNo = tb1.LotNo,
                     SkuNo = tb2.SkuNo,
                     SkuName = tb2.SkuName,
                     OwnerNo = tb2.OwnerNo,
                     OwnerName = tb2.OwnerName,
                     CompleteTime = tb2.CreateTime,
                 });

            // 获取取样拣货信息
            var samplingList = Db.Queryable<DataStockDetail, DataStock>
                (((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.Id == tb2.Id
                )))
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(skuNo), (tb1, tb2) => tb2.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), (tb1, tb2) => tb2.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(ownerNo), (tb1, tb2) => tb2.OwnerNo.Contains(ownerNo))
                .WhereIF(!string.IsNullOrEmpty(ownerName), (tb1, tb2) => tb2.OwnerName.Contains(ownerName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), (tb1, tb2) => tb2.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrEmpty(status), (tb1, tb2) => tb1.Status.Contains(status))
                .WhereIF(!string.IsNullOrEmpty(inspectStatus), (tb1, tb2) => tb1.InspectStatus.Contains(inspectStatus))
                 //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                 .Select((tb1, tb2) => new StockDetailDto()
                 {
                     Status = tb1.Status,
                     InspectStatus = tb1.InspectStatus,
                     LotNo = tb1.LotNo,
                     SkuNo = tb2.SkuNo,
                     SkuName = tb2.SkuName,
                     OwnerNo = tb2.OwnerNo,
                     OwnerName = tb2.OwnerName,
                     CompleteTime = tb2.CreateTime,
                 });

            // 合并集合
            var dataList = Db.UnionAll(asnList, soList, samplingList)
                .Select(a => new StockDetailDto()
                {
                    Status = a.Status,
                    InspectStatus = a.InspectStatus,
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    OwnerNo = a.OwnerNo,
                    OwnerName = a.OwnerName,
                    CompleteTime = a.CompleteTime,
                }).ToList();

            return dataList;
        }
        #endregion
    }