package com.yeshi.buwan.service.imp;
|
|
import java.io.Serializable;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.annotation.Resource;
|
|
import org.hibernate.HibernateException;
|
import org.hibernate.Session;
|
import org.springframework.cache.annotation.CacheEvict;
|
import org.springframework.cache.annotation.Cacheable;
|
import org.springframework.orm.hibernate4.HibernateCallback;
|
import org.springframework.stereotype.Service;
|
|
import com.yeshi.buwan.dao.user.LoginUserDao;
|
import com.yeshi.buwan.dao.VideoPlayStatisticsDao;
|
import com.yeshi.buwan.dao.VideoTypeDao;
|
import com.yeshi.buwan.domain.CategoryContry;
|
import com.yeshi.buwan.domain.system.DetailSystem;
|
import com.yeshi.buwan.domain.HotSearch;
|
import com.yeshi.buwan.domain.VideoInfo;
|
import com.yeshi.buwan.domain.VideoPlayStatistics;
|
import com.yeshi.buwan.domain.VideoResource;
|
import com.yeshi.buwan.domain.VideoType;
|
import com.yeshi.buwan.domain.recommend.AreaVideoCache;
|
import com.yeshi.buwan.domain.recommend.CategoryRecommendCacheVideo;
|
import com.yeshi.buwan.domain.web.StatisticXY;
|
import com.yeshi.buwan.log.LogHelper;
|
import com.yeshi.buwan.util.LogUtil;
|
import com.yeshi.buwan.util.StringUtil;
|
import com.yeshi.buwan.util.TimeUtil;
|
import com.yeshi.buwan.util.video.VideoDetailUtil;
|
|
import net.sf.json.JSONArray;
|
|
/**
|
* 统计
|
*
|
* @author Administrator
|
*/
|
@Service
|
public class StatisticsService {
|
@Resource
|
private VideoPlayStatisticsDao videoPlayStatisticsDao;
|
@Resource
|
private VideoDetailUtil videoDetailUtil;
|
@Resource
|
private VideoResourceService videoResourceService;
|
@Resource
|
private LoginUserDao loginUserDao;
|
@Resource
|
private VideoTypeDao videoTypeDao;
|
@Resource
|
private CategoryRecommendCacheVideoService categoryRecommendCacheVideoService;
|
|
@SuppressWarnings("unchecked")
|
public void addStatistics(final String detailSystemId, final String videoid) {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
List list = session
|
.createSQLQuery(
|
"select id from wk_video_play_statistics s where s.detailsystemid=? and s.videoid=? and s.day=?")
|
.setParameter(0, detailSystemId).setParameter(1, videoid)
|
.setParameter(2, TimeUtil.getyyyyMMddHHTime(System.currentTimeMillis())).list();
|
session.getTransaction().begin();
|
if (list != null && list.size() > 0) {
|
String id = list.get(0) + "";
|
session.createSQLQuery(
|
"update wk_video_play_statistics s set s.playcount=s.playcount+1 where s.id=" + id)
|
.executeUpdate();
|
} else {
|
VideoPlayStatistics vp = new VideoPlayStatistics();
|
vp.setDay(TimeUtil.getyyyyMMddHHTime(System.currentTimeMillis()));
|
vp.setDetailSystem(new DetailSystem(detailSystemId));
|
vp.setPlayCount(1);
|
vp.setVideoInfo(new VideoInfo(videoid));
|
session.save(vp);
|
}
|
// 将watchcount+1
|
session.createSQLQuery(
|
"update wk_video_video v set v.watchcount=v.watchcount+1 where v.id=" + videoid)
|
.executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
/**
|
* 统计视频播放数量
|
*
|
* @param from
|
* @param to
|
* @param detailSystem
|
* @return
|
*/
|
@Cacheable(value = "statisticCache", key = "'getDetailSystemWatchCount'+'-'+#from+'-'+#to+'-'+#detailSystem")
|
@SuppressWarnings("rawtypes")
|
public List<StatisticXY> getDetailSystemWatchCount(String from, String to, String detailSystem) {
|
List<StatisticXY> xylist = new ArrayList<>();
|
long fromS = TimeUtil.convertDateToTemp(from);
|
long toS = TimeUtil.convertDateToTemp(
|
TimeUtil.getGernalTime((TimeUtil.convertDateToTemp(to) + 1000 * 60 * 60 * 24L), "yyyy-MM-dd"));
|
|
try {
|
List list;
|
if ("0".equalsIgnoreCase(detailSystem))
|
list = videoPlayStatisticsDao.sqlList(
|
"SELECT SUM(s.`playcount`),FROM_UNIXTIME(UNIX_TIMESTAMP(s.`day`),'%Y-%m-%d') FROM wk_video_play_statistics s WHERE UNIX_TIMESTAMP(s.`day`)*1000>=? AND UNIX_TIMESTAMP(s.`day`)*1000<=? GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(s.`day`),'%Y-%m-%d') ORDER BY UNIX_TIMESTAMP(s.`day`)",
|
new Serializable[]{fromS, toS});
|
else
|
list = videoPlayStatisticsDao.sqlList(
|
"SELECT SUM(s.`playcount`),FROM_UNIXTIME(UNIX_TIMESTAMP(s.`day`),'%Y-%m-%d') FROM wk_video_play_statistics s WHERE UNIX_TIMESTAMP(s.`day`)*1000>=? AND UNIX_TIMESTAMP(s.`day`)*1000<=? and s.detailsystemid=? GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(s.`day`),'%Y-%m-%d') ORDER BY UNIX_TIMESTAMP(s.`day`)",
|
new Serializable[]{fromS, toS, detailSystem});
|
for (int i = 0; i < list.size(); i++) {
|
StatisticXY xy = new StatisticXY();
|
Object[] objs = (Object[]) list.get(i);
|
long count = Long.parseLong((objs[0] + "").toString());
|
xy.setTime(objs[1].toString());
|
xy.setY(count);
|
xylist.add(xy);
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return xylist;
|
}
|
|
@Cacheable(value = "statisticCache", key = "'getWatchDetail'+'-'+#from+'-'+#to+'-'+#detailsystem")
|
public List<StatisticXY> getWatchDetail(String from, String to, String detailsystem) {
|
long fromS = TimeUtil.convertDateToTemp(from);
|
long toS = TimeUtil.convertDateToTemp(
|
TimeUtil.getGernalTime((TimeUtil.convertDateToTemp(to) + 1000 * 60 * 60 * 24L), "yyyy-MM-dd"));
|
List<StatisticXY> xylist = new ArrayList<>();
|
try {
|
List list;
|
if ("0".equalsIgnoreCase(detailsystem))
|
list = videoPlayStatisticsDao.sqlList(
|
"SELECT v.`name`,SUM(s.`playcount`) t FROM wk_video_play_statistics s LEFT JOIN wk_video_video v ON v.`id`=s.`videoid` WHERE UNIX_TIMESTAMP(s.`day`)*1000>=? AND UNIX_TIMESTAMP(s.`day`)*1000<=? GROUP BY s.`videoid` ORDER BY t DESC limit 0,100",
|
new Serializable[]{fromS, toS});
|
|
else
|
list = videoPlayStatisticsDao.sqlList(
|
"SELECT v.`name`,SUM(s.`playcount`) t FROM wk_video_play_statistics s LEFT JOIN wk_video_video v ON v.`id`=s.`videoid` WHERE UNIX_TIMESTAMP(s.`day`)*1000>=? AND UNIX_TIMESTAMP(s.`day`)*1000<=? and s.detailsystemid=? GROUP BY s.`videoid` ORDER BY t DESC limit 0,100",
|
new Serializable[]{fromS, toS, detailsystem});
|
for (int i = 0; i < list.size(); i++) {
|
StatisticXY xy = new StatisticXY();
|
Object[] objs = (Object[]) list.get(i);
|
long count = Long.parseLong((objs[1] + "").toString());
|
xy.setTime(objs[0] + "");
|
xy.setY(count);
|
xylist.add(xy);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return xylist;
|
}
|
|
public long getRegisterCount(String from, String to, String detailsystem) {
|
long fromS = TimeUtil.convertDateToTemp(from);
|
long toS = TimeUtil.convertDateToTemp(
|
TimeUtil.getGernalTime((TimeUtil.convertDateToTemp(to) + 1000 * 60 * 60 * 24L), "yyyy-MM-dd"));
|
try {
|
if ("0".equalsIgnoreCase(detailsystem))
|
return loginUserDao.getCount(
|
"select count(*) from LoginUser lu where cast(lu.createtime as long)>=? and cast(lu.createtime as long)<=?",
|
new Serializable[]{fromS, toS});
|
|
else
|
return loginUserDao.getCount(
|
"select count(*) from LoginUser lu where and lu.detailsystem=? and cast(lu.createtime as long)>=? and cast(lu.createtime as long)<=?",
|
new Serializable[]{detailsystem, fromS, toS});
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return 0;
|
}
|
|
public long getTotalRegisterCount(String detailsystem) {
|
try {
|
if ("0".equalsIgnoreCase(detailsystem))
|
return loginUserDao.getCount("select count(*) from LoginUser");
|
|
else
|
return loginUserDao.getCount("select count(*) from LoginUser lu where and lu.detailsystem=?",
|
new Serializable[]{detailsystem});
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return 0;
|
}
|
|
public long getTime(String st) {
|
Date date = new Date();
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
try {
|
date = sdf.parse(st.trim());
|
LogUtil.i(date.getTime() + "");
|
return date.getTime();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return 0;
|
}
|
|
@SuppressWarnings("rawtypes")
|
public Map<String, List<StatisticXY>> getCategoryPlayStatistics(String from, String to, String detailsystem) {
|
long fromS = TimeUtil.convertDateToTemp(from) / 1000;
|
long toS = TimeUtil.convertDateToTemp(
|
TimeUtil.getGernalTime((TimeUtil.convertDateToTemp(to) + 1000 * 60 * 60 * 24L), "yyyy-MM-dd")) / 1000;
|
// 查找类型
|
List<VideoType> list = videoTypeDao.list("from VideoType vt where vt.parent is null and vt.show=1");
|
String sql = "";
|
for (VideoType vt : list) {
|
if (StringUtil.isNullOrEmpty(detailsystem) || "0".equalsIgnoreCase(detailsystem))
|
sql += String.format(
|
" (SELECT SUM(s.c) AS `count`,FROM_UNIXTIME(UNIX_TIMESTAMP(s.t) ,%s) AS `day`,s.tt AS `type` FROM (SELECT ps.`playcount` AS c,ps.`day` AS t ,%s AS tt FROM wk_video_play_statistics ps LEFT JOIN wk_category_video cv ON ps.`videoid`=cv.`videoid` LEFT JOIN wk_video_type ty ON (ty.`id`=cv.`videotypeid`) WHERE (ty.`id`=%s OR ty.`pid`=%s) AND UNIX_TIMESTAMP(ps.`day`)>=%s and UNIX_TIMESTAMP(ps.`day`)<%s GROUP BY ps.`id` ) s GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(s.t),%s))",
|
"'%Y-%m-%d'", "'" + vt.getName() + "'", vt.getId() + "", vt.getId() + "", fromS + "", toS + "",
|
"'%Y-%m-%d'");
|
else
|
sql += String.format(
|
" (SELECT SUM(s.c) AS `count`,FROM_UNIXTIME(UNIX_TIMESTAMP(s.t) ,%s) AS `day`,s.tt AS `type` FROM (SELECT ps.`playcount` AS c,ps.`day` AS t ,%s AS tt FROM wk_video_play_statistics ps LEFT JOIN wk_category_video cv ON ps.`videoid`=cv.`videoid` LEFT JOIN wk_video_type ty ON (ty.`id`=cv.`videotypeid`) WHERE (ty.`id`=%s OR ty.`pid`=%s) AND and ps.detailsystemid=%s and UNIX_TIMESTAMP(ps.`day`)>=%s and UNIX_TIMESTAMP(ps.`day`)<%s GROUP BY ps.`id` ) s GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(s.t),%s))",
|
"'%Y-%m-%d'", "'" + vt.getName() + "'", vt.getId() + "", vt.getId() + "", detailsystem,
|
fromS + "", toS + "", "'%Y-%m-%d'");
|
|
sql += " UNION ALL";
|
}
|
|
if (sql.endsWith("UNION ALL"))
|
sql = sql.substring(0, sql.length() - 9);
|
System.out.println(sql);
|
final String fsql = sql;
|
final Map<String, List<StatisticXY>> map = new HashMap<String, List<StatisticXY>>();
|
videoTypeDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
List list = session.createSQLQuery(fsql).list();
|
for (int i = 0; i < list.size(); i++) {
|
Object[] objs = (Object[]) list.get(i);
|
if (map.get(objs[2] + "") == null)
|
map.put(objs[2] + "", new ArrayList<>());
|
StatisticXY xy = new StatisticXY();
|
xy.setTime(objs[1] + "");
|
xy.setY(Long.parseLong(objs[0] + ""));
|
map.get(objs[2] + "").add(xy);
|
}
|
return null;
|
}
|
});
|
return map;
|
}
|
|
// 大区分类播放统计-按当天
|
@SuppressWarnings({"unchecked", "rawtypes"})
|
@CacheEvict(value = "topCache", allEntries = true)
|
public void categoryPlayStatistic() {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
String startTime = TimeUtil.getGernalTime(System.currentTimeMillis() - 604800000L);
|
String endTime = TimeUtil.getGernalTime(System.currentTimeMillis() + 86400000L);
|
List<VideoType> typeList = (List<VideoType>) session
|
.createQuery("from VideoType vt where vt.parent is null").list();
|
for (VideoType vt : typeList) {
|
// 只统计电影，电视剧，综艺，动漫的榜首
|
if (vt.getId() == 150 || vt.getId() == 151 || vt.getId() == 152 || vt.getId() == 153) {
|
//电影电视剧不更新榜首
|
if (vt.getId() == 150 || vt.getId() == 151)
|
continue;
|
// if(1>0)
|
// continue;
|
String sql = String.format(
|
"SELECT mm.vid, mm.c,mm.rank FROM (SELECT m.vid AS vid,SUM(m.c) AS c,m.rank AS rank FROM (SELECT p.`videoid` AS vid, p.`playcount` AS c, p.`day`, cc.rank AS rank FROM wk_video_play_statistics p LEFT JOIN wk_category_video cv ON cv.`videoid` = p.`videoid` LEFT JOIN wk_video_type ty ON cv.`videotypeid` = ty.`id` LEFT JOIN wk_recommend_category_cache cc ON cc.videoid = p.videoid WHERE (ty.`id` = %s OR ty.`pid` = %s) AND ( UNIX_TIMESTAMP(P.`day`) < UNIX_TIMESTAMP(%s) AND UNIX_TIMESTAMP(P.`day`) >= UNIX_TIMESTAMP(%s)) GROUP BY p.`id`) m GROUP BY m.vid) mm ORDER BY mm.c DESC limit 0,100",
|
vt.getId() + "", vt.getId() + "", "'" + endTime + "'", "'" + startTime + "'");
|
List list = session.createSQLQuery(sql).list();
|
session.getTransaction().begin();
|
// 删除rank等于0的
|
int update = session
|
.createQuery(
|
"delete from CategoryRecommendCacheVideo cr where cr.videoType.id=? and cr.rank = 0 ")
|
.setParameter(0, vt.getId()).executeUpdate();
|
LogHelper.print("delete from CategoryRecommendCacheVideo cr where cr.videoType.id="
|
+ vt.getId() + " and cr.rank = 0 ");
|
LogHelper.print("删除：" + update + "条");
|
for (int i = 0; i < list.size(); i++) {
|
Object[] objs = (Object[]) list.get(i);
|
String rank = String.valueOf(objs[2] + "");
|
if ("null".equalsIgnoreCase(rank)) {
|
rank = "0";
|
}
|
if (Integer.parseInt(rank + "") != 0) {
|
CategoryRecommendCacheVideo ccv = categoryRecommendCacheVideoService
|
.getByVid(objs[0] + "");
|
if (ccv != null) {
|
ccv.setOrderby(Integer.parseInt(objs[1] + ""));
|
session.update(ccv);
|
}
|
continue;
|
}
|
CategoryRecommendCacheVideo crcv = new CategoryRecommendCacheVideo();
|
crcv.setCreatetime("" + System.currentTimeMillis());
|
crcv.setOrderby(Integer.parseInt(objs[1] + ""));
|
crcv.setVideoInfo(new VideoInfo(objs[0] + ""));
|
crcv.setVideoType(vt);
|
session.persist(crcv);
|
}
|
session.flush();
|
session.getTransaction().commit();
|
} else {// 其他小分类的榜首查询最近更新的
|
String sql = String.format(
|
"SELECT v.id FROM (SELECT DISTINCT(v.`id`) FROM wk_video_video v LEFT JOIN wk_category_video cv ON cv.`videoid`=v.`id` LEFT JOIN `wk_video_type` t ON t.id=cv.`videotypeid` WHERE v.`show`=1 AND t.`id`=%s OR t.pid=%s ORDER BY v.id DESC LIMIT 200) vv LEFT JOIN wk_video_video v ON v.`id`=vv.id LIMIT 32",
|
vt.getId() + "", vt.getId() + "");
|
// 先删除原来的视频
|
session.getTransaction().begin();
|
|
// 删除Rank=0的情况
|
session.createQuery(
|
"delete from CategoryRecommendCacheVideo cr where cr.videoType.id=? and cr.rank = 0 ")
|
.setParameter(0, vt.getId()).executeUpdate();
|
|
List list = session.createSQLQuery(sql).list();
|
for (int i = 0; i < list.size(); i++) {
|
list.get(i);
|
CategoryRecommendCacheVideo crcv = new CategoryRecommendCacheVideo();
|
crcv.setCreatetime("" + System.currentTimeMillis());
|
crcv.setOrderby(100 - i);
|
crcv.setVideoInfo(new VideoInfo(list.get(i) + ""));
|
crcv.setVideoType(vt);
|
session.persist(crcv);
|
}
|
session.flush();
|
session.getTransaction().commit();
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
LogHelper.error(e.getMessage());
|
session.getTransaction().rollback();
|
}
|
LogHelper.print("统计完成...");
|
return null;
|
}
|
});
|
|
}
|
|
/**
|
* 获取单个视频观看详情
|
*
|
* @param videoid
|
* @param from
|
* @param to
|
* @param detailsystem
|
* @return
|
*/
|
@SuppressWarnings("rawtypes")
|
public List<StatisticXY> getVideoWatchDetail(String videoid, String from, String to, String detailsystem) {
|
long fromS = TimeUtil.convertDateToTemp(from) / 1000;
|
long toS = TimeUtil.convertDateToTemp(
|
TimeUtil.getGernalTime((TimeUtil.convertDateToTemp(to) + 1000 * 60 * 60 * 24L), "yyyy-MM-dd")) / 1000;
|
|
String sql = "";
|
|
if (StringUtil.isNullOrEmpty(detailsystem) || "0".equalsIgnoreCase(detailsystem))
|
sql = String.format(
|
"SELECT sts.`videoid` AS videoid,SUM(sts.`playcount`) AS `count`,(FROM_UNIXTIME(UNIX_TIMESTAMP(sts.`day`),%s)) AS `day` FROM wk_video_play_statistics sts WHERE sts.`videoid`=%s AND (UNIX_TIMESTAMP(sts.`day`)>=%s AND UNIX_TIMESTAMP(sts.`day`)<%s) GROUP BY (FROM_UNIXTIME(UNIX_TIMESTAMP(sts.`day`),%s))",
|
"'%Y-%m-%d'", videoid, fromS + "", toS + "", "'%Y-%m-%d'");
|
else
|
sql = String.format(
|
"SELECT sts.`videoid` AS videoid,SUM(sts.`playcount`) AS `count`,(FROM_UNIXTIME(UNIX_TIMESTAMP(sts.`day`),%s)) AS `day` FROM wk_video_play_statistics sts WHERE sts.`videoid`=%s AND sts.`detailsystemid`=%s AND (UNIX_TIMESTAMP(sts.`day`)>=%s AND UNIX_TIMESTAMP(sts.`day`)<%s) GROUP BY (FROM_UNIXTIME(UNIX_TIMESTAMP(sts.`day`),%s))",
|
"'%Y-%m-%d'", videoid, detailsystem, fromS + "", toS + "", "'%Y-%m-%d'");
|
|
System.out.println(sql);
|
final String fsql = sql;
|
final List<StatisticXY> rlist = new ArrayList<>();
|
videoTypeDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
List list = session.createSQLQuery(fsql).list();
|
for (int i = 0; i < list.size(); i++) {
|
Object[] objs = (Object[]) list.get(i);
|
StatisticXY xy = new StatisticXY();
|
xy.setTime(objs[2] + "");
|
xy.setY(Long.parseLong(objs[1] + ""));
|
rlist.add(xy);
|
}
|
return null;
|
}
|
});
|
return rlist;
|
}
|
|
// 热门搜索统计
|
@SuppressWarnings("unchecked")
|
public void hotSearchStatistics() {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
List<HotSearch> hotList = session.createQuery("from HotSearch").list();
|
List list = session.createSQLQuery(
|
"SELECT h.`keyword`,COUNT(*) c FROM wk_video_search_history h WHERE h.`createtime`>"
|
+ (System.currentTimeMillis() - 1000 * 24 * 60 * 60L)
|
+ " GROUP BY h.`keyword` ORDER BY c DESC limit 0,300")
|
.list();
|
for (int i = list.size() - 1; i >= 0; i--) {
|
for (HotSearch hs : hotList) {
|
if (hs.getName().equalsIgnoreCase("" + ((Object[]) list.get(i))[0])) {
|
hs.setOrderby(Integer.parseInt(((Object[]) list.get(i))[1] + ""));
|
session.getTransaction().begin();
|
session.update(hs);
|
session.flush();
|
session.getTransaction().commit();
|
break;
|
}
|
}
|
}
|
|
JSONArray array = new JSONArray();
|
for (int i = 0; i < 20; i++) {
|
array.add(((Object[]) list.get(i))[0] + "");
|
}
|
|
session.getTransaction().begin();
|
session.createSQLQuery("update wk_video_config cf set cf.value=? where cf.key=?")
|
.setParameter(0, array.toString()).setParameter(1, "topsearch").executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
@SuppressWarnings("unchecked")
|
public void secondCategoryPlayStatistic() {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
String startTime = TimeUtil.getGernalTime(System.currentTimeMillis());
|
String endTime = TimeUtil.getGernalTime(System.currentTimeMillis() + 24 * 60 * 60 * 1000L);
|
// 排除四大主分类
|
List<VideoType> typeList = (List<VideoType>) session
|
.createQuery(
|
"from VideoType vt where vt.parent is not null and ( vt.parent.id!=150 and vt.parent.id!=151 and vt.parent.id!=152 and vt.parent.id!=153)")
|
.list();
|
for (VideoType vt : typeList) {
|
String sql = String.format(
|
"SELECT mm.vid, mm.c FROM (SELECT m.vid AS vid,SUM(m.c) AS c FROM (SELECT p.`videoid` AS vid, p.`playcount` AS c, p.`day` FROM wk_video_play_statistics p LEFT JOIN wk_category_video cv ON cv.`videoid` = p.`videoid` LEFT JOIN wk_video_type ty ON cv.`videotypeid` = ty.`id` WHERE (ty.`id` = %s) AND ( UNIX_TIMESTAMP(P.`day`) < UNIX_TIMESTAMP(%s) AND UNIX_TIMESTAMP(P.`day`) >= UNIX_TIMESTAMP(%s)) GROUP BY p.`id`) m GROUP BY m.vid) mm ORDER BY mm.c DESC limit 0,100",
|
vt.getId() + "", "'" + endTime + "'", "'" + startTime + "'");
|
List list = session.createSQLQuery(sql).list();
|
session.getTransaction().begin();
|
// 删除所有
|
session.createQuery(
|
"delete from CategoryRecommendCacheVideo cr where cr.videoType.id=? and cr.rank = 0 ")
|
.setParameter(0, vt.getId()).executeUpdate();
|
|
for (int i = 0; i < list.size(); i++) {
|
Object[] objs = (Object[]) list.get(i);
|
CategoryRecommendCacheVideo crcv = new CategoryRecommendCacheVideo();
|
crcv.setCreatetime("" + System.currentTimeMillis());
|
crcv.setOrderby(Integer.parseInt(objs[1] + ""));
|
crcv.setVideoInfo(new VideoInfo(objs[0] + ""));
|
crcv.setVideoType(vt);
|
session.persist(crcv);
|
}
|
session.flush();
|
session.getTransaction().commit();
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 四大主分类的按地区播放统计
|
@Cacheable(value = "statisticCache", key = "'areaPlayStatistic'")
|
public void areaPlayStatistic() {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
@SuppressWarnings("unchecked")
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
String endTime = TimeUtil.getAllTime(System.currentTimeMillis());
|
String startTime = TimeUtil.getAllTime(System.currentTimeMillis() - 24 * 60 * 60 * 1000L);
|
// 排除四大主分类
|
List<CategoryContry> rootList = (List<CategoryContry>) session
|
.createQuery("from CategoryContry cc where cc.parent is null").list();
|
for (CategoryContry cc : rootList) {
|
//排除电影/电视剧
|
if (cc.getCid() == 150L || cc.getCid() == 151L)
|
continue;
|
List<CategoryContry> secondAreaList = (List<CategoryContry>) session
|
.createQuery("from CategoryContry cc where cc.parent.id=" + cc.getId()).list();
|
for (CategoryContry se : secondAreaList) {
|
List<CategoryContry> thirdList = (List<CategoryContry>) session
|
.createQuery("from CategoryContry cc where cc.parent.id=" + se.getId()).list();
|
String areaWhere = "";
|
for (CategoryContry tcc : thirdList) {
|
areaWhere += String.format(" v.area like %s or", "'%" + tcc.getName() + "%'");
|
}
|
if (areaWhere.endsWith("or"))
|
areaWhere = areaWhere.substring(0, areaWhere.length() - 2);
|
|
String sql = String.format(
|
"SELECT videoid,SUM(playcount) sc FROM (SELECT s.`videoid`,s.`playcount` FROM wk_video_play_statistics s LEFT JOIN wk_category_video cv ON cv.`videoid`=s.`videoid` LEFT JOIN wk_video_type ty ON ty.`id`=cv.`videotypeid` LEFT JOIN wk_video_video v ON v.`id`=s.`videoid` WHERE (ty.`id`=%s OR ty.`pid`=%s) AND (%s) AND (UNIX_TIMESTAMP(s.`day`)>=UNIX_TIMESTAMP('%s') AND UNIX_TIMESTAMP(s.`day`)<=UNIX_TIMESTAMP('%s')) GROUP BY s.`videoid`,s.`detailsystemid`,s.`day` ORDER BY s.`playcount` DESC) sm GROUP BY sm.videoid ORDER BY sc DESC limit 0,100",
|
cc.getCid() + "", cc.getCid() + "", areaWhere, startTime, endTime);
|
List list = session.createSQLQuery(sql).list();
|
session.getTransaction().begin();
|
|
session.createQuery("delete from AreaVideoCache a where a.categoryContry.id=" + se.getId())
|
.executeUpdate();
|
for (int i = 0; i < list.size(); i++) {
|
Object[] object = (Object[]) list.get(i);
|
String videoid = object[0] + "";
|
int count = Integer.parseInt(object[1] + "");
|
AreaVideoCache avc = new AreaVideoCache();
|
avc.setCategoryContry(se);
|
avc.setCreatetime(System.currentTimeMillis() + "");
|
avc.setOrderby(count);
|
avc.setVideoInfo(new VideoInfo(videoid));
|
session.persist(avc);
|
}
|
session.flush();
|
session.getTransaction().commit();
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 统计某个视频某段时间的播放数)
|
public void videoPlayStatisticDuration(final String videoid, final long time) {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
String endTime = TimeUtil.getAllTime(System.currentTimeMillis());
|
String startTime = TimeUtil.getAllTime(System.currentTimeMillis() - time);
|
|
String sql = String.format(
|
"SELECT SUM(s.`playcount`) FROM wk_video_play_statistics s WHERE s.`videoid`=%s AND (UNIX_TIMESTAMP(s.`day`)>=UNIX_TIMESTAMP(%s) AND UNIX_TIMESTAMP(s.`day`)<=UNIX_TIMESTAMP(%s))",
|
videoid + "", "'" + startTime + "'", "'" + endTime + "'");
|
Object count = session.createSQLQuery(sql).uniqueResult();
|
if (StringUtil.isNullOrEmpty(count + ""))
|
count = 0 + "";
|
session.getTransaction().begin();
|
session.createSQLQuery("update wk_video_video v set v.latestwatchcount="
|
+ Integer.parseInt(count + "") + " where id=" + videoid).executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 同步观看次数
|
public void refreshWatchCount(final String videoid) {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
session.getTransaction().begin();
|
session.createSQLQuery(
|
"UPDATE wk_video_video v SET v.`watchcount`=( SELECT SUM(s.`playcount`) FROM wk_video_play_statistics s WHERE s.`videoid`=?) WHERE v.`id`=?")
|
.setParameter(0, videoid).setParameter(1, videoid).executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 同步评论数
|
public void refreshVideoCommentCount(final String videoid) {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
session.getTransaction().begin();
|
session.createSQLQuery(
|
"UPDATE wk_video_video v SET v.`commentcount`=( SELECT COUNT(c.`id`) FROM wk_video_comment2 c WHERE c.`videoid`=?) WHERE v.`id`=?")
|
.setParameter(0, videoid).setParameter(1, videoid).executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 刷新视频详情数量
|
public void refreshVideoDetailCount(final String videoid) {
|
List<VideoResource> list = videoResourceService.getResourceList();
|
List<Long> resourceList = new ArrayList<>();
|
for (VideoResource vr : list)
|
resourceList.add(Long.parseLong(vr.getId()));
|
VideoInfo info = videoDetailUtil.getVideoInfo(null, videoid, null, resourceList,
|
StringUtil.Md5(System.currentTimeMillis() + ""));
|
int count = 0;
|
if (info != null && info.getVideoDetailList() != null)
|
count = info.getVideoDetailList().size();
|
|
final int newCount = count;
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
public Object doInHibernate(Session session) throws HibernateException {
|
try {
|
session.getTransaction().begin();
|
session.createSQLQuery("UPDATE wk_video_video v SET v.`videocount`=? WHERE v.`id`=?")
|
.setParameter(0, newCount).setParameter(1, videoid).executeUpdate();
|
session.flush();
|
session.getTransaction().commit();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
}
|
