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());
}
