using Edu.Common.Enum;
using Edu.Common.Enum.Course;
using Edu.Model.Entity.Grade;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Reserve;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB;

namespace Edu.Repository.Grade
{
    /// <summary>
    /// 班级上课计划仓储层
    /// </summary>
    public class RB_Class_PlanRepository : BaseRepository<RB_Class_Plan>
    {
        /// <summary>
        /// 获取班级上课计划列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanListRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon,IFNULL(C.ClassName,'') AS ClassName
FROM RB_Class_Plan AS A LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
     LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
     LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                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);
                }
                if (!string.IsNullOrEmpty(query.QClassPlanIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.QClassPlanIds);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
                }
                if (!string.IsNullOrEmpty(query.QMonth))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
                if (query.IsQRepeat == 1)
                {
                    builder.AppendFormat(" AND IFNULL(A.{0},'')<>'' ", nameof(RB_Class_Plan_ViewModel.RepeatPlanIds));
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取班级上课计划列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanListExtRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon
       ,Time.StartTime,time.EndTime,time.ClassTimeId
       ,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',time.StartTime), CONCAT('2020-12-16',' ',time.EndTime)) AS ClassMinutes
       ,time.TimeHour,c.ClassName,IFNULL(course.CourseName,'') AS CourseName
FROM RB_Class_Plan AS A INNER JOIN rb_class as c ON c.ClassId=A.ClassId
     LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
     LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
     LEFT JOIN rb_class_time AS time ON A.ClassPlanId=Time.ClassPlanId
     LEFT JOIN rb_course AS course ON c.CouseId=course.CourseId
WHERE 1=1 AND C.`Status`=0 AND C.ClassStatus<>3
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.ClassRoomId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
                }
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.TeacherId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                //if (query.School_Id > 0)
                //{
                //    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                //}
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND time.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
                }
                if (!string.IsNullOrEmpty(query.QMonth))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
                if (!string.IsNullOrEmpty(query.QClassDateStr))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QClassDateStr);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取上课计划
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanList_V2(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT a.*,c.Teacher_Id,c.ClassName,c.ClassType,c.ClassHourMinute,T.TeacherName ,cou.CourseName
FROM rb_class_plan as a 
    LEFT JOIN rb_class c on a.ClassId = c.ClassId
    LEFT JOIN rb_teacher AS T ON a.TeacherId=T.TId
LEFT JOIN rb_course as cou on cou.CourseId=c.CouseId
where  a.`Status`=0 and c.ClassStatus in(2,3)  and c.`Status`=0");
            if (query != null)
            {
                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);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.ClassRoomId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
                }
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                if (query.TeacherId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (!string.IsNullOrEmpty(query.Q_TeacherIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.Q_TeacherIds);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取班级上课计划
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanModelRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT a.* 
FROM rb_class_plan as a 
WHERE  a.`Status`=0 ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.TeacherId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (!string.IsNullOrEmpty(query.QClassDateStr))
                {
                    builder.AppendFormat(@" AND DATE_FORMAT(a.ClassDate,'%Y-%m-%d')=DATE_FORMAT('{0}','%Y-%m-%d') ", query.QClassDateStr);
                }
                else
                {
                    builder.AppendFormat(@" AND DATE_FORMAT(a.ClassDate,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
                }
            }
            builder.AppendFormat(@" ORDER BY  ClassDate DESC  ");
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckExistsRepository(RB_Class_Plan_ViewModel model)
        {
            List<WhereHelper> list = new List<WhereHelper>()
            {
                 new WhereHelper (nameof(RB_Class_Plan_ViewModel.ClassId),model.ClassId),
                 new WhereHelper (nameof(RB_Class_Plan_ViewModel.ClassDate),model.ClassDate),
            };
            return base.Exists(list);
        }

        /// <summary>
        /// 批量验证同一班级的上课计划
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="qClassDate"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> BatchCheckExistsRepository(int classId, string qClassDate)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*
FROM rb_class_plan AS A  	  
WHERE A.`Status`=0 AND A.ClassId={0} AND A.ClassDate IN({1})
", classId, qClassDate);
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 老师改变则修改后面的代课老师为新的教师编号
        /// </summary>
        /// <param name="TeacherId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public bool UpdatePlanTeacherIdRepository(int TeacherId, int classId)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
UPDATE rb_class_plan 
SET TeacherId={0} 
WHERE ClassId={1} AND ClassDate>'{2}'
", TeacherId, classId, DateTime.Now.ToString("yyyy-MM-dd"));
            return base.Execute(builder.ToString()) > 0;
        }

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


        /// <summary>
        /// 获取班级最后一次上课日期
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanMaxDataRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.ClassId,MAX(A.ClassDate) AS ClassDate 
FROM rb_class_plan AS A
WHERE 1=1 ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }

                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取班级上课记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanLogPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT a.*,b.Teacher_Id,c.TeacherName 
FROM rb_class_plan as a 
    LEFT JOIN rb_class as b on a.ClassId=b.ClassId
    LEFT JOIN rb_teacher as c on c.TId=a.TeacherId
WHERE  a.`Status`=0 and DATE_FORMAT(a.ClassDate,'%Y-%m-%d')<=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
            if (query != null)
            {
                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);
                //}
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.TeacherId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (query.Status >= 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)query.Status);
                }
            }
            builder.AppendFormat(@" order by  ClassDate desc  ");
            return GetPage<RB_Class_Plan_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取班级累计上课时间（分钟）
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public int GetClassTotalMinute(int classId, int group_Id)
        {
            string sql = $@"SELECT SUM(t.minNum) AS minNum FROM(
SELECT TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',t.StartTime), CONCAT('2020-12-16',' ',t.EndTime)) AS minNum FROM rb_class_plan p
INNER JOIN rb_class_time t on p.ClassPlanId = t.ClassPlanId
WHERE p.`Status`=0 AND p.Group_Id={group_Id} AND p.ClassId ={classId}
)t;
";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取班级上课计划列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetTeacherPlanListRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT * 
FROM 
 (  SELECT (SELECT COUNT(*)  from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks,
          A.*,class.ClassName,lp.LessonPlanNum,lps.LessonPlanSummaryNum,IFNULL(B.RoomName,'') AS RoomName,IFNULL(T.TeacherName,'') AS TeacherName,IFNULL(T.TeacherHead,'') AS UserIcon
    FROM RB_Class_Plan AS A LEFT JOIN rb_class_room AS B ON A.ClassRoomId=B.RoomId
         LEFT JOIN rb_class as class on a.ClassId=class.ClassId
         LEFT JOIN rb_teacher AS T ON A.TeacherId=T.TId
         LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanNum from rb_class_lessonplan where `Status`=0  GROUP BY ClassPlanId)as lp on a.ClassPlanId=lp.ClassPlanId
         LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanSummaryNum from rb_class_lessonplan where `Status`=0  and  ISNULL(Summary)=0 and LENGTH(trim(Summary))>0  GROUP BY ClassPlanId) as lps on a.ClassPlanId=lps.ClassPlanId
    WHERE 1=1 and class.ClassStatus in(1,2,3) and  t.`Status`=0 and  a.`Status`=0 and class.`Status`=0 ORDER BY a.ClassId,a.ClassDate
) AS a 
WHERE 1=1  ");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                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);
                }
                if (query.TeacherId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
                }
                if (!string.IsNullOrEmpty(query.QMonth))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取学生上课计划列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetStudentPlanListRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"
SELECT a.*,c.ClassName,lp.LessonPlanNum,c.ClassHours,b.RoomName,IFNULL(T.TeacherHead,'') AS UserIcon,IFNULL(T.TeacherName,'') AS TeacherName FROM rb_class_plan as a  LEFT JOIN rb_class as c on a.ClassId=c.ClassId
LEFT JOIN rb_class_room as b on a.ClassRoomId=b.RoomId
 LEFT JOIN rb_teacher AS T ON c.Teacher_Id=T.TId 
LEFT JOIN rb_student_orderguest as sog on sog.ClassId=c.ClassId
LEFT JOIN (SELECT ClassPlanId,COUNT(*) as LessonPlanNum from rb_class_lessonplan where `Status`=0  GROUP BY ClassPlanId)as lp on a.ClassPlanId=lp.ClassPlanId
where sog.Account_Id={query.StuId} and c.ClassStatus in(1,2) and c.`Status`=0 and sog.`Status`=0  
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.ClassPlanId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassPlanId), query.ClassPlanId);
                }
                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);
                }
                if (query.Teacher_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Teacher_Id), query.Teacher_Id);
                }
                if (!string.IsNullOrEmpty(query.QClassIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Plan_ViewModel.ClassId), query.QClassIds);
                }
                if (!string.IsNullOrEmpty(query.QMonth))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y/%m')= DATE_FORMAT('{1}-01','%y/%m') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.QMonth);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 老师上课计划统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanStatisticalRepository(RB_Class_Plan_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            where.AppendFormat(" AND B.ClassStatus  IN(1,2,3) ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
                if (query.TeacherId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (query.ClassRoomId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
                }
                if (query.School_Id > -1)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,A.CourseId,A.Teacher_Id,A.TeacherName,A.UserIcon,A.RoomName
       ,SUM(A.minNum) AS ClassMinutes,MIN(A.StartTime) AS  StartTime,MAX(A.EndTime) AS EndTime,A.CompleteProgress,a.TotalPlanNum,A.FinishNum,
(SELECT COUNT(*)  from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM
(
SELECT A.ClassPlanId, A.ClassId,A.ClassDate,A.ClassRoomId,B.ClassName,C.CourseName,c.CourseId,B.Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName
      ,IFNULL(D.TeacherHead,'') AS UserIcon,E.RoomName,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',F.StartTime), CONCAT('2020-12-16',' ',F.EndTime)) AS minNum
      ,F.StartTime,F.EndTime,B.CompleteProgress,plan.TotalPlanNum,finishPlan.TotalPlanNum AS FinishNum
FROM  rb_class_plan AS A INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
      LEFT JOIN rb_course AS C ON B.CouseId=C.CourseId
	  LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
	  LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
      LEFT JOIN rb_class_time F on A.ClassPlanId = F.ClassPlanId
      LEFT JOIN (SELECT ClassId,COUNT(*) as TotalPlanNum from rb_class_plan where `Status`=0 AND ClassDate<='{1}'  GROUP BY ClassId) as finishPlan on finishPlan.ClassId=a.ClassId
      LEFT JOIN (SELECT ClassId,COUNT(*) as TotalPlanNum from rb_class_plan where `Status`=0  GROUP BY ClassId) as plan on plan.ClassId=a.ClassId
WHERE 1=1 AND A.`Status`=0 AND B.`Status`=0 {0}
) AS A
GROUP BY A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId,A.CompleteProgress 
", where.ToString(),Common.ConvertHelper.FormatDate(DateTime.Now));
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 老师上课计划统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanStatisticalForYK(RB_Class_Plan_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                }
                if (query.TeacherId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                }
                if (query.ClassRoomId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
                }
                if (query.School_Id > -1)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.CourseId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.CourseId), query.CourseId);
                }
                if (query.PlanType > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.PlanType), query.PlanType);
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ClassPlanId,A.StuId,A.CourseId,A.ClassDate,A.ClassRoomId,A.CourseName,A.TeacherName,A.UserIcon,A.RoomName
       ,MIN(A.StartTime) AS  StartTime,MAX(A.EndTime) AS EndTime,
(SELECT COUNT(*) from rb_class_plan p inner join rb_scroll_appointment s on p.ClassPlanId = s.ClassPlanId where p.`Status`=0 and p.PlanType =2 and s.StuId ={2} and DATE_FORMAT(p.ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and p.CourseId=a.CourseId) as Ranks
FROM
(
SELECT A.ClassPlanId,sa.StuId, A.CourseId,A.ClassDate,A.ClassRoomId,C.CourseName,IFNULL(D.TeacherName,'') AS TeacherName
      ,IFNULL(D.TeacherHead,'') AS UserIcon,E.RoomName,F.StartTime,F.EndTime
FROM  rb_class_plan AS A 
      INNER JOIN rb_scroll_appointment sa on A.ClassPlanId = sa.ClassPlanId and sa.StuId ={2}
      LEFT JOIN rb_course AS C ON A.CourseId=C.CourseId
	  LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
	  LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
      LEFT JOIN rb_class_time F on A.ClassPlanId = F.ClassPlanId
WHERE 1=1 AND A.`Status`=0 {0}
) AS A
GROUP BY A.ClassPlanId,A.StuId,A.CourseId ,A.ClassDate,A.ClassRoomId 
", where.ToString(), Common.ConvertHelper.FormatDate(DateTime.Now), query.StuId);
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 查询指定计划的上课课时
        /// </summary>
        /// <param name="planId"></param>
        /// <returns></returns>
        public double GetCurrentPlanStudyHoursRepository(int planId)
        {
            string sql = "select (select Count(0) from rb_class_plan b where b.ClassId=a.ClassId and b.ClassDate<a.ClassDate) from rb_class_plan a where ClassPlanId=" + planId + " and `Status`=0";
            object obj = ExecuteScalar(sql);
            return Convert.IsDBNull(obj) ? -1 : Convert.ToDouble(obj);
        }

        #region 老师-我的课表

        /// <summary>
        /// 获取班级上课记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetTeacherPlan(int pageIndex, int pageSize, out long rowsCount, RB_Class_Plan_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder whereReserve = new StringBuilder();
            where.AppendFormat(" AND B.ClassStatus  IN(1,2) and b.`Status`=0  ");
            whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.Status), (int)DateStateEnum.Normal);
            string orderby = " a.ClassDate asc ,a.NewPlanDateTime asc";
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), query.Group_Id);
                    whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')>=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.StartTime);
                    whereReserve.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Reserve_Class_Extend.ClassDate), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    where.AppendFormat(" AND DATE_FORMAT(A.{0},'%y-%m-%d')<=DATE_FORMAT('{1}','%y-%m-%d') ", nameof(RB_Class_Plan_ViewModel.ClassDate), query.EndTime);
                    whereReserve.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Reserve_Class_Extend.ClassDate), query.EndTime);
                }
                if (query.TeacherId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.TeacherId), query.TeacherId);
                    whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.TeacherId), query.TeacherId);
                }
                if (query.ClassRoomId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassRoomId), query.ClassRoomId);
                    whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.ClassRoomId), query.ClassRoomId);
                }
                if (query.School_Id > -1)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.School_Id), query.School_Id);
                    whereReserve.AppendFormat(" AND A.{0}={1} ", nameof(RB_Reserve_Class_Extend.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderBy == 1)
                {
                    orderby = " a.ClassDate desc ,a.NewPlanDateTime asc";
                }
            }


            StringBuilder builder = new StringBuilder();
            StringBuilder builderClass = new StringBuilder();
            StringBuilder builderReserveClass = new StringBuilder();
            builderReserveClass.AppendFormat(@"SELECT 2 as ClassType,A.ReserveClassId as ClassTimeId,a.ClassTime AS NewPlanDateTime ,a.ClassTime as StartTime,a.EndTime,0 as ClassPlanId,0 as ClassId,a.ClassDate,a.ClassRoomId,'试听课' as ClassName,a.ClassContent as CourseName,
a.TeacherId as Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName,IFNULL(E.RoomName,'') AS RoomName,
IFNULL(E.SeatNum,0) AS RoomNum,0 as TotalPlanNum
FROM RB_Reserve_Class AS A LEFT JOIN rb_teacher AS D ON A.TeacherId=D.TId
     LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
WHERE 1=1 {0} ", whereReserve.ToString());

            builderClass.AppendFormat(@"

SELECT 1 as ClassType,F.ClassTimeId,CONCAT( DATE_FORMAT(A.ClassDate,'%Y-%m-%d') ,' ',f.StartTime,':00') AS NewPlanDateTime,F.StartTime,f.EndTime,A.ClassPlanId, A.ClassId,A.ClassDate,A.ClassRoomId,B.ClassName,C.CourseName,
B.Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName,E.RoomName,IFNULL(E.SeatNum,0) AS RoomNum,plan.TotalPlanNum  
FROM   rb_class_time as F INNER JOIN 
rb_class_plan AS A  on F.ClassPlanId=a.ClassPlanId
INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
      LEFT JOIN rb_course AS C ON B.CouseId=C.CourseId
	  LEFT JOIN rb_teacher AS D ON B.Teacher_Id=D.TId
	  LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
LEFT JOIN ((SELECT ClassId,COUNT(*) as TotalPlanNum from rb_class_plan where `Status`=0  GROUP BY ClassId)) as plan on plan.ClassId=a.ClassId
WHERE 1=1 AND A.`Status`=0  {0}
", where.ToString());

            if (query.ClassType == 1)
            {
                builder.AppendFormat(@"SELECT  A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,
(SELECT COUNT(*)   from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM ( {0}    ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId  ORDER BY  {1}", builderClass.ToString(), orderby);
            }
            else if (query.ClassType == 2)
            {
                builder.AppendFormat(@"SELECT  A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,
(SELECT COUNT(*)   from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM (  {0}  ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId  ORDER BY {1} ", builderReserveClass.ToString(), orderby);
            }
            else
            {
                builder.AppendFormat(@"SELECT  A.ClassType,A.ClassTimeId,A.Teacher_Id,A.TeacherName,A.RoomName, a.RoomNum,A.StartTime,A.EndTime,A.ClassPlanId,A.ClassId,A.ClassDate,A.ClassRoomId,A.ClassName,A.CourseName,a.TotalPlanNum,
(SELECT COUNT(*)   from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(a.ClassDate,'%Y-%m-%d') and ClassId=a.ClassId) as Ranks
FROM ( {0} UNION all {1}  ) AS A
GROUP BY a.ClassType,A.ClassTimeId,A.ClassPlanId,A.ClassId ,A.ClassDate,A.ClassRoomId ORDER BY {2}  ", builderClass.ToString(), builderReserveClass.ToString(), orderby);
            }
            return GetPage<RB_Class_Plan_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }
        #endregion

        #region 检查上课计划是否重复
        /// <summary>
        /// 检查上课计划是否存在时间上的交集【HK2021-07-28新增】
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="timeList"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> CheckClassPlanRepository(int classId,List<TimeItem> timeList)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* FROM 
(
	SELECT A.*,B.ClassTimeId,CONCAT(DATE_FORMAT(A.ClassDate,'%Y-%m-%d'),' ',B.StartTime,':00') AS StartTime,CONCAT(DATE_FORMAT(A.ClassDate,'%Y-%m-%d'),' ',B.EndTime,':00') AS EndTime
	FROM rb_class_plan AS A INNER JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
	     INNER JOIN rb_class AS C ON A.ClassId=C.ClassId
	WHERE A.`Status`=0 AND C.Status=0 AND A.ClassDate>='{0}' AND C.ClassStatus IN(2)
) AS A
WHERE 1=1  
", Common.ConvertHelper.FormatDate(DateTime.Now));
            if (classId > 0)
            {
                builder.AppendFormat(" AND A.{0}<>{1} ", nameof(RB_Class_Plan_ViewModel.ClassId), classId);
            }
            if (timeList != null && timeList.Count > 0)
            {
                string tempStr = "";
                int index = 0;
                foreach (var item in timeList)
                {
                    if (index == 0)
                    {
                        tempStr += string.Format(" (NOT (A.StartTime > '{0}' OR A.EndTime < '{1}')) ", item.EndTime, item.StartTime);
                    }
                    else
                    {
                        tempStr += string.Format(" OR (NOT (A.StartTime > '{0}' OR A.EndTime < '{1}')) ", item.EndTime, item.StartTime);
                    }
                 
                    index++;
                }
                if (!string.IsNullOrEmpty(tempStr))
                {
                    builder.AppendFormat(" AND ({0}) ", tempStr);
                }
            }
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 批量添加上课计划
        /// </summary>
        /// <param name="planList"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> BatchInsertClassPlanRepository(List<RB_Class_Plan_ViewModel> planList)
        {
            List<RB_Class_Plan_ViewModel> newPlanList = new List<RB_Class_Plan_ViewModel>();
            bool flag = true;
            if (planList != null && planList.Count > 0)
            {
                StringBuilder builder = new StringBuilder();
                builder.AppendFormat(" INSERT INTO RB_Class_Plan(ClassId,ClassDate,Status,ClassRoomId,Group_Id,School_Id,TeacherId,BeiKeStatus,FanKuiStatus,ParentFanKuiStatus) ");
                builder.AppendFormat(" VALUES ");
                string tempStr = "";
                foreach (var item in planList)
                {
                    tempStr += $",({item.ClassId},'{Common.ConvertHelper.FormatDate(item.ClassDate)}',0,{item.ClassRoomId},{item.Group_Id},{item.School_Id},{item.TeacherId},0,0,0) ";
                }
                builder.Append(tempStr.TrimStart(','));
                flag = base.Execute(builder.ToString()) > 0;
                if (flag)
                {
                    newPlanList = GetClassPlanListRepository(new RB_Class_Plan_ViewModel() { ClassId = planList[0].ClassId, StartTime = Common.ConvertHelper.FormatDate(DateTime.Now) });
                }
            }
            return newPlanList;
        }

        /// <summary>
        /// 批量更新上课计划冲突的计划编号
        /// </summary>
        /// <param name="planList"></param>
        /// <returns></returns>
        public bool BatchUpdateClassPlanRepository(List<RB_Class_Plan_ViewModel> planList)
        {
            bool flag = true;
            if (planList != null && planList.Count > 0)
            {
                StringBuilder builder = new StringBuilder();
                builder.AppendFormat(" INSERT INTO RB_Class_Plan(ClassPlanId,RepeatPlanIds) ");
                builder.AppendFormat(" VALUES ");
                string tempStr = "";
                foreach (var item in planList)
                {
                    tempStr += $",({item.ClassPlanId},'{item.RepeatPlanIds}') ";
                }
                builder.Append(tempStr.TrimStart(','));
                builder.Append(" ON DUPLICATE KEY UPDATE RepeatPlanIds =VALUES(RepeatPlanIds) ");
                flag = base.Execute(builder.ToString()) > 0;
            }
            return flag;
        }

        /// <summary>
        /// 检查班级上课计划是否存在重复，有重复则更新冲突的编号
        /// </summary>
        /// <param name="ClassId"></param>
        /// <param name="result"></param>
        public void CheckClassPlanRepeatRepository(int ClassId, out bool result)
        {
            result = false;
            List<TimeItem> timeItemList = new List<TimeItem>();
            var newPlanList = GetClassPlanListExtRepository(new RB_Class_Plan_ViewModel()
            {
                ClassId = ClassId,
                StartTime = Common.ConvertHelper.FormatDate(DateTime.Now)
            });
            foreach (var item in newPlanList)
            {
                timeItemList.Add(new TimeItem()
                {
                    StartTime = Common.ConvertHelper.FormatDate(item.ClassDate) + " " + item.StartTime + ":00",
                    EndTime = Common.ConvertHelper.FormatDate(item.ClassDate) + " " + item.EndTime + ":00",
                });
            }
            var repeatList = CheckClassPlanRepository(ClassId, timeItemList);
            var updatePlanList = new List<RB_Class_Plan_ViewModel>();
            foreach (var item in newPlanList)
            {
                var tempRepeatList = repeatList?.Where(qitem => qitem.ClassDate == item.ClassDate)?.ToList();
                List<int> ids = new List<int>();
                if (tempRepeatList != null && tempRepeatList.Count > 0)
                {
                    if (item.TeacherId > 0)
                    {
                        var teacherList = tempRepeatList?.Where(qitem => qitem.TeacherId == item.TeacherId)?.ToList();
                        if (teacherList != null && teacherList.Count > 0)
                        {
                            var tempIds = Common.ConvertHelper.StringToList(string.Join(",", teacherList.GroupBy(qitem => qitem.ClassPlanId).Select(qitem => qitem.Key)));
                            if (tempIds != null && tempIds.Count > 0)
                            {
                                ids.AddRange(tempIds);
                            }
                        }
                    }
                    if (item.ClassRoomId > 0)
                    {
                        var classroomList = tempRepeatList?.Where(qitem => qitem.ClassRoomId == item.ClassRoomId)?.ToList();
                        if (classroomList != null && classroomList.Count > 0)
                        {
                            var tempIds = Common.ConvertHelper.StringToList(string.Join(",", classroomList.GroupBy(qitem => qitem.ClassPlanId).Select(qitem => qitem.Key)));
                            if (tempIds != null && tempIds.Count > 0)
                            {
                                ids.AddRange(tempIds);
                            }
                        }
                    }
                }
                string RepeatPlanIds = "";
                if (ids != null && ids.Count > 0)
                {
                    RepeatPlanIds = string.Join(",", ids);
                    if (!result)
                    {
                        result = true;
                    }
                }
                updatePlanList.Add(new RB_Class_Plan_ViewModel()
                {
                    ClassPlanId = item.ClassPlanId,
                    RepeatPlanIds = RepeatPlanIds,
                });
            }
            if (updatePlanList != null && updatePlanList.Count > 0)
            {
                BatchUpdateClassPlanRepository(updatePlanList);
            }
        }

        /// <summary>
        /// 批量修改教室或者老师
        /// </summary>
        /// <param name="TeacherId"></param>
        /// <param name="ClassRoomId"></param>
        /// <param name="planIds"></param>
        /// <returns></returns>
        public bool UpdateClassPlanTeacherOrClassRoomIdRepository(int TeacherId, int ClassRoomId, string planIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" UPDATE rb_class_plan SET ");
            if (TeacherId > 0)
            {
                builder.AppendFormat(" TeacherId={0} ", TeacherId);
            }
            if (ClassRoomId > 0)
            {
                builder.AppendFormat(" ClassRoomId={0} ", ClassRoomId);
            }
            builder.AppendFormat(" WHERE ClassPlanId IN({0}) ", planIds);
            return base.Execute(builder.ToString()) > 0;
        }


        #endregion

        /// <summary>
        /// 生成上课计划列表
        /// </summary>
        /// <param name="model"></param>
        /// <param name="defaultPlanTimeList"></param>
        /// <param name="finishClassHours"></param>
        /// <param name="BasicMinutes"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> CreateClassPlanRepository(RB_Class_ViewModel model, List<ClassTimeItem> defaultPlanTimeList,decimal finishClassHours,int BasicMinutes,string defaultOpenTime="")
        {
            List<RB_Class_Plan_ViewModel> classPlanList = new List<RB_Class_Plan_ViewModel>();
            if (model.ClassHours > 0 && defaultPlanTimeList != null && defaultPlanTimeList.Count > 0)
            {
                var srartDate = model.OpenTime;
                if (model.OpenTime < DateTime.Now)
                {
                    srartDate = DateTime.Now;
                }
                if (!string.IsNullOrEmpty(defaultOpenTime))
                {
                    srartDate = Convert.ToDateTime(defaultOpenTime);
                }
                //按周排课和按月排课
                if (model.ClassStyle == ClassStyleEnum.Week || model.ClassStyle == ClassStyleEnum.Month)
                {
                    int i = 0;
                    double totalHours = Convert.ToDouble(finishClassHours);
                    while (model.ClassHours > totalHours)
                    {
                        var newDate = srartDate.AddDays(i);
                        foreach (var item in model.WeekDayList)
                        {
                            var planModel = new RB_Class_Plan_ViewModel()
                            {
                                ClassId = model.ClassId,
                                ClassPlanId = 0,
                                ClassRoomId = model.ClassRoomId,
                                Group_Id = model.Group_Id,
                                School_Id = model.School_Id,
                                PlanTimeList = new List<RB_Class_Time_ViewModel>(),
                                TeacherId = model.Teacher_Id,
                                ClassDate = newDate
                            };
                            if (model.ClassStyle == ClassStyleEnum.Week)
                            {
                                switch (item)
                                {
                                    case "1":
                                        if (newDate.DayOfWeek == DayOfWeek.Monday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "2":
                                        if (newDate.DayOfWeek == DayOfWeek.Tuesday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "3":
                                        if (newDate.DayOfWeek == DayOfWeek.Wednesday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "4":
                                        if (newDate.DayOfWeek == DayOfWeek.Thursday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "5":
                                        if (newDate.DayOfWeek == DayOfWeek.Friday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "6":
                                        if (newDate.DayOfWeek == DayOfWeek.Saturday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                    case "7":
                                        if (newDate.DayOfWeek == DayOfWeek.Sunday)
                                        {
                                            var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                            if (timeList != null && timeList.Count > 0)
                                            {
                                                var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                                totalHours += dayHours;
                                                classPlanList.Add(planModel);
                                            }
                                        }
                                        break;
                                }
                            }
                            else if (model.ClassStyle == ClassStyleEnum.Month)
                            {
                                if (newDate.Day == Convert.ToInt32(item))
                                {
                                    var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                                    if (timeList != null && timeList.Count > 0)
                                    {
                                        var dayHours = CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                                        totalHours += dayHours;
                                        classPlanList.Add(planModel);
                                    }
                                }
                            }
                        }
                        i++;
                    }
                }
                //固定日期
                else if (model.ClassStyle == ClassStyleEnum.FixedDate)
                {
                    foreach (var item in model.WeekDayList)
                    {
                        var planModel = new RB_Class_Plan_ViewModel()
                        {
                            ClassId = model.ClassId,
                            ClassPlanId = 0,
                            ClassRoomId = model.ClassRoomId,
                            Group_Id = model.Group_Id,
                            School_Id = model.School_Id,
                            PlanTimeList = new List<RB_Class_Time_ViewModel>(),
                            TeacherId = model.Teacher_Id,
                            ClassDate = Convert.ToDateTime(item)
                        };
                        var timeList = defaultPlanTimeList.Where(qitem => qitem.DateList.Contains(item))?.FirstOrDefault()?.TimeList;
                        if (timeList != null && timeList.Count > 0)
                        {
                            CalcDayClassHoursRepository(timeList, planModel.PlanTimeList, model, BasicMinutes);
                            classPlanList.Add(planModel);
                        }
                    }
                }
                //约课
                else
                {

                }
            }
            return classPlanList;
        }

        /// <summary>
        /// 计算每天课时
        /// </summary>
        /// <param name="timeList"></param>
        /// <param name="classTimeList"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        private double CalcDayClassHoursRepository(List<TimeItem> timeList, List<RB_Class_Time_ViewModel> classTimeList, RB_Class_ViewModel model, int BasicMinutes)
        {
            double dayClassHours = 0;
            double dayMinutes = 0;
            foreach (var sItem in timeList)
            {
                var timeModel = new RB_Class_Time_ViewModel()
                {
                    StartTime = sItem.StartTime,
                    EndTime = sItem.EndTime,
                    TimeHour = sItem.TimeHour,
                };
                classTimeList.Add(timeModel);
                var startDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + sItem.StartTime + ":00");
                var endDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + sItem.EndTime + ":00");
                TimeSpan span = endDate.Subtract(startDate);
                if (span.TotalMinutes > 0)
                {
                    dayMinutes += span.TotalMinutes;
                }
            }
            if (model.ClassHourMinute > 0)
            {
                dayClassHours = dayMinutes / model.ClassHourMinute;
            }
            else
            {
                dayClassHours = dayMinutes / BasicMinutes;
            }
            return dayClassHours;
        }

        /// <summary>
        /// 查询明日还有未备课的老师信息
        /// </summary>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetNotPerparingTeachRepository()
        {
            var sql = @"select b.Id as AccountId,b.WorkUserId,c.TeacherName,Count(0) as ClassCount from rb_class_plan a 
left join rb_account b on a.TeacherId=b.AccountId and b.AccountType=2
left join rb_teacher c on a.TeacherId=c.Tid
left join rb_class d on a.ClassId=d.ClassId
where ClassDate=DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d') and a.BeiKeStatus=0 and a.TeacherId>0 and d.ClassStatus=2 and d.`Status`=0 and a.`Status`=0 and b.WorkUserId is not null
group by TeacherId";

            return Get<RB_Class_Plan_ViewModel>(sql,null).ToList();
        }

        /// <summary>
        /// 获取最近7天上课计划
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="day"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetStudyCourseReview(int classId, int day, int group_Id)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT a.* 
FROM rb_class_plan as a 
WHERE  a.`Status`=0 ");

            if (group_Id > 0)
            {
                builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), group_Id);
            }
            if (classId > 0)
            {
                builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.ClassId), classId);
            }

            builder.AppendFormat(@$" And a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'");
            builder.AppendFormat(@" ORDER BY  ClassDate DESC  limit " + day);
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取最近7天上课计划 （约课）
        /// </summary>
        /// <param name="stuId"></param>
        /// <param name="courseId"></param>
        /// <param name="day"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetStudyCourseReviewForYK(int stuId, int courseId, int day, int group_Id)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT a.* 
FROM rb_class_plan as a 
inner join rb_scroll_appointment sa on a.ClassPlanId = sa.ClassPlanId
WHERE  a.`Status`=0 ");

            if (group_Id > 0)
            {
                builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Group_Id), group_Id);
            }
            if (courseId > 0)
            {
                builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.CourseId), courseId);
            }
            if (stuId > 0) {
                builder.AppendFormat(" AND sa.StuId ={0} ", stuId);
            }

            builder.AppendFormat(@$" and a.PlanType =2 And a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59'");
            builder.AppendFormat(@" ORDER BY  ClassDate DESC  limit " + day);
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取最近的上课计划
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public RB_Class_Plan_ViewModel GetToDayClassPlan(int classId, int groupId)
        {
            string sql = $@"SELECT a.*
FROM rb_class_plan as a 
WHERE  a.`Status`=0 and a.Group_Id ={groupId} and a.ClassId ={classId} 
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59' 
order by a.ClassDate desc limit 1";
            return Get<RB_Class_Plan_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取最近的上课计划
        /// </summary>
        /// <param name="stuId"></param>
        /// <param name="courseId"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public RB_Class_Plan_ViewModel GetToDayClassPlanForYK(int stuId, int courseId, int groupId)
        {
            string sql = $@"SELECT a.*
FROM rb_class_plan as a 
inner join rb_scroll_appointment sa on a.ClassPlanId = sa.ClassPlanId
WHERE  a.`Status`=0 and a.Group_Id ={groupId} and a.PlanType =2 and a.CourseId ={courseId} and sa.StuId ={stuId}
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59' 
order by a.ClassDate desc limit 1";
            return Get<RB_Class_Plan_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取最近的上课计划
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetStudyClassPlanList(int classId, int groupId)
        {
            string sql = $@"
SELECT a.*,(select count(0) from rb_class_plan p where p.`Status`=0 and p.Group_Id ={groupId} and p.ClassId ={classId} and p.ClassDate <= a.ClassDate) as LessonPlanNum
FROM rb_class_plan as a 
WHERE  a.`Status`=0 and a.Group_Id ={groupId} and a.ClassId ={classId} 
and a.ClassDate <='{Common.ConvertHelper.FormatDate(DateTime.Now)} 23:59:59' 
order by a.ClassDate desc";
            return Get<RB_Class_Plan_ViewModel>(sql).ToList();
        }
    }
}
