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

namespace Edu.Repository.Course
{
    /// <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
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
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.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')>='{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="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
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_time AS time ON A.ClassPlanId=Time.ClassPlanId
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.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 DATE_FORMAT(A.{0},'%y/%m')>='{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="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.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.Status >= 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Plan_ViewModel.Status), (int)query.Status);
                }
                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="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> GetClassPlanList_V2(RB_Class_Plan_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT a.*,c.Teacher_Id,c.ClassName,c.ClassType,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=2  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> 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 A.*,class.ClassName,lp.LessonPlanNum,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
WHERE 1=1 and class.ClassStatus=2 and  t.`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.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 NOT IN(1,4) ");
            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.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.Teacher_Id,A.TeacherName,A.RoomName
       ,SUM(A.minNum) AS ClassMinutes,MIN(A.StartTime) AS  StartTime,MAX(A.EndTime) AS EndTime,A.CompleteProgress
FROM
(
SELECT A.ClassPlanId, A.ClassId,A.ClassDate,A.ClassRoomId,B.ClassName,C.CourseName,B.Teacher_Id,IFNULL(D.TeacherName,'') AS TeacherName,E.RoomName
     ,TIMESTAMPDIFF(MINUTE, CONCAT('2020-12-16',' ',F.StartTime), CONCAT('2020-12-16',' ',F.EndTime)) AS minNum,F.StartTime,F.EndTime,B.CompleteProgress 
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 B.Teacher_Id=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.ClassId ,A.ClassDate,A.ClassRoomId,A.CompleteProgress 
", where.ToString());
            return Get<RB_Class_Plan_ViewModel>(builder.ToString()).ToList();
        }
    }
}