aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
user_name	text	IN (DEFAULT NULL)
default_room_dept	integer	IN (DEFAULT NULL)
extrabed_dept	integer	IN (DEFAULT NULL)
abf_dept	integer	IN (DEFAULT NULL)
lunch_dept	integer	IN (DEFAULT NULL)
dinner_dept	integer	IN (DEFAULT NULL)

Definition

DECLARE 
    chg record;
    charge_dept int;
    post_result json;
    posted_count int = 0;
    posted_rooms int = 0;
    --posted_trans int = 0;
    posted_tran_id int;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    success bool = false;
    item record;
    prv_room int = -1;
    TEST_MODE bool = false;
    SYS_DATE date := fn_system_date();
BEGIN 
  default_room_dept := COALESCE(default_room_dept, fn_sys_param('AUTO_POST', 'ROOM', null::int));    

    /* Individual */ 
    SELECT S.*, F.id as charge_folio_id, rm.room_number, 
        coalesce(rp.room_charge_dept, default_room_dept) as room_charge_dept 
    FROM sp_rate_schedule_breakdown(SYS_DATE, posted := false, charge_to_booking := false, register_id := $1) S
        LEFT JOIN folio f on f.register_id = s.register_id 
        LEFT JOIN rate_plan rp on rp.id = s.rateplan_id
        LEFT JOIN room rm ON rm.id = s.room_id
    --ORDER BY rm.room_number, s.register_id
    INTO chg;

    IF FOUND THEN
        SUCCESS := true;
        posted_tran_id := NULL;
        posted_count := 0;
        extrabed_dept     := COALESCE(extrabed_dept, fn_sys_param('AUTO_POST', 'EXTRABED', default_room_dept));   
        abf_dept          := COALESCE(abf_dept, fn_sys_param('AUTO_POST', 'ABF', null::int)); 
        lunch_dept        := COALESCE(lunch_dept, fn_sys_param('AUTO_POST', 'LUNCH', null::int)); 
        dinner_dept       := COALESCE(dinner_dept, fn_sys_param('AUTO_POST', 'DINNER', null::int));  
        
        PERFORM sp_raise_error_if(not chg.room_posted and not chg.approved, 'Room '||chg.room_number||', rate not approved.');

        /* Post room */
        IF (chg.room > 0 and not chg.room_posted) THEN 
            post_result := sp_post_transaction(
                chg.charge_folio_id, 
                chg.room_charge_dept, 
                chg.room, 
                1, 
                folio_seq   => fn_get_target_folio_seq(chg.charge_folio_id, chg.room_charge_dept, true), 
                user_name   => user_name, 
                reference   => 'AUTO', 
                --remark      => 'Room '||chg.room_number, 
                charge_date => chg.charge_date, 
                post_flag   => 'R'
            );
            
            RAISE NOTICE '%: Post Room, %, %, %', chg.room_number, chg.charge_date, chg.room, post_result->>'result_msg';

            SUCCESS := post_result->>'result_code' <> '4';

            IF SUCCESS THEN
                posted_count := posted_count + 1;
                posted_tran_id := (post_result->'result_data'->>'id')::int;
            END IF;

            PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
        END IF;

        /* Post Extra Bed */
        IF SUCCESS and (chg.extrabed > 0) and not chg.room_posted THEN 
            post_result := sp_post_transaction(
                chg.charge_folio_id, 
                extrabed_dept, 
                chg.extrabed, 
                1, 
                folio_seq   => fn_get_target_folio_seq(chg.charge_folio_id, extrabed_dept, true), 
                user_name   => user_name, 
                reference   => 'AUTO', 
                remark      => IIF(extrabed_dept = chg.room_charge_dept, 'Extra Bed', null::text), 
                charge_date => chg.charge_date, 
                post_flag   => 'R'
            );
            
            RAISE NOTICE '%: Post Extrabed, %, %, %', chg.room_number, chg.charge_date, chg.extrabed, post_result->>'result_msg';

            SUCCESS := post_result->>'result_code' <> '4';
            posted_count := posted_count + IIF(SUCCESS, 1, 0);

            PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
        END IF;

        /* Post ABF */
        IF SUCCESS and (chg.abf > 0) and not chg.room_posted THEN 
            post_result := sp_post_transaction(
                chg.charge_folio_id, 
                abf_dept, 
                chg.abf, 
                1, 
                folio_seq => fn_get_target_folio_seq(chg.charge_folio_id, abf_dept, true), 
                user_name => user_name, 
                reference => 'AUTO', 
                --remark => 'Room '||chg.room_number, 
                charge_date => chg.charge_date+1, 
                post_flag => 'R'
            );
            
            RAISE NOTICE '%: Post ABF, %, %, %', chg.room_number, chg.charge_date+1, chg.abf, post_result->>'result_msg';

            SUCCESS := post_result->>'result_code' <> '4';
            posted_count := posted_count + IIF(SUCCESS, 1, 0);

            PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
        END IF;

        /* Post Lunch */
        IF SUCCESS and (chg.lunch > 0) and not chg.room_posted THEN 
            post_result := sp_post_transaction(
                chg.charge_folio_id, 
                lunch_dept, 
                chg.lunch, 
                1, 
                folio_seq => fn_get_target_folio_seq(chg.charge_folio_id, lunch_dept, true), 
                user_name => user_name, 
                reference => 'AUTO', 
                --remark => 'Room '||chg.room_number, 
                charge_date => chg.charge_date+1, 
                post_flag => 'R'
            );
            
            RAISE NOTICE '%: Post Lunch, %, %, %', chg.room_number, chg.charge_date+1, chg.lunch, post_result->>'result_msg';
            SUCCESS := post_result->>'result_code' <> '4';
            posted_count := posted_count + IIF(SUCCESS, 1, 0);
            PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
        END IF;

        /* Post Dinner */
        IF SUCCESS and (chg.dinner > 0) and not chg.room_posted THEN 
            post_result := sp_post_transaction(
                chg.charge_folio_id, 
                dinner_dept, 
                chg.dinner, 
                1, 
                folio_seq => fn_get_target_folio_seq(chg.charge_folio_id, dinner_dept, true), 
                user_name => user_name, 
                reference => 'AUTO', 
                --remark => 'Room '||chg.room_number, 
                charge_date => chg.charge_date, 
                post_flag => 'R'
            );
            
            RAISE NOTICE '%: Post Dinner, %, %, %', chg.room_number, chg.charge_date, chg.dinner, post_result->>'result_msg';
            SUCCESS := post_result->>'result_code' <> '4';
            posted_count = posted_count + IIF(SUCCESS, 1, 0);
            PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
        END IF;

        /* Post inclusive charges */
        IF SUCCESS and not chg.room_posted and (fn_length(chg.inclusive_items) > 0) THEN 
            FOR item in (
                select * from json_to_recordset(chg.inclusive_items)
                as itm(inclusion_id int, name text, charge_date date, charge_dept int, frequency int, qty int, charge_rate t_money, remark text, posted bool)
                where not itm.posted
            ) 
            LOOP
                post_result := sp_post_transaction(
                    chg.charge_folio_id, 
                    item.charge_dept, 
                    item.charge_rate, 
                    item.qty, 
                    folio_seq   => fn_get_target_folio_seq(chg.charge_folio_id, item.charge_dept, true), 
                    user_name   => user_name, 
                    reference   => 'AUTO', 
                    remark      => item.name, 
                    charge_date => item.charge_date, 
                    post_flag   => 'R'
                );
            
                RAISE NOTICE '%: Post % (inclusive), %, %, %', chg.room_number, item.name, item.charge_date, item.qty*item.charge_rate, post_result->>'result_msg';
                SUCCESS := post_result->>'result_code' <> '4';
                posted_count = posted_count + IIF(SUCCESS, 1, 0);     
          
                PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
            END LOOP;
        END IF;

        IF SUCCESS and not chg.room_posted THEN  
            UPDATE registration_rates rr 
            SET posted = TRUE, 
                inclusive_posted = TRUE,
                tran_id = posted_tran_id 
            WHERE rr.register_id = chg.register_id AND rr.charge_date = SYS_DATE;  
    
            UPDATE registration rg 
            SET room_posted = TRUE 
            WHERE rg.id = chg.register_id;
        END IF;

        /* Post other charges */
        IF SUCCESS and (fn_length(chg.other_items) > 0) THEN 
            FOR item in (
                select * from json_to_recordset(chg.other_items)
                as itm(charge_id int, inclusion_id int, name text, charge_date date, charge_dept int, frequency int, qty int, charge_rate t_money, remark text, posted bool)
                where not itm.posted
            ) 
            LOOP
                post_result := sp_post_transaction(
                    chg.charge_folio_id, 
                    item.charge_dept, 
                    item.charge_rate, 
                    item.qty, 
                    folio_seq   => fn_get_target_folio_seq(chg.charge_folio_id, item.charge_dept, true), 
                    user_name   => user_name, 
                    reference   => 'AUTO', 
                    remark      => item.name, 
                    charge_date => item.charge_date, 
                    post_flag   => 'A'
                );

                UPDATE charge_schedule cs 
                SET posted = TRUE
                WHERE cs.id = item.charge_id;
            
                RAISE NOTICE '[Room: %, Reg# %] Post % (other), %, %, %', chg.room_number, chg.register_id, item.name, item.charge_date, item.qty*item.charge_rate, post_result->>'result_msg';
                SUCCESS := post_result->>'result_code' <> '4';
                posted_count = posted_count + IIF(SUCCESS, 1, 0);
                PERFORM sp_raise_error_if(NOT SUCCESS, post_result);
            END LOOP;
        END IF;
       
--        IF (posted_count > 0) THEN
--           -- posted_rooms := posted_rooms + IIF(prv_room <> chg.room_id, 1, 0);
--            posted_trans := posted_trans + posted_count;
--        END IF;

        RETURN fn_result_success(json_build_object('posted_count', posted_count), '00', '%s transactions posted', posted_count::text);
    ELSE
        RETURN fn_result_error('30501', 'No transaction to be posted');
    END IF;


   -- PERFORM sp_raise_error_if(TEST_MODE, msg||' (Test Mode, all process will rollback)');

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;   
    RAISE NOTICE E'!! Error: Room %, Register# %, %\n', chg.room_number, chg.register_id, _msg_text;
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_auto_post');      
END;