BEGIN
    DECLARE t_site char(2);
    DECLARE t_project varchar(7);
    DECLARE t_app_server varchar(30);
    DECLARE t_instance varchar(20);
    DECLARE t_status varchar(7);
    DECLARE t_check_time datetime;

    DECLARE r_last_status varchar(7);
    DECLARE r_last_from_time datetime;
    DECLARE r_last_to_time datetime;

    DECLARE ignore_missing_from_time datetime;
    DECLARE ignore_missing_to_time datetime;

    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT site, project, app_server, instance, status, check_time
        FROM tmp_app_to_db_status_report
        WHERE site = local_site
        order by site, project, app_server, instance, check_time;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO t_site, t_project, t_app_server, t_instance, t_status, t_check_time;

        IF done THEN
            LEAVE read_loop;
        END IF;

        IF NOT EXISTS (SELECT * FROM app_to_db_status_report
                       WHERE project = t_project
                       AND app_server = t_app_server
                       AND instance = t_instance
                       AND site = t_site) THEN
            IF (t_status != '') THEN
            
                INSERT INTO app_to_db_status_report
                VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
            END IF;
        ELSE
        
            SELECT status, from_time, to_time
            INTO r_last_status, r_last_from_time, r_last_to_time
            FROM app_to_db_status_report
            WHERE project = t_project
            AND app_server = t_app_server
            AND instance = t_instance
            AND site = t_site
            AND from_time = (SELECT MAX(from_time)
                           FROM app_to_db_status_report a1
                           WHERE a1.project = t_project
                           AND a1.app_server = t_app_server
                           AND a1.instance = t_instance
                           AND a1.site = t_site)
            LIMIT 1;

            IF (r_last_status = 'OK') THEN
                IF (t_status = 'OK') THEN
                    

                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;
                ELSEIF (t_status = 'ERROR') THEN
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                    INSERT INTO app_to_db_status_report
                    VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);

                ELSE 
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                    INSERT INTO app_to_db_status_report
                    VALUES (t_site, t_project, t_app_server, t_instance, 'MISSING', t_check_time, t_check_time);

                END IF;

            ELSEIF (r_last_status = 'ERROR') THEN
                IF (t_status = 'OK') THEN
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                    INSERT INTO app_to_db_status_report
                    VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);

                ELSEIF (t_status = 'ERROR') THEN
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                ELSE 
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                    INSERT INTO app_to_db_status_report
                    VALUES (t_site, t_project, t_app_server, t_instance, 'MISSING', t_check_time, t_check_time);

                END IF;

            ELSEIF (r_last_status = 'MISSING') THEN
                IF (t_status = 'OK') THEN
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    IF (TIMESTAMPDIFF(
                                MINUTE,
                                DATE_FORMAT(r_last_from_time, "%Y-%m-%d %H:%i"),
                                DATE_FORMAT(t_check_time, "%Y-%m-%d %H:%i")
                                        ) <= 0
                         OR
                         (TIME(r_last_from_time) >= TIME('23:57:00') OR
                          TIME(r_last_from_time) <= TIME('00:01:59'))
                        ) THEN

                        DELETE FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        SELECT status, from_time, to_time
                        INTO r_last_status, r_last_from_time, r_last_to_time
                        FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND site = t_site
                        AND from_time = (SELECT MAX(from_time)
                                         FROM app_to_db_status_report a1
                                         WHERE a1.project = t_project
                                         AND a1.app_server = t_app_server
                                         AND a1.site = t_site
                                         AND a1.instance = t_instance)
                        LIMIT 1;


                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        IF (r_last_status = 'ERROR') THEN
                            INSERT INTO app_to_db_status_report
                            VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                        END IF;
                    ELSE
                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        INSERT INTO app_to_db_status_report
                        VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                    END IF;

                ELSEIF (t_status = 'ERROR') THEN
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    IF (TIMESTAMPDIFF(
                                MINUTE,
                                DATE_FORMAT(r_last_from_time, "%Y-%m-%d %H:%i"),
                                DATE_FORMAT(t_check_time, "%Y-%m-%d %H:%i")
                                        ) <= 0
                         OR
                         (TIME(r_last_from_time) >= TIME('23:57:00') OR
                          TIME(r_last_from_time) <= TIME('00:01:59'))
                        ) THEN

                        DELETE FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        SELECT status, from_time, to_time
                        INTO r_last_status, r_last_from_time, r_last_to_time
                        FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND site = t_site
                        AND from_time = (SELECT MAX(from_time)
                               FROM app_to_db_status_report a1
                               WHERE a1.project = t_project
                               AND a1.app_server = t_app_server
                               AND a1.site = t_site
                               AND a1.instance = t_instance)
                        LIMIT 1;


                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        IF (r_last_status = 'OK') THEN
                            INSERT INTO app_to_db_status_report
                            VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                        END IF;

                    ELSE
                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        INSERT INTO app_to_db_status_report
                        VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                    END IF;

                ELSE 
                    
                    
                    
                    
                    IF (TIMESTAMPDIFF(MINUTE, r_last_from_time, t_check_time) >=
                        syn_timeout) THEN
                        UPDATE app_to_db_status_report
                        SET status = 'SYN_ERR', to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;
                    ELSE
                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;
                    END IF;

                END IF;

            ELSE 
                IF (t_status = 'OK') THEN
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    IF (TIMESTAMPDIFF(
                                MINUTE,
                                DATE_FORMAT(r_last_from_time, "%Y-%m-%d %H:%i"),
                                DATE_FORMAT(t_check_time, "%Y-%m-%d %H:%i")
                                        ) <= 0) THEN
                        DELETE FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        SELECT status, from_time, to_time
                        INTO r_last_status, r_last_from_time, r_last_to_time
                        FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND site = t_site
                        AND from_time = (SELECT MAX(from_time)
                                         FROM app_to_db_status_report a1
                                         WHERE a1.project = t_project
                                         AND a1.app_server = t_app_server
                                         AND a1.site = t_site
                                         AND a1.instance = t_instance)
                        LIMIT 1;

                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        IF (r_last_status = 'ERROR') THEN
                            INSERT INTO app_to_db_status_report
                            VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                        END IF;
                    ELSE
                        UPDATE app_to_db_status_report
                        SET status = 'MISSING', to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        INSERT INTO app_to_db_status_report
                        VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                    END IF;

                ELSEIF (t_status = 'ERROR') THEN
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    IF (TIMESTAMPDIFF(
                                MINUTE,
                                DATE_FORMAT(r_last_from_time, "%Y-%m-%d %H:%i"),
                                DATE_FORMAT(t_check_time, "%Y-%m-%d %H:%i")
                                        ) <= 0) THEN

                        DELETE FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        SELECT status, from_time, to_time
                        INTO r_last_status, r_last_from_time, r_last_to_time
                        FROM app_to_db_status_report
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND site = t_site
                        AND from_time = (SELECT MAX(from_time)
                                         FROM app_to_db_status_report a1
                                         WHERE a1.project = t_project
                                         AND a1.app_server = t_app_server
                                         AND a1.site = t_site
                                         AND a1.instance = t_instance)
                        LIMIT 1;

                        UPDATE app_to_db_status_report
                        SET to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        IF (r_last_status = 'ERROR') THEN
                            INSERT INTO app_to_db_status_report
                            VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);
                        END IF;
                    ELSE
                        UPDATE app_to_db_status_report
                        SET status = 'MISSING', to_time = t_check_time
                        WHERE project = t_project
                        AND app_server = t_app_server
                        AND instance = t_instance
                        AND status = r_last_status
                        AND site = t_site
                        AND from_time = r_last_from_time;

                        INSERT INTO app_to_db_status_report
                        VALUES (t_site, t_project, t_app_server, t_instance, t_status, t_check_time, t_check_time);

                    END IF;

                ELSE 
                    
                    UPDATE app_to_db_status_report
                    SET to_time = t_check_time
                    WHERE project = t_project
                    AND app_server = t_app_server
                    AND instance = t_instance
                    AND status = r_last_status
                    AND site = t_site
                    AND from_time = r_last_from_time;

                END IF;

            END IF;
        END IF;
        

    END LOOP;

    CLOSE cur;
END