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

namespace Edu.Repository.User
{
    /// <summary>
    /// 讲师仓储层
    /// </summary>
    public class RB_TeacherRepository : BaseRepository<Model.Entity.User.RB_Teacher>
    {
        /// <summary>
        /// 获取讲师列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Teacher_ViewModel> GetTeacherListRepository(RB_Teacher_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName 
FROM rb_teacher 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_Teacher_ViewModel.Status), (int)DateStateEnum.Normal);
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.AuditStatus), (int)AccountStatusEnum.Pass);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Group_Id), query.Group_Id);
                }
                //if (query.School_Id > 0)
                //{
                //    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.School_Id), query.School_Id);
                //}
                if (!string.IsNullOrWhiteSpace(query.TeacherName))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherName ", nameof(RB_Teacher_ViewModel.TeacherName));
                    parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherTel))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherTel ", nameof(RB_Teacher_ViewModel.TeacherTel));
                    parameters.Add("TeacherTel", "%" + query.TeacherTel.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherSay))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherSay ", nameof(RB_Teacher_ViewModel.TeacherSay));
                    parameters.Add("TeacherSay", "%" + query.TeacherSay.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherIntro))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherIntro ", nameof(RB_Teacher_ViewModel.TeacherIntro));
                    parameters.Add("TeacherIntro", "%" + query.TeacherIntro.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeachTag))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeachTag ", nameof(RB_Teacher_ViewModel.TeachTag));
                    parameters.Add("TeachTag", "%" + query.TeachTag.Trim() + "%");
                }
                if (query.TId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.TId), query.TId);
                }
                if (!string.IsNullOrEmpty(query.QTIds))
                {
                    builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Teacher_ViewModel.TId), query.QTIds);
                }
                //在职老师
                if (query.IsQLeave == 0)
                {
                    builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Teacher_ViewModel.LeaveStatus), "1,2,3");
                }
                else
                {
                    builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Teacher_ViewModel.LeaveStatus), "1,2,3,4");
                }
                if (query.IsShow > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.IsShow), query.IsShow);
                }
            }
            return Get<RB_Teacher_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_Teacher_ViewModel> GetTeacherPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Teacher_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,g.GroupName,s.SName,IFNULL(a.Account,'') AS TeacherAccount
FROM rb_teacher 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.TId=a.AccountId AND a.AccountType=2)
WHERE 1=1 
");
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > -1)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.School_Id), query.School_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherName))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherName ", nameof(RB_Teacher_ViewModel.TeacherName));
                    parameters.Add("TeacherName", "%" + query.TeacherName.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherTel))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherTel ", nameof(RB_Teacher_ViewModel.TeacherTel));
                    parameters.Add("TeacherTel", "%" + query.TeacherTel.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherSay))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherSay ", nameof(RB_Teacher_ViewModel.TeacherSay));
                    parameters.Add("TeacherSay", "%" + query.TeacherSay.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeacherIntro))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeacherIntro ", nameof(RB_Teacher_ViewModel.TeacherIntro));
                    parameters.Add("TeacherIntro", "%" + query.TeacherIntro.Trim() + "%");
                }
                if (!string.IsNullOrWhiteSpace(query.TeachTag))
                {
                    builder.AppendFormat(" AND t.{0} LIKE @TeachTag ", nameof(RB_Teacher_ViewModel.TeachTag));
                    parameters.Add("TeachTag", "%" + query.TeachTag.Trim() + "%");
                }
                if (query.TId > 0)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.TId), query.TId);
                }
                if (query.IsShow != -1)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.IsShow), query.IsShow);
                }
                if ((int)query.AuditStatus != -1)
                {
                    builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.AuditStatus), (int)query.AuditStatus);
                }
            }
            return GetPage<RB_Teacher_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取教师账户
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Teacher_ViewModel> GetTeacherList(string teacherIds)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,a.Id as AccountId,s.SName,d.DeptName
FROM rb_teacher AS t 
     LEFT JOIN rb_account as a ON (t.TId=a.AccountId AND a.AccountType=2)
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
     left join rb_department as d on t.Dept_Id = d.DeptId
WHERE 1=1 
");
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Status), (int)DateStateEnum.Normal);
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.AuditStatus), (int)AccountStatusEnum.Pass);
            builder.AppendFormat(" AND t.{0} IN({1}) ", nameof(RB_Teacher_ViewModel.TId), teacherIds);
            return Get<RB_Teacher_ViewModel>(builder.ToString(), parameters).ToList();
        }


        /// <summary>
        /// 根据学生id获取教师账户,必须是未开班/学习中的
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Teacher_ViewModel> GetListByStudentId(int Student_Id, int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat($@"
SELECT t.* from rb_teacher as t
 LEFT JOIN (SELECT ClassId,TeacherId from rb_class_plan where `Status`=0 GROUP BY ClassId,TeacherId) as cp on t.TId=cp.TeacherId
LEFT JOIN rb_class as c on cp.ClassId=c.ClassId
LEFT JOIN rb_student_orderguest as sog on sog.ClassId=c.ClassId
where t.`Status`=0 and c.`Status`=0 and sog.`Status`=0  and c.ClassStatus in(1,2) AND c.Group_Id={Group_Id}");
            builder.AppendFormat(" AND sog.Account_Id={0} ", Student_Id);
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Group_Id), Group_Id);
            return Get<RB_Teacher_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取教师账户
        /// </summary>
        /// <param name="teacherIds"></param>
        /// <returns></returns>
        public List<RB_Teacher_ViewModel> GetTeacherByTId(int Id, int Group_Id)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT t.*,a.Id as AccountId,s.SName,d.DeptName
FROM rb_teacher AS t 
     LEFT JOIN rb_account as a ON (t.TId=a.AccountId AND a.AccountType=2)
     LEFT JOIN rb_school AS s ON t.School_Id=s.SId
     left join rb_department as d on t.Dept_Id = d.DeptId
WHERE 1=1 
");
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.Status), (int)DateStateEnum.Normal);
            builder.AppendFormat(" AND t.{0}={1} ", nameof(RB_Teacher_ViewModel.AuditStatus), (int)AccountStatusEnum.Pass);
            builder.AppendFormat(" AND t.{0}  ={1} ", nameof(RB_Teacher_ViewModel.TId), Id);
            if (Group_Id > 0)
            {
                builder.AppendFormat(" AND a.Group_Id  ={0} ", Group_Id);
            }
            
            return Get<RB_Teacher_ViewModel>(builder.ToString(), parameters).ToList();
        }

    }
}