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

namespace WMS.BZServices.SX
{
    public class StatisticsreportService
    {
        private readonly Repository<BillInvflow> _flowrepository;
        private readonly Repository<WCS_TaskOld> _taskoldrepository;
        private readonly Repository<WCSTaskDtl> _taskdtlrepository;
        private readonly Repository<Palletizing> _palletizingrepository;

        private readonly Repository<BillInvnow> _billInvnowRepository;
        private readonly Repository<BillPboxrule> _billPboxruleRepository;

        public StatisticsreportService(Repository<BillInvflow> flowrepository, Repository<WCS_TaskOld> taskoldrepository, Repository<WCSTaskDtl> taskdtlrepository, Repository<Palletizing> palletizingrepository, Repository<BillInvnow> billInvnowRepository, Repository<BillPboxrule> billPboxruleRepository)
        {
            _flowrepository = flowrepository;
            _taskoldrepository = taskoldrepository;
            _taskdtlrepository = taskdtlrepository;
            _palletizingrepository = palletizingrepository;
            _billInvnowRepository = billInvnowRepository;
            _billPboxruleRepository = billPboxruleRepository;
        }

        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 PagedInfo<StatisticsDto> GetBGradeStatisticesPageList(Pagination pagination, StatisticsQueryDto statisticsQueryDto)
        {
           
            var list = GetBGradeStatisticesQueryable(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;
        }
        private ISugarQueryable<StatisticsDto> GetBGradeStatisticesQueryable(StatisticsQueryDto statisticsQueryDto)
        {
            var sugarQueryable = _billInvnowRepository.Context.Queryable<BillInvnow>().With(SqlWith.NoLock)
                .Where(p=> p.InvStateCode == "InvEcecState_In" && p.Grade == "B" && string.IsNullOrEmpty(p.PreStock) && (DateTime.Now - p.ProductTime).TotalHours >= 72 && (DateTime.Now - p.ProductTime).TotalHours < 120)
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.KeyWord), (p) => p.MatCode.Contains(statisticsQueryDto.KeyWord) || p.MatName.Contains(statisticsQueryDto.KeyWord))
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatCode), (p) => p.MatCode.Contains(statisticsQueryDto.MatCode))
                .WhereIF(statisticsQueryDto != null && !string.IsNullOrWhiteSpace(statisticsQueryDto.MatName), (p) => p.MatName.Contains(statisticsQueryDto.MatName))
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeFrom != null && statisticsQueryDto.AddTimeFrom.HasValue, (p) => taskold.AddTime >= statisticsQueryDto.AddTimeFrom)
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.AddTimeTo != null && statisticsQueryDto.AddTimeTo.HasValue, (p) => taskold.AddTime <= statisticsQueryDto.AddTimeTo)
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeBegin.HasValue, (p) => taskold.StartTime >= statisticsQueryDto.StartTimeBegin)
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.StartTimeEnd.HasValue, (p) => taskold.StartTime <= statisticsQueryDto.StartTimeEnd)
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeBegin.HasValue, (p) => taskold.EndTime >= statisticsQueryDto.EndTimeBegin)
                //.WhereIF(statisticsQueryDto != null && statisticsQueryDto.EndTimeEnd.HasValue, (p) => taskold.EndTime <= statisticsQueryDto.EndTimeEnd)
                .GroupBy((p) => new { p.MatCode, p.MatName, p.HWTypeCode , p.Wind })
                .Select((p) => new StatisticsDto
                {
                    MatCode = p.MatCode,
                    MatName = p.MatName,
                    HwType = p.HWTypeCode,
                    Wind = p.Wind,
                   
                    Count = SqlFunc.AggregateDistinctCount(p.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.EditTime >= hourTaskQueryDto.EndTimeBegin.Value);
            predicate = predicate.AndIF(hourTaskQueryDto.EndTimeEnd.HasValue, m => m.EditTime <= hourTaskQueryDto.EndTimeEnd.Value);
            predicate = predicate.AndIF(!string.IsNullOrEmpty(hourTaskQueryDto.TaskType) && (new string[] { "1", "2", "3" }.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.EditTime.Date,
                    Hour = it.EditTime.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.EditTime.Date,
                    Hour = it.EditTime.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");
                    //var TaskDate = plist.Find(p => p.Name == "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, (BZModels.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;
        }

        /// <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<Palletizing>();
            predicate = predicate.And(m => m.Finish != 1 && !new string[] { "Special", "OnePack" }.Contains(m.BoxRule));
            predicate = predicate.And(m => m.PalletizState == 1);
            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;
            if (QueryDto.GroupName == "Hour")
            {
                sugarQueryable = _palletizingrepository.Queryable().With(SqlWith.NoLock).Where(predicate.ToExpression())
                    .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(),
                      BigQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 1).Return(1).End<int>()),
                      SmallQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 2).Return(1).End<int>()),
                      TotalQty = SqlFunc.AggregateCount(1),
                  }).MergeTable();

            }
            else
            {
                sugarQueryable = _palletizingrepository.Queryable().With(SqlWith.NoLock).With(SqlWith.NoLock).Where(predicate.ToExpression())
                                  .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(),
                                      BigQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 1).Return(1).End<int>()),
                                      SmallQty = SqlFunc.AggregateCount(SqlFunc.IF(it.GoodsType == 2).Return(1).End<int>()),
                                      TotalQty = SqlFunc.AggregateCount(1),
                                  }).MergeTable();
                if (pagination.sidx == "Hour")
                    pagination.sidx = "Day";
            }

            var lists = sugarQueryable.ToPage(pagination);
            lists.Result.Add(new PalletizingReportDto
            {
                Year = "合计：",
                BigQty = lists.Result.Sum(o => o.BigQty),
                SmallQty = lists.Result.Sum(o => o.SmallQty),
                TotalQty = lists.Result.Sum(o => o.TotalQty)

            });
            return lists;
        }


        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, WCSTaskDtl>();
            predicate = predicate.And((taskold, dtl) => taskold.Status >= (int)BZModels.TaskStatus.Finish);
            predicate = predicate.And((taskold, dtl) => new string[] { "1", "2" }.Contains(taskold.Type.ToString()));
            predicate = predicate.AndIF(queryDto.EndTimeBegin.HasValue, (taskold, dtl) => dtl.AddTime >= queryDto.EndTimeBegin.Value);
            predicate = predicate.AndIF(queryDto.EndTimeEnd.HasValue, (taskold, dtl) => dtl.AddTime <= queryDto.EndTimeEnd.Value);
            ISugarQueryable<InOutReportDto> sugarQueryable;
            var joinq = _taskdtlrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3));
            if (queryDto.GroupName == "Hour")
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3))
                      .LeftJoin(joinq, (taskold, dtl) => taskold.ID == dtl.ParentTaskCode).Where(predicate.ToExpression())
                      .GroupBy((taskold, dtl) => new { dtl.AddTime.Year, dtl.AddTime.Month, dtl.AddTime.Day, dtl.AddTime.Hour })
                          .Select((taskold, dtl) => new InOutReportDto
                          {
                              Year = dtl.AddTime.Year.ToString(),
                              Month = dtl.AddTime.Month.ToString(),
                              Day = dtl.AddTime.Day.ToString(),
                              Hour = dtl.AddTime.Hour.ToString(),
                              CordInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End<int>()),
                              TwistInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End<int>()),
                              StackingTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End<int>()),
                              TorsionTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End<int>()),
                              TotalInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End<int>()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End<int>()),
                              TotalOutQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End<int>()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End<int>()),
                          }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour })
            }
            else
            {
                sugarQueryable = _taskoldrepository.Queryable().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3))
                     .LeftJoin(joinq, (taskold, dtl) => taskold.ID == dtl.ParentTaskCode).Where(predicate.ToExpression())
                     .GroupBy((taskold, dtl) => new { dtl.AddTime.Year, dtl.AddTime.Month, dtl.AddTime.Day })
                         .Select((taskold, dtl) => new InOutReportDto
                         {
                             Year = dtl.AddTime.Year.ToString(),
                             Month = dtl.AddTime.Month.ToString(),
                             Day = dtl.AddTime.Day.ToString(),
                             CordInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End<int>()),
                             TwistInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End<int>()),
                             StackingTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End<int>()),
                             TorsionTaskQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End<int>()),
                             TotalInQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "帘线扫码" && taskold.Status >= 99).Return(taskold.ID).End<int>()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 1 && dtl.Desc == "扭转回库").Return(taskold.ID).End<int>()),
                             TotalOutQty = SqlFunc.AggregateDistinctCount(SqlFunc.IF(dtl.Desc == "码垛抓取完成").Return(taskold.ID).End<int>()) + SqlFunc.AggregateDistinctCount(SqlFunc.IF(taskold.Type == 2 && dtl.Desc == "扭转检测").Return(taskold.ID).End<int>()),
                         }).MergeTable();//.OrderBy(o => new { o.Year, o.Month, o.Day, o.Hour })
                if (pagination.sidx == "Hour")
                    pagination.sidx = "Day";
            }
            var lists = sugarQueryable.ToPage(pagination);

            lists.Result.Add(new InOutReportDto
            {
                Year = "合计：",
                CordInQty = lists.Result.Sum(o => o.CordInQty),
                TwistInQty = lists.Result.Sum(o => o.TwistInQty),
                StackingTaskQty = lists.Result.Sum(o => o.StackingTaskQty),
                TorsionTaskQty = lists.Result.Sum(o => o.TorsionTaskQty),
                TotalInQty = lists.Result.Sum(o => o.TotalInQty),
                TotalOutQty = lists.Result.Sum(o => o.TotalOutQty),
            });

            return lists;
        }


        public PagedInfo<CurStockInfo> GetCurStockInfo(Pagination pagination, CurStockInfoQueryDto queryDto)
        {
            var predicate = Expressionable.Create<BillInvnow>();
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.KeyWord), (billInvnow) => billInvnow.MatCode.Contains(queryDto.KeyWord)
            || billInvnow.MatName.Contains(queryDto.KeyWord) || billInvnow.InDocsNo.Contains(queryDto.KeyWord) || billInvnow.ContGrpBarCode.Contains(queryDto.KeyWord));

            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatName), (billInvnow) => billInvnow.MatName.Contains(queryDto.MatName));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatCode), (billInvnow) => billInvnow.MatCode.Contains(queryDto.MatCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.DocNo), (billInvnow) => billInvnow.InDocsNo.Contains(queryDto.DocNo));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.WbGroupCode), (billInvnow) => billInvnow.WbGroupCode.Contains(queryDto.WbGroupCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Wind), (billInvnow) => billInvnow.Wind.Contains(queryDto.Wind));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.HWType), (billInvnow) => billInvnow.HWTypeCode.Contains(queryDto.HWType));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.IsBlack), (billInvnow) => billInvnow.IsBlack.Equals(queryDto.IsBlack));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Grade), (billInvnow) => billInvnow.Grade.Contains(queryDto.Grade));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.ContGrpBarCode), (billInvnow) => billInvnow.ContGrpBarCode.Contains(queryDto.ContGrpBarCode));
            predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeFrom.HasValue, (billInvnow) => billInvnow.ProductTime >= queryDto.ProductTimeFrom);
            predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeTo.HasValue, (billInvnow) => billInvnow.ProductTime <= queryDto.ProductTimeTo);

            var CurStockInfoLists = _billInvnowRepository.Context.Queryable<BillInvnow>()
                .With(SqlWith.NoLock)
                .Where((billInvnow) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && ((billInvnow.IsControlpanel == false) || (billInvnow.IsControlpanel == true && billInvnow.InDocsNo.StartsWith("CHA"))))
                 .Where(predicate.ToExpression())
                 .Select((billInvnow) => new CurStockInfo
                 {
                     MatName = billInvnow.MatName,
                     MatCode = billInvnow.MatCode,
                     DocNo = billInvnow.InDocsNo,
                     WbGroupCode = billInvnow.WbGroupCode,
                     Wind = billInvnow.Wind,
                     HWType = billInvnow.HWTypeCode,
                     //IsTorsChk = billInvnow.IsTorsChk.ToString(),
                     IsBlack = billInvnow.IsBlack.ToString(),
                     //Rule = boxrule.PackRule,
                     Grade = billInvnow.Grade,
                     ContGrpBarCode = billInvnow.ContGrpBarCode,
                     BatchNo = billInvnow.BatchNo

                 }).MergeTable().ToList();

            //var boxlistqueryable = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).LeftJoin(queryable, (billPboxrule, list) => billPboxrule.DocsNo == list.DocNo)
            //  .Select((billPboxrule, list) => billPboxrule);

            var doclist = _billPboxruleRepository.Context.Queryable<BillPboxrule>().With(SqlWith.NoLock).Select(p => new CurStockInfo() { DocNo = p.DocsNo, SKU = p.SkuCode }).Distinct().ToList();
            var newCurStockInfoLists = from st in CurStockInfoLists
                                       join doc in doclist on st.DocNo equals doc.DocNo into tempstock
                                       from nst in tempstock.DefaultIfEmpty()
                                       select new  CurStockInfo
                                       {
                                           MatName = st.MatName,
                                           MatCode = st.MatCode,
                                           DocNo = nst == null ? "" : nst.DocNo,
                                           WbGroupCode = st.WbGroupCode,
                                           Wind = st.Wind,
                                           HWType = st.HWType,
                                           //IsTorsChk = billInvnow.IsTorsChk.ToString(),
                                           IsBlack = st.IsBlack.ToString(),
                                           //Rule = boxrule.PackRule,
                                           Grade = st.Grade,
                                           ContGrpBarCode = st.ContGrpBarCode,
                                           SKU = nst == null ? "" : nst.DocNo,
                                           BatchNo = st.BatchNo
                                       };



            // var pagelists = queryable.ToPage(pagination);
            var lists = newCurStockInfoLists.Distinct(new CompareStock());
            var list = new List<CurStockInfo>();
            // var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).ToList();
            var DocNos= lists.Select(o => o.DocNo).ToList();
            var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).Where(o=> DocNos.Contains(o.DocsNo)) .ToList();

            foreach (var item in lists.GroupBy(p => new { p.SKU, p.MatCode, p.DocNo, p.WbGroupCode, p.Grade,p.BatchNo }).ToList())
            {
                list.Add(new CurStockInfo()
                {
                    SKU = item.Key.SKU ?? "",
                    MatName = item.First().MatName,
                    DocNo = item.Key.DocNo,
                    WbGroupCode = item.Key.WbGroupCode ?? "",
                    BatchNo = item.Key.BatchNo,
                    NoBCount = new List<string>() { item.Count(p => p.IsBlack == "0" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L") + "L" },
                    BCount = new List<string>() { item.Count(p => p.IsBlack == "1" && p.Wind == "R") + "R", item.Count(p => p.IsBlack == "1" && p.Wind == "L") + "L" },
                    Grade = item.Key.Grade ?? "",
                    //Rule = item.Key.Rule ?? "",
                    FinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode == 2).Count(),
                    NoFinCount = boxlist.Where(p => p.DocsNo == item.Key.DocNo && p.ZXStateCode  == 0 && !string.IsNullOrEmpty(p.BatchNo) && !string.IsNullOrEmpty(p.Wind) && p.PackRule == "SPC").Count(),
                    Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode))
                });
            }
            PagedInfo<CurStockInfo> pagelists = new PagedInfo<CurStockInfo>()
            {
                Result = new List<CurStockInfo>()
            };
            pagelists.Result = list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList();
            pagelists.PageIndex = 1;
            pagelists.PageSize = list.Count;
            pagelists.TotalNum = list.Count;
            // pagelists.Result = list;
            // return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList();
            return pagelists;
        }

        /// <summary>
        /// 时效当前库存分布（控制盘）
        /// </summary>
        /// <returns></returns>
        public PagedInfo<CurStockInfo>  GetCurStockInfoControl(Pagination pagination, CurStockInfoQueryDto queryDto)
        {
            var predicate = Expressionable.Create<BillInvnow, BillPboxrule>();
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.KeyWord), (billInvnow, boxrule) => billInvnow.MatCode.Contains(queryDto.KeyWord)
            || billInvnow.MatName.Contains(queryDto.KeyWord) || billInvnow.InDocsNo.Contains(queryDto.KeyWord) || billInvnow.ContGrpBarCode.Contains(queryDto.KeyWord) || boxrule.SkuCode.Contains(queryDto.KeyWord));

            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.SKU), (billInvnow, boxrule) => boxrule.SkuCode.Contains(queryDto.SKU));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatName), (billInvnow, boxrule) => billInvnow.MatName.Contains(queryDto.MatName));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.MatCode), (billInvnow, boxrule) => billInvnow.MatCode.Contains(queryDto.MatCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.DocNo), (billInvnow, boxrule) => billInvnow.InDocsNo.Contains(queryDto.DocNo));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.WbGroupCode), (billInvnow, boxrule) => billInvnow.WbGroupCode.Contains(queryDto.WbGroupCode));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Wind), (billInvnow, boxrule) => billInvnow.Wind.Contains(queryDto.Wind));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.HWType), (billInvnow, boxrule) => billInvnow.HWTypeCode.Contains(queryDto.HWType));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.IsBlack), (billInvnow, boxrule) => billInvnow.IsBlack.Equals(queryDto.IsBlack));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.Grade), (billInvnow, boxrule) => billInvnow.Grade.Contains(queryDto.Grade));
            predicate = predicate.AndIF(!string.IsNullOrEmpty(queryDto?.ContGrpBarCode), (billInvnow, boxrule) => billInvnow.ContGrpBarCode.Contains(queryDto.ContGrpBarCode));
            predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeFrom.HasValue, (billInvnow, boxrule) => billInvnow.ProductTime >= queryDto.ProductTimeFrom);
            predicate = predicate.AndIF(queryDto != null && queryDto.ProductTimeTo.HasValue, (billInvnow, boxrule) => billInvnow.ProductTime <= queryDto.ProductTimeTo);

            var CurStockInfoLists = _billInvnowRepository.Context.Queryable<BillInvnow, BillPboxrule>((billInvnow, boxrule) => new object[] {
                JoinType.Left,billInvnow.InDocsNo == boxrule.DocsNo,JoinType.Left})
                .With(SqlWith.NoLock)
                .Where((billInvnow, boxrule) => billInvnow.InvStateCode == SXInvState.InvEcecState_In.ToString() && string.IsNullOrEmpty(billInvnow.PreStock) && billInvnow.IsControlpanel == true && billInvnow.Grade == "A")
                .Where(predicate.ToExpression())
                .Select((billInvnow, boxrule) => new CurStockInfo
                {
                    SKU = boxrule.SkuCode,
                    MatName = billInvnow.MatName,
                    MatCode = billInvnow.MatCode,
                    DocNo = billInvnow.InDocsNo,
                    WbGroupCode = billInvnow.WbGroupCode,
                    Wind = billInvnow.Wind,
                    HWType = billInvnow.HWTypeCode,
                    //IsTorsChk = billInvnow.IsTorsChk.ToString(),
                    IsBlack = billInvnow.IsBlack.ToString(),
                    //Rule = boxrule.PackRule,
                    Grade = billInvnow.Grade,
                    ContGrpBarCode = billInvnow.ContGrpBarCode,
                    ProductTime = billInvnow.ProductTime
                }).MergeTable().ToList();

            //var pagelists = queryable.ToPage(pagination);
            var lists = CurStockInfoLists.Distinct(new CompareStock());
            //var lists = pagelists.Result ?? new List<CurStockInfo>().Distinct(new CompareStock());
            //var lists = queryable.ToList().Distinct(new CompareStock());

            var list = new List<CurStockInfo>();
            //var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).ToList();
            //var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).LeftJoin(queryable, (billPboxrule, list) => billPboxrule.DocsNo == list.DocNo)
            //           .Select((billPboxrule, list) => billPboxrule).ToList();

            var DocNos = lists.Select(o => o.DocNo).ToList();
            var boxlist = _billPboxruleRepository.Queryable().With(SqlWith.NoLock).Where(o => DocNos.Contains(o.DocsNo)).ToList();


            foreach (var item in lists.GroupBy(p => new { p.SKU, p.MatCode, p.WbGroupCode, p.Grade }).ToList())
            {
                list.Add(new CurStockInfo()
                {
                    SKU = item.Key.SKU ?? "",
                    MatName = item.First().MatName,
                    WbGroupCode = item.Key.WbGroupCode ?? "",
                    HaveBlack = new List<string>() {
                       "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" +")",
                       "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" +")",
                       "("+ item.Count(p => p.IsBlack == "1" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R"+"/"+ item.Count(p => p.IsBlack == "1" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" +")",

                    },
                    Grade = item.Key.Grade ?? "",
                    //Rule = item.Key.Rule ?? "",

                    Count = item.Count(p => !string.IsNullOrEmpty(p.ContGrpBarCode)),
                    TodayQty = new List<string>() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddHours(13)) + "L" },
                    YesterdayQty = new List<string>() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-1).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddHours(8)) + "L" },
                    BeforeYesterdayQty = new List<string>() { item.Count(p => p.IsBlack == "0" && p.Wind == "R" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "R", item.Count(p => p.IsBlack == "0" && p.Wind == "L" && p.ProductTime >= DateTime.Now.Date.AddDays(-2).AddHours(13) && p.ProductTime <= DateTime.Now.Date.AddDays(-1).AddHours(8)) + "L" },
                });
            }
            // return list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList();
            //pagelists.Result = list;

            PagedInfo<CurStockInfo> pagelists = new PagedInfo<CurStockInfo>()
            {
                Result = new List<CurStockInfo>()
            };
            pagelists.Result = list.OrderBy(o => o.SKU).ThenBy(o => o.WbGroupCode).ThenBy(o => o.Wind).ThenBy(o => o.HWType).ThenBy(o => o.Grade).ToList();
            pagelists.PageIndex = 1;
            pagelists.PageSize = list.Count;
            pagelists.TotalNum = list.Count;
            return pagelists;
        }
    }
}