1.花农报表调整 2.订单列表：异常订单 3.钱包增加体现状态查询

陶杰

2024-12-13 e96fa2622043188f3e7eeee5ddc4d203c9fa5494

1.花农报表调整
2.订单列表：异常订单
3.钱包增加体现状态查询

 src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java

@@ -91,4 +91,7 @@

    @ApiModelProperty("服务区域-区")
    private String region;

    @ApiModelProperty("异常订单状态")
    private String abnormalOrderStatus;
}

 src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java

@@ -36,7 +36,10 @@
    @DateTimeFormat(pattern="yyyy-MM-dd")
    private LocalDate approveEndDate;

    @ApiModelProperty(value = "提现状态")
    @ApiModelProperty(value = "审核状态")
    private String approveState;

    @ApiModelProperty(value = "提现状态（提现中、提现成功、提现失败）")
    private String withdrawState;

}

 src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java

@@ -35,9 +35,23 @@
    @ApiModelProperty("售后扣花农款")
    private BigDecimal salesFeeSupplier;


    @ApiModelProperty("总销售扎数")
    private Integer orderNum;

    @ApiModelProperty("实际销售扎数")
    private Integer realSaleNum;

    @ApiModelProperty("缺货扎数")
    private BigDecimal orderLackNum;

    @ApiModelProperty("降级扎数")
    private BigDecimal orderReduceNum;

    @ApiModelProperty("补货扎数")
    private BigDecimal orderReplaceNum;


    @ApiModelProperty("结算费用")
    private BigDecimal profitFeeAmount;


 src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java

@@ -77,4 +77,6 @@
    List<Order> selectAllDateList();

    void updatePaymentCreateDate(@Param("order") Order order);

    List<OrderStatusCountDTO> getAbnormalOrderStatusCount(@Param("condition") OrderQueryDTO dto);
}

 src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java

@@ -360,7 +360,8 @@
        List<OrderSupplierReportResultVO> list = orderReportMapper.getOrderSupplierReport(supplierReportCalendarBO);

        String[] rowsName = new String[]{"序号","日期", "供应商ID", "供应商","花农底价"
                , "降级扣款",  "缺货扣款（缺货+补货）","售后扣花农款","实际销售扎数","结算费用","订单状态"};
                , "降级扣款",  "缺货扣款（缺货+补货）","售后扣花农款",
                "总销售扎数","实际销售扎数","缺货扎数","降级扎数","补货扎数","结算费用","订单状态"};
        List<Object[]> dataList = new ArrayList<>();
        int sn = 1;
        for (OrderSupplierReportResultVO o : list) {
@@ -375,7 +376,13 @@
                objs[a++] = o.getOrderCheckFee(); //降级扣款
                objs[a++] = o.getOrderLackFeeSupplier();//缺货扣款
                objs[a++] = o.getSalesFeeSupplier(); // 售后扣花农款

                objs[a++] = o.getOrderNum(); // 总销售扎数
                objs[a++] = o.getRealSaleNum(); // 实际销售扎数
                objs[a++] = o.getOrderLackNum(); // 缺货扎数
                objs[a++] = o.getOrderReduceNum(); // 降级扎数
                objs[a++] = o.getOrderReplaceNum(); // 补货扎数

                objs[a++] = o.getProfitFeeAmount(); // 结算费用
                objs[a++] = o.getSettleStatus(); // 订单状态
                dataList.add(objs);

 src/main/java/com/mzl/flower/service/payment/OrderService.java

@@ -1818,4 +1818,34 @@
            orderMapper.updatePaymentCreateDate(order);
        });
    }

    public List<OrderStatusCountDTO> getAbnormalOrderStatusCount(OrderQueryDTO dto) {
         dto.setStartDate(parseLocalDateTime(dto.getStartDateStr(), true));
        dto.setEndDate(parseLocalDateTime(dto.getEndDateStr(), false));

        dto.setCreateStartDate(parseLocalDateTime(dto.getCreateStartDateStr(), 17, 0, 0, -1));
        dto.setCreateEndDate(parseLocalDateTime(dto.getCreateEndDateStr(), 17, 0, 0, 0));

        List<CodeValueDTO> ls = codeService.searchValue("ABNORMAL_ORDER_STATUS");
        List<OrderStatusCountDTO> ll = orderMapper.getAbnormalOrderStatusCount(dto);
        Map<String, Integer> llMap = new HashMap<>();
        if (ll != null && ll.size() > 0) {
            for (OrderStatusCountDTO c : ll) {
                llMap.put(c.getValue(), c.getOrderCount());
            }
        }

        List<OrderStatusCountDTO> rr = new ArrayList<>();
        for (CodeValueDTO c : ls) {
            OrderStatusCountDTO r = new OrderStatusCountDTO();
            r.setValue(c.getValue());
            r.setLabel(c.getLabel());
            Integer count = llMap.get(r.getValue());
            r.setOrderCount(count == null ? 0 : count);

            rr.add(r);
        }

        return rr;
    }
}

 src/main/java/com/mzl/flower/web/payment/OrderController.java

@@ -48,6 +48,12 @@
        return returnData(R.SUCCESS.getCode(), orderService.getOrderStatusCount(dto));
    }

    @GetMapping("/abnormal/status/count")
    @ApiOperation(value = "获取异常订单状态数量")
    public ResponseEntity<ReturnDataDTO<List<OrderStatusCountDTO>>> getAbnormalOrderStatusCount(OrderQueryDTO dto){
        return returnData(R.SUCCESS.getCode(), orderService.getAbnormalOrderStatusCount(dto));
    }

    @GetMapping("/list")
    @ApiOperation(value = "查询订单列表")
    public ResponseEntity<ReturnDataDTO<Page<OrderPlatformListDTO>>> selectOrderList(Page page, OrderQueryDTO dto){

 src/main/resources/mapper/payment/OrderMapper.xml

@@ -193,6 +193,54 @@
            AND u.nick_name LIKE concat('%', #{condition.createName},'%')
        </if>

        <if test="condition.abnormalOrderStatus != null and condition.abnormalOrderStatus != '' and condition.abnormalOrderStatus == 'UN_PROCESSED'">
            AND q.id in (
                select oi.order_id
                from t_order_item oi
                left join t_order o
                on oi.order_id=o.id
                where oi.status in ('abnormal','reduce','lack','back')
                and  o.transfer_id is null
                and o.status in ('COLLECTION','SEND')
            )
--             AND exists(
--                 select 1 from t_order_item oi
--                 where oi.order_id = q.id
--                 and oi.status in ('abnormal','reduce','lack','back')
--             )
--             and q.transfer_id is null
--             and q.status in ('COLLECTION', 'SEND')
        </if>

        <if test="condition.abnormalOrderStatus != null and condition.abnormalOrderStatus != '' and condition.abnormalOrderStatus == 'PROCESSED'">
            AND q.id in (
                select order_id from t_order_item oi where oi.status in ('abnormal','reduce','lack','back')
            )
            and q.id not in(
                select oi.order_id
                from t_order_item oi
                left join t_order o
                on oi.order_id=o.id
                where oi.status in ('abnormal','reduce','lack','back')
                and  o.transfer_id is null
                and o.status in ('COLLECTION','SEND')
            )
--             AND  exists(
--                 select 1 from t_order_item oi
--                 where oi.order_id = q.id
--                 and oi.status in ('abnormal','reduce','lack','back')
--             )
--             AND not exists(
--                 select 1 from t_order_item oi
--                 where oi.order_id = q.id
--                 and oi.status in ('abnormal','reduce','lack','back')
--                 and q.transfer_id is null
--                 and q.status in ('COLLECTION', 'SEND')
--             )


        </if>

        ORDER BY q.create_time desc, q.payment_time desc
    </select>

@@ -429,6 +477,166 @@
        FROM t_order t
        order by t.create_time desc
    </select>
    <select id="getAbnormalOrderStatusCount"
            resultType="com.mzl.flower.dto.response.payment.OrderStatusCountDTO">
        SELECT 'UN_PROCESSED' as value, count(1) orderCount
        FROM t_order q
        left join t_user u on q.create_by = u.id
        WHERE q.deleted = 0
        <include refid="getAbnormalOrderStatusCount_condition"></include>

        AND q.id in (
            select oi.order_id
            from t_order_item oi
            left join t_order o
            on oi.order_id=o.id
            where oi.status in ('abnormal','reduce','lack','back')
            and  o.transfer_id is null
            and o.status in ('COLLECTION','SEND')
        )

        union

        SELECT 'PROCESSED' as value, count(1) orderCount
        FROM t_order q
        left join t_user u on q.create_by = u.id
        WHERE q.deleted = 0
        <include refid="getAbnormalOrderStatusCount_condition"></include>
        AND q.id in (
            select order_id from t_order_item oi where oi.status in ('abnormal','reduce','lack','back')
        )
        and q.id not in(
            select oi.order_id
            from t_order_item oi
            left join t_order o
            on oi.order_id=o.id
            where oi.status in ('abnormal','reduce','lack','back')
            and  o.transfer_id is null
            and o.status in ('COLLECTION','SEND')
        )

    </select>

    <select id="getAbnormalOrderStatusCount2"
            resultType="com.mzl.flower.dto.response.payment.OrderStatusCountDTO">
        SELECT 'UN_PROCESSED' as value, count(1) orderCount
        FROM t_order q
        left join t_user u on q.create_by = u.id
        WHERE q.deleted = 0
        <include refid="getAbnormalOrderStatusCount_condition"></include>

        AND exists(
        select 1 from t_order_item oi
        where oi.order_id = q.id
        and oi.status in ('abnormal','reduce','lack','back')
        )
        and q.transfer_id is null
        and q.status in ('COLLECTION', 'SEND')

        union

        SELECT 'PROCESSED' as value, count(1) orderCount
        FROM t_order q
        left join t_user u on q.create_by = u.id
        WHERE q.deleted = 0
        <include refid="getAbnormalOrderStatusCount_condition"></include>
        AND  exists(
        select 1 from t_order_item oi
        where oi.order_id = q.id
        and oi.status in ('abnormal','reduce','lack','back')
        )
        AND not exists(
        select 1 from t_order_item oi
        where oi.order_id = q.id
        and oi.status in ('abnormal','reduce','lack','back')
        and q.transfer_id is null
        and q.status in ('COLLECTION', 'SEND')
        )

    </select>
    
    <sql id="getAbnormalOrderStatusCount_condition">
        <if test="condition.orderNo != null and condition.orderNo != ''">
            AND q.order_no LIKE concat('%', #{condition.orderNo},'%')
        </if>
        <if test="condition.customerAddress != null and condition.customerAddress != ''">
            AND q.customer_address LIKE concat('%', #{condition.customerAddress},'%')
        </if>
        <if test="condition.customer != null and condition.customer != ''">
            AND (q.customer LIKE concat('%', #{condition.customer},'%')
            or q.customer_tel LIKE concat('%', #{condition.customer},'%')
            )
        </if>
        <if test="condition.createBy != null and condition.createBy != ''">
            AND q.create_by = #{condition.createBy}
        </if>
        <if test="condition.status != null and condition.status != ''">
            AND q.status = #{condition.status}
        </if>
        <if test="condition.statusBackend != null and condition.statusBackend != ''">
            AND q.status_backend = #{condition.statusBackend}
        </if>
        <!--        <if test="condition.startDate != null">-->
        <!--            AND q.payment_time &gt;= #{condition.startDate}-->
        <!--        </if>-->
        <!--        <if test="condition.endDate != null">-->
        <!--            AND q.payment_time &lt;= #{condition.endDate}-->
        <!--        </if>-->
        <!--        <if test="condition.createStartDate != null">-->
        <!--            AND q.create_time &gt;= #{condition.createStartDate}-->
        <!--        </if>-->
        <!--        <if test="condition.createEndDate != null">-->
        <!--            AND q.create_time &lt;= #{condition.createEndDate}-->
        <!--        </if>-->
        <if test="condition.createStartDate != null">
            AND q.payment_time &gt;= #{condition.createStartDate}
        </if>
        <if test="condition.createEndDate != null">
            AND q.payment_time &lt; #{condition.createEndDate}
        </if>
        <if test="condition.partnerId != null">
            AND q.partner_id = #{condition.partnerId}
        </if>
        <if test="condition.partnerName != null and condition.partnerName != ''">
            AND q.partner_name LIKE concat('%', #{condition.partnerName},'%')
        </if>
<!--        <if test="condition.levelDown != null and condition.levelDown != ''">-->
<!--            AND exists(-->
<!--            select 1 from t_order_item oi-->
<!--            where oi.order_id = q.id-->
<!--            and (oi.status = 'reduce' or oi.status = 'abnormal')-->
<!--            )-->
<!--            and q.transfer_id is null-->
<!--            and q.status in ('COLLECTION', 'SEND')-->
<!--        </if>-->
        <if test="condition.billId != null and condition.billId != ''">
            AND q.bill_id = #{condition.billId}
        </if>
        <if test="condition.flowerName != null and condition.flowerName != ''">
            AND exists(
            select 1 from t_order_item oi
            where oi.order_id = q.id
            and oi.flower_name LIKE concat('%', #{condition.flowerName},'%')
            )
        </if>

        <if test="condition.warehouseLocationCode != null and condition.warehouseLocationCode != ''">
            AND q.warehouse_location_code LIKE concat('%', #{condition.warehouseLocationCode},'%')
        </if>
        <if test="condition.createName != null and condition.createName != ''">
            AND u.nick_name LIKE concat('%', #{condition.createName},'%')
        </if>
        <if test="condition.province != null and condition.province != ''">
            AND q.customer_province LIKE concat('%', #{condition.province},'%')
        </if>
        <if test="condition.city != null and condition.city != ''">
            AND q.customer_city LIKE concat('%', #{condition.city},'%')
        </if>
        <if test="condition.region != null and condition.region != ''">
            AND q.customer_region LIKE concat('%', #{condition.region},'%')
        </if>
    </sql>
    

    <update id="updatePaymentCreateDate">
        update t_order  set payment_date_sta=#{order.paymentDate}, create_date_sta=#{order.createDate} where id=#{order.id}

 src/main/resources/mapper/report/OrderReportMapper.xml

@@ -267,6 +267,7 @@
    <sql id="baseSql">
        select t2.*,
               IFNULL(t2.order_check_fee+t2.order_lack_fee_supplier+t2.order_replace_fee,0) as order_check_total_fee,
               IFNULL(t2.order_price_discount_amount+t2.order_coupon_amount_total,0) as order_discount_total_fee,
               IFNULL(t3.fee_supplier,0) as order_fee_supplier,
               IFNULL(t3.fee_partner,0) as order_fee_partner,
               IFNULL(t3.fee_platform,0) as order_fee_platform,
@@ -480,7 +481,9 @@
        sum( t1.replace_fee ) AS order_replace_fee,
        sum( t1.lack_fee_supplier ) AS order_lack_fee_supplier,
        sum( t1.num ) AS order_num,
        sum( t1.lack_num ) AS order_lack_num
        sum( t1.lack_num ) AS order_lack_num,
        sum( t1.reduce_num ) AS order_reduce_num,
        sum( t1.replace_num ) AS order_replace_num
        FROM
        (
        SELECT
@@ -496,8 +499,11 @@
        IFNULL( oi.supplier_price, 0 ) * IFNULL( oi.num, 0 ) AS supplier_price_amount,
        IFNULL( ois.check_fee, 0 ) AS check_fee,
        IFNULL( ois.replace_fee, 0 ) AS replace_fee,
        IFNULL( ois.lack_fee_supplier, 0 ) AS lack_fee_supplier,
        IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num
        IFNULL( ois.lack_fee_supplier, 0 ) AS lack_fee_supplier

        ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'replace' and oic.audit_status='AGREED'),0) as replace_num
        ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'reduce' and oic.audit_status='AGREED'),0) as reduce_num
        ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num
        FROM
        t_order o
        LEFT JOIN t_order_item oi ON oi.order_id = o.id
@@ -531,7 +537,11 @@
        sum(order_supplier_price_amount) order_supplier_price_amount,
        sum(order_check_fee) order_check_fee,
        sum(order_replace_fee) + sum(order_lack_fee_supplier) order_lack_fee_supplier,
        sum(order_num) as order_num,
        sum(order_num)-sum(order_lack_num) as real_sale_num,
        sum(order_lack_num) as order_lack_num,
        sum(order_reduce_num ) AS order_reduce_num,
        sum(order_replace_num ) AS order_replace_num,
        sum(sales_fee_supplier) sales_fee_supplier,
        sum(order_supplier_price_amount) -   sum(order_check_fee) - sum(order_replace_fee) -  sum(order_lack_fee_supplier) -  sum(sales_fee_supplier) profitFeeAmount,
        case when  COUNT(CASE WHEN status_backend ='COMPLETED' THEN NULL ELSE 1 END)  = 0 Then '已完成' Else '进行中' end as settleStatus
@@ -558,7 +568,11 @@
        sum(order_supplier_price_amount) order_supplier_price_amount,
        sum(order_check_fee) order_check_fee,
        sum(order_replace_fee) + sum(order_lack_fee_supplier) order_lack_fee_supplier,
        sum(order_num) as order_num,
        sum(order_num)-sum(order_lack_num) as real_sale_num,
        sum(order_lack_num) as order_lack_num,
        sum(order_reduce_num ) AS order_reduce_num,
        sum(order_replace_num ) AS order_replace_num,
        sum(sales_fee_supplier) sales_fee_supplier,
        sum(order_supplier_price_amount) -   sum(order_check_fee) - sum(order_replace_fee) -  sum(order_lack_fee_supplier) -  sum(sales_fee_supplier) profitFeeAmount,
        case when  COUNT(CASE WHEN status_backend ='COMPLETED' THEN NULL ELSE 1 END)  = 0 Then '已完成' Else '进行中' end as settleStatus

 src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml

@@ -34,6 +34,10 @@
        <if test="dto.approveState != null and dto.approveState != ''">
            and t.approve_state = #{dto.approveState}
        </if>
        <if test="dto.withdrawState != null and dto.withdrawState != ''">
            and t.withdraw_state = #{dto.withdrawState}
        </if>

        <if test="dto.createStartDate!=null ">
               AND DATE_FORMAT(t.create_time, '%Y-%m-%d') &gt;= #{dto.createStartDate}
        </if>