//常量定义
public class Constant {

	public static Integer PM_YEAR_NO = 5;

}

public class ZhiBiaoPmAction extends BaseAction {

	private static final long serialVersionUID = 1L;

	@Autowired

	ZhiBiaoPmService zbser;

	public String cn_pm25() {

		Integer year = Integer.valueOf(Tool.getCurDateByType("yyyy"));

		List<Map<String, Object>> pmlt = zbser.getPM25lt(year);

		request.setAttribute("year", year);

		JSONObject result = JSONObject.fromObject(zbser.dataset(pmlt, year));

		request.setAttribute("result", result);

		JSONObject timeline = JSONObject.fromObject(zbser.yearset(year));

		request.setAttribute("timeline", timeline);

		return "cn_pm25";

	}

}

@Service

public class ZhiBiaoPmService {

	@Autowired

	ZhiBiaoPmDao pmdao;

	public List<Map<String, Object>> getPM25lt(Integer year) {

		return pmdao.getPM25lt(year);

	}

	public String dataset(List<Map<String, Object>> lt, Integer year) {

		StringBuffer jsonstr = new StringBuffer("");

		Map<String, Object> map = null;

		jsonstr.append("{");

		for (int j = year - Constant.PM_YEAR_NO; j < year; j++) {

			jsonstr.append("data:{title:{'text':'" + j + "年pm含量'},");

			jsonstr.append("series:[{");

			jsonstr.append("'data':[");

			for (int i = 0; i < lt.size(); i++) {

				map = lt.get(i);

				jsonstr.append("{name:'").append(map.get("regionname"));

				jsonstr.append("'").append(",value:").append(map.get("y" + j))

						.append("}");

				jsonstr.append((i == lt.size() - 1) ? "" : ",");

			}

			jsonstr.append("]");

			jsonstr.append("}]");

			jsonstr.append("}").append(j == year - 1 ? "" : ",");

		}

		jsonstr.append("}");

		System.out.println(jsonstr);

		return jsonstr.toString();

	}

	public String yearset(Integer year) {

		StringBuffer jsonstr = new StringBuffer("");

		jsonstr.append("{data:[");

		for (int j = year - Constant.PM_YEAR_NO; j < year; j++) {

			jsonstr.append("'").append(j).append("-01-01'");

			jsonstr.append(j == year - 1 ? "" : ",");

		}

		jsonstr.append("]}");

		return jsonstr.toString();

	}

}

@Repository

public class ZhiBiaoPmDao extends BaseDao<T, Serializable> {

	public List<Map<String, Object>> getPM25lt(Integer year) {

		StringBuffer sql = new StringBuffer();

		sql.append(" select t2.regionname");

		for (int i = year - Constant.PM_YEAR_NO; i < year; i++) {

			sql.append(" ,isnull([").append(i).append("],0) as 'y").append(i)

					.append("'");

		}

		sql.append(" from (");

		sql.append(" select qyregion_code");

		for (int i = year - Constant.PM_YEAR_NO; i < year; i++) {

			sql.append(" ,[").append(i).append("] as '").append(i).append("'");

		}

		sql.append(" from (");

		sql.append(" select qynianfen,qyregion_code,cast (qyzbvalue as numeric(18, 2)) qyzbvalue");

		sql.append(" from t_zhiqy where qyzbtype = 'pm'");

		sql.append(" ) t");

		sql.append(" pivot (");

		sql.append(" sum ([qyzbvalue]) for [qynianfen] in (");

		for (int i = year - Constant.PM_YEAR_NO; i < year; i++) {

			sql.append("[").append(i).append("]");

			sql.append(i != year - 1 ? "," : "");

		}

		sql.append(")");

		sql.append(" ) vpt)");

		sql.append(" t1 right join (select regioncode,regionname from t_cod_region where parentcode = '0') t2");

		sql.append(" on t1.qyregion_code = t2.regioncode");

		return this.search(sql.toString(), new Object[] {});

	}

}