KB-API/Wms.Screen.DataService/Impl/ZhongTianFjDataService.cs

using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using wms.sqlsugar.model.fj;
using Wms.Screen.DataService.Interface;
using Wms.Screen.Dto;
using Wms.Screen.Dto.ZhongTian;
using Wms.Screen.Dto.ZhongTian.Request;
using Wms.Screen.Dto.ZhongTian.Response;
using Wms.Screen.SqlSugar.ZhongTian;
using BaseWarehouse = wms.sqlsugar.model.fj.BaseWarehouse;
using BillBomsetgrp = wms.sqlsugar.model.fj.BillBomsetgrp;
using BillBomsetinfo = wms.sqlsugar.model.fj.BillBomsetinfo;
using BillInvnow = wms.sqlsugar.model.fj.BillInvnow;
using WCS_TaskOld = wms.sqlsugar.model.fj.WCS_TaskOld;


namespace Wms.Screen.DataService.Impl
{
    public class ZhongTianFjDataService : IZhongTianFjDataService
    {
        private SqlSugarClient _db;
        public ZhongTianFjDataService(SqlSugarClient db)
        {
            _db = db;
        }

        public List<WCS_TaskOld> GetFjEquips(GetEquipsRequest reqEntity)
        {
            var list = _db.Queryable<wms.sqlsugar.model.fj.WCS_TaskOld>().With(SqlWith.NoLock).Where(p => p.Status != (int)TaskStatus.Finish && p.Status != (int)TaskStatus.Cancel && !string.IsNullOrEmpty(p.Device))
               .SplitTable(tabs => tabs.Take(2))
               .ToList();
            return list;
        }

        /// <summary>
        /// 跺型当前来料比
        /// </summary>
        /// <returns></returns>
        public List<BomSetGrpCurrentMaterialRateDto> GetBomSetGrpCurrentMaterialRateList(string warehouse)
        {
            var WarehouseCodes = _db.Queryable<BaseWarehouse>().Where(o => o.Code.Contains(warehouse)).Select(o => o.Code).ToList();
            var bomsetquery = _db.Queryable<BillBomsetgrp, BillBomsetinfo>((billBomsetgrp, billBomsetinfo) => new object[] {
                JoinType.Left, billBomsetgrp.Id == billBomsetinfo.BomSetHdrId,
               })
                  //.Where((billBomsetgrp, billBomsetinfo) => billBomsetgrp.IsStop == 0 && billBomsetgrp.IsDelete == 0)
                  .GroupBy((billBomsetgrp, billBomsetinfo) => new { billBomsetgrp.BomCode, billBomsetgrp.Name, billBomsetinfo.MatCode })
                 .Select((billBomsetgrp, billBomsetinfo) => new
                 {
                     SetGrpBomCode = billBomsetgrp.BomCode,
                     SetGrpMatCode = billBomsetinfo.MatCode,
                     SetGrpName = billBomsetgrp.Name,
                     SetGrpQty = SqlFunc.AggregateCount(billBomsetinfo.MatCode)

                 }).Distinct();

            var now = DateTime.Now;
            var tasksquery = _db.Queryable<wms.sqlsugar.model.fj.WCS_TaskOld>().With(SqlWith.NoLock).SplitTable(p => p.Take(2))
                  .Where(o => WarehouseCodes.Contains(o.WarehouseCode))
                  .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)
                 .With(SqlWith.NoLock).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)
                 }).ToList();

            var BomSetGrps = bomsetquery.ToList();// _billBomsetgrprepository.Queryable().Where(o => o.IsStop == 0 && o.IsDelete == 0).ToList();
            foreach (var item in tasks)
            {
                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.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.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.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.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)
            {
                var totalqty = tasks.Where(o => o.BomSetName == item.BomSetName).Sum(o => o.Qty);
                item.DiffQty = item.Qty - (int)Math.Floor((totalqty * item.NormalRate));
            }

            return tasks;
        }

        /// <summary>
        /// 库内满托占比
        /// </summary>
        /// <returns></returns>
        public List<MantuoBarCodeRateDto> GetMantuoBarCodeRate(string warehouse)
        {
            //var WarehouseIds = _db.Queryable<BaseWarehouse>().Where(o => o.Code.Contains(warehouse)).Select(o => o.Id).ToList();

            var predicate = Expressionable.Create<BillInvnow, wms.sqlsugar.model.fj.BaseWarehouse, BaseMatinfo, BillBomsetgrp>();
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => wareHouse.TypeNum == 1);
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.Secondary == false);
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => !SqlFunc.IsNullOrEmpty(billInvnow.BomMatCode));
            predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => billInvnow.InvStateCode.Equals("InvEcecState_In"));
            //predicate = predicate.And((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => WarehouseIds.Contains(billInvnow.WarehouseId));
            var list = _db.Queryable<BillInvnow, BaseWarehouse, BaseMatinfo, BillBomsetgrp>((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new object[] {
                JoinType.Left, billInvnow.WarehouseId == wareHouse.Id,
                JoinType.Left,billInvnow.BomMatCode== baseMatinfo.Code,
                 JoinType.Left,billInvnow.SetGrpCode== billBomsetgrp.Code
               }).Where(predicate.ToExpression())
               .GroupBy((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new { SetGrpCode = billBomsetgrp.Name, billInvnow.BomMatCode,   BomMatName = baseMatinfo.Name })
               .Select((billInvnow, wareHouse, baseMatinfo, billBomsetgrp) => new MantuoBarCodeRateDto
               {
                   SetGrpCode = billBomsetgrp.Name,
                   BomMatCode = billInvnow.BomMatCode,
                   Qty = SqlFunc.AggregateDistinctCount(billInvnow.ContGrpBarCode),
                
                   BomMatName = baseMatinfo.Name,
               }).ToList();

            return list;
        }
        public List<InvSetGrpDto> GetInvSetGrpList()
        {
            var predicate = Expressionable.Create<BillInvnow,   BillBomsetgrp>();
            predicate = predicate.And((billInvnow, billBomsetgrp) => billInvnow.InvStateCode == "InvEcecState_In");
            predicate = predicate.And((billInvnow, billBomsetgrp) => billInvnow.ContGrpType == FJContGrpType.Material);

            
            var list = _db.Queryable<BillInvnow , BillBomsetgrp>((billInvnow,   billBomsetgrp) => new object[] {
                 JoinType.Left,billInvnow.SetGrpCode== billBomsetgrp.Code
               }).Where(predicate.ToExpression())
              .GroupBy((billInvnow, billBomsetgrp) => new { SetGrpCode = billBomsetgrp.Name  })
              .Select((billInvnow,   billBomsetgrp) => new InvSetGrpDto
              {
                  SetGrpCode = billBomsetgrp.Name,                 
                  Qty = SqlFunc.AggregateDistinctCount(billInvnow.ContGrpBarCode),
                  
              }).ToList();
            return list;    
        }

        public List<BaseWarecell> GetLocationList(string warehouse)
        {
            //var WarehouseIds = _db.Queryable<BaseWarehouse>().Where(o => o.Code.Contains(warehouse)).Select(o => o.Id).ToList();
            return _db.Queryable<BaseWarecell>()
                //.Where(o=>SqlFunc.Subqueryable<BaseWarehouse>().Where(s => s.Code.Contains(warehouse) ).Where(s=>  s.Id == o.WarehouseId).Any())
                .ToList();
        }

        public List<TaskInOutDto> GeHistoryTaskInfo(GeFJHistoryTaskInfoRequest request)
        {
            var predicate = Expressionable.Create<WCS_TaskOld>();
            predicate = predicate.And(m => m.Status == 99);
            //predicate = predicate.And(m => SqlFunc.Subqueryable<BaseWarehouse>().Where(s => s.Code.Contains(request.WareHouse)).Where(s => s.Code == m.WarehouseCode).Any());
            predicate = predicate.AndIF(request.StartTime != null && request.StartTime != DateTime.MinValue, m => m.EndTime >= request.StartTime);
            predicate = predicate.AndIF(request.EndTime != null && request.EndTime != DateTime.MinValue, m => m.EndTime <= request.EndTime);
            var sugarQueryable = _db.Queryable<WCS_TaskOld>().With(SqlWith.NoLock).SplitTable(tabs => tabs.Take(3)).Where(predicate.ToExpression())
                 .Select(it => new
                 {
                     Type = it.Type,
                     BusType = it.BusType,
                     CreateTime = it.EndTime.Value.Date
                 })
                 .MergeTable()//将查询结果转成一个表
                 .GroupBy(o => new { o.Type, o.BusType, o.CreateTime })
                 .Select(it => new TaskInOutDto { Count = SqlFunc.AggregateCount(it.Type), Type = it.Type, BusType = it.BusType, CreateTime = it.CreateTime });
            return sugarQueryable.ToList();     
        }

        public List<TaskInfoDto> GetFjRunWcsTaskInfo(GetWcsTaskInfoRequest request)
        {
            var list = _db.Queryable<WCS_TaskInfo, BillInvnow>((wcstask, code) => new object[] {
                JoinType.Left, wcstask.BarCode == code.ContGrpBarCode
                }).With(SqlWith.NoLock)
                .Where(wcstask => wcstask.Status != TaskStatus.Finish && wcstask.Status != TaskStatus.Cancel)
                .WhereIF(request.EquipList != null && request.EquipList.Any(), (wcstask, code) => request.EquipList.Contains(wcstask.AddrFrom) || request.EquipList.Contains(wcstask.AddrTo))
                .WhereIF(request.TaskType > 0, (wcstask, code) => wcstask.Type == request.TaskType)
                .WhereIF(request.TaskTypelist != null && request.TaskTypelist.Any(), (wcstask, code) => request.TaskTypelist.Contains(wcstask.Type))
                .OrderBy((wcstask, code) => wcstask.AddTime, OrderByType.Desc)
                .Select((wcstask, code) => new TaskInfoDto
                {
                    TaskCode = wcstask.ID.ToString(),
                    TaskState = wcstask.Status == TaskStatus.NewBuild ? "新建" : wcstask.Status == TaskStatus.WaitingToExecute ? "待执行" : wcstask.Status == TaskStatus.AGVExecution ? "AGV执行中" : wcstask.Status == TaskStatus.StackerExecution ? "堆垛机执行" : wcstask.Status == TaskStatus.ConveyorExecution ? "输送机执行中" : wcstask.Status == TaskStatus.StackerCompleted ? "堆垛机完成" : "状态类型错误",
                    TaskType = wcstask.Type == TaskType.EnterDepot ? "入库" : wcstask.Type == TaskType.OutDepot ? "出库" : wcstask.Type == TaskType.TransferDepot ? "移库" : "移动",
                    HWBarCode = wcstask.BarCode,
                    HWSpec = code.HWTypeCode,
                    BeginPosition = wcstask.AddrFrom,
                    EndPosition = wcstask.AddrTo,
                    CreatedTime = wcstask.AddTime
                }).Distinct().ToList();
            return list;
        }
        public List<StackDto> GetFjStackInfo(GetWorkPlanBillListRequest reqEntity)
        {
            var list = _db.Queryable<StackDto>().ToList();

            return list;
        }
        public List<ZtLocationUsageReportViewDto> GetFjLocationUsageReportList(string warehouse)
        {
            var list = _db.Queryable<BaseWarecell, BaseWarehouse>((warecell, warehouse) => new object[] {
                JoinType.Left,warecell.WarehouseId == warehouse.Id   })
                .GroupBy((warecell, warehouse) => new
                {
                    warecell.StateNum,
                    warecell.IsStop,
                    warecell.Tunnel,
                    warehouse.Id,
                    warehouse.Name
                }).Select((warecell, warehouse) => new
                {
                    Status = warecell.StateNum,
                    warecell.Tunnel,
                    warecell.IsStop,
                    HouseId = warehouse.Id,
                    warehouse.Name,
                    Total = SqlFunc.AggregateCount(warecell.Tunnel),
                })
               .MergeTable().ToList().OrderBy(o => o.Tunnel);
            int _sort = 1;
            //基表：巷道、货架标识和仓库名称为维度查找总货位
            var baseTemp = list.GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找可用货位：未停用.已锁定也算有效货位
            var useLocation = list.Where(s => s.IsStop == 0).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找锁定货位
            var lockLocation = list.Where(s => s.Status == (int)FJLocationState.LocationState_StockIn || s.Status == (int)FJLocationState.LocationState_StockOut || s.Status == (int)FJLocationState.LocationState_StockMove).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找停用货位
            var stopLocation = list.Where(s => s.IsStop == 1).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找物料货位
            var materialLocation = list.Where(s => s.Status == (int)FJLocationState.LocationState_Full).GroupBy(s => new { s.Tunnel, s.HouseId, s.Name }).Select(m => new { m.Key.Name, m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            var locationUsageReportViewDtos = from basetemp in baseTemp
                                              join uselocation in useLocation on new { basetemp.Tunnel, basetemp.Name } equals new { uselocation.Tunnel, uselocation.Name } into useinfo
                                              join locklocation in lockLocation on new { basetemp.Tunnel, basetemp.Name } equals new { locklocation.Tunnel, locklocation.Name } into lockinfo
                                              from lockinfoif in lockinfo.DefaultIfEmpty()
                                              join stoplocation in stopLocation on new { basetemp.Tunnel, basetemp.Name } equals new { stoplocation.Tunnel, stoplocation.Name } into stopinfo
                                              from stopinfoif in stopinfo.DefaultIfEmpty()
                                              join materiallocation in materialLocation on new { basetemp.Tunnel, basetemp.Name } equals new { materiallocation.Tunnel, materiallocation.Name } into materialinfo
                                              from materialinfoif in materialinfo.DefaultIfEmpty()
                                              orderby basetemp.Name
                                              select new ZtLocationUsageReportViewDto()
                                              {
                                                  Sort = _sort++,
                                                  Tunnel = basetemp.Tunnel.ToString(),
                                                  WarehouseName = basetemp.Name,
                                                  AllLocationTotal = basetemp.Total,
                                                  CanUseLocation = useinfo.FirstOrDefault() == null ? 0 : useinfo.First().Total,
                                                  LockLocation = lockinfoif == null ? 0 : lockinfoif.Total,
                                                  StopLocation = stopinfoif == null ? 0 : stopinfoif.Total,
                                                  MaterilLocation = materialinfoif == null ? 0 : materialinfoif.Total
                                              };

           
            return locationUsageReportViewDtos.OrderBy(p => p.WarehouseName).ToList();
        }

       
    }


    public class IntRange
    {
        public int Start { get; }
        public int End { get; }

        public IntRange(int start, int end)
        {
            Start = start;
            End = end;
        }

        public bool Contains(int number)
        {
            return number >= Start && number <= End;
        }
    }

    public class RangeChecker
    {
        private List<IntRange> ranges;

        public RangeChecker()
        {
            ranges = new List<IntRange>();
        }
        public RangeChecker(List<IntRange> _ranges)
        {
            ranges = _ranges;
        }
        public void AddRange(IntRange range)
        {
            ranges.Add(range);
        }

        public bool IsCodeInRanges(string number)
        {
            if (int.TryParse(number, out var code))
            {
                foreach (var range in ranges)
                {
                    if (range.Contains(code))
                    {
                        return true; // 若整数在任何一个区间内，返回 true
                    }
                }
            }
            return false; // 若整数不在任何一个区间内，返回 false
        }


        public bool IsInRanges(int number)
        {
            foreach (var range in ranges)
            {
                if (range.Contains(number))
                {
                    return true; // 若整数在任何一个区间内，返回 true
                }
            }
            return false; // 若整数不在任何一个区间内，返回 false
        }
    }
}