aonestar.public

Parameters

Name	Type	Mode
post_date	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
status	text	INOUT (DEFAULT '')
msg	text	INOUT (DEFAULT '')

Definition

DECLARE 
    chg record;
    default_room_dept int;
    extrabed_dept int;
    abf_dept int;
    lunch_dept int;
    dinner_dept int;
    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;
    v_result json;
BEGIN 
    PERFORM sp_set_session('user_name', user_name);

--    PERFORM sp_log_debug('sp_auto_post_rooms', 
--        'current_user: '||fn_current_user()
--        ||E'\nsession-lang: '||COALESCE(fn_get_session('LANGUAGE'), 'NULL') 
--        ||E'\nuser-lang: '||COALESCE(fn_user_attribute('lang'), 'NULL') 
--        ||E'\nsys-param-lang: '||fn_sys_param('LANGUAGE','NULL')
--    );

    status := 'success';
    post_date         := COALESCE(post_date, fn_system_date());
    default_room_dept := fn_sys_param('AUTO_POST', 'ROOM', null::int);    
    extrabed_dept     := fn_sys_param('AUTO_POST', 'EXTRABED', default_room_dept);   
    abf_dept          := fn_sys_param('AUTO_POST', 'ABF', null::int); 
    lunch_dept        := fn_sys_param('AUTO_POST', 'LUNCH', null::int); 
    dinner_dept       := fn_sys_param('AUTO_POST', 'DINNER', null::int);  
   -- SAVEPOINT BEFORE_AUTO_POST;

    /* Individual */ 
    FOR chg IN 
        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(fn_system_date(), posted := false, charge_to_booking := false) 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
    LOOP
        --BEGIN -- begin transaction
            SUCCESS := true;
            posted_tran_id := NULL;
            posted_count := 0;
            
            IF not chg.approved THEN
               PERFORM sp_raise_error('30503', 'Room %s, rate not approved.', chg.room_number);
            END IF;
            -- PERFORM sp_raise_error_if(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 = $1;  
        
                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;

--            IF SUCCESS THEN
--                COMMIT;
--            ELSE
--                status := 'error';
--                msg := post_result->>'result_msg';
--                ROLLBACK;
--                EXIT;    
--            END IF;
            
--            ELSIF NOT SUCCESS and (posted_count > 0) THEN     
--                ROLLBACK;
--                msg    := post_result->>'result_msg';
--                --PERFORM sp_raise_error(post_result);
--            END IF;

            prv_room := chg.room_id;
--        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 '!! Error: Room %, Register# %, %', chg.room_number, chg.register_id, _msg_text;
--            --IF (posted_count > 0) THEN   
--            ROLLBACK; 
--            PERFORM sp_log_error('sp_auto_post_rooms', _msg_text, _detail, _sqlstate, _hint, _context, force_write=>TRUE); 
--            status := 'error';
--            msg := _msg_text;
--            RAISE;        
--        END;
    END LOOP;
    
--    if msg <> '' then
--        msg := msg ||E'\n';
--    end if;
--    msg := msg || IIF(posted_count > 0, format('%s rooms, %s transactions posted', posted_rooms::text, posted_trans::text), 'No transaction posted');
    msg := IIF(posted_count > 0, format('%s rooms, %s transactions posted', posted_rooms::text, posted_trans::text), 'No transaction posted');
    RAISE NOTICE E'----------------------------\n%\n', msg;

    PERFORM sp_raise_error_if(TEST_MODE, msg||' (Test Mode, all process will rollback)');
       
--    IF TEST_MODE and posted_count > 0 THEN
--        ROLLBACK TO BEFORE_AUTO_POST;
--        RAISE NOTICE 'Rollback auto-post';
--    END IF;
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;
    v_result := fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_auto_post_rooms');    
    --IF (posted_count > 0) THEN   
    PERFORM sp_log_error('sp_auto_post_rooms', _msg_text, _detail, _sqlstate, _hint, _context, force_write=>TRUE); 
    status := 'error';
    msg := v_result->>'result_msg';
    --RAISE;        
END;