# EquipmentChartDAOImpl.java

    package com.mycim.server.edcchart.dao.impl;

    import com.fa.sesa.threadlocal.LocalContext;
    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.time.DateUtils;
    import com.mycim.server.edcchart.dao.EquipmentChartDAO;
    import com.mycim.server.edcchart.dao.mapper.EquipmentChartInfoHistoryRowMapper;
    import com.mycim.server.edcchart.dao.mapper.EquipmentChartInfoRowMapper;
    import com.mycim.valueobject.edcspc.EquipmentChartInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Repository;

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

    /**
     * @author yanbing.chen
     * @version 6.0.0
     * @date 2019/12/13
     **/
    @Repository
    public class EquipmentChartDAOImpl implements EquipmentChartDAO {

        @Autowired
        private JdbcTemplate jdbcTemplate;

        @Override
        public Page getEquipmentChartInfoPage(Page page, Map<String, Object> condition) {
            long facilityRrn = LocalContext.getFacilityRrn();
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" EQUIPMENT_RRN, CHART_ID, FACILITY_RRN, EQUIPMENT_ID, CHART_NAME, ");
            sql.append(" AREA_ID, DERIVE_CHART_NAME, USER_NAME, RECIPE_LIST, PROD_LIST, TIME_INTERVAL, ");
            sql.append(" WARNING_TIME, FORMULA, LAST_UPLOAD_TIMESTAMP, EXPIRED_TIMESTAMP, MAIL_SEND_TIMESTAMP ");
            sql.append(" FROM EQUIPMENT_CHART_INFO ");
            sql.append(" WHERE FACILITY_RRN = ? ");

            List<Object> args = new ArrayList<>();

            args.add(facilityRrn);

            if (condition != null) {
                long longTemp = MapUtils.getLongValue(condition, "equipmentRrn");
                if (longTemp > 0) {
                    sql.append(" AND EQUIPMENT_RRN = ? ");
                    args.add(longTemp);
                }

                String stringTemp = MapUtils.getString(condition, "equipmentId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND EQUIPMENT_ID LIKE ? ");
                    args.add(StringUtils.trimToUpperCase(stringTemp.replace("*", "%")));
                }

                stringTemp = MapUtils.getString(condition, "chartName");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND CHART_NAME = ? ");
                    args.add(stringTemp.replace("*", "%"));
                }

                stringTemp = MapUtils.getString(condition, "areaId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND AREA_ID = ? ");
                    args.add(stringTemp.replace("*", "%"));
                }
            }

            sql.append(" ORDER BY EQUIPMENT_ID, CHART_NAME, AREA_ID ");

            return jdbcTemplate.queryForPage(page, sql.toString(), args.toArray(), new RowMapper<Map<String, Object>>() {

                @Override
                public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map<String, Object> map = new HashMap<>();
                    map.put("equipmentRrn", rs.getLong("EQUIPMENT_RRN"));
                    map.put("chartId", rs.getString("CHART_ID"));
                    map.put("equipmentId", rs.getString("EQUIPMENT_ID"));
                    map.put("chartName", rs.getString("CHART_NAME"));
                    map.put("areaId", rs.getString("AREA_ID"));
                    map.put("deriveChartName", StringUtils.defaultString(rs.getString("DERIVE_CHART_NAME")));
                    map.put("userName", rs.getString("USER_NAME"));
                    map.put("recipeList", StringUtils.defaultString(rs.getString("RECIPE_LIST")));
                    map.put("prodList", StringUtils.defaultString(rs.getString("PROD_LIST")));
                    map.put("timeInterval", rs.getFloat("TIME_INTERVAL"));
                    map.put("warningTime", rs.getFloat("WARNING_TIME"));
                    map.put("formula", StringUtils.defaultString(rs.getString("FORMULA")));
                    if (rs.getTimestamp("LAST_UPLOAD_TIMESTAMP") != null) {
                        map.put("lastUploadTimestamp", DateUtils.formatDate(rs.getTimestamp("LAST_UPLOAD_TIMESTAMP")));
                    } else {
                        map.put("lastUploadTimestamp", "");
                    }
                    if (rs.getTimestamp("EXPIRED_TIMESTAMP") != null) {
                        map.put("expiredTimestamp", DateUtils.formatDate(rs.getTimestamp("EXPIRED_TIMESTAMP")));
                    } else {
                        map.put("expiredTimestamp", "");
                    }
                    return map;
                }
            });
        }

        @Override
        public Page getEquipmentChartInfoHistoryPage(Page page, Map<String, Object> condition) {
            long facilityRrn = LocalContext.getFacilityRrn();
            StringBuilder sql = new StringBuilder("SELECT ");

            sql.append(" tl.TRANS_RRN, tl.TRANS_ID, tl.TRANS_START_TIMESTAMP, tl.TRANS_END_TIMESTAMP, tl" +
                               ".TRANS_PERFORMED_BY, tl.COMMENTS, ");
            sql.append(
                    " eci.TRANS_SEQUENCE, eci.EQUIPMENT_RRN, eci.CHART_ID, eci.FACILITY_RRN, eci.EQUIPMENT_ID, " + "eci" +
                            ".CHART_NAME, ");
            sql.append(" eci.AREA_ID, eci.DERIVE_CHART_NAME, eci.USER_NAME, eci.RECIPE_LIST, eci.PROD_LIST, ");
            sql.append(" eci.TIME_INTERVAL, eci.WARNING_TIME, eci.FORMULA, eci.LAST_UPLOAD_TIMESTAMP, " + " eci" +
                               ".EXPIRED_TIMESTAMP, eci.MAIL_SEND_TIMESTAMP ");
            sql.append(" FROM EQUIPMENT_CHART_INFO_H eci, TRANSACTION_LOG tl ");
            sql.append(" WHERE eci.TRANS_RRN = tl.TRANS_RRN AND eci.FACILITY_RRN = ? ");

            List<Object> args = new ArrayList<>();

            args.add(facilityRrn);

            if (condition != null) {
                long longTemp = MapUtils.getLongValue(condition, "equipmentRrn");
                if (longTemp > 0) {
                    sql.append(" AND eci.EQUIPMENT_RRN = ? ");
                    args.add(longTemp);
                }

                String stringTemp = MapUtils.getString(condition, "equipmentId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.EQUIPMENT_ID LIKE ? ");
                    args.add(StringUtils.trimToUpperCase(stringTemp.replace("*", "%")));
                }

                stringTemp = MapUtils.getString(condition, "chartName");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.CHART_NAME = ? ");
                    args.add(stringTemp);
                }

                stringTemp = MapUtils.getString(condition, "areaId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.AREA_ID = ? ");
                    args.add(stringTemp);
                }
            }

            sql.append(" ORDER BY tl.TRANS_START_TIMESTAMP DESC, eci.TRANS_SEQUENCE DESC ");

            return jdbcTemplate
                    .queryForPage(page, sql.toString(), args.toArray(), new EquipmentChartInfoHistoryRowMapper());
        }

        @Override
        public List<Map<String, Object>> getEquipmentChartInfoHistoryList(Map<String, Object> condition) {
            long facilityRrn = LocalContext.getFacilityRrn();
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" tl.TRANS_RRN, tl.TRANS_ID, tl.TRANS_START_TIMESTAMP, tl.TRANS_END_TIMESTAMP, tl" +
                               ".TRANS_PERFORMED_BY, tl.COMMENTS, ");
            sql.append(
                    " eci.TRANS_SEQUENCE, eci.EQUIPMENT_RRN, eci.CHART_ID, eci.FACILITY_RRN, eci.EQUIPMENT_ID, " + "eci" +
                            ".CHART_NAME, ");
            sql.append(" eci.AREA_ID, eci.DERIVE_CHART_NAME, eci.USER_NAME, eci.RECIPE_LIST, eci.PROD_LIST, ");
            sql.append(" eci.TIME_INTERVAL, eci.WARNING_TIME, eci.FORMULA, eci.LAST_UPLOAD_TIMESTAMP, " + "eci" +
                               ".EXPIRED_TIMESTAMP, eci.MAIL_SEND_TIMESTAMP ");
            sql.append(" FROM EQUIPMENT_CHART_INFO_H eci, TRANSACTION_LOG tl ");
            sql.append(" WHERE eci.TRANS_RRN = tl.TRANS_RRN AND eci.FACILITY_RRN = ? ");

            List<Object> args = new ArrayList<>();

            args.add(facilityRrn);

            if (condition != null) {
                long longTemp = MapUtils.getLongValue(condition, "equipmentRrn");
                if (longTemp > 0) {
                    sql.append(" AND eci.EQUIPMENT_RRN = ? ");
                    args.add(longTemp);
                }

                String stringTemp = MapUtils.getString(condition, "equipmentId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.EQUIPMENT_ID = ? ");
                    args.add(stringTemp);
                }

                stringTemp = MapUtils.getString(condition, "chartName");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.CHART_NAME = ? ");
                    args.add(stringTemp);
                }

                stringTemp = MapUtils.getString(condition, "areaId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND eci.AREA_ID = ? ");
                    args.add(stringTemp);
                }
            }

            sql.append(" ORDER BY tl.TRANS_START_TIMESTAMP DESC, eci.TRANS_SEQUENCE DESC ");
            return jdbcTemplate.query(sql.toString(), args.toArray(), new EquipmentChartInfoHistoryRowMapper());
        }

        @Override
        public EquipmentChartInfo getEquipmentChartInfo(Long equipmentRrn, String chartId) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" EQUIPMENT_RRN, CHART_ID, FACILITY_RRN, EQUIPMENT_ID, CHART_NAME, AREA_ID, ");
            sql.append(" DERIVE_CHART_NAME, USER_NAME, RECIPE_LIST, PROD_LIST, TIME_INTERVAL,");
            sql.append(" WARNING_TIME, FORMULA, LAST_UPLOAD_TIMESTAMP, EXPIRED_TIMESTAMP, MAIL_SEND_TIMESTAMP ");
            sql.append(" FROM EQUIPMENT_CHART_INFO ");
            sql.append(" WHERE EQUIPMENT_RRN = ? AND CHART_ID = ? ");

            return jdbcTemplate.queryForObjectWithNull(sql.toString(), new Object[]{equipmentRrn, chartId},
                                                       new EquipmentChartInfoRowMapper());
        }

        @Override
        public void updateEquipmentChartInfo(EquipmentChartInfo equipmentChartInfo) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(" EQUIPMENT_CHART_INFO ");
            sql.append(" SET LAST_UPLOAD_TIMESTAMP = ?, EXPIRED_TIMESTAMP = ?, MAIL_SEND_TIMESTAMP = ? ");
            sql.append(" WHERE EQUIPMENT_RRN = ? AND CHART_ID = ? ");

            jdbcTemplate.update(sql.toString(), equipmentChartInfo.getLastUploadTimestamp(),
                                equipmentChartInfo.getExpiredTimestamp(), equipmentChartInfo.getMailSendTimestamp(),
                                equipmentChartInfo.getEquipmentRrn(), equipmentChartInfo.getChartId());
        }

        @Override
        public void insertEquipmentChartInfoHistoryByBatch(Long transRrn, List<EquipmentChartInfo> equipmentChartInfos) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(" EQUIPMENT_CHART_INFO_H ");
            sql.append(" (TRANS_RRN, TRANS_SEQUENCE, EQUIPMENT_RRN, CHART_ID, ");
            sql.append(" FACILITY_RRN, EQUIPMENT_ID, CHART_NAME, AREA_ID, DERIVE_CHART_NAME, ");
            sql.append(" USER_NAME, RECIPE_LIST, PROD_LIST, TIME_INTERVAL, WARNING_TIME, ");
            sql.append(" FORMULA, LAST_UPLOAD_TIMESTAMP, EXPIRED_TIMESTAMP, MAIL_SEND_TIMESTAMP) ");
            sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            List<Object[]> args = new ArrayList<>();
            int seq = 1;
            for (EquipmentChartInfo equipmentChartInfo : equipmentChartInfos) {
                args.add(
                        new Object[]{transRrn, seq++, equipmentChartInfo.getEquipmentRrn(),
                                equipmentChartInfo.getChartId(), equipmentChartInfo.getFacilityRrn(),
                                equipmentChartInfo.getEquipmentId(), equipmentChartInfo.getChartName(),
                                equipmentChartInfo.getAreaId(), equipmentChartInfo.getDeriveChartName(),
                                equipmentChartInfo.getUserName(), equipmentChartInfo.getRecipeList(),
                                equipmentChartInfo.getProdList(), equipmentChartInfo.getTimeInterval(),
                                equipmentChartInfo.getWarningTime(), equipmentChartInfo.getFormula(),
                                equipmentChartInfo.getLastUploadTimestamp(), equipmentChartInfo.getExpiredTimestamp(),
                                equipmentChartInfo.getMailSendTimestamp()});
            }
            jdbcTemplate.batchUpdate(sql.toString(), args);
        }

        @Override
        public void insertEquipmentChartInfoByBatch(List<EquipmentChartInfo> equipmentChartInfos) {
            StringBuilder sql = new StringBuilder("INSERT INTO ");

            sql.append(" EQUIPMENT_CHART_INFO ");
            sql.append(" (EQUIPMENT_RRN, CHART_ID, FACILITY_RRN, EQUIPMENT_ID, CHART_NAME, ");
            sql.append(" AREA_ID, DERIVE_CHART_NAME, USER_NAME, RECIPE_LIST, PROD_LIST, ");
            sql.append(" TIME_INTERVAL, WARNING_TIME, FORMULA, LAST_UPLOAD_TIMESTAMP, EXPIRED_TIMESTAMP) ");
            sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            List<Object[]> args = new ArrayList<>();
            for (EquipmentChartInfo equipmentChartInfo : equipmentChartInfos) {
                args.add(
                        new Object[]{equipmentChartInfo.getEquipmentRrn(), equipmentChartInfo.getChartId(),
                                equipmentChartInfo.getFacilityRrn(), equipmentChartInfo.getEquipmentId(),
                                equipmentChartInfo.getChartName(), equipmentChartInfo.getAreaId(),
                                equipmentChartInfo.getDeriveChartName(), equipmentChartInfo.getUserName(),
                                equipmentChartInfo.getRecipeList(), equipmentChartInfo.getProdList(),
                                equipmentChartInfo.getTimeInterval(), equipmentChartInfo.getWarningTime(),
                                equipmentChartInfo.getFormula(), equipmentChartInfo.getLastUploadTimestamp(),
                                equipmentChartInfo.getExpiredTimestamp()});
            }

            jdbcTemplate.batchUpdate(sql.toString(), args);
        }

        @Override
        public void updateEquipmentChartInfoByBatch(List<EquipmentChartInfo> equipmentChartInfos) {
            StringBuilder sql = new StringBuilder("UPDATE ");

            sql.append(" EQUIPMENT_CHART_INFO ");
            sql.append(" SET CHART_NAME = ?, AREA_ID = ?, DERIVE_CHART_NAME = ?, USER_NAME = ?, ");
            sql.append(" RECIPE_LIST = ?, PROD_LIST = ?, TIME_INTERVAL = ?, WARNING_TIME = ?, FORMULA = ?, ");
            sql.append(" LAST_UPLOAD_TIMESTAMP = ?, EXPIRED_TIMESTAMP = ?, MAIL_SEND_TIMESTAMP = ? ");
            sql.append(" WHERE EQUIPMENT_RRN = ? AND CHART_ID = ? ");

            List<Object[]> args = new ArrayList<>();
            for (EquipmentChartInfo equipmentChartInfo : equipmentChartInfos) {
                args.add(
                        new Object[]{equipmentChartInfo.getChartName(), equipmentChartInfo.getAreaId(),
                                equipmentChartInfo.getDeriveChartName(), equipmentChartInfo.getUserName(),
                                equipmentChartInfo.getRecipeList(), equipmentChartInfo.getProdList(),
                                equipmentChartInfo.getTimeInterval(), equipmentChartInfo.getWarningTime(),
                                equipmentChartInfo.getFormula(), equipmentChartInfo.getLastUploadTimestamp(),
                                equipmentChartInfo.getExpiredTimestamp(), equipmentChartInfo.getMailSendTimestamp(),
                                equipmentChartInfo.getEquipmentRrn(), equipmentChartInfo.getChartId()});
            }

            jdbcTemplate.batchUpdate(sql.toString(), args);
        }

        @Override
        public int[] deleteEquipmentChartInfoByBatch(List<EquipmentChartInfo> equipmentChartInfos) {
            StringBuilder sql = new StringBuilder("DELETE ");

            sql.append(" FROM EQUIPMENT_CHART_INFO ");
            sql.append(" WHERE EQUIPMENT_RRN = ? AND CHART_ID = ? ");

            List<Object[]> args = new ArrayList<>();
            for (EquipmentChartInfo equipmentChartInfo : equipmentChartInfos) {
                args.add(new Object[]{equipmentChartInfo.getEquipmentRrn(), equipmentChartInfo.getChartId()});
            }

            return jdbcTemplate.batchUpdate(sql.toString(), args);
        }

        @Override
        public List<EquipmentChartInfo> getEquipAndChamberChart(long instanceRrn) {
            String sql = "SELECT * FROM EQUIPMENT_CHART_INFO WHERE EQUIPMENT_RRN IN" + " (SELECT EN.ENTITY_RRN FROM " +
                    "ENTITY EN WHERE EN.ENTITY_RRN=?" + " UNION SELECT CEN.ENTITY_RRN FROM ENTITY CEN WHERE CEN" +
                    ".PARENT_ENTITY_RRN=?)";
            return jdbcTemplate
                    .query(sql.toString(), new Object[]{instanceRrn, instanceRrn}, new EquipmentChartInfoRowMapper());
        }

        @Override
        public List<EquipmentChartInfo> getEquipChart(long instanceRrn) {
            String sql = "SELECT * FROM EQUIPMENT_CHART_INFO WHERE EQUIPMENT_RRN = ?";
            return jdbcTemplate.query(sql.toString(), new Object[]{instanceRrn}, new EquipmentChartInfoRowMapper());
        }

        @Override
        public List<EquipmentChartInfo> getEquipmentChartInfoList(Map condition) {
            StringBuilder sql = new StringBuilder("SELECT ");
            sql.append(" EQUIPMENT_RRN, CHART_ID, FACILITY_RRN, EQUIPMENT_ID, CHART_NAME, ");
            sql.append(" AREA_ID, DERIVE_CHART_NAME, USER_NAME, RECIPE_LIST, PROD_LIST,TIME_INTERVAL, ");
            sql.append(" WARNING_TIME, FORMULA, LAST_UPLOAD_TIMESTAMP, EXPIRED_TIMESTAMP, MAIL_SEND_TIMESTAMP ");
            sql.append(" FROM EQUIPMENT_CHART_INFO ");
            sql.append(" WHERE 1 = 1 ");
            List<Object> args = new ArrayList<>();

            if (condition != null) {
                long longTemp = MapUtils.getLongValue(condition, "equipmentRrn");
                if (longTemp > 0) {
                    sql.append(" AND EQUIPMENT_RRN = ? ");
                    args.add(longTemp);
                }

                String stringTemp = MapUtils.getString(condition, "equipmentId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND EQUIPMENT_ID LIKE ? ");
                    args.add(StringUtils.trimToUpperCase(stringTemp.replace("*", "%")));
                }

                stringTemp = MapUtils.getString(condition, "chartName");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND CHART_NAME = ? ");
                    args.add(stringTemp.replace("*", "%"));
                }

                stringTemp = MapUtils.getString(condition, "areaId");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND AREA_ID = ? ");
                    args.add(stringTemp.replace("*", "%"));
                }

                stringTemp = MapUtils.getString(condition, "mailSendFlag");
                if (StringUtils.isNotEmpty(stringTemp)) {
                    sql.append(" AND MAIL_SEND_TIMESTAMP IS NULL ");
                }
            }

            sql.append(" ORDER BY EQUIPMENT_ID, CHART_NAME, AREA_ID ");

            return jdbcTemplate.query(sql.toString(), args.toArray(), new EquipmentChartInfoRowMapper());
        }

    }