1488 lines
62 KiB
JavaScript
1488 lines
62 KiB
JavaScript
const express = require('express');
|
||
const router = express.Router();
|
||
const multer = require('multer');
|
||
const path = require('path');
|
||
const fs = require('fs');
|
||
const archiver = require('archiver');
|
||
const contentDisposition = require('content-disposition');
|
||
const FileType = require('file-type');
|
||
const db = require('../db');
|
||
const logger = require('../utils/logger');
|
||
const { verifyToken, isAdmin, isSuperAdmin } = require('../middleware/auth');
|
||
|
||
const getLeaderDepartmentSet = async () => {
|
||
try {
|
||
const res = await db.query('SELECT name FROM dict_leader_departments');
|
||
return new Set((res.rows || []).map((row) => row.name).filter(Boolean));
|
||
} catch (err) {
|
||
logger.warn(`Failed to load leader departments, fallback to no-filter: ${err.message}`);
|
||
return new Set();
|
||
}
|
||
};
|
||
|
||
const toOrderedDepartmentList = (assignedDepartments) => {
|
||
const source = Array.isArray(assignedDepartments) ? assignedDepartments : [];
|
||
const seen = new Set();
|
||
const result = [];
|
||
for (const item of source) {
|
||
const dept = String(item || '').trim();
|
||
if (!dept || seen.has(dept)) continue;
|
||
seen.add(dept);
|
||
result.push(dept);
|
||
}
|
||
return result;
|
||
};
|
||
|
||
const filterAssignedDepartments = (assignedDepartments, leaderDeptSet) => {
|
||
const normalized = toOrderedDepartmentList(assignedDepartments);
|
||
if (normalized.length === 0) return [];
|
||
if (!leaderDeptSet || leaderDeptSet.size === 0) {
|
||
return [normalized[0]];
|
||
}
|
||
|
||
for (const dept of normalized) {
|
||
if (!leaderDeptSet.has(dept)) {
|
||
return [dept];
|
||
}
|
||
}
|
||
|
||
// 全部都是院领导部门时，回退首个部门，保证单归属
|
||
return [normalized[0]];
|
||
};
|
||
|
||
const normalizeAchievementDepartments = (row, leaderDeptSet) => {
|
||
if (!row || !Object.prototype.hasOwnProperty.call(row, 'assigned_departments')) {
|
||
return row;
|
||
}
|
||
return {
|
||
...row,
|
||
assigned_departments: filterAssignedDepartments(row.assigned_departments, leaderDeptSet)
|
||
};
|
||
};
|
||
|
||
const resolveAssignedDepartmentsFromContributors = async (client, contributors = []) => {
|
||
const list = Array.isArray(contributors) ? contributors : [];
|
||
|
||
const orderedPhones = [];
|
||
const seenPhone = new Set();
|
||
const pushPhone = (phone) => {
|
||
const value = String(phone || '').trim();
|
||
if (!value || seenPhone.has(value)) return;
|
||
seenPhone.add(value);
|
||
orderedPhones.push(value);
|
||
};
|
||
|
||
// 优先主要完成人，再按排序后的其余完成人顺序回退
|
||
list.forEach((c) => {
|
||
if (c?.isMain) pushPhone(c?.phone);
|
||
});
|
||
list.forEach((c) => {
|
||
if (!c?.isMain) pushPhone(c?.phone);
|
||
});
|
||
|
||
if (orderedPhones.length === 0) return [];
|
||
|
||
const deptRes = await client.query(
|
||
'SELECT username, department FROM users WHERE username = ANY($1) AND department IS NOT NULL AND department <> \'\'',
|
||
[orderedPhones]
|
||
);
|
||
|
||
const phoneDeptMap = new Map();
|
||
deptRes.rows.forEach((row) => {
|
||
const phone = String(row.username || '').trim();
|
||
const dept = String(row.department || '').trim();
|
||
if (phone && dept && !phoneDeptMap.has(phone)) {
|
||
phoneDeptMap.set(phone, dept);
|
||
}
|
||
});
|
||
|
||
let leaderDeptSet = new Set();
|
||
try {
|
||
const leaderRes = await client.query('SELECT name FROM dict_leader_departments');
|
||
leaderDeptSet = new Set((leaderRes.rows || []).map((row) => row.name).filter(Boolean));
|
||
} catch (err) {
|
||
logger.warn(`Failed to load leader departments while resolving contributor departments: ${err.message}`);
|
||
}
|
||
|
||
let leaderFallback = null;
|
||
for (const phone of orderedPhones) {
|
||
const dept = phoneDeptMap.get(phone);
|
||
if (!dept) continue;
|
||
if (!leaderDeptSet.has(dept)) {
|
||
return [dept];
|
||
}
|
||
if (!leaderFallback) {
|
||
leaderFallback = dept;
|
||
}
|
||
}
|
||
|
||
// 全部都是院领导部门时，回退首个部门，保证“单归属部门”
|
||
return leaderFallback ? [leaderFallback] : [];
|
||
};
|
||
|
||
|
||
const getSearchPaths = () => {
|
||
const paths = [];
|
||
|
||
// 1. 涓荤洰褰?
|
||
const mainDir = process.env.UPLOAD_DIR || 'uploads';
|
||
paths.push(path.isAbsolute(mainDir) ? mainDir : path.join(__dirname, '..', mainDir));
|
||
|
||
// 2. 备用目录
|
||
if (process.env.UPLOAD_FALLBACK_DIRS) {
|
||
const fallbacks = process.env.UPLOAD_FALLBACK_DIRS.split(',').map(p => p.trim()).filter(p => p);
|
||
fallbacks.forEach(p => {
|
||
const absPath = path.isAbsolute(p) ? p : path.join(__dirname, '..', p);
|
||
// 閬垮厤閲嶅
|
||
if (!paths.includes(absPath)) {
|
||
paths.push(absPath);
|
||
}
|
||
});
|
||
}
|
||
|
||
// 3. 榛樿 uploads 鐩綍 (鑷姩鍏滃簳)
|
||
|
||
const defaultUploads = path.join(__dirname, '..', 'uploads');
|
||
const isDefaultIncluded = paths.some(p => path.resolve(p) === path.resolve(defaultUploads));
|
||
|
||
if (!isDefaultIncluded) {
|
||
paths.push(defaultUploads);
|
||
}
|
||
|
||
return paths;
|
||
};
|
||
|
||
|
||
const findFile = (relativePath) => {
|
||
const searchPaths = getSearchPaths();
|
||
for (const rootDir of searchPaths) {
|
||
|
||
|
||
const fullPath = path.join(rootDir, relativePath);
|
||
if (fs.existsSync(fullPath)) {
|
||
return fullPath;
|
||
}
|
||
}
|
||
return null;
|
||
};
|
||
|
||
|
||
const getUploadRootDir = () => {
|
||
const uploadDir = process.env.UPLOAD_DIR || 'uploads';
|
||
if (path.isAbsolute(uploadDir)) {
|
||
return uploadDir;
|
||
} else {
|
||
return path.join(__dirname, '..', uploadDir);
|
||
}
|
||
};
|
||
|
||
const cleanupUploadedFiles = (files = []) => {
|
||
for (const file of files) {
|
||
if (!file || !file.path) continue;
|
||
try {
|
||
if (fs.existsSync(file.path)) {
|
||
fs.unlinkSync(file.path);
|
||
}
|
||
} catch (e) {
|
||
logger.warn(`Failed to cleanup uploaded file: ${file.path}`);
|
||
}
|
||
}
|
||
};
|
||
|
||
// 閰嶇疆 Multer 鐢ㄤ簬鏂囦欢涓婁紶
|
||
const storage = multer.diskStorage({
|
||
destination: (req, file, cb) => {
|
||
const uploadRoot = getUploadRootDir();
|
||
const date = new Date();
|
||
const year = date.getFullYear();
|
||
const month = String(date.getMonth() + 1).padStart(2, '0');
|
||
const subDir = path.join(String(year), month);
|
||
|
||
const fullPath = path.join(uploadRoot, subDir);
|
||
|
||
if (!fs.existsSync(fullPath)) {
|
||
fs.mkdirSync(fullPath, { recursive: true });
|
||
}
|
||
cb(null, fullPath);
|
||
},
|
||
filename: (req, file, cb) => {
|
||
const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
|
||
cb(null, uniqueSuffix + path.extname(file.originalname));
|
||
}
|
||
});
|
||
|
||
// 文件类型白名单
|
||
const allowedExtensions = [
|
||
'.pdf',
|
||
'.doc', '.docx',
|
||
'.xls', '.xlsx',
|
||
'.ppt', '.pptx',
|
||
'.txt',
|
||
'.jpg', '.jpeg', '.png',
|
||
'.zip', '.rar', '.7z'
|
||
];
|
||
|
||
const MAX_UPLOAD_FILE_SIZE = Number(process.env.UPLOAD_MAX_FILE_SIZE || 50 * 1024 * 1024);
|
||
const MAX_UPLOAD_FILES = Number(process.env.UPLOAD_MAX_FILES || 20);
|
||
|
||
const upload = multer({
|
||
storage: storage,
|
||
limits: {
|
||
fileSize: MAX_UPLOAD_FILE_SIZE,
|
||
files: MAX_UPLOAD_FILES
|
||
},
|
||
fileFilter: (req, file, cb) => {
|
||
const ext = path.extname(file.originalname).toLowerCase();
|
||
if (allowedExtensions.includes(ext)) {
|
||
cb(null, true);
|
||
} else {
|
||
cb(new Error(`Unsupported file type (${ext}).`));
|
||
}
|
||
}
|
||
});
|
||
|
||
|
||
router.post('/', verifyToken, (req, res, next) => {
|
||
upload.any()(req, res, async (err) => {
|
||
if (err) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: err.message });
|
||
}
|
||
|
||
|
||
if (req.files && req.files.length > 0) {
|
||
for (const file of req.files) {
|
||
try {
|
||
const fileType = await FileType.fromFile(file.path);
|
||
|
||
|
||
const ext = path.extname(file.originalname).toLowerCase();
|
||
|
||
// 允许的 MIME 类型前缀
|
||
const allowedMimes = [
|
||
'image/',
|
||
'application/pdf',
|
||
'application/msword',
|
||
'application/vnd.openxmlformats-officedocument',
|
||
'application/vnd.ms-excel',
|
||
'application/vnd.ms-powerpoint',
|
||
'application/zip',
|
||
'application/x-zip-compressed',
|
||
'application/x-rar-compressed',
|
||
'application/x-7z-compressed'
|
||
];
|
||
|
||
|
||
if (fileType && !allowedMimes.some(mime => fileType.mime.startsWith(mime))) {
|
||
// 鍒犻櫎闈炴硶鏂囦欢
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: `File type not allowed: ${file.originalname} (${fileType.mime})` });
|
||
}
|
||
|
||
|
||
if (fileType && (fileType.mime === 'application/x-msdownload' || fileType.mime === 'application/x-executable')) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: `检测到非法可执行文件: ${file.originalname}` });
|
||
}
|
||
|
||
} catch (checkErr) {
|
||
console.error('文件类型检查失败:', checkErr);
|
||
|
||
}
|
||
}
|
||
}
|
||
next();
|
||
});
|
||
}, async (req, res) => {
|
||
if (!req.body || !req.body.type) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: '请求参数错误：缺少成果类型(type)' });
|
||
}
|
||
const { type, details, name, contributors, achievement_date, remarks } = req.body;
|
||
|
||
let parsedDetails = {};
|
||
let parsedContributors = [];
|
||
|
||
try {
|
||
parsedDetails = typeof details === 'string' ? JSON.parse(details) : (details || {});
|
||
} catch (e) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: 'details 字段格式错误（应为 JSON）' });
|
||
}
|
||
|
||
try {
|
||
parsedContributors = typeof contributors === 'string' ? JSON.parse(contributors) : (contributors || []);
|
||
} catch (e) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: 'contributors 字段格式错误（应为 JSON）' });
|
||
}
|
||
|
||
|
||
if (type === 'report') {
|
||
if ((parsedDetails.approver_id === undefined || parsedDetails.approver_id === null || parsedDetails.approver_id === '') && req.body?.approver_id) {
|
||
const approverId = Number(req.body.approver_id);
|
||
parsedDetails.approver_id = Number.isNaN(approverId) ? null : approverId;
|
||
}
|
||
if ((!parsedDetails.approver_name || parsedDetails.approver_name === '') && req.body?.approver_name) {
|
||
parsedDetails.approver_name = req.body.approver_name;
|
||
}
|
||
}
|
||
|
||
const phoneSet = new Set();
|
||
for (const c of parsedContributors) {
|
||
if (c.phone) {
|
||
if (phoneSet.has(c.phone)) {
|
||
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: `Duplicate contributor phone: ${c.phone} (${c.name})` });
|
||
}
|
||
phoneSet.add(c.phone);
|
||
}
|
||
}
|
||
|
||
// 检查重复排名（仅针对支持排名的类型）
|
||
if (!['project', 'transformation'].includes(type)) {
|
||
const rankingSet = new Set();
|
||
for (const c of parsedContributors) {
|
||
if (c.ranking) {
|
||
const r = parseInt(c.ranking);
|
||
if (rankingSet.has(r)) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: `Duplicate ranking: #${r}` });
|
||
}
|
||
rankingSet.add(r);
|
||
}
|
||
}
|
||
}
|
||
|
||
const userId = req.user.id;
|
||
const files = req.files || [];
|
||
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
// --- 鏌ラ噸閫昏緫 ---
|
||
let duplicateCheckQuery = '';
|
||
let duplicateCheckParams = [];
|
||
|
||
if (type === 'patent' && parsedDetails.patent_no) {
|
||
duplicateCheckQuery = 'SELECT a.id, u.real_name, a.created_at FROM achievement_patent d JOIN achievements a ON d.achievement_id = a.id JOIN users u ON a.user_id = u.id WHERE d.patent_no = $1 AND a.status != \'rejected\'';
|
||
duplicateCheckParams = [parsedDetails.patent_no];
|
||
} else if (type === 'software' && parsedDetails.reg_no) {
|
||
duplicateCheckQuery = 'SELECT a.id, u.real_name, a.created_at FROM achievement_software d JOIN achievements a ON d.achievement_id = a.id JOIN users u ON a.user_id = u.id WHERE d.reg_no = $1 AND a.status != \'rejected\'';
|
||
duplicateCheckParams = [parsedDetails.reg_no];
|
||
} else if (type === 'standard' && parsedDetails.standard_no) {
|
||
duplicateCheckQuery = 'SELECT a.id, u.real_name, a.created_at FROM achievement_standard d JOIN achievements a ON d.achievement_id = a.id JOIN users u ON a.user_id = u.id WHERE d.standard_no = $1 AND a.status != \'rejected\'';
|
||
duplicateCheckParams = [parsedDetails.standard_no];
|
||
} else if (type === 'paper' && parsedDetails.doi) {
|
||
duplicateCheckQuery = 'SELECT a.id, u.real_name, a.created_at FROM achievement_paper d JOIN achievements a ON d.achievement_id = a.id JOIN users u ON a.user_id = u.id WHERE d.doi = $1 AND a.status != \'rejected\'';
|
||
duplicateCheckParams = [parsedDetails.doi];
|
||
} else {
|
||
|
||
duplicateCheckQuery = 'SELECT a.id, u.real_name, a.created_at FROM achievements a JOIN users u ON a.user_id = u.id WHERE a.name = $1 AND a.type = $2 AND a.achievement_date = $3 AND a.status != \'rejected\'';
|
||
duplicateCheckParams = [name, type, achievement_date];
|
||
}
|
||
|
||
if (duplicateCheckQuery) {
|
||
const dupRes = await client.query(duplicateCheckQuery, duplicateCheckParams);
|
||
if (dupRes.rows.length > 0) {
|
||
const dup = dupRes.rows[0];
|
||
await client.query('ROLLBACK');
|
||
cleanupUploadedFiles(files);
|
||
return res.status(409).json({
|
||
message: `Duplicate submission detected. Existing submitter: ${dup.real_name}, time: ${new Date(dup.created_at).toLocaleString()}.`
|
||
});
|
||
}
|
||
}
|
||
|
||
|
||
let processedContributors = parsedContributors.map(c => ({
|
||
...c,
|
||
ranking: c.ranking ? parseInt(c.ranking) : null
|
||
}));
|
||
|
||
if (type === 'paper') {
|
||
|
||
const mainRoles = ['第一作者', '共同第一作者', '通讯作者', '共同通讯作者'];
|
||
processedContributors = processedContributors.map(c => ({
|
||
...c,
|
||
isMain: mainRoles.includes(c.role)
|
||
}));
|
||
} else if (['project', 'transformation'].includes(type)) {
|
||
|
||
} else {
|
||
|
||
const rankings = processedContributors.map(c => c.ranking).filter(r => r !== null && !isNaN(r));
|
||
const minRanking = rankings.length > 0 ? Math.min(...rankings) : null;
|
||
|
||
processedContributors = processedContributors.map(c => ({
|
||
...c,
|
||
isMain: minRanking !== null && c.ranking === minRanking
|
||
}));
|
||
}
|
||
|
||
// 鎸夋帓鍚嶆帓搴?
|
||
processedContributors.sort((a, b) => {
|
||
if (a.ranking && b.ranking) return a.ranking - b.ranking;
|
||
if (a.ranking) return -1;
|
||
if (b.ranking) return 1;
|
||
return 0;
|
||
});
|
||
|
||
|
||
const main_contributors_str = processedContributors.filter(c => c.isMain).map(c => c.name).join(' ');
|
||
const all_contributors_str = processedContributors.map(c => c.name).join(' ');
|
||
const contributor_phones_str = processedContributors.map(c => c.phone).join(' ');
|
||
|
||
|
||
const assignedDepartments = await resolveAssignedDepartmentsFromContributors(client, processedContributors);
|
||
|
||
|
||
const baseRes = await client.query(
|
||
'INSERT INTO achievements (user_id, type, name, main_contributors, all_contributors, contributor_phones, achievement_date, remarks, contributors, assigned_departments) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) RETURNING id',
|
||
[userId, type, name, main_contributors_str, all_contributors_str, contributor_phones_str, achievement_date, remarks, JSON.stringify(processedContributors), assignedDepartments]
|
||
);
|
||
const achievementId = baseRes.rows[0].id;
|
||
|
||
|
||
let detailQuery = '';
|
||
let params = [];
|
||
|
||
switch (type) {
|
||
case 'paper':
|
||
detailQuery = 'INSERT INTO achievement_paper (achievement_id, paper_type, first_unit, journal_name, publish_date, doi) VALUES ($1, $2, $3, $4, $5, $6)';
|
||
params = [achievementId, parsedDetails.paper_type, parsedDetails.first_unit, parsedDetails.journal_name, parsedDetails.publish_date, parsedDetails.doi];
|
||
break;
|
||
case 'project':
|
||
detailQuery = 'INSERT INTO achievement_project (achievement_id, project_category, source) VALUES ($1, $2, $3)';
|
||
params = [achievementId, parsedDetails.project_category, parsedDetails.source];
|
||
break;
|
||
case 'award':
|
||
detailQuery = 'INSERT INTO achievement_award (achievement_id, award_type, award_level, award_unit) VALUES ($1, $2, $3, $4)';
|
||
params = [achievementId, parsedDetails.award_type, parsedDetails.award_level, parsedDetails.award_unit];
|
||
break;
|
||
case 'standard':
|
||
detailQuery = 'INSERT INTO achievement_standard (achievement_id, standard_type, standard_no, implement_date) VALUES ($1, $2, $3, $4)';
|
||
params = [achievementId, parsedDetails.standard_type, parsedDetails.standard_no, parsedDetails.implement_date];
|
||
break;
|
||
case 'monograph':
|
||
detailQuery = 'INSERT INTO achievement_monograph (achievement_id, publisher, isbn) VALUES ($1, $2, $3)';
|
||
params = [achievementId, parsedDetails.publisher, parsedDetails.isbn];
|
||
break;
|
||
case 'report':
|
||
detailQuery = 'INSERT INTO achievement_report (achievement_id, report_type, recipient, approver_id, approver_name) VALUES ($1, $2, $3, $4, $5)';
|
||
params = [achievementId, parsedDetails.report_type, parsedDetails.recipient, parsedDetails.approver_id, parsedDetails.approver_name];
|
||
break;
|
||
case 'plan':
|
||
detailQuery = 'INSERT INTO achievement_plan (achievement_id) VALUES ($1)';
|
||
params = [achievementId];
|
||
break;
|
||
case 'patent':
|
||
detailQuery = 'INSERT INTO achievement_patent (achievement_id, patent_no, patent_type, assignee) VALUES ($1, $2, $3, $4)';
|
||
params = [achievementId, parsedDetails.patent_no, parsedDetails.patent_type, parsedDetails.assignee];
|
||
break;
|
||
case 'transformation':
|
||
detailQuery = 'INSERT INTO achievement_transformation (achievement_id, trans_method, trans_amount, project_category) VALUES ($1, $2, $3, $4)';
|
||
params = [achievementId, parsedDetails.trans_method, parsedDetails.trans_amount, parsedDetails.project_category];
|
||
break;
|
||
case 'software':
|
||
detailQuery = 'INSERT INTO achievement_software (achievement_id, reg_no, acquisition_method, scope, owner_unit) VALUES ($1, $2, $3, $4, $5)';
|
||
params = [achievementId, parsedDetails.reg_no, parsedDetails.acquisition_method, parsedDetails.scope, parsedDetails.owner_unit];
|
||
break;
|
||
default:
|
||
throw new Error('Unknown achievement type');
|
||
}
|
||
|
||
await client.query(detailQuery, params);
|
||
|
||
// 插入多个附件
|
||
const uploadRootDir = getUploadRootDir();
|
||
for (const file of files) {
|
||
let originalName = file.originalname;
|
||
if (!/[^\u0000-\u00ff]/.test(originalName)) {
|
||
try {
|
||
const decodedName = Buffer.from(originalName, 'latin1').toString('utf8');
|
||
if (/[\u4e00-\u9fa5]/.test(decodedName)) {
|
||
originalName = decodedName;
|
||
}
|
||
} catch (e) {
|
||
console.error('鏂囦欢鍚嶈浆鐮佸け璐?', e);
|
||
}
|
||
}
|
||
|
||
|
||
let relativePath = path.relative(uploadRootDir, file.path);
|
||
|
||
relativePath = relativePath.split(path.sep).join('/');
|
||
|
||
await client.query(
|
||
'INSERT INTO achievement_attachments (achievement_id, file_name, file_path) VALUES ($1, $2, $3)',
|
||
[achievementId, originalName, relativePath]
|
||
);
|
||
}
|
||
|
||
await client.query('COMMIT');
|
||
|
||
const clientIp = req.headers['x-forwarded-for'] || req.socket.remoteAddress;
|
||
logger.info(`用户 ${req.user.username} 上传了新成果: ${name} (类型: ${type}, ID: ${achievementId}) (IP: ${clientIp})`);
|
||
|
||
res.status(201).json({ message: '上传成功', id: achievementId });
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
cleanupUploadedFiles(files);
|
||
console.error(err);
|
||
res.status(500).json({ message: 'Server error' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
|
||
const buildExportQuery = (params) => {
|
||
const { type, status, keyword, startDate, endDate } = params;
|
||
let query = `
|
||
SELECT
|
||
a.id, a.type, a.name, a.status, a.created_at, a.updated_at,
|
||
u.username as submitter_username, u.real_name as submitter_real_name,
|
||
a.main_contributors, a.all_contributors, a.contributor_phones, a.assigned_departments, a.achievement_date, a.remarks,
|
||
-- Paper
|
||
ap.paper_type, ap.first_unit, ap.journal_name, ap.publish_date as paper_publish_date, ap.doi,
|
||
-- Project
|
||
apr.project_category as project_category, apr.source as project_source,
|
||
-- Award
|
||
aa.award_type, aa.award_level, aa.award_unit,
|
||
-- Standard
|
||
as_std.standard_type, as_std.standard_no, as_std.implement_date as standard_implement_date,
|
||
-- Monograph
|
||
am.publisher, am.isbn,
|
||
-- Report
|
||
ar.report_type, ar.recipient,
|
||
-- Patent
|
||
apat.patent_no, apat.patent_type, apat.assignee,
|
||
-- Transformation
|
||
at.trans_method, at.trans_amount, at.project_category as trans_project_category,
|
||
-- Software
|
||
asw.reg_no, asw.acquisition_method, asw.scope, asw.owner_unit
|
||
FROM achievements a
|
||
JOIN users u ON a.user_id = u.id
|
||
LEFT JOIN achievement_paper ap ON a.id = ap.achievement_id AND a.type = 'paper'
|
||
LEFT JOIN achievement_project apr ON a.id = apr.achievement_id AND a.type = 'project'
|
||
LEFT JOIN achievement_award aa ON a.id = aa.achievement_id AND a.type = 'award'
|
||
LEFT JOIN achievement_standard as_std ON a.id = as_std.achievement_id AND a.type = 'standard'
|
||
LEFT JOIN achievement_monograph am ON a.id = am.achievement_id AND a.type = 'monograph'
|
||
LEFT JOIN achievement_report ar ON a.id = ar.achievement_id AND a.type = 'report'
|
||
LEFT JOIN achievement_patent apat ON a.id = apat.achievement_id AND a.type = 'patent'
|
||
LEFT JOIN achievement_transformation at ON a.id = at.achievement_id AND a.type = 'transformation'
|
||
LEFT JOIN achievement_software asw ON a.id = asw.achievement_id AND a.type = 'software'
|
||
WHERE 1=1
|
||
`;
|
||
const sqlParams = [];
|
||
|
||
if (type) {
|
||
query += ' AND a.type = $' + (sqlParams.length + 1);
|
||
sqlParams.push(type);
|
||
}
|
||
if (status) {
|
||
query += ' AND a.status = $' + (sqlParams.length + 1);
|
||
sqlParams.push(status);
|
||
}
|
||
if (keyword) {
|
||
query += ` AND (a.name ILIKE $${sqlParams.length + 1} OR a.main_contributors ILIKE $${sqlParams.length + 1} OR a.all_contributors ILIKE $${sqlParams.length + 1} OR a.contributor_phones ILIKE $${sqlParams.length + 1})`;
|
||
sqlParams.push(`%${keyword}%`);
|
||
}
|
||
if (startDate) {
|
||
query += ` AND a.achievement_date >= $${sqlParams.length + 1}`;
|
||
sqlParams.push(startDate);
|
||
}
|
||
if (endDate) {
|
||
query += ` AND a.achievement_date <= $${sqlParams.length + 1}`;
|
||
sqlParams.push(endDate);
|
||
}
|
||
|
||
return { query, sqlParams };
|
||
};
|
||
|
||
|
||
router.get('/export/csv', verifyToken, isSuperAdmin, async (req, res) => {
|
||
try {
|
||
const { query, sqlParams } = buildExportQuery(req.query);
|
||
const result = await db.query(query, sqlParams);
|
||
const leaderDeptSet = await getLeaderDepartmentSet();
|
||
const rows = result.rows.map((row) => normalizeAchievementDepartments(row, leaderDeptSet));
|
||
|
||
if (rows.length === 0) return res.status(404).json({ message: '没有可导出的数据' });
|
||
|
||
// 处理 CSV 内容
|
||
const header = Object.keys(rows[0]).join(',');
|
||
const csvData = rows.map(row => {
|
||
return Object.values(row).map(val => {
|
||
if (val === null || val === undefined) return '';
|
||
|
||
const strVal = String(val);
|
||
if (strVal.includes(',') || strVal.includes('\n') || strVal.includes('"')) {
|
||
return `"${strVal.replace(/"/g, '""')}"`;
|
||
}
|
||
return strVal;
|
||
}).join(',');
|
||
}).join('\n');
|
||
|
||
|
||
const bom = '\uFEFF';
|
||
const fullCsv = bom + header + '\n' + csvData;
|
||
|
||
res.setHeader('Content-Type', 'text/csv; charset=utf-8');
|
||
res.setHeader('Content-Disposition', 'attachment; filename=achievements_export.csv');
|
||
res.status(200).send(fullCsv);
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ message: '导出失败' });
|
||
}
|
||
});
|
||
|
||
// 5. 瀵煎嚭鍘嬬缉鍖?(鍖呭惈绛涢€夊悗鐨勬暟鎹〃鍜岄檮浠?
|
||
router.get('/export/zip', verifyToken, isSuperAdmin, async (req, res) => {
|
||
const archive = archiver('zip', { zlib: { level: 9 } });
|
||
res.setHeader('Content-Type', 'application/zip');
|
||
res.setHeader('Content-Disposition', 'attachment; filename=achievements_export.zip');
|
||
|
||
archive.on('error', (err) => {
|
||
if (!res.headersSent) res.status(500).send({ error: err.message });
|
||
});
|
||
archive.pipe(res);
|
||
|
||
try {
|
||
// 1. 鑾峰彇绛涢€夊悗鐨勬暟鎹?
|
||
const { query, sqlParams } = buildExportQuery(req.query);
|
||
const resultData = await db.query(query, sqlParams);
|
||
const leaderDeptSet = await getLeaderDepartmentSet();
|
||
const normalizedRows = resultData.rows.map((row) => normalizeAchievementDepartments(row, leaderDeptSet));
|
||
|
||
if (normalizedRows.length > 0) {
|
||
// 鐢熸垚 CSV
|
||
const header = Object.keys(normalizedRows[0]).join(',');
|
||
const csvData = normalizedRows.map(row => {
|
||
return Object.values(row).map(val => {
|
||
if (val === null || val === undefined) return '';
|
||
const strVal = String(val);
|
||
if (strVal.includes(',') || strVal.includes('\n') || strVal.includes('"')) {
|
||
return `"${strVal.replace(/"/g, '""')}"`;
|
||
}
|
||
return strVal;
|
||
}).join(',');
|
||
}).join('\n');
|
||
const bom = '\uFEFF';
|
||
archive.append(bom + header + '\n' + csvData, { name: 'achievements_list.csv' });
|
||
|
||
// 2. 获取相关附件
|
||
const achievementIds = normalizedRows.map(r => r.id);
|
||
if (achievementIds.length > 0) {
|
||
const fileQuery = 'SELECT aa.file_path, aa.file_name, a.name as achievement_name, a.type FROM achievement_attachments aa JOIN achievements a ON aa.achievement_id = a.id WHERE aa.achievement_id = ANY($1)';
|
||
const resultFiles = await db.query(fileQuery, [achievementIds]);
|
||
|
||
resultFiles.rows.forEach(row => {
|
||
const filePath = findFile(row.file_path);
|
||
if (filePath) {
|
||
|
||
// 处理文件名中的非法字符
|
||
const safeAchName = row.achievement_name.replace(/[\\/:*?"<>|]/g, '_');
|
||
const archivePath = `${row.type}/${safeAchName}/${row.file_name}`;
|
||
archive.file(filePath, { name: archivePath });
|
||
}
|
||
});
|
||
}
|
||
} else {
|
||
archive.append('No data matched current filters.', { name: 'readme.txt' });
|
||
}
|
||
|
||
archive.finalize();
|
||
} catch (err) {
|
||
console.error(err);
|
||
if (!res.headersSent) res.status(500).json({ message: '压缩失败' });
|
||
}
|
||
});
|
||
|
||
// 7. 下载单个附件
|
||
router.get('/attachments/:id/download', verifyToken, async (req, res) => {
|
||
const { id } = req.params;
|
||
try {
|
||
|
||
console.log('正在下载附件 ID:', id);
|
||
const result = await db.query(
|
||
'SELECT aa.file_name, aa.file_path, aa.achievement_id, a.user_id FROM achievement_attachments aa JOIN achievements a ON aa.achievement_id = a.id WHERE aa.id = $1',
|
||
[id]
|
||
);
|
||
if (result.rows.length === 0) return res.status(404).json({ message: 'Attachment not found' });
|
||
|
||
const attachment = result.rows[0];
|
||
|
||
const isSystemAdmin = ['admin', 'super_admin'].includes(req.user.role);
|
||
|
||
if (!isSystemAdmin) {
|
||
|
||
const achRes = await db.query('SELECT status, contributors FROM achievements WHERE id = $1', [attachment.achievement_id]);
|
||
const achievement = achRes.rows[0];
|
||
|
||
|
||
if (attachment.user_id === req.user.id && ['pending', 'rejected'].includes(achievement.status)) {
|
||
// 鍏佽
|
||
} else {
|
||
|
||
const userPhone = req.user.username;
|
||
const isContributor = achievement.contributors && Array.isArray(achievement.contributors) && achievement.contributors.some(c => c.phone === userPhone);
|
||
|
||
|
||
if (!isContributor) return res.status(403).json({ message: '无权访问' });
|
||
}
|
||
}
|
||
|
||
const filePath = findFile(attachment.file_path);
|
||
if (!filePath) return res.status(404).json({ message: 'File not found' });
|
||
|
||
const encodedFileName = encodeURIComponent(attachment.file_name);
|
||
res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodedFileName}`);
|
||
res.sendFile(filePath, (err) => {
|
||
if (err && !res.headersSent) res.status(500).json({ message: '下载失败' });
|
||
});
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ message: 'Server error' });
|
||
}
|
||
});
|
||
|
||
|
||
router.get('/', verifyToken, async (req, res) => {
|
||
const { type, status, keyword, startDate, endDate, scope, department } = req.query;
|
||
let query = 'SELECT a.*, u.username, u.real_name as submitter_name FROM achievements a JOIN users u ON a.user_id = u.id WHERE 1=1';
|
||
const params = [];
|
||
|
||
|
||
|
||
|
||
let realName = req.user.real_name;
|
||
let userDept = null;
|
||
let deptPhones = [];
|
||
|
||
// 鏌ヨ鏈€鏂扮殑 real_name 鍜?department
|
||
const userRes = await db.query('SELECT real_name, department FROM users WHERE id = $1', [req.user.id]);
|
||
if (userRes.rows.length > 0) {
|
||
realName = userRes.rows[0].real_name;
|
||
userDept = userRes.rows[0].department;
|
||
}
|
||
|
||
const phonePattern = '(^|[[:space:],])' + req.user.username + '([[:space:],]|$)';
|
||
const namePattern = realName || '__NO_NAME__';
|
||
const phoneParam = phonePattern;
|
||
const nameParam = namePattern;
|
||
|
||
|
||
|
||
const contributorJsonParam = JSON.stringify([{ phone: req.user.username }]);
|
||
|
||
if (scope === 'my') {
|
||
// 銆愭垜鐨勬垚鏋溿€戣鍥?
|
||
|
||
|
||
query += ` AND ((a.contributors @> $${params.length + 1}) OR (a.user_id = $${params.length + 2} AND a.status IN ('pending', 'rejected')))`;
|
||
params.push(contributorJsonParam);
|
||
params.push(req.user.id);
|
||
} else {
|
||
// 銆愭垚鏋滃叕绀恒€戣鍥?(榛樿)
|
||
if (req.user.role === 'maintainer') {
|
||
|
||
return res.status(403).json({ message: '无权访问' });
|
||
} else if (req.user.role === 'admin' || req.user.role === 'super_admin') {
|
||
// 绠＄悊鍛?& 瓒呯骇绠＄悊鍛橈細
|
||
|
||
} else if (req.user.role === 'senior_user') {
|
||
|
||
|
||
query += ` AND (a.status = 'approved' OR (a.contributors @> $${params.length + 1}) OR (a.user_id = $${params.length + 2} AND a.status IN ('pending', 'rejected')))`;
|
||
params.push(contributorJsonParam);
|
||
params.push(req.user.id);
|
||
} else if (req.user.role === 'intermediate_user') {
|
||
|
||
// 1. 涓庢垜鐩稿叧 (contributors @> myPhone)
|
||
|
||
|
||
|
||
|
||
if (userDept) {
|
||
const deptUsersRes = await db.query('SELECT username FROM users WHERE department = $1', [userDept]);
|
||
deptPhones = deptUsersRes.rows.map(u => u.username);
|
||
}
|
||
|
||
// 1. 涓庢垜鐩稿叧
|
||
const relatedCondition = `(a.contributors @> $${params.length + 1})`;
|
||
params.push(contributorJsonParam);
|
||
|
||
|
||
const myPendingCondition = `(a.user_id = $${params.length + 1} AND a.status IN ('pending', 'rejected'))`;
|
||
params.push(req.user.id);
|
||
|
||
|
||
let deptExtendedCondition = 'FALSE';
|
||
if (userDept && deptPhones.length > 0) {
|
||
deptExtendedCondition = `(
|
||
a.status = 'approved' AND (
|
||
$${params.length + 1} = ANY(a.assigned_departments)
|
||
OR
|
||
EXISTS (
|
||
SELECT 1
|
||
FROM jsonb_array_elements(a.contributors) c
|
||
WHERE c->>'phone' = ANY($${params.length + 2})
|
||
)
|
||
)
|
||
)`;
|
||
params.push(userDept);
|
||
params.push(deptPhones);
|
||
}
|
||
|
||
query += ` AND (${relatedCondition} OR ${myPendingCondition} OR ${deptExtendedCondition})`;
|
||
} else {
|
||
|
||
|
||
|
||
// 1. 涓庢垜鐩稿叧
|
||
const relatedCondition = `(a.contributors @> $${params.length + 1})`;
|
||
params.push(contributorJsonParam);
|
||
|
||
|
||
let deptCondition = 'FALSE';
|
||
if (userDept) {
|
||
deptCondition = `(a.status = 'approved' AND $${params.length + 1} = ANY(a.assigned_departments))`;
|
||
params.push(userDept);
|
||
}
|
||
|
||
|
||
const myPendingCondition = `(a.user_id = $${params.length + 1} AND a.status IN ('pending', 'rejected'))`;
|
||
params.push(req.user.id);
|
||
|
||
query += ` AND (${relatedCondition} OR ${deptCondition} OR ${myPendingCondition})`;
|
||
}
|
||
}
|
||
if (type) {
|
||
query += ' AND a.type = $' + (params.length + 1);
|
||
params.push(type);
|
||
}
|
||
if (status) {
|
||
query += ' AND a.status = $' + (params.length + 1);
|
||
params.push(status);
|
||
}
|
||
if (keyword) {
|
||
query += ` AND (a.name ILIKE $${params.length + 1} OR a.main_contributors ILIKE $${params.length + 1} OR a.all_contributors ILIKE $${params.length + 1} OR a.contributor_phones ILIKE $${params.length + 1})`;
|
||
params.push(`%${keyword}%`);
|
||
}
|
||
if (startDate) {
|
||
query += ` AND a.achievement_date >= $${params.length + 1}`;
|
||
params.push(startDate);
|
||
}
|
||
if (endDate) {
|
||
query += ` AND a.achievement_date <= $${params.length + 1}`;
|
||
params.push(endDate);
|
||
}
|
||
if (department) {
|
||
query += ` AND $${params.length + 1} = ANY(a.assigned_departments)`;
|
||
params.push(department);
|
||
}
|
||
|
||
|
||
query += ' ORDER BY a.achievement_date DESC, a.created_at DESC';
|
||
|
||
try {
|
||
const result = await db.query(query, params);
|
||
const leaderDeptSet = await getLeaderDepartmentSet();
|
||
|
||
// 妫€鏌ユ暟鎹槸鍚﹀寘鍚?'寰呭畾' 鍐呭
|
||
let rows = result.rows.map(row => {
|
||
const normalizedRow = normalizeAchievementDepartments(row, leaderDeptSet);
|
||
let hasIssue = false;
|
||
|
||
// 1. 妫€鏌ュ綊灞為儴闂?
|
||
if (normalizedRow.assigned_departments && normalizedRow.assigned_departments.includes('寰呭畾')) hasIssue = true;
|
||
|
||
// 2. 检查各类型特有字段
|
||
const checkFields = [
|
||
normalizedRow.paper_type, normalizedRow.first_unit, // paper
|
||
normalizedRow.project_category, normalizedRow.project_source, // project
|
||
normalizedRow.award_type, normalizedRow.award_level, normalizedRow.award_unit, // award
|
||
normalizedRow.standard_type, // standard
|
||
normalizedRow.publisher, // monograph
|
||
normalizedRow.trans_project_category, // transformation
|
||
normalizedRow.owner_unit // software
|
||
];
|
||
|
||
if (checkFields.some(val => val === '寰呭畾')) hasIssue = true;
|
||
|
||
return { ...normalizedRow, has_data_issue: hasIssue };
|
||
});
|
||
|
||
const getOwnerDepartment = (row) => {
|
||
const depts = Array.isArray(row?.assigned_departments) ? row.assigned_departments : [];
|
||
return String(depts[0] || '').trim();
|
||
};
|
||
const isRelatedToMe = (row) => {
|
||
const myPhone = String(req.user.username || '').trim();
|
||
if (!myPhone || !Array.isArray(row?.contributors)) return false;
|
||
return row.contributors.some((c) => String(c?.phone || '').trim() === myPhone);
|
||
};
|
||
const isMyPending = (row) => {
|
||
return Number(row?.user_id) === Number(req.user.id) && ['pending', 'rejected'].includes(row?.status);
|
||
};
|
||
|
||
// 部门筛选统一按“单归属部门”口径，不按历史多部门数组匹配
|
||
if (department) {
|
||
const targetDept = String(department || '').trim();
|
||
rows = rows.filter((row) => getOwnerDepartment(row) === targetDept);
|
||
}
|
||
|
||
// 对历史多部门数据做二次收敛，避免 ANY(a.assigned_departments) 带来的越权/误入
|
||
if (scope !== 'my') {
|
||
if (req.user.role === 'user') {
|
||
rows = rows.filter((row) => {
|
||
if (isRelatedToMe(row) || isMyPending(row)) return true;
|
||
return row.status === 'approved' && !!userDept && getOwnerDepartment(row) === userDept;
|
||
});
|
||
} else if (req.user.role === 'intermediate_user') {
|
||
const deptPhoneSet = new Set((deptPhones || []).map((p) => String(p || '').trim()).filter(Boolean));
|
||
rows = rows.filter((row) => {
|
||
if (isRelatedToMe(row) || isMyPending(row)) return true;
|
||
if (row.status !== 'approved') return false;
|
||
if (userDept && getOwnerDepartment(row) === userDept) return true;
|
||
if (deptPhoneSet.size === 0 || !Array.isArray(row?.contributors)) return false;
|
||
return row.contributors.some((c) => deptPhoneSet.has(String(c?.phone || '').trim()));
|
||
});
|
||
}
|
||
}
|
||
|
||
res.json(rows);
|
||
} catch (err) {
|
||
res.status(500).json({ message: 'Server error' });
|
||
}
|
||
});
|
||
|
||
|
||
|
||
router.get('/validate', verifyToken, async (req, res) => {
|
||
// 鏉冮檺妫€鏌?
|
||
if (req.user.role !== 'super_admin') {
|
||
return res.status(403).json({ message: '无权访问' });
|
||
}
|
||
|
||
try {
|
||
|
||
const achievementsRes = await db.query(`
|
||
SELECT
|
||
a.id, a.name, a.type, a.contributors, a.assigned_departments,
|
||
u.username as submitter_username, u.real_name as submitter_name,
|
||
-- Paper
|
||
ap.paper_type, ap.first_unit,
|
||
-- Project
|
||
apr.project_category,
|
||
-- Award
|
||
aa.award_type, aa.award_level, aa.award_unit,
|
||
-- Standard
|
||
as_std.standard_type,
|
||
-- Transformation
|
||
at.project_category as trans_project_category,
|
||
-- Software
|
||
asw.owner_unit
|
||
FROM achievements a
|
||
JOIN users u ON a.user_id = u.id
|
||
LEFT JOIN achievement_paper ap ON a.id = ap.achievement_id AND a.type = 'paper'
|
||
LEFT JOIN achievement_project apr ON a.id = apr.achievement_id AND a.type = 'project'
|
||
LEFT JOIN achievement_award aa ON a.id = aa.achievement_id AND a.type = 'award'
|
||
LEFT JOIN achievement_standard as_std ON a.id = as_std.achievement_id AND a.type = 'standard'
|
||
LEFT JOIN achievement_transformation at ON a.id = at.achievement_id AND a.type = 'transformation'
|
||
LEFT JOIN achievement_software asw ON a.id = asw.achievement_id AND a.type = 'software'
|
||
`);
|
||
const achievements = achievementsRes.rows;
|
||
|
||
|
||
const usersRes = await db.query('SELECT username, real_name, dept_status FROM users');
|
||
const userMap = new Map(); // username -> { real_name, dept_status }
|
||
usersRes.rows.forEach(u => userMap.set(u.username, { real_name: u.real_name, dept_status: u.dept_status }));
|
||
|
||
// 3. 鑾峰彇鎵€鏈夐儴闂ㄦ暟鎹?(鐢ㄤ簬姣斿)
|
||
const deptsRes = await db.query('SELECT name FROM departments');
|
||
const deptSet = new Set(deptsRes.rows.map(d => d.name));
|
||
|
||
// 4. 鑾峰彇鎵€鏈夊瓧鍏歌〃鏁版嵁 (鐢ㄤ簬姣斿)
|
||
const dicts = {
|
||
orgs: new Set((await db.query('SELECT name FROM dict_organizations')).rows.map(r => r.name)),
|
||
awardTypes: new Set((await db.query('SELECT name FROM dict_award_types')).rows.map(r => r.name)),
|
||
awardLevels: new Set((await db.query('SELECT name FROM dict_award_levels')).rows.map(r => r.name)),
|
||
paperTypes: new Set((await db.query('SELECT name FROM dict_paper_types')).rows.map(r => r.name)),
|
||
standardTypes: new Set((await db.query('SELECT name FROM dict_standard_types')).rows.map(r => r.name)),
|
||
projectCats: new Set((await db.query('SELECT name FROM dict_project_categories')).rows.map(r => r.name)),
|
||
};
|
||
|
||
const validationResults = [];
|
||
|
||
for (const ach of achievements) {
|
||
const errors = [];
|
||
const contributors = ach.contributors || [];
|
||
const assignedDepts = ach.assigned_departments || [];
|
||
|
||
|
||
for (const c of contributors) {
|
||
if (c.phone) {
|
||
const userInfo = userMap.get(c.phone);
|
||
if (!userInfo) {
|
||
errors.push(`完成人 ${c.name}(${c.phone}) 在用户表中不存在`);
|
||
} else if (userInfo.dept_status === 'pending') {
|
||
errors.push(`完成人 ${c.name}(${c.phone}) 的部门状态为待定，请核实`);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 4.2 鏍￠獙閮ㄩ棬
|
||
for (const dept of assignedDepts) {
|
||
if (!deptSet.has(dept)) {
|
||
errors.push(`归属部门 '${dept}' 在部门表中不存在`);
|
||
}
|
||
}
|
||
|
||
// 4.3 校验字典字段
|
||
switch (ach.type) {
|
||
case 'paper':
|
||
if (ach.paper_type && !dicts.paperTypes.has(ach.paper_type)) errors.push(`paper_type '${ach.paper_type}' not found in dictionary`);
|
||
if (ach.first_unit && !dicts.orgs.has(ach.first_unit)) errors.push(`first_unit '${ach.first_unit}' not found in organization dictionary`);
|
||
break;
|
||
case 'project':
|
||
if (ach.project_category && !dicts.projectCats.has(ach.project_category)) errors.push(`project_category '${ach.project_category}' not found in dictionary`);
|
||
break;
|
||
case 'award':
|
||
if (ach.award_type && !dicts.awardTypes.has(ach.award_type)) errors.push(`award_type '${ach.award_type}' not found in dictionary`);
|
||
if (ach.award_level && !dicts.awardLevels.has(ach.award_level)) errors.push(`award_level '${ach.award_level}' not found in dictionary`);
|
||
if (ach.award_unit && !dicts.orgs.has(ach.award_unit)) errors.push(`award_unit '${ach.award_unit}' not found in organization dictionary`);
|
||
break;
|
||
case 'standard':
|
||
if (ach.standard_type && !dicts.standardTypes.has(ach.standard_type)) errors.push(`standard_type '${ach.standard_type}' not found in dictionary`);
|
||
break;
|
||
case 'transformation':
|
||
if (ach.trans_project_category && !dicts.projectCats.has(ach.trans_project_category)) errors.push(`trans_project_category '${ach.trans_project_category}' not found in dictionary`);
|
||
break;
|
||
}
|
||
|
||
if (errors.length > 0) {
|
||
validationResults.push({
|
||
id: ach.id,
|
||
name: ach.name,
|
||
type: ach.type,
|
||
submitter: ach.submitter_name || ach.submitter_username,
|
||
errors: errors
|
||
});
|
||
}
|
||
}
|
||
|
||
res.json(validationResults);
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ message: '校验失败' });
|
||
}
|
||
});
|
||
|
||
|
||
router.get('/:id', verifyToken, async (req, res) => {
|
||
const { id } = req.params;
|
||
try {
|
||
const baseRes = await db.query('SELECT * FROM achievements WHERE id = $1', [id]);
|
||
if (baseRes.rows.length === 0) return res.status(404).json({ message: '未找到该成果' });
|
||
|
||
const achievement = baseRes.rows[0];
|
||
const leaderDeptSet = await getLeaderDepartmentSet();
|
||
const normalizedAchievement = normalizeAchievementDepartments(achievement, leaderDeptSet);
|
||
|
||
const isSystemAdmin = ['admin', 'super_admin'].includes(req.user.role);
|
||
|
||
if (!isSystemAdmin) {
|
||
|
||
|
||
if (achievement.user_id === req.user.id && ['pending', 'rejected'].includes(achievement.status)) {
|
||
|
||
} else {
|
||
|
||
let userDept = null;
|
||
|
||
|
||
const userRes = await db.query('SELECT department FROM users WHERE id = $1', [req.user.id]);
|
||
if (userRes.rows.length > 0) {
|
||
userDept = userRes.rows[0].department;
|
||
}
|
||
|
||
const userPhone = req.user.username;
|
||
const isContributor = achievement.contributors && Array.isArray(achievement.contributors) && achievement.contributors.some(c => c.phone === userPhone);
|
||
|
||
if (isContributor) {
|
||
|
||
} else {
|
||
|
||
if (achievement.status === 'approved') {
|
||
if (req.user.role === 'senior_user') {
|
||
|
||
} else if (req.user.role === 'user') {
|
||
|
||
if (!userDept || !normalizedAchievement.assigned_departments || !normalizedAchievement.assigned_departments.includes(userDept)) {
|
||
return res.status(403).json({ message: '无权访问（非本部门成果）' });
|
||
}
|
||
} else {
|
||
return res.status(403).json({ message: '无权访问' });
|
||
}
|
||
} else {
|
||
|
||
return res.status(403).json({ message: '无权访问' });
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
const detailTable = `achievement_${achievement.type}`;
|
||
const detailRes = await db.query(`SELECT * FROM ${detailTable} WHERE achievement_id = $1`, [id]);
|
||
const attachmentsRes = await db.query('SELECT * FROM achievement_attachments WHERE achievement_id = $1', [id]);
|
||
|
||
res.json({ ...normalizedAchievement, details: detailRes.rows[0], attachments: attachmentsRes.rows });
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ message: 'Server error' });
|
||
}
|
||
});
|
||
|
||
|
||
router.post('/audit/:id', verifyToken, isAdmin, async (req, res) => {
|
||
const { id } = req.params;
|
||
const { status, audit_comment } = req.body;
|
||
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
|
||
if (req.user.role === 'admin') {
|
||
const checkRes = await client.query('SELECT contributor_phones FROM achievements WHERE id = $1', [id]);
|
||
const contributorPhones = checkRes.rows[0]?.contributor_phones || '';
|
||
const selfPhone = req.user.username;
|
||
const phonesArray = contributorPhones.split(/[ \t\n,]+/);
|
||
|
||
if (phonesArray.includes(selfPhone)) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(403).json({ message: 'Conflict-of-interest rule: you cannot audit achievements you contributed to.' });
|
||
}
|
||
}
|
||
|
||
|
||
await client.query(
|
||
'UPDATE achievements SET status = $1, audit_comment = $2, updated_at = CURRENT_TIMESTAMP WHERE id = $3',
|
||
[status, audit_comment, id]
|
||
);
|
||
|
||
|
||
if (status === 'rejected') {
|
||
const filesRes = await client.query('SELECT file_path FROM achievement_attachments WHERE achievement_id = $1', [id]);
|
||
|
||
for (const row of filesRes.rows) {
|
||
const filePath = findFile(row.file_path);
|
||
if (filePath) {
|
||
try { fs.unlinkSync(filePath); } catch (e) {}
|
||
}
|
||
}
|
||
await client.query('DELETE FROM achievement_attachments WHERE achievement_id = $1', [id]);
|
||
}
|
||
|
||
await client.query('COMMIT');
|
||
|
||
const clientIp = req.headers['x-forwarded-for'] || req.socket.remoteAddress;
|
||
logger.info(`管理员 ${req.user.username} 审核了成果 ID ${id}: 状态变更为 ${status} (IP: ${clientIp})`);
|
||
|
||
res.json({ message: status === 'rejected' ? 'Rejected and related attachments cleaned' : 'Audit completed' });
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
console.error(err);
|
||
res.status(500).json({ message: 'Server error' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
|
||
router.put('/:id', verifyToken, async (req, res) => {
|
||
const { id } = req.params;
|
||
const { details, name, contributors, achievement_date, remarks } = req.body;
|
||
let parsedDetails = details || {};
|
||
let parsedContributors = contributors || [];
|
||
|
||
try {
|
||
parsedDetails = typeof details === 'string' ? JSON.parse(details) : (details || {});
|
||
} catch (e) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: 'details 字段格式错误（应为 JSON）' });
|
||
}
|
||
|
||
try {
|
||
parsedContributors = typeof contributors === 'string' ? JSON.parse(contributors) : (contributors || []);
|
||
} catch (e) {
|
||
cleanupUploadedFiles(req.files || []);
|
||
return res.status(400).json({ message: 'contributors 字段格式错误（应为 JSON）' });
|
||
}
|
||
|
||
|
||
const phoneSet = new Set();
|
||
for (const c of parsedContributors) {
|
||
if (c.phone) {
|
||
if (phoneSet.has(c.phone)) {
|
||
return res.status(400).json({ message: `Duplicate contributor phone: ${c.phone} (${c.name})` });
|
||
}
|
||
phoneSet.add(c.phone);
|
||
}
|
||
}
|
||
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
const baseRes = await client.query('SELECT type, user_id, status FROM achievements WHERE id = $1', [id]);
|
||
if (baseRes.rows.length === 0) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(404).json({ message: '未找到该成果' });
|
||
}
|
||
|
||
const achievement = baseRes.rows[0];
|
||
// ??????????????????
|
||
if (!['project', 'transformation'].includes(achievement.type)) {
|
||
const rankingSet = new Set();
|
||
for (const c of parsedContributors) {
|
||
if (c.ranking) {
|
||
const r = parseInt(c.ranking);
|
||
if (rankingSet.has(r)) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(400).json({ message: `Duplicate ranking: #${r}` });
|
||
}
|
||
rankingSet.add(r);
|
||
}
|
||
}
|
||
}
|
||
|
||
let canEdit = false;
|
||
if (req.user.role === 'super_admin') {
|
||
canEdit = true;
|
||
} else if (achievement.user_id === req.user.id && ['pending', 'rejected'].includes(achievement.status)) {
|
||
canEdit = true;
|
||
}
|
||
|
||
if (!canEdit) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(403).json({ message: '无权修改（仅超级管理员，或成果提交人在待审核/已驳回状态下可修改）' });
|
||
}
|
||
|
||
const type = achievement.type;
|
||
let updateQuery = '';
|
||
let params = [];
|
||
|
||
switch (type) {
|
||
case 'paper':
|
||
updateQuery = 'UPDATE achievement_paper SET paper_type=$2, first_unit=$3, journal_name=$4, publish_date=$5, doi=$6 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.paper_type, parsedDetails.first_unit, parsedDetails.journal_name, parsedDetails.publish_date, parsedDetails.doi];
|
||
break;
|
||
case 'project':
|
||
updateQuery = 'UPDATE achievement_project SET project_category=$2, source=$3 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.project_category, parsedDetails.source];
|
||
break;
|
||
case 'award':
|
||
updateQuery = 'UPDATE achievement_award SET award_type=$2, award_level=$3, award_unit=$4 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.award_type, parsedDetails.award_level, parsedDetails.award_unit];
|
||
break;
|
||
case 'standard':
|
||
updateQuery = 'UPDATE achievement_standard SET standard_type=$2, standard_no=$3, implement_date=$4 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.standard_type, parsedDetails.standard_no, parsedDetails.implement_date];
|
||
break;
|
||
case 'monograph':
|
||
updateQuery = 'UPDATE achievement_monograph SET publisher=$2, isbn=$3 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.publisher, parsedDetails.isbn];
|
||
break;
|
||
case 'report':
|
||
updateQuery = 'UPDATE achievement_report SET report_type=$2, recipient=$3, approver_id=$4, approver_name=$5 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.report_type, parsedDetails.recipient, parsedDetails.approver_id, parsedDetails.approver_name];
|
||
break;
|
||
case 'plan':
|
||
updateQuery = 'SELECT 1'; // 规划表目前没有额外字段需要更新
|
||
params = [];
|
||
break;
|
||
case 'patent':
|
||
updateQuery = 'UPDATE achievement_patent SET patent_no=$2, patent_type=$3, assignee=$4 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.patent_no, parsedDetails.patent_type, parsedDetails.assignee];
|
||
break;
|
||
case 'transformation':
|
||
updateQuery = 'UPDATE achievement_transformation SET trans_method=$2, trans_amount=$3, project_category=$4 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.trans_method, parsedDetails.trans_amount, parsedDetails.project_category];
|
||
break;
|
||
case 'software':
|
||
updateQuery = 'UPDATE achievement_software SET reg_no=$2, acquisition_method=$3, scope=$4, owner_unit=$5 WHERE achievement_id=$1';
|
||
params = [id, parsedDetails.reg_no, parsedDetails.acquisition_method, parsedDetails.scope, parsedDetails.owner_unit];
|
||
break;
|
||
default:
|
||
throw new Error('Unknown achievement type');
|
||
}
|
||
|
||
|
||
let processedContributors = parsedContributors.map(c => ({
|
||
...c,
|
||
ranking: c.ranking ? parseInt(c.ranking) : null
|
||
}));
|
||
|
||
if (type === 'paper') {
|
||
const mainRoles = ['第一作者', '共同第一作者', '通讯作者', '共同通讯作者'];
|
||
processedContributors = processedContributors.map(c => ({
|
||
...c,
|
||
isMain: mainRoles.includes(c.role)
|
||
}));
|
||
} else if (['project', 'transformation'].includes(type)) {
|
||
|
||
} else {
|
||
const rankings = processedContributors.map(c => c.ranking).filter(r => r !== null && !isNaN(r));
|
||
const minRanking = rankings.length > 0 ? Math.min(...rankings) : null;
|
||
|
||
processedContributors = processedContributors.map(c => ({
|
||
...c,
|
||
isMain: minRanking !== null && c.ranking === minRanking
|
||
}));
|
||
}
|
||
|
||
// 鎸夋帓鍚嶆帓搴?
|
||
processedContributors.sort((a, b) => {
|
||
if (a.ranking && b.ranking) return a.ranking - b.ranking;
|
||
if (a.ranking) return -1;
|
||
if (b.ranking) return 1;
|
||
return 0;
|
||
});
|
||
|
||
|
||
if (!processedContributors.some(c => c.isMain) && (type === 'paper' || ['project', 'transformation'].includes(type))) {
|
||
processedContributors = processedContributors.map(c => ({ ...c, isMain: true }));
|
||
}
|
||
|
||
const main_contributors_str = processedContributors.filter(c => c.isMain).map(c => c.name).join(' ');
|
||
const all_contributors_str = processedContributors.map(c => c.name).join(' ');
|
||
const contributor_phones_str = processedContributors.map(c => c.phone).join(' ');
|
||
|
||
|
||
const assignedDepartments = await resolveAssignedDepartmentsFromContributors(client, processedContributors);
|
||
|
||
await client.query(updateQuery, params);
|
||
await client.query(
|
||
'UPDATE achievements SET name=$2, main_contributors=$3, all_contributors=$4, contributor_phones=$5, achievement_date=$6, remarks=$7, contributors=$8, assigned_departments=$9, updated_at = CURRENT_TIMESTAMP WHERE id = $1',
|
||
[id, name, main_contributors_str, all_contributors_str, contributor_phones_str, achievement_date, remarks, JSON.stringify(processedContributors), assignedDepartments]
|
||
);
|
||
await client.query('COMMIT');
|
||
res.json({ message: '修改成功' });
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
console.error(err);
|
||
res.status(500).json({ message: 'Server error' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
|
||
router.delete('/:id', verifyToken, async (req, res) => {
|
||
const { id } = req.params;
|
||
const client = await db.pool.connect();
|
||
try {
|
||
await client.query('BEGIN');
|
||
|
||
const checkRes = await client.query('SELECT * FROM achievements WHERE id = $1', [id]);
|
||
if (checkRes.rows.length === 0) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(404).json({ message: '未找到该成果' });
|
||
}
|
||
|
||
const achievement = checkRes.rows[0];
|
||
|
||
|
||
let canDelete = false;
|
||
if (req.user.role === 'super_admin') {
|
||
canDelete = true;
|
||
} else if (achievement.user_id === req.user.id && ['pending', 'rejected'].includes(achievement.status)) {
|
||
canDelete = true;
|
||
}
|
||
|
||
if (!canDelete) {
|
||
await client.query('ROLLBACK');
|
||
return res.status(403).json({ message: '无权删除（仅超级管理员，或成果提交人在待审核/已驳回状态下可删除）' });
|
||
}
|
||
|
||
const filesRes = await client.query('SELECT file_path FROM achievement_attachments WHERE achievement_id = $1', [id]);
|
||
for (const row of filesRes.rows) {
|
||
const filePath = findFile(row.file_path);
|
||
if (filePath) {
|
||
try { fs.unlinkSync(filePath); } catch (e) {}
|
||
}
|
||
}
|
||
|
||
await client.query('DELETE FROM achievements WHERE id = $1', [id]);
|
||
|
||
await client.query('COMMIT');
|
||
|
||
const clientIp = req.headers['x-forwarded-for'] || req.socket.remoteAddress;
|
||
logger.info(`用户 ${req.user.username} 删除了成果 ID ${id} (IP: ${clientIp})`);
|
||
|
||
res.json({ message: '删除成功，相关文件已清理' });
|
||
} catch (err) {
|
||
await client.query('ROLLBACK');
|
||
res.status(500).json({ message: 'Server error' });
|
||
} finally {
|
||
client.release();
|
||
}
|
||
});
|
||
|
||
|
||
router.get('/:id/attachments/zip', verifyToken, async (req, res) => {
|
||
const { id } = req.params;
|
||
try {
|
||
const baseRes = await db.query('SELECT user_id, name, contributors, status FROM achievements WHERE id = $1', [id]);
|
||
if (baseRes.rows.length === 0) return res.status(404).json({ message: '未找到该成果' });
|
||
|
||
const achievement = baseRes.rows[0];
|
||
|
||
const isSystemAdmin = ['admin', 'super_admin'].includes(req.user.role);
|
||
|
||
if (!isSystemAdmin) {
|
||
|
||
if (achievement.user_id === req.user.id && ['pending', 'rejected'].includes(achievement.status)) {
|
||
// 鍏佽
|
||
} else {
|
||
|
||
const userPhone = req.user.username;
|
||
const isContributor = achievement.contributors && Array.isArray(achievement.contributors) && achievement.contributors.some(c => c.phone === userPhone);
|
||
|
||
if (!isContributor) {
|
||
return res.status(403).json({ message: '无权访问' });
|
||
}
|
||
}
|
||
}
|
||
|
||
const attachmentsRes = await db.query('SELECT * FROM achievement_attachments WHERE achievement_id = $1', [id]);
|
||
if (attachmentsRes.rows.length === 0) return res.status(404).json({ message: 'No attachments found for this achievement' });
|
||
|
||
const archive = archiver('zip', { zlib: { level: 9 } });
|
||
const zipName = `成果附件_${achievement.name || id}.zip`;
|
||
res.setHeader('Content-Type', 'application/zip');
|
||
res.setHeader('Content-Disposition', contentDisposition(zipName));
|
||
|
||
archive.pipe(res);
|
||
|
||
attachmentsRes.rows.forEach(file => {
|
||
const filePath = findFile(file.file_path);
|
||
if (filePath) archive.file(filePath, { name: file.file_name });
|
||
});
|
||
|
||
archive.finalize();
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ message: '压缩失败' });
|
||
}
|
||
});
|
||
|
||
module.exports = router;
|
||
|