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);
                }
            }
            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="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Plan_ViewModel> GetClassPlanForProduct(string classIds)
        {
            string sql = $@"SELECT p.ClassId,DAYOFWEEK(p.ClassDate) as WeekDay FROM rb_class_plan p 
INNER JOIN rb_class c on p.ClassId = c.ClassId
WHERE c.ClassStyle =1 AND p.`Status`=0 and p.ClassId in({classIds})  GROUP BY p.ClassId,DAYOFWEEK(p.ClassDate)";
            string sql2 = $@"
SELECT p.ClassId,DAYOFMONTH(p.ClassDate) as WeekDay FROM rb_class_plan p 
INNER JOIN rb_class c on p.ClassId = c.ClassId
WHERE c.ClassStyle =2 AND p.`Status`=0 and p.ClassId in({classIds})  GROUP BY p.ClassId,DAYOFMONTH(p.ClassDate)";

            var list1 = Get<RB_Class_Plan_ViewModel>(sql).ToList();
            var list2 = Get<RB_Class_Plan_ViewModel>(sql2).ToList();
            list1.AddRange(list2);
            return list1;
        }

        /// <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 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
 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.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="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="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
");
            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> 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();
        }
    }
}
