/* ==================================================================
* Created [2009-4-27 下午11:32:55] by Jon.King
* ==================================================================
* TSS
* ==================================================================
* mailTo:jinpujun@hotmail.com
* Copyright (c) Jon.King, 2009-2012
* ==================================================================
*/
package com.jinhe.tss.cms.dao.impl;
import java.util.Date;
import java.util.List;
import com.jinhe.tss.cms.CMSConstants;
import com.jinhe.tss.cms.dao.IArticleRelationDao;
import com.jinhe.tss.cms.helper.ArticleQueryCondition;
import com.jinhe.tss.component.support.persistence.pagequery.PageInfo;
import com.jinhe.tss.component.support.persistence.pagequery.PaginationQueryByHQL;
import com.jinhe.tss.core.persistence.BaseDao;
import com.jinhe.tss.core.persistence.IEntity;
import com.jinhe.tss.core.util.EasyUtils;
public class ArticleRelationDao extends BaseDao<IEntity> implements IArticleRelationDao{
public ArticleRelationDao() {
super(IEntity.class);
}
@SuppressWarnings("unchecked")
public List<Long> getArticleIdList(Date startTime, Date endTime, String author) {
String hql ="select distinct a.id from Article a, ChannelArticle ca, Temp t "
+ " where a.id = ca.id.articleId and ca.id.channelId = t.id and a.issueDate between ? and ?" ;
if( !EasyUtils.isNullOrEmpty(author) ) {
hql += " and a.creatorName like ?";
return (List<Long>) getEntities(hql, startTime, endTime, "'%" + author + "%'");
}
return (List<Long>) getEntities(hql, startTime, endTime);
}
public List<?> getSummaryGroupByAuthor(Date startTime, Date endTime) {
String hql = "select a.author, sum(a.hitCount) as hitcount, count(a) as articleNum "
+ " from Article a, ChannelArticle ca, Temp temp "
+ " where a.id = ca.id.articleId and ca.id.channelId = temp.id "
+ " and ca.articleOrigin = ? and a.issueDate between ? and ?"
+ " group by a.author "
+ " order by hitcount desc ";
return getEntities(hql, CMSConstants.ARTICLE_COMMON, startTime, endTime);
}
//没有文章的栏目不展示
public List<?> getSummaryGroupByChannel(Date startTime, Date endTime) {
String hql = "select c.id as channelId, sum(t.hitCount) as hitcount "
+ "from Article t, ChannelArticle ca, Channel c "
+ " where ca.id.articleId = t.id and ca.id.channelId = c.id and t.issueDate between ? and ? "
+ " group by c.id "
+ " order by hitcount DESC";
return getEntities(hql, startTime, endTime);
}
public Integer getChannelArtilceCount(Long channelId, Date startTime, Date endTime){
String hql = "select count(*) from Article a, ChannelArticle ca "
+ " where a.id = ca.id.articleId and ca.id.channelId = ? and a.issueDate between ? and ? ";
List<?> list = getEntities(hql, channelId, startTime, endTime);
return list.isEmpty() ? 0 : (Integer)list.get(0);
}
public List<?> getAverageScoreByChannel(Long channelId, Date startTime, Date endTime){
String hql = "select sum(a.scores/a.scoreCount)/count(*), sum(a.scoreCount)"
+ " from Article a, ChannelArticle ca "
+ " where a.id = ca.id.articleId and ca.id.channelId = ? and a.issueDate between ? and ? and a.scoreCount <> 0 ";
return getEntities(hql, channelId, startTime, endTime);
}
public List<?> getAverageScoreByAuthor(String author, Date startTime, Date endTime){
String hql = "select sum(a.scores/a.scoreCount)/count(*), sum(a.scoreCount) "
+ " from Article a, ChannelArticle ca, Temp t "
+ " where a.id = ca.id.articleId and ca.id.channelId = t.id "
+ " and a.issueDate between ? and ? and a.author = ? and a.scoreCount <> 0";
return getEntities(hql, startTime, endTime, author);
}
/*******************************************评论部分*********************************************/
public PageInfo getCommentsList(Long articleId, Integer pageNum) {
String hql = "from ArticleComment c where c.articleId = :articleId";
ArticleQueryCondition condition = new ArticleQueryCondition();
condition.setArticleId(articleId);
condition.getPage().setPageNum(pageNum);
PaginationQueryByHQL pageQuery = new PaginationQueryByHQL(em, hql, condition);
return pageQuery.getResultList();
}
public PageInfo getCommentsList4Search(String keyword, Integer pageNum) {
String hql = "from ArticleComment c where c.commentContent like :keyword";
ArticleQueryCondition condition = new ArticleQueryCondition();
condition.setKeyword("%" + keyword + "%");
condition.getPage().setPageNum(pageNum);
PaginationQueryByHQL pageQuery = new PaginationQueryByHQL(em, hql, condition);
return pageQuery.getResultList();
}
}