using Edu.Common.Enum;
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_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),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);
                }
            }
            return Get<RB_Account_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_Account_ViewModel> GetAccountPageListRepository(int pageIndex, int pageSize, out long rowsCount, 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), 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);
                }
            }
            return GetPage<RB_Account_ViewModel>(pageIndex,pageSize,out rowsCount,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();
            if (query != null)
            {
                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);
                }
            }

            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.*,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.*,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.*,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}
		UNION ALL
		SELECT A.*,IFNULL(B.StuName,'') AS AccountName,B.StuIcon AS UserIcon,0 AS Dept_Id,0 AS Post_Id
		FROM rb_account AS A INNER JOIN rb_student AS B ON A.AccountId=B.StuId AND A.AccountType=4
		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 list = Get<RB_Account_ViewModel>(builder.ToString()).ToList();
            return list;
        }


        /// <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();
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.Group_Id), query.Group_Id);
                }
                if (query.School_Id > 0)
                {
                    where.AppendFormat(" AND A.{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.AccountType > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.AccountType), (int)query.AccountType);
                }
                if (query.Id > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.Id), query.Id);
                }
                if (!string.IsNullOrEmpty(query.QIds))
                {
                    where.AppendFormat(" AND A.{0} IN({1}) ", nameof(Employee_ViewModel.Id), query.QIds);
                }
                if (query.AccountId > 0)
                {
                    where.AppendFormat(" AND A.{0}={1} ", nameof(Employee_ViewModel.AccountId), query.AccountId);
                }
                if (query.Post_Id > 0)
                {
                    where.AppendFormat(" AND B.{0}={1} ", nameof(Employee_ViewModel.Post_Id), query.Post_Id);
                }
                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);
                }
            }

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT 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
      ,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.*,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
		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.*,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
		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.*,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
		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());
            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
       ,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.*,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.*,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.*,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;
        }
    }
}