using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mall.Model.Entity.Education;
using Mall.Model.Extend.Education;
using VT.FW.DB.Dapper;

namespace Mall.Repository.Education
{
    public class RB_AccountRepository : BaseRepository<RB_Account>
    {



        /// <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>
        /// 获取员工查询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), 0);
            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 (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)
                {
                    wheremanager.AppendFormat(" AND b.MId={0} ", query.AccountId);
                    whereteacher.AppendFormat(" AND b.TId={0} ", query.AccountId);
                    whereassist.AppendFormat(" AND b.AId={0} ", 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);
                }

                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.`Password`,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();
        }

    }
}
