1.供应商-钱包：统计修改

陶杰

2024-11-08 78082b601d0eda9b820fbeba9a01fbfeb5309692

1.供应商-钱包：统计修改

 src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java

@@ -25,5 +25,8 @@
    @ApiModelProperty(value = "供应商ID")
    private Long supplierId;

    @ApiModelProperty(value = "支付时间")
    private String paymentTime;


}

 src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java

@@ -30,4 +30,6 @@
    BigDecimal getCheckReduceAmount(@Param("dto") QueryWalletAmountDTO queryWalletAmountDTO);

    BigDecimal getSaleReduceAmount(@Param("dto")QueryWalletAmountDTO queryWalletAmountDTO);

    BigDecimal getStationFeeAmount(@Param("dto") QueryWalletAmountDTO queryWalletAmountDTO);
}

 src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java

@@ -42,4 +42,9 @@
    public BigDecimal getSaleReduceAmount(QueryWalletAmountDTO queryWalletAmountDTO) {
        return walletReduceMapper.getSaleReduceAmount(queryWalletAmountDTO);
    }

    @Override
    public BigDecimal getStationFeeAmount(QueryWalletAmountDTO queryWalletAmountDTO) {
        return walletReduceMapper.getStationFeeAmount(queryWalletAmountDTO);
    }
}

 src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java

@@ -171,8 +171,12 @@
        BigDecimal saleDeduceAmount = walletReduceService.getSaleReduceAmount(queryWalletAmountDTO);
        saleDeduceAmount = saleDeduceAmount != null ? saleDeduceAmount : BigDecimal.ZERO;

        // 集货站运费
        BigDecimal stationFeeAmount = walletReduceService.getStationFeeAmount(queryWalletAmountDTO);
        stationFeeAmount = stationFeeAmount != null ? stationFeeAmount : BigDecimal.ZERO;

        // 总扣款
        BigDecimal deduceAmount = checkDeduceAmount.add(saleDeduceAmount);
        BigDecimal deduceAmount = checkDeduceAmount.add(saleDeduceAmount).add(stationFeeAmount);

        return deduceAmount;
    }

 src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java

@@ -35,4 +35,11 @@
     * @return
     */
    BigDecimal getSaleReduceAmount(QueryWalletAmountDTO queryWalletAmountDTO);

    /**
     * 获取集货站扣款数量
     * @param queryWalletAmountDTO
     * @return
     */
    BigDecimal getStationFeeAmount(QueryWalletAmountDTO queryWalletAmountDTO);
}

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

@@ -23,7 +23,9 @@

    </resultMap>
    <select id="getWaittingSettlementAmount" resultType="java.math.BigDecimal">
         <include refid="supplier_settlement_amount"></include>
      <!-- <include refid="supplier_settlement_amount"></include>-->
        <include refid="supplier_settlement_amount_timing"></include>

    </select>

    <!--    总交易额-->
@@ -32,7 +34,9 @@
        from t_order_item oi
        left join t_order o
        on oi.order_id = o.id
        where oi.supplier_id = #{dto.supplierId}
        where o.deleted=0
          and o.status_backend not in ('PENDING','CANCEL','REFUND')
          and  oi.supplier_id = #{dto.supplierId}
          and o.payment_time &gt; #{dto.startTime}
    </select>

@@ -47,4 +51,40 @@
          AND USER_ID=#{dto.userId}
    </sql>


    <sql id="supplier_settlement_amount_timing">
        select
            sum(t2.final_price) as settlement_amount
        from(
                select
                    t.order_item_id,
                    t.supplier_id,
                    t.supplier_price,
                    t.num-t.reduce_num-t.replace_num-t.lack_num as final_num,
                    t.supplier_price*(t.num-t.reduce_num-t.replace_num-t.lack_num )-t.fee_supplier-t.station_fee as final_price,
                    t.fee_supplier,
                    t.station_fee
                from (
                         SELECT
                             o.order_no,
                             oi.id as order_item_id,
                             oi.supplier_id,
                             o.payment_time,
                             oi.supplier_price,
                             oi.num as num,
                             IFNULL( (select oic.num from t_order_item_check oic where oic.deleted=0 and  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.deleted=0 and  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.deleted=0 and  oic.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num,
                             IFNULL( (select ois.fee_supplier from t_order_item_sales ois where ois.deleted=0 and ois.order_item_id = oi.id and ois.status='AGREED'),0) as fee_supplier,
                             IFNULL( (select ois.station_fee from t_order_item_settlement ois where ois.deleted=0 and ois.order_item_id = oi.id ),0) as station_fee
                         FROM t_order_item oi
                         LEFT JOIN t_order o ON oi.order_id = o.id
                         WHERE o.deleted=0
                           and o.status_backend not in ('PENDING','CANCEL','REFUND','COMPLETED')
                           and oi.supplier_id = #{dto.supplierId}
                     ) t
            ) t2

    </sql>

</mapper>

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

@@ -6,57 +6,78 @@
    <select id="getPage" resultType="com.mzl.flower.dto.response.wallet.WalletReduceVO">
        select *
        from (
                 SELECT si.id as id,si.fee_supplier as fee ,si.audit_time as audit_time,'sale' as type,'售后扣款' as type_name,o.order_no as order_no,oi.supplier_id as supplier_id
                 SELECT si.id as id
                      ,si.fee_supplier as fee
                      ,si.audit_time as audit_time
                      ,o.payment_time as payment_time
                      ,'sale' as type,'售后扣款' as type_name
                      ,o.order_no as order_no
                      ,oi.supplier_id as supplier_id
                 FROM t_order_item_sales si
                 join t_order_item oi on oi.id = si.order_item_id
                 left join t_order o on oi.order_id=o.id
                 where si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
                 where o.deleted=0
                 and o.status_backend not in ('PENDING','CANCEL','REFUND')
                 and si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
                and o.payment_time &gt;= #{dto.walletOnlineTime}
                <if test="dto.supplierId != null">
                    and oi.supplier_id = #{dto.supplierId}
                </if>
<!--                <if test="dto.startDate != null">-->
<!--                    and DATE_FORMAT(si.audit_time, '%Y-%m-%d') &gt;= #{dto.startDate}-->
<!--                </if>-->
<!--                <if test="dto.endDate != null">-->
<!--                    and DATE_FORMAT(si.audit_time, '%Y-%m-%d') &lt;= #{dto.endDate}-->
<!--                </if>-->
                <if test="dto.startDate != null">
                    and DATE_FORMAT(si.audit_time, '%Y-%m-%d') &gt;= #{dto.startDate}
                    and DATE_FORMAT(o.payment_time, '%Y-%m-%d') &gt;= #{dto.startDate}
                </if>
                <if test="dto.endDate != null">
                    and DATE_FORMAT(si.audit_time, '%Y-%m-%d') &lt;= #{dto.endDate}
                    and DATE_FORMAT(o.payment_time, '%Y-%m-%d') &lt;= #{dto.endDate}
                </if>
                 union

                 select  oic.id as id ,oic.deduct_amount as fee,oic.audit_time as audit_time,oic.type as type,check_type.label as type_name,o.order_no as order_no,oi.supplier_id as supplier_id
                 select  oic.id as id ,
                         oic.num*oi.supplier_price AS fee,
                         oic.audit_time AS audit_time,
                         o.payment_time as payment_time,
                         oic.type as type,
                         check_type.label as type_name,
                         o.order_no as order_no,
                         oi.supplier_id as supplier_id
                 from t_order_item_check oic
                 left join t_order_item oi
                 on oic.order_item_id=oi.id
                 left join t_order o
                 on oi.order_id=o.id
                 left join t_code_value check_type
                on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
                 where oic.deleted=0  and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
                 on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
                 where
                    o.deleted=0
                    and o.status_backend not in ('PENDING','CANCEL','REFUND')
                    and  oic.deleted=0  and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
                    and o.payment_time &gt;= #{dto.walletOnlineTime}
                <if test="dto.supplierId != null">
                    and oi.supplier_id = #{dto.supplierId}
                </if>
<!--                <if test="dto.startDate != null">-->
<!--                    and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') &gt;= #{dto.startDate}-->
<!--                </if>-->
<!--                <if test="dto.endDate != null">-->
<!--                    and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') &lt;= #{dto.endDate}-->
<!--                </if>-->
                <if test="dto.startDate != null">
                    and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') &gt;= #{dto.startDate}
                    and DATE_FORMAT(o.payment_time, '%Y-%m-%d') &gt;= #{dto.startDate}
                </if>
                <if test="dto.endDate != null">
                    and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') &lt;= #{dto.endDate}
                    and DATE_FORMAT(o.payment_time, '%Y-%m-%d') &lt;= #{dto.endDate}
                </if>
             ) t

        order by audit_time desc
    </select>
    <select id="getCheckReduceAmount" resultType="java.math.BigDecimal">
        SELECT sum(si.fee_supplier)
        FROM t_order_item_sales si
        join t_order_item oi on oi.id = si.order_item_id
        left join t_order o on oi.order_id=o.id
        where si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
          and oi.supplier_id = #{dto.supplierId}
          and o.payment_time &gt;= #{dto.startTime}
    </select>
    <select id="getSaleReduceAmount" resultType="java.math.BigDecimal">
        select  sum(oic.deduct_amount)
        select  sum(oic.num*oi.supplier_price)
        from t_order_item_check oic
        left join t_order_item oi
        on oic.order_item_id=oi.id
@@ -64,7 +85,32 @@
        on oi.order_id=o.id
        left join t_code_value check_type
        on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
        where oic.deleted=0  and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
        where o.deleted=0
          and o.status_backend not in ('PENDING','CANCEL','REFUND')
          and oic.deleted=0  and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
          and oi.supplier_id = #{dto.supplierId}
          and o.payment_time &gt;= #{dto.startTime}
    </select>
    <select id="getSaleReduceAmount" resultType="java.math.BigDecimal">

        SELECT sum(si.fee_supplier)
        FROM t_order_item_sales si
        join t_order_item oi on oi.id = si.order_item_id
        left join t_order o on oi.order_id=o.id
        where o.deleted=0
          and o.status_backend not in ('PENDING','CANCEL','REFUND')
          and  si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
          and oi.supplier_id = #{dto.supplierId}
          and o.payment_time &gt;= #{dto.startTime}
    </select>
    <select id="getStationFeeAmount" resultType="java.math.BigDecimal">
        SELECT sum(ois.station_fee)
        FROM t_order_item_settlement ois
        join t_order_item oi on oi.id = ois.order_item_id
        left join t_order o on oi.order_id=o.id
        where o.deleted=0
          and o.status_backend not in ('PENDING','CANCEL','REFUND')
          and  ois.deleted=0 and oi.deleted=0
          and oi.supplier_id = #{dto.supplierId}
          and o.payment_time &gt;= #{dto.startTime}
    </select>