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

namespace Edu.Repository.Grade
{
    /// <summary>
    /// 学员签到仓储层
    /// </summary>
    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,IFNULL(C.ClassName,'') AS ClassName,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
       ,IFNULL(F.TeacherName,'') AS TeacherName,IFNULL(B.OrderId,0) AS OrderId,IFNULL(F.TeacherHead,'') AS TeacherHead,ct.StartTime,ct.EndTime
FROM RB_Class_Check AS A LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id 
     LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
	 LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
	 LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
	 LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId
     LEFT JOIN rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
WHERE 1=1 AND A.Status=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 (query.OrderGuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
                }
                if (!string.IsNullOrEmpty(query.QOrderGuestIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.QOrderGuestIds);
                }
                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, int ClassCheckId = 0)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.OrderGuestId AS GuestId,SUM(A.StudyNum) AS FinishMinutes,SUM(A.CurrentDeductionHours) AS FinishClassHours
FROM rb_class_check AS A   
WHERE A.`Status`=0 ");
            if (!string.IsNullOrEmpty(guestIds))
            {
                builder.AppendFormat(" AND A.OrderGuestId IN({0})", guestIds);
            }
            if (ClassCheckId > 0)
            {
                builder.AppendFormat(" AND A.ClassCheckId<>{0} ", ClassCheckId);
            }
            builder.AppendFormat(" GROUP BY A.OrderGuestId ");
            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,SUM(A.CurrentDeductionHours) AS FinishClassHours FROM
(
    SELECT A.ClassId,A.ClassDate,B.ClassPlanId,C.ClassTimeId,a.CurrentDeductionHours
    ,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,C.ClassTimeId
) 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 (query.ClassCheckId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassCheckId), query.ClassCheckId);
                }
                if (query.OrderGuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassTimeIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.Q_ClassTimeIds);
                }
                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);
                }
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrWhiteSpace(query.ClassTimeIds))
                {
                    builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeIds);
                }
            }
            return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取班级签到列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassCheckList_V2(RB_Class_Check_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@" SELECT A.*,sog.Student_Id as StuId FROM RB_Class_Check AS A
inner join rb_student_orderguest sog on A.OrderGuestId = sog.GuestId
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 (query.ClassCheckId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassCheckId), query.ClassCheckId);
                }
                if (query.OrderGuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND sog.Student_Id ={0} ", query.StuId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassTimeIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.Q_ClassTimeIds);
                }
                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);
                }
                if (query.ClassTimeId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeId);
                }
                if (!string.IsNullOrWhiteSpace(query.ClassTimeIds))
                {
                    builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_Check_ViewModel.ClassTimeId), query.ClassTimeIds);
                }
            }
            return Get<RB_Class_Check_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取学生的上课时间
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="guestIds"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassGuestStudentTimeList(int groupId, string guestIds, string xFStartTime, string xFEndTime)
        {
            string where = "";
            if (!string.IsNullOrEmpty(xFStartTime))
            {
                where += $@" and ClassDate >= '{xFStartTime}'";
            }
            if (!string.IsNullOrEmpty(xFEndTime))
            {
                where += $@" and ClassDate <= '{xFEndTime} 23:59:59'";
            }

            string sql = $@"SELECT OrderGuestId, SUM(CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check where `Status`=0 and Group_Id ={groupId} and OrderGuestId in ({guestIds}) {where} group by OrderGuestId";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取订单每月 消费课时
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassStudentHoursListForOrderMonth(int groupId, string orderIds)
        {
            string sql = $@"SELECT g.OrderId,DATE_FORMAT(c.ClassDate,'%Y-%m') as MonthDateStr,SUM(c.CurrentDeductionHours) CurrentDeductionHours FROM rb_order_guest g
left join rb_class_check c on g.Id = c.OrderGuestId
WHERE g.Group_Id ={groupId} and g.`Status`=0 and c.`Status` =0 and g.OrderId in({orderIds})
group by g.OrderId,DATE_FORMAT(c.ClassDate,'%Y-%m');";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        #region 学生出勤管理

        /// <summary>
        /// 获取学生签到数据
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetStudentAttendanceDayStatistics(RB_Class_Check_ViewModel query)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.GuestName,'') AS GuestName,IFNULL(C.ClassName,'') AS ClassName,C.ClassNo,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
,IFNULL(F.TeacherName,'') AS TeacherName,CONCAT(ct.StartTime,'~',ct.EndTime) as TimeBucket
FROM RB_Class_Check AS A 
     left join rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
     LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id 
     LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
	 LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
	 LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
	 LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId
WHERE 1=1 AND A.Status=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 C.{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 (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Class_Check_ViewModel.CouseId), query.CouseId);
                }
                if (query.CheckStatus > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.CheckStatus), query.CheckStatus);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_Check_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (query.OrderGuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
                }
                if (!string.IsNullOrEmpty(query.GuestName))
                {
                    builder.AppendFormat(" AND B.GuestName like @GuestName ");
                    parameters.Add("GuestName", "%" + query.GuestName + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.StartDate))
                {
                    builder.AppendFormat(" AND A.{0} ='{1}' ", nameof(RB_Class_Check_ViewModel.ClassDate), query.StartDate);
                }
            }
            return Get<RB_Class_Check_ViewModel>(builder.ToString(), parameters).ToList();
        }

        #endregion

        #region 报表统计

        /// <summary>
        /// 获取教师时间范围内 累计上课时长
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="userIds"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        public List<RB_Class_Check_ViewModel> GetTeacherHoursList(int groupId, string userIds, string startMonth, string endMonth)
        {
            string sql = $@"SELECT tt.TeacherId,tt.ClassId,SUM(timestampdiff(MINUTE,CONCAT('2021-06-28 ',tt.StartTime),CONCAT('2021-06-28 ',tt.EndTime))) as StudyNum FROM(
SELECT p.TeacherId,p.ClassId,p.ClassTimeId,t.StartTime,t.EndTime FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
WHERE p.`Status`=0 and p.Group_Id ={groupId} and p.TeacherId in ({userIds}) and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
)tt
GROUP BY tt.TeacherId,tt.ClassId;";

            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }


        /// <summary>
        /// 获取学生时间范围内  累计上课时长  +  合同课时单价
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="userIds"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetStudentHoursList(int groupId, string userIds, string startMonth, string endMonth)
        {
            string sql = $@"SELECT q.TeacherId,q.OrderGuestId,q.CurrentDeductionHours,
case when q.ClassHours>0 and q.TotalSub >0 then 
	(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
	else 0 END AS UnitPrice
 FROM (
SELECT tt.TeacherId,tt.OrderGuestId,tt.CurrentDeductionHours
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
 FROM(
SELECT p.TeacherId,p.OrderGuestId,SUM(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
WHERE p.`Status`=0 and p.Group_Id ={groupId} and p.TeacherId in ({userIds}) and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.OrderGuestId
)tt 
LEFT JOIN rb_education_contract ec on tt.OrderGuestId = ec.GuestId and ec.Status <>4
GROUP BY tt.TeacherId,tt.OrderGuestId
)q";

            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取老师课时统计明细
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="teacherId"></param>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetTeacherConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, int group_Id)
        {
            string where = $@" ";
            if (teacherId > 0)
            {
                where += $@" and p.TeacherId ={teacherId}";
            }
            if (schoolId >= 0)
            {
                where += $@" and c.School_Id ={schoolId}";
            }
            if (classId > 0)
            {
                where += $@" and p.ClassId ={classId}";
            }

            string sql = $@"
SELECT p.TeacherId,t2.TeacherName,t2.BaseHourFee,t2.BaseHoursEnabled,t2.EnableTime,c.ClassName,co.CourseName,p.ClassId,c.ClassType,c.ClassNo,s.SName,c.ClassHourMinute,c.CouseId,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,Max(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
LEFT JOIN rb_teacher t2 on p.TeacherId = t2.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_school s on s.SId = c.School_Id
WHERE p.`Status`=0 and p.Group_Id ={group_Id} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
ORDER BY p.ClassDate ASC
";
            return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取老师课时统计明细
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetTeacherConsumptionHoursDetialList(string teacherIds, int schoolId, int classId, string startMonth, string endMonth, int group_Id)
        {
            string where = $@" ";
            if (!string.IsNullOrEmpty(teacherIds) && teacherIds != "-1" && teacherIds != "0")
            {
                where += $@" and p.TeacherId in({teacherIds})";
            }
            if (schoolId >= 0)
            {
                where += $@" and c.School_Id ={schoolId}";
            }
            if (classId > 0)
            {
                where += $@" and p.ClassId ={classId}";
            }

            string sql = $@"
SELECT p.TeacherId,t2.TeacherName,t2.BaseHourFee,t2.BaseHoursEnabled,t2.EnableTime,c.ClassName,co.CourseName,p.ClassId,s.SName,c.ClassType,c.ClassNo,c.ClassHourMinute,c.CouseId,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,Max(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
LEFT JOIN rb_teacher t2 on p.TeacherId = t2.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
left join rb_school s on s.SId = c.School_Id
WHERE p.`Status`=0 and p.Group_Id ={group_Id} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
GROUP BY p.TeacherId,p.ClassId,p.ClassTimeId
ORDER BY p.ClassDate ASC
";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取学生课时统计明细
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="teacherId"></param>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="studentName"></param>
        /// <param name="orderId"></param>
        /// <param name="contractNo"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetStudentConsumptionHoursDetialPageList(int pageIndex, int pageSize, out long count, int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId, int StudentId)
        {
            string where = $@" ";
            if (teacherId > 0)
            {
                where += $@" and p.TeacherId ={teacherId}";
            }
            if (schoolId >= 0)
            {
                where += $@" and c.School_Id ={schoolId}";
            }
            if (classId > 0)
            {
                where += $@" and p.ClassId ={classId}";
            }
            if (!string.IsNullOrEmpty(studentName))
            {
                where += $@" and t2.GuestName like '%{studentName}%'";
            }
            if (orderId > 0)
            {
                where += $@" and t2.OrderId ={orderId}";
            }
            if (StudentId > 0)
            {
                where += $@" and t2.Id ={StudentId}";
            }
            if (!string.IsNullOrEmpty(contractNo))
            {
                where += $@" and ec.ContractNo ='{contractNo}'";
            }

            string sql = $@"
SELECT tt.*,
case when tt.ClassHours>0 and tt.TotalSub >0 then 
	(tt.CourseFee - tt.DiscountMoney / tt.TotalSub * tt.CourseFee) /tt.ClassHours
	else 0 END AS UnitPrice
 FROM (
SELECT p.OrderGuestId,p.CurrentDeductionHours,t2.GuestName,p.TeacherId,t3.TeacherName,t2.OrderId,ec.ContractNo,c.ClassName,c.ClassNo,co.CourseName,p.ClassId,c.CouseId
,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,o.EnterID,o.HelpEnterId
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
,o.JoinType,o.TargetJoinType,o.SourceOrderId,o.TargetOrderId
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
LEFT JOIN rb_order o on o.OrderId = t2.OrderId
LEFT JOIN rb_teacher t3 on p.TeacherId = t3.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
LEFT JOIN rb_education_contract ec on ec.GuestId = p.OrderGuestId
WHERE p.`Status`=0 and p.Group_Id ={groupId} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
) tt
ORDER BY tt.ClassDate ASC
";
            return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取学生课时统计明细
        /// </summary>
        /// <param name="teacherId"></param>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="startMonth"></param>
        /// <param name="endMonth"></param>
        /// <param name="studentName"></param>
        /// <param name="orderId"></param>
        /// <param name="contractNo"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetStudentConsumptionHoursDetialList(int teacherId, int schoolId, int classId, string startMonth, string endMonth, string studentName, int orderId, string contractNo, int groupId, string classIds = "")
        {
            string where = $@" ";
            if (teacherId > 0)
            {
                where += $@" and p.TeacherId ={teacherId}";
            }
            if (schoolId >= 0)
            {
                where += $@" and c.School_Id ={schoolId}";
            }
            if (classId > 0)
            {
                where += $@" and p.ClassId ={classId}";
            }
            if (!string.IsNullOrEmpty(classIds) && classIds != "0")
            {
                where += $@" and p.ClassId in({classIds})";
            }
            if (!string.IsNullOrEmpty(studentName))
            {
                where += $@" and t2.GuestName like '%{studentName}%'";
            }
            if (orderId > 0)
            {
                where += $@" and t2.OrderId ={orderId}";
            }
            if (!string.IsNullOrEmpty(contractNo))
            {
                where += $@" and ec.ContractNo ='{contractNo}'";
            }

            string sql = $@"
SELECT tt.*,
case when tt.ClassHours>0 and tt.TotalSub >0 then 
	(tt.CourseFee - tt.DiscountMoney / tt.TotalSub * tt.CourseFee) /tt.ClassHours
	else 0 END AS UnitPrice
 FROM (
SELECT p.OrderGuestId,p.CurrentDeductionHours,t2.GuestName,p.TeacherId,t3.TeacherName,t2.OrderId,ec.ContractNo,c.ClassName,c.ClassNo,co.CourseName,p.ClassId,c.CouseId
,p.ClassDate,p.ClassTimeId,t.StartTime as StartDate,t.EndTime as EndDate,o.EnterID,o.HelpEnterId
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
FROM rb_class_check p
INNER JOIN rb_class_time t on p.ClassTimeId = t.ClassTimeId
LEFT JOIN rb_order_guest t2 on p.OrderGuestId = t2.Id
LEFT JOIN rb_order o on o.OrderId = t2.OrderId
LEFT JOIN rb_teacher t3 on p.TeacherId = t3.TId
LEFT JOIN rb_class c on p.ClassId = c.ClassId
LEFT JOIN rb_course co on c.CouseId = co.CourseId
LEFT JOIN rb_education_contract ec on ec.GuestId = p.OrderGuestId
WHERE p.`Status`=0 and p.Group_Id ={groupId} {where} and p.ClassDate >= '{startMonth}' and p.ClassDate <='{endMonth} 23:59:59'
) tt
ORDER BY tt.ClassDate ASC
";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        #endregion

        #region 营收报表

        /// <summary>
        /// 获取班级已上课时
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassLearnHoursList(int groupId, string classIds)
        {
            string sql = $@"SELECT t.ClassId,SUM(t.CurrentDeductionHours) AS CurrentDeductionHours FROM(
SELECT ClassId,ClassDate,ClassTimeId,MAX(CurrentDeductionHours) as CurrentDeductionHours 
FROM rb_class_check WHERE `Status` =0 and Group_Id ={groupId} and ClassId in({classIds}) GROUP BY ClassId,ClassDate,ClassTimeId
)t GROUP BY t.ClassId";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取班级学生已上课金额
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="classIds"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassStuLearnMoneyList(int groupId, string classIds)
        {
            string sql = $@"SELECT hq.ClassId, SUM(hq.CurrentDeductionHours * hq.UnitPrice) AS UnitPrice FROM (
SELECT q.ClassId,q.OrderGuestId,q.CurrentDeductionHours,
case when q.ClassHours>0 and q.TotalSub >0 then 
	(q.CourseFee - q.DiscountMoney / q.TotalSub * q.CourseFee) /q.ClassHours
	else 0 END AS UnitPrice
 FROM (
SELECT tt.ClassId,tt.OrderGuestId,tt.CurrentDeductionHours
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee as CourseFee
,ec.FirstDiscountMoney + ec.SecondDiscountMoney + ec.ThirdDiscountMoney as DiscountMoney
,ec.FirstCourseFee + ec.SecondCourseFee + ec.ThirdCourseFee
+ec.FirstBookFee + ec.SecondBookFee + ec.ThirdBookFee
+ec.FirstClassFee + ec.SecondClassFee + ec.ThirdClassFee as TotalSub
,ec.FirstClassHours + ec.SecondClassHours + ec.ThirdClassHours as ClassHours
 FROM(
SELECT p.ClassId,p.OrderGuestId,SUM(p.CurrentDeductionHours) as CurrentDeductionHours FROM rb_class_check p
WHERE p.`Status`=0 and p.Group_Id ={groupId} and p.ClassId in({classIds})
GROUP BY p.ClassId,p.OrderGuestId
)tt 
LEFT JOIN rb_education_contract ec on tt.OrderGuestId = ec.GuestId and ec.Status <>4
GROUP BY tt.ClassId,tt.OrderGuestId
)q)hq GROUP BY hq.ClassId";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        #endregion

        #region 简易报表

        /// <summary>
        /// 获取时间范围内的班级
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="classId"></param>
        /// <param name="classNo"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetAllClassNowMonthStatistics(string startTime, string endTime, int classId, string classNo, int schoolId, int group_Id)
        {
            string where = "";
            if (classId > 0)
            {
                where += " and cc.ClassId =" + classId;
            }
            if (schoolId >= 0)
            {
                where += " and c.School_Id =" + schoolId;
            }
            if (!string.IsNullOrEmpty(classNo))
            {
                where += " and c.ClassNo ='" + classNo + "'";
            }
            string sql = $@"
SELECT q.ClassId,q.ClassNo,q.ClassName,SUM(q.CurrentDeductionHours) AS CurrentDeductionHours FROM(
SELECT cc.ClassId,cc.ClassDate,cc.ClassTimeId,MAX(cc.CurrentDeductionHours) as CurrentDeductionHours,
c.ClassName,c.ClassNo
FROM rb_class_check cc
LEFT JOIN rb_class c on cc.ClassId = c.ClassId
 WHERE cc.`Status` =0 and cc.Group_Id ={group_Id} and cc.ClassDate >='{startTime}' and cc.ClassDate <='{endTime} 23:59:59' {where} GROUP BY cc.ClassId,cc.ClassDate,cc.ClassTimeId)q GROUP BY q.ClassId";
            return Get<RB_Class_Check_ViewModel>(sql).ToList();
        }

        #endregion

        #region 学生可补课时/缺勤次数，请假次数

        /// <summary>
        /// 获取签到状态记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_Check_ViewModel> GetClassCheckPageList(int pageIndex, int pageSize, out long rowsCount, RB_Class_Check_ViewModel query)
        {
            StringBuilder builderWhere = new StringBuilder();
            StringBuilder builder = new StringBuilder();
            StringBuilder builderMakeUp = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.OrderGuestId > 0)
                {
                    builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.OrderGuestId), query.OrderGuestId);
                }
                if (query.CheckStatus > -1)
                {
                    builderWhere.AppendFormat(" AND a.{0}={1} ", nameof(RB_Class_Check_ViewModel.CheckStatus), query.CheckStatus);
                    if (query.CheckStatus == 1 && query.MakeUpStatus == 2)
                    {
                        builderMakeUp.AppendFormat(" INNER JOIN rb_student_makeup as stum on stum.ClassCheckId=a.ClassCheckId  and stum.MakeUpStatus=2");
                    }
                }
            }

            builder.AppendFormat(@" SELECT A.*,IFNULL(B.GuestName,'') AS GuestName,IFNULL(C.ClassName,'') AS ClassName,C.ClassNo,IFNULL(D.CourseName,'') AS CourseName,IFNULL(E.RoomName,'') AS RoomName
,IFNULL(F.TeacherName,'') AS TeacherName,CONCAT(ct.StartTime,'~',ct.EndTime) as TimeBucket
FROM RB_Class_Check AS A  {0}
     left join rb_class_time ct on ct.ClassTimeId = A.ClassTimeId
     LEFT JOIN rb_order_guest AS B ON A.OrderGuestId=B.Id 
     LEFT JOIN rb_class AS C ON A.ClassId=C.ClassId
	 LEFT JOIN rb_course AS D ON C.CouseId=D.CourseId
	 LEFT JOIN rb_class_room AS E ON A.ClassRoomId=E.RoomId
	 LEFT JOIN rb_teacher AS F ON A.TeacherId=F.TId 
WHERE 1=1 AND A.Status=0  {1} ", builderMakeUp.ToString(), builderWhere.ToString());
            return GetPage<RB_Class_Check_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }

        #endregion
    }
}
