aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN

Definition

WITH fol AS (
		SELECT IIF(folio_type = 'R', register_id, NULL) AS register_id, 
			   IIF(folio_type = 'B', booking_id, NULL) AS booking_id 
		FROM folio WHERE id = $1 
	), schedule AS (
		SELECT charge_date AS "date", rm.room_number, charge_type, dp."name" AS department, charge_qty AS qty, charge_amount AS amount, total_amount, charge_to_booking, posted 
		FROM fol, LATERAL sp_get_charge_schedule(register_id => fol.register_id, booking_id => fol.booking_id) AS cs	
			LEFT JOIN department dp ON dp.id = cs.charge_dept  
			LEFT JOIN room rm ON rm.id = cs.room_id
	), rooms AS (
		SELECT "date", room_number, department, qty, amount, total_amount, charge_to_booking, posted 
		FROM schedule cs WHERE charge_type = 'room'
		ORDER BY 1,2
	), inclusions AS (
		SELECT "date", room_number, department, qty, amount, total_amount, charge_to_booking, posted 
		FROM schedule cs WHERE charge_type = 'inclusion'
		ORDER BY 1,2
	), other AS (
		SELECT charge_date AS "date", rm.room_number, dp."name" AS department, charge_qty AS qty, charge_amount AS amount, total_amount, cs.charge_to_booking, posted 
		FROM fol, charge_schedule cs 
			LEFT JOIN department dp ON dp.id = cs.charge_dept  
			LEFT JOIN registration rg ON rg.id = cs.register_id
			LEFT JOIN room rm ON rm.id = rg.room_id
		WHERE cs.register_id = fol.register_id OR cs.booking_id = fol.booking_id
		ORDER BY 1,2
	)
	SELECT json_build_object(
		'room_charges', (SELECT COALESCE(json_agg(rooms), '[]') FROM rooms),
		'inclusion_charges', (SELECT COALESCE(json_agg(inclusions), '[]') FROM inclusions),
		'other_charges', (SELECT COALESCE(json_agg(other), '[]') FROM other)
	)

sp_get_charge_schedule1_js

Parameters

Definition