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 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">