package com.ruoyi.sales;
|
|
import com.ruoyi.RuoYiApplication;
|
import com.ruoyi.account.pojo.AccountIncome;
|
import com.ruoyi.account.service.AccountIncomeService;
|
import com.ruoyi.sales.mapper.ReceiptPaymentMapper;
|
import com.ruoyi.sales.mapper.SalesLedgerMapper;
|
import com.ruoyi.sales.mapper.SalesLedgerProductMapper;
|
import com.ruoyi.sales.pojo.ReceiptPayment;
|
import com.ruoyi.sales.pojo.SalesLedger;
|
import com.ruoyi.sales.pojo.SalesLedgerProduct;
|
import com.ruoyi.sales.service.impl.ReceiptPaymentServiceImpl;
|
import lombok.Getter;
|
import lombok.Setter;
|
import org.junit.jupiter.api.Disabled;
|
import org.junit.jupiter.api.Test;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.boot.test.context.SpringBootTest;
|
import org.springframework.jdbc.core.BeanPropertyRowMapper;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
|
import org.springframework.test.annotation.Rollback;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import javax.sql.DataSource;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.time.LocalDate;
|
import java.util.Collections;
|
import java.util.Date;
|
import java.util.List;
|
|
import static org.junit.jupiter.api.Assertions.assertTrue;
|
|
/**
|
* 回款批量结清 —— 依据库脚本 {@code product-inventory-management-hbtmblc.sql} 中表结构与真实 INSERT 样本编写。
|
* <p>
|
* <b>表结构摘要（与脚本一致）</b>
|
* <ul>
|
* <li>{@code receipt_payment}：{@code sales_ledger_id}、{@code sales_ledger_product_id} 为 {@code int}；
|
* {@code invoice_ledger_id} 为 {@code bigint}；{@code receipt_payment_type} 为 {@code char(1)}（0/1）；
|
* {@code receipt_payment_amount} {@code decimal(10,2)}；{@code receipt_payment_date} 为 {@code datetime}。</li>
|
* <li>脚本中回款样本：{@code invoice_ledger_id} 与 {@code sales_ledger_product_id} 取值相同（例如 2158），
|
* 与回款登记页把列表行主键当作 {@code invoiceLedgerId} 提交的行为一致。</li>
|
* <li>{@code sales_ledger_product}：待回款/已回款列为 {@code pending_invoice_total}、{@code invoice_total}（注释为回款总金额）；
|
* 产品行无 {@code tenant_id}，租户在 {@code sales_ledger.tenant_id}。</li>
|
* <li>{@code account_income}：{@code business_id} 为 {@code int}，与 {@code receipt_payment.id} 对应；
|
* {@code income_type='3'}、{@code income_described='回款收入'}、{@code business_type=1} 与库中回款样本一致。</li>
|
* <li>{@code registrant} / {@code input_user}：与脚本里 {@code receipt_payment} 登记人字段一致（样本为「樊志英」）。</li>
|
* </ul>
|
* <p>
|
* <b>列表页</b>：{@code GET /sales/product/listPageSalesLedger} → {@code SalesLedgerProductMapper.listPage}，
|
* 筛选「待回款>0」即 {@code pending_invoice_total > 0}。
|
* <p>
|
* <b>单笔保存</b>：{@link ReceiptPaymentServiceImpl#receiptPaymentSaveOrUpdate(java.util.List)}（本测试复用其更新逻辑与收入字段含义）。
|
* <p>
|
* <b>开票台账校验</b>：结清前根据 {@code invoice_ledger} + {@code invoice_registration_product} 汇总开票金额，
|
* 与已有 {@code receipt_payment} 比对；若开票侧已全部覆盖（按产品汇总回款或按真实开票台账 id 汇总回款），则跳过该产品，避免重复结清。
|
* <p>
|
* 执行前去掉 {@link Disabled}；默认 {@link Rollback}，落库时改为 {@code @Rollback(false)}。
|
*/
|
@SpringBootTest(classes = RuoYiApplication.class)
|
//@Disabled("连接 hbtmblc 库并配置 spring.profiles 后去掉本注解再运行")
|
class InvoiceLedgerPendingReceiptBatchTest {
|
|
/**
|
* 对应 {@code sales_ledger.tenant_id}；null 表示不按租户过滤。
|
*/
|
private static final Long TENANT_ID = null;
|
|
/**
|
* 仅处理这些 {@code sales_ledger_product.id}（与列表行主键一致）；空表示全部待回款产品行。
|
*/
|
private static final List<Integer> ONLY_SALES_LEDGER_PRODUCT_IDS = Collections.emptyList();
|
|
/**
|
* 与 {@code product-inventory-management-hbtmblc.sql} 中 {@code receipt_payment} 样本的 {@code registrant} 一致（如 id=286 一行：樊志英）。
|
* 若导入库中登记人不同，请改成与你库 {@code receipt_payment.registrant} 实际取值相同。
|
*/
|
private static final String REGISTRANT = "樊志英";
|
private static final String RECEIPT_PAYMENT_TYPE = "0";
|
private static final LocalDate RECEIPT_PAYMENT_DATE = LocalDate.now();
|
|
/**
|
* 是否在结清前校验「开票台账是否已回款完毕」。关闭则仅看 {@code pending_invoice_total}。
|
*/
|
private static final boolean VERIFY_INVOICE_LEDGER_PAID = true;
|
|
/**
|
* 开票金额与已回款比较时的容差（元）
|
*/
|
private static final BigDecimal INVOICE_PAY_TOLERANCE = new BigDecimal("0.02");
|
|
/**
|
* 与 listPage 维度一致：待回款 = {@code pending_invoice_total}。
|
*/
|
private static final String PENDING_BY_PRODUCT_SQL =
|
"SELECT slp.id AS salesLedgerProductId, "
|
+ "slp.sales_ledger_id AS salesLedgerId, "
|
+ "sl.tenant_id AS tenantId "
|
+ "FROM sales_ledger_product slp "
|
+ "INNER JOIN sales_ledger sl ON sl.id = slp.sales_ledger_id "
|
+ "WHERE slp.type = 1 "
|
+ "AND IFNULL(slp.pending_invoice_total, 0) > 0 ";
|
|
@Autowired
|
private DataSource dataSource;
|
|
@Autowired
|
private ReceiptPaymentMapper receiptPaymentMapper;
|
|
@Autowired
|
private SalesLedgerMapper salesLedgerMapper;
|
|
@Autowired
|
private SalesLedgerProductMapper salesLedgerProductMapper;
|
|
@Autowired
|
private AccountIncomeService accountIncomeService;
|
|
@Test
|
@Transactional
|
void batchCompletePendingReceipts_hbtmblcSchema() {
|
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
|
StringBuilder sql = new StringBuilder(PENDING_BY_PRODUCT_SQL);
|
MapSqlParameterSource params = new MapSqlParameterSource();
|
if (!ONLY_SALES_LEDGER_PRODUCT_IDS.isEmpty()) {
|
sql.append(" AND slp.id IN (:productIds) ");
|
params.addValue("productIds", ONLY_SALES_LEDGER_PRODUCT_IDS);
|
}
|
sql.append(" ORDER BY slp.id ASC ");
|
|
List<ProductPendingRow> rows = named.query(sql.toString(), params,
|
new BeanPropertyRowMapper<>(ProductPendingRow.class));
|
|
int done = 0;
|
for (ProductPendingRow row : rows) {
|
SalesLedger salesLedger = salesLedgerMapper.selectById(row.getSalesLedgerId().longValue());
|
if (salesLedger == null) {
|
throw new IllegalStateException("sales_ledger_product.id=" + row.getSalesLedgerProductId() + " 关联 sales_ledger 不存在");
|
}
|
SalesLedgerProduct product = salesLedgerProductMapper.selectById(row.getSalesLedgerProductId().longValue());
|
if (product == null) {
|
throw new IllegalStateException("sales_ledger_product.id=" + row.getSalesLedgerProductId() + " 不存在");
|
}
|
|
BigDecimal remaining = product.getPendingInvoiceTotal() == null ? BigDecimal.ZERO : product.getPendingInvoiceTotal();
|
if (remaining.compareTo(BigDecimal.ZERO) <= 0) {
|
continue;
|
}
|
remaining = remaining.setScale(2, RoundingMode.HALF_UP);
|
|
if (VERIFY_INVOICE_LEDGER_PAID && isInvoiceLedgerSideFullyReceived(named, row.getSalesLedgerProductId())) {
|
continue;
|
}
|
|
BigDecimal invoicedTotal = product.getInvoiceTotal() == null ? BigDecimal.ZERO : product.getInvoiceTotal();
|
BigDecimal taxTotal = product.getTaxInclusiveTotalPrice() == null ? BigDecimal.ZERO : product.getTaxInclusiveTotalPrice();
|
product.setInvoiceTotal(invoicedTotal.add(remaining));
|
product.setPendingInvoiceTotal(taxTotal.subtract(product.getInvoiceTotal()));
|
|
ReceiptPayment rp = new ReceiptPayment();
|
rp.setReceiptPaymentType(RECEIPT_PAYMENT_TYPE);
|
rp.setReceiptPaymentAmount(remaining);
|
rp.setRegistrant(REGISTRANT);
|
rp.setInvoiceLedgerId(row.getSalesLedgerProductId());
|
rp.setSalesLedgerId(row.getSalesLedgerId().longValue());
|
rp.setSalesLedgerProductId(row.getSalesLedgerProductId().longValue());
|
rp.setReceiptPaymentDate(RECEIPT_PAYMENT_DATE);
|
rp.setTenantId(row.getTenantId());
|
receiptPaymentMapper.insert(rp);
|
|
AccountIncome income = new AccountIncome();
|
income.setIncomeDate(salesLedger.getEntryDate());
|
income.setIncomeType("3");
|
income.setCustomerName(salesLedger.getCustomerName());
|
income.setIncomeMoney(remaining);
|
income.setIncomeDescribed("回款收入");
|
income.setIncomeMethod(RECEIPT_PAYMENT_TYPE);
|
income.setInputTime(new Date());
|
income.setInputUser(REGISTRANT);
|
if (rp.getId() != null) {
|
income.setBusinessId(rp.getId().longValue());
|
}
|
income.setBusinessType(1);
|
income.setTenantId(row.getTenantId());
|
accountIncomeService.save(income);
|
|
salesLedgerProductMapper.updateById(product);
|
done++;
|
}
|
assertTrue(done >= 0, "新增回款笔数: " + done);
|
}
|
|
/**
|
* 判断本产品关联的「开票台账」是否已在回款侧结清（无需再插入回款）。
|
* <ul>
|
* <li>无开票记录（Σ invoice_total 为 0）：返回 false，不据此拦截。</li>
|
* <li>有开票：若 {@code receipt_payment} 按 {@code sales_ledger_product_id} 汇总已 ≥ 开票总额，视为已回款（兼容 hbtmblc 将 invoice_ledger_id 存成产品 id 的写法）。</li>
|
* <li>否则：若每条 {@code invoice_ledger} 按真实 {@code invoice_ledger_id} 汇总的已回款均 ≥ 该行发票金额，也视为已回款。</li>
|
* </ul>
|
*/
|
private boolean isInvoiceLedgerSideFullyReceived(NamedParameterJdbcTemplate named, int salesLedgerProductId) {
|
MapSqlParameterSource p = new MapSqlParameterSource("pid", salesLedgerProductId);
|
|
BigDecimal invoicedSum = named.queryForObject(
|
"SELECT COALESCE(SUM(il.invoice_total), 0) FROM invoice_ledger il "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "WHERE irp.sales_ledger_product_id = :pid",
|
p, BigDecimal.class);
|
if (invoicedSum == null) {
|
invoicedSum = BigDecimal.ZERO;
|
}
|
invoicedSum = invoicedSum.setScale(2, RoundingMode.HALF_UP);
|
if (invoicedSum.compareTo(BigDecimal.ZERO) <= 0) {
|
return false;
|
}
|
|
BigDecimal paidByProduct = named.queryForObject(
|
"SELECT COALESCE(SUM(receipt_payment_amount), 0) FROM receipt_payment WHERE sales_ledger_product_id = :pid",
|
p, BigDecimal.class);
|
if (paidByProduct == null) {
|
paidByProduct = BigDecimal.ZERO;
|
}
|
paidByProduct = paidByProduct.setScale(2, RoundingMode.HALF_UP);
|
if (paidByProduct.compareTo(invoicedSum.subtract(INVOICE_PAY_TOLERANCE)) >= 0) {
|
return true;
|
}
|
|
Integer unpaidLedgerLineCount = named.queryForObject(
|
"SELECT COUNT(1) FROM ("
|
+ " SELECT 1 FROM invoice_ledger il "
|
+ " INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ " LEFT JOIN receipt_payment rp ON rp.invoice_ledger_id = il.id "
|
+ " WHERE irp.sales_ledger_product_id = :pid "
|
+ " GROUP BY il.id, il.invoice_total "
|
+ " HAVING (IFNULL(il.invoice_total, 0) - IFNULL(SUM(rp.receipt_payment_amount), 0)) > :tol"
|
+ ") t",
|
p.addValue("tol", INVOICE_PAY_TOLERANCE), Integer.class);
|
|
return unpaidLedgerLineCount != null && unpaidLedgerLineCount == 0;
|
}
|
|
@Setter
|
@Getter
|
@SuppressWarnings("unused")
|
public static class ProductPendingRow {
|
private Integer salesLedgerProductId;
|
private Integer salesLedgerId;
|
private Long tenantId;
|
|
}
|
}
|
