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

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using wms.sqlsugar.model.pt;
using Wms.Screen.DataService.Interface;
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.SqlSugar.ZhongTian.PT;
using BillInvnow = wms.sqlsugar.model.pt.BillInvnow;
using WCS_TaskOld = wms.sqlsugar.model.pt.WCS_TaskOld;


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

        public List<WCS_TaskOld> GetPtEquips(GetEquipsRequest reqEntity)
        {
            var list = _db.Queryable<WCS_TaskOld>().Where(p => p.Status != (int)TaskStatus.Finish && p.Status != (int)TaskStatus.Cancel &&
            (p.Type == TaskType.OutDepot || p.Type == TaskType.EnterDepot) && !string.IsNullOrEmpty(p.Tunnel))
                .SplitTable(tabs => tabs.Take(2))
                .ToList();
            return list;
        }
        public List<TaskInfoDto> GetPtRunWcsTaskInfo(GetWcsTaskInfoRequest request)
        {
            var list = _db.Queryable<WCS_TaskOld>().SplitTable(p => p.Take(2))
                .LeftJoin<BillInvnow>((taskold, now) => taskold.BarCode == now.ContGrpBarCode)
                .Where(taskold => taskold.Status < (int)TaskStatus.Finish)
                .WhereIF(request.TaskType > 0, (taskold, now) => taskold.Type == request.TaskType)
                .WhereIF(request.TaskTypelist != null && request.TaskTypelist.Any(), (taskold, now) => request.TaskTypelist.Contains(taskold.Type))
                .OrderBy((taskold, now) => taskold.AddTime, OrderByType.Desc)
                .Select((taskold, now) => new TaskInfoDto
                {
                    TaskCode = taskold.ID.ToString(),
                    TaskState = taskold.Status == (int)TaskStatus.NewBuild ? "新建" : "执行中",
                    TaskType = taskold.Type == TaskType.EnterDepot ? "入库" : taskold.Type == TaskType.OutDepot ? "出库" : "移动",
                    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> GetPtBillList(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 == 1)
              .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<BaseWarecell> GetLocationList()
        {
            return _db.Queryable<BaseWarecell>().ToList();
        }
        public List<BillInvnow> GetBillInvnowList()
        {
            return _db.Queryable<BillInvnow>().ToList();
        }
        public List<WCS_TaskOld> GeHistoryTaskInfo(GeHistoryTaskInfoRequest request)
        {
            return _db.Queryable<WCS_TaskOld>().SplitTable(tabs => tabs.Take(3)).Where(p => p.Status == 99 && request.BusTypeList.Contains(p.BusType) && request.StartTime <= p.EndTime && p.EndTime <= request.EndTime).ToList();
        }

        public List<ZtLocationUsageReportViewDto> GetPtLocationUsageReportList()
        {
            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();
        }

        public List<BoilerNoMatCodeModel> GetBoilerNoMatCodeQtyList()
        {
           var lists= _db.Queryable<BillInvnow>().Where(o => o.InvStateCode == "InvEcecState_In")
                .Where(it => SqlFunc.Subqueryable<SqlSugar.ZhongTian.PT.SysConfig>().Where(s => s.default5 == it.BoilerNo && s.default1 == "ProductPinKu").Any())
              .GroupBy(x => new { x.BoilerNo, x.MatCode })
              .Select(x => new BoilerNoMatCodeModel
              {
                  BoilerNo = x.BoilerNo,
                  MatCode = x.MatCode,
                  Qty = SqlFunc.AggregateCount(x.MatCode)
              }).OrderBy(x=>x.BoilerNo).OrderBy(x=>x.MatCode).ToList();

            return lists;
        }
    }
}