# EdcSpcDaoImpl.java

    package com.mycim.server.edcchart.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.CollectionUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.edcchart.dao.EdcSpcDAO;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.edcspc.*;
    import com.mycim.valueobject.edcspc.dto.nonrt.NonRTUploadDetailDTO;
    import com.mycim.valueobject.spc.RunCardRawDataInfo;
    import com.mycim.valueobject.wip.SRCLotSpecialStep;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author zhi.cai
     * @version 6.0.0
     * @date 2019/12/14
     **/
    @Repository
    public class EdcSpcDaoImpl implements EdcSpcDAO {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public void insertNonRTUploadHistory(List<NonRTUploadDataInfo> list, Long transRrn) {
            String sql = "INSERT INTO NON_RT_DATA_HISTORY (TRANS_RRN,FACILITY_RRN,EQPT_ID,EQPT_RRN,LOT_ID,LOT_RRN," +
                    "UNIT_ID,SPC_JOB_ID,NON_RT_KEY," + "RETEST_FLAG,SUBGROUP_SIZE_FLAG,AREA,PRO1,PRO2,PRO3,PRO4,PRO5," +
                    "DATA_VALUES,CREATE_USER_ID," + "CREATE_TIME,SPC_JOB_NAME,VIOLATION_INFO,VALUE_TYPE,TIME_DATA_VALUE," +
                    "FORMULA,FORMULA_PARA,FORMULA_TYPE,IMPORT_TYPE,UNIT_SHOW,COMMENTS,VALID_HIGH, VALID_LOW)" +
                    " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?,?,?,?)";
            List<Object[]> batchArgs = new ArrayList<Object[]>();
            for (int i = 0; i < list.size(); i++) {
                NonRTUploadDataInfo data = list.get(i);
                String dataValues = "";
                if (data.getValueList() != null) {
                    for (double value : data.getValueList()) {
                        String valueStr = "";
                        if (value - Math.floor(value) < 1e-10) {
                            valueStr = new Double(value).intValue() + "";
                        } else {
                            valueStr = value + "";
                        }
                        if (StringUtils.isBlank(valueStr)) {
                            valueStr = "0";
                        }
                        dataValues += valueStr + ",";
                    }
                    if (StringUtils.isNotBlank(dataValues)) {
                        dataValues = dataValues.substring(0, dataValues.length() - 1);
                    } else {
                        dataValues = "0";
                    }
                }
                Object[] args = new Object[]{transRrn, data.getFacilityRrn(), data.getEqptId(), data.getEqptRrn(),
                        data.getLotId(), data.getLotRrn(), data.getUnitId(), data.getJobId(), data.getNonRTKey(),
                        data.getRetestFlag(), data.getSubgroupSizeFlag(), data.getArea(), data.getPro1(), data.getPro2(),
                        data.getPro3(), data.getPro4(), data.getPro5(), dataValues, data.getCreateUser(),
                        data.getJobName(), data.getViolationInfo(), data.getValueType(), data.getTime(),
                        data.getFormula(), data.getFormulaPara(), data.getFormulaType(), data.getImportType(),
                        data.getUnitShow(), data.getComment(), data.getValidHigh(), data.getValidLow()};
                batchArgs.add(args);
            }
            jdbcTemplate.batchUpdate(sql, batchArgs);
        }

        @Override
        public List getActiveParameterSetVersion(long instanceRrn, int instanceVersion) {
            ArrayList parameterSetVersions = new ArrayList();
            // get the value object by object rrn
            String sql = "SELECT PARAMETER_SEQUENCE," + DataBaseNames.PARAMETER + ".PARAMETER_RRN AS PARAMETER_RRN," + " " +
                    "DATA_TYPE,FILE_COLLECTION_FLAG,UNIT_OF_MEASURE,PRECISION," + " DERIVED_FLAG,LOT_COLLECTION_FLAG," +
                    "VALIDATE_AGAINST," + " LIMIT_CHECKING_AGAINST,APPLY_CALCULATION_TO, PARAMETER_UNIT, " + " " +
                    "COLLECTION_LEVEL,INSTANCE_ID,INSTANCE_DESC," + " MEASURE_TYPE ,OPTIONAL_FLAG,TARGET_VALUE," + " " +
                    "CHECK_UPPER_LIMIT_FLAG,CHECK_LOWER_LIMIT_FLAG ," + " LOWER_WARNING_LIMIT,UPPER_WARNING_LIMIT," + " " +
                    "WARNING_ALARM_ID,LOWER_CONTROL_LIMIT ," + " UPPER_CONTROL_LIMIT,CONTROL_ALARM_ID," + " " +
                    "LOWER_SHUTDOWN_LIMIT,UPPER_SHUTDOWN_LIMIT ," + " SHUTDOWN_ALARM_ID,LOWER_SPECIFICATION_LIMIT," +
                    DataBaseNames.PARAMETER + ".CP_PARA," + DataBaseNames.PARAMETER + ".KEY_CHART_FLAG," +
                    " UPPER_SPECIFICATION_LIMIT,SPECIFICATION_ALARM_ID, RULE_TYPE " + " FROM " +
                    DataBaseNames.PARAMETER_SET_VERSION + "," + DataBaseNames.PARAMETER + "," + DataBaseNames.NAMEDOBJECT +
                    " WHERE PARAMETER_SET_RRN = ? and parameter_set_version = ? and " +
                    DataBaseNames.PARAMETER_SET_VERSION + ".parameter_rrn=" + " " + DataBaseNames.PARAMETER +
                    ".parameter_rrn" + " and " + DataBaseNames.PARAMETER + ".parameter_rrn = instance_rrn" +
                    " order by PARAMETER_SEQUENCE";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, new Object[]{instanceRrn, instanceVersion});
            int calDataTypeNumber = 0;
            // if parameterSetVersion table does exist in the value object
            // table, throw a GetterException
            // that means orphan or malformed data existed
            while (rs.next()) {
                // construct the value object
                Parameter parameter = new Parameter();
                ParameterSetVersion parameterSetVersion = new ParameterSetVersion();
                parameter.setInstanceId(rs.getString("INSTANCE_ID"));
                parameter.setInstanceDesc(rs.getString("INSTANCE_DESC"));
                parameter.setInstanceRrn(rs.getInt("PARAMETER_RRN"));
                parameter.setDataType(rs.getString("DATA_TYPE"));
                parameter.setFileCollectionFlag(rs.getString("FILE_COLLECTION_FLAG"));
                parameter.setUnitOfMeasure(rs.getString("UNIT_OF_MEASURE"));
                parameter.setPrecision(new Long(rs.getLong("PRECISION")));
                parameter.setDerivedFlag(rs.getString("DERIVED_FLAG"));
                parameter.setLotCollectionFlag(rs.getString("LOT_COLLECTION_FLAG"));
                parameter.setValidateAgainst(rs.getString("VALIDATE_AGAINST"));
                parameter.setLimitCheckingAgainst(rs.getString("LIMIT_CHECKING_AGAINST"));
                parameter.setApplyCalculationTo(rs.getString("APPLY_CALCULATION_TO"));
                parameter.setParameterUnit(rs.getString("PARAMETER_UNIT"));
                parameter.setCpPara(rs.getString("CP_PARA"));
                parameter.setKeyChartFlag(rs.getString("KEY_CHART_FLAG"));

                parameterSetVersion.setInstanceRrn(instanceRrn);
                parameterSetVersion.setInstanceVersion(instanceVersion);
                parameterSetVersion.setParameterRrn(parameter.getInstanceRrn());
                parameterSetVersion.setParameterId(rs.getString("INSTANCE_ID"));
                parameterSetVersion.setParameterSequence(rs.getInt("PARAMETER_SEQUENCE"));
                parameterSetVersion.setCollectionLevel(rs.getString("COLLECTION_LEVEL"));
                parameterSetVersion.setMeasureType(rs.getString("MEASURE_TYPE"));
                parameterSetVersion.setOptionalFlag(rs.getString("OPTIONAL_FLAG"));
                parameterSetVersion.setRuleType(rs.getString("RULE_TYPE"));

                rs.getDouble("TARGET_VALUE");
                parameterSetVersion.setTargetValue(rs.wasNull() ? null : (new Double(rs.getDouble("TARGET_VALUE"))));
                parameterSetVersion.setCheckUpperLimitFlag(rs.getString("CHECK_UPPER_LIMIT_FLAG"));
                parameterSetVersion.setCheckLowerLimitFlag(rs.getString("CHECK_LOWER_LIMIT_FLAG"));

                rs.getDouble("LOWER_WARNING_LIMIT");
                parameterSetVersion
                        .setLowerWarningLimit(rs.wasNull() ? null : (new Double(rs.getDouble("LOWER_WARNING_LIMIT"))));
                rs.getDouble("UPPER_WARNING_LIMIT");
                parameterSetVersion
                        .setUpperWarningLimit(rs.wasNull() ? null : (new Double(rs.getDouble("UPPER_WARNING_LIMIT"))));
                parameterSetVersion.setWarningAlarmId(rs.getString("WARNING_ALARM_ID"));

                rs.getDouble("LOWER_CONTROL_LIMIT");
                parameterSetVersion
                        .setLowerControlLimit(rs.wasNull() ? null : (new Double(rs.getDouble("LOWER_CONTROL_LIMIT"))));
                rs.getDouble("UPPER_CONTROL_LIMIT");
                parameterSetVersion
                        .setUpperControlLimit(rs.wasNull() ? null : (new Double(rs.getDouble("UPPER_CONTROL_LIMIT"))));
                parameterSetVersion.setControlAlarmId(rs.getString("CONTROL_ALARM_ID"));

                rs.getDouble("LOWER_SHUTDOWN_LIMIT");
                parameterSetVersion
                        .setLowerShutdownLimit(rs.wasNull() ? null : (new Double(rs.getDouble("LOWER_SHUTDOWN_LIMIT"))));
                rs.getDouble("UPPER_SHUTDOWN_LIMIT");
                parameterSetVersion
                        .setUpperShutdownLimit(rs.wasNull() ? null : (new Double(rs.getDouble("UPPER_SHUTDOWN_LIMIT"))));
                parameterSetVersion.setShutdownAlarmId(rs.getString("SHUTDOWN_ALARM_ID"));

                rs.getDouble("LOWER_SPECIFICATION_LIMIT");
                parameterSetVersion.setLowerSpecificationLimit(
                        rs.wasNull() ? null : (new Double(rs.getDouble("LOWER_SPECIFICATION_LIMIT"))));
                rs.getDouble("UPPER_SPECIFICATION_LIMIT");
                parameterSetVersion.setUpperSpecificationLimit(
                        rs.wasNull() ? null : (new Double(rs.getDouble("UPPER_SPECIFICATION_LIMIT"))));
                parameterSetVersion.setSpecificationAlarmId(rs.getString("SPECIFICATION_ALARM_ID"));

                parameterSetVersion.setParameters(new ArrayList());
                parameterSetVersion.getParameters().add(parameter);

                String dataType = rs.getString("DATA_TYPE");
                if ("AVERAGE".equalsIgnoreCase(dataType) || "STDDEVIATION".equalsIgnoreCase(dataType) ||
                        "UNIFORMITY-1".equalsIgnoreCase(dataType) || "UNIFORMITY-2".equalsIgnoreCase(dataType)) {
                    calDataTypeNumber++;
                    parameterSetVersions.add(parameterSetVersion);
                } else {
                    parameterSetVersions.add(parameterSetVersions.size() - calDataTypeNumber, parameterSetVersion);
                }
            }
            Iterator it = parameterSetVersions.iterator();

            while (it.hasNext()) {
                ParameterSetVersion parameterSetVersion = (ParameterSetVersion) it.next();
                Parameter parameter = (Parameter) parameterSetVersion.getParameters().iterator().next();

                // nsi reading 没有版本管理，取parameter的reading
                sql = "SELECT PARAMETER_SET_RRN,PARAMETER_SET_VERSION,PARAMETER_RRN,READING_SEQUENCE," + "READING_PROMPT," +
                        "INSTANCE_ID,INSTANCE_DESC FROM " + DataBaseNames.READING_PROMPTS + "," +
                        DataBaseNames.NAMEDOBJECT +
                        " WHERE PARAMETER_SET_RRN = 0 AND PARAMETER_SET_VERSION =0  AND PARAMETER_RRN=? AND " +
                        "READING_PROMPT=INSTANCE_RRN " + " AND OBJECT='LABEL' " + " ORDER BY READING_SEQUENCE ";

                rs = jdbcTemplate.queryForRowSet(sql, new Object[]{parameter.getInstanceRrn()});
                List readingPrompts = new ArrayList();

                while (rs.next()) {
                    Prompt prompt = new Prompt();
                    prompt.setParameterSetRrn(new Long(rs.getLong("PARAMETER_SET_RRN")));
                    prompt.setParameterSetVersion(new Integer(rs.getInt("PARAMETER_SET_VERSION")));

                    prompt.setParameterRrn(new Long(rs.getLong("PARAMETER_RRN")));
                    prompt.setSequence(new Integer(rs.getInt("READING_SEQUENCE")));
                    prompt.setPromptRrn(new Long(rs.getLong("READING_PROMPT")));
                    prompt.setPromptId(rs.getString("INSTANCE_ID"));
                    prompt.setPromptDesc(rs.getString("INSTANCE_DESC"));
                    readingPrompts.add(prompt);
                }

                parameter.setReadingPrompts(readingPrompts);
                parameter.setSamplePrompts(new ArrayList());
                String dataType = parameter.getDataType();
                if ("AVERAGE".equalsIgnoreCase(dataType) || "STDDEVIATION".equalsIgnoreCase(dataType) ||
                        "UNIFORMITY-1".equalsIgnoreCase(dataType) || "UNIFORMITY-2".equalsIgnoreCase(dataType)) {
                    List sourceParameters = this.getCalculateSourceParameters(parameterSetVersion.getInstanceRrn(),
                                                                              parameterSetVersion.getInstanceVersion(),
                                                                              parameter.getInstanceRrn());
                    parameter.setSourceParameters(sourceParameters);
                }
            }
            return parameterSetVersions;
        }

        @Override
        public String getParameterSetUseForumla(long parameterSetRrn) {
            String sql = "SELECT " + " USE_FORUMLA " + " FROM PARAMETER_SET_EXT " + " WHERE PARAMETER_SET_RRN =?";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, new Object[]{parameterSetRrn});
            if (rs.next()) {
                return rs.getString("USE_FORUMLA");
            } else {
                return null;
            }
        }

        @Override
        public String getParameterSetMultiEqptMonitor(long parameterSetRrn) {
            String sql = "SELECT " + " ATTRIBUTE_DATA3 " + " FROM PARAMETER_SET_EXT " + " WHERE PARAMETER_SET_RRN =?";
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, new Object[]{parameterSetRrn});
            if (rs.next()) {
                return rs.getString("ATTRIBUTE_DATA3");
            } else {
                return null;
            }
        }

        @Override
        public List<NonRTUploadDataInfo> getNonRTDataValues(String spcJobName, Long lotRrn, List<String> unitList) {

            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append("n.TRANS_RRN,n.FACILITY_RRN,n.EQPT_ID,n.EQPT_RRN,n.LOT_ID,n.LOT_RRN,n.UNIT_ID,");
            sql.append("n.SPC_JOB_ID,n.SPC_JOB_NAME,n.NON_RT_KEY,n.RETEST_FLAG,n.SUBGROUP_SIZE_FLAG,n.AREA,N.DATA_VALUES,");
            sql.append("n.PRO1,n.PRO2,N.PRO3,N.PRO4,N.PRO5,");
            sql.append("n.VIOLATION_INFO,n.FORMULA,n.FORMULA_PARA,N.FORMULA_TYPE,N.VALUE_TYPE,N.TIME_DATA_VALUE,");
            sql.append("N.IMPORT_TYPE ").append(" FROM ").append("non_rt_data_history n,");
            sql.append("(select SPC_JOB_ID,lot_rrn,unit_id,value_type,max(create_time) as max_create_time ");
            sql.append("from non_rt_data_history group by SPC_JOB_ID,lot_rrn,unit_id,value_type) g ");
            sql.append("where n.spc_job_id=g.spc_job_id and n.lot_rrn = g.lot_rrn and n.unit_id = g.unit_id AND ");
            sql.append("N.Value_Type=G.value_type and n.create_time = g.max_create_time ");
            sql.append(" AND N.SPC_JOB_NAME = ? AND N.LOT_RRN = ? ");

            List<Object> argList = new ArrayList<>();
            argList.add(spcJobName);
            argList.add(lotRrn);

            StringBuilder unitBuilder = new StringBuilder("AND N.UNIT_ID IN (");
            for(String unitId:unitList) {
                unitBuilder.append("?,");
                argList.add(unitId);
            }
            unitBuilder.delete(unitBuilder.lastIndexOf(StringUtils.COMMA_SIGN), unitBuilder.length()).append(")");
            sql.append(unitBuilder);

            List<NonRTUploadDataInfo> list = jdbcTemplate
                    .query(sql.toString(), argList.toArray(), new RowMapper<NonRTUploadDataInfo>() {
                        @Override
                public NonRTUploadDataInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    NonRTUploadDataInfo data = new NonRTUploadDataInfo();
                    data.setFacilityRrn(rs.getLong("FACILITY_RRN"));
                    data.setEqptId(rs.getString("EQPT_ID"));
                    data.setEqptRrn(rs.getLong("EQPT_RRN"));
                    data.setLotId(rs.getString("LOT_ID"));
                    data.setLotRrn(rs.getLong("LOT_RRN"));
                    data.setUnitId(rs.getString("UNIT_ID"));
                    data.setJobId(rs.getString("SPC_JOB_ID"));
                    data.setJobName(rs.getString("SPC_JOB_NAME"));
                    data.setNonRTKey(rs.getString("NON_RT_KEY"));
                    data.setRetestFlag(rs.getString("RETEST_FLAG"));
                    data.setSubgroupSizeFlag(rs.getString("SUBGROUP_SIZE_FLAG"));
                    data.setArea(rs.getString("AREA"));
                    String dataValuesStr = rs.getString("DATA_VALUES");
                    String[] dataValues = dataValuesStr.split(",");
                    if (dataValues != null && dataValues.length > 0) {
                        double[] datavalues = new double[dataValues.length];
                        for (int i = 0; i < dataValues.length; i++) {
                            datavalues[i] = NumberUtils.toDouble(dataValues[i]);
                        }
                        data.setValueList(datavalues);
                    }
                    data.setDataValues(rs.getString("DATA_VALUES"));
                    data.setViolationInfo(rs.getString("VIOLATION_INFO"));
                    data.setValueType(rs.getString("VALUE_TYPE"));
                    data.setTime(rs.getString("TIME_DATA_VALUE"));
                    data.setFormula(rs.getString("FORMULA"));
                    data.setFormulaPara(rs.getString("FORMULA_PARA"));
                    data.setFormulaType(rs.getString("FORMULA_TYPE"));
                    data.setPro1(rs.getString("PRO1"));
                    data.setPro2(rs.getString("PRO2"));
                    data.setPro3(rs.getString("PRO3"));
                    data.setPro4(rs.getString("PRO4"));
                    data.setPro5(rs.getString("PRO5"));
                    data.setImportType(rs.getString("IMPORT_TYPE"));
                    return data;
                }
            });
            return list;
        }

        @Override
        public Page getNonRTUploadHistory(Page page) {
            StringBuffer sql = new StringBuffer(
                    "SELECT TRANS_RRN,FACILITY_RRN,EQPT_ID,EQPT_RRN,LOT_ID,LOT_RRN,UNIT_ID,SPC_JOB_ID," + "NON_RT_KEY," +
                            "RETEST_FLAG," + "SUBGROUP_SIZE_FLAG,AREA,PRO1,PRO2,PRO3,PRO4,PRO5,DATA_VALUES," +
                            "CREATE_USER_ID," + "CREATE_TIME," + "SPC_JOB_NAME,VIOLATION_INFO," + "TIME_DATA_VALUE," +
                            "FORMULA,FORMULA_PARA,VALUE_TYPE,FORMULA_TYPE,IMPORT_TYPE,UNIT_SHOW," + "COMMENTS ,VALID_HIGH" +
                            " , VALID_LOW FROM NON_RT_DATA_HISTORY WHERE 1=1 ");
            Map condition = page.getBaseInfo();

            List args = new ArrayList();
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "eqptRrn"))) {
                sql.append(" AND EQPT_RRN = ?");
                args.add(MapUtils.getLongValue(condition, "eqptRrn"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "lotRrn"))) {
                sql.append(" AND LOT_RRN = ?");
                args.add(MapUtils.getLongValue(condition, "lotRrn"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "jobName"))) {
                sql.append(" AND INSTR(SPC_JOB_NAME,?)>0");
                args.add(MapUtils.getString(condition, "jobName"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "areaId"))) {
                sql.append(" AND AREA= ?");
                args.add(MapUtils.getString(condition, "areaId"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "startTime"))) {
                sql.append(" AND CREATE_TIME > to_date(?,'yyyy-MM-dd HH24:mi:ss')");

                args.add(MapUtils.getString(condition, "startTime"));
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "endTime"))) {
                sql.append(" AND CREATE_TIME < to_date(?,'yyyy-MM-dd HH24:mi:ss')");

                args.add(MapUtils.getString(condition, "endTime"));
            }
            sql.append(" ORDER BY CREATE_TIME DESC,UNIT_ID");

            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(), new RowMapper<NonRTUploadDataInfo>() {
                @Override
                public NonRTUploadDataInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    NonRTUploadDataInfo data = new NonRTUploadDataInfo();
                    data.setFacilityRrn(rs.getLong("FACILITY_RRN"));
                    data.setEqptId(rs.getString("EQPT_ID"));
                    data.setEqptRrn(rs.getLong("EQPT_RRN"));
                    data.setLotId(rs.getString("LOT_ID"));
                    data.setLotRrn(rs.getLong("LOT_RRN"));
                    data.setUnitId(rs.getString("UNIT_ID"));
                    data.setJobId(rs.getString("SPC_JOB_ID"));
                    data.setJobName(rs.getString("SPC_JOB_NAME"));
                    data.setNonRTKey(rs.getString("NON_RT_KEY"));
                    data.setRetestFlag(rs.getString("RETEST_FLAG"));
                    data.setSubgroupSizeFlag(rs.getString("SUBGROUP_SIZE_FLAG"));
                    data.setArea(rs.getString("AREA"));
                    String dataValuesStr = rs.getString("DATA_VALUES");
                    if (StringUtils.isNotBlank(dataValuesStr)) {
                        String[] dataValues = dataValuesStr.split(",");
                        if (dataValues != null && dataValues.length > 0) {
                            double[] datavalues = new double[dataValues.length];
                            for (int i = 0; i < dataValues.length; i++) {
                                datavalues[i] = NumberUtils.toDouble(dataValues[i]);
                            }
                            data.setValueList(datavalues);
                        }
                    }
                    data.setDataValues(rs.getString("DATA_VALUES"));
                    data.setViolationInfo(rs.getString("VIOLATION_INFO"));
                    data.setValueType(rs.getString("VALUE_TYPE"));
                    data.setTime(rs.getString("TIME_DATA_VALUE"));
                    data.setFormula(rs.getString("FORMULA"));
                    data.setFormulaPara(rs.getString("FORMULA_PARA"));
                    data.setFormulaType(rs.getString("FORMULA_TYPE"));
                    data.setPro1(rs.getString("PRO1"));
                    data.setPro2(rs.getString("PRO2"));
                    data.setPro3(rs.getString("PRO3"));
                    data.setPro4(rs.getString("PRO4"));
                    data.setPro5(rs.getString("PRO5"));
                    data.setCreateUser(rs.getString("CREATE_USER_ID"));
                    data.setCreateTime(DateUtils.formatDate(rs.getTimestamp("CREATE_TIME")));
                    data.setImportType(rs.getString("IMPORT_TYPE"));
                    data.setUnitShow(rs.getString("UNIT_SHOW"));
                    data.setComment(rs.getString("COMMENTS"));
                    data.setValidHigh(rs.getString("VALID_HIGH"));
                    data.setValidLow(rs.getString("VALID_LOW"));
                    return data;
                }
            });
        }

        @Override
        public List getSamples(DataCollection dc) {
            String sql = "select * from " + DataBaseNames.SAMPLE_DATA + " a where a.dcol_rrn=? and a.dcol_sequence=? " +
                    "order by a.sample_sequence";
            List sampleList = new ArrayList();
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, new Object[]{dc.getDcolRrn(), dc.getDcolSequence()});
            while (rs.next()) {
                Sample sample = new Sample();
                sample.setDcolRrn(new Long(rs.getLong("DCOL_RRN")));
                sample.setDcolSequence(Integer.valueOf(rs.getString("DCOL_SEQUENCE")));
                sample.setSampleSequence(Integer.valueOf(rs.getString("SAMPLE_SEQUENCE")));
                sample.setUnitRrn(new Long(rs.getLong("UNIT_RRN")));
                sample.setSampleId(rs.getString("SAMPLE_ID"));
                sample.setRecipeId(rs.getString("RECIPE_ID"));
                rs.getDouble("SAMPLE_VALUE");
                sample.setSampleValue(rs.wasNull() ? null : (new Double(rs.getDouble("SAMPLE_VALUE"))));

                rs.getDouble("SUM_VALUE");
                sample.setSumValue(rs.wasNull() ? null : (new Double(rs.getDouble("SUM_VALUE"))));

                rs.getDouble("AVERAGE_VALUE");
                sample.setAverageValue(rs.wasNull() ? null : (new Double(rs.getDouble("AVERAGE_VALUE"))));

                rs.getDouble("RANGE_VALUE");
                sample.setRangeValue(rs.wasNull() ? null : (new Double(rs.getDouble("RANGE_VALUE"))));

                rs.getDouble("MINIMUM_VALUE");
                sample.setMinimumValue(rs.wasNull() ? null : (new Double(rs.getDouble("MINIMUM_VALUE"))));

                rs.getDouble("MAXIMUM_VALUE");
                sample.setMaximumValue(rs.wasNull() ? null : (new Double(rs.getDouble("MAXIMUM_VALUE"))));

                rs.getDouble("STANDARD_DEVIATION_VALUE");
                sample.setStandardDeviationValue(
                        rs.wasNull() ? null : (new Double(rs.getDouble("STANDARD_DEVIATION_VALUE"))));

                rs.getDouble("CUSTOM_VALUE");
                sample.setCustomValue(rs.wasNull() ? null : (new Double(rs.getDouble("CUSTOM_VALUE"))));
                sample.setComments(rs.getString("COMMENTS"));

                List readings = this.getReadings4RawData(sample);
                if (!readings.isEmpty()) {
                    sample.setReadings(readings);
                }
                sampleList.add(sample);
            }
            return sampleList;
        }

        @Override
        public List getReadings4RawData(Sample sample) {
            String sql = "select * from " + DataBaseNames.RAW_DATA + " a where a.dcol_rrn=? and a.dcol_sequence= ? and a" +
                    ".sample_sequence=? order by" + " a.reading_sequence ";
            List readsList = new ArrayList();
            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql,
                                                       new Object[]{sample.getDcolRrn(), sample.getDcolSequence(),
                                                               sample.getSampleSequence()});
            while (rs.next()) {
                Reading reading = new Reading();
                reading.setDataPointRrn(new Long(rs.getLong("DATA_POINT_RRN")));
                reading.setDcolRrn(new Long(rs.getLong("DCOL_RRN")));
                reading.setDcolSequence(Integer.valueOf(rs.getString("DCOL_SEQUENCE")));
                reading.setSampleSequence(Integer.valueOf(rs.getString("SAMPLE_SEQUENCE")));
                reading.setReadingSequence(Integer.valueOf(rs.getString("READING_SEQUENCE")));
                reading.setReadingId(rs.getString("READING_ID"));
                rs.getDouble("DATA_VALUE");
                reading.setDataValue(rs.wasNull() ? null : (new Double(rs.getDouble("DATA_VALUE"))));
                reading.setCollectionTimestamp(String.valueOf(rs.getDate("COLLECTION_TIMESTAMP")));
                reading.setOosFlag(rs.getString("OOS_FLAG"));
                reading.setOowFlag(rs.getString("OOW_FLAG"));
                reading.setPlotFlag(rs.getString("PLOT_FLAG"));
                reading.setCalculationFlag(rs.getString("CALCULATION_FLAG"));
                readsList.add(reading);
            }
            return readsList;
        }

        @Override
        public Page getEdcPlanImportHistory(Page page) {
            String sql = "SELECT p.trans_rrn,p.import_sequence,p.import_data_type,p.categroy,p.product_id,p" +
                    ".product_rrn,p.process_id,p.process_rrn," + "p.flow_seq,p.operation_id,p.operation_rrn,p" +
                    ".operation_desc,p.edc_plan,p.meas_spec,p" + ".sequence,p.meas_desc,p.prompt,p.data_type," + "p.unit," +
                    "p.meas_type,p.dept,p.units,p.sites,p.is_derived,p.edc_operator,p.operand1,p" + ".operand2,p" +
                    ".auto_exclude,p.uscr,p.uspec,p.target,p.lspec,p.lscr," + "p.chart_id,p.chart_desc,p.chart_template,p" +
                    ".allow_partial_required,p" + ".receive_violations,p" + ".rule_violoca,p.spec_violoca,p.ocap,p" +
                    ".measurement_type," + "p.NUMBER_of_units,p.NUMBER_of_sites,p.sub_group_style,p.sub_group_size,p" +
                    ".trend_usl," + "p.trend_ucl,p.trend_cl,p.trend_lcl,p.trend_lsl,p.trend_rules,p.xbar_usl,p.xbar_ucl," +
                    "p.xbar_cl,p.xbar_lcl," +
                    "p.xbar_lsl,p.xbar_rules,p.rs_usl,p.rs_ucl,p.rs_cl,p.rs_lcl,p.rs_lsl,p.rs_rules," +
                    "t.trans_id,t.trans_end_timestamp,t.trans_performed_by,cp_para,key_chart_flag" +
                    " FROM EDC_PLAN_IMPORT_HISTORY P left join TRANSACTION_LOG T " +
                    "on p.trans_rrn=t.trans_rrn where 1=1  ";

            Map condition = page.getBaseInfo();

            /*if (StringUtils.isNotBlank(MapUtils.getString(condition, "productRrn"))) {
                sql += " AND p.PRODUCT_RRN = " + MapUtils.getLongValue(condition, "productRrn");
            }*/
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "productId"))) {
                sql += " AND p.PRODUCT_ID = '" + MapUtils.getString(condition, "productId") + "'";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "edcPlan"))) {
                sql += " AND p.EDC_PLAN = '" + MapUtils.getString(condition, "edcPlan") + "'";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "transId"))) {
                sql += " AND T.TRANS_ID= '" + MapUtils.getString(condition, "transId") + "'";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "impDataType"))) {
                sql += " AND p.import_data_type= '" + MapUtils.getString(condition, "impDataType") + "'";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "startTime"))) {
                sql += " AND t.trans_start_timestamp > to_date('" + MapUtils.getString(condition, "startTime") +
                        "','yyyy-MM-dd " + "HH24:mi:ss')";
            }
            if (StringUtils.isNotBlank(MapUtils.getString(condition, "endTime"))) {
                sql += " AND t.trans_end_timestamp < to_date('" + MapUtils.getString(condition, "endTime") +
                        "','yyyy-MM-dd " + "HH24:mi:ss')";
            }

            sql = "SELECT * FROM(SELECT T.*" + " FROM (SELECT * FROM (" + sql + ") n ORDER BY trans_end_timestamp DESC," +
                    "import_sequence) T )";

            Object[] args = new Object[]{};

            Page resultPage = jdbcTemplate
                    .queryForPage(page, sql, args, new org.springframework.jdbc.core.RowMapper<EDCPlanExcelData>() {

                        @Override
                        public EDCPlanExcelData mapRow(ResultSet rs, int rowNum) throws SQLException {
                            EDCPlanExcelData data = new EDCPlanExcelData();
                            data.setCategory(rs.getString("categroy"));
                            data.setProductId(rs.getString("product_id"));
                            data.setProductRrn(rs.getLong("product_rrn"));
                            data.setProcessId(rs.getString("process_id"));
                            data.setProcessRrn(rs.getLong("process_rrn"));
                            data.setStepSeq(rs.getString("flow_seq"));
                            data.setStepId(rs.getString("operation_id"));
                            data.setStepRrn(rs.getLong("operation_rrn"));
                            data.setStepDes(rs.getString("operation_desc"));
                            data.setEdcplan(rs.getString("edc_plan"));
                            data.setMeasspec(rs.getString("meas_spec"));
                            data.setSequence(rs.getString("sequence"));
                            data.setMeasdes(rs.getString("meas_desc"));
                            data.setPrompt(rs.getString("prompt"));
                            data.setDataType(rs.getString("data_type"));
                            data.setUnit(rs.getString("unit"));
                            data.setMeasType(rs.getString("meas_type"));
                            data.setDept(rs.getString("dept"));
                            data.setUnits(rs.getString("units"));
                            data.setSites(rs.getString("sites"));
                            data.setIsDerived(rs.getString("is_derived"));
                            data.setEdcOperator(rs.getString("edc_operator"));
                            data.setOperand1(rs.getString("operand1"));
                            data.setOperand2(rs.getString("operand2"));
                            data.setAutoExclude(rs.getString("auto_exclude"));
                            data.setChartId(rs.getString("chart_id"));
                            data.setChartDes(rs.getString("chart_desc"));
                            data.setChartTemplate(rs.getString("chart_template"));
                            data.setAllowPartialRequired(rs.getString("allow_partial_required"));
                            data.setReceiveViolations(rs.getString("receive_violations"));
                            data.setRulevioloca(rs.getString("rule_violoca"));
                            data.setSpecvioloca(rs.getString("spec_violoca"));
                            data.setOcap(rs.getString("ocap"));
                            data.setMeasurementType(rs.getString("measurement_type"));
                            data.setNumberOfUnits(rs.getString("NUMBER_of_units"));
                            data.setNumberOfSites(rs.getString("NUMBER_of_sites"));
                            data.setSubgroupStyle(rs.getString("sub_group_style"));
                            data.setSubgroupSize(rs.getString("sub_group_size"));
                            data.setTrend_USL(rs.getString("trend_usl"));
                            data.setTrend_UCL(rs.getString("trend_ucl"));
                            data.setTrend_CL(rs.getString("trend_cl"));
                            data.setTrend_LCL(rs.getString("trend_lcl"));
                            data.setTrend_LSL(rs.getString("trend_lsl"));
                            data.setTrend_rules(rs.getString("trend_rules"));
                            data.setXBAR_USL(rs.getString("xbar_usl"));
                            data.setXBAR_UCL(rs.getString("xbar_ucl"));
                            data.setXBAR_CL(rs.getString("xbar_cl"));
                            data.setXBAR_LCL(rs.getString("xbar_lcl"));
                            data.setXBAR_LSL(rs.getString("xbar_lsl"));
                            data.setXBAR_RULES(rs.getString("xbar_rules"));
                            data.setRS_USL(rs.getString("rs_usl"));
                            data.setRS_UCL(rs.getString("rs_ucl"));
                            data.setRS_CL(rs.getString("rs_cl"));
                            data.setRS_LCL(rs.getString("rs_lcl"));
                            data.setRS_LSL(rs.getString("rs_lsl"));
                            data.setRS_rules(rs.getString("rs_rules"));
                            data.setImpType(rs.getString("trans_id"));
                            data.setImpUser(rs.getString("trans_performed_by"));
                            data.setImpSeq(rs.getInt("import_sequence"));
                            data.setImpTime(DateUtils.formatDate(rs.getTimestamp("trans_end_timestamp"),
                                                                 DateUtils.DATE_FORMAT4DATE));
                            data.setImpDataType(rs.getString("import_data_type"));
                            data.setCpPara(rs.getString("cp_para"));
                            data.setKeyChartFlag(rs.getString("KEY_CHART_FLAG"));
                            return data;
                        }
                    });

            return resultPage;
        }

        @Override
        public void insertEdcPlanImportHistory(List<EDCPlanExcelData> list, long transRrn) {
            String sql = "INSERT INTO EDC_PLAN_IMPORT_HISTORY (trans_rrn,import_sequence,import_data_type,categroy," +
                    "product_id,product_rrn,process_id,process_rrn," + "flow_seq,operation_id,operation_rrn," +
                    "operation_desc,edc_plan,meas_spec,sequence," + "meas_desc,prompt,data_type," + "unit,meas_type,dept," +
                    "units,sites,is_derived,edc_operator,operand1,operand2,auto_exclude," + "chart_id,chart_desc," +
                    "chart_template,allow_partial_required,receive_violations," + "rule_violoca,spec_violoca,ocap," +
                    "measurement_type," + "NUMBER_of_units,NUMBER_of_sites,sub_group_style,sub_group_size," + "trend_usl," +
                    "trend_ucl,trend_cl,trend_lcl,trend_LSL,trend_rules,trend_SPEC,XBAR_USL," + "xbar_ucl,xbar_cl," +
                    "xbar_lcl,XBAR_LSL,xbar_rules,XBAR_SPEC,RS_USL,rs_ucl," + "rs_cl,rs_lcl,RS_LSL,rs_rules,RS_SPEC," +
                    "cp_para,key_chart_flag)" + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                    "?,?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List<Object[]> batchArgs = new ArrayList<>();
            for (int i = 0; i < list.size(); i++) {
                EDCPlanExcelData data = list.get(i);
                Object[] args = new Object[]{transRrn, i +
                        1, data.getImpDataType(), data.getCategory(), data.getProductId(), data.getProductRrn(),
                        data.getProcessId(), data.getProcessRrn(), data.getStepSeq(), data.getStepId(), data.getStepRrn()
                        , data.getStepDes(), data.getEdcplan(), data.getMeasspec(), NumberUtils.toLong(
                        data.getSequence()), data.getMeasdes(), data.getPrompt(), data.getDataType(), data.getUnit(),
                        data.getMeasType(), data.getDept(), data.getUnits(), data.getSites(), data.getIsDerived(),
                        data.getEdcOperator(), data.getOperand1(), data.getOperand2(), data.getAutoExclude(),

                        data.getChartId(), data.getChartDes(), data.getChartTemplate(), data.getAllowPartialRequired(),
                        data.getReceiveViolations(), data.getRulevioloca(), data.getSpecvioloca(), data.getOcap(),
                        data.getMeasurementType(), data.getNumberOfUnits(), data.getNumberOfSites(),
                        data.getSubgroupStyle(), data.getSubgroupSize(), data.getTrend_USL(), data.getTrend_UCL(),
                        data.getTrend_CL(), data.getTrend_LCL(), data.getTrend_LSL(), data.getTrend_rules(),
                        data.getTrend_SPEC(), data.getXBAR_USL(), data.getXBAR_UCL(), data.getXBAR_CL(),
                        data.getXBAR_LCL(), data.getXBAR_LSL(), data.getXBAR_RULES(), data.getXBAR_SPEC(),
                        data.getRS_USL(), data.getRS_UCL(), data.getRS_CL(), data.getRS_LCL(), data.getRS_LSL(),
                        data.getRS_rules(), data.getRS_SPEC(), data.getCpPara(), data.getKeyChartFlag()};
                batchArgs.add(args);
            }
            jdbcTemplate.batchUpdate(sql, batchArgs);
        }

        @Override
        public void deleteParameterSetVersion(Long parameterSetRrn, Integer parameterSetVersion) {
            String sql = "DELETE FROM " + DataBaseNames.PARAMETER_SET_VERSION + " WHERE parameter_set_rrn = ? and " +
                    "parameter_set_version = ?";
            jdbcTemplate.update(sql, new Object[]{parameterSetRrn, parameterSetVersion});
        }

        @Override
        public void deleteSamplePromptsForAll(Long parameterSetRrn, Integer parameterSetVersion) {
            String sql = "DELETE FROM " + DataBaseNames.SAMPLE_PROMPTS + " WHERE parameter_set_rrn = ? and " +
                    "parameter_set_version = ?";
            jdbcTemplate.update(sql, new Object[]{parameterSetRrn, parameterSetVersion});
        }

        @Override
        public void deleteReadingPromptsForAll(Long parameterSetRrn, Integer parameterSetVersion) {
            String sql = "DELETE FROM " + DataBaseNames.READING_PROMPTS + " WHERE PARAMETER_SET_RRN = ? AND " +
                    "PARAMETER_SET_VERSION = ?";
            jdbcTemplate.update(sql, new Object[]{parameterSetRrn, parameterSetVersion});
        }

        @Override
        public void deleteDataCollectionRuleForAll(Long parameterSetRrn, Integer parameterSetVersion) {
            String sql = "DELETE FROM DATA_COLLECTION_RULE WHERE PARAMETER_SET_RRN = ? AND " + "PARAMETER_SET_VERSION = ? ";
            jdbcTemplate.update(sql, parameterSetRrn, parameterSetVersion);
        }

        @Override
        public List<RunCardRawDataInfo> getRunCardRawDataInfoList(Long lotRrn, Long stepSequence) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" UNIT_ID,UNIT_RRN,RUNCARD_ID,RUNCARD_RRN,RECIPE_ID,PARAMETER_ID,DATA_VALUES ");
            sql.append(" FROM ").append(DataBaseNames.SRC_SAMPLE_RAWDATA);
            sql.append(" WHERE LOT_RRN= ? AND STEP_SEQUENCE = ?");

            Object[] args = new Object[]{lotRrn, stepSequence};

            return jdbcTemplate.query(sql.toString(), args, new RowMapper<RunCardRawDataInfo>() {

                @Override
                public RunCardRawDataInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    RunCardRawDataInfo rawDataInfo = new RunCardRawDataInfo();
                    rawDataInfo.setUnitId(rs.getString("UNIT_ID"));
                    rawDataInfo.setUnitRrn(rs.getLong("UNIT_RRN"));
                    rawDataInfo.setRunCardId(rs.getString("RUNCARD_ID"));
                    rawDataInfo.setRunCardRrn(rs.getLong("RUNCARD_RRN"));
                    rawDataInfo.setRecipeId(rs.getString("RECIPE_ID"));
                    rawDataInfo.setParameterId(rs.getString("PARAMETER_ID"));
                    rawDataInfo.setDataValues(rs.getString("DATA_VALUES"));
                    return rawDataInfo;
                }
            });
        }

        @Override
        public void insertSrcSampleRawdata(List<RunCardRawDataInfo> rawDataInfoList) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");
            sql.append(DataBaseNames.SRC_SAMPLE_RAWDATA);
            sql.append(" (lot_special_step_rrn,runcard_id,runcard_rrn,lot_rrn,step_sequence,unit_id,unit_rrn,");
            sql.append(" eqpt_id,eqpt_Rrn,step_no,recipe_id,parameter_set_rrn,parameter_id,parameter_rrn,data_values)");
            sql.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            List<Object[]> batchArgs = new ArrayList<Object[]>();
            for (RunCardRawDataInfo rawDataInfo : rawDataInfoList) {
                Object[] args = new Object[]{rawDataInfo.getLotSpecialStepRrn(), rawDataInfo.getRunCardId(),
                                             rawDataInfo.getRunCardRrn(), rawDataInfo.getLotRrn(),
                                             rawDataInfo.getStepSequence(), rawDataInfo.getUnitId(),
                                             rawDataInfo.getUnitRrn(), rawDataInfo.getEquipmentId(),
                                             rawDataInfo.getEquipmentRrn(), rawDataInfo.getStepNo(),
                                             rawDataInfo.getRecipeId(), rawDataInfo.getParameterSetRrn(),
                                             rawDataInfo.getParameterId(), rawDataInfo.getParameterRrn(),
                                             rawDataInfo.getDataValues()};
                batchArgs.add(args);
            }
            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        @Override
        public void updateNonRTUploadHistoryWithOcapId(String ocapId, Long transRrn) {
            StringBuffer sql = new StringBuffer();
            sql.append("UPDATE NON_RT_DATA_HISTORY SET OCAP_ID = ?");
            sql.append(" WHERE TRANS_RRN = ? ");
            Object[] args = new Object[]{ocapId, transRrn};
            jdbcTemplate.update(sql.toString(), args);
        }

        @Override
        public String queryOfflineUnitList(String jobId, String lotId, String ocapId) {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT DISTINCT UNIT_ID  FROM NON_RT_DATA_HISTORY L WHERE ");
            sql.append(
                    " L.CREATE_TIME = (SELECT MAX(CREATE_TIME) FROM NON_RT_DATA_HISTORY L WHERE L.SPC_JOB_ID = " + "?  " +
                            "AND L.LOT_ID = ? AND L.OCAP_ID = ?) ORDER BY UNIT_ID");
            List<String> unitList = jdbcTemplate.query(sql.toString(), String.class, new Object[]{jobId, lotId, ocapId});
            StringBuffer units = new StringBuffer();
            String unitIds = StringUtils.EMPTY;
            if (CollectionUtils.isNotEmpty(unitList)) {
                for (String unitId : unitList) {
                    units.append(unitId).append(",");
                }
                unitIds = units.substring(0, units.lastIndexOf(","));
            }
            return unitIds;
        }

        @Override
        public void insertNonRTUploadHistory(List<NonRTUploadDetailDTO> uploadDetails, long transRrn, String ocapId) {
            StringBuffer sql = new StringBuffer(
                    "INSERT INTO NON_RT_DATA_HISTORY (TRANS_RRN,FACILITY_RRN,EQPT_ID,EQPT_RRN,LOT_ID,LOT_RRN," +
                            "UNIT_ID,SPC_JOB_ID,NON_RT_KEY,");
            sql.append(" RETEST_FLAG,SUBGROUP_SIZE_FLAG,AREA,PRO1,PRO2,PRO3,PRO4,PRO5,DATA_VALUES,CREATE_USER_ID," +
                               "CREATE_TIME,SPC_JOB_NAME,VIOLATION_INFO,");
            sql.append(" VALUE_TYPE,TIME_DATA_VALUE,FORMULA,FORMULA_PARA,FORMULA_TYPE,IMPORT_TYPE, OCAP_ID");
            sql.append(") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?,?,?,?,?,?,?,?,?)");
            List<Object[]> batchArgs = new ArrayList<Object[]>();
            for (NonRTUploadDetailDTO detailDTO : uploadDetails) {
                NonRTUploadDataInfo data = detailDTO.getUploadData();
                String dataValues = "";
                if (data.getValueList() != null) {
                    for (double value : data.getValueList()) {
                        String valueStr = "";
                        if (value - Math.floor(value) < 1e-10) {
                            valueStr = new Double(value).intValue() + "";
                        } else {
                            valueStr = value + "";
                        }
                        if (StringUtils.isBlank(valueStr)) {
                            valueStr = "0";
                        }
                        dataValues += valueStr + ",";
                    }
                    if (StringUtils.isNotBlank(dataValues)) {
                        dataValues = dataValues.substring(0, dataValues.length() - 1);
                    } else {
                        dataValues = "0";
                    }
                }

                Object[] args = new Object[]{transRrn, data.getFacilityRrn(), data.getEqptId(), data.getEqptRrn(),
                        data.getLotId(), data.getLotRrn(), data.getUnitId(), data.getJobId(), data.getNonRTKey(),
                        data.getRetestFlag(), data.getSubgroupSizeFlag(), data.getArea(), data.getPro1(), data.getPro2(),
                        data.getPro3(), data.getPro4(), data.getPro5(), dataValues, data.getCreateUser(),
                        data.getJobName(), data.getViolationInfo(), data.getValueType(), data.getTime(),
                        data.getFormula(), data.getFormulaPara(), data.getFormulaType(), data.getImportType(), ocapId};
                batchArgs.add(args);
            }
            jdbcTemplate.batchUpdate(sql.toString(), batchArgs);
        }

        public List getCalculateSourceParameters(long parameterSetRrn, long parameterSetVersion, long parameterRrn) {
            List sourceParameters = new ArrayList();
            // get the value object by object rrn
            String sql = "select SOURCE_PARAMETER_SEQUENCE,instance_id,instance_desc, SOURCE_PARAMETER " + "from " +
                    DataBaseNames.CALCULATE_SOURCE_PARAMETERS + "," + DataBaseNames.NAMEDOBJECT +
                    " where SOURCE_PARAMETER=instance_rrn and " +
                    " parameter_rrn=? and parameter_set_rrn= ? and parameter_set_version= ? order by " +
                    "SOURCE_PARAMETER_SEQUENCE";
            SqlRowSet rs = jdbcTemplate
                    .queryForRowSet(sql, new Object[]{parameterRrn, parameterSetRrn, parameterSetVersion});
            while (rs.next()) {
                HashMap sourceParameter = new HashMap();
                sourceParameter.put("SOURCE_PARAMETER_SEQUENCE", new Long(rs.getLong("SOURCE_PARAMETER_SEQUENCE")));
                sourceParameter.put("SOURCE_PARAMETER_RRN", new Long(rs.getLong("SOURCE_PARAMETER")));
                sourceParameter.put("INSTANCE_ID", rs.getString("instance_id"));
                sourceParameter.put("INSTANCE_DESC", rs.getString("instance_desc"));
                sourceParameters.add(sourceParameter);
            }
            return sourceParameters;
        }

    }