Запрос MySQL: Расчет записей билетов с началом и окончанием

SELECT 
    stop.ticket_number,
    stop.date AS stop_date,
    stop.location AS stop_location,
    start.date AS start_date,
    start.location AS start_location,
    TIMESTAMPDIFF(HOUR, start.date, stop.date) AS duration_hours,
    TIMESTAMPDIFF(MINUTE, start.date, stop.date) AS duration_minutes
FROM 
    tickets stop
INNER JOIN 
    tickets start ON stop.ticket_number = start.ticket_number 
                  AND stop.event_type = 'Stop'
                  AND start.event_type = 'Start'
WHERE 
    stop.date = '2024-01-15'
    AND start.date = '2024-01-15'

CREATE TABLE tickets (
    ticket_number INT PRIMARY KEY,
    start_location VARCHAR(100),
    start_date DATETIME,
    stop_location VARCHAR(100),
    stop_date DATETIME,
    duration_hours INT,
    status ENUM('active', 'completed', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

WITH numbered_tickets AS (
    SELECT 
        ticket_number,
        location,
        date,
        event_type,
        CASE 
            WHEN event_type = 'Start' THEN 1
            WHEN event_type = 'Stop' THEN 2
        END AS event_order,
        ROW_NUMBER() OVER (PARTITION BY ticket_number ORDER BY date, event_type) AS rn
    FROM tickets
    WHERE date = '2024-01-15'
)
SELECT 
    t1.ticket_number,
    t1.location AS start_location,
    t1.date AS start_date,
    t2.location AS stop_location,
    t2.date AS stop_date,
    TIMESTAMPDIFF(HOUR, t1.date, t2.date) AS duration_hours
FROM numbered_tickets t1
JOIN numbered_tickets t2 ON t1.ticket_number = t2.ticket_number 
                       AND t1.event_order = 1 
                       AND t2.event_order = 2
                       AND t1.rn = t2.rn - 1

SELECT 
    COALESCE(stop.ticket_number, start.ticket_number) AS ticket_number,
    stop.location AS stop_location,
    stop.date AS stop_date,
    start.location AS start_location,
    start.date AS start_date,
    CASE 
        WHEN stop.ticket_number IS NOT NULL AND start.ticket_number IS NOT NULL 
        THEN TIMESTAMPDIFF(HOUR, start.date, stop.date)
        ELSE NULL
    END AS duration_hours
FROM 
    (SELECT * FROM tickets WHERE event_type = 'Stop' AND date = '2024-01-15') stop
FULL OUTER JOIN 
    (SELECT * FROM tickets WHERE event_type = 'Start' AND date = '2024-01-15') start
    ON stop.ticket_number = start.ticket_number

<?php
class TicketCalculator {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Расчет длительности билетов для конкретной даты
     */
    public function calculateDailyDurations($date) {
        $sql = "SELECT 
                    stop.ticket_number,
                    stop.date AS stop_date,
                    stop.location AS stop_location,
                    start.date AS start_date,
                    start.location AS start_location,
                    TIMESTAMPDIFF(HOUR, start.date, stop.date) AS duration_hours,
                    TIMESTAMPDIFF(MINUTE, start.date, stop.date) AS duration_minutes
                FROM tickets stop
                INNER JOIN tickets start 
                    ON stop.ticket_number = start.ticket_number 
                    AND stop.event_type = 'Stop'
                    AND start.event_type = 'Start'
                WHERE 
                    stop.date = :date
                    AND start.date = :date";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['date' => $date]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Генерация ежедневного отчета со статистикой
     */
    public function generateDailyReport($date) {
        $tickets = $this->calculateDailyDurations($date);
        
        $totalTickets = count($tickets);
        $totalHours = array_sum(array_column($tickets, 'duration_hours'));
        $averageDuration = $totalTickets > 0 ? $totalHours / $totalTickets : 0;
        
        return [
            'date' => $date,
            'total_tickets' => $totalTickets,
            'total_hours' => $totalHours,
            'average_duration_hours' => round($averageDuration, 2),
            'tickets' => $tickets
        ];
    }
    
    /**
     * Проверка отсутствующих записей о начале или окончании
     */
    public function findIncompleteTickets($date) {
        // Поиск билетов с окончанием, но без начала
        $sql = "SELECT DISTINCT t1.ticket_number, 'missing_start' AS issue
                FROM tickets t1
                LEFT JOIN tickets t2 ON t1.ticket_number = t2.ticket_number 
                                   AND t1.event_type = 'Stop' 
                                   AND t2.event_type = 'Start'
                WHERE t1.event_type = 'Stop' 
                AND t1.date = :date
                AND t2.ticket_number IS NULL
                
                UNION
                
                SELECT DISTINCT t2.ticket_number, 'missing_stop' AS issue
                FROM tickets t2
                LEFT JOIN tickets t1 ON t2.ticket_number = t1.ticket_number 
                                   AND t2.event_type = 'Start' 
                                   AND t1.event_type = 'Stop'
                WHERE t2.event_type = 'Start' 
                AND t2.date = :date
                AND t1.ticket_number IS NULL";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['date' => $date]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

// Пример использования
try {
    $pdo = new PDO('mysql:host=localhost;dbname=tickets_db', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $calculator = new TicketCalculator($pdo);
    
    // Генерация отчета за 15 января 2024 года
    $report = $calculator->generateDailyReport('2024-01-15');
    echo "Ежедневный отчет за {$report['date']}:";
    echo "Всего билетов: {$report['total_tickets']}";
    echo "Всего часов: {$report['total_hours']}";
    echo "Средняя длительность: {$report['average_duration_hours']} часов";
    
    // Проверка неполных билетов
    $incomplete = $calculator->findIncompleteTickets('2024-01-15');
    if (!empty($incomplete)) {
        echo "Обнаружены неполные билеты:";
        print_r($incomplete);
    }
    
} catch (PDOException $e) {
    echo "Ошибка базы данных: " . $e->getMessage();
}
?>

-- Создание индексов для наиболее распространенных шаблонов запросов
CREATE INDEX idx_ticket_number ON tickets(ticket_number);
CREATE INDEX idx_event_type ON tickets(event_type);
CREATE INDEX idx_date ON tickets(date);
CREATE INDEX idx_ticket_event_date ON tickets(ticket_number, event_type, date);

-- Составной индекс для наиболее распространенного запроса
CREATE INDEX idx_composite_query ON tickets(date, event_type, ticket_number);