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

namespace Edu.Repository.User
{
    /// <summary>
    /// 学生订单旅客关联仓储层
    /// </summary>
    public class RB_Student_OrderGuestRepository : BaseRepository<RB_Student_OrderGuest>
    {
        /// <summary>
        /// 获取员工订单旅客关联列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetStrOrderGuestListRepository(RB_Student_OrderGuest_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.ClassName,'') AS ClassName,IFNULL(B.ClassNo,'') AS ClassNo,IFNULL(C.JoinType,0) AS StuJoinType,IFNULL(D.GuestState,0) AS StuGuestState
       ,C.OrderState,IFNULL(D.TotalHours,0) AS TotalHours,IFNULL(D.CompleteHours,0) AS CompleteHours,IFNULL(B.ClassStatus,0) AS ClassStatus,IFNULL(D.RenewState,0) AS RenewState
	   ,IFNULL(E.CourseName,'') AS CourseName,IFNULL(B.CouseId,0) AS CourseId 
FROM RB_Student_OrderGuest AS A INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
     INNER JOIN rb_order AS C  ON A.OrderId=C.OrderId
	 INNER JOIN rb_order_guest AS D ON A.GuestId=D.Id
     LEFT JOIN rb_course AS E ON B.CouseId=E.CourseId
WHERE 1=1 AND C.OrderState IN(1) AND D.Status=0
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Order_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.OrderId);
                }
                if (!string.IsNullOrEmpty(query.QOrderIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.QOrderIds);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.GuestId), query.GuestId);
                }
                if (query.Student_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.Student_Id);
                }
                if (!string.IsNullOrEmpty(query.QStudentIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.QStudentIds);
                }
                if (query.Account_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Account_Id), query.Account_Id);
                }
                if (query.AssistId > 0)
                {
                    builder.AppendFormat(" AND EXISTS(SELECT 1 FROM rb_student_assist WHERE `Status`=0 AND AssistType=4 AND StuId=A.Student_Id AND AssistId={0} ) ", query.AssistId);
                }
                if (query.CourseId > 0)
                {
                    builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Order_ViewModel.CourseId), query.CourseId);
                }
                //课程等级
                if (query.CourseRate > 0)
                {
                    builder.AppendFormat(" AND EXISTS(SELECT 1 FROM rb_course_chapter WHERE `Status`=0  AND CourseId=C.CourseId AND CourseRate IN({0}))",(int)query.CourseRate);
                }
                //学员状态
                if (query.StuGuestState > 0)
                {
                    builder.AppendFormat(@" AND D.GuestState IN({0}) ", (int)query.StuGuestState);
                }
            }
            return Get<RB_Student_OrderGuest_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取员工订单旅客关联列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetStrOrderGuestListStaticRepository(RB_Student_OrderGuest_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.ClassName,'') AS ClassName,IFNULL(B.ClassNo,'') AS ClassNo,IFNULL(C.JoinType,0) AS StuJoinType,IFNULL(D.GuestState,0) AS StuGuestState
       ,C.OrderState,IFNULL(D.TotalHours,0) AS TotalHours,IFNULL(D.CompleteHours,0) AS CompleteHours,IFNULL(B.ClassStatus,0) AS ClassStatus
       ,IFNULL(D.RenewState,0) AS RenewState,C.CreateTime AS OrderCreateTime
FROM RB_Student_OrderGuest AS A INNER JOIN rb_order AS C  ON A.OrderId=C.OrderId
	 INNER JOIN rb_order_guest AS D ON A.GuestId=D.Id
     LEFT JOIN rb_class AS B ON A.ClassId=B.ClassId
WHERE 1=1 AND C.OrderState<>3 AND D.Status=0
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.OrderId);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.GuestId), query.GuestId);
                }
                if (query.Student_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.Student_Id);
                }
                if (!string.IsNullOrEmpty(query.QStudentIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.QStudentIds);
                }

                if (query.Account_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Account_Id), query.Account_Id);
                }
            }
            string sql = string.Format(@"
SELECT A.Student_Id,A.OrderId,A.OrderState,A.RenewState,A.OrderCreateTime 
FROM 
(
   {0}
) AS A
GROUP BY A.Student_Id,A.OrderId
", builder.ToString());
            return Get<RB_Student_OrderGuest_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取学员关联订单分页列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetStuOrderGuestPageRepository(int pageIndex,int pageSize,out long rowsCount, RB_Student_OrderGuest_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.ClassName,'') AS ClassName,IFNULL(B.ClassNo,'') AS ClassNo,IFNULL(C.JoinType,0) AS StuJoinType,IFNULL(D.GuestState,0) AS StuGuestState
       ,C.OrderState,IFNULL(D.TotalHours,0) AS TotalHours,IFNULL(D.CompleteHours,0) AS CompleteHours,IFNULL(B.ClassStatus,0) AS ClassStatus,IFNULL(D.RenewState,0) AS RenewState
       ,IFNULL(D.GuestName,'') AS GuestName,IFNULL(E.CourseName,'') AS CourseName,IFNULL(C.OrderType,0) AS OrderType,IFNULL(C.SourceId,0) AS SourceId,B.ClassScrollType
FROM RB_Student_OrderGuest AS A INNER JOIN rb_order AS C ON A.OrderId=C.OrderId
	 INNER JOIN rb_order_guest AS D ON A.GuestId=D.Id
     LEFT JOIN RB_student AS stu ON A.Student_Id=stu.StuId
     LEFT JOIN rb_course AS E ON c.CourseId=E.CourseId
     LEFT JOIN rb_class AS B ON A.ClassId=B.ClassId
WHERE 1=1 AND C.OrderState<>3 AND D.Status=0
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.OrderId);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.GuestId), query.GuestId);
                }
                if (query.Student_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.Student_Id);
                }
                if (!string.IsNullOrEmpty(query.QStudentIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.QStudentIds);
                }
                if (query.OrderType > 0)
                {
                    builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderType), (int)query.OrderType);
                }
                if (query.Account_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Account_Id), query.Account_Id);
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND (C.CreateBy={0} OR C.EnterID={0} OR C.CourseConsultantId={0}) ", query.CreateBy);
                }
                if (query.IsNotContract == 1)
                {
                    builder.AppendFormat(" AND C.OrderType=1 and C.CourseId<>1 AND stu.School_Id NOT IN(6,7,9) ");
                    builder.AppendFormat(@" AND NOT EXISTS(SELECT 1 FROM rb_education_contract WHERE A.OrderId=OrderId AND A.GuestId=GuestId AND `Status` NOT IN(4)) ");
                }
                if (!string.IsNullOrEmpty(query.KeyWords))
                {
                    builder.Append("  AND (A.OrderId LIKE @KeyWords OR stu.StuName LIKE @KeyWords OR stu.WeChatNo LIKE @KeyWords OR stu.StuRealMobile LIKE @KeyWords) ");
                    parameters.Add("KeyWords", "%" + query.KeyWords.Trim() + "%");
                }
            }
            return GetPage<RB_Student_OrderGuest_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 获取学员关联订单合同分页列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetStuContractGuestPageRepository(int pageIndex, int pageSize, out long rowsCount, RB_Student_OrderGuest_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(B.ClassName,'') AS ClassName,IFNULL(B.ClassNo,'') AS ClassNo,IFNULL(C.JoinType,0) AS StuJoinType,IFNULL(D.GuestState,0) AS StuGuestState
       ,C.OrderState,IFNULL(D.TotalHours,0) AS TotalHours,IFNULL(D.CompleteHours,0) AS CompleteHours,IFNULL(B.ClassStatus,0) AS ClassStatus,IFNULL(D.RenewState,0) AS RenewState
       ,IFNULL(D.GuestName,'') AS GuestName,IFNULL(E.CourseName,'') AS CourseName,IFNULL(F.Id,0) AS ContractId,F.ContractNo
FROM RB_Student_OrderGuest AS A INNER JOIN rb_class AS B ON A.ClassId=B.ClassId
     INNER JOIN rb_order AS C  ON A.OrderId=C.OrderId
	 INNER JOIN rb_order_guest AS D ON A.GuestId=D.Id
     INNER JOIN rb_course AS E ON c.CourseId=E.CourseId
     INNER JOIN rb_education_contract AS F  ON A.GuestId=F.GuestId AND A.OrderId=F.OrderId
WHERE 1=1
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.OrderId);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.GuestId), query.GuestId);
                }
                if (query.Student_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.Student_Id);
                }
                if (!string.IsNullOrEmpty(query.QStudentIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.QStudentIds);
                }

                if (query.Account_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Account_Id), query.Account_Id);
                }
            }
            return GetPage<RB_Student_OrderGuest_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString()).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetList(RB_Student_OrderGuest_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@" 
SELECT A.*,IFNULL(C.JoinType,0) AS StuJoinType,IFNULL(D.GuestState,0) AS StuGuestState
       ,C.OrderState,IFNULL(D.TotalHours,0) AS TotalHours,IFNULL(D.CompleteHours,0) AS CompleteHours,IFNULL(D.RenewState,0) AS RenewState
FROM RB_Student_OrderGuest AS A 
     INNER JOIN rb_order AS C  ON A.OrderId=C.OrderId
	 INNER JOIN rb_order_guest AS D ON A.GuestId=D.Id
WHERE 1=1 AND C.OrderState<>3 AND D.Status=0
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.ClassId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.ClassId), query.ClassId);
                }
                if (query.OrderId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.OrderId);
                }
                if (!string.IsNullOrEmpty(query.QOrderIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.OrderId), query.QOrderIds);
                }
                if (query.GuestId > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.GuestId), query.GuestId);
                }
                if (query.Student_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.Student_Id);
                }
                if (!string.IsNullOrEmpty(query.QStudentIds))
                {
                    builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_OrderGuest_ViewModel.Student_Id), query.QStudentIds);
                }

                if (query.Account_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Account_Id), query.Account_Id);
                }
            }
            return Get<RB_Student_OrderGuest_ViewModel>(builder.ToString()).ToList();
        }

        /// <summary>
        /// 根据退课编号查询学员信息
        /// </summary>
        /// <param name="backId"></param>
        /// <returns></returns>
        public RB_Student_OrderGuest_ViewModel GetModelByBackId(int backId)
        {
            var sql = "select c.* from rb_backclass_protocol a  left join rb_student_backclass b on a.BackId = b.BackId left join rb_student_orderguest c on c.GuestId = b.GuestId where a.Id = @backId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("backId", backId);

            return Get<RB_Student_OrderGuest_ViewModel>(sql, parameters).FirstOrDefault();
        }

        /// <summary>
        /// 获取学生订单/留学/合同数量
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public RB_Student_OrderGuest_ViewModel GetStuendOrderNum(int accountId, int group_Id)
        {
            string sql = $@" select
(select count(0) from RB_Student_OrderGuest og
inner join rb_order o on og.OrderId = o.OrderId
where og.Status =0 and o.Group_Id ={group_Id} and o.OrderState <>3 and o.OrderType =1 and og.Student_Id ={accountId}) as OrderNum,";
            sql += $@"(select count(0) from RB_Student_OrderGuest og
inner join rb_order o on og.OrderId = o.OrderId
where og.Status =0 and o.Group_Id ={group_Id} and o.OrderState <>3 and o.OrderType =2 and og.Student_Id ={accountId}) as StudyAbroadNum,";
            sql += $@"(select count(0) from RB_Student_OrderGuest og
inner join rb_education_contract ec on ec.GuestId = og.GuestId
inner join rb_order o on og.OrderId = o.OrderId
where og.Status =0 and ec.Group_Id ={group_Id} and ec.Status <>4 and o.OrderState <>3 and o.OrderType =1 and og.Student_Id ={accountId}) as ContractNum";

            return Get<RB_Student_OrderGuest_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取正在学习的课程
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public RB_Student_OrderGuest_ViewModel GetLearningCourseInfo(int accountId, int group_Id, int Type)
        {
            string sql = $@"SELECT og.*,o.CourseId,
case when g.TotalHours <= g.CompleteHours then 3 else
case when g.CompleteHours =0 then 1 else
case when g.TotalHours > g.CompleteHours then 2 else 0 end end end as ClassStatus,c.ClassName,c.ClassScrollType 
FROM rb_student_orderguest og 
INNER JOIN rb_order_guest g on og.GuestId = g.Id
INNER JOIN rb_order o on og.OrderId = o.OrderId
INNER JOIN rb_class c on o.ClassId = c.ClassId
WHERE og.Status =0 and g.`Status` =0 and o.Group_Id ={group_Id} and o.OrderState <>3 and o.OrderType =1 and og.Student_Id ={accountId} and {(Type==1? "g.TotalHours > g.CompleteHours" : "g.TotalHours <= g.CompleteHours")}
ORDER BY {(Type == 1 ? "og.Id asc" : "og.Id desc")} LIMIT 1";
            return Get<RB_Student_OrderGuest_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取正在学习的课程 -最新 兼容滚动开班
        /// </summary>
        /// <param name="accountId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public RB_Student_OrderGuest_ViewModel GetStudentLearningCourse(int accountId, int group_Id, int guestId =0)
        {
            string sql = $@"
SELECT og.*,o.CourseId,g.CourseChapterNo,c.ClassScrollType,o.ScrollSchoolId
FROM rb_student_orderguest og 
    INNER JOIN rb_order_guest g on og.GuestId = g.Id
    INNER JOIN rb_order o on og.OrderId = o.OrderId
    INNER JOIN rb_class c on o.ClassId = c.ClassId
WHERE og.Status =0 AND g.`Status` =0 AND o.OrderState <>3 AND o.OrderType =1
";
            if (group_Id > 0)
            {
                sql += $" AND o.Group_Id ={group_Id} ";
            }
            if (accountId > 0)
            {
                sql += $"  AND og.Student_Id ={ accountId} ";
            }
            if (guestId > 0)
            {
                sql += $" AND og.GuestId ={guestId} ";
            }
            else
            {
                sql += " AND g.TotalHours > g.CompleteHours ";
            }
            sql += " ORDER BY og.Id ASC LIMIT 1 ";
            return Get<RB_Student_OrderGuest_ViewModel>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 获取学生订单关联
        /// </summary>
        /// <param name="stuIds"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetStuOrderList(string stuIds, int groupId) {
            string sql = $@"select sog.* from RB_Student_OrderGuest sog
INNER JOIN rb_order o on sog.OrderId = o.OrderId
 where sog.Status =0 and o.Group_Id ={groupId} and Student_Id in({stuIds})";
            return Get<RB_Student_OrderGuest_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 获取学生是否有未付款有效订单
        /// </summary>
        /// <param name="stuIds"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> ValidataStuOrder(string stuIds, int group_Id)
        {
            string sql = $@"select sog.*,s.StuName as GuestName from RB_Student_OrderGuest sog
INNER JOIN rb_student s on sog.Student_Id =s.StuId
INNER JOIN rb_order o on sog.OrderId =o.OrderId
where sog.Status =0 and o.Group_Id ={group_Id} and o.OrderState <>3 and IFNULL(o.Income,0) <=0 and sog.Student_Id in({stuIds})";
            return Get<RB_Student_OrderGuest_ViewModel>(sql).ToList();
        }

        #region 学员跟进

        /// <summary>
        /// 获取学员跟进分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentFollowUpPageRepository(int pageIndex, int pageSize, out long count, RB_Student_ViewModel demodel)
        {
            string where = GetStudentFollowUpSql(demodel, out DynamicParameters parameters);
            string joinType = "INNER JOIN";
            if (demodel.QueryTpye == 3)
            {
                joinType= "LEFT JOIN";
            }
            string sql = $@"
SELECT s.StuId,s.StuName,s.StuTel,s.FirstEnrollDate,s.StuRealMobile,s.StuPurpose,s.QQ,DATE_FORMAT(ec.CreateTime,'%Y-%m-%d') as followUpTime
	   ,c.CourseId,c.CourseName,s.JapanBaseInfo,s.StuBirth,org.GuestState as StuGuestState,s.StuChannel,s.CreateType,s.StuSourceId,s.CreateBy
       ,s.AdvisorStatus,s.AdvisorWinRate,s.AdvisorExpectDate,IFNULL(B.Name,'') AS AdvisorStatusName,org.GraduationTime,clcheck.ClassDate AS BeginClassDate
FROM rb_student s
		{joinType} rb_student_orderguest og on og.Student_Id = s.StuId
		{joinType} (SELECT og.Student_Id,MAX(og.Id) as Id FROM rb_student_orderguest og INNER JOIN rb_order o on og.OrderId = o.OrderId	WHERE o.OrderState <>3 GROUP BY og.Student_Id) stu on og.Id = stu.Id and og.Student_Id = s.StuId
		{joinType} rb_order_guest org on org.Id = og.GuestId
		{joinType} rb_order o on og.OrderId = o.OrderId
		{joinType} rb_class cl on cl.ClassId = o.ClassId
		{joinType} rb_course c on o.CourseId = c.CourseId
		LEFT JOIN rb_education_contract ec on ec.GuestId = og.GuestId
		LEFT JOIN rb_student_advisorconfig AS b ON s.AdvisorStatus=B.Id
		LEFT JOIN (
			        SELECT B.Student_Id,MIN(A.ClassDate) AS ClassDate 
			        FROM rb_class_check AS A INNER JOIN rb_student_orderguest AS B ON A.OrderGuestId=B.GuestId
			        WHERE A.Status=0 AND A.CheckStatus=0  GROUP BY B.Student_Id
		          ) AS clcheck ON s.StuId=clcheck.Student_Id
";
            if (!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime)) {
                //增加跟进链表
                sql += $@" LEFT JOIN rb_student_follow f on s.StuId = f.StuId and f.`Status` =0 and f.AssistType in(2,4) and f.CreateTime >='{demodel.SFTime}' and f.CreateTime <='{demodel.EFTime} 23:59:59'";
            }
            sql += $" where {where} ";
            if ((!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime)))
            {
                sql += $" GROUP BY s.StuId";
            }
            sql += $" order by s.StuId desc";
            return GetPage<RB_Student_ViewModel>(pageIndex, pageSize, out count, sql, parameters).ToList();
        }

        public string GetStudentFollowUpSql(RB_Student_ViewModel demodel, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string where = " 1=1 and s.Status =0 and c.CourseId <>1";//排除内部班课程
            if (demodel.Group_Id > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (!string.IsNullOrEmpty(demodel.StuName))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuName)} like @StuName";
                parameters.Add("StuName", "%" + demodel.StuName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.StuRealMobile))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuRealMobile)} like @StuRealMobile";
                parameters.Add("StuRealMobile", "%" + demodel.StuRealMobile + "%");
            }
            if (!string.IsNullOrEmpty(demodel.QQ))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.QQ)} like @QQ";
                parameters.Add("QQ", "%" + demodel.QQ + "%");
            }
            if (!string.IsNullOrEmpty(demodel.WeChatNo))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.WeChatNo)} like @WeChatNo";
                parameters.Add("WeChatNo", "%" + demodel.WeChatNo + "%");
            }
            if (demodel.StuType > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuType)} ={demodel.StuType}";
            }
            if (demodel.StuChannel > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuChannel)} ={demodel.StuChannel}";
            }
            if (demodel.CreateType > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.CreateType)} ={(int)demodel.CreateType}";
            }
            if (demodel.StuSourceId > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuSourceId)} ={demodel.StuSourceId}";
            }
            if (demodel.StuGuestState > 0)
            {
                where += $" and org.GuestState ={(int)demodel.StuGuestState}";
            }
            if (demodel.StuStage > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuStage)} ={demodel.StuStage}";
            }
            if (demodel.AdvisorStatus > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.AdvisorStatus)} ={demodel.AdvisorStatus}";
            }
            if (demodel.StuPurpose > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuPurpose)} ={demodel.StuPurpose}";
            }
            //if (demodel.School_Id > -1)
            //{
            //    where += $" and cl.School_Id ={demodel.School_Id}";
            //}
            if (demodel.ConsultantId > 0)
            {
                where += $@" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId ={demodel.ConsultantId} AND `Status`=0)";
            }
            if (!string.IsNullOrEmpty(demodel.ConsultantIds))
            {
                where += $@" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({demodel.ConsultantIds}) AND `Status`=0)";
            }
            if (demodel.HeadTeacherId > 0)
            {
                where += $@" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId ={demodel.HeadTeacherId} AND `Status`=0 AND AssistType=4)";
            }
            if (!string.IsNullOrEmpty(demodel.HeadTeacherIds))
            {
                where += $@" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({demodel.HeadTeacherIds}) AND `Status`=0 AND AssistType=4)";
            }
            if (demodel.CourseId > 0)
            {
                where += $" and o.CourseId ={demodel.CourseId}";
            }
            if (!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime))
            {
                if (demodel.Q_FType == 1)
                {
                    where += $" and f.Id is not null";
                }
                else
                {
                    where += $" and f.Id is null";
                }
            }
            if (demodel.BelongType > 0)
            {
                //全部
                if (demodel.BelongType == 1)
                {
                    string tempCreateIds = demodel.CreateBy.ToString();
                    if (!string.IsNullOrEmpty(demodel.QCreateBys))
                    {
                        tempCreateIds += "," + demodel.QCreateBys;
                    }
                    where += string.Format(@"
  AND (  s.CreateBy IN({0}) 
      OR s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({0}) AND `Status`=0)
      )
", tempCreateIds.TrimStart(',').TrimEnd(','));
                }
                //我负责的
                else if (demodel.BelongType == 2)
                {
                    where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                }
                //我协同的
                else if (demodel.BelongType == 3)
                {
                    where += $" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId ={demodel.CreateBy} AND `Status`=0) ";
                }
                //我下属的
                else if (demodel.BelongType == 4)
                {
                    if (demodel.CreateIds != null && demodel.CreateIds.Count > 0)
                    {
                        string createIds = string.Join(",", demodel.CreateIds);
                        if (!string.IsNullOrEmpty(createIds))
                        {
                            where += $" AND (s.{nameof(RB_Student_ViewModel.CreateBy)} IN({createIds}) OR s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({createIds}) AND `Status`=0)) ";
                        }
                    }
                    else
                    {
                        where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                    }
                }
            }
            else
            {
                if (demodel.CreateBy > 0)
                {
                    where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                }
            }
            return where;
        }

        /// <summary>
        /// 获取人数统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public RB_Student_ViewModel GetStudentFollowUpStat(RB_Student_ViewModel demodel)
        {
            string where = GetStudentFollowUpSql(demodel, out DynamicParameters parameters);
            string joinType = "INNER JOIN";
            if (demodel.QueryTpye == 3)
            {
                joinType = "LEFT JOIN";
            }
            string sql = $@"
SELECT count(0) as OrderCount,sum(case when t.RenewState = 1 then 1 else 0 end) AS RenewOrderCount,
        SUM(CASE WHEN t.StuGuestState in(1,3,4,6,8,9) THEN 1 ELSE 0 END) AS StudyNum,
        SUM(CASE WHEN t.StuGuestState =2 THEN 1 ELSE 0 END) AS DropOutNum,
        SUM(CASE WHEN t.StuGuestState =5 THEN 1 ELSE 0 END) AS SuspendNum,
        SUM(CASE WHEN t.StuGuestState =7 THEN 1 ELSE 0 END) AS GraduateNum
FROM(
SELECT s.StuId,s.StuName,s.StuTel,s.StuRealMobile,s.QQ,ec.CreateTime,c.CourseId,c.CourseName,s.JapanBaseInfo,s.StuBirth
       ,org.GuestState as StuGuestState,case when o.RenewOrderId >0 or org.RenewState =2 then 1 else 0 end AS RenewState
FROM rb_student s
    {joinType} rb_student_orderguest og on og.Student_Id = s.StuId
    {joinType} (SELECT og.Student_Id,MAX(og.Id) as Id FROM rb_student_orderguest og INNER JOIN rb_order o on og.OrderId = o.OrderId WHERE o.OrderState <>3 GROUP BY og.Student_Id) stu on og.Id = stu.Id and og.Student_Id = s.StuId
    {joinType} rb_order_guest org on org.Id = og.GuestId
    {joinType} rb_order o on og.OrderId = o.OrderId
    {joinType} rb_class cl on cl.ClassId = o.ClassId
    {joinType} rb_course c on o.CourseId = c.CourseId
    LEFT JOIN rb_education_contract ec on ec.GuestId = og.GuestId ";
            if (!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime))
            {
                //增加跟进链表
                sql += $@" LEFT JOIN rb_student_follow f on s.StuId = f.StuId and f.`Status` =0 and f.AssistType in(2,4) and f.CreateTime >='{demodel.SFTime}' and f.CreateTime <='{demodel.EFTime} 23:59:59'";
            }
            sql += $" where {where} ";
            if ((!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime)))
            {
                sql += $" GROUP BY s.StuId";
            }
            sql += $" order by s.StuId desc)t";
            return Get<RB_Student_ViewModel>(sql, parameters).FirstOrDefault();
        }

        /// <summary>
        /// 获取学员咨询分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentConsultPageList(int pageIndex, int pageSize, out long count, RB_Student_ViewModel demodel)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = " 1=1 and s.Status =0 ";
            if (demodel.Group_Id > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.Group_Id)} ={demodel.Group_Id}";
            }
            if (!string.IsNullOrEmpty(demodel.StuName))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuName)} like @StuName";
                parameters.Add("StuName", "%" + demodel.StuName + "%");
            }
            if (!string.IsNullOrEmpty(demodel.StuRealMobile))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuRealMobile)} like @StuRealMobile";
                parameters.Add("StuRealMobile", "%" + demodel.StuRealMobile + "%");
            }
            if (!string.IsNullOrEmpty(demodel.QQ))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.QQ)} like @QQ";
                parameters.Add("QQ", "%" + demodel.QQ + "%");
            }
            if (!string.IsNullOrEmpty(demodel.WeChatNo))
            {
                where += $" and s.{nameof(RB_Student_ViewModel.WeChatNo)} like @WeChatNo";
                parameters.Add("WeChatNo", "%" + demodel.WeChatNo + "%");
            }
            if (demodel.StuType > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuType)} ={demodel.StuType}";
            }
            if (demodel.StuChannel > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuChannel)} ={demodel.StuChannel}";
            }
            if (demodel.StuGuestState > 0)
            {
                where += $" and og.GuestState ={(int)demodel.StuGuestState}";
            }
            if (demodel.Q_EnrollState > 0) {
                if (demodel.Q_EnrollState == 1)
                {
                    where += $" and o.OrderId is not null and c.CourseId <>1";//排除内部班
                }
                else {
                    where += $" and o.OrderId is null";
                }
            }
            if (demodel.StuStage > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuStage)} ={demodel.StuStage}";
            }
            if (demodel.ConsultantId > 0)
            {
                where += $@" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE  AssistType=2 AND AssistId ={demodel.ConsultantId} AND `Status`=0)";
            }
            if (!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime))
            {
                if (demodel.Q_FType == 1)
                {
                    where += $" and f.Id is not null";
                }
                else
                {
                    where += $" and f.Id is null";
                }
            }
            if (demodel.BelongType > 0)
            {
                //全部
                if (demodel.BelongType == 1)
                {
                    where += $@" AND (s.CreateBy={demodel.CreateBy} OR s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId ={demodel.CreateBy} AND `Status`=0))";
                }
                //我负责的
                else if (demodel.BelongType == 2)
                {
                    where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                }
                //我协同的
                else if (demodel.BelongType == 3)
                {
                    where += $" AND s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId ={demodel.CreateBy} AND `Status`=0) ";
                }
                //我下属的
                else if (demodel.BelongType == 4)
                {
                    if (demodel.CreateIds != null && demodel.CreateIds.Count > 0)
                    {
                        string createIds = string.Join(",", demodel.CreateIds);
                        if (!string.IsNullOrEmpty(createIds))
                        {
                            where += $" AND (s.{nameof(RB_Student_ViewModel.CreateBy)} IN({createIds}) OR s.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({createIds}) AND `Status`=0)) ";
                        }
                    }
                    else
                    {
                        where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                    }
                }
            }
            else
            {
                if (demodel.CreateBy > 0)
                {
                    where += $" AND s.{nameof(RB_Student_ViewModel.CreateBy)} ={demodel.CreateBy} ";
                }
            }

            string sql = $@"SELECT s.*,case when o.OrderId is null then 2 else 1 end as Q_EnrollState,og.GuestState as StuGuestState,c.CourseId,c.CourseName,ch.Name as StuChannelName
FROM rb_student s
LEFT JOIN (
SELECT sog.Student_Id,MAX(og.Id) AS GuestId FROM rb_student_orderguest sog 
INNER JOIN rb_order o on sog.OrderId = o.OrderId 
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
WHERE o.Group_Id ={demodel.Group_Id} and sog.`Status` =0 and o.OrderState <>3 and og.`Status` =0 GROUP BY sog.Student_Id
) ot on s.StuId = ot.Student_Id
LEFT JOIN rb_order_guest og on ot.GuestId = og.Id
LEFT JOIN rb_order o on og.OrderId = o.OrderId
LEFT JOIN rb_course c on o.CourseId = c.CourseId
LEFT JOIN rb_channel ch on s.StuChannel = ch.Id";
            if (!string.IsNullOrEmpty(demodel.SFTime) && !string.IsNullOrEmpty(demodel.EFTime))
            {
                //增加跟进链表
                sql += $@" LEFT JOIN rb_student_follow f on s.StuId = f.StuId and f.`Status` =0 and f.AssistType =2 and f.CreateTime >='{demodel.SFTime}' and f.CreateTime <='{demodel.EFTime} 23:59:59'";
            }
            sql +=$@" WHERE {where}
GROUP BY s.StuId order by s.StuId desc";
            return GetPage<RB_Student_ViewModel>(pageIndex, pageSize, out count, sql, parameters).ToList();
        }

        #endregion

        #region 预约管理

        /// <summary>
        /// 获取可约课的学生列表
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetCanAppointmentStuList(int courseId, int NextChapterGradeNo, int CourseGradeId, int SchoolId, int group_Id)
        {
            string sql = $@"SELECT sog.Student_Id,sog.GuestId,s.StuName as GuestName,og.CourseChapterNo,DATE_FORMAT(sa.Date,'%Y-%m-%d') as AppointDate,sa.AppointNum FROM rb_student_orderguest sog
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
INNER JOIN rb_student s on sog.Student_Id = s.StuId
INNER JOIN rb_class c on o.ClassId = c.ClassId
inner join rb_course_chapter cc on o.CourseId = cc.CourseId and cc.ParentId =0 and cc.ChapterGradeNo ='{NextChapterGradeNo}' and cc.CourseRate ={CourseGradeId}
LEFT JOIN (
	SELECT StuId,MAX(Date) AS Date, COUNT(0) as AppointNum FROM rb_scroll_appointment 
WHERE `Status` =0 and State <>5 and CourseEndTime >=NOW() and Group_Id ={group_Id} GROUP BY StuId
) sa on sog.Student_Id = sa.StuId
WHERE o.Group_Id ={group_Id} and sog.`Status` =0 and og.GuestState <>2 and o.OrderState <>3 and c.ClassScrollType =2 
and og.TotalChapterNo > og.CourseChapterNo and (og.CourseChapterNo + 1 + IFNULL(sa.AppointNum,0)) =cc.ChapterNo";
            if (SchoolId >= 0) {
                sql += $@" and o.ScrollSchoolId ={SchoolId}";
            }

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

        /// <summary>
        /// 获取可约课的学生列表
        /// </summary>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetCanAppointmentStuList_V2(string stuName, int NextChapterGradeNo, int CourseGradeId, int SchoolId, int group_Id)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = $@"
SELECT sog.Student_Id,sog.GuestId,sog.OrderId,cou.CourseName,s.StuName as GuestName,og.CourseChapterNo,sa.AppointNum,ss.MakeUpNum
       ,o.CourseId
FROM rb_student_orderguest sog
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
INNER JOIN rb_course cou on o.CourseId = cou.CourseId
INNER JOIN rb_student s on sog.Student_Id = s.StuId
INNER JOIN rb_class c on o.ClassId = c.ClassId
LEFT JOIN (
	SELECT sa1.StuId,COUNT(0) as AppointNum 
    FROM rb_scroll_appointment sa1 
    WHERE sa1.`Status` =0 and sa1.State <>5 and sa1.Group_Id ={group_Id}  and sa1.CourseGradeId ={CourseGradeId} and sa1.CourseGradeNo ={NextChapterGradeNo} GROUP BY sa1.StuId
) sa on sog.Student_Id = sa.StuId
LEFT JOIN (
	SELECT ss1.StuId,COUNT(0) as MakeUpNum 
    FROM rb_scroll_skipcourse ss1 
    WHERE ss1.`Status` =0 and ss1.State =1 and ss1.Group_Id ={group_Id} and ss1.CourseGradeId ={CourseGradeId} and ss1.CourseGradeNo ={NextChapterGradeNo} GROUP BY ss1.StuId
) ss on sog.Student_Id = ss.StuId
WHERE o.Group_Id ={group_Id} and sog.`Status` =0 and og.GuestState NOT IN(2) and o.OrderState <>3 and c.ClassScrollType =2 ";

            if (!string.IsNullOrEmpty(stuName))
            {
                sql += " AND s.StuName LIKE @stuName ";
                parameters.Add("stuName", "%" + stuName + "%");
            }
            if (SchoolId >= 0)
            {
                sql += $@" and o.ScrollSchoolId ={SchoolId}";
            }
            //sql += string.Format(@" AND EXISTS (SELECT 1 FROM rb_course_chapter WHERE Status=0 AND CourseId= o.CourseId AND ParentId =0 AND ChapterGradeNo ={0} AND CourseRate ={1})", NextChapterGradeNo, CourseGradeId);
            sql += string.Format(@" AND EXISTS (SELECT 1 FROM rb_course_chapter WHERE Status=0 AND CourseId= o.CourseId AND ParentId =0 AND CourseRate ={0})",  CourseGradeId);
            return Get<RB_Student_OrderGuest_ViewModel>(sql, parameters).ToList();
        }

        /// <summary>
        /// 获取所有学习中的学生列表
        /// </summary>
        /// <param name="courseId"></param>
        /// <param name="NextChapterNo"></param>
        /// <param name="CourseGradeId"></param>
        /// <param name="SchoolId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_OrderGuest_ViewModel> GetAppointmentStudyList(int group_Id)
        {
            string sql = $@"SELECT sog.Student_Id,sog.GuestId,sog.OrderId,o.CourseId,s.StuName as GuestName,og.TotalHours,og.CompleteHours FROM rb_student_orderguest sog
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
INNER JOIN rb_student s on sog.Student_Id = s.StuId
WHERE o.Group_Id ={group_Id} and sog.`Status` =0 and og.GuestState <>2 and o.OrderState <>3
and og.TotalHours > og.CompleteHours order by sog.OrderId asc ";

            return Get<RB_Student_OrderGuest_ViewModel>(sql).ToList();
        }
        #endregion
    }
}