-- Obtém exemplares do da regra.
CREATE OR REPLACE FUNCTION getMaterialLocationExemplaries(p_materiallocationid int, p_itemnumber varchar)
RETURNS TABLE (itemnumber varchar) AS
/*************************************************************************************
  NAME: getMaterialLocationExemplaries
  DESCRIPTION: Obtém os exemplares que são atendidos pela regra de localização.
**************************************************************************************/
$BODY$ 
BEGIN
    RETURN QUERY 
    SELECT EXEMPLARY.itemnumber 
      FROM gtcexemplarycontrol EXEMPLARY
INNER JOIN gtcmaterial MATERIAL ON (EXEMPLARY.controlnumber = MATERIAL.controlnumber AND MATERIAL.fieldid = '090' AND MATERIAL.subfieldid = 'a')
INNER JOIN gtcmaterial KNOWLEDGE ON (EXEMPLARY.controlnumber = KNOWLEDGE.controlnumber AND KNOWLEDGE.fieldid = '901' AND KNOWLEDGE.subfieldid = 'b')
WHERE 
	-- Se tiver filtros 'Inclui', faz a pesquisa por eles, caso contrário desconsidera o filtro.
	CASE WHEN (SELECT count(*) FROM gtcmateriallocationclassification C1 WHERE C1.materiallocationid = p_materiallocationid) > 0 
	THEN
	    p_materiallocationid IN (SELECT I.materiallocationid 
	            FROM gtcmateriallocationclassification I 
	           WHERE preparetopographicindexsearch(removePrefixForClassification(MATERIAL.content), '') 
	         BETWEEN preparetopographicindexsearch(removePrefixForClassification(I.beginclassification), '') 
		     AND preparetopographicindexsearch(removePrefixForClassification(I.endclassification), '') 
		     AND I.materiallocationid = p_materiallocationid ) 
	ELSE
	    TRUE
	END
	AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationmaterialtype C1 WHERE C1.materiallocationid = p_materiallocationid AND C1.rule = 'I') > 0 
                      THEN  
                          EXEMPLARY.materialtypeid IN (SELECT I.materialtypeid FROM gtcmateriallocationmaterialtype I WHERE I.rule = 'I' AND I.materiallocationid = p_materiallocationid) 
                      ELSE
                          TRUE
                      END
                      -- Se tiver filtros 'Exclui', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                      AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationmaterialtype C2 WHERE C2.materiallocationid = p_materiallocationid AND C2.rule = 'E') > 0 
                      THEN
                          EXEMPLARY.materialtypeid NOT IN (SELECT E.materialtypeid from gtcmateriallocationmaterialtype E where E.rule = 'E' AND E.materiallocationid = p_materiallocationid)
                      ELSE
                          TRUE
                      END
       AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationmaterialphysicaltype C1 WHERE C1.materiallocationid = p_materiallocationid AND C1.rule = 'I') > 0 
                       THEN  
                           EXEMPLARY.materialphysicaltypeid IN (SELECT I.materialphysicaltypeid from gtcmateriallocationmaterialphysicaltype I where I.rule = 'I' AND I.materiallocationid = p_materiallocationid) 
                       ELSE
                           TRUE
                       END
                       -- Se tiver filtros 'E', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                       AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationmaterialphysicaltype C2 WHERE C2.materiallocationid = p_materiallocationid AND C2.rule = 'E') > 0 
                       THEN
                           EXEMPLARY.materialphysicaltypeid NOT IN (SELECT E.materialphysicaltypeid from gtcmateriallocationmaterialphysicaltype E where E.rule = 'E' AND E.materiallocationid = p_materiallocationid)
                       ELSE
                           TRUE
                       END
         AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationlibraryunit C1 WHERE C1.materiallocationid = p_materiallocationid AND C1.rule = 'I') > 0 
                      THEN  
                          EXEMPLARY.libraryunitid IN (SELECT I.libraryunitid from gtcmateriallocationlibraryunit I WHERE I.rule = 'I' AND I.materiallocationid = p_materiallocationid) 
                      ELSE
                          TRUE
                      END
                      -- Se tiver filtros 'E', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                      AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationlibraryunit C2 WHERE C2.materiallocationid = p_materiallocationid AND C2.rule = 'E') > 0 
                      THEN
                          EXEMPLARY.libraryunitid NOT IN (SELECT E.libraryunitid FROM gtcmateriallocationlibraryunit E WHERE E.rule = 'E' AND E.materiallocationid = p_materiallocationid)
                      ELSE
                          TRUE
                      END
         AND  CASE WHEN (SELECT count(*) FROM gtcmateriallocationexemplarystatus C1 WHERE C1.materiallocationid = p_materiallocationid AND C1.rule = 'I') > 0 
                       THEN  
                           EXEMPLARY.exemplarystatusid IN (SELECT I.exemplarystatusid FROM gtcmateriallocationexemplarystatus I WHERE I.rule = 'I' AND I.materiallocationid = p_materiallocationid) 
                       ELSE
                           TRUE
                       END
                       -- Se tiver filtros 'E', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                       AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationexemplarystatus C2 WHERE C2.materiallocationid = p_materiallocationid AND C2.rule = 'E') > 0 
                       THEN
                           EXEMPLARY.exemplarystatusid NOT IN (SELECT E.exemplarystatusid FROM gtcmateriallocationexemplarystatus E WHERE E.rule = 'E' AND E.materiallocationid = p_materiallocationid)
                       ELSE
                           TRUE
                       END
         AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationknowledgearea C1 WHERE C1.materiallocationid = p_materiallocationid AND C1.rule = 'I') > 0 
                       THEN  
                           KNOWLEDGE.content IN (SELECT I.knowledgeareaid FROM gtcmateriallocationknowledgearea I WHERE I.rule = 'I' AND I.materiallocationid = p_materiallocationid) 
                       ELSE
                           TRUE
                       END
                       -- Se tiver filtros 'E', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                       AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationknowledgearea C2 WHERE C2.materiallocationid = p_materiallocationid AND C2.rule = 'E') > 0 
                       THEN
                           KNOWLEDGE.content NOT IN (SELECT E.knowledgeareaid FROM gtcmateriallocationknowledgearea E WHERE E.rule = 'E' AND E.materiallocationid = p_materiallocationid)
                       ELSE
                           TRUE
                       END
         AND CASE WHEN p_itemnumber <> '' THEN EXEMPLARY.itemnumber = p_itemnumber ELSE TRUE END;                                       
      
END; 
$BODY$ 
language plpgsql;
