CREATE OR REPLACE VIEW searchmaterialview AS
--comentário qualquer
(
    (
    /* Ajuste da searchmaterialview para levar não precisar usar a preferencia
     * MATERIAL_TYPE_ID_PERIODIC_COLLECTION se tiver dúvidas do porque deste update
     * olhe o ticket #12511
     */
       --Busca todos as obras e une com o próprio exemplar
                 SELECT gtcmaterialcontrol.controlnumber, 
                        gtcmaterialcontrol.entrancedate, 
                        gtcmaterialcontrol.lastchangedate, 
                        gtcmaterialcontrol.category, 
                        gtcmaterialcontrol.level, 
                        gtcmaterialcontrol.materialgenderid, 
                        gtcmaterialcontrol.materialtypeid, 
                        gtcmaterialcontrol.materialphysicaltypeid, 
                        gtcexemplarycontrol.itemnumber AS exemplaryitemnumber, 
                        gtcexemplarycontrol.originallibraryunitid AS exemplaryoriginallibraryunitid, 
                        gtcexemplarycontrol.libraryunitid AS exemplarylibraryunitid, 
                        gtcexemplarycontrol.acquisitiontype AS exemplaryacquisitiontype, 
                        gtcexemplarycontrol.exemplarystatusid AS exemplaryexemplarystatusid, 
                        gtcexemplarycontrol.materialgenderid AS exemplarymaterialgenderid, 
                        gtcexemplarycontrol.materialtypeid AS exemplarymaterialtypeid, 
                        gtcexemplarycontrol.materialphysicaltypeid AS exemplarymaterialphysicaltypeid, 
                        gtcexemplarycontrol.entrancedate AS exemplaryentrancedate, 
                        gtcexemplarycontrol.lowdate AS exemplarylowdate
                   FROM gtcmaterialcontrol
              LEFT JOIN gtcexemplarycontrol USING (controlnumber)
              UNION
                 --Busca os dados do pai com a obra e exemplar do filho. A categoria e nível é do pai
                 SELECT gtcmaterialcontrol.controlnumberfather AS controlnumber, 
                        gtcmaterialcontrol.entrancedate, 
                        gtcmaterialcontrol.lastchangedate, 
                        gtcmaterialcontrolfather.category, 
                        gtcmaterialcontrolfather.level, 
                        gtcmaterialcontrol.materialgenderid, 
                        gtcmaterialcontrol.materialtypeid, 
                        gtcmaterialcontrol.materialphysicaltypeid, 
                        gtcexemplarycontrol.itemnumber AS exemplaryitemnumber, 
                        gtcexemplarycontrol.originallibraryunitid AS exemplaryoriginallibraryunitid, 
                        gtcexemplarycontrol.libraryunitid AS exemplarylibraryunitid, 
                        gtcexemplarycontrol.acquisitiontype AS exemplaryacquisitiontype, 
                        gtcexemplarycontrol.exemplarystatusid AS exemplaryexemplarystatusid, 
                        gtcexemplarycontrol.materialgenderid AS exemplarymaterialgenderid, 
                        gtcexemplarycontrol.materialtypeid AS exemplarymaterialtypeid, 
                        gtcexemplarycontrol.materialphysicaltypeid AS exemplarymaterialphysicaltypeid, 
                        gtcexemplarycontrol.entrancedate AS exemplaryentrancedate, 
                        gtcexemplarycontrol.lowdate AS exemplarylowdate
                   FROM gtcmaterialcontrol
              LEFT JOIN gtcexemplarycontrol USING (controlnumber)
             INNER JOIN gtcmaterialcontrol as gtcmaterialcontrolfather ON (gtcmaterialcontrol.controlnumberfather = gtcmaterialcontrolfather.controlnumber)
                  WHERE gtcmaterialcontrol.controlnumberfather IS NOT NULL
    )
    UNION
    (
             --Busca os dados de obra do PAI e relaciona com os dados do exemplare do filho
             SELECT a.controlnumberfather AS controlnumber, 
                    b.entrancedate, 
                    b.lastchangedate, 
                    b.category, 
                    b.level, 
                    b.materialgenderid, 
                    b.materialtypeid, 
                    b.materialphysicaltypeid, 
                    c.itemnumber AS exemplaryitemnumber, 
                    c.originallibraryunitid AS exemplaryoriginallibraryunitid, 
                    c.libraryunitid AS exemplarylibraryunitid, 
                    c.acquisitiontype AS exemplaryacquisitiontype, 
                    c.exemplarystatusid AS exemplaryexemplarystatusid, 
                    c.materialgenderid AS exemplarymaterialgenderid, 
                    c.materialtypeid AS exemplarymaterialtypeid, 
                    c.materialphysicaltypeid AS exemplarymaterialphysicaltypeid, 
                    c.entrancedate AS exemplaryentrancedate, 
                    c.lowdate AS exemplarylowdate
               FROM gtcmaterialcontrol a
          LEFT JOIN gtcmaterialcontrol b ON b.controlnumber = a.controlnumberfather
          LEFT JOIN gtcexemplarycontrol c ON a.controlnumber = c.controlnumber
              WHERE a.controlnumberfather IS NOT NULL)
    UNION
    (
             --Busca os dados de obra do filho e une com os exemplares do PAI
             SELECT gtcmaterialcontrol.controlnumber, 
                    gtcmaterialcontrol.entrancedate, 
                    gtcmaterialcontrol.lastchangedate, 
                    gtcmaterialcontrol.category, 
                    gtcmaterialcontrol.level, 
                    gtcmaterialcontrol.materialgenderid, 
                    gtcmaterialcontrol.materialtypeid, 
                    gtcmaterialcontrol.materialphysicaltypeid, 
                    gtcexemplarycontrol.itemnumber AS exemplaryitemnumber, 
                    gtcexemplarycontrol.originallibraryunitid AS exemplaryoriginallibraryunitid, 
                    gtcexemplarycontrol.libraryunitid AS exemplarylibraryunitid, 
                    gtcexemplarycontrol.acquisitiontype AS exemplaryacquisitiontype, 
                    gtcexemplarycontrol.exemplarystatusid AS exemplaryexemplarystatusid, 
                    gtcexemplarycontrol.materialgenderid AS exemplarymaterialgenderid, 
                    gtcexemplarycontrol.materialtypeid AS exemplarymaterialtypeid, 
                    gtcexemplarycontrol.materialphysicaltypeid AS exemplarymaterialphysicaltypeid, 
                    gtcexemplarycontrol.entrancedate AS exemplaryentrancedate, 
                    gtcexemplarycontrol.lowdate AS exemplarylowdate
               FROM gtcmaterialcontrol
               JOIN gtcexemplarycontrol ON gtcmaterialcontrol.controlnumberfather = gtcexemplarycontrol.controlnumber
    )
    UNION
    (
         --Busca os dados do kardex trazendo mesmo os que não tem exemplares
         SELECT gtcmaterialcontrol.controlnumber,
                gtcmaterialcontrol.entrancedate,
                gtcmaterialcontrol.lastchangedate,
                gtcmaterialcontrol.category,
                gtcmaterialcontrol.level,
                gtcmaterialcontrol.materialgenderid,
                gtcmaterialcontrol.materialtypeid,
                gtcmaterialcontrol.materialphysicaltypeid,
                ''::varchar(20) AS exemplaryitemnumber,
                gtckardexcontrol.libraryunitid AS exemplaryoriginallibraryunitid,
                gtckardexcontrol.libraryunitid AS exemplarylibraryunitid,
                gtckardexcontrol.acquisitiontype AS exemplaryacquisitiontype,
                null AS exemplaryexemplarystatusid,
                null AS exemplarymaterialgenderid,
                --Antes estava assim : (SELECT value FROM basconfig WHERE parameter = 'MATERIAL_TYPE_ID_PERIODIC_COLLECTION')::int AS exemplarymaterialtypeid,
                gtcmaterialcontrol.materialtypeid AS exemplarymaterialtypeid, 
                null AS exemplarymaterialphysicaltypeid,
                gtckardexcontrol.entrancedate AS exemplaryentrancedate,
                null AS exemplarylowdate
                FROM gtcmaterialcontrol
     RIGHT JOIN gtckardexcontrol USING (controlnumber) 
    )
);

create or replace view materiaisview as 

select gtcmaterialcontrol.controlnumber as numero_de_controle,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '950' and subfieldid = 'a' limit 1) as numero_da_obra,
gtcmaterialcontrol.controlnumberfather as pai,
obterNomeDaPlanilha(gtcmaterialcontrol.category, gtcmaterialcontrol.level) as planilha,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '245' and subfieldid = 'a' limit 1) as mat_titulo,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '100' and subfieldid = 'a' limit 1) as mat_autor,
classificacao.content as mat_classificacao,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '090' and subfieldid = 'b' limit 1) as mat_cutter,
classificacao.searchcontent as mat_classificacao_ordem,
classificacao.searchcontentforsearchmodule as mat_classificacao_pesquisa,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '250' and subfieldid = 'a' limit 1) as mat_edicao,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '260' and subfieldid = 'a' limit 1) as mat_imprenta_lugar,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '260' and subfieldid = 'b' limit 1) as mat_imprenta_editora,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '260' and subfieldid = 'c' limit 1) as mat_imprenta_data_publicacao,
area_de_conhecimento.content as mat_codigo_area_de_conhecimento,
(select description from gtcmarctaglistingoption WHERE option = area_de_conhecimento.content AND marctaglistingid = '901.b') as mat_area_de_conhecimento,
curso.content as mat_codigo_curso,
(select description from gtcmarctaglistingoption WHERE option = area_de_conhecimento.content AND marctaglistingid = '901.e') as mat_curso,
gtcmaterialcontrol.materialtypeid as mat_codigo_tipo_do_material,
gtcmaterialtype.description as mat_tipo_do_material,
gtcmaterialcontrol.materialphysicaltypeid as mat_codigo_tipo_fisico_do_material,
gtcmaterialphysicaltype.description as mat_tipo_fisico_do_material,
assunto.content as mat_assunto,
gtcexemplarycontrol.itemnumber as exe_numero_do_tombo,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '949' and subfieldid = 'v' and line = gtcexemplarycontrol.line limit 1) as exe_volume,
gtcexemplarycontrol.entrancedate as exe_data_de_entrada,
gtcexemplarycontrol.libraryunitid as exe_codigo_da_unidade,
gtclibraryunit.libraryname as exe_unidade,
gtcexemplarycontrol.exemplarystatusid as exe_codigo_do_estado,
gtcexemplarystatus.description as exe_estado,
gtcexemplarycontrol.materialgenderid as exe_codigo_do_genero,
gtcmaterialgender.description as exe_genero,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '949' and subfieldid = 'n' and line = gtcexemplarycontrol.line limit 1) as exe_patrimonio,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '949' and subfieldid = 'e' and line = gtcexemplarycontrol.line limit 1) as exe_exemplar,
(select content from gtcmaterial where controlnumber = gtcmaterialcontrol.controlnumber and fieldid = '949' and subfieldid = 't' and line = gtcexemplarycontrol.line limit 1) as exe_tomo

from gtcmaterialcontrol
left join gtcexemplarycontrol
on (gtcexemplarycontrol.controlnumber = gtcmaterialcontrol.controlnumber)
left join gtcmaterial as classificacao
on (classificacao.controlnumber = gtcmaterialcontrol.controlnumber and classificacao.fieldid = '090' and classificacao.subfieldid = 'a' and classificacao.line = 0) --Classificação
left join gtcmaterial as assunto
on (assunto.controlnumber = gtcmaterialcontrol.controlnumber and assunto.fieldid = '650' and assunto.subfieldid = 'a') --Assunto
left join gtclibraryunit
on (gtcexemplarycontrol.libraryunitid = gtclibraryunit.libraryunitid)
left join gtcmaterialtype
on (gtcmaterialcontrol.materialtypeid = gtcmaterialtype.materialtypeid)
left join gtcmaterialphysicaltype
on (gtcmaterialcontrol.materialphysicaltypeid = gtcmaterialphysicaltype.materialphysicaltypeid)
left join gtcmaterial area_de_conhecimento
on (area_de_conhecimento.controlnumber = gtcmaterialcontrol.controlnumber and area_de_conhecimento.fieldid = '901' and area_de_conhecimento.subfieldid = 'b')
left join gtcmaterial curso
on (curso.controlnumber = gtcmaterialcontrol.controlnumber and curso.fieldid = '901' and curso.subfieldid = 'e')
left join gtcexemplarystatus
on (gtcexemplarystatus.exemplarystatusid = gtcexemplarycontrol.exemplarystatusid)
left join gtcmaterialgender
on (gtcexemplarycontrol.materialgenderid = gtcmaterialgender.materialgenderid);