using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.X86;
using System.Security.Claims;
using System.Text;
using Model.ModelDto;
using Model.ModelDto.DataDto;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
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 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;
                }
            }

            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> GetInventoryList1(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 //质检状态
            });
            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";
            }
            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>
        /// <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.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 导出库存
        /// <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
    }
}