using System;
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 MySqlConnector;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.BllQualityEntity;
using WMS.Entity.BllSoEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.Entity.SysEntity;
using WMS.IBLL.IDataServer;

namespace WMS.BLL.DataServer
{
    public class StockServer : DbHelper<DataStock>, IStockServer
    {
        private static readonly SqlSugarScope Db = DataContext.Db;
        public StockServer() : base(Db)
        {

        }


        #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 async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo, 
            string ownerName, string lotNo, string status, string inspectStatus)
        {
            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();//注意 这一句 不能少


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

            List<MateDataStockDto> data2;
            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();
                    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 data2;
        }

        /// <summary>
        /// 获取库存明细
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="locatNo">储位地址</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <returns></returns>
        public List<StockDetailDto> GetInventoryList1(string skuNo, string skuName, string lotNo, string locatNo, string palletNo,
            string status, string inspectStatus, string ownerNo, string ownerName, string startTime, string endTime)
        {
            string str = "select detail.*,house.WareHouseNo + '-' + house.WareHouseName as WareHouseName," +
                "roadway.RoadwayNo + '-' + roadway.RoadwayName as RoadwayName,area.AreaNo + '-' + area.AreaName as AreaName  " +
                "from DataStockDetail detail " +
                "left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo " +
                "left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo " +
                "left join SysStorageArea area on detail.AreaNo = area.AreaNo " +
                "Where detail.IsDel = @isdel";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and detail.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and detail.SkuName like @skuname";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and detail.LotNo like @lotno";
            }
            //判断储位地址是否为空
            if (!string.IsNullOrEmpty(locatNo))
            {
                str += " and detail.LocatNo like @locatno";
            }
            //判断托盘条码是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and detail.PalletNo like @palletno";
            }
            //判断库存状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and detail.Status = @status";
            }
            //判断质检状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and detail.InspectStatus = @inspectstatus";
            }
            //判断货主编码是否为空
            if (!string.IsNullOrEmpty(ownerNo))
            {
                str += " and detail.OwnerNo like @ownerNo";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(ownerName))
            {
                str += " and detail.OwnerName like @ownerName";
            }
            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, //质检状态
                ownerNo = "%" + ownerNo + "%", //货主编码
                ownerName = "%" + ownerName + "%" //货主名称
            });
            return stockDetailsList;
        }

        #endregion

        #region 低库存预警

        /// <summary>
        /// 低库存预警
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="type">物料类型</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetInventoryWarning(string skuNo, string skuName, string lotNo, string type)
        {
            string str = "select mate.Id,mate.SkuNo,mate.SkuName,mate.Standard,mate.Type,stock.Qty,stock.LockQty,stock.FrozenQty,stock.Qty - stock.LockQty - Stock.FrozenQty ResidueQty,stock.LotNo from SysMaterials mate right join DataStock stock on mate.SkuNo = stock.SkuNo Where stock.IsDel = @isdel and stock.Qty - stock.LockQty - Stock.FrozenQty <= mate.LowInventory";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and stock.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and stock.SkuName like @skuname";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and stock.LotNo like @lotno";
            }
            //判断物料类型是否为空
            if (!string.IsNullOrEmpty(type))
            {
                str += " and mate.Type = @type";
            }
            //排序
            str += " order by stock.SkuNo";
            List<MateDataStockDto> stockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次
                type, //物料类型
            });
            return stockList;
        }

        #endregion

        #region 出入库报表

        /// <summary>
        /// 获取出入库总量
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo, string startTime, string endTime)
        {
            // 获取入库单明细信息
            var asnList = Db.Queryable<BllArrivalNoticeDetail>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    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),
                    CQty = 0,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = 0
                });

            // 获取出库单明细信息
            var soList = Db.Queryable<BllExportNoticeDetail>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CQty = SqlFunc.AggregateSumNoNull(a.Qty),
                    CFactQty = (decimal)SqlFunc.AggregateSum(a.FactQty),
                    CAllotQty = (decimal)SqlFunc.AggregateSum(a.AllotQty),
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                });
            // 获取取样拣货信息
            var samplingList = Db.Queryable<BllSamplingDetails>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty),
                });

            // 合并集合
            var dataList = Db.UnionAll(asnList, soList, samplingList)
                .GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    RQty = SqlFunc.AggregateSumNoNull(a.RQty),
                    RFactQty = (decimal)SqlFunc.AggregateSum(a.RFactQty),
                    RCompleteQty = (decimal)SqlFunc.AggregateSum(a.RCompleteQty),
                    CQty = SqlFunc.AggregateSumNoNull(a.CQty),
                    CFactQty = (decimal)SqlFunc.AggregateSum(a.CFactQty),
                    CAllotQty = (decimal)SqlFunc.AggregateSum(a.CAllotQty),
                    CompleteQty = (decimal)SqlFunc.AggregateSum(a.CompleteQty)
                }).ToList();

            return dataList;
        }

        /// <summary>
        /// 获取出入库记录
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘</param>
        /// <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)
        {
            // 获取入库单明细信息
            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")
                .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(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()
                {
                    ASNNo = tb2.ASNNo,
                    LotNo = tb1.LotNo,
                    SkuNo = tb2.SkuNo,
                    SkuName = tb2.SkuName,
                    PalletNo = tb1.PalletNo,
                    RQty = tb1.Qty,
                    RFactQty = tb1.Qty,
                    RCompleteQty = tb1.Qty,
                    CompleteTime = tb2.CreateTime,
                    LotText = tb1.LotText,
                    SupplierLot = tb2.SupplierLot,
                    
                    SONo = "",
                    CTaskNo = "",
                    CCreateTime = null,
                    CQty = 0,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = 0
                });

            // 获取出库单明细信息
            var soList = Db.Queryable<BllExportAllot, BllExportNoticeDetail>
                (((tb1, tb2) => new JoinQueryInfos(
                 JoinType.Left, tb1.SODetailNo == tb2.Id
                )))
                .Where((tb1, tb2) => tb1.IsDel == "0" && tb2.IsDel == "0")
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), tb1 => tb1.UpdateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), tb1 => tb1.UpdateTime <= Convert.ToDateTime(endTime).AddDays(1))
                .WhereIF(!string.IsNullOrEmpty(palletNo), tb1 => tb1.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrEmpty(skuNo), tb1 => tb1.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), tb1 => tb1.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), tb1 => tb1.LotNo.Contains(lotNo))
                 //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                 .Select((tb1, tb2) => new TotalRecordDto()
                 {
                     ASNNo = "",
                     LotNo = tb1.LotNo,
                     SkuNo = tb2.SkuNo,
                     SkuName = tb2.SkuName,
                     PalletNo = tb1.PalletNo,
                     RQty = 0,
                     RFactQty = 0,
                     RCompleteQty = 0,
                     CompleteTime = tb2.CreateTime,
                     LotText = tb1.LotText,
                     SupplierLot = tb2.SupplierLot,

                     SONo = tb2.SONo,
                     CTaskNo = tb1.TaskNo,
                     CCreateTime = tb1.UpdateTime,
                     CQty = tb1.Qty,
                     CFactQty = tb1.Qty,
                     CAllotQty = tb1.Qty,
                     CompleteQty = (decimal)tb1.CompleteQty
                 });

            // 获取取样拣货信息
            var samplingList = Db.Queryable<BllSamplingDetails>()
                .Where(m => m.IsDel == "0")
                .WhereIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
                .WhereIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
                .WhereIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
                .WhereIF(!string.IsNullOrEmpty(palletNo), a => a.PalletNo.Contains(palletNo))
                .WhereIF(!string.IsNullOrWhiteSpace(startTime), a => a.CreateTime >= Convert.ToDateTime(startTime))
                .WhereIF(!string.IsNullOrWhiteSpace(endTime), a => a.CreateTime <= Convert.ToDateTime(endTime).AddDays(1))
                //.GroupBy(a => new { a.LotNo, a.SkuNo, a.SkuName })
                .Select(a => new TotalRecordDto()
                {
                    ASNNo = "",
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    PalletNo = a.PalletNo,
                    RQty = 0,
                    RFactQty = 0,
                    RCompleteQty = 0,
                    CompleteTime = a.CreateTime,
                    LotText = "",
                    SupplierLot = "",

                    SONo = a.QcNo,
                    CTaskNo = "",
                    CCreateTime = a.CreateTime,
                    CQty = (decimal)a.CompleteQty,
                    CFactQty = 0,
                    CAllotQty = 0,
                    CompleteQty = (decimal)a.CompleteQty
                });

            // 合并集合
            var dataList = Db.UnionAll(asnList, soList, samplingList)
                .Select(a => new TotalRecordDto()
                {
                    ASNNo = a.ASNNo,
                    LotNo = a.LotNo,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    PalletNo = a.PalletNo,
                    RQty = a.RQty,
                    RFactQty = a.RFactQty,
                    RCompleteQty = a.RCompleteQty,
                    CompleteTime = a.CompleteTime,
                    LotText = a.LotText,
                    SupplierLot = a.SupplierLot,

                    SONo = a.SONo,
                    CTaskNo = a.CTaskNo,
                    CCreateTime = a.CCreateTime,
                    CQty = a.CQty,
                    CFactQty = a.CFactQty,
                    CAllotQty = a.CAllotQty,
                    CompleteQty = a.CompleteQty
                }).ToList();

            return dataList;
        }

        #endregion

        #region 年度报表

        /// <summary>
        /// 获取年报表总表
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public List<SysMaterials> GetAssSoInfo(string StartTime, string EndTime)
        {
            try
            {
                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))
                {
                    str += $" and a.CompleteTime >= '{StartTime}' and b.UpdateTime >= '{StartTime}'";
                }
                //判断结束时间是否为空
                if (!string.IsNullOrWhiteSpace(EndTime))
                {
                    EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
                    str += $" and a.CompleteTime < '{EndTime}' and b.UpdateTime < '{EndTime}'";
                }

                str += " group by a.SkuNo,b.SkuNo)";

                var sku = Db.Ado.SqlQuery<SysMaterials>(str);
                return sku;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取年报表明细
        /// </summary>
        /// <param name="skuNo"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public List<AssSoDateStockInfoDto> GetAssSoInfoDetail(string skuNo, string StartTime, string EndTime)
        {
            try
            {
                //用于合并数据
                List<AssSoDateStockInfoDto> infolist = new List<AssSoDateStockInfoDto>();
                //获取开始时间当天的结存数量
                //DataStockInfo stockInfo = new DataStockInfo();
                //获取物料理论重量
                string weightstr = $"select Weight from SysMaterials where SkuNo = '{skuNo}'";
                var weight = Db.Ado.SqlQuerySingle<decimal>(weightstr);

                //判断开始时间是否为空
                if (!string.IsNullOrWhiteSpace(StartTime))
                {
                    //string str = $"select * from DataStockInfo where IsDel = '0' and CONVERT(date,CreateTime) = '{StartTime}'";
                    //获取当天库存信息
                    //stockInfo = Db.Ado.SqlQuerySingle<DataStockInfo>(str);
                }
                //判断结束时间是否为空
                if (!string.IsNullOrWhiteSpace(EndTime))
                {
                    EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
                }
                decimal countjie = 0; //统计结存数量
                                      ////判断库存信息是否为空
                                      //if (stockInfo != null)
                                      //{
                                      //    countjie = stockInfo.BalanceQty; //统计结存数量
                                      //}
                                      //else if (stockInfo == null)
                                      //{
                                      //判断开始时间是否为空
                if (!string.IsNullOrWhiteSpace(StartTime))
                {
                    //string StartTimelin = (Convert.ToDateTime(StartTime).AddDays(1)).ToString();
                    //获取出库数量
                    string str = $"select SUM(CompleteQty) as CompleteQtyc from BllExportNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0'  and CreateTime < '{StartTime}'";
                    string CompleteQtyc = Db.Ado.SqlQuerySingle<string>(str);
                    //获取入库数量
                    str = $"select SUM(CompleteQty) as CompleteQtyr from BllArrivalNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
                    string CompleteQtyr = Db.Ado.SqlQuerySingle<string>(str);

                    //计算结存数量
                    countjie = int.Parse(CompleteQtyr) - int.Parse(CompleteQtyc);

                }

                //}
                decimal yeara = 0; //年入库
                decimal years = 0; //年出库
                decimal montha = 0; //月入库
                decimal months = 0; //月出库
                DateTime createTime = DateTime.Now.AddYears(-1000); //时间

                //获取有多少年
                string yearstr = "select DATEPART(YEAR, CreateTime) SkuName from BllArrivalNoticeDetail where IsDel = '0' and Status = '2' ";
                if (!string.IsNullOrWhiteSpace(StartTime))
                {
                    yearstr += $" and CreateTime >= '{StartTime}'";
                }
                if (!string.IsNullOrWhiteSpace(EndTime))
                {

                    yearstr += $" and CreateTime < '{EndTime}'";
                }
                yearstr += "group by DATEPART(YEAR, CreateTime)";
                var yearlist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(yearstr);
                if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
                {
                    //判断开始时间年与结束时间年是否相等
                    var startyear = StartTime.Split('-');
                    var endyear = EndTime.Split("-");
                }
                foreach (var yearcount in yearlist)
                {
                    //获取当前物料每年入库单据明细
                    string ruyearstr = $"select ASNNo,SkuNo,SkuName,CompleteQty as ASNQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllArrivalNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
                    //获取当前物料每年出库单据明细
                    string chuyearstr = $"select SONo,SkuNo,SkuName,CompleteQty as SOQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllExportNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";

                    if (!string.IsNullOrWhiteSpace(StartTime))
                    {
                        ruyearstr += $" and CreateTime >= '{StartTime}'";
                        chuyearstr += $" and CreateTime >= '{StartTime}'";
                    }
                    if (!string.IsNullOrWhiteSpace(EndTime))
                    {
                        //EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
                        ruyearstr += $" and CreateTime < '{EndTime}'";
                        chuyearstr += $" and CreateTime < '{EndTime}'";
                    }

                    ruyearstr += " order by CreateTime";
                    chuyearstr += " order by CreateTime";
                    //每年入库
                    List<AssSoDateStockInfoDto> ruinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(ruyearstr);
                    //每年出库
                    List<AssSoDateStockInfoDto> chuinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(chuyearstr);

                    //循环12个月
                    for (int i = 1; i <= 12; i++)
                    {
                        //临时入
                        List<AssSoDateStockInfoDto> linshiru = new List<AssSoDateStockInfoDto>();
                        //临时出
                        List<AssSoDateStockInfoDto> linshichu = new List<AssSoDateStockInfoDto>();

                        if (ruinfolist.Count > 0)
                        {
                            linshiru = ruinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月入库信息
                        }
                        if (chuinfolist.Count > 0)
                        {
                            linshichu = chuinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月出库信息
                        }
                        if (linshichu.Count > 0)
                        {
                            foreach (var h in linshichu)
                            {
                                linshiru.Add(h);
                            }
                        }
                        var data = linshiru.OrderBy(a => a.CreateTime).ToList();

                        //判断是否存在当月入库信息
                        if (linshiru.Count > 0)
                        {

                            foreach (var item in data)
                            {
                                item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
                                //总计结存数量
                                if (item.ASNNo != null)
                                {
                                    //入库
                                    countjie += item.ASNQty;
                                    montha += item.ASNQty; //月入库
                                    yeara += item.ASNQty; //年入库
                                }
                                else if (item.SONo != null)
                                {
                                    //出库
                                    countjie -= item.SOQty;
                                    months += item.SOQty; //月出库
                                    years += item.SOQty; //年出库
                                }
                                item.ASNSOQty = countjie; //结存数量
                                item.Weight = weight; //重量
                                infolist.Add(item);

                                if (createTime < item.CreateTime)
                                {
                                    createTime = item.CreateTime; //时间
                                }
                            }
                        }
                        //判断是否存在当月出库信息
                        //if (linshichu.Count > 0)
                        //{
                        //    //出库
                        //    foreach (var item in linshichu)
                        //    {
                        //        item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
                        //        countjie -= item.SOQty; //总计结存数量
                        //        item.ASNSOQty = countjie; //结存数量
                        //        item.Weight = weight; //重量
                        //        infolist.Add(item);
                        //        months += item.SOQty; //月出库
                        //        years += item.SOQty; //年出库
                        //        if (createTime < item.CreateTime)
                        //        {
                        //            createTime = item.CreateTime; //时间
                        //        }
                        //    }
                        //}

                        //判断是否有月出入库
                        if (linshiru.Count > 0 || linshichu.Count > 0)
                        {
                            //月信息
                            var addmonthinfo = new AssSoDateStockInfoDto()
                            {
                                ASNNo = "", // 入库单据
                                SONo = "", // 出库单据

                                SkuNo = "", // 物料编码
                                SkuName = "", // 物料名称

                                NoticeType = "本月累计", //统计类别

                                ASNQty = montha, // 入库数量
                                SOQty = months, // 出库数量
                                ASNSOQty = countjie, // 结存数量

                                Weight = weight, // 理论重量

                                NoticeDateTime = yearcount.SkuName + "年" + i + "月", // 单据时间
                                CreateTime = createTime, //时间
                            };
                            infolist.Add(addmonthinfo);
                        }
                        montha = 0; //月入库
                        months = 0; //月出库
                    }
                    //判断是否有年出入库
                    if (ruinfolist.Count > 0 || chuinfolist.Count > 0)
                    {
                        //年信息
                        var addyearinfo = new AssSoDateStockInfoDto()
                        {
                            ASNNo = "", // 入库单据
                            SONo = "", // 出库单据

                            SkuNo = "", // 物料编码
                            SkuName = "", // 物料名称

                            NoticeType = "本年累计", //统计类别

                            ASNQty = yeara, // 入库数量
                            SOQty = years, // 出库数量
                            ASNSOQty = countjie, // 结存数量

                            Weight = weight, // 理论重量

                            NoticeDateTime = yearcount.SkuName + "年", // 单据时间
                            CreateTime = createTime, //时间
                        };
                        infolist.Add(addyearinfo);
                    }
                    yeara = 0; //年入库
                    years = 0; //年出库
                }

                infolist = infolist.OrderBy(x => x.CreateTime).ToList();
                return infolist;

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 导出库存
        /// <summary>
        /// 导出库存总量
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName)
        {
            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)
                {
                    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}";
                }
            }

            return StockListDto;
        }

        /// <summary>
        /// 导出库存明细
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <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="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)
        {
            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")
                {
                    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;
                }

            }
            return stockDetailsList;
        }
        #endregion
    }
}