vueiot-api/migrations/202406020000_heater_usage_procedure.sql

DELIMITER $$

CREATE PROCEDURE ProcessHeaterUsage(
    IN p_device_id VARCHAR(255),
    IN p_event_time DATETIME,
    IN p_raw_id INT
)
BEGIN
    DECLARE v_room_id INT;
    DECLARE v_room_name VARCHAR(255);
    DECLARE v_device_status ENUM('online','offline');
    DECLARE v_switch_status ENUM('on','off');
    DECLARE v_temperature DECIMAL(5,2);
    
    -- 唯一的异常处理器声明
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO error_logs (error_message, error_time)
        VALUES (CONCAT('Heater usage processing failed for ', p_device_id), NOW());
    END;

    -- 获取设备最新状态
    SELECT 
        d.room_id,
        d.room_name,
        d.status,
        d.switch_status,
        d.temperature
    INTO 
        v_room_id,
        v_room_name,
        v_device_status,
        v_switch_status,
        v_temperature
    FROM devices d
    WHERE d.device_id = p_device_id
    LIMIT 1;
    
    -- 开启条件：在线、开关开启、温度>14
    IF v_device_status = 'online' 
       AND v_switch_status = 'on' 
       AND v_temperature > 14 THEN
        
        -- 检查是否存在未关闭记录
        IF NOT EXISTS (
            SELECT 1 FROM heater_usage 
            WHERE device_id = p_device_id 
            AND end_time IS NULL
        ) THEN
            INSERT INTO heater_usage (
                device_id,
                room_id,
                room_name,
                start_time,
                end_time,
                duration,
                date
            ) VALUES (
                p_device_id,
                v_room_id,
                v_room_name,
                p_event_time,
                NULL,
                0,
                DATE(p_event_time - INTERVAL IF(HOUR(p_event_time) < 12, 1, 0) DAY)
            );
        END IF;

    -- 关闭条件：任意条件不满足时更新结束时间
    ELSE
        UPDATE heater_usage
        SET end_time = p_event_time,
            duration = TIMESTAMPDIFF(SECOND, start_time, p_event_time),
            -- 同步更新房间信息（设备可能更换房间）
            room_id = v_room_id,
            room_name = v_room_name
        WHERE device_id = p_device_id
          AND end_time IS NULL
        ORDER BY start_time DESC
        LIMIT 1;
    END IF;

END$$

DELIMITER ;