package com.ruoyi.sales;
|
|
import com.ruoyi.RuoYiApplication;
|
import org.junit.jupiter.api.Assumptions;
|
import org.junit.jupiter.api.Disabled;
|
import org.junit.jupiter.api.Test;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.boot.test.context.SpringBootTest;
|
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 static org.junit.jupiter.api.Assertions.assertTrue;
|
|
/**
|
* <b>业务口径</b>：历史里 {@code receipt_payment.invoice_ledger_id} 常被存成 <b>销售产品行 id</b>（{@code sales_ledger_product_id}），
|
* 与真实 {@code invoice_ledger.id} 不一致。同步时应以 <b>开票台账</b> 为准：凡是「已经开票」的销售台账产品行，
|
* 就必须有与之对应的回款流水与回款收入，且<strong>一行开票台账 → 一行回款 → 一行收入</strong>。
|
* <p>
|
* <b>推荐：{@link #syncReceiptByInvoicedSalesProducts_hbtmblcSchema()}</b>
|
* <ol>
|
* <li>查出所有在开票台账里出现过的 {@code sales_ledger_product_id}（{@code invoice_ledger} → {@code invoice_registration_product}）。</li>
|
* <li>删除这些<strong>销售产品行</strong>上现有的 {@code receipt_payment}，并删除 {@code business_type=1} 且挂在这些回款上的 {@code account_income}。</li>
|
* <li>按 {@code invoice_ledger} 逐行插入新的 {@code receipt_payment}：{@code sales_ledger_id}、{@code sales_ledger_product_id} 来自登记产品行，
|
* {@code invoice_ledger_id = il.id}（真实台账主键），金额/日期/开票人与开票台账一致。</li>
|
* <li>按新回款主键插入 {@code account_income}（与 {@link com.ruoyi.sales.service.impl.ReceiptPaymentServiceImpl} 一致：{@code business_id=receipt_payment.id}）。</li>
|
* </ol>
|
* 未在开票里出现的销售产品，其回款记录<strong>不删不改</strong>。
|
* <p>
|
* <b>慎用：{@link #fullRebuildReceiptFromInvoiceLedger_hbtmblcSchema()}</b> — 清空<b>全部</b>回款与<b>全部</b>回款类收入后重建，
|
* 仅用于整库以开票为唯一事实源的场景。
|
* <p>
|
* <b>写库开关</b>：{@code -Druoyi.invoiceReceiptRebuild.commit=true} 或 {@code RUOYI_INVOICE_RECEIPT_REBUILD_COMMIT=true}。
|
*/
|
@SpringBootTest(classes = RuoYiApplication.class)
|
class InvoiceLedgerReceiptIncomeRebuildBatchTest {
|
|
private static final Logger log = LoggerFactory.getLogger(InvoiceLedgerReceiptIncomeRebuildBatchTest.class);
|
|
private static final Long TENANT_ID = null;
|
|
private static final String DEFAULT_REGISTRANT = "樊志英";
|
|
private static final String RECEIPT_PAYMENT_TYPE = "0";
|
|
static boolean commitSwitchEnabled() {
|
String p = System.getProperty("ruoyi.invoiceReceiptRebuild.commit");
|
if (p != null && "true".equalsIgnoreCase(p.trim())) {
|
return true;
|
}
|
String e = System.getenv("RUOYI_INVOICE_RECEIPT_REBUILD_COMMIT");
|
return e != null && "true".equalsIgnoreCase(e.trim());
|
}
|
|
static MapSqlParameterSource tenantParams() {
|
return new MapSqlParameterSource("tenantId", TENANT_ID);
|
}
|
|
static String tenantCondIl() {
|
return "(:tenantId IS NULL OR COALESCE(irp.tenant_id, il.tenant_id) = :tenantId)";
|
}
|
|
static String tenantRp(String alias) {
|
return "(:tenantId IS NULL OR " + alias + ".tenant_id = :tenantId)";
|
}
|
|
/**
|
* 出现在开票链路里的销售产品 id 集合（子查询片段，不含外层括号）。
|
*/
|
static String invoicedProductIdSubquery() {
|
return "SELECT DISTINCT irp.sales_ledger_product_id "
|
+ "FROM invoice_ledger il "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "WHERE irp.sales_ledger_product_id IS NOT NULL "
|
+ "AND (" + tenantCondIl() + ")";
|
}
|
|
@Autowired
|
private DataSource dataSource;
|
|
/**
|
* 按「已开票的销售台账产品行」重建回款与收入（推荐）。
|
*/
|
@Test
|
@Transactional
|
void syncReceiptByInvoicedSalesProducts_hbtmblcSchema() {
|
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
|
MapSqlParameterSource p = tenantParams();
|
|
String deleteIncome =
|
"DELETE ai FROM account_income ai "
|
+ "INNER JOIN receipt_payment rp ON ai.business_id = rp.id AND ai.business_type = 1 "
|
+ "WHERE rp.sales_ledger_product_id IN (" + invoicedProductIdSubquery() + ") "
|
+ "AND (" + tenantRp("rp") + ")";
|
int delInc = named.update(deleteIncome, p);
|
log.warn("已删除「已开票产品」关联的回款收入 account_income 行数: {}", delInc);
|
|
String deleteReceipt =
|
"DELETE rp FROM receipt_payment rp "
|
+ "WHERE rp.sales_ledger_product_id IN (" + invoicedProductIdSubquery() + ") "
|
+ "AND (" + tenantRp("rp") + ")";
|
int delRp = named.update(deleteReceipt, p);
|
log.warn("已删除「已开票产品」上的旧回款 receipt_payment 行数: {}", delRp);
|
|
MapSqlParameterSource insRp = tenantParams().addValue("rpType", RECEIPT_PAYMENT_TYPE);
|
String insertReceipt =
|
"INSERT INTO receipt_payment ("
|
+ "sales_ledger_id, sales_ledger_product_id, invoice_ledger_id, "
|
+ "receipt_payment_type, receipt_payment_amount, registrant, receipt_payment_date, "
|
+ "create_time, create_user, update_time, update_user, tenant_id) "
|
+ "SELECT "
|
+ " CAST(irp.sales_ledger_id AS SIGNED), "
|
+ " CAST(irp.sales_ledger_product_id AS SIGNED), "
|
+ " il.id, "
|
+ " :rpType, "
|
+ " il.invoice_total, "
|
+ " NULLIF(TRIM(il.invoice_person), ''), "
|
+ " DATE(il.invoice_date), "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " il.create_user, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " il.update_user, "
|
+ " COALESCE(irp.tenant_id, il.tenant_id) "
|
+ "FROM invoice_ledger il "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "WHERE irp.sales_ledger_product_id IS NOT NULL "
|
+ "AND (" + tenantCondIl() + ")";
|
|
int insRpCnt = named.update(insertReceipt, insRp);
|
log.warn("已按开票台账插入 receipt_payment 行数: {}", insRpCnt);
|
|
MapSqlParameterSource regP = tenantParams().addValue("defReg", DEFAULT_REGISTRANT);
|
named.update(
|
"UPDATE receipt_payment rp SET rp.registrant = :defReg "
|
+ "WHERE (rp.registrant IS NULL OR rp.registrant = '') AND (" + tenantRp("rp") + ")",
|
regP);
|
|
MapSqlParameterSource insAi = tenantParams()
|
.addValue("rpType", RECEIPT_PAYMENT_TYPE)
|
.addValue("defReg", DEFAULT_REGISTRANT);
|
String insertIncome =
|
"INSERT INTO account_income ("
|
+ "income_date, income_type, customer_name, income_money, income_described, income_method, "
|
+ "invoice_number, input_user, input_time, business_id, business_type, tenant_id, "
|
+ "create_time, create_user, update_time, update_user) "
|
+ "SELECT "
|
+ " DATE(il.invoice_date), "
|
+ " '3', "
|
+ " sl.customer_name, "
|
+ " rp.receipt_payment_amount, "
|
+ " '回款收入', "
|
+ " :rpType, "
|
+ " il.invoice_no, "
|
+ " COALESCE(NULLIF(TRIM(rp.registrant), ''), :defReg), "
|
+ " DATE(il.invoice_date), "
|
+ " rp.id, "
|
+ " 1, "
|
+ " rp.tenant_id, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " rp.create_user, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " rp.update_user "
|
+ "FROM receipt_payment rp "
|
+ "INNER JOIN invoice_ledger il ON il.id = rp.invoice_ledger_id "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "INNER JOIN sales_ledger sl ON sl.id = irp.sales_ledger_id "
|
+ "WHERE (" + tenantCondIl() + ") "
|
+ "AND (" + tenantRp("rp") + ") "
|
+ "AND NOT EXISTS ("
|
+ " SELECT 1 FROM account_income x WHERE x.business_id = rp.id AND x.business_type = 1"
|
+ ")";
|
|
int insAiCnt = named.update(insertIncome, insAi);
|
log.warn("已插入回款收入 account_income 行数: {}", insAiCnt);
|
|
assertTrue(insRpCnt >= 0 && insAiCnt >= 0);
|
}
|
|
/**
|
* 清空<b>全部</b>回款与回款类收入后，仅按开票台账重建（会影响未开票产品的回款，慎用）。
|
*/
|
@Test
|
@Transactional
|
@Rollback(false)
|
void fullRebuildReceiptFromInvoiceLedger_hbtmblcSchema() {
|
|
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
|
MapSqlParameterSource insertParams = tenantParams().addValue("rpType", RECEIPT_PAYMENT_TYPE);
|
|
int deletedIncome = named.update("DELETE FROM account_income WHERE business_type = 1", new MapSqlParameterSource());
|
log.warn("fullRebuild: 已删除全部回款类收入行数: {}", deletedIncome);
|
|
int deletedReceipt = named.update("DELETE FROM receipt_payment", new MapSqlParameterSource());
|
log.warn("fullRebuild: 已清空 receipt_payment 行数: {}", deletedReceipt);
|
|
String insertReceipt =
|
"INSERT INTO receipt_payment ("
|
+ "sales_ledger_id, sales_ledger_product_id, invoice_ledger_id, "
|
+ "receipt_payment_type, receipt_payment_amount, registrant, receipt_payment_date, "
|
+ "create_time, create_user, update_time, update_user, tenant_id) "
|
+ "SELECT "
|
+ " CAST(irp.sales_ledger_id AS SIGNED), "
|
+ " CAST(irp.sales_ledger_product_id AS SIGNED), "
|
+ " il.id, "
|
+ " :rpType, "
|
+ " il.invoice_total, "
|
+ " NULLIF(TRIM(il.invoice_person), ''), "
|
+ " DATE(il.invoice_date), "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " il.create_user, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " il.update_user, "
|
+ " COALESCE(irp.tenant_id, il.tenant_id) "
|
+ "FROM invoice_ledger il "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "WHERE " + tenantCondIl();
|
|
int insertedReceipt = named.update(insertReceipt, insertParams);
|
log.warn("fullRebuild: 已插入 receipt_payment 行数: {}", insertedReceipt);
|
|
named.update(
|
"UPDATE receipt_payment SET registrant = :defReg WHERE registrant IS NULL OR registrant = ''",
|
new MapSqlParameterSource("defReg", DEFAULT_REGISTRANT));
|
|
insertParams.addValue("defReg", DEFAULT_REGISTRANT);
|
String insertIncome =
|
"INSERT INTO account_income ("
|
+ "income_date, income_type, customer_name, income_money, income_described, income_method, "
|
+ "invoice_number, input_user, input_time, business_id, business_type, tenant_id, "
|
+ "create_time, create_user, update_time, update_user) "
|
+ "SELECT "
|
+ " DATE(il.invoice_date), "
|
+ " '3', "
|
+ " sl.customer_name, "
|
+ " rp.receipt_payment_amount, "
|
+ " '回款收入', "
|
+ " :rpType, "
|
+ " il.invoice_no, "
|
+ " COALESCE(NULLIF(TRIM(rp.registrant), ''), :defReg), "
|
+ " DATE(il.invoice_date), "
|
+ " rp.id, "
|
+ " 1, "
|
+ " rp.tenant_id, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " rp.create_user, "
|
+ " CAST(DATE(il.invoice_date) AS DATETIME), "
|
+ " rp.update_user "
|
+ "FROM receipt_payment rp "
|
+ "INNER JOIN invoice_ledger il ON il.id = rp.invoice_ledger_id "
|
+ "INNER JOIN invoice_registration_product irp ON irp.id = il.invoice_registration_product_id "
|
+ "INNER JOIN sales_ledger sl ON sl.id = irp.sales_ledger_id "
|
+ "WHERE " + tenantCondIl();
|
|
int insertedIncome = named.update(insertIncome, insertParams);
|
log.warn("fullRebuild: 已插入 account_income 行数: {}", insertedIncome);
|
|
assertTrue(insertedReceipt >= 0 && insertedIncome >= 0);
|
}
|
}
|
