@Override
public AcademicKPIWrapper getAllByAcademicYear(String getByAcademicYear) {
String sqlpbp_work_type =
" select * from pbp_work_type where academic_year ='" + getByAcademicYear + "'";
logger.info(" getAllByAcademicYear sqlpbp_work_type:" + sqlpbp_work_type);
AcademicKPIWrapper academicKPIWrapper = new AcademicKPIWrapper();
List<PBPWorkType> pBPWorkTypeList =
this.jdbcTemplate.query(sqlpbp_work_type, new PBPWorkTypeMapper());
for (PBPWorkType tmp : pBPWorkTypeList) {
String sqlkpi =
" select * from academic_kpi where academic_year ='"
+ getByAcademicYear
+ "' and work_type_code ='"
+ tmp.getCode()
+ "' order by order_no";
logger.info(" sqlkpi:" + sqlkpi);
List<AcademicKPI> academicKPIList = null;
try {
academicKPIList = this.jdbcTemplate.query(sqlkpi, new AcademicKPIMapper());
} catch (org.springframework.dao.EmptyResultDataAccessException ex) {
ex.printStackTrace();
}
for (AcademicKPI tmpxx : academicKPIList) {
String sqlAttribute =
" select * from academic_kpi_attribute where academic_year ='"
+ tmpxx.getAcademicYear()
+ "' and academic_kpi_code ='"
+ tmpxx.getCode()
+ "'";
List<AcademicKPIAttribute> academicKPIAttributeList = new ArrayList();
try {
academicKPIAttributeList =
this.jdbcTemplate.query(sqlAttribute, new AcademicKPIAttributeMapper());
} catch (org.springframework.dao.EmptyResultDataAccessException ex) {
ex.printStackTrace();
}
tmpxx.setAcademicKPIAttributeList(academicKPIAttributeList);
}
tmp.setAcademicKPIList(academicKPIList);
}
academicKPIWrapper.setpBPWorkTypeList(pBPWorkTypeList);
return academicKPIWrapper;
}
@Override
public Long create(AcademicKPI domain) {
logger.info("domain : " + BeanUtils.getBeanString(domain));
final int nexCode = generateCodeUtil.getNextaAcademicKPI(domain);
final AcademicKPI finalDomain = domain;
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(
""
+ " insert into academic_kpi (name, code,work_type_code,mark,academic_year,unit_code,rule_code,order_no,description) values (?, ?,?,?,?,?,?,?,?)"
+ "",
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, finalDomain.getName());
ps.setInt(2, nexCode);
ps.setInt(3, new Integer(finalDomain.getWorkTypeCode()));
ps.setBigDecimal(4, finalDomain.getMark());
ps.setString(5, finalDomain.getAcademicYear());
ps.setInt(6, new Integer(finalDomain.getUnitCode()));
ps.setString(7, finalDomain.getMultiplyValue());
ps.setInt(8, new Integer(finalDomain.getOrderNo()));
ps.setString(9, finalDomain.getDescription());
return ps;
}
},
keyHolder);
Long returnid = keyHolder.getKey().longValue();
AcademicKPIAttribute academicKPIAttribute = new AcademicKPIAttribute();
academicKPIAttribute.setAcademicKPICode(nexCode + "");
academicKPIAttribute.setAcademicKPIId(returnid);
academicKPIAttribute.setName("���ͧҹ");
// academicKPIAttribute.setMandatory("Y");
academicKPIAttribute.setAcademicYear(finalDomain.getAcademicYear());
addNewAttribute(academicKPIAttribute);
return returnid;
}
@Override
public boolean isExistCreate(AcademicKPI domain) {
boolean returnValue = false;
try {
String sqltmp =
"select count(*) as totalItem from academic_kpi t where t.name='"
+ StringEscapeUtils.escapeSql(domain.getName())
+ "' and t.work_type_code='"
+ domain.getWorkTypeCode()
+ "' and t.academic_year='"
+ domain.getAcademicYear()
+ "'";
Long found = this.jdbcTemplate.queryForLong(sqltmp);
if (found != null && found.intValue() > 0) {
returnValue = true;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return returnValue;
}
@Override
public AcademicKPI getByCodeAcademicYear(String code, String getByAcademicYear) {
String sql =
" select * from academic_kpi where academic_year ='"
+ getByAcademicYear
+ "' and code ='"
+ code
+ "'";
logger.info(" sql:" + sql);
AcademicKPI academicKPI = null;
try {
academicKPI = this.jdbcTemplate.queryForObject(sql, new AcademicKPIMapper());
} catch (org.springframework.dao.EmptyResultDataAccessException ex) {
ex.printStackTrace();
}
if (academicKPI != null) {
// Get KPI Attribute
String sqlAttribute =
" select * from academic_kpi_attribute where academic_year ='"
+ getByAcademicYear
+ "' and academic_kpi_code ='"
+ code
+ "'";
List<AcademicKPIAttribute> academicKPIAttributeList = new ArrayList();
try {
academicKPIAttributeList =
this.jdbcTemplate.query(sqlAttribute, new AcademicKPIAttributeMapper());
} catch (org.springframework.dao.EmptyResultDataAccessException ex) {
ex.printStackTrace();
}
academicKPI.setAcademicKPIAttributeList(academicKPIAttributeList);
}
return academicKPI;
}
@Override
public AcademicKPI getById(String id) {
String sql = " select * from academic_kpi where academic_kpi_id =" + id + "";
logger.info(" sql:" + sql);
AcademicKPI academicKPI =
academicKPI = this.jdbcTemplate.queryForObject(sql, new AcademicKPIMapper());
if (academicKPI != null) {
String sqlAttribute =
" select * from academic_kpi_attribute where academic_kpi_code ='"
+ academicKPI.getCode()
+ "' and academic_year='"
+ academicKPI.getAcademicYear()
+ "'";
logger.info(" sqlAttribute:" + sqlAttribute);
List<AcademicKPIAttribute> academicKPIAttributeList =
this.jdbcTemplate.query(sqlAttribute, new AcademicKPIAttributeMapper());
academicKPI.setAcademicKPIAttributeList(academicKPIAttributeList);
}
return academicKPI;
}
@Override
public AcademicKPI mapRow(ResultSet rs, int rowNum) throws SQLException {
AcademicKPI domain = new AcademicKPI();
domain.setAcademicKPIId(rs.getLong("academic_kpi_id"));
domain.setName(rs.getString("name"));
domain.setCode(rs.getString("code"));
domain.setWorkTypeCode(rs.getString("work_type_code"));
domain.setDescription(rs.getString("description"));
domain.setStatus(rs.getString("status"));
domain.setAcademicYear(rs.getString("academic_year"));
domain.setMark(rs.getBigDecimal("mark"));
domain.setUnitCode(rs.getString("unit_code"));
domain.setMultiplyValue(rs.getString("rule_code"));
domain.setOrderNo(rs.getString("order_no"));
domain.setSpecialP1(rs.getString("special_p1"));
domain.setSpecialP2(rs.getString("special_p2"));
domain.setSpecialP3(rs.getString("special_p3"));
domain.setSpecialP4(rs.getString("special_p4"));
domain.setTotalStudentFrom(rs.getString("total_student_from"));
domain.setTotalStudentTo(rs.getString("total_student_to"));
domain.setFromRegis(rs.getString("from_reg"));
// logger.info(" ###### Multiply Value:"+rs.getString("rule_code"));
try {
domain.setUnitDesc(
schoolUtil.getUnitDescMyCode(rs.getString("unit_code"), rs.getString("academic_year")));
} catch (org.springframework.dao.EmptyResultDataAccessException ex) {
ex.printStackTrace();
}
return domain;
}
@Override
public void edit(AcademicKPI domain) {
logger.info("domain : " + BeanUtils.getBeanString(domain));
this.jdbcTemplate.update(
"update academic_kpi set name=?,mark=?,unit_code=? ,rule_code=? ,order_no=?,description=? ,"
+ " special_p1=?, special_p2=?, special_p3=?, special_p4=?, total_student_from=?, total_student_to=?,from_reg=? where academic_kpi_id=? ",
domain.getName(),
domain.getMark(),
domain.getUnitCode(),
domain.getMultiplyValue(),
domain.getOrderNo(),
domain.getDescription(),
domain.getSpecialP1(),
domain.getSpecialP2(),
domain.getSpecialP3(),
domain.getSpecialP4(),
domain.getTotalStudentFrom(),
domain.getTotalStudentTo(),
domain.getFromRegis(),
domain.getAcademicKPIId());
List<AcademicKPIAttribute> academicKPIAttributeList = domain.getAcademicKPIAttributeList();
if (academicKPIAttributeList != null && academicKPIAttributeList.size() > 0) {
for (AcademicKPIAttribute tmp : academicKPIAttributeList) {
logger.info(" checkValue:" + tmp.getIsCalculate());
String isCheckFlage = "on".equalsIgnoreCase(tmp.getIsCalculate()) ? "Y" : "N";
String isCheckFlage2 = "on".equalsIgnoreCase(tmp.getIsValidateNumber()) ? "Y" : "N";
logger.info(
" Name :"
+ tmp.getName()
+ " isCal:"
+ isCheckFlage
+ " isValidateNumber:"
+ isCheckFlage2);
this.jdbcTemplate.update(
"update academic_kpi_attribute set name=?, is_calculate=?,is_validate_number=? where kpi_attribute_id=? ",
tmp.getName(),
isCheckFlage,
isCheckFlage2,
tmp.getAcademicKPIAtributeId());
}
}
}