From 88c94d51fb9f82e856245908696ea4a970541a33 Mon Sep 17 00:00:00 2001
From: shinnosuke <tantao@1hoper.com>
Date: 星期五, 28 八月 2026 16:55:59 +0800
Subject: [PATCH] 又又更新
---
talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantSettlementService.java | 12
talent-card-server/src/main/resources/db/migration/V124__backfill_merchant_operator_store_access.sql | 15 +
talent-card-admin/src/views/SpouseEmploymentDetailView.vue | 2
talent-card-server/src/main/java/com/talentcard/server/business/admin/AdminBusinessService.java | 2
talent-card-admin/src/views/ParkingPlateDetailView.vue | 2
talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java | 255 ----------------
talent-card-server/src/main/java/com/talentcard/server/business/payment/huifu/HuifuPaymentProperties.java | 2
talent-card-admin/src/views/DashboardView.vue | 7
talent-card-server/src/main/java/com/talentcard/server/business/portal/PortalService.java | 4
talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java | 146 ---------
talent-card-server/src/main/java/com/talentcard/server/mapper/GovernmentSubsidySql.java | 71 ++++
talent-card-server/src/main/java/com/talentcard/server/service/AdminWorkbenchStatisticsService.java | 2
talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java | 340 ++++++++++++++++++++++
13 files changed, 456 insertions(+), 404 deletions(-)
diff --git a/talent-card-admin/src/views/DashboardView.vue b/talent-card-admin/src/views/DashboardView.vue
index 7c20baa..2fa08e2 100644
--- a/talent-card-admin/src/views/DashboardView.vue
+++ b/talent-card-admin/src/views/DashboardView.vue
@@ -296,10 +296,9 @@
.subsidy-compare { display: flex; align-items: center; gap: 4px; margin-top: 8px; color: #8d98a7; font-size: 12px; }
.subsidy-compare span { color: inherit; }
.subsidy-compare b { font-size: 15px; line-height: 12px; font-weight: 700; }
-.subsidy-compare.trend-up { color: #18a957; }
-.subsidy-compare.trend-down { color: #f04444; }
-.subsidy-compare.trend-flat { color: #8d98a7; }
-.subsidy-compare span { color: #ff3b30; }
+.subsidy-compare.trend-up span, .subsidy-compare.trend-up b { color: #f04444; }
+.subsidy-compare.trend-down span, .subsidy-compare.trend-down b { color: #18a957; }
+.subsidy-compare.trend-flat span, .subsidy-compare.trend-flat b { color: #8d98a7; }
.summary-chart { position: absolute; right: 14px; bottom: 14px; width: 104px; height: 50px; pointer-events: none; }
.spark-line { display: block; width: 100%; height: 100%; overflow: visible; }
.spark-area { fill: rgb(22 119 255 / 9%); stroke: none; }
diff --git a/talent-card-admin/src/views/ParkingPlateDetailView.vue b/talent-card-admin/src/views/ParkingPlateDetailView.vue
index 4c4baab..e805c1c 100644
--- a/talent-card-admin/src/views/ParkingPlateDetailView.vue
+++ b/talent-card-admin/src/views/ParkingPlateDetailView.vue
@@ -129,7 +129,7 @@
</div>
</section>
- <el-button v-if="canProcess" type="primary" class="reservation-detail-action" :loading="processing" @click="markHandled">已处理</el-button>
+ <el-button v-if="canProcess" type="primary" class="reservation-detail-action" :loading="processing" @click="markHandled">处理</el-button>
</div>
</template>
diff --git a/talent-card-admin/src/views/SpouseEmploymentDetailView.vue b/talent-card-admin/src/views/SpouseEmploymentDetailView.vue
index 765e57f..75000e9 100644
--- a/talent-card-admin/src/views/SpouseEmploymentDetailView.vue
+++ b/talent-card-admin/src/views/SpouseEmploymentDetailView.vue
@@ -103,6 +103,6 @@
</div>
</section>
- <el-button v-if="!handled" type="primary" class="reservation-detail-action" @click="markHandled">已处理</el-button>
+ <el-button v-if="!handled" type="primary" class="reservation-detail-action" @click="markHandled">处理</el-button>
</div>
</template>
diff --git a/talent-card-server/src/main/java/com/talentcard/server/business/admin/AdminBusinessService.java b/talent-card-server/src/main/java/com/talentcard/server/business/admin/AdminBusinessService.java
index 067351d..c635e18 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/business/admin/AdminBusinessService.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/business/admin/AdminBusinessService.java
@@ -94,7 +94,7 @@
countSql.append(" and date(publish_time) <= ?");
args.add(publishEnd);
}
- sql.append(" order by is_top desc, case when is_top = 1 then top_time end desc, publish_time desc, id desc");
+ sql.append(" order by is_top desc, case when is_top = 1 then updated_at end desc, publish_time desc, id desc");
return page(sql.toString(), countSql.toString(), page, size, args.toArray());
}
diff --git a/talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantSettlementService.java b/talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantSettlementService.java
index 49e3146..2e37e77 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantSettlementService.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantSettlementService.java
@@ -17,6 +17,7 @@
import com.talentcard.server.business.payment.huifu.HuifuEnterpriseOnboardingCommand;
import com.talentcard.server.business.payment.huifu.HuifuEnterpriseOnboardingGateway;
import com.talentcard.server.business.payment.huifu.HuifuPaymentGatewayException;
+import com.talentcard.server.business.payment.huifu.HuifuPaymentProperties;
import com.talentcard.server.business.payment.huifu.HuifuUserBusinessOpenCommand;
import com.talentcard.server.business.payment.huifu.HuifuUserBusinessOpenGateway;
import com.talentcard.server.common.SensitiveDataCrypto;
@@ -34,14 +35,16 @@
private final HuifuEnterpriseOnboardingGateway enterpriseOnboardingGateway;
private final HuifuMerchantWithdrawalService withdrawalService;
private final HuifuUserBusinessOpenGateway userBusinessOpenGateway;
+ private final HuifuPaymentProperties huifuPaymentProperties;
- public MerchantSettlementService(JdbcTemplate jdbc, SensitiveDataCrypto sensitiveDataCrypto, HuifuIndividualOnboardingGateway individualOnboardingGateway, HuifuEnterpriseOnboardingGateway enterpriseOnboardingGateway, HuifuMerchantWithdrawalService withdrawalService, HuifuUserBusinessOpenGateway userBusinessOpenGateway) {
+ public MerchantSettlementService(JdbcTemplate jdbc, SensitiveDataCrypto sensitiveDataCrypto, HuifuIndividualOnboardingGateway individualOnboardingGateway, HuifuEnterpriseOnboardingGateway enterpriseOnboardingGateway, HuifuMerchantWithdrawalService withdrawalService, HuifuUserBusinessOpenGateway userBusinessOpenGateway, HuifuPaymentProperties huifuPaymentProperties) {
this.jdbc = jdbc;
this.sensitiveDataCrypto = sensitiveDataCrypto;
this.individualOnboardingGateway = individualOnboardingGateway;
this.enterpriseOnboardingGateway = enterpriseOnboardingGateway;
this.withdrawalService = withdrawalService;
this.userBusinessOpenGateway = userBusinessOpenGateway;
+ this.huifuPaymentProperties = huifuPaymentProperties;
}
@Transactional(readOnly = true)
@@ -207,10 +210,13 @@
Map<String, Object> subject = onboarding.get(0);
String bankCode = clean(branch.get("headBankCode"));
if (bankCode.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "The selected bank branch has no Huifu bank code");
+ boolean enterprise = "enterprise".equalsIgnoreCase(clean(subject.get("subjectType")));
+ String cardName = enterprise ? clean(branch.get("bankName")) : clean(subject.get("subjectName"));
+ if (!enterprise && cardName.isBlank()) throw new BusinessException(ErrorCode.CONFLICT, "进件姓名缺失，无法绑定个人银行卡");
String requestNo = BusinessIdGenerator.next("BC");
try {
HuifuUserBusinessOpenGateway.Result result = userBusinessOpenGateway.create(new HuifuUserBusinessOpenCommand(
- requestNo, clean(subject.get("providerHuifuId")), clean(subject.get("providerHuifuId")), "enterprise".equalsIgnoreCase(clean(subject.get("subjectType"))) ? "0" : "1", clean(branch.get("bankName")), cardNumber,
+ requestNo, clean(subject.get("providerHuifuId")), huifuPaymentProperties.getMerchantId().trim(), enterprise ? "0" : "1", cardName, cardNumber,
provinceCode(clean(card.get("cityCode"))), clean(card.get("cityCode")), bankCode, clean(branch.get("clearingCode")), clean(branch.get("branchName")),
sensitiveDataCrypto.decrypt(clean(subject.get("subjectIdNoEncrypted"))), certificateValidityType(clean(subject.get("subjectCertValidityType"))), clean(subject.get("subjectCertBeginDate")), clean(subject.get("subjectCertEndDate")), clean(subject.get("contactMobile"))));
if (result.accepted() && !result.tokenNo().isBlank()) jdbc.update("update merchant_bank_card set status='approved', provider_token_no=?, provider_apply_no=?, provider_request_no=?, provider_response_code=?, provider_response_message=?, reviewed_at=now(), reject_reason=null where id=?", result.tokenNo(), result.applyNo(), requestNo, result.code(), result.message(), card.get("id"));
@@ -245,7 +251,7 @@
}
private void requireKey(String key) { if (key == null || key.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "缺少幂等键"); }
private void store(MerchantPrincipal principal) { if (rows("select id from merchant_store where id = ? and merchant_id = ? and status = 1 and deleted = 0", principal.storeId(), principal.merchantId()).isEmpty()) throw new BusinessException(ErrorCode.FORBIDDEN, "当前门店不可操作"); }
- private Map<String, Object> onboarding(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "applicationNo", row.get("applicationNo"), "status", row.get("status"), "reason", clean(row.get("rejectReason")), "submittedAt", String.valueOf(row.get("createdAt"))); }
+ private Map<String, Object> onboarding(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "applicationNo", row.get("applicationNo"), "status", row.get("status"), "subjectType", clean(row.get("subjectType")), "reason", clean(row.get("rejectReason")), "submittedAt", String.valueOf(row.get("createdAt"))); }
private Map<String, Object> bankCard(Map<String, Object> row) { return Map.of("id", String.valueOf(row.get("id")), "bankName", row.get("bankName"), "branchName", row.get("branchName"), "cardMasked", row.get("cardMasked"), "status", row.get("status"), "reason", clean(row.get("rejectReason")), "createdAt", String.valueOf(row.get("createdAt"))); }
private Map<String, Object> withdrawal(Map<String, Object> row) { Map<String, Object> value = new LinkedHashMap<>(); value.put("id", String.valueOf(row.get("id"))); value.put("withdrawalNo", row.get("withdrawalNo")); value.put("amountCents", row.get("amountCents")); value.put("status", row.get("status")); value.put("reason", clean(row.get("failureReason"))); value.put("bankName", clean(row.get("bankName"))); value.put("cardMasked", clean(row.get("cardMasked"))); value.put("createdAt", String.valueOf(row.get("createdAt"))); value.put("reviewedAt", clean(row.get("reviewedAt"))); value.put("paidAt", clean(row.get("paidAt"))); return value; }
private Map<String, Object> branch(String id, String branchName, String bankName) { return Map.of("id", id, "branchName", branchName, "bankName", bankName); }
diff --git a/talent-card-server/src/main/java/com/talentcard/server/business/payment/huifu/HuifuPaymentProperties.java b/talent-card-server/src/main/java/com/talentcard/server/business/payment/huifu/HuifuPaymentProperties.java
index 1785236..cd0db5e 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/business/payment/huifu/HuifuPaymentProperties.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/business/payment/huifu/HuifuPaymentProperties.java
@@ -17,7 +17,7 @@
private String withdrawalProductId = "";
private String withdrawalNotifyUrl = "";
private String merchantBusinessNotifyUrl = "";
- private String withdrawalIntoAccountDateType = "D0";
+ private String withdrawalIntoAccountDateType = "DM";
private boolean delayAccount;
public boolean isEnabled() { return enabled; }
diff --git a/talent-card-server/src/main/java/com/talentcard/server/business/portal/PortalService.java b/talent-card-server/src/main/java/com/talentcard/server/business/portal/PortalService.java
index 54924d1..f339b61 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/business/portal/PortalService.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/business/portal/PortalService.java
@@ -49,7 +49,7 @@
select id, title, summary, cover_url, source, view_count, is_top, publish_time
from news_article
where deleted = 0 and status = 1
- order by is_top desc, case when is_top = 1 then top_time end desc, publish_time desc, id desc
+ order by is_top desc, case when is_top = 1 then updated_at end desc, publish_time desc, id desc
limit 4
"""));
result.put("policies", query("""
@@ -99,7 +99,7 @@
select id, title, summary, cover_url, source, content, view_count, is_top, publish_time
from news_article
where deleted = 0 and status = 1
- order by is_top desc, case when is_top = 1 then top_time end desc, publish_time desc, id desc
+ order by is_top desc, case when is_top = 1 then updated_at end desc, publish_time desc, id desc
""", "select count(*) from news_article where deleted = 0 and status = 1", page, size);
List<Map<String, Object>> records = result.records().stream().map(row -> {
Map<String, Object> item = new LinkedHashMap<>(row);
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
index fcf7fe8..2c86409 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
@@ -4,6 +4,7 @@
import com.talentcard.server.entity.BenefitOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
+import org.apache.ibatis.annotations.SelectProvider;
import java.util.List;
import java.util.Map;
@@ -12,152 +13,13 @@
@Mapper
public interface AdminWorkbenchStatisticsMapper extends BaseMapper<BenefitOrder> {
- @Select("""
- select
- coalesce(sum(case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then o.subsidy_amount else 0 end), 0) as subsidyAmountCents,
- (select count(*) from benefit_order verified_order
- where verified_order.deleted = 0
- and verified_order.order_status = 1
- and verified_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) >= date_format(current_date, '%Y-%m-01')
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
- (select count(*) from talent_user u where u.deleted = 0) as talentCount,
- (select count(*) from benefit_order po where po.deleted = 0 and po.order_status = 0) as pendingOrderCount,
- (select count(*) from merchant_product pp where pp.deleted = 0 and pp.audit_status = 1) as pendingProductCount,
- (select count(*) from service_application sa where sa.deleted = 0 and sa.status in ('待处理', '待审核')) as pendingApplicationCount
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummary")
Map<String, Object> selectSummary();
- @Select("""
- select
- coalesce((select count(*) from talent_user u
- where u.deleted = 0
- and date(u.created_at) = date_sub(current_date, interval 1 day)), 0) as talentCompare,
- (coalesce((select count(*) from benefit_order current_month_order
- where current_month_order.deleted = 0
- and current_month_order.order_status = 1
- and current_month_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = current_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- current_month_order.updated_at
- ) >= date_format(current_date, '%Y-%m-01')
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = current_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- current_month_order.updated_at
- ) < date_add(last_day(current_date), interval 1 day)), 0)
- - coalesce((select count(*) from benefit_order previous_month_order
- where previous_month_order.deleted = 0
- and previous_month_order.order_status = 1
- and previous_month_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = previous_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- previous_month_order.updated_at
- ) >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = previous_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- previous_month_order.updated_at
- ) < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
- coalesce(sum(case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then o.subsidy_amount else 0 end), 0) as subsidyCompareCents
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- and exists (
- select 1 from verify_record yesterday_verify
- where yesterday_verify.order_id = o.id
- and yesterday_verify.status = 1
- and yesterday_verify.verify_time >= date_sub(current_date, interval 1 day)
- and yesterday_verify.verify_time < current_date
- )
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectCompareSummary")
Map<String, Object> selectCompareSummary();
- @Select("""
- select date_format(days.bucket_day, '%c/%e') as time,
- (select count(*)
- from talent_user u
- where u.deleted = 0
- and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
- (select count(*)
- from benefit_order verified_order
- where verified_order.deleted = 0
- and verified_order.order_status = 1
- and verified_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) >= days.bucket_day
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) < date_add(days.bucket_day, interval 1 day)) as verification,
- round(coalesce((select sum(case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then o.subsidy_amount else 0 end)
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- and exists (
- select 1
- from verify_record subsidy_verify
- where subsidy_verify.order_id = o.id
- and subsidy_verify.status = 1
- and subsidy_verify.verify_time < date_add(days.bucket_day, interval 1 day)
- )), 0) / 100, 2) as subsidy
- from (
- select date_sub(current_date, interval 6 day) as bucket_day union all
- select date_sub(current_date, interval 5 day) union all
- select date_sub(current_date, interval 4 day) union all
- select date_sub(current_date, interval 3 day) union all
- select date_sub(current_date, interval 2 day) union all
- select date_sub(current_date, interval 1 day) union all
- select current_date
- ) days
- order by days.bucket_day
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummaryTrend")
List<Map<String, Object>> selectSummaryTrend();
@Select("""
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/GovernmentSubsidySql.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/GovernmentSubsidySql.java
new file mode 100644
index 0000000..1a2c1f6
--- /dev/null
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/GovernmentSubsidySql.java
@@ -0,0 +1,71 @@
+package com.talentcard.server.mapper;
+
+/**
+ * 政府补贴金额（分）：人才折扣仅在对应「是否政府补贴」开关打开时计入，
+ * 另加政府补贴券面额；商家自行让利不计入。
+ * 工作台按已支付订单计入，不要求核销。
+ * <p>
+ * 兼容 MySQL 5.7：不用 JSON_TABLE，也不用会引用外层列的派生表 UNION。
+ * 依赖当前语句中的表别名：{@code o} benefit_order、{@code p} merchant_product、
+ * {@code m} merchant、{@code c} merchant_coupon。
+ */
+final class GovernmentSubsidySql {
+ private static final String CFG = "if(o.product_id is not null, p.rights_config, m.merchant_rights_config)";
+ private static final String OL = "coalesce(nullif(o.card_level_snapshot, ''), (select tc.card_level from talent_card tc where tc.id = o.card_id and tc.deleted = 0 limit 1), (select talent.talent_level from talent_user talent where talent.id = o.talent_id and talent.deleted = 0 limit 1), '')";
+ private static final String AREA = "upper(coalesce((select talent.source_area_type from talent_user talent where talent.id = o.talent_id and talent.deleted = 0 limit 1), ''))";
+ private static final String LN_OL = lv(OL);
+
+ /** 有效下单时间：已支付取支付成功时间，零元单取创建时间。 */
+ static final String ORDER_TIME = "coalesce("
+ + "(select max(pt.paid_at) from payment_transaction pt"
+ + " where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),"
+ + " o.created_at)";
+
+ static final String AMOUNT_CENTS = "("
+ + "case " + whenThen(0) + whenThen(1) + whenThen(2) + whenThen(3)
+ + whenThen(4) + whenThen(5) + whenThen(6) + whenThen(7)
+ + whenThen(8) + whenThen(9) + whenThen(10) + whenThen(11)
+ + " else 0 end"
+ + " + case when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1 then coalesce(o.coupon_amount, 0) else 0 end)";
+
+ private static String whenThen(int i) {
+ return " when " + matches(i) + " then if(" + governmentFlag(i)
+ + ", greatest(coalesce(o.subsidy_amount, 0) - coalesce(o.coupon_amount, 0), 0), 0)";
+ }
+
+ private static String matches(int i) {
+ String p = "$[" + i + "]";
+ String level = "json_unquote(json_extract(" + CFG + ", '" + p + ".level'))";
+ return "json_extract(" + CFG + ", '" + p + ".level') is not null"
+ + " and " + fl(p + ".enabled")
+ + " and " + lv(level) + " = " + LN_OL
+ + " and (" + LN_OL + " <> 'EXCELLENT'"
+ + " or (" + AREA + " = 'LOCAL' and " + level + " not like '%外地%')"
+ + " or (" + AREA + " in ('OUTSIDE', '外地') and " + level + " not like '%本地%')"
+ + " or (" + AREA + " not in ('LOCAL', 'OUTSIDE', '外地') and " + level + " not like '%本地%' and " + level + " not like '%外地%'))";
+ }
+
+ private static String governmentFlag(int i) {
+ String p = "$[" + i + "]";
+ return "(case when o.product_id is not null then"
+ + " case when coalesce(o.base_pay_amount, coalesce(o.pay_amount, 0) + coalesce(o.coupon_amount, 0)) = 0 then "
+ + fl(p + ".subsidy")
+ + " when " + fl(p + ".free") + " then " + fl(p + ".exceedSubsidy")
+ + " else " + fl(p + ".subsidy") + " end"
+ + " else " + fl(p + ".governmentSubsidy") + " end)";
+ }
+
+ private static String fl(String path) {
+ return "lower(replace(cast(json_extract(" + CFG + ", '" + path + "') as char), '\"', '')) in ('true', '1', 'yes')";
+ }
+
+ private static String lv(String expr) {
+ return "(case when coalesce(" + expr + ", '') like '%卓越%' or upper(coalesce(" + expr + ", '')) like '%EXCELLENT%' then 'EXCELLENT'"
+ + " when coalesce(" + expr + ", '') like '%杰出%' or upper(coalesce(" + expr + ", '')) like '%OUTSTANDING%' then 'OUTSTANDING'"
+ + " when coalesce(" + expr + ", '') like '%菁英%' or coalesce(" + expr + ", '') like '%精英%' or upper(coalesce(" + expr + ", '')) like '%ELITE%' then 'ELITE'"
+ + " else upper(trim(coalesce(" + expr + ", ''))) end)";
+ }
+
+ private GovernmentSubsidySql() {
+ }
+}
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
index 06b430e..3fb5366 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
@@ -9,74 +9,12 @@
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
+import org.apache.ibatis.annotations.SelectProvider;
@Mapper
public interface MerchantSettlementStatisticsMapper extends BaseMapper<BenefitOrder> {
- @Select("""
- <script>
- select base.period_key as periodKey,
- base.merchant_id as merchantId,
- base.merchant_name as merchantName,
- base.merchant_type as merchantType,
- count(*) as orderCount,
- coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
- coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
- coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
- coalesce(sum(base.order_amount), 0) as originalAmountCents,
- coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
- coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
- min(base.business_time) as periodStartTime,
- max(base.business_time) as periodEndTime
- from (
- select case
- when #{periodType} = 'MONTH' then date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m')
- else date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m-%d')
- end as period_key,
- coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
- o.merchant_id,
- m.merchant_name,
- m.merchant_type,
- coalesce(o.order_amount, 0) as order_amount,
- coalesce(o.pay_amount, 0) as pay_amount,
- coalesce(o.coupon_amount, 0) as coupon_amount,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as government_subsidy_amount
- from benefit_order o
- join merchant m on m.id = o.merchant_id and m.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
- <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
- <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
- <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
- ) base
- group by base.period_key, base.merchant_id, base.merchant_name, base.merchant_type
- order by base.period_key desc, base.merchant_id desc
- limit #{size} offset #{offset}
- </script>
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectPeriodSummaries")
List<Map<String, Object>> selectPeriodSummaries(@Param("merchantId") Long merchantId,
@Param("merchantType") String merchantType,
@Param("periodType") String periodType,
@@ -119,200 +57,21 @@
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime);
- @Select("""
- <script>
- select base.merchant_id as merchantId,
- max(base.merchant_name) as merchantName,
- max(base.merchant_type) as merchantType,
- count(*) as orderCount,
- coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
- coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
- coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
- coalesce(sum(base.order_amount), 0) as originalAmountCents,
- coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
- coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
- min(base.business_time) as startTime,
- max(base.business_time) as endTime
- from (
- select coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
- o.merchant_id,
- m.merchant_name,
- m.merchant_type,
- coalesce(o.order_amount, 0) as order_amount,
- coalesce(o.pay_amount, 0) as pay_amount,
- coalesce(o.coupon_amount, 0) as coupon_amount,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as government_subsidy_amount
- from benefit_order o
- join merchant m on m.id = o.merchant_id and m.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
- <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
- <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
- <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
- ) base
- group by base.merchant_id
- order by settlementAmountCents desc, base.merchant_id desc
- </script>
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectMerchantOverviews")
List<Map<String, Object>> selectMerchantOverviews(@Param("merchantId") Long merchantId,
@Param("merchantType") String merchantType,
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime);
- @Select("""
- <script>
- select o.id as orderId,
- o.order_no as orderNo,
- o.order_type as orderType,
- o.merchant_id as merchantId,
- m.merchant_name as merchantName,
- m.merchant_type as merchantType,
- o.store_id as storeId,
- s.store_name as storeName,
- o.talent_id as talentId,
- t.name as talentName,
- t.mobile as talentMobile,
- tc.card_no as cardNo,
- tc.card_level as cardLevel,
- coalesce(o.product_name_snapshot, p.product_name, '') as productName,
- coalesce(o.order_amount, 0) as originalAmountCents,
- coalesce(o.pay_amount, 0) as transactionAmountCents,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as subsidyAmountCents,
- coalesce(o.pay_amount, 0)
- +
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as settlementAmountCents,
- coalesce(o.coupon_amount, 0) as couponAmountCents,
- o.order_status as orderStatus,
- o.verify_status as verifyStatus,
- o.created_at as createdAt,
- o.updated_at as paidAt,
- v.verify_time as verifyTime,
- v.verify_operator as verifyOperator
- from benefit_order o
- join merchant m on m.id = o.merchant_id and m.deleted = 0
- left join merchant_store s on s.id = o.store_id and s.deleted = 0
- left join talent_user t on t.id = o.talent_id and t.deleted = 0
- left join talent_card tc on tc.id = o.card_id and tc.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time, max(staff_id) as verify_operator
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.merchant_id = #{merchantId}
- <if test="orderStatus != null">and o.order_status = #{orderStatus}</if>
- <if test="verifyStatus != null">and o.verify_status = #{verifyStatus}</if>
- <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
- <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
- order by coalesce(v.verify_time, o.updated_at, o.created_at) desc, o.id desc
- limit #{limit}
- </script>
- """)
+
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSettlementExportDetails")
List<Map<String, Object>> selectSettlementExportDetails(@Param("merchantId") Long merchantId,
@Param("orderStatus") Integer orderStatus,
@Param("verifyStatus") Integer verifyStatus,
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime,
@Param("limit") int limit);
- @Select("""
- select
- coalesce((
- select sum(o.pay_amount)
- from benefit_order o
- where o.deleted = 0
- and o.order_status = 1
- and o.merchant_id = #{merchantId}
- ), 0) as transactionAmountCents,
- coalesce((
- select count(*)
- from verify_record r
- where r.status = 1
- and r.merchant_id = #{merchantId}
- and r.verify_time >= #{todayStart}
- and r.verify_time < #{todayEnd}
- ), 0) as todayVerificationCount,
- coalesce((
- select count(*)
- from verify_record r
- where r.status = 1
- and r.merchant_id = #{merchantId}
- and r.verify_time >= #{weekStart}
- and r.verify_time < #{weekEnd}
- ), 0) as weekVerificationCount,
- coalesce((
- select sum(
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- )
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.merchant_id = #{merchantId}
- ), 0) as subsidyAmountCents
- """)
+
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectDashboardCards")
Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
@Param("todayStart") LocalDateTime todayStart,
@Param("todayEnd") LocalDateTime todayEnd,
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java
new file mode 100644
index 0000000..ef387db
--- /dev/null
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java
@@ -0,0 +1,340 @@
+package com.talentcard.server.mapper;
+
+/** MyBatis SQL provider：政府补贴表达式在运行期拼接，兼容 MySQL 5.7。 */
+public class StatisticsQuerySql {
+ public String selectSummary() {
+ return sql("""
+ select
+ coalesce(sum(/*AMOUNT*/), 0) as subsidyAmountCents,
+ (select count(*) from benefit_order verified_order
+ where verified_order.deleted = 0
+ and verified_order.order_status = 1
+ and verified_order.verify_status = 1
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = verified_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ verified_order.updated_at
+ ) >= date_format(current_date, '%Y-%m-01')
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = verified_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ verified_order.updated_at
+ ) < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
+ (select count(*) from talent_user u where u.deleted = 0) as talentCount,
+ (select count(*) from benefit_order po where po.deleted = 0 and po.order_status = 0) as pendingOrderCount,
+ (select count(*) from merchant_product pp where pp.deleted = 0 and pp.audit_status = 1) as pendingProductCount,
+ (select count(*) from service_application sa where sa.deleted = 0 and sa.status in ('待处理', '待审核')) as pendingApplicationCount
+ from benefit_order o
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ where o.deleted = 0 and o.order_status = 1
+ """);
+ }
+
+ public String selectCompareSummary() {
+ return sql("""
+ select
+ coalesce((select count(*) from talent_user u
+ where u.deleted = 0
+ and u.created_at >= current_date
+ and u.created_at < date_add(current_date, interval 1 day)), 0) as talentCompare,
+ (coalesce((select count(*) from benefit_order current_month_order
+ where current_month_order.deleted = 0
+ and current_month_order.order_status = 1
+ and current_month_order.verify_status = 1
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = current_month_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ current_month_order.updated_at
+ ) >= date_format(current_date, '%Y-%m-01')
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = current_month_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ current_month_order.updated_at
+ ) < date_add(last_day(current_date), interval 1 day)), 0)
+ - coalesce((select count(*) from benefit_order previous_month_order
+ where previous_month_order.deleted = 0
+ and previous_month_order.order_status = 1
+ and previous_month_order.verify_status = 1
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = previous_month_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ previous_month_order.updated_at
+ ) >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = previous_month_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ previous_month_order.updated_at
+ ) < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
+ coalesce(sum(/*AMOUNT*/), 0) as subsidyCompareCents
+ from benefit_order o
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ where o.deleted = 0
+ and o.order_status = 1
+ and /*ORDER_TIME*/ >= current_date
+ and /*ORDER_TIME*/ < date_add(current_date, interval 1 day)
+ """);
+ }
+
+ public String selectSummaryTrend() {
+ return sql("""
+ select date_format(days.bucket_day, '%c/%e') as time,
+ (select count(*)
+ from talent_user u
+ where u.deleted = 0
+ and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
+ (select count(*)
+ from benefit_order verified_order
+ where verified_order.deleted = 0
+ and verified_order.order_status = 1
+ and verified_order.verify_status = 1
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = verified_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ verified_order.updated_at
+ ) >= days.bucket_day
+ and coalesce(
+ (select max(v.verify_time) from verify_record v
+ where v.order_id = verified_order.id and v.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ verified_order.updated_at
+ ) < date_add(days.bucket_day, interval 1 day)) as verification,
+ round(coalesce((select sum(/*AMOUNT*/)
+ from benefit_order o
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ where o.deleted = 0
+ and o.order_status = 1
+ and /*ORDER_TIME*/ < date_add(days.bucket_day, interval 1 day)
+ ), 0) / 100, 2) as subsidy
+ from (
+ select date_sub(current_date, interval 6 day) as bucket_day union all
+ select date_sub(current_date, interval 5 day) union all
+ select date_sub(current_date, interval 4 day) union all
+ select date_sub(current_date, interval 3 day) union all
+ select date_sub(current_date, interval 2 day) union all
+ select date_sub(current_date, interval 1 day) union all
+ select current_date
+ ) days
+ order by days.bucket_day
+ """);
+ }
+
+ public String selectPeriodSummaries() {
+ return sql("""
+ <script>
+ select base.period_key as periodKey,
+ base.merchant_id as merchantId,
+ base.merchant_name as merchantName,
+ base.merchant_type as merchantType,
+ count(*) as orderCount,
+ coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
+ coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
+ coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
+ coalesce(sum(base.order_amount), 0) as originalAmountCents,
+ coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
+ coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
+ min(base.business_time) as periodStartTime,
+ max(base.business_time) as periodEndTime
+ from (
+ select case
+ when #{periodType} = 'MONTH' then date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m')
+ else date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m-%d')
+ end as period_key,
+ coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
+ o.merchant_id,
+ m.merchant_name,
+ m.merchant_type,
+ coalesce(o.order_amount, 0) as order_amount,
+ coalesce(o.pay_amount, 0) as pay_amount,
+ coalesce(o.coupon_amount, 0) as coupon_amount,
+ /*AMOUNT*/ as government_subsidy_amount
+ from benefit_order o
+ join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ left join (
+ select order_id, max(verify_time) as verify_time
+ from verify_record
+ where status = 1
+ group by order_id
+ ) v on v.order_id = o.id
+ where o.deleted = 0
+ and o.order_status = 1
+ and o.verify_status = 1
+ <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
+ <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
+ <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
+ <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
+ ) base
+ group by base.period_key, base.merchant_id, base.merchant_name, base.merchant_type
+ order by base.period_key desc, base.merchant_id desc
+ limit #{size} offset #{offset}
+ </script>
+ """);
+ }
+
+ public String selectMerchantOverviews() {
+ return sql("""
+ <script>
+ select base.merchant_id as merchantId,
+ max(base.merchant_name) as merchantName,
+ max(base.merchant_type) as merchantType,
+ count(*) as orderCount,
+ coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
+ coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
+ coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
+ coalesce(sum(base.order_amount), 0) as originalAmountCents,
+ coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
+ coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
+ min(base.business_time) as startTime,
+ max(base.business_time) as endTime
+ from (
+ select coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
+ o.merchant_id,
+ m.merchant_name,
+ m.merchant_type,
+ coalesce(o.order_amount, 0) as order_amount,
+ coalesce(o.pay_amount, 0) as pay_amount,
+ coalesce(o.coupon_amount, 0) as coupon_amount,
+ /*AMOUNT*/ as government_subsidy_amount
+ from benefit_order o
+ join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ left join (
+ select order_id, max(verify_time) as verify_time
+ from verify_record
+ where status = 1
+ group by order_id
+ ) v on v.order_id = o.id
+ where o.deleted = 0
+ and o.order_status = 1
+ and o.verify_status = 1
+ <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
+ <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
+ <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
+ <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
+ ) base
+ group by base.merchant_id
+ order by settlementAmountCents desc, base.merchant_id desc
+ </script>
+ """);
+ }
+
+ public String selectSettlementExportDetails() {
+ return sql("""
+ <script>
+ select o.id as orderId,
+ o.order_no as orderNo,
+ o.order_type as orderType,
+ o.merchant_id as merchantId,
+ m.merchant_name as merchantName,
+ m.merchant_type as merchantType,
+ o.store_id as storeId,
+ s.store_name as storeName,
+ o.talent_id as talentId,
+ t.name as talentName,
+ t.mobile as talentMobile,
+ tc.card_no as cardNo,
+ tc.card_level as cardLevel,
+ coalesce(o.product_name_snapshot, p.product_name, '') as productName,
+ coalesce(o.order_amount, 0) as originalAmountCents,
+ coalesce(o.pay_amount, 0) as transactionAmountCents,
+ /*AMOUNT*/ as subsidyAmountCents,
+ coalesce(o.pay_amount, 0) + /*AMOUNT*/ as settlementAmountCents,
+ coalesce(o.coupon_amount, 0) as couponAmountCents,
+ o.order_status as orderStatus,
+ o.verify_status as verifyStatus,
+ o.created_at as createdAt,
+ o.updated_at as paidAt,
+ v.verify_time as verifyTime,
+ v.verify_operator as verifyOperator
+ from benefit_order o
+ join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_store s on s.id = o.store_id and s.deleted = 0
+ left join talent_user t on t.id = o.talent_id and t.deleted = 0
+ left join talent_card tc on tc.id = o.card_id and tc.deleted = 0
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ left join (
+ select order_id, max(verify_time) as verify_time, max(staff_id) as verify_operator
+ from verify_record
+ where status = 1
+ group by order_id
+ ) v on v.order_id = o.id
+ where o.deleted = 0
+ and o.merchant_id = #{merchantId}
+ <if test="orderStatus != null">and o.order_status = #{orderStatus}</if>
+ <if test="verifyStatus != null">and o.verify_status = #{verifyStatus}</if>
+ <if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
+ <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
+ order by coalesce(v.verify_time, o.updated_at, o.created_at) desc, o.id desc
+ limit #{limit}
+ </script>
+ """);
+ }
+
+ public String selectDashboardCards() {
+ return sql("""
+ select
+ coalesce((
+ select sum(o.pay_amount)
+ from benefit_order o
+ where o.deleted = 0
+ and o.order_status = 1
+ and o.merchant_id = #{merchantId}
+ ), 0) as transactionAmountCents,
+ coalesce((
+ select count(*)
+ from verify_record r
+ where r.status = 1
+ and r.merchant_id = #{merchantId}
+ and r.verify_time >= #{todayStart}
+ and r.verify_time < #{todayEnd}
+ ), 0) as todayVerificationCount,
+ coalesce((
+ select count(*)
+ from verify_record r
+ where r.status = 1
+ and r.merchant_id = #{merchantId}
+ and r.verify_time >= #{weekStart}
+ and r.verify_time < #{weekEnd}
+ ), 0) as weekVerificationCount,
+ coalesce((
+ select sum(/*AMOUNT*/)
+ from benefit_order o
+ left join merchant_product p on p.id = o.product_id and p.deleted = 0
+ left join merchant m on m.id = o.merchant_id and m.deleted = 0
+ left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
+ where o.deleted = 0
+ and o.order_status = 1
+ and o.merchant_id = #{merchantId}
+ ), 0) as subsidyAmountCents
+ """);
+ }
+
+ private static String sql(String template) {
+ return template.replace("/*AMOUNT*/", GovernmentSubsidySql.AMOUNT_CENTS)
+ .replace("/*ORDER_TIME*/", GovernmentSubsidySql.ORDER_TIME);
+ }
+}
diff --git a/talent-card-server/src/main/java/com/talentcard/server/service/AdminWorkbenchStatisticsService.java b/talent-card-server/src/main/java/com/talentcard/server/service/AdminWorkbenchStatisticsService.java
index 3c847f6..7cee596 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/service/AdminWorkbenchStatisticsService.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/service/AdminWorkbenchStatisticsService.java
@@ -58,7 +58,7 @@
addAlert(alerts, "待处理申请", number(row.get("pendingApplicationCount")));
result.put("alertCount", alerts.stream().mapToLong(a -> number(a.get("count"))).sum());
result.put("alerts", alerts);
- result.put("statisticsBasis", "补贴仅统计政府承担的商品补贴和政府补贴券金额，商家自行让利不计入政府补贴");
+ result.put("statisticsBasis", "补贴按已支付订单计入（不要求核销）；仅统计政府承担的商品补贴和政府补贴券金额，商家自行让利不计入");
result.put("verificationTrend", verificationTrend(period));
result.put("verificationPeriod", normalizePeriod(period));
diff --git a/talent-card-server/src/main/resources/db/migration/V124__backfill_merchant_operator_store_access.sql b/talent-card-server/src/main/resources/db/migration/V124__backfill_merchant_operator_store_access.sql
new file mode 100644
index 0000000..ae0e3e1
--- /dev/null
+++ b/talent-card-server/src/main/resources/db/migration/V124__backfill_merchant_operator_store_access.sql
@@ -0,0 +1,15 @@
+insert into merchant_operator_store (operator_id, store_id)
+select o.id, s.store_id
+from merchant_operator o
+join (
+ select merchant_id, min(id) as store_id
+ from merchant_store
+ where status = 1 and deleted = 0
+ group by merchant_id
+) s on s.merchant_id = o.merchant_id
+where o.deleted = 0
+ and not exists (
+ select 1
+ from merchant_operator_store os
+ where os.operator_id = o.id and os.store_id = s.store_id
+ );
--
Gitblit v1.7.1