SHJX_cod/SHJX.Service.Dao/ServiceDataManager.cs

using SqlSugar;
using System.Linq;
using SHJX.Service.Model.Dao;
using System.Collections.Generic;
using SHJX.Service.Common.ReadXML;

namespace SHJX.Service.Dao
{
    public class ServiceDataManager : DataManagerImp
    {
        public ServiceDataManager(ReadConfigUtil config)
        {
            InitData(config);
        }

        #region SY

        /// <summary>
        /// 查询任务状态为正在执行
        /// </summary>
        /// <param name="status"></param>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public EquipmentTask QueryNewTaskFirst(TaskState status, int routeId) =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(item => item.RouteType.Equals("SY"))
                .Where("RouteID=@routeID ", new { routeID = routeId })
                .WhereIF(routeId.Equals(11), "status not in ('4','6')")
                .WhereIF(!routeId.Equals(11), "status= @status", new { status })
                .Clone()
                .First();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="routeId"></param>
        /// <param name="opType"></param>
        /// <returns></returns>
        public int QueryEqualsRouteIdCount(int routeId, string opType = "SY") =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(item => item.RouteType.Equals(opType))
                .Where(item => item.RouteId.Equals(routeId))
                .Where(item => item.Status.Equals(TaskState.Doing))
                .Clone()
                .Count();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public int RouteIdStateEqualsDoing(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(it => it.RouteType.Equals("SY"))
                .Where(it => it.RouteId.Equals(routeId))
                .Where(it => it.Status.Equals(TaskState.Doing))
                .Count();

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool CheckRouteIdNew() =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(it => it.RouteType.Equals("SY"))
                .Where(it => it.RouteId.Equals(1))
                .Where("NOT EXISTS(SELECT * FROM equipment_task WHERE Status != '4' and RouteID = '5' or RouteID = '6' AND RouteType = 'BD')")
                .Where(it => it.Status.Equals(TaskState.New))
                .Any();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        /// 当RouteID为1时 做以下的查询：
        /// <![CDATA[
        /// SELECT * FROM `equipment_task` WHERE RouteType='SY' and RouteID=1 and status in(1,3)  
        /// and EXISTS(SELECT 1 FROM equipment_task WHERE RouteType = 'SY' and RouteID = '4' and RouteStepID = '27' AND Status = '3' AND
        ///  NOT EXISTS(SELECT 1 From equipment_task WHERE RouteType = 'SY' and `Status`in ('3','1') and RouteID>'1' and(RouteID<>'4' or (RouteID= '4' and RouteStepID<>'27'))))
        /// ]]>
        ///
        /// 当RouteID为2时 做以下的查询：
        ///<![CDATA[
        /// SELECT * FROM `equipment_task` WHERE RouteType='SY' and RouteID='2' AND status in(1,3) AND 
        /// ((NOT EXISTS(SELECT 1 From equipment_task WHERE Status IN ('3','1') AND RouteType='SY' AND RouteID>2)) or 
        /// EXISTS(SELECT 1 From equipment_task WHERE  RouteType='SY' and Status=3 AND RouteID='8' and RouteStepID='30'))
        /// ]]>
        /// 
        /// 这里给定1：
        ///<![CDATA[
        /// NOT EXISTS(SELECT * FROM `equipment_task` WHERE RouteType='SY' AND status in (3,1) AND RouteID>1) OR
        /// EXISTS(SELECT 1 FROM equipment_task WHERE RouteType = 'SY' and RouteID = '4' and RouteStepID = '27' AND Status = '3' AND
        /// NOT EXISTS(SELECT 1 From equipment_task WHERE RouteType = 'SY' and `Status`in ('3','1') and RouteID>'1' and(RouteID<>'4' or (RouteID= '4' and RouteStepID<>'27'))))
        /// ]]>
        public bool CheckElseRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(item => item.RouteType.Equals("SY"))
                .Where(item => item.RouteId.Equals(routeId) && new[] { TaskState.New, TaskState.Doing }.Contains(item.Status))
                .WhereIF(routeId.Equals(1), @"(
	NOT EXISTS(SELECT * FROM `equipment_task` WHERE RouteType='SY' AND status in (3,1) AND RouteID>1) 
	OR
	EXISTS
	(
		SELECT 1 FROM equipment_task WHERE RouteType='SY' and  RouteID='4' and RouteStepID='27' AND Status='3' AND 
		NOT EXISTS
		(
			SELECT 1 From equipment_task WHERE RouteType='SY' and `Status`in ('3','1') and RouteID>'1' and (RouteID<>'4' or (RouteID='4' and RouteStepID<>'27'))
		)
	)
	OR
	EXISTS 
	(
		SELECT COUNT(*) From equipment_task WHERE RouteType = 'SY' and Status='1' and RouteID='2' and RouteStepID='1' and NOT EXISTS(SELECT 1 From equipment_task WHERE status='3' and (RouteID<>'4' or (RouteID= '4' and RouteStepID<>'27'))) GROUP BY RouteType HAVING COUNT(*)<24
	)
)")
                .WhereIF(routeId.Equals(2), @"((NOT EXISTS(SELECT 1 From equipment_task WHERE Status IN ('3','1') AND RouteType='SY' AND RouteID>2))  
or EXISTS(SELECT 1 From equipment_task WHERE  RouteType='SY' and Status=3 AND RouteID='9' and RouteStepID='30' 
and NOT EXISTS(SELECT * From equipment_task WHERE RouteType='SY' AND RouteID='7' AND Status='1')))", new { routeID2 = routeId + 1 })
                .WhereIF(routeId.Equals(5), "NOT EXISTS(SELECT * From equipment_task WHERE RouteType='SY' AND RouteID>=9 AND Status in (1,3))")
                .WhereIF(routeId.Equals(10), "NOT EXISTS(SELECT 1 From equipment_task WHERE RouteType='SY' AND RouteID in (9) and status not in (4,6))")
                .Clone()
                .Any();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public List<EquipmentTask> GetTaskByRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(it => it.RouteType.Equals("SY"))
                .Where(it => it.RouteId.Equals(routeId))
                .Where(it => it.Status.Equals(TaskState.New))
                .WhereIF(routeId.Equals(10), it => !it.Status.Equals(TaskState.Finished))
                .Clone()
                ?.ToList();


        /// <summary>
        /// 
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public bool GetLessThanRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .OrderBy(it => it.Priority, OrderByType.Desc)
                .Where(item => item.RouteType.Equals("SY"))
                .Where(item => item.Status.Equals(TaskState.Doing))
                .WhereIF(routeId.Equals(11), "NOT EXISTS(SELECT * FROM equipment_task WHERE Status='3' and RouteID='4' AND RouteStepID='27')")
                .WhereIF(!routeId.Equals(11), item => item.RouteId < routeId)
                .Clone()
                .Any();

        #endregion

        #region Wash

        /// <summary>
        /// 
        /// </summary>
        /// <param name="status"></param>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public EquipmentTask QueryDoingWashTaskFirst(TaskState status, int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("Wash"))
                .Where("RouteID=@routeID ", new { routeID = routeId })
                .WhereIF(routeId.Equals(6), "status not in ('4','6')")
                .WhereIF(!routeId.Equals(6), "status= @status", new { status })
                .Clone()
                ?.First();

        /// <summary>
        /// 检查是否有润洗的任务存在
        /// </summary>
        /// <returns></returns>
        public bool CheckWashTaskIsRunning() =>
            Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("Wash"))
                .Where(item => item.Status.Equals(TaskState.New) || item.Status.Equals(TaskState.Doing))
                .Any();

        /// <summary>
        /// 查找润洗的任务
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public List<EquipmentTask> GetWashTaskByRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(it => it.RouteType.Equals("Wash"))
                .Where(it => it.RouteId.Equals(routeId))
                .WhereIF(routeId.Equals(6), it => !it.Status.Equals(TaskState.Finished))?.ToList();

        #endregion

        #region BD
        public bool CheckSampleTask() =>
            Db.Queryable<EquipmentTask>()
                .Where(it => it.RouteType.Equals("SY"))
                .Where(it => it.RouteId < 3)
                .Where(it => it.Status.Equals(TaskState.Doing))
                .Clone()
                .ToList()
                .Any();

        public List<EquipmentTask> GetBdTaskByRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(it => it.RouteType.Equals("BD"))
                .Where(it => it.RouteId.Equals(routeId))
                .WhereIF(routeId.Equals(9), it => !it.Status.Equals(TaskState.Finished))
                .Clone()
                ?.ToList();

        public bool CheckElseBdRouteId(int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("BD"))
                .Where("RouteID=@routeID and status in(1,3) ", new { routeID = routeId })
                .WhereIF(routeId.Equals(1), "(SELECT COUNT(*) From equipment_task WHERE RouteType='BD' AND RouteID<>1 and status<>4) <1")
                .WhereIF(routeId.Equals(8), "(SELECT COUNT(*) From equipment_task WHERE RouteType='BD' AND RouteID=7) <1")
                .Clone()
                .Any();

        public EquipmentTask QueryDoingBdTaskFirst(TaskState status, int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("BD"))
                .Where("RouteID=@routeID ", new { routeID = routeId })
                .WhereIF(routeId.Equals(9), "status not in (4,6)")
                .WhereIF(!routeId.Equals(9), "status= @status", new { status })
                .Clone()
                ?.First();

        #endregion

        #region
        public EquipmentTask QueryDoingSpTaskFirst(TaskState status, int routeId) =>
            Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("SP"))
                .Where("RouteID=@routeID ", new { routeID = routeId })
                .Where("status= @status", new { status })
                .Clone()
                ?.First();
        #endregion

        /// <summary>
        /// 根据任务优先级查找是否有任务
        /// </summary>
        /// <returns></returns>
        public bool CheckTaskByPriority(int value)
        {
            TaskState[] states = new TaskState[] { TaskState.New, TaskState.Doing };
            return Db.Queryable<EquipmentTask>().Where(item => states.Contains(item.Status) && item.Priority > value).Any();
        }

        public bool GetTaskByStateNew(params string[] routeName)
        {
            return Db.Queryable<EquipmentTask>().Where(item => routeName.Contains(item.RouteType) && (item.Status.Equals(TaskState.New)|| item.Status.Equals(TaskState.Doing))).Any();
        }

        /// <summary>
        /// 这里要改 要所有的都是BD
        /// </summary>
        /// <returns></returns>
        public bool GetBdTaskIsCooling()
        {
            return Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("BD")
                    && item.Status.Equals(TaskState.Doing)
                    && item.RouteId.Equals(7)
                    && item.RouteStepId.Equals(25))
                .Any();
        }
        public bool GetBdTaskIsT_Move_W()
        {
            return Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("SY")
                    && item.Status.Equals(TaskState.New)
                    && item.RouteId.Equals(5)
                    && item.RouteStepId.Equals(28))
                .Any();
        }


        public bool GetSYRouteIDIsNotOne()
        {
            TaskState[] states = new TaskState[] { TaskState.Doing, TaskState.New };
            return Db.Queryable<EquipmentTask>()
                .Where(item => item.RouteType.Equals("SY")
                    && states.Contains(item.Status)
                    && item.RouteId > 5)
                .Any();
        }

        /// <summary>
        /// 查询样品加热
        /// </summary>
        /// <returns></returns>
        public bool QuerySampleHeating() =>
            Db.Queryable<EquipmentTask>().
                Where(item => item.RouteType.Equals("SY")
                && item.RouteId.Equals(4)
                && item.RouteStepId.Equals(27)
                && item.Status.Equals(TaskState.Doing))
                .Any();

        /// <summary>
        /// 查询样品冷却任务
        /// </summary>
        /// <returns></returns>
        /// 这里的任务状态也要为正在执行
        public EquipmentTask QuerySampleCoolingAfter() => 
            Db.Queryable<EquipmentTask>().
                  Where(item => item.RouteType.Equals("SY")
                   && item.RouteId > 9
                   && item.Status.Equals(TaskState.Doing))
                   .First();
    }
}