WEBUI/WMS.BZServices/FJ/StatisticsreportService.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WMS.BZModels;
using WMS.Info;
using wms.sqlsugar.model.fj;
using WMS.BZSqlSugar;
using WMS.BZModels.Dto.FJ.ReportDtos;
using WMS.BZModels.Dto;
using WMS.Util;
using NPOI.SS.Formula.Functions;
using System.Reflection;
using System.Collections;

namespace WMS.BZServices.FJ
{
    public class StatisticsreportService
    {
        private readonly Repository<BillInvflow> _flowrepository;
        private readonly Repository<WCS_TaskOld> _taskoldrepository;
        private readonly Repository<BillBomsetgrp> _billBomsetgrprepository;
        private readonly Repository<BillBomsetinfo> _billBomsetinforepository;
        private readonly Repository<BaseWarehouse> _baseWarehouseRepository;

        public StatisticsreportService(Repository<BillInvflow> flowrepository, Repository<WCS_TaskOld> taskoldrepository, Repository<BillBomsetgrp> billBomsetgrprepository, Repository<BillBomsetinfo> billBomsetinforepository, Repository<BaseWarehouse> baseWarehouseRepository)
        {
            _flowrepository = flowrepository;
            _taskoldrepository = taskoldrepository;
            _billBomsetgrprepository = billBomsetgrprepository;
            _billBomsetinforepository = billBomsetinforepository;
            _baseWarehouseRepository = baseWarehouseRepository;
        }

        public PagedInfo<StatisticsDto> GetPageList(Pagination pagination, StatisticsQueryDto statisticsQueryDto)
        {
            //if (pagination.sord.ToUpper() != "ASC")
            //{
            //    pagination.sidx = pagination.sidx + " DESC";
            //}
            //if (pagination.sidx.IsEmpty())
            //{
            //    pagination.sidx = "AddTime DESC";
            //}

            //var predicate = Expressionable.Create<BillInvflow, WCS_TaskOld>();

            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.KeyWord), (flow, taskold) => flow.MatCode.Contains(statisticsQueryDto.KeyWord) || taskold.Floor.ToString() == statisticsQueryDto.KeyWord
            //|| taskold.Tunnel.Contains(statisticsQueryDto.KeyWord) || taskold.Device.Contains(statisticsQueryDto.KeyWord) || taskold.AddrTo.Contains(statisticsQueryDto.KeyWord) || taskold.AddrFrom.Contains(statisticsQueryDto.KeyWord));
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.MatCode), (flow, taskold) => flow.MatCode.Contains(statisticsQueryDto.MatCode));
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Tunnel), (flow, taskold) => taskold.Tunnel.Contains(statisticsQueryDto.Tunnel));
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Floor), (flow, taskold) => taskold.Floor.ToString() == statisticsQueryDto.Floor);
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.Device), (flow, taskold) => taskold.Device.Contains(statisticsQueryDto.Device));
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.AddrFrom), (flow, taskold) => taskold.AddrFrom.Contains(statisticsQueryDto.AddrFrom));
            //predicate = predicate.AndIF(!string.IsNullOrEmpty(statisticsQueryDto?.AddrTo), (flow, taskold) => taskold.AddrTo.Contains(statisticsQueryDto.AddrTo));

            var list = GetQueryable(statisticsQueryDto).ToPage(pagination);
            return list;
        }

        public IList<StatisticsDto> GetList(StatisticsQueryDto statisticsQueryDto)
        {
            ISugarQueryable<StatisticsDto> sugarQueryable = GetQueryable(statisticsQueryDto);
            var list = sugarQueryable.ToList();
            return list;
        }

        private ISugarQueryable<StatisticsDto> GetQueryable(StatisticsQueryDto statisticsQueryDto)
        {
            var sugarQueryable = _taskoldrepository.Context.Queryable<WCS_TaskOld>().With(SqlWith.NoLock).SplitTable(p => p.Take(10))
                .LeftJoin<BaseMatinfo>((taskold, mater) => taskold.MaterialCode == mater.Code)

                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.Status), (taskold, mater) => taskold.Status.Equals(statisticsQueryDto.Status))
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.KeyWord) || mater.Name.Contains(statisticsQueryDto.KeyWord))
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.MatCode))
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (taskold, mater) => mater.Name.Contains(statisticsQueryDto.MatName))
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (taskold, mater) => taskold.AddTime >= statisticsQueryDto.AddTimeFrom)
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeTo != null && statisticsQueryDto.AddTimeTo.HasValue, (taskold, mater) => taskold.AddTime <= statisticsQueryDto.AddTimeTo)
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeBegin.HasValue, (taskold, mater) => taskold.StartTime >= statisticsQueryDto.StartTimeBegin)
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeEnd.HasValue, (taskold, mater) => taskold.StartTime <= statisticsQueryDto.StartTimeEnd)
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeBegin.HasValue, (taskold, mater) => taskold.EndTime >= statisticsQueryDto.EndTimeBegin)
                .WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeEnd.HasValue, (taskold, mater) => taskold.EndTime <= statisticsQueryDto.EndTimeEnd)
                .GroupBy((taskold, mater) => new { mater.Code, mater.Name, taskold.Floor, taskold.Type })
                .Select((taskold, mater) => new StatisticsDto
                {
                    MatCode = mater.Code,
                    MatName = mater.Name,
                    Floor = taskold.Floor,
                    Type = taskold.Type,
                    Count = SqlFunc.AggregateDistinctCount(taskold.ID)
                }).MergeTable();
            return sugarQueryable;
        }

        /// <summary>
        /// 跺型当前来料比
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="statisticsQueryDto"></param>
        /// <returns></returns>
        public PagedInfo<BomSetGrpCurrentMaterialRateDto> GetBomSetGrpCurrentMaterialRateList(Pagination pagination, BomSetGrpCurrentMaterialRateQueryDto statisticsQueryDto)
        {
            if (string.IsNullOrEmpty(pagination.sidx))
            {
                pagination.sidx = "SetGrpBomCode";
            }
            var bomsetquery = _billBomsetgrprepository.Context.Queryable<BillBomsetgrp, BillBomsetinfo>((billBomsetgrp, billBomsetinfo) => new object[] {
                JoinType.Left, billBomsetgrp.Id == billBomsetinfo.BomSetHdrId,
               }).With(SqlWith.NoLock)
                  //.Where((billBomsetgrp, billBomsetinfo) => billBomsetgrp.IsStop == 0 && billBomsetgrp.IsDelete == 0)
                  .GroupBy((billBomsetgrp, billBomsetinfo) => new { billBomsetgrp.ProCode, billBomsetgrp.Name, billBomsetinfo.MatCode })
                 .Select((billBomsetgrp, billBomsetinfo) => new
                 {
                     SetGrpBomCode = billBomsetgrp.ProCode,
                     SetGrpMatCode = billBomsetinfo.MatCode,
                     SetGrpName = billBomsetgrp.Name,
                     SetGrpQty = SqlFunc.AggregateCount(billBomsetinfo.MatCode)

                 }).Distinct();

            var now = DateTime.Now;
            var tasksquery = _taskoldrepository.Context.Queryable<WCS_TaskOld>().With(SqlWith.NoLock).SplitTable(p => p.Take(2))

                  .Where(o => o.Status > 0 && o.Status <= 10)
                  .Where(o => o.EditTime >= now.AddHours(-3) && o.EditTime <= now)
                  //.WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord),o=>o.MatCode.Contains(statisticsQueryDto.KeyWord))
                  .GroupBy(taskold => taskold.MatCode)
                  .Select(taskold => new
                  {
                      MatCode = taskold.MatCode,
                      Qty = SqlFunc.AggregateCount(taskold.ID)
                  });//.ToPage<BomSetGrpCurrentMaterialRateDto>(pagination);
            var tasks = tasksquery.LeftJoin(bomsetquery, (taskold, bomset) => taskold.MatCode == bomset.SetGrpMatCode)
                 .Where((taskold, bomset) => !SqlFunc.IsNullOrEmpty(bomset.SetGrpBomCode))
                 .GroupBy((taskold, bomset) => new { bomset.SetGrpBomCode, taskold.MatCode })
                 .Select((taskold, bomset) =>
                 new BomSetGrpCurrentMaterialRateDto
                 {
                     BomCode = bomset.SetGrpBomCode,
                     MatCode = taskold.MatCode,
                     Qty = SqlFunc.AggregateCount(taskold.MatCode)
                 }).ToPage<BomSetGrpCurrentMaterialRateDto>(pagination);

            var BomSetGrps = bomsetquery.ToList();// _billBomsetgrprepository.Queryable().Where(o => o.IsStop == 0 && o.IsDelete == 0).ToList();
            foreach (var item in tasks.Result)
            {
                var bom = BomSetGrps.FirstOrDefault(o => o.SetGrpBomCode.Split('|').Any(v => v.Equals(item.MatCode)));
                if (bom != null)
                {
                    var boms = bom.SetGrpBomCode.Split('|');
                    if (boms.Length > 1)
                    {
                        var total = tasks.Result.Where(o => boms.Contains(o.MatCode)).Sum(o => o.Qty);
                        if (total == 0)
                        {
                            item.Rate = 0;// "0%";
                        }
                        else
                        {

                            item.Rate = (decimal)item.Qty / total;// rate.ToString("P2");
                        }
                    }
                    else
                    {
                        item.Rate = 1;// "100%";
                    }
                    item.BomSetName = bom.SetGrpName;
                    var bomtotalqty = BomSetGrps.Where(o => o.SetGrpBomCode == bom.SetGrpBomCode).Sum(o => o.SetGrpQty);
                    var matQty = BomSetGrps.Where(o => o.SetGrpMatCode == item.MatCode).Sum(o => o.SetGrpQty);
                    if (bomtotalqty == 0)
                    {
                        item.NormalRate = 0;
                    }
                    else
                    {
                        item.NormalRate = (decimal)matQty / bomtotalqty;
                    }
                }

            }
            var groups = tasks.Result.Select(o => o.BomSetName).Distinct().ToList();
            foreach (var item in groups)
            {
                foreach (var child in BomSetGrps.Where(o => o.SetGrpName == item && o.SetGrpQty > 0).ToList())
                {
                    var bommat = tasks.Result.FirstOrDefault(o => o.MatCode == child.SetGrpMatCode);
                    if (bommat == null)
                    {
                        var bomtotalqty = BomSetGrps.Where(o => o.SetGrpName == item).Sum(o => o.SetGrpQty);
                        var matQty = BomSetGrps.Where(o => o.SetGrpMatCode == child.SetGrpMatCode).Sum(o => o.SetGrpQty);
                        tasks.Result.Add(new BomSetGrpCurrentMaterialRateDto
                        {
                            BomSetName = child.SetGrpName,
                            MatCode = child.SetGrpMatCode,
                            Qty = 0,
                            Rate = 0,
                            NormalRate = bomtotalqty == 0 ? 0 : (decimal)matQty / bomtotalqty,
                        });
                    }
                }
            }
            foreach (var item in tasks.Result)
            {
                var totalqty = tasks.Result.Where(o => o.BomSetName == item.BomSetName).Sum(o => o.Qty);
                item.DiffQty = item.Qty - (int)Math.Floor((totalqty * item.NormalRate));
            }
            //foreach (var item in groups)
            //{
            //    var qty = tasks.Result.Where(o => o.BomSetName == item).Max(o => o.Qty);
            //    var rate = tasks.Result.Where(o => o.BomSetName == item).Max(o => o.NormalRate);
            //    foreach (var child in tasks.Result.Where(o => o.BomSetName == item).ToList())
            //    {
            //        if (child.Qty < qty)
            //        {
            //           int childrate = Convert.ToInt32((decimal)rate / child.NormalRate);
            //           child.DiffQty =Convert.ToInt32( Math.Ceiling( ((decimal)qty / childrate) - child.Qty));
            //        }
            //    }
            //}               

            return tasks;
        }


        /// <summary>
        /// 24小时内任务动态
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public PagedInfo<HourTaskDto> GetHourTask(Pagination pagination, HourTaskQueryDto hourTaskQueryDto)
        {
         
            if (!hourTaskQueryDto.EndTimeBegin.HasValue)
            {
                hourTaskQueryDto.EndTimeBegin = DateTime.Now.Date;
            }
            if (!hourTaskQueryDto.EndTimeEnd.HasValue)
            {
                hourTaskQueryDto.EndTimeEnd = DateTime.Now.AddHours(1);
            }
            if ((hourTaskQueryDto.EndTimeEnd.Value - hourTaskQueryDto.EndTimeBegin.Value).Days > 60)
            {
                throw BZSysExCore.ThrowFailException("查询日期范围不能超过60天!");
            }

            var predicate = Expressionable.Create<WCS_TaskOld>();
            predicate = predicate.And(m => m.Status == (int)BZModels.TaskStatus.Finish && m.Type != (int)TaskType.Delivery);
            predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.WarehouseCode), m => hourTaskQueryDto.WarehouseCode.Equals(m.WarehouseCode));
            if (hourTaskQueryDto.GroupName == "AddTime")
            {
                predicate = predicate.AndIF(hourTaskQueryDto.EndTimeBegin.HasValue, m => m.AddTime >= hourTaskQueryDto.EndTimeBegin.Value);
                predicate = predicate.AndIF(hourTaskQueryDto.EndTimeEnd.HasValue, m => m.AddTime <= hourTaskQueryDto.EndTimeEnd.Value);
            }
            else
            {
                predicate = predicate.AndIF(hourTaskQueryDto.EndTimeBegin.HasValue, m => m.EndTime >= hourTaskQueryDto.EndTimeBegin.Value);
                predicate = predicate.AndIF(hourTaskQueryDto.EndTimeEnd.HasValue, m => m.EndTime <= hourTaskQueryDto.EndTimeEnd.Value);
            }

            predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (new string[] { "1", "2", "0" }.Any(o => o.Equals(hourTaskQueryDto.TaskType))), m => m.Type.Equals(hourTaskQueryDto.TaskType));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.BusType), m => m.BusType.Equals(hourTaskQueryDto.BusType));

            var sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Select(it => new TaskInOutDto { });

            if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101"))
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                .Select(it => new
                {
                    WarehouseCode = it.WarehouseCode,
                    EditTime = hourTaskQueryDto.GroupName == "AddTime" ? it.AddTime.Date : it.EndTime.Value.Date,
                    Hour = hourTaskQueryDto.GroupName == "AddTime" ? it.AddTime.Hour : it.EndTime.Value.Hour
                })
                .MergeTable()//将查询结果转成一个表 
                .GroupBy(o => new { o.Hour, o.EditTime, o.WarehouseCode })
                .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Hour), Hour = it.Hour, CreateTime = it.EditTime, WarehouseCode = it.WarehouseCode })
                .OrderBy(o => o.CreateTime);
            }
            else
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                .Select(it => new
                {
                    WarehouseCode = it.WarehouseCode,
                    Type = it.Type,
                    EditTime = hourTaskQueryDto.GroupName == "AddTime" ? it.AddTime.Date : it.EndTime.Value.Date,
                    Hour = hourTaskQueryDto.GroupName == "AddTime" ? it.AddTime.Hour : it.EndTime.Value.Hour
                })
                .MergeTable()//将查询结果转成一个表 
                .GroupBy(o => new { o.Type, o.Hour, o.EditTime, o.WarehouseCode })
                .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Type), Type = it.Type, Hour = it.Hour, CreateTime = it.EditTime, WarehouseCode = it.WarehouseCode })
                .OrderBy(o => o.CreateTime);

            }


            var lists = sugarQueryable.ToList();
            PagedInfo<HourTaskDto> HourTasks = new PagedInfo<HourTaskDto>()
            {
                Result = new List<HourTaskDto>()
            };
            //var plist = new List<PropertyInfo>(typeof(HourTaskDto).GetProperties());
            var plist = typeof(HourTaskDto);
            var WarehouseCodes = lists.Select(o => o.WarehouseCode).Distinct().OrderBy(o => o).ToList();
            var types = lists.Select(o => o.Type).Distinct().OrderBy(o => o).ToList();
            var dates = lists.Select(o => o.CreateTime).Distinct().OrderBy(o => o).ToList();
            var warehouses = _baseWarehouseRepository.Queryable().Where(o => o.IsStop == 0).ToList();
            foreach (var warehouse in WarehouseCodes)
            {
                foreach (var p in dates)
                {
                    if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101"))
                    {
                        if (!lists.Any(o => o.WarehouseCode == warehouse && o.CreateTime == p))
                        {
                            continue;
                        }
                        HourTaskDto s = Activator.CreateInstance<HourTaskDto>();

                        var warehouseCode = plist.GetProperty("WarehouseCode");
                        if (warehouseCode != null)
                            warehouseCode.SetValue(s, warehouse, null);

                        var warehousename = warehouses.FirstOrDefault(o => o.Code == warehouse);
                        var name = plist.GetProperty("WarehouseName");
                        if (warehousename != null && name != null)
                            name.SetValue(s, warehousename.Name, null);

                        var TaskDate = plist.GetProperty("TaskDate");
                        if (TaskDate != null)
                            TaskDate.SetValue(s, p.ToString("yyyy-MM-dd"), null);
                        var TaskType = plist.GetProperty("TaskType");
                        if (TaskType != null)
                            TaskType.SetValue(s, -1, null);

                        for (int i = 0; i < 24; i++)
                        {
                            PropertyInfo info = plist.GetProperty("A" + i.ToString());
                            if (info != null)
                            {
                                var first = lists.FirstOrDefault(o => o.WarehouseCode == warehouse && o.CreateTime == p && o.Hour == i);
                                if (first != null)
                                {
                                    info.SetValue(s, first.Count, null);
                                }
                            }
                        }
                        HourTasks.Result.Add(s);
                    }
                    else
                    {
                        foreach (var t in types)
                        {
                            if (!lists.Any(o => o.WarehouseCode == warehouse && o.CreateTime == p && o.Type == t))
                            {
                                continue;
                            }
                            HourTaskDto s = Activator.CreateInstance<HourTaskDto>();

                            var warehouseCode = plist.GetProperty("WarehouseCode");
                            if (warehouseCode != null)
                                warehouseCode.SetValue(s, warehouse, null);

                            var warehousename = warehouses.FirstOrDefault(o => o.Code == warehouse);
                            var name = plist.GetProperty("WarehouseName");
                            if (warehousename != null && name != null)
                                name.SetValue(s, warehousename.Name, null);

                            var TaskType = plist.GetProperty("TaskType");
                            if (TaskType != null)
                                TaskType.SetValue(s, (TaskType)t, null);
                            var TaskDate = plist.GetProperty("TaskDate");
                            if (TaskDate != null)
                                TaskDate.SetValue(s, p.ToString("yyyy-MM-dd"), null);



                            for (int i = 0; i < 24; i++)
                            {
                                PropertyInfo info = plist.GetProperty("A" + i.ToString());
                                if (info != null)
                                {
                                    var first = lists.FirstOrDefault(o => o.WarehouseCode == warehouse && o.Type == t && o.CreateTime == p && o.Hour == i);
                                    if (first != null)
                                    {
                                        info.SetValue(s, first.Count, null);
                                    }
                                }
                            }
                            HourTasks.Result.Add(s);
                        }
                    }


                }
            }
            if (!pagination.sidx.IsEmpty())
            {
                pagination.sidx = pagination.sidx.Replace("DESC", "").Replace("ASC", "");
                HourTasks.Result.Sort(
                 delegate (HourTaskDto info1, HourTaskDto info2)
                 {
                     Type t = typeof(HourTaskDto);
                     PropertyInfo pro = t.GetProperty(pagination.sidx);
                     if (pagination.sidx == "TaskDate" || pagination.sidx == "TypeName" || pagination.sidx == "WarehouseName")
                     {
                         return pagination.sord.ToLower().Contains("asc") ?
                                                       pro.GetValue(info1, null).ToEmptyString().CompareTo(pro.GetValue(info2, null).ToEmptyString()) :
                                                       pro.GetValue(info2, null).ToEmptyString().CompareTo(pro.GetValue(info1, null).ToEmptyString());
                     }
                     else
                         return pagination.sord.ToLower().Contains("asc") ?
                              pro.GetValue(info1, null).ToInt().CompareTo(pro.GetValue(info2, null).ToInt()) :
                              pro.GetValue(info2, null).ToInt().CompareTo(pro.GetValue(info1, null).ToInt());

                 });
                //typeof(HourTaskDto).GetProperty( pagination.sidx.Replace("DESC", "").Replace("ASC", ""))).ToList();
            }
            HourTasks.PageIndex = 1;
            HourTasks.PageSize = HourTasks.Result.Count;
            HourTasks.TotalNum = HourTasks.Result.Count;
            return HourTasks;
        }
        /// <summary>
        /// 分拣码垛数
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="QueryDto"></param>
        /// <returns></returns>
        public PagedInfo<PalletizingReportDto> GetPalletizingReport(Pagination pagination, PalletizingReportQueryDto QueryDto)
        {
            if (!QueryDto.EndTimeBegin.HasValue)
            {
                QueryDto.EndTimeBegin = DateTime.Now.Date;
            }
            if (!QueryDto.EndTimeEnd.HasValue)
            {
                QueryDto.EndTimeEnd = DateTime.Now.AddHours(1);
            }
            var predicate = Expressionable.Create<WCS_TaskOld>();
            predicate = predicate.And(m => m.BusType == "码垛入库" && m.BarCode.Contains("TP"));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(QueryDto.WarehouseCode), m => QueryDto.WarehouseCode.Equals(m.WarehouseCode));

            predicate = predicate.AndIF(QueryDto.EndTimeBegin.HasValue, m => m.AddTime >= QueryDto.EndTimeBegin.Value);
            predicate = predicate.AndIF(QueryDto.EndTimeEnd.HasValue, m => m.AddTime <= QueryDto.EndTimeEnd.Value);

            ISugarQueryable<PalletizingReportDto> sugarQueryable=null;
            //var sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3)).Select(it => new PalletizingReportDto { });
            if (QueryDto.GroupName == "Hour" && QueryDto.WarehouseGroupName== "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.WarehouseCode, it.EditTime.Year, it.EditTime.Month, it.EditTime.Day, it.EditTime.Hour })
                  .Select(it => new PalletizingReportDto
                  {
                      WarehouseCode=it.WarehouseCode,
                      Year = it.EditTime.Year.ToString(),
                      Month = it.EditTime.Month.ToString(),
                      Day = it.EditTime.Day.ToString(),
                      Hour = it.EditTime.Hour.ToString(),
                      Qty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>())
                  })
                .OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour });
            }
            else if (QueryDto.GroupName == "Hour" && QueryDto.WarehouseGroupName != "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.EditTime.Year, it.EditTime.Month, it.EditTime.Day, it.EditTime.Hour })
                  .Select(it => new PalletizingReportDto
                  {
                      Year = it.EditTime.Year.ToString(),
                      Month = it.EditTime.Month.ToString(),
                      Day = it.EditTime.Day.ToString(),
                      Hour = it.EditTime.Hour.ToString(),
                      Qty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>())
                  })
                .OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour });
            }
            else if (QueryDto.GroupName == "Day" && QueryDto.WarehouseGroupName == "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.WarehouseCode, it.EditTime.Year, it.EditTime.Month, it.EditTime.Day  })
                  .Select(it => new PalletizingReportDto
                  {
                      WarehouseCode = it.WarehouseCode,
                      Year = it.EditTime.Year.ToString(),
                      Month = it.EditTime.Month.ToString(),
                      Day = it.EditTime.Day.ToString(),             
                      Qty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>())
                  })
                .OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour });
            }            
            else if (QueryDto.GroupName == "Day" && QueryDto.WarehouseGroupName != "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                                  .GroupBy(it => new { it.EditTime.Year, it.EditTime.Month, it.EditTime.Day })
                                  .Select(it => new PalletizingReportDto
                                  {
                                      Year = it.EditTime.Year.ToString(),
                                      Month = it.EditTime.Month.ToString(),
                                      Day = it.EditTime.Day.ToString(),
                                      Qty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>())
                                  })
                                .OrderBy(o => new { o.Year, o.Month, o.Day });
            }
        
            var lists = sugarQueryable?.ToList();
            if ( QueryDto.WarehouseGroupName == "Warehouse")
            {
                var warehouses = _baseWarehouseRepository.Queryable().Where(o => o.IsStop == 0).ToList();
                foreach (var item in lists)
                {
                    item.WarehouseName = warehouses.FirstOrDefault(o => o.Code == item.WarehouseCode)?.Name ?? "";
                }
            }
              
            PagedInfo<PalletizingReportDto> PalletizingReports = new PagedInfo<PalletizingReportDto>()
            {
                Result = lists
            };
            if (!pagination.sidx.IsEmpty())
            {
                pagination.sidx = pagination.sidx.Replace("DESC", "").Replace("ASC", "");
                PalletizingReports.Result.Sort(
                 delegate (PalletizingReportDto info1, PalletizingReportDto info2)
                 {
                     Type t = typeof(PalletizingReportDto);
                     PropertyInfo pro = t.GetProperty(pagination.sidx);
                     if (pagination.sidx == "WarehouseCode" || pagination.sidx == "WarehouseName")
                     {
                         return pagination.sord.ToLower().Contains("asc") ?
                                                       pro.GetValue(info1, null).ToEmptyString().CompareTo(pro.GetValue(info2, null).ToEmptyString()) :
                                                       pro.GetValue(info2, null).ToEmptyString().CompareTo(pro.GetValue(info1, null).ToEmptyString());
                     }
                     else
                         return pagination.sord.ToLower().Contains("asc") ?
                              pro.GetValue(info1, null).ToInt().CompareTo(pro.GetValue(info2, null).ToInt()) :
                              pro.GetValue(info2, null).ToInt().CompareTo(pro.GetValue(info1, null).ToInt());

                 });              
            }
            PalletizingReports.PageIndex = 1;
            PalletizingReports.PageSize = PalletizingReports.Result.Count;
            PalletizingReports.TotalNum = PalletizingReports.Result.Count;

            PalletizingReports.Result.Add(new PalletizingReportDto
            {
                WarehouseName = "合计：",
                Qty = PalletizingReports.Result.Sum(o => o.Qty)

            });

            return PalletizingReports;
        }

        public PagedInfo<InOutReportDto> GetInOutReport(Pagination pagination, InOutReportQueryDto QueryDto)
        {
            if (!QueryDto.EndTimeBegin.HasValue)
            {
                QueryDto.EndTimeBegin = DateTime.Now.Date;
            }
            if (!QueryDto.EndTimeEnd.HasValue)
            {
                QueryDto.EndTimeEnd = DateTime.Now.AddHours(1);
            }

            var predicate = Expressionable.Create<WCS_TaskOld>();
            predicate = predicate.And(m => m.Status == (int)BZModels.TaskStatus.Finish);
            predicate = predicate.And(m =>m.Type==0);
            predicate = predicate.AndIF(!string.IsNullOrEmpty(QueryDto.WarehouseCode), m => QueryDto.WarehouseCode.Equals(m.WarehouseCode));
            predicate = predicate.AndIF(QueryDto.EndTimeBegin.HasValue, m => m.AddTime >= QueryDto.EndTimeBegin.Value);
            predicate = predicate.AndIF(QueryDto.EndTimeEnd.HasValue, m => m.AddTime <= QueryDto.EndTimeEnd.Value);
            ISugarQueryable<InOutReportDto> sugarQueryable = null; 
            if (QueryDto.GroupName == "Hour" && QueryDto.WarehouseGroupName == "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.WarehouseCode, it.AddTime.Year, it.AddTime.Month, it.AddTime.Day, it.AddTime.Hour })
                  .Select(it => new InOutReportDto
                  {
                      WarehouseCode = SqlFunc.IF(it.WarehouseCode == "1N").Return("一北桁架")
                                              .ElseIF(it.WarehouseCode == "1S").Return("一南桁架")
                                              .ElseIF(it.WarehouseCode == "1NR").Return("一北环库")
                                              .ElseIF(it.WarehouseCode == "1SR").Return("一南环库")
                                              .ElseIF(it.WarehouseCode == "2N").Return("二北桁架")
                                              .ElseIF(it.WarehouseCode == "2S").Return("二南桁架")
                                              .ElseIF(it.WarehouseCode == "2NR").Return("二北环库")
                                              .ElseIF(it.WarehouseCode == "2SR").Return("二南环库")
                                              .ElseIF(it.WarehouseCode == "3N").Return("三北桁架")
                                              .ElseIF(it.WarehouseCode == "3S").Return("三南桁架")
                                              .ElseIF(it.WarehouseCode == "3NR").Return("三北环库")
                                              .ElseIF(it.WarehouseCode == "3SR").Return("三南环库")
                                              .End<string>() ,
                      Year = it.AddTime.Year.ToString(),
                      Month = it.AddTime.Month.ToString(),
                      Day = it.AddTime.Day.ToString(),
                      Hour = it.AddTime.Hour.ToString(),
                      GroupQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 0).Return(1).End<int>())
                  }).MergeTable();
            }
            else if (QueryDto.GroupName == "Hour" && QueryDto.WarehouseGroupName != "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.AddTime.Year, it.AddTime.Month, it.AddTime.Day, it.AddTime.Hour })
                  .Select(it => new InOutReportDto
                  {
                      Year = it.AddTime.Year.ToString(),
                      Month = it.AddTime.Month.ToString(),
                      Day = it.AddTime.Day.ToString(),
                      Hour = it.AddTime.Hour.ToString(),
                      GroupQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 0).Return(1).End<int>())
                  }).MergeTable();
                if (pagination.sidx == "Warehouse")
                    pagination.sidx = "";
            }
            else if (QueryDto.GroupName == "Day" && QueryDto.WarehouseGroupName == "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                    .GroupBy(it => new { it.WarehouseCode, it.AddTime.Year, it.AddTime.Month, it.AddTime.Day })
                  .Select(it => new InOutReportDto
                  {
                      WarehouseCode = SqlFunc.IF(it.WarehouseCode == "1N").Return("一北桁架")
                                              .ElseIF(it.WarehouseCode == "1S").Return("一南桁架")
                                              .ElseIF(it.WarehouseCode == "1NR").Return("一北环库")
                                              .ElseIF(it.WarehouseCode == "1SR").Return("一南环库")
                                              .ElseIF(it.WarehouseCode == "2N").Return("二北桁架")
                                              .ElseIF(it.WarehouseCode == "2S").Return("二南桁架")
                                              .ElseIF(it.WarehouseCode == "2NR").Return("二北环库")
                                              .ElseIF(it.WarehouseCode == "2SR").Return("二南环库")
                                              .ElseIF(it.WarehouseCode == "3N").Return("三北桁架")
                                              .ElseIF(it.WarehouseCode == "3S").Return("三南桁架")
                                              .ElseIF(it.WarehouseCode == "3NR").Return("三北环库")
                                              .ElseIF(it.WarehouseCode == "3SR").Return("三南环库")
                                              .End<string>(),
                      Year = it.AddTime.Year.ToString(),
                      Month = it.AddTime.Month.ToString(),
                      Day = it.AddTime.Day.ToString(),
                      GroupQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 0).Return(1).End<int>())
                  }).MergeTable();
                if (pagination.sidx == "Hour")
                    pagination.sidx = "Day";
            }
            else if (QueryDto.GroupName == "Day" && QueryDto.WarehouseGroupName != "Warehouse")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                                  .GroupBy(it => new { it.AddTime.Year, it.AddTime.Month, it.AddTime.Day })
                                  .Select(it => new InOutReportDto
                                  {
                                      Year = it.AddTime.Year.ToString(),
                                      Month = it.AddTime.Month.ToString(),
                                      Day = it.AddTime.Day.ToString(),
                                      GroupQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 0).Return(1).End<int>())
                                  }).MergeTable();
                if (pagination.sidx == "Warehouse" || pagination.sidx == "Hour")
                    pagination.sidx = "Day";
            }
            var lists = sugarQueryable.ToPage(pagination);
            lists.Result.Add(new InOutReportDto
            {
                WarehouseCode = "合计：",
                GroupQty=lists.Result.Sum(o=>o.GroupQty)
            });
            return lists;
        }
    }
}