Cacher is the code snippet organizer for pro developers

using System;
using System.Collections;
using System.Collections.Generic;
using FieldBook.Core.Domain.Employees;
using FieldBook.Core.Domain.Equipments;
using FieldBook.Core.Domain.OperationCenter;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.SqlCommand;
using NHibernate.Transform;

namespace FieldBook.Core.Domain.Queries
{
    public interface IAvailableEmployeesQuery
    {
        IEnumerable GetClockedInEmployees(Guid territoryId);
        IEnumerable<Employee> GetClockedOutEmployees(Guid territoryId);
        IEnumerable<Employee> GetSupervisors();
    }

    public class AvailableEmployeesQuery : IAvailableEmployeesQuery
    {
        private readonly ISession _session;

        public AvailableEmployeesQuery(ISession session)
        {
            _session = session;
        }

        public IEnumerable GetClockedInEmployees(Guid territoryId)
        {
            Employee employeeAlias = null;
            var statusNotComplete =
                QueryOver.Of<DispatchRecord>()
                    .WhereNot(x => x.Status == DispatchStatus.Complete.DisplayName)
                    .JoinQueryOver(x => x.Employees, () => employeeAlias)
                    .Select(x =>employeeAlias.Id);

            Yard yardAlias = null;
           
            var yardInTerritory =
                QueryOver.Of<Territory>()
                    .Where(x => x.Id == territoryId)
                    .JoinQueryOver(x => x.Yards, () => yardAlias)
                    .Select(x => yardAlias.Id);

            return _session.QueryOver<TimeRecord>()
                .Where(x => x.TimeOut == null)
                .WithSubquery.WhereProperty(x => x.Employee).NotIn(statusNotComplete)
                .JoinQueryOver(x => x.Employee, () => employeeAlias)
                .WithSubquery.WhereProperty(x => x.Yard.Id).In(yardInTerritory)
                .Fetch(x => x.Employee).Eager
                .List();
        }

        public IEnumerable<Employee> GetClockedOutEmployees(Guid territoryId)
        {
            var clockedIn =
                QueryOver.Of<TimeRecord>()
                    .Where(x => x.TimeOut == null)
                    .Select(x => x.Employee.Id);
            Yard yardAlias = null;

            var yardInTerritory =
                QueryOver.Of<Territory>()
                    .Where(x => x.Id == territoryId)
                    .JoinQueryOver(x => x.Yards, () => yardAlias)
                    .Select(x => yardAlias.Id);

            return _session.QueryOver<Employee>()
                .WithSubquery.WhereProperty(x => x.Id).NotIn(clockedIn)
                 .WithSubquery.WhereProperty(x => x.Yard.Id).In(yardInTerritory)
                .Future<Employee>();

        }

        public IEnumerable<Employee> GetSupervisors()
        {
            return _session.QueryOver<Employee>()
                 .Where(x => x.IsSupervisor)
                 .Future<Employee>();
        }
    }

    public interface IAvailableEquipmentQuery
    {
        IEnumerable<EquipmentOpCenterDTO> GetEquipmentByTerritory(Guid id);
    }

    public class AvailableEquipmentQuery : IAvailableEquipmentQuery
    {
        private readonly ISession _session;

        public AvailableEquipmentQuery(ISession session)
        {
            _session = session;
        }

        public IEnumerable<Equipment> GetEquipment()
        {
            Equipment equipmentAlias = null;
            var statusNotComplete =
               QueryOver.Of<DispatchRecord>()
                   .WhereNot(x => x.Status == DispatchStatus.Complete.DisplayName)
                   .JoinQueryOver(x => x.Equipment, () => equipmentAlias)
                   .Select(x => equipmentAlias.Id);

            return _session.QueryOver<Equipment>()
                .WithSubquery.WhereProperty(x => x.Id).NotIn(statusNotComplete)
                .List();
        }

        public IEnumerable<EquipmentOpCenterDTO> GetEquipmentByTerritory(Guid id)
        {
            Equipment equipment = null;
            var statusNotComplete =
               QueryOver.Of<DispatchRecord>()
                   .WhereNot(x => x.Status == DispatchStatus.Complete.DisplayName)
                   .JoinQueryOver(x => x.Equipment, () => equipment)
                   .Select(x => equipment.Id);
            
            Yard yardAlias = null;
           
            var yardInTerritory =
                QueryOver.Of<Territory>()
                    .Where(x => x.Id == id)
                    .JoinQueryOver(x => x.Yards, () => yardAlias)
                    .Select(x => yardAlias.Id);

            EquipmentType equipmentTypeAlias = null;
            EquipmentOpCenterDTO equipmentAlias = null;
            return _session.QueryOver<Equipment>()
                .WithSubquery.WhereProperty(x => x.Yard.Id).In(yardInTerritory)
                .WithSubquery.WhereProperty(x => x.Id).NotIn(statusNotComplete)
                .JoinQueryOver(x => x.EquipmentType, () => equipmentTypeAlias)
                .Where(x => x.IsDispatchable)
                .SelectList(list => list
                                        .Select(() => equipmentTypeAlias.Name).WithAlias(() => equipmentAlias.EquipmentTypeName)
                                        .Select(x => x.Name).WithAlias(() => equipmentAlias.Name)
                                        .Select(x => x.Id).WithAlias(() => equipmentAlias.Id))
                .TransformUsing(Transformers.AliasToBean<EquipmentOpCenterDTO>())
                .List<EquipmentOpCenterDTO>();

        }
    }

    public class EquipmentOpCenterDTO
    {

        public String EquipmentTypeName { get; set; }
        public string Name { get; set; }
        public Guid Id { get; set; }
    }

  
}