# FutureHoldContextValueDAOImpl.java

    package com.mycim.server.ctx.exec.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.MapUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.framework.utils.lang.time.DateUtils;
    import com.mycim.server.ctx.exec.dao.FutureHoldContextValueDAO;
    import com.mycim.server.wip.dao.LotQueryDAO;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.EenActionType;
    import com.mycim.valueobject.consts.VersionStatus;
    import com.mycim.valueobject.prp.FutureHold;
    import com.mycim.valueobject.wip.Lot;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Timestamp;
    import java.text.SimpleDateFormat;
    import java.util.*;

    /**
     * @author shijie.deng
     * @version 6.0.0
     * @date 2019/9/21
     **/
    @Repository
    public class FutureHoldContextValueDAOImpl implements FutureHoldContextValueDAO {
        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Autowired
        private LotQueryDAO lotQueryDAO;

        @Override
        public List<Map> getFutureHoldListByLot(long refRrn, long lotRrn) {

            String sql = "SELECT V.status,V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3, V.SEQUENCE_NUMBER, " + " V" +
                    ".RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V.RESULT_VALUE5,V.RESULT_VALUE6,V" + ".RESULT_VALUE1," +
                    " " + " V.EFFECTIVE_DATE_FROM,E.ACTION_TYPE FROM CONTEXT_VALUE V,EEN_ACTION E WHERE V" +
                    ".CONTEXT_RRN = ? AND V.CONTEXT_KEY3=?" + " AND V.RESULT_VALUE1 = E.ACTION_RRN";

            List futureHoldList = jdbcTemplate.query(sql, new Object[]{refRrn, lotRrn}, new RowMapper<Map>() {
                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    // TODO: fix me
                    Map futureHoldMap = new HashMap();
                    futureHoldMap.put("processId", "");
                    long routeRrn = NumberUtils.toLong(rs.getString("CONTEXT_KEY1"));
                    futureHoldMap.put("routeRrn", routeRrn);
                    long operationRrn = NumberUtils.toLong(rs.getString("CONTEXT_KEY2"));
                    futureHoldMap.put("operationRrn", operationRrn);
                    HashMap tempInfo = new HashMap();
                    Lot lot = lotQueryDAO.getLot(lotRrn);
                    //                tempInfo.put("operationRrn", new Long(operationRrn));
                    tempInfo.put("productRrn", lot.getProductRrn());
                    tempInfo.put("productVersion", lot.getProductVersion());
                    //                tempInfo.put("routeRrn", StringUtils.trimToEmpty(rs.getString
                    //                ("CONTEXT_KEY1")));
                    tempInfo.put("technologyRrn", lot.getProcessRrn());
                    tempInfo.put("processRrn", lot.getProcessRrn());
                    tempInfo.put("processVersion", lot.getProcessVersion());
                    tempInfo.put("recipeRrn", null);

                    tempInfo.put("lotRrn", new Long(lotRrn));

                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", lot.getLotId());
                    tempInfo.put("facilityRrn", lot.getFacilityRrn());
                    tempInfo.put("entityRrn", null);
                    tempInfo.put("productLayer", lot.getProductLayer());

                    String transByName = getTransByName(rs.getString("RESULT_VALUE2"));
                    if (transByName != null) {
                        futureHoldMap.put("userNamed", rs.getString("RESULT_VALUE2") + " " + transByName);
                    } 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"));
                    futureHoldMap.put("effectCount", rs.getString("RESULT_VALUE6"));
                    futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                    futureHoldMap.put("operationRrn", new Long(operationRrn));
                    futureHoldMap.put("routeRrn", StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY1")));

                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = DateUtils.formatDate(db_Date, DateUtils.DATE_FORMAT4DAY);
                    futureHoldMap.put("futureholdsetupdate", dbDate);
                    futureHoldMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    futureHoldMap.put("flag", rs.getString("ACTION_TYPE"));
                    futureHoldMap.put("lotStatus", rs.getString("status"));
                    Collection executedLotList = new ArrayList();
                    executedLotList = getLotExecutedForLotFH(lotRrn, routeRrn, operationRrn, rs.getString("RESULT_VALUE1"));
                    if (!executedLotList.isEmpty()) {
                        futureHoldMap.put("executedFlag", "EXECUTED");
                    } else {
                        futureHoldMap.put("executedFlag", "");
                    }
                    return futureHoldMap;
                }
            });

            return futureHoldList;
        }

        @Override
        public List<Map> getFutureHoldListByProduct(long refRrn, long productRrn, long processRrn, long lotRrn) {
            List<Map> futureHoldList = new ArrayList();
            List args = new ArrayList();
            args.add(refRrn);
            args.add(productRrn);
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3, V.CONTEXT_KEY4,V.CONTEXT_KEY5,V" +
                    ".CONTEXT_KEY6, V.SEQUENCE_NUMBER," + " V.RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V" +
                    ".RESULT_VALUE5,V.RESULT_VALUE1, " + " V.EFFECTIVE_DATE_FROM , E.ACTION_TYPE FROM CONTEXT_VALUE V " +
                    "left join " + " (SELECT * FROM NAMED_OBJECT WHERE OBJECT='WFL' AND OBJECT_SUBTYPE='PROCEDURE') R " +
                    "on TRIM(V.CONTEXT_KEY3)=R.INSTANCE_RRN left join " +
                    " (SELECT * FROM NAMED_OBJECT WHERE OBJECT='OPERATION') O on TRIM(V.CONTEXT_KEY4)" + "=O" +
                    ".INSTANCE_RRN left join EEN_ACTION E on V.RESULT_VALUE1=E.ACTION_RRN " +
                    " WHERE V.CONTEXT_RRN = ? AND V.CONTEXT_KEY1=? ";

            if (processRrn > 0) {
                sql = sql + " AND V.CONTEXT_KEY2=?";
                args.add(processRrn);
            }
            sql = sql + " ORDER BY R.INSTANCE_ID, O.INSTANCE_ID ";

            futureHoldList = jdbcTemplate.query(sql, args.toArray(), new RowMapper<Map>() {
                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    // TODO: fix me
                    Map futureHoldMap = new HashMap();
                    futureHoldMap.put("processRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY2")));
                    futureHoldMap.put("routeRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY3")));
                    futureHoldMap.put("operationRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY4")));

                    Map tempInfo = new HashMap();
                    tempInfo.put("operationRrn",
                                 new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY4")))));
                    tempInfo.put("productRrn", new Long(productRrn));
                    tempInfo.put("routeRrn", StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY3")));
                    tempInfo.put("technologyRrn",
                                 new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY2")))));
                    tempInfo.put("processRrn",
                                 new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY2")))));
                    tempInfo.put("recipeRrn", null);

                    tempInfo.put("lotRrn", null);

                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", null);
                    tempInfo.put("facilityRrn", null);
                    tempInfo.put("entityRrn", null);
                    tempInfo.put("productLayer", null);

                    futureHoldMap.putAll(tempInfo);

                    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"));
                    futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                    futureHoldMap.put("operationRrn",
                                      new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY4")))));
                    futureHoldMap.put("routeRrn", StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY3")));

                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = DateUtils.formatDate(db_Date, DateUtils.DATE_FORMAT4DAY);
                    futureHoldMap.put("futureholdsetupdate", dbDate);
                    futureHoldMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    futureHoldMap.put("flag", rs.getString("ACTION_TYPE"));
                    Collection executedLotList = new ArrayList();
                    if ((rs.getString("CONTEXT_KEY3") != null) && (rs.getString("CONTEXT_KEY4") != null) &&
                            productRrn != 0 && lotRrn != 0) {
                        executedLotList = getLotExecutedForProductFH(productRrn, Long.parseLong(
                                StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY2"))), Long.parseLong(
                                StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY4"))), lotRrn, Long.parseLong(
                                StringUtils.trimToEmpty(rs.getString("RESULT_VALUE1"))));
                        if (!executedLotList.isEmpty()) {
                            futureHoldMap.put("executedFlag", "EXECUTED");
                        } else {
                            futureHoldMap.put("executedFlag", "");
                        }
                    } else {
                        futureHoldMap.put("executedFlag", "");
                    }
                    return futureHoldMap;
                }
            });
            return futureHoldList;
        }

        @Override
        public List<Map> getFutureHoldListbyOperation(long refRrn, long operationRrn) {
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3, V.CONTEXT_KEY4,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_KEY1=?";
            Object[] args = new Object[]{refRrn, String.valueOf(operationRrn)};
            List futureHoldList = jdbcTemplate.query(sql, args, new RowMapper<Map<String, Object>>() {

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

                    Map futureHoldMap = new HashMap();
                    // futureHoldMap.put("processId",
                    // this.getInstanceId(Long.parseLong(rs.getString("CONTEXT_KEY2").trim())));
                    // futureHoldMap.put("routeId",
                    // this.getInstanceId(Long.parseLong(rs.getString("CONTEXT_KEY3").trim())));
                    // futureHoldMap.put("operationId",
                    // this.getInstanceId(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    // futureHoldMap.put("operationDesc",
                    // this.getInstanceDesc(Long.parseLong(rs.getString("CONTEXT_KEY4").trim())));
                    /*Map tempInfo = new HashMap();
                    tempInfo.put("operationRrn",
                                 new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY1")))));
                    tempInfo.put("productRrn", null);
                    tempInfo.put("routeRrn", null);
                    tempInfo.put("technologyRrn", null);
                    tempInfo.put("recipeRrn", null);

                    tempInfo.put("lotRrn", null);

                    tempInfo.put("equipmentModel", null);
                    tempInfo.put("lotId", null);
                    tempInfo.put("facilityRrn", null);
                    tempInfo.put("entityRrn", null);
                    tempInfo.put("productLayer", null);
                    String recipeString = getRecipeString(tempInfo);

                    String recipeId = parseRecipeId(recipeString);
                    futureHoldMap.put("recipeId", recipeId);*/
                    futureHoldMap.put("operationRrn",
                                      new Long(Long.parseLong(StringUtils.trimToEmpty(rs.getString("CONTEXT_KEY1")))));

                    String transName = 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"));
                    futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                    java.sql.Date db_Date = rs.getDate("EFFECTIVE_DATE_FROM");
                    String dbDate = timestampToString(db_Date);
                    futureHoldMap.put("futureholdsetupdate", dbDate);
                    futureHoldMap.put("seqNum", new Long(rs.getLong("SEQUENCE_NUMBER")));
                    return futureHoldMap;
                }
            });
            return futureHoldList;

        }

        @Override
        public List getFutureHoldLotList(Long refRrn, Long routeRrn, Long operationRrn, Long lotRrn, Long routeSeq,
                                         Long operationSeq) {
            String sql =
                    "SELECT V.STATUS,V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3,V.CONTEXT_KEY4,V.CONTEXT_KEY5, " + " V" +
                            ".RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V.RESULT_VALUE5,V.RESULT_VALUE6,V" +
                            ".RESULT_VALUE1, " + " V.EFFECTIVE_DATE_FROM,E.ACTION_TYPE FROM CONTEXT_VALUE V,EEN_ACTION E " +
                            "WHERE V" + ".CONTEXT_RRN = ? AND " + " V.CONTEXT_KEY1=? AND V.CONTEXT_KEY2=? AND V" +
                            ".CONTEXT_KEY3=? AND V.CONTEXT_KEY4=? AND V" + ".CONTEXT_KEY5=? AND V.STATUS=?" + " AND V" +
                            ".RESULT_VALUE1 =E.ACTION_RRN";

            Object[] args = new Object[]{refRrn,
                    routeRrn == null ? " " : routeRrn.toString(),
                    operationRrn == null ? " " : operationRrn.toString(),
                    lotRrn == null ? " " : lotRrn.toString(),
                    routeSeq == null ? " " : routeSeq.toString(),
                    operationSeq == null ? " " : operationSeq.toString(), VersionStatus.ACTIVE_KEY};

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

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map futureHoldMap = new HashMap();
                    futureHoldMap.put("routeRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY1")));
                    futureHoldMap.put("operationRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY2")));

                    futureHoldMap.put("routeSeq", rs.getString("CONTEXT_KEY4"));
                    futureHoldMap.put("operationSeq", rs.getString("CONTEXT_KEY5"));
                    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"));
                    futureHoldMap.put("effectCount", rs.getString("RESULT_VALUE6"));
                    futureHoldMap.put("status", rs.getString("STATUS"));
                    futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                    futureHoldMap.put("futureholdsetupdate", DateUtils.formatDate(rs.getDate("EFFECTIVE_DATE_FROM")));
                    futureHoldMap.put("flag", rs.getString("ACTION_TYPE"));
                    if (hasExecuted(lotRrn, routeRrn, operationRrn)) {
                        futureHoldMap.put("executedFlag", "EXECUTED");
                    } else {
                        futureHoldMap.put("executedFlag", "");
                    }

                    return futureHoldMap;
                }
            });
            List oldFutureHoldList = getFutureHoldLotListByOldData(refRrn, routeRrn, operationRrn, lotRrn);
            if (oldFutureHoldList.size() > 0) {
                futureHoldList = oldFutureHoldList;
            }
            return futureHoldList;
        }

        @Override
        public List getFutureHoldProductList(Long refRrn, Long routeRrn, Long operationRrn, Long lotRrn, Long productRrn,
                                             Long processRrn) {
            String sql = "SELECT V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3, V.CONTEXT_KEY4, " + " 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_KEY1=? " + " AND V.CONTEXT_KEY2=? AND V" +
                    ".CONTEXT_KEY3=? AND V.CONTEXT_KEY4=? ";

            List futureHoldList = jdbcTemplate.query(sql, new Object[]{refRrn,
                                                             productRrn == null ? " " : productRrn.toString(),
                                                             processRrn == null ? " " : processRrn.toString(),
                                                             routeRrn == null ? " " : routeRrn.toString(),
                                                             operationRrn == null ? " " : operationRrn.toString()},
                                                     new RowMapper<Map>() {
                                                         @Override
                                                         public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                                                             Map futureHoldMap = new HashMap();
                                                             futureHoldMap.put("processRrn", NumberUtils
                                                                     .toLong(rs.getString("CONTEXT_KEY2")));
                                                             futureHoldMap.put("routeRrn", NumberUtils
                                                                     .toLong(rs.getString("CONTEXT_KEY3")));
                                                             futureHoldMap.put("operationRrn", NumberUtils
                                                                     .toLong(rs.getString("CONTEXT_KEY4")));

                                                             Lot lot = new Lot();

                                                             lot.setOperationRrn(Long.parseLong(StringUtils.trimToEmpty(
                                                                     rs.getString("CONTEXT_KEY4"))));
                                                             lot.setProductRrn(productRrn);
                                                             lot.setRouteRrn(Long.parseLong(StringUtils.trimToEmpty(
                                                                     rs.getString("CONTEXT_KEY3"))));
                                                             lot.setProcessRrn(Long.parseLong(StringUtils.trimToEmpty(
                                                                     rs.getString("CONTEXT_KEY2"))));

                                                             futureHoldMap.put("recipeId", lot);

                                                             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"));
                                                             futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                                                             futureHoldMap.put("futureholdsetupdate", DateUtils
                                                                     .formatDate(rs.getDate("EFFECTIVE_DATE_FROM")));
                                                             futureHoldMap.put("flag", "FUTURE HOLD BY PRODUCT");
                                                             if ((rs.getString("CONTEXT_KEY3") != null) &&
                                                                     (rs.getString("CONTEXT_KEY4") != null) &&
                                                                     (productRrn != null) && (lotRrn != null)) {
                                                                 List executedLotList = getLotExecutedForProductFH(
                                                                         productRrn, Long.parseLong(StringUtils.trimToEmpty(
                                                                                 rs.getString("CONTEXT_KEY2"))),
                                                                         Long.parseLong(StringUtils.trimToEmpty(
                                                                                 rs.getString("CONTEXT_KEY4"))), lotRrn);
                                                                 if (!executedLotList.isEmpty()) {
                                                                     futureHoldMap.put("executedFlag", "EXECUTED");
                                                                 } else {
                                                                     futureHoldMap.put("executedFlag", "");
                                                                 }
                                                             } else {
                                                                 futureHoldMap.put("executedFlag", "");
                                                             }
                                                             return futureHoldMap;
                                                         }
                                                     });
            return futureHoldList;
        }

        @Override
        public Page getFutureHoldPage(Page page, Map<String, Object> condition) {
            String userId = MapUtils.getString(condition, "userId");
            String userName = MapUtils.getString(condition, "userName");
            String holdType = MapUtils.getString(condition, "holdType");
            List args = new ArrayList();
            StringBuilder sql = new StringBuilder();
            sql.append(" select v.context_rrn,e.action_type,v.result_value2 userid, ");
            sql.append(" v.status,v.context_key1,v.context_key2,v.context_key3,v.context_key4, ");
            sql.append(" v.sequence_number,v.result_value3,v.result_value4, ");
            sql.append(" v.result_value5,v.result_value6,v.result_value1,v.result_value2, ");
            sql.append(" v.effective_date_from from context_value v,named_object n,een_action e, ");
            sql.append(" user_profile u where v.result_value1 = e.action_rrn  ");
            sql.append(" and v.result_value2 = n.instance_id and u.user_rrn = n.instance_rrn");
            if (StringUtils.isNotBlank(userId)) {
                sql.append(" and v.result_value2 = ?");
                args.add(userId);
            }
            if (StringUtils.isNotBlank(userName)) {
                sql.append(" and u.user_name = ? ");
                args.add(userName);
            }
            if (StringUtils.isNotBlank(holdType)) {
                sql.append(" and v.context_rrn in ( select instance_rrn from ");
                sql.append(" named_object where instance_id in ('" + holdType + "') )");
            }

            sql.append(" order by v.effective_date_from desc ");
            return jdbcTemplate
                    .queryForPage(page, sql.toString(), args.toArray(), (RowMapper<Map<String, Object>>) (rs, rowNum) -> {
                        Map futureHoldMap = new HashMap();
                        String actionType = rs.getString("action_type");
                        if (StringUtils.equalsIgnoreCase(EenActionType.FUTURE_HOLD_LOT_KEY, actionType)) {
                            long routeRrn = NumberUtils.toLong(rs.getString("context_key1"));
                            long operationRrn = NumberUtils.toLong(rs.getString("context_key2"));
                            long lotRrn = NumberUtils.toLong(rs.getString("context_key3"));
                            futureHoldMap.put("routeRrn", routeRrn);
                            futureHoldMap.put("operationRrn", operationRrn);
                            futureHoldMap.put("lotRrn", lotRrn);
                            List<Long> executedLotList = getLotExecutedForLotFH(lotRrn, routeRrn, operationRrn,
                                                                                rs.getString("result_value1"));
                            if (!executedLotList.isEmpty()) {
                                futureHoldMap.put("executedFlag", "EXECUTED");
                            } else {
                                futureHoldMap.put("executedFlag", "");
                            }

                        } else if (StringUtils.equalsIgnoreCase(EenActionType.FUTURE_HOLD_PRODUCT_KEY, actionType)) {
                            long productRrn = NumberUtils.toLong(rs.getString("context_key1"));
                            long processRrn = NumberUtils.toLong(rs.getString("context_key2"));
                            long routeRrn = NumberUtils.toLong(rs.getString("context_key3"));
                            long operationRrn = NumberUtils.toLong(rs.getString("context_key4"));
                            futureHoldMap.put("productRrn", productRrn);
                            futureHoldMap.put("processRrn", processRrn);
                            futureHoldMap.put("routeRrn", routeRrn);
                            futureHoldMap.put("operationRrn", operationRrn);
                        }
                        String transByName = getTransByName(rs.getString("result_value2"));
                        futureHoldMap.put("userNamed", transByName);
                        futureHoldMap.put("userId", 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"));
                        futureHoldMap.put("effectCount", rs.getString("result_value6"));
                        futureHoldMap.put("actionRrn", rs.getString("result_value1"));
                        Date db_Date = rs.getDate("effective_date_from");
                        String dbDate = DateUtils.formatDate(db_Date, DateUtils.DATE_FORMAT4DAY);
                        futureHoldMap.put("futureholdsetupdate", dbDate);
                        futureHoldMap.put("seqNum", new Long(rs.getLong("sequence_number")));
                        futureHoldMap.put("flag", actionType);
                        futureHoldMap.put("lotStatus", rs.getString("status"));
                        return futureHoldMap;
                    });
        }

        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;
        }

        private List<Long> getLotExecutedForProductFH(long productRrn, long processRrn, long operationRrn, long lotRrn) {
            Collection executedLotList = new ArrayList();
            String sql = "SELECT L.LOT_RRN FROM LOT_TRANS_HISTORY L, LOT_STEP_HISTORY H, TRANS_REASON R," + "(SELECT V" +
                    ".CONTEXT_KEY1 AS PRODUCT_RRN, V.CONTEXT_KEY2 AS PROCESS_RRN,V.CONTEXT_KEY3" + " AS ROUTE_RRN, " + "V" +
                    ".CONTEXT_KEY4 AS OPERATION_RRN, V.SEQUENCE_NUMBER, V.RESULT_VALUE2, V" + ".RESULT_VALUE3 AS " +
                    "HOLDGROUP," + " V.RESULT_VALUE4 AS HOLDCODE, V.RESULT_VALUE5 AS HOLDREASON, V.RESULT_VALUE1 AS " +
                    "ACTIONRRN  " + "FROM CONTEXT_VALUE V WHERE V.CONTEXT_RRN = (SELECT L.INSTANCE_RRN FROM NAMED_OBJECT" +
                    " L " + "WHERE L.INSTANCE_ID='EEN_CONTEXT_HOLDPRODUCT' AND L.NAMED_SPACE='MYCIM2') AND V" +
                    ".CONTEXT_KEY1 = ?)C " + "WHERE L.LOT_RRN = H.LOT_RRN AND L.TRANS_ID = 'HOLD' AND H.STEP_SEQUENCE = L" +
                    ".STEP_SEQUENCE " + "AND L.TRANS_RRN = R.TRANS_RRN AND R.REASON LIKE '%(FH)%' AND H.PRODUCT_RRN = C" +
                    ".PRODUCT_RRN " + "AND H.PROCESS_RRN = C.PROCESS_RRN AND H.OPERATION_RRN = C.OPERATION_RRN " +
                    "AND C.ACTIONRRN = L.ACTION_RRN "
                    //+ "AND R.REASON_CODE = C.HOLDCODE AND R.REASON='(FH)'||C.HOLDREASON "
                    + "AND H.PRODUCT_RRN = ? " + "AND H.PROCESS_RRN = ? AND H.OPERATION_RRN = ? AND L.LOT_RRN = ?";
            List<Long> executedLotRrns = jdbcTemplate
                    .query(sql, new Object[]{String.valueOf(productRrn), productRrn, processRrn, operationRrn, lotRrn},
                           Long.class);
            return executedLotRrns;

        }

        private List<Long> getLotExecutedForProductFH(long productRrn, long processRrn, long operationRrn, long lotRrn,
                                                      long actionRrn) {
            Collection executedLotList = new ArrayList();
            String sql = "SELECT L.LOT_RRN FROM LOT_TRANS_HISTORY L, LOT_STEP_HISTORY H, TRANS_REASON R," + "(SELECT V" +
                    ".CONTEXT_KEY1 AS PRODUCT_RRN, V.CONTEXT_KEY2 AS PROCESS_RRN,V.CONTEXT_KEY3" + " AS ROUTE_RRN, " + "V" +
                    ".CONTEXT_KEY4 AS OPERATION_RRN, V.SEQUENCE_NUMBER, V.RESULT_VALUE2, V" + ".RESULT_VALUE3 AS " +
                    "HOLDGROUP," + " V.RESULT_VALUE4 AS HOLDCODE, V.RESULT_VALUE5 AS HOLDREASON, V.RESULT_VALUE1 AS " +
                    "ACTIONRRN  " + "FROM CONTEXT_VALUE V WHERE V.CONTEXT_RRN = (SELECT L.INSTANCE_RRN FROM NAMED_OBJECT" +
                    " L " + "WHERE L.INSTANCE_ID='EEN_CONTEXT_HOLDPRODUCT' AND L.NAMED_SPACE='MYCIM2') AND V" +
                    ".CONTEXT_KEY1 = ?)C " + "WHERE L.LOT_RRN = H.LOT_RRN AND L.TRANS_ID = 'HOLD' AND H.STEP_SEQUENCE = L" +
                    ".STEP_SEQUENCE " + "AND L.TRANS_RRN = R.TRANS_RRN AND R.REASON LIKE '%(FH)%' AND H.PRODUCT_RRN = C" +
                    ".PRODUCT_RRN AND H.PROCESS_RRN = C.PROCESS_RRN " +
                    " AND H.OPERATION_RRN = C.OPERATION_RRN AND C.ACTIONRRN = L.ACTION_RRN "
                    //+ "AND R.REASON_CODE = C.HOLDCODE AND R.REASON='(FH)'||C.HOLDREASON "
                    + "AND H.PRODUCT_RRN = ? AND H.PROCESS_RRN = ? AND H.OPERATION_RRN = ? " + "AND L.LOT_RRN = ? AND C" +
                    ".ACTIONRRN = L.ACTION_RRN  AND L.ACTION_RRN = ?";
            List<Long> executedLotRrns = jdbcTemplate
                    .query(sql, new Object[]{productRrn, productRrn, processRrn, operationRrn, lotRrn, actionRrn},
                           Long.class);
            return executedLotRrns;

        }

        private String getTransByName(String transBy) {
            String transByName = "";
            String sql =
                    "select t.user_name from user_profile t,named_object o " + " where t.user_rrn=o.instance_rrn " + " " +
                            "and o.instance_id='" + transBy + "' and o.object='USER'";
            List<String> results = jdbcTemplate.query(sql, String.class);
            if (results != null && results.size() > 0) {
                transByName = results.get(0);
            }
            return transByName;
        }

        private List<Long> getLotExecutedForLotFH(long lotRrn, long routeRrn, long operationRrn, String actionRrn) {
            List<Long> executedLotList = new ArrayList<Long>();
            String sql = "SELECT L.LOT_RRN  FROM LOT_TRANS_HISTORY L,LOT_STEP_HISTORY H " + " WHERE L.LOT_RRN = H.LOT_RRN" +
                    " " + " AND L.TRANS_ID = 'HOLD' AND H.STEP_SEQUENCE = L.STEP_SEQUENCE AND L.LOT_RRN =? " + " AND H" +
                    ".OPERATION_RRN= ? AND H.PROCESS_STEP_VERSION LIKE ?" + " AND L.ACTION_RRN = ?";

            List<Long> list = jdbcTemplate
                    .query(sql, new Object[]{lotRrn, operationRrn, "%" + routeRrn + "%", actionRrn}, Long.class);
            if (list != null) {
                executedLotList = list;
            }
            return executedLotList;
        }

        private List getFutureHoldLotListByOldData(Long refRrn, Long routeRrn, Long operationRrn, Long lotRrn) {
            String routeSeq = " ";
            String operationSeq = " ";

            String sql =
                    "SELECT V.STATUS,V.CONTEXT_KEY1,V.CONTEXT_KEY2,V.CONTEXT_KEY3,V.CONTEXT_KEY4,V.CONTEXT_KEY5, " + " V" +
                            ".RESULT_VALUE2,V.RESULT_VALUE3,V.RESULT_VALUE4,V.RESULT_VALUE5,V.RESULT_VALUE6,V" +
                            ".RESULT_VALUE1, " + " V.EFFECTIVE_DATE_FROM,E.ACTION_TYPE FROM CONTEXT_VALUE V,EEN_ACTION E " +
                            "WHERE V" + ".CONTEXT_RRN = ? AND " + " V.CONTEXT_KEY1=? AND V.CONTEXT_KEY2=? AND V" +
                            ".CONTEXT_KEY3=? AND " + "V.CONTEXT_KEY4=? AND V" + ".CONTEXT_KEY5=? AND V.STATUS=?" + " AND " +
                            "V.RESULT_VALUE1 = E.ACTION_RRN";

            Object[] args = new Object[]{refRrn, routeRrn, operationRrn, lotRrn, routeSeq, operationSeq,
                    VersionStatus.ACTIVE_KEY};
            List oldFutureHoldList = jdbcTemplate.query(sql, args, new RowMapper<Map>() {

                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map futureHoldMap = new HashMap();
                    futureHoldMap.put("routeRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY1")));
                    futureHoldMap.put("operationRrn", NumberUtils.toLong(rs.getString("CONTEXT_KEY2")));
                    futureHoldMap.put("routeSeq", rs.getString("CONTEXT_KEY4"));
                    futureHoldMap.put("operationSeq", rs.getString("CONTEXT_KEY5"));
                    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"));
                    futureHoldMap.put("effectCount", rs.getString("RESULT_VALUE6"));
                    futureHoldMap.put("status", rs.getString("STATUS"));
                    futureHoldMap.put("actionRrn", rs.getString("RESULT_VALUE1"));
                    futureHoldMap.put("futureholdsetupdate", DateUtils.formatDate(rs.getDate("EFFECTIVE_DATE_FROM")));
                    futureHoldMap.put("flag", rs.getString("ACTION_TYPE"));
                    if (hasExecuted(lotRrn, routeRrn, operationRrn)) {
                        futureHoldMap.put("executedFlag", "EXECUTED");
                    } else {
                        futureHoldMap.put("executedFlag", "");
                    }

                    return futureHoldMap;
                }
            });
            return oldFutureHoldList;
        }

        private boolean hasExecuted(long lotRrn, long routeRrn, long operationRrn) {
            String sql = "select count(l.lot_rrn)  from lot_trans_history l,lot_step_history h where l.lot_rrn = h" +
                    ".lot_rrn " + " and l.trans_id = 'HOLD' and h.step_sequence = l.step_sequence and l.lot_rrn =? " +
                    "and h.operation_rrn=? and h.process_step_version like ?";

            return jdbcTemplate.queryForObject(sql, new Object[]{lotRrn, operationRrn, "%" + routeRrn + "%"}, int.class) >
                    0;
        }

        @Override
        public List<FutureHold> getAllFutureHoldListByLot(long contextRrn, long lotRrn) {
            String sql = " SELECT STATUS,CONTEXT_KEY1,CONTEXT_KEY2,CONTEXT_KEY3,CONTEXT_KEY4,CONTEXT_KEY5,RESULT_VALUE2, " +
                    " RESULT_VALUE3,RESULT_VALUE4,RESULT_VALUE5,RESULT_VALUE6,RESULT_VALUE1,EFFECTIVE_DATE_FROM " +
                    " FROM "+ DataBaseNames.CONTEXT_VALUE +" WHERE CONTEXT_RRN = ? AND CONTEXT_KEY3 = ? AND STATUS='"+ VersionStatus.ACTIVE_KEY +"' " ;

            return jdbcTemplate.query(sql, new Object[]{contextRrn, StringUtils.toString(lotRrn)}, new RowMapper<FutureHold>() {
                @Override
                public FutureHold mapRow(ResultSet rs, int rowNum) throws SQLException {
                    FutureHold futureHold = new FutureHold();
                    futureHold.setStatus(rs.getString("STATUS"));
                    futureHold.setRouteRrn(rs.getLong("CONTEXT_KEY1"));
                    futureHold.setOperationRrn(rs.getLong("CONTEXT_KEY2"));
                    futureHold.setLotRrn(rs.getLong("CONTEXT_KEY3"));
                    futureHold.setCreateUserId(rs.getString("RESULT_VALUE2"));
                    futureHold.setHoldGroup(rs.getString("RESULT_VALUE3"));
                    futureHold.setHoldCode(rs.getString("RESULT_VALUE4"));
                    futureHold.setHoldReason(rs.getString("RESULT_VALUE5"));
                    futureHold.setEffictiveCount(rs.getLong("RESULT_VALUE6"));
                    futureHold.setCreateTime(rs.getTimestamp("EFFECTIVE_DATE_FROM"));
                    futureHold.setFlag(VersionStatus.ACTIVE_KEY);
                    return futureHold;
                }
            });
        }

        @Override
        public List<FutureHold> getAllFutureHoldListByProduct(long contextRrn, Long productRrn, Long processRrn) {
            String sql = " SELECT CONTEXT_KEY1,CONTEXT_KEY2,CONTEXT_KEY3,CONTEXT_KEY4,RESULT_VALUE2, " +
                    " RESULT_VALUE3,RESULT_VALUE4,RESULT_VALUE5,RESULT_VALUE1,EFFECTIVE_DATE_FROM FROM "+
                    DataBaseNames.CONTEXT_VALUE + " WHERE CONTEXT_RRN = ? AND CONTEXT_KEY1=? AND CONTEXT_KEY2=? ";
            return jdbcTemplate.query(sql, new Object[]{contextRrn, StringUtils.toString(productRrn),StringUtils.toString(processRrn)}, new RowMapper<FutureHold>() {
                @Override
                public FutureHold mapRow(ResultSet rs, int rowNum) throws SQLException {
                    FutureHold futureHold = new FutureHold();
                    futureHold.setProductRrn(rs.getLong("CONTEXT_KEY1"));
                    futureHold.setProcessRrn(rs.getLong("CONTEXT_KEY2"));
                    futureHold.setRouteRrn(rs.getLong("CONTEXT_KEY3"));
                    futureHold.setOperationRrn(rs.getLong("CONTEXT_KEY4"));
                    futureHold.setCreateUserId(rs.getString("RESULT_VALUE2"));
                    futureHold.setHoldGroup(rs.getString("RESULT_VALUE3"));
                    futureHold.setHoldCode(rs.getString("RESULT_VALUE4"));
                    futureHold.setHoldReason(rs.getString("RESULT_VALUE5"));
                    futureHold.setCreateTime(rs.getTimestamp("EFFECTIVE_DATE_FROM"));
                    futureHold.setFlag(VersionStatus.ACTIVE_KEY);
                    return futureHold;
                }
            });
        }
    }