一、主要工作：
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 t left join( select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_total from overview_game_server where 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_total from overview_game_server where 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_server left join( select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_total from overview_game_server where 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_server left join( select product,preiod_type_value,game,game_server,guild_upload_success_total,guild_upload_fail_total from overview_game_server where 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_server where t.game_server='null' and t.preiod_type = 1 and t.product='yygame' and t.preiod_type_value = '2012-02-05';
使用case when
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 " ;
2.数据库数据取出来后，使用
Map<String,Object> map = new HashMap<String,Object>();
map.put("tdate", date);
return getNamedParameterJdbcTemplate().queryForList(queryString.toString(),map);
返回一个List<Map<String,Object>>用于存储取出来的表格。
3.Controller层：Controller层实现对网页数据的输出，这里用到Spring的映射机制。
@RequestMapping
public 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);
// sum
Map 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;
}
}
4.前端显示，因为要做成一个连续5天的库表，前端显示的关键在于数据与显示时间匹配，还有注意的是数字的格式要为整数，要使用JSTL中的<fmt:formatNumber>
</fmt:formatNumber>，结合使用
<td><fmt:formatNumber value="${item['guild_upload_success_total4']}" pattern="####" /></td>
...
<td>...</td>
5.页面需要循环输出数据使用方法：
<c:forEach items="${yourMapping}" var="item" varStatus="index">
