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

namespace Edu.Repository.Exam
{
    /// <summary>
    /// 试卷发布仓储层
    /// </summary>
    public class RB_Examination_PublishRepository : BaseRepository<RB_Examination_Publish>
    {
        /// <summary>
        /// 获取发布试卷分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Examination_Publish_ViewModel> GetExaminationPublishPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Examination_Publish_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.PaperName,'') AS PaperName
FROM RB_Examination_Publish AS A LEFT JOIN rb_examination_paper AS B ON A.PaperId=B.PaperId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.PaperName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @PaperName ", nameof(RB_Examination_Publish_ViewModel.PaperName));
                    parameters.Add("PaperName", "%" + query.PaperName.Trim() + "%");
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.CreateBy), query.CreateBy);
                }
                if (!string.IsNullOrEmpty(query.QStartTime))
                {
                    builder.AppendFormat(@" AND A.{0}>='{1}' ",nameof(RB_Examination_Publish_ViewModel.ExamStartTime),query.QStartTime);
                }
                if (!string.IsNullOrEmpty(query.QEndTime))
                {
                    builder.AppendFormat(@" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Examination_Publish_ViewModel.ExamEndTime), query.QEndTime);
                }
                if (query.QTestStatus > 0)
                {
                    //未开始
                    if (query.QTestStatus == 1)
                    {
                        builder.AppendFormat(" AND A.{0}>'{1}' ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime), Common.ConvertHelper.FormatTime(DateTime.Now));
                    }
                    //进行中
                    if (query.QTestStatus == 2)
                    {
                        builder.AppendFormat(" AND A.{0}<='{1}' AND '{1}'<=A.{2} ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime)
                            ,Common.ConvertHelper.FormatTime(DateTime.Now)
                            ,nameof(RB_Examination_Publish_ViewModel.ExamEndTime)
                            );
                    }
                    //已结束
                    if (query.QTestStatus == 3)
                    {
                        builder.AppendFormat(" AND A.{0}<'{1}' ", nameof(RB_Examination_Publish_ViewModel.ExamEndTime), Common.ConvertHelper.FormatTime(DateTime.Now));
                    }
                }
            }
            builder.AppendFormat(" ORDER BY A.{0} DESC ", nameof(RB_Examination_Publish_ViewModel.Id));
            return GetPage<RB_Examination_Publish_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 获取发布试卷列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Examination_Publish_ViewModel> GetExaminationPublishListRepository(RB_Examination_Publish_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.PaperName,'') AS PaperName
FROM RB_Examination_Publish AS A LEFT JOIN rb_examination_paper AS B ON A.PaperId=B.PaperId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.PaperName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @PaperName ", nameof(RB_Examination_Publish_ViewModel.PaperName));
                    parameters.Add("PaperName", "%" + query.PaperName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.QPublishIds))
                {
                    builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Examination_Publish_ViewModel.Id), query.QPublishIds);
                }
                if (query.IsQNotNormal == 1)
                {
                    builder.AppendFormat(" AND A.ExamineStatus NOT IN(2,5,6) AND A.ExamEndTime<'{0}' ", Common.ConvertHelper.FormatTime(DateTime.Now));
                }
            }
            return Get<RB_Examination_Publish_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取学生发布试卷分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Examination_Publish_ViewModel> GetGuestExaminationPublishPage(int pageIndex, int pageSize, out long rowsCount, RB_Examination_Publish_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ExamStartTime,A.ExamEndTime,A.ExamineStatus,A.Id,A.PublishPic, C.PaperId,IFNULL(B.PaperName,'') AS PaperName,c.ExamStatus,C.Id AS Exam_Student_Id,C.GuestId 
FROM rb_examination_student AS C INNER JOIN RB_Examination_Publish AS A ON C.PublishId=A.Id
     INNER JOIN rb_examination_paper AS B ON C.PaperId=B.PaperId
		 LEFT JOIN rb_student_orderguest AS D ON C.GuestId=D.Id
WHERE 1=1  and  a.ExamineStatus=2 and b.ExamineStatus=2 
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND c.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.GuestId), query.GuestId);
                }
                if (!string.IsNullOrEmpty(query.QAccountIds))
                {
                    builder.AppendFormat(" AND D.Account_Id IN({0}) ",  query.QAccountIds);
                }
                if (!string.IsNullOrEmpty(query.PaperName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @PaperName ", nameof(RB_Examination_Publish_ViewModel.PaperName));
                    parameters.Add("PaperName", "%" + query.PaperName.Trim() + "%");
                }
                if (query.ExamStatus > 0)
                {
                    if (query.ExamStatus == ExamTestStatusEnum.NotStarted)
                    {
                        builder.AppendFormat(" AND a.{0} > '{1}' ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.FinishStarted)
                    {
                        builder.AppendFormat(" AND a.{0} < '{1}' and a.{2} > '{1}' and c.{3}=1 ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), nameof(RB_Examination_Publish_ViewModel.ExamEndTime), nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.MissedTest)
                    {
                        builder.AppendFormat(" AND a.{0} < '{1}' and c.{2} NOT IN(3,5) ", nameof(RB_Examination_Publish_ViewModel.ExamEndTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.FinishTest)
                    {
                        builder.AppendFormat(" AND c.{0} IN(3,5)", nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                }
            }
            builder.AppendFormat("  order by a.{0} desc ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime));
            return GetPage<RB_Examination_Publish_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_Examination_Publish_ViewModel> GetGuestExaminationPublishPage_V2(int pageIndex, int pageSize, out long rowsCount, RB_Examination_Publish_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.Id,A.ExamStartTime,A.ExamEndTime,A.ExamineStatus,A.PublishPic, 
C.PaperId,IFNULL(B.PaperName,'') AS PaperName,C.Id AS Exam_Student_Id,C.ExamStatus,
C.GuestId,C.TotalScore,CASE WHEN C.ExamEndTime IS NOT NULL THEN TIMESTAMPDIFF(SECOND,C.CreateTime,C.ExamEndTime) ELSE 0 END AS ExamSecondTime,
CASE WHEN C.TotalScore >0 THEN (SELECT COUNT(0) FROM rb_examination_student s2 WHERE s2.TotalScore > C.TotalScore and s2.PublishId = C.PublishId) +1 ELSE -1 END AS MyRank,
(SELECT MAX(TotalScore) FROM rb_examination_student s2 WHERE s2.PublishId = C.PublishId) AS MaxScore
FROM rb_examination_student AS C 
INNER JOIN RB_Examination_Publish AS A ON C.PublishId=A.Id
INNER JOIN rb_examination_paper AS B ON C.PaperId=B.PaperId
WHERE 1=1  and  A.ExamineStatus=2 and B.ExamineStatus=2 
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND c.{0}={1} ", nameof(RB_Examination_Publish_ViewModel.GuestId), query.GuestId);
                }
                if (!string.IsNullOrEmpty(query.PaperName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @PaperName ", nameof(RB_Examination_Publish_ViewModel.PaperName));
                    parameters.Add("PaperName", "%" + query.PaperName.Trim() + "%");
                }
                if (query.ExamStatus > 0)
                {
                    if (query.ExamStatus == ExamTestStatusEnum.NotStarted)
                    {
                        builder.AppendFormat(" AND a.{0} > '{1}' ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.FinishStarted)
                    {
                        builder.AppendFormat(" AND a.{0} < '{1}' and a.{2} > '{1}' and c.{3}=1 ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), nameof(RB_Examination_Publish_ViewModel.ExamEndTime), nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.MissedTest)
                    {
                        builder.AppendFormat(" AND a.{0} < '{1}' and c.{2} NOT IN(3,5) ", nameof(RB_Examination_Publish_ViewModel.ExamEndTime), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                    else if (query.ExamStatus == ExamTestStatusEnum.FinishTest)
                    {
                        builder.AppendFormat(" AND c.{0} IN(3,5)", nameof(RB_Examination_Publish_ViewModel.ExamStatus));
                    }
                }
            }
            builder.AppendFormat("  order by a.{0} desc ", nameof(RB_Examination_Publish_ViewModel.ExamStartTime));
            return GetPage<RB_Examination_Publish_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }
    }
}
