using Edu.Common.Enum.Course;
using Edu.Model.Entity.Sell;
using Edu.Model.ViewModel.Grade;
using Edu.Model.ViewModel.Sell;
using System;
using System.Collections.Generic;
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>
        /// <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="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<=A.ValidClassHours AND A.CompleteHours=A.ValidClassHours AND B.OrderState<>3 AND (IFNULL(A.GraduationTime,'')='' OR 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
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_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  {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
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)} ={(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>
        /// <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)} ={(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> GetTeacherStudentPage(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
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)} ={(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> 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="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)} ={(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> GetAllStudentPage(int pageIndex, int pageSize, out long rowsCount, RB_Order_Guest_Extend demodel)
        {
            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
FROM RB_Order_Guest AS A LEFT JOIN rb_order AS B ON A.OrderId=B.OrderId
	 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_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 '%{demodel.GuestName}%'");
            }
            if (!string.IsNullOrEmpty(demodel.ClassNo))
            {
                builder.AppendFormat($@" AND class.{nameof(RB_Order_Guest_Extend.ClassNo)} like '%{demodel.ClassNo}%'");
            }
            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 ");
            }
            builder.AppendFormat($@" order by A.{nameof(RB_Order_Guest_Extend.Id)} desc");
            return GetPage<RB_Order_Guest_Extend>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }

        #endregion

    }
}