453 lines
19 KiB
JavaScript
453 lines
19 KiB
JavaScript
|
||
const express = require('express');
|
||
const router = express.Router();
|
||
const multer = require('multer');
|
||
const { nanoid } = require('nanoid');
|
||
const path = require('path');
|
||
const fs = require('fs');
|
||
|
||
// Import separated modules
|
||
const mediaModel = require('../utils/mediaModel');
|
||
const jobService = require('../utils/job');
|
||
const { pool } = require('../config/db');
|
||
|
||
// --- Multer Config ---
|
||
const storage = multer.diskStorage({
|
||
destination: (req, file, cb) => {
|
||
const jobId = req.jobId;
|
||
// 使用项目根目录下的 results 文件夹，而不是 routes/results
|
||
const dir = path.join(__dirname, '..', 'results', jobId, 'uploads');
|
||
try {
|
||
fs.mkdirSync(dir, { recursive: true });
|
||
console.log(`[Upload] Created directory: ${dir}`);
|
||
console.log(`[Upload] Job ID: ${jobId}, File: ${file.originalname}`);
|
||
} catch (error) {
|
||
console.error(`[Upload] Failed to create directory ${dir}:`, error);
|
||
return cb(error);
|
||
}
|
||
cb(null, dir);
|
||
},
|
||
filename: (req, file, cb) => {
|
||
console.log(`[Upload] Saving file: ${file.originalname}`);
|
||
cb(null, file.originalname);
|
||
}
|
||
});
|
||
const upload = multer({ storage }).array('files', 30);
|
||
|
||
// --- Task API Routes ---
|
||
|
||
router.post('/upload', (req, res, next) => {
|
||
req.jobId = nanoid(12);
|
||
console.log(`[Upload] New upload request, Job ID: ${req.jobId}`);
|
||
next();
|
||
}, upload, async (req, res) => {
|
||
try {
|
||
console.log(`[Upload] Files received: ${req.files ? req.files.length : 0}`);
|
||
console.log(`[Upload] Analysis type: ${req.body?.analysis_type || 'nutrition'}`);
|
||
console.log(`[Upload] Email: ${req.body?.email || 'none'}`);
|
||
|
||
if (!req.files || req.files.length === 0) {
|
||
console.error('[Upload] No files received');
|
||
return res.status(400).json({ success: false, error: 'No files' });
|
||
}
|
||
|
||
const fileData = req.files.map(f => {
|
||
console.log(`[Upload] File saved: ${f.path}, Size: ${f.size} bytes`);
|
||
return {
|
||
filePath: f.path,
|
||
fileName: f.originalname,
|
||
fileSize: f.size,
|
||
mimeType: f.mimetype
|
||
};
|
||
});
|
||
|
||
console.log(`[Upload] Creating job with ID: ${req.jobId}`);
|
||
const job = jobService.createJob(req.jobId, req.body?.analysis_type || 'nutrition', req.body?.email, fileData);
|
||
|
||
console.log(`[Upload] Job created successfully: ${job.id}`);
|
||
res.json({ success: true, analysis_id: job.id, message: 'Job started' });
|
||
} catch (e) {
|
||
console.error('[Upload] Error:', e);
|
||
res.status(500).json({ success: false, error: e.message });
|
||
}
|
||
});
|
||
|
||
router.get('/status/:id', (req, res) => {
|
||
const job = jobService.getJob(req.params.id);
|
||
if (!job) return res.status(404).json({ success: false, error: 'Job not found' });
|
||
|
||
res.json({
|
||
success: true,
|
||
status: job.status,
|
||
progress: job.progress,
|
||
eta_seconds: job.eta_seconds,
|
||
result: job.status === 'completed' ? job.result : undefined,
|
||
error: job.status === 'failed' ? job.error : undefined
|
||
});
|
||
});
|
||
|
||
router.post('/stop/:id', async (req, res) => {
|
||
try {
|
||
await jobService.stopJob(req.params.id);
|
||
res.json({ success: true, message: 'Job stopped' });
|
||
} catch (e) {
|
||
res.status(500).json({ success: false, error: e.message });
|
||
}
|
||
});
|
||
|
||
router.get('/download/:id', (req, res) => {
|
||
const job = jobService.getJob(req.params.id);
|
||
if (!job || job.status !== 'completed' || !job.result) return res.status(400).json({ error: 'Not ready' });
|
||
res.download(job.result.result_file, `analysis_${req.params.id}.csv`);
|
||
});
|
||
|
||
router.post('/send-results-email', async (req, res) => {
|
||
try {
|
||
await jobService.resendEmail(req.body.analysis_id, req.body.email);
|
||
res.json({ success: true });
|
||
} catch (e) {
|
||
res.status(500).json({ success: false, error: e.message });
|
||
}
|
||
});
|
||
|
||
// --- Data & Browse API Routes ---
|
||
|
||
router.get('/browse', async (req, res) => {
|
||
try {
|
||
const { page = 1, pageSize = 20, chartData = false } = req.query;
|
||
|
||
const [data, total] = await Promise.all([
|
||
mediaModel.getBrowseData(req.query, page, pageSize),
|
||
mediaModel.getTotalCount(req.query)
|
||
]);
|
||
// console.log(data)
|
||
// console.log(total)
|
||
let sankeyData = null;
|
||
if (chartData === 'true') {
|
||
sankeyData = await mediaModel.getSankeyData(req.query);
|
||
}
|
||
// console.log(sankeyData)
|
||
res.json({
|
||
success: true,
|
||
data,
|
||
pagination: {
|
||
currentPage: parseInt(page),
|
||
pageSize: parseInt(pageSize),
|
||
total,
|
||
totalPages: Math.ceil(total / pageSize)
|
||
},
|
||
sankeyData
|
||
});
|
||
} catch (e) {
|
||
res.status(500).json({ success: false, error: e.message });
|
||
}
|
||
});
|
||
|
||
router.get('/microbial-detail', async (req, res) => {
|
||
try {
|
||
const data = await mediaModel.getMicrobialDetail(req.query.name, req.query.level);
|
||
if (!data) return res.json({ success: false, message: 'Not found' });
|
||
res.json({ success: true, data });
|
||
} catch (e) {
|
||
res.status(500).json({ success: false, error: e.message });
|
||
}
|
||
});
|
||
|
||
router.get('/taxonomy-stats', async (req, res) => {
|
||
try {
|
||
const stats = await mediaModel.getTaxonomyStats(req.query.level || 'phylum', req.query.cultured_type || 'cultured');
|
||
res.json({ success: true, ...stats });
|
||
} catch (e) { res.status(500).json({ error: e.message }); }
|
||
});
|
||
|
||
router.get('/physchem-stats', async (req, res) => {
|
||
try {
|
||
const { type = 'o2', cultured_type = 'cultured' } = req.query;
|
||
|
||
// 验证 type 参数
|
||
const validTypes = ['o2', 'ph', 'temperature'];
|
||
if (!validTypes.includes(type)) {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: 'Invalid physical and chemical properties type',
|
||
validTypes: validTypes
|
||
});
|
||
}
|
||
|
||
const data = await mediaModel.getPhyschemStats(type, cultured_type);
|
||
res.json({ success: true, data, type: type });
|
||
} catch (e) {
|
||
console.error('Error in /physchem-stats:', e);
|
||
res.status(500).json({
|
||
success: false,
|
||
error: e.message,
|
||
message: 'Failed to get physical and chemical properties statistics'
|
||
});
|
||
}
|
||
});
|
||
|
||
router.get('/nutrition-stats', async (req, res) => {
|
||
try {
|
||
const stats = await mediaModel.getNutritionStats(req.query.cultured_type || 'cultured');
|
||
res.json({ success: true, ...stats });
|
||
} catch (e) { res.status(500).json({ error: e.message }); }
|
||
});
|
||
|
||
router.get('/sunburst-stats', async (req, res) => {
|
||
try {
|
||
const data = await mediaModel.getSunburstData(req.query);
|
||
res.json({ success: true, data, total: data.length });
|
||
} catch (e) { res.status(500).json({ error: e.message }); }
|
||
});
|
||
|
||
router.get('/health', async (req, res) => {
|
||
try {
|
||
await mediaModel.checkHealth();
|
||
res.json({ success: true });
|
||
} catch (e) { res.status(500).json({ error: e.message }); }
|
||
});
|
||
|
||
// 下载数据 API
|
||
router.get('/download-data/:type', async (req, res) => {
|
||
try {
|
||
const { type } = req.params;
|
||
const { cultured_type = 'cultured' } = req.query;
|
||
|
||
// 定义允许的下载类型
|
||
const allowedTypes = ['all_data', 'ph', 'temperature', 'oxygen', 'culture_medium', 'max_growth_rate'];
|
||
if (!allowedTypes.includes(type)) {
|
||
return res.status(400).json({
|
||
success: false,
|
||
message: 'Invalid download type',
|
||
allowedTypes: allowedTypes
|
||
});
|
||
}
|
||
|
||
// 根据类型定义需要查询的字段
|
||
let selectFields = '';
|
||
let headers = [];
|
||
|
||
switch (type) {
|
||
case 'all_data':
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species,
|
||
m.taxonomy,
|
||
m.pH as pH,
|
||
m.temperature as temperature,
|
||
m.o2 as o2,
|
||
m.nutrition,
|
||
m.gram_stain,
|
||
m.synonym,
|
||
m.media_name,
|
||
m.media_source,
|
||
m.mediadive_id,
|
||
TRIM(CASE
|
||
WHEN m.predict_pH IS NOT NULL AND m.predict_pH != '' THEN
|
||
SUBSTRING(m.predict_pH, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_pH)) > 0 THEN LENGTH(m.predict_pH) - LOCATE(' ', REVERSE(m.predict_pH)) ELSE LENGTH(m.predict_pH) END)
|
||
ELSE NULL
|
||
END) as ppH,
|
||
TRIM(CASE
|
||
WHEN m.predict_temperature IS NOT NULL AND m.predict_temperature != '' THEN
|
||
SUBSTRING(m.predict_temperature, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_temperature)) > 0 THEN LENGTH(m.predict_temperature) - LOCATE(' ', REVERSE(m.predict_temperature)) ELSE LENGTH(m.predict_temperature) END)
|
||
ELSE NULL
|
||
END) as ptemperature,
|
||
TRIM(CASE
|
||
WHEN m.predict_o2 IS NOT NULL AND m.predict_o2 != '' THEN
|
||
SUBSTRING(m.predict_o2, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_o2)) > 0 THEN LENGTH(m.predict_o2) - LOCATE(' ', REVERSE(m.predict_o2)) ELSE LENGTH(m.predict_o2) END)
|
||
ELSE NULL
|
||
END) as po2
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species', 'taxonomy', 'pH', 'temperature', 'o2', 'nutrition', 'gram_stain', 'synonym', 'media_name', 'media_source', 'mediadive_id', 'predict_pH', 'predict_temperature', 'predict_o2'];
|
||
break;
|
||
case 'ph':
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species,
|
||
m.pH as pH,
|
||
TRIM(CASE
|
||
WHEN m.predict_pH IS NOT NULL AND m.predict_pH != '' THEN
|
||
SUBSTRING(m.predict_pH, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_pH)) > 0 THEN LENGTH(m.predict_pH) - LOCATE(' ', REVERSE(m.predict_pH)) ELSE LENGTH(m.predict_pH) END)
|
||
ELSE NULL
|
||
END) as ppH
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species', 'pH', 'predict_pH'];
|
||
break;
|
||
case 'temperature':
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species,
|
||
m.temperature as temperature,
|
||
TRIM(CASE
|
||
WHEN m.predict_temperature IS NOT NULL AND m.predict_temperature != '' THEN
|
||
SUBSTRING(m.predict_temperature, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_temperature)) > 0 THEN LENGTH(m.predict_temperature) - LOCATE(' ', REVERSE(m.predict_temperature)) ELSE LENGTH(m.predict_temperature) END)
|
||
ELSE NULL
|
||
END) as ptemperature
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species', 'temperature', 'predict_temperature'];
|
||
break;
|
||
case 'oxygen':
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species,
|
||
m.o2 as o2,
|
||
TRIM(CASE
|
||
WHEN m.predict_o2 IS NOT NULL AND m.predict_o2 != '' THEN
|
||
SUBSTRING(m.predict_o2, 1, CASE WHEN LOCATE(' ', REVERSE(m.predict_o2)) > 0 THEN LENGTH(m.predict_o2) - LOCATE(' ', REVERSE(m.predict_o2)) ELSE LENGTH(m.predict_o2) END)
|
||
ELSE NULL
|
||
END) as po2
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species', 'o2', 'predict_o2'];
|
||
break;
|
||
case 'culture_medium':
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species,
|
||
m.nutrition,
|
||
m.media_name,
|
||
m.media_source,
|
||
m.mediadive_id
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species', 'nutrition', 'media_name', 'media_source', 'mediadive_id'];
|
||
break;
|
||
case 'max_growth_rate':
|
||
// 注意：如果数据库中没有 max_growth_rate 字段，这里需要根据实际情况调整
|
||
selectFields = `
|
||
m.species_dive_id,
|
||
m.microbial_name,
|
||
m.genome,
|
||
m.domain,
|
||
m.phylum,
|
||
m.class,
|
||
m.\`order\`,
|
||
m.family,
|
||
m.genus,
|
||
m.species
|
||
`;
|
||
headers = ['species_dive_id', 'microbial_name', 'genome', 'domain', 'phylum', 'class', 'order', 'family', 'genus', 'species'];
|
||
break;
|
||
}
|
||
|
||
// 为每种类型定义 GROUP BY 字段
|
||
let groupByFields = '';
|
||
switch (type) {
|
||
case 'all_data':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species, m.taxonomy, m.pH, m.temperature, m.o2, m.nutrition, m.gram_stain, m.synonym, m.media_name, m.media_source, m.mediadive_id, m.predict_pH, m.predict_temperature, m.predict_o2`;
|
||
break;
|
||
case 'ph':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species, m.pH, m.predict_pH`;
|
||
break;
|
||
case 'temperature':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species, m.temperature, m.predict_temperature`;
|
||
break;
|
||
case 'oxygen':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species, m.o2, m.predict_o2`;
|
||
break;
|
||
case 'culture_medium':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species, m.nutrition, m.media_name, m.media_source, m.mediadive_id`;
|
||
break;
|
||
case 'max_growth_rate':
|
||
groupByFields = `m.species_dive_id, m.microbial_name, m.genome, m.domain, m.phylum, m.class, m.\`order\`, m.family, m.genus, m.species`;
|
||
break;
|
||
}
|
||
|
||
// 构建查询
|
||
const query = `
|
||
SELECT ${selectFields}
|
||
FROM cultured_data m
|
||
WHERE m.cultured_type = ?
|
||
GROUP BY ${groupByFields}
|
||
ORDER BY m.species_dive_id
|
||
`;
|
||
|
||
console.log('Execute download query for type:', type);
|
||
const [rows] = await pool.execute(query, [cultured_type]);
|
||
console.log('Download query result row count:', rows.length);
|
||
|
||
// 将数据转换为 CSV 格式
|
||
const escapeCSVValue = (value) => {
|
||
if (value === null || value === undefined) return '';
|
||
const str = String(value);
|
||
if (str.includes(',') || str.includes('"') || str.includes('\n')) {
|
||
return `"${str.replace(/"/g, '""')}"`;
|
||
}
|
||
return str;
|
||
};
|
||
|
||
const csvLines = [
|
||
headers.join(','),
|
||
...rows.map(row => {
|
||
return headers.map(header => {
|
||
// 处理字段名映射（如 pH -> pH, ppH -> predict_pH）
|
||
let value = row[header];
|
||
if (value === undefined && header === 'predict_pH') {
|
||
value = row['ppH'];
|
||
} else if (value === undefined && header === 'predict_temperature') {
|
||
value = row['ptemperature'];
|
||
} else if (value === undefined && header === 'predict_o2') {
|
||
value = row['po2'];
|
||
}
|
||
return escapeCSVValue(value);
|
||
}).join(',');
|
||
})
|
||
];
|
||
|
||
const csvContent = csvLines.join('\n');
|
||
|
||
// 设置响应头
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
|
||
res.setHeader('Content-Disposition', `attachment; filename="${type}.csv"`);
|
||
res.send(csvContent);
|
||
|
||
} catch (error) {
|
||
console.error('Error downloading data:', error);
|
||
res.status(500).json({
|
||
success: false,
|
||
message: 'Failed to download data',
|
||
error: error.message
|
||
});
|
||
}
|
||
});
|
||
|
||
module.exports = router;
|