107 lines
5.8 KiB
JavaScript
107 lines
5.8 KiB
JavaScript
const mysql = require('mysql2');
|
||
|
||
async function updateDatabase() {
|
||
const connection = mysql.createConnection({
|
||
host: process.env.DB_HOST || 'nj-cdb-3pwh2kz1.sql.tencentcdb.com',
|
||
port: process.env.DB_PORT || 20784,
|
||
user: process.env.DB_USER || 'jiebanke',
|
||
password: process.env.DB_PASSWORD || 'aiot741$12346',
|
||
database: process.env.DB_NAME || 'niumall',
|
||
multipleStatements: true
|
||
});
|
||
|
||
try {
|
||
console.log('连接到远程数据库...');
|
||
|
||
// 检查并更新表结构（如果有差异）
|
||
console.log('检查表结构差异...');
|
||
|
||
// 这里可以根据数据库设计文档添加具体的ALTER TABLE语句
|
||
// 目前表结构基本完整，主要工作是生成测试数据
|
||
|
||
// 生成测试数据
|
||
console.log('生成测试数据...');
|
||
await generateTestData(connection);
|
||
|
||
console.log('数据库更新完成！');
|
||
|
||
} catch (error) {
|
||
console.error('数据库操作错误:', error.message);
|
||
} finally {
|
||
await connection.end();
|
||
}
|
||
}
|
||
|
||
async function generateTestData(connection) {
|
||
const promises = [];
|
||
|
||
// 清空现有测试数据（可选）
|
||
console.log('清空现有测试数据...');
|
||
const truncateQueries = [
|
||
'DELETE FROM orders WHERE id < 1000',
|
||
'DELETE FROM payments WHERE id < 1000',
|
||
'DELETE FROM quality_inspections WHERE id < 1000',
|
||
'DELETE FROM quality_records WHERE id < 1000',
|
||
'DELETE FROM settlements WHERE id < 1000',
|
||
'DELETE FROM transport_tasks WHERE id < 1000',
|
||
'DELETE FROM transport_tracks WHERE id < 1000',
|
||
'DELETE FROM transports WHERE id < 1000'
|
||
];
|
||
|
||
for (const query of truncateQueries) {
|
||
promises.push(connection.promise().query(query).catch(console.error));
|
||
}
|
||
await Promise.all(promises);
|
||
promises.length = 0;
|
||
|
||
// 生成供应商测试数据
|
||
console.log('生成供应商数据...');
|
||
const supplierData = [
|
||
[1, '张氏牧场', '张三', '13800138001', '河南省郑州市金水区', '豫A12345', '大型现代化牧场', 'active', '2024-01-15 08:00:00', '2024-01-15 08:00:00'],
|
||
[2, '李氏养殖', '李四', '13800138002', '山东省济南市历下区', '鲁B54321', '专业肉牛养殖', 'active', '2024-01-15 08:00:00', '2024-01-15 08:00:00'],
|
||
[3, '王庄牛业', '王五', '13800138003', '河北省石家庄市长安区', '冀C98765', '家族式养殖企业', 'active', '2024-01-15 08:00:00', '2024-01-15 08:00:00']
|
||
];
|
||
|
||
for (const data of supplierData) {
|
||
promises.push(connection.promise().query(
|
||
'INSERT INTO suppliers (id, name, contact_person, contact_phone, address, license_number, description, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at)',
|
||
data
|
||
).catch(console.error));
|
||
}
|
||
|
||
// 生成司机测试数据
|
||
console.log('生成司机数据...');
|
||
const driverData = [
|
||
[1, '赵司机', '13800138111', '410105198001011234', 'A12345678901', 'A1', '2025-12-31', 'D123456', '2025-12-31', '赵夫人', '13800138112', null, 'full_time', '2024-01-01', 8000.00, 4.50, 'active', '经验丰富的老司机', '2024-01-15 08:00:00', '2024-01-15 08:00:00'],
|
||
[2, '钱师傅', '13800138113', '410105198002022345', 'B12345678902', 'B2', '2025-11-30', 'D234567', '2025-11-30', '钱夫人', '13800138114', null, 'contract', '2024-02-01', 7500.00, 4.20, 'active', '擅长长途运输', '2024-01-15 08:00:00', '2024-01-15 08:00:00'],
|
||
[3, '孙司机', '13800138115', '410105198003033456', 'C12345678903', 'C1', '2025-10-31', 'D345678', '2025-10-31', '孙夫人', '13800138116', null, 'part_time', null, 6000.00, 4.00, 'active', '兼职司机', '2024-01-15 08:00:00', '2024-01-15 08:00:00']
|
||
];
|
||
|
||
for (const data of driverData) {
|
||
promises.push(connection.promise().query(
|
||
'INSERT INTO drivers (id, name, phone, id_card, driver_license, license_type, license_expiry_date, qualification_certificate, qualification_expiry_date, emergency_contact, emergency_phone, current_vehicle_id, employment_type, hire_date, salary, performance_rating, status, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at)',
|
||
data
|
||
).catch(console.error));
|
||
}
|
||
|
||
// 生成订单测试数据
|
||
console.log('生成订单数据...');
|
||
const orderData = [
|
||
[1, 'ORDER20240115001', 1, '牛牛采购公司', 1, '张氏牧场', null, null, '西门塔尔', 20, 10000.00, 9800.00, 45.50, 445900.00, '河南省郑州市金水区农业路1号', '2024-01-20', 'bank_transfer', 'paid', 'delivered', '首批测试订单', '2024-01-15 09:00:00', '2024-01-15 09:00:00'],
|
||
[2, 'ORDER20240115002', 1, '牛牛采购公司', 2, '李氏养殖', null, null, '安格斯', 15, 7500.00, 7600.00, 48.00, 364800.00, '山东省济南市历下区经十路2号', '2024-01-22', 'online_payment', 'partial_paid', 'confirmed', '优质安格斯牛', '2024-01-15 10:00:00', '2024-01-15 10:00:00'],
|
||
[3, 'ORDER20240115003', 1, '牛牛采购公司', 3, '王庄牛业', null, null, '本地黄牛', 25, 6250.00, null, 42.00, 262500.00, '河北省石家庄市长安区中山路3号', '2024-01-25', 'cash', 'unpaid', 'pending', '传统黄牛品种', '2024-01-15 11:00:00', '2024-01-15 11:00:00']
|
||
];
|
||
|
||
for (const data of orderData) {
|
||
promises.push(connection.promise().query(
|
||
'INSERT INTO orders (id, orderNo, buyerId, buyerName, supplierId, supplierName, traderId, traderName, cattleBreed, cattleCount, expectedWeight, actualWeight, unitPrice, totalAmount, deliveryAddress, deliveryDate, paymentMethod, paymentStatus, orderStatus, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at)',
|
||
data
|
||
).catch(console.error));
|
||
}
|
||
|
||
// 等待所有插入操作完成
|
||
await Promise.all(promises);
|
||
console.log('测试数据生成完成！共生成3条供应商、3条司机、3条订单数据');
|
||
}
|
||
|
||
updateDatabase(); |