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

namespace Edu.Repository.User
{
    /// <summary>
    /// 账号仓储层
    /// </summary>
    public class RB_AccountRepository : BaseRepository<Model.Entity.User.RB_Account>
    {
        /// <summary>
        /// 获取账号列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetAccountListRepository(RB_Account_ViewModel query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM rb_account AS A 
WHERE 1=1 
");
            builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (!string.IsNullOrWhiteSpace(query.Account))
                {
                    builder.AppendFormat(" AND a.{0} LIKE @Account ", nameof(RB_Account_ViewModel.Account));
                    parameters.Add("Account", "%" + query.Account.Trim() + "%");
                }
                if (query.AccountType > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.AccountType), (int)query.AccountType);
                }
                if (query.School_Id > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.School_Id), query.School_Id);
                }
                if (query.Group_Id > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.Id > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.Id), query.Id);
                }
                if (query.AccountId > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.AccountId), query.AccountId);
                }
            }
            return Get<RB_Account_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取后台用户账号列表扩展列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetAccountListExtRepository(RB_Account_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder where2 = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.Account))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.Account), query.Account.Trim());
                }
                if (query.AccountType > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountType), (int)query.AccountType);
                }
                if (query.Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Id), query.Id);
                }
                if (!string.IsNullOrEmpty(query.QIds))
                {
                    where.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Account_ViewModel.Id), query.QIds);
                }
                if (query.AccountId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountId), query.AccountId);
                }
                if (!string.IsNullOrEmpty(query.OpenId))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.OpenId), query.OpenId);
                }
                if (!string.IsNullOrEmpty(query.AccountName))
                {
                    where2.AppendFormat(" AND A.{0} like '%{1}%' ", nameof(RB_Account_ViewModel.AccountName), query.AccountName);
                }
                if (!string.IsNullOrEmpty(query.PostName))
                {
                    where2.AppendFormat(" AND p.{0}='{1}' ", nameof(RB_Account_ViewModel.PostName), query.PostName);
                }
                if (query.Post_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(RB_Account_ViewModel.Post_Id), query.Post_Id);
                }
                if (query.Dept_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(RB_Account_ViewModel.Dept_Id), query.Dept_Id);
                }
                if (!string.IsNullOrEmpty(query.DeptIds))
                {
                    where.AppendFormat(" AND B.{0} in({1}) ", nameof(RB_Account_ViewModel.Dept_Id), query.DeptIds);
                }
                if (query.LeaveStatus > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.LeaveStatus), query.LeaveStatus);
                }
                if (query.IsSelectLevelNor == 1)
                {
                    where.AppendFormat(" AND B.{0} <>4 ", nameof(Employee_ViewModel.LeaveStatus));
                }
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(G.GroupName,'') AS GroupName,IFNULL(s.SName,'') AS SchoolName,IFNULL(G.Logo,'') AS GroupLogo
       ,IFNULL(d.DeptName,'') AS DeptName,IFNULL(p.PostName,'') AS PostName
FROM 
(
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.MName,'') AS AccountName,B.MHead AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_manager AS B ON A.AccountId=B.MId AND A.AccountType=1
		WHERE 1=1  {0}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.TeacherName,'') AS AccountName,B.TeacherIcon AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_teacher AS B ON A.AccountId=B.TId AND A.AccountType=2
		WHERE 1=1  {0}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.AssistName,'') AS AccountName,B.AssistIcon AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_assist AS B ON A.AccountId=B.AId AND A.AccountType=3
		WHERE 1=1  {0}
) AS A LEFT JOIN rb_group AS g ON A.Group_Id=g.GId
       LEFT JOIN rb_school AS s ON A.School_Id=s.SId
       LEFT JOIN rb_department AS d ON A.Dept_Id=d.DeptId
       LEFT JOIN rb_post AS p ON A.Post_Id=p.PostId
WHERE 1=1 {1}
", where.ToString(), where2.ToString());
            var list = Get<RB_Account_ViewModel>(builder.ToString()).ToList();
            return list;
        }


        /// <summary>
        /// 获取学员账号列表扩展列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetStudentExt(RB_Account_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder where2 = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.Account))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.Account), query.Account.Trim());
                }
                if (query.AccountType > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountType), (int)query.AccountType);
                }
                if (query.Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Id), query.Id);
                }
                if (!string.IsNullOrEmpty(query.QIds))
                {
                    where.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Account_ViewModel.Id), query.QIds);
                }
                if (query.AccountId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountId), query.AccountId);
                }
                if (!string.IsNullOrEmpty(query.OpenId))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.OpenId), query.OpenId);
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(G.GroupName,'') AS GroupName,IFNULL(s.SName,'') AS SchoolName,IFNULL(G.Logo,'') AS GroupLogo
       ,IFNULL(B.StuName,'') AS AccountName
FROM   rb_account AS A INNER JOIN rb_student AS B ON A.AccountId=B.StuId 
       LEFT JOIN rb_group AS g ON A.Group_Id=g.GId
       LEFT JOIN rb_school AS s ON A.School_Id=s.SId
WHERE 1=1 {0}
", where.ToString());
            var list = Get<RB_Account_ViewModel>(builder.ToString()).ToList();
            return list;
        }

        /// <summary>
        /// 获取账户总数量
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public int GetAccountTotalNum(RB_Account_ViewModel query)
        {
            StringBuilder where = new StringBuilder();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Group_Id), query.Group_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.Account))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.Account), query.Account.Trim());
                }
                if (query.AccountType > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountType), (int)query.AccountType);
                }
                if (query.Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.Id), query.Id);
                }
                if (query.AccountId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(RB_Account_ViewModel.AccountId), query.AccountId);
                }
                if (!string.IsNullOrEmpty(query.OpenId))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(RB_Account_ViewModel.OpenId), query.OpenId);
                }
                if (query.Post_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(RB_Account_ViewModel.Post_Id), query.Post_Id);
                }
                if (query.Dept_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(RB_Account_ViewModel.Dept_Id), query.Dept_Id);
                }
                if (!string.IsNullOrEmpty(query.DeptIds))
                {
                    where.AppendFormat(" AND B.{0} in({1}) ", nameof(RB_Account_ViewModel.Dept_Id), query.DeptIds);
                }
                if (query.LeaveStatus > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.LeaveStatus), query.LeaveStatus);
                }
                if (query.IsSelectLevelNor == 1)
                {
                    where.AppendFormat(" AND B.{0} <>4 ", nameof(Employee_ViewModel.LeaveStatus));
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT count(0)
FROM 
(
		SELECT A.Id
		FROM rb_account AS A INNER JOIN rb_manager AS B ON A.AccountId=B.MId AND A.AccountType=1
		WHERE 1=1 {0}
		UNION ALL
		SELECT A.Id
		FROM rb_account AS A INNER JOIN rb_teacher AS B ON A.AccountId=B.TId AND A.AccountType=2
		WHERE 1=1 {0}
		UNION ALL
		SELECT A.Id
		FROM rb_account AS A INNER JOIN rb_assist AS B ON A.AccountId=B.AId AND A.AccountType=3
		WHERE 1=1 {0}
 
) AS t 
", where.ToString());
            var obj = ExecuteScalar(builder.ToString());
            return obj != null ? Convert.ToInt32(obj) : 0;
        }


        /// <summary>
        /// 获取员工列表【管理者、讲师、助教】
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<Employee_ViewModel> GetEmployeeListRepository(Employee_ViewModel query)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = GetEmployeeSqlReposiroty(query, parameters);
            var list = Get<Employee_ViewModel>(sql, parameters).ToList();
            return list;
        }

        /// <summary>
        /// 获取员工分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<Employee_ViewModel> GetEmployeePageListRepository(int pageIndex, int pageSize, out long rowsCount, Employee_ViewModel query)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = GetEmployeeSqlReposiroty(query, parameters);
            var list = GetPage<Employee_ViewModel>(pageIndex, pageSize, out rowsCount, sql, parameters).ToList();
            return list;
        }

        /// <summary>
        /// 获取员工查询SQL
        /// </summary>
        /// <param name="query"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public string GetEmployeeSqlReposiroty(Employee_ViewModel query, DynamicParameters parameters)
        {
            StringBuilder where = new StringBuilder();
            StringBuilder where2 = new StringBuilder();

            StringBuilder wheremanager = new StringBuilder();
            StringBuilder whereteacher = new StringBuilder();
            StringBuilder whereassist = new StringBuilder();
            where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.School_Id), query.School_Id);
                }
                if (!string.IsNullOrWhiteSpace(query.Account))
                {
                    where.AppendFormat(" AND A.{0}='{1}' ", nameof(Employee_ViewModel.Account), query.Account.Trim());
                }
                if (query.DirectSupervisor > 0)
                {
                    where.AppendFormat(" AND A.{0} ={1} ", nameof(Employee_ViewModel.DirectSupervisor), query.DirectSupervisor);
                }
                if (!string.IsNullOrEmpty(query.DirectSupervisorIds))
                {
                    where.AppendFormat(" AND A.{0} in({1}) ", nameof(Employee_ViewModel.DirectSupervisor), query.DirectSupervisorIds);
                }
                if (!string.IsNullOrEmpty(query.QIds))
                {
                    where.AppendFormat(" AND A.{0} IN({1}) ", nameof(Employee_ViewModel.Id), query.QIds);
                }
                if (query.AccountId > 0)
                {
                    wheremanager.AppendFormat(" AND b.{0}={1} ", nameof(RB_Manager_ViewModel.MId), query.AccountId);
                    whereteacher.AppendFormat(" AND b.{0}={1} ", nameof(RB_Teacher_ViewModel.TId), query.AccountId);
                    whereassist.AppendFormat(" AND b.{0}={1} ", nameof(RB_Assist_ViewModel.AId), query.AccountId);
                }
                if (query.Post_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.Post_Id), query.Post_Id);
                }
                if (query.LeaveStatus > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.LeaveStatus), (int)query.LeaveStatus);
                }
                if (query.IsLeave == 1)
                {
                    where.AppendFormat(" AND B.{0}<>{1} ", nameof(Employee_ViewModel.LeaveStatus), (int)LeaveStatusEnum.Departure);
                }
                if (query.IsLeave == 2)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.LeaveStatus), (int)LeaveStatusEnum.Departure);
                }
                if (!string.IsNullOrEmpty(query.QPostIds))
                {
                    where.AppendFormat(" AND B.{0} IN({1}) ", nameof(Employee_ViewModel.Post_Id), query.QPostIds);
                }
                if (query.Dept_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.Dept_Id), query.Dept_Id);
                }
                if (!string.IsNullOrEmpty(query.QDeptIds))
                {
                    where.AppendFormat(" AND B.{0} IN({1}) ", nameof(Employee_ViewModel.Dept_Id), query.QDeptIds);
                }
                //员工姓名
                if (!string.IsNullOrEmpty(query.EmployeeName))
                {
                    where2.AppendFormat(" AND A.{0} LIKE @EmployeeName ", nameof(Employee_ViewModel.EmployeeName));
                    parameters.Add("EmployeeName", "%" + query.EmployeeName.Trim() + "%");
                }
                if (!string.IsNullOrEmpty(query.AccountTypeStr))
                {
                    where2.AppendFormat(" AND A.{0} IN({1}) ", nameof(Employee_ViewModel.AccountType), query.AccountTypeStr);
                }

                if (!string.IsNullOrEmpty(query.StartEntryTime))
                {
                    where.AppendFormat(" AND b.{0}>='{1}' ", nameof(Employee_ViewModel.EntryTime), query.StartEntryTime);
                }
                if (!string.IsNullOrEmpty(query.EndEntryTime))
                {
                    where.AppendFormat(" AND b.{0}<='{1} 23:59:59' ", nameof(Employee_ViewModel.EntryTime), query.EndEntryTime);
                }
                if (!string.IsNullOrEmpty(query.StartLeaveTime))
                {
                    where.AppendFormat(" AND B.LeaveStatus=4 AND b.{0}>='{1}' ", nameof(Employee_ViewModel.LeaveTime), query.StartLeaveTime);
                }
                if (!string.IsNullOrEmpty(query.EndLeaveTime))
                {
                    where.AppendFormat(" AND B.LeaveStatus=4 AND b.{0}<='{1} 23:59:59' ", nameof(Employee_ViewModel.LeaveTime), query.EndLeaveTime);
                }
                if (!string.IsNullOrEmpty(query.StartBirthDate))
                {
                    where.AppendFormat($@" AND STR_TO_DATE(CONCAT(DATE_FORMAT(now(),'%Y'),'-',DATE_FORMAT(b.BirthDate,'%m-%d')), '%Y-%m-%d') >= DATE_FORMAT('{query.StartBirthDate}' , '%Y-%m-%d') ");
                }
                if (!string.IsNullOrEmpty(query.EndBirthDate))
                {
                    where.AppendFormat($@" AND STR_TO_DATE( CONCAT(DATE_FORMAT(now(),'%Y'),'-',DATE_FORMAT(b.BirthDate,'%m-%d')), '%Y-%m-%d') <= DATE_FORMAT('{query.EndBirthDate}' , '%Y-%m-%d') ");
                }
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT ar.AccountRemark,A.Id,A.Account,A.AccountId,A.Group_Id,A.School_Id,A.EmployeeName,A.UserIcon,A.IDCard,A.Sex,A.Education,A.EntryTime,A.Address,A.BirthDate
      ,A.LeaveStatus,A.LeaveTime,A.EmployeeTel,A.AccountType,A.Email,IFNULL(A.DirectSupervisor,0) AS DirectSupervisor
      ,IFNULL(G.GroupName,'') AS GroupName,IFNULL(s.SName,'') AS SchoolName
      ,IFNULL(d.DeptId,0) AS Dept_Id,IFNULL(d.DeptName,'') AS DeptName,IFNULL(p.PostId,0) AS Post_Id, IFNULL(p.PostName,'') AS PostName
      ,(CASE  WHEN CONCAT(DATE_FORMAT(now(),'%Y'),'-',DATE_FORMAT(BirthDate,'%m-%d')) >= DATE_FORMAT(now(),'%Y-%m-%d')  
               AND CONCAT(DATE_FORMAT(now(),'%Y'),'-',DATE_FORMAT(BirthDate,'%m-%d')) <= DATE_FORMAT(date_add(now(),INTERVAL 6 DAY),'%Y-%m-%d')
              THEN 1
        ELSE 0 END) IsBirth    
FROM 
(
		SELECT A.Id,A.Account,A.`Password`, 1 as AccountType,B.MId as AccountId
               ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
               ,b.School_Id,IFNULL(B.MName,'') AS EmployeeName,B.MHead AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
               ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
               ,B.LeaveStatus,B.LeaveTime,B.MTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM rb_manager AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.MId AND A.AccountType=1
		WHERE 1=1 {0} {1}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`, 2 as AccountType,B.TId as AccountId
              ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
              ,b.School_Id,IFNULL(B.TeacherName,'') AS EmployeeName,B.TeacherIcon AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
              ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
              ,B.LeaveStatus,B.LeaveTime,B.TeacherTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM rb_teacher AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.TId AND A.AccountType=2
		WHERE 1=1 {0} {2}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`, 3 as AccountType,B.AId as AccountId
              ,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,A.`Status`,A.AnnualLeaveDay,A.DirectSupervisor
              ,b.School_Id,IFNULL(B.AssistName,'') AS EmployeeName,B.AssistIcon AS UserIcon,B.Dept_Id,B.Post_Id,B.IDCard
              ,B.Sex,B.Education,B.EntryTime,B.Address,B.BirthDate
              ,B.LeaveStatus,B.LeaveTime,B.AssistTel AS EmployeeTel,IFNULL(B.Email,'') AS Email
		FROM  rb_assist AS B LEFT JOIN rb_account AS A  ON A.AccountId=B.AId AND A.AccountType=3  
		WHERE 1=1 {0} {3}
) AS A LEFT JOIN rb_group AS g ON A.Group_Id=g.GId
       LEFT JOIN rb_school AS s ON A.School_Id=s.SId
       LEFT JOIN rb_department AS d ON A.Dept_Id=d.DeptId
       LEFT JOIN rb_post AS p ON A.Post_Id=p.PostId   
LEFT JOIN (
              SELECT AccountId,AccountType,MAX(Content) AS AccountRemark 
              FROM rb_accountremark 
              WHERE `Status`=0 
              GROUP BY AccountId,AccountType 
              ORDER BY CreateTime DESC
         ) AS ar on a.AccountId=ar.AccountId AND A.AccountType=ar.AccountType
WHERE  1=1 {4}
", where.ToString(), wheremanager.ToString(), whereteacher.ToString(), whereassist.ToString(), where2.ToString());
            return builder.ToString();
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="EmpId"></param>
        /// <returns></returns>
        public Employee_ViewModel GetEmployeeInfo(int EmpId)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder where = new StringBuilder();
            where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.Id), EmpId);

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.Id,A.Account,A.Group_Id,A.School_Id,A.EmployeeName,A.UserIcon,A.AccountType
       ,IFNULL(G.GroupName,'') AS GroupName,IFNULL(s.SName,'') AS SchoolName
      ,IFNULL(d.DeptId,0) AS Dept_Id,IFNULL(d.DeptName,'') AS DeptName,IFNULL(p.PostId,0) AS Post_Id, IFNULL(p.PostName,'') AS PostName
FROM 
(
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.MName,'') AS EmployeeName,B.MHead AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_manager AS B ON A.AccountId=B.MId AND A.AccountType=1
		WHERE 1=1 {0}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.TeacherName,'') AS EmployeeName,B.TeacherIcon AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_teacher AS B ON A.AccountId=B.TId AND A.AccountType=2
		WHERE 1=1 {0}
		UNION ALL
		SELECT A.Id,A.Account,A.`Password`,A.AccountType,A.AccountId,A.CreateBy,A.CreateTime,A.UpdateBy,A.UpdateTime,A.Group_Id,B.School_Id,A.`Status`
              ,A.AnnualLeaveDay, A.DirectSupervisor,A.OpenId,A.ActivationStatus,IFNULL(B.AssistName,'') AS EmployeeName,B.AssistIcon AS UserIcon,B.Dept_Id,B.Post_Id
		FROM rb_account AS A INNER JOIN rb_assist AS B ON A.AccountId=B.AId AND A.AccountType=3
		WHERE 1=1 {0}
) AS A LEFT JOIN rb_group AS g ON A.Group_Id=g.GId
       LEFT JOIN rb_school AS s ON A.School_Id=s.SId
       LEFT JOIN rb_department AS d ON A.Dept_Id=d.DeptId
       LEFT JOIN rb_post AS p ON A.Post_Id=p.PostId
", where.ToString());
            var model = Get<Employee_ViewModel>(builder.ToString(), parameters).FirstOrDefault();
            return model;
        }

        /// <summary>
        /// 获取账号列表(批量修改密码专用)
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetUpdateAccountListRepository(List<RB_Account_ViewModel> query)
        {
            var parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            StringBuilder builderWhere = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM rb_account AS A 
WHERE 1=1 
");
            builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.Status), (int)DateStateEnum.Normal);
            if (query != null && query.Any())
            {
                if (query.FirstOrDefault().Group_Id > 0)
                {
                    builder.AppendFormat(" AND a.{0}={1} ", nameof(RB_Account_ViewModel.Group_Id), query.FirstOrDefault().Group_Id);
                }
                foreach (var item in query)
                {
                    if (item.AccountType > 0 && item.AccountId > 0)
                    {
                        if (!string.IsNullOrWhiteSpace(builderWhere.ToString()))
                        {
                            builderWhere.AppendFormat("  or ( a.{0}={1} and a.{2}={3}  ) ", nameof(RB_Account_ViewModel.AccountType), (int)item.AccountType, nameof(RB_Account_ViewModel.AccountId), item.AccountId);
                        }
                        else
                        {
                            builderWhere.AppendFormat("  ( a.{0}={1} and a.{2}={3}  ) ", nameof(RB_Account_ViewModel.AccountType), (int)item.AccountType, nameof(RB_Account_ViewModel.AccountId), item.AccountId);
                        }
                    }
                }
                builder.AppendFormat(" and (  {0}  ) ", builderWhere.ToString());
            }
            return Get<RB_Account_ViewModel>(builder.ToString(), parameters).ToList();
        }

        /// <summary>
        /// 获取OKR未填写目标的用户
        /// </summary>
        /// <param name="GroupId"></param>
        /// <returns></returns>
        public List<RB_Employee_Extend> GetOKRUserNotObjectiveList(int GroupId)
        {
            string sql = $@"
SELECT e.* 
FROM rb_employee e LEFT JOIN rb_okr_objective o on e.EmployeeId = o.CreateBy AND o.`Status` in(1,2)
WHERE e.RB_Group_id ={GroupId} AND e.IsLeave =0 AND o.Id is null";
            return Get<RB_Employee_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取OKR未打分目标的用户
        /// </summary>
        /// <param name="GroupId"></param>
        /// <returns></returns>
        public List<RB_Employee_Extend> GetOKRUserNotScoreList(int GroupId)
        {
            string sql = $@"
SELECT e.* 
FROM rb_employee e LEFT JOIN rb_okr_objective o on e.EmployeeId = o.CreateBy AND o.`Status` =2
WHERE e.RB_Group_id ={GroupId} AND e.IsLeave =0 AND o.Score =0 GROUP BY e.EmployeeId";
            return Get<RB_Employee_Extend>(sql).ToList();
        }
    }
}