# ProcessSpecInfoDaoImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.server.spec.dao.ProcessSpecInfoDao;
    import com.mycim.server.spec.dao.mapper.ProcessSpecInfoRowMapper;
    import com.mycim.valueobject.prp.ProcessSpecInfo;
    import com.mycim.valueobject.prp.ProcessVersion;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;

    /**
     * @author Qiansheng.Wang
     * @since 2020-09-08
     */
    @Repository
    public class ProcessSpecInfoDaoImpl implements ProcessSpecInfoDao {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public int insertProcessSpecInfo(ProcessSpecInfo processSpecInfo) {
            StringBuilder sql = new StringBuilder("insert into ");

            sql.append(" PROCESS_SPEC_ITEM_INFO ");
            sql.append(" (PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, ");
            sql.append(" CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" VERSION_LAST_UPDATED_TIME) ");
            sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");

            return jdbcTemplate
                    .update(sql.toString(), processSpecInfo.getProcessRrn(), processSpecInfo.getProcessId(),
                            processSpecInfo.getProcessVersion(), processSpecInfo.getActiveFlag(),
                            processSpecInfo.getActiveTime(), processSpecInfo.getActiveUser(),
                            processSpecInfo.getCurrentStatus(), processSpecInfo.getLastUpdatedUser(),
                            processSpecInfo.getLastUpdatedTime(), processSpecInfo.getCreateUserId(),
                            processSpecInfo.getCreateTime(), processSpecInfo.getVersionLastUpdateTime());
        }

        @Override
        public int updateProcessSpecInfo(ProcessSpecInfo processSpecInfo) {
            StringBuilder sql = new StringBuilder("update ");

            sql.append(" PROCESS_SPEC_ITEM_INFO ");
            sql.append(" set ACTIVE_FLAG = ?, ACTIVE_TIME = ?, ACTIVE_USER = ?, VERSION_LAST_UPDATED_TIME = ?, ");
            sql.append(" CURRENT_STATUS = ?, LAST_UPDATED_USER = ?, LAST_UPDATED_TIME = ? ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? ");

            return jdbcTemplate
                    .update(sql.toString(), processSpecInfo.getActiveFlag(), processSpecInfo.getActiveTime(),
                            processSpecInfo.getActiveUser(), processSpecInfo.getVersionLastUpdateTime(),
                            processSpecInfo.getCurrentStatus(), processSpecInfo.getLastUpdatedUser(),
                            processSpecInfo.getLastUpdatedTime(), processSpecInfo.getProcessRrn(),
                            processSpecInfo.getProcessVersion());
        }

        @Override
        public ProcessSpecInfo getProcessSpecInfoByPrimaryKey(Long processRrn, Integer processVersion) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, ");
            sql.append(" CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" VERSION_LAST_UPDATED_TIME ");
            sql.append(" from PROCESS_SPEC_ITEM_INFO ");
            sql.append(" where PROCESS_RRN = ? and PROCESS_VERSION = ? ");

            return jdbcTemplate
                    .queryForObjectWithNull(sql.toString(), new ProcessSpecInfoRowMapper(), processRrn, processVersion);
        }

        @Override
        public List<ProcessSpecInfo> getProcessSpecInfos(Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PROCESS_RRN, PROCESS_ID, PROCESS_VERSION, ACTIVE_FLAG, ACTIVE_TIME, ACTIVE_USER, ");
            sql.append(" CURRENT_STATUS, LAST_UPDATED_USER, LAST_UPDATED_TIME, CREATE_USER_ID, CREATE_TIME, ");
            sql.append(" VERSION_LAST_UPDATED_TIME ");
            sql.append(" from PROCESS_SPEC_ITEM_INFO ");
            sql.append(" where PROCESS_RRN = ? ");
            sql.append(" order by PROCESS_VERSION desc ");

            return jdbcTemplate.query(sql.toString(), new ProcessSpecInfoRowMapper(), processRrn);
        }

        @Override
        public List<Integer> getAllActivatedProcessVersions(Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PROCESS_VERSION ");
            sql.append(" from PROCESS_SPEC_ITEM_INFO ");
            sql.append(" where PROCESS_RRN = ? and ACTIVE_FLAG = ? ");

            sql.append(" order by PROCESS_VERSION desc ");

            return jdbcTemplate.query(sql.toString(), Integer.class, processRrn, true);
        }

        @Override
        public List<Integer> getAllProcessVersions(Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" PROCESS_VERSION ");
            sql.append(" from PROCESS_SPEC_ITEM_INFO ");
            sql.append(" where PROCESS_RRN = ? ");

            sql.append(" order by PROCESS_VERSION desc ");

            return jdbcTemplate.query(sql.toString(), Integer.class, processRrn);
        }

        @Override
        public Integer getHighestActivatedProcessVersion(Long processRrn) {
            StringBuilder sql = new StringBuilder("select ");

            sql.append(" max(PROCESS_VERSION) ");
            sql.append(" from PROCESS_SPEC_ITEM_INFO ");
            sql.append(" where PROCESS_RRN = ? and ACTIVE_FLAG = ? ");

            return jdbcTemplate.queryForObject(sql.toString(), new Object[]{processRrn, true}, Integer.class);
        }

        @Override
        public boolean hasActiveProcessSpec(long processRrn, Integer processVersion) {
            String sql = "SELECT COUNT(*) FROM PROCESS_SPEC_ITEM_INFO WHERE PROCESS_RRN=? " +
                    " AND  PROCESS_VERSION=? AND CURRENT_STATUS = 'ACTIVE'";
            Integer count = jdbcTemplate
                    .queryForObjectWithNull(sql, new Object[]{processRrn, processVersion}, Integer.class);
            return count != null && count > 0;
        }

    }