# EntityCounterDAOImpl.java

    package com.mycim.server.pms.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.MiscUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.server.pms.dao.EntityCounterDAO;
    import com.mycim.server.pms.dao.mapper.EntityCounterRowMapper;
    import com.mycim.valueobject.bas.TransactionLog;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.ems.EntityCounter;
    import com.mycim.valueobject.ems.EntityCounterType;
    import org.springframework.jdbc.support.rowset.SqlRowSet;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author Johnson.Wang
     * @version 6.0.0
     * @date 2019/9/25
     **/
    @Repository
    public class EntityCounterDAOImpl implements EntityCounterDAO {

        private final JdbcTemplate jdbcTemplate;

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

        @Override
        public List<EntityCounter> getEntityCountersOfEntity(long entityRrn) {
            String sql = "SELECT ENTITY_RRN, INSTANCE_ID AS ENTITY_ID, INSTANCE_DESC, COUNTER_TYPE, COUNTER_DESC, " +
                    "TARGET_COUNT, " + "CHECKLIST_RRN, ALARM_ENABLE_FLAG, ALARM_ID, WARNING_COUNT, " +
                    "WARN_ALARM_ENABLE_FLAG, " + "WARN_ALARM_ID, ACTUAL_COUNT " + "FROM " +
                    DataBaseNames.ENTITY_COUNTER_VALUE + ", " + DataBaseNames.NAMEDOBJECT +
                    " WHERE ENTITY_RRN = INSTANCE_RRN " + " AND ENTITY_RRN = ? " + " ORDER BY COUNTER_TYPE ";

            return jdbcTemplate.query(sql, new Object[]{entityRrn}, new EntityCounterRowMapper());

        }

        @Override
        public List<EntityCounterType> getEntityCounterTypesOfEntity(long entityRrn) {

            String sql = "SELECT ENTITY_RRN,EVENT_RRN,RECIPE_RRN," + "COUNTER_TYPE,QUANTITY,BASIS_CODE" + " FROM " +
                    DataBaseNames.ENTITY_COUNTER + " WHERE ENTITY_RRN = ?" + " ORDER BY EVENT_RRN,RECIPE_RRN,COUNTER_TYPE";

            return jdbcTemplate.query(sql, new Object[]{entityRrn}, (RowMapper<EntityCounterType>) (rs, rowNum) -> {
                EntityCounterType entityCounterType = new EntityCounterType();
                entityCounterType.setEventRrn(rs.getLong("EVENT_RRN"));
                entityCounterType.setRecipeRrn(rs.getLong("RECIPE_RRN"));
                entityCounterType.setCounterType(rs.getString("COUNTER_TYPE"));
                entityCounterType.setQuantity(rs.getLong("QUANTITY"));
                entityCounterType.setBasisCode(rs.getString("BASIS_CODE"));
                entityCounterType.setSequenceNumber((long) rowNum);
                return entityCounterType;
            });
        }

        @Override
        public List<Map<String, Object>> getChecklistEntityCounterLists(long checklistRrn) {
            String sql =
                    "SELECT DISTINCT N.INSTANCE_RRN,N.INSTANCE_ID,N.INSTANCE_DESC,EC.COUNTER_TYPE, EC" + ".COUNTER_DESC " +
                            " FROM ENTITY_COUNTER_VALUE EC,NAMED_OBJECT N " + " WHERE EC.CHECKLIST_RRN=? " +
                            " AND  EC.ENTITY_RRN=N.INSTANCE_RRN" + " AND N.OBJECT=\'ENTITY\'" +
                            " ORDER BY N.INSTANCE_ID, EC" + ".COUNTER_TYPE";
            return jdbcTemplate.query(sql, new RowMapper<Map<String, Object>>() {
                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> m = new HashMap<>();
                    m.put("equipmentId", rs.getString("INSTANCE_ID"));
                    m.put("equipmentDesc", rs.getString("INSTANCE_DESC"));
                    m.put("equipmentRrn", rs.getLong("INSTANCE_RRN"));
                    m.put("counterType", rs.getString("COUNTER_TYPE"));
                    m.put("counterDesc", rs.getString("COUNTER_DESC"));
                    return m;
                }
            }, checklistRrn);

        }

        @Override
        public long insertEntityCounter(EntityCounter entityCounter, TransactionLog transactionLog) {
            long instanceRrn = entityCounter.getEntityRrn();

            String sql = "SELECT * FROM " + DataBaseNames.ENTITY_COUNTER_VALUE + " WHERE ENTITY_RRN = " + instanceRrn +
                    " AND COUNTER_TYPE = " + MiscUtils.parseSQL(entityCounter.getCounterType());

            SqlRowSet sr = jdbcTemplate.queryForRowSet(sql);
            if (sr.next()) {
                return 0;
            }

            Object[] obj = {entityCounter.getCounterType(), entityCounter.getCounterDesc(),
                    entityCounter.getTargetCount(), entityCounter.getAlarmEnableFlag(), entityCounter.getAlarmId(),
                    entityCounter.getWarningCount(), entityCounter.getChecklistRrn(),
                    entityCounter.getWarnAlarmEnableFlag(), entityCounter.getWarnAlarmId(),
                    entityCounter.getActualCount(), entityCounter.getAttri1(), entityCounter.getAttri2(),
                    entityCounter.getAttri3(), entityCounter.getAttri4()};

            // insert into entityCounter table
            sql = "INSERT INTO " + DataBaseNames.ENTITY_COUNTER_VALUE + "(ENTITY_RRN,COUNTER_TYPE,COUNTER_DESC," +
                    "TARGET_COUNT," + "ALARM_ENABLE_FLAG,ALARM_ID,WARNING_COUNT,CHECKLIST_RRN," +
                    "WARN_ALARM_ENABLE_FLAG,WARN_ALARM_ID,ACTUAL_COUNT,ATTRI_1,ATTRI_2,ATTRI_3,ATTRI_4)" + " VALUES( " +
                    instanceRrn + "," + MiscUtils.parseSQL(obj) + ")";

            jdbcTemplate.update(sql);
            insertEntityCounterHistory(entityCounter, transactionLog.getTransRrn());
            return instanceRrn;
        }

        @Override
        public void updateEntityCounter(EntityCounter entityCounter, TransactionLog transactionLog) {
            insertEntityCounterHistory(entityCounter, transactionLog.getTransRrn());

            String sql = "UPDATE " + DataBaseNames.ENTITY_COUNTER_VALUE + " SET TARGET_COUNT = " +
                    MiscUtils.parseSQL(entityCounter.getTargetCount()) + "," + " CHECKLIST_RRN = " +
                    MiscUtils.parseSQL(entityCounter.getChecklistRrn()) + "," + " COUNTER_DESC = " +
                    MiscUtils.parseSQL(entityCounter.getCounterDesc()) + "," + " ALARM_ENABLE_FLAG = " +
                    MiscUtils.parseSQL(entityCounter.getAlarmEnableFlag()) + "," + " ALARM_ID = " +
                    MiscUtils.parseSQL(entityCounter.getAlarmId()) + "," + " WARNING_COUNT = " +
                    MiscUtils.parseSQL(entityCounter.getWarningCount()) + "," + " WARN_ALARM_ENABLE_FLAG = " +
                    MiscUtils.parseSQL(entityCounter.getWarnAlarmEnableFlag()) + "," + " WARN_ALARM_ID = " +
                    MiscUtils.parseSQL(entityCounter.getWarnAlarmId()) + "," + " ATTRI_1 = " +
                    MiscUtils.parseSQL(entityCounter.getAttri1()) + "," + " ATTRI_2 = " +
                    MiscUtils.parseSQL(entityCounter.getAttri2()) + "," + " ATTRI_3 = " +
                    MiscUtils.parseSQL(entityCounter.getAttri3()) + "," + " ATTRI_4 = " +
                    MiscUtils.parseSQL(entityCounter.getAttri4()) +
                    // delete by ben ,when modify do not to update ACTUAL_COUNT
                    // "," + " ACTUAL_COUNT = " +
                    // MiscUtils.parseSQL(entityCounter.getActualCount()) +
                    " WHERE ENTITY_RRN = " + entityCounter.getEntityRrn() + " AND COUNTER_TYPE = " +
                    MiscUtils.parseSQL(entityCounter.getCounterType());

            jdbcTemplate.update(sql);

        }

        @Override
        public void deleteEntityCounter(EntityCounter entityCounter, TransactionLog transactionLog) {
            insertEntityCounterHistory(entityCounter, transactionLog.getTransRrn());
            String sql = "DELETE FROM " + DataBaseNames.ENTITY_COUNTER_VALUE + " WHERE ENTITY_RRN = " +
                    entityCounter.getEntityRrn() + " AND COUNTER_TYPE = " +
                    MiscUtils.parseSQL(entityCounter.getCounterType());
            jdbcTemplate.update(sql);
        }

        @Override
        public EntityCounter getActualCounter(long entityRrn, String counterType) {
            String sql = " SELECT DISTINCT actual_count,warning_count,WARN_ALARM_ID," + " warn_alarm_enable_flag," +
                    "target_count,ALARM_ID,alarm_enable_flag " + " FROM  " + DataBaseNames.ENTITY_COUNTER_VALUE + " WHERE" +
                    " entity_rrn =" + entityRrn + " AND counter_type= " + MiscUtils.parseSQL(counterType);

            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{}, new RowMapper<EntityCounter>() {
                @Override
                public EntityCounter mapRow(ResultSet rs, int rowNum) throws SQLException {
                    EntityCounter entityCounter = new EntityCounter();
                    entityCounter.setActualCount(rs.getLong("actual_count"));
                    entityCounter.setWarningCount(rs.getLong("warning_count"));
                    entityCounter.setWarnAlarmId(rs.getString("WARN_ALARM_ID"));
                    entityCounter.setWarnAlarmEnableFlag(rs.getString("warn_alarm_enable_flag"));
                    entityCounter.setTargetCount(rs.getLong("target_count"));
                    entityCounter.setAlarmId(rs.getString("ALARM_ID"));
                    entityCounter.setAlarmEnableFlag(rs.getString("alarm_enable_flag"));
                    entityCounter.setCounterType(counterType);
                    return entityCounter;
                }
            });
        }

        @Override
        public void updateActualValue(long entityRrn, String type, double count) {
            String sql = "UPDATE entity_counter_value SET actual_count=NVL(actual_count,0) + ? " + " WHERE entity_rrn= ? " +
                    "AND counter_type= ?";
            jdbcTemplate.update(sql, count, entityRrn, type);
        }

        @Override
        public void insertEntityCounterHistory(EntityCounter entityCounter, long transRrn) {
            String sql_h = "INSERT INTO " + DataBaseNames.ENTITY_COUNTER_VALUE_HISTORY + "(ENTITY_RRN,COUNTER_TYPE," +
                    "COUNTER_DESC,TARGET_COUNT," + "ALARM_ENABLE_FLAG,ALARM_ID,WARNING_COUNT,CHECKLIST_RRN," +
                    "WARN_ALARM_ENABLE_FLAG,WARN_ALARM_ID,ACTUAL_COUNT,ATTRI_1,ATTRI_2,ATTRI_3," + "ATTRI_4,TRANS_RRN)" +
                    " SELECT ENTITY_RRN,COUNTER_TYPE,COUNTER_DESC,TARGET_COUNT," +
                    "ALARM_ENABLE_FLAG,ALARM_ID,WARNING_COUNT,CHECKLIST_RRN," +
                    "WARN_ALARM_ENABLE_FLAG,WARN_ALARM_ID,ACTUAL_COUNT,ATTRI_1,ATTRI_2,ATTRI_3," + "ATTRI_4,? FROM " +
                    DataBaseNames.ENTITY_COUNTER_VALUE + " WHERE ENTITY_RRN=? and COUNTER_TYPE=?";

            jdbcTemplate.update(sql_h, transRrn, entityCounter.getEntityRrn(), entityCounter.getCounterType());
        }

        @Override
        public Page getEntityCountersAllForPage(Page page) {
            String sql = "SELECT ENTITY_RRN, INSTANCE_ID AS ENTITY_ID, INSTANCE_DESC, COUNTER_TYPE, COUNTER_DESC, " +
                    "TARGET_COUNT, " + "CHECKLIST_RRN, ALARM_ENABLE_FLAG, ALARM_ID, WARNING_COUNT, " +
                    "WARN_ALARM_ENABLE_FLAG, " + "WARN_ALARM_ID, ACTUAL_COUNT " + " FROM " +
                    DataBaseNames.ENTITY_COUNTER_VALUE + ", " + DataBaseNames.NAMEDOBJECT +
                    " WHERE ENTITY_RRN = INSTANCE_RRN ORDER BY COUNTER_TYPE ";

            return jdbcTemplate.queryForPage(page, sql, null, new EntityCounterRowMapper());
        }

        @Override
        public Page getEntityCountersAllForPageByEntityRrn(long entityRrn, Page page) {
            String sql = "SELECT ENTITY_RRN, INSTANCE_ID AS ENTITY_ID, INSTANCE_DESC, COUNTER_TYPE, COUNTER_DESC, " +
                    "TARGET_COUNT, " + "CHECKLIST_RRN, ALARM_ENABLE_FLAG, ALARM_ID, WARNING_COUNT, " +
                    "WARN_ALARM_ENABLE_FLAG, " + "WARN_ALARM_ID, ACTUAL_COUNT " + " FROM " +
                    DataBaseNames.ENTITY_COUNTER_VALUE + ", " + DataBaseNames.NAMEDOBJECT +
                    " WHERE ENTITY_RRN = INSTANCE_RRN AND ENTITY_RRN = ? ORDER BY COUNTER_TYPE ";

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

            return jdbcTemplate.queryForPage(page, sql, args, new EntityCounterRowMapper());
        }

        @Override
        public List<Map> getEqptCounter(long eqptRrn, long recipeRrn, long eventRrn, long jobRrn) {
            String sql = "SELECT T.ENTITY_RRN,T.COUNTER_TYPE," + " T.RECIPE_RRN,T.QUANTITY,T.BASIS_CODE" + " FROM " +
                    "ENTITY_COUNTER T,ENTITY_COUNTER_VALUE V" + " WHERE T.ENTITY_RRN=V.ENTITY_RRN" + " AND T.COUNTER_TYPE" +
                    "=V.COUNTER_TYPE" + " AND T.ENTITY_RRN=" + eqptRrn + " AND T.EVENT_RRN=" + eventRrn + " AND ((T" +
                    ".RECIPE_RRN=0) ";
            if (recipeRrn > 0) {
                sql = sql + " OR (T.RECIPE_RRN=" + recipeRrn + ")";
            }
            sql = sql + ")";

            return jdbcTemplate.query(sql, new RowMapper<Map>() {
                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    String basisCode = rs.getString("BASIS_CODE");
                    String counterType = rs.getString("COUNTER_TYPE");
                    double counter = rs.getDouble("QUANTITY");

                    if (StringUtils.equalsIgnoreCase(basisCode, "2")) {
                        // lot
                        counter = counter * getJobDetailNumber(jobRrn, "LOT");
                    }
                    if (StringUtils.equalsIgnoreCase(basisCode, "3")) {
                        // carrier
                        counter = counter * getJobDetailNumber(jobRrn, "CARRIER");
                    }
                    if (StringUtils.equalsIgnoreCase(basisCode, "4")) {
                        // qty1
                        counter = counter * getJobDetailNumber(jobRrn, "QTY1");
                    }
                    if (StringUtils.equalsIgnoreCase(basisCode, "5")) {
                        // qty2
                        counter = counter * getJobDetailNumber(jobRrn, "QTY2");
                    }
                    Map<String, Object> map = new HashMap<>();

                    map.put("entityRrn", eqptRrn);
                    map.put("counterType", counterType);
                    map.put("counter", counter);

                    return map;
                }
            });

        }

        @Override
        public void updateEqptCounteValue(List<Map> valueList) {
            String sql = "UPDATE ENTITY_COUNTER_VALUE T " + " SET T.ACTUAL_COUNT=NVL(ACTUAL_COUNT,0)+?" + " WHERE T" +
                    ".ENTITY_RRN=? " + " AND T.COUNTER_TYPE=?";

            for (Map map : valueList) {
                Long entityRrn = (Long) map.get("entityRrn");
                String counterType = (String) map.get("counterType");
                Double counter = (Double) map.get("counter");

                if (counter == null) {
                    counter = 0.0;
                }

                if (entityRrn == null) {
                    entityRrn = 0L;
                }
                jdbcTemplate.update(sql, counter, entityRrn, counterType);
            }
        }

        @Override
        public List<Map> checkCounterValue(long entityRrn, String type) {

            String sql = "";
            if ("warning".equalsIgnoreCase(type)) {

                sql = "SELECT T.WARN_ALARM_ID ALARM_ID,-1 CHECKLIST_RRN  FROM ENTITY_COUNTER_VALUE T" + " WHERE T" +
                        ".ENTITY_RRN=" + entityRrn + " AND T.WARN_ALARM_ENABLE_FLAG='1'" + " AND T.ACTUAL_COUNT>=T" +
                        ".WARNING_COUNT";
            } else {
                sql = "SELECT T.ALARM_ID ALARM_ID,T.CHECKLIST_RRN FROM ENTITY_COUNTER_VALUE T" + " WHERE T.ENTITY_RRN=" +
                        entityRrn + " AND T.ALARM_ENABLE_FLAG='1'" + " AND T.ACTUAL_COUNT>=T.TARGET_COUNT";
            }

            return jdbcTemplate.query(sql, new RowMapper<Map>() {
                @Override
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    String alarmId = rs.getString("ALARM_ID");
                    long checkListRrn = rs.getLong("CHECKLIST_RRN");

                    Map<String, Object> map = new HashMap<>();

                    map.put("alarmId", alarmId);

                    if (checkListRrn >= 0) {
                        map.put("checkListRrn", checkListRrn);
                    }

                    return map;
                }
            });
        }

        private double getJobDetailNumber(Long jobRrn, String type) {

            double qty = 0;

            String sql = "";
            if (jobRrn == null || jobRrn < 1) {
                return qty;
            }

            if (StringUtils.equalsIgnoreCase(type, "lot")) {
                sql = "SELECT COUNT(*) COUNT FROM LOT T WHERE T.JOB_RRN=" + jobRrn;
            }
            if (StringUtils.equalsIgnoreCase(type, "carrier")) {
                sql = "SELECT COUNT(*) COUNT FROM CARRIER T WHERE T.JOB_RRN=" + jobRrn;
            }
            if (StringUtils.equalsIgnoreCase(type, "qty1")) {
                sql = "SELECT SUM(QTY1) COUNT FROM LOT T WHERE T.JOB_RRN=" + jobRrn;
            }
            if (StringUtils.equalsIgnoreCase(type, "qty2")) {
                sql = "SELECT SUM(QTY2) COUNT FROM LOT T WHERE T.JOB_RRN=" + jobRrn;
            }
            return jdbcTemplate.queryForObjectWithNull(sql, Double.class);
        }

    }