private List <DbParameter> ListFilter(LogOperateListParam param, StringBuilder strSql) { strSql.Append(@"SELECT a.Id, a.BaseCreateTime, a.BaseCreatorId, a.LogStatus, a.IpAddress, a.IpLocation, a.Remark, a.ExecuteUrl, a.ExecuteParam, a.ExecuteResult, a.ExecuteTime, b.UserName, c.DepartmentName FROM SysLogOperate a LEFT JOIN SysUser b ON a.BaseCreatorId = b.Id LEFT JOIN SysDepartment c ON b.DepartmentId = c.Id WHERE 1 = 1"); var parameter = new List <DbParameter>(); if (param != null) { if (!string.IsNullOrEmpty(param.UserName)) { strSql.Append(" AND b.UserName like @UserName"); parameter.Add(DbParameterExtension.CreateDbParameter("@UserName", '%' + param.UserName + '%')); } if (param.LogStatus > -1) { strSql.Append(" AND a.LogStatus = @LogStatus"); parameter.Add(DbParameterExtension.CreateDbParameter("@LogStatus", param.LogStatus)); } if (!string.IsNullOrEmpty(param.ExecuteUrl)) { strSql.Append(" AND a.ExecuteUrl like @ExecuteUrl"); parameter.Add(DbParameterExtension.CreateDbParameter("@ExecuteUrl", '%' + param.ExecuteUrl + '%')); } if (!string.IsNullOrEmpty(param.StartTime.ParseToString())) { strSql.Append(" AND a.BaseCreateTime >= @StartTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@StartTime", param.StartTime)); } if (!string.IsNullOrEmpty(param.EndTime.ParseToString())) { param.EndTime = (param.EndTime.Value.ToString("yyyy-MM-dd") + " 23:59:59").ParseToDateTime(); strSql.Append(" AND a.BaseCreateTime <= @EndTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@EndTime", param.EndTime)); } } return(parameter); }
private List <DbParameter> ListFilter(LogOperateListParam param, StringBuilder strSql) { strSql.Append(@"SELECT a.id as Id, a.base_create_time as BaseCreateTime, a.base_creator_id as BaseCreatorId, a.log_status as LogStatus, a.ip_address as IpAddress, a.ip_location as IpLocation, a.remark as Remark, a.execute_url as ExecuteUrl, a.execute_param as ExecuteParam, a.execute_result as ExecuteResult, a.execute_time as ExecuteTime, b.user_name as UserName, c.department_name as DepartmentName FROM sys_log_operate a LEFT JOIN sys_user b ON a.base_creator_id = b.id LEFT JOIN sys_department c ON b.department_id = c.id WHERE 1 = 1"); var parameter = new List <DbParameter>(); if (param != null) { if (!string.IsNullOrEmpty(param.UserName)) { strSql.Append(" AND b.user_name like @UserName"); parameter.Add(DbParameterExtension.CreateDbParameter("@UserName", '%' + param.UserName + '%')); } if (param.LogStatus > -1) { strSql.Append(" AND a.log_status = @LogStatus"); parameter.Add(DbParameterExtension.CreateDbParameter("@LogStatus", param.LogStatus)); } if (!string.IsNullOrEmpty(param.ExecuteUrl)) { strSql.Append(" AND a.execute_url like @ExecuteUrl"); parameter.Add(DbParameterExtension.CreateDbParameter("@ExecuteUrl", '%' + param.ExecuteUrl + '%')); } if (!string.IsNullOrEmpty(param.StartTime.ParseToString())) { strSql.Append(" AND a.base_modify_time >= @StartTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@StartTime", param.StartTime)); } if (!string.IsNullOrEmpty(param.EndTime.ParseToString())) { param.EndTime = (param.EndTime.Value.ToString("yyyy-MM-dd") + " 23:59:59").ParseToDateTime(); strSql.Append(" AND a.base_modify_time <= @EndTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@EndTime", param.EndTime)); } } return(parameter); }
/// <summary> /// 获取授权功能Url、操作Url /// </summary> /// <param name="userId"></param> /// <returns></returns> public async Task <List <AuthorizeUrlModel> > GetUrlList(string userId) { string strSql = string.Empty; strSql = string.Format(@" SELECT id AS AuthorizeId , id AS ModuleId, url_address AS UrlAddress , full_name AS FullName, {0} AS ViewType FROM dbo.sys_module WHERE id IN ( SELECT item_id FROM sys_authorize WHERE item_type = {0} AND ( [object_id] IN ( SELECT [object_id] FROM sys_user_relation WHERE [user_id] = @UserId ) ) OR (item_type = {0} and [object_id] = @UserId) ) AND is_enabled = 1 AND is_delete = 0 AND is_menu = 1 AND url_address IS NOT NULL UNION SELECT id AS AuthorizeId , module_id AS ModuleId, action_address AS UrlAddress , full_name AS FullName, {1} AS ViewType FROM dbo.sys_module_button WHERE id IN ( SELECT item_id FROM dbo.sys_authorize WHERE item_type = {1} AND ( [object_id] IN ( SELECT [object_id] FROM dbo.sys_user_relation WHERE [user_id] = @UserId ) ) OR (item_type = {1} and [object_id] = @UserId) ) AND is_enabled = 1 AND is_delete = 0 AND action_address IS NOT NULL", (int)ViewTypeEnum.Menu, (int)ViewTypeEnum.Button); var parameter = new List <DbParameter>(); parameter.Add(DbParameterExtension.CreateDbParameter("@UserId", userId)); var list = await this.BaseRepository().FindList <AuthorizeUrlModel>(strSql, parameter.ToArray()); return(list.ToList()); }
private List <DbParameter> ListFilter(LogLoginListParam param, StringBuilder strSql) { strSql.Append(@"SELECT a.id as Id, a.base_modify_time as BaseModifyTime, a.base_modifier_id as BaseModifierId, a.log_status as LogStatus, a.ip_address as IpAddress, a.ip_location as IpLocation, a.browser as Browser, a.os as OS, a.remark as Remark, b.user_name as UserName FROM sys_log_login a LEFT JOIN sys_user b ON a.base_modifier_id = b.id WHERE 1 = 1"); var parameter = new List <DbParameter>(); if (param != null) { if (!string.IsNullOrEmpty(param.UserName)) { strSql.Append(" AND b.user_name like @UserName"); parameter.Add(DbParameterExtension.CreateDbParameter("@UserName", '%' + param.UserName + '%')); } if (param.LogStatus > -1) { strSql.Append(" AND a.log_status = @LogStatus"); parameter.Add(DbParameterExtension.CreateDbParameter("@LogStatus", param.LogStatus)); } if (!string.IsNullOrEmpty(param.IpAddress)) { strSql.Append(" AND a.ip_address like @IpAddress"); parameter.Add(DbParameterExtension.CreateDbParameter("@IpAddress", '%' + param.IpAddress + '%')); } if (!string.IsNullOrEmpty(param.StartTime.ParseToString())) { strSql.Append(" AND a.base_modify_time >= @StartTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@StartTime", param.StartTime)); } if (!string.IsNullOrEmpty(param.EndTime.ParseToString())) { param.EndTime = (param.EndTime.Value.ToString("yyyy-MM-dd") + " 23:59:59").ParseToDateTime(); strSql.Append(" AND a.base_modify_time <= @EndTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@EndTime", param.EndTime)); } } return(parameter); }
private List <DbParameter> ListFilter(LogLoginListParam param, StringBuilder strSql) { strSql.Append(@"SELECT a.Id, a.BaseCreateTime, a.BaseCreatorId, a.LogStatus, a.IpAddress, a.IpLocation, a.Browser, a.OS, a.Remark, b.UserName FROM SysLogLogin a LEFT JOIN SysUser b ON a.BaseCreatorId = b.Id WHERE 1 = 1"); var parameter = new List <DbParameter>(); if (param != null) { if (!string.IsNullOrEmpty(param.UserName)) { strSql.Append(" AND b.UserName like @UserName"); parameter.Add(DbParameterExtension.CreateDbParameter("@UserName", '%' + param.UserName + '%')); } if (param.LogStatus > -1) { strSql.Append(" AND a.LogStatus = @LogStatus"); parameter.Add(DbParameterExtension.CreateDbParameter("@LogStatus", param.LogStatus)); } if (!string.IsNullOrEmpty(param.IpAddress)) { strSql.Append(" AND a.IpAddress like @IpAddress"); parameter.Add(DbParameterExtension.CreateDbParameter("@IpAddress", '%' + param.IpAddress + '%')); } if (!string.IsNullOrEmpty(param.StartTime.ParseToString())) { strSql.Append(" AND a.BaseCreateTime >= @StartTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@StartTime", param.StartTime)); } if (!string.IsNullOrEmpty(param.EndTime.ParseToString())) { param.EndTime = param.EndTime.Value.Date.Add(new TimeSpan(23, 59, 59)); strSql.Append(" AND a.BaseCreateTime <= @EndTime"); parameter.Add(DbParameterExtension.CreateDbParameter("@EndTime", param.EndTime)); } } return(parameter); }
public async Task<List<TableFieldInfo>> GetTableFieldList(string tableName) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT COLUMN_NAME TableColumn, DATA_TYPE Datatype, (CASE COLUMN_KEY WHEN 'PRI' THEN COLUMN_NAME ELSE '' END) TableIdentity, REPLACE(REPLACE(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)),'(',''),')','') FieldLength, (CASE IS_NULLABLE WHEN 'NO' THEN 'N' ELSE 'Y' END) IsNullable, IFNULL(COLUMN_DEFAULT,'') FieldDefault, COLUMN_COMMENT Remark FROM information_schema.columns WHERE table_schema='" + GetDatabase() + "' AND table_name=@TableName"); var parameter = new List<DbParameter>(); parameter.Add(DbParameterExtension.CreateDbParameter("@TableName", tableName)); var list = await this.BaseRepository().FindList<TableFieldInfo>(strSql.ToString(), parameter.ToArray()); return list.ToList(); }
public async Task<List<TableInfo>> GetTablePageList(string tableName, Pagination pagination) { StringBuilder strSql = new StringBuilder(); var parameter = new List<DbParameter>(); strSql.Append(@"SELECT table_name TableName FROM information_schema.tables where table_schema='" + GetDatabase() + "' and (table_type='base table' or table_type='BASE TABLE')"); if (!string.IsNullOrEmpty(tableName)) { strSql.Append(" AND table_name like @TableName "); parameter.Add(DbParameterExtension.CreateDbParameter("@TableName", '%' + tableName + '%')); } IEnumerable<TableInfo> list = await this.BaseRepository().FindList<TableInfo>(strSql.ToString(), parameter.ToArray(), pagination); await SetTableDetail(list); return list.ToList(); }
public async Task <List <TableInfo> > GetTablePageList(string tableName, Pagination pagination) { StringBuilder strSql = new StringBuilder(); var parameter = new List <DbParameter>(); strSql.Append(@"SELECT id Id,name TableName FROM sysobjects WHERE xtype = 'u'"); if (!tableName.IsEmpty()) { strSql.Append(" AND name like @TableName "); parameter.Add(DbParameterExtension.CreateDbParameter("@TableName", '%' + tableName + '%')); } IEnumerable <TableInfo> list = await this.BaseRepository().FindList <TableInfo>(strSql.ToString(), parameter.ToArray(), pagination); SetTableDetail(list); return(list.ToList()); }
/// <summary> /// 获取授权功能视图 /// </summary> /// <param name="userId">用户Id</param> /// <returns></returns> public async Task <List <ModuleColumnEntity> > GetModuleColumnList(string userId) { string strSql = string.Empty; strSql = string.Format(@" SELECT * FROM dbo.sys_module_column WHERE id IN ( SELECT item_id FROM sys_authorize WHERE item_type = '{0}' AND ( [object_id] IN ( SELECT [object_id] FROM sys_user_relation WHERE [user_id] = @UserId ) ) OR (item_type = '{0}' and [object_id] = @UserId) ) AND is_enabled=1 AND is_delete=0 Order By sort_code", (int)ViewTypeEnum.View); var parameter = new List <DbParameter>(); parameter.Add(DbParameterExtension.CreateDbParameter("@UserId", userId)); var list = await this.BaseRepository().FindList <ModuleColumnEntity>(strSql, parameter.ToArray()); return(list.ToList()); }
/// <summary> /// 为即将执行准备一个命令 /// </summary> /// <param name="conn">SqlConnection对象</param> /// <param name="cmd">SqlCommand对象</param> /// <param name="isOpenTrans">DbTransaction对象</param> /// <param name="cmdType">执行命令的类型(存储过程或T-SQL,等等)</param> /// <param name="strSql">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param> /// <param name="dbParameter">执行命令所需的sql语句对应参数</param> private void PrepareCommand(DbConnection conn, IDbCommand cmd, DbTransaction isOpenTrans, CommandType cmdType, string strSql, params DbParameter[] dbParameter) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = strSql; cmd.CommandTimeout = GlobalContext.SystemConfig.DBCommandTimeout; if (isOpenTrans != null) { cmd.Transaction = isOpenTrans; } cmd.CommandType = cmdType; if (dbParameter != null) { cmd.Parameters.Clear(); dbParameter = DbParameterExtension.ToDbParameter(dbParameter); foreach (var parameter in dbParameter) { cmd.Parameters.Add(parameter); } } }
/// <summary> /// 获得可读数据权限范围SQL /// </summary> /// <param name="userId"></param> /// <param name="isSystem"></param> /// <param name="isWrite"></param> /// <returns></returns> public async Task <string> GetDataAuthor(string userId, bool isSystem = false, bool isWrite = false) { if (isSystem) { return(""); } string strAuthorData = string.Empty; if (isWrite) { strAuthorData = @" SELECT * FROM dbo.sys_authorize_data WHERE is_read=1 AND [object_id] IN ( SELECT [object_id] FROM dbo.sys_user_relation WHERE [user_id] =@UserId) or [object_id] =@UserId"; } else { strAuthorData = @" SELECT * FROM dbo.sys_authorize_data WHERE [object_id] IN ( SELECT [object_id] FROM dbo.sys_user_relation WHERE [user_id] =@UserId) or [object_id] =@UserId"; } var parameter = new List <DbParameter>(); parameter.Add(DbParameterExtension.CreateDbParameter("@UserId", userId)); StringBuilder whereSb = new StringBuilder(" select id from dbo.sys_user where 1=1 "); whereSb.Append(string.Format("AND( id ='{0}'", userId)); var list = await this.BaseRepository().FindList <AuthorizeDataEntity>(strAuthorData, parameter.ToArray()); List <AuthorizeDataEntity> listAuthorizeData = list.ToList(); foreach (AuthorizeDataEntity item in listAuthorizeData) { switch (item.authorize_ype) { //0代表最大权限 case AuthorizationTypeEnum.All: // return(""); case AuthorizationTypeEnum.OneSelf: //本人 whereSb.Append(""); break; //本人及下属 case AuthorizationTypeEnum.OneSelfAndSubordinate: // whereSb.Append(string.Format(" OR manager_id ='{0}'", userId)); break; case AuthorizationTypeEnum.Department: whereSb.Append(string.Format(@" OR department_id = ( SELECT department_id FROM sys_user WHERE id ='{0}' )", userId)); break; case AuthorizationTypeEnum.Organization: whereSb.Append(string.Format(@" OR organize_id = ( SELECT organize_id FROM sys_user WHERE id ='{0}' )", userId)); break; case AuthorizationTypeEnum.DetailSetting: whereSb.Append(string.Format(@" OR department_id='{0}'", item.resource_id)); break; } } whereSb.Append(")"); return(whereSb.ToString()); }