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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：菜单模块数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理菜单模块的业务数据
    /// </summary>
    public class MenuRepository : BaseRepository<SysWebMenu>, IMenuRepository
    {
        public MenuRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 根据传入条件获得菜单列表数据
        /// </summary>
        public List<SysWebMenu> getList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select  * from dbo.sys_web_menu ");
                if (strWhere != null && !strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                List<SysWebMenu> result = dbConn.Query<SysWebMenu>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据登录账号获取菜单列表
        /// </summary>
        /// <param name="empId"></param>
        /// <returns></returns>
        public List<SysWebMenu> getListByUser(int empId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT m.* ");
                SqlStringBuilder.Append("FROM sys_web_menu m ");
                SqlStringBuilder.Append("	JOIN sys_role_privliege rp ON m.menu_id = rp.fun_name AND rp.program = 'web' ");
                SqlStringBuilder.Append("	JOIN sys_role_emp re ON rp.role_id = re.role_id ");
                SqlStringBuilder.Append("WHERE re.emp_id = @emp_id AND m.enabled = 'Y' ");
                Hashtable htParams = new Hashtable();
                htParams.Add("@emp_id", empId);
                List<SysWebMenu> result = dbConn.Query<SysWebMenu>(SqlStringBuilder.ToString(), htParams).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据登录账号获取首页菜单列表
        /// </summary>
        /// <param name="empId"></param>
        /// <returns></returns>
        public Hashtable getHomeMenuListByUser(int empId, string menuId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  m.*,e.emp_id,e.passwd ");
                SqlStringBuilder.Append("FROM    sys_web_menu m ");
                SqlStringBuilder.Append("        JOIN sys_role_privliege rp ON m.menu_id = rp.fun_name ");
                SqlStringBuilder.Append("         AND rp.program = 'web' ");
                SqlStringBuilder.Append("        JOIN sys_role_emp re ON rp.role_id = re.role_id ");
                SqlStringBuilder.Append("        JOIN sys_emp e ON re.emp_id = e.emp_id  ");
                SqlStringBuilder.Append("WHERE   re.emp_id = @emp_id ");
                SqlStringBuilder.Append("        AND m.enabled = 'Y' ");
                SqlStringBuilder.Append("        AND m.parent_id = 0 ");
                SqlStringBuilder.Append("        AND m.menu_id <> 0 ");
                SqlStringBuilder.Append("        AND m.menu_id = @menu_id ");
                List<SysWebMenu> menus = dbConn.Query<SysWebMenu>(SqlStringBuilder.ToString(), new { emp_id = empId, menu_id = menuId }).ToList();
                result.Add("dataList", menus);
                return result;
            }
        }

        /// <summary>
        /// 根据登录账号获取Main页主菜单列表
        /// </summary>
        /// <param name="empId"></param>
        /// <returns></returns>
        public List<SysWebMenu> getMainMenuListByUser(int empId, string menuId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  m.* ");
                SqlStringBuilder.Append("FROM    sys_web_menu m ");
                SqlStringBuilder.Append("        JOIN sys_role_privliege rp ON m.menu_id = rp.fun_name ");
                SqlStringBuilder.Append("        AND rp.program = 'web' ");
                SqlStringBuilder.Append("        JOIN sys_role_emp re ON rp.role_id = re.role_id ");
                SqlStringBuilder.Append("WHERE   re.emp_id = @emp_id ");
                SqlStringBuilder.Append("        AND m.enabled = 'Y' ");
                SqlStringBuilder.Append("        AND m.parent_id = @menu_id ");
                SqlStringBuilder.Append("ORDER BY m.sort_num ");
                Hashtable htParams = new Hashtable();
                htParams.Add("@emp_id", empId);
                menuId = menuId == null || menuId.Trim().Equals("") ? "-1" : menuId;
                List<SysWebMenu> result = dbConn.Query<SysWebMenu>(SqlStringBuilder.ToString(), new { emp_id = empId, menu_id = menuId }).ToList();
                return result;
            }
        }

        /// <summary>
        /// 更加用户获取和菜单路径获取操作权限
        /// </summary>
        /// <param name="empId"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<SysProgramFunOp> getOpMenuListByUser(int empId, string url, string opType, string gridName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State!=ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT DISTINCT op.op_name,op_class,op_method,op_params,op_type,op.sort_num ");
                SqlStringBuilder.Append("FROM    dbo.sys_program_fun_op op ");
                SqlStringBuilder.Append("        JOIN dbo.sys_role_op_privliege rop ON op.fun_name = rop.menu_id ");
                SqlStringBuilder.Append("        AND op.ruid = rop.op_id ");
                SqlStringBuilder.Append("        JOIN dbo.sys_role_emp re ON rop.role_id = re.role_id ");
                SqlStringBuilder.Append("        JOIN dbo.sys_web_menu wm ON op.fun_name = wm.menu_id ");
                SqlStringBuilder.Append("WHERE   re.emp_id = @emp_id ");
                SqlStringBuilder.Append("        AND op.enabled = 'Y' ");
                SqlStringBuilder.Append("        AND wm.url = @url ");
                SqlStringBuilder.Append("        AND op.op_type = @opType ");
                if (!String.IsNullOrEmpty(gridName))
                {
                    SqlStringBuilder.Append("        AND op.grid_name = @gridName ");
                }
                SqlStringBuilder.Append("        ORDER BY op.sort_num ");

                DynamicParameters paras = new DynamicParameters();
                paras.Add("@emp_id", empId);
                paras.Add("@url", url);
                paras.Add("@opType", opType);
                if (!String.IsNullOrEmpty(gridName))
                {
                    paras.Add("@gridName", gridName);
                }
                List<SysProgramFunOp> result = dbConn.Query<SysProgramFunOp>(SqlStringBuilder.ToString(), paras).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                Hashtable result = new Hashtable();

                DynamicParameters paras = new DynamicParameters();
                paras.Add("@Table", typeof(SysWebMenu).GetEntityTableName());
                paras.Add("@Column", " * ");
                paras.Add("@OrderColumn", OrderBy);
                paras.Add("@GroupColumn", "");
                paras.Add("@PageSize", PageSize);
                paras.Add("@CurrentPage", PageIndex);
                paras.Add("@Group", 0);
                paras.Add("@Condition", strWhere);
                paras.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                paras.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);

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

        /// <summary>
        /// 获取下拉框菜单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectMenu()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  menu_id as [value],name as [key] from dbo.sys_web_menu where parent_id < 100 AND enabled = 'Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveMenu(SysWebMenu menu)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_web_menu (name ");
                SqlStringBuilder.Append("						, description ");
                SqlStringBuilder.Append("						, url ");
                SqlStringBuilder.Append("						, parent_id ");
                SqlStringBuilder.Append("						, icon_url ");
                SqlStringBuilder.Append("						, icon_close_url ");
                SqlStringBuilder.Append("						, icon_open_url ");
                SqlStringBuilder.Append("						, icon_skin ");
                SqlStringBuilder.Append("						, enabled ");
                SqlStringBuilder.Append("						, sort_num ");
                SqlStringBuilder.Append("						,create_userid ");
                SqlStringBuilder.Append("						,create_time ");
                SqlStringBuilder.Append("						) ");
                SqlStringBuilder.Append("					VALUES(@name ");
                SqlStringBuilder.Append("						, @description ");
                SqlStringBuilder.Append("						, @url ");
                SqlStringBuilder.Append("						, @parentId ");
                SqlStringBuilder.Append("						, @iconUrl ");
                SqlStringBuilder.Append("						, @iconCloseUrl ");
                SqlStringBuilder.Append("						, @iconOpenUrl ");
                SqlStringBuilder.Append("						, @iconSkin ");
                SqlStringBuilder.Append("						, @enabled ");
                SqlStringBuilder.Append("						, @sortNum ");
                SqlStringBuilder.Append("						, @createUserid ");
                SqlStringBuilder.Append("						, CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("						) ");

                var result = dbConn.Execute(SqlStringBuilder.ToString(), menu);
                return result;
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateMenu(SysWebMenu menu)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update sys_web_menu  ");
                SqlStringBuilder.Append("SET name=@name ");
                SqlStringBuilder.Append(" , description=@description ");
                SqlStringBuilder.Append(" , url=@url ");
                SqlStringBuilder.Append(" , parent_id=@parentId ");
                SqlStringBuilder.Append(" , icon_url=@iconUrl ");
                SqlStringBuilder.Append(" , icon_close_url=@iconCloseUrl ");
                SqlStringBuilder.Append(" , icon_open_url=@iconOpenUrl ");
                SqlStringBuilder.Append(" , icon_skin=@iconSkin ");
                SqlStringBuilder.Append(" , enabled=@enabled ");
                SqlStringBuilder.Append(" , sort_num=@sortNum ");
                SqlStringBuilder.Append(" ,update_userid = @updateUserId ");
                SqlStringBuilder.Append(" ,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("WHERE menu_id=@menuId	 ");

                var result = dbConn.Execute(SqlStringBuilder.ToString(), menu);
                return result;
            }
        }

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteMenu(String menuId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                String strQuery = "SELECT * FROM sys_web_menu WHERE parent_id = @menuId";
                var exist = dbConn.QuerySingleOrDefault<SysWebMenu>(strQuery, new { menuId = menuId });
                if (exist != null && exist.MenuId > 0)
                {
                    return -2;
                }
                String delStr = "update sys_web_menu set Enabled='N'  where menu_id = @menuId";
                var result = dbConn.Execute(delStr, new { menuId = menuId });
                return result;
            }
        }
        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                String delStr = "update sys_web_menu set Enabled='Y'  WHERE menu_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State != ConnectionState.Open)
                {
                    dbConn.Open();
                }
                String delStr = "update sys_web_menu set Enabled='N'  WHERE menu_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }
        #endregion  成员方法
    }
}