backend/models/Ticket.js - master - varsl/HTML-CSS

backend/models/Ticket.js
238 строк7 KB
varsl
Создание моделей данных для приложения
21 дек 2025, 23:53
21 дек 2025, 23:53a1e5649
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const { dbPath } = require('../config');
const { v4: uuidv4 } = require('uuid');

const db = new sqlite3.Database(path.resolve(dbPath));

class Ticket {
    // Создание билета
    static async create(ticketData) {
        const {
            userId,
            ticketData: ticketDetails,
            visitDate,
            visitTime,
            totalAmount,
            customerName,
            customerEmail,
            customerPhone
        } = ticketData;

        const orderId = `TICKET-${uuidv4().substring(0, 8).toUpperCase()}`;

        return new Promise((resolve, reject) => {
            const sql = `
        INSERT INTO tickets (
          user_id, order_id, ticket_data, visit_date, visit_time,
          total_amount, customer_name, customer_email, customer_phone
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;

            db.run(sql, [
                userId || null,
                orderId,
                JSON.stringify(ticketDetails),
                visitDate,
                visitTime,
                totalAmount,
                customerName,
                customerEmail,
                customerPhone
            ], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve({
                        id: this.lastID,
                        orderId: orderId
                    });
                }
            });
        });
    }

    // Поиск билета по orderId
    static async findByOrderId(orderId) {
        return new Promise((resolve, reject) => {
            const sql = `SELECT * FROM tickets WHERE order_id = ?`;
            db.get(sql, [orderId], (err, row) => {
                if (err) {
                    reject(err);
                } else {
                    if (row) {
                        try {
                            row.ticket_data = JSON.parse(row.ticket_data);
                        } catch (e) {
                            row.ticket_data = {};
                        }
                    }
                    resolve(row);
                }
            });
        });
    }

    // Получение билетов пользователя
    static async findByUserId(userId, limit = 100, offset = 0) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT * FROM tickets 
        WHERE user_id = ? 
        ORDER BY created_at DESC 
        LIMIT ? OFFSET ?
      `;
            db.all(sql, [userId, limit, offset], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    // Парсим JSON данные билетов
                    rows.forEach(row => {
                        try {
                            row.ticket_data = JSON.parse(row.ticket_data);
                        } catch (e) {
                            row.ticket_data = {};
                        }
                    });
                    resolve(rows);
                }
            });
        });
    }

    // Обновление статуса билета
    static async updateStatus(orderId, status) {
        const validStatuses = ['active', 'used', 'cancelled', 'refunded'];
        if (!validStatuses.includes(status)) {
            throw new Error('Invalid status');
        }

        return new Promise((resolve, reject) => {
            const sql = `UPDATE tickets SET status = ? WHERE order_id = ?`;
            db.run(sql, [status, orderId], function(err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes > 0);
                }
            });
        });
    }

    // Получение статистики билетов
    static async getStats(startDate = null, endDate = null) {
        let whereClause = '';
        const params = [];

        if (startDate && endDate) {
            whereClause = 'WHERE created_at BETWEEN ? AND ?';
            params.push(startDate, endDate);
        } else if (startDate) {
            whereClause = 'WHERE created_at >= ?';
            params.push(startDate);
        }

        return new Promise((resolve, reject) => {
            const sql = `
        SELECT 
          COUNT(*) as total_tickets,
          SUM(total_amount) as total_revenue,
          AVG(total_amount) as average_ticket_price,
          COUNT(DISTINCT user_id) as unique_customers,
          status,
          DATE(created_at) as date
        FROM tickets
        ${whereClause}
        GROUP BY DATE(created_at), status
        ORDER BY date DESC
      `;

            db.all(sql, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Получение ежемесячной статистики
    static async getMonthlyStats(months = 12) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT 
          strftime('%Y-%m', created_at) as month,
          COUNT(*) as ticket_count,
          SUM(total_amount) as revenue,
          AVG(total_amount) as avg_ticket_price
        FROM tickets
        WHERE created_at >= DATE('now', ?)
        GROUP BY strftime('%Y-%m', created_at)
        ORDER BY month
      `;

            db.all(sql, [`-${months} months`], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }

    // Поиск билетов по email
    static async findByEmail(email, limit = 50) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT * FROM tickets 
        WHERE customer_email = ? 
        ORDER BY created_at DESC 
        LIMIT ?
      `;
            db.all(sql, [email, limit], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    rows.forEach(row => {
                        try {
                            row.ticket_data = JSON.parse(row.ticket_data);
                        } catch (e) {
                            row.ticket_data = {};
                        }
                    });
                    resolve(rows);
                }
            });
        });
    }

    // Получение билетов по дате посещения
    static async findByVisitDate(date, limit = 100) {
        return new Promise((resolve, reject) => {
            const sql = `
        SELECT * FROM tickets 
        WHERE visit_date = ? 
        ORDER BY visit_time 
        LIMIT ?
      `;
            db.all(sql, [date, limit], (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    rows.forEach(row => {
                        try {
                            row.ticket_data = JSON.parse(row.ticket_data);
                        } catch (e) {
                            row.ticket_data = {};
                        }
                    });
                    resolve(rows);
                }
            });
        });
    }
}

module.exports = Ticket;