213 lines
9.2 KiB
JavaScript
213 lines
9.2 KiB
JavaScript
const { Pool } = require('pg');
|
|
const bcrypt = require('bcryptjs');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
require('dotenv').config({ path: path.join(__dirname, '../.env') });
|
|
|
|
const pool = new Pool({
|
|
user: process.env.DB_USER,
|
|
host: process.env.DB_HOST,
|
|
database: process.env.DB_DATABASE || process.env.DB_NAME, // 兼容 .env 中的不同命名
|
|
password: process.env.DB_PASSWORD,
|
|
port: process.env.DB_PORT,
|
|
});
|
|
|
|
// 随机数据源
|
|
const DEPARTMENTS = ['研发部', '市场部', '人事部', '财务部', '销售部', '运维部'];
|
|
const SURNAMES = ['赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩', '杨'];
|
|
const NAMES = ['伟', '芳', '娜', '敏', '静', '强', '磊', '军', '洋', '勇', '艳', '杰', '娟', '涛', '明', '超'];
|
|
const TITLES_PREFIX = ['基于AI的', '高性能', '分布式', '新一代', '云原生', '跨平台', '智能', '自动化'];
|
|
const TITLES_SUFFIX = ['系统设计', '算法研究', '平台开发', '应用实践', '优化方案', '技术白皮书', '分析报告'];
|
|
|
|
// 字典数据
|
|
const DICTS = {
|
|
dict_organizations: ['科技大学', '软件研究所', '创新中心', '数据实验室'],
|
|
dict_paper_types: ['SCI', 'EI', '核心期刊', '会议论文'],
|
|
dict_award_types: ['科技进步奖', '技术发明奖', '自然科学奖'],
|
|
dict_award_levels: ['一等奖', '二等奖', '三等奖', '特等奖'],
|
|
dict_project_categories: ['国家重点研发计划', '自然科学基金', '省部级项目', '横向课题'],
|
|
dict_standard_types: ['国家标准', '行业标准', '团体标准', '企业标准']
|
|
};
|
|
|
|
const ACHIEVEMENT_TYPES = [
|
|
'paper', 'project', 'award', 'standard', 'monograph', 'report', 'plan', 'patent', 'transformation', 'software'
|
|
];
|
|
|
|
const STATUSES = ['pending', 'approved', 'rejected'];
|
|
|
|
// 辅助函数
|
|
const randomInt = (min, max) => Math.floor(Math.random() * (max - min + 1)) + min;
|
|
const randomChoice = (arr) => arr[randomInt(0, arr.length - 1)];
|
|
const generatePhone = () => `1${randomChoice(['3', '5', '7', '8', '9'])}${randomInt(100000000, 999999999)}`;
|
|
const generateName = () => `${randomChoice(SURNAMES)}${randomChoice(NAMES)}${Math.random() > 0.5 ? randomChoice(NAMES) : ''}`;
|
|
const generateTitle = () => `${randomChoice(TITLES_PREFIX)}${randomChoice(TITLES_SUFFIX)}`;
|
|
const generateDate = (startYear, endYear) => {
|
|
const year = randomInt(startYear, endYear);
|
|
const month = randomInt(0, 11);
|
|
const day = randomInt(1, 28);
|
|
return new Date(year, month, day);
|
|
};
|
|
|
|
async function seed() {
|
|
const client = await pool.connect();
|
|
try {
|
|
console.log('🚀 开始注入测试数据...');
|
|
|
|
// 0. 初始化数据库架构
|
|
console.log('🏗️ 初始化数据库架构...');
|
|
const schemaPath = path.join(__dirname, '../../database/schema.sql');
|
|
if (fs.existsSync(schemaPath)) {
|
|
const schemaSql = fs.readFileSync(schemaPath, 'utf8');
|
|
await client.query(schemaSql);
|
|
console.log('✅ 数据库架构已应用');
|
|
} else {
|
|
console.warn('⚠️ 未找到 schema.sql，跳过架构初始化');
|
|
}
|
|
|
|
// 1. 清理数据 (保留特定账号)
|
|
console.log('🧹 清理旧数据...');
|
|
await client.query('BEGIN');
|
|
|
|
// 级联删除所有成果 (这将自动删除详情表和附件表)
|
|
await client.query('TRUNCATE achievements CASCADE');
|
|
|
|
// 删除除保留账号外的所有用户
|
|
const keepUsers = ['18888888888', '19999999999'];
|
|
await client.query('DELETE FROM users WHERE username != $1 AND username != $2', [keepUsers[0], keepUsers[1]]);
|
|
|
|
console.log('✅ 旧数据清理完成');
|
|
|
|
// 2. 填充字典和部门
|
|
console.log('📚 填充字典数据...');
|
|
for (const [table, values] of Object.entries(DICTS)) {
|
|
for (const val of values) {
|
|
await client.query(`INSERT INTO ${table} (name) VALUES ($1) ON CONFLICT (name) DO NOTHING`, [val]);
|
|
}
|
|
}
|
|
|
|
for (const dept of DEPARTMENTS) {
|
|
await client.query(
|
|
'INSERT INTO departments (name) VALUES ($1) ON CONFLICT (name) DO NOTHING',
|
|
[dept]
|
|
);
|
|
}
|
|
|
|
// 3. 生成新用户
|
|
console.log('👥 生成测试用户...');
|
|
const passwordHash = await bcrypt.hash('123456', 10);
|
|
const userIds = [];
|
|
|
|
// 获取保留用户的ID
|
|
const existingUsers = await client.query('SELECT id FROM users WHERE username = $1 OR username = $2', [keepUsers[0], keepUsers[1]]);
|
|
existingUsers.rows.forEach(row => userIds.push(row.id));
|
|
|
|
// 生成 20 个新用户
|
|
for (let i = 0; i < 20; i++) {
|
|
const username = generatePhone();
|
|
const realName = generateName();
|
|
const dept = randomChoice(DEPARTMENTS);
|
|
|
|
try {
|
|
const res = await client.query(
|
|
'INSERT INTO users (username, password_hash, role, real_name, department, dept_status) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id',
|
|
[username, passwordHash, 'user', realName, dept, 'verified']
|
|
);
|
|
userIds.push(res.rows[0].id);
|
|
} catch (e) {
|
|
if (e.code !== '23505') console.error(e);
|
|
}
|
|
}
|
|
console.log(`✅ 已生成 ${userIds.length} 个用户`);
|
|
|
|
// 4. 生成成果数据
|
|
console.log('📊 生成成果数据 (目标 500+ 条)...');
|
|
const targetCount = 550;
|
|
let insertedCount = 0;
|
|
|
|
for (let i = 0; i < targetCount; i++) {
|
|
const userId = randomChoice(userIds);
|
|
const type = randomChoice(ACHIEVEMENT_TYPES);
|
|
const status = randomChoice(STATUSES);
|
|
const date = generateDate(2023, 2026);
|
|
const title = generateTitle();
|
|
const dept = randomChoice(DEPARTMENTS);
|
|
|
|
// 构造 contributors JSONB
|
|
const contributors = [
|
|
{ name: generateName(), phone: generatePhone(), isMain: true },
|
|
{ name: generateName(), phone: generatePhone(), isMain: false }
|
|
];
|
|
|
|
// 插入主表
|
|
const res = await client.query(
|
|
`INSERT INTO achievements
|
|
(user_id, type, name, contributors, assigned_departments, achievement_date, status, remarks, created_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`,
|
|
[
|
|
userId,
|
|
type,
|
|
title,
|
|
JSON.stringify(contributors),
|
|
[dept], // 数组格式
|
|
date,
|
|
status,
|
|
'自动生成的数据',
|
|
date
|
|
]
|
|
);
|
|
const achievementId = res.rows[0].id;
|
|
|
|
// 插入附件
|
|
await client.query(
|
|
'INSERT INTO achievement_attachments (achievement_id, file_name, file_path) VALUES ($1, $2, $3)',
|
|
[achievementId, 'test_report.pdf', 'uploads/mock_file.pdf']
|
|
);
|
|
|
|
// 插入详情表 (根据类型)
|
|
switch (type) {
|
|
case 'paper':
|
|
await client.query(
|
|
'INSERT INTO achievement_paper (achievement_id, paper_type, journal_name, publish_date) VALUES ($1, $2, $3, $4)',
|
|
[achievementId, randomChoice(DICTS.dict_paper_types), '计算机学报', date]
|
|
);
|
|
break;
|
|
case 'award':
|
|
await client.query(
|
|
'INSERT INTO achievement_award (achievement_id, award_type, award_level, award_unit) VALUES ($1, $2, $3, $4)',
|
|
[achievementId, randomChoice(DICTS.dict_award_types), randomChoice(DICTS.dict_award_levels), '科技部']
|
|
);
|
|
break;
|
|
case 'project':
|
|
await client.query(
|
|
'INSERT INTO achievement_project (achievement_id, project_category, source) VALUES ($1, $2, $3)',
|
|
[achievementId, randomChoice(DICTS.dict_project_categories), '国家自然科学基金委员会']
|
|
);
|
|
break;
|
|
case 'standard':
|
|
await client.query(
|
|
'INSERT INTO achievement_standard (achievement_id, standard_type, standard_no, implement_date) VALUES ($1, $2, $3, $4)',
|
|
[achievementId, randomChoice(DICTS.dict_standard_types), `GB/T ${randomInt(1000, 9999)}-2025`, date]
|
|
);
|
|
break;
|
|
// 其他类型暂略，或插入空记录
|
|
}
|
|
|
|
insertedCount++;
|
|
if (insertedCount % 100 === 0) process.stdout.write('.');
|
|
}
|
|
|
|
await client.query('COMMIT');
|
|
console.log(`\n✅ 成功注入 ${insertedCount} 条成果数据！`);
|
|
console.log('🎉 数据注入完成！');
|
|
|
|
} catch (e) {
|
|
await client.query('ROLLBACK');
|
|
console.error('❌ 数据注入失败:', e);
|
|
} finally {
|
|
client.release();
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
seed();
|