1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

-- 创建数据库
CREATE DATABASE IF NOT EXISTS bank_reg_report CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bank_reg_report;

-- 分支机构表
CREATE TABLE branch (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(100) NOT NULL
);

-- 客户表（简化）
CREATE TABLE customer (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  id_no VARCHAR(50),
  industry varchar(50) DEFAULT '未知',
  risk_level varchar(20) DEFAULT '未知'
);

-- 账户/存款表（用于存款汇总）
CREATE TABLE account (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT,
  customer_id INT,
  account_type ENUM('DEPOSIT','LOAN','OTHER') DEFAULT 'DEPOSIT',
  balance DECIMAL(18,2) DEFAULT 0,
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (branch_id) REFERENCES branch(id),
  FOREIGN KEY (customer_id) REFERENCES customer(id)
);

-- 贷款表（用于贷款敞口）
CREATE TABLE loan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT,
  customer_id INT,
  original_amount DECIMAL(18,2),
  outstanding_amount DECIMAL(18,2),
  loan_type VARCHAR(50),
  status VARCHAR(20),
  created_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (branch_id) REFERENCES branch(id),
  FOREIGN KEY (customer_id) REFERENCES customer(id)
);

-- 交易表（用于可疑交易统计）
CREATE TABLE transaction_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT,
  customer_id INT,
  amount DECIMAL(18,2),
  txn_type VARCHAR(50),
  txn_time DATETIME,
  channel VARCHAR(50),
  description VARCHAR(255),
  FOREIGN KEY (branch_id) REFERENCES branch(id)
);

-- 报表存储结果表：每个报表类型通用一张（这里简单）
CREATE TABLE report_store (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_code VARCHAR(50) NOT NULL,
  branch_id INT,
  data_key VARCHAR(100),
  data_value TEXT,
  period DATE,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW()
);

-- 报表提交记录表
CREATE TABLE report_submission (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_code VARCHAR(50),
  period DATE,
  branch_id INT,
  status ENUM('DRAFT','SUBMITTED','APPROVED','REJECTED') DEFAULT 'DRAFT',
  submitter VARCHAR(100),
  submit_time DATETIME,
  remark VARCHAR(255)
);

-- 示例数据
INSERT INTO branch (code,name) VALUES ('B001','总行'),('B002','分行A'),('B003','分行B');
INSERT INTO customer (name,id_no) VALUES ('张三','ID1001'),('李四','ID1002'),('王五','ID1003');

INSERT INTO account (branch_id,customer_id,account_type,balance) VALUES
(1,1,'DEPOSIT',1000000.00),(1,2,'DEPOSIT',500000.00),(2,3,'DEPOSIT',200000.00),
(2,1,'DEPOSIT',120000.00),(3,2,'DEPOSIT',300000.00);

INSERT INTO loan (branch_id,customer_id,original_amount,outstanding_amount,loan_type,status) VALUES
(1,1,500000,200000,'CORPORATE','ACTIVE'),(1,2,200000,100000,'RETAIL','ACTIVE'),
(2,3,300000,120000,'RETAIL','ACTIVE');

INSERT INTO transaction_log (branch_id,customer_id,amount,txn_type,txn_time,channel,description) VALUES
(1,1,150000,'TRANSFER',NOW(),'ONLINE','大额转账'),(2,3,50000,'WITHDRAW',NOW(),'ATM','取款'),(3,2,200000,'TRANSFER',NOW(),'ONLINE','大额转账');

-- 存储过程：计算余额汇总（Balance Summary）
DROP PROCEDURE IF EXISTS sp_calc_balance_summary;
DELIMITER $$
CREATE PROCEDURE sp_calc_balance_summary(IN p_period DATE, IN p_branch_id INT)
BEGIN
  -- 返回分支当日/当月的存款总额和贷款余额总额，并将结果写入 report_store
  DECLARE v_deposits DECIMAL(18,2) DEFAULT 0;
  DECLARE v_loans DECIMAL(18,2) DEFAULT 0;

  SELECT IFNULL(SUM(balance),0) INTO v_deposits FROM account WHERE branch_id = p_branch_id AND account_type='DEPOSIT';
  SELECT IFNULL(SUM(outstanding_amount),0) INTO v_loans FROM loan WHERE branch_id = p_branch_id AND status='ACTIVE';

  -- 写入 report_store: key=TOTAL_DEPOSITS, TOTAL_LOANS
  INSERT INTO report_store(report_code,branch_id,data_key,data_value,period)
  VALUES('BALANCE_SUMMARY', p_branch_id, 'TOTAL_DEPOSITS', CAST(v_deposits AS CHAR), p_period);
  INSERT INTO report_store(report_code,branch_id,data_key,data_value,period)
  VALUES('BALANCE_SUMMARY', p_branch_id, 'TOTAL_LOANS', CAST(v_loans AS CHAR), p_period);

  -- 返回结果集（供直接查询）
  SELECT v_deposits AS total_deposits, v_loans AS total_loans;
END$$
DELIMITER ;

-- 存储过程：计算贷款敞口（Loan Exposure）
DROP PROCEDURE IF EXISTS sp_calc_loan_exposure;
DELIMITER $$
CREATE PROCEDURE sp_calc_loan_exposure(IN p_period DATE, IN p_branch_id INT)
BEGIN
  SELECT loan_type, COUNT(*) AS cnt, IFNULL(SUM(outstanding_amount),0) AS exposure
  FROM loan WHERE branch_id = p_branch_id AND status='ACTIVE' GROUP BY loan_type;
END$$
DELIMITER ;

-- 存储过程：可疑交易统计（大额交易）
DROP PROCEDURE IF EXISTS sp_calc_suspicious_transactions;
DELIMITER $$
CREATE PROCEDURE sp_calc_suspicious_transactions(IN p_period DATE, IN p_branch_id INT, IN p_threshold DECIMAL(18,2))
BEGIN
  SELECT customer_id, COUNT(*) AS txn_count, IFNULL(SUM(amount),0) AS total_amount
  FROM transaction_log
  WHERE branch_id = p_branch_id AND amount >= p_threshold
  GROUP BY customer_id HAVING SUM(amount) >= p_threshold;
END$$
DELIMITER ;

-- 存储过程：保存/更新报表草稿
DROP PROCEDURE IF EXISTS sp_save_report_data;
DELIMITER $$
CREATE PROCEDURE sp_save_report_data(IN p_report_code VARCHAR(50), IN p_branch_id INT, IN p_period DATE, IN p_key VARCHAR(100), IN p_value TEXT)
BEGIN
  DECLARE v_id INT;
  SELECT id INTO v_id FROM report_store WHERE report_code = p_report_code AND branch_id = p_branch_id AND period = p_period AND data_key = p_key LIMIT 1;
  IF v_id IS NULL THEN
    INSERT INTO report_store(report_code,branch_id,data_key,data_value,period) VALUES(p_report_code,p_branch_id,p_key,p_value,p_period);
  ELSE
    UPDATE report_store SET data_value = p_value WHERE id = v_id;
  END IF;
END$$
DELIMITER ;

-- 存储过程：提交报表
DROP PROCEDURE IF EXISTS sp_submit_report;
DELIMITER $$
CREATE PROCEDURE sp_submit_report(IN p_report_code VARCHAR(50), IN p_branch_id INT, IN p_period DATE, IN p_submitter VARCHAR(100))
BEGIN
  INSERT INTO report_submission(report_code,period,branch_id,status,submitter,submit_time)
  VALUES(p_report_code,p_period,p_branch_id,'SUBMITTED',p_submitter,NOW());
END$$
DELIMITER ;

-- 索引
CREATE INDEX idx_report_store_code ON report_store(report_code);
CREATE INDEX idx_report_submission ON report_submission(report_code);

COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.yutao.report</groupId>
  <artifactId>bank-regulatory-reporting</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>银行监管报送系统</name>
  <description>基于JSP的银行监管报表系统</description>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>21</maven.compiler.source>
    <maven.compiler.target>21</maven.compiler.target>
    <mysql.version>8.0.30</mysql.version>
    <gson.version>2.8.9</gson.version>
    <servlet.api.version>4.0.1</servlet.api.version>
  </properties>

  <dependencies>
    <!-- MySQL数据库驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.version}</version>
    </dependency>

    <!-- Gson（JSON处理） -->
    <dependency>
      <groupId>com.google.code.gson</groupId>
      <artifactId>gson</artifactId>
      <version>${gson.version}</version>
    </dependency>

    <!-- Servlet API（Web容器提供，打包时排除） -->
    <!-- <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>${servlet.api.version}</version>
      <scope>provided</scope> 
    </dependency> -->

    <dependency>
      <groupId>jakarta.servlet</groupId>
      <artifactId>jakarta.servlet-api</artifactId>
      <version>6.0.0</version>
    </dependency>

  </dependencies>

  <build>
    <finalName>bank-regulatory-reporting</finalName>
    <resources>
      <resource>
        <directory>src/main/resources</directory>
        <filtering>false</filtering>
      </resource>
    </resources>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to
      parent pom) -->
      <plugins>
        <!-- 编译插件 -->
        <plugin>
          <groupId>org.apache.maven.plugins</groupId>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.1</version>
          <configuration>
            <source>${maven.compiler.source}</source>
            <target>${maven.compiler.target}</target>
            <encoding>UTF-8</encoding>
          </configuration>
        </plugin>
        <!-- Web打包插件 -->
        <plugin>
          <groupId>org.apache.maven.plugins</groupId>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.3.2</version>
          <configuration>
            <webResources>
              <!-- 确保XML模板和JSP被正确打包 -->
              <resource>
                <directory>src/main/webapp</directory>
                <filtering>true</filtering>
              </resource>
            </webResources>
          </configuration>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

package com.yutao.report.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.yutao.report.util.DBUtil;

public class ReportDAO {

    // 读取 xml 模板（由 servlet 负责读取文件） - 这里是数据层操作示例

    // 调用存储过程并返回 ResultSet 转成 List<Map>
    public List<Map<String, Object>> callProcedure(String proc, Object... params) throws Exception {
        try (Connection c = DBUtil.getConnection()){
            // 构建占位符
            StringBuilder sb = new StringBuilder();
            for (int i=0;i<params.length;i++) sb.append("?,");
            String ph = params.length>0? sb.substring(0,sb.length()-1):"";
            String sql = "CALL "+proc+"("+ph+")";
            try (CallableStatement cs = c.prepareCall(sql)){
                for (int i=0;i<params.length;i++){
                    cs.setObject(i+1, params[i]);
                }
                boolean has = cs.execute();
                List<Map<String,Object>> list = new ArrayList<>();
                if (has) {
                    try (ResultSet rs = cs.getResultSet()){
                        ResultSetMetaData md = rs.getMetaData();
                        int cols = md.getColumnCount();
                        while (rs.next()){
                            Map<String,Object> row = new LinkedHashMap<>();
                            for (int i=1;i<=cols;i++){
                                row.put(md.getColumnLabel(i), rs.getObject(i));
                            }
                            list.add(row);
                        }
                    }
                }
                return list;
            }
        }
    }

    // 保存报表数据 (调用存储过程 sp_save_report_data)
    public void saveReportData(String reportCode, int branchId, java.sql.Date period, String key, String value) throws Exception{
        try (Connection c = DBUtil.getConnection(); CallableStatement cs = c.prepareCall("CALL sp_save_report_data(?,?,?,?,?)")){
            cs.setString(1, reportCode);
            cs.setInt(2, branchId);
            cs.setDate(3, period);
            cs.setString(4, key);
            cs.setString(5, value);
            cs.execute();
        }
    }

    public void submitReport(String reportCode, int branchId, java.sql.Date period, String submitter) throws Exception{
        try (Connection c = DBUtil.getConnection(); CallableStatement cs = c.prepareCall("CALL sp_submit_report(?,?,?,?)")){
            cs.setString(1, reportCode);
            cs.setInt(2, branchId);
            cs.setDate(3, period);
            cs.setString(4, submitter);
            cs.execute();
        }
    }

    // 直接读取 report_store 表中指定报表和 period 的 key-value 列表
    public Map<String,String> loadReportStore(String reportCode, int branchId, java.sql.Date period) throws Exception{
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement("SELECT data_key,data_value FROM report_store WHERE report_code=? AND branch_id=? AND period=?")){
            ps.setString(1, reportCode);
            ps.setInt(2, branchId);
            ps.setDate(3, period);
            try (ResultSet rs = ps.executeQuery()){
                Map<String,String> m = new LinkedHashMap<>();
                while (rs.next()){
                    m.put(rs.getString("data_key"), rs.getString("data_value"));
                }
                return m;
            }
        }
    }
}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

package com.yutao.report.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yutao.report.util.DBUtil;

public class ReportService {
    
    // 获取报表数据
    public List<Map<String, Object>> getReportData(String reportCode, String reportDate) throws SQLException {
        String tableName = getTableNameByCode(reportCode);
        if (tableName == null) {
            throw new IllegalArgumentException("无效的报表代码: " + reportCode);
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> result = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM " + tableName + " WHERE report_date = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reportDate);
            rs = pstmt.executeQuery();
            
            int columnCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rs.getMetaData().getColumnName(i);
                    row.put(columnName, rs.getObject(i));
                }
                result.add(row);
            }
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        }
        
        return result;
    }
    
    // 执行存储过程获取统计数据
    public Map<String, Object> executeProcedure(String procedureName, String reportDate) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Map<String, Object> result = new HashMap<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "CALL " + procedureName + "(?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, reportDate);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                int columnCount = rs.getMetaData().getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rs.getMetaData().getColumnName(i);
                    result.put(columnName, rs.getObject(i));
                }
            }
        } finally {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        }
        
        return result;
    }
    
    // 更新报表数据
    public int updateReportData(String reportCode, Map<String, Object> data) throws SQLException {
        String tableName = getTableNameByCode(reportCode);
        if (tableName == null) {
            throw new IllegalArgumentException("无效的报表代码: " + reportCode);
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rowsAffected = 0;
        
        try {
            conn = DBUtil.getConnection();
            // 构建更新SQL
            StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
            List<Object> params = new ArrayList<>();
            
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                if (!"id".equals(entry.getKey()) && !"report_date".equals(entry.getKey())) {
                    sql.append(entry.getKey()).append(" = ?, ");
                    params.add(entry.getValue());
                }
            }
            
            // 移除最后一个逗号和空格
            if (sql.toString().endsWith(", ")) {
                sql.setLength(sql.length() - 2);
            }
            
            sql.append(" WHERE id = ? AND report_date = ?");
            params.add(data.get("id"));
            params.add(data.get("report_date"));
            
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            
            rowsAffected = pstmt.executeUpdate();
        } finally {
            if (pstmt != null) pstmt.close();
            DBUtil.closeConnection(conn);
        }
        
        return rowsAffected;
    }
    
    // 根据报表代码获取对应的表名
    private String getTableNameByCode(String reportCode) {
        Map<String, String> codeToTable = new HashMap<>();
        codeToTable.put("BALANCE_SHEET", "balance_sheet");
        codeToTable.put("LOAN_DETAILS", "loan_details");
        codeToTable.put("DEPOSIT_DETAILS", "deposit_details");
        codeToTable.put("INTERBANK_BUSINESS", "interbank_business");
        codeToTable.put("RISK_EXPOSURE", "risk_exposure");
        codeToTable.put("CAPITAL_ADQUACY", "capital_adequacy");
        
        return codeToTable.get(reportCode);
    }
    
    // 获取所有报表类型
    public List<Map<String, Object>> getAllReportTypes() throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> result = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT report_id, report_name, report_code, description FROM report_type");
            
            while (rs.next()) {
                Map<String, Object> reportType = new HashMap<>();
                reportType.put("reportId", rs.getInt("report_id"));
                reportType.put("reportName", rs.getString("report_name"));
                reportType.put("reportCode", rs.getString("report_code"));
                reportType.put("description", rs.getString("description"));
                result.add(reportType);
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            DBUtil.closeConnection(conn);
        }
        
        return result;
    }
}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188

package com.yutao.report.servlet;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.nio.charset.StandardCharsets;
import java.sql.Date;
import java.util.List;
import java.util.Map;

import com.yutao.report.dao.ReportDAO;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

@WebServlet(name = "ReportServlet", urlPatterns = { "/report/*" })
public class ReportServlet extends HttpServlet {
    private ReportDAO dao = new ReportDAO();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        handleRequest(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        handleRequest(req, resp);
    }

    private void handleRequest(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        String path = req.getPathInfo();
        if (path == null)
            path = "/";
        resp.setContentType("application/json;charset=UTF-8");
        PrintWriter out = resp.getWriter();

        try {
            switch (path.toLowerCase()) {
                case "/template":
                    handleTemplate(req, resp, out);
                    break;
                case "/query":
                    handleQuery(req, resp, out);
                    break;
                case "/save":
                    handleSave(req, resp, out);
                    break;
                case "/submit":
                    handleSubmit(req, resp, out);
                    break;
                default:
                    resp.setStatus(404);
                    out.write("{\"error\":\"not found\"}");
            }
        } catch (Exception e) {
            resp.setStatus(500);
            e.printStackTrace();
            out.write("{\"error\":\"" + escapeJson(e.getMessage()) + "\"}");
        }
    }

    private void handleTemplate(HttpServletRequest req, HttpServletResponse resp, PrintWriter out) throws Exception {
        String code = req.getParameter("code");
        if (code == null) {
            resp.setStatus(400);
            out.write("{\"error\":\"missing code\"}");
            return;
        }

        String xml = readTemplate(code);
        out.write("{\"template\":\"" + escapeJson(xml) + "\"}");
    }

    private void handleQuery(HttpServletRequest req, HttpServletResponse resp, PrintWriter out) throws Exception {
        String code = req.getParameter("code");
        int branch = Integer.parseInt(req.getParameter("branch"));
        Date period = Date.valueOf(req.getParameter("period"));

        // <<< 在这里添加日志，打印前端传来的参数
        System.out.println("ReportServlet.handleQuery -> code=" + code + ", branch=" + branch + ", period=" + period);

        String xml = readTemplate(code);
        String proc = parseProcedureFromXml(xml);
        if (proc != null && !proc.isEmpty()) {
            List<Map<String, Object>> result = dao.callProcedure(proc, period, branch);
            out.write(mapListToJson(result));
        } else {
            Map<String, String> store = dao.loadReportStore(code, branch, period);
            out.write(mapToJson(store));
        }
    }

    private void handleSave(HttpServletRequest req, HttpServletResponse resp, PrintWriter out) throws Exception {
        String code = req.getParameter("code");
        int branch = Integer.parseInt(req.getParameter("branch"));
        Date period = Date.valueOf(req.getParameter("period"));
        String key = req.getParameter("key");
        String value = req.getParameter("value");

        dao.saveReportData(code, branch, period, key, value);
        out.write("{\"ok\":true}");
    }

    private void handleSubmit(HttpServletRequest req, HttpServletResponse resp, PrintWriter out) throws Exception {
        String code = req.getParameter("code");
        int branch = Integer.parseInt(req.getParameter("branch"));
        Date period = Date.valueOf(req.getParameter("period"));
        String submitter = req.getParameter("submitter");

        dao.submitReport(code, branch, period, submitter);
        out.write("{\"ok\":true}");
    }

    // ---------------- 辅助方法 ----------------
    private String readTemplate(String code) throws Exception {
        // 使用 ServletContext 获取绝对路径
        String basePath = getServletContext().getRealPath("/WEB-INF/templates/");
        if (basePath == null) {
            throw new FileNotFoundException("无法找到模板目录：/WEB-INF/templates/");
        }

        String fileName1 = "report_" + code.toLowerCase() + ".xml";
        String fileName2 = code.toLowerCase() + ".xml";
        File f1 = new File(basePath, fileName1);
        File f2 = new File(basePath, fileName2);

        File target = f1.exists() ? f1 : (f2.exists() ? f2 : null);
        if (target == null)
            throw new FileNotFoundException("template not found for code: " + code);

        byte[] bytes = java.nio.file.Files.readAllBytes(target.toPath());
        return new String(bytes, StandardCharsets.UTF_8);
    }

    private String parseProcedureFromXml(String xml) {
        try {
            int i = xml.indexOf("<procedure>");
            int j = xml.indexOf("</procedure>");
            if (i >= 0 && j > i)
                return xml.substring(i + "<procedure>".length(), j).trim();
        } catch (Exception ignored) {
        }
        return null;
    }

    private String escapeJson(String s) {
        if (s == null)
            return "";
        return s.replace("\\", "\\\\").replace("\"", "\\\"").replace("\n", "\\n").replace("\r", "\\r");
    }

    private String mapListToJson(List<Map<String, Object>> list) {
        StringBuilder sb = new StringBuilder("[");
        boolean first = true;
        for (Map<String, Object> row : list) {
            if (!first)
                sb.append(",");
            first = false;
            sb.append(mapToJson(row));
        }
        sb.append("]");
        return sb.toString();
    }

    private String mapToJson(Map<?, ?> m) {
        StringBuilder sb = new StringBuilder("{");
        boolean first = true;
        for (Object k : m.keySet()) {
            if (!first)
                sb.append(",");
            first = false;
            Object v = m.get(k);
            sb.append('"').append(k.toString()).append('"').append(":");
            if (v == null)
                sb.append("null");
            else if (v instanceof Number)
                sb.append(v.toString());
            else
                sb.append('"').append(escapeJson(v.toString())).append('"');
        }
        sb.append("}");
        return sb.toString();
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

package com.yutao.report.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
    private static String url;
    private static String user;
    private static String pass;

    static {
        try {
            System.out.println("[DBUtil] 开始加载数据库配置...");
            InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
            if (in == null) {
                System.err.println("[DBUtil] db.properties 未找到！请确认文件是否在 classpath 下（如 src/main/resources/）");
                throw new RuntimeException("db.properties not found in classpath");
            } else {
                System.out.println("[DBUtil] 成功找到 db.properties");
            }

            Properties p = new Properties();
            p.load(in);
            System.out.println("[DBUtil] 加载属性完成：");
            System.out.println("  jdbc.url = " + p.getProperty("jdbc.url"));
            System.out.println("  jdbc.user = " + p.getProperty("jdbc.user"));
            System.out.println("  jdbc.driver = " + p.getProperty("jdbc.driver"));

            url = p.getProperty("jdbc.url");
            user = p.getProperty("jdbc.user");
            pass = p.getProperty("jdbc.password");

            System.out.println("[DBUtil] 尝试加载数据库驱动：" + p.getProperty("jdbc.driver"));
            Class.forName(p.getProperty("jdbc.driver"));
            System.out.println("[DBUtil] 数据库驱动加载成功！");

        } catch (Exception e) {
            System.err.println("[DBUtil] 数据库配置加载失败！");
            e.printStackTrace();
            throw new RuntimeException("数据库配置加载失败", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pass);
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

1
2
3
4

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/bank_reg_report?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8&useUnicode=true
jdbc.user=root
jdbc.password=root

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131