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

namespace Edu.Repository.Course
{
    /// <summary>
    /// 课程章节仓储层
    /// </summary>
    public class RB_Course_ChapterRepository : BaseRepository<Model.Entity.Course.RB_Course_Chapter>
    {
        /// <summary>
        /// 获取课程章节列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Course_Chapter_ViewModel> GetChapterListRepository(RB_Course_Chapter_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT * FROM rb_course_chapter WHERE 1=1 ");
            builder.AppendFormat(" AND {0}={1}", nameof(RB_Course_Chapter_ViewModel.Status), EnumHelper.ToInt(DateStateEnum.Normal));
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Course_Chapter_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.CourseId > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Course_Chapter_ViewModel.CourseId), query.CourseId);
                }
                if (!string.IsNullOrWhiteSpace(query.CourseIds))
                {
                    builder.AppendFormat(" AND {0} IN({1}) ", nameof(RB_Course_Chapter_ViewModel.CourseId), query.CourseIds);
                }
                if (!string.IsNullOrEmpty(query.QCreateTime))
                {
                    builder.AppendFormat(" AND {0}>='{1}' ", nameof(RB_Course_Chapter_ViewModel.CreateTime), query.QCreateTime);
                }
                if (query.CourseRate > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Course_Chapter_ViewModel.CourseRate), (int)query.CourseRate);
                }
                if (query.ChapterGradeNo > 0)
                {
                    builder.AppendFormat(" AND {0}={1} ", nameof(RB_Course_Chapter_ViewModel.ChapterGradeNo), query.ChapterGradeNo);
                }
                if (!string.IsNullOrEmpty(query.ChapterNo))
                {
                    builder.AppendFormat(" AND {0}='{1}' ", nameof(RB_Course_Chapter_ViewModel.ChapterNo), query.ChapterNo);
                }
                if (query.IsQRoot == 1)
                {
                    builder.AppendFormat(" AND {0}=0 ", nameof(RB_Course_Chapter_ViewModel.ParentId));
                }
            }
            if (query.QOrderBy == 1)
            {
                builder.AppendFormat(" ORDER BY CourseRate ASC,ChapterGradeNo ASC ");
            }
            return Get<RB_Course_Chapter_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取有章节的课程编号
        /// </summary>
        /// <returns></returns>
        public List<RB_Course_Chapter_ViewModel> GetCourseChapterListExtRepository()
        {
            string sql = " SELECT DISTINCT CourseId FROM rb_course_chapter WHERE Status=0 ORDER BY CourseId ";
            return Get<RB_Course_Chapter_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool SetBatchInsertCourseChapterRepository(List<RB_Course_Chapter_ViewModel> param)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("INSERT INTO rb_course_chapter (CourseId, ChapterNo, ChapterName,ChapterContent,ParentId,OpenStatus,Progress,CreateBy,CreateTime,UpdateBy,UpdateTime,Status,School_Id,Group_Id,SortNum,StudyHours,Objectives,Requirement,CourseRate,StudyMinutes,CurrentHours,ChapterGradeNo) VALUES");
            param.ForEach(x =>
            {
                var newChapterNo = x.ChapterNo.Replace(".", "");
                double.TryParse(newChapterNo, out double SortNum);
                builder.Append($"({x.CourseId},'{x.ChapterNo}','{x.ChapterName}','{x.ChapterContent}',{x.ParentId},{x.OpenStatus},{x.Progress},{x.CreateBy},'{x.CreateTime}',{x.UpdateBy},'{x.UpdateTime}',{(int)x.Status},{x.School_Id},{x.Group_Id},{x.SortNum},{x.StudyHours},'{x.Objectives}','{x.Requirement}',{(int)x.CourseRate},{x.StudyMinutes},{x.CurrentHours},{x.ChapterGradeNo}),");
            });
            builder = builder.Remove(builder.Length - 1, 1);
            return base.Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 批量更新编号
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool SetBatchUpdateChapterNoRepository(List<RB_Course_Chapter_ViewModel> param)
        {
            bool flag = false;
            StringBuilder builder = new StringBuilder();

            foreach (var x in param)
            {
                if (x.ChapterId > 0)
                {
                    builder.AppendFormat("; UPDATE rb_course_chapter SET ChapterNo='{0}',ParentId={1},SortNum={2} WHERE ChapterId={3}", x.ChapterNo, x.ParentId, x.SortNum, x.ChapterId);
                }
            }
            if (!string.IsNullOrEmpty(builder.ToString()))
            {
                flag = Execute(builder.ToString()) > 0;
            }
            return flag;
        }


        /// <summary>
        /// 批量插入当前课时
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool SetBatchCurrentHoursRepository(List<ChapterCurrentHoursItem> param)
        {
            bool flag = false;
            try
            {
                StringBuilder builder = new StringBuilder();
                foreach (var item in param)
                {
                    if (item.ChapterId > 0)
                    {
                        builder.AppendFormat(@";UPDATE rb_course_chapter SET CurrentHours={0} WHERE ChapterId={1} ", item.CurrentHours, item.ChapterId);
                    }
                }
                flag = Execute(builder.ToString()) > 0;
            }
            catch (Exception ex)
            {
                Common.Plugin.LogHelper.Write(ex, "SetBatchCurrentHoursRepository::param" + Common.Plugin.JsonHelper.Serialize(param));
            }
            return flag;
        }

        /// <summary>
        /// 批量更新课程等级
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SetBatchUpdateCourseRateRepository(RB_Course_Chapter_ViewModel model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update rb_course_chapter ");
            builder.AppendFormat("set {0}={1}", nameof(RB_Course_Chapter_ViewModel.CourseRate), EnumHelper.ToInt(model.CourseRate));
            builder.AppendFormat(" where {0}={1}", nameof(RB_Course_Chapter_ViewModel.CourseId), model.CourseId);
            builder.AppendFormat(" and {0} like '{1}%'", nameof(RB_Course_Chapter_ViewModel.ChapterNo), model.ChapterNo);
            return Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 批量删除课程章节
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteBatchChpterRepository(RB_Course_Chapter_ViewModel model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("DELETE FROM rb_course_chapter ");
            builder.AppendFormat(" where {0}={1}", nameof(RB_Course_Chapter_ViewModel.CourseId), model.CourseId);
            builder.AppendFormat(" and ({0} like '{1}%' or {2}={3})", nameof(RB_Course_Chapter_ViewModel.ChapterNo), model.ChapterNo, nameof(RB_Course_Chapter_ViewModel.ChapterId), model.ChapterId);
            return Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 查询符合的课程章节
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="currentHours"></param>
        /// <returns></returns>
        public List<RB_Course_Chapter_ViewModel> GetMatchHoursChapterRepository(int courseId, double currentHours)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT *  FROM rb_course_chapter WHERE 1=1 ");
            builder.AppendFormat(" AND {0}={1}", nameof(RB_Course_Chapter_ViewModel.Status), EnumHelper.ToInt(DateStateEnum.Normal));
            builder.AppendFormat(" AND {0}={1}", nameof(RB_Course_Chapter_ViewModel.CourseId), courseId);
            builder.AppendFormat(" AND ({0}='{1}' or {2} like '{3}.%')", nameof(RB_Course_Chapter_ViewModel.ChapterNo), currentHours, nameof(RB_Course_Chapter_ViewModel.ChapterNo), currentHours);
            return Get<RB_Course_Chapter_ViewModel>(builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 查询符合的课程章节最大以及最小
        /// </summary>
        /// <param name="courseIds"></param>
        /// <returns></returns>
        public List<RB_Course_Chapter_ViewModel> GetMaxMinChapterRepository(string courseIds)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT CourseRate,CourseId FROM rb_course_chapter WHERE 1=1");
            builder.AppendFormat(" AND {0} in({1})", nameof(RB_Course_Chapter_ViewModel.CourseId), courseIds);
            builder.AppendFormat(" GROUP BY CourseId,CourseRate ");
            return Get<RB_Course_Chapter_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 查询符合的课程章节
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="currentHours"></param>
        /// <returns></returns>
        public List<RB_Course_Chapter_ViewModel> GetMatchChapterRepository(int courseId, string currentHours)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT * FROM rb_course_chapter  WHERE 1=1 ");
            builder.AppendFormat(" AND {0}={1}", nameof(RB_Course_Chapter_ViewModel.Status), EnumHelper.ToInt(DateStateEnum.Normal));
            builder.AppendFormat(" AND {0}={1}", nameof(RB_Course_Chapter_ViewModel.CourseId), courseId);
            if (!string.IsNullOrWhiteSpace(currentHours))
            {
                builder.AppendFormat(" AND  (");

                int i = 0;
                foreach (var item in currentHours.Split(","))
                {
                    i += 1;
                    builder.AppendFormat("  ({0}='{1}' or {2} like '{3}.%')", nameof(RB_Course_Chapter_ViewModel.ChapterNo), item, nameof(RB_Course_Chapter_ViewModel.ChapterNo), item);
                    if (i != currentHours.Split(",").Length)
                    {
                        builder.AppendFormat(" Or ");
                    }
                }
                builder.AppendFormat("   )");
            }
            return Get<RB_Course_Chapter_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取当前章节对应的最大的等级节点
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="chapterNo"></param>
        /// <param name="courseRate"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public int GetMaxChapterGradeNo(int courseId, string chapterNo, CourseRateEnum courseRate, int group_Id)
        {
            string sql = $@"SELECT MAX(ChapterGradeNo) ChapterGradeNo FROM rb_course_chapter where Group_Id ={group_Id} and CourseId ={courseId} and ChapterNo <={chapterNo} and CourseRate ={(int)courseRate}";
            var robj = ExecuteScalar(sql);
            return robj == null ? 0 : Convert.ToInt32(robj);
        }

        /// <summary>
        /// 更新课程等级对应节点
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="group_Id"></param>
        public void SetInitChapterGradeNoRepository(int courseId, int group_Id)
        {
            string sql = $@"
UPDATE rb_course_chapter cc
INNER JOIN (
                SELECT  C.ChapterId,SUM(c1.StudyMinutes)/45 AS CurrentHours
                FROM rb_course_chapter C JOIN rb_course_chapter AS C1 ON (C.CourseId=C1.CourseId  AND C1.SortNum<=C.SortNum )
                WHERE C.`Status` =0 AND C.CourseId ={courseId} 
                GROUP BY C.ChapterId
          ) cc2 on cc.ChapterId =cc2.ChapterId
INNER JOIN(
            SELECT  C.ChapterId,(CASE WHEN C.CourseRate=c2.CourseRate THEN  COUNT(1) ELSE 0 END )  AS GrageNum
            FROM rb_course_chapter C JOIN rb_course_chapter AS C2 ON (C.CourseId=C2.CourseId  AND C2.SortNum<=C.SortNum AND C.CourseRate=C2.CourseRate)
            WHERE C.`Status` =0 AND C.CourseId ={courseId} 
            GROUP BY C.ChapterId    
          ) AS CC3 on cc.ChapterId =CC3.ChapterId
SET cc.CurrentHours = cc2.CurrentHours,cc.ChapterGradeNo=CC3.GrageNum
WHERE cc.Group_Id ={group_Id} and cc.CourseId ={courseId}";
            Execute(sql);
        }
    }
}