using Edu.Model.Entity.Course;
using Edu.Model.ViewModel.Course;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Edu.Repository.Course
{
    /// <summary>
    /// 班级上课时间仓储层
    /// </summary>
    public class RB_Class_TimeRepository : BaseRepository<RB_Class_Time>
    {
        /// <summary>
        /// 获取班级上课时间列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Time_ViewModel> GetClassTimeListRepository(RB_Class_Time_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT *
FROM RB_Class_Time
WHERE 1=1
");
            if (query != null)
            {
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Class_Time_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrEmpty(query.QClassPlanIds))
                {
                    builder.AppendFormat(" AND {0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.QClassPlanIds);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Class_Time_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND {0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassId), query.QClassIds);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                //if (query.School_Id > 0)
                //{
                //    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                //}
            }
            return Get<RB_Class_Time_ViewModel>(builder.ToString()).ToList();
        }





        /// <summary>
        /// 获取班级上课时间列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Time_ViewModel> GetClassTimeList_V2(RB_Class_Time_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT  B.*,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.StartTime,':00') AS NewPlanDateTime,
CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00') AS NewEndPlanDateTime
FROM rb_class_time AS B INNER JOIN rb_class_plan AS A ON A.ClassPlanId=B.ClassPlanId WHERE 1=1
");
            if (query != null)
            {
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrEmpty(query.QClassPlanIds))
                {
                    builder.AppendFormat(" AND B.{0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.QClassPlanIds);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND B.{0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassId), query.QClassIds);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                }
            }
            return Get<RB_Class_Time_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 根据上课计划编号删除上课计划
        /// </summary>
        /// <param name="ClassPlanIds"></param>
        /// <returns></returns>
        public bool DeleteClassTimeByPlanIdsRepository(string ClassPlanIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" DELETE FROM RB_Class_Time WHERE ClassPlanId IN({0}) ", ClassPlanIds);
            return base.Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 获取班级上课时间
        /// </summary>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Time_ViewModel> GetClassTimeListRepository(string classIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ClassId,DATE_FORMAT(MIN(A.NewPlanDateTime),'%Y-%m-%d %h:%i')  AS NewPlanDateTime FROM 
(
SELECT A.ClassPlanId,A.ClassId,A.ClassDate,A.`Status`,A.ClassRoomId,A.Group_Id,A.School_Id,A.TeacherId,B.ClassTimeId,B.StartTime,B.EndTime
       ,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.StartTime,':00') AS NewPlanDateTime
FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
) AS A
WHERE 1=1 
");
            if (!string.IsNullOrEmpty(classIds))
            {
                builder.AppendFormat(" AND A.ClassId IN({0}) ", classIds);
            }
            builder.AppendFormat(" GROUP BY A.ClassId ");
            return Get<RB_Class_Time_ViewModel>(builder.ToString()).ToList();
        }




        /// <summary>
        /// 获取班级上课时间列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Time_ViewModel> GetClassTimeList(RB_Class_Time_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT a.*,c.ClassName,b.ClassRoomId,t.TeacherName from rb_class_time as a LEFT JOIN rb_class_plan as b on a.ClassPlanId=b.ClassPlanId 
LEFT JOIN rb_class as c on a.ClassId=c.ClassId
LEFT JOIN rb_teacher as t on b.TeacherId=t.TId
where b.`Status`=0 and DATE_FORMAT(b.ClassDate,'%y/%m/%d')=DATE_FORMAT(NOW(),'%y/%m/%d') and t.`Status`=0  and c.`Status`=0 and c.ClassStatus=2 ");
            if (query != null)
            {
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrEmpty(query.QClassPlanIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassPlanId), query.QClassPlanIds);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Time_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Time_ViewModel.ClassId), query.QClassIds);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                }
            }
            return Get<RB_Class_Time_ViewModel>(builder.ToString()).ToList();
        }


        #region 教师上课挂件查询

        /// <summary>
        /// 获取班级上课时间
        /// </summary>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Time_Extend> GetClassTimeByRoomId(int RoomId, int Group_Id)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"SELECT t.TeacherName,t.TeacherHead,c.ClassHours,c.ClassName,cou.CourseName,lp.LessonPlanId,A.ClassPlanId,A.ClassId,A.`Status`,A.ClassRoomId,A.Group_Id,A.School_Id,A.TeacherId,B.ClassTimeId,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.StartTime,':00') AS NewPlanDateTime,
B.EndTime,B.StartTime,B.SuiPai,B.VideoUrl,B.ClassStatus,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00') AS NewEndPlanDateTime
FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
LEFT JOIN rb_teacher as t on a.TeacherId=t.TId
LEFT JOIN rb_class as c on c.ClassId=a.ClassId
LEFT JOIN rb_course as cou on cou.CourseId=c.CouseId
LEFT JOIN (SELECT ClassPlanId,MAX(LessonPlanId) as LessonPlanId from rb_class_lessonplan where `Status`=0 GROUP BY ClassPlanId) as  lp on lp.ClassPlanId=a.ClassPlanId
where a.`Status`=0 and b.ClassStatus<>2 and c.ClassStatus =2  and a.ClassRoomId={RoomId} and a.Group_Id={Group_Id} and DATE_FORMAT(A.ClassDate,'%Y-%m-%d')>=DATE_FORMAT(NOW(),'%Y-%m-%d') ORDER BY CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00')  asc LIMIT 10");
            return Get<RB_Class_Time_Extend>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取班级上课时间
        /// </summary>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Time_Extend> GetClassTimeByClassId(int ClassId, int Group_Id)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"SELECT t.TeacherName,croom.RoomName,t.TeacherHead,c.ClassHours,c.ClassName,cou.CourseName,lp.LessonPlanId,A.ClassPlanId,A.ClassId,A.`Status`,A.ClassRoomId,A.Group_Id,A.School_Id,A.TeacherId,B.ClassTimeId,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.StartTime,':00') AS NewPlanDateTime,
B.EndTime,B.StartTime,B.SuiPai,B.ClassStatus,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00') AS NewEndPlanDateTime
FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
LEFT JOIN rb_teacher as t on a.TeacherId=t.TId
LEFT JOIN rb_class as c on c.ClassId=a.ClassId
LEFT JOIN rb_course as cou on cou.CourseId=c.CouseId
LEFT JOIN rb_class_room as croom on croom.RoomId=a.ClassRoomId
LEFT JOIN (SELECT ClassPlanId,MAX(LessonPlanId) as LessonPlanId from rb_class_lessonplan where `Status`=0 GROUP BY ClassPlanId) as  lp on lp.ClassPlanId=a.ClassPlanId
where a.`Status`=0 and c.ClassStatus =2  and a.ClassId={ClassId} and a.Group_Id={Group_Id} and DATE_FORMAT(A.ClassDate,'%Y-%m-%d')>=DATE_FORMAT(NOW(),'%Y-%m-%d') ORDER BY CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00')  asc LIMIT 10");
            return Get<RB_Class_Time_Extend>(builder.ToString()).ToList();
        }
        /// <summary>
        /// 获取班级上课时间
        /// </summary>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Time_Extend> GetClassTimeByTId(int TId, int Group_Id)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"SELECT t.TeacherName,croom.RoomName,t.TeacherIcon,c.ClassHours,c.ClassName,cou.CourseName,lp.LessonPlanId,A.ClassPlanId,A.ClassId,A.`Status`,A.ClassRoomId,A.Group_Id,A.School_Id,A.TeacherId,B.ClassTimeId,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.StartTime,':00') AS NewPlanDateTime,
B.EndTime,B.StartTime,B.SuiPai,B.ClassStatus,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00') AS NewEndPlanDateTime
FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
LEFT JOIN rb_teacher as t on a.TeacherId=t.TId
LEFT JOIN rb_class as c on c.ClassId=a.ClassId
LEFT JOIN rb_course as cou on cou.CourseId=c.CouseId
LEFT JOIN rb_class_room as croom on croom.RoomId=a.ClassRoomId
LEFT JOIN (SELECT ClassPlanId,MAX(LessonPlanId) as LessonPlanId from rb_class_lessonplan where `Status`=0 GROUP BY ClassPlanId) as  lp on lp.ClassPlanId=a.ClassPlanId
where a.`Status`=0 and c.ClassStatus =2  and a.TeacherId={TId} and a.Group_Id={Group_Id} and DATE_FORMAT(A.ClassDate,'%Y-%m-%d')>=DATE_FORMAT(NOW(),'%Y-%m-%d') ORDER BY CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',B.EndTime,':00')  asc LIMIT 10");
            return Get<RB_Class_Time_Extend>(builder.ToString()).ToList();
        }

        #endregion
    }
}