/*
* 获取所有收费标准费用总和
* Map(key:标准ID value:费用总和_学分费用)
* 如果学分不为0，则收费标准为学分否则为年度
*/
private Map<String, String> getFeeStandard() {
Map<String, String> mapResult = new HashMap<String, String>();
String sql =
" select policy_fee_detail_id,sum(charging_floor) as jin_e,credit_fee as xue_fen "
+ " from tb_e_fee_standard "
+ " where 1=1 "
+ " group by policy_fee_detail_id,credit_fee ";
System.out.println(sql);
@SuppressWarnings("unchecked")
List<Map<String, String>> listResult =
jdbcTemplatePlus.query(
sql,
new RowMapper() {
public Map<String, String> mapRow(ResultSet resultSet, int index)
throws SQLException {
Map<String, String> map = new HashMap<String, String>();
map.put("policy_fee_detail_id", resultSet.getString("policy_fee_detail_id"));
map.put("jin_e", resultSet.getString("jin_e"));
map.put("xue_fen", resultSet.getString("xue_fen"));
return map;
}
});
if (listResult != null) {
for (Map<String, String> map : listResult) {
mapResult.put(
map.get("policy_fee_detail_id") + "", map.get("jin_e") + "_" + map.get("xue_fen"));
}
}
return mapResult;
}
/*
* 根据批次中心获取中心院校政策的标准id
* Map(key:中心ID_院校ID value:标准IDList)
* 如果批次为空，则返回空
*/
private Map<String, List<Integer>> getPolicyFeeByBatch(int batchId, int branchId, int academyId) {
Map<String, List<Integer>> mapResult = new HashMap<String, List<Integer>>();
String sql =
" select branch_id,academy_id,policy_fee_id from tb_e_policy_fee_detail "
+ " where 1=1 "
+ " and audit_status = "
+ Constants.AUDIT_STATUS_TRUE
+ " "
+ " and fee_subject_id = "
+ FeeSubjectEnum.TuitionFee.value()
+ " ";
if (branchId != -2) {
sql += " and branch_id = " + branchId + " ";
}
if (academyId != -2) {
sql += " and academy_id = " + academyId + " ";
}
sql +=
" and batch_id in "
+ " ( "
+ " select id from tb_e_academy_enroll_batch "
+ " where delete_flag=0 ";
if (batchId != -2) {
sql += " and global_enroll_batch_id = " + batchId + " ";
} else {
return mapResult;
}
sql += " ) group by branch_id,academy_id,policy_fee_id ";
System.out.println(sql);
@SuppressWarnings("unchecked")
List<Map<String, Integer>> listResult =
jdbcTemplatePlus.query(
sql,
new RowMapper() {
public Map<String, Integer> mapRow(ResultSet resultSet, int index)
throws SQLException {
Map<String, Integer> map = new HashMap<String, Integer>();
map.put("branch_id", resultSet.getInt("branch_id"));
map.put("academy_id", resultSet.getInt("academy_id"));
map.put("policy_fee_id", resultSet.getInt("policy_fee_id"));
return map;
}
});
List<Integer> policyFeeIds = null;
if (listResult != null) {
for (Map<String, Integer> map : listResult) {
if (!mapResult.containsKey(map.get("branch_id") + "_" + map.get("academy_id"))) {
policyFeeIds = new ArrayList<Integer>();
policyFeeIds.add(map.get("policy_fee_id"));
mapResult.put(map.get("branch_id") + "_" + map.get("academy_id"), policyFeeIds);
} else {
mapResult
.get(map.get("branch_id") + "_" + map.get("academy_id"))
.add(map.get("policy_fee_id"));
}
}
}
return mapResult;
}
public Map statistics(Map<String, Integer> params) {
jdbcTemplatePlus = getJdbcTemplatePlus();
final int school = params.get("school"); // 学院
final int batch = params.get("batch"); // 全局批次
// final int serachStudentDataSource = params.get("studentDataSource");// 数据来源
// final int way = params.get("way");// 市场途径
// final int source = params.get("source");// 招生途径
// final int quyuId = params.get("manager");// 区域经理ID
final int xuexiId = params.get("branch"); // 学习中心
// final int fuwuId = params.get("fuwu");// 服务站ID
// final int userId = params.get("user");// 用户ID
// 初始化map集合
// Map(key:中心ID_院校ID value:标准ID)
final Map<String, List<Integer>> key_a_b_value_policy_fee =
getPolicyFeeByBatch(batch, xuexiId, school);
// Map(key:标准ID value:费用总和_学分)
final Map<String, String> key_a_b_value_fee_standard = getFeeStandard();
// 生成%数
final NumberFormat format = NumberFormat.getPercentInstance(); // 获取格式化类实例
format.setMinimumFractionDigits(2); // 设置小数位
// 金钱
final DecimalFormat format_ = new DecimalFormat();
format_.setMinimumFractionDigits(2);
// 获取所有中心
String branchSql =
" select id,name,short_name from tb_e_branch where delete_flag="
+ Constants.DELETE_FALSE
+ " and id>"
+ BranchEnum.Admin.value()
+ " ";
if (xuexiId != -2) {
branchSql += " and id=" + xuexiId;
}
// 获取所有院校
String academySql =
" and id in "
+ " ( "
+ " select academyId from AcademyEnrollBatch "
+ " where deleteFlag= "
+ Constants.DELETE_FALSE
+ " ";
if (batch != -2) {
academySql += " and globalEnrollBatchId = " + batch + " ";
}
academySql += " ) ";
if (school != -2) {
academySql += " and id = " + school + " ";
}
final List<Academy> academyList = academyDao.getByProperty(academySql);
// 遍历中心
List branchList =
jdbcTemplatePlus.query(
branchSql,
new RowMapper() {
public Object mapRow(ResultSet resultSet, int index) throws SQLException {
Map<String, Object> branchMap = new HashMap<String, Object>();
branchMap.put("branch_id", resultSet.getInt("id"));
branchMap.put("branch_name", resultSet.getString("name"));
branchMap.put("branch_city", resultSet.getString("short_name"));
// 遍历院校
List<String> tuitionStandard = new ArrayList<String>();
if (academyList != null && academyList.size() > 0) {
for (Academy academy : academyList) {
tuitionStandard.add(
getTuitionStandard(
key_a_b_value_policy_fee.get(
resultSet.getInt("id") + "_" + academy.getId()),
key_a_b_value_fee_standard));
}
}
branchMap.put("tuitionStandard", tuitionStandard);
return branchMap;
}
});
Map<String, Object> resultMap = new HashMap<String, Object>();
resultMap.put("academyList", academyList);
resultMap.put("branchList", branchList);
return resultMap;
}