# SorterDAOImpl.java

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

    import com.mycim.framework.jdbc.BatchPreparedStatementSetter;
    import com.mycim.framework.jdbc.JdbcTemplate;
    import com.mycim.framework.utils.beans.BeanUtils;
    import com.mycim.framework.utils.lang.StringUtils;
    import com.mycim.framework.utils.lang.collections.MapUtils;
    import com.mycim.server.sorter.dao.SorterDAO;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.SorterEnum;
    import com.mycim.valueobject.consts.SystemConstants;
    import com.mycim.valueobject.sorter.SortJobBean;
    import com.mycim.valueobject.sorter.SorterBean;
    import com.mycim.valueobject.sorter.SorterCarrierMap;
    import com.mycim.valueobject.sorter.SorterDetailBean;
    import com.mycim.valueobject.wip.Unit;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
    import org.springframework.stereotype.Repository;

    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;

    /**
     * @author yibing.liu
     * @version 1.0
     * @date 2021/06/29
     */
    @Repository
    public class SorterDAOImpl implements SorterDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Override
        public void addSorter(SorterBean sorterBean, long transRrn) {
            //1、先插入 SORTER_JOB 表
            insertSorter(sorterBean);
            //2、在批量插入 SORTER_JOB_MAPPING 表
            insertSorterDetail(sorterBean.getSorterDetailBeanList());
            //3、批量插入 SORTER_JOB_MAPPING_H 历史表
            insertSorterDetailH(sorterBean, transRrn);
        }

        @Override
        public void updateSorter(SortJobBean sortJob) {
            //暂无需要修改的属性
            String sql;
            Object[] args;
            if (sortJob.getMainJobRrn() > 0) {
                sql = "UPDATE SORTER_JOB SET STATUS=?, EXECUTION_RRN=? WHERE MAIN_JOB_RRN=? ";
                args = new Object[]{sortJob.getStatus(), sortJob.getExecutionRrn(), sortJob.getMainJobRrn()};
            } else {
                sql = "UPDATE SORTER_JOB SET STATUS=?, EXECUTION_RRN=? WHERE SUB_JOB_RRN IN (SELECT SUB_JOB_RRN FROM " +
                        "SORTER_JOB " + " WHERE SOURCE_CARRIER_RRN=? AND TARGET_CARRIER_RRN=? AND STATUS=?) ";
                args = new Object[]{sortJob.getStatus(), sortJob.getExecutionRrn(), sortJob.getSourceCarrierRrn(),
                                    sortJob.getTargetCarrierRrn(), SorterEnum.Constant.CREATE};
            }
            jdbcTemplate.update(sql, args);
        }

        @Override
        public void updateSorterBean(SorterBean sb) {
            if (sb == null) {
                return;
            }
            if (sb.getMainJobRrn() <= 0 && sb.getSubJobRrn() <= 0) {
                return;
            }
            String sql = " UPDATE SORTER_JOB SET ";
            String sqlUpdate = "";
            String whereSql = " WHERE ";
            List<Object> args = new ArrayList<>();
            if (StringUtils.isNotBlank(sb.getTargetCarrierId())) {
                sqlUpdate += ",TARGET_CARRIER_ID = ? ";
                args.add(sb.getTargetCarrierId());
            }
            if (sb.getTargetCarrierRrn() > 0) {
                sqlUpdate += ",TARGET_CARRIER_RRN = ? ";
                args.add(sb.getTargetCarrierRrn());
            }
            if (StringUtils.isNotEmpty(sb.getStatus())) {
                sqlUpdate += ",STATUS = ? ";
                args.add(sb.getStatus());
            }
            if (StringUtils.isNotEmpty(sb.getJsonAttributeData1())) {
                sqlUpdate += ",ATTRIBUTE_DATA1 = ? ";
                args.add(sb.getJsonAttributeData1());
            }
            if (StringUtils.isNotEmpty(sb.getJsonAttributeData2())) {
                sqlUpdate += ",ATTRIBUTE_DATA2 = ? ";
                args.add(sb.getJsonAttributeData2());
            }
            if (StringUtils.isNotEmpty(sb.getJsonAttributeData3())) {
                sqlUpdate += ",ATTRIBUTE_DATA3 = ? ";
                args.add(sb.getJsonAttributeData3());
            }
            if (StringUtils.isNotEmpty(sb.getJsonAttributeData4())) {
                sqlUpdate += ",ATTRIBUTE_DATA4 = ? ";
                args.add(sb.getJsonAttributeData4());
            }
            if (StringUtils.isNotEmpty(sb.getJsonAttributeData5())) {
                sqlUpdate += ",ATTRIBUTE_DATA5 = ? ";
                args.add(sb.getJsonAttributeData5());
            }

            if (args.size() == 0) {
                return;
            }

            if (sb.getMainJobRrn() > 0 && sb.getSubJobRrn() > 0) {
                whereSql += " MAIN_JOB_RRN = ? AND SUB_JOB_RRN = ? ";
                args.add(sb.getMainJobRrn());
                args.add(sb.getSubJobRrn());
            } else {
                if (sb.getMainJobRrn() > 0) {
                    whereSql += " MAIN_JOB_RRN = ? ";
                    args.add(sb.getMainJobRrn());
                } else {
                    whereSql += " SUB_JOB_RRN = ? ";
                    args.add(sb.getSubJobRrn());
                }
            }
            jdbcTemplate.update(sql + sqlUpdate.substring(1) + whereSql, args.toArray());
        }

        @Override
        public void deleteSorterByJobRrn(SorterBean sorterBean, long transRrn) {
            //先删除 SORTER_JOB_MAPPING 表的数据，然后在修改 SORTER_JOB 的状态
            deleteSorterDetail(sorterBean);
            //取消SortJob时，只能取消还未 FINISH 的SortJob
            transferData4Cancel(sorterBean, transRrn);
            cancelSorter(sorterBean);
        }

        @Override
        public void endSorter(SortJobBean sortJobBean, long transRrn) {
            for (SorterBean sorterBean : sortJobBean.getSorterBeans()) {
                //1 删除 SORTER_JOB_MAPPING 数据
                deleteSorterDetail(sorterBean);//TODO
                //2 修改 SORTER_JOB 状态
                sorterBean.setUpdateTime(sortJobBean.getLastUpdateTimestamp());
                sorterBean.setUpdateUser(sortJobBean.getLastUpdateUserId());
                sorterBean.setEquipmentId(sortJobBean.getEquipmentId());
                sorterBean.setExecutionRrn(sortJobBean.getExecutionRrn());
                transferData4Finish(sorterBean, transRrn);
                finishSorter(sorterBean);
            }
        }

        @Override
        public void insertSorterCarrierMapList(List<SorterCarrierMap> carrierMapList) {
            StringBuilder sql = new StringBuilder(DataBaseNames.INSERT_INTO);
            sql.append(DataBaseNames.SORTER_CARRIER_MAP_H);
            sql.append(" (MAIN_JOB_RRN, SUB_JOB_RRN, CARRIER_MAP_RRN, CARRIER_RRN, CARRIER_ID, LOT_RRN, LOT_ID,");
            sql.append("  UNIT_RRN, UNIT_ID, POSITION) ").append(" VALUES ");
            sql.append(" (:mainJobRrn, :subJobRrn, :carrierMapRrn, :carrierRrn, :carrierId, :lotRrn, :lotId,");
            sql.append("  :unitRrn, :unitId, :position) ");

            Map<String, Object>[] batchValues = carrierMapList.stream().map(map -> {
                return BeanUtils.copyBeanToMap(map);
            }).collect(Collectors.toList()).toArray(new Map[carrierMapList.size()]);

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

        private void insertSorter(SorterBean s) {
            String sql = "INSERT INTO SORTER_JOB (MAIN_JOB_RRN, SUB_JOB_RRN, SOURCE_CARRIER_RRN, SOURCE_CARRIER_ID, " +
                    "TARGET_CARRIER_RRN," + "TARGET_CARRIER_ID, STATUS, EXCHANGE_TOTAL_QTY, JOB_TYPE, JOB_RRN," +
                    "EXECUTION_RRN, PORT_NUM, ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3," +
                    "ATTRIBUTE_DATA4, ATTRIBUTE_DATA5, CREATE_TIME, CREATE_USER, UPDATE_TIME," +
                    "UPDATE_USER, LOCK_VERSION, SORTER_TYPE)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(sql, s.getMainJobRrn(), s.getSubJobRrn(), s.getSourceCarrierRrn(), s.getSourceCarrierId(),
                                s.getTargetCarrierRrn(), s.getTargetCarrierId(), s.getStatus(), s.getExchangeTotalQty(),
                                s.getJobType(), s.getJobRrn(), s.getExecutionRrn(), s.getPortNum(),
                                s.getJsonAttributeData1(), s.getJsonAttributeData2(), s.getJsonAttributeData3(),
                                s.getJsonAttributeData4(), s.getJsonAttributeData5(), s.getCreateTime(), s.getCreateUser(),
                                s.getUpdateTime(), s.getUpdateUser(), s.getLockVersion(), s.getSorterType());
        }

        private void insertSorterDetail(List<SorterDetailBean> sorterDetailBeanList) {
            String sql = "INSERT INTO SORTER_JOB_MAPPING (SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION," +
                    "TARGET_POSITION)" + " VALUES(?,?,?,?,?,?,?)";

            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                SorterDetailBean sd;

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    sd = sorterDetailBeanList.get(i);
                    ps.setLong(1, sd.getSubJobRrn());
                    ps.setLong(2, sd.getLotRrn());
                    ps.setString(3, sd.getLotId());
                    ps.setLong(4, sd.getUnitRrn());
                    ps.setString(5, sd.getUnitId());
                    ps.setInt(6, sd.getSourcePosition());
                    ps.setInt(7, sd.getTargetPosition());
                }

                @Override
                public int getBatchSize() {
                    return sorterDetailBeanList.size();
                }
            });
        }

        private void insertSorterDetailH(SorterBean sd, long transRrn) {
            String sql = "INSERT INTO SORTER_JOB_MAPPING_H (SUB_JOB_RRN,TRANS_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID," +
                    "SOURCE_POSITION,TARGET_POSITION)" +
                    " SELECT SUB_JOB_RRN,?,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION,TARGET_POSITION " +
                    " FROM SORTER_JOB_MAPPING WHERE SUB_JOB_RRN=?";
            jdbcTemplate.update(sql, transRrn, sd.getSubJobRrn());
        }

        private void finishSorter(SorterBean sorterBean) {
            String sql = "DELETE FROM SORTER_JOB WHERE MAIN_JOB_RRN=? AND SUB_JOB_RRN=?";
            jdbcTemplate.update(sql, sorterBean.getMainJobRrn(), sorterBean.getSubJobRrn());
        }

        private void cancelSorter(SorterBean sorterBean) {
            String sql = "DELETE FROM SORTER_JOB WHERE MAIN_JOB_RRN=? ";
            jdbcTemplate.update(sql, sorterBean.getMainJobRrn());
        }

        private void transferData4Finish(SorterBean sorterBean, long transRrn) {
            String sql = "INSERT INTO SORTER_JOB_H (MAIN_JOB_RRN, SUB_JOB_RRN, TRANS_RRN, SOURCE_CARRIER_RRN, " +
                    "SOURCE_CARRIER_ID, TARGET_CARRIER_RRN," +
                    "TARGET_CARRIER_ID, STATUS, EQUIPMENT_ID, EXCHANGE_TOTAL_QTY, JOB_TYPE, JOB_RRN," +
                    "EXECUTION_RRN, PORT_NUM, ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3," +
                    "ATTRIBUTE_DATA4, ATTRIBUTE_DATA5, CREATE_TIME, CREATE_USER, UPDATE_TIME," +
                    "UPDATE_USER, LOCK_VERSION, SORTER_TYPE)" +
                    " SELECT MAIN_JOB_RRN, SUB_JOB_RRN, ?, SOURCE_CARRIER_RRN, SOURCE_CARRIER_ID, " +
                    "TARGET_CARRIER_RRN," + "TARGET_CARRIER_ID, ?, ?, EXCHANGE_TOTAL_QTY, JOB_TYPE, JOB_RRN," +
                    " ?, PORT_NUM, ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3," +
                    " ATTRIBUTE_DATA4, ATTRIBUTE_DATA5, CREATE_TIME, CREATE_USER, ?, ?, LOCK_VERSION, SORTER_TYPE" +
                    " FROM SORTER_JOB WHERE MAIN_JOB_RRN=? AND SUB_JOB_RRN=? ";
            jdbcTemplate.update(sql, transRrn, SorterEnum.Status.FINISH.getStatus(), sorterBean.getEquipmentId(),
                                sorterBean.getExecutionRrn(), sorterBean.getUpdateTime(), sorterBean.getUpdateUser(),
                                sorterBean.getMainJobRrn(), sorterBean.getSubJobRrn());
        }

        private void transferData4Cancel(SorterBean sorterBean, long transRrn) {
            String sql = "INSERT INTO SORTER_JOB_H (MAIN_JOB_RRN, SUB_JOB_RRN, TRANS_RRN, SOURCE_CARRIER_RRN, " +
                    "SOURCE_CARRIER_ID, TARGET_CARRIER_RRN," +
                    "TARGET_CARRIER_ID, STATUS, EQUIPMENT_ID, EXCHANGE_TOTAL_QTY, JOB_TYPE, JOB_RRN," +
                    "EXECUTION_RRN, PORT_NUM, ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3," +
                    "ATTRIBUTE_DATA4, ATTRIBUTE_DATA5, CREATE_TIME, CREATE_USER, UPDATE_TIME," +
                    "UPDATE_USER, LOCK_VERSION, SORTER_TYPE)" +
                    " SELECT MAIN_JOB_RRN, SUB_JOB_RRN, ?, SOURCE_CARRIER_RRN, SOURCE_CARRIER_ID, " +
                    "TARGET_CARRIER_RRN," + "TARGET_CARRIER_ID, ?, '', EXCHANGE_TOTAL_QTY, JOB_TYPE, JOB_RRN," +
                    " EXECUTION_RRN, PORT_NUM, ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3," +
                    " ATTRIBUTE_DATA4, ATTRIBUTE_DATA5, CREATE_TIME, CREATE_USER, ?, ?, LOCK_VERSION, SORTER_TYPE" +
                    " FROM SORTER_JOB WHERE MAIN_JOB_RRN=? AND STATUS IN (?,?)";
            jdbcTemplate.update(sql, transRrn, SorterEnum.Status.CANCEL.getStatus(), sorterBean.getUpdateTime(),
                                sorterBean.getUpdateUser(), sorterBean.getMainJobRrn(),
                                SorterEnum.Status.CREATE.getStatus(), SorterEnum.Status.DISPATCH.getStatus());
        }

        private void deleteSorterDetail(SorterBean sorterBean) {
            String sql = "DELETE FROM SORTER_JOB_MAPPING WHERE  ";
            if (sorterBean.getSubJobRrn() > 0) {
                sql += " SUB_JOB_RRN = ? ";
                jdbcTemplate.update(sql, sorterBean.getSubJobRrn());
            } else {
                sql += " SUB_JOB_RRN IN (SELECT SUB_JOB_RRN FROM SORTER_JOB WHERE MAIN_JOB_RRN = ?) ";
                jdbcTemplate.update(sql, sorterBean.getMainJobRrn());
            }
        }

    }