WEBUI/WMS.BZServices/CP/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.cp;
using WMS.BZSqlSugar;
using WMS.BZModels.Dto.CP.ReportDtos;
using System.Reflection;
using WMS.BZModels.Dto;
using WMS.Util;

namespace WMS.BZServices.CP
{
    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);
            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>().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>
        /// 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().Select(it => new TaskInOutDto { });

            if (!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (hourTaskQueryDto.TaskType == "100" || hourTaskQueryDto.TaskType == "101"))
            {
                sugarQueryable = _taskoldrepository.Queryable().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().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 = new List<PropertyInfo>(typeof(HourTaskDto).GetProperties());
            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(),
                              InQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>()),
                              OutQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2).Return(1).End<int>()),
                              RelocationQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 3).Return(1).End<int>()),
                              TrayQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 5).Return(1).End<int>()),
                              TotalQty = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2", "3", "5" }.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(),
                             InQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 1).Return(1).End<int>()),
                             OutQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 2).Return(1).End<int>()),
                             RelocationQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 3).Return(1).End<int>()),
                             TrayQty = SqlFunc.AggregateCount(SqlFunc.IF(it.Type == 5).Return(1).End<int>()),
                             TotalQty = SqlFunc.AggregateCount(SqlFunc.IF(new string[] { "1", "2", "3", "5" }.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 = "合计：",
                InQty = lists.Result.Sum(o => o.InQty),
                OutQty = lists.Result.Sum(o => o.OutQty),
                RelocationQty = lists.Result.Sum(o => o.RelocationQty),
                TrayQty = lists.Result.Sum(o => o.TrayQty),
                TotalQty = lists.Result.Sum(o => o.TotalQty),
            });
            return lists;
        }

    }
}