--CREATE OR REPLACE DIRECTORY censo_dir AS 'c:\censo';

declare
  v_cpf VARCHAR2(11) := '0';
  v_ano_base NUMBER := 2014;
  v_id_ies NUMBER :=587;
  fileHandler UTL_FILE.FILE_TYPE;
  
begin
  fileHandler := UTL_FILE.FOPEN('/home/oracle/censo', 'arquivoCensoDiscenteUFRPE-FULL' || to_char(sysdate,'YYYYMMDDHH24MI') || '.txt', 'W');
  UTL_FILE.PUTF(fileHandler,'40|' || v_id_ies || '|4\n');
  
  for r in (
    select '41' as TipoRegistro,
           null as IdAlunoInep,
           null as IdAlunoIes,
           translate(p.nm_pess,'âàãáÁÂÀÃÄéèêÈÉÊìíÌÍòóôõÒÓÔÕùüúÙÜÚÇç','AAAAAAAAAEEEEEEIIIIOOOOOOOOUUUUUUCC') as Nome,
	         p.nm_cpf_pess as CPF,
           case p.nm_sigl_pais_nac
                when 'BRA' then ''
                else p.nu_pass
           end as Passaporte,
           to_char(p.dt_nasc,'DDMMYYYY') as DataNascimento,
           case p.nm_sexo
                when 'M' then '0'
                else '1'
           end as Sexo,
           case 
                when v.nu_ano_admis >= 2014 and p.nm_cor_raca=7 then '0'
                when v.nu_ano_admis >= 2014 and p.nm_cor_raca<>7 then p.nm_cor_raca
                else '6'
           end as Cor,
           translate(p.nm_mae,'âàãáÁÂÀÃÄéèêÈÉÊìíÌÍòóôõÒÓÔÕùüúÙÜÚÇç','AAAAAAAAAEEEEEEIIIIOOOOOOOOUUUUUUCC') as NomeMae,
           case p.nm_sigl_pais_nac
                when 'BRA' then '1'
                else '3'
           end as Nacionalidade,
           case 
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483') then '26'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='9668' then '35'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat is null then '26'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat not in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483','9668') then
                    (select uf.cd_uf
                       from ibge_uf uf,                   
                            ibge_municipio mu,
                            siga_cidade ci
                      where uf.nm_sigla=ci.nm_sigl_uf
                        and mu.cd_uf=uf.cd_uf
                        and mu.nm_munic=ci.nm_cidd
                        and ci.cd_cidd=p.cd_cidd_nat
                     ) 
                else null
           end as UFNascimento,
           case 
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5136' then '2600104'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5154' then '2601201'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5189' then '2602902'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5198' then '2603454'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5216' then '2604106'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5255' then '2605707'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5259' then '2606002'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat in ('5301','5302') then '2607901'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5362' then '2609600'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5384' then '2610707'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5406' then '2611606'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5416' then '2612208'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5453' then '2613909'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5483' then '2615706'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='9668' then '3550308'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat is null then '2611606'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat not in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483','9668') then                    
                     (select mu.cd_munic
                        from ibge_uf uf,                   
                             ibge_municipio mu,
                             siga_cidade ci
                       where uf.nm_sigla=ci.nm_sigl_uf
                         and mu.cd_uf=uf.cd_uf
                         and mu.nm_munic=ci.nm_cidd
                          and ci.cd_cidd=p.cd_cidd_nat
                      )
                else null
           end as MunicipioNascimento,           
           p.nm_sigl_pais_nac as PaisOrigem,
           '0' as AlunoDeficiencia,
           null as DeficienciaCegueira,
           null as DeficienciaVisao,
           null as DeficienciaSurdez,
           null as DeficienciaAuditiva,
           null as DeficienciaFisica,
           null as DeficienciaSurdoCegueira,
           null as DeficienciaMultipla,
           null as DeficienciaIntelectual,
           null as DeficienciaAutismo,
           null as DeficienciaAsperger,
           null as DeficienciaRett,
           null as DeficienciaDesintegrativo,
           null as DeficienciaSuperdotacao,
           '42' as TipoRegistro42,
           substr(sa.cd_perd_letv,6) as SemestreReferencia,
           pf.cd_inep as IdCurso,
           case mpf.cd_modal
                when 1 then (
                             select pe.nm_codigo
                               from siga_vinculo_polo_ead vpe,
                                    siga_polo_ead pe
                              where vpe.cd_polo_ead=pe.cd_polo_ead
                                and vpe.nu_matr_curso=v.nu_matr_curso
                                and vpe.cd_perd_letv=(
                                    select max(vpe1.cd_perd_letv)
                                      from siga_vinculo_polo_ead vpe1
                                     where vpe1.nu_matr_curso=vpe.nu_matr_curso
                                       and vpe1.cd_polo_ead=vpe.cd_polo_ead
                                       and substr(vpe1.cd_perd_letv,1,4)<=v_ano_base)                               
                            )
                else null 
           end as Polo,
           case  
                when tv.cd_turno in (1,6) and mpf.cd_modal<>1 then 4
                when tv.cd_turno=2 and mpf.cd_modal<>1 then 1
                when tv.cd_turno=3 and mpf.cd_modal<>1 then 2
                when tv.cd_turno in (4,5) and mpf.cd_modal<>1 then 3
                else null
           end as Turno,
           case 
                when sa.cd_tp_sit_acad in (1,19,20) then 2
                when sa.cd_tp_sit_acad in (2,3) then 3
                when sa.cd_tp_sit_acad in (6,7,8,17) then 4
                when sa.cd_tp_sit_acad=9 then 5
                when sa.cd_tp_sit_acad in (4,21) then 6
                else null
           end as SituacaoVinculo,
           case                 
                when v.nu_ano_admis || '.' || v.nu_semtr_admis = sa.cd_perd_letv and v.cd_tp_ingrs='6' then
                     (select pf1.cd_inep
                        from siga_programa_formacao pf1,
                             siga_vinculo v1,
                             siga_situacao_academica sa1
                       where pf1.cd_progr_form=v1.cd_progr_form
                         and v1.nu_matr_curso<>v.nu_matr_curso
                         and v1.nm_cpf_pess=v.nm_cpf_pess
                         and pf1.cd_nivel='1'
                         and v1.nu_matr_curso=sa1.nu_matr_curso
                         and sa1.cd_perd_letv=sa.cd_perd_letv
                         and sa1.cd_tp_sit_acad=9
                         and sa1.dt_def_sit=(select max(sa2.dt_def_sit)
                                               from siga_situacao_academica sa2
                                              where sa1.nu_matr_curso=sa2.nu_matr_curso
                                                and sa1.cd_perd_letv=sa2.cd_perd_letv
                                            )
                      )                                            
                else null
           end as CursoOrigem,          
           null as SemestreConclusao,
           case
                when pf.tp_grad=1 and pf.cd_progr_form in (120,121) then 1
                when pf.tp_grad=1 and pf.cd_progr_form not in (120,121) then 0
                else null
           end as AlunoParfor,
           LPAD(v.nu_semtr_admis,2,'0') || v.nu_ano_admis as SemestreIngresso,
           case 
                when v.nu_ano_admis >= 2013 then v.nm_origem_ensinomedio
                else '2'
           end as TipoEscola,
           case
                when v.cd_tp_ingrs=7 and v.nu_ano_admis<2010 then 1
                else 0
           end as IngressoVestibular,
           case
                when v.cd_tp_ingrs=7 and v.nu_ano_admis>=2010 then 1
                when v.cd_tp_ingrs=15 then 1
                else 0
           end as IngressoEnem,
           '0' as IngressoAvaliacaoSeriada,
           case
                when v.cd_tp_ingrs=8 then 1                
                else 0
           end as IngressoSelecaoSimplificada,
           case
                when v.cd_tp_ingrs=1 and p.nm_sigl_pais_nac<>'BRA' then 1
                else 0
           end as IngressoPecG,
           case
                when v.cd_tp_ingrs=10 then 1
                else 0
           end as IngressoExOficio,
           case
                when v.cd_tp_ingrs=9 then 1
                else 0
           end as IngressoDecisaoJudicial,
           case
                when v.cd_tp_ingrs in (7,8,9,10,15) then 0
                when v.cd_tp_ingrs=1 and p.nm_sigl_pais_nac='BRA' then 1
                when v.cd_tp_ingrs=1 and p.nm_sigl_pais_nac<>'BRA' then 0
                else 1
           end as IngressoVgReman,          
           '0' as IngressoVgProgEsp,
           case               
                when sa.cd_tp_sit_acad in (1,19,20) then 0
                else null
           end as MobilidadeAcademica,
           null as TipoMobilidadeAcademica,
           null as IESDestino,
           null as TpMobAcadInternacional,
           null as PaisDestino,
           case               
                when v.cd_tipo_cota<>0 then 1
                else 0
           end as Reserva,
           case               
                when v.cd_tipo_cota=0 or v.cd_tipo_cota is null then null
                when v.cd_tipo_cota in (3,5) and p.nm_cor_raca in (2,3,5) then 1
                else 0
           end as ReservaEtnico,
           case               
                when v.cd_tipo_cota=0 or v.cd_tipo_cota is null then null
                when v.cd_tipo_cota in (7) then 1
                else 0
           end as ReservaDeficiencia,
           case               
                when v.cd_tipo_cota=0 or v.cd_tipo_cota is null then null                
                else 1
           end as ReservaEnsinoPublico,
           case               
                when v.cd_tipo_cota=0 or v.cd_tipo_cota is null then null
                when v.cd_tipo_cota in (3,4) then 1
                else 0
           end as ReservaSocial,
           case               
                when v.cd_tipo_cota=0 or v.cd_tipo_cota is null then null
                when v.cd_tipo_cota in (8) then 1
                else 0
           end as ReservaOutros,
           null as Financiamento,
           '0' as ApoioSocial,
           '0' as AtividadeExtraCurricular
      from siga_pessoa p,
           siga_vinculo v,
           siga_programa_formacao pf,
           siga_turno_vinculo tv,
           siga_situacao_academica sa,
           siga_periodo_letivo pl,           
           siga_modal_progr_form mpf
     where p.nm_cpf_pess=v.nm_cpf_pess
       and v.cd_progr_form=pf.cd_progr_form
       and v.nu_matr_curso=tv.nu_matr_curso
       and tv.cd_perd_letv=(select max(tv1.cd_perd_letv)
                              from siga_turno_vinculo tv1
                             where tv1.nu_matr_curso=tv.nu_matr_curso)                                
       and sa.nu_matr_curso=v.nu_matr_curso
 /*      and sa.cd_perd_letv=(select max(sa1.cd_perd_letv)
                              from siga_situacao_academica sa1
                             where sa.nu_matr_curso=sa1.nu_matr_curso
                               and sa1.cd_perd_letv in (v_ano_base || '.1', v_ano_base || '.2')
                           )*/
       and sa.cd_perd_letv in (v_ano_base || '.1', v_ano_base || '.2')  
       and sa.cd_tp_sit_acad not in (18)
       and sa.dt_def_sit=(select max(sa2.dt_def_sit)
                            from siga_situacao_academica sa2
                           where sa.nu_matr_curso=sa2.nu_matr_curso
                             and sa.cd_perd_letv=sa2.cd_perd_letv
                         )                            
       and not exists (select 8
                         from siga_vinculo v1
                        where v1.nm_cpf_pess=v.nm_cpf_pess
                          and v1.nu_matr_curso<>v.nu_matr_curso
                          and v1.cd_progr_form=v.cd_progr_form
                          and v1.nu_ano_admis || '.' || v1.nu_semtr_admis > v.nu_ano_admis || '.' || v.nu_semtr_admis)
       and v.nu_ano_admis || '.' || v.nu_semtr_admis=pl.cd_perd_letv                                
       and pf.cd_progr_form = mpf.cd_progr_form
       and pf.cd_nivel='1'
       and pf.cd_progr_form not in (14,15)
      -- and mpf.cd_modal<>1
      -- and (p.nm_pess like ('A%') OR p.nm_pess like ('Á%') OR p.nm_pess like ('Â%'))
      -- and (p.nm_pess like ('B%') OR p.nm_pess like ('C%') OR p.nm_pess like ('D%') OR p.nm_pess like ('E%') OR p.nm_pess like ('É%') OR p.nm_pess like ('Ê%') OR p.nm_pess like ('F%'))
      -- and (p.nm_pess like ('G%') OR p.nm_pess like ('H%') OR p.nm_pess like ('I%') OR p.nm_pess like ('Í%') OR p.nm_pess like ('J%') OR p.nm_pess like ('K%'))
      -- and (p.nm_pess like ('L%') OR p.nm_pess like ('M%') OR p.nm_pess like ('N%'))
      -- and (p.nm_pess like ('O%') OR p.nm_pess like ('Ó%') OR p.nm_pess like ('Ô%') OR p.nm_pess like ('P%'))
      -- and (p.nm_pess like ('Q%') OR p.nm_pess like ('R%') OR p.nm_pess like ('S%') OR p.nm_pess like ('T%') OR p.nm_pess like ('U%') OR p.nm_pess like ('Ú%')) 
      -- and (p.nm_pess like ('V%') OR p.nm_pess like ('X%') OR p.nm_pess like ('W%') OR p.nm_pess like ('Y%') OR p.nm_pess like ('Z%')) 
      -- and p.nm_pess like ('Z%')
      -- and p.nm_cpf_pess in ('08023550489')    
    order by p.nm_pess,
             p.nm_cpf_pess,
             sa.cd_perd_letv
  ) loop
    if v_cpf <> r.CPF then
       UTL_FILE.PUTF(fileHandler,       
                            r.TipoRegistro || '|' ||
                            r.IdAlunoInep || '|' ||
                            r.IdAlunoIes || '|' ||
                            r.Nome || '|' ||
	                          r.CPF || '|' ||
                            r.Passaporte || '|' ||
                            r.DataNascimento || '|' ||
                            r.Sexo || '|' ||
                            r.Cor || '|' ||
                            r.NomeMae || '|' ||
                            r.Nacionalidade || '|' ||
                            r.UFNascimento || '|' ||
                            r.MunicipioNascimento || '|' ||
                            r.PaisOrigem || '|' ||
                            r.AlunoDeficiencia || '|' ||
                            r.DeficienciaCegueira || '|' ||
                            r.DeficienciaVisao || '|' ||
                            r.DeficienciaSurdez || '|' ||
                            r.DeficienciaAuditiva || '|' ||
                            r.DeficienciaFisica || '|' ||
                            r.DeficienciaSurdoCegueira || '|' ||
                            r.DeficienciaMultipla || '|' ||
                            r.DeficienciaIntelectual || '|' ||
                            r.DeficienciaAutismo || '|' ||
                            r.DeficienciaAsperger || '|' ||
                            r.DeficienciaRett || '|' ||
                            r.DeficienciaDesintegrativo || '|' ||
                            r.DeficienciaSuperdotacao                            
                            );                                       
    end if;
    
    UTL_FILE.PUTF(fileHandler,    
                         r.TipoRegistro42 || '|' ||
                         r.SemestreReferencia || '|' ||
                         r.IdCurso || '|' ||
                         r.Polo || '|' ||
                         r.Turno || '|' ||
                         r.SituacaoVinculo || '|' ||
                         r.CursoOrigem || '|' ||
                         r.SemestreConclusao || '|' ||
                         r.AlunoParfor || '|' ||
                         r.SemestreIngresso || '|' ||
                         r.TipoEscola || '|' ||
                         r.IngressoVestibular || '|' ||
                         r.IngressoEnem || '|' ||
                         r.IngressoAvaliacaoSeriada || '|' ||
                         r.IngressoSelecaoSimplificada || '|' ||
                         r.IngressoPecG || '|' ||
                         r.IngressoExOficio || '|' ||
                         r.IngressoDecisaoJudicial || '|' ||                         
                         r.IngressoVgReman || '|' ||
                         r.IngressoVgProgEsp || '|' ||
                         r.MobilidadeAcademica || '|' ||
                         r.TipoMobilidadeAcademica || '|' ||
                         r.IESDestino || '|' ||
                         r.TpMobAcadInternacional || '|' ||
                         r.PaisDestino || '|' ||
                         r.Reserva || '|' ||
                         r.ReservaEtnico || '|' ||
                         r.ReservaDeficiencia || '|' ||
                         r.ReservaEnsinoPublico || '|' ||
                         r.ReservaSocial || '|' ||
                         r.ReservaOutros || '|' ||
                         r.Financiamento || '||||||||||||' ||
                         r.ApoioSocial || '|||||||' ||
                         r.AtividadeExtraCurricular || '||||||||'                        
                         );    
                                       
    v_cpf := r.CPF;    
  end loop;
  UTL_FILE.FCLOSE(fileHandler);
end;