using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Text;
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 DataBoxInfoServer: DbHelper<DataBoxInfo>,IDataBoxInfoServer
    {
        private static readonly SqlSugarScope Db = DataContext.Db;
        public DataBoxInfoServer():base(Db)
        {
        }

        #region 托盘明细

        /// <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> GetDataBoxInfo(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 //质量状态
            });

            List<DataBoxInfo> box = new List<DataBoxInfo>();
            foreach (var b1 in boxInfor)
            {
                //判断box是否为空
                if (box.Count <= 0)
                {
                    //无数据获取第一条
                    box.Add(b1);
                    continue;
                }
                int i = 0;
                foreach (var b2 in box)
                {
                    if (b2.BoxNo == b1.BoxNo)
                    {
                        b2.Qty += b1.Qty;
                        break;
                    }
                    i += 1;
                }
                if (i == box.Count)
                {
                    box.Add(b1);
                    continue;
                }
            }

            return box;
        }

        /// <summary>
        /// 根据箱码获取箱支信息
        /// </summary>
        /// <param name="boxNo">箱码</param>
        /// <param name="boxNo3">支码</param>
        /// <returns></returns>
        public List<DataBoxInfo> GetBoxInfoByBoxNo(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="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<AdventBoxInfoDto> GetEmergencyWarning(string skuNo, string skuName, string lotNo, string palletNo, string status, string inspectMark, string inspectStatus)
        {
            string str = @" select d.WareHouseName,d.WareHouseNo,e.RoadwayName,e.RoadwayNo,f.AreaName,f.AreaNo,c.LocatNo,a.PalletNo,
                             a.SkuNo,a.SkuName,SUM(a.Qty) as Qty,a.Standard,a.LotNo,a.SupplierLot,a.ProductionTime,a.Status
                             from DataBoxInfo a
                             left join SysMaterials b on a.SkuNo = b.SkuNo 
                             left join DataStockDetail c on a.StockDetailId = c.Id
                             left join SysWareHouse d on d.WareHouseNo = c.WareHouseNo
                             left join SysStorageRoadway e on e.RoadwayNo = c.RoadwayNo
                             left join SysStorageArea f on f.AreaNo = c.AreaNo
                             where a.ProductionTime+b.Warranty-b.AdventTime <= GETDATE() ";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and a.SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and a.SkuName like @skuname";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and a.LotNo like @lotno";
            }
            //判断托盘号是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and a.PalletNo like @palletno";
            }
            //判断状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                str += " and a.Status = @status";
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                str += " and a.InspectMark = @inspectmark";
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                str += " and a.InspectStatus = @inspectstatus";
            }
            //排序
            str += @" group by d.WareHouseName,d.WareHouseNo,e.RoadwayName,e.RoadwayNo,f.AreaName,f.AreaNo,c.LocatNo,a.PalletNo,a.SkuNo,a.SkuName,a.Standard,a.LotNo,a.SupplierLot,a.ProductionTime,a.Status order by a.SkuNo,a.LotNo ";
            List<AdventBoxInfoDto> stockList = Db.Ado.SqlQuery<AdventBoxInfoDto>(str, new
            {
                skuno = skuNo, //物料号
                skuname = skuName, //物料名称
                lotno = lotNo, //批次
                palletno = palletNo, //托盘号
                status, //状态
                inspectmark = inspectMark, //检验标记
                inspectstatus = inspectStatus, //质量状态
            });
            return stockList;
        }

        #endregion

        #region 导出箱码明细
        /// <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> GetDataBoxInfoDaoChu(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 //质量状态
            });

            List<DataBoxInfo> box = new List<DataBoxInfo>();
            foreach (var b1 in boxInfor)
            {
                //判断box是否为空
                if (box.Count <= 0)
                {
                    //无数据获取第一条
                    box.Add(b1);
                    continue;
                }
                int i = 0;
                foreach (var b2 in box)
                {
                    if (b2.BoxNo == b1.BoxNo)
                    {
                        b2.Qty += b1.Qty;
                        break;
                    }
                    i += 1;
                }
                if (i == box.Count)
                {
                    box.Add(b1);
                    continue;
                }
            }

            foreach (var item in box)
            {
                //物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
                if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
                {
                    item.SkuNo = $"'{item.SkuNo}";
                }
                //箱码，加上单引号是防止导出到excel纯数字太长
                if (!string.IsNullOrEmpty(item.BoxNo))
                {
                    item.BoxNo = $"'{item.BoxNo}";
                }
                //抽检标记
                switch (item.InspectMark)
                {
                    case "0":
                        item.InspectMark = "否";
                        break;
                    case "1":
                        item.InspectMark = "是";
                        break;
                    default:
                        break;
                }
                //零箱标记
                switch (item.BitBoxMark)
                {
                    case "0":
                        item.BitBoxMark = "否";
                        break;
                    case "1":
                        item.BitBoxMark = "是";
                        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 box;
        }
        #endregion

        #region 废代码 仅供参考

        ////获取分组后的箱码明细
        //public List<DataBoxInfo> GetDataBoxInfoGourp(string id, string skuNo, string skuName, string palletNo, string lotNo, string boxNo,
        //    string status, string inspectMark, string bitBoxMark, string inspectStatus, int page, int limit, out int count)
        //{

        //    try
        //    {
        //        Expression<Func<DataBoxInfo, bool>> item = Expressionable.Create<DataBoxInfo>()
        //            .AndIF(!string.IsNullOrWhiteSpace(id), it => it.StockDetailId == int.Parse(id))
        //            .AndIF(!string.IsNullOrWhiteSpace(skuNo), it => it.SkuNo.Contains(skuNo.Trim()))
        //            .AndIF(!string.IsNullOrWhiteSpace(skuName), it => it.SkuName.Contains(skuName.Trim()))
        //            .AndIF(!string.IsNullOrWhiteSpace(palletNo), it => it.PalletNo.Contains(palletNo.Trim()))
        //            .AndIF(!string.IsNullOrWhiteSpace(lotNo), it => it.LotNo.Contains(lotNo.Trim()))
        //            .AndIF(!string.IsNullOrWhiteSpace(boxNo), it=>it.BoxNo.Contains(boxNo.Trim()))
        //            .AndIF(!string.IsNullOrWhiteSpace(status), it => it.Status == status)
        //            .AndIF(!string.IsNullOrWhiteSpace(inspectMark), it => it.InspectMark == inspectMark)
        //            .AndIF(!string.IsNullOrWhiteSpace(bitBoxMark), it => it.BitBoxMark == bitBoxMark)
        //            .AndIF(!string.IsNullOrWhiteSpace(inspectStatus), it => it.InspectStatus == inspectStatus)
        //            .ToExpression();//注意 这一句 不能少
        //        var total = 0;
        //        var list = GetAllWhereAsync(item).GroupBy(m=>new {m.PalletNo,m.BoxNo,m.SkuNo,m.SkuName,m.LotNo});//.ToOffsetPage(page, limit, ref total);
        //        var data = list.Select(m=>new
        //        {
        //            a=m.PalletNo,
        //            b=m.BoxNo,
        //            c=m.SkuNo,
        //            d=m.SkuName,
        //            e=m.LotNo,
        //            f=SqlFunc.AggregateSum(m.Qty),

        //        }).ToOffsetPage(page, limit, ref total);
        //        count = total;

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

        ////根据箱码获取箱支信息
        //public List<DataBoxInfo> GetBoxInfoByBoxNo(string boxNo, string boxNo3)
        //{
        //    try
        //    {
        //        Expression<Func<DataBoxInfo, bool>> item = Expressionable.Create<DataBoxInfo>()
        //            .AndIF(!string.IsNullOrWhiteSpace(boxNo), it => it.BoxNo == boxNo)
        //            .AndIF(!string.IsNullOrWhiteSpace(boxNo3), it => it.BoxNo3.Contains(boxNo3.Trim()))
        //            .ToExpression();//注意 这一句 不能少

        //        var data = GetAllWhereAsync(item).ToList();//.ToOffsetPage(page, limit, ref total);
        //        return data;
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message);
        //    }
        //}

        #endregion
    }
}