using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：角色管理数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理角色模块的业务数据
    /// </summary>
    public class RoleRepository : BaseRepository<SysRole>, IRoleRepository
    {
        public RoleRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        #region 角色管理

        /// <summary>
        /// 根据分页条件获取角色列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public Hashtable getRoleListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append("role_id,role_name,role_desc,enabled");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", "sys_role a");
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<SysRole> list = dbConn.Query<SysRole>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", list);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 根据角色ID，获取角色功能树数据
        /// </summary>
        /// <param name="role_id"></param>
        /// <returns></returns>
        public List<SysWebMenu> getRoleMenuTree(String role_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT m.menu_id ");
                SqlStringBuilder.Append("	 , m.name ");
                SqlStringBuilder.Append("	 , m.parent_id ");
                SqlStringBuilder.Append("	 , (SELECT rp.role_id ");
                SqlStringBuilder.Append("		FROM sys_role_privliege rp ");
                SqlStringBuilder.Append("		WHERE rp.fun_name = m.menu_id ");
                SqlStringBuilder.Append("	   AND rp.program = 'web' ");
                SqlStringBuilder.Append("	   AND rp.role_id = @role_id) AS role_id ");
                SqlStringBuilder.Append("FROM sys_web_menu m ");
                SqlStringBuilder.Append("WHERE m.enabled = 'Y' ");

                DynamicParameters htParams = new DynamicParameters();
                htParams.Add("@role_id", role_id);
                List<SysWebMenu> result = dbConn.Query<SysWebMenu>(SqlStringBuilder.ToString(), htParams).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据角色ID，获取角色功能树数据
        /// </summary>
        /// <param name="role_id"></param>
        /// <returns></returns>
        public List<SysProgramFunOp> getRoleMenuOpTree(String role_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  op.fun_name AS parent_id, op.op_name, op.ruid AS op_id, ");
                SqlStringBuilder.Append("        ( SELECT rop.role_id ");
                SqlStringBuilder.Append("         FROM sys_role_op_privliege rop ");
                SqlStringBuilder.Append("         WHERE rop.menu_id = op.fun_name ");
                SqlStringBuilder.Append("          AND rop.op_id = op.ruid ");
                SqlStringBuilder.Append("          AND rop.role_id = @role_id ");
                SqlStringBuilder.Append("       ) AS role_id ");
                SqlStringBuilder.Append("FROM   dbo.sys_program_fun_op op ");
                SqlStringBuilder.Append("WHERE  op.program = 'web' ");
                SqlStringBuilder.Append("       AND op.enabled = 'Y' ");

                DynamicParameters htParams = new DynamicParameters();
                htParams.Add("@role_id", role_id);

                List<SysProgramFunOp> result = dbConn.Query<SysProgramFunOp>(SqlStringBuilder.ToString(), htParams).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据角色ID获取角色信息
        /// </summary>
        /// <param name="role_id"></param>
        /// <returns></returns>
        public List<SysRole> getRoleInfoById(String role_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT role_id, role_name, role_desc, enabled FROM sys_role where role_id = @role_id ");
                DynamicParameters htParams = new DynamicParameters();
                htParams.Add("@role_id", role_id);
                List<SysRole> result = dbConn.Query<SysRole>(strSql.ToString(), htParams).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入角色数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveRoleInfo(SysRole htParams, string menuTreeInfo, string opMenuTreeInfo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strQuery = "SELECT role_id FROM sys_role where role_name = @role_name";
                DynamicParameters htQuery = new DynamicParameters();
                htQuery.Add("@role_name", htParams.RoleName);
                List<SysRole> result = dbConn.Query<SysRole>(strQuery, htQuery).ToList();

                if (result.Count > 0)
                {
                    return 2;
                }
                else
                {
                    List<string> sqlStrings = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();

                    String menuTree = menuTreeInfo;
                    String opMenuTree = opMenuTreeInfo;
                    String createUserId = htParams.CreateUserId.ToString();
                    //htParams.Remove("@menuTree");
                    String[] roleArray = menuTree.Split(',');
                    String[] opRoleArray = opMenuTree.Split(',');

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append(" INSERT INTO sys_role(role_name,role_desc,enabled,create_userid,create_time) ");
                    SqlStringBuilder.Append(" VALUES(@roleName,@roleDesc,@enabled,@createUserId, ");
                    SqlStringBuilder.Append(" CONVERT(varchar(50), GETDATE(), 21)) ");
                    int insert = dbConn.Execute(SqlStringBuilder.ToString(), htParams);

                    object role_id = dbConn.ExecuteScalar(strQuery, htQuery);
                    DynamicParameters dyParams = new DynamicParameters();
                    if (insert > 0)
                    {
                        SqlStringBuilder.Clear();
                        SqlStringBuilder.Append("INSERT INTO sys_role_privliege(role_id,program,fun_name,auth_seq,create_userid,create_time) ");
                        SqlStringBuilder.Append(" VALUES(@role_id,'web',@menu_id,31,@create_userid, ");
                        SqlStringBuilder.Append(" CONVERT(varchar(50), GETDATE(), 21)) ");
                        foreach (String menu_id in roleArray)
                        {
                            dyParams = new DynamicParameters();
                            dyParams.Add("@role_id", role_id);
                            dyParams.Add("@menu_id", menu_id);
                            dyParams.Add("@create_userid", createUserId);
                            sqlStrings.Add(SqlStringBuilder.ToString());
                            parameters.Add(dyParams);
                        }
                        SqlStringBuilder.Clear();
                        SqlStringBuilder.Append("INSERT  INTO dbo.sys_role_op_privliege ( role_id, menu_id, op_id, create_userid, create_time ) ");
                        SqlStringBuilder.Append("VALUES  (@role_id, @menu_id, @op_id, @create_userid, CONVERT(varchar(50), GETDATE(), 21)) ");
                        string opId = "";
                        string menuId = "";
                        foreach (String opMenu in opRoleArray)
                        {
                            opId = opMenu.Split('|')[0];
                            menuId = opMenu.Split('|')[1];
                            dyParams = new DynamicParameters();
                            dyParams.Add("@role_id", role_id);
                            dyParams.Add("@menu_id", menuId);
                            dyParams.Add("@op_id", opId);
                            dyParams.Add("create_userid", createUserId);
                            sqlStrings.Add(SqlStringBuilder.ToString());
                            parameters.Add(dyParams);
                        }

                        IDbTransaction transaction = dbConn.BeginTransaction();
                        try
                        {
                            for (int i = 0; i < sqlStrings.Count; i++)
                            {
                                 dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            return 0;
                        }

                        return 1;

                    }
                    else
                    {
                        return 0;
                    }
                }

            }
        }

        /// <summary>
        /// 更新角色数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateRoleInfo(SysRole htParams, string menuTreeInfo, string opMenuTreeInfo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlStrings = new List<string>();
                List<SysRole> parameters = new List<SysRole>();

                String menuTree = menuTreeInfo;
                String updateUserId = htParams.UpdateUserId.ToString();
                String opMenuTree = opMenuTreeInfo;
                String role_id = htParams.RoleId.ToString();
                //htParams.Remove("@menuTree");
                //htParams.Remove("@role_name");
                String[] roleArray = menuTree.Split(',');
                String[] opRoleArray = opMenuTree.Split(',');

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_role ");
                SqlStringBuilder.Append("SET ");
                SqlStringBuilder.Append("	role_desc = @roleDesc,  ");
                SqlStringBuilder.Append("	enabled = @enabled,  ");
                SqlStringBuilder.Append("	update_userid = @updateUserId, ");
                SqlStringBuilder.Append("	update_time =CONVERT(varchar(50), GETDATE(), 21) ");
                SqlStringBuilder.Append("WHERE ");
                SqlStringBuilder.Append("	role_id = @roleId ");
                sqlStrings.Add(SqlStringBuilder.ToString());
                parameters.Add(htParams);

                SqlStringBuilder.Clear();
                List<string> dysqlStrings = new List<string>();
                List<DynamicParameters> dyparameters = new List<DynamicParameters>();
                //SqlStringBuilder.Append(" DELETE FROM sys_role_privliege WHERE role_id = @role_id AND program = 'web' ");
                SqlStringBuilder.Append(" DELETE FROM sys_role_privliege WHERE role_id = @role_id ");
                DynamicParameters dyParams = new DynamicParameters();
                dyParams.Add("@role_id", role_id);
                dysqlStrings.Add(SqlStringBuilder.ToString());
                dyparameters.Add(dyParams);

                SqlStringBuilder.Clear();
                SqlStringBuilder.Append(" DELETE FROM sys_role_op_privliege WHERE role_id = @role_id ");
                dyParams = new DynamicParameters();
                dyParams.Add("@role_id", role_id);
                dysqlStrings.Add(SqlStringBuilder.ToString());
                dyparameters.Add(dyParams);

                SqlStringBuilder.Clear();
                SqlStringBuilder.Append("INSERT INTO sys_role_privliege(role_id,program,fun_name,auth_seq,create_userid,create_time) ");
                SqlStringBuilder.Append(" VALUES(@role_id,'web',@menu_id,31,@create_userid, ");
                SqlStringBuilder.Append(" CONVERT(varchar(50), GETDATE(), 21)) ");
                foreach (String menu_id in roleArray)
                {
                    if (menu_id != null && !"".Equals(menu_id.Trim()))
                    {
                        dyParams = new DynamicParameters();
                        dyParams.Add("@role_id", role_id);
                        dyParams.Add("@menu_id", menu_id);
                        dyParams.Add("@create_userid", updateUserId);
                        dysqlStrings.Add(SqlStringBuilder.ToString());
                        dyparameters.Add(dyParams);
                    }
                }
                SqlStringBuilder.Clear();
                SqlStringBuilder.Append("INSERT  INTO dbo.sys_role_op_privliege ( role_id, menu_id, op_id, create_userid , create_time) ");
                SqlStringBuilder.Append("VALUES  (@role_id, @menu_id, @op_id, @create_userid, CONVERT(varchar(50), GETDATE(), 21)) ");
                string opId = "";
                string menuId = "";
                foreach (String opMenu in opRoleArray)
                {
                    opId = opMenu.Split('|')[0];
                    menuId = opMenu.Split('|')[1];
                    dyParams = new DynamicParameters();
                    dyParams.Add("@role_id", role_id);
                    dyParams.Add("@menu_id", menuId);
                    dyParams.Add("@op_id", opId);
                    dyParams.Add("@create_userid", updateUserId);
                    dysqlStrings.Add(SqlStringBuilder.ToString());
                    dyparameters.Add(dyParams);
                }

                int ret = 0;
                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < sqlStrings.Count; i++)
                    {
                        ret = dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                    }

                    for (int i = 0; i < dysqlStrings.Count; i++)
                    {
                        ret = dbConn.Execute(dysqlStrings[i], dyparameters[i], transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return 0;
                }
                return 1;
            }
        }

        /// <summary>
        /// 删除角色数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteRole(String role_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters htparams = new DynamicParameters();
                String strQuery = "SELECT 1 FROM sys_role_emp WHERE role_id = @role_id";
                htparams.Add("@role_id", role_id);
                List<SysRoleEmp> exist = dbConn.Query<SysRoleEmp>(strQuery, htparams).ToList();

                if (exist.Count > 0)
                {
                    return -2;
                }
                StringBuilder delStr = new StringBuilder();
                delStr.Append(" update sys_role set Enabled='N'  WHERE role_id = @role_id ");
                delStr.Append(" update sys_role_privliege set Enabled='N'  WHERE role_id = @role_id1 AND program = 'web' ");
                htparams = new DynamicParameters();
                htparams.Add("@role_id", role_id);
                htparams.Add("@role_id1", role_id);
                int ret = dbConn.Execute(delStr.ToString(), htparams);
                return ret;
            }
        }

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters htparams = new DynamicParameters();
                StringBuilder delStr = new StringBuilder();
                delStr.Append(" update sys_role set Enabled='Y'  WHERE role_id = @role_id ");
                delStr.Append(" update sys_role_privliege set Enabled='Y'  WHERE role_id = @role_id1 AND program = 'web' ");
                htparams = new DynamicParameters();
                htparams.Add("@role_id", ids);
                htparams.Add("@role_id1", ids);
                int ret = dbConn.Execute(delStr.ToString(), htparams);
                return ret;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters htparams = new DynamicParameters();
                //String strQuery = "SELECT 1 FROM sys_role_emp WHERE role_id = @role_id";
                //htparams.Add("@role_id", ids);
                //List<SysRoleEmp> exist = dbConn.Query<SysRoleEmp>(strQuery, htparams).ToList();

                //if (exist.Count > 0)
                //{
                //    return -2;
                //}
                StringBuilder delStr = new StringBuilder();
                delStr.Append(" update sys_role set Enabled='N'  WHERE role_id = @role_id ");
                delStr.Append(" update sys_role_privliege set Enabled='N'  WHERE role_id = @role_id1 AND program = 'web' ");
                htparams = new DynamicParameters();
                htparams.Add("@role_id", ids);
                htparams.Add("@role_id1", ids);
                int ret = dbConn.Execute(delStr.ToString(), htparams);
                return ret;
            }
        }

        #endregion
    }
}