Estsh-Yiri/Estsh.Core.Services/Repositories/CommonRepository.cs

using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using SqlSugar;
using System.Data;
using System.Security.Policy;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：公共数据类，可以将一些常用的公共方法放在此类中，供其他模块调用。
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 公共数据访问层
    /// </summary>
    public class CommonRepository : BaseRepository<SysFactory>, ICommonRepository
    {
        public CommonRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 获取工厂信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getFactoryInfo()
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT factory_id as [value],factory_name as [key] from sys_factory where enabled = 'Y'");
            //List<KeyValueResult> lists = SqlSugarDbContext.Ado.SqlQuery<KeyValueResult>(strSql.ToString()).ToList();
            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 获取部门信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getDeptInfo()
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT dept_id as [value],dept_name as [key] from sys_dept where enabled = 'Y'");
            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 获取班别信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getShiftInfo()
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT shift_id as [value],shift_name as [key] from sys_shift where enabled = 'Y'");
            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 获取枚举表中的数据，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetSysEnum(String enumType)
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT enum_value as [value],enum_desc as [key] from sys_enum where enum_type=@enum_type and enabled='Y' ");
            List<SugarParameter> htParams = new List<SugarParameter>();
            htParams.Add(new SugarParameter("@enum_type", enumType));
            //List<KeyValueResult> lists = SqlSugarDbContext.Ado.SqlQuery<KeyValueResult>(strSql.ToString(), htParams).ToList();
            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).AddParameters(htParams).ToList();
            return lists;
            //}

        }

        /// <summary>
        /// 获取供应商信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getVendorInfo()
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT vendor_id as [value],vendor_name as [key] FROM sys_vendor WHERE enabled='Y' ORDER BY vendor_name");
            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 获取项目名称信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getModelTypeInfo()
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    dbConn.Open();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT type_id as [value],type_name as [key] FROM sys_model_type WHERE enabled='Y' ORDER BY type_name");

            List<KeyValueResult> lists = SqlSugarDbContext.SqlQueryable<KeyValueResult>(strSql.ToString()).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 根据菜单url获取操作权限
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public List<SysProgramFunOp> getOpMenuList(string url, string opType, string gridName)
        {
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //    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_web_menu wm ON op.fun_name = wm.menu_id ");
            SqlStringBuilder.Append("WHERE 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 ");

            List<SugarParameter> htParams = new List<SugarParameter>();
            htParams.Add(new SugarParameter("@url", url));
            htParams.Add(new SugarParameter("@opType", opType));
            if (!String.IsNullOrEmpty(gridName))
            {
                htParams.Add(new SugarParameter("@gridName", gridName));
            }

            //DynamicParameters htParams = new DynamicParameters();
            //    htParams.Add("@url", url);
            //    htParams.Add("@opType", opType);
            //    if (!String.IsNullOrEmpty(gridName))
            //    {
            //        htParams.Add("@gridName", gridName);
            //    }

            List<SysProgramFunOp> lists = SqlSugarDbContext.SqlQueryable<SysProgramFunOp>(SqlStringBuilder.ToString()).AddParameters(htParams).ToList();
            return lists;
            //}
        }

        /// <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())
            //{
            //    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 ");

            List<SugarParameter> htParams = new List<SugarParameter>();
            htParams.Add(new SugarParameter("@emp_id", empId));
            htParams.Add(new SugarParameter("@url", url));
            htParams.Add(new SugarParameter("@opType", opType));
            if (!String.IsNullOrEmpty(gridName))
            {
                htParams.Add(new SugarParameter("@gridName", gridName));
            }
            //DynamicParameters htParams = new DynamicParameters();
            //    htParams.Add("@emp_id", empId);
            //    htParams.Add("@url", url);
            //    htParams.Add("@opType", opType);
            //    if (!String.IsNullOrEmpty(gridName))
            //    {
            //        htParams.Add("@gridName", gridName);
            //    }

            List<SysProgramFunOp> lists = SqlSugarDbContext.Ado.SqlQuery<SysProgramFunOp>(SqlStringBuilder.ToString(), htParams).ToList();
            return lists;
            //}
        }

        /// <summary>
        /// 根据人员id 工厂id，通过权限 找到home页的菜单
        /// </summary>
        /// <param name="empid"></param>
        /// <param name="factoryId"></param>
        /// <returns></returns>
        public List<SysWebMenu> GetWebMenus(int empid ,int factoryId)
        {


			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder SqlStringBuilder = new StringBuilder(1024);
				SqlStringBuilder.Append("select a.name,a.menu_id,a.url ,a.icon_url,A.sort_num  from dbo.sys_web_menu as A (nolock)   ");
				SqlStringBuilder.Append("INNER join dbo.sys_role_privliege as B (nolock)  on a.menu_id=b.fun_name ");
				SqlStringBuilder.Append("INNER join sys_role_emp as D (nolock)  on d.role_id=b.role_id ");
				SqlStringBuilder.Append("INNER join sys_emp as E (nolock)  on e.emp_id=d.emp_id   ");
				SqlStringBuilder.Append("where  e.emp_id='"+ empid + "' AND E.factory_id='"+ factoryId + "' ");
				SqlStringBuilder.Append("      AND a.parent_id=0 AND a.sort_num>0 AND a.name!='PDA' AND a.enabled='Y' order by A.sort_num  ");

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


		}



    }
}