using Edu.Common.Enum;
using Edu.Common.Plugin;
using Edu.Model.ViewModel.User;
using Edu.Repository.Customer;
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<Model.Entity.User.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_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)
        {
            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 
");
            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 (!string.IsNullOrEmpty(query.StuIds))
                {
                    builder.AppendFormat(" AND t.{0} in({1}) ", nameof(RB_Student_ViewModel.StuId), query.StuIds);
                }
            }
            return Get<RB_Student_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <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 
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 a ON (t.StuId=a.AccountId AND a.AccountType=4 )
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.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.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 (!string.IsNullOrWhiteSpace(query.KeyWords))
                {
                    builder.AppendFormat(" AND (t.{0} LIKE @KeyWords OR  t.{1} LIKE @KeyWords) ", nameof(RB_Student_ViewModel.StuName), nameof(RB_Student_ViewModel.StuTel));
                    parameters.Add("KeyWords", "%" + query.KeyWords.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.CreateBy > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.CreateBy), query.CreateBy);
                }
                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} ", nameof(RB_Student_ViewModel.CustomerId), query.CustomerId);
                }
                if (query.StuStage > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Student_ViewModel.StuStage), (int)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);
                }
            }
            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> GetStudentInfo(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,c.ClassName,c.ClassId,c.ClassStatus,sch.SName,sch.SId,t.TeacherName,o.EnterID, if((og.ValidClassHours-og.CompleteHours)<0,0,(og.ValidClassHours-og.CompleteHours))  as SurplusHours  
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=1 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="model"></param>
        /// <returns></returns>
        public bool SetStudentRepository(RB_Student_ViewModel model)
        {
            bool flag;
            string logContent = "";
            string logTitle = "";
            int createBy = 0;

            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)
                {
                    logContent += string.Format("学习目的：由【{0}】=>【{1}】,", oldModel.StuPurpose.ToName(), model.StuPurpose.ToName());
                }
                if (oldModel.StuProfession != model.StuProfession)
                {
                    logContent += string.Format("职业：由【{0}】=>【{1}】,", oldModel.StuProfession, model.StuProfession);
                }
                if (oldModel.StuStage != model.StuStage)
                {
                    logContent += string.Format("客户阶段：由【{0}】=>【{1}】,", oldModel.StuStage.ToName(), model.StuStage.ToName());
                }
                if (oldModel.StuSource != model.StuSource)
                {
                    logContent += string.Format("来源：由【{0}】=>【{1}】,", oldModel.StuSource.ToName(), model.StuSource.ToName());
                }
                if (oldModel.StuChannel != model.StuChannel)
                {
                    logContent += string.Format("收客渠道：由【{0}】=>【{1}】,", oldModel.StuChannel.ToName(), model.StuChannel.ToName());
                }
                if (oldModel.PlatformName != model.PlatformName)
                {
                    logContent += string.Format("地三方平台名称：由【{0}】=>【{1}】,", oldModel.PlatformName, model.PlatformName);
                }
                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.StuIcon),model.StuIcon.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.Interest),model.Interest },
                    {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.StuAddress),model.StuAddress },
                    {nameof(RB_Student_ViewModel.StuContract),model.StuContract },
                    {nameof(RB_Student_ViewModel.StuContractMobile),model.StuContractMobile },
                    {nameof(RB_Student_ViewModel.StuIDCard),model.StuIDCard },
                    {nameof(RB_Student_ViewModel.StuIDCardAddress),model.StuIDCardAddress },
                    {nameof(RB_Student_ViewModel.StuStage),model.StuStage },
                    {nameof(RB_Student_ViewModel.StuChannel),model.StuChannel },
                    {nameof(RB_Student_ViewModel.PlatformName),model.PlatformName },
                };
                flag = base.Update(fileds, new WhereHelper(nameof(RB_Student_ViewModel.StuId), model.StuId));
                createBy = model.UpdateBy;
            }
            else
            {
                var newId = base.Insert(model);
                model.StuId = newId;
                flag = newId > 0;

                //创建学生账号
                model.Account_Id = accountRepository.Insert(new Model.Entity.User.RB_Account()
                {
                    Id = 0,
                    Account = model.StuTel,
                    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)
                });
                if (model.CreateType == 1)
                {
                    createBy = model.CreateBy;
                }
                else
                {
                    createBy = model.CustomerId;
                }
                logContent = "创建了该客户";
                logTitle = "创建客户";
            }

            if (model.AssistList != null && model.AssistList.Count > 0)
            {
                var oldAssistList = student_AssistRepository.GetStudentAssistListRepository(new Model.ViewModel.Customer.RB_Student_Assist_Extend()
                {

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

        /// <summary>
        /// 学员转交
        /// </summary>
        /// <param name="StuId">学员编号</param>
        /// <param name="CreateBy">负责人</param>
        /// <param name="OperateId">操作人</param>
        /// <returns></returns>
        public bool ForwardStudentRepository(int StuId, int CreateBy, int OperateId)
        {
            string logTitle = "客户转交";
            string logContent = "";
            var oldModel = base.GetEntity(StuId);
            Dictionary<string, object> fileds = new Dictionary<string, object>()
            {
              {nameof(RB_Student_ViewModel.CreateBy),CreateBy },
            };
            if (oldModel.CreateBy != CreateBy)
            {
                var empList = accountRepository.GetEmployeeListRepository(new Employee_ViewModel()
                {
                    QIds = oldModel.CreateBy + "," + CreateBy
                });
                logContent = string.Format("负责人：由【{0}】=>【{1}】",
                    empList?.FirstOrDefault(qitem => qitem.CreateBy == oldModel.CreateBy)?.EmployeeName,
                    empList?.FirstOrDefault(qitem => qitem.CreateBy == CreateBy)?.EmployeeName
                    );
            }
            var flag = base.Update(fileds, new WhereHelper(nameof(RB_Student_ViewModel.StuId), 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 group_Id)
        {
            string sql = $@"SELECT StuStage,COUNT(0) as OrderCount FROM rb_student WHERE Group_Id ={group_Id} and CustomerId ={customerId} GROUP BY 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')) = YEARWEEK(now())
");
            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();
        }
    }
}