# SorterQueryDAOImpl.java

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

    import com.fa.sesa.i18n.I18nUtils;
    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.CollectionUtils;
    import com.mycim.framework.utils.lang.math.NumberUtils;
    import com.mycim.server.base.dao.NamedObjectDAO;
    import com.mycim.server.sorter.dao.SorterQueryDAO;
    import com.mycim.valueobject.consts.DataBaseNames;
    import com.mycim.valueobject.consts.SorterEnum;
    import com.mycim.valueobject.sorter.*;
    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: yibing.liu
     * @Date: 2021/7/16 11:01
     */
    @Repository
    public class SorterQueryDAOImpl implements SorterQueryDAO {

        @Autowired
        JdbcTemplate jdbcTemplate;

        @Autowired
        NamedObjectDAO namedObjectDAO;

        @Override
        public Page querySorterList(Page page, SorterBean sorterBean) {
            List<Object> arg = new ArrayList<>();
            String whereSql = "";
            String sql = "SELECT MAIN_JOB_RRN, SUB_JOB_RRN,SOURCE_CARRIER_ID, TARGET_CARRIER_ID," +
                    "STATUS,EXCHANGE_TOTAL_QTY,JOB_TYPE,CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + "FROM (" +
                    " SELECT MAIN_JOB_RRN,TO_CHAR(listagg(SUB_JOB_RRN,',') within group(order by SUB_JOB_RRN)" +
                    ") SUB_JOB_RRN," + "TO_CHAR(listagg(SOURCE_CARRIER_ID,',') within group(order by SOURCE_CARRIER_ID)) " +
                    "SOURCE_CARRIER_ID, " +
                    " TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,SUM(EXCHANGE_TOTAL_QTY) EXCHANGE_TOTAL_QTY,JOB_TYPE," +
                    " CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + " FROM SORTER_JOB " +
                    " GROUP BY MAIN_JOB_RRN,STATUS,UPDATE_TIME,CREATE_TIME,JOB_TYPE,CREATE_USER," +
                    "TARGET_CARRIER_RRN,TARGET_CARRIER_ID,SORTER_TYPE " + " UNION ALL" +
                    " SELECT MAIN_JOB_RRN,TO_CHAR(listagg(SUB_JOB_RRN,',') within group(order by SUB_JOB_RRN)" +
                    ") SUB_JOB_RRN," + "TO_CHAR(listagg(SOURCE_CARRIER_ID,',') within group(order by SOURCE_CARRIER_ID)) " +
                    "SOURCE_CARRIER_ID, " +
                    " TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,SUM(EXCHANGE_TOTAL_QTY) EXCHANGE_TOTAL_QTY,JOB_TYPE," +
                    " CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + " FROM SORTER_JOB_H " +
                    " GROUP BY MAIN_JOB_RRN,STATUS,UPDATE_TIME,CREATE_TIME,JOB_TYPE,CREATE_USER," +
                    "TARGET_CARRIER_RRN,TARGET_CARRIER_ID,SORTER_TYPE " + ") ST ";

            // 增加Sorter Type类型查询条件
            sql += " WHERE SORTER_TYPE = ?";
            arg.add(sorterBean.getSorterType());

            if (StringUtils.isNotEmpty(sorterBean.getSourceCarrierId())) {
                whereSql += "AND ST.SOURCE_CARRIER_ID LIKE ?";
                arg.add("%" + sorterBean.getSourceCarrierId() + "%");
            }
            if (StringUtils.isNotEmpty(sorterBean.getTargetCarrierId())) {
                whereSql += "AND ST.TARGET_CARRIER_ID = ? ";
                arg.add(sorterBean.getTargetCarrierId());
            }
            if (StringUtils.isNotEmpty(sorterBean.getStatus())) {
                whereSql += "AND ST.STATUS = ? ";
                arg.add(sorterBean.getStatus());
            }
            if (StringUtils.isNotEmpty(sorterBean.getJobType())) {
                whereSql += "AND ST.JOB_TYPE = ? ";
                arg.add(sorterBean.getJobType());
            }
            sql += whereSql;
            sql += " ORDER BY UPDATE_TIME DESC,MAIN_JOB_RRN DESC ";
            return jdbcTemplate.queryForPage(page, sql, arg.toArray(), (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean s = new SorterBean();
                s.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                String subJobRrn = rs.getString("SUB_JOB_RRN");
                if (!StringUtils.contains(subJobRrn, StringUtils.COMMA_SIGN)) {
                    s.setSubJobRrn(NumberUtils.toLong(subJobRrn, 0L));
                }
                s.setSourceCarrierId(rs.getString("SOURCE_CARRIER_ID"));
                s.setTargetCarrierId(rs.getString("TARGET_CARRIER_ID"));
                s.setExchangeTotalQty(rs.getInt("EXCHANGE_TOTAL_QTY"));
                s.setJobType(rs.getString("JOB_TYPE"));
                s.setJobTypeParse(SorterEnum.Constant.parseTextType(s.getJobType(), I18nUtils.getCurrentLanguage().name()));
                s.setStatus(rs.getString("STATUS"));
                s.setStatusParse(SorterEnum.Constant.parseTextType(s.getStatus(), I18nUtils.getCurrentLanguage().name()));
                s.setCreateUser(rs.getString("CREATE_USER"));
                s.setCreateTime(rs.getTimestamp("CREATE_TIME"));
                s.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
                if (s.getUpdateTime() == null) {
                    s.setUpdateTime(s.getCreateTime());
                }
                s.setSorterType(rs.getString("SORTER_TYPE"));
                return s;
            });
        }

        @Override
        public SorterBean getSorterByCarrier(String carrierId) {
            List<String> list = jdbcTemplate.query(
                    "SELECT SOURCE_CARRIER_ID FROM SORTER_JOB WHERE SOURCE_CARRIER_ID = ? AND STATUS IN (?,?)",
                    new Object[]{carrierId, SorterEnum.Status.CREATE.getStatus(), SorterEnum.Status.DISPATCH.getStatus()},
                    (RowMapper<String>) (rs, rowNum) -> rs.getString("SOURCE_CARRIER_ID"));
            if (CollectionUtils.isEmpty(list)) {
                return null;
            } else {
                return new SorterBean();
            }
        }

        @Override
        public List<SorterBean> getSorterList(SorterBean sorterBean) {
            return null;
        }

        @Override
        public List<SorterDetailBean> getSorterDetailList(SorterBean sorterBean) {
            return null;
        }

        @Override
        public SortJobBean getSortJobByStatus(long jobRrn, Long subJobRrn, StatusBean status) {
            SortJobBean sortJobBean = new SortJobBean();
            List<SorterBean> sorterBeans = getSorterBeanListByJobRrn(jobRrn, subJobRrn, status);
            Map<Long, List<SorterDetailBean>> sorterDetailList = getSorterDetailListByJobRrnAndStatus(jobRrn, status);
            dealResults(sortJobBean, sorterBeans, sorterDetailList);
            sortJobBean.setSorterBeans(sorterBeans);
            return sortJobBean;
        }

        /**
         * 只查询CREATE和DISPATCH
         *
         * @param sortBean
         * @return
         */
        @Override
        public SortJobBean getSortJob(SorterBean sortBean) {
            //1个SortJob对应多个SorterBean，一个SorterBean对应多个SorterDetailBean。
            SortJobBean sortJobBean = new SortJobBean();
            if (sortBean != null) {
                List<SorterBean> sorterBeans;
                Map<Long, List<SorterDetailBean>> sorterDetailList;
                if (sortBean.getMainJobRrn() > 0) {//没有 JobRrn 时，通过 sourceCarrierRrn 和 targetCarrierRrn 来查询
                    sorterBeans = getSorterBeanListByJobRrn(sortBean.getMainJobRrn(), null, new StatusBean());
                    sorterDetailList = getSorterDetailListByJobRrn(sortBean.getMainJobRrn(),
                                                                   new StatusBean(SorterEnum.Constant.CREATE));
                } else {
                    sorterBeans = getSorterBeanListByCarrierRrn(sortBean.getSourceCarrierRrn(),
                                                                sortBean.getTargetCarrierRrn(), 0L, new StatusBean());
                    sorterDetailList = getSorterDetailListBySubJobRrn(
                            sorterBeans.stream().map(SorterBean::getSubJobRrn).toArray());
                }
                dealResults(sortJobBean, sorterBeans, sorterDetailList);
                sortJobBean.setSorterBeans(sorterBeans);
            }
            return sortJobBean;
        }

        @Override
        public SortJobBean getSortJob(SortJobBean sortJobBean) {
            List<SorterBean> sorterBeans;
            if (sortJobBean.getMainJobRrn() <= 0) {
                sorterBeans = getSorterBeanListByCarrierRrn(sortJobBean.getSourceCarrierRrn(),
                                                            sortJobBean.getTargetCarrierRrn(),
                                                            sortJobBean.getTargetCarrierRrn2(), new StatusBean());
            } else {
                //TODO 多Port的手动处理时，subJobRrn为null。multiLot时，需要subJobRrn。
                sorterBeans = getSorterBeanListByJobRrn(sortJobBean.getMainJobRrn(), null, new StatusBean());
            }
            Map<Long, List<SorterDetailBean>> sorterDetailList = getSorterDetailListBySubJobRrn(
                    sorterBeans.stream().map(SorterBean::getSubJobRrn).toArray());

            dealResults(sortJobBean, sorterBeans, sorterDetailList);
            sortJobBean.setSorterBeans(sorterBeans);
            return sortJobBean;
        }

        @Override
        public List<String> getChildLotIdListByLotId(String lotId) {
            String sql =
                    "SELECT DISTINCT SJ.ATTRIBUTE_DATA4 AS CHILD_LOT_ID " + " FROM SORTER_JOB_MAPPING SJD,SORTER_JOB SJ " +
                            "WHERE SJD.LOT_ID = ? AND SJD.SUB_JOB_RRN=SJ.SUB_JOB_RRN " +
                            " ORDER BY SJ.ATTRIBUTE_DATA4 DESC";
            return jdbcTemplate.query(sql, String.class, lotId);
        }

        @Override
        public List<String> getSourceLotIdByTargetCarrier(Long carrierRrn, String jobType) {
            String sql = "SELECT DISTINCT SJD.LOT_ID " + "FROM SORTER_JOB_MAPPING SJD,SORTER_JOB SJ " +
                    "WHERE (SJ.TARGET_CARRIER_RRN = ? OR SJ.SOURCE_CARRIER_RRN = ?) AND SJ.JOB_TYPE = ? " +
                    "AND SJD.SUB_JOB_RRN=SJ.SUB_JOB_RRN";
            return jdbcTemplate.query(sql, String.class, carrierRrn, carrierRrn, jobType);
        }

        @Override
        public List<SorterBean> querySorterByCarriers(long sourceCarrierRrn, long targetCarrierRrn) {
            String sql = " SELECT SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN" + " FROM ( " +
                    " SELECT SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN,STATUS  FROM " +
                    "SORTER_JOB WHERE SOURCE_CARRIER_RRN = ? " + " UNION " +
                    " SELECT SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN,STATUS  FROM " +
                    "SORTER_JOB WHERE TARGET_CARRIER_RRN = ? " + " ) WHERE STATUS IN (?,?)";
            return jdbcTemplate.query(sql,
                                      new Object[]{sourceCarrierRrn, targetCarrierRrn, SorterEnum.Status.CREATE.getStatus(),
                                                   SorterEnum.Status.DISPATCH.getStatus()},
                                      (RowMapper<SorterBean>) (rs, rowNum) -> {
                                          SorterBean sorterBean = new SorterBean();
                                          sorterBean.setSourceCarrierId(rs.getString("SOURCE_CARRIER_ID"));
                                          sorterBean.setSourceCarrierRrn(rs.getLong("SOURCE_CARRIER_RRN"));
                                          sorterBean.setTargetCarrierRrn(rs.getLong("TARGET_CARRIER_RRN"));
                                          return sorterBean;
                                      });
        }

        @Override
        public List<SorterBean> getSoterListByCarrier(Long sourceCarrierRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT MAIN_JOB_RRN,SUB_JOB_RRN,SOURCE_CARRIER_RRN,SOURCE_CARRIER_ID,");
            sql.append("TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,JOB_TYPE,JOB_RRN ");
            sql.append("FROM SORTER_JOB ");
            sql.append("WHERE SOURCE_CARRIER_RRN=? AND STATUS IN (?,?)");

            Object[] args = new Object[]{sourceCarrierRrn, SorterEnum.Status.CREATE.getStatus(),
                                         SorterEnum.Status.DISPATCH.getStatus()};
            return jdbcTemplate.query(sql.toString(), args, (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean sorterBean = new SorterBean();
                sorterBean.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                sorterBean.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));
                sorterBean.setSourceCarrierId(rs.getString("SOURCE_CARRIER_ID"));
                sorterBean.setSourceCarrierRrn(rs.getLong("SOURCE_CARRIER_RRN"));
                sorterBean.setTargetCarrierRrn(rs.getLong("TARGET_CARRIER_RRN"));
                sorterBean.setTargetCarrierId(rs.getString("TARGET_CARRIER_ID"));
                return sorterBean;
            });
        }

        @Override
        public List<SorterBean> getSoterListByCarrier(Long sourceCarrierRrn, Long targetCarrierRrn) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT MAIN_JOB_RRN,SUB_JOB_RRN,SOURCE_CARRIER_RRN,SOURCE_CARRIER_ID,");
            sql.append("TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,JOB_TYPE,JOB_RRN ");
            sql.append("FROM SORTER_JOB ");
            sql.append("WHERE SOURCE_CARRIER_RRN=? AND TARGET_CARRIER_RRN=? AND STATUS IN (?,?)");

            Object[] args = new Object[]{sourceCarrierRrn, targetCarrierRrn, SorterEnum.Status.CREATE.getStatus(),
                                         SorterEnum.Status.DISPATCH.getStatus()};
            return jdbcTemplate.query(sql.toString(), args, (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean sorterBean = new SorterBean();
                sorterBean.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                sorterBean.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));
                sorterBean.setSourceCarrierId(rs.getString("SOURCE_CARRIER_ID"));
                sorterBean.setSourceCarrierRrn(rs.getLong("SOURCE_CARRIER_RRN"));
                sorterBean.setTargetCarrierRrn(rs.getLong("TARGET_CARRIER_RRN"));
                sorterBean.setTargetCarrierId(rs.getString("TARGET_CARRIER_ID"));
                return sorterBean;
            });
        }

        @Override
        public List<SorterBean> getJobTypeOrStatusList(String type, String language) {
            boolean needStatus = "STATUS".equalsIgnoreCase(type);
            return jdbcTemplate.query("SELECT DISTINCT " + (needStatus ? "STATUS" : "JOB_TYPE") + " FROM SORTER_JOB ",
                                      (RowMapper<SorterBean>) (rs, rowNum) -> {
                                          SorterBean s = new SorterBean();
                                          if (needStatus) {
                                              s.setStatus(rs.getString("STATUS"));
                                              s.setStatusParse(SorterEnum.Constant.parseTextType(s.getStatus(), language));
                                          } else {
                                              s.setJobType(rs.getString("JOB_TYPE"));
                                              s.setJobTypeParse(
                                                      SorterEnum.Constant.parseTextType(s.getJobType(), language));
                                          }
                                          return s;
                                      });
        }

        @Override
        public List<SortJobBean> getSortJobByCarrierRrn(long targetCarrierRrn) {
            List<Long> jobRrns = getJobRrnListByCarrier(targetCarrierRrn);
            List<SortJobBean> sortJobBeanList = new ArrayList<>();
            for (Long jr : jobRrns) {
                sortJobBeanList.add(getSortJob(new SorterBean(jr)));
            }
            return sortJobBeanList;
        }

        @Override
        public SorterDetailBean getSorterByLotRrn(Long lotRrn) {
            String sql = "SELECT SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION,TARGET_POSITION " +
                    "FROM SORTER_JOB_MAPPING WHERE LOT_RRN = ?  AND ROWNUM=1";
            return jdbcTemplate.queryForObjectWithNull(sql, new Object[]{lotRrn},
                                                       (RowMapper<SorterDetailBean>) (rs, rowNum) -> new SorterDetailBean(
                                                               rs.getLong("LOT_RRN"), rs.getString("LOT_ID"),
                                                               rs.getLong("UNIT_RRN"), rs.getString("UNIT_ID"),
                                                               rs.getInt("SOURCE_POSITION"), rs.getInt("TARGET_POSITION")));
        }

        @Override
        public List<SorterBean> getJobStatusList(SorterBean sorterBean) {
            String sql = "SELECT MAIN_JOB_RRN,SUB_JOB_RRN,STATUS,JOB_TYPE FROM SORTER_JOB WHERE ";
            Object[] arg;
            if (sorterBean.getSubJobRrn() > 0) {
                sql += " SUB_JOB_RRN = ? ";
                arg = new Object[]{sorterBean.getSubJobRrn()};
            } else {
                sql += " MAIN_JOB_RRN = ?";
                arg = new Object[]{sorterBean.getMainJobRrn()};
            }
            return jdbcTemplate.query(sql, arg, (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean s = new SorterBean();
                s.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                s.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));
                s.setStatus(rs.getString("STATUS"));
                s.setJobType(rs.getString("JOB_TYPE"));
                return s;
            });
        }

        @Override
        public List<SorterBean> getSorterBeanListByJobRrn(long jobRrn, Long subJobRrn, StatusBean status) {
            List<Object> args = new ArrayList<>();
            String sql = "SELECT MAIN_JOB_RRN,SUB_JOB_RRN,SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN," +
                    "TARGET_CARRIER_ID,STATUS,EXCHANGE_TOTAL_QTY,JOB_TYPE,CREATE_TIME,CREATE_USER,UPDATE_TIME," +
                    "ATTRIBUTE_DATA1,ATTRIBUTE_DATA2," + "ATTRIBUTE_DATA3,ATTRIBUTE_DATA4,ATTRIBUTE_DATA5,SORTER_TYPE " +
                    "FROM " + (status.hadDone() ? "SORTER_JOB_H" : "SORTER_JOB") + " WHERE MAIN_JOB_RRN = ? " +
                    (subJobRrn != null ? " AND SUB_JOB_RRN = ? " : "");
            args.add(jobRrn);
            if (subJobRrn != null) {
                args.add(subJobRrn);
            }
            if (status.noStatus()) {//没有状态时，默认查询未完成状态的数据，既：CREATE和DISPATCH
                sql += " AND STATUS IN (?,?)";
                args.add(status.getUndone()[0]);
                args.add(status.getUndone()[1]);
            } else if (status.stateful()) {
                sql += " AND STATUS = ? ";
                args.add(status.getStatus());
            }
            return buildResult(sql, args.toArray());
        }

        @Override
        public Long querySorterJobRrnByCarrierRrn(long carrierRrn, StatusBean status) {
            String sql = "SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE SOURCE_CARRIER_RRN = ? " +
                    " UNION SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE TARGET_CARRIER_RRN = ? ";
            List<String> mainJobRrnList = jdbcTemplate.query(sql, new Object[]{carrierRrn, carrierRrn}, String.class);
            if (!mainJobRrnList.isEmpty()) {
                return NumberUtils.toLong(mainJobRrnList.get(0), 0);
            } else {
                return 0L;
            }
        }

        /**
         * 根据 carrierRRN 获取SORTER_JOB表中所有的SortJob，即获取所有未执行的SortJob
         *
         * @param sourceCarrierRrn 晶舟RRN
         * @return
         */
        @Override
        public List<SorterBean> getAllSortJobBySourceCarrierRrn(long sourceCarrierRrn, StatusBean status) {
            String sql = "SELECT MAIN_JOB_RRN,SUB_JOB_RRN,SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN," +
                    "TARGET_CARRIER_ID ,STATUS,EXCHANGE_TOTAL_QTY,JOB_TYPE,CREATE_TIME,CREATE_USER,UPDATE_TIME," +
                    "ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3, ATTRIBUTE_DATA4, ATTRIBUTE_DATA5,SORTER_TYPE " +
                    " FROM SORTER_JOB  " + " WHERE SOURCE_CARRIER_RRN = ? " + " AND STATUS IN (?,?) ";
            Object[] args = new Object[]{sourceCarrierRrn, status.getUndone()[0], status.getUndone()[1]};
            return buildResult(sql, args);
        }

        @Override
        public List<String> querySorterJobRrnListByCarrierRrn(long carrierRrn) {
            String sql = "SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE SOURCE_CARRIER_RRN = ? " +
                    " UNION SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE TARGET_CARRIER_RRN = ? ";
            return jdbcTemplate.query(sql, new Object[]{carrierRrn, carrierRrn}, String.class);
        }

        @Override
        public long querySorterJobRrnBySourceAndTargetCarrierRrn(long sourceCarrierRrn, long targetCarrierRrn) {
            String sql = "SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE SOURCE_CARRIER_RRN = ? AND TARGET_CARRIER_RRN = ? ";
            List<String> mainJobRrnList = jdbcTemplate.query(sql, new Object[]{sourceCarrierRrn, targetCarrierRrn},
                                                             String.class);
            if (!mainJobRrnList.isEmpty()) {
                return NumberUtils.toLong(mainJobRrnList.get(0), 0);
            } else {
                return 0L;
            }

        }

        @Override
        public long querySorterJobRrnBySourceCarrierRrn(long sourceCarrierRrn) {
            String sql = "SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE SOURCE_CARRIER_RRN = ?  ORDER BY NVL" +
                    "(TARGET_CARRIER_RRN, 0) ASC ";
            List<String> mainJobRrnList = jdbcTemplate.query(sql, new Object[]{sourceCarrierRrn}, String.class);
            if (!mainJobRrnList.isEmpty()) {
                return NumberUtils.toLong(mainJobRrnList.get(0), 0);
            } else {
                return 0L;
            }
        }

        private Map<Long, List<SorterDetailBean>> getSorterDetailListByJobRrn(long jobRrn, StatusBean status) {
            Map<Long, List<SorterDetailBean>> listMap = new HashMap<>();
            String sql = "SELECT SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION,TARGET_POSITION " +
                    " FROM SORTER_JOB_MAPPING WHERE SUB_JOB_RRN IN (SELECT SUB_JOB_RRN FROM SORTER_JOB WHERE " +
                    " MAIN_JOB_RRN = ? AND STATUS IN (?,?)) " +
                    " UNION SELECT SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION," + "TARGET_POSITION " +
                    " FROM SORTER_JOB_MAPPING_H WHERE SUB_JOB_RRN IN (SELECT SUB_JOB_RRN FROM SORTER_JOB " +
                    "WHERE MAIN_JOB_RRN = ? AND STATUS IN (?,?)) ";
            Object[] args;
            if (status.stateful()) {//有状态参数
                if (status.hadDone()) {
                    args = new Object[]{jobRrn, status.getDone()[0], status.getDone()[1], jobRrn, status.getDone()[0],
                                        status.getDone()[1]};//状态为已完成
                } else {
                    args = new Object[]{jobRrn, status.getUndone()[0], status.getUndone()[1], jobRrn, status.getUndone()[0],
                                        status.getUndone()[1]};//状态为未完成
                }
            } else {//没有状态参数，默认查询所有
                args = new Object[]{jobRrn, status.getUndone()[0], status.getUndone()[1], jobRrn, status.getDone()[0],
                                    status.getDone()[1]};
            }
            buildResultMap(sql, args, listMap);
            return listMap;
        }

        private Map<Long, List<SorterDetailBean>> getSorterDetailListByJobRrnAndStatus(long jobRrn, StatusBean status) {
            Map<Long, List<SorterDetailBean>> listMap = new HashMap<>();
            String sql = "SELECT SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION,TARGET_POSITION " + " FROM " +
                    (status.hadDone() ? "SORTER_JOB_MAPPING_H" : "SORTER_JOB_MAPPING") +
                    " WHERE SUB_JOB_RRN IN (SELECT SUB_JOB_RRN FROM " + (status.hadDone() ? "SORTER_JOB_H" : "SORTER_JOB") +
                    " WHERE MAIN_JOB_RRN = ? AND " + "STATUS = ?) ";
            buildResultMap(sql, new Object[]{jobRrn, status.getStatus()}, listMap);
            return listMap;
        }

        private Map<Long, List<SorterDetailBean>> getSorterDetailListBySubJobRrn(Object[] subJobRrn) {
            Map<Long, List<SorterDetailBean>> listMap = new HashMap<>();
            if (subJobRrn != null && subJobRrn.length > 0) {
                StringBuilder sql = new StringBuilder(
                        "SELECT SUB_JOB_RRN,LOT_RRN,LOT_ID,UNIT_RRN,UNIT_ID,SOURCE_POSITION,TARGET_POSITION " +
                                " FROM SORTER_JOB_MAPPING WHERE SUB_JOB_RRN IN (");
                int i = 1, count = subJobRrn.length;
                for (Object ignored : subJobRrn) {
                    sql.append(i != count ? "?," : "?)");
                    i++;
                }
                buildResultMap(sql.toString(), subJobRrn, listMap);
            }
            return listMap;
        }

        private void buildResultMap(String sql, Object[] args, Map<Long, List<SorterDetailBean>> listMap) {
            jdbcTemplate.query(sql, args, (RowMapper<SorterDetailBean>) (rs, rowNum) -> {
                SorterDetailBean sorterDetailBean = new SorterDetailBean(rs.getLong("LOT_RRN"), rs.getString("LOT_ID"),
                                                                         rs.getLong("UNIT_RRN"), rs.getString("UNIT_ID"),
                                                                         rs.getInt("SOURCE_POSITION"),
                                                                         rs.getInt("TARGET_POSITION"));
                sorterDetailBean.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));

                if (listMap.containsKey(sorterDetailBean.getSubJobRrn())) {
                    List<SorterDetailBean> sdbl = listMap.get(sorterDetailBean.getSubJobRrn());
                    sdbl.add(sorterDetailBean);
                    listMap.put(sorterDetailBean.getSubJobRrn(), sdbl);
                } else {
                    List<SorterDetailBean> sdbl = new ArrayList<>();
                    sdbl.add(sorterDetailBean);
                    listMap.put(sorterDetailBean.getSubJobRrn(), sdbl);
                }
                return null;
            });
        }

        private List<SorterBean> getSorterBeanListByCarrierRrn(long sourceCarrierRrn, long targetCarrierRrn,
                                                               Long targetCarrierRrn2, StatusBean status) {
            String sql = "SELECT MAIN_JOB_RRN,SUB_JOB_RRN,SOURCE_CARRIER_ID,SOURCE_CARRIER_RRN,TARGET_CARRIER_RRN," +
                    "TARGET_CARRIER_ID" + ",STATUS,EXCHANGE_TOTAL_QTY,JOB_TYPE,CREATE_TIME,CREATE_USER,UPDATE_TIME," +
                    "ATTRIBUTE_DATA1, ATTRIBUTE_DATA2, ATTRIBUTE_DATA3, ATTRIBUTE_DATA4, ATTRIBUTE_DATA5,SORTER_TYPE " +
                    " FROM SORTER_JOB  " + " WHERE SOURCE_CARRIER_RRN = ? AND TARGET_CARRIER_RRN IN (?,?) " +
                    " AND STATUS IN (?,?) ";
            Object[] args = new Object[]{sourceCarrierRrn, targetCarrierRrn,
                                         targetCarrierRrn2 != null ? targetCarrierRrn2 : -1, status.getUndone()[0],
                                         status.getUndone()[1]};
            return buildResult(sql, args);
        }

        private List<SorterBean> buildResult(String sql, Object[] args) {
            return jdbcTemplate.query(sql, args, (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean sorterBean = new SorterBean(rs.getLong("SOURCE_CARRIER_RRN"), rs.getLong("TARGET_CARRIER_RRN"),
                                                       rs.getInt("EXCHANGE_TOTAL_QTY"), rs.getString("SOURCE_CARRIER_ID"),
                                                       rs.getString("STATUS"), rs.getString("JOB_TYPE"),
                                                       rs.getString("CREATE_USER"), new ArrayList<>());
                sorterBean.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                sorterBean.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));
                sorterBean.setTargetCarrierId(
                        StringUtils.defaultIfBlank(rs.getString("TARGET_CARRIER_ID"), StringUtils.EMPTY));
                sorterBean.setCreateTime(rs.getTimestamp("CREATE_TIME"));
                sorterBean.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
                sorterBean.setJsonAttributeData1(rs.getString("ATTRIBUTE_DATA1"));
                sorterBean.setJsonAttributeData2(rs.getString("ATTRIBUTE_DATA2"));
                sorterBean.setJsonAttributeData3(rs.getString("ATTRIBUTE_DATA3"));
                sorterBean.setJsonAttributeData4(rs.getString("ATTRIBUTE_DATA4"));
                sorterBean.setJsonAttributeData5(rs.getString("ATTRIBUTE_DATA5"));
                sorterBean.setSorterType(rs.getString("SORTER_TYPE"));
                return sorterBean;
            });
        }

        private void dealResults(SortJobBean sortJobBean, List<SorterBean> sorterBeans,
                                 Map<Long, List<SorterDetailBean>> sorterDetailList) {
            for (SorterBean sb : sorterBeans) {
                sortJobBean.setMainJobRrn(sb.getMainJobRrn());
                sb.setSorterDetailBeanList(sorterDetailList.get(sb.getSubJobRrn()));
                if (sortJobBean.getSourceCarrierId() == null) {
                    sortJobBean.setSourceCarrierId(sb.getSourceCarrierId());
                    sortJobBean.setSourceCarrierRrn(sb.getSourceCarrierRrn());
                }
                if (sortJobBean.getTargetCarrierId() == null) {//TODO 多PORT时需要修改
                    sortJobBean.setTargetCarrierId(sb.getTargetCarrierId());
                    sortJobBean.setTargetCarrierRrn(sb.getTargetCarrierRrn());
                }
                if (sortJobBean.getStatus() == null) {
                    sortJobBean.setStatus(sb.getStatus());
                    sortJobBean.setStatusParse(
                            SorterEnum.Constant.parseTextType(sb.getStatus(), I18nUtils.getCurrentLanguage().name()));
                }
                if (sortJobBean.getJobType() == null) {
                    sortJobBean.setJobType(sb.getJobType());
                    sortJobBean.setJobTypeParse(
                            SorterEnum.Constant.parseTextType(sb.getJobType(), I18nUtils.getCurrentLanguage().name()));
                }
            }
        }

        private List<Long> getJobRrnListByCarrier(long targetCarrierRrn) {
            String sql = "SELECT MAIN_JOB_RRN FROM SORTER_JOB WHERE TARGET_CARRIER_RRN = ? AND STATUS IN (?,?)";
            return jdbcTemplate.query(sql, Long.class, targetCarrierRrn, SorterEnum.Status.CREATE.getStatus(),
                                      SorterEnum.Status.DISPATCH.getStatus());
        }

        @Override
        public Page queryAllSorterList(Page page, SorterBean sorterBean) {
            List<Object> arg = new ArrayList<>();
            String whereSql = "";
            String sql = "SELECT MAIN_JOB_RRN, SUB_JOB_RRN,SOURCE_CARRIER_ID, TARGET_CARRIER_ID," +
                    "STATUS,EXCHANGE_TOTAL_QTY,JOB_TYPE,CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + "FROM (" +
                    " SELECT MAIN_JOB_RRN,TO_CHAR(listagg(SUB_JOB_RRN,',') within group(order by SUB_JOB_RRN)" +
                    ") SUB_JOB_RRN," + "TO_CHAR(listagg(SOURCE_CARRIER_ID,',') within group(order by SOURCE_CARRIER_ID)) " +
                    "SOURCE_CARRIER_ID, " +
                    " TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,SUM(EXCHANGE_TOTAL_QTY) EXCHANGE_TOTAL_QTY,JOB_TYPE," +
                    " CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + " FROM SORTER_JOB " +
                    " GROUP BY MAIN_JOB_RRN,STATUS,UPDATE_TIME,CREATE_TIME,JOB_TYPE,CREATE_USER," +
                    "TARGET_CARRIER_RRN,TARGET_CARRIER_ID,SORTER_TYPE " + " UNION ALL" +
                    " SELECT MAIN_JOB_RRN,TO_CHAR(listagg(SUB_JOB_RRN,',') within group(order by SUB_JOB_RRN)" +
                    ") SUB_JOB_RRN," + "TO_CHAR(listagg(SOURCE_CARRIER_ID,',') within group(order by SOURCE_CARRIER_ID)) " +
                    "SOURCE_CARRIER_ID, " +
                    " TARGET_CARRIER_RRN,TARGET_CARRIER_ID,STATUS,SUM(EXCHANGE_TOTAL_QTY) EXCHANGE_TOTAL_QTY,JOB_TYPE," +
                    " CREATE_USER,CREATE_TIME,UPDATE_TIME,SORTER_TYPE " + " FROM SORTER_JOB_H " +
                    " GROUP BY MAIN_JOB_RRN,STATUS,UPDATE_TIME,CREATE_TIME,JOB_TYPE,CREATE_USER," +
                    "TARGET_CARRIER_RRN,TARGET_CARRIER_ID,SORTER_TYPE " + ") ST ";

            //不管是inline还是offline都查询出来
            sql += " WHERE 1=1 ";
            // sql += " WHERE SORTER_TYPE IN (?,?)";
            // arg.add(SorterEnum.Type.INLINE.getName());
            // arg.add(SorterEnum.Type.OFFLINE.getName());

            if (StringUtils.isNotEmpty(sorterBean.getSourceCarrierId())) {
                whereSql += "AND ST.SOURCE_CARRIER_ID LIKE ?";
                arg.add("%" + sorterBean.getSourceCarrierId() + "%");
            }
            if (StringUtils.isNotEmpty(sorterBean.getTargetCarrierId())) {
                whereSql += "AND ST.TARGET_CARRIER_ID = ? ";
                arg.add(sorterBean.getTargetCarrierId());
            }
            if (StringUtils.isNotEmpty(sorterBean.getStatus())) {
                whereSql += "AND ST.STATUS = ? ";
                arg.add(sorterBean.getStatus());
            }

            if (StringUtils.isNotEmpty(sorterBean.getJobType())) {
                whereSql += "AND ST.JOB_TYPE = ? ";
                arg.add(sorterBean.getJobType());
            }
            sql += whereSql;
            sql += " ORDER BY UPDATE_TIME DESC,MAIN_JOB_RRN DESC ";
            return jdbcTemplate.queryForPage(page, sql, arg.toArray(), (RowMapper<SorterBean>) (rs, rowNum) -> {
                SorterBean s = new SorterBean();
                s.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                String subJobRrn = rs.getString("SUB_JOB_RRN");
                if (!StringUtils.contains(subJobRrn, StringUtils.COMMA_SIGN)) {
                    s.setSubJobRrn(NumberUtils.toLong(subJobRrn, 0L));
                }
                s.setSourceCarrierId(rs.getString("SOURCE_CARRIER_ID"));
                s.setTargetCarrierId(rs.getString("TARGET_CARRIER_ID"));
                s.setExchangeTotalQty(rs.getInt("EXCHANGE_TOTAL_QTY"));
                s.setJobType(rs.getString("JOB_TYPE"));
                s.setJobTypeParse(SorterEnum.Constant.parseTextType(s.getJobType(), I18nUtils.getCurrentLanguage().name()));
                s.setStatus(rs.getString("STATUS"));
                s.setStatusParse(SorterEnum.Constant.parseTextType(s.getStatus(), I18nUtils.getCurrentLanguage().name()));
                s.setCreateUser(rs.getString("CREATE_USER"));
                s.setCreateTime(rs.getTimestamp("CREATE_TIME"));
                s.setUpdateTime(rs.getTimestamp("UPDATE_TIME"));
                if (s.getUpdateTime() == null) {
                    s.setUpdateTime(s.getCreateTime());
                }
                s.setSorterType(rs.getString("SORTER_TYPE"));
                return s;
            });
        }

        @Override
        public List<SorterCarrierMap> getSorterCarrierMapList(Long subJobRrn) {
            StringBuilder sql = new StringBuilder("SELECT ");
            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(" FROM ");
            sql.append(DataBaseNames.SORTER_CARRIER_MAP_H);
            sql.append(" WHERE SUB_JOB_RRN = ? ").append(" ORDER BY CARRIER_RRN,POSITION ");

            List<SorterCarrierMap> list = jdbcTemplate
                    .query(sql.toString(), new Object[]{subJobRrn}, new RowMapper<SorterCarrierMap>() {
                        @Override
                        public SorterCarrierMap mapRow(ResultSet rs, int rowNum) throws SQLException {
                            SorterCarrierMap map = new SorterCarrierMap();
                            map.setMainJobRrn(rs.getLong("MAIN_JOB_RRN"));
                            map.setSubJobRrn(rs.getLong("SUB_JOB_RRN"));
                            map.setCarrierMapRrn(rs.getLong("CARRIER_MAP_RRN"));
                            map.setCarrierRrn(rs.getLong("CARRIER_RRN"));
                            map.setCarrierId(rs.getString("CARRIER_ID"));
                            map.setLotRrn(rs.getLong("LOT_RRN"));
                            map.setLotId(rs.getString("LOT_ID"));
                            map.setUnitRrn(rs.getLong("UNIT_RRN"));
                            map.setUnitId(rs.getString("UNIT_ID"));
                            map.setPosition(rs.getInt("POSITION"));
                            return map;
                        }
                    });
            return list;
        }

    }