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 getDetailSystemWatchCount(String from, String to, String detailSystem) { List 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 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 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> 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 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> map = new HashMap>(); 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; } // 大区分类播放统计-按当天 // @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 typeList = (List) 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 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 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 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 typeList = (List) 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 rootList = (List) session .createQuery("from CategoryContry cc where cc.parent is null").list(); for (CategoryContry cc : rootList) { List secondAreaList = (List) session .createQuery("from CategoryContry cc where cc.parent.id=" + cc.getId()).list(); for (CategoryContry se : secondAreaList) { List thirdList = (List) 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 list = videoResourceService.getResourceList(); List resourceList = new ArrayList(); 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 typeList = (List) 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; } }); } }