IHK-Projekt/03_Realisierung/OrdersHost Datenmodell/OrdersHostQueries.cs

using System;
using System.Collections.Generic;
using System.Linq;
using Gebhardt.StoreWare.Wcs.Common.Dao;
using Gebhardt.StoreWare.Wcs.Common.DbAccess.Model;
using Gebhardt.StoreWare.Wcs.Common.DbAccess.Model.Enums;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Internal;
using static Gebhardt.StoreWare.Wcs.Common.Constants;

namespace Gebhardt.StoreWare.Wcs.Common.DbAccess.Queries
{
	public static class OrdersHostQueries
	{
		public static IQueryable<OrdersHost> ByStatus(this IQueryable<OrdersHost> entity, params TransportOrderStatus[] states)
		{
			return entity.Where(o => states.Contains(o.Status));
		}

		public static IQueryable<OrdersHost> ByType(this IQueryable<OrdersHost> entity, params TransportOrderType[] types)
		{
			return entity.Where(o => types.Contains(o.Type));
		}

		public static IQueryable<OrdersHost> ByLeNo(this IQueryable<OrdersHost> entity, string leNo)
		{
			return entity.Where(o => o.LeNo == leNo);
		}

		/// <summary>
		///     select entities with one of the given destinations
		/// </summary>
		/// <returns></returns>
		public static IQueryable<OrdersHost> ByDestination(this IQueryable<OrdersHost> entity, IEnumerable<string> destinations)
		{
			return entity.Where(o => destinations.Contains(o.Destination));
		}

		/// <summary>
		///     select entities which hs not one of the given destinations
		/// </summary>
		/// <returns></returns>
		public static IQueryable<OrdersHost> ExcludeDestination(this IQueryable<OrdersHost> entity, IEnumerable<string> destinations)
		{
			return entity.Where(o => !destinations.Contains(o.Destination));
		}

		/// <summary>
		///     select entities with the given destination
		/// </summary>
		/// <returns></returns>
		public static IQueryable<OrdersHost> ByDestination(this IQueryable<OrdersHost> entity, string destination)
		{
			return entity.Where(o => destination == o.Destination);
		}

		/// <summary>
		///     searches for OH
		/// </summary>
		/// <param name="entity"></param>
		/// <param name="leNo"></param>
		/// <returns></returns>
		public static OrdersHost ActiveByLeNo(this IQueryable<OrdersHost> entity, string leNo)
		{
			return entity
				.ByLeNo(leNo)
				.SingleOrDefault(o => o.Status == TransportOrderStatus.InProgress || o.Status == TransportOrderStatus.InDestinationZone || o.Status == TransportOrderStatus.InSequencer);
		}

		public static IQueryable<OrdersHost> AllActiveByLeNo(this IQueryable<OrdersHost> entity, string leNo)
		{
			return entity
				.ByLeNo(leNo)
				.Where(o => o.Status == TransportOrderStatus.InProgress || o.Status == TransportOrderStatus.InDestinationZone || o.Status == TransportOrderStatus.InSequencer);
		}

		/// <summary>
		///     find - if exists  open orders host entries   (Status: Initial, InProgress, InDestinationZone, Pending)
		/// </summary>
		/// <param name="entity"></param>
		/// <param name="leNo">the le number</param>
		/// <returns>null or an open orders host</returns>
		public static IQueryable<OrdersHost> OpenByLeNo(this IQueryable<OrdersHost> entity, string leNo)
		{
			return entity
				.Where(o => o.LeNo == leNo)
				.Open()
				//Better include this here in case one of the initial orders is started by that (done in many places)
				.ApplyWmsOrdering();
		}

		/// <summary>
		///     find - open orders host entries (Status: Initial, InProgress, InDestinationZone, Pending)
		/// </summary>
		/// <param name="entity"></param>
		/// <returns>null or an open orders host</returns>
		public static IQueryable<OrdersHost> Open(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => TransportOrderStatusGroups.Open.Contains(o.Status));
		}

		public static IQueryable<OrdersHost> Active(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => TransportOrderStatusGroups.Active.Contains(o.Status));
		}

		public static IQueryable<OrdersHost> ActivePendingOrInSequecer(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => TransportOrderStatusGroups.Active.Contains(o.Status) || o.Status == TransportOrderStatus.InSequencer || o.Status == TransportOrderStatus.Pending);
		}

		/// <summary>
		///     Get all destinations for which there are orders host existing in any state
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static List<string> GetAllDestinations(this IQueryable<OrdersHost> entity)
		{
			return entity.Select(oh => oh.Destination).Distinct().ToList();
		}

		/// <summary>
		///     Get all destinations for which there are orders host existing in the given states
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static List<string> GetAllDestinations(this IQueryable<OrdersHost> entity, params TransportOrderStatus[] states)
		{
			return entity.Where(oh => states.Contains(oh.Status)).Select(oh => oh.Destination).Distinct().ToList();
		}

		/// <summary>
		///     Get all destinations for the orders host (maybe precede by an "Open()"!)
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static List<string> GetAllDestinations(this IQueryable<OrdersHost> entity, IEnumerable<string> destinations)
		{
			return entity.Where(oh => destinations.Contains(oh.Destination)).Select(oh => oh.Destination).Distinct().ToList();
		}

		public static IQueryable<OrderCountByDay> GetCountByDate(this IQueryable<OrdersHost> entity, TransportOrderType orderType, IEnumerable<DateTime> dates)
		{
			return entity.Where(oh => oh.Status == TransportOrderStatus.Finished && oh.Created.Date > dates.First().Date && oh.Type == orderType).GroupBy(oh => oh.Created.Date).OrderBy(g => g.Key).Select(r => new OrderCountByDay(r.Count(), r.Key));
		}

		public static IQueryable<OrderCountByDestination> GetCountByDestination(this IQueryable<OrdersHost> entity)
		{
			// This SQL is translated into a Lambda expression
			DateTime today = DateTime.Now.Date;
			DateTime yesterday = today.AddDays(-1);
			return entity.Where(oh => oh.Status == TransportOrderStatus.Finished && oh.Created.Date >= yesterday && oh.Type == TransportOrderType.TransportHost).GroupBy(oh => oh.Destination).OrderBy(g => g.Key).Select(r => new OrderCountByDestination(
				r.Key,
				r.Sum(d => d.Created.Date == today ? 1 : 0),
				r.Sum(d => d.Created.Date == yesterday ? 1 : 0)));
		}

		public static IQueryable<OrdersHost> ExcludeNextEmpty(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => o.LeNo != LeTypeName.NextEmptyMiniloadSmall.ToString() && o.LeNo != LeTypeName.NextEmptyMiniloadBig.ToString());
		}

		public static IQueryable<OrdersHost> OnlyNextEmpty(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => o.LeNo == LeTypeName.NextEmptyMiniloadSmall.ToString() || o.LeNo == LeTypeName.NextEmptyMiniloadBig.ToString());
		}

		/// <summary>
		/// Special WMS ordering that takes the common Sequencer Retrieval Time into account
		/// </summary>
		/// <param name="entity"></param>
		/// <param name="db"></param>
		/// <returns></returns>
		public static IOrderedQueryable<OrdersHost> ApplyWmsOrderingSequencerRetrievalTime(this IQueryable<OrdersHost> entity,
			IWcsDbContext db)
		{
			var query = entity
				.GroupJoin(
					db.OrdersHost
						.GroupBy(xx => xx.IdOrderWmsHead)
						.Select(g => new
						{
							IdOrderWmsHead = g.Key,
							SequencerRetrievalTime = g.Min(y => y.SequencerRetrievalTime)
						}),
					o => o.IdOrderWmsHead,
					m => m.IdOrderWmsHead,
					(o, m) => new { o, m }
				)
				.SelectMany(x => x.m.DefaultIfEmpty(), (x, m) => new { x.o, m })
				.OrderByDescending(x => x.o.IsStolen)
				.ThenByDescending(x => x.o.IsDirectPicking)
				.ThenByDescending(x => x.m.SequencerRetrievalTime != null)
				.ThenBy(x => x.m.SequencerRetrievalTime)
				.ThenBy(x => x.o.Created)
				.Select(x => x.o);
			return (IOrderedQueryable<OrdersHost>) query;
		}

		public static IOrderedQueryable<OrdersHost> ApplyWmsOrdering(this IQueryable<OrdersHost> entity)
		{
			// Only order by Created, as WMS anyways only releases a limited number of orders. This saves us from running into dealocks.
			//Sequencer RetrievalTime is set when box is retrieved from sequencer.
			return entity
		.OrderByDescending(o => o.IsStolen)
		.ThenByDescending(o => o.IsDirectPicking)
		.ThenByDescending(o => o.SequencerRetrievalTime != null)
		.ThenBy(o => o.SequencerRetrievalTime)
		.ThenBy(o => o.Created);
		}

		/// <summary>
		///     Exclude all OHs where the LE is located in the sequencer, unless this order is a cancel order
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static IQueryable<OrdersHost> ExcludeOrdersInSequencer(this IQueryable<OrdersHost> entity)
		{
			//The comented orders are started in Seq_Dispo
			return entity.Where(o => !(o.Le.Status == LeStatus.InStorage && o.Le.Aisle.Type == AisleType.Sequencer) /*|| (o.Source.Contains(WcsNames.SEQ) && o.Destination == MfcAllDestinations.StorageLoop2)*/);
		}

		/// <summary>
		/// Filter by sequenceorders that has been cancelled
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static IQueryable<OrdersHost> ByCancelledSequencerOrder(this IQueryable<OrdersHost> entity)
		{
			return entity.Where(o => o.Source.Contains(WcsNames.SEQ) && o.Destination == MfcAllDestinations.StorageLoop2);
		}
		/// <summary>
		/// Filters by all orders that have been marked for departure by HostBooking (DepartureNotificationHandler)
		/// </summary>
		/// <param name="entity"></param>
		/// <returns></returns>
		public static IQueryable<OrdersHost> IsDepartureReady(this IQueryable<OrdersHost> entity) {
			return entity.Where(o => o.DepartureLocation != null && o.DepartureFlag == true);
		}
	}
}