aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
status	text	INOUT
msg	text	INOUT

Definition

DECLARE 
    v_system_date date       = fn_system_date();
    v_backup_days interval   = (fn_sys_param('BACKUP','BACKUP_DAYS', 60)||' days')::interval;
    v_clearing_days interval = (fn_sys_param('BACKUP','CLEARING_DAYS', 100)||' days')::interval;
    register_ids int[] = '{}';
    rec record;
    reg record; 
    fol backup.folio;
    bk backup.booking;
    rec_count int = 0;
    deleted_rows int = 0;
    archived_rows int = 0;
    table_name TEXT;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text  TEXT;
    _id int;
    ids int[];
BEGIN   
    
    /* Move folio data to backup */
    FOR fol IN (SELECT GREATEST(f.close_date, rg.departure, rbk.departure, fbk.departure) AS date_ref, f.* 
                FROM folio f
                    LEFT JOIN registration rg ON rg.id = f.register_id 
                    LEFT JOIN booking rbk ON rbk.id = rg.booking_id  
                    LEFT JOIN booking fbk ON fbk.id = f.booking_id  
                WHERE f.status = 'C' 
                    AND GREATEST(f.close_date, rg.departure, rbk.departure, fbk.departure) + v_backup_days <= v_system_date) 
    LOOP 
        INSERT INTO backup.folio SELECT (fol).* 
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to folio', rec_count;
    
        INSERT INTO backup.folio_items  
        SELECT fol.date_ref, * FROM folio_items WHERE folio_id = fol.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to folio_items', rec_count;
        
        INSERT INTO backup.folio_details    
        SELECT fol.date_ref, * FROM folio_details WHERE folio_id = fol.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to folio_details', rec_count;
    
        INSERT INTO backup.transactions(date_ref, id, folio_item, post_type, post_date, post_time, tran_type, dept_id, 
            description, qty, amount, reference, remark, post_user, post_shift, post_source, card_id, voided, splitted, 
            splitted_src, transfer_in, transfer_src, transfer_out, transfer_des, original_folio_id, post_flag, tax_code, 
            itemizers, charge_date, active, total_amount, acc_qty, acc_amount, folio_id)     
        SELECT fol.date_ref, t.id, t.folio_item, t.post_type, t.post_date, t.post_time, t.tran_type, t.dept_id, 
            t.description, t.qty, t.amount, t.reference, t.remark, t.post_user, t.post_shift, t.post_source, t.card_id, t.voided, t.splitted, 
            t.splitted_src, t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des, t.original_folio_id, t.post_flag, t.tax_code, 
            t.itemizers, t.charge_date, t.active, t.total_amount, t.acc_qty, t.acc_amount, t.folio_id
        FROM transactions t 
        WHERE t.folio_id = fol.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to transactions', rec_count;
    
        IF fol.folio_type = 'C' THEN 
            INSERT INTO backup.cash_sale    
            SELECT fol.date_ref, * FROM cash_sale WHERE folio_id = fol.id
            ON CONFLICT DO NOTHING; 
            --RAISE NOTICE '% records inserted to cash_sale', rec_count;
        END IF;
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
--        INSERT INTO backup.cashier_log  
--        SELECT fol.date_ref, l.* 
--        FROM transaction_log l 
--            LEFT JOIN transactions t ON t.id = l.trn_id 
--            LEFT JOIN folio_items fi ON fi.item_id = t.folio_item 
--            LEFT JOIN folio f ON f.id = fi.folio_id
--        WHERE f.id = fol.id
--        ON CONFLICT DO NOTHING; 
--        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
--      
        BEGIN
            INSERT INTO backup.cashier_log  
            SELECT fol.date_ref, lg.* FROM cashier_log lg WHERE lg.folio_id = _id;
        
            DELETE FROM folio WHERE id = fol.id;
            ids := ids || fol.id;
        EXCEPTION WHEN OTHERS THEN
           -- PERFORM sp_log_warning('sp_endday_clear_data', 'Delete folio failed: '||fol.id::TEXT, true);
            GET STACKED DIAGNOSTICS
                _sqlstate= RETURNED_SQLSTATE,
                _msg_text= MESSAGE_TEXT,
                _detail  = PG_EXCEPTION_DETAIL,
                _hint    = PG_EXCEPTION_HINT,
                _context = PG_EXCEPTION_CONTEXT;    
            PERFORM sp_log_error('sp_endday_clear_data', _msg_text, _detail, _sqlstate, _hint, _context, null, user_name, notify_admin := false); 
        END;
    
--        INSERT INTO backup.cashier_log  
--        SELECT /*fol.date_ref,*/ l.* 
--        FROM cashier_log l 
--            LEFT JOIN transactions t ON t.id = l.tran_id 
--            LEFT JOIN folio_items fi ON fi.item_id = t.folio_item 
--            --LEFT JOIN folio f ON f.id = fi.folio_id
--        WHERE fi.folio_id = fol.id
--        ON CONFLICT DO NOTHING; 
--        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
--        RAISE NOTICE '% records inserted to cashier_log', rec_count;
    
    END LOOP;
    
    
    --DELETE FROM folio WHERE id  = ANY(ids);
--  FOR _id IN SELECT unnest(ids) LOOP 
--      BEGIN
--          INSERT INTO backup.cashier_log  
--          SELECT * FROM cashier_log lg WHERE lg.folio_id = _id;
--            DELETE FROM folio WHERE id = _id;
--            ids := ids || fol.id;
--        EXCEPTION WHEN OTHERS THEN
--            PERFORM sp_log_warning('sp_endday_clear_data', 'Delete folio failed: '||fol.id::TEXT, true);
--        END;
--  END LOOP;
    

/* Move registration data and to backup */
    ids := NULL;
    FOR reg IN (
        SELECT GREATEST(b.departure, rg.departure) AS date_ref, rg.* 
        FROM registration rg LEFT JOIN booking b ON b.id = rg.booking_id  
        WHERE rg.status in ('O','X') and (GREATEST(b.departure, rg.departure) + v_backup_days) <= v_system_date 
    )
    LOOP    
        ids := ids || reg.id;
        INSERT INTO backup.registration ( 
            date_ref, id, status, booking_id, booking_item, room_id, guest_id, group_id, arrival, departure, departure_time, walk_in, adult, child, infant, 
            extra_adult, extra_child, extra_infant, approved, approve_user, approve_time, checkin_user, checkin_time, checkout_user, checkout_time, 
            member_code, channel_id, origin_id, arrive_by, arrive_from, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code, payment_method, 
            sales_id, market_id, stay_type, folio_id, credit_limit, note, accno, msg_count, flag_count, rateplan_id, room_posted, charge_to_booking, 
            promotion_id, rate_id, modify_user, modify_time, house_used, shared_register_id, share_room, day_used, nights, join_register_id, is_join_room, 
            is_group, early_checkout, rsvtype_id, pax, early_checkout_date, mealtype_id, room_charge, other_charge, extra_charge, total_deposit)
        SELECT reg.date_ref, reg.id, reg.status, reg.booking_id, reg.booking_item, reg.room_id, reg.guest_id, reg.group_id, reg.arrival, reg.departure, 
            reg.departure_time, reg.walk_in, reg.adult, reg.child, reg.infant, reg.extra_adult, reg.extra_child, reg.extra_infant, reg.approved, 
            reg.approve_user, reg.approve_time, reg.checkin_user, reg.checkin_time, reg.checkout_user, reg.checkout_time, reg.member_code, reg.channel_id, 
            reg.origin_id, reg.arrive_by, reg.arrive_from, reg.depart_by, reg.depart_to, reg.folio_pattern_id, reg.voucher_no, reg.promo_code,  
            reg.payment_method, reg.sales_id, reg.market_id, reg.stay_type, reg.folio_id, reg.credit_limit, reg.note, reg.accno, reg.msg_count, reg.flag_count, 
            reg.rateplan_id, reg.room_posted, reg.charge_to_booking, reg.promotion_id, reg.rate_id, reg.modify_user, reg.modify_time, reg.house_used, 
            reg.shared_register_id, reg.share_room, reg.day_used, reg.nights, reg.join_register_id, reg.is_join_room, reg.is_group, reg.early_checkout, 
            reg.rsvtype_id, reg.pax,reg.early_checkout_date, reg.mealtype_id, reg.room_charge, reg.other_charge, reg.extra_charge, reg.total_deposit
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to registration', rec_count;
    
        INSERT INTO backup.register_guests  
        SELECT reg.date_ref, * FROM register_guests WHERE register_id = reg.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to register_guests', rec_count;
    
        INSERT INTO backup.registration_rates   
        SELECT reg.date_ref, * FROM registration_rates WHERE register_id = reg.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to registration_rates', rec_count;
    
        INSERT INTO backup.guest_request    
        SELECT reg.date_ref, * FROM guest_request WHERE register_id = reg.id
        ON CONFLICT DO NOTHING;
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to guest_request', rec_count;
    
        INSERT INTO backup.deposit  
        SELECT reg.date_ref, * FROM deposit WHERE register_id = reg.id
        ON CONFLICT DO NOTHING;
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
        --RAISE NOTICE '% records inserted to deposit', rec_count;
    
--      INSERT INTO backup.registration_log 
--      SELECT reg.date_ref, * FROM registration_log WHERE register_id = reg.id
--      ON CONFLICT DO NOTHING; 
--      GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
--        RAISE NOTICE '% records inserted to registration_log', rec_count;
    
        BEGIN
            INSERT INTO backup.registration_log  
            SELECT reg.date_ref, lg.* FROM registration_log lg WHERE lg.register_id = reg.id;
        
            DELETE FROM registration WHERE id = reg.id;
        EXCEPTION WHEN OTHERS THEN
            --PERFORM sp_log_warning('sp_endday_clear_data', 'Delete registration failed: '||reg.id::TEXT, true);
            GET STACKED DIAGNOSTICS
                _sqlstate= RETURNED_SQLSTATE,
                _msg_text= MESSAGE_TEXT,
                _detail  = PG_EXCEPTION_DETAIL,
                _hint    = PG_EXCEPTION_HINT,
                _context = PG_EXCEPTION_CONTEXT;    
            PERFORM sp_log_error('sp_endday_clear_data', _msg_text, _detail, _sqlstate, _hint, _context, null, user_name, notify_admin := false); 
        END;
    
    END LOOP;
    
--    INSERT INTO backup.registration_log  
--    SELECT * FROM registration_log lg WHERE lg.register_id = ANY(ids);
--    DELETE FROM registration WHERE id = ANY(ids);


    /* Move booking data and to backup */
    ids := NULL;
    FOR bk IN 
       SELECT b.departure AS date_ref, b.* 
       FROM booking b
       WHERE ((b.departure + v_backup_days <= v_system_date) OR (b.status = 'W' AND b.arrival = v_system_date))
            and not exists(select * from registration rg where rg.booking_id = b.id)
    LOOP 
        INSERT INTO backup.booking (
             date_ref, id, status, rsvtype_id, book_type, book_by, guest_id, group_id, party_id, member_code, channel_id, origin_id, arrival, 
             arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code, 
             payment_method, flags, sales_id, market_id, create_user, create_time, modify_user, modify_time, cancel_user, cancel_time, msg_count, 
             flag_count, stay_type, accno, confirm_no, folio_id, credit_limit, confirm_remark, confirm_user, confirm_time, note, book_date, 
             cancel_reason, booking_name, room_charge, other_charge, extra_charge, total_deposit)   
        VALUES (bk.date_ref, bk.id, bk.status, bk.rsvtype_id, bk.book_type, bk.book_by, bk.guest_id, bk.group_id, bk.party_id, bk.member_code, 
             bk.channel_id, bk.origin_id, bk.arrival, bk.arrival_time, bk.arrive_by, bk.arrive_from, bk.departure, bk.departure_time, 
             bk.depart_by, bk.depart_to, bk.folio_pattern_id, bk.voucher_no, bk.promo_code, bk.payment_method, bk.flags, bk.sales_id, 
             bk.market_id, bk.create_user, bk.create_time, bk.modify_user, bk.modify_time, bk.cancel_user, bk.cancel_time, bk.msg_count, 
             bk.flag_count, bk.stay_type, bk.accno, bk.confirm_no, bk.folio_id, bk.credit_limit, bk.confirm_remark, bk.confirm_user, 
             bk.confirm_time, bk.note, bk.book_date, bk.cancel_reason, bk.booking_name, bk.room_charge, bk.other_charge, bk.extra_charge, bk.total_deposit)
        --FROM bk
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
        INSERT INTO backup.booking_items (
            date_ref, item_id, booking_id, booking_seq, rateplan_id, roomtype_id, bedtype_id, arrival, departure, qty, adult, child, infant, 
            allot_qty, block_qty, inhouse_qty, charge_to, extra_adult, extra_child, extra_infant, no_amend, no_cancel, no_refund, 
            room_numbers, rate_id, rooms, promotion_id, noshow_qty, pax, mealtype_id, room_charge, other_charge, extra_charge
        )   
        SELECT bk.date_ref, item_id, booking_id, booking_seq, rateplan_id, roomtype_id, bedtype_id, arrival, departure, qty, adult, child, 
            infant, allot_qty, block_qty, inhouse_qty, charge_to, extra_adult, extra_child, extra_infant, no_amend, no_cancel, no_refund, 
            room_numbers, rate_id, rooms, promotion_id, noshow_qty, pax, mealtype_id, room_charge, other_charge, extra_charge
        FROM booking_items WHERE booking_id = bk.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
        INSERT INTO backup.booking_guests   
        SELECT bk.date_ref, * FROM booking_guests WHERE booking_id = bk.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
        INSERT INTO backup.booking_rates    
        SELECT bk.date_ref, * FROM booking_rates WHERE booking_id = bk.id
        ON CONFLICT DO NOTHING; 
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
        INSERT INTO backup.guest_request    
        SELECT bk.date_ref, * FROM guest_request WHERE booking_id = bk.id
        ON CONFLICT DO NOTHING;
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
    
        INSERT INTO backup.deposit  
        SELECT bk.date_ref, * FROM deposit WHERE booking_id = bk.id
        ON CONFLICT DO NOTHING;
        GET DIAGNOSTICS rec_count = ROW_COUNT; archived_rows = archived_rows + rec_count;
                
        BEGIN
            INSERT INTO backup.booking_log  
            SELECT bk.date_ref, * FROM booking_log WHERE booking_id = bk.id
            ON CONFLICT DO NOTHING; 
        
            DELETE FROM booking WHERE id = bk.id;
        EXCEPTION WHEN OTHERS THEN
            --PERFORM sp_log_warning('sp_endday_clear_data', 'Delete booking failed: '||bk.id::TEXT, true);
            GET STACKED DIAGNOSTICS
                _sqlstate= RETURNED_SQLSTATE,
                _msg_text= MESSAGE_TEXT,
                _detail  = PG_EXCEPTION_DETAIL,
                _hint    = PG_EXCEPTION_HINT,
                _context = PG_EXCEPTION_CONTEXT;    
            PERFORM sp_log_error('sp_endday_clear_data', _msg_text, _detail, _sqlstate, _hint, _context, null, user_name, notify_admin := false); 
        END;
    END LOOP;
    
--    INSERT INTO backup.booking_log  
--    SELECT * FROM booking_log lg WHERE lg.booking_id = ANY(ids);
--    DELETE FROM booking WHERE id = ANY(ids);


    FOR table_name IN (
        SELECT t.table_name::text
        FROM information_schema.TABLES t 
        WHERE t.table_schema='backup')
    LOOP 
        EXECUTE format('DELETE FROM backup.%I WHERE date_ref <= %L', table_name, v_system_date - v_clearing_days);
        GET DIAGNOSTICS rec_count = ROW_COUNT; deleted_rows = deleted_rows + rec_count;
    END LOOP;

--
--  INSERT INTO backup.registration 
--  SELECT reg.departure, rg.* 
--  FROM registration WHERE id = ANY(register_ids);
--
--  INSERT INTO backup.registration_rates 
--  SELECT reg.departure, rg.* 
--  FROM registration_rates WHERE register_id = ANY(register_ids);


--  CREATE TABLE IF NOT EXISTS registration_history() INHERITS (regis); 
--  CREATE TABLE IF NOT EXISTS registration_recent() INHERITS (regis);  
--  
--  ALTER TABLE registration_history DROP CONSTRAINT IF EXISTS chk_departure;
--  ALTER TABLE registration_history ADD CONSTRAINT chk_departure CHECK (departure + fn_sys_param('BACKUP','BACKUPDAY', 90) <= fn_system_date());
--
--  ALTER TABLE registration_recent DROP CONSTRAINT IF EXISTS chk_departure;
--  ALTER TABLE registration_recent ADD CONSTRAINT chk_departure CHECK (departure + fn_sys_param('BACKUP','BACKUPDAY', 90) > fn_system_date());
--
--  IF NOT EXISTS(SELECT * FROM registration_recent) AND NOT EXISTS(SELECT * FROM registration_history) THEN 
--      FOR reg in SELECT * FROM regis LOOP 
--          DELETE FROM regis WHERE id = reg.id;
--          IF reg.departure + v_backup_days > v_system_date THEN 
--              INSERT INTO registration_recent 
--              SELECT (reg).*;
--          ELSE 
--              INSERT INTO registration_history 
--              SELECT (reg).*;
--          END IF;
--          rec_count = rec_count +1;
--      END LOOP;
--  ELSE 
--      WITH outdated AS (
--          INSERT INTO registration_history
--          SELECT * FROM registration_recent WHERE departure + v_backup_days <= v_system_date 
--          RETURNING id
--      ), del AS (
--          DELETE FROM registration_recent
--          WHERE id IN (SELECT id FROM outdated)
--          RETURNING id
--      )
--      SELECT count(id) FROM del
--      INTO rec_count; 
--  END IF;

    status = 'success';
    msg = format('%s records archived, %s records deleted', archived_rows, deleted_rows);
END