/**
|
* SQL语句定义文件
|
* 包含创建表的SQL语句和其他常用SQL查询
|
*/
|
|
/**
|
* id:主键
|
* username:用户名
|
* password:密码
|
* real_name:真实姓名
|
* role:角色
|
* last_login_time:上次登录事件
|
*/
|
// 创建用户表
|
const createUserTable = `
|
CREATE TABLE IF NOT EXISTS users (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
username TEXT NOT NULL UNIQUE,
|
password TEXT NOT NULL,
|
real_name TEXT,
|
role TEXT DEFAULT 'user',
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
last_login_time TIMESTAMP
|
)`;
|
|
/**
|
* id:主键
|
* orderNo:订单号
|
* status:状态
|
* originalDoc:原始单据
|
* customer:客户
|
* createUserId:创建人id
|
* createTime:创建时间
|
* completeTime:完成时间
|
* remark:备注
|
*/
|
// 创建订单表
|
const createOrderTable = `
|
CREATE TABLE IF NOT EXISTS orders (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
orderNo TEXT NOT NULL UNIQUE,
|
status TEXT DEFAULT 'pedding',
|
originalDoc TEXT,
|
customer TEXT,
|
createUserId INTEGER,
|
createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
completeTime TIMESTAMP,
|
remark TEXT
|
)`;
|
|
/**
|
* id:主键
|
* name:物料名称
|
* create_time:创建时间
|
*/
|
// 创建核心物料表
|
const createCoreMaterialTable = `
|
CREATE TABLE IF NOT EXISTS core_materials (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
name TEXT NOT NULL,
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
)`;
|
|
/**
|
* id:主键
|
* orderNo:订单号
|
* material_name:物料名称
|
* tab_order:tab排序
|
*/
|
// 创建订单物料关联表
|
const createOrderMaterialTable = `
|
CREATE TABLE IF NOT EXISTS order_materials (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
orderNo TEXT NOT NULL,
|
material_name TEXT NOT NULL,
|
tab_order INTEGER
|
)`;
|
|
/**
|
* id:主键
|
* orderNo:订单号
|
* material_name:物料名称
|
* reference_content:标定信息
|
* reference_content_index:标定信息索引
|
* original_content:二维码原始信息
|
*/
|
//物料标定信息表
|
const createMaterialContentTable = `
|
CREATE TABLE IF NOT EXISTS materials_content (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
orderNo TEXT NOT NULL,
|
material_name TEXT NOT NULL,
|
reference_content TEXT,
|
reference_content_index TEXT,
|
original_content TEXT
|
)`;
|
|
/**
|
* id:主键
|
* orderNo:订单号
|
* materialName:物料名称
|
* scan_content:扫描信息
|
* reference_content:标定信息
|
* is_matched:是否匹配
|
* scan_time:扫描时间
|
*/
|
// 创建扫描记录表
|
const createScanRecordTable = `
|
CREATE TABLE IF NOT EXISTS scan_records (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
orderNo TEXT NOT NULL,
|
materialName TEXT NOT NULL,
|
scan_content TEXT NOT NULL,
|
reference_content TEXT,
|
is_matched BOOLEAN,
|
scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
)`;
|
|
// 创建激活信息表(暂时无用)
|
const createActivationTable = `
|
CREATE TABLE IF NOT EXISTS activation_info (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
device_uuid TEXT NOT NULL UNIQUE,
|
device_info TEXT,
|
runtime_code TEXT,
|
activation_code TEXT,
|
is_activated BOOLEAN DEFAULT 0,
|
trial_start_date DATE,
|
trial_end_date DATE,
|
activation_date DATE,
|
expiration_date DATE
|
)`;
|
|
// 创建系统设置表(暂时无用)
|
const createSettingsTable = `
|
CREATE TABLE IF NOT EXISTS settings (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
setting_key TEXT NOT NULL UNIQUE,
|
setting_value TEXT,
|
setting_desc TEXT,
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
)`;
|
|
/**
|
* id:主键
|
* seq:序号
|
* date:时间
|
*/
|
//序列表
|
const createSeqTable = `
|
CREATE TABLE IF NOT EXISTS seq (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
seq INTEGER NOT NULL,
|
date TEXT NOT NULL
|
)`;
|
|
/**
|
* id:主键
|
* customerName:客户名称
|
* create_time:创建时间
|
*/
|
//客户表
|
const createCustomerTable = `
|
CREATE TABLE IF NOT EXISTS customers (
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
customerName TEXT NOT NULL,
|
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
)`;
|
|
// 初始化系统设置
|
const initSettings = `
|
INSERT OR IGNORE INTO settings (setting_key, setting_value, setting_desc) VALUES
|
('retention_days', '90', '订单数据保留天数')
|
`;
|
|
// 初始化管理员账户
|
const initAdminUser = `
|
INSERT OR IGNORE INTO users (username, password, real_name, role) VALUES
|
('admin', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', '系统管理员', 'admin')
|
`;
|
|
const initSeq = `
|
INSERT OR IGNORE INTO seq (seq, date) VALUES (1, CURRENT_DATE)
|
`
|
|
// 导出所有SQL语句
|
export const createTables = [
|
createUserTable,
|
createOrderTable,
|
createCoreMaterialTable,
|
createOrderMaterialTable,
|
createScanRecordTable,
|
createActivationTable,
|
createSettingsTable,
|
createMaterialContentTable,
|
createSeqTable,
|
createCustomerTable
|
];
|
|
// 导出初始化数据的SQL语句
|
export const initData = [
|
initSettings,
|
initAdminUser,
|
initSeq
|
];
|
|
// 常用查询语句
|
export const queries = {
|
// 用户相关
|
getUserByUsername: 'SELECT * FROM users WHERE username = ?',
|
updateUserLastLogin: 'UPDATE users SET last_login_time = CURRENT_TIMESTAMP WHERE id = ?',
|
|
// 订单相关
|
createOrder: 'INSERT INTO orders (order_no, order_name, create_user_id) VALUES (?, ?, ?)',
|
updateOrderStatus: 'UPDATE orders SET order_status = ? WHERE id = ?',
|
getOrderById: 'SELECT * FROM orders WHERE id = ?',
|
getOrderByNo: 'SELECT * FROM orders WHERE order_no = ?',
|
getRecentOrders: 'SELECT * FROM orders ORDER BY create_time DESC LIMIT ?',
|
|
// 核心物料相关
|
getAllMaterials: 'SELECT * FROM core_materials ORDER BY last_used_time DESC',
|
getMaterialById: 'SELECT * FROM core_materials WHERE id = ?',
|
getMaterialByCode: 'SELECT * FROM core_materials WHERE material_code = ?',
|
updateMaterialLastUsed: 'UPDATE core_materials SET last_used_time = CURRENT_TIMESTAMP WHERE id = ?',
|
|
// 扫描记录相关
|
addScanRecord: 'INSERT INTO scan_records (order_id, material_id, scan_content, reference_content, is_matched, scan_user_id) VALUES (?, ?, ?, ?, ?, ?)',
|
getScanRecordsByOrderId: 'SELECT * FROM scan_records WHERE order_id = ? ORDER BY scan_time DESC',
|
|
// 激活相关
|
getActivationInfo: 'SELECT * FROM activation_info WHERE device_uuid = ?',
|
updateActivationCode: 'UPDATE activation_info SET activation_code = ?, is_activated = 1, activation_date = CURRENT_DATE, expiration_date = ? WHERE device_uuid = ?'
|
};
|
