print-utils-desktop/electron/service/database/PrintRecordService.ts

import {DbStorage} from "./DbStorage";
import {logger} from 'ee-core/log';
class PrintRecordService {

    private static tableName: String = 'print_record'

    constructor() {
        this.createTable()
    }

    /**
     * 创建打印记录表
     * @private
     */
    private createTable() {
        if (!DbStorage.checkTableExists(PrintRecordService.tableName)) {
            const sql = `CREATE TABLE IF NOT EXISTS ${PrintRecordService.tableName} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                jobId VARCHAR(128),
                printerName VARCHAR(200),
                printHtml TEXT,
                printStatus VARCHAR(32),
                failureReason TEXT,
                createdAt TEXT DEFAULT (datetime('now', 'localtime'))
            )`
            DbStorage.getDb().exec(sql)
        }
    }

    public add(record: AddPrintRecord): PrintRecord {
        const uuId = crypto.randomUUID().replace(/-/g, '')
        const sql = `INSERT INTO ${PrintRecordService.tableName} (jobId,printerName,printHtml,printStatus,failureReason) VALUES (@jobId,@printerName, @printHtml,@printStatus,@failureReason)`
        const insert = DbStorage.getDb().prepare(sql)
        insert.run({jobId: uuId, ...record})
        return this.getByUuId(uuId)
    }

    public getByUuId(uuId: String): PrintRecord {
        const sql = `SELECT * FROM ${PrintRecordService.tableName} WHERE job_id = ?`
        const select = DbStorage.getDb().prepare(sql)
        return select.get(uuId) as PrintRecord
    }

    /**
     * 分页查询打印记录
     * @param query {@link PageQuery}
     */
    public getPage(query: PageQuery): Promise<Page> {
        return new Promise((resolve, _) => {
            this.validatePaginationParams(query)
            logger.info('【PrintRecordService#getPage】打印记录分页查询参数:', query)
            // 计算偏移量
            const offset = (query.page - 1) * query.size;
            const { whereSql, queryParams } = this.buildWhereClause(query);

            // 查询数据
            const dataQuery = `
            SELECT * FROM ${PrintRecordService.tableName}
            ${whereSql}
            ORDER BY createdAt DESC
            LIMIT ? OFFSET ?
          `;

            // 查询总数
            const countQuery = `
            SELECT COUNT(1) as total FROM ${PrintRecordService.tableName}
            ${whereSql}
          `;
            try {
                const db = DbStorage.getDb();
                const selectPage = db.prepare(dataQuery)
                const dataRows = selectPage.all(...queryParams, query.size, offset)
                const totalQuery = db.prepare(countQuery)
                const result = totalQuery.get(...queryParams);
                // @ts-ignore
                const total = result?.total ?? 0;

                const totalPages = Math.ceil((total as number) / query.size);
                resolve({
                    total: total as number,
                    page: query.page,
                    size: query.size,
                    totalPages: totalPages,
                    data: dataRows as PrintRecord[]
                })
            }catch (e) {
                logger.error('【PrintRecordService#getPage】打印记录数据库查询失败:', e)
                resolve({
                    total: 0,
                    page: query.page,
                    size: query.size,
                    totalPages: 0,
                    data: []
                })
            }
        })
    }

    private buildWhereClause(query: PageQuery): { whereSql: string; queryParams: any[] } {
        const whereClauses: string[] = [];
        const queryParams: any[] = [];

        if (query.startDate) {
            whereClauses.push('createdAt >= ?');
            queryParams.push(query.startDate);
        }

        if (query.endDate) {
            whereClauses.push('createdAt <= ?');
            queryParams.push(query.endDate);
        }

        const whereSql = whereClauses.length > 0
            ? `WHERE ${whereClauses.join(' AND ')}`
            : '';

        return { whereSql, queryParams };
    }

    private validatePaginationParams(params: PageQuery): void {
        // 校验页码和每页数量
        if (params.page < 1 || !Number.isInteger(params.page)) {
            throw new Error('页码必须为正整数');
        }

        if (params.size < 1 || !Number.isInteger(params.size)) {
            throw new Error('每页数量必须为正整数');
        }
        if (params.startDate) {
            const startDate = new Date(params.startDate)
            if (isNaN(startDate.getTime())) {
                throw new Error('开始时间格式错误');
            }
            params.startDate = startDate.toISOString()
        }
        if (params.endDate) {
            const endDate = new Date(params.endDate)
            if (isNaN(endDate.getTime())) {
                throw new Error('结束时间格式错误');
            }
            params.endDate = endDate.toISOString()
        }
        if (params.startDate && params.endDate) {
            if (params.startDate > params.endDate) {
                throw new Error('开始时间不能大于结束时间');
            }
        }
    }
}

interface PrintRecord {
    id: number
    /**
     * 作业ID
     */
    jobId: string
    /**
     * 打印机名称
     */
    printerName: string
    /**
     * 打印HTML
     */
    printHtml: string
    /**
     * 打印状态
     */
    printStatus: string
    /**
     * 打印失败原因
     */
    failureReason: string
    /**
     * 创建时间
     */
    createdAt: string
}

interface Page {
    total: number
    page: number
    size: number
    totalPages: number,
    data: Array<PrintRecord>
}

interface PageQuery {
    page: number
    size: number
    startDate: string,
    endDate: string
}

interface AddPrintRecord {
    /**
     * 打印机名称
     */
    printerName: string
    /**
     * 打印HTML
     */
    printHtml: string
    /**
     * 打印状态
     */
    printStatus: string
    /**
     * 打印失败原因
     */
    failureReason: string
}

PrintRecordService.toString = () => '[class PrintRecordService]'
const printRecordService = new PrintRecordService()
export {
    PrintRecordService,
    printRecordService,
}

export type {
    PrintRecord,
    Page,
    PageQuery,
    AddPrintRecord
}