aonestar.public

Parameters

Name	Type	Mode
number_of_days	integer	IN
start_date	date	IN (DEFAULT NULL)

Definition

DECLARE
    json_result json;
    end_date date;
begin
    if (START_DATE is null) then
        START_DATE = coalesce(fn_system_date(), current_date);
    end if;

    end_date = start_date + number_of_days -1;

    with dates AS (
        SELECT fn_date_range(start_date, end_date) AS adate
    ), OCC as (
        SELECT adate AS "date", occ_percent_overall AS occ_percent
        FROM sp_get_occupancy_calendar(start_date, end_date)
        ORDER BY 1
    ), inv_rates as (
        SELECT *
        FROM sp_inventory_rates(start_date, end_date, allow_special_rates := False, allow_inactive_rates := True)
    ), inv_data as (
        select rt.id roomtype_id, rt.code, rt.name roomtype_name, order_seq AS "sequence", rt.total_rooms,--setseed(rt.id / 100),
            -- Room avail row
            (select json_agg(dataset)
             FROM (SELECT avail_date AS "date", avail
                   FROM sp_get_room_available(start_date, start_date+number_of_days-1, rt.id)) as dataset
            ) as available,
            -- Sellable status row
            (select json_agg(dataset)
             from (select d.adate "date", coalesce(stop_sell, false) AS stop_sell, min_avail, max_avail
                   from dates d
                        left join room_restriction r on r.roomtype_id = rt.id and r.effect_date = d.adate
                  ) dataset
            ) as sell_status,
            -- Rate and restriction rows
            (SELECT coalesce(json_agg(dataset),'[]')
             FROM (SELECT rp.*,
                        (SELECT json_agg(rate_items)
                         FROM (SELECT dr.rate_date AS "date", dr.editable,
                                       dr.rate_amount AS amount,
                                       --IIF(dr.available, dr.rate_amount, NULL) AS amount,
                                       json_build_object(
                                           'stop_sell', IIF(dr.available, dr.stop_sell, NULL),
                                           'cta', IIF(dr.available, dr.cta, NULL),
                                           'ctd', IIF(dr.available, dr.ctd, NULL),
                                           'min_stay', IIF(dr.available, dr.min_stay, NULL),
                                           'max_stay', IIF(dr.available, dr.max_stay, NULL)
                                       ) AS restrictions
                               FROM inv_rates AS dr
                               WHERE dr.roomtype_id = rt.id AND dr.rate_id = rp.rate_id
                              ) AS rate_items
                        ) AS rates
                    FROM (SELECT DISTINCT rate_id, rateplan_id,
                                rateplan_name || COALESCE((' (' || NULLIF(description, '')) || ')', '') AS description,
                                link_plan_id, link_description, editable, rateplan_seq AS "sequence", inv.min_rate
                         FROM inv_rates inv
                         WHERE inv.roomtype_id = rt.id and (inv.enabled or (inv.plan_type = 1 and inv.has_active_child))
                         ORDER BY rateplan_seq) AS rp
                 ) AS dataset
            ) AS rate_plans
        from room_type rt
        WHERE rt.enabled
        order by order_seq
    )
    Select json_build_object(
        'inventory', (select json_agg(inv_data) from inv_data),
        'occupancy', (Select json_agg(OCC) from OCC)
    )
    into json_result;

    return json_result;
end

sp_get_inventory_js

Parameters

Definition