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

namespace Edu.Repository.Course
{
    /// <summary>
    /// 订单客人仓储层
    /// </summary>
    public class RB_Order_GuestRepository : BaseRepository<RB_Order_Guest>
    {
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderGuestListRepository(RB_Order_Guest_ViewModel demodel)
        {
            string where = $@" 1=1 and A.Status=0 ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Id)} ={demodel.Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.OrderIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} in({demodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'";
            }
            if (demodel.GuestState > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}";
            }
            if (demodel.QEffectStatus == 1)
            {
                where += $@" AND (B.IsChaBan=0 OR (B.IsChaBan=1 AND B.EffectStatus IN(1) )) ";
            }

            string sql = $@" 
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours,IFNULL(C.CourseName,'') AS CourseName
       ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId,B.IsRenewOrder,B.RenewOrderId
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
     LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1) WHERE {where}";
            return Get<RB_Order_Guest_ViewModel>(sql).ToList();
        }



        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderGuestPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* ,IFNULL(course.CourseName,'') AS CourseName,tOrder.CourseId,tOrder.OrderType,tOrder.StartClassHours,tOrder.IsChaBan,tOrder.EffectTime
       ,tOrder.EffectStatus,tOrder.UpOrderId,sc.StopClassEffectTime
FROM RB_Order_Guest AS A LEFT  JOIN RB_Order as tOrder ON A.OrderId=tOrder.OrderId
     LEFT JOIN rb_course AS course ON (tOrder.CourseId=course.CourseId AND tOrder.CourseId>0 AND tOrder.OrderType=1)
LEFT JOIN (SELECT OrderGuestId,MAX(TakeEffectTime) as StopClassEffectTime from rb_student_stopclass where IsAuditThrough=1 GROUP BY OrderGuestId) as sc on sc.OrderGuestId=a.Id
WHERE 1=1 AND A.Status=0
");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}");
            }
            if (demodel.SourceId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.SourceId)} ={demodel.SourceId}");
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'");
            }
            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}");
            }
            return GetPage<RB_Order_Guest_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取客人数量
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public int GetOrderGuestNumRepository(RB_Order_Guest_ViewModel demodel)
        {
            string where = $@" 1=1 and Status=0";
            if (demodel.Group_Id > 0)
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.OrderIds))
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.OrderId)} in({demodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'";
            }
            if (demodel.GuestState > 0)
            {
                where += $@" and {nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}";
            }
            string sql = $@" select count(0) from RB_Order_Guest where {where}";
            var obj = ExecuteScalar(sql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }



        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderGuestListByEnterID(RB_Order_Guest_ViewModel demodel)
        {
            string where = $@" 1=1 and  a.CompleteHours<15 and b.OrderState=1  and c.ClassStatus in(1,2) and c.`Status`=0 and a.`Status`=0";
            if (demodel.Group_Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            //if (demodel.School_Id > 0)
            //{
            //    where += $@" and A.{nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}";
            //}
            if (demodel.Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Id)} ={demodel.Id}";
            }
            if (demodel.EnterID > 0)
            {
                where += $@" and b.{nameof(RB_Order_Guest_ViewModel.EnterID)} ={demodel.EnterID}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.OrderIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} in({demodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'";
            }
            if (demodel.GuestState > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}";
            }
            if (demodel.QEffectStatus == 1)
            {
                where += $@" AND (B.IsChaBan=0 OR (B.IsChaBan=1 AND B.EffectStatus IN(1) )) ";
            }

            string sql = $@" 
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours
       ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
    LEFT JOIN rb_class as c on a.ClassId=c.ClassId 
WHERE {where}";
            return Get<RB_Order_Guest_ViewModel>(sql).ToList();
        }


        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetTeacherStudentPage(int pageIndex, int pageSize, out long rowsCount, RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours,IFNULL(C.CourseName,'') AS CourseName,class.ClassName,b.EnterID
      ,plan.TotalPlanNum,planr.Ranks ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId,B.IsRenewOrder,B.RenewOrderId,sc.StopClassEffectTime
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
LEFT JOIN rb_class as class on class.ClassId=a.ClassId
     LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
LEFT JOIN (SELECT ClassId,COUNT(*) TotalPlanNum from rb_class_plan where `Status`=0 GROUP BY ClassId) as plan on plan.ClassId=class.ClassId
LEFT JOIN (SELECT ClassId,COUNT(*) Ranks from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY ClassId) as planr on planr.ClassId=class.ClassId
LEFT JOIN (SELECT OrderGuestId,MAX(TakeEffectTime) as StopClassEffectTime from rb_student_stopclass where IsAuditThrough=1 GROUP BY OrderGuestId) as sc on sc.OrderGuestId=a.Id
WHERE  1=1 and A.Status=0 and class.Status=0   and b.OrderState<>3 
");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.Teacher_Id > 0)
            {
                builder.AppendFormat($@" and class.Teacher_Id ={demodel.Teacher_Id}");
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}");
            }
            if (demodel.CourseId > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.CourseId)} ={demodel.CourseId}");
            }
            if (demodel.EnterID > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.EnterID)} ={demodel.EnterID}");
            }
            if (demodel.SourceId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.SourceId)} ={demodel.SourceId}");
            }

            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}");
            }
            builder.AppendFormat($@" order by A.{nameof(RB_Order_Guest_ViewModel.Id)} desc");
            return GetPage<RB_Order_Guest_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }




        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetStopingStudentPage(RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours,school.SName as SchoolName,teacher.TeacherName,IFNULL(C.CourseName,'') AS CourseName,class.ClassNo,class.ClassName,b.EnterID
      ,plan.TotalPlanNum,planr.Ranks ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId,B.IsRenewOrder,B.RenewOrderId,sc.StopClassEffectTime
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
LEFT JOIN rb_class as class on class.ClassId=a.ClassId
     LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
LEFT JOIN (SELECT ClassId,COUNT(*) TotalPlanNum from rb_class_plan where `Status`=0 GROUP BY ClassId) as plan on plan.ClassId=class.ClassId
LEFT JOIN (SELECT ClassId,COUNT(*) Ranks from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY ClassId) as planr on planr.ClassId=class.ClassId
LEFT JOIN rb_teacher as teacher on teacher.TId=class.Teacher_Id
LEFT JOIN rb_school as school on school.SId=class.School_Id
LEFT JOIN (SELECT OrderGuestId,MAX(TakeEffectTime) as StopClassEffectTime from rb_student_stopclass where IsAuditThrough=1 GROUP BY OrderGuestId) as sc on sc.OrderGuestId=a.Id
WHERE  1=1 and A.Status=0   and class.Status=0  and b.OrderState<>3  ");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.Teacher_Id > 0)
            {
                builder.AppendFormat($@" and class.Teacher_Id ={demodel.Teacher_Id}");
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}");
            }
            if (demodel.CourseId > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.CourseId)} ={demodel.CourseId}");
            }
            if (demodel.EnterID > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.EnterID)} ={demodel.EnterID}");
            }
            if (demodel.SourceId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.SourceId)} ={demodel.SourceId}");
            }
            if (!string.IsNullOrWhiteSpace(demodel.OrderGuestIds))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Id)}  in ({demodel.OrderGuestIds})");
            }
            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}");
            }
            builder.AppendFormat($@" order by A.{nameof(RB_Order_Guest_ViewModel.Id)} desc");
            return Get<RB_Order_Guest_ViewModel>(builder.ToString()).ToList();
        }



        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetNotClassStudentList(RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours,school.SName as SchoolName,teacher.TeacherName,IFNULL(C.CourseName,'') AS CourseName,class.ClassNo,class.ClassName,b.EnterID
      ,plan.TotalPlanNum,planr.Ranks ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId,B.IsRenewOrder,B.RenewOrderId
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
LEFT JOIN rb_class as class on class.ClassId=a.ClassId
     LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
LEFT JOIN (SELECT ClassId,COUNT(*) TotalPlanNum from rb_class_plan where `Status`=0 GROUP BY ClassId) as plan on plan.ClassId=class.ClassId
LEFT JOIN (SELECT ClassId,COUNT(*) Ranks from rb_class_plan where `Status`=0 and DATE_FORMAT(ClassDate,'%Y-%m-%d') <=DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY ClassId) as planr on planr.ClassId=class.ClassId
LEFT JOIN rb_teacher as teacher on teacher.TId=class.Teacher_Id
LEFT JOIN rb_school as school on school.SId=class.School_Id
WHERE  1=1 and A.Status=0   and class.Status=0  and b.OrderState<>3  and a.GuestState in(1,6)  ");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.Teacher_Id > 0)
            {
                builder.AppendFormat($@" and class.Teacher_Id ={demodel.Teacher_Id}");
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.ClassId)} !={demodel.ClassId}");
            }
            if (demodel.CourseId > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.CourseId)} ={demodel.CourseId}");
            }
            if (demodel.EnterID > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_ViewModel.EnterID)} ={demodel.EnterID}");
            }
            if (demodel.SourceId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.SourceId)} ={demodel.SourceId}");
            }
            if (!string.IsNullOrWhiteSpace(demodel.OrderGuestIds))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.Id)}  in ({demodel.OrderGuestIds})");
            }
            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}");
            }
            builder.AppendFormat($@" order by A.{nameof(RB_Order_Guest_ViewModel.Id)} desc");
            return Get<RB_Order_Guest_ViewModel>(builder.ToString()).ToList();
        }



        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderStopClassing(RB_Order_Guest_ViewModel demodel)
        {
            string where = $@" 1=1 and A.Status=0 ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
           
            if (demodel.Id > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Id)} ={demodel.Id}";
            }
            if (demodel.OrderId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} ={demodel.OrderId}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (!string.IsNullOrEmpty(demodel.OrderIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} in({demodel.OrderIds})";
            }
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestName)} like '%{demodel.GuestName}%'";
            }
            if (demodel.GuestState > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={demodel.GuestState}";
            }
            if (demodel.QEffectStatus == 1)
            {
                where += $@" AND (B.IsChaBan=0 OR (B.IsChaBan=1 AND B.EffectStatus IN(1) )) ";
            }

            string sql = $@" 
SELECT A.*,IFNULL(B.IsChaBan,0) AS IsChaBan,IFNULL(B.StartClassHours,0) AS StartClassHours,IFNULL(C.CourseName,'') AS CourseName
       ,B.EffectStatus,B.EffectTime,B.UpOrderId,B.OrderType,B.CourseId,B.IsRenewOrder,B.RenewOrderId,sc.StopClassEffectTime
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
     LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
LEFT JOIN (SELECT OrderGuestId,MAX(TakeEffectTime) as StopClassEffectTime from rb_student_stopclass where IsAuditThrough=1 GROUP BY OrderGuestId) as sc on sc.OrderGuestId=a.Id
WHERE {where}";
            return Get<RB_Order_Guest_ViewModel>(sql).ToList();
        }

    }
}
