stat2项目总结2012-02-27至2012-03-02
一、主要工作：
1. 页面上显示的数据是：上报次数、有效数据；
上报次数 = 上报成功次数 + 上报失败次数
有效数据 = 上报成功次数
2. 页面上横头显示连续5天的数据：
2.1 最后一天是的[系统当前日期的昨天]；
2.2 【日期选择框】中的日期，默认日期是[系统当前日期的昨天]，
2.3 页面上，还增加3个按钮：前一天 今天 后一天，
点击【今天】按钮，【日期选择框】中的日期变为默认日期，页面显示的5天数据的最后一天是默认日期；
3. 除了列出所有游戏外，还应列出：【全部游戏】，数据即为：单个游戏的汇总；
二、技术通关：
1.Dao层：使用Spring JDBC读取数据库字段，其中因为5天数据与数据字段的特殊关系，可以使用case when 或 left join：
使用left join有一个弊端，因为表格是向左表看齐，所以如果左表select出来为空，则后面全部都是空的了，所以最好使用case when。
left join
select t.product,t.preiod_type,t.preiod_type_value,t.game,t.game_server,t.guild_upload_success_total,t.guild_upload_fail_total,t1.product,t1.preiod_type_value,t1.game,t1.game_server,t1.guild_upload_success_total,t1.guild_upload_fail_total,t2.product,t2.preiod_type_value,t2.game,t2.game_server,t2.guild_upload_success_total,t2.guild_upload_fail_total,t3.product,t3.preiod_type_value,t3.game,t3.game_server,t3.guild_upload_success_total,t3.guild_upload_fail_total,t4.product,t4.preiod_type_value,t4.game,t4.game_server,t4.guild_upload_success_total,t4.guild_upload_fail_total from overview_game_server tleft join(select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_totalfrom overview_game_serverwhere game_server='null' and preiod_type = 1) t1 on t.preiod_type_value = DATE_SUB(t1.preiod_type_value,INTERVAL -1 DAY ) and t.game = t1.game and t.product=t1.product and t.game_server = t1.game_server left join(select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_totalfrom overview_game_serverwhere game_server='null' and preiod_type = 1) t2 on t.preiod_type_value = DATE_SUB(t2.preiod_type_value,INTERVAL -2 DAY ) and t.game = t2.game and t.product=t2.product and t.game_server = t2.game_serverleft join(select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_totalfrom overview_game_serverwhere game_server='null' and preiod_type = 1) t3 on t.preiod_type_value = DATE_SUB(t3.preiod_type_value,INTERVAL -3 DAY ) and t.game = t3.game and t.product=t3.product and t.game_server = t3.game_serverleft join(select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_totalfrom overview_game_serverwhere game_server='null' and preiod_type = 1) t4 on t.preiod_type_value = DATE_SUB(t4.preiod_type_value,INTERVAL -4 DAY ) and t.game = t4.game and t.product=t4.product and t.game_server = t4.game_serverwhere t.game_server='null' and t.preiod_type = 1 and t.product='yygame' and t.preiod_type_value = '2012-02-05';
String queryString = "select " +" product,game,preiod_type, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 0 day) then preiod_type_value else date_sub(:tdate, interval 0 day) end) as times0, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 0 day) then guild_upload_success_total else 0 end) as guild_upload_success_total, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 0 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 1 day) then preiod_type_value else date_sub(:tdate, interval 1 day) end) as times1, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 1 day) then guild_upload_success_total else 0 end) as guild_upload_success_total1, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 1 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total1, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 2 day) then preiod_type_value else date_sub(:tdate, interval 2 day) end) as times2, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 2 day) then guild_upload_success_total else 0 end) as guild_upload_success_total2, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 2 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total2, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 3 day) then preiod_type_value else date_sub(:tdate, interval 3 day) end) as times3, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 3 day) then guild_upload_success_total else 0 end) as guild_upload_success_total3, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 3 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total3, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 4 day) then preiod_type_value else date_sub(:tdate, interval 4 day) end) as times4, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 4 day) then guild_upload_success_total else 0 end) as guild_upload_success_total4, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 4 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total4, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 5 day) then preiod_type_value else date_sub(:tdate, interval 5 day) end) as times5, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 5 day) then guild_upload_success_total else 0 end) as guild_upload_success_total5, " +" max(case when preiod_type=1 and preiod_type_value=date_sub(:tdate, interval 5 day) then guild_upload_fail_total else 0 end) as guild_upload_fail_total5 " +" from overview_game_server " +" where preiod_type_value>= date_sub(:tdate, interval 5 day) and product=:product and preiod_type=1 and game_server='null' " +" group by product,game " ;
Map<String,Object> map = new HashMap<String,Object>();map.put("tdate", date);return getNamedParameterJdbcTemplate().queryForList(queryString.toString(),map);@RequestMappingpublic String queryGuildGameUploadTotal(Date startDate,HttpServletRequest request, ModelMap model) {startDate = (Date) ObjectUtils.defaultIfNull(startDate,DateUtils.addDays(DateUtil.getToday(), -1));List<Map<String, Object>> dataList = overviewGameServerService.listGuildGameUploadTotal(startDate);model.put("dataList", dataList);model.put("startDate", startDate);addDateList2Model(startDate, model);// sumMap sumMap = MapCalcUtil.sumByMultiKeys(dataList, "guild_upload_success_total","guild_upload_fail_total","guild_upload_success_total1","guild_upload_fail_total1","guild_upload_success_total2","guild_upload_fail_total2","guild_upload_success_total3","guild_upload_fail_total3","guild_upload_success_total4","guild_upload_fail_total4");sumMap.put("game", "全部游戏");//在game字段前添加全部游戏的<td>dataList.add(0, sumMap);return "overviewgameserver/queryGuildGameUploadTotal";}/** * model.put("startDate" + (4-i), DateUtils.addDays(startDate, -i)); 使日期从低到高排序（注意对应数据库表的数据排序） * @param startDate * @param model */private void addDateList2Model(Date startDate, ModelMap model) {for (int i = 0; i < 5; i++) {model.put("startDate" + (4-i), DateUtils.addDays(startDate, -i));}}注：sumMap.put("game", "全部游戏");对应的是game字段。public class MapCalcUtil {public static Map<String,Double> sumByMultiKeys(List<Map<String,Object>> rows,String... keys) {Map<String, Double> result = new HashMap<String, Double>();for(String key : keys) {double v = sum(rows, key);result.put(key, v);}return result;}public static double sum(List<Map<String,Object>> rows,String key) {double result = 0;for(Map row : rows) {Number number = (Number)row.get(key);if(number != null) {result += number.doubleValue();}}return result;}}<td><fmt:formatNumber value="${item['guild_upload_success_total4']}" pattern="####" /></td>...<td>...</td><c:forEach items="${yourMapping}" var="item" varStatus="index">