调序的业务处理方法

来源：互联网发布：阿里云增值税专用发票编辑：程序博客网时间：2024/06/06 08:03

在开发过程中，碰到了这样的一个业务：车辆订单下发到车间之前，可以进行订单的调序，操作页面是这样的：

输入两个车辆的id，然后根据radio的值判断是移动到后面还是前面，然后点击移动进行调序。

刚开始的时候以为这个会很简单，就是把顺序号改改就完了，后来才了解到，车辆的id，也可以理解为顺序号，并不是连续的，而且车辆订单还有状态这么一个限制条件，直接更改顺序号可能会导致出现重复数据或者乱序等糟糕的情况。然后经过讨论，定了一个解决方案：在顺序号字段productSeq的后面添加一个临时顺序号字段：productSeqTemp。然后要实现的调序就是这样一个过程（显示的号为车辆的id号，顺序号初始化是1——n的正整数）：

而在数据库中要做的事情是，把移动车辆的顺序号变成和目标车辆一样的顺序号，移动车辆的temp值为1，目标车辆的temp值变成0。然后查询的时候，sql语句中order by productSeq,productSeqTemp。这样就可以按照顺序排下来了，也达到了移动车辆的目的：

当然，这样仅仅是移动到目标车辆的之后，还有一种移动到之前的情况，还有目标车辆是第一辆也就是要把移动车辆置顶的情况。这些都是衍生出来的，主要思想就是上面的图所示，仅仅改变移动车辆的顺序号，而不会影响到其他的顺序。下面给出完整的方法：

 /**     * @param orderNoSource  移动车辆的id号     * @param orderSeqTarget 目标车辆的id号     * @param direction      移动方向： A:表示移动到之后,B:表示移动到之前     * @return     */    @Override    public int moveOrderSeq(String orderNoSource, String orderSeqTarget, String direction) {        if (StringUtil.isNullOrBlank(orderNoSource) || StringUtil.isNullOrBlank(orderSeqTarget) || StringUtil.isNullOrBlank(direction)) {            return 0;        }        int count = 0;        if ("A".equals(direction)) {//移动到之后的情况            StringBuilder sqlA1 = new StringBuilder();            StringBuilder sqlA2 = new StringBuilder();            sqlA1.append(" update to_pp_order ");            sqlA1.append("    set PRODUCT_SEQ_TEMP = to_number(nvl(PRODUCT_SEQ_TEMP, '0')) + 1 ");            sqlA1.append("  where PRODUCT_SEQ = (select PRODUCT_SEQ ");            sqlA1.append("                         from to_pp_order ");            sqlA1.append("                        where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlA1.append("                          and ORDER_STATUS_TYPE_I = 0) ");            sqlA1.append("    and PRODUCT_SEQ_TEMP > ");            sqlA1.append("        (select PRODUCT_SEQ_TEMP ");            sqlA1.append("           from to_pp_order ");            sqlA1.append("          where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlA1.append("            and ORDER_STATUS_TYPE_I = 0) ");            int countA1 = getDao().updateNativeSQL(sqlA1.toString(), null);            sqlA2.append(" update to_pp_order ");            sqlA2.append("    set (PRODUCT_SEQ, PRODUCT_SEQ_TEMP) = ");            sqlA2.append("        (select PRODUCT_SEQ, to_number(nvl(PRODUCT_SEQ_TEMP,'0')) + 1 ");            sqlA2.append("           from to_pp_order ");            sqlA2.append("          where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlA2.append("            and ORDER_STATUS_TYPE_I = 0) ");            sqlA2.append("  where PLSORDER_NO_S = '" + orderNoSource + "' ");            count = getDao().updateNativeSQL(sqlA2.toString(), null);        } else if ("B".equals(direction)) {//移动到之前的情况            StringBuilder sqlB1 = new StringBuilder();            StringBuilder sqlB2 = new StringBuilder();            sqlB1.append(" update TO_PP_ORDER ");            sqlB1.append("    set PRODUCT_SEQ_TEMP = to_number(nvl(PRODUCT_SEQ_TEMP, '0')) + 1 ");            sqlB1.append("  where PRODUCT_SEQ = (select PRODUCT_SEQ ");            sqlB1.append("                         from TO_PP_ORDER ");            sqlB1.append("                        where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlB1.append("                          and ORDER_STATUS_TYPE_I = 0) ");            sqlB1.append("    and to_number(nvl(PRODUCT_SEQ_TEMP,'0')) >= ");            sqlB1.append("        (select to_number(nvl(PRODUCT_SEQ_TEMP,'0')) ");            sqlB1.append("           from TO_PP_ORDER ");            sqlB1.append("          where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlB1.append("            and ORDER_STATUS_TYPE_I = 0) ");            int countB1 = getDao().updateNativeSQL(sqlB1.toString(), null);            sqlB2.append(" update TO_PP_ORDER ");            sqlB2.append("    set (PRODUCT_SEQ, PRODUCT_SEQ_TEMP) = ");            sqlB2.append("        (select PRODUCT_SEQ, to_number(nvl(PRODUCT_SEQ_TEMP,'0')) - 1 ");            sqlB2.append("           from TO_PP_ORDER ");            sqlB2.append("          where PLS_SEQUENCE = '" + orderSeqTarget + "' ");            sqlB2.append("            and ORDER_STATUS_TYPE_I = 0) ");            sqlB2.append("  where PLSORDER_NO_S = '" + orderNoSource + "' ");            count = getDao().updateNativeSQL(sqlB2.toString(), null);        }        return count;    }    /**     * 车辆订单置顶操作     * @param orderNo 车辆订单id号     * @return     */    @Override    public int moveOrderTop(String orderNo) {        if (StringUtil.isNullOrBlank(orderNo)) {            return 0;        }        int count = 0;        StringBuilder sqlA1 = new StringBuilder();        StringBuilder sqlA2 = new StringBuilder();        sqlA1.append(" update TO_PP_ORDER ");        sqlA1.append("    set PRODUCT_SEQ_TEMP = to_number(nvl(PRODUCT_SEQ_TEMP, '0')) + 1 ");        sqlA1.append("  where (PRODUCT_SEQ || '-' || nvl(PRODUCT_SEQ_TEMP, 0)) >= ");        sqlA1.append("        (select min(PRODUCT_SEQ || '-' || nvl(PRODUCT_SEQ_TEMP, 0)) ");        sqlA1.append("           from TO_PP_ORDER ");        sqlA1.append("          where ORDER_STATUS_TYPE_I = 0) ");        int countA1 = getDao().updateNativeSQL(sqlA1.toString(), null);        sqlA2.append(" update TO_PP_ORDER ");        sqlA2.append("    set (PRODUCT_SEQ, PRODUCT_SEQ_TEMP) = ");        sqlA2.append("        (select PRODUCT_SEQ, '0' from TO_PP_ORDER ");        sqlA2.append("  where (PRODUCT_SEQ || '-' || nvl(PRODUCT_SEQ_TEMP, 0)) = ");        sqlA2.append("        (select min(PRODUCT_SEQ || '-' || nvl(PRODUCT_SEQ_TEMP, 0)) ");        sqlA2.append("           from TO_PP_ORDER ");        sqlA2.append("          where ORDER_STATUS_TYPE_I = 0)) ");        sqlA2.append("   where PLSORDER_NO_S='" + orderNo + "' ");        count = getDao().updateNativeSQL(sqlA2.toString(), null);        return count;    }

0 0