aonestar.public

Parameters

Name	Type	Mode
folio_id	integer	IN

Definition

WITH dataset AS (
		SELECT i.item_id, i.folio_seq, i.folio_name, i.note, 
            --i.close_date, i.close_time, i.close_user,
            coalesce(i.close_date, f.close_date) as close_date, 
            coalesce(i.close_time, f.close_time) as close_time, 
            coalesce(i.close_user, f.close_user) as close_user, 
            i.close_shift, 
		    i.charge_to_folio AS link_folio_id, link.folio_type AS link_folio_type,
		   CASE link.folio_type 
		        WHEN 'R' THEN 
		            (SELECT full_name FROM registration rg LEFT JOIN guest g ON g.id = rg.guest_id where rg.id = link.register_id) 
		        WHEN 'B' THEN
		            (SELECT full_name FROM booking bk LEFT JOIN guest g ON g.id = bk.guest_id where bk.id = link.booking_id)  
		        WHEN 'C' THEN 
		            (SELECT name FROM cash_sale cs where cs.folio_id = link.id)
		    END AS link_folio_name,
		   (SELECT coalesce(array_agg(row_to_json(trn)), '{}')  
		    FROM (SELECT t.id, t.charge_date AS "date", t.post_type, t.tran_type, dep.code, 
		            coalesce(t.description, dep."name") description, t.qty, 
		            t.amount, t.post_time, t.post_shift, t.post_user, t.reference, t.itemizers, t.remark,
		            t.voided, t.splitted, t.splitted_src, t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des,
		            not(t.voided OR t.transfer_out) AS active,
		            (SELECT COALESCE(json_agg(log_data), '[]')
		             FROM  (SELECT id, log_user AS user_name, log_type, log_text, log_date, log_time, log_shift AS shift
		                    FROM cashier_log l 
		                    WHERE l.tran_id  = t.id
		                    ) AS log_data
		            ) AS logs 
		         FROM transactions t LEFT JOIN department dep ON dep.id = t.dept_id 
		         WHERE (t.folio_item = i.item_id) 
		         ORDER BY t.post_time
		         ) AS trn
		    ) AS transactions                  
		FROM folio_items i 
            LEFT JOIN folio f on f.id = i.folio_id
		    LEFT JOIN folio link ON link.id = i.charge_to_folio 
		WHERE i.folio_id  = $1 AND (i.closed or f.status = 'C')
		ORDER BY i.close_time, i.folio_seq 
	)
	SELECT coalesce(json_agg(dataset), '[]') FROM dataset

sp_get_folio_history_js

Parameters

Definition