# EdcQueryDAOImpl.java

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

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.wip.dao.EdcQueryDAO;
    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 Johnson.Wang
     * @version 6.0.0
     * @date 2019/9/27
     **/
    @Repository
    public class EdcQueryDAOImpl implements EdcQueryDAO {

        private final JdbcTemplate jdbcTemplate;

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

        @Override
        public double getParamValByCondition(long lotRrn, long operationRrn, long productRrn, long processRrn,
                                             long parameterRrn, String puType) {
            double paramVal = 0;
            List<Object> args = new ArrayList<>();

            String sql = "select avg(r.data_value) from raw_data r," + "(select * from (select  dcol_rrn, dcol_sequence, " +
                    "number_of_samples from " + "data_collection c" + " where c.dcol_rrn in (select dcol_rrn from " +
                    "dcol_step_info" + " where 1=1 ";
            if (lotRrn > 0) {
                sql = sql + " and lot_rrn = ?";
                args.add(lotRrn);
            }
            if (operationRrn > 0) {
                sql = sql + " and manufacturing_oper_rrn = ?";
                args.add(operationRrn);
            }
            if (productRrn > 0) {
                sql = sql + "  and product_rrn = ?";
                args.add(productRrn);
            }
            if (processRrn > 0) {
                sql = sql + "  and process_rrn = ?";
                args.add(processRrn);
            }
            if (StringUtils.isNotBlank(puType)) {
                sql = sql + "  and manufacturing_eqpt_rrn in (select eqpt_rrn from entity_ext where " + "equipment_owner=" +
                        " ?)";
                args.add(puType);
            }
            if (parameterRrn > 0) {
                sql = sql + " and c.parameter_rrn = ?";
                args.add(parameterRrn);
            }
            sql = sql + ") order by c.collection_timestamp desc ) t1 where t1.rownum = 1) e where r.dcol_rrn = e" +
                    ".dcol_rrn " + "and r.dcol_sequence = e.dcol_sequence and r.sample_sequence = e.number_of_samples";

            return jdbcTemplate.queryForObjectWithNull(sql, args.toArray(), double.class);
        }

        @Override
        public List<Map<String, Object>> getAllDataCollectionPointInfoByDcolRrn(Long dcolRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" dc.dcol_rrn, dc.dcol_sequence, dc.parameter_set_rrn, dc.parameter_set_version, ");
            sql.append(" dc.parameter_rrn, dc.number_of_samples, sd.sample_sequence, ");
            sql.append(" sd.unit_rrn, sd.sample_id, rd.reading_sequence, rd.reading_id, rd.data_value ");
            sql.append(" FROM data_collection dc, sample_data sd, raw_data rd ");
            sql.append(" WHERE 1 = 1 ");
            sql.append(" AND dc.dcol_rrn = sd.dcol_rrn AND dc.dcol_sequence = sd.dcol_sequence ");
            sql.append(" AND sd.dcol_rrn = rd.dcol_rrn AND sd.dcol_sequence = rd.dcol_sequence ");
            sql.append(" AND sd.sample_sequence = rd.sample_sequence ");
            sql.append(" AND dc.dcol_rrn = ? ");

            sql.append(" ORDER BY dc.dcol_sequence, sd.sample_sequence, rd.reading_sequence ");

            return jdbcTemplate.query(sql.toString(), new Object[]{dcolRrn}, new RowMapper<Map<String, Object>>() {
                private Map<String, Object> map;

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    map = new HashMap<>();
                    map.put("dcolRrn", rs.getLong("dcol_rrn"));
                    map.put("dcolSequence", rs.getInt("dcol_sequence"));
                    map.put("parameterSetRrn", rs.getLong("parameter_set_rrn"));
                    map.put("parameterSetVersion", rs.getInt("parameter_set_version"));
                    map.put("parameterRrn", rs.getLong("parameter_rrn"));
                    map.put("numberOfSamples", rs.getInt("number_of_samples"));
                    map.put("sampleSequence", rs.getInt("sample_sequence"));
                    map.put("unitRrn", rs.getLong("unit_rrn"));
                    map.put("sampleId", rs.getString("sample_id"));
                    map.put("readingSequence", rs.getInt("reading_sequence"));
                    map.put("readingId", rs.getString("reading_id"));
                    map.put("dataValue", rs.getDouble("data_value"));
                    return map;
                }
            });
        }

    }