From 858f515995fd1dca7cf825069ce38c32703298d0 Mon Sep 17 00:00:00 2001
From: peng <peng.com>
Date: 星期五, 07 十一月 2025 14:14:50 +0800
Subject: [PATCH] 报名人员导出
---
backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java | 133 ++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 133 insertions(+), 0 deletions(-)
diff --git a/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java b/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
index 772be77..fe813f9 100644
--- a/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
+++ b/backend/src/main/java/com/rongyichuang/player/service/PlayerApplicationService.java
@@ -4,8 +4,12 @@
import com.rongyichuang.player.dto.response.ActivityPlayerApplicationResponse;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
+import java.io.ByteArrayOutputStream;
+import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@@ -124,6 +128,135 @@
}
/**
+ * 瀵煎嚭娲诲姩鎶ュ悕淇℃伅涓篍xcel
+ */
+ @SuppressWarnings("unchecked")
+ public byte[] exportApplicationsToExcel(String name, Long activityId, Integer state) throws IOException {
+ String baseSql =
+ "SELECT ap.id, p.name AS player_name, parent.name AS activity_name, ap.project_name AS project_name, u.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
+ "COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
+ "FROM t_activity_player ap " +
+ "JOIN t_player p ON p.id = ap.player_id " +
+ "JOIN t_user u ON u.id = p.user_id " +
+ "JOIN t_activity stage ON stage.id = ap.stage_id " +
+ "JOIN t_activity parent ON parent.id = stage.pid " +
+ "LEFT JOIN (" +
+ " SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
+ " FROM t_activity_player_rating " +
+ " WHERE state = 1 " +
+ " GROUP BY activity_player_id" +
+ ") rating_stats ON rating_stats.activity_player_id = ap.id ";
+
+ StringBuilder whereClause = new StringBuilder();
+ boolean hasCondition = false;
+
+ // 榛樿鍙樉绀虹涓�闃舵鐨勬暟鎹紙鍩轰簬sort_order=1锛夛紝閬垮厤纭紪鐮侀樁娈靛悕绉�
+ whereClause.append("stage.sort_order = 1");
+ hasCondition = true;
+
+ if (name != null && !name.isEmpty()) {
+ if (hasCondition) {
+ whereClause.append(" AND ");
+ }
+ whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
+ hasCondition = true;
+ }
+
+ if (activityId != null) {
+ if (hasCondition) {
+ whereClause.append(" AND ");
+ }
+ // 鏌ヨ鎸囧畾涓绘瘮璧涚殑绗竴闃舵鎶ュ悕椤圭洰锛歛ctivity_id=涓绘瘮璧汭D, stage_id=绗竴闃舵ID
+ whereClause.append("ap.activity_id = :activityId AND ap.stage_id = stage.id AND stage.pid = :activityId AND stage.sort_order = 1");
+ hasCondition = true;
+ }
+
+ if (state != null) {
+ if (hasCondition) {
+ whereClause.append(" AND ");
+ }
+ whereClause.append("ap.state = :state");
+ hasCondition = true;
+ }
+
+ String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
+ String order = "ORDER BY ap.create_time DESC ";
+
+ var q = em.createNativeQuery(baseSql + where + order);
+ if (name != null && !name.isEmpty()) {
+ q.setParameter("name", name);
+ }
+ if (activityId != null) {
+ q.setParameter("activityId", activityId);
+ }
+
+ if (state != null) {
+ q.setParameter("state", state);
+ }
+ List<Object[]> rows = q.getResultList();
+
+ // 鍒涘缓Excel宸ヤ綔绨�
+ Workbook workbook = new XSSFWorkbook();
+ Sheet sheet = workbook.createSheet("鎶ュ悕浜哄憳");
+
+ // 鍒涘缓鏍囬琛�
+ Row headerRow = sheet.createRow(0);
+ String[] headers = {"ID", "瀛﹀憳鍚嶇О", "姣旇禌鍚嶇О", "椤圭洰鍚嶇О", "鑱旂郴鐢佃瘽", "鐢宠鏃堕棿", "鐘舵��", "璇勫垎娆℃暟", "骞冲潎鍒�"};
+ for (int i = 0; i < headers.length; i++) {
+ Cell cell = headerRow.createCell(i);
+ cell.setCellValue(headers[i]);
+
+ // 璁剧疆鏍囬鏍峰紡
+ CellStyle headerStyle = workbook.createCellStyle();
+ Font font = workbook.createFont();
+ font.setBold(true);
+ headerStyle.setFont(font);
+ cell.setCellStyle(headerStyle);
+ }
+
+ // 濉厖鏁版嵁
+ int rowNum = 1;
+ for (Object[] r : rows) {
+ Row row = sheet.createRow(rowNum++);
+ row.createCell(0).setCellValue(r[0] != null ? r[0].toString() : "");
+ row.createCell(1).setCellValue(r[1] != null ? r[1].toString() : "");
+ row.createCell(2).setCellValue(r[2] != null ? r[2].toString() : "");
+ row.createCell(3).setCellValue(r[3] != null ? r[3].toString() : "");
+ row.createCell(4).setCellValue(r[4] != null ? r[4].toString() : "");
+ row.createCell(5).setCellValue(r[5] != null ? r[5].toString() : "");
+
+ // 鐘舵�佽浆鎹�
+ String stateText = "鏈煡";
+ if (r[6] != null) {
+ int stateValue = Integer.parseInt(r[6].toString());
+ switch (stateValue) {
+ case 0: stateText = "鏈鏍�"; break;
+ case 1: stateText = "瀹℃牳閫氳繃"; break;
+ case 2: stateText = "瀹℃牳椹冲洖"; break;
+ default: stateText = "鏈煡";
+ }
+ }
+ row.createCell(6).setCellValue(stateText);
+
+ row.createCell(7).setCellValue(r[7] != null ? r[7].toString() : "0");
+ row.createCell(8).setCellValue(r[8] != null ? r[8].toString() : "");
+ }
+
+ // 鑷姩璋冩暣鍒楀
+ for (int i = 0; i < headers.length; i++) {
+ sheet.autoSizeColumn(i);
+ }
+
+ // 灏嗗伐浣滅翱鍐欏叆瀛楄妭鏁扮粍
+ ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
+ workbook.write(outputStream);
+ workbook.close();
+ outputStream.close();
+
+ return outputStream.toByteArray();
+ }
+
+ /**
* 椤圭洰璇勫涓撶敤鏌ヨ锛屽寘鍚墍鏈夐樁娈垫暟鎹紙鍖呮嫭澶嶈禌銆佸喅璧涳級
* 涓巐istApplications鐨勫尯鍒細涓嶈繃婊ゅ璧涘拰鍐宠禌闃舵
*/
--
Gitblit v1.8.0