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

namespace Edu.Repository.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
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
WHERE 1=1 AND C.OrderState<>3
");
            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 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
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
WHERE 1=1 AND C.OrderState<>3
");
            builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_OrderGuest_ViewModel.Status), (int)DateStateEnum.Normal);
            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 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)
        {
            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_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
");
            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);
                }
            }
            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> 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="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.Group_Id ={group_Id} and o.OrderState <>3 and o.OrderType =1 and g.TotalHours > g.CompleteHours and og.Student_Id ={accountId} {(guestId > 0 ? "and og.GuestId =" + guestId : "")}
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)
        {
            DynamicParameters 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.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 (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)
                {
                    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.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
FROM rb_student s
		INNER JOIN rb_student_orderguest og on og.Student_Id = s.StuId
		INNER JOIN (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
		INNER JOIN rb_order_guest org on org.Id = og.GuestId
		INNER JOIN rb_order o on og.OrderId = o.OrderId
		inner join rb_class cl on cl.ClassId = o.ClassId
		INNER JOIN 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
";
            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();
        }

        /// <summary>
        /// 获取人数统计
        /// </summary>
        /// <param name="demodel"></param>
        /// <returns></returns>
        public RB_Student_ViewModel GetStudentFollowUpStat(RB_Student_ViewModel demodel) {
            DynamicParameters 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.StuGuestState > 0)
            {
                where += $" and org.GuestState ={(int)demodel.StuGuestState}";
            }
            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.StuStage > 0)
            {
                where += $" and s.{nameof(RB_Student_ViewModel.StuStage)} ={demodel.StuStage}";
            }
            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 (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)
                {
                    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 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
INNER JOIN rb_student_orderguest og on og.Student_Id = s.StuId
INNER JOIN (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
INNER JOIN rb_order_guest org on org.Id = og.GuestId
INNER JOIN rb_order o on og.OrderId = o.OrderId
inner join rb_class cl on cl.ClassId = o.ClassId
INNER JOIN 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 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 NextChapterNo, 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
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 ";
            if (courseId > 0) {
                sql += $@" and o.CourseId ={courseId}";
            }
            if (NextChapterNo > 0) {
                sql += $@" and (og.CourseChapterNo + 1 + IFNULL(sa.AppointNum,0)) ={NextChapterNo}";
            }
            if (SchoolId >= 0) {
                sql += $@" and o.ScrollSchoolId ={SchoolId}";
            }

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

        #endregion
    }
}
