From 0a18a8cb0a7a57bf1f82df425251334c57f8c39a Mon Sep 17 00:00:00 2001
From: admin <weikou2014>
Date: 星期六, 23 一月 2021 15:40:37 +0800
Subject: [PATCH] 新增手机号/qq/微信绑定
---
src/main/java/com/yeshi/buwan/service/imp/StatisticsService.java | 1134 +++++++++++++++++++++++++++++-----------------------------
1 files changed, 569 insertions(+), 565 deletions(-)
diff --git a/src/main/java/com/yeshi/buwan/service/imp/StatisticsService.java b/src/main/java/com/yeshi/buwan/service/imp/StatisticsService.java
index 7492739..9078cbf 100644
--- a/src/main/java/com/yeshi/buwan/service/imp/StatisticsService.java
+++ b/src/main/java/com/yeshi/buwan/service/imp/StatisticsService.java
@@ -10,7 +10,6 @@
import javax.annotation.Resource;
-import org.apache.log4j.pattern.LogEvent;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.cache.annotation.CacheEvict;
@@ -18,7 +17,7 @@
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Service;
-import com.yeshi.buwan.dao.LoginUserDao;
+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;
@@ -41,617 +40,622 @@
/**
* 缁熻
- *
+ *
* @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;
+ @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);
- }
- // 灏唚atchcount+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;
- }
- });
+ @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);
+ }
+ // 灏唚atchcount+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"));
+ /**
+ * 缁熻瑙嗛鎾斁鏁伴噺
+ *
+ * @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);
- }
+ 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;
- }
+ } 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 });
+ @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;
- }
+ 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 });
+ 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;
- }
+ 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");
+ 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;
- }
+ 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;
- }
+ 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'");
+ @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";
- }
+ 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;
- }
+ 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;
+ }
- // 澶у尯鍒嗙被鎾斁缁熻-鎸夊綋澶�
- @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(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();
+ // 澶у尯鍒嗙被鎾斁缁熻-鎸夊綋澶�
+ @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();
+ // 鍒犻櫎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());
- if (session.getTransaction().isActive())
- session.getTransaction().rollback();
- }
- LogHelper.print("缁熻瀹屾垚...");
- return null;
- }
- });
+ 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());
+ if (session.getTransaction().isActive())
+ 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;
+ /**
+ * 鑾峰彇鍗曚釜瑙嗛瑙傜湅璇︽儏
+ *
+ * @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 = "";
+ 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'");
+ 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;
- }
+ 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;
- }
- }
- }
+ // 鐑棬鎼滅储缁熻
+ @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] + "");
- }
+ 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();
+ 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;
- }
- });
+ } 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=? and cr.rank = 0 ")
- .setParameter(0, vt.getId()).executeUpdate();
+ @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();
- if (session.getTransaction().isActive())
- session.getTransaction().rollback();
- }
- return null;
- }
- });
+ 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;
+ }
+ });
- }
+ }
- // 鍥涘ぇ涓诲垎绫荤殑鎸夊湴鍖烘挱鏀剧粺璁�
- @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) {
- 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);
+ // 鍥涘ぇ涓诲垎绫荤殑鎸夊湴鍖烘挱鏀剧粺璁�
+ @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();
+ 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;
- }
- });
+ 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);
+ // 缁熻鏌愪釜瑙嗛鏌愭鏃堕棿鐨勬挱鏀炬暟)
+ 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;
- }
- });
+ 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 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 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();
+ // 鍒锋柊瑙嗛璇︽儏鏁伴噺
+ 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(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;
- }
- });
+ 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;
+ }
+ });
- }
+ }
}
--
Gitblit v1.8.0