using Edu.Model.Entity.Course;
using Edu.Model.Entity.Grade;
using Edu.Model.ViewModel.Scroll;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VT.FW.DB.Dapper;

namespace Edu.Repository.Scroll
{
    /// <summary>
    /// 排课
    /// </summary>
    public class ScheduleCourseRepository:BaseRepository<RB_Class_Course>
    {
        /// <summary>
        /// 排课计划表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<SchedulePlanResult> GetScheduleCourseStuListRepository(ScheduleQuery query)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.Id AS GuestId,A.OrderId,A.GuestName,A.GuestState,IFNULL(B.EnterID,0) AS EnterID,IFNULL(D.AssistId,0) AS HeadMasterId,B.CourseId,A.TotalHours AS TotalTimes
       ,IFNULL(cs.CourseName,'') AS CourseName,IFNULL(cs.CourseRate,0) AS CourseRate,IFNULL(A.ScheduleStatus,0) AS ScheduleStatus,IFNULL(A.TeachingMethod,0) AS TeachingMethod
       ,IFNULL(A.XGRemark,'') AS XGRemark,A.StopDeadline,IFNULL(A.ScheduleRemark,'') AS ScheduleRemark,C.Student_Id AS StuId
FROM rb_order_guest AS A INNER JOIN RB_Order AS B ON A.OrderId=B.OrderId 
	 INNER JOIN rb_class AS E ON B.ClassId=E.ClassId
     INNER JOIN rb_student_orderguest AS C ON A.Id=C.GuestId
     INNER JOIN RB_Course AS cs ON B.CourseId=cs.CourseId
	 LEFT JOIN rb_student_assist AS D ON (C.Student_Id=D.StuId AND D.AssistType=4)
WHERE 1=1 AND B.OrderState=1  AND B.OrderType=1 AND E.ClassScrollType=2
");
            if (query.QCourseRate == Common.Enum.Course.CourseRateEnum.N5
                || query.QCourseRate == Common.Enum.Course.CourseRateEnum.N4
                || query.QCourseRate == Common.Enum.Course.CourseRateEnum.N3
                || query.QCourseRate == Common.Enum.Course.CourseRateEnum.N2
                || query.QCourseRate == Common.Enum.Course.CourseRateEnum.N1)
            {
                List<int> courseIdList = new List<int>();
                string chapterSql = string.Format(" SELECT DISTINCT CourseId FROM rb_course_chapter WHERE Status=0 AND CourseRate ={0} ", (int)query.QCourseRate);
                List<RB_Course_Chapter> chapterList = base.Get<RB_Course_Chapter>(chapterSql).ToList();
                if (chapterList != null && chapterList.Count > 0)
                {
                    courseIdList.AddRange(chapterList.Select(qitem => qitem.CourseId).ToList());
                }
                builder.AppendFormat(@" AND (B.CourseId IN({0}) OR EXISTS (SELECT 1 FROM rb_order_course WHERE OrderId=A.OrderId AND  CourseId IN({0}))) ", string.Join(",", courseIdList));
                //builder.AppendFormat(" AND EXISTS (SELECT 1 FROM rb_course_chapter WHERE 1=1 AND (CourseId=B.CourseId OR CourseId IN(SELECT CourseId FROM rb_order_course WHERE OrderId=A.OrderId))  AND CourseRate IN({0}))  ", (int)query.QCourseRate);
            }
            else
            {
                builder.AppendFormat(" AND cs.CourseRate IN({0}) ", (int)query.QCourseRate);
            }
            if (query.QHeadMasterId > 0)
            {
                builder.AppendFormat(@" AND D.AssistId={0} ",query.QHeadMasterId);
            }
            if (query.QGuestState > 0)
            {
                builder.AppendFormat(@" AND A.GuestState={0} ", query.QGuestState);
            }
            else
            {
                builder.AppendFormat(@" AND A.GuestState in(1,5,7) ");
            }
            if (query.QScheduleStatus > -1)
            {
                builder.AppendFormat(@" AND A.ScheduleStatus={0} ", query.QScheduleStatus);
            }
            if (query.QTeachingMethod > 0)
            {
                builder.AppendFormat(@" AND A.TeachingMethod={0} ", query.QTeachingMethod);
            }
            if (!string.IsNullOrEmpty(query.QGuestName))
            {
                builder.AppendFormat(" AND A.GuestName LIKE @GuestName ");
                parameters.Add("GuestName", "%" + query.QGuestName + "%");
            }

            builder.AppendFormat(" ORDER BY A.OrderId DESC ");


            var list = Get<SchedulePlanResult>(builder.ToString(), parameters).ToList();
            if (list != null && list.Count > 0)
            {
                string guestIds = string.Join(",", list.Select(qitem => qitem.GuestId).Distinct());
                string courseIds = string.Join(",", list.Select(qitem => qitem.CourseId).Distinct());
 
                StringBuilder appointBuilder = new StringBuilder();
                appointBuilder.AppendFormat(@"
SELECT A.Date,A.AccountId,A.State,A.GuestId,A.LearnCourseId,A.CourseGradeId,A.CourseGradeNo,IFNULL(C.ClassCheckId,0) AS ClassCheckId
       ,IFNULL(C.CurrentDeductionHours,0) AS CurrentDeductionHours,IFNULL(C.AttendanceMethod,0) AS AttendanceMethod,IFNULL(A.CourseSTime,'') AS StartTime
       ,IFNULL(A.CourseETime,'') AS EndTime,IFNULL(A.RoomId,0) AS ClassRoomId,A.Id AS AppointId,A.ShiftSort
FROM rb_scroll_appointment AS A LEFT JOIN rb_class_time AS B ON A.ClassPlanId=B.ClassPlanId
     LEFT JOIN rb_class_check AS C ON B.ClassTimeId=C.ClassTimeId AND A.GuestId=C.OrderGuestId
WHERE 1=1 AND A.State IN(1,2,3,6) AND A.AppointType IN(1,2) 
");
                appointBuilder.AppendFormat(@" AND A.LearnCourseId IN ({0}) ", courseIds);
                appointBuilder.AppendFormat(@" AND A.CourseGradeId IN ({0}) ", (int)query.QCourseRate);
                appointBuilder.AppendFormat(@" AND A.GuestId IN({0}) ", guestIds);

                var courseList = Get<PCourseItem>(appointBuilder.ToString()).ToList();
                foreach (var item in list)
                {
                    item.CourseItems = courseList?.Where(qitem => qitem.GuestId == item.GuestId && qitem.LearnCourseId == item.CourseId)?.ToList() ?? new List<PCourseItem>();
                    if (item.CourseItems != null && item.CourseItems.Count > 0)
                    {
                        item.ClassProgress = item.CourseItems.Sum(qitem => qitem.CurrentDeductionHours);
                    }
                }
            }
            return list;
        }


        /// <summary>
        /// 约课签到管理
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<ScrollAppointmentResult> ScrollAppointmentCheckPageRepository(int pageIndex,int pageSize,out long rowsCount, ScrollAppointmentQuery query)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.Date,A.AccountId,A.State,A.GuestId,g.GuestName,A.LearnCourseId,A.CourseGradeId,A.CourseGradeNo,A.ClassPlanId,A.CourseETime AS EndTime,A.CourseSTime AS StartTime
FROM rb_scroll_appointment AS A LEFT JOIN rb_order_guest AS g ON A.GuestId=g.Id
WHERE 1=1 AND A.State IN(3) AND A.AppointType IN(1,2)
");
            if (query != null)
            {
                if (!string.IsNullOrEmpty(query.StartDate))
                {
                    builder.AppendFormat(@" AND A.Date>='{0}' ",query.StartDate);
                }

                if (!string.IsNullOrEmpty(query.EndDate))
                {
                    builder.AppendFormat(@" AND A.Date<='{0} 23:59:59 ' ", query.StartDate);
                }

                if (!string.IsNullOrEmpty(query.GuestName))
                {
                    builder.AppendFormat(@" AND g.GuestName LIKE @GuestName ");
                    parameters.Add("GuestName", "%" + query.GuestName + "%");
                }
            }

            var list = base.GetPage<ScrollAppointmentResult>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
            return list;
        }

        /// <summary>
        /// 查询学员排课设置结果
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<ScrollPlanShowResult> GetStuPlanShowPageRepository(int pageIndex,int pageSize,out long rowsCount, ScrollPlanShowQuery query)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT s.StuName,sog.Student_Id AS StuId,sog.OrderId,sog.GuestId,o.CourseId,IFNULL(oc.OrderCourseIds,'') AS OrderCourseIds,IFNULL(sps.ShowRetes,'') AS ShowRetes,IFNULL(sps.Id,0) AS PlanShowId
FROM rb_student_orderguest AS sog LEFT JOIN rb_order_guest AS og ON sog.GuestId=og.Id
     LEFT JOIN rb_order AS O ON sog.OrderId=o.OrderId
	 LEFT JOIN rb_student AS s ON sog.Student_Id=s.StuId
	 LEFT JOIN (SELECT OrderId,GROUP_CONCAT(CourseId) AS OrderCourseIds FROM rb_order_course GROUP BY OrderId) AS oc ON sog.OrderId=oc.OrderId
     LEFT JOIN rb_class AS E ON O.ClassId=E.ClassId
     LEFT JOIN rb_scroll_planshow AS sps ON sog.Student_Id=sps.StuId AND sog.OrderId=sps.OrderId AND sog.GuestId=sps.GuestId and sps.Status=0
WHERE 1=1  AND O.OrderState=1  AND O.OrderType=1 AND E.ClassScrollType=2
");
            if (query != null)
            {
                if (!string.IsNullOrEmpty(query.StuName))
                {
                    builder.AppendFormat(@" AND s.StuName LIKE @StuName ");
                    parameters.Add("StuName", "%" + query.StuName.Trim() + "%");
                }
            }
            builder.AppendFormat(" ORDER BY sog.OrderId DESC ");
            var list = GetPage<ScrollPlanShowResult>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
            return list;
        }
    }
}