722 lines
24 KiB
JavaScript
722 lines
24 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
|
|
class DatabaseManager {
|
|
constructor() {
|
|
this.db = null;
|
|
}
|
|
|
|
init() {
|
|
// Ensure data directory exists
|
|
const dataDir = path.join(__dirname, 'data');
|
|
if (!fs.existsSync(dataDir)) {
|
|
fs.mkdirSync(dataDir, { recursive: true });
|
|
}
|
|
|
|
const dbPath = path.join(dataDir, 'kitchen.db');
|
|
this.db = new Database(dbPath);
|
|
this.db.pragma('journal_mode = WAL');
|
|
|
|
this.createTables();
|
|
console.log('Database initialized successfully');
|
|
}
|
|
|
|
createTables() {
|
|
// Config table
|
|
this.db.exec(`
|
|
CREATE TABLE IF NOT EXISTS config (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT UNIQUE NOT NULL,
|
|
value TEXT
|
|
)
|
|
`);
|
|
|
|
// Orders table
|
|
this.db.exec(`
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER UNIQUE NOT NULL,
|
|
bot_id INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
local_status TEXT,
|
|
order_data TEXT NOT NULL,
|
|
customer_data TEXT NOT NULL,
|
|
total_amount REAL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
printed_at INTEGER,
|
|
synced_at INTEGER
|
|
)
|
|
`);
|
|
|
|
// Print queue table
|
|
this.db.exec(`
|
|
CREATE TABLE IF NOT EXISTS print_queue (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER NOT NULL,
|
|
print_type TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
created_at INTEGER NOT NULL,
|
|
printed_at INTEGER,
|
|
FOREIGN KEY (order_id) REFERENCES orders(order_id)
|
|
)
|
|
`);
|
|
|
|
// Printers table - stores individual printer configurations
|
|
this.db.exec(`
|
|
CREATE TABLE IF NOT EXISTS printers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
interface TEXT NOT NULL,
|
|
printer_type TEXT NOT NULL DEFAULT 'epson',
|
|
paper_width INTEGER NOT NULL DEFAULT 48,
|
|
paper_format TEXT NOT NULL DEFAULT '80mm',
|
|
is_default INTEGER NOT NULL DEFAULT 0,
|
|
is_enabled INTEGER NOT NULL DEFAULT 1,
|
|
font_size TEXT DEFAULT 'normal',
|
|
line_style TEXT DEFAULT 'single',
|
|
qr_code_enabled INTEGER DEFAULT 1,
|
|
qr_code_size INTEGER DEFAULT 3,
|
|
qr_code_correction TEXT DEFAULT 'M',
|
|
qr_code_content_template TEXT DEFAULT 'ORDER-{id}',
|
|
header_text TEXT DEFAULT 'KITCHEN ORDER',
|
|
footer_text TEXT DEFAULT 'Thank you!',
|
|
business_name TEXT,
|
|
business_address TEXT,
|
|
business_phone TEXT,
|
|
business_website TEXT,
|
|
business_email TEXT,
|
|
business_contact_size TEXT DEFAULT 'normal',
|
|
show_customer_info INTEGER DEFAULT 1,
|
|
show_order_items INTEGER DEFAULT 1,
|
|
show_prices INTEGER DEFAULT 1,
|
|
show_timestamps INTEGER DEFAULT 1,
|
|
logo_path TEXT,
|
|
logo_max_width_dots INTEGER,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Create indexes
|
|
this.db.exec(`
|
|
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_local_status ON orders(local_status);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_created ON orders(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_print_queue_status ON print_queue(status);
|
|
CREATE INDEX IF NOT EXISTS idx_printers_enabled ON printers(is_enabled);
|
|
CREATE INDEX IF NOT EXISTS idx_printers_default ON printers(is_default);
|
|
`);
|
|
|
|
// Initialize default config values if not exists
|
|
this.setConfigDefaults();
|
|
|
|
// Migrate old printer config to new table if needed
|
|
this.migrateOldPrinterConfig();
|
|
}
|
|
|
|
setConfigDefaults() {
|
|
const defaults = {
|
|
apiUrl: 'https://api.thinklink.ai',
|
|
pollingInterval: '15000',
|
|
dashboardRefreshInterval: '10000',
|
|
printerType: 'epson',
|
|
printerInterface: 'usb',
|
|
printerPath: '/dev/usb/lp0',
|
|
printerWidth: '48',
|
|
fontSize: 'normal',
|
|
qrCodeEnabled: 'true',
|
|
headerText: 'KITCHEN ORDER',
|
|
footerText: 'Thank you!',
|
|
showOrderStats: 'true',
|
|
lineStyle: 'single',
|
|
printMargins: JSON.stringify({ left: 0, right: 0 }),
|
|
showCustomerInfo: 'true',
|
|
showOrderItems: 'true',
|
|
showPrices: 'true',
|
|
showTimestamps: 'true'
|
|
};
|
|
|
|
const insert = this.db.prepare('INSERT OR IGNORE INTO config (key, value) VALUES (?, ?)');
|
|
for (const [key, value] of Object.entries(defaults)) {
|
|
insert.run(key, value);
|
|
}
|
|
}
|
|
|
|
// Config operations
|
|
getConfig(key = null) {
|
|
if (key) {
|
|
const row = this.db.prepare('SELECT value FROM config WHERE key = ?').get(key);
|
|
return row ? row.value : null;
|
|
}
|
|
|
|
const rows = this.db.prepare('SELECT key, value FROM config').all();
|
|
const config = {};
|
|
for (const row of rows) {
|
|
config[row.key] = row.value;
|
|
}
|
|
return config;
|
|
}
|
|
|
|
setConfig(key, value) {
|
|
this.db.prepare('INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)').run(key, value);
|
|
}
|
|
|
|
setConfigMultiple(configObj) {
|
|
const insert = this.db.prepare('INSERT OR REPLACE INTO config (key, value) VALUES (?, ?)');
|
|
const transaction = this.db.transaction(() => {
|
|
for (const [key, value] of Object.entries(configObj)) {
|
|
insert.run(key, value);
|
|
}
|
|
});
|
|
transaction();
|
|
}
|
|
|
|
// Order operations
|
|
insertOrder(order) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
return this.db.prepare(`
|
|
INSERT INTO orders (order_id, bot_id, status, local_status, order_data, customer_data, total_amount, created_at, updated_at, synced_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`).run(
|
|
order.id,
|
|
order.botId,
|
|
order.status,
|
|
'new',
|
|
JSON.stringify(order.order),
|
|
JSON.stringify(order.customer),
|
|
order.totalAmount,
|
|
order.createdAt,
|
|
order.updatedAt,
|
|
now
|
|
);
|
|
}
|
|
|
|
getOrderById(orderId) {
|
|
const row = this.db.prepare(`
|
|
SELECT * FROM orders WHERE order_id = ?
|
|
`).get(orderId);
|
|
|
|
if (!row) return null;
|
|
|
|
return {
|
|
id: row.order_id,
|
|
botId: row.bot_id,
|
|
status: row.status,
|
|
localStatus: row.local_status,
|
|
order: JSON.parse(row.order_data),
|
|
customer: JSON.parse(row.customer_data),
|
|
totalAmount: row.total_amount,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at,
|
|
printedAt: row.printed_at,
|
|
syncedAt: row.synced_at
|
|
};
|
|
}
|
|
|
|
getLastOrder() {
|
|
const row = this.db.prepare(`
|
|
SELECT * FROM orders ORDER BY order_id DESC LIMIT 1
|
|
`).get();
|
|
|
|
if (!row) return null;
|
|
|
|
return {
|
|
order_id: row.order_id,
|
|
botId: row.bot_id,
|
|
status: row.status,
|
|
localStatus: row.local_status
|
|
};
|
|
}
|
|
|
|
updateOrder(order) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
return this.db.prepare(`
|
|
UPDATE orders
|
|
SET status = ?, order_data = ?, customer_data = ?, total_amount = ?, updated_at = ?, synced_at = ?
|
|
WHERE order_id = ?
|
|
`).run(
|
|
order.status,
|
|
JSON.stringify(order.order),
|
|
JSON.stringify(order.customer),
|
|
order.totalAmount,
|
|
order.updatedAt,
|
|
now,
|
|
order.id
|
|
);
|
|
}
|
|
|
|
updateOrderStatus(orderId, localStatus) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
return this.db.prepare(`
|
|
UPDATE orders SET local_status = ?, updated_at = ? WHERE order_id = ?
|
|
`).run(localStatus, now, orderId);
|
|
}
|
|
|
|
markOrderPrinted(orderId) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
return this.db.prepare(`
|
|
UPDATE orders SET printed_at = ? WHERE order_id = ?
|
|
`).run(now, orderId);
|
|
}
|
|
|
|
getOrders(filters = {}) {
|
|
let query = 'SELECT * FROM orders WHERE 1=1';
|
|
const params = [];
|
|
|
|
if (filters.status) {
|
|
query += ' AND local_status = ?';
|
|
params.push(filters.status);
|
|
}
|
|
|
|
if (filters.date) {
|
|
// Get orders from start of day to end of day
|
|
const startOfDay = Math.floor(new Date(filters.date).setHours(0, 0, 0, 0) / 1000);
|
|
const endOfDay = Math.floor(new Date(filters.date).setHours(23, 59, 59, 999) / 1000);
|
|
query += ' AND created_at BETWEEN ? AND ?';
|
|
params.push(startOfDay, endOfDay);
|
|
}
|
|
|
|
query += ' ORDER BY created_at DESC';
|
|
|
|
if (filters.limit) {
|
|
query += ' LIMIT ?';
|
|
params.push(filters.limit);
|
|
}
|
|
|
|
const rows = this.db.prepare(query).all(...params);
|
|
|
|
return rows.map(row => ({
|
|
id: row.order_id,
|
|
botId: row.bot_id,
|
|
status: row.status,
|
|
localStatus: row.local_status,
|
|
order: JSON.parse(row.order_data),
|
|
customer: JSON.parse(row.customer_data),
|
|
totalAmount: row.total_amount,
|
|
createdAt: row.created_at,
|
|
updatedAt: row.updated_at,
|
|
printedAt: row.printed_at,
|
|
syncedAt: row.synced_at
|
|
}));
|
|
}
|
|
|
|
getOrderStats() {
|
|
const today = Math.floor(new Date().setHours(0, 0, 0, 0) / 1000);
|
|
|
|
const stats = {
|
|
total: 0,
|
|
new: 0,
|
|
preparing: 0,
|
|
ready: 0
|
|
};
|
|
|
|
const rows = this.db.prepare(`
|
|
SELECT local_status, COUNT(*) as count
|
|
FROM orders
|
|
WHERE created_at >= ?
|
|
GROUP BY local_status
|
|
`).all(today);
|
|
|
|
for (const row of rows) {
|
|
if (row.local_status === 'new') stats.new = row.count;
|
|
else if (row.local_status === 'preparing') stats.preparing = row.count;
|
|
else if (row.local_status === 'ready') stats.ready = row.count;
|
|
stats.total += row.count;
|
|
}
|
|
|
|
return stats;
|
|
}
|
|
|
|
// Print queue operations
|
|
addToPrintQueue(orderId, printType) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
const recentWindowSeconds = 300; // 5 minutes window to prevent duplicates
|
|
const cutoff = now - recentWindowSeconds;
|
|
|
|
// Enhanced idempotency: check for any recent job (pending, processing, or recently completed)
|
|
// Apply time window to pending/processing as well to avoid stale blocks
|
|
const existing = this.db.prepare(`
|
|
SELECT id, status, created_at FROM print_queue
|
|
WHERE order_id = ? AND print_type = ?
|
|
AND ((status IN ('pending','processing') AND created_at > ?) OR (status = 'completed' AND created_at > ?))
|
|
ORDER BY id DESC LIMIT 1
|
|
`).get(orderId, printType, cutoff, cutoff);
|
|
|
|
if (existing && existing.id) {
|
|
// If there's a pending or processing job, reuse it
|
|
if (existing.status === 'pending' || existing.status === 'processing') {
|
|
console.log(`[DB] Reusing existing ${existing.status} job ${existing.id} for order #${orderId} (${printType})`);
|
|
return existing.id;
|
|
}
|
|
// If there's a recently completed job, allow creating a new job for audit
|
|
if (existing.status === 'completed') {
|
|
console.log(`[DB] Recent completed job ${existing.id} exists for order #${orderId} (${printType}); creating a new queued job.`);
|
|
}
|
|
}
|
|
|
|
console.log(`[DB] Creating new print job for order #${orderId} (${printType})`);
|
|
const info = this.db.prepare(`
|
|
INSERT INTO print_queue (order_id, print_type, status, created_at)
|
|
VALUES (?, ?, 'pending', ?)
|
|
`).run(orderId, printType, now);
|
|
return info && info.lastInsertRowid ? info.lastInsertRowid : null;
|
|
}
|
|
|
|
getPendingPrintJobs() {
|
|
return this.db.prepare(`
|
|
SELECT * FROM print_queue WHERE status = 'pending' ORDER BY created_at ASC
|
|
`).all();
|
|
}
|
|
|
|
markPrintJobCompleted(id) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
return this.db.prepare(`
|
|
UPDATE print_queue SET status = 'completed', printed_at = ? WHERE id = ?
|
|
`).run(now, id);
|
|
}
|
|
|
|
markPrintJobFailed(id) {
|
|
return this.db.prepare(`
|
|
UPDATE print_queue SET status = 'failed' WHERE id = ?
|
|
`).run(id);
|
|
}
|
|
|
|
// Transition a job to processing to prevent the worker from picking it up concurrently
|
|
markPrintJobProcessing(id) {
|
|
return this.db.prepare(`
|
|
UPDATE print_queue SET status = 'processing' WHERE id = ?
|
|
`).run(id);
|
|
}
|
|
|
|
// Revert a job back to pending (e.g., if immediate print failed) so the worker can retry
|
|
markPrintJobPending(id) {
|
|
return this.db.prepare(`
|
|
UPDATE print_queue SET status = 'pending' WHERE id = ?
|
|
`).run(id);
|
|
}
|
|
|
|
hasPrintedCancellation(orderId) {
|
|
const row = this.db.prepare(`
|
|
SELECT 1 FROM print_queue
|
|
WHERE order_id = ? AND print_type = 'canceled' AND status = 'completed'
|
|
LIMIT 1
|
|
`).get(orderId);
|
|
return !!row;
|
|
}
|
|
|
|
// Cleanup other jobs for the same order+type when one succeeds
|
|
// This prevents duplicate prints from multiple pending jobs
|
|
cleanupDuplicateJobs(successfulJobId, orderId, printType) {
|
|
const result = this.db.prepare(`
|
|
UPDATE print_queue
|
|
SET status = 'completed', printed_at = ?
|
|
WHERE order_id = ? AND print_type = ?
|
|
AND id != ?
|
|
AND status IN ('pending', 'processing')
|
|
`).run(Math.floor(Date.now() / 1000), orderId, printType, successfulJobId);
|
|
|
|
if (result.changes > 0) {
|
|
console.log(`[DB] Cleaned up ${result.changes} duplicate job(s) for order #${orderId} (${printType})`);
|
|
}
|
|
return result.changes;
|
|
}
|
|
|
|
// Check if there's any active or recently completed job for order+type
|
|
hasActiveOrRecentJob(orderId, printType, windowSeconds = 60) {
|
|
const cutoff = Math.floor(Date.now() / 1000) - windowSeconds;
|
|
const row = this.db.prepare(`
|
|
SELECT id, status FROM print_queue
|
|
WHERE order_id = ? AND print_type = ?
|
|
AND ((status IN ('processing', 'pending') AND created_at > ?) OR (status = 'completed' AND printed_at > ?))
|
|
ORDER BY id DESC LIMIT 1
|
|
`).get(orderId, printType, cutoff, cutoff);
|
|
return row ? { hasActive: true, jobId: row.id, status: row.status } : { hasActive: false };
|
|
}
|
|
|
|
// Reset stale 'processing' jobs to 'pending' so the worker can pick them up
|
|
// Useful when immediate prints failed (e.g., printer was offline) and jobs got stuck
|
|
resetStuckProcessingJobs(maxAgeSeconds = 120) {
|
|
const cutoff = Math.floor(Date.now() / 1000) - maxAgeSeconds;
|
|
const result = this.db.prepare(`
|
|
UPDATE print_queue
|
|
SET status = 'pending'
|
|
WHERE status = 'processing' AND created_at < ?
|
|
`).run(cutoff);
|
|
if (result && result.changes > 0) {
|
|
console.log(`[DB] Recovered ${result.changes} stuck processing job(s) to pending`);
|
|
}
|
|
return result && result.changes ? result.changes : 0;
|
|
}
|
|
|
|
// Get count of recently completed jobs for debugging
|
|
getRecentlyCompletedJobCount(orderId, printType, windowSeconds = 300) {
|
|
const cutoff = Math.floor(Date.now() / 1000) - windowSeconds;
|
|
const row = this.db.prepare(`
|
|
SELECT COUNT(*) as count FROM print_queue
|
|
WHERE order_id = ? AND print_type = ?
|
|
AND status = 'completed'
|
|
AND printed_at > ?
|
|
`).get(orderId, printType, cutoff);
|
|
return row ? row.count : 0;
|
|
}
|
|
|
|
// Printer CRUD operations
|
|
addPrinter(config) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
const stmt = this.db.prepare(`
|
|
INSERT INTO printers (
|
|
name, type, interface, printer_type, paper_width, paper_format,
|
|
is_default, is_enabled, font_size, line_style,
|
|
qr_code_enabled, qr_code_size, qr_code_correction, qr_code_content_template,
|
|
header_text, footer_text,
|
|
business_name, business_address, business_phone, business_website, business_email,
|
|
business_contact_size, show_customer_info, show_order_items, show_prices, show_timestamps,
|
|
logo_path, logo_max_width_dots, created_at, updated_at
|
|
) VALUES (
|
|
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
|
|
)
|
|
`);
|
|
|
|
const info = stmt.run(
|
|
config.name || 'Unnamed Printer',
|
|
config.type || 'usb',
|
|
config.interface || '',
|
|
config.printer_type || 'epson',
|
|
config.paper_width || 48,
|
|
config.paper_format || '80mm',
|
|
config.is_default ? 1 : 0,
|
|
config.is_enabled !== false ? 1 : 0,
|
|
config.font_size || 'normal',
|
|
config.line_style || 'single',
|
|
config.qr_code_enabled !== false ? 1 : 0,
|
|
config.qr_code_size || 3,
|
|
config.qr_code_correction || 'M',
|
|
config.qr_code_content_template || 'ORDER-{id}',
|
|
(typeof config.header_text !== 'undefined' ? config.header_text : 'KITCHEN ORDER'),
|
|
(typeof config.footer_text !== 'undefined' ? config.footer_text : 'Thank you!'),
|
|
config.business_name || '',
|
|
config.business_address || '',
|
|
config.business_phone || '',
|
|
config.business_website || '',
|
|
config.business_email || '',
|
|
config.business_contact_size || 'normal',
|
|
config.show_customer_info !== false ? 1 : 0,
|
|
config.show_order_items !== false ? 1 : 0,
|
|
config.show_prices !== false ? 1 : 0,
|
|
config.show_timestamps !== false ? 1 : 0,
|
|
config.logo_path || null,
|
|
config.logo_max_width_dots || null,
|
|
now,
|
|
now
|
|
);
|
|
|
|
// If this is set as default, unset other defaults
|
|
if (config.is_default) {
|
|
this.db.prepare('UPDATE printers SET is_default = 0 WHERE id != ?').run(info.lastInsertRowid);
|
|
}
|
|
|
|
return info.lastInsertRowid;
|
|
}
|
|
|
|
updatePrinter(id, config) {
|
|
const now = Math.floor(Date.now() / 1000);
|
|
const stmt = this.db.prepare(`
|
|
UPDATE printers SET
|
|
name = ?, type = ?, interface = ?, printer_type = ?, paper_width = ?, paper_format = ?,
|
|
is_default = ?, is_enabled = ?, font_size = ?, line_style = ?,
|
|
qr_code_enabled = ?, qr_code_size = ?, qr_code_correction = ?, qr_code_content_template = ?,
|
|
header_text = ?, footer_text = ?,
|
|
business_name = ?, business_address = ?, business_phone = ?, business_website = ?, business_email = ?,
|
|
business_contact_size = ?, show_customer_info = ?, show_order_items = ?, show_prices = ?, show_timestamps = ?,
|
|
logo_path = ?, logo_max_width_dots = ?, updated_at = ?
|
|
WHERE id = ?
|
|
`);
|
|
|
|
const result = stmt.run(
|
|
config.name || 'Unnamed Printer',
|
|
config.type || 'usb',
|
|
config.interface || '',
|
|
config.printer_type || 'epson',
|
|
config.paper_width || 48,
|
|
config.paper_format || '80mm',
|
|
config.is_default ? 1 : 0,
|
|
config.is_enabled !== false ? 1 : 0,
|
|
config.font_size || 'normal',
|
|
config.line_style || 'single',
|
|
config.qr_code_enabled !== false ? 1 : 0,
|
|
config.qr_code_size || 3,
|
|
config.qr_code_correction || 'M',
|
|
config.qr_code_content_template || 'ORDER-{id}',
|
|
(typeof config.header_text !== 'undefined' ? config.header_text : 'KITCHEN ORDER'),
|
|
(typeof config.footer_text !== 'undefined' ? config.footer_text : 'Thank you!'),
|
|
config.business_name || '',
|
|
config.business_address || '',
|
|
config.business_phone || '',
|
|
config.business_website || '',
|
|
config.business_email || '',
|
|
config.business_contact_size || 'normal',
|
|
config.show_customer_info !== false ? 1 : 0,
|
|
config.show_order_items !== false ? 1 : 0,
|
|
config.show_prices !== false ? 1 : 0,
|
|
config.show_timestamps !== false ? 1 : 0,
|
|
config.logo_path || null,
|
|
config.logo_max_width_dots || null,
|
|
now,
|
|
id
|
|
);
|
|
|
|
// If this is set as default, unset other defaults
|
|
if (config.is_default) {
|
|
this.db.prepare('UPDATE printers SET is_default = 0 WHERE id != ?').run(id);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
deletePrinter(id) {
|
|
return this.db.prepare('DELETE FROM printers WHERE id = ?').run(id);
|
|
}
|
|
|
|
getPrinter(id) {
|
|
const row = this.db.prepare('SELECT * FROM printers WHERE id = ?').get(id);
|
|
return row ? this.mapPrinterRow(row) : null;
|
|
}
|
|
|
|
getAllPrinters() {
|
|
const rows = this.db.prepare('SELECT * FROM printers ORDER BY is_default DESC, name ASC').all();
|
|
return rows.map(row => this.mapPrinterRow(row));
|
|
}
|
|
|
|
getEnabledPrinters() {
|
|
const rows = this.db.prepare('SELECT * FROM printers WHERE is_enabled = 1 ORDER BY is_default DESC, name ASC').all();
|
|
return rows.map(row => this.mapPrinterRow(row));
|
|
}
|
|
|
|
getDefaultPrinter() {
|
|
const row = this.db.prepare('SELECT * FROM printers WHERE is_default = 1 LIMIT 1').get();
|
|
return row ? this.mapPrinterRow(row) : null;
|
|
}
|
|
|
|
setDefaultPrinter(id) {
|
|
const transaction = this.db.transaction(() => {
|
|
this.db.prepare('UPDATE printers SET is_default = 0').run();
|
|
this.db.prepare('UPDATE printers SET is_default = 1 WHERE id = ?').run(id);
|
|
});
|
|
transaction();
|
|
}
|
|
|
|
togglePrinterEnabled(id) {
|
|
const printer = this.getPrinter(id);
|
|
if (!printer) return null;
|
|
const newEnabled = printer.is_enabled ? 0 : 1;
|
|
this.db.prepare('UPDATE printers SET is_enabled = ? WHERE id = ?').run(newEnabled, id);
|
|
return { is_enabled: newEnabled === 1 };
|
|
}
|
|
|
|
mapPrinterRow(row) {
|
|
return {
|
|
id: row.id,
|
|
name: row.name,
|
|
type: row.type,
|
|
interface: row.interface,
|
|
printer_type: row.printer_type,
|
|
paper_width: row.paper_width,
|
|
paper_format: row.paper_format,
|
|
is_default: row.is_default === 1,
|
|
is_enabled: row.is_enabled === 1,
|
|
font_size: row.font_size,
|
|
line_style: row.line_style,
|
|
qr_code_enabled: row.qr_code_enabled === 1,
|
|
qr_code_size: row.qr_code_size,
|
|
qr_code_correction: row.qr_code_correction,
|
|
qr_code_content_template: row.qr_code_content_template,
|
|
header_text: row.header_text,
|
|
footer_text: row.footer_text,
|
|
business_name: row.business_name,
|
|
business_address: row.business_address,
|
|
business_phone: row.business_phone,
|
|
business_website: row.business_website,
|
|
business_email: row.business_email,
|
|
business_contact_size: row.business_contact_size,
|
|
show_customer_info: row.show_customer_info === 1,
|
|
show_order_items: row.show_order_items === 1,
|
|
show_prices: row.show_prices === 1,
|
|
show_timestamps: row.show_timestamps === 1,
|
|
logo_path: row.logo_path,
|
|
logo_max_width_dots: row.logo_max_width_dots,
|
|
created_at: row.created_at,
|
|
updated_at: row.updated_at
|
|
};
|
|
}
|
|
|
|
// Migration from old config system to new printers table
|
|
migrateOldPrinterConfig() {
|
|
try {
|
|
// Check if we already have printers
|
|
const existingPrinters = this.db.prepare('SELECT COUNT(*) as count FROM printers').get();
|
|
if (existingPrinters && existingPrinters.count > 0) {
|
|
return; // Already migrated
|
|
}
|
|
|
|
// Get old config
|
|
const oldConfig = this.getConfig();
|
|
|
|
// Only migrate if old printer config exists
|
|
if (!oldConfig.printerInterface && !oldConfig.printerPath) {
|
|
return; // No old config to migrate
|
|
}
|
|
|
|
console.log('Migrating old printer configuration to new printers table...');
|
|
|
|
// Create a printer from old config
|
|
const printerConfig = {
|
|
name: 'Default Printer (Migrated)',
|
|
type: oldConfig.printerInterface === 'serial' ? 'com' : (oldConfig.printerInterface || 'usb'),
|
|
interface: oldConfig.printerPath || '/dev/usb/lp0',
|
|
printer_type: oldConfig.printerType || 'epson',
|
|
paper_width: parseInt(oldConfig.printerWidth, 10) || 48,
|
|
paper_format: (parseInt(oldConfig.printerWidth, 10) || 48) >= 48 ? '80mm' : '58mm',
|
|
is_default: true,
|
|
is_enabled: true,
|
|
font_size: oldConfig.fontSize || 'normal',
|
|
line_style: oldConfig.lineStyle || 'single',
|
|
qr_code_enabled: oldConfig.qrCodeEnabled !== 'false',
|
|
qr_code_size: parseInt(oldConfig.qrCodeSize, 10) || 3,
|
|
qr_code_correction: oldConfig.qrCodeCorrection || 'M',
|
|
qr_code_content_template: oldConfig.qrCodeContentTemplate || 'ORDER-{id}',
|
|
header_text: oldConfig.headerText || 'KITCHEN ORDER',
|
|
footer_text: oldConfig.footerText || 'Thank you!',
|
|
business_name: oldConfig.businessName || '',
|
|
business_address: oldConfig.businessAddress || '',
|
|
business_phone: oldConfig.businessPhone || '',
|
|
business_website: oldConfig.businessWebsite || '',
|
|
business_email: oldConfig.businessEmail || '',
|
|
business_contact_size: oldConfig.businessContactSize || 'normal',
|
|
show_customer_info: oldConfig.showCustomerInfo !== 'false',
|
|
show_order_items: oldConfig.showOrderItems !== 'false',
|
|
show_prices: oldConfig.showPrices !== 'false',
|
|
show_timestamps: oldConfig.showTimestamps !== 'false',
|
|
logo_path: oldConfig.logoPath || null,
|
|
logo_max_width_dots: oldConfig.logoMaxWidthDots ? parseInt(oldConfig.logoMaxWidthDots, 10) : null
|
|
};
|
|
|
|
this.addPrinter(printerConfig);
|
|
console.log('Migration complete: Old printer configuration transferred to new system');
|
|
} catch (error) {
|
|
console.error('Error migrating old printer config:', error.message);
|
|
}
|
|
}
|
|
|
|
close() {
|
|
if (this.db) {
|
|
this.db.close();
|
|
}
|
|
}
|
|
}
|
|
|
|
module.exports = new DatabaseManager();
|
|
|