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

namespace Edu.Repository.Sell
{
    /// <summary>
    /// 订单客人仓储层
    /// </summary>
    public class RB_Order_GuestRepository : BaseRepository<RB_Order_Guest>
    {
        /// <summary>
        /// 订单仓储层对象
        /// </summary>
        private readonly RB_OrderRepository orderRepository = new RB_OrderRepository();

        /// <summary>
        /// 学员协助
        /// </summary>
        private readonly RB_Student_AssistRepository student_AssistRepository = new RB_Student_AssistRepository();
        /// <summary>
        /// 同业
        /// </summary>
        private readonly RB_CustomerRepository customerRepository = new RB_CustomerRepository();
        /// <summary>
        /// 学生仓储层对象
        /// </summary>
        private readonly RB_StudentRepository studentRepository = new RB_StudentRepository();
        /// <summary>
        /// 约课记录
        /// </summary>
        private readonly RB_Scroll_AppointmentRepository scroll_AppointmentRepository = new RB_Scroll_AppointmentRepository();

        /// <summary>
        /// 账号管理处理类对象
        /// </summary>
        private readonly RB_AccountRepository accountRepository = new RB_AccountRepository();

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderGuestListRepository(RB_Order_Guest_ViewModel demodel)
        {
            var list = GetCommonOrderGuestListRepository(demodel);
            if (list != null)
            {
                List<RB_Order_ViewModel> sourceTargetList = new List<RB_Order_ViewModel>();
                List<int> idList = new List<int>();
                var sourceOrderIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(x => x.SourceOrderId)));
                if (sourceOrderIdList != null && sourceOrderIdList.Count > 0)
                {
                    idList.AddRange(sourceOrderIdList);
                }
                var targetOrderIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(x => x.TargetOrderId)));
                if (targetOrderIdList != null && targetOrderIdList.Count > 0)
                {
                    idList.AddRange(targetOrderIdList);
                }
                if (idList != null && idList.Count > 0)
                {
                    string ids = string.Join(",", idList);
                    sourceTargetList = orderRepository.GetOrderListRepository(new RB_Order_ViewModel { QOrderIds = ids });
                }
                foreach (var item in list)
                {
                    if (item.SourceOrderId > 0)
                    {
                        item.SourceClassName = sourceTargetList?.Where(qitem => qitem.OrderId == item.SourceOrderId)?.FirstOrDefault()?.ClassName;
                    }
                    if (item.TargetOrderId > 0)
                    {
                        item.TargetClassName = sourceTargetList?.Where(qitem => qitem.OrderId == item.TargetOrderId)?.FirstOrDefault()?.ClassName;
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 获取未签到学员
        /// </summary>
        /// <param name="classTimeId"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetUnCheckStudentRepository(int classTimeId)
        {
            string sql = "select c.ClassTimeId,b.GuestName,b.Id,b.Sex from rb_class_plan a left join rb_order_guest b on a.ClassId=b.ClassId left join rb_class_time c on a.ClassPlanId=c.ClassPlanId left join rb_class_check d on c.ClassTimeId=d.ClassTimeId and b.Id=d.OrderGuestId where c.ClassTimeId=@classTimeId and d.ClassCheckId is null and b.GuestState in(1,3,4,6,8,9)";

            var parameters = new DynamicParameters();
            parameters.Add("classTimeId", classTimeId);

            return Get<RB_Order_Guest_ViewModel>(sql, parameters).ToList();
        }

        /// <summary>
        /// 获取未签到学员
        /// </summary>
        /// <param name="classTimeId"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetUnCheckAppointmentStudentRepository(int classTimeId)
        {
            string sql = "select a.ClassTimeId,e.GuestName,e.Id,e.Sex from rb_class_time a left join rb_scroll_appointment b on a.ClassPlanId=b.ClassPlanId left join rb_student_orderguest c on b.StuId=c.Student_Id left join rb_class_check d on c.GuestId=d.OrderGuestId and a.ClassTimeId = d.ClassTimeId left join rb_order_guest e on c.GuestId=e.Id where a.ClassTimeId=@classTimeId  and d.ClassCheckId is null group by b.StuId";

            var parameters = new DynamicParameters();
            parameters.Add("classTimeId", classTimeId);

            return Get<RB_Order_Guest_ViewModel>(sql, parameters).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetCommonOrderGuestListRepository(RB_Order_Guest_ViewModel demodel)
        {
            var parameters = new DynamicParameters();
            string where = "";
            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 (!string.IsNullOrEmpty(demodel.OrderGuestIds))
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.Id)} IN ({demodel.OrderGuestIds})";
            }
            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 @GuestName ";
                parameters.Add("GuestName", "%" + demodel.GuestName.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(demodel.ClassName))
            {
                where += $@" and cs.{nameof(RB_Order_Guest_ViewModel.ClassName)} LIKE @ClassName ";
                parameters.Add("ClassName", "%" + demodel.ClassName.Trim() + "%");
            }
            if (!string.IsNullOrEmpty(demodel.ClassNo))
            {
                where += $@" and cs.{nameof(RB_Order_Guest_ViewModel.ClassNo)} LIKE @ClassNo ";
                parameters.Add("ClassNo", "%" + demodel.ClassNo.Trim() + "%");
            }
            if (demodel.GuestState > 0)
            {
                where += $@" and A.{nameof(RB_Order_Guest_ViewModel.GuestState)} ={(int)demodel.GuestState}";
            }
            if (demodel.QEffectStatus == 1)
            {
                where += $@" AND (B.JoinType=1 OR B.JoinType=3 OR B.JoinType=4 OR B.JoinType=5 OR (B.JoinType=2 AND B.EffectStatus IN(1) )) ";
            }
            //查询课时完成的学员
            if (demodel.QFinishGuest == 1)
            {
                where += string.Format(" AND A.TotalHours>0 AND A.TotalHours=A.CompleteHours  AND (A.GuestState<>{0} OR (IFNULL(A.GraduationTime,'')='' AND A.GuestState={0})) ", (int)GuestStateEnum.Graduate);
            }

            string sql = $@" 
SELECT sog.Student_Id as StudentId,A.*,cs.ClassName,cs.Teacher_Id,cs.ClassNo,IFNULL(B.JoinType,0) AS JoinType,IFNULL(B.TargetJoinType,0) AS TargetJoinType,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,B.EnterID
       ,sc.StopClassEffectTime,B.SourceOrderId,B.TargetOrderId,IFNULL(D.FirstClassHours,0)+IFNULL(D.SecondClassHours,0)+IFNULL(D.ThirdClassHours,0) AS ContractTotalHours
	   ,IFNULL(stu.StuIcon,'') AS UserIcon,stu.StuTel,stu.StuRealMobile,sog.Account_Id
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
     LEFT JOIN rb_class AS cs ON B.ClassId=cs.ClassId
     LEFT JOIN rb_student_orderguest as sog on sog.GuestId=a.Id
	 LEFT JOIN rb_student AS stu ON sog.Student_Id=stu.StuId
     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
     LEFT JOIN rb_education_contract AS D ON A.OrderId=D.OrderId AND A.Id=D.GuestId AND D.IsSystemUse=1
WHERE 1=1 and A.Status=0 AND B.OrderState<>3  {where}";
            return Get<RB_Order_Guest_ViewModel>(sql, parameters).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
       ,IFNULL(tOrder.JoinType,0) AS JoinType,IFNULL(tOrder.TargetJoinType,0) AS TargetJoinType,tOrder.EffectTime
       ,tOrder.EffectStatus,tOrder.UpOrderId,sc.StopClassEffectTime,tOrder.SourceOrderId,tOrder.TargetOrderId
	   ,st.StuTel,st.StuRealMobile
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 rb_student_orderguest AS og ON (A.Id=og.GuestId AND A.OrderId=og.OrderId)
	 LEFT JOIN rb_student as st ON og.Student_Id=st.StuId
     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.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)} ={(int)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.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 (demodel.GuestState > 0)
            {
                where += $@" AND {nameof(RB_Order_Guest_ViewModel.GuestState)} ={(int)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>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetSingleListRepository(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.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 (demodel.GuestState > 0)
            {
                where += $@" AND {nameof(RB_Order_Guest_ViewModel.GuestState)} ={(int)demodel.GuestState}";
            }
            if (!string.IsNullOrEmpty(demodel.OrderGuestIds))
            {
                where += $@" AND {nameof(RB_Order_Guest_ViewModel.Id)} in({demodel.OrderGuestIds})";
            }
            string sql = $@" SELECT * FROM RB_Order_Guest WHERE {where}";
            return Get<RB_Order_Guest_ViewModel>(sql).ToList();
        }

        /// <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.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)} ={(int)demodel.GuestState}";
            }
            string sql = $@" 
SELECT A.*,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> GetTeacherStudentPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.JoinType,0) AS JoinType,IFNULL(B.TargetJoinType,0) AS TargetJoinType,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,B.SourceOrderId,B.TargetOrderId,stu.StuTel,stu.StuRealMobile,IFNULL(B.CreateBy,0) AS OrderCreateBy,IFNULL(stu.StuId,0) AS StudentId
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId AND B.OrderState IN(1,2)
     LEFT JOIN rb_class as class on class.ClassId=a.ClassId
	 LEFT JOIN rb_student_orderguest as sog on sog.GuestId=a.Id
	 LEFT JOIN rb_student AS stu ON sog.Student_Id=stu.StuId
     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 || demodel.HelpEnterId > 0)
            {
                builder.AppendFormat($@" and (class.Teacher_Id ={demodel.Teacher_Id} OR stu.StuId IN(SELECT StuId FROM rb_student_assist WHERE `Status`=0 AND AssistId={demodel.HelpEnterId}) )  ");
            }
            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)} ={(int)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="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetTeacherStudentStaticRepository(RB_Order_Guest_ViewModel demodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT a.GuestState,COUNT(1) AS StudentNum 
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId AND B.OrderState IN(1,2)
     LEFT JOIN rb_class as class on class.ClassId=a.ClassId
	 LEFT JOIN rb_student_orderguest as sog on sog.GuestId=a.Id
	 LEFT JOIN rb_student AS stu ON sog.Student_Id=stu.StuId
     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 || demodel.HelpEnterId > 0)
            {
                builder.AppendFormat($@" and (class.Teacher_Id ={demodel.Teacher_Id} OR stu.StuId IN(SELECT StuId FROM rb_student_assist WHERE `Status`=0 AND AssistId={demodel.HelpEnterId}) )  ");
            }
            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)} ={(int)demodel.GuestState}");
            }
            builder.AppendFormat($@" GROUP BY A.GuestState");
            return Get<RB_Order_Guest_ViewModel>(builder.ToString()).ToList();
        }


        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="demodel"></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.JoinType,0) AS JoinType,IFNULL(B.TargetJoinType,0) AS TargetJoinType,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,B.SourceOrderId,B.TargetOrderId
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)} ={(int)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>
        /// <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)} ={(int)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)} ={(int)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();
        }

        #region 临时上课邀请学生查询
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <param name="orderIds"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetTempInvitationGuest(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel demodel)
        {
            string where = $@" 1=1 and A.Status=0  and ti.Status=0  AND (B.JoinType=1 OR (B.JoinType=2 AND B.EffectStatus IN(1) )) ";
            if (demodel.Group_Id > 0)
            {
                where += $@" and A.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (demodel.ClassId > 0)
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.ClassId)} ={demodel.ClassId}";
            }
            if (demodel.School_Id > 0)
            {
                where += $@" and Ati.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.School_Id)} ={demodel.School_Id}";
            }
            if (demodel.CourseId > 0)
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.CourseId)} ={demodel.CourseId}";
            }
            if (demodel.ClassTimeId > 0)
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.ClassTimeId)} ={demodel.ClassTimeId}";
            }
            //if (!string.IsNullOrEmpty(demodel.OrderIds))
            //{
            //    where += $@" and A.{nameof(RB_Order_Guest_ViewModel.OrderId)} in({demodel.OrderIds})";
            //}
            if (!string.IsNullOrEmpty(demodel.ClassIds))
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.ClassId)} in({demodel.ClassIds})";
            }
            if (!string.IsNullOrEmpty(demodel.ClassTimeIds))
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.ClassTimeId)} in({demodel.ClassTimeIds})";
            }
            if (!string.IsNullOrEmpty(demodel.ClassPlanIds))
            {
                where += $@" and ti.{nameof(Model.ViewModel.EduTask.RB_Temporary_Invitation_ViewModel.ClassPlanId)} in({demodel.ClassPlanIds})";
            }
            //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}";
            //}


            string sql = $@" 
SELECT A.Id,A.ContactMobile,a.GuestName,a.GuestState,a.Sex,a.Mobile,a.TotalHours,a.CompleteHours,a.MakeUpHours,a.ChangeEffectTime,a.ValidClassHours,ti.ClassId,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,ti.ClassTimeId,ti.ClassPlanId
FROM RB_Order_Guest AS A
INNER JOIN rb_temporary_invitation as ti on ti.OrderGuestId=a.Id
 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();
        }
        #endregion


        #region 学员名单
        /// <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_Extend> GetAllStudentPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Order_Guest_Extend demodel)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT a.*,contract.ContractNo,contract.Id as ContractId,b.CourseId,IFNULL(C.CourseName,'') AS CourseName,C.CourseRate,class.ClassName,class.ClassNo
       ,class.Teacher_Id,teacher.TeacherName,b.CreateTime as OrderTime,b.EnterID,b.PreferPrice,b.Income,b.Refund,b.PlatformTax
			 ,(a.TotalHours-a.CompleteHours) as SurplusHours,gc.AbsenceNum,gc.LeaveNum,eventlog.EventlogNum,b.EffectStatus,b.JoinType,sog.Student_Id,st.StuTel,st.StuRealMobile,chl.Name as StuChannelName,st.CreateType,st.StuSourceId,st.StuId,st.CreateBy as StuCreateBy
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId AND B.OrderState IN(1,2)
	 LEFT JOIN rb_education_contract as contract on contract.GuestId=a.Id AND contract.`Status`=0 
	 LEFT JOIN rb_student_orderguest as sog on sog.GuestId=a.Id AND sog.`Status`=0
	 LEFT JOIN rb_student as st ON sog.Student_Id=st.StuId
     LEFT JOIN rb_channel as chl ON st.StuChannel=chl.Id
	 LEFT JOIN rb_class as class on class.ClassId=b.ClassId
	 LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
	 LEFT JOIN rb_teacher as teacher on teacher.TId=class.Teacher_Id  
	 LEFT JOIN (
			    SELECT OrderGuestId,SUM(CASE CheckStatus WHEN 1 THEN 1 ELSE 0 END) AS AbsenceNum,SUM(CASE CheckStatus WHEN 2 THEN 1 ELSE 0 END) AS LeaveNum
			    FROM rb_class_check 
				WHERE `Status`=0 and CheckStatus <>0  GROUP BY OrderGuestId
				)  AS gc on gc.OrderGuestId=a.Id
	 LEFT JOIN (SELECT StuId,COUNT(*) AS EventlogNum FROM rb_student_eventlog WHERE `Status`=0 GROUP BY StuId) as eventlog on eventlog.StuId=a.Id
WHERE  1=1 AND A.Status=0 AND class.Status=0 AND class.ClassStatus <>4  AND b.OrderState<>3  
");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > -1)
            {
                builder.AppendFormat($@" AND class.{nameof(RB_Class_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.CourseId > 0)
            {
                builder.AppendFormat($@" AND b.{nameof(RB_Order_Guest_Extend.CourseId)} ={demodel.CourseId}");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.ClassId)} ={demodel.ClassId}");
            }

            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestName)} like @GuestName ");
                parameters.Add("GuestName", "%" + demodel.GuestName.Trim() + "%");
            }
            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestState)} ={(int)demodel.GuestState}");
            }
            if (!string.IsNullOrEmpty(demodel.ClassNo))
            {
                builder.AppendFormat($@" AND class.{nameof(RB_Order_Guest_Extend.ClassNo)} like @ClassNo ");
                parameters.Add("ClassNo", "%" + demodel.ClassNo.Trim() + "%");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.EnterID > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.EnterID)} ={demodel.EnterID}");
            }

            if (demodel.OrderTime.HasValue)
            {
                builder.AppendFormat($@" AND  DATE_FORMAT(B.CreateTime,'%y-%m-%d')>=DATE_FORMAT('{Common.ConvertHelper.FormatDate(demodel.OrderTime)}','%y-%m-%d')");
            }
            if (demodel.EndOrderTime.HasValue)
            {
                builder.AppendFormat($@" AND  DATE_FORMAT(B.CreateTime,'%y-%m-%d')<=DATE_FORMAT('{Common.ConvertHelper.FormatDate(demodel.EndOrderTime)}','%y-%m-%d')");
            }
            if (demodel.EffectStatus.HasValue)
            {
                if (demodel.EffectStatus == 0)
                {
                    builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.EffectStatus)} =0");
                }
                else
                {
                    builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestState)} ={demodel.EffectStatus.Value}");
                }
            }
            if (demodel.JoinType > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.JoinType)} ={demodel.JoinType}");
            }
            if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotFifteen)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <15 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotFive)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <5 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotTen)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <10 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotTwenty)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <20 ");
            }
            //2022-01-17 
            if (demodel.CreateType > 0)
            {
                builder.AppendFormat($@" AND st.CreateType ={(int)demodel.CreateType}");
            }
            if (demodel.StuChannelId > 0)
            {
                builder.AppendFormat($@" AND st.StuChannel ={demodel.StuChannelId}");
            }
            if (demodel.StuSourceId > 0)
            {
                builder.AppendFormat($@" AND st.StuSourceId ={demodel.StuSourceId}");
            }
            //查询快毕业的学生 HK 2022-05-09新增
            if (demodel.IsImmediatelyGraduate == 1)
            {
                builder.AppendFormat(" AND A.GuestState<>7 AND (A.TotalHours-A.CompleteHours)<=20 ");
                if (demodel.CreateBy > 0)
                {
                    builder.AppendFormat(" AND(st.CreateBy = {0} OR  EXISTS(SELECT 1 FROM rb_student_assist WHERE StuId = st.StuId AND AssistId = {0})) ", demodel.CreateBy);
                }
            }
            //班主任
            if (demodel.Teacher_Id > 0)
            {
                builder.AppendFormat(" AND EXISTS(SELECT 1 FROM rb_student_assist WHERE AssistType=4 AND StuId=st.StuId AND AssistId={0}) ", demodel.Teacher_Id);
            }
            builder.AppendFormat($@" order by A.{nameof(RB_Order_Guest_Extend.Id)} desc");
            var list= GetPage<RB_Order_Guest_Extend>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
            if (list.Any())
            {
                //课程顾问  协助老师   负责人； 客户来源   来源关联人
                string stuIds = string.Join(",", list.Select(x => x.StuId).Distinct().ToList());
                //协助人员列表
                var assistList = student_AssistRepository.GetStudentAssistListRepository(new Model.ViewModel.Customer.RB_Student_Assist_Extend()
                {
                    QStuIds = stuIds
                });
                //内部介绍人
                List<int> empIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Where(qitem => qitem.CreateType == StuCreateTypeEnum.InternalIntroduction).Select(qitem => qitem.StuSourceId)));
                List<int> aIds = Common.ConvertHelper.StringToList(string.Join(",", assistList.Select(qitem => qitem.AssistId)));
                List<RB_Account_ViewModel> accountList = new List<RB_Account_ViewModel>();
                if (aIds != null && aIds.Count > 0)
                {
                    empIdList.AddRange(aIds);
                }
                List<int> EnterIDList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(qitem => qitem.EnterID)));
                if (EnterIDList != null && EnterIDList.Count > 0)
                {
                    empIdList.AddRange(EnterIDList);
                }
                List<int> CreateByList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(qitem => qitem.CreateBy)));
                if (CreateByList != null && CreateByList.Count > 0)
                {
                    empIdList.AddRange(CreateByList);
                }
                
                List<int> StuCreateByList = Common.ConvertHelper.StringToList(string.Join(",", list.Select(qitem => qitem.StuCreateBy)));
                if (StuCreateByList != null && StuCreateByList.Count > 0)
                {
                    empIdList.AddRange(StuCreateByList);
                }

                if (empIdList != null && empIdList.Count > 0)
                {
                    accountList = accountRepository.GetAccountListExtRepository(new RB_Account_ViewModel() { QIds = string.Join(",", empIdList) });
                }

                //同业录入列表
                List<Model.ViewModel.Customer.RB_Customer_Extend> customerList = new List<Model.ViewModel.Customer.RB_Customer_Extend>();
                List<int> customerIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Where(qitem => qitem.CreateType == StuCreateTypeEnum.CustomerInput).Select(qitem => qitem.StuSourceId)));
                if (customerIdList != null && customerIdList.Count > 0)
                {
                    customerList = customerRepository.GetCustomerListRepository(new Model.ViewModel.Customer.RB_Customer_Extend()
                    {
                        CustomerIds = string.Join(",", customerIdList)
                    });
                }
                List<RB_Student_ViewModel> transStudentList = new List<RB_Student_ViewModel>();
                //转介人列表
                List<int> transIdList = Common.ConvertHelper.StringToList(string.Join(",", list.Where(qitem => qitem.CreateType == StuCreateTypeEnum.TransIntroduction).Select(qitem => qitem.StuSourceId)));
                if (transIdList != null && transIdList.Count > 0)
                {
                    transStudentList = studentRepository.GetStudentListRepository(new RB_Student_ViewModel()
                    {
                        StuIds = string.Join(",", transIdList)
                    });
                }

                string GuestIds = string.Join(",", list.Select(x => x.Id));
                var appointList = scroll_AppointmentRepository.GetAppointList(new Model.ViewModel.Scroll.RB_Scroll_Appointment_ViewModel() { Group_Id = demodel.Group_Id, GuestIds = GuestIds, State = CourseAppointStateEnum.WaitCourse });

                foreach (var item in list)
                {
                    var tempAssistList = assistList?.Where(qitem => qitem.StuId == item.StuId)?.ToList();
                    foreach (var sItem in tempAssistList)
                    {
                        sItem.AssistName = accountList?.FirstOrDefault(qitem => qitem.Id == sItem.AssistId)?.AccountName ?? "";
                    }
                    item.AssistList = tempAssistList;
                    if (item.CreateType == StuCreateTypeEnum.InternalIntroduction)
                    {
                        item.StuSourceIdName = accountList?.FirstOrDefault(qitem => qitem.Id == item.StuSourceId)?.AccountName ?? "";
                    }
                    else if (item.CreateType == StuCreateTypeEnum.CustomerInput)
                    {
                        var tempCustomer = customerList?.FirstOrDefault(qitem => qitem.CustomerId == item.StuSourceId);
                        item.StuSourceIdName = tempCustomer?.CustomerName ?? "";
                        if (tempCustomer != null)
                        {
                            if (tempCustomer.CustomerType == Common.Enum.Customer.CatetoryTypeEnum.Other)
                            {
                                item.EnterpriseName = tempCustomer?.EnterpriseName ?? "";
                            }
                            else
                            {
                                item.EnterpriseName = tempCustomer?.CategoryName ?? "";
                            }
                        }
                        else
                        {
                            item.EnterpriseName = "";
                        }

                    }
                    else if (item.CreateType == StuCreateTypeEnum.TransIntroduction)
                    {
                        item.StuSourceIdName = transStudentList?.FirstOrDefault(qitem => qitem.StuId == item.StuSourceId)?.StuName ?? "";
                    }

                    item.AppointNum = appointList.Where(x => x.GuestId == item.Id)?.Count() ?? 0;

                    if (item.EnterID > 0)
                    {
                        item.EnterName = accountList?.FirstOrDefault(qitem=>qitem.Id==item.EnterID)?.AccountName ?? "";
                    }
                    if (item.EnterID <= 0 && item.CreateBy > 0)
                    {
                        item.EnterName = accountList?.FirstOrDefault(qitem => qitem.Id == item.CreateBy)?.AccountName ?? "";
                    }
                    if (item.StuCreateBy > 0)
                    {
                        item.StuCreateByName = accountList?.FirstOrDefault(qitem => qitem.Id == item.StuCreateBy)?.AccountName ?? "";
                    }
                }
            }
            return list;
        }

        /// <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_Extend> GetAllStudentStaticRepository(RB_Order_Guest_Extend demodel)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT a.GuestState,COUNT(1) AS StudentNum
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId AND B.OrderState IN(1,2)
	 LEFT JOIN rb_education_contract as contract on contract.GuestId=a.Id AND contract.`Status`=0 
	 LEFT JOIN rb_student_orderguest as sog on sog.GuestId=a.Id AND sog.`Status`=0
	 LEFT JOIN rb_student as st ON sog.Student_Id=st.StuId
     LEFT JOIN rb_channel as chl ON st.StuChannel=chl.Id
	 LEFT JOIN rb_class as class on class.ClassId=b.ClassId
	 LEFT JOIN rb_course AS C ON (B.CourseId=C.CourseId AND B.CourseId>0 AND B.OrderType=1)
	 LEFT JOIN rb_teacher as teacher on teacher.TId=class.Teacher_Id  
	 LEFT JOIN (
			    SELECT OrderGuestId,SUM(CASE CheckStatus WHEN 1 THEN 1 ELSE 0 END) AS AbsenceNum,SUM(CASE CheckStatus WHEN 2 THEN 1 ELSE 0 END) AS LeaveNum
			    FROM rb_class_check 
				WHERE `Status`=0 and CheckStatus <>0  GROUP BY OrderGuestId
				)  AS gc on gc.OrderGuestId=a.Id
	 LEFT JOIN (SELECT StuId,COUNT(*) AS EventlogNum FROM rb_student_eventlog WHERE `Status`=0 GROUP BY StuId) as eventlog on eventlog.StuId=a.Id
WHERE  1=1 AND A.Status=0 AND class.Status=0 AND class.ClassStatus <>4  AND b.OrderState<>3  
");
            if (demodel.Group_Id > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.Group_Id)} ={demodel.Group_Id}");
            }
            if (demodel.School_Id > -1)
            {
                builder.AppendFormat($@" AND class.{nameof(RB_Class_ViewModel.School_Id)} ={demodel.School_Id}");
            }
            if (demodel.CourseId > 0)
            {
                builder.AppendFormat($@" AND b.{nameof(RB_Order_Guest_Extend.CourseId)} ={demodel.CourseId}");
            }
            if (demodel.ClassId > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.ClassId)} ={demodel.ClassId}");
            }

            if (!string.IsNullOrEmpty(demodel.GuestName))
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestName)} like @GuestName ");
                parameters.Add("GuestName", "%" + demodel.GuestName.Trim() + "%");
            }
            if (demodel.GuestState > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestState)} ={(int)demodel.GuestState}");
            }
            if (!string.IsNullOrEmpty(demodel.ClassNo))
            {
                builder.AppendFormat($@" AND class.{nameof(RB_Order_Guest_Extend.ClassNo)} like @ClassNo ");
                parameters.Add("ClassNo", "%" + demodel.ClassNo.Trim() + "%");
            }
            if (demodel.OrderId > 0)
            {
                builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.OrderId)} ={demodel.OrderId}");
            }
            if (demodel.EnterID > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.EnterID)} ={demodel.EnterID}");
            }

            if (demodel.OrderTime.HasValue)
            {
                builder.AppendFormat($@" AND  DATE_FORMAT(B.CreateTime,'%y-%m-%d')>=DATE_FORMAT('{Common.ConvertHelper.FormatDate(demodel.OrderTime)}','%y-%m-%d')");
            }
            if (demodel.EndOrderTime.HasValue)
            {
                builder.AppendFormat($@" AND  DATE_FORMAT(B.CreateTime,'%y-%m-%d')<=DATE_FORMAT('{Common.ConvertHelper.FormatDate(demodel.EndOrderTime)}','%y-%m-%d')");
            }
            if (demodel.EffectStatus.HasValue)
            {
                if (demodel.EffectStatus == 0)
                {
                    builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.EffectStatus)} =0");
                }
                else
                {
                    // builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.EffectStatus)} >0");
                    builder.AppendFormat($@" AND A.{nameof(RB_Order_Guest_Extend.GuestState)} ={demodel.EffectStatus.Value}");
                }
            }
            if (demodel.JoinType > 0)
            {
                builder.AppendFormat($@" AND B.{nameof(RB_Order_Guest_Extend.JoinType)} ={demodel.JoinType}");
            }
            if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotFifteen)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <15 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotFive)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <5 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotTen)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <10 ");
            }
            else if (demodel.EarlyWarning == Common.Enum.Course.GuestClassHoursEarlyWarningEnum.NotTwenty)
            {
                builder.AppendFormat($@" AND (a.TotalHours-a.CompleteHours) <20 ");
            }
            //2022-01-17 
            if (demodel.CreateType > 0)
            {
                builder.AppendFormat($@" AND st.CreateType ={(int)demodel.CreateType}");
            }
            if (demodel.StuChannelId > 0)
            {
                builder.AppendFormat($@" AND st.StuChannel ={demodel.StuChannelId}");
            }
            if (demodel.StuSourceId > 0)
            {
                builder.AppendFormat($@" AND st.StuSourceId ={demodel.StuSourceId}");
            }
            if (demodel.Teacher_Id > 0)
            {
                builder.AppendFormat(" AND EXISTS(SELECT 1 FROM rb_student_assist WHERE AssistType=4 AND StuId=st.StuId AND AssistId={0}) ", demodel.Teacher_Id);
            }
            builder.AppendFormat($@" GROUP BY A.GuestState ");
            return Get<RB_Order_Guest_Extend>(builder.ToString(),parameters).ToList();
        }

        #endregion

        /// <summary>
        /// 获取学生课时 请假 缺席
        /// </summary>
        /// <param name="guestId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public RB_Order_Guest_Extend GetOrderGusetHoursInfo(int guestId, int classId)
        {
            string sql = $@"
SELECT A.* ,IFNULL(gc.AbsenceNum,0) AS AbsenceNum,IFNULL(gc2.LeaveNum,0) AS LeaveNum
FROM RB_Order_Guest AS A 
	 LEFT JOIN (
			     SELECT OrderGuestId,SUM(CurrentDeductionHours) AS AbsenceNum 
			     FROM rb_class_check 
				 WHERE Status=0 AND CheckStatus=1 AND ClassId ={classId} AND OrderGuestId={guestId}
				 GROUP BY OrderGuestId
			   )  AS gc on gc.OrderGuestId=a.Id
	 LEFT JOIN (
			     SELECT OrderGuestId,SUM(CurrentDeductionHours) AS LeaveNum 
			     FROM rb_class_check 
				 WHERE Status=0 AND CheckStatus=1 AND ClassId ={classId} AND OrderGuestId={guestId}
				 GROUP BY OrderGuestId
			   )  AS gc2 on gc2.OrderGuestId=a.Id
WHERE A.Id ={guestId}
";
            return Get<RB_Order_Guest_Extend>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> GetOrderGuestListForYK(RB_Order_Guest_ViewModel demodel)
        {
            string sql = $@"select og.*,a.LearnCourseId as CourseId FROM rb_order_guest og
INNER JOIN rb_scroll_appointment a on og.Id = a.GuestId
INNER JOIN rb_class_time t on t.ClassPlanId = a.ClassPlanId
WHERE og.Group_Id ={demodel.Group_Id} and a.`Status` =0 and a.State <>5 and t.ClassTimeId ={demodel.ClassTimeId}
group by og.Id";
            return Get<RB_Order_Guest_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取需要更新学员状态的数据
        /// </summary>
        /// <returns></returns>
        public List<RB_Order_Guest_ViewModel> UpdateGuestStateRepository()
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.OrderId,A.ClassId,A.Income,A.OrderState,A.CourseId,B.GuestName,B.Id,B.GuestState,B.TotalHours,B.CompleteHours 
FROM rb_order AS A INNER JOIN rb_order_guest AS B ON A.OrderId=B.OrderId
WHERE 1=1 AND A.OrderState IN(1) ");
            var orderList = Common.Config.FinishOrderIdList;
            if (orderList != null && orderList.Count > 0)
            {
                builder.AppendFormat(" AND A.OrderId IN({0}) ", string.Join(",", orderList));
                return Get<RB_Order_Guest_ViewModel>(builder.ToString()).ToList();
            }
            else
            {
                return new List<RB_Order_Guest_ViewModel>();
            }
        }
    }
}