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') ");
                }
                if (query.IsLeave == 1)
                {
                    where.AppendFormat(" AND B.LeaveStatus <>4 ");
                }
            }

            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();
        }





        /// <summary>
        /// 判断用户是否有某项功能权限
        /// </summary>
        /// <param name="actionCode">权限编码</param>
        /// <param name="postIds">岗位编号【可以多个】</param>
        /// <returns></returns>
        public bool CheckUserFunctionRepository(string actionCode, string postIds)
        {
            int result = 0;
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT 1
FROM RB_Menu_Function AS A 
WHERE 1=1 AND A.FunctionCode='{0}' AND A.Id IN(SELECT Action_Id FROM rb_role_functionpermission WHERE Role_Id IN (SELECT RoleId FROM rb_post_role WHERE PostId IN({1})))
", actionCode.Trim(), postIds);
            var obj = base.ExecuteScalar(builder.ToString());
            if (obj != null)
            {
                Int32.TryParse(obj.ToString(), out result);
            }
            return result > 0;
        }


        /// <summary>
        /// 获取学员账号列表扩展列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetStudentExt(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 (!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.QAccountIds))
                {
                    where.AppendFormat(" AND A.{0} IN({1}) ", nameof(RB_Account_ViewModel.AccountId), query.QAccountIds);
                }
                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,B.StuIcon as UserIcon
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="accountId"></param>
        /// <returns></returns>
        public int GetStuLearningCourse(int accountId,out int FreeCoffeeNum, out int courseId)
        {
            string sql = $@"SELECT COUNT(0) AS IsSelectLevelNor,max(IFNULL(c.FreeCoffeeNum,0)) as FreeCoffeeNum, max(o.CourseId) as LeaveStatus FROM rb_account a
INNER JOIN rb_student s on a.AccountId = s.StuId
INNER JOIN rb_student_orderguest sog on sog.Student_Id = s.StuId
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
left join rb_course c on o.CourseId = c.CourseId
WHERE a.`Status` =0 and a.AccountType =4 and a.Id ={accountId} and og.GuestState not in(2,7) and o.OrderState =1 and o.OrderType =1 and og.TotalHours > og.CompleteHours";

            var rmodel = Get<RB_Account_ViewModel>(sql).FirstOrDefault();
            FreeCoffeeNum = rmodel?.FreeCoffeeNum ?? 0;
            courseId = rmodel?.LeaveStatus ?? 0;
            return rmodel?.IsSelectLevelNor ?? 0;
        }

        /// <summary>
        /// 获取 正在学习中的学生列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Account_ViewModel> GetStuLearningCourseList(int groupId =100000)
        {
            string sql = $@"SELECT a.Id,a.AccountId,IFNULL(s.StuName,'') AS AccountName,s.StuIcon as UserIcon, max(IFNULL(c.FreeCoffeeNum,0)) as FreeCoffeeNum
FROM rb_account a
INNER JOIN rb_student s on a.AccountId = s.StuId
INNER JOIN rb_student_orderguest sog on sog.Student_Id = s.StuId
INNER JOIN rb_order_guest og on sog.GuestId = og.Id
INNER JOIN rb_order o on sog.OrderId = o.OrderId
left join rb_course c on o.CourseId = c.CourseId
WHERE a.`Status` =0 and a.Group_Id ={groupId} and a.AccountType =4 and case when o.CourseId =1 then 1=1 else og.GuestState not in(2,7) end and o.OrderState =1 and o.OrderType =1 and og.TotalHours > og.CompleteHours
group by a.Id";
            return Get<RB_Account_ViewModel>(sql).ToList();
        }

        /// <summary>
        /// 根据部门ID 获取 部门名称
        /// </summary>
        /// <param name="deptId"></param>
        /// <returns></returns>
        public string GetDeptmentName(int deptId) {
            string sql = $@"select DeptName from rb_department where DeptId ={deptId}";
            var robj = ExecuteScalar(sql);
            return robj == null ? "" : robj.ToString();
        }

    }
}
