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

using Dm;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
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 Wms.Screen.Util.Extensions;
using static Dm.net.buffer.ByteArrayBuffer;
using BillInvnow = wms.sqlsugar.model.hj.BillInvnow;

namespace Wms.Screen.DataService.Impl
{
    public class ZhongTianHjDataService : IZhongTianHjDataService
    {
        private SqlSugarClient _db;

        public ZhongTianHjDataService(SqlSugarClient db)
        {
            _db = db;
        }

        public List<WCS_TaskOld> GetHjEquips(GetEquipsRequest reqEntity)
        {
            var list = _db.Queryable<WCS_TaskOld>().Where(p => p.Status != TaskStatus.Finish && p.Status != TaskStatus.Cancel && 
            (p.Type == TaskType.OutDepot || p.Type == TaskType.EnterDepot || p.Type == TaskType.EmptyInit) && !string.IsNullOrEmpty(p.Tunnel))
                .SplitTable(tabs => tabs.Take(1))
                .ToList();
            return list;
        }
        public List<TaskInfoDto> GetHjRunWcsTaskInfo(GetWcsTaskInfoRequest reqEntity)
        {
            //var list = _db.Queryable<WCS_TaskInfo, BillInvnow>((wcstask, code) => new object[] {
            //    JoinType.Left, wcstask.BarCode == code.ContGrpBarCode
            //    })
            //    .Where(wcstask => wcstask.Status != TaskStatus.Finish && wcstask.Status != TaskStatus.Cancel)
            //    //.WhereIF(reqEntity.EquipList != null && reqEntity.EquipList.Any(), (wcstask, code) => reqEntity.EquipList.Contains(wcstask.AddrFrom) || reqEntity.EquipList.Contains(wcstask.AddrTo))
            //    .WhereIF(reqEntity.TaskType > 0, (wcstask, code) => wcstask.Type == reqEntity.TaskType)
            //    .WhereIF(reqEntity.TaskTypelist != null && reqEntity.TaskTypelist.Any(), (wcstask, code) => reqEntity.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.EmptyInit ? "空轮初始化入库" : "移动",
            //        HWBarCode = wcstask.BarCode,
            //        HWSpec = code.HWTypeCode,
            //        BeginPosition = wcstask.AddrFrom,
            //        EndPosition = wcstask.AddrTo,
            //        CreatedTime = wcstask.AddTime,
            //        Tunnel = wcstask.Tunnel,
            //        AddTime = wcstask.AddTime.ToString("HH:mm:ss")
            //    }).Distinct().ToList();

            var list = _db.Queryable<WCS_TaskOld>().SplitTable(p => p.Take(2))
                .LeftJoin<BillInvnow>((taskold, now) => taskold.BarCode == now.ContGrpBarCode)
                .Where(taskold => taskold.Status < TaskStatus.Finish)
                .WhereIF(reqEntity.TaskType > 0, (taskold, now) => taskold.Type == reqEntity.TaskType)
                .WhereIF(reqEntity.TaskTypelist != null && reqEntity.TaskTypelist.Any(), (taskold, now) => reqEntity.TaskTypelist.Contains(taskold.Type))
                .OrderBy((taskold, now) => taskold.AddTime, OrderByType.Desc)
                .Select((taskold, now) => new TaskInfoDto
                {
                    TaskCode = taskold.ID.ToString(),
                    TaskState = taskold.Status == TaskStatus.NewBuild ? "新建" : "执行中",
                    TaskType = taskold.Type == TaskType.EnterDepot ? "入库" : taskold.Type == TaskType.OutDepot ? "出库" : taskold.Type == TaskType.EmptyInit ? "空轮初始化入库" : "移动",
                    HWBarCode = taskold.BarCode,
                    HWSpec = now.HWTypeCode,
                    BeginPosition = taskold.AddrFrom,
                    EndPosition = taskold.AddrTo,
                    CreatedTime = taskold.AddTime,
                    Tunnel = taskold.Tunnel,
                    AddTime = taskold.AddTime.ToString("HH:mm:ss")
                }).Distinct().ToList();

            return list;
        }

        public List<ZTBillDto> GetHjBillList(GetWorkPlanBillListRequest reqEntity)
        {
            var list = _db.Queryable<BillDocsinfo, BillInvnow, BaseMatinfo, BaseWarehouse>
             ((doc, barcode, material, house) => new object[] {
                    JoinType.Left, doc.DocsNo == barcode.InDocsNo,
                    JoinType.Left, barcode.MatId == material.Id,
                    JoinType.Left, doc.WarehouseId == house.Id 
                 })
              .Where((doc, barcode, material, house) => doc.IsStop == 0)
              //.WhereIF(!string.IsNullOrEmpty(reqEntity.WarehouseDocumentType), (doc, barcode, material, house) => doc.TypeNum == int.Parse(reqEntity.WarehouseDocumentType))
              //.WhereIF(reqEntity.WarehouseDocumentTypeList != null && reqEntity.WarehouseDocumentTypeList.Any(), (doc, barcode, material, house) => reqEntity.WarehouseDocumentTypeList.Contains(doc.TypeNum.ToString()))
              .WhereIF(reqEntity.WarehouseDocumentStateList != null && reqEntity.WarehouseDocumentStateList.Any(), (doc, barcode, material, house) => reqEntity.WarehouseDocumentStateList.Contains(doc.StateNum.ToString()))
              //.WhereIF(reqEntity.WarehouseCodeList.Any(), (doc, barcode, material, house) => reqEntity.WarehouseCodeList.Contains(house.Code))
              .Select((doc, barcode, material, house) => new ZTBillDto
              {
                  OutDocumentCode = doc.DocsNo,
                  MaterialName = material.Name,
                  MaterialCode = material.Code,
                  StockQuantity = 0,
                  TypeCode = doc.TypeNum.ToString(),
                  StateCode = doc.StateNum == DocState.DocState_Create.GetHashCode() ? "新建" : "执行中"
              }).Distinct().ToList();

            return list;
        }
        //public List<BaseWareLocation> GetLocationList(GetLocationListRequest reqEntity)
        //{
        //    var list = _db.Queryable<BaseWareLocation, BaseWareZone, BaseWarehouse>
        //      ((location, zone, house) => new object[] {
        //            JoinType.Inner,location.ZoneId == zone.Id,
        //            JoinType.Inner,zone.WareId == house.Id
        //        })
        //     .Where((location, zone, house) => location.LocationType == reqEntity.LocationType)
        //     .WhereIF(!string.IsNullOrEmpty(reqEntity.WareshouseCode), (location, zone, house) => house.Code == reqEntity.WareshouseCode)
        //     .Select((location, zone, house) => location).ToList();
        //    return list;
        //}

        public List<SysConfig> GetSysConfig(string code, string configType)
        {
            return _db.Queryable<SysConfig>().WhereIF(!string.IsNullOrEmpty(code), s => s.Code.Contains(code))
                .WhereIF(!string.IsNullOrEmpty(configType), s => s.SType.Contains(configType)).ToList();
        }
        //public int GetEmtpyLocationCountByTunnel(List<string> tunnels, int size)
        //{
        //    return _db.Queryable<BaseWareLocation>()
        //   .Where((p) => p.LocationLock == LocationLock.LocationLock_None.GetHashCode() &&
        //   p.LocationState == LocationState.LocationState_Empty.GetHashCode() &&
        //   p.LocationType == LocationType.LocationType_StorageLocation.GetHashCode() &&
        //   p.Size >= size
        //   && tunnels.Contains(p.Tunnel.ToString()))
        //   .Count();
        //}
        public int GetRuningInTaskCountByTunnel(int tunnel, string warehousecode, int size)
        {
            var listCount = _db.Queryable<WCS_TaskInfo>().Where(p => p.Type == TaskType.EnterDepot
              && p.Tunnel == tunnel.ToString()
              && p.Status != TaskStatus.Finish
              && p.Status != TaskStatus.Cancel
              && p.WarehouseCode == warehousecode
              ).Count();
            return listCount;
        }
     
        public bool IsHjHouse()
        {
            return _db.Queryable<BaseWarehouse>().Where(p => p.Code == Const.Hjhouse_putong).Any();
        }
        public List<BaseWarecell> GetLocationList()
        {
            return _db.Queryable<BaseWarecell>().ToList();
        }
        public List<BillInvnow> GetBillInvnowList()
        {
            return _db.Queryable<BillInvnow>().ToList();
        }

        public List<TunelCount> GetTunelCountList()
        {
            return _db.Queryable<BillInvnow, BaseWarecell>((billInvnow, warecell) => new object[] {
                JoinType.Left,billInvnow.ContGrpId == warecell.ContGrpId && billInvnow.WarehouseId ==warecell.WarehouseId  ,
                })
                .GroupBy((billInvnow, warecell) => new { billInvnow.ContGrpType, warecell.Tunnel })
                .Select((billInvnow, warecell) => new TunelCount
                {
                    ContGrpType=billInvnow.ContGrpType,
                    Tunnel= warecell.Tunnel,
                    Qty= SqlFunc.AggregateCount(billInvnow.ContGrpType)
                })
              
                .ToList();
        }

        public List<WCS_TaskOld> GeHistoryTaskInfo(GeHistoryTaskInfoRequest request)
        {
            return _db.Queryable<WCS_TaskOld>().SplitTable(tabs => tabs.Take(3)).Where(p => request.BusTypeList.Contains(p.BusType) && request.StartTime <= p.AddTime && p.EditTime <= request.EndTime).ToList();
        }
        public List<ZtLocationUsageReportViewDto> GetHjLocationUsageReportList()
        {
            int _sort = 1;
            var list = _db.Queryable<BaseWarehouse, BaseWarecell, BillInvnow>((wareHouse, location, groupStock) => new object[]
                   {
                   JoinType.Left,wareHouse.Id == location.WarehouseId,
                   JoinType.Left,location.ContGrpId == groupStock.ContGrpId && groupStock.InvStateCode == InvEcecState.InvEcecState_In.ToString()
                   })
                .Where((wareHouse, location, groupStock) => location.TypeNum == LocationType.LocationType_StorageLocation.GetHashCode())
                .GroupBy((wareHouse, location, groupStock) => new
                {
                    location.IsStop,
                    location.Tunnel,
                    location.StateNum,
                    wareHouse.Id,
                    wareHouse.Name,
                    location.ContGrpId,
                    location.Size
                }).Select((wareHouse, location, groupStock) => new
                {
                    Status = location.IsStop,
                    Tunnel = location.Tunnel,
                    LocationLock = location.StateNum,
                    WareHouseId = wareHouse.Id,
                    Name = wareHouse.Name,
                    GroupStockType = location.ContGrpId,
                    Size = location.Size,
                    Total = SqlFunc.AggregateDistinctCount(location.Id)
                }).ToList();

            //基表：巷道和仓库名称为维度查找总货位
            var baseTemp = list.GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找可用货位：未停用.已锁定也算有效货位
            var useLocation = list.Where(s => s.Status == 1).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList(); //&& s.LocationLock == DictionaryConst.LocationLockNoneCode
            //关联表：巷道和仓库名称为维度，查找锁定货位
            var lockLocation = list.Where(s => s.Status == 1 && s.LocationLock != LocationLock.LocationLock_None.GetHashCode()).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找停用货位
            var stopLocation = list.Where(s => s.Status == 0).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找物料货位
            var materialLocation = list.Where(s => s.Status == 1 && s.GroupStockType == 2 && s.LocationLock == LocationLock.LocationLock_None.GetHashCode()).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找容器货位
            var containerLocation = list.Where(s => s.Status == 1 && s.GroupStockType == 1 && s.LocationLock == LocationLock.LocationLock_None.GetHashCode()).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();
            //关联表：巷道和仓库名称为维度，查找小货位数量
            var littleSizeLocation = list.Where(s => s.Status == 1 && s.Size == 1 && s.LocationLock == LocationLock.LocationLock_None.GetHashCode() && s.GroupStockType != 2 && s.GroupStockType != 1).GroupBy(s => new { s.Tunnel, s.WareHouseId, s.Name }).Select(m => new { Name = m.Key.Name, Tunnel = m.Key.Tunnel, Total = m.Sum(n => n.Total) }).ToList();

            var locationUsageReportViewDtos = from basetemp in baseTemp
                                              join uselocation in useLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = uselocation.Tunnel, Name = uselocation.Name } into useinfo
                                              join locklocation in lockLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = locklocation.Tunnel, Name = locklocation.Name } into lockinfo
                                              join stoplocation in stopLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = stoplocation.Tunnel, Name = stoplocation.Name } into stopinfo
                                              join materiallocation in materialLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = materiallocation.Tunnel, Name = materiallocation.Name } into materialinfo
                                              join containerlocation in containerLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = containerlocation.Tunnel, Name = containerlocation.Name } into containerinfo
                                              join littlesizelocation in littleSizeLocation.DefaultIfEmpty() on new { Tunnel = basetemp.Tunnel, Name = basetemp.Name } equals new { Tunnel = littlesizelocation.Tunnel, Name = littlesizelocation.Name } into littlesizeinfo
                                              orderby basetemp.Name + basetemp.Tunnel
                                              select new ZtLocationUsageReportViewDto()
                                              {
                                                  Sort = _sort++,
                                                  Tunnel = basetemp.Tunnel.ToString(),
                                                  WarehouseName = basetemp.Name,
                                                  AllLocationTotal = basetemp.Total,
                                                  CanUseLocation = useinfo.FirstOrDefault() == null ? 0 : useinfo.First().Total,
                                                  LockLocation = lockinfo.FirstOrDefault() == null ? 0 : lockinfo.First().Total,
                                                  StopLocation = stopinfo.FirstOrDefault() == null ? 0 : stopinfo.First().Total,
                                                  MaterilLocation = materialinfo.FirstOrDefault() == null ? 0 : materialinfo.First().Total,
                                                  ContainLocation = containerinfo.FirstOrDefault() == null ? 0 : containerinfo.First().Total,
                                                  LittleSizeLocation = littlesizeinfo.FirstOrDefault() == null ? 0 : littlesizeinfo.First().Total,
                                                  InStates = "启用",
                                                  OutStates = "启用"
                                              };
            return locationUsageReportViewDtos.OrderBy(p => p.WarehouseName).ToList();
        }
    }
}