fix: 优化供应商交易查询逻辑，修复付款金额和应付金额计算问题

yuan

6 天以前 bb5a4ce65ce5a2d68829cf88df4704cb079ce628

fix: 优化供应商交易查询逻辑，修复付款金额和应付金额计算问题

 src/main/resources/mapper/basic/SupplierManageMapper.xml

@@ -70,44 +70,74 @@
        </where>
    </select>
    <select id="supplierTransactions" resultType="com.ruoyi.purchase.vo.SupplierTransactionsVo">
        select T1.supplier_id,
        select c.supplier_id,
               sm.supplier_name,
               T1.contractAmounts,
               IFNULL(T2.paymentAmount, 0) AS paymentAmount,
               IFNULL(T3.InboundAmount, 0) - IFNULL(T4.returnAmount, 0) AS payableAmount
        from (select supplier_id, sum(contract_amount) as contractAmounts from purchase_ledger group by supplier_id) T1
        left join (select supplier_id, sum(payment_amount) as paymentAmount from account_purchase_payment group by supplier_id) T2 on T1.supplier_id = T2.supplier_id
        left join (
            SELECT
                pl.supplier_id,
                sum(sir.stock_in_num * slp.tax_inclusive_unit_price) AS InboundAmount
            FROM stock_in_record sir
                     -- 10 类型才关联质检表
                     LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
                -- 动态关联采购（自动适配 7 和 10）
                     LEFT JOIN purchase_ledger pl
                               ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
                -- 产品关联不动
                     LEFT JOIN sales_ledger_product slp ON pl.id = slp.sales_ledger_id
            -- 条件
            WHERE sir.approval_status = 1 AND slp.type = 2
              AND sir.record_type IN ('7','10')
            group by pl.supplier_id
        ) T3 on T3.supplier_id=T1.supplier_id
               c.contractAmounts,
               -- 付款金额：逐合同按合同金额封顶后再汇总，避免重复付款导致付款金额大于合同金额
               c.paymentAmount,
               -- 应付金额：逐合同「取(入库金额, 合同金额)较小者 - 封顶后付款金额 - 退货金额」后再汇总，下限为 0
               c.payableAmount
        from (
            select pl.supplier_id,
                   sum(COALESCE(pl.contract_amount, 0)) as contractAmounts,
                   sum(LEAST(IFNULL(pay.paymentAmount, 0), COALESCE(pl.contract_amount, 0))) as paymentAmount,
                   sum(GREATEST(
                       IFNULL(ib.InboundAmount, 0)
                           - IF(ib.InboundAmount <![CDATA[ > ]]> COALESCE(pl.contract_amount, 0), ib.InboundAmount - COALESCE(pl.contract_amount, 0), 0)
                       - LEAST(IFNULL(pay.paymentAmount, 0), COALESCE(pl.contract_amount, 0))
                       - IFNULL(rt.returnAmount, 0)
                   , 0)) as payableAmount
            from purchase_ledger pl
        left join (
            select
                supplier_id,
                sum(total_amount) as returnAmount
                    t.purchase_ledger_id as id,
                    sum(t.payment_amount) as paymentAmount
                from (
                    select
                        app.id,
                        app.payment_amount,
                        pl.id as purchase_ledger_id
                    from account_purchase_payment app
                    left join account_payment_application apa on app.account_payment_application_id = apa.id
                    left join stock_in_record sir on FIND_IN_SET(sir.id, apa.stock_in_record_ids) > 0
                    LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
                    LEFT JOIN purchase_ledger pl
                              ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
                    WHERE sir.approval_status = 1
                      AND sir.record_type IN ('7','10')
                    group by app.id, pl.id
                ) t
                group by t.purchase_ledger_id
            ) pay on pay.id = pl.id
            left join (
                SELECT
                    pl.id,
                    sum(sir.stock_in_num * slp.tax_inclusive_unit_price) AS InboundAmount
                FROM stock_in_record sir
                         LEFT JOIN quality_inspect qi ON sir.record_type = 10 AND sir.record_id = qi.id
                         LEFT JOIN purchase_ledger pl
                                   ON pl.id = IF(sir.record_type = 7, sir.record_id, qi.purchase_ledger_id)
                         LEFT JOIN sales_ledger_product slp ON pl.id = slp.sales_ledger_id
                WHERE sir.approval_status = 1 AND slp.type = 2
                  AND sir.record_type IN ('7','10')
                group by pl.id
            ) ib on ib.id = pl.id
            left join (
                select pl.id,
                       sum(pro.total_amount) as returnAmount
            from purchase_return_orders pro
            group by supplier_id
        ) T4 on T4.supplier_id=T1.supplier_id
        left join supplier_manage sm on T1.supplier_id = sm.id
                         left join purchase_ledger pl on pro.purchase_ledger_id = pl.id
                group by pl.id
            ) rt on rt.id = pl.id
            group by pl.supplier_id
        ) c
        left join supplier_manage sm on c.supplier_id = sm.id
        <where>
            <if test="supplierName!=null and supplierName!=''">
                AND sm.supplier_name LIKE CONCAT('%',#{supplierName},'%')
            </if>
        </where>
        order by T1.supplier_id desc
        order by c.supplier_id desc
    </select>
    <select id="supplierTransactionsDetails"
            resultType="com.ruoyi.purchase.vo.SupplierTransactionsDetailsVo">
@@ -115,8 +145,17 @@
              pl.purchase_contract_number,
              pl.execution_date,
              pl.contract_amount,
              IFNULL(T1.paymentAmount, 0) AS paymentAmount,
              IFNULL(T2.InboundAmount, 0) - IFNULL(T3.returnAmount, 0) AS payableAmount
              -- 付款金额按合同金额封顶，避免重复付款导致付款金额大于合同金额
              LEAST(IFNULL(T1.paymentAmount, 0), COALESCE(pl.contract_amount, 0)) AS paymentAmount,
              -- 应付金额 = 取(入库金额, 合同金额)的较小者 - 封顶后付款金额 - 退货金额
              -- 入库金额按合同金额封顶，避免入库流程重复提交导致应付金额大于合同金额；
              -- 已付款金额（回款/付款单）与退货金额均需从应付中扣减。
              GREATEST(
                  IFNULL(T2.InboundAmount, 0)
                      - IF(T2.InboundAmount <![CDATA[ > ]]> COALESCE(pl.contract_amount, 0), T2.InboundAmount - COALESCE(pl.contract_amount, 0), 0)
                  - LEAST(IFNULL(T1.paymentAmount, 0), COALESCE(pl.contract_amount, 0))
                  - IFNULL(T3.returnAmount, 0)
              , 0) AS payableAmount
       from purchase_ledger pl
       left join (
           select