# InstructionContextValueManagerDaoImpl.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.lang.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.ctx.exec.dao.InstructionContextValueManagerDao;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;

    /**
     * InstructionContextValueManagerDaoImpl
     *
     * @author kang.zhang
     * @version 6.0.0
     * @date 2019-10-8
     **/
    @Repository
    public class InstructionContextValueManagerDaoImpl implements InstructionContextValueManagerDao {
        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public List getInstructionByProduct(long refRrn, long productRrn) {
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,getInstanceId(to_number(nvl(decode(v.CONTEXT_KEY2,' " +
                    "',null,v.CONTEXT_KEY2),'0'))) as process_id, " + "V.CONTEXT_KEY3, getInstanceId(to_number(nvl(decode" +
                    "(v.CONTEXT_KEY3,' ',null,v" + ".CONTEXT_KEY3),'0'))) as route_id, " + "V.CONTEXT_KEY4,getInstanceId" +
                    "(to_number(nvl(decode(v.CONTEXT_KEY4,' ',null,v" + ".CONTEXT_KEY4),'0'))) as operation_id, " + "V" +
                    ".CONTEXT_KEY7,V.SEQUENCE_NUMBER, V.RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V" +
                    ".RESULT_VALUE5,V.RESULT_VALUE1, " + "V.EFFECTIVE_DATE_FROM FROM CONTEXT_VALUE V WHERE (V" +
                    ".CONTEXT_KEY6=' ' or V" + ".CONTEXT_KEY6 is null) AND V.CONTEXT_RRN = ? AND V.CONTEXT_KEY1=? ";

            Object[] args = new Object[]{refRrn, String.valueOf(productRrn)};
            List instructionList = jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

                    Map<String, Object> instructionMap = new HashMap();
                    instructionMap.put("processId", rs.getString("process_id"));
                    instructionMap.put("routeId", rs.getString("route_id"));
                    instructionMap.put("operationId", rs.getString("operation_id"));
                    Map tempInfo = new HashMap();
                    tempInfo.put("operationRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    tempInfo.put("productRrn", rs.getLong("CONTEXT_KEY1"));
                    tempInfo.put("routeRrn", rs.getString("CONTEXT_KEY3").trim());
                    instructionMap.put("actionPoint", rs.getString("CONTEXT_KEY7").trim());
                    tempInfo.put("technologyRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY2").trim())));
                    tempInfo.put("recipeRrn", null);
                    tempInfo.put("lotRrn", null);
                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", null);
                    tempInfo.put("facilityRrn", null);
                    tempInfo.put("entityRrn", null);
                    instructionMap.put("instruction", rs.getString("RESULT_VALUE1"));
                    instructionMap.put("userName", rs.getString("RESULT_VALUE2"));
                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = timestampToString(db_Date);
                    instructionMap.put("futureholdsetupdate", dbDate);
                    instructionMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    return instructionMap;
                }
            });

            return instructionList;
        }

        @Override
        public List getInstructionByLot(long refRrn, long productRrn, long lotRrn) {
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,getInstanceId(NVL(decode(v.CONTEXT_KEY2,' " + "',null,v" +
                    ".CONTEXT_KEY2),'0')) as process_id, V.CONTEXT_KEY3, getInstanceId" + "(NVL(decode(v.CONTEXT_KEY3,' " +
                    "',null,v.CONTEXT_KEY3),'0')) as route_id," + " V.CONTEXT_KEY4,getInstanceId(NVL(decode(v" +
                    ".CONTEXT_KEY4,' ',null,v" + ".CONTEXT_KEY4),'0')) as operation_id, n.instance_desc as " +
                    "operation_desc, V" + ".CONTEXT_KEY7,V.SEQUENCE_NUMBER, V.RESULT_VALUE2,V.RESULT_VALUE3,V" +
                    ".RESULT_VALUE4,V" + ".RESULT_VALUE5,V.RESULT_VALUE1, V.EFFECTIVE_DATE_FROM FROM CONTEXT_VALUE V left" +
                    " " + "join named_object n  on V.CONTEXT_KEY4=n.instance_rrn WHERE (V.CONTEXT_KEY6='" + " ' or  V" +
                    ".CONTEXT_KEY6 is null OR V.CONTEXT_KEY6=?)   AND V" + ".CONTEXT_RRN = ? AND V.CONTEXT_KEY1=?";
            Object[] args = new Object[]{String.valueOf(lotRrn), refRrn, String.valueOf(productRrn)};

            List instructionList = jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

                    Map instructionMap = new HashMap();
                    instructionMap.put("processId", rs.getString("process_id"));
                    instructionMap.put("routeId", rs.getString("route_id"));
                    instructionMap.put("operationId", rs.getString("operation_id"));
                    instructionMap.put("operationDesc", rs.getString("operation_desc"));
                    Map tempInfo = new HashMap();
                    tempInfo.put("operationRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    tempInfo.put("productRrn", rs.getLong("CONTEXT_KEY1"));
                    tempInfo.put("routeRrn", rs.getString("CONTEXT_KEY3").trim());
                    instructionMap.put("actionPoint", rs.getString("CONTEXT_KEY7").trim());
                    tempInfo.put("technologyRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY2").trim())));
                    tempInfo.put("recipeRrn", null);
                    tempInfo.put("lotRrn", null);
                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", null);
                    tempInfo.put("facilityRrn", null);
                    tempInfo.put("entityRrn", null);
                    instructionMap.put("instruction", rs.getString("RESULT_VALUE1"));
                    instructionMap.put("userName", rs.getString("RESULT_VALUE2"));
                    instructionMap.put("operationRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    instructionMap.put("routeRrn", rs.getString("CONTEXT_KEY3").trim());
                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = timestampToString(db_Date);
                    instructionMap.put("futureholdsetupdate", dbDate);
                    instructionMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    return instructionMap;
                }
            });

            return instructionList;
        }

        @Override
        public List getInstructionByProcess(long refRrn, long processRrn) {
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3, V.CONTEXT_KEY4, V.CONTEXT_KEY7,V" +
                    ".SEQUENCE_NUMBER, " + " V.RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V.RESULT_VALUE5,V" +
                    ".RESULT_VALUE1, " + " V.EFFECTIVE_DATE_FROM FROM CONTEXT_VALUE V WHERE V.CONTEXT_RRN = ? AND V" +
                    ".CONTEXT_KEY2=?";
            Object[] args = new Object[]{refRrn, String.valueOf(processRrn)};
            List instructionList = jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

                    Map instructionMap = new HashMap();
                    instructionMap.put("processRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY2")));
                    instructionMap.put("routeRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY3")));
                    instructionMap.put("operationRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY4")));

                    /*Map tempInfo = new HashMap();
                    tempInfo.put("operationRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    tempInfo.put("productRrn", "");
                    tempInfo.put("routeRrn", rs.getString("CONTEXT_KEY3").trim());*/
                    instructionMap.put("actionPoint", rs.getString("CONTEXT_KEY7").trim());
                    /*tempInfo.put("technologyRrn", new Long(Long.parseLong(rs.getString("CONTEXT_KEY2").trim()
                    )));
                    tempInfo.put("recipeRrn", null);

                    tempInfo.put("lotRrn", null);

                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", null);
                    tempInfo.put("facilityRrn", null);
                    tempInfo.put("entityRrn", null);*/
                    // String recipeString = getRecipeString(tempInfo);
                    //
                    // String recipeId = parseRecipeId(recipeString);
                    // instructionMap.put("recipeId", recipeId);

                    // String transName = this.getTransByName(rs.getString("RESULT_VALUE2"));
                    // if (transName != null) {
                    // futureHoldMap.put("userNamed", rs.getString("RESULT_VALUE2") + " " + transName);
                    // } else {
                    // futureHoldMap.put("userNamed", rs.getString("RESULT_VALUE2"));
                    // }
                    // futureHoldMap.put("holdGroup", rs.getString("RESULT_VALUE3"));
                    // futureHoldMap.put("holdCode", rs.getString("RESULT_VALUE4"));
                    // futureHoldMap.put("holdReason", rs.getString("RESULT_VALUE5"));
                    instructionMap.put("instruction", rs.getString("RESULT_VALUE1"));
                    instructionMap.put("userName", rs.getString("RESULT_VALUE2"));
                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = timestampToString(db_Date);
                    instructionMap.put("futureholdsetupdate", dbDate);
                    instructionMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    return instructionMap;
                }
            });

            return instructionList;
        }

        public String timestampToString(Date date) {
            String dateString = "";

            if (date != null) {
                SimpleDateFormat formatter = new SimpleDateFormat(DateUtils.DATE_FORMAT4DAY);

                // formatter= new SimpleDateFormat("dd/MM/yyyy");
                dateString = formatter.format(date);
            }

            return dateString;
        }

    }