又又更新

shinnosuke

2026-08-28 88c94d51fb9f82e856245908696ea4a970541a33

又又更新

 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; }

 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>


 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>

 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());
    }


 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); }

 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; }

 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);

 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("""

 talent-card-server/src/main/java/com/talentcard/server/mapper/GovernmentSubsidySql.java

New file
@@ -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() {
    }
}

 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) &gt;= #{startTime}</if>
                  <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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) &gt;= #{startTime}</if>
                  <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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) &gt;= #{startTime}</if>
              <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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,

 talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java

New file
@@ -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) &gt;= #{startTime}</if>
                  <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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) &gt;= #{startTime}</if>
                  <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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) &gt;= #{startTime}</if>
              <if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) &lt; #{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);
    }
}

 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));

 talent-card-server/src/main/resources/db/migration/V124__backfill_merchant_operator_store_access.sql

New file
@@ -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
  );