aonestar.public

Parameters

Name	Type	Mode
request_id	integer	IN

Definition

DECLARE 
    request record;
    item record;
    item_count int = 0;
    _charge_type TEXT;
BEGIN
    --PERFORM sp_log_debug('sp_gen_request_charge_schedule', 'request_id: '||request_id::text);
    SELECT INTO request * FROM guest_request WHERE id = $1;

    DELETE FROM charge_schedule cs WHERE cs.request_id = $1 AND NOT cs.posted;

    IF request.charge_rate <> 0 THEN    
        /* Query requirements group by charge date */
        FOR item IN ( 
            WITH req AS (           
                /* booking items */              
                SELECT NULL::int AS register_id, b.booking_id, b.item_id AS booking_item, b.qty AS room_qty, b.arrival, b.departure, 
                    IIF(request.inclusive, b.included_adult,  b.adult)  AS adult, 
                    IIF(request.inclusive, b.included_child,  b.child)  AS child, 
                    IIF(request.inclusive, b.included_infant, b.infant) AS infant
                FROM booking_items b
                WHERE (request.register_id IS NULL) 
                    AND (b.booking_id = request.booking_id) 
                    AND (request.booking_item IS NULL OR request.booking_item = b.item_id) 
                    AND (request.multiplier <> 1)
                
                UNION ALL /* per booking */
                SELECT NULL, b.id, NULL, 1, b.arrival, b.departure, NULL, NULL, NULL
                FROM booking b 
                WHERE (b.id = request.booking_id) AND (request.multiplier = 1)
                
                UNION ALL /* in-house */
                SELECT r.id, NULL, NULL, 1, r.arrival, r.departure, --iif(r.arrival < system_date, system_date, r.arrival), 
                    IIF(request.inclusive, r.included_adult,  r.adult)  AS adult, 
                    IIF(request.inclusive, r.included_child,  r.child)  AS child, 
                    IIF(request.inclusive, r.included_infant, r.infant) AS infant
                FROM registration R 
                WHERE (request.register_id IS NOT NULL) AND (r.id = request.register_id) --AND (request.multiplier <> 1)
            ), chg AS (
                SELECT fn_get_request_qty(req.room_qty, req.adult, req.child, req.infant, request.qty, request.multiplier) AS charge_qty,               
                       fn_get_request_start(request.frequency , req.arrival, req.departure, request.start_date, request.end_date) AS start_date,    
                       fn_get_request_end(request.frequency , req.arrival, req.departure, request.start_date, request.end_date) AS end_date         
                FROM req   
            ) 
            SELECT charge_date, sum(charge_qty) AS charge_qty 
            FROM chg, LATERAL fn_date_range(chg.start_date, chg.end_date) AS charge_date
            GROUP BY 1 
            HAVING sum(charge_qty) > 0
            ORDER BY 1
        )
        LOOP 
            item_count := item_count + 1;
            _charge_type := IIF(request.inclusive, 'inclusion', 'requirement');
            INSERT INTO charge_schedule(request_id, register_id, booking_id, charge_date, charge_type, charge_dept, charge_qty, charge_amount, charge_to_booking)
            VALUES (request.id, request.register_id, request.booking_id, item.charge_date, _charge_type, request.charge_dept, item.charge_qty, request.charge_rate, COALESCE(request.charge_to_booking,FALSE))
            ON CONFLICT DO NOTHING;
        END LOOP;
    END IF;
    RETURN item_count;
END