# ResistEqptMappingDAOImpl.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.time.DateUtils;
    import com.mycim.server.resist.dao.ResistEqptMappingDAO;
    import com.mycim.server.resist.dao.mapper.ResistEqptMappingRowMapper;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.ems.Resist;
    import com.mycim.valueobject.ems.ResistEqptMapping;
    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.List;

    /**
     * @author yanbing.chen
     * @version 6.0.0
     * @date 2019/10/31
     **/
    @Repository
    public class ResistEqptMappingDAOImpl implements ResistEqptMappingDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public Page qryResistEqptMappingByPage(ResistEqptMapping mapping, Page page) {
            StringBuffer sql = new StringBuffer("SELECT ");
            sql.append("R.RESIST_NO, R.EQPTID, R.PRPIPE, R.UNIT, R.CREATE_BY, R.CREATE_TIME, R.MODIFY_BY, R")
               .append(".MODIFY_TIME ,R.RESIST_EQPT_BARCODE ");
            sql.append("FROM ");
            sql.append(DataBaseNames.RESIST_EQPT_MAPPING);
            sql.append(" R ");
            sql.append("WHERE 1=1 ");

            List<Object> argList = new ArrayList<>();
            if (StringUtils.isNotBlank(mapping.getResistNo())) {
                sql.append("AND R.RESIST_NO LIKE ? ");
                argList.add(mapping.getResistNo());
            }
            if (StringUtils.isNotBlank(mapping.getEqptId())) {
                sql.append("AND R.EQPTID LIKE ? ");
                argList.add(mapping.getEqptId());
            }
            if (StringUtils.isNotBlank(mapping.getPrPipe())) {
                sql.append("AND R.PRPIPE LIKE ? ");
                argList.add(mapping.getPrPipe());
            }
            if (StringUtils.isNotBlank(mapping.getUnit())) {
                sql.append("AND R.UNIT LIKE ? ");
                argList.add(mapping.getUnit());
            }

            sql.append("ORDER BY R.CREATE_TIME DESC ");

            return jdbcTemplate.queryForPage(page, sql.toString(), argList.toArray(), new ResistEqptMappingRowMapper());
        }

        @Override
        public void addResistEqptMapping(ResistEqptMapping mapping) {
            StringBuffer sql = new StringBuffer("INSERT INTO ");
            sql.append(DataBaseNames.RESIST_EQPT_MAPPING);
            sql.append(" (RESIST_NO, EQPTID, PRPIPE, UNIT, CREATE_BY, MODIFY_BY, CREATE_TIME, MODIFY_TIME, RESIST_EQPT_BARCODE) ");
            sql.append("VALUES (?, ?, ?, ?, ?, ?, sysdate, sysdate,?) ");
            jdbcTemplate.update(sql.toString(),
                                new Object[]{mapping.getResistNo(), mapping.getEqptId(), mapping.getPrPipe(),
                                        mapping.getUnit(), mapping.getCreateBy(), mapping.getCreateBy(), mapping.getResistEqptBarcode()});
        }

        @Override
        public void addResistEqptMappingH(long transRrn, int transSequence, ResistEqptMapping mapping) {
            StringBuffer sql = new StringBuffer("INSERT INTO ");
            sql.append(DataBaseNames.RESIST_EQPT_MAPPING_H);
            sql.append(" (TRANS_RRN, TRANS_SEQUENCE, RESIST_NO, EQPTID, PRPIPE, UNIT, RESIST_EQPT_BARCODE) ");
            sql.append("VALUES (?, ?, ?, ?, ?, ?, ?) ");
            jdbcTemplate.update(sql.toString(),
                                new Object[]{transRrn, transSequence, mapping.getResistNo(), mapping.getEqptId(),
                                        mapping.getPrPipe(), mapping.getUnit(), mapping.getResistEqptBarcode()});
        }

        @Override
        public void deleteResistEqptMapping(ResistEqptMapping mapping) {
            StringBuilder sql = new StringBuilder("DELETE ");
            sql.append("FROM RESIST_EQPT_MAPPING R ");
            sql.append("WHERE R.EQPTID = ? AND R.PRPIPE = ? AND R.UNIT = ? AND R.RESIST_NO = ? ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{mapping.getEqptId(), mapping.getPrPipe(), mapping.getUnit(),
                                        mapping.getResistNo()});
        }

        @Override
        public void modifyResistEqptMapping(ResistEqptMapping mapping) {
            StringBuffer sql = new StringBuffer("UPDATE ");
            sql.append("RESIST_EQPT_MAPPING SET RESIST_NO = ?, MODIFY_BY = ?, MODIFY_TIME = sysdate ");
            sql.append("WHERE EQPTID = ? AND PRPIPE = ? AND UNIT = ? ");

            jdbcTemplate.update(sql.toString(),
                                new Object[]{mapping.getResistNo(), mapping.getModifyBy(), mapping.getEqptId(),
                                        mapping.getPrPipe(), mapping.getUnit()});
        }

        @Override
        public Page qryResistEqptMappingHByPage(ResistEqptMapping mapping, Page page) {
            StringBuffer sql = new StringBuffer("SELECT ");
            sql.append("T.TRANS_ID, H.RESIST_NO, H.EQPTID, H.PRPIPE, H.UNIT, H.RESIST_EQPT_BARCODE, T.TRANS_PERFORMED_BY, T" +
                               ".TRANS_START_TIMESTAMP ");
            sql.append("FROM RESIST_EQPT_MAPPING_H H, TRANSACTION_LOG T ");
            sql.append("WHERE H.TRANS_RRN = T.TRANS_RRN ");

            List<Object> argList = new ArrayList<Object>();
            if (StringUtils.isNotBlank(mapping.getResistNo())) {
                sql.append("AND H.RESIST_NO LIKE ? ");
                argList.add(mapping.getResistNo());
            }
            if (StringUtils.isNotBlank(mapping.getEqptId())) {
                sql.append("AND H.EQPTID LIKE ? ");
                argList.add(mapping.getEqptId());
            }
            if (StringUtils.isNotBlank(mapping.getPrPipe())) {
                sql.append("AND H.PRPIPE LIKE ? ");
                argList.add(mapping.getPrPipe());
            }
            if (StringUtils.isNotBlank(mapping.getUnit())) {
                sql.append("AND H.UNIT LIKE ? ");
                argList.add(mapping.getUnit());
            }
            if (StringUtils.isNotBlank(mapping.getTransPerformedBy())) {
                sql.append("AND T.TRANS_PERFORMED_BY LIKE ? ");
                argList.add(mapping.getTransPerformedBy());
            }
            if (StringUtils.isNotBlank(mapping.getTransStartTime())) {
                sql.append(" AND T.TRANS_START_TIMESTAMP > to_timestamp (?,'" + DateUtils.DATE_FORMAT24 + "')");
                argList.add(mapping.getTransStartTime() + " 00:00:00");
            }

            if (StringUtils.isNotBlank(mapping.getTransEndTime())) {
                sql.append(" AND T.TRANS_START_TIMESTAMP <= to_timestamp (?,'" + DateUtils.DATE_FORMAT24 + "')");
                argList.add(mapping.getTransEndTime() + " 23:59:59");
            }

            sql.append("ORDER BY T.TRANS_START_TIMESTAMP DESC, H.TRANS_SEQUENCE DESC ");

            return jdbcTemplate.queryForPage(page, sql.toString(), argList.toArray(), new RowMapper<ResistEqptMapping>() {

                @Override
                public ResistEqptMapping mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ResistEqptMapping mapping = new ResistEqptMapping();
                    mapping.setTransId(rs.getString("TRANS_ID"));
                    mapping.setResistNo(rs.getString("RESIST_NO"));
                    mapping.setEqptId(rs.getString("EQPTID"));
                    mapping.setPrPipe(rs.getString("PRPIPE"));
                    mapping.setUnit(rs.getString("UNIT"));
                    mapping.setCreateBy(rs.getString("TRANS_PERFORMED_BY"));
                    mapping.setCreateTime(
                            DateUtils.parse(rs.getString("TRANS_START_TIMESTAMP"), DateUtils.DATE_FORMAT4DATE));
                    mapping.setResistEqptBarcode(rs.getString("RESIST_EQPT_BARCODE"));
                    return mapping;
                }
            });
        }

        @Override
        public ResistEqptMapping getResistEqptMappingByResist(Resist resist) {
            StringBuilder sql = new StringBuilder(" SELECT ");
            sql.append("R.RESIST_NO, R.EQPTID, R.PRPIPE, R.UNIT, R.CREATE_BY, R.CREATE_TIME, R.MODIFY_BY, R");
            sql.append(".MODIFY_TIME ,R.RESIST_EQPT_BARCODE ");
            sql.append("FROM ");
            sql.append(DataBaseNames.RESIST_EQPT_MAPPING);
            sql.append(" R ");
            sql.append(" WHERE R.RESIST_NO = ? AND R.EQPTID = ? AND R.PRPIPE = ? AND R.UNIT = ?");
            List args = new ArrayList();
            args.add(resist.getResistNo());
            args.add(resist.getEqptId());
            args.add(resist.getPrPipe());
            args.add(resist.getUnit());
            return jdbcTemplate.queryForObject(sql.toString(), args.toArray(), new ResistEqptMappingRowMapper());

        }

    }