public override IEnumerable <string> GetFieldEnumerable(TableMapperAttribute tableMapper)
{
var list = EntityTools.GetFields <CommentInfo>().ToList();
// list.Add($@"
//(
// {SqlCharConst.SELECT} {SqlCharConst.COUNT}(0)
// {SqlCharConst.FROM} {EntityTools.GetTableName<CommentInfo>()}
// {SqlCharConst.WHERE} {EntityTools.GetField<CommentInfo>(nameof(CommentInfo.ReplyId))} = {tableMapper.Alias}.{nameof(BaseModel.Id)}
// {SqlCharConst.AND} {EntityTools.GetField<CommentInfo>(nameof(CommentInfo.ValidFlag))} = {(int)ValidFlagMenu.UseFul}
// {SqlCharConst.AND} {EntityTools.GetField<CommentInfo>(nameof(CommentInfo.CommentType))} = {(int)CommentTypeMenu.Article}
//)
// AS {EntityTools.GetField<CommentDto>(nameof(CommentDto.ReplyCount))}
//");
list.Add($@"
(
SELECT displayName FROM user_info
WHERE id = (
SELECT actionUser FROM comment_info
WHERE id = {tableMapper.Alias}.replyId
)
) AS {nameof(CommentDto.ReplyUserName)}
");
return(list);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <param name="conn"></param>
/// <param name="whereEnumerable"></param>
/// <param name="param"></param>
/// <param name="loadNow"></param>
/// <returns></returns>
public static Task <PageModel <IEnumerable <T> > > GetPageList <T>(int pageNo, int pageSize, IDbConnection conn,
IEnumerable <string> whereEnumerable, object param = null, bool loadNow = true) where T : BaseModel
{
return(GetPageList <T>(pageNo, pageSize, conn, EntityTools.GetTableName <T>(), whereEnumerable,
EntityTools.GetFields <T>(), new[]
{
SqlCharConst.DefaultOrder
}, nameof(T), param, loadNow));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <param name="whereEnumerable"></param>
/// <param name="param"></param>
/// <returns></returns>
public static async Task <T> GetItem <T>(IDbConnection conn, string tableName,
IEnumerable <string> whereEnumerable, object param = null)
{
var whereSql = GetWhereSql(whereEnumerable);
var sql = $@"
{SqlCharConst.SELECT} {string.Join(",", EntityTools.GetFields<T>())}
{SqlCharConst.FROM} {tableName}
{whereSql}";
Logger.Debug($"{nameof(GetItem)}:{sql}");
return(await conn.QueryFirstOrDefaultAsync <T>(sql, param));
}
public async Task <ResultModel> Run(AcceptParam acceptParam, AppSetting appSetting, HttpContext context)
{
var mysqlConn = appSetting.GetMysqlConn();
var dbConnection = context.GetConnection(mysqlConn.FlagKey, mysqlConn.ConnStr);
IEnumerable <ArticleInfo> articleInfos = await dbConnection.QueryAsync <ArticleInfo>($@"
{SqlCharConst.SELECT} {string.Join(",", EntityTools.GetFields<ArticleInfo>())}
{SqlCharConst.FROM} {EntityTools.GetTableName<ArticleInfo>()}
{SqlCharConst.WHERE} {SqlCharConst.DefaultWhere}
");
return(ResultModel.GetSuccessModel(string.Empty, articleInfos.ToList()));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <param name="param"></param>
/// <returns></returns>
public static async Task <int> CreateItem <T>(IDbConnection conn, string tableName, T param)
{
var sql = $@"
{SqlCharConst.INSERT} {SqlCharConst.INTO} {tableName}
(
{string.Join(",", EntityTools.GetFields<T>())}
)
{SqlCharConst.VALUES}
(
{string.Join(",", EntityTools.GetFields<T>("@"))}
)
";
Logger.Debug($"{nameof(CreateItem)}:{sql}");
var result = await conn.ExecuteAsync(sql, param);
return(result);
}
public async Task <ResultModel> Run(AcceptParam acceptParam, AppSetting appSetting, HttpContext context, long userId)
{
var req = acceptParam.AnalyzeParam <PageModel <AttentionPageFilterReq> >();
if (req == null || req.PageNo == 0 || req.Result == null)
{
return(ResultModel.GetParamErrorModel());
}
var conn = appSetting.GetMysqlConn(context);
var attentionMapper = typeof(AttentionInfo).GetCustomAttribute <TableMapperAttribute>();
var userMapper = typeof(UserInfo).GetCustomAttribute <TableMapperAttribute>();
var joinRelation = string.Empty;
var whereList = new List <string>()
{
$"{attentionMapper.Alias}.{nameof(BaseModel.ValidFlag)}={(int) ValidFlagMenu.UseFul}"
};
if (req.Result.FilterType == 1)
{
whereList.Add(
$"{attentionMapper.Alias}.{EntityTools.GetField<AttentionInfo>(nameof(AttentionInfo.AttentionUser))}={userId}");
joinRelation = $"{attentionMapper.Alias}.userId = {userMapper.Alias}.id";
}
else
{
whereList.Add(
$"{attentionMapper.Alias}.{EntityTools.GetField<AttentionInfo>(nameof(AttentionInfo.UserId))}={userId}");
joinRelation = $"{attentionMapper.Alias}.attentionUser = {userMapper.Alias}.id";
}
var whereSql = DapperTools.GetWhereSql(whereList);
var selectCountSql = $@"
{SqlCharConst.SELECT} {SqlCharConst.COUNT}(1)
{SqlCharConst.FROM} {attentionMapper.TableName} {SqlCharConst.AS} {attentionMapper.Alias}
{whereSql}
";
Logger.Debug(selectCountSql);
var count = await conn.QueryFirstAsync <int>(selectCountSql);
var resultPage = new PageModel <IEnumerable <AttentionInfoDto> >()
{
Count = count,
PageNo = req.PageNo,
PageSize = req.PageSize
};
if ((req.PageNo - 1) * req.PageSize <= count)
{
var fieldList = new List <string>();
fieldList.AddRange(EntityTools.GetFields <AttentionInfoDto>($"{attentionMapper.Alias}."
, null
, (propertyInfo => propertyInfo.Name != nameof(AttentionInfoDto.UserInfo))));
fieldList.AddRange(EntityTools.GetFields <UserInfoDto>($"{userMapper.Alias}."));
var pageSql = $@"
{SqlCharConst.SELECT}
{string.Join(',', fieldList)}
{SqlCharConst.FROM}
{attentionMapper.TableName} {SqlCharConst.AS} {attentionMapper.Alias}
{SqlCharConst.INNER} {SqlCharConst.JOIN}
{userMapper.TableName} {SqlCharConst.AS} {userMapper.Alias}
{SqlCharConst.ON} {joinRelation}
{DapperTools.GetWhereSql(whereList)}
{SqlCharConst.ORDERBY}
{attentionMapper.Alias}.{nameof(BaseModel.UpdateTime)} {SqlCharConst.DESC},
{attentionMapper.Alias}.{nameof(BaseModel.CreateTime)} {SqlCharConst.DESC}
{SqlCharConst.LIMIT} {(req.PageNo - 1) * req.PageSize},{req.PageSize}
";
Logger.Debug(pageSql);
var info = await conn.QueryAsync <AttentionInfoDto, UserInfoDto, AttentionInfoDto>(pageSql,
(attention, user) =>
{
attention.UserInfo = user;
return(attention);
}
//The field we should split and read the second object from (default: "Id")
//当select中的列出现 不同表的重复列名 需要使用在此配置
//,splitOn: "Name,BookID"
);
resultPage.Result = info.ToList();
// return ResultModel.GetSuccessModel(info);
}
// var result = await DapperTools.GetPageList<AttentionInfo>(req.PageNo, req.PageSize, conn, whereList);
// return ResultModel.GetSuccessModel(result);
return(ResultModel.GetSuccessModel(resultPage));
}
public async Task <ResultModel> Run(AcceptParam acceptParam, AppSetting appSetting, HttpContext context, long userId)
{
//解析参数
var pageModel = acceptParam.AnalyzeParam <PageModel <ArticlePageFilterAuthReq> >();
if (pageModel == null)
{
return(ResultModel.GetNullErrorModel(string.Empty));
}
//动态sql
var whereArr = new List <string>()
{
$"{EntityTools.GetField<ArticleInfo>(nameof(ArticleInfo.ValidFlag))} = {(int) ValidFlagMenu.UseFul}",
$"{EntityTools.GetField<ArticleInfo>(nameof(ArticleInfo.UserId))} = {userId}"
};
var tableMapper = typeof(ArticleInfo).GetCustomAttribute <TableMapperAttribute>();
var fields = EntityTools.GetFields <ArticleInfo>().ToList();
fields.Add($@"
(
{SqlCharConst.SELECT} {SqlCharConst.COUNT}(0)
{SqlCharConst.FROM} {EntityTools.GetTableName<CommentInfo>()}
{SqlCharConst.WHERE} {EntityTools.GetField<CommentInfo>(nameof(CommentInfo.CommentType))} = {(int) CommentTypeMenu.Article}
{SqlCharConst.AND} {EntityTools.GetField<CommentInfo>(nameof(CommentInfo.JoinKey))} = {tableMapper.Alias}.{nameof(BaseModel.Id)}
{SqlCharConst.AND} {nameof(BaseModel.ValidFlag)} = {(int) ValidFlagMenu.UseFul}
) {SqlCharConst.AS} {EntityTools.GetField<ArticleInfoDto>(nameof(ArticleInfoDto.CommentCount))}
"); //评论数量
fields.Add($@"
(
{SqlCharConst.SELECT} {SqlCharConst.COUNT}(0)
{SqlCharConst.FROM} {EntityTools.GetTableName<ArticleOptInfo>()}
{SqlCharConst.WHERE} {EntityTools.GetField<ArticleOptInfo>(nameof(ArticleOptInfo.OptionType))} = {(int) ArticleOptMenu.Like}
{SqlCharConst.AND} {EntityTools.GetField<ArticleOptInfo>(nameof(ArticleOptInfo.RelationKey))} = {tableMapper.Alias}.{nameof(BaseModel.Id)}
{SqlCharConst.AND} {nameof(BaseModel.ValidFlag)} = {(int) ValidFlagMenu.UseFul}
) {SqlCharConst.AS} {EntityTools.GetField<ArticleInfoDto>(nameof(ArticleInfoDto.LikeCount))}
"); //点赞数量
if (pageModel.Result != null && pageModel.Result.ArticleType > 0)
{
if (pageModel.Result.ArticleType > 0)
{
whereArr.Add($@"
{EntityTools.GetField<ArticleInfo>(nameof(ArticleInfo.ArticleType))} = {pageModel.Result.ArticleType}
");
}
if (pageModel.Result.FilterType == 1)
{
whereArr.Add($@"
{SqlCharConst.EXISTS} (
{SqlCharConst.SELECT} * {SqlCharConst.FROM} {EntityTools.GetTableName<ArticleOptInfo>()}
{SqlCharConst.WHERE} {EntityTools.GetField<ArticleOptInfo>(nameof(ArticleOptInfo.OptionType))} = {(int) ArticleOptMenu.Collect}
{SqlCharConst.AND} {nameof(BaseModel.ValidFlag)} = {(int) ValidFlagMenu.UseFul}
{SqlCharConst.AND} {EntityTools.GetField<ArticleOptInfo>(nameof(ArticleOptInfo.ActionUser))} = {userId}
{SqlCharConst.AND} {EntityTools.GetField<ArticleOptInfo>(nameof(ArticleOptInfo.RelationKey))} = {tableMapper.Alias}.{nameof(BaseModel.Id)}
)
");
}
else if (pageModel.Result.FilterType == 2)
{
whereArr.Add($"{EntityTools.GetField<ArticleInfo>(nameof(ArticleInfo.UserId))} = {userId}");
}
}
//获取连接
var mysqlConn = appSetting.GetMysqlConn();
var dbConnection = context.GetConnection(mysqlConn.FlagKey, mysqlConn.ConnStr);
//采用工具类分页查询
var pageList = await DapperTools.GetPageList <ArticleInfoDto>(pageModel.PageNo, pageModel.PageSize, dbConnection,
tableMapper.TableName,
whereArr, fields, new[]
{
SqlCharConst.DefaultOrder
}, tableMapper.Alias, pageModel.Result);
//返回结果集
return(ResultModel.GetSuccessModel(string.Empty, pageList));
}
public override IEnumerable <string> GetFieldEnumerable(TableMapperAttribute tableMapper)
{
return(EntityTools.GetFields <CommentInfo>());
}