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

namespace Edu.Repository.User
{
    /// <summary>
    /// 学生仓储层
    /// </summary>
    public class RB_StudentRepository : BaseRepository<RB_Student>
    {
        /// <summary>
        /// 账号
        /// </summary>
        private readonly RB_AccountRepository accountRepository = new RB_AccountRepository();

        /// <summary>
        /// 学员日志仓储层对象
        /// </summary>
        private readonly RB_Student_LogRepository student_LogRepository = new RB_Student_LogRepository();

        /// <summary>
        /// 客户阶段仓储层对象
        /// </summary>
        private readonly RB_StageRepository stageRepository = new RB_StageRepository();

        /// <summary>
        /// 同业客户仓储层对象
        /// </summary>
        private readonly RB_CustomerRepository customerRepository = new RB_CustomerRepository();

        /// <summary>
        /// 客户类型仓储层对象
        /// </summary>
        private readonly RB_Student_TypeRepository student_TypeRepository = new RB_Student_TypeRepository();

        /// <summary>
        /// 收客渠道仓储层对象
        /// </summary>
        private readonly RB_ChannelRepository channelRepository = new RB_ChannelRepository();

        /// <summary>
        /// 学习目的仓储层对象
        /// </summary>
        private readonly RB_LearningGoalsRepository learningGoalsRepository = new RB_LearningGoalsRepository();

        /// <summary>
        /// 客户需求仓储层对象
        /// </summary>
        private readonly RB_NeedsRepository needsRepository = new RB_NeedsRepository();

        /// <summary>
        /// 学员协助人员仓储层对象
        /// </summary>
        private readonly RB_Student_AssistRepository student_AssistRepository = new RB_Student_AssistRepository();

        /// <summary>
        /// 获取学生列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentListRepository(RB_Student_ViewModel query, bool isQueryCreateType = false, bool isQueryAssist = false)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            if (isQueryCreateType && !string.IsNullOrEmpty(query.QOrderIds))
            {
                builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName,stuog.OrderId
FROM rb_student AS t LEFT JOIN rb_group AS g ON t.Group_Id=g.GId
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
     LEFT JOIN rb_student_orderguest AS stuog ON t.StuId=stuog.Student_Id
WHERE 1=1 
");
            }
            else
            {
                builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName,acc.Id AS Account_Id 
FROM rb_student AS t LEFT JOIN rb_group AS g ON t.Group_Id=g.GId
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
		 LEFT JOIN rb_account AS acc ON t.StuId=acc.AccountId AND acc.AccountType=4
WHERE 1=1 
");
            }

            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.StuName))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuName ", nameof(RB_Student_ViewModel.StuName));
                    parameters.Add("StuName", "%" + query.StuName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.StuTel))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuTel ", nameof(RB_Student_ViewModel.StuTel));
                    parameters.Add("StuTel", "%" + query.StuTel.Trim() + "%");
                }
                if (query.ProviceId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.ProviceId), query.ProviceId);
                }
                if (query.CityId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CityId), query.CityId);
                }
                if (query.AreaId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.AreaId), query.AreaId);
                }
                if (query.CustomerId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CustomerId), query.CustomerId);
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuId), query.StuId);
                }
                if (!string.IsNullOrEmpty(query.StuIds))
                {
                    builder.AppendFormat(" AND t.{0} in({1}) ", nameof(RB_Student_ViewModel.StuId), query.StuIds);
                }
                if (query.StuStage > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuStage), (int)query.StuStage);
                }
                if (query.CreateBy > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CreateBy), query.CreateBy);
                }
                if (!string.IsNullOrEmpty(query.QCreateBys))
                {
                    builder.AppendFormat(@" AND (t.CreateBy IN({0}) OR t.StuId IN(SELECT StuId FROM rb_student_assist WHERE AssistId IN({0}) AND `Status`= 0) ) ", query.QCreateBys);
                }
                //根据订单编号查询学员
                if (!string.IsNullOrEmpty(query.QOrderIds))
                {
                    builder.AppendFormat(" AND stuog.OrderId IN({0}) ", query.QOrderIds);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND t.{0}>='{1}' ", nameof(RB_Student_ViewModel.CreateTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND t.{0}<='{1} 23:59:59' ", nameof(RB_Student_ViewModel.CreateTime), query.EndTime);
                }
            }
            var stuList = Get<RB_Student_ViewModel>(builder.ToString(), parameters).ToList();
            if (isQueryCreateType)
            {
                //转介人列表
                List<RB_Student_ViewModel> transStudentList = new List<RB_Student_ViewModel>();
                //同业录入列表
                List<RB_Customer_Extend> customerList = new List<RB_Customer_Extend>();
                //内部介绍人
                List<RB_Account_ViewModel> accountList = new List<RB_Account_ViewModel>();
                if (stuList != null && stuList.Count > 0)
                {
                    List<int> customerIdList = Common.ConvertHelper.StringToList(string.Join(",", stuList.Where(qitem => qitem.CreateType == StuCreateTypeEnum.CustomerInput).Select(qitem => qitem.StuSourceId)));
                    if (customerIdList != null && customerIdList.Count > 0)
                    {
                        customerList = customerRepository.GetCustomerListRepository(new RB_Customer_Extend()
                        {
                            CustomerIds = string.Join(",", customerIdList)
                        });
                    }

                    List<int> transIdList = Common.ConvertHelper.StringToList(string.Join(",", stuList.Where(qitem => qitem.CreateType == StuCreateTypeEnum.TransIntroduction).Select(qitem => qitem.StuSourceId)));
                    if (transIdList != null && transIdList.Count > 0)
                    {
                        transStudentList = GetStudentListRepository(new RB_Student_ViewModel()
                        {
                            StuIds = string.Join(",", transIdList)
                        });
                    }

                    List<int> empIdList = Common.ConvertHelper.StringToList(string.Join(",", stuList.Where(qitem => qitem.CreateType == StuCreateTypeEnum.InternalIntroduction).Select(qitem => qitem.StuSourceId)));
                    if (empIdList != null && empIdList.Count > 0)
                    {
                        accountList = accountRepository.GetAccountListExtRepository(new RB_Account_ViewModel() { QIds = string.Join(",", empIdList) });
                    }

                    foreach (var item in stuList)
                    {
                        if (item.CreateType == StuCreateTypeEnum.InternalIntroduction)
                        {
                            item.StuSourceIdName = accountList?.FirstOrDefault(qitem => qitem.Id == item.StuSourceId)?.AccountName ?? "";
                        }
                        else if (item.CreateType == StuCreateTypeEnum.CustomerInput)
                        {
                            var tempCustomer = customerList?.FirstOrDefault(qitem => qitem.CustomerId == item.StuSourceId);
                            var tempStr = (tempCustomer?.CustomerName ?? "");
                            if (tempCustomer != null)
                            {
                                if (tempCustomer.CustomerType == Common.Enum.Customer.CatetoryTypeEnum.Other)
                                {
                                    tempStr += "(" + (tempCustomer?.EnterpriseName ?? "") + ")";
                                }
                                else
                                {
                                    tempStr += "(" + (tempCustomer?.CategoryName ?? "") + ")";
                                }
                            }

                            item.StuSourceIdName = tempStr;
                        }
                        else if (item.CreateType == StuCreateTypeEnum.TransIntroduction)
                        {
                            item.StuSourceIdName = transStudentList?.FirstOrDefault(qitem => qitem.StuId == item.StuSourceId)?.StuName ?? "";
                        }
                    }
                }
            }
            if (isQueryAssist)
            {
                if (stuList != null && stuList.Count > 0)
                {
                    string stuIds = string.Join(",", stuList.Select(qitem => qitem.StuId));
                    List<RB_Student_Assist_Extend> assistList = student_AssistRepository.GetStudentAssistListRepository(new RB_Student_Assist_Extend() { QStuIds = stuIds });
                    foreach (var item in stuList)
                    {
                        item.AssistList = assistList?.Where(qitem => qitem.StuId == item.StuId)?.ToList() ?? new List<RB_Student_Assist_Extend>();
                    }
                }
            }
            return stuList;
        }

        /// <summary>
        /// 查询学员的有效订单数（正常，退课）
        /// </summary>
        /// <returns></returns>
        public int GetStudentOrderCount(int studentId)
        {
            var sql = "select Count(0) from rb_student_orderguest a left join rb_order b on a.OrderId=b.OrderId where a.Student_Id=@stuId and b.OrderState!=3";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("stuId", studentId);
            var result = ExecuteScalar(sql, parameters);
            return Convert.ToInt32(result);
        }

        /// <summary>
        /// 获取学生分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Student_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName,IFNULL(a.Account,'') AS StudentAccount ,c.CustomerName,IFNULL(B.Name,'') AS AdvisorStatusName
FROM rb_student AS t LEFT JOIN rb_group AS g ON t.Group_Id=g.GId
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
     LEFT JOIN rb_customer c on (c.CustomerId = t.StuSourceId AND t.CreateType=2)
     LEFT JOIN rb_account as a ON (t.StuId=a.AccountId AND a.AccountType=4 )
     LEFT JOIN rb_student_advisorconfig AS b ON t.AdvisorStatus=B.Id
     LEFT JOIN (
                 SELECT A.Student_Id,Count(1) AS OrderCount 
                 FROM rb_student_orderguest AS A INNER JOIN RB_Order AS B ON A.OrderId=B.OrderId 
				 WHERE B.OrderState IN(1)
                 GROUP BY A.Student_Id 
                ) AS org ON t.StuId=org.Student_Id
WHERE 1=1 
");
            if (query != null)
            {
                if (query.Status >= 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Status), (int)query.Status);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.School_Id), query.School_Id);
                }

                if (query.QStudentType == 1)
                {
                    builder.AppendFormat(" AND t.{0}=0 ", nameof(RB_Student_ViewModel.CustomerId));
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuId), query.StuId);
                }
                if (!string.IsNullOrEmpty(query.StuIds))
                {
                    builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Student_ViewModel.StuId), query.StuIds);
                }
                if (!string.IsNullOrWhiteSpace(query.StuName))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuName ", nameof(RB_Student_ViewModel.StuName));
                    parameters.Add("StuName", "%" + query.StuName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.StuTel))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuTel ", nameof(RB_Student_ViewModel.StuRealMobile));
                    parameters.Add("StuTel", "%" + query.StuTel.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.QQ))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @QQ ", nameof(RB_Student_ViewModel.QQ));
                    parameters.Add("QQ", "%" + query.QQ.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.WeChatNo))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @WeChatNo ", nameof(RB_Student_ViewModel.WeChatNo));
                    parameters.Add("WeChatNo", "%" + query.WeChatNo.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.KeyWords))
                {
                    builder.AppendFormat(" AND (t.{0} LIKE @KeyWords OR  t.{1} LIKE @KeyWords  OR  t.{2} LIKE @KeyWords) ", nameof(RB_Student_ViewModel.StuName), nameof(RB_Student_ViewModel.WeChatNo), nameof(RB_Student_ViewModel.StuRealMobile));
                    parameters.Add("KeyWords", "%" + query.KeyWords.Trim() + "%");
                }
                if (query.StuType > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuType), query.StuType);
                }
                if (query.ProviceId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.ProviceId), query.ProviceId);
                }
                if (query.CityId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CityId), query.CityId);
                }
                if (query.AreaId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.AreaId), query.AreaId);
                }

                if (query.StuStage > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuStage), (int)query.StuStage);
                }
                if (query.CustomerId > 0)
                {
                    builder.AppendFormat(" AND (t.{0}={1} OR (t.StuSourceId={1} AND t.CreateType=2) ) ", nameof(RB_Student_ViewModel.CustomerId), query.CustomerId);
                }
                if (query.StuStage > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuStage), query.StuStage);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND t.{0}>='{1}' ", nameof(RB_Student_ViewModel.CreateTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND t.{0}<='{1} 23:59:59' ", nameof(RB_Student_ViewModel.CreateTime), query.EndTime);
                }
                if (!string.IsNullOrEmpty(query.QDate))
                {
                    builder.AppendFormat(" AND DATE_FORMAT(t.{0},'%y/%m/%d')= DATE_FORMAT('{1}','%y/%m/%d') ", nameof(RB_Student_ViewModel.CreateTime), query.QDate);
                }
                if (query.BelongType > 0)
                {
                    //全部
                    if (query.BelongType == 1)
                    {
                        string tempCreateIds = query.CreateBy.ToString();
                        if (!string.IsNullOrEmpty(query.QCreateBys))
                        {
                            tempCreateIds += "," + query.QCreateBys;
                        }
                        builder.AppendFormat(@"
  AND (  t.CreateBy IN({0}) 
      OR t.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({0}) AND `Status`=0)
      )
", tempCreateIds.TrimStart(',').TrimEnd(','));
                    }
                    //我负责的
                    if (query.BelongType == 2 && query.CreateBy > 0)
                    {
                        builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CreateBy), query.CreateBy);
                    }
                    //我协同的
                    if (query.BelongType == 3)
                    {
                        builder.AppendFormat(" AND t.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId={0} AND `Status`=0) ", query.CreateBy);
                    }
                    //我下属的
                    if (query.BelongType == 4)
                    {
                        if (query.CreateIds != null && query.CreateIds.Count > 0)
                        {
                            string createIds = string.Join(",", query.CreateIds);
                            if (!string.IsNullOrEmpty(createIds))
                            {
                                builder.AppendFormat(" AND (t.{0} IN({1}) OR t.StuId IN (SELECT StuId FROM rb_student_assist WHERE AssistId IN({1}) AND `Status`=0)) ", nameof(RB_Student_ViewModel.CreateBy), createIds);
                            }
                        }
                        else
                        {
                            builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Student_ViewModel.CreateBy), query.CreateBy);
                        }
                    }
                }
                else
                {
                    if (query.CreateBy > 0)
                    {
                        builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CreateBy), query.CreateBy);
                    }
                }

                if (query.ExistCourseConsultant > 0)
                {
                    if (query.ExistCourseConsultant == 1)
                    {
                        builder.AppendFormat(" AND  Exists (SELECT StuId FROM rb_student_assist WHERE AssistType=2 AND `Status`=0 AND t.StuId=StuId) ");
                    }
                    if (query.ExistCourseConsultant == 2)
                    {
                        builder.AppendFormat(" AND NOT Exists (SELECT StuId FROM rb_student_assist WHERE AssistType=2 AND `Status`=0 AND t.StuId=StuId) ");
                    }
                }
                if (query.CategoryId > 0)
                {
                    builder.AppendFormat(" AND C.{0}={1} ", nameof(RB_Student_ViewModel.CategoryId), query.CategoryId);
                }
                if (query.AdvisorStatus > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.AdvisorStatus), query.AdvisorStatus);
                }
                //有效客户
                if (query.QStudentStatus == 1)
                {
                    builder.AppendFormat(" AND t.StuStage<>{0} AND t.AdvisorStatus<>{1} ", 7, 7);
                }
                //无效客户
                if (query.QStudentStatus == 2)
                {
                    builder.AppendFormat(" AND (t.StuStage={0} OR t.AdvisorStatus={1}) ", 7, 7);
                }
                //渠道
                if (query.StuChannel > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuChannel), query.StuChannel);
                }
                if (query.CreateType > 0 && query.StuSourceId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} AND t.{2}={3} ",
                        nameof(RB_Student_ViewModel.CreateType),
                        (int)query.CreateType,
                        nameof(RB_Student_ViewModel.StuSourceId),
                        query.StuSourceId);
                }

                //分配给我的线索（无订单&&从未跟进学员）
                if (query.IsQueryMyClue == 1)
                {
                    builder.Append(" AND IFNULL(org.OrderCount,0)=0 ");
                    builder.AppendFormat(" AND  NOT Exists (SELECT StuId FROM rb_student_follow WHERE  `Status`=0 AND t.StuId=StuId AND CreateBy={0}) ", query.CreateBy);
                }

                //分配给我的学员（有订单&&从未跟进该学员）
                if (query.IsSelectMyStu == 1)
                {
                    builder.Append(" AND IFNULL(org.OrderCount,0)>0 ");
                    builder.AppendFormat(" AND  NOT Exists (SELECT StuId FROM rb_student_follow WHERE  `Status`=0 AND t.StuId=StuId AND CreateBy={0}) ", query.CreateBy);
                }
                //查询今日被跟进学员(有订单&&有跟进记录)
                if (query.IsQueryTodayFollow == 1)
                {
                    builder.Append(" AND IFNULL(org.OrderCount,0)>0 ");
                    builder.AppendFormat(" AND  Exists (SELECT StuId FROM rb_student_follow WHERE `Status`=0 AND t.StuId=StuId AND CreateBy={0} AND CreateTime>='{1}' AND CreateTime<='{2} 23:59:59') ", query.CreateBy, query.FollowStartTime, query.FollowEndTime);
                }
            }
            builder.AppendFormat(" ORDER BY t.{0} DESC ", nameof(RB_Student_ViewModel.StuId));
            return GetPage<RB_Student_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 根据学生id获取同班账户,必须是未开班/学习中的
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetListByStudentId(int Student_Id, int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"
SELECT c.*,rbc.ClassName 
FROM rb_student as c 
LEFT JOIN rb_student_orderguest as sog  on sog.Student_Id=c.StuId
LEFT JOIN rb_class as rbc  on rbc.ClassId=sog.ClassId 
where sog.ClassId in(SELECT a.ClassId from rb_student_orderguest as a LEFT JOIN rb_class as b on a.ClassId=b.ClassId 
LEFT JOIN rb_student as c  on c.StuId =a.Student_Id
LEFT JOIN rb_order as o on a.OrderId=o.OrderId
where b.`Status`=0 and b.ClassStatus in(1,2) and  a.status=0 and  a.Account_Id={Student_Id} and o.OrderState=1 AND c.Group_Id={Group_Id} )  and sog.Account_Id!={Student_Id}
 AND c.Group_Id={Group_Id}");
            return Get<RB_Student_ViewModel>(builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 根据学生id获取学生的班级课程学校信息
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentInfoRepository(int Student_Id, int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"
SELECT s.*,sog.GuestId,o.OrderState,cou.CourseName,cou.CourseId,cou.B2BIcon,cou.B2BBackground,c.ClassName,c.ClassId,c.ClassScrollType,
       case when og.TotalHours <= og.CompleteHours then 3 else
       case when og.CompleteHours = 0 then 1 else
       case when og.TotalHours > og.CompleteHours then 2 else 0 end end end as ClassStatus,
       sch.SName,sch.SId,t.TeacherName,o.EnterID,og.TotalHours,og.CompleteHours, if((og.ValidClassHours-og.CompleteHours)<0,0,(og.ValidClassHours-og.CompleteHours))  as SurplusHours
       ,cou.OpenBankLevel
FROM rb_student as s LEFT JOIN rb_student_orderguest as sog on s.StuId=sog.Student_Id
    LEFT JOIN rb_order_guest as og on og.Id=sog.GuestId  
    LEFT JOIN rb_order as o on og.OrderId=o.OrderId
    LEFT JOIN rb_course as cou on o.CourseId=cou.CourseId
    LEFT JOIN rb_class as c on c.ClassId=o.ClassId
    LEFT JOIN rb_school as sch on sch.SId=c.School_Id
    LEFT JOIN rb_teacher as t on t.TId=c.Teacher_Id
WHERE o.OrderState=1 and og.`Status`=0 and sog.`Status`=0 and og.GuestState <>2 and cou.`Status`=0 and c.`Status`=0  and s.`Status`=0 and s.StuId={Student_Id} and s.Group_Id={Group_Id}");
            return Get<RB_Student_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取学生课程的上课次数
        /// </summary>
        /// <param name="guestIds"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentClassNumber(string guestIds, int groupId)
        {
            string sql = $@"SELECT GuestId,COUNT(0) as CompleteHours FROM rb_scroll_appointment 
WHERE Group_Id ={groupId} and `Status`=0 and State =3 and AppointType =1 and GuestId in({guestIds})
GROUP BY GuestId";
            return Get<RB_Student_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 新增修改学员资料
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool SetStudentRepository(RB_Student_ViewModel model)
        {
            bool flag;
            string logContent = "";
            string logTitle = "";
            int createBy = 0;
            if (model.CreateType == Common.Enum.User.StuCreateTypeEnum.EmployeeInput)
            {
                model.StuSourceId = 0;
            }
            else if (model.CreateType == Common.Enum.User.StuCreateTypeEnum.CustomerInput)
            {
                if (model.CustomerId <= 0 && model.StuSourceId > 0)
                {
                    model.CustomerId = model.StuSourceId;
                }
            }
            //内部介绍=>直接修改内部介绍人为学员创建人
            else if (model.CreateType == StuCreateTypeEnum.InternalIntroduction)
            {
                model.CreateBy = model.StuSourceId;
            }

            model.StuTel = Common.Plugin.StringHelper.ReplaceMobile(model.StuRealMobile);
            if (model.StuId > 0)
            {
                logTitle = "修改客户";
                var oldModel = base.GetEntity<RB_Student_ViewModel>(model.StuId);
                if (oldModel.StuName != model.StuName)
                {
                    logContent += string.Format("昵称：由【{0}】=>【{1}】,", oldModel.StuName, model.StuName);
                }
                if (oldModel.StuTel != model.StuTel)
                {
                    logContent += string.Format("电话：由【{0}】=>【{1}】,", oldModel.StuTel, model.StuTel);
                }
                if (oldModel.StuIcon != model.StuIcon)
                {
                    logContent += string.Format("头像：由【{0}】=>【{1}】,", oldModel.StuIcon, model.StuIcon);
                }
                if (oldModel.StuSex != model.StuSex)
                {
                    logContent += string.Format("性别：由【{0}】=>【{1}】,", oldModel.StuSex == 0 ? "男" : "女", model.StuSex == 0 ? "男" : "女");
                }
                if (oldModel.StuBirth != model.StuBirth)
                {
                    logContent += string.Format("出生日期：由【{0}】=>【{1}】,", Common.ConvertHelper.FormatDate(oldModel.StuBirth), Common.ConvertHelper.FormatDate(model.StuBirth));
                }
                if (oldModel.JapanBaseInfo != model.JapanBaseInfo)
                {
                    logContent += string.Format("日语基础：由【{0}】=>【{1}】,", oldModel.JapanBaseInfo.ToName(), model.JapanBaseInfo.ToName());
                }
                if (oldModel.StuEducation != model.StuEducation)
                {
                    logContent += string.Format("学历：由【{0}】=>【{1}】,", oldModel.StuEducation.ToName(), model.StuEducation.ToName());
                }
                if (oldModel.StuPurpose != model.StuPurpose)
                {
                    string oldName = learningGoalsRepository.GetLearningGoalsExtEntityRepository(oldModel.StuPurpose)?.Name ?? "";
                    string newName = learningGoalsRepository.GetLearningGoalsExtEntityRepository(model.StuPurpose)?.Name ?? "";
                    logContent += string.Format("学习目的：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.StuProfession != model.StuProfession)
                {
                    logContent += string.Format("职业：由【{0}】=>【{1}】,", oldModel.StuProfession, model.StuProfession);
                }
                if (oldModel.StuStage != model.StuStage)
                {
                    var oldName = stageRepository.GetEntity(oldModel.StuStage)?.StageName ?? "";
                    var newName = stageRepository.GetEntity(model.StuStage)?.StageName ?? "";
                    logContent += string.Format("客户阶段：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.StuSource != model.StuSource)
                {
                    logContent += string.Format("来源：由【{0}】=>【{1}】,", oldModel.StuSource.ToName(), model.StuSource.ToName());
                }
                if (oldModel.StuChannel != model.StuChannel)
                {
                    string oldName = channelRepository.GetChannelExtEntityRepository(oldModel.StuChannel)?.Name ?? "";
                    string newName = channelRepository.GetChannelExtEntityRepository(model.StuChannel)?.Name ?? "";
                    logContent += string.Format("收客渠道：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.PlatformName != model.PlatformName)
                {
                    logContent += string.Format("地三方平台名称：由【{0}】=>【{1}】,", oldModel.PlatformName, model.PlatformName);
                }
                if (oldModel.CreateType != model.CreateType)
                {
                    logContent += string.Format("客人来源：由【{0}】=>【{1}】,", oldModel.CreateType.ToName(), model.CreateType.ToName());
                }

                if (oldModel.StuSourceId != model.StuSourceId)
                {
                    string oldName = "";
                    string newName = "";
                    if (oldModel.CreateType == Common.Enum.User.StuCreateTypeEnum.InternalIntroduction)
                    {
                        oldName = accountRepository.GetEmployeeInfo(oldModel.StuSourceId)?.EmployeeName ?? "";
                    }
                    if (oldModel.CreateType == Common.Enum.User.StuCreateTypeEnum.TransIntroduction)
                    {
                        oldName = customerRepository.GetEntity(oldModel.StuSourceId)?.CustomerName ?? "";
                    }
                    if (model.CreateType == Common.Enum.User.StuCreateTypeEnum.InternalIntroduction)
                    {
                        newName = accountRepository.GetEmployeeInfo(model.StuSourceId)?.EmployeeName ?? "";
                    }
                    if (model.CreateType == Common.Enum.User.StuCreateTypeEnum.TransIntroduction)
                    {
                        newName = customerRepository.GetEntity(model.StuSourceId)?.CustomerName ?? "";
                    }
                    logContent += string.Format("来源人：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.CustomerId != model.CustomerId)
                {
                    string oldName = customerRepository.GetEntity(oldModel.CustomerId)?.CustomerName ?? "";
                    string newName = customerRepository.GetEntity(model.CustomerId)?.CustomerName ?? "";
                    logContent += string.Format("同行：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.QQ != model.QQ)
                {
                    logContent += string.Format("QQ：由【{0}】=>【{1}】,", oldModel.QQ, model.QQ);
                }
                if (oldModel.WeChatNo != model.WeChatNo)
                {
                    logContent += string.Format("微信号：由【{0}】=>【{1}】,", oldModel.WeChatNo, model.WeChatNo);
                }
                if (oldModel.StuType != model.StuType)
                {
                    string oldName = student_TypeRepository.GetEntity(oldModel.StuType)?.Name;
                    string newName = student_TypeRepository.GetEntity(model.StuType)?.Name;
                    logContent += string.Format("客户类型：由【{0}】=>【{1}】,", oldName, newName);
                }
                if (oldModel.StuNeeds != model.StuNeeds)
                {
                    string oldName = needsRepository.GetNeedsExtEntityRepository(oldModel.StuNeeds)?.Name;
                    string newName = needsRepository.GetNeedsExtEntityRepository(model.StuNeeds)?.Name;
                    logContent += string.Format("客户需求：由【{0}】=>【{1}】,", oldName, newName);
                }
                Dictionary<string, object> fileds = new Dictionary<string, object>()
                {
                    {nameof(RB_Student_ViewModel.StuName),model.StuName.Trim() },
                    {nameof(RB_Student_ViewModel.StuTel),model.StuTel.Trim() },
                    {nameof(RB_Student_ViewModel.StuSex),model.StuSex },
                    {nameof(RB_Student_ViewModel.StuBirth),model.StuBirth },
                    {nameof(RB_Student_ViewModel.ProviceId),model.ProviceId },
                    {nameof(RB_Student_ViewModel.CityId),model.CityId },
                    {nameof(RB_Student_ViewModel.AreaId),model.AreaId },
                    {nameof(RB_Student_ViewModel.JapanBaseInfo),model.JapanBaseInfo },
                    {nameof(RB_Student_ViewModel.StuProfession),model.StuProfession },
                    {nameof(RB_Student_ViewModel.StuEducation),model.StuEducation },
                    {nameof(RB_Student_ViewModel.StuPurpose),model.StuPurpose },
                    {nameof(RB_Student_ViewModel.StuSource),model.StuSource },
                    {nameof(RB_Student_ViewModel.CreateType),model.CreateType },
                    {nameof(RB_Student_ViewModel.CustomerId),model.CustomerId},
                    {nameof(RB_Student_ViewModel.QQ),model.QQ},
                    {nameof(RB_Student_ViewModel.WeChatNo),model.WeChatNo},
                    {nameof(RB_Student_ViewModel.StuType),model.StuType},
                    {nameof(RB_Student_ViewModel.StuRealMobile),model.StuRealMobile},
                };
                if (!string.IsNullOrEmpty(model.FileVoucher))
                {
                    fileds.Add(nameof(RB_Student_ViewModel.FileVoucher), model.FileVoucher);
                }
                //教育后台操作
                if (model.OperateType == 1)
                {
                    fileds.Add(nameof(RB_Student_ViewModel.StuSourceId), model.StuSourceId);
                    fileds.Add(nameof(RB_Student_ViewModel.StuStage), model.StuStage);
                    fileds.Add(nameof(RB_Student_ViewModel.StuChannel), model.StuChannel);
                    fileds.Add(nameof(RB_Student_ViewModel.PlatformName), model.PlatformName);
                    fileds.Add(nameof(RB_Student_ViewModel.StuNeeds), model.StuNeeds);
                    fileds.Add(nameof(RB_Student_ViewModel.StuIcon), model.StuIcon.Trim());
                }
                //App小程序操作
                else
                {
                    fileds.Add(nameof(RB_Student_ViewModel.StuAddress), model.StuAddress);
                    fileds.Add(nameof(RB_Student_ViewModel.StuContract), model.StuContract);
                    fileds.Add(nameof(RB_Student_ViewModel.StuContractMobile), model.StuContractMobile);
                    fileds.Add(nameof(RB_Student_ViewModel.StuIDCard), model.StuIDCard);
                    fileds.Add(nameof(RB_Student_ViewModel.StuIDCardAddress), model.StuIDCardAddress);
                    fileds.Add(nameof(RB_Student_ViewModel.StuIcon), model.StuIcon.Trim());
                }
                flag = base.Update(fileds, new WhereHelper(nameof(RB_Student_ViewModel.StuId), model.StuId));
                createBy = model.UpdateBy;
            }
            else
            {
                model.FirstEnrollDate = DateTime.Now;//2022-01-12 ld  
                if (model.StuStage <= 0)
                {
                    model.StuStage = 1;
                }
                var newId = base.Insert(model);
                model.StuId = newId;
                flag = newId > 0;
                if (model.CreateType == Common.Enum.User.StuCreateTypeEnum.EmployeeInput)
                {
                    createBy = model.CreateBy;
                }
                else
                {
                    createBy = model.CustomerId;
                }
                logContent = "创建了该客户";
                logTitle = "创建客户";
            }

            #region 处理学员账号信息

            var accountModel = accountRepository.GetAccountListRepository(new RB_Account_ViewModel() { Group_Id = model.Group_Id, AccountType = Common.Enum.User.AccountTypeEnum.Student, AccountId = model.StuId }).FirstOrDefault();
            string account = "";
            if (!string.IsNullOrEmpty(model.WeChatNo))
            {
                account = model.WeChatNo;
            }
            if (!string.IsNullOrEmpty(model.QQ))
            {
                account = model.QQ;
            }
            if (!string.IsNullOrEmpty(model.StuRealMobile))
            {
                account = model.StuRealMobile;
            }
            if (accountModel == null)
            {
                //创建学生账号
                model.Account_Id = accountRepository.Insert(new RB_Account()
                {
                    Id = 0,
                    Account = account,
                    AccountId = model.StuId,
                    AccountType = Common.Enum.User.AccountTypeEnum.Student,
                    AnnualLeaveDay = 0,
                    CreateBy = model.CreateBy,
                    CreateTime = DateTime.Now,
                    Group_Id = model.Group_Id,
                    School_Id = model.School_Id,
                    Status = DateStateEnum.Normal,
                    UpdateBy = model.UpdateBy,
                    UpdateTime = DateTime.Now,
                    Password = Common.DES.Encrypt(Common.Config.DefaultPwd)
                });
            }
            else
            {
                if (account != accountModel.Account)
                {
                    Dictionary<string, object> accountFileds = new Dictionary<string, object>()
                    {
                        {nameof(RB_Account.Account),account}
                    };
                    accountRepository.Update(accountFileds, new WhereHelper(nameof(RB_Account.Id), accountModel.Id));
                }
            }
            #endregion

            student_LogRepository.AddStuLogRepository(model.StuId, Common.Enum.Log.StudentLogTypeEnum.BasicInfo, logTitle, logContent, createBy, CreateType: model.OperateType);
            return flag;
        }

        /// <summary>
        /// 修改客户阶段
        /// </summary>
        /// <param name="StuId"></param>
        /// <param name="StuStage"></param>
        /// <param name="Uid"></param>
        /// <returns></returns>
        public bool SetStudentStageRepository(int StuId, int StuStage, int Uid)
        {
            string logContent = "";
            string logTitle = "";
            bool flag = false;
            var oldModel = base.GetEntity(StuId);
            if (oldModel != null)
            {
                if (oldModel.StuStage != StuStage)
                {
                    var oldName = stageRepository.GetEntity(oldModel.StuStage)?.StageName ?? "";
                    var newName = stageRepository.GetEntity(StuStage)?.StageName ?? "";
                    logContent += string.Format("客户阶段：由【{0}】=>【{1}】,", oldName, newName);
                }

                Dictionary<string, object> fileds = new Dictionary<string, object>()
                {
                    {nameof(RB_Student_ViewModel.StuStage),StuStage }
                };
                flag = base.Update(fileds, new WhereHelper(nameof(RB_Student_ViewModel.StuId), StuId));
                student_LogRepository.AddStuLogRepository(oldModel.StuId, Common.Enum.Log.StudentLogTypeEnum.BasicInfo, logTitle, logContent, Uid, CreateType: 1);
            }
            return flag;
        }

        /// <summary>
        /// 学员转交
        /// </summary>
        /// <param name="StuIds">学员编号</param>
        /// <param name="CreateBy">负责人</param>
        /// <param name="OperateId">操作人</param>
        /// <returns></returns>
        public bool ForwardStudentRepository(string StuIds, int CreateBy, int OperateId)
        {
            bool flag = false;
            string logTitle = "客户转交";
            string logContent = "";
            var stuList = GetStudentListRepository(new RB_Student_ViewModel() { StuIds = StuIds });
            if (stuList != null && stuList.Count > 0)
            {
                string createByIds = string.Join(",", stuList.Select(qitem => qitem.CreateBy)) + "," + CreateBy;
                var empList = accountRepository.GetEmployeeListRepository(new Employee_ViewModel()
                {
                    QIds = createByIds.TrimStart(',').TrimEnd(',')
                });
                foreach (var oldModel in stuList)
                {
                    Dictionary<string, object> fileds = new Dictionary<string, object>()
                    {
                      {nameof(RB_Student_ViewModel.CreateBy),CreateBy },
                    };
                    if (oldModel.CreateBy != CreateBy)
                    {
                        logContent = string.Format("负责人：由【{0}】=>【{1}】",
                            empList?.FirstOrDefault(qitem => qitem.Id == oldModel.CreateBy)?.EmployeeName,
                            empList?.FirstOrDefault(qitem => qitem.Id == CreateBy)?.EmployeeName
                            );
                    }
                    flag = base.Update(fileds, new WhereHelper(nameof(RB_Student_ViewModel.StuId), oldModel.StuId));
                    student_LogRepository.AddStuLogRepository(oldModel.StuId, Common.Enum.Log.StudentLogTypeEnum.BasicInfo, logTitle, logContent, OperateId);
                }
            }
            return flag;
        }

        /// 获取同业下学生阶段人数统计
        /// </summary>
        /// <param name="customerId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetCustomerStuStageStatistics(int customerId, int CategoryId, int group_Id)
        {
            StringBuilder builder = new StringBuilder();
            if (customerId > 0)
            {
                builder.AppendFormat(" AND A.StuSourceId = {0}", customerId);
            }
            if (CategoryId > 0)
            {
                builder.AppendFormat(" AND B.CategoryId = {0}", CategoryId);
            }
            string sql = $@"
SELECT A.StuStage,COUNT(0) AS OrderCount 
FROM rb_student AS A INNER JOIN rb_customer AS B ON (A.CreateType=2 AND A.StuSourceId=B.CustomerId)
WHERE A.Group_Id ={group_Id} AND A.CreateType=2 {builder}
GROUP BY A.StuStage ";
            return Get<RB_Student_ViewModel>(sql).ToList();
        }


        /// <summary>
        /// 获取同行每周新增客户数量
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetCustomerStuWeekListRepository(RB_Student_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName 
FROM rb_student AS t LEFT JOIN rb_group AS g ON t.Group_Id=g.GId
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
WHERE 1=1 AND YEARWEEK(date_format(t.CreateTime, '%Y-%m-%d'),1) = YEARWEEK(now(),1)
");
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > -1)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.School_Id), query.School_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.StuName))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuName ", nameof(RB_Student_ViewModel.StuName));
                    parameters.Add("StuName", "%" + query.StuName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.StuTel))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @StuTel ", nameof(RB_Student_ViewModel.StuTel));
                    parameters.Add("StuTel", "%" + query.StuTel.Trim() + "%");
                }
                if (query.ProviceId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.ProviceId), query.ProviceId);
                }
                if (query.CityId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CityId), query.CityId);
                }
                if (query.AreaId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.AreaId), query.AreaId);
                }
                if (query.CustomerId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CustomerId), query.CustomerId);
                }
            }
            return Get<RB_Student_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 根据电话号码、QQ、微信号获取学员信息
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public RB_Student_ViewModel GetStuByTelRepository(RB_Student_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,B.Id AS Account_Id
FROM rb_student AS t LEFT  JOIN rb_account AS B ON t.StuId=B.AccountId AND B.AccountType=4
WHERE 1=1 
");
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.StuTel))
                {
                    builder.AppendFormat(" AND t.{0}=@StuTel ", nameof(RB_Student_ViewModel.StuTel));
                    parameters.Add("StuTel", query.StuTel.Trim());
                }
                if (!string.IsNullOrWhiteSpace(query.StuRealMobile))
                {
                    builder.AppendFormat(" AND t.{0}=@StuRealMobile ", nameof(RB_Student_ViewModel.StuRealMobile));
                    parameters.Add("StuRealMobile", query.StuRealMobile.Trim());
                }
                if (!string.IsNullOrWhiteSpace(query.QQ))
                {
                    builder.AppendFormat(" AND t.{0}=@QQ ", nameof(RB_Student_ViewModel.QQ));
                    parameters.Add("QQ", query.QQ.Trim());
                }
                if (!string.IsNullOrWhiteSpace(query.WeChatNo))
                {
                    builder.AppendFormat(" AND t.{0}=@WeChatNo ", nameof(RB_Student_ViewModel.WeChatNo));
                    parameters.Add("WeChatNo", query.WeChatNo.Trim());
                }
                if (query.StuId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuId), query.StuId);
                }
            }
            return Get<RB_Student_ViewModel>(builder.ToString(), parameters)?.FirstOrDefault();
        }

        /// <summary>
        /// 线索统计
        /// </summary>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentStaticRepository(string createByIds, string startDate, string endDate)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
 SELECT CreateBy,DATE_FORMAT(CreateTime,'%Y-%m-%d') AS CreateTime,Count(1) AS ClueCount
 FROM RB_Student 
 WHERE Status=0
 ");
            if (!string.IsNullOrEmpty(createByIds))
            {
                builder.AppendFormat(" AND CreateBy IN({0}) ", createByIds);
            }
            if (!string.IsNullOrEmpty(startDate))
            {
                builder.AppendFormat(" AND CreateTime>='{0}' ", startDate);
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                builder.AppendFormat(" AND CreateTime<='{0} 23:59:59'  ", endDate);
            }
            builder.AppendFormat(" GROUP BY CreateBy,DATE_FORMAT(CreateTime,'%Y/%m/%d') ");
            builder.AppendFormat(" ORDER BY CreateBy,DATE_FORMAT(CreateTime,'%Y/%m/%d') ");
            var list = Get<RB_Student_ViewModel>(builder.ToString()).ToList();
            return list;
        }

        /// <summary>
        /// 市场部、课程顾问部、线索统计
        /// </summary>
        /// <param name="createByIds">线索创建人</param>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <param name="StuStage">客户阶段</param>
        /// <returns></returns>
        public Dictionary<string, List<RB_Student_ViewModel>> GetStudentClueRepository(RB_Student_ViewModel query)
        {
            Dictionary<string, List<RB_Student_ViewModel>> dicList = new Dictionary<string, List<RB_Student_ViewModel>>();
            string whereStr = "";
            if (query.CreateBy > 0)
            {
                whereStr += string.Format(" AND CreateBy={0} ", query.CreateBy);
            }

            StringBuilder builder = new StringBuilder();
            builder.Append(@"
 SELECT Count(1) AS ClueCount,CreateBy
 FROM RB_Student 
 WHERE Status=0 {0}
 ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                builder.Append(whereStr);
            }
            builder.Append(" GROUP BY CreateBy ");
            string todayWhere = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                todayWhere += string.Format(" AND to_days(CreateTime)=to_days('{0}') ", query.StartTime);
            }
            else
            {
                todayWhere += string.Format(" AND to_days(CreateTime)=to_days(now()) ");
            }

            //今日
            var todayList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), todayWhere)).ToList();
            dicList.Add("today", todayList);

            //昨日
            string yesWhere = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                yesWhere += string.Format(" AND TO_DAYS('{0}')-TO_DAYS(CreateTime)=1 ", query.StartTime);
            }
            else
            {
                yesWhere += string.Format(" AND TO_DAYS(now())-TO_DAYS(CreateTime)=1 ");
            }
            var yesList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), yesWhere)).ToList();
            dicList.Add("yestoday", yesList);

            //本周
            string curWeek = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(CreateTime,'%Y-%m-%d'),1) = YEARWEEK('{0}',1) ", query.StartTime);
            }
            else
            {
                curWeek += string.Format(" AND YEARWEEK(date_format(CreateTime,'%Y-%m-%d'),1) = YEARWEEK(now(),1) ");
            }
            var curWeekList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), curWeek)).ToList();
            dicList.Add("currentWeek", curWeekList);

            //上周
            string lastWeek = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(CreateTime,'%Y-%m-%d'),1) = YEARWEEK('{0}',1)-1 ", query.StartTime);
            }
            else
            {
                lastWeek += string.Format(" AND YEARWEEK(date_format(CreateTime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 ");
            }
            var lastWeekList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), lastWeek)).ToList();
            dicList.Add("lastWeek", lastWeekList);

            //本月
            string curMonth = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                curMonth += string.Format(" AND DATE_FORMAT(CreateTime,'%Y%m')=DATE_FORMAT('{0}','%Y%m') ", query.StartTime);
            }
            else
            {
                curMonth += string.Format(" AND DATE_FORMAT(CreateTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') ");
            }
            var curMonthList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), curMonth)).ToList();
            dicList.Add("curMonth", curMonthList);

            //上月
            string lastMonth = "";
            if (!string.IsNullOrEmpty(query.StartTime))
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format('{0}','%Y%m'),date_format(CreateTime,'%Y%m')) =1 ", query.StartTime);
            }
            else
            {
                lastMonth += string.Format(" AND PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(CreateTime,'%Y%m')) =1 ");
            }
            var lastMonthList = Get<RB_Student_ViewModel>(string.Format(builder.ToString(), lastMonth)).ToList();
            dicList.Add("lastMonth", lastMonthList);

            return dicList;
        }

        /// <summary>
        /// 市场部渠道数据统计
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetStudentChannelStaticRepository(RB_Student_ViewModel query)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.StuId,A.CreateBy,A.StuChannel,A.CreateType,IFNULL(B.VisitCount,0) AS VisitCount,IFNULL(C.CourseOrderCount,0) AS OrderCount,IFNULL(c.Income,0) AS OrderInCome
      ,A.CustomerId,A.StuSourceId,IFNULL(D.CustomerType,0) AS CustomerType,IFNULL(D.CategoryId,0) AS CategoryId,IFNULL(E.CategoryName,'') AS CategoryName,IFNULL(E.CatetoryType,0) AS CatetoryType
FROM RB_Student AS A 
     LEFT JOIN(SELECT StuId,Count(1) AS VisitCount FROM rb_student_visit WHERE Status=0 GROUP BY StuId) AS B ON A.StuId=B.StuId
     LEFT JOIN(SELECT og.Student_Id,COUNT(1) AS CourseOrderCount,SUM(o.Income) AS Income FROM  rb_student_orderguest AS og INNER JOIN rb_order AS o ON og.OrderId=o.OrderId WHERE O.OrderState NOT IN(3,4) GROUP BY og.Student_Id ) AS C ON A.StuId=C.Student_Id
     LEFT JOIN rb_customer AS D ON A.StuSourceId=D.CustomerId AND A.CreateType=2
	 LEFT JOIN rb_customer_category AS E ON D.CategoryId=E.CategoryId
WHERE A.Status=0  
");
            builder.AppendFormat(" AND A.StuStage<>{0} AND A.AdvisorStatus<>{1} ", 7, 7);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND A.{0}={1} ", nameof(RB_Student_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrEmpty(query.StartTime))
                {
                    builder.AppendFormat(" AND A.{0}>='{1}' ", nameof(RB_Student_ViewModel.CreateTime), query.StartTime);
                }
                if (!string.IsNullOrEmpty(query.EndTime))
                {
                    builder.AppendFormat(" AND A.{0}<='{1} 23:59:59' ", nameof(RB_Student_ViewModel.CreateTime), query.EndTime);
                }
                if (query.CreateIds != null && query.CreateIds.Count > 0)
                {
                    string str = string.Join(",", query.CreateIds);
                    if (!string.IsNullOrEmpty(str))
                    {
                        builder.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Student_ViewModel.CreateBy), str);
                    }
                }
            }
            var list = Get<RB_Student_ViewModel>(builder.ToString()).ToList();
            return list;
        }

        /// <summary>
        /// 获取转交学员
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public List<RB_Student_ViewModel> GetUserStudentCareOf(int userId, int group_Id)
        {
            string sql = $@"SELECT s.* FROM rb_student s
LEFT JOIN rb_student_assist a on s.StuId = a.StuId and a.`Status` =0
WHERE s.`Status`=0 and s.Group_Id ={group_Id} and (s.CreateBy ={userId} or a.AssistId ={userId}) 
group by s.StuId";
            return Get<RB_Student_ViewModel>(sql).ToList();
        }
    }
}