-- obtem restrições da biblioteca para o SAGU
CREATE OR REPLACE FUNCTION gtcObterRestricoesParaSagu(person int4)
RETURNS SETOF type_obter_restricoes as $BODY$
DECLARE
BEGIN
	RETURN QUERY
	SELECT
		'Penalidade' AS tipo,
		( SELECT COUNT()
		  FROM gtcpenalty
		  WHERE coalesce( penaltyEndDate > now(), penaltyEndDate IS NULL )
		  AND personid = person ) as quantidade
	UNION
	(
		SELECT 'Multas' AS tipo,
		( SELECT COUNT()
		  FROM gtcfine f
		  LEFT JOIN gtcloan l
		  ON f.loanid = l.loanid
		  WHERE finestatusid = ( SELECT value FROM basconfig WHERE parameter ='ID_FINESTATUS_OPEN' )::int
		  AND personid = person ) as quantidade
	)
	UNION
	(
		SELECT 'Total de Empréstimos' AS tipo,
		( SELECT count()
		  FROM gtcloan
		  WHERE personid = person
		  AND returndate is null ) as quantidade
	)
	UNION
	(
		SELECT 'Empréstimos atrasados' AS tipo,
		( SELECT count()
		  FROM gtcloan
		  WHERE personid = person
		  AND returndate is null
		  AND returnforecastdate < NOW ) as quantidade
	);
END;
$BODY$ language plpgsql;
