java/talent-card.git

			@@ -180,28 +180,45 @@
			if (status != null) { sql.append(" and status = ?"); countSql.append(" and status = ?"); args.add(status); }
			if (publishStart != null && !publishStart.isBlank()) { sql.append(" and date(publish_time) >= ?"); countSql.append(" and date(publish_time) >= ?"); args.add(publishStart); }
			if (publishEnd != null && !publishEnd.isBlank()) { sql.append(" and date(publish_time) <= ?"); countSql.append(" and date(publish_time) <= ?"); args.add(publishEnd); }
			sql.append(" order by is_top desc, sort_no desc, publish_time desc, id desc");
			// 置顶优先；置顶组内按“修改时间”倒序（最新修改的排前面，和人才端人才资讯一致），非置顶按排序值倒序。
			sql.append(" order by is_top desc, case when is_top = 1 then updated_at end desc, sort_no desc, publish_time desc, id desc");
			return page(sql.toString(), countSql.toString(), page, size, args.toArray());
			}

			@Transactional(readOnly = true)
			public Map<String, Object> policyDetail(Long id) {
			return one("select id, policy_no, title, subtitle, cover_url, author, source, content, view_count, is_top, sort_no, publish_time, status, created_at, updated_at from policy_article where deleted = 0 and id = ?", id);
			// created_by/updated_by 存的是管理员 ID，这里一并查出姓名，前端直接展示发布人/修改人。
			// 历史数据没有记录操作人时回落到 author（创建人）。
			return one("""
			select p.id, p.policy_no, p.title, p.subtitle, p.cover_url, p.author,
			p.created_by, p.updated_by, p.is_top, p.top_time,
			coalesce(creator.display_name, p.author) as created_by_name,
			coalesce(updater.display_name, p.author) as updated_by_name,
			p.source, p.content, p.view_count, p.sort_no, p.publish_time, p.status,
			p.created_at, p.updated_at
			from policy_article p
			left join admin_operator creator on creator.id = p.created_by and creator.deleted = 0
			left join admin_operator updater on updater.id = p.updated_by and updater.deleted = 0
			where p.deleted = 0 and p.id = ?
			""", id);
			}

			@Transactional
			public Map<String, Object> createPolicy(PolicyArticleRequest request, String operatorName) {
			public Map<String, Object> createPolicy(PolicyArticleRequest request, Long operatorId, String operatorName) {
			int status = defaultNewsStatus(request.status());
			jdbcTemplate.update("insert into policy_article(policy_no, title, subtitle, cover_url, author, source, content, is_top, sort_no, publish_time, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?, case when ? = 1 then now() else null end, ?)",
			BusinessIdGenerator.next("POLICY"), request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(operatorName), blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status);
			// top_time 只在勾选置顶时写入，列表用它做置顶组内的排序依据。
			jdbcTemplate.update("insert into policy_article(policy_no, title, subtitle, cover_url, author, created_by, updated_by, source, content, is_top, top_time, sort_no, publish_time, status) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, case when ? = 1 then now() else null end, ?)",
			BusinessIdGenerator.next("POLICY"), request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(operatorName), operatorId, operatorId, blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status);
			return policyDetail(jdbcTemplate.queryForObject("select last_insert_id()", Long.class));
			}

			@Transactional
			public Map<String, Object> updatePolicy(Long id, PolicyArticleRequest request) {
			public Map<String, Object> updatePolicy(Long id, PolicyArticleRequest request, Long operatorId) {
			int status = defaultNewsStatus(request.status());
			int updated = jdbcTemplate.update("update policy_article set title = ?, subtitle = ?, cover_url = ?, author = ?, source = ?, content = ?, is_top = ?, sort_no = ?, status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end where deleted = 0 and id = ?",
			request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(request.author()), blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status, id);
			// author 保持原样（发布人只在创建时确定）；updated_by 记录本次保存的管理员，空值不覆盖。
			// 新勾选置顶时记录置顶时间；取消置顶时清空，保证置顶组内按"最近置顶"排序。
			int updated = jdbcTemplate.update("update policy_article set title = ?, subtitle = ?, cover_url = ?, author = coalesce(nullif(?, ''), author), updated_by = coalesce(?, updated_by), source = ?, content = ?, is_top = ?, top_time = case when ? = 1 and coalesce(is_top, 0) = 0 then now() when ? = 0 then null else top_time end, sort_no = ?, status = ?, publish_time = case when ? = 1 then coalesce(publish_time, now()) else publish_time end where deleted = 0 and id = ?",
			request.title().trim(), blankToNull(request.subtitle()), blankToNull(request.coverUrl()), blankToNull(request.author()), operatorId, blankToNull(request.source()), sanitizeNewsContent(request.content()), Boolean.TRUE.equals(request.isTop()) ? 1 : 0, Boolean.TRUE.equals(request.isTop()) ? 1 : 0, Boolean.TRUE.equals(request.isTop()) ? 1 : 0, request.sortNo() == null ? 0 : request.sortNo(), status, status, id);
			ensureUpdated(updated);
			return policyDetail(id);
			}
			@@ -226,9 +243,11 @@
			int size
			) {
			StringBuilder sql = new StringBuilder("""
			select m.id, m.merchant_name, m.merchant_type, m.contact_name, m.contact_mobile,
			select m.id, m.merchant_name, m.merchant_type, m.merchant_type_code, m.contact_name, m.contact_mobile,
			m.logo_url, m.cover_url, m.address, m.is_recommend, m.sort_no, m.status,
			m.created_at,
			m.created_at, m.merchant_rights_config,
			(select s.external_link_type from transport_car_wash_service s
			where s.merchant_id = m.id and s.deleted = 0 order by s.id desc limit 1) as car_wash_external_link_type,
			(select count(*) from merchant_product p where p.merchant_id = m.id and p.deleted = 0) as product_count,
			(select count(*) from benefit_order o where o.merchant_id = m.id and o.deleted = 0) as order_count,
			0 as reservation_count
			@@ -346,6 +365,21 @@
			where merchant_id = ? and deleted = 0 and enabled = 1
			order by sort_no asc, id asc
			""", id)));
			// 加油服务详情页顶部轮播图：后台在商家编辑页维护，保存时整表替换。
			result.put("heroImages", DatabaseRows.normalize(jdbcTemplate.queryForList("""
			select id, image_url, sort_no, enabled
			from transport_fuel_hero_image
			where merchant_id = ? and deleted = 0
			order by sort_no asc, id asc
			""", id)));
			} else if (HERO_IMAGE_MERCHANT_TYPES.contains(String.valueOf(result.get("merchantTypeCode")))) {
			// 子女托管 / 行政审批的详情页轮播图同样按商家配置。
			result.put("heroImages", DatabaseRows.normalize(jdbcTemplate.queryForList("""
			select id, image_url, sort_no, enabled
			from merchant_hero_image
			where merchant_id = ? and deleted = 0
			order by sort_no asc, id asc
			""", id)));
			}
			return result;
			}
			@@ -408,6 +442,7 @@
			ensureParkingBenefit(id, merchantType.code());
			ensureFuelBenefit(id, merchantType.code(), request.serviceLabel());
			syncFuelStores(id, merchantType.code(), request.applicableStores());
			syncMerchantHeroImages(id, merchantType.code(), request.heroImages());
			syncPremiumTopicMerchant(id, merchantType, request.sortNo());
			Long storeId = createDefaultStore(id, request.merchantName(), request.address());
			syncMerchantBusinessHours(id, storeId, request.businessHours());
			@@ -553,6 +588,7 @@
			ensureParkingBenefit(id, merchantType.code());
			ensureFuelBenefit(id, merchantType.code(), request.serviceLabel());
			syncFuelStores(id, merchantType.code(), request.applicableStores());
			syncMerchantHeroImages(id, merchantType.code(), request.heroImages());
			syncPremiumTopicMerchant(id, merchantType, request.sortNo());
			Long storeId = findOrCreateDefaultStore(id, request.merchantName(), request.address());
			syncMerchantBusinessHours(id, storeId, request.businessHours());
			@@ -667,10 +703,80 @@
			}
			}

			/**
			* 子女托管 / 行政审批的详情页图：这两类服务的权益是全局一条，图片也全局共用。
			*/
			@Transactional(readOnly = true)
			public Map<String, Object> serviceHeroImages() {
			Map<String, Object> result = new LinkedHashMap<>();
			result.put("children", firstRowOrEmpty("select id, hero_image from children_entrustment_benefit where deleted = 0 order by id asc limit 1"));
			result.put("approval", firstRowOrEmpty("select id, hero_image from admin_approval_benefit where deleted = 0 order by id asc limit 1"));
			return result;
			}

			@Transactional
			public Map<String, Object> saveServiceHeroImage(String type, String imageUrl) {
			String table = switch (type == null ? "" : type.trim()) {
			case "children" -> "children_entrustment_benefit";
			case "approval" -> "admin_approval_benefit";
			default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "不支持的服务类型");
			};
			String url = imageUrl == null \|\| imageUrl.isBlank() ? null : imageUrl.trim();
			int updated = jdbcTemplate.update("update " + table + " set hero_image = ? where deleted = 0 order by id asc limit 1", url);
			if (updated == 0) throw new BusinessException(ErrorCode.NOT_FOUND, "该服务的权益暂未配置");
			return serviceHeroImages();
			}

			private Map<String, Object> firstRowOrEmpty(String sql, Object... args) {
			List<Map<String, Object>> rows = DatabaseRows.normalize(jdbcTemplate.queryForList(sql, args));
			return rows.isEmpty() ? new LinkedHashMap<>() : rows.get(0);
			}

			private boolean enabledFlag(Object value) {
			if (value == null) return true;
			if (value instanceof Boolean bool) return bool;
			if (value instanceof Number number) return number.intValue() != 0;
			String text = String.valueOf(value).trim();
			return "true".equalsIgnoreCase(text) \|\| "1".equals(text);
			}

			private Double numberValue(Object value) {
			if (value instanceof Number number) return number.doubleValue();
			try { return value == null ? null : Double.valueOf(String.valueOf(value)); }
			catch (NumberFormatException ignored) { return null; }
			}

			/** 详情页轮播图按商家配置的服务类型：其余类型顶部图仍用封面图或团单封面。 */
			private static final List<String> HERO_IMAGE_MERCHANT_TYPES = List.of("children", "approval");

			/**
			* 同步商家详情页轮播图。
			*
			* 后台提交整份列表，这里按商家整表替换；列表为 null 表示本次提交没有这个字段，保持原样。
			* 加油服务沿用原有的 transport_fuel_hero_image，子女托管/行政审批使用通用的 merchant_hero_image。
			*/
			private void syncMerchantHeroImages(Long merchantId, String merchantType, List<Map<String, Object>> images) {
			if (images == null) return;
			boolean fuel = "fuel".equals(merchantType);
			if (!fuel && !HERO_IMAGE_MERCHANT_TYPES.contains(merchantType)) return;
			List<Map<String, Object>> normalized = new ArrayList<>();
			for (Map<String, Object> image : images) {
			if (image == null) continue;
			Object urlValue = image.containsKey("imageUrl") ? image.get("imageUrl") : image.get("image_url");
			String url = urlValue == null ? "" : String.valueOf(urlValue).trim();
			if (url.isBlank()) continue;
			Map<String, Object> item = new LinkedHashMap<>();
			item.put("imageUrl", url);
			item.put("enabled", enabledFlag(image.get("enabled")) ? 1 : 0);
			normalized.add(item);
			}
			String table = fuel ? "transport_fuel_hero_image" : "merchant_hero_image";
			jdbcTemplate.update("delete from " + table + " where merchant_id = ?", merchantId);
			String insertSql = "insert into " + table + "(merchant_id, image_url, sort_no, enabled, deleted) values (?, ?, ?, ?, 0)";
			for (int index = 0; index < normalized.size(); index++) {
			Map<String, Object> image = normalized.get(index);
			jdbcTemplate.update(insertSql, merchantId, image.get("imageUrl"), (index + 1) * 10, image.get("enabled"));
			}
			}

			private void syncPremiumTopicMerchant(Long merchantId, MerchantType merchantType, Integer sortNo) {
			@@ -732,6 +838,64 @@
			return one("select * from benefit where id = ?", id);
			}

			/**
			* 商家编辑页面的团单列表。
			*
			* 按商品维度展示（一条团单一行），修改团单只更新原记录的状态为待审核；
			* 审核记录（merchant_product_audit_record）仍只在团单审核页面展示。
			*/
			@Transactional(readOnly = true)
			public PageResult<Map<String, Object>> merchantProducts(
			Long merchantId,
			String keyword,
			Integer auditStatus,
			String createdStart,
			String createdEnd,
			int page,
			int size
			) {
			StringBuilder sql = new StringBuilder("""
			select p.id, p.id as product_id, p.merchant_id, m.merchant_name, m.merchant_type, m.merchant_type_code, m.contact_mobile,
			p.product_no, p.product_type, p.product_name, p.subtitle, p.cover_url,
			p.original_price, p.sale_price, p.subsidy_amount, p.is_government_subsidy,
			p.stock, %s, p.detail_text, p.benefit_desc, p.purchase_notice,
			p.valid_start_time, p.valid_end_time, p.product_images, p.rights_config,
			p.audit_status, p.audit_remark, p.status, p.sort_no, p.submit_time, p.created_at, p.updated_at
			from merchant_product p
			left join merchant m on m.id = p.merchant_id
			where p.deleted = 0 and p.merchant_id = ?
			""".formatted(salesCountSelect("p.id", "p")));
			StringBuilder countSql = new StringBuilder("""
			select count(*)
			from merchant_product p
			where p.deleted = 0 and p.merchant_id = ?
			""");
			List<Object> args = new ArrayList<>();
			args.add(merchantId);
			if (keyword != null && !keyword.isBlank()) {
			sql.append(" and p.product_name like ?");
			countSql.append(" and p.product_name like ?");
			args.add("%" + keyword.trim() + "%");
			}
			if (auditStatus != null) {
			sql.append(" and p.audit_status = ?");
			countSql.append(" and p.audit_status = ?");
			args.add(auditStatus);
			}
			if (createdStart != null && !createdStart.isBlank()) {
			sql.append(" and date(coalesce(p.submit_time, p.created_at)) >= ?");
			countSql.append(" and date(coalesce(p.submit_time, p.created_at)) >= ?");
			args.add(createdStart);
			}
			if (createdEnd != null && !createdEnd.isBlank()) {
			sql.append(" and date(coalesce(p.submit_time, p.created_at)) <= ?");
			countSql.append(" and date(coalesce(p.submit_time, p.created_at)) <= ?");
			args.add(createdEnd);
			}
			sql.append(" order by coalesce(p.updated_at, p.submit_time, p.created_at) desc, p.id desc");
			return page(sql.toString(), countSql.toString(), page, size, args.toArray());
			}

			@Transactional(readOnly = true)
			public PageResult<Map<String, Object>> products(
			Long merchantId,
			@@ -749,7 +913,7 @@
			select r.id, r.product_id, r.merchant_id, m.merchant_name, m.merchant_type, m.merchant_type_code, m.contact_mobile,
			r.product_no, r.product_type, r.product_name,
			r.subtitle, r.cover_url, r.original_price, r.sale_price, r.subsidy_amount,
			r.is_government_subsidy, r.stock, r.sales_count, r.detail_text, r.benefit_desc,
			r.is_government_subsidy, r.stock, %s, r.detail_text, r.benefit_desc,
			r.purchase_notice, r.valid_start_time, r.valid_end_time, r.product_images, r.rights_config,
			r.audit_status, r.audit_remark, r.audit_by, r.audit_time, r.submit_time,
			r.source_created_at as created_at, p.status
			@@ -757,7 +921,7 @@
			left join merchant_product p on p.id = r.product_id
			left join merchant m on m.id = r.merchant_id
			where r.deleted = 0
			""");
			""".formatted(salesCountSelect("r.product_id", "p")));
			StringBuilder countSql = new StringBuilder("""
			select count(*)
			from merchant_product_audit_record r
			@@ -816,13 +980,13 @@
			select p.id, p.merchant_id, m.merchant_name, m.merchant_type, m.merchant_type_code, m.contact_mobile,
			p.product_no, p.product_type, p.product_name,
			p.subtitle, p.cover_url, p.original_price, p.sale_price, p.subsidy_amount,
			p.is_government_subsidy, p.stock, p.sales_count, p.detail_text, p.benefit_desc,
			p.is_government_subsidy, p.stock, %s, p.detail_text, p.benefit_desc,
			p.purchase_notice, p.valid_start_time, p.valid_end_time, p.product_images, p.rights_config,
			p.audit_status, p.audit_remark, p.status, p.sort_no, p.submit_time, p.created_at, p.updated_at
			from merchant_product p
			left join merchant m on m.id = p.merchant_id
			where p.deleted = 0 and p.id = ?
			""", id);
			""".formatted(salesCountSelect("p.id", "p")), id);
			}

			@Transactional(readOnly = true)
			@@ -831,7 +995,7 @@
			select r.id, r.product_id, r.merchant_id, m.merchant_name, m.merchant_type, m.merchant_type_code, m.contact_mobile,
			r.product_no, r.product_type, r.product_name, r.subtitle, r.cover_url,
			r.original_price, r.sale_price, r.subsidy_amount, r.is_government_subsidy,
			r.stock, r.sales_count, r.detail_text, r.benefit_desc, r.purchase_notice,
			r.stock, %s, r.detail_text, r.benefit_desc, r.purchase_notice,
			r.valid_start_time, r.valid_end_time, r.product_images, r.rights_config,
			r.audit_status, r.audit_remark, r.audit_by, r.audit_time, r.submit_time,
			r.source_created_at as created_at, p.status
			@@ -839,7 +1003,7 @@
			left join merchant_product p on p.id = r.product_id
			left join merchant m on m.id = r.merchant_id
			where r.deleted = 0 and r.id = ?
			""", auditRecordId);
			""".formatted(salesCountSelect("r.product_id", "p")), auditRecordId);
			}

			@Transactional
			@@ -1416,6 +1580,18 @@
			return Boolean.TRUE.equals(value == null ? defaultValue : value) ? (byte) 1 : (byte) 0;
			}

			/**
			* 团单销量 = 该商品的有效订单数（已支付，且未取消/未退款）。
			*
			* 商品表上的 sales_count 只在部分流程（如酒店房型预约）里累加，普通团单下单后一直是 0，
			* 后台列表就统计不出销量。这里直接按订单算，同时保留 sales_count 作为下限，兼容历史数据。
			*/
			String salesCountSelect(String productIdExpression, String productAlias) {
			return "greatest(coalesce(" + productAlias + ".sales_count, 0), (select count(*) from benefit_order bo"
			+ " where bo.deleted = 0 and bo.product_id = " + productIdExpression
			+ " and bo.order_status = 1 and (bo.refund_status is null or bo.refund_status = 0))) as sales_count";
			}

			private List<Long> couponProductIds(Long couponId) {
			return jdbcTemplate.queryForList(
			"select product_id from merchant_coupon_product where coupon_id = ? order by id",
			@@ -1425,13 +1601,13 @@

			private List<Map<String, Object>> couponProducts(Long couponId) {
			return DatabaseRows.normalize(jdbcTemplate.queryForList("""
			select p.id, p.product_name, p.cover_url, p.sale_price, p.sales_count, p.submit_time, p.created_at,
			select p.id, p.product_name, p.cover_url, p.sale_price, %s, p.submit_time, p.created_at,
			p.audit_status, p.status
			from merchant_coupon_product cp
			join merchant_product p on p.id = cp.product_id
			where cp.coupon_id = ? and p.deleted = 0
			order by cp.id
			""", couponId));
			""".formatted(salesCountSelect("p.id", "p")), couponId));
			}

			private void syncCouponProducts(Long couponId, List<Long> productIds, String productScope) {