--PROCEDIMENTO PARA MONTAGEM DO ARQUIVO CENSO 2014 NA TABELA TEMPORÁRIA
CREATE TABLE MIGRACAO_AUX (REGISTRO_TEXTO VARCHAR2(2000 BYTE), ORDEM NUMBER);

declare
  v_cpf VARCHAR2(11):= '0';
  v_ano_base NUMBER:= 2015;
  v_id_ies NUMBER:=587;
  registro_texto varchar2(2000):= '';
  ordem number := 1;
  
begin
  registro_texto := '40|' || v_id_ies || '|';
  insert into migracao_aux values (registro_texto, ordem);
  ordem := ordem + 1; 
  
  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 >= 2013 and p.nm_cor_raca=7 then '0' 
                when v.nu_ano_admis >= 2013 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 (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) and (v.nm_origem_ensinomedio = '2')) then '0' 
                when ((v.nu_ano_admis >= 2013) and (v.nm_origem_ensinomedio = '3')) then '1' 
                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,
           pe.nu_carga_horr_tot as CHTotalCursoAluno,
           
           case
             when exists (select 8
                          from siga_dado_hist dh1
                          where dh1.nu_matr_curso=v.nu_matr_curso
                            and dh1.cd_sit_hist IN (1,7,8,9)
                         ) then
                           (SELECT SUM(cc.nu_carga_horr_tot)
                              FROM siga_dado_hist dh, 
                                   siga_atividade_academica aa, 
                                   siga_componente_curricular cc
                             WHERE dh.nu_matr_curso = v.nu_matr_curso
                               AND dh.cd_ativ_acad = aa.cd_ativ_acad
                               AND aa.cd_compnt_curr = cc.cd_compnt_curr
                               AND dh.cd_sit_hist IN (1,7,8,9)
                               AND pe.nu_carga_horr_tot <> 0
                           ) 
                           else 0
           end as CHIntegralizadaAluno
         
      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,
           siga_perfil pe,
           siga_vinculo_perfil vp           
     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 v.nu_matr_curso=vp.nu_matr_curso
       and vp.cd_perf=pe.cd_perf
       and vp.tp_perf=pe.tp_perf
       and vp.cd_perd_letv=(select max(vp1.cd_perd_letv)
                              from siga_vinculo_perfil vp1
                             where vp1.nu_matr_curso=vp.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 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 sa2.cd_tp_sit_acad <> 4
                         )                            
       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
     registro_texto := 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;  
      insert into migracao_aux values (registro_texto, ordem);
      ordem := ordem + 1;
    end if;
    
       registro_texto := 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 || '||||||||',
                         r.AtividadeExtraCurricular || '||||||||' ||
                         r.CHTotalCursoAluno || '|' ||
                         r.CHIntegralizadaAluno;
                         
    insert into migracao_aux values (registro_texto, ordem);
    ordem := ordem + 1;                                   
    v_cpf := r.CPF;    
  end loop;
end;  


-- GERAR O ARQUIVO CENSO 2015
--SELECT registro_texto from MIGRACAO_AUX order by ordem;



--ELIMINAR A TABELA TEMPORÁRIA MIGRACAO_AUX 
--DROP TABLE MIGRACAO_AUX;


/*
Caso o problema do SQL acima apresente erros devido a uma sub-consulta que devia retornar
apenas uma linha e retornou mais do que isso. Neste caso, é provável que o problema seja o pólo
dos alunos, ou seja, há mais de um pólo com o mesmo período letivo para o mesmo discente. Desse
modo, deve-se fazer a consulta do Item (01). Após isso, verifica no SIG@, através do CPF do aluno
para saber qual o pólo correto do discente e remove a tupla que não possui o polo determinado pelo SIG@.
Após essa análise, consulte o aluno atraves do Item (02), informando a matricula do curso e o periodo letivo.
Com o polo correto determinado pelo SIG@, remova a tupla, especificado no Item (03), que não está armazenada em Vinculo pelo aplicativo SIG@
(Adm. Sistemas -> Recursos Humanos -> Vinculo ->. A remoção é feita atraves das seguintes informações: matricula do curso, periodo letivo e o codigo do polo


-- Item (01)- A: Retorna todos discentes que possuem o mesmo periodo letivo em mais de um polo. Consulta pela matricula do curso e periodo letivo
--select count(vpe.cd_polo_ead), sv.NU_MATR_CURSO, vpe.CD_PERD_LETV from siga_vinculo sv, SIGA_VINCULO_POLO_EAD vpe
--where sv.NU_MATR_CURSO = vpe.NU_MATR_CURSO group by sv.NU_MATR_CURSO, vpe.CD_PERD_LETV  having count(vpe.cd_polo_ead)>1;


-- Item (01)- B: Retorna todos discentes que possuem o mesmo periodo letivo em mais de um polo. Consulta pelo CPF  e periodo letivo
--select count(vpe.cd_polo_ead), sv.nm_cpf_pess, vpe.CD_PERD_LETV from siga_vinculo sv, SIGA_VINCULO_POLO_EAD vpe
--where sv.NU_MATR_CURSO = vpe.NU_MATR_CURSO group by sv.nm_cpf_pess, vpe.CD_PERD_LETV  having count(vpe.cd_polo_ead)>1;


--Item (02): Exemplo de um discente que possui mais de um polo no mesmo periodo letivo:
--select sv.NM_CPF_PESS, vpe.CD_PERD_LETV, sp.CD_POLO_EAD, sp.NM_POLO_EAD from SIGA_VINCULO_POLO_EAD vpe, SIGA_POLO_EAD sp, SIGA_VINCULO sv where vpe.CD_POLO_EAD = sp.CD_POLO_EAD
--and vpe.NU_MATR_CURSO = '?' and sv.NU_MATR_CURSO = vpe.NU_MATR_CURSO and  vpe.CD_PERD_LETV = '?';


--Item (03): Remove a tupla do discente com o Polo errado
--delete from SIGA_VINCULO_POLO_EAD where NU_MATR_CURSO = '?' and CD_PERD_LETV = '?' and CD_POLO_EAD = ?;
*/