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