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

发票列表查询优化建议

问题描述

当前发票列表查询没有关联项目表和结算表，导致无法显示项目名称、结算年份、结算季度等信息。

需求

发票列表需要显示以下字段：

• 项目编号 (projectCode)
• 项目名称 (projectName)
• 结算年份 (settlementYear)
• 结算季度 (settlementQuarter)
• 税率 (taxRate)

数据库表关系

biz_invoice (发票表)
    |
    |-- settlementId (结算ID) --> biz_settlement (结算表)
    |                              |
    |                              |-- projectId --> biz_project (项目表)
    |                              |-- contractId --> biz_contract (合同表)
    |
    |-- contractId (合同ID) --> biz_contract (合同表)

SQL 查询优化建议

当前查询（可能的问题）

SELECT 
    bi.*,
    ba.attachmentCount
FROM biz_invoice bi
LEFT JOIN (
    SELECT businessId, COUNT(*) as attachmentCount
    FROM biz_attachment
    WHERE businessType = 'invoice'
    GROUP BY businessId
) ba ON bi.business_id = ba.businessId
-- 问题：缺少关联 biz_settlement 和 biz_project 表

优化后的查询

SELECT 
    bi.invoice_id,
    bi.invoice_no,
    bi.invoice_title_a,
    bi.invoice_title_b,
    bi.invoice_amount,
    bi.settlement_id,
    bi.contract_id,
    bi.tax_rate,
    bi.status,
    bi.payment_date,
    bi.payment_remark,
    bi.remark,
    bi.create_time,
    -- 结算表字段
    bs.settlement_year,
    bs.settlement_quarter,
    bs.settlement_quarter_name,
    -- 项目表字段
    bp.project_id,
    bp.project_code,
    bp.project_name,
    -- 合同表字段
    bc.contract_code,
    bc.contract_name,
    bc.contract_amount,
    -- 附件数量
    COALESCE(ba.attachment_count, 0) as attachment_count
FROM biz_invoice bi
LEFT JOIN biz_settlement bs ON bi.settlement_id = bs.settlement_id
LEFT JOIN biz_project bp ON bs.project_id = bp.project_id
LEFT JOIN biz_contract bc ON bi.contract_id = bc.contract_id
LEFT JOIN (
    SELECT business_id, COUNT(*) as attachment_count
    FROM biz_attachment
    WHERE business_type = 'invoice'
    GROUP BY business_id
) ba ON bi.invoice_id = ba.business_id
WHERE 1=1
ORDER BY bi.create_time DESC

MyBatis-Plus 配置建议

如果使用 MyBatis-Plus，可以使用以下方式：

方案1：使用 @TableField (注解方式)

public class BizInvoice {
    @TableId
    private Long invoiceId;
    
    @TableField("settlement_id")
    private Long settlementId;
    
    // 以下字段不对应数据库表字段，仅用于返回前端
    @TableField(exist = false)
    private String projectCode;
    
    @TableField(exist = false)
    private String projectName;
    
    @TableField(exist = false)
    private String settlementYear;
    
    @TableField(exist = false)
    private String settlementQuarter;
    
    @TableField(exist = false)
    private String taxRate;
    
    // ... 其他字段
}

方案2：使用 VO 类

创建一个 BizInvoiceVO 类：

@Data
public class BizInvoiceVO extends BizInvoice {
    private String projectCode;
    private String projectName;
    private String settlementYear;
    private String settlementQuarter;
    private String settlementQuarterName;
    private String taxRate;
    private String taxRateName;
    private String contractCode;
    private String contractName;
    private BigDecimal contractAmount;
    private Integer attachmentCount;
}

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

@Mapper
public interface BizInvoiceMapper extends BaseMapper<BizInvoice> {
    
    @Select("""
        SELECT 
            bi.*,
            bs.settlement_year,
            bs.settlement_quarter,
            bs.settlement_quarter_name,
            bp.project_code,
            bp.project_name,
            bc.contract_code,
            bc.contract_name,
            bc.contract_amount,
            COALESCE(ba.attachment_count, 0) as attachment_count
        FROM biz_invoice bi
        LEFT JOIN biz_settlement bs ON bi.settlement_id = bs.settlement_id
        LEFT JOIN biz_project bp ON bs.project_id = bp.project_id
        LEFT JOIN biz_contract bc ON bi.contract_id = bc.contract_id
        LEFT JOIN (
            SELECT business_id, COUNT(*) as attachment_count
            FROM biz_attachment
            WHERE business_type = 'invoice'
            GROUP BY business_id
        ) ba ON bi.invoice_id = ba.business_id
        ${ew.customSqlSegment}
    """)
    Page<BizInvoiceVO> selectInvoicePage(
        @Param("page") Page<BizInvoiceVO> page,
        @Param("ew") Wrapper<BizInvoice> queryWrapper
    );
}

查询条件处理

查询时支持按以下条件筛选：

• 发票抬头(甲方) - invoice_title_a
• 发票抬头(乙方) - invoice_title_b
• 结算信息ID - settlement_id
• 发票状态 - status

// 示例查询条件
QueryWrapper<BizInvoice> wrapper = new QueryWrapper<>();
wrapper.like(StringUtils.isNotBlank(params.getInvoiceTitleA()), 
    "invoice_title_a", params.getInvoiceTitleA())
    .like(StringUtils.isNotBlank(params.getInvoiceTitleB()), 
        "invoice_title_b", params.getInvoiceTitleB())
    .eq(params.getSettlementId() != null, 
        "settlement_id", params.getSettlementId())
    .eq(StringUtils.isNotBlank(params.getStatus()), 
        "status", params.getStatus());

数据返回格式

{
  "code": 200,
  "msg": "查询成功",
  "rows": [
    {
      "invoiceId": 1,
      "invoiceNo": "INV2024001",
      "invoiceTitleA": "甲方公司",
      "invoiceTitleB": "乙方公司",
      "invoiceAmount": 10000.00,
      "settlementId": 10,
      "projectId": 5,
      "projectCode": "PRJ001",
      "projectName": "某某项目",
      "settlementYear": "2024",
      "settlementQuarter": "1",
      "taxRate": "6",
      "taxRateName": "6%",
      "contractId": 3,
      "contractCode": "CONTRACT001",
      "contractName": "服务合同",
      "contractAmount": 50000.00,
      "status": "0",
      "paymentDate": null,
      "remark": null,
      "createTime": "2024-01-01 10:00:00",
      "attachmentCount": 2
    }
  ],
  "total": 100
}

注意事项

1. LEFT JOIN 使用：确保使用 LEFT JOIN，避免因为没有关联数据而导致发票记录不显示
2. 字段映射：确保数据库字段与Java实体类字段正确映射（使用下划线转驼峰）
3. 字典转换：后端需要将字典代码转换为字典名称
   • settlementQuarter (1,2,3,4) -> settlementQuarterName (第一季度,第二季度...)
   • taxRate (6,13) -> taxRateName (6%,13%)
4. 性能优化：为关联字段添加索引
   • biz_invoice.settlement_id
   • biz_invoice.contract_id
   • biz_settlement.project_id

验证方法

1. 查看发票列表API返回的数据是否包含项目名称、结算年份、结算季度等字段
2. 确认这些字段有正确的值（不是null或空）
3. 前端列表能正确显示这些信息