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 userData = JSON.parse(reqData.user);
    await mysql.usselect([_mysqluser[0], _mysqluser[1], "select_registration_user", reqData.idCard], async function (r) {
        console.log(r[0] && r[0], r[0].some(item => item['type'] == Number(reqData.type)));
        if (r[0] && r[0].some(item => item['type'] == Number(reqData.type))) {
            res.send({ status: 'error', message: '选手报名失败，选手身份证号已存在' });
        } else {
            // const insertUserPromises = userData.map(item => {
            //     return new Promise((resolve, reject) => {
            //         const userList = [];
            //         userList.unshift(_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);
            //         mysql.usselect(userList, function (data) {
            //             // 可以根据 data 判断是否插入成功
            //             console.log("insert_registration_users", data);
            //             if (data != 0) {
            //                 resolve(data);
            //             } else {
            //                 reject(data);
            //             }
            //         });
            //     });
            // });
            // signUpData.unshift(_mysqluser[0], _mysqluser[1], "insert_registrations", reqData.uploadUrl, "", reqData.type, reqData.idCard);
            // Promise.all(insertUserPromises).then(() => {
            //     // 所有用户插入完成后再插入报名数据
            //     mysql.usselect(signUpData, function (data) {
            //         if (data != 0) {
            //             res.send({ status: 'ok', message: '选手报名成功' });
            //         } else {
            //             res.send({ status: 'error', message: '选手报名失败' });
            //         }
            //     });
            // }).catch(error => {
            //     console.error("error", error);
            //     res.send({ status: 'error', message: '选手报名失败，选手身份证号已存在' });
            // });


            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,reqData.type,reqData.area]
                await new Promise((resolve, reject) => {
                    mysql.usselect(userList, function (data) {
                        // 可以根据 data 判断是否插入成功
                        console.log("insert_registration_users", data);
                        if (data != 0) {
                            resolve(data);
                        } else {
                            res.send({ status: 'error', message: '选手报名失败' });
                            reject(data);
                        }
                    });
                });

            }

            const signUpData = [_mysqluser[0], _mysqluser[1], "insert_registrations", reqData.uploadUrl, "", reqData.type, reqData.idCard]
            mysql.usselect(signUpData, function (data) {
                if (data != 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,type];
    mysql.usselect(queryList, function (data) {
        console.log(data);
        if (data == 1) {
            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()
                }
            });
            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;