aonestar.public

Parameters

Name	Type	Mode
reset_system_date	boolean	IN (DEFAULT true)
clear_history	boolean	IN (DEFAULT false)
clear_guests	boolean	IN (DEFAULT false)
clear_channels	boolean	IN (DEFAULT false)
clear_rates	boolean	IN (DEFAULT false)
clear_rooms	boolean	IN (DEFAULT false)
clear_room_attributes	boolean	IN (DEFAULT false)
clear_users	boolean	IN (DEFAULT false)
clear_all	boolean	IN (DEFAULT false)

Definition

DECLARE backup_table TEXT;
BEGIN
    IF clear_all OR clear_history THEN 
        TRUNCATE TABLE 
        recapitulate, transaction_summary, outstanding, room_summary, rsv_pickup 
        RESTART IDENTITY CASCADE;

        -- Delete backup data
        FOR backup_table IN (
            SELECT t.table_name::text
            FROM information_schema.TABLES t 
            WHERE t.table_schema='backup')
        LOOP 
            EXECUTE format('TRUNCATE TABLE backup.%I', backup_table);
        END LOOP;
    END IF;

    /* Clear booking, registration data and user logs */
    TRUNCATE TABLE 
    folio, registration, booking, alert_followup, alert, followup, group_info, 
    room_activity, room_usage, out_of_order, user_logs, sys_log, notification 
    RESTART IDENTITY CASCADE;

    IF clear_all OR clear_guests THEN 
        TRUNCATE TABLE guest RESTART IDENTITY CASCADE;
    END IF;

    IF clear_all OR clear_channels THEN 
        TRUNCATE TABLE channel RESTART IDENTITY CASCADE;
    END IF;

    IF clear_all OR clear_rates THEN 
        TRUNCATE TABLE rate_plan, rate_group RESTART IDENTITY CASCADE;
    END IF;

    IF clear_all OR clear_rooms THEN 
        TRUNCATE TABLE room RESTART IDENTITY CASCADE;

        IF clear_all OR clear_room_attributes THEN 
            TRUNCATE TABLE room_exposure, room_facility, room_group, room_location, room_type, bed_type, ooo_reason 
            RESTART IDENTITY CASCADE;
        END IF;
    ELSE 
        /* Reset room status to VAC/CL */
        TRUNCATE TABLE out_of_order RESTART IDENTITY CASCADE;
        UPDATE room_status SET status = 'VAC', hk_status = 'CL';
    END IF;

    IF clear_all OR clear_users THEN 
         PERFORM dblink_exec(fn_conn_authen(), 
            format($$delete 
                     from user_property usp
                     where usp.prop_id  = %L
                          and 'S' <> (select group_type 
                                      from users u inner join user_group ug on ug.id = u.user_group_id
                                      where u.id = usp.user_id)
                   $$, 
                   fn_prop_id()
             )
         );
    END IF;


    IF clear_all OR reset_system_date THEN 
        PERFORM sp_set_param('SYSTEM','SYSTEM_DATE', current_date);
    END IF; 
END

sp_db_reset_data

Parameters

Definition