using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.X86;
using System.Security.Claims;
using System.Security.Policy;
using System.Text;
using Model.InterFaceModel;
using Model.ModelDto;
using Model.ModelDto.DataDto;
using Model.ModelDto.SysDto;
using Model.ModelVm.DataVm;
using Newtonsoft.Json;
using SqlSugar;
using Utility.Tools;
using WMS.BLL.LogServer;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.BllSoEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.Entity.LogEntity;
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>
        /// <param name="skuName">物料名称</param>
        /// <returns></returns>
        public List<MateDataStockDto> GetDataStockList(string skuNo, string skuName, string ownerNo, string ownerName, string lotNo)
        {
            string str = "select stock.SkuNo,stock.SkuName,stock.LotNo,stock.LotText,stock.Standard,stock.Qty," +
                "stock.LockQty,stock.FrozenQty,stock.OwnerNo,stock.OwnerName,(mate.Weight * stock.Qty) WeightSum " +
                "from DataStock stock " +
                "left join SysMaterials mate on stock.SkuNo = mate.SkuNo " +
                "Where stock.IsDel = @isdel and mate.IsDel = 0 ";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and stock.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and stock.SkuName like @skuname";
            }
            //判断货主编码是否为空
            if (!string.IsNullOrEmpty(ownerNo))
            {
                str += " and stock.OwnerNo like @ownerNo";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(ownerName))
            {
                str += " and stock.OwnerName like @ownerName";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and stock.LotNo like @lotNo";
            }
            //排序
            str += " order by stock.SkuNo";
            List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料编码
                skuname = "%" + skuName + "%", //物料名称
                ownerNo= "%" + ownerNo + "%", //货主编码
                ownerName= "%" + ownerName + "%", //货主名称
                lotNo= "%" + lotNo + "%", //批次号
            });

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

            foreach (var item in StockList)
            {
                StockListDto.Add(item);
                ////判断库存总量是否拥有物料
                //if (StockListDto.Count > 0)
                //{
                //    int i = 0;
                //    //foreach循环库存总量
                //    foreach (var dto in StockListDto)
                //    {
                //        //判断物料和批次是否相同
                //        if (dto.SkuNo == item.SkuNo && dto.LotNo == item.LotNo)
                //        {
                //            dto.Qty =  (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString();
                //            dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString();
                //            dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString();
                //            dto.ResidueQty= (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString();
                //            break;
                //        }
                //        i += 1;
                //        //判断已有相同物料
                //        if (i == StockListDto.Count)
                //        {
                //            StockListDto.Add(item);
                //            break;
                //        }
                //    }
                //}
                //else
                //{
                //    StockListDto.Add(item);
                //    continue;
                //}
            }

            return StockListDto;
        }

        /// <summary>
        /// 获取库存明细
        /// </summary>
        /// <param name="skuNo">物料编码</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="locatNo">储位地址</param>
        /// <param name="palletNo">托盘条码</param>
        /// <param name="status">库存状态</param>
        /// <param name="inspectStatus">质检状态</param>
        /// <param name="houseNo">所属仓库</param>
        /// <param name="areaNo">所属区域</param>
        /// <param name="type">物料类型</param>
        /// <param name="ownerNo">货主编号</param>
        /// <param name="ownerName">货主名称</param>
        /// <returns></returns>
        public List<StockDetailDto> GetInventoryList1(string skuNo, string skuName, string lotNo, string locatNo, string palletNo, 
            string status, string inspectStatus, string ownerNo, string ownerName, string houseNo, string areaNo, string categoryNo, string type)
        {
            string str = "select detail.*,sku.Type,house.WareHouseNo + '-' + house.WareHouseName as WareHouseName," +
                "roadway.RoadwayNo + '-' + roadway.RoadwayName as RoadwayName,area.AreaNo + '-' + area.AreaName as AreaName  " +
                "from DataStockDetail detail " +
                "left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo " +
                "left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo " +
                "left join SysStorageArea area on detail.AreaNo = area.AreaNo " +
                "left join SysMaterials as sku on detail.skuNo = sku.skuNo " +
                "left join SysMaterialCategory as cat on sku.CategoryNo = cat.CategoryNo " +
                "Where detail.IsDel = @isdel";
            //判断物料编码是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and detail.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and detail.SkuName like @skuname";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and detail.LotNo like @lotno";
            }            
            //判断储位地址是否为空
            if (!string.IsNullOrEmpty(locatNo))
            {
                str += " and detail.LocatNo like @locatno";
            }
            //判断托盘条码是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and detail.PalletNo like @palletno";
            }
            //判断库存状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and detail.Status = @status";
            }
            //判断质检状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and detail.InspectStatus = @inspectstatus";
            }
            //判断货主编码是否为空
            if (!string.IsNullOrEmpty(ownerNo))
            {
                str += " and detail.OwnerNo like @ownerNo";
            }
            //判断货主名称是否为空
            if (!string.IsNullOrEmpty(ownerName))
            {
                str += " and detail.OwnerName like @ownerName";
            }   
            if (!string.IsNullOrEmpty(houseNo))     
            {
                str += " and house.WareHouseNo = @wareHouseNo";
            }
            if (!string.IsNullOrEmpty(areaNo))
            {
                str += " and area.AreaNo = @areaNo";
            }
            if (!string.IsNullOrEmpty(categoryNo))
            {
                str += " and cat.CategoryNo = @categoryNo";
            }
            if (!string.IsNullOrEmpty(type))
            {
                str += " and sku.Type = @type";
            }

            //排序
            str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
            List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
            {
                isdel = "0",                                //是否删除
                skuno = "%" + skuNo + "%",                  //物料编码
                skuname = "%" + skuName + "%",              //物料名称
                lotno = "%" + lotNo + "%",                  //批次
                locatno = "%" + locatNo + "%",              //储位地址
                palletno = "%" + palletNo + "%",            //托盘条码
                status = status,                            //库存状态
                inspectstatus = inspectStatus,              //质检状态
                ownerNo = "%" + ownerNo + "%",              //货主编码
                ownerName = "%" + ownerName + "%",          //货主名称
                wareHouseNo = houseNo,                      
                areaNo = areaNo,                         //所属区域
                categoryNo = categoryNo,                //逻辑库区
                type = type
            });
            return stockDetailsList;
        }

        #endregion

        #region 低库存预警

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

        #endregion

        #region 出入库报表

        /// <summary>
        /// 获取出入库总量
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="lotNo">批次</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo, string startTime, string endTime)
        {
            //入库
            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";
            }
            // liudl 时间不准确 暂时注释。
            //if (!string.IsNullOrWhiteSpace(startTime))
            //{
            //    rstr += " and UpdateTime >= @startTime";
            //    cstr += " and UpdateTime >= @startTime";
            //}
            //if (!string.IsNullOrWhiteSpace(endTime))
            //{
            //    rstr += " and UpdateTime <= @endTime";
            //    cstr += " and UpdateTime <= @endTime";
            //}

            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 + "%", //批次号
                //startTime = startTime,
                //endTime = endTime

            });
            List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                //startTime = startTime,
                //endTime = endTime
            });

            //合并数据
            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>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo, 
            string startTime, string endTime)
        {
            //入库
            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.UpdateTime 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";
            }
            if (!string.IsNullOrWhiteSpace(startTime)) 
            {
                rstr += " and bind.CompleteTime >= @startTime";
                cstr += " and allot.UpdateTime >= @startTime";
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                rstr += " and bind.CompleteTime <= @endTime";
                cstr += " and allot.UpdateTime <= @endTime";
            }

            //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 + "%", //托盘号
                startTime = startTime,
                endTime = endTime
            });
            //出库
            List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                lotno = "%" + lotNo + "%", //批次号
                palletno = "%" + palletNo + "%", //托盘号
                startTime = startTime,
                endTime = endTime
            });

            //合并
            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 年度报表

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

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

                    }

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

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

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

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

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

                    //循环12个月
                    for (int i = 1; i <= 12; i++)
                    {
                        //临时入
                        List<AssSoDateStockInfoDto> linshiru = new List<AssSoDateStockInfoDto>();
                        //临时出
                        List<AssSoDateStockInfoDto> linshichu = new List<AssSoDateStockInfoDto>();
                        
                        if (ruinfolist.Count > 0)
                        {
                            linshiru = ruinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月入库信息
                        }
                        if (chuinfolist.Count > 0)
                        {
                            linshichu = chuinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月出库信息
                        }
                        if (linshichu.Count > 0)
                        {
                            foreach (var h in linshichu)
                            {
                                linshiru.Add(h);
                            }
                        }
                        var data = linshiru.OrderBy(a => a.CreateTime).ToList();

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

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

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

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

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

                                Weight = weight, // 理论重量

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

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

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

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

                            Weight = weight, // 理论重量

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

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

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }


        #endregion

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

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

            foreach (var item in StockList)
            {
                //判断库存总量是否拥有物料
                if (StockListDto.Count > 0)
                {
                    int i = 0;
                    //foreach循环库存总量
                    foreach (var dto in StockListDto)
                    {
                        //判断物料是否相同
                        if (dto.SkuNo == item.SkuNo)
                        {
                            var s = Convert.ToDecimal(dto.Qty) ;
                            var ss = Convert.ToDecimal(item.Qty);
                            dto.Qty = (Convert.ToDecimal(dto.Qty) + Convert.ToDecimal(item.Qty)).ToString();
                            dto.FrozenQty = (Convert.ToDecimal(dto.FrozenQty) + Convert.ToDecimal(item.FrozenQty)).ToString();
                            dto.LockQty = (Convert.ToDecimal(dto.LockQty) + Convert.ToDecimal(item.LockQty)).ToString();
                            dto.ResidueQty = (Convert.ToDecimal(dto.ResidueQty) + Convert.ToDecimal(item.ResidueQty)).ToString();
                            break;
                        }
                        i += 1;
                        //判断已有相同物料
                        if (i == StockListDto.Count)
                        {
                            StockListDto.Add(item);
                            break;
                        }
                    }
                }
                else
                {
                    StockListDto.Add(item);
                    continue;
                }
            }
            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 AGV小车任务完成更新库存

        /// <summary>
        /// AGV入库完成
        /// </summary>
        /// <param name="TaskCode">任务号</param>
        /// <param name="wcsurl">下发WCS放货完成地址</param>
        /// <returns></returns>
        public void ArriveFinish(string TaskCode,string wcsurl)
        {
            try
            {
                var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "0" && m.Status == "1");//获取正在执行的任务信息
                if (palletNo == null)
                {
                    throw new Exception("此任务已完成");

                }
                var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
                var stockDetail = Db.Queryable<DataStockDetail>().First(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo);
                //开启事务
                Db.BeginTran();
               
                //修改起始库位状态
                storageLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                Db.Updateable(storageLocat).ExecuteCommand();

                //修改任务状态
                palletNo.Status = "2";   //已完成
                Db.Updateable(palletNo).ExecuteCommand();

                
                if (stockDetail !=null)
                {
                    //修改库存明细
                    stockDetail.Status = "0";//待分配
                    stockDetail.LocatNo = "";
                    stockDetail.AreaNo = "";
                    stockDetail.RoadwayNo = "";
                    stockDetail.WareHouseNo = "";
                    Db.Updateable(stockDetail).ExecuteCommand();
                }
                

                #region 下发WCS放货完成
                var data = new
                {
                    Port = palletNo.EndLocat
                };
                var jsonData = JsonConvert.SerializeObject(data);
                string response = "";
                try
                {
                    var time1 = DateTime.Now;//发送时间 .ToString("yyyy-MM-dd HH:mm:ss")
                    response = HttpHelper.DoPost(wcsurl, jsonData, "下发给WCS放货完成命令", "WCS");
                    var time2 = DateTime.Now;//返回时间 .ToString("yyyy-MM-dd HH:mm:ss")

                    //////解析返回数据 
                    var res = JsonConvert.DeserializeObject<WcsModel>(response);
                    if (res.StatusCode == -1)
                    {
                        throw new Exception("放货失败，WCS返回信息错误");
                    }
                }

                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }

                #endregion


                Db.CommitTran();

            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw new Exception("AGV返回入库完成信号处理错误，错误信息：" + ex);
            }
        }

        /// <summary>
        /// AGV出库完成
        /// </summary>
        /// <param name="TaskCode">任务号</param>
        /// <param name="url">反馈MES备料完成地址</param>
        /// <returns></returns>
        public void SoFinish(string TaskCode,string url)
        {
            try
            {
                var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "1" && m.Status == "1");//获取正在执行的任务信息
                if (palletNo == null)
                {
                    throw new Exception("此任务已完成");
                }
                var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取库位信息
                var storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
                var stockDetail = Db.Queryable<DataStockDetail>().First(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo); //获取库存信息

                //开启事务
                Db.BeginTran();
                //修改目的库位状态
                storageLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                Db.Updateable(storageLocat).ExecuteCommand();

                //修改任务状态
                palletNo.Status = "2";   //已完成
                Db.Updateable(palletNo).ExecuteCommand();

                //修改库存明细
                stockDetail.Status = "2";// 0:待分配   1:部分分配 2：已分配 3：盘点锁定： 4移库锁定
                stockDetail.LocatNo = storageLocat.LocatNo;
                stockDetail.AreaNo = storageLocat.AreaNo;
                stockDetail.RoadwayNo = storageLocat.RoadwayNo;
                stockDetail.WareHouseNo = storageLocat.WareHouseNo;
                Db.Updateable(stockDetail).ExecuteCommand();


                Db.CommitTran();

            }
            catch (Exception ex )
            {

                throw new Exception("AGV返回出库完成信号处理错误，错误信息：" + ex);
            }
        }


        //完成接口修改（未完成）
        //try
        //{
        //    var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.Status == "1");//获取正在执行的任务信息
        //    if (palletNo == null)
        //    {
        //        throw new Exception("此任务已完成");
        //    }
        //    var storageLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取库位信息
        //    var storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取库位信息
        //    var stockDetail = Db.Queryable<DataStockDetail>().Where(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo).ToList();
        //    if (stockDetail.Count == 0)
        //    {
        //        throw new Exception("托盘上物料库存明细信息不存在,请检查!");
        //    }
        //    //开启事务
        //    Db.BeginTran();
        //    if (storageLocat != null)
        //    {
        //        if (storageLocat.AreaNo == "B12")//车间缓存位需要通知MES 
        //        {
        //            var data = new List<RequertBeiliaoInfoModel>();
        //            //更改库存明细
        //            foreach (var item in stockDetail)
        //            {
        //                item.LocatNo = item.LocatNo;//储位更改
        //                item.WareHouseNo = item.WareHouseNo;//所属仓库更改
        //                item.RoadwayNo = item.RoadwayNo;//所属巷道更改
        //                item.AreaNo = item.AreaNo;//所属区域更改 

        //                Db.Updateable(item).ExecuteCommand();


        //                if (string.IsNullOrWhiteSpace(item.SONo))
        //                {
        //                    throw new Exception("当前托盘不是拼托出库托盘");
        //                }
        //                var boxInfo = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).ToList();
        //                if (boxInfo.Count == 0)
        //                {
        //                    throw new Exception("托盘上物料箱码信息不存在,请检查!");
        //                }
        //                //var boxno = boxInfo.GroupBy(w => w.BoxNo).ToList();
        //                var boxno = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).GroupBy(m => new
        //                {
        //                    m.BoxNo
        //                }).Select(it => new DataBoxInfo()
        //                {
        //                    BoxNo = it.BoxNo
        //                }).ToList();

        //                //记录托盘上信息给MES
        //                foreach (var item2 in boxno)
        //                {
        //                    var a = item.ProductionTime.ToString();
        //                    data.Add(new RequertBeiliaoInfoModel()
        //                    {
        //                        no = item2.BoxNo,
        //                        materiel_no = item.SkuNo,
        //                        materiel_name = item.SkuName,
        //                        qty = item.Qty,
        //                        batch = item.LotNo,
        //                        producttime = item.ProductionTime.ToString().Substring(0, 10),
        //                        expiry = item.ExpirationTime.ToString().Substring(0, 10)

        //                    });
        //                }
        //                //库存箱码明细删除
        //                Db.Deleteable(boxInfo).ExecuteCommand();
        //                //删除库存托盘信息
        //                Db.Deleteable(item).ExecuteCommand();
        //                //更改库存总表
        //                var stock = Db.Queryable<DataStock>().First(w => w.IsDel == "0" && w.SkuNo == item.SkuNo && w.LotNo == item.LotNo);
        //                stock.LockQty -= (decimal)item.Qty;
        //                stock.Qty -= (decimal)item.Qty;
        //                Db.Updateable(stock).ExecuteCommand();
        //                //更改托盘状态
        //                var pallet = Db.Queryable<SysPallets>().First(m => m.IsDel == "0" && m.PalletNo == palletNo);
        //                if (pallet != null)
        //                {
        //                    pallet.Status = "0";
        //                    Db.Updateable(pallet).ExecuteCommand();
        //                }

        //            }

        //            //获取令牌
        //            //var token = new Token().GetMesToken(mesTokenUrl);
        //            var token = "";
        //            Dictionary<string, string> mesDic = new Dictionary<string, string>()
        //        {
        //        {"Authorization",token }
        //        };
        //            var endlono = palletNo.EndLocat;
        //            //缓存库位转换
        //            switch (palletNo.EndLocat.ToString().Substring(4, 1))
        //            {
        //                case "1":
        //                    endlono = "Y003_00" + palletNo.EndLocat.ToString().Substring(6, 1);
        //                    break;
        //                case "2":
        //                    endlono = "Y138_0" + palletNo.EndLocat.ToString().Substring(5, 2);
        //                    break;
        //                case "3":
        //                    endlono = "Y128_0" + palletNo.EndLocat.ToString().Substring(5, 2);
        //                    break;
        //                default:
        //                    break;
        //            }
        //            var mescode = Db.Queryable<BllExportNotice>().Where(w => w.SONo == stockDetail.First().SONo).First();
        //            var mesData = new RequertBeiliaoModel()
        //            {
        //                morder_no = mescode.OrderCode,
        //                pallet = stockDetail.First().PalletNo,
        //                layer_no = endlono,
        //                items = data
        //            };
        //            var jsonData = JsonConvert.SerializeObject(mesData);
        //            //调用接口
        //            var response = HttpHelper.DoPost(url, jsonData, "备料完成运至缓存区反馈至MES", "MES", mesDic);

        //            var obj = JsonConvert.DeserializeObject<MesModel>(response);//解析返回数据
        //            if (obj.status != "success")
        //            {
        //                throw new Exception("备料同步MES失败：" + obj.message);
        //            }


        //        }
        //        //修改目的库位状态
        //        storageLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
        //        Db.Updateable(storageLocat).ExecuteCommand();

        //        //修改任务状态
        //        palletNo.Status = "2";   //已完成
        //        Db.Updateable(palletNo).ExecuteCommand();

        //        //修改库存明细
        //        item.Status = "2";// 0:待分配   1:部分分配 2：已分配 3：盘点锁定： 4移库锁定
        //        stockDetail.LocatNo = storageLocat.LocatNo;
        //        stockDetail.AreaNo = storageLocat.AreaNo;
        //        stockDetail.RoadwayNo = storageLocat.RoadwayNo;
        //        stockDetail.WareHouseNo = storageLocat.WareHouseNo;
        //        Db.Updateable(stockDetail).ExecuteCommand();
        //    }
        //    Db.CommitTran();

        //}
        /// <summary>
        /// AGV移库完成
        /// </summary>
        /// <param name="TaskCode">任务号</param>
        /// <param name="url">反馈MES备料完成地址</param>
        /// <returns></returns>
        public void MoveFinish(string TaskCode,string url)
        {
            try
            {
                var palletNo = Db.Queryable<LogTask>().First(m => m.TaskNo == TaskCode && m.OrderType == "3" && m.Status == "1");//获取正在执行的任务信息
                if (palletNo == null)
                {
                    throw new Exception("此任务已完成");
                }
                var startLocat = new SysStorageLocat();

                var endLocat = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat); //获取目的库位信息
                if (endLocat == null)
                {
                    throw new Exception("未找到相应的目的库位");
                }
                var storageStart = new SysStorageLocat();
                
                int isstock = 1;
                var bindDetail = new BllPalletBind();
                var sysPanlno = new SysPallets();

                var stockDetail = Db.Queryable<DataStockDetail>().Where(w => w.IsDel == "0" && w.PalletNo == palletNo.PalletNo).ToList(); //获取库存信息
                if (stockDetail.Count == 0)
                {
                    //未找到库存后需要判断是否是空托盘移库
                    isstock = 0;
                    bindDetail = Db.Queryable<BllPalletBind>().OrderByDescending(w=>w.Id).First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat);//获取组托盘信息
                    if (bindDetail == null)
                    {
                        bindDetail = Db.Queryable<BllPalletBind>().OrderByDescending(w => w.Id).First(w => w.IsDel == "0" && w.LocatNo == palletNo.EndLocat);//获取组托盘信息
                        if (bindDetail == null)
                        {
                            throw new Exception("未找到空托盘组托信息");
                        }
                    }
                }
                else
                {
                    storageStart = Db.Queryable<SysStorageLocat>().First(w => w.IsDel == "0" && w.LocatNo == palletNo.StartLocat); //获取起始库位信息
                    if (storageStart == null)
                    {
                        throw new Exception("未找到相应的起始库位");
                    }
                }
                //var soAllot = Db.Queryable<BllExportAllot>().First(m => m.IsDel == "0" && m.PalletNo == palletNo.PalletNo);//获取分配信息
                
                
                //开启事务
                Db.BeginTran();

                //修改目的库位状态
                endLocat.Status = "1";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                Db.Updateable(endLocat).ExecuteCommand();

                //修改任务状态
                palletNo.Status = "2";   //已完成
                Db.Updateable(palletNo).ExecuteCommand();

                if (isstock == 1)
                {
                    startLocat = Db.Queryable<SysStorageLocat>().First(w=> w.IsDel == "0" && w.LocatNo == palletNo.StartLocat);
                    //修改起始库位状态
                    startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                    Db.Updateable(startLocat).ExecuteCommand();

                    //调用MES备料反馈完成接口
                    #region  MES备料完成反馈
                    var data = new List<RequertBeiliaoInfoModel>();

                    //更改库存明细
                    foreach (var item in stockDetail)
                    {
                        item.LocatNo = endLocat.LocatNo;//储位更改
                        item.WareHouseNo = endLocat.WareHouseNo;//所属仓库更改
                        item.RoadwayNo = endLocat.RoadwayNo;//所属巷道更改
                        item.AreaNo = endLocat.AreaNo;//所属区域更改 

                        Db.Updateable(item).ExecuteCommand();

                        if (endLocat != null && endLocat.AreaNo == "B12") //是否是3楼缓存区 是：删除库存
                        {
                            if (string.IsNullOrWhiteSpace(item.SONo))
                            {
                                throw new Exception("当前托盘不是拼托出库托盘");
                            }
                            var boxInfo = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).ToList();
                            if (boxInfo.Count == 0)
                            {
                                throw new Exception("托盘上物料箱码信息不存在,请检查!");
                            }
                            //var boxno = boxInfo.GroupBy(w => w.BoxNo).ToList();
                            var boxno = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.StockDetailId == item.Id).GroupBy(m => new
                            {
                                m.BoxNo,
                                m.ProductionTime,
                                m.ExpirationTime,
                            }).Select(it => new DataBoxInfo()
                            {
                                BoxNo = it.BoxNo,
                                ProductionTime = it.ProductionTime,
                                ExpirationTime = it.ExpirationTime,
                            }).ToList();

                            //记录托盘上信息给MES
                            foreach (var item2 in boxno)
                            {
                                //var a = item.ProductionTime.ToString();
                                data.Add(new RequertBeiliaoInfoModel()
                                {
                                    no = item2.BoxNo,
                                    materiel_no = item.SkuNo,
                                    materiel_name = item.SkuName,
                                    qty = item.Qty,
                                    batch = item.LotNo,
                                    producttime = item2.ProductionTime.ToString().Substring(0, 10),
                                    expiry = item2.ExpirationTime.ToString().Substring(0, 10)

                                });
                            }
                            //库存箱码明细删除
                            Db.Deleteable(boxInfo).ExecuteCommand();
                            //删除库存托盘信息
                            Db.Deleteable(item).ExecuteCommand();
                            //更改库存总表
                            var stock = Db.Queryable<DataStock>().First(w => w.IsDel == "0" && w.SkuNo == item.SkuNo && w.LotNo == item.LotNo);
                            stock.LockQty -= (decimal)item.Qty;
                            stock.Qty -= (decimal)item.Qty;
                            Db.Updateable(stock).ExecuteCommand();
                            //更改托盘状态
                            var pallet = Db.Queryable<SysPallets>().First(m => m.IsDel == "0" && m.PalletNo == palletNo.PalletNo);
                            if (pallet != null)
                            {
                                pallet.Status = "0";
                                Db.Updateable(pallet).ExecuteCommand();
                            }
                        }
                    }


                    if (endLocat != null && endLocat.AreaNo == "B12")
                    {
                        //获取令牌
                        //var token = new Token().GetMesToken(mesTokenUrl);
                        var token = "";
                        Dictionary<string, string> mesDic = new Dictionary<string, string>()
                        {
                            {"Authorization",token }
                        };
                        var endlono = endLocat.LocatNo;
                        //缓存库位转换
                        switch (endLocat.LocatNo.ToString().Substring(4, 1))
                        {
                            case "1":
                                endlono = "Y003_0" + endLocat.LocatNo.ToString().Substring(5, 2);
                                break;
                            
                        }
                        var mescode = Db.Queryable<BllExportNotice>().Where(w => w.SONo == stockDetail.First().SONo).First();
                        var mesData = new RequertBeiliaoModel()
                        {
                            morder_no = mescode.OrderCode,
                            pallet = stockDetail.First().PalletNo,
                            layer_no = endlono,
                            items = data
                        };
                        var jsonData = JsonConvert.SerializeObject(mesData);
                        //调用接口
                        var response = HttpHelper.DoPost(url, jsonData, "备料完成运至缓存区反馈至MES", "MES", mesDic);

                        var obj = JsonConvert.DeserializeObject<MesModel>(response);//解析返回数据
                        if (obj.status != "success")
                        {
                            throw new Exception("备料同步MES失败：" + obj.message);
                        }

                    }
                    #endregion
                }
                else  //空托盘处理
                {
                    //修改组托信息
                    if (bindDetail.WareHouseNo == "W01") //1、空托盘垛到空托盘收集器；
                    {
                        bindDetail.WareHouseNo = "W02";
                        bindDetail.LocatNo = endLocat.LocatNo;
                        bindDetail.RoadwayNo = "";
                        //修改起始库位状态
                        startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                        Db.Updateable(startLocat).ExecuteCommand();
                    }
                    else//2、拣货叫空托盘，小车完成一次，扣减一个空托盘垛上数量
                    {
                        bindDetail.Qty = bindDetail.Qty - 1;
                        if (bindDetail.Qty == 0)//数量为0后更改组托状态和托盘使用状态
                        {
                            bindDetail.Status = "2";
                            bindDetail.IsDel = "1";
                            //修改起始库位状态
                            startLocat.Status = "0";//0：空储位 1：有物品 2：入库中 3：出库中 4：移入中 5：移出中 
                            Db.Updateable(startLocat).ExecuteCommand();
                        }
                        else
                        {
                            bindDetail.LocatNo = startLocat.LocatNo;//?????拣货位托盘如何处理，拣货位是否需要更改状态，是否需要增加库存；
                        }
                    }
                    Db.Updateable(bindDetail).ExecuteCommand();
                }
                Db.CommitTran();
            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw new Exception("AGV返回移库完成信号处理错误，错误信息："+ex);
            }
        }
        #endregion

        #region 数字孪生系统获取库存信息

        /// <summary>
        /// 反馈数字孪生系统库存信息
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public List<ReLocateDataModel> GetLocateList()
        {
            try
            {

                //库存信息
                var stockDetailsList = Db.Queryable<DataStockDetail>().ToList();
                var sql = "select LocatNo,PalletNo,SkuName,Standard,LotNo,InspectStatus,Qty,ExpirationTime as Warranty from DataStockDetail ";

                List<ReLocateDataModel> list = Db.Ado.SqlQuery<ReLocateDataModel>(sql);

                //if (stockDetailsList.Count == 0)
                //{
                //    return list;
                //}
                //foreach (var item in stockDetailsList)
                //{
                //    list.Add(item);

                //}
                //for (int i = 0; i < stockDetailsList.Count; i++)
                //{

                //    list.Add(stockDetailsList);
                //    list[i].LocatNo = stockDetailsList[i].LocatNo;                  //库位
                //    list[i].PalletNo = stockDetailsList[i].PalletNo;                //托盘号
                //    list[i].SkuName = stockDetailsList[i].SkuName;                  //物料名称
                //    list[i].Stadard = stockDetailsList[i].Standard;                  //规格
                //    list[i].LotNo = stockDetailsList[i].LotNo;                      //批次
                //    list[i].InspectStatus = stockDetailsList[i].InspectStatus;      //质量状态
                //    list[i].Qty = stockDetailsList[i].Qty.ToString();                           //库存数量
                //    list[i].Warranty = stockDetailsList[i].ExpirationTime.ToString();           //有效期   ??过期时间，是否要更改
                //}
                return list;
            }
            catch (Exception ex)
            {

                throw new Exception("返回库存信息有误，错误信息：" + ex);
            }
        }
        #endregion
    }
}