# RecipeContextManagerDaoImpl.java

    package com.mycim.server.ctx.exec.dao.impl;

    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.jdbc.mapper.RowMapper;
    import com.mycim.framework.utils.MiscUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.ctx.exec.dao.RecipeContextManagerDao;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.prp.ContextValue;
    import com.mycim.valueobject.prp.RecipeContextValue;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    /**
     * RecipeContextManagerDaoImpl
     *
     * @author pinyan.song
     * @version 6.0.0
     * @date 2019-9-4
     **/
    @Repository
    public class RecipeContextManagerDaoImpl implements RecipeContextManagerDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public List<ContextValue> getContextValues(RecipeContextValue contextValue) {
            StringBuffer sb = new StringBuffer("");

            if ((contextValue.getContextKey1() != null) && !contextValue.getContextKey1().trim().equals("")) {
                sb.append(" AND context_key1 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey1()));
            }

            if ((contextValue.getContextKey2() != null) && !contextValue.getContextKey2().trim().equals("")) {
                sb.append(" AND context_key2 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey2()));
            }

            if ((contextValue.getContextKey3() != null) && !contextValue.getContextKey3().trim().equals("")) {
                sb.append(" AND context_key3 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey3()));
            }

            if ((contextValue.getContextKey4() != null) && !contextValue.getContextKey4().trim().equals("")) {
                sb.append(" AND context_key4 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey4()));
            }

            if ((contextValue.getContextKey5() != null) && !contextValue.getContextKey5().trim().equals("")) {
                sb.append(" AND context_key5 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey5()));
            }

            if ((contextValue.getContextKey6() != null) && !contextValue.getContextKey6().trim().equals("")) {
                sb.append(" AND context_key6 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey6()));
            }

            if ((contextValue.getContextKey7() != null) && !contextValue.getContextKey7().trim().equals("")) {
                sb.append(" AND context_key7 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey7()));
            }

            if ((contextValue.getContextKey8() != null) && !contextValue.getContextKey8().trim().equals("")) {
                sb.append(" AND context_key8 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey8()));
            }

            if ((contextValue.getContextKey9() != null) && !contextValue.getContextKey9().trim().equals("")) {
                sb.append(" AND context_key9 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey9()));
            }

            if ((contextValue.getContextKey10() != null) && !contextValue.getContextKey10().trim().equals("")) {
                sb.append(" AND context_key10 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getContextKey10()));
            }

            if ((contextValue.getResultValue1() != null) && !contextValue.getResultValue1().trim().equals("")) {
                sb.append(" AND result_value1 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getResultValue1()));
            }

            if ((contextValue.getResultValue5() != null) && !contextValue.getResultValue5().trim().equals("")) {
                sb.append(" AND result_value5 = ");
                sb.append(MiscUtils.parseSQL(contextValue.getResultValue5()));
            }

            String sql =
                    " SELECT context_rrn,sequence_number,status,effective_date_from,effective_date_to," + "ecn_rrn," + " " +
                            "context_key1, context_key2, context_key3, context_key4, context_key5, " + " context_key6, " +
                            "context_key7, context_key8, context_key9, " + " context_key10,context_key11,context_key12," +
                            "context_key13,context_key14,context_key15," + " result_value1,result_value2,result_value3," +
                            " result_value4,result_value5,result_value6, result_value7," +
                            " result_value8,result_value9,result_value10,result_value11," +
                            " result_value12,result_value13,result_value14,result_value15 " + " FROM " +
                            DataBaseNames.CONTEXT_VALUE + "    WHERE context_rrn = " + contextValue.getContextRrn();

            sql = sql + sb.toString();

            return jdbcTemplate.query(sql, new RowMapper<ContextValue>() {
                @Override
                public ContextValue mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ContextValue contextValue = new ContextValue();
                    contextValue.setContextRrn(rs.getLong("context_rrn"));
                    contextValue.setStatus(rs.getString("status"));
                    contextValue.setSequenceNumber(rs.getInt("sequence_number"));

                    if (rs.getDate("effective_date_from") != null) {
                        contextValue.setEffectiveDateFrom(DateUtils.formatDate(rs.getTimestamp("effective_date_from")));
                    }

                    contextValue.setEcnRrn(rs.getLong("ecn_rrn"));

                    if (rs.getDate("effective_date_to") != null) {
                        contextValue.setEffectiveDateTo(DateUtils.formatDate(rs.getTimestamp("effective_date_to")));
                    }

                    contextValue.setContextKey1(rs.getString("context_key1"));
                    contextValue.setContextKey2(rs.getString("context_key2"));
                    contextValue.setContextKey3(rs.getString("context_key3"));
                    contextValue.setContextKey4(rs.getString("context_key4"));
                    contextValue.setContextKey5(rs.getString("context_key5"));
                    contextValue.setContextKey6(rs.getString("context_key6"));
                    contextValue.setContextKey7(rs.getString("context_key7"));
                    contextValue.setContextKey8(rs.getString("context_key8"));
                    contextValue.setContextKey9(rs.getString("context_key9"));
                    contextValue.setContextKey10(rs.getString("context_key10"));
                    contextValue.setContextKey11(rs.getString("context_key11"));
                    contextValue.setContextKey12(rs.getString("context_key12"));
                    contextValue.setContextKey13(rs.getString("context_key13"));
                    contextValue.setContextKey14(rs.getString("context_key14"));
                    contextValue.setContextKey15(rs.getString("context_key15"));
                    contextValue.setResultValue1(rs.getString("result_value1"));
                    contextValue.setResultValue2(rs.getString("result_value2"));
                    contextValue.setResultValue3(rs.getString("result_value3"));
                    contextValue.setResultValue4(rs.getString("result_value4"));
                    contextValue.setResultValue5(rs.getString("result_value5"));
                    contextValue.setResultValue6(rs.getString("result_value6"));
                    contextValue.setResultValue7(rs.getString("result_value7"));
                    contextValue.setResultValue8(rs.getString("result_value8"));
                    contextValue.setResultValue9(rs.getString("result_value9"));
                    contextValue.setResultValue10(rs.getString("result_value10"));
                    contextValue.setResultValue11(rs.getString("result_value11"));
                    contextValue.setResultValue12(rs.getString("result_value12"));
                    contextValue.setResultValue13(rs.getString("result_value13"));
                    contextValue.setResultValue14(rs.getString("result_value14"));
                    contextValue.setResultValue15(rs.getString("result_value15"));

                    contextValue.setSequence(rowNum + 1);
                    return contextValue;
                }
            });
        }

        @Override
        public List getContextValuesForRecipeAndOperationSeq(long contextRrn, String extendWhereClause) {
            String sql = " SELECT sequence_number,status,effective_date_from,effective_date_to,ecn_rrn," + " " +
                    "context_key1, context_key2, context_key3, context_key4, context_key5, " + " context_key6, " +
                    "context_key7, context_key8, context_key9, " + " context_key10, context_key11, context_key12, " +
                    "context_key13," + " context_key14,context_key15, result_value1,result_value2,result_value3," + " " +
                    "result_value4,result_value5,result_value6, result_value7," + " result_value8,result_value9," +
                    "result_value10 " + " FROM " + DataBaseNames.CONTEXT_VALUE + "    WHERE context_rrn = " + contextRrn;

            if (extendWhereClause != null) {
                sql = sql + extendWhereClause;
            }
            sql = "select * from (select * from (" + sql + " order by sequence_number desc ) result ) where rownum=1 ";

            return jdbcTemplate.query(sql, new RowMapper<ContextValue>() {
                @Override
                public ContextValue mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ContextValue contextValue = new ContextValue();
                    contextValue.setContextRrn(contextRrn);
                    contextValue.setStatus(rs.getString("status"));
                    contextValue.setSequenceNumber(new Integer(rs.getInt("sequence_number")));

                    if (rs.getDate("effective_date_from") != null) {
                        contextValue.setEffectiveDateFrom(DateUtils.formatDate(rs.getTimestamp("effective_date_from")));
                    }

                    contextValue.setEcnRrn(new Long(rs.getLong("ecn_rrn")));

                    if (rs.getDate("effective_date_to") != null) {
                        contextValue.setEffectiveDateTo(DateUtils.formatDate(rs.getTimestamp("effective_date_to")));
                    }

                    contextValue.setContextKey1(rs.getString("context_key1"));
                    contextValue.setContextKey2(rs.getString("context_key2"));
                    contextValue.setContextKey3(rs.getString("context_key3"));
                    contextValue.setContextKey4(rs.getString("context_key4"));
                    contextValue.setContextKey5(rs.getString("context_key5"));
                    contextValue.setContextKey6(rs.getString("context_key6"));
                    contextValue.setContextKey7(rs.getString("context_key7"));
                    contextValue.setContextKey8(rs.getString("context_key8"));
                    contextValue.setContextKey9(rs.getString("context_key9"));
                    contextValue.setContextKey10(rs.getString("context_key10"));
                    contextValue.setContextKey11(rs.getString("context_key11"));
                    contextValue.setContextKey12(rs.getString("context_key12"));
                    contextValue.setContextKey13(rs.getString("context_key13"));
                    contextValue.setContextKey14(rs.getString("context_key14"));
                    contextValue.setContextKey15(rs.getString("context_key15"));
                    contextValue.setResultValue1(rs.getString("result_value1"));
                    contextValue.setResultValue2(rs.getString("result_value2"));
                    contextValue.setResultValue3(rs.getString("result_value3"));
                    contextValue.setResultValue4(rs.getString("result_value4"));
                    contextValue.setResultValue5(rs.getString("result_value5"));
                    contextValue.setResultValue6(rs.getString("result_value6"));
                    contextValue.setResultValue7(rs.getString("result_value7"));
                    contextValue.setResultValue8(rs.getString("result_value8"));
                    contextValue.setResultValue9(rs.getString("result_value9"));
                    contextValue.setResultValue10(rs.getString("result_value10"));
                    contextValue.setSequence(rowNum + 1);
                    return contextValue;
                }
            });
        }

    }