using Edu.Common.Enum;
using Edu.Model.Entity.Course;
using Edu.Model.ViewModel.Course;
using Edu.Model.ViewModel.Reserve;
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 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="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,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="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) 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();

            ////            string sql = $@"
            ////SELECT * from (SELECT IF
            ////	(
            ////		@class = a.ClassId,
            ////	IF
            ////		( @score = a.ClassDate, @rank := @rank, @rank := @colum + 1 ),
            ////		@rank := 1 
            ////	) AS Ranks,
            ////IF
            ////	( @class = a.ClassId, @colum := @colum + 1, @colum := 1 ) AS colums,
            //// 	(@class := a.ClassId) as rr,
            //// (	@score := a.ClassDate) as yy ,
            ////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,
            ////( SELECT @rank := 0, @class := 0, @score := '2020-01-01', @colum := 0 ) r
            ////WHERE 1=1 and class.ClassStatus=2 and  t.`Status`=0 and  a.`Status`=0 ORDER BY a.ClassId,a.ClassDate) as a where 1=1 {builder.ToString()} ";

           // string sql = $@"CALL proc_GetClassPlanRank('{builder.ToString()}')";
           // return Get<RB_Class_Plan_ViewModel>(sql).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) ");
            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.Teacher_Id,A.TeacherName,A.RoomName
       ,SUM(A.minNum) AS ClassMinutes,MIN(A.StartTime) AS  StartTime,MAX(A.EndTime) AS EndTime,A.CompleteProgress,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
(
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,plan.TotalPlanNum  
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  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());
            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 SUM(timestampdiff(MINUTE, Concat(SUBSTRING_INDEX(d.ClassDate, ' ', 1), ' ', c.StartTime), Concat(SUBSTRING_INDEX(d.ClassDate, ' ', 1), ' ', c.EndTime))) / 45

            //    from rb_class_time c left
            //    join rb_class_plan d on c.ClassPlanId = d.ClassPlanId

            //    where d.ClassDate < b.ClassDate and c.ClassId = b.ClassId)
            //from rb_class_plan b where b.ClassPlanId = " + planId + " and b.`Status`= 0";
            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);
            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);
                }
            }


            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 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,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  ", builderClass.ToString());
            }
            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  ", builderReserveClass.ToString());
            }
            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 a.ClassDate asc  ", builderClass.ToString(), builderReserveClass.ToString());
            }
            return GetPage<RB_Class_Plan_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }
        #endregion
    }
}