using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Model.ModelDto.BllSoDto;
using Model.ModelVm.BllSoVm;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.BllSoEntity;
using WMS.Entity.Context;
using WMS.Entity.SysEntity;
using WMS.IBLL.IBllSoServer;

namespace WMS.BLL.BllSoServer
{
    public class CompleteDetailServer : DbHelper<BllCompleteDetail>, ICompleteDetailServer
    {
        private static readonly SqlSugarScope Db = DataContext.Db;
        public CompleteDetailServer() : base(Db)
        {
        }

        //查询拣货明细箱码信息（分组后）
        public async Task<List<CompleteDetailDto>> GetCompleteDetailList(GetCompleteDetailVm model, RefAsync<int> count)
        {
            Expression<Func<BllCompleteDetail, bool>> item = Expressionable.Create<BllCompleteDetail>()
                .And(it => it.ExportAllotId == model.Id)
                .And(it => it.IsDel == "0")
                .ToExpression();//注意 这一句 不能少
            var data = await GetAllWhereAsync(item)
                .GroupBy(m => new
                {
                    m.SONo,
                    m.SODetailNo,
                    m.StockId,
                    m.ExportAllotId,

                    m.BoxNo,
                    m.LotNo,
                    m.LotText,
                    m.SupplierLot,
                    m.SkuNo,
                    m.SkuName,
                    m.Standard,
                    m.PalletNo,
                    m.NowPalletNo
                })
                .Select(a => new CompleteDetailDto()
                {
                    SONo = a.SONo,
                    SODetailNo = a.SODetailNo,
                    StockId = a.StockId,
                    ExportAllotId = a.ExportAllotId,

                    BoxNo = a.BoxNo,
                    LotNo = a.LotNo,
                    LotText = a.LotText,
                    SupplierLot = a.SupplierLot,
                    SkuNo = a.SkuNo,
                    SkuName = a.SkuName,
                    Standard = a.Standard,
                    PalletNo = a.PalletNo,
                    CompleteQty = SqlFunc.AggregateSum(a.CompleteQty),
                    NowPalletNo = a.NowPalletNo,
                }).ToPageListAsync(model.Page, model.Limit, count);

            return data;
        }

        //查询拣货明细支码详细信息
        public List<CompleteDetailDto> GetCompBoxInfoList(string boxNo, string boxNo3)
        {
            try
            {
                Expression<Func<BllCompleteDetail, bool>> item = Expressionable.Create<BllCompleteDetail>()
                    .AndIF(!string.IsNullOrWhiteSpace(boxNo), it => it.BoxNo == boxNo.Trim())
                    .AndIF(!string.IsNullOrWhiteSpace(boxNo3), it => it.BoxNo3.Contains(boxNo3.Trim()))
                    .ToExpression();
                var data = GetAllWhereAsync(item)
                    .LeftJoin<SysUserInfor>((a, b) => a.CreateUser == b.Id)
                    .LeftJoin<SysUserInfor>((a, b, c) => a.UpdateUser == c.Id)
                    .Select((a, b, c) => new CompleteDetailDto()
                    {
                        Id = a.Id,
                        SONo = a.SONo,
                        SODetailNo = a.SODetailNo,
                        StockId = a.StockId,
                        ExportAllotId = a.ExportAllotId,

                        BoxNo = a.BoxNo,
                        BoxNo3 = a.BoxNo3,
                        LotNo = a.LotNo,
                        LotText = a.LotText,
                        SupplierLot = a.SupplierLot,
                        SkuNo = a.SkuNo,
                        SkuName = a.SkuName,
                        Standard = a.Standard,
                        PalletNo = a.PalletNo,
                        CompleteQty = a.CompleteQty,
                        NowPalletNo = a.NowPalletNo,

                        CreateUserName = b.RealName,
                        UpdateUserName = c.RealName,
                        CreateTime = a.CreateTime,
                        UpdateTime = a.UpdateTime,
                    }).ToList();
                return data;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        #region 归档数据
        //查询拣货明细箱码信息（分组后）- 归档数据
        public List<CompleteDetailDto> GetArchivingCompleteDetailList(int id, int page, int limit, out int count)
        {
            try
            {
                var detailInfo = Db.Queryable<BllCompleteDetail>().First(w => string.IsNullOrEmpty(w.BoxNo) || string.IsNullOrEmpty(w.BoxNo3));
                if (detailInfo == null)
                {
                    string sqlString = string.Empty;
                    string sqlCount = string.Empty;
                    string sqlPub = string.Empty;

                    sqlCount += "SELECT COUNT(SONo) FROM ArchivingCompleteDetail ";
                    sqlString += "SELECT SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,sum(CompleteQty) as CompleteQty," +
                        "SupplierLot,SkuNo,SkuName,Standard,PalletNo,NowPalletNo ";
                    sqlString += " FROM ArchivingCompleteDetail  ";


                    sqlPub += $"WHERE ExportAllotId = '{id}' and isDel = '0' ";
                    sqlPub += $" group by SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,SupplierLot,SkuNo,SkuName,Standard," +
                        $"PalletNo,NowPalletNo ";

                    sqlCount += sqlPub;

                    if (page == 0)
                    {
                        page = 1;
                    }

                    sqlString += sqlPub + $" order by 1  offset {((page - 1) * limit)} rows fetch next {limit} rows only;";

                    var com = new Common();
                    count = com.GetRowCount(sqlCount);

                    var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
                    return modelList;
                }
                else
                {
                    string sqlString = string.Empty;
                    string sqlCount = string.Empty;
                    string sqlPub = string.Empty;

                    sqlCount += "SELECT COUNT(SONo) FROM ArchivingCompleteDetail ";
                    sqlString += "SELECT SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,sum(CompleteQty) as CompleteQty," +
                        "SupplierLot,SkuNo,SkuName,Standard,PalletNo,NowPalletNo ";
                    sqlString += " FROM ArchivingCompleteDetail  ";

                    sqlPub += $"WHERE ExportAllotId = '{id}' and isDel = '0' ";
                    sqlPub += $" group by SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,SupplierLot,SkuNo,SkuName,Standard," +
                        $"PalletNo,NowPalletNo,CreateTime ";

                    sqlCount += sqlPub;

                    if (page == 0)
                    {
                        page = 1;
                    }
                    sqlString += sqlPub + $" order by 1 offset {((page - 1) * limit)} rows fetch next {limit} rows only;";

                    var com = new Common();
                    count = com.GetRowCount(sqlCount);

                    var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
                    return modelList;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        //查询拣货明细支码详细信息-数据归档
        public List<CompleteDetailDto> GetArchivingCompBoxInfoList(string boxNo, string boxNo3)
        {
            try
            {
                string sqlString = string.Empty;
                string sqlPub = string.Empty;

                sqlString += "SELECT tb1.Id,tb1.SONo,tb1.SODetailNo,tb1.StockId,tb1.ExportAllotId,tb1.BoxNo,tb1.BoxNo3" +
                    ",tb1.LotNo,tb1.LotText,tb1.CompleteQty,tb1.SupplierLot,tb1.SkuNo,tb1.SkuName,tb1.Standard,tb1.PalletNo,tb1.NowPalletNo, ";
                sqlString += "tb3.RealName as CreateUserName,tb4.RealName as UpdateUserName,tb1.CreateTime,tb1.UpdateTime ";
                sqlString += " FROM ArchivingCompleteDetail  AS tb1 ";
                sqlString += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
                sqlString += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";


                sqlString += $"WHERE tb1.isDel = '0' ";

                if (!string.IsNullOrWhiteSpace(boxNo))
                {
                    sqlString += $"AND tb1.BoxNo = '{boxNo.Trim()}' ";
                }
                if (!string.IsNullOrWhiteSpace(boxNo3))
                {
                    sqlString += $"AND tb1.BoxNo3 like  '%{boxNo3.Trim()}%' ";
                }

                var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
                return modelList;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion
    }
}