Challenge_Contest/routes/router.js

var express = require('express');
var router = express.Router();
var mysql = require('../model/mysql');
const _mysqluser = ["172.16.12.5", "registration"]; //用戶數據庫信息

// const _mysqluser = ["183.36.25.93", "registration"]; //本地测试用戶數據庫信息
const crypto = require('crypto');
const XLSX = require('xlsx');

router.route('/user').post(async function (req, res) {
    const reqData = req.body;
    const uuid = crypto.randomUUID();
    let ifadd = true;
    let userData = JSON.parse(reqData.user);
    for (const item of userData) {
        const userList = [_mysqluser[0], _mysqluser[1], "insert_registration_users", item.name, item.gender, item.ethnicity, item.birthdate, item.email, item.id_number, item.group, item.grade, item.school, item.schoolRegion, item.phone, uuid, Number(reqData.type), reqData.area]
        await new Promise((resolve, reject) => {
            mysql.usselect(userList, function (data) {
                // 可以根据 data 判断是否插入成功
                console.log("insert_registration_users", data);
                if (data[0] && data[0][0]) {
                    resolve(data);
                } else {
                    ifadd = false;
                    res.send({ status: 'error', message: '选手报名失败' });
                    reject(data);
                }
            });
        });

    }

    if (ifadd) {
        const signUpData = [_mysqluser[0], _mysqluser[1], "insert_registrations", reqData.uploadUrl, "", Number(reqData.type), reqData.idCard, reqData.area]
        console.log(signUpData);
        mysql.usselect(signUpData, function (data) {
            console.log("insert_registrations", data);
            if (data[0] && data[0][0]) {
                res.send({ status: 'ok', message: '选手报名成功' });
            } else {
                res.send({ status: 'error', message: '选手报名失败' });
            }
        });
    }
});

router.route('/update').post(async function (req, res) {
    const { url, id_number, type } = req.body;
    const queryList = [_mysqluser[0], _mysqluser[1], "update_registration", url, id_number, Number(type)];
    mysql.usselect(queryList, function (data) {
        console.log(data);
        if (data[0] && data[0][0]) {
            res.send({ status: 'ok', message: '报名信息更新成功' });
        } else {
            res.send({ status: 'error', message: '未找到报名信息' });
        }
    });
});


router.route('/exportData').get(async function (req, res) {
    const { start, end } = req.query;
    const queryList = [_mysqluser[0], _mysqluser[1], "sp_export_registration_data", start, end];
    let dataList = [];
    await mysql.usselect(queryList, function (data) {
        if (data[0] && data[0][0]) {
            dataList = data[0];
            dataList = dataList.map(item => {
                let user_group = ""
                if (item.type == 1) {
                    user_group = item.user_group == 1 ? "小学低龄" : item.user_group == 2 ? "小学高龄" : item.user_group == 3 ? "中学" : "高校"
                } else {
                    user_group = item.user_group == 1 ? "小学组（4-6年级）" : item.user_group == 2 ? "初中组（7-9年级）" : item.user_group == 3 ? "高中组（10-12年级，含中职）" : "高校组（含高职）"
                }
                return {
                    小组id: item.user_groupid,
                    小组人数: item.id_card.split(",").length,
                    指导老师: item.upload_url,
                    比赛类型: item.type == 1 ? "桌游挑战赛" : "智能体应用",
                    姓名: item.user_name,
                    邮箱: item.user_email,
                    身份证号: item.user_id_number,
                    组别: user_group,
                    年级: item.user_grade,
                    学校: item.user_school,
                    学校地区: item.user_school_region,
                    电话: item.user_phone,
                    上传文件: item.registration_form_url.split(",")[0],
                    上传视频: item.registration_form_url.split(",")[1],
                    报名时间: new Date(item.created_at).toLocaleString(),
                    区域: item.user_area
                }
            });
            const workbook = XLSX.utils.book_new();
            const worksheet = XLSX.utils.json_to_sheet(dataList);
            XLSX.utils.book_append_sheet(workbook, worksheet, '报名数据');
            const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });

            res.setHeader('Content-Disposition', 'attachment; filename="' + start + ':' + end + '.xlsx"');
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.send(buffer);
            return;
        }
    });

});


module.exports = router;