using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Net.WebSockets;
using System.Reflection;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using Model.ModelDto;
using Model.ModelDto.DataDto;
using Model.ModelVm.DataVm;
using Model.ModelVm.SysVm;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
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="skuNo">物料编码</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetDataStockList(string skuNo)
        {
            string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,CONVERT(decimal(18,4),(mate.Weight * stock.Qty)) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = 0 and mate.IsDel = 0";
            //判断物料编码是否为空
            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)).ToString();
                            dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString();
                            dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString();
                            dto.ResidueQty = (Convert.ToInt32(dto.ResidueQty) + Convert.ToInt32(item.ResidueQty)).ToString();
                            dto.WeightSum = Convert.ToDecimal(dto.WeightSum) + Convert.ToDecimal(item.WeightSum);
                            break;
                        }
                        i += 1;
                        //判断已有相同物料
                        if (i == StockListDto.Count)
                        {
                            StockListDto.Add(item);
                            break;
                        }
                    }
                }
                else
                {
                    StockListDto.Add(item);
                    continue;
                }
            }

            return StockListDto;
        }

        /// <summary>
        /// 获取库存明细
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="type">类型</param>
        /// <param name="locatNo">储位地址</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <param name="PalletType">是否字母拖</param>
        /// <param name="IsBale">是否缠膜</param>
        /// <param name="IsBelt">是否打包</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetInventoryList1(string skuNo, string lotNo, string type, string locatNo, string palletNo, string status, string inspectStatus, string PalletType, string IsBale, string IsBelt)
        {
            string str = "select detail.Id,detail.PalletNo,detail.LocatNo,roadway.RoadwayName RoadwayNo,detail.SkuNo,detail.SkuName,detail.Standard,detail.LotNo,detail.LotText,detail.Qty,(mate.Weight * detail.Qty) WeightSum,detail.LockQty,detail.Status,detail.InspectStatus,detail.CompleteTime,detail.IsBale,detail.IsBelt,detail.PalletType,detail.Demo from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysMaterials mate on detail.SkuNo = mate.SkuNo 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(type))
            {
                str += " and mate.Type = @type";
            }
            //判断储位地址是否为空
            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.IsNullOrWhiteSpace(PalletType))
            {
                //判断零托标记是否为1
                if (PalletType == "1")
                {
                    str += " and detail.PalletType != '0'";
                }
                else
                {
                    str += " and detail.PalletType = '0'";
                }
            }
            //判断缠膜状态是否为空
            if (!string.IsNullOrWhiteSpace(IsBale))
            {
                if (IsBale == "1")
                {
                    str += " and detail.IsBale = '1'";
                }
                else
                {
                    str += " and detail.IsBale != '1'";
                }
            }
            //判断打包状态是否为空
            if (!string.IsNullOrWhiteSpace(IsBelt))
            {
                if (IsBelt == "1")
                {
                    str += " and detail.IsBelt = '1'";
                }
                else
                {
                    str += " and detail.IsBelt != '1'";
                }
            }
            //排序
            str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
            List<MateDataStockDto> stockDetailsList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = skuNo, //物料编码
                //skuname = skuName, //物料名称
                lotno = lotNo, //批次
                type, //物料类型
                locatno = locatNo, //储位地址
                palletno = palletNo, //托盘条码
                status, //库存状态
                inspectstatus = inspectStatus //质检状态
            });
            return stockDetailsList;
        }

        /// <summary>
        /// 获取库存明细
        /// </summary>
        /// <param name="Id">Id</param>
        /// <returns></returns>
        public MateDataStockDto GetInventoryListById(string Id)
        {
            string str = "select Id,PalletNo,SkuNo,SkuName,Qty,Demo from DataStockDetail detail where IsDel = @isdel and Id = @id";

            MateDataStockDto stockDetailsList = Db.Ado.SqlQuerySingle<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                id = Id, //Id
            });
            return stockDetailsList;
        }

        /// <summary>
        /// 增加库存备注
        /// </summary>
        /// <param name="model">库存model</param>
        /// <returns></returns>
        public async Task<int> EditDetailDemo(DataStockDetail model)
        {
            string str = "update DataStockDetail set Demo = @demo,UpdateTime = @utime,UpdateUser = @uuser where Id = @id";
            int i = await Db.Ado.ExecuteCommandAsync(str, new
            {
                demo = model.Demo, //备注
                utime = Db.GetDate(), //修改时间
                uuser = model.UpdateUser, //修改人
                id = model.Id, //库存id
            });
            return i;
        }

        /// <summary>
        /// 获取库存明细
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param> 
        /// <param name="palletNo">托盘条码</param>
        /// <param name="status">库存状态</param>
        /// <param name="page"></param>
        /// <param name="limit"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public List<DataStockDetail> GetOutSideStockList(string skuNo, string skuName, string lotNo, string palletNo, string status, int page, int limit, out int count)
        {
            try
            {
                Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
                    .AndIF(!string.IsNullOrWhiteSpace(skuNo), it => it.SkuNo.Contains(skuNo.Trim()))
                    .AndIF(!string.IsNullOrWhiteSpace(skuName), it => it.SkuName.Contains(skuName.Trim()))
                    .AndIF(!string.IsNullOrWhiteSpace(status), it => it.Status == status)
                    .AndIF(!string.IsNullOrWhiteSpace(lotNo), it => it.LotNo.Contains(lotNo.Trim()))
                    .AndIF(!string.IsNullOrWhiteSpace(palletNo), it => it.PalletNo.Contains(palletNo.Trim()))
                    .And(it => string.IsNullOrWhiteSpace(it.LocatNo))
                    .ToExpression();//注意 这一句 不能少
                var total = 0;
                var data = Db.Queryable<DataStockDetail>().Where(item).ToOffsetPage(page, limit, ref total);

                count = total;

                return data;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }


        #endregion

        #region 托盘明细

        /// <summary>
        /// 获取托盘明细
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="palletNo">托盘号</param>
        /// <param name="lotNo">批次号</param>
        /// <param name="boxNo">箱码</param>
        /// <param name="status">箱支状态</param>
        /// <param name="inspectMark">检验标记</param>
        /// <param name="bitPalletMark">零托标记</param>
        /// <param name="bitBoxMark">零箱标记</param>
        /// <param name="inspectStatus">质量状态</param>
        /// <returns></returns>
        public List<DataStockDetail> GetBindList(string skuNo, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitPalletMark, string bitBoxMark, string inspectStatus)
        {
            string str = "select Id,LotNo,LotText,SupplierLot,SkuNo,SkuName,Qty,LockQty,FrozenQty,AreaNo,LocatNo,PalletNo,PalletNo2,PalletNo3,InspectMark,BitPalletMark,Status from DataStockDetail where IsDel = @isdel";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and SkuNo like @skuno";
            }
            //判断托盘号是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and PalletNo like @palletno";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and LotNo like @lotno";
            }
            //判断零托标记是否为空
            if (!string.IsNullOrEmpty(bitPalletMark))
            {
                str += " and BitPalletMark = @bitpalletmark";
            }

            int i = 0;
            //在箱码明细中获取相应数据
            string boxstr = "select StockDetailId from DataBoxInfo Where IsDel = @isdel";
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
                boxstr += " and BoxNo like @boxno";
                i = 1;
            }
            //判断箱支状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                boxstr += " and Status = @status";
                i = 1;
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                //boxstr += " and InspectMark = @inspectmark";
                str += " and InspectMark = @inspectmark";
                i = 1;
            }
            //判断零箱标记是否为空
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                boxstr += " and BitBoxMark = @bitboxmark";
                i = 1;
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                boxstr += " and InspectStatus = @inspectstatus";
                str += " and InspectStatus = @inspectstatus";
                i = 1;
            }
            //将箱码数据进行排序`
            boxstr += " order by StockDetailId";
            //查出箱码明细中信息
            List<int> stockDetailId = Db.Ado.SqlQuery<int>(boxstr, new
            {
                isdel = "0", //是否删除
                boxno = "%" + boxNo + "%", //箱码
                status, //箱支状态
                inspectmark = inspectMark, //检验标记
                bitboxmark = bitBoxMark, //零箱标记
                inspectstatus = inspectStatus //质量状态
            });
            string arr = "";
            //判断是否有查箱码信息
            if (i == 1)
            {
                if (stockDetailId.Count > 0)
                {
                    //去重
                    arr += stockDetailId[0].ToString();

                    for (int a = 1; a < stockDetailId.Count; a++)
                    {
                        if (stockDetailId[a] != stockDetailId[a - 1])
                        {
                            arr += ',' + stockDetailId[a].ToString();
                        }
                    }
                }
                if (!string.IsNullOrEmpty(arr))
                {
                    str += $" and Id in ({arr})";
                }
                else
                {
                    str += " and Id = ''";
                }
            }

            //排序
            str += " order by LocatNo,LotNo,SkuNo,PalletNo";
            List<DataStockDetail> boxInforList = Db.Ado.SqlQuery<DataStockDetail>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                palletno = "%" + palletNo + "%", //托盘
                lotno = "%" + lotNo + "%", //批次号
                bitpalletmark = bitPalletMark, //零托标记
                inspectmark = inspectMark, //检验标记
                inspectstatus = inspectStatus, //质量状态
                //stockdetailid = "(" + arr + ")" //库存明细id
            });

            return boxInforList;
        }

        /// <summary>
        /// 获取箱码明细
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="palletNo">托盘号</param>
        /// <param name="lotNo">批次号</param>
        /// <param name="boxNo">箱码</param>
        /// <param name="status">箱支状态</param>
        /// <param name="inspectMark">检验标记</param>
        /// <param name="bitBoxMark">零箱标记</param>
        /// <param name="inspectStatus">质量状态</param>
        /// <returns></returns>
        public List<DataBoxInfo> GetBoxInfor(string id, string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitBoxMark, string inspectStatus)
        {
            string str = "select PalletNo,PalletNo2,PalletNo3,BoxNo,BoxNo2,BoxNo3,Status,LotNo,Qty,FullQty,SkuNo,SkuName,LotText,ProductionTime,InspectMark,BitBoxMark,InspectStatus,InspectTime from DataBoxInfo Where IsDel = @isdel";
            //判断id是否为空
            if (!string.IsNullOrEmpty(id))
            {
                str += " and StockDetailId = @id";
            }
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and SkuName like @skuname";
            }
            //判断托盘号是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and PalletNo like @palletno";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and LotNo like @lotno";
            }
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
                str += " and BoxNo like @boxno";
            }
            //判断箱支状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and Status = @status";
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                str += " and InspectMark = @inspectmark";
            }
            //判断零箱标记是否为空
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                str += " and BitBoxMark = @bitboxmark";
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and InspectStatus = @inspectstatus";
            }
            //排序
            str += " order by LotNo,PalletNo,SkuNo";
            List<DataBoxInfo> boxInfor = Db.Ado.SqlQuery<DataBoxInfo>(str, new
            {
                isdel = "0", //是否删除
                id, //id
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                palletno = "%" + palletNo + "%", //托盘
                lotno = "%" + lotNo + "%", //批次号
                boxno = "%" + boxNo + "%", //箱码
                status, //箱支状态
                inspectmark = inspectMark, //检验标记
                bitboxmark = bitBoxMark, //零箱标记
                inspectstatus = inspectStatus //质量状态
            });
            return boxInfor;
        }

        /// <summary>
        /// 根据箱码获取箱支信息
        /// </summary>
        /// <param name="boxNo">箱码</param>
        /// <param name="boxNo3">支码</param>
        /// <returns></returns>
        public List<DataBoxInfo> GetBoxInforByBoxNo(string boxNo, string boxNo3)
        {
            string str = "select PalletNo,PalletNo2,PalletNo3,BoxNo,BoxNo2,BoxNo3,Status,LotNo,Qty,FullQty,SkuNo,SkuName,LotText,ProductionTime,InspectMark,BitBoxMark,InspectStatus,InspectTime from DataBoxInfo Where IsDel = @isdel";
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
                str += " and BoxNo = @boxno";
            }
            //判断支码是否为空
            if (!string.IsNullOrEmpty(boxNo3))
            {
                str += " and BoxNo3 like @boxno3";
            }
            //排序
            str += " order by LotNo,PalletNo,SkuNo";
            List<DataBoxInfo> boxInfor = Db.Ado.SqlQuery<DataBoxInfo>(str, new
            {
                isdel = "0", //是否删除
                boxno = boxNo, //箱码
                boxno3 = "%" + boxNo3 + "%", //支码
            });
            return boxInfor;
        }

        #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="palletNo">托盘号</param>
        /// <param name="status">状态</param>
        /// <param name="inspectMark">检验标记</param>
        /// <param name="inspectStatus">质量状态</param>
        /// <param name="isBale">是否裹包</param>
        /// <param name="isBelt">是否打带</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetEmergencyWarning(string skuNo, string skuName, string lotNo, string palletNo, string status, string inspectMark, string inspectStatus, string isBale, string isBelt)
        {
            string str = "select stock.Id,stock.LotNo,stock.LotText,stock.SupplierLot,stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,stock.LocatNo,stock.PalletNo,stock.ProductionTime,stock.ExpirationTime,stock.Status,stock.InspectMark,stock.InspectStatus,stock.IsBale,stock.IsBelt from DataStockDetail stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo where stock.ExpirationTime-mate.AdventTime <= GETDATE()";
            //判断物料号是否为空
            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(palletNo))
            {
                str += " and stock.PalletNo like @palletno";
            }
            //判断状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and stock.Status = @status";
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                str += " and stock.InspectMark = @inspectmark";
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and stock.InspectStatus = @inspectstatus";
            }
            //判断裹包是否为空
            if (!string.IsNullOrEmpty(isBale))
            {
                str += " and stock.IsBale = @isbale";
            }
            //判断打带是否为空
            if (!string.IsNullOrEmpty(isBelt))
            {
                str += " and stock.IsBelt = @isbelt";
            }
            //排序
            str += " order by stock.SkuNo,stock.LotNo";
            List<MateDataStockDto> stockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                skuno = skuNo, //物料号
                skuname = skuName, //物料名称
                lotno = lotNo, //批次
                palletno = palletNo, //托盘号
                status, //状态
                inspectmark = inspectMark, //检验标记
                inspectstatus = inspectStatus, //质量状态
                isbale = isBale, //是否裹包
                isbel = isBelt //是否打带
            });
            return stockList;
        }

        #endregion

        #region 出入库报表

        /// <summary>
        /// 获取出入库总量
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo)
        {
            //入库
            string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail where IsDel = '0' group by ASNDetailNo) ";
            //出库
            string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' and LotNo in (select LotNo from DataStock where IsDel = '0') ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and SkuNo like @skuno";
                cstr += " and SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                rstr += " and SkuName like @skuname";
                cstr += " and SkuName like @skuname";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and LotNo like @lotno";
                cstr += " and LotNo like @lotno";
            }
            rstr += " group by LotNo,SkuNo,SkuName";
            cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
            });
            List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
            });

            //合并数据
            int a = 0;
            foreach (var h in totalHListData)
            {
                if (a >= totalCListData.Count)
                {
                    continue;
                }
                foreach (var c in totalCListData)
                {
                    //判断出库是否拥有相同批次 且物料相同
                    if (h.LotNo == c.LotNo)
                    {
                        if (h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
                        {
                            h.CQty = c.CQty; //出库数量
                            h.CAllotQty = c.CAllotQty; //分配数量
                            h.CFactQty = c.CFactQty; //下架数量
                            h.CompleteQty = c.CompleteQty; //拣货数量
                            //h.SONo = c.SONo; //出库单号

                            a += 1;

                            break;
                        }
                    }
                }
            }

            return totalHListData;
        }

        /// <summary>
        /// 获取出入库记录
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo)
        {
            //入库
            string rstr = "select notice.ASNNo,bind.LotNo,notice.SkuNo,notice.SkuName,bind.PalletNo,bind.Qty RQty,bind.Qty RFactQty,bind.Qty  RCompleteQty,bind.CompleteTime,notice.LotText,notice.SupplierLot,bind.CompleteTime from BllPalletBind bind left join BllArrivalNoticeDetail notice on bind.ASNDetailNo = notice.Id where bind.IsDel = '0' and notice.IsDel = '0' and bind.LotNo in (select LotNo from DataStock where IsDel = '0')   ";
            //出库
            string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo,allot.CreateTime CCreateTime,allot.Qty CQty,allot.Qty CAllotQty,Allot.Qty CFactQty,Allot.CompleteQty CompleteQty,allot.LotText,allot.SupplierLot from BllExportAllot allot left join BllExportNoticeDetail notice on allot.SODetailNo = notice.Id where allot.IsDel = '0' and notice.IsDel = '0' and notice.LotNo in (select LotNo from DataStock where IsDel = '0')  ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and notice.SkuNo like @skuno";
                cstr += " and allot.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                rstr += " and notice.SkuName like @skuname";
                cstr += " and allot.SkuName like @skuname";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and notice.LotNo like @lotno";
                cstr += " and allot.LotNo like @lotno";
            }

            //判断托盘是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                rstr += " and bind.PalletNo like @palletno";
                cstr += " and allot.PalletNo like @palletno";
            }

            //rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
            //cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });
            //出库
            List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });

            //合并
            foreach (var c in totalCList)
            {
                int i = 0;
                foreach (var r in totalRList)
                {
                    i += 1;
                    //判断入库中是否存在出库中的批次物料托盘
                    if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
                    {
                        if (r.SONo != null)
                        {
                            if (r.SONo != c.SONo)
                            {
                                totalRList.Add(c);
                            }
                        }
                        if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
                        {
                            r.SONo = c.SONo; //出库单号
                            r.CQty += c.CQty; //出库数量
                            r.CAllotQty += c.CAllotQty; //分配数量
                            r.CFactQty += c.CFactQty; //下架数量
                            r.CompleteQty += c.CompleteQty; //拣货数量
                            r.CCreateTime = c.CCreateTime; //拣货时间
                        }
                        if (r.CompleteTime == null)
                        {
                            r.RCompleteQty = 0;
                        }
                        break;
                    }
                    if (i == totalRList.Count)
                    {
                        totalRList.Add(c);
                        break;
                    }
                }
            }
            if (totalCList.Count == 0)
            {
                foreach (var r in totalRList)
                {
                    if (r.CompleteTime == null)
                    {
                        r.RCompleteQty = 0;
                        continue;
                    }
                }
            }

            return totalRList;
        }

        #endregion


        #region 周期报表

        #region 周期
        /// <summary>
        /// 获取周期报表
        /// </summary>
        /// <param name="TaskNo">任务号</param>
        /// <param name="DataStatus">时间状态</param>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="type">出、入库</param>
        /// <param name="page">页</param>
        /// <param name="limit">每页多少数</param>
        /// <param name="count">库存数量</param>
        /// <returns></returns>
        public List<SysMaterialsDto> GetToCycleDataStockDetails(string TaskNo, string DataStatus, string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
        {
            //获取当前日期
            var data = Db.GetDate();
            //判断时间
            if (DataStatus == "1")
            {
                //日
                data = data.AddDays(-1);

            }
            else if (DataStatus == "2")
            {
                //周
                data = data.AddDays(-7);
            }
            else if (DataStatus == "3")
            {
                //月
                data = data.AddMonths(-1);
            }
            else if (DataStatus == "4")
            {
                //年
                data = data.AddYears(-1);
            }

            //入库
            if (type == "1")
            {
                string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
                string stra = $" and SkuNo IN (SELECT SkuNo FROM BllBoxInfo where IsDel = '0' and Status = '2' and CompleteTime >= '{data}'  "; //物料信息子条件
                string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllBoxInfo where IsDel = '0' and CompleteTime >= '{data}' and Status = '2' "; //物料入库数量
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                    stra += $" and SkuNo = '{skuNo}'";
                    str1 += $" and SkuNo = '{skuNo}'";
                }

                stra += " group by SkuNo,SkuName);";
                str += stra;
                str1 += " group by SkuNo,SkuName ";

                var total = 0;
                var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
                var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
                foreach (var sku in skuinfo)
                {
                    foreach (var qty in qtyList)
                    {
                        if (sku.SkuNo == qty.SkuNo)
                        {
                            sku.Qty += qty.Qty;
                        }
                    }
                }
                count = total;

                return skuinfo;
            }

            //出库
            else if (type == "2")
            {
                string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
                string stra = $" and SkuNo IN (SELECT SkuNo FROM BllExportAllot where IsDel = '0' and Status = '5' and CreateTime >= '{data}'  "; //物料信息子条件
                string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllExportAllot where IsDel = '0' and CreateTime >= '{data}' and Status = '5' "; //物料出库数量
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                    stra += $" and SkuNo = '{skuNo}'";
                    str1 += $" and SkuNo = '{skuNo}'";
                }

                stra += " group by SkuNo,SkuName);";
                str += stra;
                str1 += " group by SkuNo,SkuName ";

                var total = 0;
                var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
                var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
                foreach (var sku in skuinfo)
                {
                    foreach (var qty in qtyList)
                    {
                        if (sku.SkuNo == qty.SkuNo)
                        {
                            sku.Qty += qty.Qty;
                        }
                    }
                }
                count = total;

                return skuinfo;
            }
            //盘库
            else if (type == "3")
            {
                string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
                string stra = $" and SkuNo IN (SELECT SkuNo FROM BllStockCheckDetail where IsDel = '0' and Status = '4' and CreateTime >= '{data}'  "; //物料信息子条件
                string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllStockCheckDetail where IsDel = '0' and CreateTime >= '{data}' and Status = '4' "; //物料出库数量
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                    stra += $" and SkuNo = '{skuNo}'";
                    str1 += $" and SkuNo = '{skuNo}'";
                }

                stra += " group by SkuNo,SkuName);";
                str += stra;
                str1 += " group by SkuNo,SkuName ";

                var total = 0;
                var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
                var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
                foreach (var sku in skuinfo)
                {
                    foreach (var qty in qtyList)
                    {
                        if (sku.SkuNo == qty.SkuNo)
                        {
                            sku.Qty += qty.Qty;
                        }
                    }
                }
                count = total;

                return skuinfo;
            }
            else
            {
                throw new Exception("异常！");
            }
        }


        /// <summary>
        /// 获取周期报表
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public List<DataStockInfoVM> GetDataStockInfo(string StartTime, string EndTime)
        {
            try
            {
                //当日信息
                string str = "select a.*,b.Weight from DataStockInfo a left join SysMaterials b on a.SkuNo = b.SkuNo where a.IsDel = '0' ";
                //判断开始时间是否为空
                if (!string.IsNullOrWhiteSpace(StartTime))
                {
                    str += $" and CONVERT(date,a.CreateTime) = '{StartTime}'";
                }
                else if (string.IsNullOrWhiteSpace(StartTime))
                {
                    str += " and DateDiff(dd,a.CreateTime,getdate())=0";
                }
                //实例化库存信息
                var stockinfo = Db.Ado.SqlQuery<DataStockInfoVM>(str);
                return stockinfo;


                #region 废弃代码
                /*
                //实例化库存信息
                List<DataStockInfo> stockinfo = new List<DataStockInfo>();
                //实例化库存期初信息
                List<DataStockInfo> qiStockinfo = new List<DataStockInfo>();
                //实例化库存结存信息
                List<DataStockInfo> jieStockinfo = new List<DataStockInfo>();
                //开始为空期初集合
                List<DataStockInfo> startnullinfo = new List<DataStockInfo>();
                //结束为空结存数量
                List<DataStockInfo> endnullinfo = new List<DataStockInfo>();

                //今天的年月日
                string year = DateTime.Now.ToString("yyyy"); //年
                string month = DateTime.Now.ToString("MM"); //月
                string day = DateTime.Now.ToString("dd"); //日
                //当天时间查询条件
                string datastr = $" and (DATEPART(YEAR, CreateTime)= '{year}') and (DATEPART(MONTH, CreateTime)= '{month}') and (DATEPART(DAY, CreateTime)= '{day}') ";

                #region 获取数据

                //判断开始或结束时间是否为空
                if (string.IsNullOrWhiteSpace(StartTime) && string.IsNullOrWhiteSpace(EndTime))
                {
                    string str = "select * from DataStockInfo where IsDel = '0' " + datastr + " order by Id";
                    stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
                }
                //判断开始时间与结束时间是否相等
                else if (StartTime == EndTime)
                {
                    string str = "select * from DataStockInfo where IsDel = '0' " + datastr + " order by Id";
                    stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
                }
                else if (!string.IsNullOrWhiteSpace(StartTime) || !string.IsNullOrWhiteSpace(EndTime))
                {
                    //开始时间划分
                    var start = StartTime.Split('-');
                    //结束时间划分
                    var end = EndTime.Split('-');

                    //获取库存信息sql
                    string str = " select SkuNo,SUM(ArrivalQty) as ArrivalQty,SUM(ExportQty) as ExportQty from DataStockInfo where IsDel = '0'";
                    //获取期初数量sql 开始时间当天
                    string qstr = "select SkuNo,SkuName,Standard,Type,ExpectedQty from DataStockInfo where IsDel = '0'";
                    //获取结存数量sql 结束时间当天
                    string jstr = "select SkuNo,SkuName,Standard,Type,BalanceQty from DataStockInfo where IsDel = '0'";

                    //判断开始时间不为空 结束时间为空
                    if (!string.IsNullOrWhiteSpace(StartTime) && string.IsNullOrWhiteSpace(EndTime))
                    {
                        //获取库存信息
                        str += $" and CreateTime >= '{StartTime}' group by SkuNo ";
                        stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
                        //结束为空结存数量
                        string jieqtystr = $"select SkuNo,SkuName,Standard,Type,BalanceQty from DataStockInfo where Id in (select MAX(Id) as Id from DataStockInfo where IsDel = '0' group by SkuNo)";
                        endnullinfo = Db.Ado.SqlQuery<DataStockInfo>(jieqtystr);
                        //库存信息与结存数量合并
                        foreach (var item in stockinfo)
                        {
                            //结存数量
                            foreach (var enditem in endnullinfo)
                            {
                                //判断物料编码是否相同
                                if (item.SkuNo == enditem.SkuNo)
                                {
                                    item.SkuName = enditem.SkuName; //物料名称
                                    item.Standard = enditem.Standard; //规格
                                    item.Type = enditem.Type; //类型
                                    item.BalanceQty = enditem.BalanceQty; //结存数量
                                    break;
                                }
                            }
                        }
                        //获取期初信息
                        year = start[0];
                        month = start[1];
                        day = start[2];
                        qstr += datastr;
                        qiStockinfo = Db.Ado.SqlQuery<DataStockInfo>(qstr);
                    }
                    //判断开始时间为空 结束时间不为空
                    else if (string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
                    {
                        EndTime = end[0] + "-" + end[1] + "-" + (int.Parse(end[2]) + 1).ToString();
                        //获取库存信息
                        str += $" and CreateTime < '{EndTime}'  group by SkuNo ";
                        stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
                        //结束为空期初数量
                        string qiqtystr = $"select SkuNo,SkuName,Standard,Type,ExpectedQty from DataStockInfo where Id in (select Min(Id) as Id from DataStockInfo where IsDel = '0' group by SkuNo)";
                        startnullinfo = Db.Ado.SqlQuery<DataStockInfo>(qiqtystr);
                        //库存信息与期初数量合并
                        foreach (var item in stockinfo)
                        {
                            //期初数量
                            foreach (var enditem in startnullinfo)
                            {
                                //判断物料编码是否相同
                                if (item.SkuNo == enditem.SkuNo)
                                {
                                    item.SkuName = enditem.SkuName; //物料名称
                                    item.Standard = enditem.Standard; //规格
                                    item.Type = enditem.Type; //类型
                                    item.ExpectedQty = enditem.ExpectedQty; //期初数量
                                    break;
                                }
                            }
                        }
                        //获取结存信息
                        year = end[0];
                        month = end[1];
                        day = end[2];
                        jstr += datastr;
                        jieStockinfo = Db.Ado.SqlQuery<DataStockInfo>(jstr);
                    }
                    //判断开始和结束时间不为空
                    else if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
                    {
                        EndTime = end[0] + "-" + end[1] + "-" + (int.Parse(end[2]) + 1).ToString();
                        //获取库存信息
                        str += $" and CreateTime >= '{StartTime}' and CreateTime < '{EndTime}'  group by SkuNo ";
                        stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
                        //获取期初信息
                        year = start[0];
                        month = start[1];
                        day = start[2];
                        qstr += datastr;
                        qiStockinfo = Db.Ado.SqlQuery<DataStockInfo>(qstr);
                        //获取结存信息
                        year = end[0];
                        month = end[1];
                        day = end[2];
                        jstr += datastr;
                        jieStockinfo = Db.Ado.SqlQuery<DataStockInfo>(jstr);
                    }

                }

                #endregion

                #region 合并

                //判断期初或结存集合是否为空
                if (qiStockinfo.Count > 0 || jieStockinfo.Count > 0)
                {
                    //库存信息
                    foreach (var item in stockinfo)
                    {
                        //判断期初是否为空
                        if (qiStockinfo.Count > 0)
                        {
                            //期初信息
                            foreach (var qitem in qiStockinfo)
                            {
                                //判断物料编码是否一致
                                if (item.SkuNo == qitem.SkuNo)
                                {
                                    item.SkuName = qitem.SkuName; //物料名称
                                    item.Standard = qitem.Standard; //规格
                                    item.Type = qitem.Type; //类型
                                    item.ExpectedQty = qitem.ExpectedQty; //期初数量
                                    break;
                                }
                            }
                        }
                        //判断结存是否为空
                        if (jieStockinfo.Count > 0)
                        {
                            //结存信息
                            foreach (var jitem in jieStockinfo)
                            {
                                //判断物料编码是否一致
                                if (item.SkuNo == jitem.SkuNo)
                                {
                                    item.SkuName = jitem.SkuName; //物料名称
                                    item.Standard = jitem.Standard; //规格
                                    item.Type = jitem.Type; //类型
                                    item.BalanceQty = jitem.BalanceQty; //结存数量
                                    break;
                                }
                            }
                        }

                    }
                }
                */

                #endregion


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }


        /// <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 inner 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();
                }
                int 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);

                    }

                }
                int yeara = 0; //年入库
                int years = 0; //年出库
                int montha = 0; //月入库
                int 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 (linshiru.Count > 0)
                        {
                            //入库
                            foreach (var item in linshiru)
                            {
                                item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
                                countjie += item.ASNQty; //总计结存数量
                                item.ASNSOQty = countjie; //结存数量
                                item.Weight = weight; //重量
                                infolist.Add(item);
                                montha += item.ASNQty; //月入库
                                yeara += item.ASNQty; //年入库
                                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="lotNo">批次</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="type">出、入库</param>
        /// <param name="page">页</param>
        /// <param name="limit">每页多少数</param>
        /// <param name="count">库存数量</param>
        /// <returns></returns>
        public List<PalletBindDto> GetToDayDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
        {
            //获取前一天现在的日期
            var data = Db.GetDate().AddDays(-1);

            //入库
            if (type == "1")
            {
                string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and box.SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and box.LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and bind.PalletNo = '{palletNo}'";
                }

                str += $" and bind.CompleteTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //出库
            else if (type == "2")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";


                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //盘库
            else if (type == "3")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                foreach (var item in palletBindDto)
                {
                    item.IsBale = "0";
                    item.IsBelt = "0";
                }
                count = total;

                return palletBindDto;
            }
            else
            {
                throw new Exception("异常！");
            }



        }

        #endregion

        #region 周

        /// <summary>
        /// 每周报表
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="type">出、入库</param>
        /// <param name="page">页</param>
        /// <param name="limit">每页多少数</param>
        /// <param name="count">库存数量</param>
        /// <returns></returns>
        public List<PalletBindDto> GetToWeekDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
        {
            //获取前一年现在的日期
            var data = Db.GetDate().AddDays(-7);

            //入库
            if (type == "1")
            {
                string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and box.SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and box.LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and bind.PalletNo = '{palletNo}'";
                }

                str += $" and bind.CompleteTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //出库
            else if (type == "2")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";


                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //盘库
            else if (type == "3")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                foreach (var item in palletBindDto)
                {
                    item.IsBale = "0";
                    item.IsBelt = "0";
                }
                count = total;

                return palletBindDto;
            }
            else
            {
                throw new Exception("异常！");
            }
        }

        #endregion

        #region 月

        /// <summary>
        /// 每月报表
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="type">出、入库</param>
        /// <param name="page">页</param>
        /// <param name="limit">每页多少数</param>
        /// <param name="count">库存数量</param>
        /// <returns></returns>
        public List<PalletBindDto> GetToMonthsDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
        {
            //获取前一年现在的日期
            var data = Db.GetDate().AddMonths(-1);

            //入库
            if (type == "1")
            {
                string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and box.SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and box.LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and bind.PalletNo = '{palletNo}'";
                }

                str += $" and bind.CompleteTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //出库
            else if (type == "2")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";


                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //盘库
            else if (type == "3")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                foreach (var item in palletBindDto)
                {
                    item.IsBale = "0";
                    item.IsBelt = "0";
                }
                count = total;

                return palletBindDto;
            }
            else
            {
                throw new Exception("异常！");
            }
        }

        #endregion

        #region 年

        /// <summary>
        /// 每年报表
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="type">出、入库</param>
        /// <param name="page">页</param>
        /// <param name="limit">每页多少数</param>
        /// <param name="count">库存数量</param>
        /// <returns></returns>
        public List<PalletBindDto> GetToYearDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
        {
            //获取前一年现在的日期
            var data = Db.GetDate().AddYears(-1);

            //入库
            if (type == "1")
            {
                string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and box.SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and box.LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and bind.PalletNo = '{palletNo}'";
                }

                str += $" and bind.CompleteTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //出库
            else if (type == "2")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";


                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                count = total;

                return palletBindDto;
            }
            //盘库
            else if (type == "3")
            {
                string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
                if (!string.IsNullOrWhiteSpace(skuNo))
                {
                    str += $" and SkuNo = '{skuNo}'";
                }
                if (!string.IsNullOrWhiteSpace(lotNo))
                {
                    str += $" and LotNo = '{lotNo}'";
                }
                if (!string.IsNullOrWhiteSpace(palletNo))
                {
                    str += $" and PalletNo = '{palletNo}'";
                }

                str += $" and CreateTime >= '{data}'";

                var total = 0;
                //var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
                var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
                foreach (var item in palletBindDto)
                {
                    item.IsBale = "0";
                    item.IsBelt = "0";
                }
                count = total;

                return palletBindDto;
            }
            else
            {
                throw new Exception("异常！");
            }
        }

        #endregion
        */
        #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)).ToString();
                            dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString();
                            dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString();
                            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>
        /// <returns></returns>
        public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string palletNo, string status, string inspectStatus)
        {
            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";
            }
            //排序
            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

        #region 导出出入库报表
        /// <summary>
        /// 导出库存总量
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘号</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetTotalRecordDaoChu(string skuNo, string lotNo, string palletNo)
        {
            //入库
            string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail where IsDel = '0' group by ASNDetailNo) ";
            //出库
            string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' and LotNo in (select LotNo from DataStock where IsDel = '0') ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and SkuNo like @skuno";
                cstr += " and SkuNo like @skuno";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and LotNo like @lotno";
                cstr += " and LotNo like @lotno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                rstr += " and PalletNo like @palletno";
                cstr += " and PalletNo like @palletno";
            }
            rstr += " group by LotNo,SkuNo,SkuName";
            cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });
            List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });

            //合并数据
            int a = 0;
            foreach (var h in totalHListData)
            {
                if (a >= totalCListData.Count)
                {
                    continue;
                }
                foreach (var c in totalCListData)
                {
                    //判断出库是否拥有相同批次 且物料相同
                    if (h.LotNo == c.LotNo)
                    {
                        if (h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
                        {
                            h.CQty = c.CQty; //出库数量
                            h.CAllotQty = c.CAllotQty; //分配数量
                            h.CFactQty = c.CFactQty; //下架数量
                            h.CompleteQty = c.CompleteQty; //拣货数量
                            //h.SONo = c.SONo; //出库单号

                            a += 1;

                            break;
                        }
                    }
                }
            }

            foreach (var item in totalHListData)
            {
                //物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
                if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
                {
                    item.SkuNo = $"'{item.SkuNo}";
                }
            }

            return totalHListData;
        }

        /// <summary>
        /// 导出出入库报表记录
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="lotNo">批次</param>
        /// <param name="palletNo">托盘号</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetDetailedRecordDaoChu(string skuNo, string lotNo, string palletNo)
        {
            //入库
            string rstr = "select notice.ASNNo,bind.LotNo,notice.SkuNo,notice.SkuName,bind.PalletNo,bind.Qty RQty,bind.Qty RFactQty,bind.Qty  RCompleteQty,bind.CompleteTime,notice.LotText,notice.SupplierLot,bind.CompleteTime from BllPalletBind bind left join BllArrivalNoticeDetail notice on bind.ASNDetailNo = notice.Id where bind.IsDel = '0' and notice.IsDel = '0' and bind.LotNo in (select LotNo from DataStock where IsDel = '0')   ";
            //出库
            string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo,allot.CreateTime CCreateTime,allot.Qty CQty,allot.Qty CAllotQty,Allot.Qty CFactQty,Allot.CompleteQty CompleteQty,allot.LotText,allot.SupplierLot from BllExportAllot allot left join BllExportNoticeDetail notice on allot.SODetailNo = notice.Id where allot.IsDel = '0' and notice.IsDel = '0' and notice.LotNo in (select LotNo from DataStock where IsDel = '0')  ";

            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                rstr += " and notice.SkuNo like @skuno";
                cstr += " and allot.SkuNo like @skuno";
            }
            //判断批次号是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                rstr += " and notice.LotNo like @lotno";
                cstr += " and allot.LotNo like @lotno";
            }

            //判断托盘是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                rstr += " and bind.PalletNo like @palletno";
                cstr += " and allot.PalletNo like @palletno";
            }

            //rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
            //cstr += " group by LotNo,SkuNo,SkuName";
            //入库
            List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });
            //出库
            List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
            });

            //合并
            foreach (var c in totalCList)
            {
                int i = 0;
                foreach (var r in totalRList)
                {
                    i += 1;
                    //判断入库中是否存在出库中的批次物料托盘
                    if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
                    {
                        if (r.SONo != null)
                        {
                            if (r.SONo != c.SONo)
                            {
                                totalRList.Add(c);
                            }
                        }
                        if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
                        {
                            r.SONo = c.SONo; //出库单号
                            r.CQty += c.CQty; //出库数量
                            r.CAllotQty += c.CAllotQty; //分配数量
                            r.CFactQty += c.CFactQty; //下架数量
                            r.CompleteQty += c.CompleteQty; //拣货数量
                            r.CCreateTime = c.CCreateTime; //拣货时间
                        }
                        if (r.CompleteTime == null)
                        {
                            r.RCompleteQty = 0;
                        }
                        break;
                    }
                    if (i == totalRList.Count)
                    {
                        totalRList.Add(c);
                        break;
                    }
                }
            }
            if (totalCList.Count == 0)
            {
                foreach (var r in totalRList)
                {
                    if (r.CompleteTime == null)
                    {
                        r.RCompleteQty = 0;
                        continue;
                    }
                }
            }


            foreach (var item in totalCList)
            {
                //储位地址，加上单引号是防止导出到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 totalRList;
        }

        List<BllPalletBind> IStockServer.GetOutSideStockList(string skuNo, string skuName, string lotNo, string palletNo, string status, int page, int limit, out int count)
        {
            throw new NotImplementedException();
        }
        #endregion

    }
}