// 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("操作完成！");
}
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("操作完成！");
}
@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();
}
}