前言
今天将昨天没有完成的有关管理员端博客和文章信息的统计接口补齐
获取文章最近七天的点赞数量 降序排序
/**
* 获取最近七天 给文章点赞最多的用户 及点赞数量 降序排列
* @param page
* @param size
* @return
*/
@RequestMapping(value = "/getUserLikingCountRecently", method = RequestMethod.GET, produces = "application/json")
public Response getUserLikingCountRecently(
@RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
@RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
@RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
if (page < 0) {
page = 1;
}
if (size < 0) {
size = 20;
}
List<UserRecord> userRecords = userDataRecordService.selectUserLikingCountRecently(page,size,day);
return Response.success("获取用户点赞量", userRecords);
}
/**
* 获取最近day天 给文章点赞最多的用户 及点赞数量 降序排列
* @param page
* @param size
* @param day
* @return
*/
public List<UserRecord> selectUserLikingCountRecently(int page ,int size,int day)
{
int start = (page - 1) * size;
if (start < 0) {
start = 0;
}
if (size < 0 || size > 100) {
size = 10;
}
return userDataRecordDao.selectUserLikingCountRecently(size,start,day);
}
/**
* 获取最近day天 给文章点赞最多的用户 及点赞数量 降序排列
* @param limit
* @param offset
* @param day
* @return
*/
@Select("select B.id , B.name ,B.email ,count(B.id) as count" +
" from user_liking as A, user as B " +
"where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(click_liking_time) " +
"and A.user_id = B.id " +
"group by A.user_id " +
"order by count desc " +
"limit #{limit} offset #{offset}")
List<UserRecord> selectUserLikingCountRecently(@Param("limit") int limit, @Param("offset") int offset,@Param("day") int day);
最近几天每个博客发布的文章数量
/**
* 最近几天每个博客发布的文章数量
* @param day
* @param page
* @param size
* @return
*/
@RequestMapping(value = "/getBlogArticleRecently", method = RequestMethod.GET, produces = "application/json")
public Response getBlogArticleRecently(
@RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
@RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
@RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
if (page < 0) {
page = 1;
}
if (size < 0) {
size = 20;
}
List<Blog> blogs = articleService.selectBlogArticleRecently(page,size,day);
return Response.success("获取最近博客发布文章数成功", blogs);
}
/**
* 获取几天内每个博客发布的文章数量
* @param page
* @param size
* @param day
* @return
*/
public List<Blog> selectBlogArticleRecently(int page,int size,int day)
{
int start = (page - 1) * size;
if (start < 0) {
start = 0;
}
if (size < 0 || size > 100) {
size = 20;
}
return articleDao.selectBlogArticleRecently(start,size,day);
}
简单表的自然连接,然后进行统计
/**
* 获取七天内每个博客发布的文章数量
* @param offset
* @param limit
* @param day
* @return
*/
@Select("select B.id,B.title , B.url , B.feed , count(*) as articleCount " +
"from article as A ,blog as B where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(update_time) " +
"and A.blog_id = B.id group by B.id" +
" order by articleCount desc " +
"LIMIT #{limit} OFFSET #{offset} ")
List<Blog> selectBlogArticleRecently(@Param("offset")int offset, @Param("limit") Integer limit,@Param("day") int day);
}
最近几天发布文章的数量
/**
* 最近几天发布文章的数量
* @param day
* @return
*/
@RequestMapping(value = "/getArticleRecently", method = RequestMethod.GET, produces = "application/json")
public Response getArticleRecently(
@RequestParam(value = "day", required = false, defaultValue = "15") Integer day
) {
List<ArticleUpdateTimeCount> count = articleService.selectArticleRecently(day);
return Response.success("获取最近发布文章数量成功", count);
}
这里对返回的结果进行了一些的处理,对于由于可能不是每天都有人发文章,所以可能有的天的文章数量为0,统计出来不会显示,所以我们需要把缺少的天数补上,然后在发布文章数量的地方填上0。所以这里做了一些的处理
/**
* 最近几天的文章数量
* @param day
* @return
*/
public List<ArticleUpdateTimeCount> selectArticleRecently(int day)
{
List<ArticleUpdateTimeCount> articleUpdateTimeCounts =articleDao.selectArticleRecently(day);
String startTime;
if(articleUpdateTimeCounts.size() >=2)
{
startTime = articleUpdateTimeCounts.get(0).getTime();
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
LocalDateTime startDate = LocalDateTime.of(LocalDate.parse(startTime,df),LocalTime.parse("00:00:00"));
LocalDateTime endDate = LocalDateTime.now();
List<String> dateList = completionDate(startDate,endDate);
for(String str :dateList)
{
if(!dateInList(str,articleUpdateTimeCounts))
{
ArticleUpdateTimeCount articleUpdateTimeCount = new ArticleUpdateTimeCount();
articleUpdateTimeCount.setCount(0);
articleUpdateTimeCount.setTime(str);
articleUpdateTimeCounts.add(articleUpdateTimeCount);
}
}
}
sortClass sort = new sortClass();
Collections.sort(articleUpdateTimeCounts,sort);
return articleUpdateTimeCounts;
}
/**
* 比较日期大小
*/
public class sortClass implements Comparator {
@Override
public int compare(Object arg0, Object arg1){
ArticleUpdateTimeCount item0 = (ArticleUpdateTimeCount)arg0;
ArticleUpdateTimeCount item1 = (ArticleUpdateTimeCount)arg1;
int flag = item0.getTime().compareTo(item1.getTime());
return flag;
}
}
/**
* 此日期是否在时间内
* @param date
* @param dateList
* @return
*/
public boolean dateInList(String date,List<ArticleUpdateTimeCount> dateList)
{
for(ArticleUpdateTimeCount str :dateList)
{
if(str.getTime().equals(date) )
{
return true;
}
}
return false;
}
/**
* 补全时间
* @param startTime
* @param endTime
* @return
*/
public static List<String> completionDate(
LocalDateTime startTime,
LocalDateTime endTime) {
//日期格式化
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
List<String> dateList = new ArrayList<>();
//遍历给定的日期期间的每一天
for (int i = 0; !Duration.between(startTime.plusDays(i), endTime).isNegative(); i++) {
//添加日期
dateList.add(startTime.plusDays(i).format(formatter));
}
return dateList;
}
/**
* 最近几天获取到的博客数量
* @param day
* @return
*/
@Select("select count(*) as count , DATE_FORMAT( update_time, '%Y-%m-%d' ) AS time " +
"from article " +
"where update_time < CURDATE( ) + 1 " +
"and update_time >= DATE_SUB( CURDATE( ), INTERVAL #{day} DAY ) " +
"group by DATE_FORMAT( update_time, '%Y-%m-%d') " +
"order by update_time asc")
List<ArticleUpdateTimeCount> selectArticleRecently(@Param("day") int day);
总结