/*
* ORSON
* SQLFIDIAS
* docentes / dta ult atualiz do currículo
* usar conta gica que tem acesso no PLATAO 
*/

/*
*
* dados dos autores (docentes) e dados do lattes
*
*/
if object_id ('#TMP_GRAFOS') is not null
   drop table #TMP_GRAFOS
go
-- obtem dados em uma tabela temporaria
-- Ativos
select  C.idfcpq, V.nompes, U.sglund, S.nomabvset, datepart(yy,V.dtainivin) anoini
  into #TMP_GRAFOS
  from VINCULOPESSOAUSP V, UNIDADE U, SETOR S, CONTROLE_CARGACNPQ C
 where V.nomcaa    =  "Docente" and
       V.dtafimvin =  NULL     and
       V.tipcon   !=  "Doc Apos" and
       U.codund    =  V.codund and
       S.codset    =* V.codset and
       V.codpes    =  C.codpes and
       C.idfcpq != null and
       C.dtaultalt != null 
UNION
-- Aposentados Credenciados na Pós-graduacao
select  C.idfcpq, V.nompes, U.sglund, S.nomabvset, datepart(yy,V.dtainivin) anoini
from VINCULOPESSOAUSP V, UNIDADE U, SETOR S, CONTROLE_CARGACNPQ C
 where V.nomcaa     =  "Docente"  and
       V.dtafimvin  =  null       and
       V.tipcon     =  "Doc Apos" and
       U.codund     =  V.codund   and
       S.codset     =* V.codset   and
       V.codpes     =  C.codpes   and
       C.idfcpq    !=  null       and
       C.dtaultalt !=  null       and
       V.codpes in (select distinct PLATAO..R25CRECREDOC.codpes 
                               from PLATAO..R25CRECREDOC, PESSOA
                              where PLATAO..R25CRECREDOC.codpes = PESSOA.codpes and
                                    PLATAO..R25CRECREDOC.dtavalini   <= getdate()     and
                                   (PLATAO..R25CRECREDOC.dtavalfim   >= getdate()     or
                                    PLATAO..R25CRECREDOC.dtavalfim    = null))
order by U.sglund, S.nomabvset, V.nompes

-- remove setores e unidades null
delete #TMP_GRAFOS where sglund=null
delete #TMP_GRAFOS where nomabvset=null

-- tratamento para centros, museus e institutos
update #TMP_GRAFOS set sglund="CEBIMar", nomabvset="CEBIMar" where sglund="CEBIMar"
update #TMP_GRAFOS set sglund="MAC", nomabvset="MAC" where sglund="MAC"
update #TMP_GRAFOS set sglund="MAE", nomabvset="MAE" where sglund="M A E"
update #TMP_GRAFOS set sglund="MP", nomabvset="MP" where sglund="MP"
update #TMP_GRAFOS set sglund="MZ", nomabvset="MZ" where sglund="MZ"
update #TMP_GRAFOS set sglund="IEA", nomabvset="IEA" where sglund="IEA"
update #TMP_GRAFOS set sglund="IEB", nomabvset="IEB" where sglund="IEB"
update #TMP_GRAFOS set sglund="IEE", nomabvset="IEE" where sglund="IEE"
update #TMP_GRAFOS set sglund="IMT", nomabvset="IMT" where sglund="IMT"

select  rtrim(idfcpq) + "|" + 
        rtrim(nompes) + "|" + 
        convert(char(4),anoini)+"-HOJE" + "|" +        
        lower(rtrim(sglund))+ "|" + 
        lower(rtrim(nomabvset)) as autor
  from #TMP_GRAFOS
order by sglund, nomabvset, nompes


/*
* obtem unidade e departamentos 
* Coloca "Departamento de " antes do nome do Depto de Ensino
*/
if object_id ('#TMP_GRAFOS') is not null
   drop table #TMP_GRAFOS
go

-- obtem os dados em uma tabela temporaria
select  distinct S.tipset, U.sglund, S.nomabvset, U.nomund, S.nomset
into #TMP_GRAFOS    
  from VINCULOPESSOAUSP V, UNIDADE U, SETOR S, CONTROLE_CARGACNPQ C
 where V.nomcaa    =  "Docente" and
       V.dtafimvin =  NULL     and
       U.codund    =  V.codund and
       S.codset    =* V.codset and
       S.dtadtvset = null and
       V.codpes    =  C.codpes and
       C.idfcpq != null and
       C.dtaultalt != null

-- tratamento para centros, museus e institutos
update #TMP_GRAFOS set nomabvset="CEBIMar" where sglund="CEBIMar"
update #TMP_GRAFOS set nomset="Centro de Biologia Marinha" where sglund="CEBIMar"

delete from #TMP_GRAFOS where sglund="IEE" and nomabvset != "IEE"
update #TMP_GRAFOS set nomset="Instituto Eletrotécnica Energia" where sglund="IEE"

update #TMP_GRAFOS set nomset="Instituto de Estudos Brasileiros" where sglund="IEB"

delete from #TMP_GRAFOS where sglund="M A E" and nomabvset != "MAE-USP"
update #TMP_GRAFOS set sglund="MAE", nomabvset="MAE" where sglund="M A E"

delete from #TMP_GRAFOS where sglund="MAC" and nomabvset != "MAC-USP"
update #TMP_GRAFOS set nomabvset="MAC", nomset="Museu Arte Contemporânea" where sglund="MAC"

delete from #TMP_GRAFOS where sglund="MP" and nomabvset != "MP"
update #TMP_GRAFOS set nomset="Museu Paulista" where sglund="MP"

delete from #TMP_GRAFOS where sglund="MZ" and nomabvset != "MZ"
update #TMP_GRAFOS set nomset="Museu de Zoologia" where sglund="MZ"

-- formato final
select  distinct "depto"=
    case
        when tipset!="Departamento de Ensino"
            then rtrim(lower(sglund)) + "|" +rtrim(lower(nomabvset)) + "|" +rtrim(nomund) + "|" + rtrim(nomset)
        when tipset="Departamento de Ensino"
            then rtrim(lower(sglund)) + "|" +rtrim(lower(nomabvset)) + "|" +rtrim(nomund) + "|Departamento de " + rtrim(nomset)
    end
from #TMP_GRAFOS