# EcnDAOImpl.java

    package com.mycim.server.ecn.dao.impl;

    import com.fa.sesa.threadlocal.LocalContext;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.Page;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.ecn.dao.EcnDAO;
    import com.mycim.server.ecn.dao.mapper.EcnMapper;
    import com.mycim.valueobject.bas.ObjectVersion;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.ObjectStatus;
    import com.mycim.valueobject.prp.ContextValue;
    import com.mycim.valueobject.prp.Ecn;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    /**
     * @author Johnson.Wang
     * @version 6.0.0
     * @date 2019/8/31
     **/
    @Repository
    public class EcnDAOImpl implements EcnDAO {

        private final JdbcTemplate jdbcTemplate;

        public EcnDAOImpl(JdbcTemplate jdbcTemplate) {
            this.jdbcTemplate = jdbcTemplate;
        }

        @Override
        public String getMaxEcnNo(String today) {
            today = "F" + today + "." + "_____";
            String sql = "select count(t.instance_id) as cutid from named_object t where object='ECN' and t" +
                    ".instance_id like '" + today + "' ";
            long count = jdbcTemplate.queryForObjectWithNull(sql, null, long.class);

            if (count > 0) {
                sql = "select instance_id " + "from (" + "select SUBSTR(t.instance_id,INSTR( t.instance_id,'.') + " +
                        "1,LENGTH(t.instance_id))" + " as d, " +
                        "t.instance_rrn as rrn,t.instance_id " + "from named_object t" +
                        " " + "where t.object='ECN' " + "and t.instance_id like '" + today + "' " + "ORDER BY d DESC " +
                        " ) l where rownum =1 ";
                return jdbcTemplate.queryForObjectWithNull(sql, null, String.class).toUpperCase().trim();
            }

            return null;
        }

        @Override
        public void insertEcn(Ecn ecn) {
            String ecnStatus;
            if ("ACTIVEP".equalsIgnoreCase(ecn.getEcnStatus())) {
                ecnStatus = ObjectStatus.ACTIVE_KEY;
            } else if (StringUtils.equals("SPLITRUNCARD", ecn.getObjectSubtype()) ||
                    StringUtils.equals("RECOVERYRUNCARD", ecn.getObjectSubtype())) {
                ecnStatus = ecn.getEcnStatus();
            } else {
                ecnStatus = ObjectStatus.UNFROZEN_KEY;
            }

            String sql = "INSERT INTO " + DataBaseNames.ECN + "(" + "ECN_RRN,REQUEST_BY,COMMENTS,ECN_STATUS," + "SYS_RRN," +
                    "ECN_ID,FACILITY_RRN,DESCRIPTION,OBJ_SPACE," + "OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS,CREATE_TIME," +
                    "UPDATE_TIME," + "CREATE_USER_RRN,UPDATE_USER_RRN,LOCK_VERSION," + "EFFECTIVE_DATE_FROM," +
                    "EFFECTIVE_DATE_TO" + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,";

            if (StringUtils.isNotEmpty(ecn.getEffectiveDateFrom())) {
                sql = sql + " to_date('" + ecn.getEffectiveDateFrom() + "', '" + DateUtils.DATE_FORMAT24 + "'),";
            } else {
                sql = sql + " null,";
            }

            if (StringUtils.isNotEmpty(ecn.getEffectiveDateTo())) {
                sql = sql + "to_date('" + ecn.getEffectiveDateTo() + "', '" + DateUtils.DATE_FORMAT24 + "')";
            } else {
                sql = sql + " null";
            }

            sql = sql + ")";
            Object[] args = new Object[]{ecn.getInstanceRrn(), ecn.getRequestBy(), ecn.getComments(), ecnStatus,
                    ecn.getInstanceRrn(), ecn.getInstanceId(), LocalContext.getFacilityRrn(), ecn.getInstanceDesc(),
                    ecn.getNamedSpace(), ecn.getObjectType(), ecn.getObjectSubtype(), ecn.getInstanceStatus(),
                    ecn.getCreatedTimestamp(), ecn.getCreatedTimestamp(), ecn.getRequestBy(), ecn.getRequestBy(),
                    ecn.getLockVersion()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertEcnHistory(Long transRrn, Integer transSequence, String transId, Long ecnRrn,
                                     Timestamp transTime) {
            String sql = "INSERT INTO  ECN_H  (TRANS_RRN, TRANS_SEQUENCE, TRANS_ID, " + " ECN_RRN, REQUEST_BY, " +
                    "APPROVAL_WFL_RRN, COMMENTS, " + " ECN_STATUS, EFFECTIVE_DATE_FROM, EFFECTIVE_DATE_TO," + " SYS_RRN," +
                    "ECN_ID,FACILITY_RRN,DESCRIPTION,OBJ_SPACE," + " OBJ_TYPE,OBJ_SUBTYPE,OBJ_STATUS,TRANS_USER_RRN," +
                    "TRANS_TIME" + ") SELECT " + " ?, ?, ?, ECN_RRN, REQUEST_BY, APPROVAL_WFL_RRN, COMMENTS, " + " " +
                    "ECN_STATUS, EFFECTIVE_DATE_FROM, EFFECTIVE_DATE_TO," + " SYS_RRN, ECN_ID, FACILITY_RRN, DESCRIPTION," +
                    " OBJ_SPACE," + " OBJ_TYPE, OBJ_SUBTYPE, OBJ_STATUS, ?,? " + " FROM ECN " + " WHERE ECN_RRN = ? ";
            Object[] args = new Object[]{transRrn, transSequence, transId, LocalContext.getUserRrn(), transTime, ecnRrn};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void insertEcnHistoryComment(Ecn ecn, long transRrn) {
            String sql = "INSERT INTO " + DataBaseNames.OBJECT_COMMENT + " (trans_rrn,instance_rrn,comments )" + " VALUES" +
                    " (" + transRrn + "," + ecn.getInstanceRrn() + "," +
                    StringUtils.wrapWithApostropheIfMissing(ecn.getComments()) + ")";

            jdbcTemplate.execute(sql);
        }

        @Override
        public Ecn getEcn(long ecnRrn) {
            String sql = "SELECT INSTANCE_ID,INSTANCE_RRN,INSTANCE_DESC,OBJECT_TYPE,OBJECT_SUBTYPE," +
                    "CREATED_TIMESTAMP,REQUEST_BY,ECN_STATUS,APPROVAL_WFL_RRN ,COMMENTS," + "ECN.EFFECTIVE_DATE_FROM,ECN" +
                    ".EFFECTIVE_DATE_TO FROM " + DataBaseNames.NAMEDOBJECT + "," + DataBaseNames.ECN + " WHERE " +
                    "NAMED_OBJECT.INSTANCE_RRN=ECN.ECN_RRN AND ECN.ECN_RRN = ?";
            return jdbcTemplate.queryForObjectWithNull(sql, new EcnMapper(), ecnRrn);
        }

        @Override
        public List<Ecn> getActiveEcnList() {
            String sql = "SELECT INSTANCE_ID,INSTANCE_RRN,INSTANCE_DESC,OBJECT_TYPE,OBJECT_SUBTYPE," +
                    "CREATED_TIMESTAMP,TRANSLATE_RRN(REQUEST_BY) REQUEST_BY_ID,REQUEST_BY,ECN_STATUS," +
                    "APPROVAL_WFL_RRN ,COMMENTS,EFFECTIVE_DATE_FROM,EFFECTIVE_DATE_TO FROM " + DataBaseNames.NAMEDOBJECT +
                    "," + DataBaseNames.ECN + " WHERE NAMED_OBJECT.INSTANCE_RRN=ECN.ECN_RRN" + " AND ECN_STATUS='ACTIVE'";
            return jdbcTemplate.query(sql, new EcnMapper());
        }

        @Override
        public List<ObjectVersion> getEcnObjectVersions(long ecnRrn) {
            String sql = "SELECT A.INSTANCE_RRN,A.INSTANCE_ID,A.INSTANCE_DESC,A.OBJECT," + "B.INSTANCE_VERSION,B" +
                    ".VERSION_ID,B.VERSION_DESC" + " FROM " + DataBaseNames.NAMEDOBJECT + " A," +
                    DataBaseNames.OBJECTVERSION + " B " + " WHERE ECN_RRN = ?" + " AND A.INSTANCE_RRN = B.INSTANCE_RRN" +
                    " ORDER BY INSTANCE_ID";

            return jdbcTemplate.query(sql, new Object[]{ecnRrn}, (RowMapper<ObjectVersion>) (rs, rowNum) -> {
                ObjectVersion objectVersion = new ObjectVersion();
                int i = 1;
                objectVersion.setInstanceRrn(rs.getLong(i++));
                objectVersion.setInstanceId(rs.getString(i++));
                objectVersion.setInstanceDesc(rs.getString(i++));
                objectVersion.setObject(rs.getString(i++));
                objectVersion.setInstanceVersion(rs.getInt(i++));
                objectVersion.setVersionId(rs.getString(i++));
                objectVersion.setVersionDesc(rs.getString(i++));
                return objectVersion;
            });
        }

        @Override
        public List<ContextValue> getEcnContextValues(long ecnRrn) {
            String sql = " SELECT d.instance_id,sequence_number,status,effective_date_from,effective_date_to,ecn_rrn," +
                    " context_key1, context_key2, context_key3, context_key4, context_key5, " + " context_key6, " +
                    "context_key7, context_key8, context_key9, " + " context_key10,context_key11,context_key12," +
                    "context_key13,context_key14,context_key15," + " result_value1,result_value2,result_value3," + " " +
                    "result_value4,result_value5,result_value6, result_value7," + " result_value8,result_value9," +
                    "result_value10 " + " FROM " + DataBaseNames.CONTEXT_VALUE + " C," + DataBaseNames.NAMEDOBJECT + " D " +
                    " WHERE ecn_rrn = ?" + " and  C.CONTEXT_RRN=D.INSTANCE_RRN";

            return jdbcTemplate.query(sql, new Object[]{ecnRrn}, new RowMapper<ContextValue>() {
                int i = 0;

                @Override
                public ContextValue mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ContextValue contextValue = new ContextValue();

                    contextValue.setStatus(rs.getString("status"));
                    contextValue.setSequenceNumber(rs.getInt("sequence_number"));

                    if (rs.getDate("effective_date_from") != null) {
                        contextValue.setEffectiveDateFrom(rs.getDate("effective_date_from").toString());
                    }

                    contextValue.setEcnRrn(new Long(rs.getLong("ecn_rrn")));

                    if (rs.getDate("effective_date_to") != null) {
                        contextValue.setEffectiveDateTo(rs.getDate("effective_date_to").toString());
                    }

                    contextValue.setContextId(rs.getString("instance_id"));
                    contextValue.setContextKey1(rs.getString("context_key1"));
                    contextValue.setContextKey2(rs.getString("context_key2"));
                    contextValue.setContextKey3(rs.getString("context_key3"));
                    contextValue.setContextKey4(rs.getString("context_key4"));
                    contextValue.setContextKey5(rs.getString("context_key5"));
                    contextValue.setContextKey6(rs.getString("context_key6"));
                    contextValue.setContextKey7(rs.getString("context_key7"));
                    contextValue.setContextKey8(rs.getString("context_key8"));
                    contextValue.setContextKey9(rs.getString("context_key9"));
                    contextValue.setContextKey10(rs.getString("context_key10"));
                    contextValue.setContextKey11(rs.getString("context_key11"));
                    contextValue.setContextKey12(rs.getString("context_key12"));
                    contextValue.setContextKey13(rs.getString("context_key13"));
                    contextValue.setContextKey14(rs.getString("context_key14"));
                    contextValue.setContextKey15(rs.getString("context_key15"));
                    contextValue.setResultValue1(rs.getString("result_value1"));
                    contextValue.setResultValue2(rs.getString("result_value2"));
                    contextValue.setResultValue3(rs.getString("result_value3"));
                    contextValue.setResultValue4(rs.getString("result_value4"));
                    contextValue.setResultValue5(rs.getString("result_value5"));
                    contextValue.setResultValue6(rs.getString("result_value6"));
                    contextValue.setResultValue7(rs.getString("result_value7"));
                    contextValue.setResultValue8(rs.getString("result_value8"));
                    contextValue.setResultValue9(rs.getString("result_value9"));
                    contextValue.setResultValue10(rs.getString("result_value10"));
                    contextValue.setSequence(i++);
                    return contextValue;
                }
            });
        }

        @Override
        public void updateEcnEffectiveDate(String fromDate, String toDate, long ecnRrn) {
            String sql =
                    "UPDATE " + DataBaseNames.ECN + " SET EFFECTIVE_DATE_FROM = to_date(?,'" + DateUtils.DATE_FORMAT24 +
                            "'), EFFECTIVE_DATE_TO = to_date(?,'" + DateUtils.DATE_FORMAT24 + "'), UPDATE_TIME = ?, " +
                            "UPDATE_USER_RRN = ? WHERE ECN_RRN = ? ";
            jdbcTemplate.update(sql, fromDate, toDate, new Timestamp(System.currentTimeMillis()), LocalContext.getUserRrn(),
                                ecnRrn);
        }

        @Override
        public void updateEcnStatus(String status, String comments, long ecnRrn) {
            String sql = "UPDATE " + DataBaseNames.ECN + " SET ECN_STATUS = ?, COMMENTS = ?, UPDATE_TIME = ?, " +
                    "UPDATE_USER_RRN = ? " + " WHERE ECN_RRN = ? ";
            jdbcTemplate.update(sql, status, comments, new Timestamp(System.currentTimeMillis()), LocalContext.getUserRrn(),
                                ecnRrn);
        }

        @Override
        public void updateStatusForContext(String status, long ecnRrn) {
            String sql = "UPDATE " + DataBaseNames.CONTEXT_VALUE + " SET STATUS = ? WHERE ECN_RRN = ? ";
            jdbcTemplate.update(sql, status, ecnRrn);
        }

        @Override
        public void updateStatusForObjectVersion(String status, long ecnRrn) {
            String sql = "UPDATE " + DataBaseNames.OBJECTVERSION + " SET VERSION_STATUS = ? WHERE ECN_RRN = ? ";
            jdbcTemplate.update(sql, status, ecnRrn);
        }

        @Override
        public long getEcnSignOffRrn(String ecnType, String refNamedSpace, String tNamedSpace) {
            String sql = "SELECT t.data_2_value FROM (SELECT N.NAMED_SPACE,N.INSTANCE_ID REF_FILE_ID," + "D.KEY_1_VALUE,D" +
                    ".KEY_2_VALUE,D.DATA_1_VALUE,D.DATA_2_VALUE,D.DATA_3_VALUE," + "D.DATA_4_VALUE,D.DATA_5_VALUE FROM " +
                    "NAMED_OBJECT N, REFERENCE_FILE_DETAIL D " + " WHERE N.INSTANCE_RRN = D.REFERENCE_FILE_RRN) t " + " " +
                    "WHERE t.ref_file_id = '$ECN_TYPE' " + " AND named_space='" + refNamedSpace + "'" + " AND upper" +
                    "(key_1_value)='" + ecnType.toUpperCase() + "'";

            long signOffRrn = 0;

            String _routineId = jdbcTemplate.queryForObjectWithNull(sql, String.class);

            sql = "select instance_rrn from named_object where instance_id='" + _routineId.trim().toUpperCase() +
                    "' and   " + "named_space='" + tNamedSpace + "'";

            return jdbcTemplate.queryForObjectWithNull(sql, long.class);
        }

        @Override
        public boolean isEcnControled(String object, String namedSpace) {
            String sql = "SELECT COUNT(*) FROM  NAMED_SPACE_DEFINITION " + " WHERE  substr(control_flag,3,1)='1'" + " AND" +
                    " object=? AND named_space=? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{object, namedSpace}, long.class) > 0;
        }

        @Override
        public void updateEcnForStatus(Ecn ecn) {
            String sql = "UPDATE " + DataBaseNames.ECN + " SET ECN_STATUS = ?, COMMENTS = ?, " + " EFFECTIVE_DATE_FROM = " +
                    "to_date(?, '" + DateUtils.DATE_FORMAT24 + "'), " + " EFFECTIVE_DATE_TO = to_date(?, '" +
                    DateUtils.DATE_FORMAT24 + "'), UPDATE_TIME = ?, UPDATE_USER_RRN = ? " + " WHERE ECN_RRN = ? ";
            Object[] args = new Object[]{ecn.getEcnStatus(), ecn.getComments(), ecn.getEffectiveDateFrom(),
                    ecn.getEffectiveDateTo(), new Timestamp(
                    System.currentTimeMillis()), LocalContext.getUserRrn(), ecn.getInstanceRrn()};
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void updateContextValueForEcn(Ecn ecn) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.CONTEXT_VALUE);
            sql.append(" SET STATUS = ?, ");
            sql.append(" EFFECTIVE_DATE_FROM = to_date(?, '" + DateUtils.DATE_FORMAT24 + "'), ");
            sql.append(" EFFECTIVE_DATE_TO = to_date(?, '" + DateUtils.DATE_FORMAT24 + "') ");
            sql.append(" WHERE ECN_RRN = ? ");

            Object[] args = new Object[]{ecn.getEcnStatus(), ecn.getEffectiveDateFrom(), ecn.getEffectiveDateTo(),
                    ecn.getInstanceRrn()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public void updateObjectVersionForEcn(Ecn ecn) {
            StringBuilder sql = new StringBuilder("UPDATE ");
            sql.append(DataBaseNames.OBJECTVERSION);
            sql.append(" SET VERSION_STATUS = ?, ");
            sql.append(" EFFECTIVE_DATE_FROM = to_date(?, '" + DateUtils.DATE_FORMAT24 + "'), ");
            sql.append(" EFFECTIVE_DATE_TO = to_date(?, '" + DateUtils.DATE_FORMAT24 + "') ");
            sql.append(" WHERE ECN_RRN = ? ");

            Object[] args = new Object[]{ecn.getEcnStatus(), ecn.getEffectiveDateFrom(), ecn.getEffectiveDateTo(),
                    ecn.getInstanceRrn()};

            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public String getMaxEcnIdFromHistory(String ecnIdPattern) {
            String sql = "SELECT " + " MAX(no.INSTANCE_ID) AS MAXID " + " FROM (SELECT DISTINCT INSTANCE_RRN, " +
                    "INSTANCE_ID, OBJECT FROM NAMED_OBJECT_H) no " + " WHERE no.OBJECT = 'ECN' AND no.INSTANCE_ID LIKE ? ";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{ecnIdPattern}, String.class);
        }

        @Override
        public void updateRunCardStatus(Long runCardRrn, Timestamp effectiveDateFrom, Timestamp effectiveDateTo,
                                        String status) {
            String sql = "UPDATE " + DataBaseNames.ECN + " SET ECN_STATUS = ?, EFFECTIVE_DATE_FROM = ?, EFFECTIVE_DATE_TO" +
                    " = ? " + ", UPDATE_TIME = ?, UPDATE_USER_RRN = ?  WHERE ECN_RRN = ? ";
            jdbcTemplate.update(sql, status, effectiveDateFrom, effectiveDateTo, new Timestamp(System.currentTimeMillis()),
                                LocalContext.getUserRrn(), runCardRrn);
        }

        @Override
        public Page getEcnHistoryPage(int pageNo, int pageSize, Long ecnRrn) {
            Page page = new Page(pageNo, pageSize);
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" eh.TRANS_RRN, eh.TRANS_SEQUENCE, tl.TRANS_ID, tl.TRANS_START_TIMESTAMP, tl" +
                               ".TRANS_END_TIMESTAMP, tl.TRANS_PERFORMED_BY, ");
            sql.append(
                    " tl.COMMENTS AS TRANS_COMMENTS, eh.ECN_RRN, eh.REQUEST_BY, eh.APPROVAL_WFL_RRN, eh" + ".COMMENTS, ");
            sql.append(" eh.ECN_STATUS, eh.EFFECTIVE_DATE_FROM, eh.EFFECTIVE_DATE_TO ");
            sql.append(" FROM ECN_H eh, TRANSACTION_LOG tl ");
            sql.append(" WHERE eh.TRANS_RRN = tl.TRANS_RRN ");
            sql.append(" AND eh.ECN_RRN = ? ");
            sql.append(" ORDER BY tl.TRANS_START_TIMESTAMP DESC, eh.TRANS_SEQUENCE DESC ");
            return jdbcTemplate
                    .queryForPage(page, sql.toString(), new Object[]{ecnRrn}, new RowMapper<Map<String, Object>>() {
                        Map<String, Object> map;

                        @Override
                        public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                            map = new HashMap<>();

                            map.put("transRrn", rs.getLong("TRANS_RRN"));
                            map.put("transSequence", rs.getInt("TRANS_SEQUENCE"));
                            map.put("transId", rs.getString("TRANS_ID"));
                            map.put("transStartTimestamp", DateUtils.formatDate(rs.getTimestamp("TRANS_START_TIMESTAMP")));
                            map.put("transEndTimestamp", DateUtils.formatDate(rs.getTimestamp("TRANS_END_TIMESTAMP")));
                            map.put("transPerformedBy", rs.getString("TRANS_PERFORMED_BY"));

                            map.put("ecnRrn", rs.getLong("ECN_RRN"));
                            map.put("requestBy", rs.getLong("REQUEST_BY"));
                            map.put("approvalWflRrn", rs.getLong("APPROVAL_WFL_RRN"));
                            map.put("comments", StringUtils.defaultString(rs.getString("COMMENTS")));
                            map.put("ecnStatus", rs.getString("ECN_STATUS"));
                            map.put("effectiveDateFrom", DateUtils.formatDate(rs.getTimestamp("EFFECTIVE_DATE_FROM")));
                            map.put("effectiveDateTo", DateUtils.formatDate(rs.getTimestamp("EFFECTIVE_DATE_TO")));

                            map.put("transComments", StringUtils.defaultString(rs.getString("TRANS_COMMENTS")));

                            return map;
                        }
                    });
        }

    }