ChoChoX/backmanager-server
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
backmanager-server/db.js

173 lines
10 KiB
JavaScript
Raw Permalink Normal View History

init
2026-06-15 20:46:48 +08:00
// db.js —— 只做两件事：导出连接池；启动时建库/建表/插 admin
require('dotenv').config() //自动寻找.env文件，并把里面的键值对全部导入进来
const mysql = require('mysql2/promise')
const bcrypt = require('bcryptjs') //用来给密码做哈希加密
// ============ 1. 连接池 ============
// 业务代码里用 pool.query() / pool.execute()，会自动从池里取/还连接
const pool = mysql.createPool({
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT) || 3306,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
waitForConnections: true, // 池里没连接时排队等
connectionLimit: 10, // 最多 10 个连接
decimalNumbers: true,
})
// ============ 2. 启动时初始化 ============
// 思路：先用一个"无 database"的连接建库，再回到 pool 建表、插 admin
async function initDB() {
const { DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME } = process.env
// 2.1 建库（如果不存在）
const conn = await mysql.createConnection({
host: DB_HOST,
port: Number(DB_PORT) || 3306,
user: DB_USER,
password: DB_PASSWORD,
})
await conn.query(
`CREATE DATABASE IF NOT EXISTS \`${DB_NAME}\` DEFAULT CHARACTER SET utf8mb4`
)
await conn.end()
done
2026-06-22 20:48:29 +08:00
// 2.2 建表（如果不存在）
init
2026-06-15 20:46:48 +08:00
await pool.query(`
CREATE TABLE IF NOT EXISTS users (
done
2026-06-22 20:48:29 +08:00
id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID (主键)',
username VARCHAR(50) NOT NULL COMMENT '用户名 (登录账号)',
password VARCHAR(255) NOT NULL COMMENT '登录密码 (应存储加密后的值)',
real_name VARCHAR(50) DEFAULT NULL COMMENT '真实姓名',
role VARCHAR(20) NOT NULL DEFAULT 'user' COMMENT '角色: admin-管理员, user-普通用户',
status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '账号状态: 0-禁用, 1-启用',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表'
init
2026-06-15 20:46:48 +08:00
`)
done
2026-06-22 20:48:29 +08:00
await pool.query(`
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL AUTO_INCREMENT COMMENT '加盟商ID (主键)',
name VARCHAR(100) NOT NULL COMMENT '加盟商姓名',
phone VARCHAR(20) DEFAULT NULL COMMENT '联系电话',
province VARCHAR(50) DEFAULT NULL COMMENT '省',
city VARCHAR(50) DEFAULT NULL COMMENT '市',
district VARCHAR(50) DEFAULT NULL COMMENT '区',
address VARCHAR(200) DEFAULT NULL COMMENT '详细地址',
customer_type VARCHAR(20) DEFAULT 'Normal' COMMENT '客户类型: VIP-地区总代理, Normal-普通代理',
email VARCHAR(100) DEFAULT NULL COMMENT '电子邮箱',
remark TEXT DEFAULT NULL COMMENT '备注信息',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_customers_name (name),
KEY idx_customers_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加盟商信息表'
`)
// 确保 customer_type 列存在（兼容旧表）
try { await pool.query(`ALTER TABLE customers ADD COLUMN customer_type VARCHAR(20) DEFAULT 'Normal' COMMENT '客户类型: VIP-地区总代理, Normal-普通代理' AFTER address`) } catch {}
await pool.query(`
CREATE TABLE IF NOT EXISTS employees (
id INT NOT NULL AUTO_INCREMENT COMMENT '员工ID (主键)',
name VARCHAR(100) NOT NULL COMMENT '员工姓名',
gender VARCHAR(4) DEFAULT NULL COMMENT '性别: 男/女',
age INT DEFAULT NULL COMMENT '年龄',
education VARCHAR(50) DEFAULT NULL COMMENT '学历: 高中/专科/本科/硕士/博士',
department VARCHAR(100) DEFAULT NULL COMMENT '所属部门',
entry_date DATE DEFAULT NULL COMMENT '入职时间',
position VARCHAR(100) DEFAULT NULL COMMENT '职务/岗位',
salary DECIMAL(10,2) DEFAULT NULL COMMENT '工资金额',
phone VARCHAR(20) DEFAULT NULL COMMENT '联系电话',
email VARCHAR(100) DEFAULT NULL COMMENT '电子邮箱',
status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '在职状态: 0-离职, 1-在职',
remark TEXT DEFAULT NULL COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_employees_name (name),
KEY idx_employees_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表'
`)
await pool.query(`
CREATE TABLE IF NOT EXISTS products (
id INT NOT NULL AUTO_INCREMENT COMMENT '产品ID (主键)',
name VARCHAR(200) NOT NULL COMMENT '产品名称',
type VARCHAR(100) DEFAULT NULL COMMENT '产品类型/分类',
quantity INT NOT NULL DEFAULT 0 COMMENT '产品库存数量',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '产品单价',
unit VARCHAR(20) DEFAULT '件' COMMENT '计量单位',
specification VARCHAR(200) DEFAULT NULL COMMENT '产品规格/型号',
supplier VARCHAR(200) DEFAULT NULL COMMENT '供应商',
remark TEXT DEFAULT NULL COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_products_name (name),
KEY idx_products_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品信息表'
`)
// contracts 依赖 customers 和 employees，after_sales 同样依赖
await pool.query(`
CREATE TABLE IF NOT EXISTS contracts (
id INT NOT NULL AUTO_INCREMENT COMMENT '合同ID (主键)',
customer_id INT NOT NULL COMMENT '客户ID (关联客户表)',
contract_name VARCHAR(200) NOT NULL COMMENT '合同名称',
contract_no VARCHAR(100) DEFAULT NULL COMMENT '合同编号',
contract_content TEXT DEFAULT NULL COMMENT '合同内容/条款',
amount DECIMAL(12,2) DEFAULT NULL COMMENT '合同金额',
effective_date DATE DEFAULT NULL COMMENT '合同生效日期',
expiry_date DATE DEFAULT NULL COMMENT '合同有效期 (截止日期)',
employee_id INT DEFAULT NULL COMMENT '业务员ID (关联员工表)',
status VARCHAR(20) NOT NULL DEFAULT '生效' COMMENT '合同状态: 草稿/生效/完成/作废',
remark TEXT DEFAULT NULL COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_contracts_customer_id (customer_id),
KEY idx_contracts_employee_id (employee_id),
KEY idx_contracts_effective_date (effective_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同信息表'
`)
await pool.query(`
CREATE TABLE IF NOT EXISTS after_sales (
id INT NOT NULL AUTO_INCREMENT COMMENT '售后记录ID (主键)',
customer_id INT NOT NULL COMMENT '客户ID (关联客户表)',
feedback TEXT NOT NULL COMMENT '客户反馈意见/售后内容',
employee_id INT DEFAULT NULL COMMENT '处理业务员ID (关联员工表)',
handle_method TEXT DEFAULT NULL COMMENT '处理方式/解决方案',
handle_status VARCHAR(20) NOT NULL DEFAULT '待处理' COMMENT '处理状态: 待处理/处理中/已完成',
service_date DATE DEFAULT NULL COMMENT '售后日期',
remark TEXT DEFAULT NULL COMMENT '备注',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_after_sales_customer_id (customer_id),
KEY idx_after_sales_employee_id (employee_id),
KEY idx_after_sales_status (handle_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='售后信息表'
`)
console.log('[init] 数据表初始化完成')
init
2026-06-15 20:46:48 +08:00
// 2.3 插 admin（仅当不存在时）
const [rows] = await pool.query(
'SELECT id FROM users WHERE username = ?',
['admin']
)
if (rows.length === 0) {
const hash = await bcrypt.hash('123456', 10)
await pool.query(
done
2026-06-22 20:48:29 +08:00
'INSERT INTO users (username, password, real_name, role) VALUES (?, ?, ?, ?)',
init
2026-06-15 20:46:48 +08:00
['admin', hash, '超级管理员', 'admin']
)
console.log('[init] 已创建默认账号 admin / 123456')
} else {
console.log('[init] admin 已存在，跳过')
}
}
module.exports = { pool, initDB }
Reference in New Issue Copy Permalink