视图sql

zc

2024-07-29 7ff441cde857c41ae9908100de9c469ed5ab76be

视图sql

 src/main/java/asr_show_diff.sql

@@ -1,44 +1,57 @@
SELECT SUM
       ( asrsQty ) asrsQty,
       SUM ( erpQty ) erpQty,
       matnr,
       specs,
       maktx
    ( asrsQty ) asrsQty,
    SUM ( erpQty ) erpQty,
    matnr,
    maktx,
    pgNo,
    outOrderNo,
    batch,
    model ,specs
FROM
    (
        SELECT CAST
               ( SUM ( anfme ) AS INT ) asrsQty,
               0 AS erpQty,
               matnr,
               specs,
               maktx
        FROM
            asr_loc_detl
        GROUP BY
            matnr,
            specs,
            maktx UNION ALL
        SELECT
            0,
            CAST ( SUM ( numis ) AS INT ),
            itemid,
            itemcode,
            itemname
        FROM
            (
                SELECT
                    *
                FROM
                    OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage )
        WHERE
            LKName = '中扬' 
        ) a 
    (
    SELECT CAST
        ( SUM ( anfme ) AS INT ) asrsQty,
        0 AS erpQty,
        matnr,
        model,
        maktx,
        pg_no AS pgNo,
        out_order_no AS outOrderNo,
        batch ,specs
    FROM
        asr_loc_detl
    GROUP BY
        matnr,
        maktx,
        pg_no,
        out_order_no,
        batch,
        model,specs UNION ALL
    SELECT
        0,
        CAST ( SUM ( numis ) AS INT ),
        itemid,
        itemcode,
        itemname,
        '' AS pgNo,
        '' AS outOrderNo,
        itembatch AS batch
        ,itemSpc specs
    FROM
        ( SELECT * FROM OPENROWSET ( 'SQLOLEDB', '192.168.1.147,1800'; 'XGMLK'; 'xunda0413', XDLinkLK.dbo.vwErpLKStorage ) WHERE LKName = '中扬二期' ) a
    GROUP BY
        itemid,
        itemcode,
        itemname 
    ) b WHERE 1=1 and matnr not in (select matnr from man_order_detl)
        itemname,
        itembatch ,itemSpc
    ) b
WHERE
    1 = 1
    AND matnr NOT IN ( SELECT matnr FROM man_order_detl )
GROUP BY
    matnr,
    specs,
    maktx
    model,
    maktx,
    pgNo,
    outOrderNo,
    batch,specs

 src/main/java/com/zy/asrs/controller/MobileController.java

@@ -159,7 +159,6 @@
                elem.setBatch(elem.getItemBatch());
            }
        });
        System.out.println(combParam);
        mobileService.comb(combParam, getUserId());
        return R.ok("组托成功");
    }
@@ -428,12 +427,14 @@
    @RequestMapping("/bill/query1/auth")
    @ManagerAuth(memo = "根据通知单查询明细")
    public R billQuery1(@RequestParam String billNo, @RequestParam String size) {
        System.out.println("-------------------------");
        System.out.println(billNo);
        System.out.println(size);
        List<OrderDetl> waitMatins = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("pg_no", billNo).like("model", size).last("and anfme > work_qty"));
//        List<WaitMatin> waitMatins = waitMatinService.selectList(new EntityWrapper<WaitMatin>().eq("bill_no", billNo).last("and qty > in_qty"));

        Order temp1 = orderService.selectOne(new EntityWrapper<Order>().eq("temp1", billNo));
        if (temp1 == null) {
            throw new CoolException("该订单不存在");
        }
        List<OrderDetl> waitMatins = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("order_no", temp1.getOrderNo()).like("model", size).last("and anfme > work_qty"));
        if (waitMatins == null || waitMatins.isEmpty()) {
            throw new CoolException("该订单项不存在");
        }
        //如果通知单单号为空，则查询调拨单号
//        if (Cools.isEmpty(waitMatins) || waitMatins.size() <= 0) {
//            waitMatins = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("temp1", billNo).like("size", size).last("and anfme > work_qty"));
@@ -456,17 +457,10 @@
                vo.setItemBatch(waitMatin.getBatch());
                //vo.setOveFlag(waitMatin.getOveFlag());
                vos.add(vo);
                System.out.println(waitMatin.getAnfme());
                System.out.println(waitMatin.getWorkQty());
                System.out.println(vo);
//                10.0
//                0.0
//                CombBillQueryVo(billNo=ZJFLC2407280005, seqNo=1, matNo=41011659, matName=弹簧盘, specs=null, size=null, color=null, count=10.0, qty=0.0, unit=Pcs, type=null, itemBatch=, oveFlag=null)
//                10.0
//                0.0
//                CombBillQueryVo(billNo=ZJFLC2407280005, seqNo=2, matNo=4103020554, matName=支架, specs=null, size=null, color=null, count=10.0, qty=0.0, unit=Pcs, type=null, itemBatch=, oveFlag=null)
            }
        }
        return R.ok().add(vos);
    }


}

 src/main/java/com/zy/asrs/service/impl/MobileServiceImpl.java

@@ -149,11 +149,8 @@
            }
            // 关联组托
        } else {
            List<OrderDetl> pg_no = orderDetlService.selectList(new EntityWrapper<OrderDetl>().eq("pg_no", param.getOrderNo()));
            if (pg_no == null || pg_no.isEmpty()) {
                throw new CoolException("单据编号已过期");
            }
            Order order = orderService.selectByNo(pg_no.get(0).getOrderNo());

            Order order = orderService.selectOne(new EntityWrapper<Order>().eq("temp1", param.getOrderNo()));
            if (Cools.isEmpty(order) || order.getSettle() > 2) {
                throw new CoolException("单据编号已过期");
            }

 src/main/java/com/zy/asrs/service/impl/OrderServiceImpl.java

@@ -74,6 +74,7 @@
            // 出库订单重新整理明细
            DocType docType = docTypeService.selectById(order.getDocType());
            if (null != docType && docType.getPakout() == 1) {

                if (!orderDetlService.delete(new EntityWrapper<OrderDetl>().eq("order_id", order.getId()))) {
                    throw new CoolException("重整出库订单【orderNo = " + order.getOrderNo() + "】明细失败");
                }

 src/main/java/com/zy/asrs/task/handler/WorkLogHandler.java

@@ -44,7 +44,6 @@
    @Transactional
    public ReturnT<String> start(WrkMast wrkMast) {
        try {
            System.out.println("-----------------------------------------------------");
            System.out.println(DateUtils.convert(new Date()));
            List<WrkDetl> wrkDetls = wrkDetlService.selectByWrkNo(wrkMast.getWrkNo());
            // 修改订单状态 作业中 ===>> 已完成
@@ -58,7 +57,6 @@
                    }
                }
            }
            System.out.println(DateUtils.convert(new Date()));
            // 入库 --------------------------------------------------------------------------------
            if (wrkMast.getWrkSts() == 5) {
                // 全板入库
@@ -97,7 +95,6 @@
//                    }
//                }
            }
            System.out.println(DateUtils.convert(new Date()));
            // 保存工作主档历史档
            if (!wrkMastLogService.save(wrkMast.getWrkNo())) {
                exceptionHandle("保存工作历史档[workNo={0}]失败", wrkMast.getWrkNo());
@@ -114,7 +111,6 @@
            if (!wrkDetlService.delete(new EntityWrapper<WrkDetl>().eq("wrk_no", wrkMast.getWrkNo()))) {
//                exceptionHandle("删除工作明细档[workNo={0}]失败", wrkMast.getWrkNo());
            }
            System.out.println(DateUtils.convert(new Date()));
        } catch (Exception e) {
            log.error("fail", e);
            e.printStackTrace();

 src/main/java/com/zy/common/web/WcsController.java

@@ -53,6 +53,9 @@
    @Autowired
    private StaDescService staDescService;

    @Autowired
    private OrderService orderService;


    @PostMapping("/pakin/loc/v1")
    @ResponseBody
@@ -354,6 +357,11 @@
            wrkDetl.setIoTime(wrkMast.getIoTime());
            wrkDetl.setAppeTime(now);
            wrkDetl.setModiTime(now);
            if (!Cools.isEmpty(waitPakin.getOrderNo())) {
                Order order = orderService.selectByNo(waitPakin.getOrderNo());
                wrkDetl.setSPgNO(order.getTemp2());

            }
            if (!wrkDetlService.insert(wrkDetl)) {
                throw new CoolException("保存工作明细失败");
            }

 src/main/java/com/zy/third/erp/task/ERPInHedTBScheduler.java

@@ -50,7 +50,7 @@
                hedAndDet.put("det", inDetTBS);
                boolean result = inMS.createInOrder(inHed, inDetTBS);
                if (result) {
                    log.info("入库单据成功,{}", inHed.getBillNo());
                    log.info("入库单据成功,{}", inHed);
                    move(hedAndDet);
                } else {
                    String sql = "UPDATE erp_InHedTB SET Temp3 = ‘fault’ WHERE BillNo = '" + inHed.getBillNo() + "'";

 src/main/java/com/zy/third/erp/task/ERPOutHedTBScheduler.java

@@ -50,7 +50,7 @@
                hedAndDet.put("det", inDetTBS);
                boolean result = inMS.createOutOrder(inHed, inDetTBS);
                if (result) {
                    log.info("出库单据成功,{}", inHed.getBillNo());
                    log.info("出库单据成功,{}", inHed);
                    move(hedAndDet);
                } else {
                    String sql = "UPDATE erp_OutHedTB SET Temp3 = ‘fault’ WHERE BillNo = '" + inHed.getBillNo() + "'";