xykj-plus/xykj-web-plus/backend-optimization-contract-list.md

合同列表查询优化建议

问题描述

合同列表需要显示发票相关的金额信息，需要关联发票表进行计算：

• 合同金额：合同本身的金额
• 已开票金额：该合同下所有发票的开票金额总和
• 已付款金额：已付款的发票金额总和
• 未确认开票金额：合同金额 - 已开票金额（即尚未开票的金额）

数据库表关系

biz_contract (合同表)
    |
    |-- contract_id (合同ID)
    |
    |-- biz_invoice (发票表)
            |
            |-- contract_id (关联合同ID)
            |-- invoice_amount (开票金额)
            |-- status (发票状态: 0=已开票未付款, 1=已开票已付款)

SQL 查询优化建议

当前查询（可能缺少发票关联）

SELECT 
    bc.*,
    COUNT(ba.attachment_id) as attachment_count
FROM biz_contract bc
LEFT JOIN biz_attachment ba ON bc.contract_id = ba.business_id AND ba.business_type = 'contract'
WHERE 1=1
-- 问题：缺少发票表的关联

优化后的查询

SELECT 
    bc.contract_id,
    bc.contract_code,
    bc.contract_name,
    bc.contract_amount,
    bc.contract_type,
    -- 已开票金额（该合同下所有发票的总金额）
    COALESCE(invoiced.total_invoiced_amount, 0) as invoiced_amount,
    -- 已付款金额（已付款的发票总金额）
    COALESCE(paid.total_paid_amount, 0) as paid_amount,
    -- 未确认开票金额（合同金额 - 已开票金额）
    bc.contract_amount - COALESCE(invoiced.total_invoiced_amount, 0) as unpaid_amount,
    -- 附件数量
    COALESCE(attachment_count, 0) as attachment_count
FROM biz_contract bc
-- 关联已开票金额统计
LEFT JOIN (
    SELECT 
        contract_id,
        SUM(invoice_amount) as total_invoiced_amount
    FROM biz_invoice
    WHERE status IN ('0', '1')  -- 已开票状态
    GROUP BY contract_id
) invoiced ON bc.contract_id = invoiced.contract_id
-- 关联已付款金额统计
LEFT JOIN (
    SELECT 
        contract_id,
        SUM(invoice_amount) as total_paid_amount
    FROM biz_invoice
    WHERE status = '1'  -- 已付款状态
    GROUP BY contract_id
) paid ON bc.contract_id = paid.contract_id
-- 附件数量统计
LEFT JOIN (
    SELECT 
        business_id,
        COUNT(*) as attachment_count
    FROM biz_attachment
    WHERE business_type = 'contract'
    GROUP BY business_id
) attachments ON bc.contract_id = attachments.business_id
WHERE 1=1
ORDER BY bc.create_time DESC

MyBatis-Plus 配置建议

方案1：在 VO 类中添加统计字段

创建 BizContractVO 类：

@Data
public class BizContractVO extends BizContract {
    /** 已开票金额 */
    private BigDecimal invoicedAmount;
    
    /** 已付款金额 */
    private BigDecimal paidAmount;
    
    /** 未确认开票金额 */
    private BigDecimal unpaidAmount;
    
    /** 附件数量 */
    private Integer attachmentCount;
}

方案2：在 Mapper 中使用关联查询

@Mapper
public interface BizContractMapper extends BaseMapper<BizContract> {
    
    @Select("""
        SELECT 
            bc.contract_id,
            bc.contract_code,
            bc.contract_name,
            bc.contract_amount,
            bc.contract_type,
            bc.paid_amount,
            bc.unpaid_amount,
            COALESCE(invoiced.total_invoiced_amount, 0) as invoiced_amount,
            bc.contract_amount - COALESCE(invoiced.total_invoiced_amount, 0) as unpaid_amount,
            COALESCE(paid.total_paid_amount, 0) as paid_amount,
            COALESCE(attachments.attachment_count, 0) as attachment_count
        FROM biz_contract bc
        LEFT JOIN (
            SELECT contract_id, SUM(invoice_amount) as total_invoiced_amount
            FROM biz_invoice
            WHERE status IN ('0', '1')
            GROUP BY contract_id
        ) invoiced ON bc.contract_id = invoiced.contract_id
        LEFT JOIN (
            SELECT contract_id, SUM(invoice_amount) as total_paid_amount
            FROM biz_invoice
            WHERE status = '1'
            GROUP BY contract_id
        ) paid ON bc.contract_id = paid.contract_id
        LEFT JOIN (
            SELECT business_id, COUNT(*) as attachment_count
            FROM biz_attachment
            WHERE business_type = 'contract'
            GROUP BY business_id
        ) attachments ON bc.contract_id = attachments.business_id
        ${ew.customSqlSegment}
    """)
    Page<BizContractVO> selectContractPage(
        @Param("page") Page<BizContractVO> page,
        @Param("ew") Wrapper<BizContract> queryWrapper
    );
}

字段计算逻辑说明

1. 合同金额 (contract_amount)

• 直接使用合同表中的 contract_amount 字段
• 金额由合同创建时确定

2. 已开票金额 (invoiced_amount)

SUM(invoice_amount) 
WHERE contract_id = ? 
  AND status IN ('0', '1')  -- 已开票状态（已开票未付款 + 已开票已付款）

• 计算该合同下所有已开票的发票金额总和
• 不论是否付款，只要开票就算

3. 已付款金额 (paid_amount)

SUM(invoice_amount) 
WHERE contract_id = ? 
  AND status = '1'  -- 已付款状态

• 计算该合同下已付款的发票金额总和
• 只有状态为"已开票已付款"的发票才算

4. 未确认开票金额 (unpaid_amount)

contract_amount - invoiced_amount

• 合同金额减去已开票金额
• 表示尚未开票的金额

数据返回格式示例

{
  "code": 200,
  "msg": "查询成功",
  "rows": [
    {
      "contractId": 1,
      "contractCode": "CONTRACT001",
      "contractName": "技术服务合同",
      "contractAmount": 100000.00,
      "contractTypeName": "人月合同",
      "invoicedAmount": 60000.00,
      "paidAmount": 40000.00,
      "unpaidAmount": 40000.00,
      "attachmentCount": 2,
      "createTime": "2024-01-01 10:00:00"
    }
  ],
  "total": 100
}

前端显示

列名	说明	示例
合同编号	合同的唯一编号	CONTRACT001
合同名称	合同的名称	技术服务合同
合同金额(元)	合同总金额	100,000.00
已开票金额(元)	该合同已开票金额总和	60,000.00
已付款金额(元)	该合同已付款金额总和	40,000.00
未付款金额(元)	合同金额-已开票金额	40,000.00

注意事项

1. LEFT JOIN 使用：确保使用 LEFT JOIN，避免因为没有发票数据的合同导致记录不显示
2. 金额计算精度：使用 DECIMAL 或 BIGINT 类型存储金额，避免精度丢失
3. NULL 值处理：使用 COALESCE(value, 0) 将 NULL 转换为 0
4. 性能优化：为关联字段添加索引
   • biz_invoice.contract_id
   • biz_invoice.status
5. 数据一致性：确保发票状态正确更新（从"已开票未付款"到"已开票已付款"）

验证方法

1. 查看合同列表API返回的数据是否包含 invoicedAmount 字段
2. 确认金额计算正确：
   • invoicedAmount = 所有发票金额总和
   • paidAmount = 已付款发票金额总和
   • unpaidAmount = contractAmount - invoicedAmount
3. 前端列表能正确格式化显示金额（千分位分隔，保留两位小数）