public async Task <List <TableFieldInfo> > GetTableFieldList(string tableName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT
TableColumn = rtrim(b.name),
TableIdentity = CASE WHEN h.id IS NOT NULL THEN 'PK' ELSE '' END,
Datatype = type_name(b.xusertype)+CASE WHEN b.colstat&1=1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name))+','+CONVERT(varchar,ident_incr(a.name))+')]' ELSE '' END,
FieldLength = b.length,
IsNullable = CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END,
FieldDefault = ISNULL(e.text, ''),
Remark = (SELECT ep.value FROM sys.columns sc LEFT JOIN sys.extended_properties ep ON ep.major_id = sc.object_id AND ep.minor_id = sc.column_id
WHERE sc.object_id = a.id AND sc.name = b.name)
FROM sysobjects a, syscolumns b
LEFT OUTER JOIN syscomments e ON b.cdefault = e.id
LEFT OUTER JOIN (Select g.id, g.colid FROM sysindexes f, sysindexkeys g Where (f.id=g.id)AND(f.indid=g.indid)AND(f.indid>0)AND(f.indid<255)AND(f.status&2048)<>0) h ON (b.id=h.id)AND(b.colid=h.colid)
Where (a.id=b.id)AND(a.id=object_id(@TableName))
ORDER BY b.colid");
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());
}
private List <DbParameter> ListFilter(NewsListParam param, StringBuilder strSql, bool bNewsContent = false)
{
strSql.Append(@"SELECT a.Id,
a.BaseModifyTime,
a.BaseModifierId,
a.NewsTitle,
a.ThumbImage,
a.NewsTag,
a.NewsAuthor,
a.NewsSort,
a.NewsDate,
a.NewsType,
a.ProvinceId,
a.CityId,
a.CountyId,
a.ViewTimes");
if (bNewsContent)
{
strSql.Append(",a.NewsContent");
}
strSql.Append(@" FROM SysNews a
WHERE 1 = 1");
var parameter = new List <DbParameter>();
if (param != null)
{
if (!string.IsNullOrEmpty(param.NewsTitle))
{
strSql.Append(" AND a.NewsTitle like @NewsTitle");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsTitle", '%' + param.NewsTitle + '%'));
}
if (param.NewsType > 0)
{
strSql.Append(" AND a.NewsType = @NewsType");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsType", param.NewsType));
}
if (!string.IsNullOrEmpty(param.NewsTag))
{
strSql.Append(" AND a.NewsTag like @NewsTag");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsTag", '%' + param.NewsTag + '%'));
}
if (param.ProvinceId > 0)
{
strSql.Append(" AND a.ProvinceId = @ProvinceId");
parameter.Add(DbParameterExtension.CreateDbParameter("@ProvinceId", param.ProvinceId));
}
if (param.CityId > 0)
{
strSql.Append(" AND a.CityId = @CityId");
parameter.Add(DbParameterExtension.CreateDbParameter("@CityId", param.CityId));
}
if (param.CountyId > 0)
{
strSql.Append(" AND a.CountId = @CountId");
parameter.Add(DbParameterExtension.CreateDbParameter("@CountyId", param.CountyId));
}
}
return(parameter);
}
private List <DbParameter> ListFilter(NewsListParam param, StringBuilder strSql, bool bNewsContent = false)
{
strSql.Append(@"SELECT a.id as Id,
a.base_modify_time as BaseModifyTime,
a.base_modifier_id as BaseModifierId,
a.news_title as NewsTitle,
a.thumb_image as ThumbImage,
a.news_tag as NewsTag,
a.news_author as NewsAuthor,
a.news_sort as NewsSort,
a.news_date as NewsDate,
a.news_type as NewsType,
a.province_id as ProvinceId,
a.city_id as CityId,
a.county_id as CountyId,
a.view_times as ViewTimes");
if (bNewsContent)
{
strSql.Append(",a.news_content as NewsContent");
}
strSql.Append(@" FROM sys_news a
WHERE 1 = 1");
var parameter = new List <DbParameter>();
if (param != null)
{
if (!string.IsNullOrEmpty(param.NewsTitle))
{
strSql.Append(" AND a.news_title like @NewsTitle");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsTitle", '%' + param.NewsTitle + '%'));
}
if (param.NewsType > 0)
{
strSql.Append(" AND a.news_type = @NewsType");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsType", param.NewsType));
}
if (!string.IsNullOrEmpty(param.NewsTag))
{
strSql.Append(" AND a.news_tag like @NewsTag");
parameter.Add(DbParameterExtension.CreateDbParameter("@NewsTag", '%' + param.NewsTag + '%'));
}
if (param.ProvinceId > 0)
{
strSql.Append(" AND a.province_id = @ProvinceId");
parameter.Add(DbParameterExtension.CreateDbParameter("@ProvinceId", param.ProvinceId));
}
if (param.CityId > 0)
{
strSql.Append(" AND a.city_id = @CityId");
parameter.Add(DbParameterExtension.CreateDbParameter("@CityId", param.CityId));
}
if (param.CountyId > 0)
{
strSql.Append(" AND a.county_id = @CountyId");
parameter.Add(DbParameterExtension.CreateDbParameter("@CountyId", param.CountyId));
}
}
return(parameter);
}
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>
/// 获得可读数据权限范围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());
}