예제 #1
0
  //	  String taday = PropertiesParse.parse("taday", propertiesName);
  //	  String yesterday = PropertiesParse.parse("yesterday", propertiesName);
  public void oper(String time, int setNum) {

    // 获取数据库连接配置
    DBHelper.setFileName(propertiesName);

    String sqlBlog =
        "SELECT CHANNEL from wk_facebook_data_channel_date where DATETIME = '" + time + "'";
    List<Map<String, Object>> list = null;
    try {
      list = DBHelper.getQueryRunner().query(sqlBlog, new MapListHandler());

      System.out.println(list.size());
    } catch (SQLException e) {
      e.printStackTrace();
    }
    if (CollectionUtils.isEmpty(list)) {
      // 生成channel中间表
      String sql =
          "SELECT e.CHANNEL, sum(`e`.`VIEWCOUNT`) AS `VIEWCOUNT`,sum(`e`.`GOODCOUNT`) AS `GOODCOUNT`,sum(`e`.`BADCOUNT`) AS `BADCOUNT`,sum(`e`.`SHARECOUNT`) AS `SHARECOUNT`,sum(`e`.`REVIEWCOUNT`) AS `REVIEWCOUNT`,count(`e`.`URL`) AS `CITEMS`,e.DATETIME from  wk_facebook_data_paste e WHERE DATETIME='"
              + time
              + "' and UPLOADTIME>= DATE_ADD(NOW(),INTERVAL "
              + setNum
              + " DAY) GROUP BY CHANNEL";

      List<Map<String, Object>> mapList = null;
      try {
        mapList = DBHelper.getQueryRunner().query(sql, new MapListHandler());

        System.out.println(mapList.size());
      } catch (SQLException e) {
        e.printStackTrace();
      }

      DBUtil.addListMap(mapList, "wk_facebook_data_channel_date");

      // 生成channel表
      String channelSql =
          "SELECT  a.*,a.id as top_id,d.VIEWCOUNT AS view_count, d.GOODCOUNT as good_count, d.BADCOUNT AS bad_count , d.SHARECOUNT AS share_count , d.REVIEWCOUNT as review_count ,d.CITEMS as citems_count,d.DATETIME as update_time FROM ufeng_tops as a JOIN ufeng_tops_to_channel as b on a.id= b.tops_id JOIN ufeng_tops_channel as c on c.id = b.channel_id JOIN wk_facebook_data_channel_date as d on c.code=d.channel and d.DATETIME='"
              + time
              + "'";

      List<Map<String, Object>> channelMapList = null;
      try {
        channelMapList = DBHelper.getQueryRunner().query(channelSql, new MapListHandler());

        System.out.println("插入ufeng_facebook_tops_class_count大小:" + channelMapList.size());
      } catch (SQLException e) {
        e.printStackTrace();
      }

      DBUtil.addListMapForClassCount(channelMapList, "ufeng_facebook_tops_class_count");

      // 生成Tv表
      new TvQueryFaceBook().oper(time);
    } else {
      System.out.println("channel 中间表 已经生成");
    }
    //		}
    System.out.println("操作完成!");
  }
예제 #2
0
  public void operNew(String time, int setNum) {

    // 获取数据库连接配置
    DBHelper.setFileName(propertiesName);

    String sqlBlog =
        "SELECT CHANNEL from wk_facebook_data_channel_date where DATETIME = '" + time + "'";
    List<Map<String, Object>> list = null;
    try {
      list = DBHelper.getQueryRunner().query(sqlBlog, new MapListHandler());

      System.out.println(list.size());
    } catch (SQLException e) {
      e.printStackTrace();
    }
    if (CollectionUtils.isEmpty(list)) {
      if (time.equals("2015-08-23")) {
        String sql =
            "SELECT e.CHANNEL, sum(`e`.`VIEWCOUNT`) AS `VIEWCOUNT`,sum(`e`.`GOODCOUNT`) AS `GOODCOUNT`,sum(`e`.`BADCOUNT`) AS `BADCOUNT`,sum(`e`.`SHARECOUNT`) AS `SHARECOUNT`,sum(`e`.`REVIEWCOUNT`) AS `REVIEWCOUNT`,count(`e`.`URL`) AS `CITEMS`,e.DATETIME from  wk_facebook_data_2015_35 e WHERE DATETIME='"
                + time
                + "' and UPLOADTIME>= DATE_ADD(NOW(),INTERVAL "
                + setNum
                + " DAY) GROUP BY CHANNEL";
        List<Map<String, Object>> mapList = null;
        try {
          mapList = DBHelper.getQueryRunner().query(sql, new MapListHandler());

          System.out.println(mapList.size());
        } catch (SQLException e) {
          e.printStackTrace();
        }
        DBUtil.addListMap(mapList, "wk_facebook_data_channel_date");
        // 生成channel表
        String channelSql =
            "SELECT a.*,a.id as top_id,d.VIEWCOUNT AS view_count, d.GOODCOUNT as good_count, d.BADCOUNT AS bad_count ,d.REVIEWCOUNT as review_count ,d.SHARECOUNT AS share_count ,d.CITEMS as citems_count,d.DATETIME as update_time FROM ufeng_tops as a JOIN ufeng_tops_to_channel as b on a.id= b.tops_id JOIN ufeng_tops_channel as c on c.id = b.channel_id JOIN wk_facebook_data_channel_date as d on c.code=d.channel and d.DATETIME='"
                + time
                + "'";

        List<Map<String, Object>> channelMapList = null;
        try {
          channelMapList = DBHelper.getQueryRunner().query(channelSql, new MapListHandler());

          System.out.println("插入ufeng_facebook_tops_class_count大小:" + channelMapList.size());
        } catch (SQLException e) {
          e.printStackTrace();
        }

        DBUtil.addListMapForClassCount(channelMapList, "ufeng_facebook_tops_class_count");
        // 生成Tv表
        new TvQueryFaceBook().oper(time);

      } else {
        Date myDate1 = null;
        try {
          myDate1 = sdf.parse(time);
        } catch (ParseException e1) {
          // TODO Auto-generated catch block
          e1.printStackTrace();
        }
        // 判断当前日期属于本年内第几周
        TimeUtil tu = new TimeUtil();
        intList = tu.getWeekOfYearFR(myDate1);
        String paramYear = String.valueOf(intList.get(0));
        String paramWeek = String.valueOf(intList.get(1));
        System.out.println(
            "查询表为:" + "wk_facebook_data_" + paramYear + "_" + paramWeek + "    传入time为:" + time);
        String sql =
            "SELECT e.CHANNEL, sum(`e`.`VIEWCOUNT`) AS `VIEWCOUNT`,sum(`e`.`GOODCOUNT`) AS `GOODCOUNT`,sum(`e`.`BADCOUNT`) AS `BADCOUNT`,sum(`e`.`SHARECOUNT`) AS `SHARECOUNT`,sum(`e`.`REVIEWCOUNT`) AS `REVIEWCOUNT`,count(`e`.`URL`) AS `CITEMS`,e.DATETIME from  wk_facebook_data_"
                + paramYear
                + "_"
                + paramWeek
                + " e WHERE DATETIME='"
                + time
                + "' and UPLOADTIME>= DATE_ADD(NOW(),INTERVAL "
                + setNum
                + " DAY)  GROUP BY CHANNEL";
        List<Map<String, Object>> mapList = null;
        try {
          mapList = DBHelper.getQueryRunner().query(sql, new MapListHandler());

          System.out.println(mapList.size());
        } catch (SQLException e) {
          e.printStackTrace();
        }
        DBUtil.addListMap(mapList, "wk_facebook_data_channel_date");
        // 生成channel表
        String channelSql =
            "SELECT a.*,a.id as top_id,d.VIEWCOUNT AS view_count, d.GOODCOUNT as good_count, d.BADCOUNT AS bad_count ,d.REVIEWCOUNT as review_count ,d.SHARECOUNT AS share_count ,d.CITEMS as citems_count,d.DATETIME as update_time FROM ufeng_tops as a JOIN ufeng_tops_to_channel as b on a.id= b.tops_id JOIN ufeng_tops_channel as c on c.id = b.channel_id JOIN wk_facebook_data_channel_date as d on c.code=d.channel and d.DATETIME='"
                + time
                + "'";

        List<Map<String, Object>> channelMapList = null;
        try {
          channelMapList = DBHelper.getQueryRunner().query(channelSql, new MapListHandler());

          System.out.println(channelMapList.size());
        } catch (SQLException e) {
          e.printStackTrace();
        }

        DBUtil.addListMapForClassCount(channelMapList, "ufeng_facebook_tops_class_count");
        // 生成Tv表
        new TvQueryFaceBook().oper(time);
      }
    } else {
      System.out.println("channel 中间表 已经生成");
    }
    //		}
    System.out.println("操作完成!");
  }
예제 #3
0
  @SuppressWarnings("deprecation")
  public void operFromTemp(String time, int setNum) {
    Date myDate1 = null;
    Calendar cal = Calendar.getInstance();
    try {
      myDate1 = sdf.parse(time);
    } catch (ParseException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
    }
    // 判断当前日期属于本年内第几周
    TimeUtil tu = new TimeUtil();
    intList = tu.getWeekOfYearFR(myDate1);
    String paramYear = String.valueOf(intList.get(0));
    String paramWeek = String.valueOf(intList.get(1));
    cal.setTime(myDate1);
    String month = String.valueOf(cal.get(Calendar.MONTH) + 1);
    System.out.println(
        "当前时间为:" + paramYear + "年" + month + "月" + paramWeek + " 周...  传入time为:" + time);

    // 根据今天时间获取上月时间
    Date lastMonth = new Date();
    String lastMonthStr = null;
    cal.setTime(myDate1);
    cal.add(Calendar.MONTH, -1);
    lastMonth = cal.getTime();
    lastMonthStr = String.valueOf(lastMonth.getMonth() + 1);
    cal.set(Calendar.DATE, 1);
    cal.roll(Calendar.DATE, -1);
    // System.out.println("此月最后一天为:"+);
    int totalDay = cal.get(Calendar.DATE);
    System.out.println("上个月为:" + lastMonthStr + "月,本月共有" + totalDay + "天");
    // 获取数据库连接配置
    DBHelper.setFileName(propertiesName);

    try {
      String channelJudge =
          "SELECT  month from ufeng_ggplus_tops_tv_count_month where month='"
              + lastMonthStr
              + "' limit 0,10";
      List<Map<String, Object>> judgeMapList = null;
      judgeMapList = DBHelper.getQueryRunner().query(channelJudge, new MapListHandler());

      if (judgeMapList != null && judgeMapList.size() > 1) {
        System.out.println("已生成" + lastMonthStr + "月数据!!!");
      } else {
        // 生成channel展示表
        String channelSql =
            "SELECT a.*,a.id as top_id,b.month as month,"
                + "sum(b.view_count) as view_count,sum(b.view_count_interval) as view_count_interval,"
                + "sum(b.good_count) as good_count ,sum(b.good_count_interval) as good_count_interval,"
                + "sum(b.bad_count) as bad_count,sum(b.bad_count_interval) as bad_count_interval,"
                + "sum(b.share_count) as share_count,sum(b.share_count_interval) as share_count_interval,"
                + "sum(b.review_count) as review_count,sum(b.review_count_interval) as review_count_interval,"
                + "sum(b.citems_count) as citems_count,sum(b.citems_count_interval) as citems_count_interval,"
                + "b.update_time FROM ufeng_tops as a "
                + "LEFT JOIN ufeng_ggplus_tops_class_count_month as b "
                + "ON a.id = b.pid and b.month ='"
                + lastMonthStr
                + "' WHERE a.level='1'  GROUP BY id";
        ;
        List<Map<String, Object>> channelMapList = null;
        channelMapList = DBHelper.getQueryRunner().query(channelSql, new MapListHandler());
        System.out.println("插入ufeng_ggplus_tops_tv_count_month表大小为:" + channelMapList.size());
        DBUtil.addListMapForTVCount(channelMapList, "ufeng_ggplus_tops_tv_count_month");
        System.out.println("插入ufeng_ggplus_tops_tv_count_month表大小为:" + channelMapList.size());
      }
      System.out.println("操作完成!");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }