DROP VIEW IF EXISTS materiaisview;

create or replace view materiaisview as 

WITH autores AS (   
    select controle.controlnumber,
          array_to_string(array_cat(array_cat(array_cat(array_agg(DISTINCT(material_100a.content)), array_agg(DISTINCT(material_700a.content))), array_agg(DISTINCT(material_110a.content))), array_agg(DISTINCT(material_710a.content))), ' / ') as autor
     FROM gtcmaterialcontrol controle 
left join gtcmaterial material_100a 
       on (controle.controlnumber = material_100a.controlnumber 
           and material_100a.fieldid = '100' 
             and material_100a.subfieldid = 'a') 
left join gtcmaterial material_700a 
      on (controle.controlnumber =  material_700a.controlnumber 
          AND material_700a.fieldid = '700' 
            and material_700a.subfieldid = 'a')
left join gtcmaterial material_110a 
       on (controle.controlnumber = material_110a.controlnumber 
           and material_110a.fieldid = '110' 
             and material_110a.subfieldid = 'a')
left join gtcmaterial material_710a 
       on (controle.controlnumber = material_710a.controlnumber 
           and material_710a.fieldid = '710' 
             and material_710a.subfieldid = 'a')              
GROUP BY controle.controlnumber)

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 autor from autores where autores.controlnumber = gtcmaterialcontrol.controlnumber) 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' limit 1) 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' limit 1) 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,
gtcexemplarycontrol.materialtypeid as exe_codigo_tipo_do_material,
gtcmaterialtype_exe.description as exe_tipo_do_material,
gtcexemplarycontrol.materialphysicaltypeid as exe_codigo_tipo_fisico_do_material,
gtcmaterialphysicaltype_exe.description as exe_tipo_fisico_do_material,
(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)
left join gtcmaterialtype gtcmaterialtype_exe
on (gtcexemplarycontrol.materialtypeid = gtcmaterialtype_exe.materialtypeid)
left join gtcmaterialphysicaltype gtcmaterialphysicaltype_exe
on (gtcexemplarycontrol.materialphysicaltypeid = gtcmaterialphysicaltype_exe.materialphysicaltypeid);
