package com.defaultcompany.external.repository;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.defaultcompany.external.model.Approver;
public class ApproverDAO {
private JdbcTemplate jt;
public ApproverDAO(DataSource ds) {
jt = new JdbcTemplate(ds);
}
public Approver getByInstanceIdAndTracingTag(Serializable approvalKey, Serializable instanceId, Serializable tracingTag) {
StringBuffer sql = new StringBuffer();
// 유저 정보 없는 쿼리
// sql.append(" SELECT ");
// sql.append(" REG.INSTID, INFO.APPR_KEY, INFO.APPR_VERSION, INFO.APPR_EMPCODE, INFO.APPR_TRUTH_EMPCODE, ");
// sql.append(" INFO.APPR_TYPE, INFO.APPR_COMMENT, INFO.APPR_STATUS, INFO.APPR_ENDDATE, ");
// sql.append(" INFO.TASKID, INFO.TRCTAG, INFO.APPR_ORDERBY ");
// sql.append(" FROM BPM_APPRREG REG LEFT JOIN BPM_APPRINFO INFO ON REG.APPR_KEY=INFO.APPR_KEY ");
// sql.append(" WHERE REG.INSTID = ? AND INFO.TRCTAG = ? ");
// sql.append(" AND INFO.APPR_VERSION = (select MAX(APPR_VERSION) from BPM_APPRINFO where APPR_KEY = ?) ");
sql.append("SELECT ");
sql.append("B.* ");
sql.append("FROM ");
sql.append("( ");
sql.append(" SELECT ");
sql.append(" A.*, ");
sql.append(" EMP.EMPNAME AS APPR_TRUTH_EMPNAME, ");
sql.append(" EMP.JIKNAME AS APPR_TRUTH_JIKNAME, ");
sql.append(" EMP.PARTCODE AS APPR_TRUTH_PARTCODE, ");
sql.append(" PART.PARTNAME AS APPR_TRUTH_PARTNAME ");
sql.append(" FROM ");
sql.append(" ( ");
sql.append(" SELECT ");
sql.append(" REG.INSTID, ");
sql.append(" INFO.APPR_KEY, ");
sql.append(" INFO.APPR_VERSION, ");
sql.append(" INFO.APPR_TYPE, ");
sql.append(" INFO.APPR_COMMENT, ");
sql.append(" INFO.APPR_STATUS, ");
sql.append(" INFO.APPR_ENDDATE, ");
sql.append(" INFO.TASKID, ");
sql.append(" INFO.TRCTAG, ");
sql.append(" INFO.APPR_ORDERBY, ");
sql.append(" INFO.APPR_EMPCODE, ");
sql.append(" EMP.EMPNAME AS APPR_EMPNAME, ");
sql.append(" EMP.JIKNAME AS APPR_JIKNAME, ");
sql.append(" EMP.PARTCODE AS APPR_PARTCODE, ");
sql.append(" PART.PARTNAME AS APPR_PARTNAME, ");
sql.append(" INFO.APPR_TRUTH_EMPCODE ");
sql.append(" FROM BPM_APPRREG REG LEFT JOIN BPM_APPRINFO INFO ON REG.APPR_KEY=INFO.APPR_KEY ");
sql.append(" LEFT JOIN EMPTABLE EMP ON EMP.EMPCODE = INFO.APPR_EMPCODE ");
sql.append(" LEFT JOIN PARTTABLE PART ON PART.PARTCODE = EMP.PARTCODE ");
sql.append(" WHERE REG.INSTID = ? AND INFO.TRCTAG = ? ");
sql.append(" AND INFO.APPR_VERSION = ");
sql.append(" ( ");
sql.append(" select ");
sql.append(" MAX(APPR_VERSION) ");
sql.append(" from BPM_APPRINFO ");
sql.append(" where APPR_KEY = ? ");
sql.append(" ) ");
sql.append(" ) ");
sql.append(" A ");
sql.append(" LEFT JOIN EMPTABLE EMP ON EMP.EMPCODE = A.APPR_TRUTH_EMPCODE ");
sql.append(" LEFT JOIN PARTTABLE PART ON PART.PARTCODE = EMP.PARTCODE ");
sql.append(") ");
sql.append("B ");
try {
return (Approver) jt.queryForObject(sql.toString(), new Object[] { instanceId, tracingTag, approvalKey }, new ApproverRowMapper());
} catch (DataAccessException e) {
System.err.println("결재선 정보 가져오기 실패");
System.err.println("instanceId:" + instanceId + "\ttracingTag:" + tracingTag);
e.printStackTrace();
return null;
}
}
public void add(final List<Approver> approverList) {
StringBuffer sql = new StringBuffer();
sql.append(" INSERT INTO BPM_APPRINFO ");
sql.append(" (APPR_KEY, APPR_VERSION, APPR_EMPCODE, APPR_TRUTH_EMPCODE, APPR_TYPE, APPR_COMMENT, APPR_STATUS, APPR_ENDDATE, TASKID, TRCTAG, APPR_ORDERBY) ");
sql.append(" VALUES (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ");
BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int index) throws SQLException {
Approver approver = approverList.get(index);
int parameterIndex = 1;
ps.setString(parameterIndex++, approver.getApprovalKey());
ps.setInt(parameterIndex++, approver.getVersion());
ps.setString(parameterIndex++, approver.getEmpCode());
ps.setString(parameterIndex++, approver.getTruthEmpCode());
ps.setString(parameterIndex++, approver.getType());
ps.setString(parameterIndex++, approver.getComment());
ps.setString(parameterIndex++, approver.getStatus());
ps.setTimestamp(parameterIndex++, approver.getEndDate());
ps.setString(parameterIndex++, approver.getTaskId());
ps.setString(parameterIndex++, approver.getTracingTag());
ps.setInt(parameterIndex++, approver.getOrder());
}
public int getBatchSize() {
return approverList.size();
}
};
this.jt.batchUpdate(sql.toString(), pss);
}
public void update(Approver... approver) {
this.update(Arrays.asList(approver));
}
public void update(final List<Approver> approverList) {
StringBuffer sql = new StringBuffer();
sql.append(" UPDATE BPM_APPRINFO ");
sql.append(" SET TASKID = ?, APPR_TRUTH_EMPCODE = ?, APPR_STATUS = ?, APPR_ENDDATE = ?, TRCTAG = ?, APPR_COMMENT =? ");
sql.append(" WHERE APPR_KEY = ? AND APPR_ORDERBY = ? AND APPR_VERSION = ? ");
BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int index) throws SQLException {
Approver approver = approverList.get(index);
int parameterIndex = 1;
ps.setString(parameterIndex++, approver.getTaskId());
ps.setString(parameterIndex++, approver.getTruthEmpCode());
ps.setString(parameterIndex++, approver.getStatus());
ps.setTimestamp(parameterIndex++, approver.getEndDate());
ps.setString(parameterIndex++, approver.getTracingTag());
ps.setString(parameterIndex++, approver.getComment());
ps.setString(parameterIndex++, approver.getApprovalKey());
ps.setInt(parameterIndex++, approver.getOrder());
ps.setInt(parameterIndex++, approver.getVersion());
}
public int getBatchSize() {
return approverList.size();
}
};
this.jt.batchUpdate(sql.toString(), pss);
}
@SuppressWarnings("unchecked")
public List<Approver> list(Serializable approvalLineKey) {
StringBuffer sql = new StringBuffer();
// 유저 정보 없는 쿼리
// sql.append(" SELECT ");
// sql.append(" APPR_KEY, APPR_VERSION, APPR_EMPCODE, APPR_TRUTH_EMPCODE, APPR_TYPE, APPR_COMMENT, APPR_STATUS, APPR_ENDDATE, TASKID, TRCTAG, APPR_ORDERBY ");
// sql.append(" FROM BPM_APPRINFO ");
// sql.append(" WHERE APPR_KEY = ? ");
// sql.append(" AND APPR_VERSION = (select MAX(APPR_VERSION) from BPM_APPRINFO where APPR_KEY = ?) ");
// sql.append(" ORDER BY APPR_ORDERBY ASC ");
sql.append("SELECT ");
sql.append("B.* ");
sql.append("FROM ");
sql.append("( ");
sql.append(" SELECT ");
sql.append(" A.*, ");
sql.append(" EMP.EMPNAME AS APPR_TRUTH_EMPNAME, ");
sql.append(" EMP.JIKNAME AS APPR_TRUTH_JIKNAME, ");
sql.append(" EMP.PARTCODE AS APPR_TRUTH_PARTCODE, ");
sql.append(" PART.PARTNAME AS APPR_TRUTH_PARTNAME ");
sql.append(" FROM ");
sql.append(" ( ");
sql.append(" SELECT ");
sql.append(" INFO.APPR_KEY, ");
sql.append(" INFO.APPR_VERSION, ");
sql.append(" INFO.APPR_TYPE, ");
sql.append(" INFO.APPR_COMMENT, ");
sql.append(" INFO.APPR_STATUS, ");
sql.append(" INFO.APPR_ENDDATE, ");
sql.append(" INFO.TASKID, ");
sql.append(" INFO.TRCTAG, ");
sql.append(" INFO.APPR_ORDERBY, ");
sql.append(" INFO.APPR_EMPCODE, ");
sql.append(" EMP.EMPNAME AS APPR_EMPNAME, ");
sql.append(" EMP.JIKNAME AS APPR_JIKNAME, ");
sql.append(" EMP.PARTCODE AS APPR_PARTCODE, ");
sql.append(" PART.PARTNAME AS APPR_PARTNAME, ");
sql.append(" INFO.APPR_TRUTH_EMPCODE ");
sql.append(" FROM BPM_APPRINFO INFO ");
sql.append(" LEFT JOIN EMPTABLE EMP ON EMP.EMPCODE = INFO.APPR_EMPCODE ");
sql.append(" LEFT JOIN PARTTABLE PART ON PART.PARTCODE = EMP.PARTCODE ");
sql.append(" WHERE INFO.APPR_KEY = ? ");
sql.append(" AND INFO.APPR_VERSION = ");
sql.append(" ( ");
sql.append(" select ");
sql.append(" MAX(APPR_VERSION) ");
sql.append(" from BPM_APPRINFO ");
sql.append(" where APPR_KEY = ? ");
sql.append(" ) ");
sql.append(" ) ");
sql.append(" A ");
sql.append(" LEFT JOIN EMPTABLE EMP ON EMP.EMPCODE = A.APPR_TRUTH_EMPCODE ");
sql.append(" LEFT JOIN PARTTABLE PART ON PART.PARTCODE = EMP.PARTCODE ");
sql.append(") ");
sql.append("B ");
sql.append("ORDER BY B.APPR_ORDERBY ASC ");
return jt.query(sql.toString(), new Object[] { approvalLineKey, approvalLineKey }, new ApproverRowMapper());
}
class ApproverRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
Approver approver = new Approver();
approver.setApprovalKey(rs.getString("APPR_KEY"));
approver.setVersion(rs.getInt("APPR_VERSION"));
approver.setComment(rs.getString("APPR_COMMENT"));
approver.setEndDate(rs.getTimestamp("APPR_ENDDATE"));
approver.setType(rs.getString("APPR_TYPE"));
approver.setStatus(rs.getString("APPR_STATUS"));
approver.setTaskId(rs.getString("TASKID"));
approver.setTracingTag(rs.getString("TRCTAG"));
approver.setOrder(rs.getInt("APPR_ORDERBY"));
approver.setEmpCode(rs.getString("APPR_EMPCODE"));
approver.setTruthEmpCode(rs.getString("APPR_TRUTH_EMPCODE"));
return approver;
}
}
}