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

namespace Edu.Repository.Course
{
    public class RB_Class_CheckRepository : BaseRepository<RB_Class_Check>
    {
        /// <summary>
        /// 获取签到记录列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassCheckListRepository(RB_Class_Check_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.GuestName,'') AS GuestName 
FROM RB_Class_Check AS A LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id 
WHERE 1=1  ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    //builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')>=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
                }
                if (!string.IsNullOrWhiteSpace(query.EndDate))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')<=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
                }
            }

            return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取教室列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassCheckStatistics(RB_Class_Check_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT A.ClassId,A.ClassDate,COUNT(0) AS CheckNum FROM RB_Class_Check AS A WHERE 1=1 AND `Status` =0 and CheckStatus =0 ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
                }
                if (!string.IsNullOrWhiteSpace(query.EndDate))
                {
                    builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
                }
            }
            builder.AppendFormat(" GROUP BY A.ClassId,A.ClassDate ");

            return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
        }





        



        /// <summary>
        /// 班级总人数
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public int GetClassToalPeopleNum(int classId, int groupId)
        {
            string sql = $@"SELECT count(*) AS Num FROM(SELECT OrderGuestId FROM rb_class_check WHERE `Status`=0 AND Group_Id={groupId} AND ClassId ={classId} GROUP BY OrderGuestId)t";
            var obj = ExecuteScalar(sql);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 获取平均上课率
        /// </summary>
        /// <param name="classId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public decimal GetClassAvgTakeClassRate(int classId, int group_Id)
        {
            string sql1 = $@"SELECT COUNT(0) AS TotalNum FROM rb_class_check WHERE `Status`=0 AND Group_Id={group_Id} AND ClassId = {classId};";
            string sql2 = $@"SELECT COUNT(0) AS TotalNum FROM rb_class_check WHERE `Status`=0 AND Group_Id={group_Id} AND CheckStatus =0 AND ClassId = {classId};";
            var obj1 = ExecuteScalar(sql1);
            var obj2 = ExecuteScalar(sql2);
            int Num1 = Convert.ToInt32(obj1 ?? "0"), Num2 = Convert.ToInt32(obj2 ?? "0");
            if (Num1 == 0) { return 0; }
            decimal avgClassRate = Math.Round(Convert.ToDecimal(Num2) / Num1, 2, MidpointRounding.AwayFromZero);
            return avgClassRate;
        }

        /// <summary>
        /// 学员完成学习分钟数
        /// </summary>
        /// <param name="guestIds">学员编号</param>
        /// <returns></returns>
        public List<GuestFinishMinute_ViewModel> GetGuestFinishMinutesRepository(string guestIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT B.OrderGuestId AS GuestId,SUM(IFNULL(B.FinishMinutes,0)) AS FinishMinutes
FROM
(
		SELECT B.ClassDate,A.OrderGuestId
					 ,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(CONCAT('2021-01-01 ',C.StartTime,':00'),'%Y-%m-%d %H:%i')),(DATE_FORMAT(CONCAT('2021-01-01 ',C.EndTime,':00'), '%Y-%m-%d %H:%i'))) AS FinishMinutes
		FROM rb_class_check AS A INNER JOIN rb_class_plan AS B ON A.ClassDate=B.ClassDate AND A.ClassId=B.ClassId AND B.`Status`=0
				 INNER JOIN rb_class_time AS C ON B.ClassPlanId=C.ClassPlanId
		WHERE A.CheckStatus=0 AND A.OrderGuestId IN({0})
) AS B
GROUP BY B.OrderGuestId
", guestIds);
            return Get<GuestFinishMinute_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 班级签到完成学习分钟数
        /// </summary>
        /// <param name="classIds">班级编号</param>
        /// <returns></returns>
        public List<GuestFinishMinute_ViewModel> GetClassFinishMinutesRepository(string classIds)
        {
            string where = "";
            if (!string.IsNullOrEmpty(classIds))
            {
                where += string.Format(" AND A.ClassId IN({0}) ", classIds);
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ClassId,SUM(A.FinishMinutes) AS FinishMinutes FROM
(
    SELECT A.ClassId,A.ClassDate,B.ClassPlanId
    ,TIMESTAMPDIFF(MINUTE,(DATE_FORMAT(CONCAT('2021-01-01 ',C.StartTime,':00'),'%Y-%m-%d %H:%i')),(DATE_FORMAT(CONCAT('2021-01-01 ',C.EndTime,':00'), '%Y-%m-%d %H:%i'))) AS FinishMinutes
    FROM rb_class_check AS A INNER JOIN rb_class_plan AS B ON A.ClassId=B.ClassId AND A.ClassDate=B.ClassDate AND B.`Status`=0
          INNER JOIN rb_class_time AS C ON B.ClassPlanId=C.ClassPlanId
    WHERE 1=1 {0}
    GROUP BY A.ClassId,A.ClassDate,B.ClassPlanId
) AS A 
WHERE 1=1 
GROUP BY A.ClassId
", where);
            return Get<GuestFinishMinute_ViewModel>(builder.ToString()).ToList();
        }



        /// <summary>
        /// 获取教室列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassCheckList(RB_Class_Check_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" SELECT A.* FROM RB_Class_Check AS A WHERE 1=1  ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')>=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
                }
                if (!string.IsNullOrWhiteSpace(query.EndDate))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(A.{0},'%Y-%m-%d')<=DATE_FORMAT('{1}','%Y-%m-%d') ", nameof(RB_Class_Check_ViewModel.ClassDate), query.EndDate);
                }
            }

            return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
        }

    }
}
