package com.newvideo.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 net.sf.json.JSONArray;
|
|
import org.hibernate.HibernateException;
|
import org.hibernate.Session;
|
import org.springframework.cache.annotation.Cacheable;
|
import org.springframework.orm.hibernate4.HibernateCallback;
|
import org.springframework.stereotype.Service;
|
|
import com.newvideo.dao.LoginUserDao;
|
import com.newvideo.dao.VideoPlayStatisticsDao;
|
import com.newvideo.dao.VideoTypeDao;
|
import com.newvideo.domain.CategoryContry;
|
import com.newvideo.domain.DetailSystem;
|
import com.newvideo.domain.HotSearch;
|
import com.newvideo.domain.VideoInfo;
|
import com.newvideo.domain.VideoPlayStatistics;
|
import com.newvideo.domain.VideoResource;
|
import com.newvideo.domain.VideoType;
|
import com.newvideo.domain.recommend.AreaVideoCache;
|
import com.newvideo.domain.recommend.CategoryRecommendCacheVideo;
|
import com.newvideo.domain.web.StatisticXY;
|
import com.newvideo.util.LogUtil;
|
import com.newvideo.util.StringUtil;
|
import com.newvideo.util.TimeUtil;
|
import com.newvideo.util.video.VideoDetailUtil;
|
|
/**
|
* 统计
|
*
|
* @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;
|
|
public VideoResourceService getVideoResourceService() {
|
return videoResourceService;
|
}
|
|
public void setVideoResourceService(VideoResourceService videoResourceService) {
|
this.videoResourceService = videoResourceService;
|
}
|
|
public VideoDetailUtil getVideoDetailUtil() {
|
return videoDetailUtil;
|
}
|
|
public void setVideoDetailUtil(VideoDetailUtil videoDetailUtil) {
|
this.videoDetailUtil = videoDetailUtil;
|
}
|
|
public VideoPlayStatisticsDao getVideoPlayStatisticsDao() {
|
return videoPlayStatisticsDao;
|
}
|
|
public void setVideoPlayStatisticsDao(VideoPlayStatisticsDao videoPlayStatisticsDao) {
|
this.videoPlayStatisticsDao = videoPlayStatisticsDao;
|
}
|
|
@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<StatisticXY>();
|
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<StatisticXY>();
|
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>());
|
StatisticXY xy = new StatisticXY();
|
xy.setTime(objs[1] + "");
|
xy.setY(Long.parseLong(objs[0] + ""));
|
map.get(objs[2] + "").add(xy);
|
}
|
return null;
|
}
|
});
|
return map;
|
}
|
|
// 大区分类播放统计-按当天
|
// @Cacheable(value = "statisticCache", key = "'categoryPlayStatistic'")
|
// public void categoryPlayStatistic() {
|
// 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 null").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 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();
|
// // 删除所有
|
// session.createQuery("delete from CategoryRecommendCacheVideo cr where cr.videoType.id=?")
|
// .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();
|
// if (session.getTransaction().isActive())
|
// session.getTransaction().rollback();
|
// }
|
// 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<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);
|
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();
|
if (session.getTransaction().isActive())
|
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=?")
|
.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();
|
if (session.getTransaction().isActive())
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
// 四大主分类的按地区播放统计
|
@SuppressWarnings({ "unchecked", "rawtypes" })
|
@Cacheable(value = "statisticCache", key = "'areaPlayStatistic'")
|
public void areaPlayStatistic() {
|
videoPlayStatisticsDao.excute(new HibernateCallback() {
|
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) {
|
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();
|
if (session.getTransaction().isActive())
|
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();
|
if (session.getTransaction().isActive())
|
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<Long>();
|
for (VideoResource vr : list)
|
resourceList.add(Long.parseLong(vr.getId()));
|
VideoInfo info = videoDetailUtil.getVideoInfo(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;
|
}
|
});
|
|
}
|
|
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) {
|
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 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();
|
// 删除所有
|
session.createQuery("delete from CategoryRecommendCacheVideo cr where cr.videoType.id=?")
|
.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();
|
if (session.getTransaction().isActive())
|
session.getTransaction().rollback();
|
}
|
return null;
|
}
|
});
|
|
}
|
|
}
|
