WEBUI/WMS.BZServices/HJ/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.hj;
using WMS.BZSqlSugar;
using WMS.BZModels.Dto.HJ.ReportDtos;
using static Microsoft.Extensions.Logging.EventSource.LoggingEventSource;
using System.Reflection;
using WMS.BZModels.Dto;
using WMS.Util;

namespace WMS.BZServices.HJ
{
    public class StatisticsreportService
    {
        private readonly Repository<BillInvflow> _flowrepository;
        private readonly Repository<WCS_TaskOld> _taskoldrepository;
        public StatisticsreportService(Repository<BillInvflow> flowrepository, Repository<WCS_TaskOld> taskoldrepository)
        {
            _flowrepository = flowrepository;
            _taskoldrepository = taskoldrepository;
        }
        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);
            list.Result.ForEach(o =>
            {
                o.NetWQty = Math.Round(Convert.ToDecimal(o.NetWQty), 2, MidpointRounding.AwayFromZero);
            });
            return list;
        }

        public IList<StatisticsDto> GetList(StatisticsQueryDto statisticsQueryDto)
        {
            ISugarQueryable<StatisticsDto> sugarQueryable = GetQueryable(statisticsQueryDto);
            var list = sugarQueryable.ToList();
            list.ForEach(o =>
            {
                o.NetWQty = Math.Round(Convert.ToDecimal(o.NetWQty), 3, MidpointRounding.AwayFromZero);
            });
            return list;
        }

        private ISugarQueryable<StatisticsDto> GetQueryable(StatisticsQueryDto statisticsQueryDto)
        {
            var sugarQueryable = _flowrepository.Context.Queryable<BillInvflow>()
                .LeftJoin<BaseMatinfo>((billInvflow, mater) => billInvflow.MatCode == mater.Code)
                .Where((billInvflow, mater) => billInvflow.Memo == "出库任务完成" || billInvflow.Memo == "入库任务完成")
                .WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (billInvflow, mater) => mater.Code.Contains(statisticsQueryDto.KeyWord) || mater.Name.Contains(statisticsQueryDto.KeyWord))
                .WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (billInvflow, mater) => mater.Code.Contains(statisticsQueryDto.MatCode))
                .WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (billInvflow, mater) => mater.Name.Contains(statisticsQueryDto.MatName))
                .WhereIF(statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (billInvflow, mater) => billInvflow.AddTime >= statisticsQueryDto.AddTimeFrom)
                .WhereIF(statisticsQueryDto.AddTimeTo != null && statisticsQueryDto.AddTimeTo.HasValue, (billInvflow, mater) => billInvflow.AddTime <= statisticsQueryDto.AddTimeTo)
                .GroupBy((billInvflow, mater) => new { mater.Code, mater.Name, billInvflow.Floor, billInvflow.Memo })
                .Select((billInvflow, mater) => new StatisticsDto
                {
                    MatCode = mater.Code,
                    MatName = mater.Name,
                    Floor = billInvflow.Floor,
                    Type = billInvflow.Memo == "出库任务完成" ? 2 : (billInvflow.Memo == "入库任务完成" ? 1 : -1),
                    NetWQty = SqlFunc.AggregateSum(billInvflow.NetWQty),
                    Count = SqlFunc.AggregateDistinctCount(billInvflow.Id)
                }).MergeTable();
            //var sugarQueryable = _taskoldrepository.Context.Queryable<WCS_TaskOld>().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(!string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.KeyWord) || mater.Name.Contains(statisticsQueryDto.KeyWord))
            //    .WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (taskold, mater) => mater.Code.Contains(statisticsQueryDto.MatCode))
            //    .WhereIF(!string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (taskold, mater) => mater.Name.Contains(statisticsQueryDto.MatName))
            //    .WhereIF(statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (taskold, mater) => taskold.AddTime >= statisticsQueryDto.AddTimeFrom)
            //    .WhereIF(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,
            //        NetWQty = SqlFunc.AggregateSum(Convert.ToDecimal( taskold.Weight??0.0)),
            //        Count = SqlFunc.AggregateDistinctCount(taskold.ID)
            //    }).MergeTable();
            return sugarQueryable;
        }

        /// <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);
            predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.WarehouseCode), m => hourTaskQueryDto.WarehouseCode.Equals(m.WarehouseCode));
            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", "3", "5" }.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
                {
                    EditTime = it.EndTime.Value.Date,
                    Hour = it.EndTime.Value.Hour
                })
                .MergeTable()//将查询结果转成一个表 
                .GroupBy(o => new { o.Hour, o.EditTime })
                .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Hour), Hour = it.Hour, CreateTime = it.EditTime })
                .OrderBy(o => o.CreateTime);
            }
            else
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                .Select(it => new
                {
                    Type = it.Type,
                    EditTime = it.EndTime.Value.Date,
                    Hour = it.EndTime.Value.Hour
                })
                .MergeTable()//将查询结果转成一个表 
                .GroupBy(o => new { o.Type, o.Hour, o.EditTime })
                .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Type), Type = it.Type, Hour = it.Hour, CreateTime = it.EditTime })
                .OrderBy(o => o.CreateTime);

            }


            var lists = sugarQueryable.ToList();
            PagedInfo<HourTaskDto> HourTasks = new PagedInfo<HourTaskDto>()
            {
                Result = new List<HourTaskDto>()
            };

            var plist = typeof(HourTaskDto);
            var types = lists.Select(o => o.Type).Distinct().OrderBy(o => o).ToList();
            var dates = lists.Select(o => o.CreateTime).Distinct().OrderBy(o => o).ToList();
            foreach (var p in dates)
            {
                if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101"))
                {
                    if (!lists.Any(o => o.CreateTime == p))
                    {
                        continue;
                    }
                    HourTaskDto s = Activator.CreateInstance<HourTaskDto>();
                    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.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.CreateTime == p && o.Type == t))
                        {
                            continue;
                        }
                        HourTaskDto s = Activator.CreateInstance<HourTaskDto>();
                        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.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")
                     {
                         return pagination.sord.ToLower().Contains("asc") ?
                                                       pro.GetValue(info1, null).ToString().CompareTo(pro.GetValue(info2, null).ToString()) :
                                                       pro.GetValue(info2, null).ToString().CompareTo(pro.GetValue(info1, null).ToString());
                     }
                     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;
        }

        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 => new string[] { "1", "2", "3", "5" }.Contains(m.Type.ToString()));
            predicate = predicate.AndIF(queryDto.EndTimeBegin.HasValue, m => m.EndTime >= queryDto.EndTimeBegin.Value);
            predicate = predicate.AndIF(queryDto.EndTimeEnd.HasValue, m => m.EndTime <= queryDto.EndTimeEnd.Value);
            ISugarQueryable<InOutReportDto> sugarQueryable;
            if (queryDto.GroupName == "Hour")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                      .GroupBy(it => new { it.EndTime.Value.Year, it.EndTime.Value.Month, it.EndTime.Value.Day, it.EndTime.Value.Hour })
                          .Select(it => new InOutReportDto
                          {
                              Year = it.EndTime.Value.Year.ToString(),
                              Month = it.EndTime.Value.Month.ToString(),
                              Day = it.EndTime.Value.Day.ToString(),
                              Hour = it.EndTime.Value.Hour.ToString(),
                              FirstFloorFullIn = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && it.BusType == "镀铜一楼取满" && it.ProdLine != 1).Return(1).End<int>() ),
                              ProdLine = SqlFunc.AggregateCount(SqlFunc.IF(it.ProdLine == 1).Return(1).End<int>()),
                              CopperPlated = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 5 && it.BusType == "镀铜线补空轮").Return(1).End<int>()),
                              TotalFirstFloorEmpty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼立库出空轮"  ).Return(1).End<int>()) + SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "1012", "1014", "1016", "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                              SecondFloorEmpty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && it.BusType == "二楼湿拉空轮回立库" && !new string[] { "1012", "1014", "1016", "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                              SecondFloorReturn = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "二楼湿拉线退料回立库", "二楼湿拉线余料回立库" }.Contains(it.BusType)  ).Return(1).End<int>()),
                              SecondFloorDeliveryTask = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "二楼湿拉自动叫料出库"  ).Return(1).End<int>()),
                              FirstFloorOutTask = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼立库出空轮"  ).Return(1).End<int>()),
                              FirstFloorFullOut = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼出满轮"  ).Return(1).End<int>()),
                              SecondFloorToFirstFloor = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "1012", "1014", "1016", "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                              Relocation = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 3  ).Return(1).End<int>()),
                              TotalInOut = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2" }.Contains(it.Type.ToString())  ).Return(1).End<int>()),
                              TotalTask = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2", "3" }.Contains(it.Type.ToString())  ).Return(1).End<int>()),

                          }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour })
            }
            else
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression()).SplitTable(tabs => tabs.Take(3))
                     .GroupBy(it => new { it.EndTime.Value.Year, it.EndTime.Value.Month, it.EndTime.Value.Day })
                         .Select(it => new InOutReportDto
                         {
                             Year = it.EndTime.Value.Year.ToString(),
                             Month = it.EndTime.Value.Month.ToString(),
                             Day = it.EndTime.Value.Day.ToString(),
                             FirstFloorFullIn = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && it.BusType == "镀铜一楼取满" && it.ProdLine != 1).Return(1).End<int>() ),
                             ProdLine = SqlFunc.AggregateCount(SqlFunc.IF( it.ProdLine == 1).Return(1).End<int>()),
                             CopperPlated = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 5 && it.BusType == "镀铜线补空轮"  ).Return(1).End<int>()),
                             TotalFirstFloorEmpty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼立库出空轮"  ).Return(1).End<int>()) + SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "1012", "1014", "1016", "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                             SecondFloorEmpty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && it.BusType == "二楼湿拉空轮回立库" && !new string[] { "1012", "1014", "1016" , "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                             SecondFloorReturn = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "二楼湿拉线退料回立库", "二楼湿拉线余料回立库" }.Contains(it.BusType)  ).Return(1).End<int>()),
                             SecondFloorDeliveryTask = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "二楼湿拉自动叫料出库"  ).Return(1).End<int>()),
                             FirstFloorOutTask = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼立库出空轮"  ).Return(1).End<int>()),
                             FirstFloorFullOut = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2 && it.BusType == "一楼出满轮"  ).Return(1).End<int>()),
                             SecondFloorToFirstFloor = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1 && new string[] { "1012", "1014", "1016", "1118", "1131", "1132", "1133", "1134", "1135", "1136", "1137", "1139" }.Contains(it.AddrTo)  ).Return(1).End<int>()),
                             Relocation = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 3  ).Return(1).End<int>()),
                             TotalInOut = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2" }.Contains(it.Type.ToString())  ).Return(1).End<int>()),
                             TotalTask = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2", "3" }.Contains(it.Type.ToString())).Return(1).End<int>()),

                         }).MergeTable(); //.OrderBy(o => new { o.Year, o.Month, o.Day })
                if (pagination.sidx == "Hour")
                    pagination.sidx = "Day";
            }


            var lists = sugarQueryable.ToPage(pagination);
            lists.Result.Add(new InOutReportDto
            {
                Year = "合计：",
                FirstFloorFullIn = lists.Result.Sum(o => o.FirstFloorFullIn),
                ProdLine = lists.Result.Sum(o => o.ProdLine),
                CopperPlated = lists.Result.Sum(o => o.CopperPlated),
                TotalFirstFloorEmpty = lists.Result.Sum(o => o.TotalFirstFloorEmpty),
                SecondFloorEmpty = lists.Result.Sum(o => o.SecondFloorEmpty),
                SecondFloorReturn = lists.Result.Sum(o => o.SecondFloorReturn),
                SecondFloorDeliveryTask = lists.Result.Sum(o => o.SecondFloorDeliveryTask),
                FirstFloorOutTask = lists.Result.Sum(o => o.FirstFloorOutTask),
                FirstFloorFullOut = lists.Result.Sum(o => o.FirstFloorFullOut),
                SecondFloorToFirstFloor = lists.Result.Sum(o => o.SecondFloorToFirstFloor),
                Relocation = lists.Result.Sum(o => o.Relocation),
                TotalInOut = lists.Result.Sum(o => o.TotalInOut),
                TotalTask = lists.Result.Sum(o => o.TotalTask),
            });
            return lists;
        }

    }
}