# ResistMappingDAOImpl.java

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

    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.collections.MapUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.resist.dao.ResistMappingDAO;
    import com.mycim.server.resist.dao.mapper.ResistMappingRowMapper;
    import com.mycim.valueobject.ems.ResistMapping;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author yanbing.chen
     * @version 6.0.0
     * @date 2019/10/29
     **/
    @Repository
    public class ResistMappingDAOImpl implements ResistMappingDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public ResistMapping getResistMappingByResistNo(String resistNo) {

            StringBuilder sb = new StringBuilder(" SELECT ");

            sb.append(" RESIST_NO,RESIST_NAME,MATERIAL_NO,LIFETIME_TYPE,DEFROST_TIME,");
            sb.append(" BUFFER_DAYS,CREATE_BY,MODIFY_BY,CREATE_TIME,MODIFY_TIME ");
            sb.append(" FROM RESIST_MAPPING ");
            sb.append("  WHERE RESIST_NO = ? ");

            return jdbcTemplate.queryForObjectWithNull(sb.toString(), new Object[]{resistNo}, new ResistMappingRowMapper());
        }

        @Override
        public Page qryResistMappingByPage(Page page, Map<String, String> argMap) {

            String resistNo = MapUtils.getString(argMap, "resistNo");
            String resistName = MapUtils.getString(argMap, "resistName");
            String materialNo = MapUtils.getString(argMap, "materialNo");

            List<String> args = new ArrayList<>();

            StringBuilder sb = new StringBuilder(" SELECT ");

            sb.append(" RESIST_NO,RESIST_NAME,MATERIAL_NO,LIFETIME_TYPE,DEFROST_TIME,");
            sb.append(" BUFFER_DAYS,CREATE_BY,MODIFY_BY,CREATE_TIME,MODIFY_TIME ");
            sb.append(" FROM RESIST_MAPPING ");
            sb.append(" WHERE 1 = 1 ");

            if (StringUtils.isNotBlank(resistNo)) {
                sb.append("  AND RESIST_NO LIKE ? ");
                args.add(StringUtils.replace(resistNo, "*", "%").toString());

            }
            if (StringUtils.isNotBlank(resistName)) {
                sb.append("  AND RESIST_NAME LIKE ? ");
                args.add(StringUtils.replace(resistName, "*", "%").toString());

            }

            if (StringUtils.isNotBlank(materialNo)) {
                sb.append(" AND MATERIAL_NO LIKE ? ");
                args.add(StringUtils.replace(materialNo, "*", "%").toString());

            }
            return jdbcTemplate.queryForPage(page, sb.toString(), args.toArray(), new ResistMappingRowMapper());
        }

        @Override
        public void addResistMapping(ResistMapping resistMapping) {
            String sql = "INSERT INTO RESIST_MAPPING (RESIST_NO, MATERIAL_NO, RESIST_NAME," + " LIFETIME_TYPE, " +
                    "DEFROST_TIME,DEFROST_TIME_SEC,BUFFER_DAYS, CREATE_BY, MODIFY_BY, " + " CREATE_TIME, MODIFY_TIME)" +
                    "VALUES (?,?,?, ?, ?, ?, ?, ?, ?,sysdate,sysdate)";

            jdbcTemplate.update(sql,
                                new Object[]{resistMapping.getResistNo(), resistMapping.getMaterialNo(),
                                        resistMapping.getResistName(), resistMapping.getLifeTimeType(),
                                        resistMapping.getDefrostTime(), resistMapping.getDefrosTimsec(),
                                        resistMapping.getBufferDays(), resistMapping.getCreateBy(),
                                        resistMapping.getModifyBy()});
        }

        @Override
        public void addResistMappingH(Long transRrn, Integer transSequence, String resistNo) {
            String sql = "INSERT INTO RESIST_MAPPING_H (TRANS_RRN,TRANS_SEQUENCE,RESIST_NO," + " MATERIAL_NO, " +
                    "RESIST_NAME, LIFETIME_TYPE, DEFROST_TIME,DEFROST_TIME_SEC,BUFFER_DAYS, " + " CREATE_BY, MODIFY_BY, " +
                    "CREATE_TIME, MODIFY_TIME)" + "SELECT ?, ?,RESIST_NO, MATERIAL_NO, RESIST_NAME, LIFETIME_TYPE, " +
                    "DEFROST_TIME,DEFROST_TIME_SEC,BUFFER_DAYS, CREATE_BY, MODIFY_BY," + " CREATE_TIME, MODIFY_TIME FROM " +
                    "RESIST_MAPPING WHERE RESIST_NO =? ";

            jdbcTemplate.update(sql, new Object[]{transRrn, transSequence, resistNo});
        }

        @Override
        public void addResistMappingH(ResistMapping resistMapping) {
            String sql = "INSERT INTO RESIST_MAPPING_H (TRANS_RRN,TRANS_SEQUENCE,RESIST_NO, MATERIAL_NO, RESIST_NAME," +
                    " LIFETIME_TYPE, DEFROST_TIME,DEFROST_TIME_SEC,BUFFER_DAYS, CREATE_BY, MODIFY_BY, " + " CREATE_TIME, " +
                    "MODIFY_TIME)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,to_timestamp(?,'" + DateUtils.DATE_FORMAT24 +
                    "'),to_timestamp(?,'" + DateUtils.DATE_FORMAT24 + "'))";

            jdbcTemplate.update(sql,
                                new Object[]{resistMapping.getTransRrn(), resistMapping.getTransSequence(),
                                        resistMapping.getResistNo(), resistMapping.getMaterialNo(),
                                        resistMapping.getResistName(), resistMapping.getLifeTimeType(),
                                        resistMapping.getDefrostTime(), resistMapping.getDefrosTimsec(),
                                        resistMapping.getBufferDays(), resistMapping.getCreateBy(),
                                        resistMapping.getModifyBy(), DateUtils.formatDate(
                                        resistMapping.getCreateTime(), DateUtils.DATE_FORMAT4DATE), DateUtils.formatDate(
                                        resistMapping.getModifyTime(), DateUtils.DATE_FORMAT4DATE)});
        }

        @Override
        public void modifyResistMapping(ResistMapping resistMapping) {
            String sql = "UPDATE RESIST_MAPPING SET" + " MATERIAL_NO = ?, RESIST_NAME = ?, LIFETIME_TYPE = ?, " +
                    "DEFROST_TIME = ?," + "DEFROST_TIME_SEC = ?, BUFFER_DAYS = ?, " + " MODIFY_BY = ?, MODIFY_TIME = " +
                    "sysdate WHERE RESIST_NO = ?";

            jdbcTemplate.update(sql,
                                new Object[]{resistMapping.getMaterialNo(), resistMapping.getResistName(),
                                        resistMapping.getLifeTimeType(), resistMapping.getDefrostTime(),
                                        resistMapping.getDefrosTimsec(), resistMapping.getBufferDays(),
                                        resistMapping.getModifyBy(), resistMapping.getResistNo()});
        }

        @Override
        public void deleteResistMapping(String resistNo) {
            String sql = "DELETE FROM RESIST_MAPPING WHERE RESIST_NO = ?";
            jdbcTemplate.update(sql, resistNo);
        }

        @Override
        public Page qryResistMappingHistByPage(Page page, Map<String, String> argMap) {
            String resistNo = MapUtils.getString(argMap, "resistNo");
            String resistName = MapUtils.getString(argMap, "resistName");
            String materialNo = MapUtils.getString(argMap, "materialNo");
            String transPerformedBy = MapUtils.getString(argMap, "transPerformedBy");
            String startDate = MapUtils.getString(argMap, "startDate");
            String endDate = MapUtils.getString(argMap, "endDate");

            List<String> args = new ArrayList<String>();

            StringBuilder sb = new StringBuilder(" SELECT ");

            sb.append(" RESIST_NO,RESIST_NAME,MATERIAL_NO,LIFETIME_TYPE,DEFROST_TIME,");
            sb.append(" BUFFER_DAYS,CREATE_BY,MODIFY_BY,CREATE_TIME,MODIFY_TIME, ");
            sb.append(" TR.TRANS_ID,TR.TRANS_START_TIMESTAMP,TR.TRANS_PERFORMED_BY ");
            sb.append(" FROM RESIST_MAPPING_H RPH,TRANSACTION_LOG TR ");
            sb.append(" WHERE RPH.TRANS_RRN=TR.TRANS_RRN ");

            if (StringUtils.isNotBlank(resistNo)) {
                sb.append(" AND RPH.RESIST_NO LIKE ?");
                args.add(StringUtils.replace(resistNo, "*", "%").toString());
            }

            if (StringUtils.isNotBlank(resistName)) {
                sb.append(" AND RPH.RESIST_NAME LIKE ?");
                args.add(StringUtils.replace(resistName, "*", "%").toString());

            }

            if (StringUtils.isNotBlank(materialNo)) {
                sb.append(" AND RPH.MATERIAL_NO LIKE ? ");
                args.add(StringUtils.replace(materialNo, "*", "%").toString());
            }

            if (StringUtils.isNotBlank(transPerformedBy)) {
                sb.append(" AND TR.TRANS_PERFORMED_BY LIKE ? ");
                args.add(StringUtils.replace(transPerformedBy, "*", "%").toString());

            }
            if (StringUtils.isNotBlank(startDate)) {
                sb.append(" AND TR.TRANS_START_TIMESTAMP > to_timestamp (?,'" + DateUtils.DATE_FORMAT24 + "')");
                args.add(startDate + " 00:00:00");
            }

            if (StringUtils.isNotBlank(endDate)) {
                sb.append(" AND TR.TRANS_START_TIMESTAMP <= to_timestamp (?,'" + DateUtils.DATE_FORMAT24 + "')");
                args.add(endDate + " 23:59:59");
            }

            sb.append(" ORDER BY TR.TRANS_START_TIMESTAMP DESC, RPH.TRANS_SEQUENCE DESC ");

            return jdbcTemplate.queryForPage(page, sb.toString(), args.toArray(), new RowMapper<Map>() {

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

                    resistMappingHist.put("resistNo", rs.getString("RESIST_NO"));
                    resistMappingHist.put("resistName", rs.getString("RESIST_NAME"));
                    resistMappingHist.put("materialNo", rs.getString("MATERIAL_NO"));
                    resistMappingHist.put("lifetimeType", rs.getString("LIFETIME_TYPE"));
                    resistMappingHist.put("defrostTime", rs.getString("DEFROST_TIME"));
                    resistMappingHist.put("bufferDays", rs.getString("BUFFER_DAYS"));
                    resistMappingHist.put("transStartTimestamp", rs.getString("TRANS_START_TIMESTAMP"));
                    resistMappingHist.put("transPerformedBy", rs.getString("trans_performed_by"));
                    resistMappingHist.put("transId", rs.getString("TRANS_ID"));
                    return resistMappingHist;
                }
            });
        }

    }