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

namespace Edu.Repository.User
{
    /// <summary>
    /// 部门管理仓储层
    /// </summary>
    public class RB_DepartmentRepository : BaseRepository<RB_Department>
    {
        /// <summary>
        /// 获取部门分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowsCount"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Department_ViewModel> GetDepartmentPageListRepository(int pageIndex, int pageSize, out long rowsCount, RB_Department_ViewModel query)
        {
            rowsCount = 0;
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.*,IFNULL(B.GroupName,'') AS GroupName,IFNULL(C.SName,'') AS SchoolName
FROM rb_department AS A LEFT JOIN rb_group AS B ON A.Group_Id=B.GId
     LEFT JOIN rb_school AS C ON A.School_Id=C.SId
WHERE 1=1
");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.Group_Id)}={query.Group_Id} ");
                }
                if (query.School_Id > -1)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.School_Id)}={query.School_Id} ");
                }
                if (query.DeptTier > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptTier)}={query.DeptTier} ");
                }
                if (query.ParentId > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.ParentId)}={query.ParentId} ");
                }
                if (query.DeptId > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptId)}={query.DeptId} ");
                }
                if (query.IsCompany == 1) 
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.IsCompany)}=1 ");
                }
                if (!string.IsNullOrEmpty(query.DeptName))
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptName)} LIKE @DeptName ");
                    parameters.Add("DeptName", "%" + query.DeptName.Trim() + "%");
                }
                if (query.Status != null && (int)query.Status > -1)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.Status)}={(int)query.Status} ");
                }
                builder.Append($" ORDER BY A.{nameof(RB_Department_ViewModel.DeptSort)} ASC ");
                return GetPage<RB_Department_ViewModel>(pageIndex, pageSize, out rowsCount, builder.ToString(), parameters).ToList();
            }
            else
            {
                return new List<RB_Department_ViewModel>();
            }
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<RB_Department_ViewModel> GetDepartmentListRepository(RB_Department_ViewModel query)
        {
            DynamicParameters parameters = new DynamicParameters();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(@"
SELECT A.* 
FROM rb_department AS A
WHERE 1=1
");
            builder.Append($" AND A.{nameof(RB_Department_ViewModel.Status)}={(int)DateStateEnum.Normal} ");
            if (query != null)
            {
                if (query.Group_Id > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.Group_Id)}={query.Group_Id} ");
                }
                if (query.School_Id >= 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.School_Id)}={query.School_Id} ");
                }
                if (query.DeptTier > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptTier)}={query.DeptTier} ");
                }
                if (query.ParentId > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.ParentId)}={query.ParentId} ");
                }
                if (!string.IsNullOrEmpty(query.QParentIds))
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.ParentId)} in({query.QParentIds}) ");
                }
                if (query.DeptId > 0)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptId)}={query.DeptId} ");
                }
                if (!string.IsNullOrEmpty(query.DeptName))
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptName)} LIKE @DeptName ");
                    parameters.Add("DeptName", "%" + query.DeptName.Trim() + "%");
                }
                if (query.Status != null && (int)query.Status > -1)
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.Status)}={(int)query.Status} ");
                }
                if (!string.IsNullOrEmpty(query.QDeptIds))
                {
                    builder.Append($" AND A.{nameof(RB_Department_ViewModel.DeptId)} IN({query.QDeptIds}) ");
                }
                if (!string.IsNullOrEmpty(query.ManagerIds))
                {
                    var ManagerList = query.ManagerIds.Split(',');
                    builder.Append($" AND ");
                    for (var i = 1; i <= ManagerList.Count(); i++)
                    {
                        if (i == ManagerList.Count() && i == 1)
                        {
                            builder.Append($" FIND_IN_SET('{ManagerList[i - 1]}', A.{nameof(RB_Department_ViewModel.ManagerIds)})");
                        }
                        else if (i == 1)
                        {
                            builder.Append($" (FIND_IN_SET('{ManagerList[i - 1]}', A.{nameof(RB_Department_ViewModel.ManagerIds)}) OR ");
                        }
                        else if (i == ManagerList.Count())
                        {
                            builder.Append($" FIND_IN_SET('{ManagerList[i - 1]}', A.{nameof(RB_Department_ViewModel.ManagerIds)}))");
                        }
                        else {
                            builder.Append($" FIND_IN_SET('{ManagerList[i - 1]}', A.{nameof(RB_Department_ViewModel.ManagerIds)}) OR ");
                        }
                    }
                }
                return Get<RB_Department_ViewModel>(builder.ToString(), parameters).ToList();
            }
            else
            {
                return new List<RB_Department_ViewModel>();
            }
        }


        /// <summary>
        /// 获取当前部门的所有上级部门编号【例如:1,2,3...】
        /// </summary>
        /// <param name="DeptId"></param>
        /// <returns></returns>
        public string GetAllSuperiorDepartmentIdsRepository(object DeptId)
        {
            string deptIds = "";
            var list = GetAllSuperiorDepartmentListRepository(DeptId);
            if (list != null)
            {
                deptIds = string.Join(",", list.Select(qitem => qitem.DeptId));
            }
            return deptIds;
        }

        /// <summary>
        /// 获取当前部门的所有上级部门列表
        /// </summary>
        /// <param name="currentIds"></param>
        /// <returns></returns>
        public List<RB_Department_ViewModel> GetAllSuperiorDepartmentListRepository(object DeptId)
        {
            var parameters = new DynamicParameters();
            string procName = "proc_getsuperiordepartment";
            parameters.Add("QDeptIds", DeptId, direction: ParameterDirection.Input);
            return Get<RB_Department_ViewModel>(procName, parameters, commandType: CommandType.StoredProcedure).ToList();
        }

        /// <summary>
        /// 获取当前部门和当前部门所有的下级部门【例如:1,2,3...】
        /// </summary>
        /// <param name="DeptIds"></param>
        /// <returns></returns>
        public string GetCurrentAndChildDepartmentIdsRepository(object DeptId)
        {
            string deptIds = "";
            var list = GetCurrentAndChildDepartmentListRepository(DeptId);
            if (list != null)
            {
                deptIds = string.Join(",", list.Select(qitem => qitem.DeptId));
            }
            return deptIds;
        }

        /// <summary>
        /// 获取当前部门和当前部门所有的下级部门列表
        /// </summary>
        /// <param name="DeptId"></param>
        /// <returns></returns>
        public List<RB_Department_ViewModel> GetCurrentAndChildDepartmentListRepository(object DeptId)
        {
            var parameters = new DynamicParameters();
            string procName = "proc_getchilddepartment";
            parameters.Add("QDeptIds", DeptId, direction: ParameterDirection.Input);
            return Get<RB_Department_ViewModel>(procName, parameters, commandType: CommandType.StoredProcedure).ToList();
        }

        /// <summary>
        /// 获取所有部门负责人
        /// </summary>
        /// <param name="group_Id"></param>
        /// <returns></returns>
        public string GetAllDepartmentManagerIds(int group_Id)
        {
            string sql = $" SELECT GROUP_CONCAT(ManagerIds) AS ManagerIds FROM rb_department WHERE `Status`=0 and Group_Id ={group_Id} and ManagerIds <> '' ";
            var obj = ExecuteScalar(sql);
            return obj == null ? "" : obj.ToString();
        }
    }
}