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

namespace Edu.Repository.Grade
{
    /// <summary>
    /// 班级仓储层
    /// </summary>
    public class RB_ClassRepository : BaseRepository<RB_Class>
    {
        /// <summary>
        /// 获取班级列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetClassListRepository(RB_Class_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* ,IFNULL(B.CourseName,'') AS CourseName,IFNULL(C.GuestNum,0) AS GuestNum,IFNULL(D.SName,'') AS SchoolName
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
     LEFT JOIN(SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState=1 GROUP BY ClassId) AS C ON A.ClassId=C.ClassId
	 LEFT JOIN rb_school AS D ON A.School_Id=D.SId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (query.School_Id > -1)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
                }
                if (query.Teacher_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
                }
                if (!string.IsNullOrEmpty(query.MoreStatus))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassStatus), query.MoreStatus);
                }
                //查询未报满的班级
                if (query.IsQuerySurplus == 1)
                {
                    builder.AppendFormat(" AND A.{0}>IFNULL(C.GuestNum,0) ", nameof(RB_Class_ViewModel.ClassPersion));
                }
                if (query.Teacher_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
                }
                if (query.ClassScrollType > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), query.ClassScrollType);
                    if (query.ClassScrollType == 2) {
                        //滚动开班  默认查询当月的
                        builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.ToString("yyyy-MM"));
                    }
                }
            }
            return Get<RB_Class_ViewModel>(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_Class_ViewModel> GetClassAndCourseListRepository(RB_Class_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,B.CourseName,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead ,E.SName AS SchoolName,c.CateName,E.ManagerId,IFNULL(t.GuestNum,0) AS OrderStudentCount
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
     LEFT JOIN rb_course_category c on b.CateId = c.CateId
     LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
     LEFT JOIN rb_school AS E ON A.School_Id=E.SId
LEFT JOIN (SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState IN (1,2) GROUP BY ClassId)t on A.ClassId = t.ClassId
WHERE 1=1 
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
                }
                if (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in ({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (query.Status >= 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)query.Status);
                }
                if (query.ClassStatus > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
                }
                if (!string.IsNullOrWhiteSpace(query.ClassName))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
                    parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.CourseName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Class_ViewModel.CourseName));
                    parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherName))
                {
                    builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Class_ViewModel.TeacherName));
                    parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
                }
                if (query.ClassScrollType > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), query.ClassScrollType);
                    if (query.ClassScrollType == 2)
                    {
                        //滚动开班  默认查询当月的
                        builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.ToString("yyyy-MM"));
                    }
                }
            }
            return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取班级列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetClassListForProperty(RB_Class_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,B.CourseName 
FROM rb_class AS A
left join rb_course AS B ON A.CouseId=B.CourseId
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)DateStateEnum.Normal);
            builder.AppendFormat(" AND A.{0} in(1,2,3) ", nameof(RB_Class_ViewModel.ClassStatus));// 未开班 + 学习中的
            if (query != null)
            {
                if (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
                }
                if (!string.IsNullOrEmpty(query.Q_ClassIds))
                {
                    builder.AppendFormat(" AND A.{0} in({1}) ", nameof(RB_Class_ViewModel.ClassId), query.Q_ClassIds);
                }
                if (query.ClassStatus > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
                }
                if (query.ClassScrollType > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), query.ClassScrollType);
                    if (query.ClassScrollType == 2)
                    {
                        //滚动开班  默认查询当月的
                        builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.ToString("yyyy-MM"));
                    }
                }
            }
            return Get<RB_Class_ViewModel>(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_Class_ViewModel> GetClassPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Class_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,B.CourseName,C.AssistName,IFNULL(C.AssistIcon,'') AS AssistIcon,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead
       ,E.SName AS SchoolName,IFNULL(F.RoomName,'') AS RoomName,plan.TotalPlanNum,ySplan.UsePlanNum,B.CourseSubject,IFNULL(csubject.SubjectName,'') AS CourseSubjectName
FROM rb_class AS A LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
     LEFT JOIN rb_assist AS C ON A.Assist_Id=C.AId
     LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
     LEFT JOIN rb_school AS E ON A.School_Id=E.SId
     LEFT JOIN rb_class_room AS F ON A.ClassRoomId=F.RoomId
     LEFT JOIN ((SELECT ClassId,COUNT(*) AS TotalPlanNum FROM rb_class_plan where `Status`=0  GROUP BY ClassId)) as plan on plan.ClassId=a.ClassId
     LEFT JOIN ((SELECT ClassId,COUNT(*) AS UsePlanNum FROM rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY ClassId)) as ySplan on ySplan.ClassId=a.ClassId
     LEFT JOIN rb_course_subject AS csubject ON B.CourseSubject=csubject.Id
WHERE 1=1 
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id >-1)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
                }
                if (query.Status >= 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)query.Status);
                }
                if (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
                }
                if (query.ClassStatus > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassStatus), (int)query.ClassStatus);
                }
                if (!string.IsNullOrEmpty(query.QClassStatus))
                {
                    builder.AppendFormat(" AND A.{0} IN ({1}) ", nameof(RB_Class_ViewModel.ClassStatus), query.QClassStatus);
                }
                if (!string.IsNullOrWhiteSpace(query.ClassName))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
                    parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.ClassNo))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ClassNo ", nameof(RB_Class_ViewModel.ClassNo));
                    parameters.Add("ClassNo", "%" + query.ClassNo.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.CourseName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Course_ViewModel.CourseName));
                    parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.AssistName))
                {
                    builder.AppendFormat(" AND C.{0} LIKE @AssistName ", nameof(RB_Assist_ViewModel.AssistName));
                    parameters.Add("AssistName", "%" + query.AssistName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherName))
                {
                    builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Teacher_ViewModel.TeacherName));
                    parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
                }
                if (query.OldClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}<>{1} ", nameof(RB_Class_ViewModel.ClassId), query.OldClassId);
                }
                if (query.Teacher_Id > 0)
                {
                    builder.AppendFormat(" AND D.{0}={1} ", nameof(RB_Teacher_ViewModel.TId), query.Teacher_Id);
                }
                if (query.CourseSubject > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_ViewModel.CourseSubject), query.CourseSubject);
                }
                if (query.ClassScrollType > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), query.ClassScrollType);
                    if (query.ClassScrollType == 2)
                    {
                        //滚动开班  默认查询当月的
                        builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.ToString("yyyy-MM"));
                    }
                }
            }
            return GetPage<RB_Class_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 更新班级结课日期
        /// </summary>
        /// <param name="classId"></param>
        /// <returns></returns>
        public bool UpdateClassEndDateRepository(int classId)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
UPDATE rb_class AS A 
INNER JOIN(
			 SELECT MAX(ClassDate) AS ClassDate,ClassId  
			 FROM rb_class_plan WHERE Status=0
			 GROUP BY ClassId
		  ) AS B  ON A.ClassId=B.ClassId
SET A.EndClassDate=B.ClassDate
WHERE A.ClassId={0}
", classId);
            return base.Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 获取班课产品
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetClassProductPageList(int pageIndex, int pageSize, out long rowsCount, RB_Class_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,B.CourseName,B.CourseFeature,D.TeacherName,IFNULL(D.TeacherHead,'') AS TeacherHead,E.SName AS SchoolName,R.RoomName
       ,IFNULL(t.GuestNum,0) AS OrderStudentCount,IFNULL(B.SellPrice,0) AS CourseSellPrice,IFNULL(B.CoursewareFee,0) AS CoursewareFee,IFNULL(B.TextbookFee,0) AS TextbookFee,B.SellPriceType as CourseSellPriceType,IFNULL(B.OriginalPrice,0) AS CourseOriginalPrice ,B.B2CRatio,B.B2CReNewRatio,B.B2BRebateRatio,B.B2BReNewRatio,B.SchoolRebateRatio,B.SchoolReNewRatio
       ,B.CourseSubject,IFNULL(csubject.SubjectName,'') AS CourseSubjectName
FROM rb_class AS A  LEFT JOIN rb_course AS B ON A.CouseId=B.CourseId
     LEFT JOIN rb_teacher AS D ON A.Teacher_Id=D.TId
     LEFT JOIN rb_class_room AS R ON A.ClassRoomId=R.RoomId
     LEFT JOIN rb_school AS E ON A.School_Id=E.SId
     LEFT JOIN (SELECT ClassId,SUM(GuestNum) AS GuestNum FROM rb_order WHERE OrderState IN (1,2) GROUP BY ClassId)t on A.ClassId = t.ClassId
	 LEFT JOIN rb_course_subject AS csubject ON B.CourseSubject=csubject.Id
WHERE 1=1 
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), 0);
            builder.AppendFormat(" AND A.{0} in(1,2) ", nameof(RB_Class_ViewModel.ClassStatus));
            builder.AppendFormat(" AND B.Saleplat<>'' AND B.SaleState=1 AND B.Status=0 ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.CourseSubject > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_ViewModel.CourseSubject), (int)query.CourseSubject);
                }
                if (query.School_Id > -1)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.School_Id), query.School_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassId), query.ClassId);
                }
                if (query.CouseId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.CouseId), query.CouseId);
                }
                //课程分类编号
                if (query.CateId > 0)
                {
                    builder.AppendFormat(" AND B.{0}={1} ", nameof(RB_Class_ViewModel.CateId), query.CateId);
                }
                if (query.Teacher_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Teacher_Id), query.Teacher_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.ClassName))
                {
                    builder.AppendFormat(" AND A.{0} LIKE @ClassName ", nameof(RB_Class_ViewModel.ClassName));
                    parameters.Add("ClassName", "%" + query.ClassName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.ClassNo))
                {
                    builder.AppendFormat(" AND A.{0} ='{1}' ", nameof(RB_Class_ViewModel.ClassNo), query.ClassNo);
                }
                if (!string.IsNullOrWhiteSpace(query.CourseName))
                {
                    builder.AppendFormat(" AND B.{0} LIKE @CourseName ", nameof(RB_Class_ViewModel.CourseName));
                    parameters.Add("CourseName", "%" + query.CourseName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherName))
                {
                    builder.AppendFormat(" AND D.{0} LIKE @TeacherName ", nameof(RB_Class_ViewModel.TeacherName));
                    parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.OpenTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_ViewModel.OpenTime), query.EndTime);
                }
                //报名截止日期开始
                if (!string.IsNullOrEmpty(query.JoinStartTime))
                {
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.EndOrderTime), query.JoinStartTime);
                }
                //报名截止日期结束
                if (!string.IsNullOrEmpty(query.JoinEndTime))
                {
                    builder.AppendFormat(" AND A.{0} <='{1} 23:59:59' ", nameof(RB_Class_ViewModel.EndOrderTime), query.JoinEndTime);
                }
                if (query.Q_CanApply == 1)
                {
                    builder.AppendFormat(" AND (IFNULL(A.ClassPersion,0)-IFNULL(t.GuestNum,0))>0 ");
                    builder.AppendFormat(" AND A.{0} >='{1}' ", nameof(RB_Class_ViewModel.EndOrderTime), DateTime.Now.ToString("yyyy-MM-dd"));
                }
                //销售平台
                if (!string.IsNullOrEmpty(query.Saleplat))
                {
                    var salePlatList = Common.ConvertHelper.StringToList(query.Saleplat);
                    string str = "";
                    if (salePlatList != null && salePlatList.Count > 0)
                    {
                        for (var i = 0; i < salePlatList.Count; i++)
                        {
                            if (i == 0)
                            {
                                str += string.Format(" FIND_IN_SET('{0}', B.Saleplat)>0 ", salePlatList[i]);
                            }
                            else
                            {
                                str += string.Format(" AND FIND_IN_SET('{0}', B.Saleplat)>0 ", salePlatList[i]);
                            }
                        }
                    }
                    if (!string.IsNullOrEmpty(str))
                    {
                        builder.AppendFormat("  AND ({0}) ", str);
                    }
                }

                if (query.ClassScrollType > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), query.ClassScrollType);
                    if (query.ClassScrollType == 2)
                    {
                        //滚动开班  默认查询当月的
                        builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.ToString("yyyy-MM"));
                    }
                }
            }
            builder.AppendFormat(" order by A.{0} asc", nameof(RB_Class_ViewModel.OpenTime));
            Common.Plugin.LogHelper.WriteInfo("GetClassProductPageList::" + builder.ToString());
            return GetPage<RB_Class_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }

 
        /// <summary>
        /// 班级类型统计
        /// </summary>
        /// <param name="qClassTypeIds"></param>
        /// <returns></returns>
        public List<ClassTypeStatic_ViewModel> GetClassTypeStaticListRepository(string qClassTypeIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.ClassType,A.TotalClassNum,IFNULL(B.CompleteClassNum,0) AS CompleteClassNum
FROM   (
			SELECT ClassType, COUNT(1) AS TotalClassNum
			FROM rb_class WHERE `Status`=0  AND ClassStatus IN(1,2,3) AND ClassType IN({0})
			GROUP BY ClassType
		) AS A  LEFT JOIN
	   (
			SELECT ClassType, COUNT(1) AS CompleteClassNum
			FROM rb_class WHERE `Status`=0  AND ClassStatus=3 AND ClassType IN({0})
			GROUP BY ClassType
		) AS B ON A.ClassType=B.ClassType
", qClassTypeIds);
            return Get<ClassTypeStatic_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取教师列表
        /// </summary>
        /// <param name="GroupId"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetTeachingPerfClassList(int GroupId)
        {
            string sql = $@"
SELECT c.* 
FROM rb_class c LEFT JOIN rb_teaching_perf t on (c.ClassId = t.ClassId AND t.`Status`=0)
WHERE c.`Status`=0 and c.Group_Id ={GroupId} and c.ClassStatus =3 AND t.Id IS NULL";
            return Get<RB_Class_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 统计教师带班数量
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<TeacherClassStatic_ViewModel> GetTeacherClassCountRepository(string teacherIds)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT Teacher_Id,COUNT(1) AS ClassCount 
FROM rb_class 
WHERE status=0 AND ClassStatus<>4 AND Teacher_Id IN({0})
GROUP BY Teacher_Id
", teacherIds);
            return Get<TeacherClassStatic_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取上个月 没有订单的滚动班级
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public bool SetLastMonthNotOrderClassDel(int groupId)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
update rb_class AS A 
left join rb_order o on A.ClassId =o.ClassId and o.OrderState <>3
set A.Status =1
WHERE 1=1 and o.OrderId IS NULL
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Status), (int)DateStateEnum.Normal);
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), groupId);
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Class_ViewModel.ClassScrollType), 2);
            builder.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Class_ViewModel.ScrollMonth), DateTime.Now.AddMonths(-1).ToString("yyyy-MM"));//上个月

            return Execute(builder.ToString()) > 0;
        }

        /// <summary>
        /// 根据学生id获取教师账户
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetListByStudentId(int Student_Id, int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT c.*,cp.ClassPlanId,cp.ClassDate 
FROM rb_class  as c LEFT JOIN rb_student_orderguest as sog on sog.ClassId=c.ClassId
     LEFT JOIN (SELECT ClassId,MIN(ClassDate) as ClassDate,MIN(ClassPlanId) as ClassPlanId from rb_class_plan  where `Status`=0  and  DATE_FORMAT(ClassDate,'%y-%M-%d')>=DATE_FORMAT(now(),'%y-%M-%d')  GROUP BY ClassId) as cp
     on c.ClassId=cp.ClassId
WHERE  c.`Status`=0 and sog.`Status`=0  ");
            builder.AppendFormat(" AND sog.Student_Id={0} ", Student_Id);
            builder.AppendFormat(" AND c.{0}={1} ", nameof(RB_Class_ViewModel.Group_Id), Group_Id);
            return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 获取app约课
        /// </summary>
        /// <param name="Group_Id"></param>
        /// <returns></returns>
        public List<RB_Class_ViewModel> GetStuAppointment(int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"SELECT a.CourseClassType,a.StudentNumType,SUM(b.OrderNum) as OrderStudentCount from rb_class as a LEFT JOIN 
(SELECT ClassId,COUNT(OrderId) as OrderNum from rb_order where OrderState=1  GROUP BY ClassId )  as b on a.ClassId=b.ClassId
where a.`Status`=0  and a.IsSubscribe=1 and ClassStatus in(1,2)");
            builder.AppendFormat(" AND a.Group_Id={0} ", Group_Id);
            builder.AppendFormat("  GROUP BY  a.CourseClassType,a.StudentNumType ");
            return Get<RB_Class_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取班级编码
        /// </summary>
        /// <param name="OpenTime"></param>
        /// <returns></returns>
        public int GetMaxClassNoPrefix(string OpenTime)
        {
            string sql = $@"SELECT Max(ClassLetterNum) AS ClassLetterNum FROM RB_Class WHERE Status =0 AND OpenTime ='{OpenTime}'";
            var obj = ExecuteScalar(sql);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }
    }
}