package com.talentcard.server.business.portal;
|
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.time.LocalDate;
|
import java.time.LocalDateTime;
|
import java.time.temporal.TemporalAdjusters;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Collections;
|
import java.util.Locale;
|
|
import com.talentcard.server.business.DatabaseRows;
|
import com.talentcard.server.business.PageResult;
|
import com.talentcard.server.common.BusinessException;
|
import com.talentcard.server.common.ErrorCode;
|
import org.jsoup.Jsoup;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import tools.jackson.core.type.TypeReference;
|
import tools.jackson.databind.ObjectMapper;
|
|
@Service
|
public class PortalService {
|
|
private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();
|
private final JdbcTemplate jdbcTemplate;
|
|
public PortalService(JdbcTemplate jdbcTemplate) {
|
this.jdbcTemplate = jdbcTemplate;
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> home(Long talentId) {
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("notices", query("""
|
select id, title, content, notice_type, created_at
|
from notice_message
|
where receiver_type = 'TALENT'
|
order by created_at desc
|
limit 3
|
"""));
|
result.put("news", query("""
|
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
|
limit 4
|
"""));
|
result.put("policies", query("""
|
select id, policy_no, title, subtitle, cover_url, is_top, publish_time
|
from policy_article
|
where deleted = 0 and status = 1
|
order by is_top desc, sort_no desc, publish_time desc, id desc
|
limit 5
|
"""));
|
result.put("benefits", query("""
|
select id, benefit_name, benefit_type, cover_url, description
|
from benefit
|
where deleted = 0 and status = 1
|
order by sort_no desc, id desc
|
limit 8
|
"""));
|
result.put("recommendedMerchants", query("""
|
select id, merchant_name, merchant_type, logo_url, cover_url, address
|
from merchant
|
where deleted = 0 and status = 1 and is_recommend = 1
|
order by sort_no desc, id desc
|
limit 6
|
"""));
|
Map<String, Object> homeSettings = new LinkedHashMap<>();
|
for (Map<String, Object> row : query("""
|
select setting_key, setting_value from admin_setting
|
where setting_key in ('homeVideoUrl', 'miniProgramUrl')
|
""")) {
|
homeSettings.put(String.valueOf(row.get("settingKey")), row.get("settingValue"));
|
}
|
result.put("homeVideoUrl", String.valueOf(homeSettings.getOrDefault("homeVideoUrl", "")));
|
result.put("miniProgramUrl", String.valueOf(homeSettings.getOrDefault("miniProgramUrl", "")));
|
if (talentId != null) {
|
result.put("cards", query("""
|
select id, talent_id, card_no, card_type, card_level, valid_start, valid_end, status
|
from talent_card
|
where deleted = 0 and talent_id = ?
|
order by id desc
|
""", talentId));
|
}
|
return result;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> news(int page, int size) {
|
PageResult<Map<String, Object>> result = page("""
|
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
|
""", "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);
|
item.put("contentPreview", htmlToText((String) item.get("content")));
|
item.remove("content");
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional
|
public Map<String, Object> newsDetail(Long id) {
|
int updated = jdbcTemplate.update("update news_article set view_count = view_count + 1 where id = ? and deleted = 0 and status = 1", id);
|
if (updated == 0) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "资讯不存在");
|
}
|
return one("""
|
select id, title, summary, cover_url, source, author, content, view_count, is_top, publish_time
|
from news_article
|
where deleted = 0 and status = 1 and id = ?
|
""", id);
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> policies(int page, int size) {
|
return page("""
|
select id, policy_no, title, subtitle, category_name, cover_url, tags, author, source, view_count, is_top, publish_time
|
from policy_article
|
where deleted = 0 and status = 1
|
order by is_top desc, sort_no desc, publish_time desc, id desc
|
""", "select count(*) from policy_article where deleted = 0 and status = 1", page, size);
|
}
|
|
@Transactional
|
public Map<String, Object> policy(Long id) {
|
int updated = jdbcTemplate.update("update policy_article set view_count = view_count + 1 where id = ? and deleted = 0 and status = 1", id);
|
if (updated == 0) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "政策已下架或不存在");
|
}
|
Map<String, Object> policy = one("""
|
select id, policy_no, title, subtitle, category_name, cover_url, content_images, tags, author, source, content, view_count, publish_time
|
from policy_article
|
where deleted = 0 and status = 1 and id = ?
|
""", id);
|
policy.put("categoryName", textOrDefault(policy.get("categoryName"), "东区政策"));
|
policy.put("heroImage", policy.get("coverUrl"));
|
policy.put("contentImages", imageUrls(policy.get("contentImages")));
|
policy.put("contentText", htmlToText((String) policy.get("content")));
|
return policy;
|
}
|
|
private String htmlToText(String content) {
|
return content == null || content.isBlank() ? "" : Jsoup.parse(content).wholeText().trim();
|
}
|
|
private String textOrDefault(Object value, String fallback) {
|
if (value == null) return fallback;
|
String text = String.valueOf(value).trim();
|
return text.isEmpty() ? fallback : text;
|
}
|
|
private List<String> imageUrls(Object value) {
|
if (value == null || String.valueOf(value).isBlank()) return List.of();
|
try {
|
return OBJECT_MAPPER.readValue(String.valueOf(value), new TypeReference<List<String>>() {}).stream()
|
.filter(url -> url != null && !url.isBlank())
|
.toList();
|
} catch (Exception ignored) {
|
return List.of();
|
}
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> benefits(String type, int page, int size) {
|
if (type == null || type.isBlank()) {
|
return page("""
|
select id, benefit_name, benefit_type, cover_url, description, valid_start, valid_end, total_quota, used_quota
|
from benefit
|
where deleted = 0 and status = 1
|
order by sort_no desc, id desc
|
""", "select count(*) from benefit where deleted = 0 and status = 1", page, size);
|
}
|
return page("""
|
select id, benefit_name, benefit_type, cover_url, description, valid_start, valid_end, total_quota, used_quota
|
from benefit
|
where deleted = 0 and status = 1 and benefit_type = ?
|
order by sort_no desc, id desc
|
""", "select count(*) from benefit where deleted = 0 and status = 1 and benefit_type = ?", page, size, type);
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> merchants(
|
String type,
|
String keyword,
|
String categoryId,
|
String mode,
|
String sortField,
|
String sortOrder,
|
int page,
|
int size
|
) {
|
String merchantWhere = " where deleted = 0 and status = 1";
|
List<Object> args = new java.util.ArrayList<>();
|
String requestedType = categoryId == null || categoryId.isBlank() ? type : categoryId;
|
if (requestedType != null && !requestedType.isBlank() && !"all".equalsIgnoreCase(requestedType)) {
|
merchantWhere = appendMerchantTypeFilter(merchantWhere, merchantTypesForRequestedValue(requestedType), args);
|
}
|
if (keyword != null && !keyword.isBlank()) {
|
List<String> categoryTypes = merchantTypesForCategory(keyword.trim());
|
if (!categoryTypes.isEmpty()) {
|
merchantWhere = appendMerchantTypeFilter(merchantWhere, categoryTypes, args);
|
} else {
|
merchantWhere += " and (merchant_name like ? or address like ? or merchant_type like ?)";
|
String like = "%" + keyword.trim() + "%";
|
args.add(like);
|
args.add(like);
|
args.add(like);
|
}
|
}
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
is_recommend, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size, args.toArray());
|
List<Map<String, Object>> records = result.records().stream().map(this::enrichMerchantSearchRow).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> foodTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'food'
|
""");
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'food' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> foodMerchants(int page, int size, Long talentId) {
|
String merchantWhere = " where deleted = 0 and merchant_type in ('food', '餐饮服务')";
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
status, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> item = enrichMerchantSearchRow(row);
|
Long merchantId = item.get("id") instanceof Number id ? id.longValue() : null;
|
item.put("couponAvailable", merchantId != null && hasActiveMerchantCoupon(merchantId));
|
item.put("discountText", merchantId == null ? "" : merchantLowestTalentDiscount(merchantId, talentId));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> travelTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'city'
|
""");
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'city' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> travelMerchants(int page, int size) {
|
String merchantWhere = " where deleted = 0 and merchant_type in ('scenic', '景区服务')";
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
status, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> normalized = new LinkedHashMap<>(row);
|
normalized.put("merchantType", "scenic");
|
return enrichMerchantSearchRow(normalized);
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> stayTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'home'
|
""");
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'home' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> stayMerchants(String type, int page, int size) {
|
String requestedType = type == null ? "" : type.trim();
|
String merchantType = switch (requestedType) {
|
case "hotel", "酒店服务" -> "酒店服务";
|
case "apartment", "公寓租赁服务" -> "公寓租赁服务";
|
default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的住宿服务类型");
|
};
|
String responseMerchantType = merchantType.equals("公寓租赁服务") ? "apartment" : "hotel";
|
String merchantWhere = " where deleted = 0 and merchant_type = ?";
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
status, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size, merchantType);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> normalized = new LinkedHashMap<>(row);
|
normalized.put("merchantType", responseMerchantType);
|
return enrichMerchantSearchRow(normalized);
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> entertainmentTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'entertainment'
|
""");
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'entertainment' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> entertainmentMerchants(int page, int size) {
|
String merchantWhere = " where deleted = 0 and merchant_type in ('cinema', 'theatre', '电影院')";
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
service_label, status, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> item = enrichMerchantSearchRow(row);
|
item.put("discountText", textOrDefault(item.get("serviceLabel"), textOrDefault(item.get("discountText"), "人才专享")));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> transportTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'transport'
|
""");
|
topic.put("title", topic.get("categoryLabel"));
|
topic.put("themeColor", "#F26A38");
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'transport' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> transportMerchants(String type, int page, int size) {
|
if (!List.of("fuel", "parking", "carWash").contains(type)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的行服务类型");
|
}
|
String merchantWhere = " where deleted = 0 and merchant_type = ?";
|
if (type.equals("parking")) {
|
type = "停车服务";
|
}if (type.equals("carWash")) {
|
type = "自助洗车";
|
}
|
PageResult<Map<String, Object>> result = page("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
service_label, status, sort_no, longitude, latitude, business_status,
|
(select min(p.sale_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as sale_price,
|
(select min(p.original_price) from merchant_product p where p.merchant_id = merchant.id and p.deleted = 0 and p.status = 1 and p.audit_status = 2) as original_price
|
from merchant
|
""" + merchantWhere + " order by sort_no asc, id asc",
|
"select count(*) from merchant" + merchantWhere, page, size, type);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> item = enrichMerchantSearchRow(row);
|
item.put("discountText", textOrDefault(item.get("serviceLabel"), textOrDefault(item.get("discountText"), "人才专享")));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> shoppingTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'shopping'
|
""");
|
topic.put("title", topic.get("categoryLabel"));
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'shopping' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
topic.put("categories", query("""
|
select category_key as id, category_name as name, sort_no
|
from shopping_topic_category
|
where deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> shoppingMerchants(String categoryId, int page, int size) {
|
if (categoryId == null || categoryId.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择购物服务分类");
|
int categoryCount = jdbcTemplate.queryForObject("""
|
select count(*) from shopping_topic_category
|
where category_key = ? and enabled = 1 and deleted = 0
|
""", Integer.class, categoryId);
|
if (categoryCount == 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的购物服务分类");
|
String merchantWhere = """
|
where m.deleted = 0 and m.merchant_type = 'shopping'
|
and mapping.deleted = 0 and mapping.category_key = ?
|
""";
|
PageResult<Map<String, Object>> result = page("""
|
select m.id, m.merchant_name, m.merchant_type, m.contact_name, m.contact_mobile, m.logo_url, m.cover_url, m.address,
|
m.service_label, m.status, m.sort_no, m.longitude, m.latitude, m.business_status
|
from merchant m
|
join shopping_topic_merchant mapping on mapping.merchant_id = m.id
|
""" + merchantWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
|
"select count(*) from merchant m join shopping_topic_merchant mapping on mapping.merchant_id = m.id " + merchantWhere,
|
page, size, categoryId);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> item = enrichMerchantSearchRow(row);
|
item.put("discountText", textOrDefault(item.get("serviceLabel"), "人才专享"));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> premiumTopic() {
|
Map<String, Object> topic = one("""
|
select category_key, category_label, intro
|
from equity_image_category
|
where category_key = 'premium'
|
""");
|
topic.put("title", topic.get("categoryLabel"));
|
topic.put("description", topic.get("intro"));
|
topic.put("images", query("""
|
select id, item_name as name, image_url, sort_no
|
from equity_image_item
|
where category_key = 'premium' and deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
topic.put("categories", query("""
|
select category_key as id, category_name as name, business_type, sort_no
|
from premium_topic_category
|
where deleted = 0 and enabled = 1
|
order by sort_no asc, id asc
|
"""));
|
return topic;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> premiumServices(String categoryId, int page, int size) {
|
if (categoryId == null || categoryId.isBlank()) throw new BusinessException(ErrorCode.BAD_REQUEST, "请选择臻享服务分类");
|
Integer categoryCount = jdbcTemplate.queryForObject("""
|
select count(*) from premium_topic_category
|
where category_key = ? and enabled = 1 and deleted = 0
|
""", Integer.class, categoryId);
|
if (categoryCount == null || categoryCount == 0) throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的臻享服务分类");
|
String serviceWhere = """
|
where m.deleted = 0 and mapping.deleted = 0 and mapping.category_key = ?
|
""";
|
PageResult<Map<String, Object>> result = page("""
|
select m.id, m.merchant_name, m.merchant_type, m.contact_mobile, m.cover_url, m.address,
|
m.service_label, m.status, m.sort_no, m.longitude, m.latitude,
|
mapping.category_key, mapping.business_type
|
from merchant m
|
join premium_topic_merchant mapping on mapping.merchant_id = m.id
|
""" + serviceWhere + " order by mapping.sort_no asc, m.sort_no asc, m.id asc",
|
"select count(*) from merchant m join premium_topic_merchant mapping on mapping.merchant_id = m.id " + serviceWhere,
|
page, size, categoryId);
|
List<Map<String, Object>> records = result.records().stream().map(row -> {
|
Map<String, Object> item = enrichMerchantSearchRow(row);
|
item.put("businessType", row.get("businessType"));
|
item.put("targetAction", row.get("businessType"));
|
item.put("benefitText", textOrDefault(row.get("serviceLabel"), "人才专享"));
|
item.put("discountText", textOrDefault(row.get("serviceLabel"), "人才专享"));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
private Map<String, Object> enrichMerchantSearchRow(Map<String, Object> row) {
|
Map<String, Object> result = new LinkedHashMap<>(row);
|
String merchantType = String.valueOf(result.getOrDefault("merchantType", ""));
|
result.put("categoryName", categoryName(merchantType));
|
result.put("detailType", detailType(merchantType));
|
result.put("detailBusinessId", result.get("id"));
|
result.put("targetAction", targetAction(merchantType));
|
Object status = result.get("status");
|
result.put("available", !(status instanceof Number number) || number.intValue() == 1);
|
result.put("sort", result.get("sortNo"));
|
result.put("discountText", discountText(result.get("salePrice"), result.get("originalPrice")));
|
return result;
|
}
|
|
private String categoryName(String merchantType) {
|
return switch (merchantType) {
|
case "food", "餐饮服务" -> "暖心食享";
|
case "hotel", "apartment" -> "舒心安居";
|
case "cinema", "theatre" -> "怡乐生活";
|
case "scenic", "景区服务" -> "悦览花城";
|
case "fuel", "parking", "carWash" -> "畅行无忧";
|
case "shopping" -> "惠享好物";
|
case "medical", "finance" -> "臻享礼遇";
|
default -> "其他权益";
|
};
|
}
|
|
private String appendMerchantTypeFilter(String merchantWhere, List<String> merchantTypes, List<Object> args) {
|
if (merchantTypes.size() == 1) {
|
args.add(merchantTypes.get(0));
|
return merchantWhere + " and merchant_type = ?";
|
}
|
args.addAll(merchantTypes);
|
String placeholders = String.join(",", Collections.nCopies(merchantTypes.size(), "?"));
|
return merchantWhere + " and merchant_type in (" + placeholders + ")";
|
}
|
|
private List<String> merchantTypesForRequestedValue(String value) {
|
List<String> categoryTypes = merchantTypesForCategory(value);
|
return categoryTypes.isEmpty() ? List.of(value) : categoryTypes;
|
}
|
|
private List<String> merchantTypesForCategory(String value) {
|
return switch (value) {
|
case "暖心食享", "餐饮服务", "food" -> List.of("food", "餐饮服务");
|
case "舒心安居", "hotel" -> List.of("hotel");
|
case "怡乐生活", "cinema", "theatre" -> List.of("cinema", "theatre");
|
case "悦览花城", "scenic", "景区服务" -> List.of("scenic", "景区服务");
|
case "畅行无忧" -> List.of("fuel", "parking", "carWash");
|
case "fuel" -> List.of("fuel");
|
case "parking" -> List.of("parking");
|
case "carWash" -> List.of("carWash");
|
case "惠享好物", "shopping" -> List.of("shopping");
|
case "臻享礼遇" -> List.of("medical", "finance");
|
case "medical" -> List.of("medical");
|
case "finance" -> List.of("finance");
|
default -> List.of();
|
};
|
}
|
|
private String detailType(String merchantType) {
|
return switch (merchantType) {
|
case "cinema" -> "CINEMA";
|
case "theatre" -> "THEATRE";
|
case "shopping" -> "SHOPPING";
|
case "medical" -> "MEDICAL";
|
case "finance" -> "FINANCE";
|
case "carWash" -> "CAR_WASH";
|
case "fuel" -> "FUEL";
|
case "parking" -> "PARKING";
|
default -> "MERCHANT";
|
};
|
}
|
|
private String targetAction(String merchantType) {
|
return detailType(merchantType);
|
}
|
|
private String discountText(Object salePrice, Object originalPrice) {
|
if (salePrice instanceof Number sale && originalPrice instanceof Number original
|
&& original.doubleValue() > 0 && sale.doubleValue() == 0) {
|
return "免费";
|
}
|
if (salePrice instanceof Number sale && originalPrice instanceof Number original
|
&& sale.doubleValue() > 0 && original.doubleValue() > sale.doubleValue()) {
|
double discount = Math.round(sale.doubleValue() / original.doubleValue() * 100) / 10.0;
|
return discount + "折";
|
}
|
return "人才专享";
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> merchantDetail(Long id) {
|
return merchantDetail(id, null);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> merchantDetail(Long id, Long talentId) {
|
Map<String, Object> merchant = one("""
|
select id, merchant_name, merchant_type, contact_name, contact_mobile, logo_url, cover_url, address,
|
business_status, longitude, latitude, group_buying_enabled, usage_instructions, rights_text,
|
service_label, status, is_recommend, sort_no
|
from merchant
|
where deleted = 0 and id = ?
|
""", id);
|
merchant.put("available", ((Number) merchant.getOrDefault("status", 0)).intValue() == 1);
|
merchant.put("groupBuyingEnabled", ((Number) merchant.getOrDefault("groupBuyingEnabled", 1)).intValue() == 1);
|
merchant.put("couponAvailable", hasActiveMerchantCoupon(id));
|
merchant.put("discountText", merchantLowestTalentDiscount(id, talentId));
|
return merchant;
|
}
|
|
private boolean hasActiveMerchantCoupon(Long merchantId) {
|
Long count = jdbcTemplate.queryForObject("""
|
select count(*)
|
from merchant_coupon
|
where merchant_id = ? and deleted = 0 and status = 1
|
and (valid_start is null or valid_start <= current_date)
|
and (valid_end is null or valid_end >= current_date)
|
""", Long.class, merchantId);
|
return count != null && count > 0;
|
}
|
|
private String merchantLowestTalentDiscount(Long merchantId, Long talentId) {
|
String cardLevel = activeProductCardLevel(talentId);
|
if (cardLevel == null) return "";
|
BigDecimal lowestRate = null;
|
BigDecimal lowestSalePrice = null;
|
BigDecimal lowestOriginalPrice = null;
|
for (Map<String, Object> product : query("""
|
select id, original_price, sale_price, rights_config
|
from merchant_product
|
where merchant_id = ? and deleted = 0 and status = 1 and audit_status = 2
|
""", merchantId)) {
|
BigDecimal originalPrice = decimal(product.get("originalPrice"));
|
if (originalPrice.signum() <= 0) originalPrice = decimal(product.get("salePrice"));
|
if (originalPrice.signum() <= 0) continue;
|
ProductPriceCalculator.ProductPrice price = ProductPriceCalculator.calculate(
|
originalPrice, product.get("salePrice"), product.get("rightsConfig"), cardLevel,
|
usedFreeCount(talentId, product.get("id"), product.get("rightsConfig"), cardLevel));
|
if (!price.personalized()) continue;
|
BigDecimal rate = price.salePrice().divide(originalPrice, 6, RoundingMode.HALF_UP);
|
if (lowestRate == null || rate.compareTo(lowestRate) < 0) {
|
lowestRate = rate;
|
lowestSalePrice = price.salePrice();
|
lowestOriginalPrice = originalPrice;
|
}
|
}
|
return lowestRate == null ? "" : discountText(lowestSalePrice, lowestOriginalPrice);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> productDetail(Long id) {
|
return productDetail(id, null);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> productDetail(Long id, Long talentId) {
|
Map<String, Object> product = one("""
|
select id, merchant_id, product_no, product_type, product_name, subtitle, cover_url,
|
original_price, sale_price, subsidy_amount, stock, sales_count, detail_text, benefit_desc,
|
purchase_notice, product_images, rights_config, valid_start_time, valid_end_time,
|
audit_status, status
|
from merchant_product
|
where deleted = 0 and id = ?
|
""", id);
|
int stock = ((Number) product.getOrDefault("stock", 0)).intValue();
|
boolean available = ((Number) product.getOrDefault("status", 0)).intValue() == 1
|
&& ((Number) product.getOrDefault("auditStatus", 0)).intValue() == 2;
|
product.put("available", available);
|
product.put("soldOut", stock == 0);
|
BigDecimal configuredOriginalPrice = decimal(product.get("originalPrice"));
|
if (configuredOriginalPrice.signum() <= 0) {
|
configuredOriginalPrice = decimal(product.get("salePrice"));
|
product.put("originalPrice", configuredOriginalPrice);
|
}
|
BigDecimal configuredSalePrice = decimal(product.get("salePrice"));
|
BigDecimal personalizedPrice = personalizedProductPrice(product, talentId);
|
product.put("salePrice", personalizedPrice);
|
product.put("personalized", talentId != null && personalizedPrice.compareTo(configuredSalePrice) != 0);
|
product.put("discountText", discountText(personalizedPrice, decimal(product.get("originalPrice"))));
|
return product;
|
}
|
|
@Transactional(readOnly = true)
|
public List<Map<String, Object>> availableOrderCoupons(Long merchantId, Long productId, String orderId) {
|
return availableOrderCoupons(merchantId, productId, orderId, null);
|
}
|
|
@Transactional(readOnly = true)
|
public List<Map<String, Object>> availableOrderCoupons(Long merchantId, Long productId, String orderId, Long talentId) {
|
Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
|
BigDecimal productAmount = personalizedProductPrice(product, talentId);
|
String cardLevel = talentId == null ? null : activeCouponCardLevel(talentId);
|
return query("""
|
select c.id, c.coupon_name, c.condition_type, c.condition_amount, c.coupon_amount,
|
c.valid_start, c.valid_end
|
from merchant_coupon c
|
where c.deleted = 0 and c.status = 1 and c.merchant_id = ?
|
and (c.valid_start is null or c.valid_start <= current_date)
|
and (c.valid_end is null or c.valid_end >= current_date)
|
and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
|
and (? is null or coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
|
and (c.product_scope = 'ALL' or exists (
|
select 1 from merchant_coupon_product cp
|
where cp.coupon_id = c.id and cp.product_id = ?
|
))
|
and (? is null or not exists (
|
select 1 from benefit_order o
|
where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
|
and (o.expires_at is null or o.expires_at > current_timestamp)
|
))
|
order by c.coupon_amount desc, c.id desc
|
""", merchantId, productAmount, cardLevel, cardLevel, productId, talentId, talentId).stream()
|
.map(this::couponListItem)
|
.toList();
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> orderCouponQuote(Long merchantId, Long productId, String orderId, Long couponId) {
|
return orderCouponQuote(merchantId, productId, orderId, couponId, null);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> orderCouponQuote(Long merchantId, Long productId, String orderId, Long couponId, Long talentId) {
|
Map<String, Object> product = orderCouponProduct(merchantId, productId, orderId);
|
BigDecimal originalAmount = decimal(product.get("originalPrice"));
|
if (originalAmount.signum() <= 0) originalAmount = decimal(product.get("salePrice"));
|
BigDecimal productAmount = personalizedProductPrice(product, talentId);
|
String cardLevel = talentId == null ? null : activeCouponCardLevel(talentId);
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("orderId", orderId);
|
result.put("merchantId", merchantId);
|
result.put("productId", productId);
|
result.put("productName", product.get("productName"));
|
result.put("coverUrl", product.get("coverUrl"));
|
result.put("originalAmount", originalAmount);
|
result.put("productAmount", productAmount);
|
result.put("productDiscountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
|
result.put("discountText", discountText(productAmount, originalAmount));
|
|
if (couponId == null) {
|
result.put("couponId", null);
|
result.put("couponLabel", "无");
|
result.put("couponAmount", BigDecimal.ZERO);
|
result.put("discountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
|
result.put("payAmount", productAmount);
|
result.put("subsidyAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO));
|
return result;
|
}
|
|
List<Map<String, Object>> coupons = query("""
|
select c.id, c.coupon_name, c.condition_type, c.condition_amount, c.coupon_amount,
|
c.valid_start, c.valid_end
|
from merchant_coupon c
|
where c.id = ? and c.deleted = 0 and c.status = 1 and c.merchant_id = ?
|
and (c.valid_start is null or c.valid_start <= current_date)
|
and (c.valid_end is null or c.valid_end >= current_date)
|
and (c.condition_type = 'NONE' or c.condition_amount is null or c.condition_amount <= ?)
|
and (? is null or coalesce(c.talent_levels, '') = '' or find_in_set(?, replace(c.talent_levels, '、', ',')) > 0)
|
and (c.product_scope = 'ALL' or exists (
|
select 1 from merchant_coupon_product cp
|
where cp.coupon_id = c.id and cp.product_id = ?
|
))
|
and (? is null or not exists (
|
select 1 from benefit_order o
|
where o.coupon_id = c.id and o.talent_id = ? and o.deleted = 0 and o.order_status = 0
|
and (o.expires_at is null or o.expires_at > current_timestamp)
|
))
|
""", couponId, merchantId, productAmount, cardLevel, cardLevel, productId, talentId, talentId);
|
if (coupons.isEmpty()) {
|
throw new BusinessException(ErrorCode.CONFLICT, "该优惠券当前不可用，请重新选择");
|
}
|
Map<String, Object> coupon = coupons.get(0);
|
BigDecimal couponAmount = decimal(coupon.get("couponAmount")).min(productAmount);
|
result.put("couponId", couponId);
|
result.put("couponName", coupon.get("couponName"));
|
result.put("couponLabel", "减" + moneyText(couponAmount) + "元");
|
result.put("couponAmount", couponAmount);
|
result.put("discountAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO).add(couponAmount));
|
result.put("payAmount", productAmount.subtract(couponAmount).max(BigDecimal.ZERO));
|
result.put("subsidyAmount", originalAmount.subtract(productAmount).max(BigDecimal.ZERO).add(couponAmount));
|
return result;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> products(Long merchantId, String type, Long talentId, int page, int size) {
|
StringBuilder sql = new StringBuilder("""
|
select id, merchant_id, product_no, product_type, product_name, subtitle, cover_url,
|
original_price, sale_price, subsidy_amount, stock, sales_count, benefit_desc, rights_config
|
from merchant_product
|
where deleted = 0 and status = 1 and audit_status = 2
|
""");
|
StringBuilder countSql = new StringBuilder("""
|
select count(*) from merchant_product
|
where deleted = 0 and status = 1 and audit_status = 2
|
""");
|
new Object();
|
List<Object> args = new java.util.ArrayList<>();
|
if (merchantId != null) {
|
sql.append(" and merchant_id = ?");
|
countSql.append(" and merchant_id = ?");
|
args.add(merchantId);
|
}
|
if (type != null && !type.isBlank()) {
|
sql.append(" and product_type = ?");
|
countSql.append(" and product_type = ?");
|
args.add(type);
|
}
|
sql.append(" order by sort_no desc, id desc");
|
PageResult<Map<String, Object>> result = page(sql.toString(), countSql.toString(), page, size, args.toArray());
|
List<Map<String, Object>> records = result.records().stream().map(product -> {
|
Map<String, Object> item = new LinkedHashMap<>(product);
|
BigDecimal originalPrice = decimal(item.get("originalPrice"));
|
if (originalPrice.signum() <= 0) originalPrice = decimal(item.get("salePrice"));
|
BigDecimal personalizedPrice = personalizedProductPrice(item, talentId);
|
item.put("originalPrice", originalPrice);
|
item.put("salePrice", personalizedPrice);
|
item.put("personalized", talentId != null && personalizedPrice.compareTo(decimal(product.get("salePrice"))) != 0);
|
item.put("discountText", discountText(personalizedPrice, originalPrice));
|
return item;
|
}).toList();
|
return new PageResult<>(records, result.page(), result.size(), result.total());
|
}
|
|
private Map<String, Object> orderCouponProduct(Long merchantId, Long productId, String orderId) {
|
if (merchantId == null || productId == null || orderId == null || orderId.isBlank()) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "订单、商户和商品信息不能为空");
|
}
|
Map<String, Object> product = one("""
|
select id, merchant_id, product_name, cover_url, original_price, sale_price, rights_config, stock, status, audit_status
|
from merchant_product
|
where deleted = 0 and id = ? and merchant_id = ?
|
""", productId, merchantId);
|
boolean available = ((Number) product.getOrDefault("status", 0)).intValue() == 1
|
&& ((Number) product.getOrDefault("auditStatus", 0)).intValue() == 2
|
&& ((Number) product.getOrDefault("stock", 0)).intValue() != 0;
|
if (!available) {
|
throw new BusinessException(ErrorCode.CONFLICT, "商品当前不可购买");
|
}
|
return product;
|
}
|
|
private BigDecimal personalizedProductPrice(Map<String, Object> product, Long talentId) {
|
if (talentId == null) return decimal(product.get("salePrice"));
|
String cardLevel = activeProductCardLevel(talentId);
|
if (cardLevel == null) return decimal(product.get("salePrice"));
|
return ProductPriceCalculator.calculate(product.get("originalPrice"), product.get("salePrice"), product.get("rightsConfig"), cardLevel,
|
usedFreeCount(talentId, product.get("id"), product.get("rightsConfig"), cardLevel)).salePrice();
|
}
|
|
private String activeProductCardLevel(Long talentId) {
|
if (talentId == null) return null;
|
List<Map<String, Object>> cards = query("select card_level from talent_card where deleted = 0 and status = 1 and talent_id = ? and (card_type = 'PERSONAL' or card_type is null) order by id desc limit 1", talentId);
|
Object cardLevel = cards.isEmpty() ? null : cards.get(0).get("cardLevel");
|
if (cardLevel == null) {
|
List<Map<String, Object>> talents = query("select talent_level from talent_user where deleted = 0 and status = 1 and id = ? limit 1", talentId);
|
if (!talents.isEmpty()) cardLevel = talents.get(0).get("talentLevel");
|
}
|
return cardLevel == null ? null : String.valueOf(cardLevel);
|
}
|
|
private int usedFreeCount(Long talentId, Object productId, Object rightsConfig, Object cardLevel) {
|
ProductPriceCalculator.ProductRight right = ProductPriceCalculator.productRight(rightsConfig, cardLevel);
|
if (right == null || !right.free() || !(productId instanceof Number number)) return 0;
|
Long count = jdbcTemplate.queryForObject("""
|
select count(*)
|
from benefit_order
|
where deleted = 0 and talent_id = ? and product_id = ? and pay_amount = 0 and order_status = 1
|
and created_at >= ?
|
""", Long.class, talentId, number.longValue(), freeWindowStart(right.resetCycle()));
|
return count == null ? 0 : count.intValue();
|
}
|
|
private LocalDateTime freeWindowStart(String resetCycle) {
|
String cycle = resetCycle == null ? "" : resetCycle.trim().toUpperCase(Locale.ROOT);
|
LocalDate today = LocalDate.now();
|
if (cycle.isBlank() || "NONE".equals(cycle)) return LocalDate.of(1970, 1, 1).atStartOfDay();
|
if (cycle.contains("DAY") || cycle.contains("天") || cycle.contains("日")) return today.atStartOfDay();
|
if (cycle.contains("WEEK") || cycle.contains("周") || cycle.contains("星期")) return today.minusDays(today.getDayOfWeek().getValue() - 1L).atStartOfDay();
|
if (cycle.contains("MONTH") || cycle.contains("月")) return today.withDayOfMonth(1).atStartOfDay();
|
return today.with(TemporalAdjusters.firstDayOfYear()).atStartOfDay();
|
}
|
|
private Map<String, Object> couponListItem(Map<String, Object> coupon) {
|
BigDecimal amount = decimal(coupon.get("couponAmount"));
|
BigDecimal conditionAmount = decimal(coupon.get("conditionAmount"));
|
boolean noThreshold = "NONE".equalsIgnoreCase(String.valueOf(coupon.get("conditionType")))
|
|| coupon.get("conditionAmount") == null;
|
Map<String, Object> item = new LinkedHashMap<>();
|
item.put("id", coupon.get("id"));
|
item.put("name", coupon.get("couponName"));
|
item.put("amount", moneyText(amount));
|
item.put("selectedLabel", "减" + moneyText(amount) + "元");
|
item.put("scope", noThreshold ? "无门槛可用" : "满" + moneyText(conditionAmount) + "元可用");
|
item.put("expireTime", coupon.get("validEnd") == null ? "长期有效" : String.valueOf(coupon.get("validEnd")));
|
item.put("available", true);
|
item.put("unavailableReason", "");
|
return item;
|
}
|
|
private BigDecimal decimal(Object value) {
|
if (value == null) return BigDecimal.ZERO;
|
return new BigDecimal(String.valueOf(value)).setScale(2, RoundingMode.HALF_UP);
|
}
|
|
private String moneyText(BigDecimal value) {
|
return value.setScale(2, RoundingMode.HALF_UP).stripTrailingZeros().toPlainString();
|
}
|
|
private String activeCouponCardLevel(Long talentId) {
|
List<Map<String, Object>> cards = query("select card_level from talent_card where talent_id=? and deleted=0 and status=1 and card_type='PERSONAL' and (valid_end is null or valid_end >= current_date) order by id desc limit 1", talentId);
|
if (cards.isEmpty()) throw new BusinessException(ErrorCode.CONFLICT, "当前暂无有效人才卡");
|
return String.valueOf(cards.get(0).get("cardLevel"));
|
}
|
|
private List<Map<String, Object>> query(String sql, Object... args) {
|
return DatabaseRows.normalize(jdbcTemplate.queryForList(sql, args));
|
}
|
|
private Map<String, Object> one(String sql, Object... args) {
|
List<Map<String, Object>> rows = query(sql, args);
|
if (rows.isEmpty()) {
|
throw new BusinessException(ErrorCode.NOT_FOUND, "数据不存在");
|
}
|
return rows.get(0);
|
}
|
|
private PageResult<Map<String, Object>> page(String sql, String countSql, int page, int size, Object... args) {
|
int safePage = Math.max(page, 1);
|
int safeSize = Math.min(Math.max(size, 1), 100);
|
Object[] pageArgs = java.util.Arrays.copyOf(args, args.length + 2);
|
pageArgs[args.length] = safeSize;
|
pageArgs[args.length + 1] = (safePage - 1) * safeSize;
|
List<Map<String, Object>> records = query(sql + " limit ? offset ?", pageArgs);
|
Long total = jdbcTemplate.queryForObject(countSql, Long.class, args);
|
return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
|
}
|
}
|
