====== Views do Banco de Dados ======

===== vw_cafe_conta =====

<code>
CREATE OR REPLACE VIEW vw_cafe_conta AS 
 SELECT pf.cpf,
    s.matricula,
    au.password AS senha,
    'md5' AS algoritmo,
    'IFCE' AS dominio
   FROM servidor s
     LEFT JOIN pessoa_fisica pf ON s.funcionario_ptr_id = pf.pessoa_ptr_id
     LEFT JOIN auth_user au ON au.id = pf.user_id
     LEFT JOIN pessoa p ON pf.pessoa_ptr_id = p.id
  WHERE p.email_secundario::text <> ''::text AND p.email_secundario IS NOT NULL AND pf.cpf::text <> ''::text AND pf.cpf::text <> '   .   .   -  '::text AND pf.cpf::text <> '___.___.___-__'::text;

ALTER TABLE vw_cafe_conta
  OWNER TO suap;
</code>  

===== vw_cafe_email =====

<code>
CREATE OR REPLACE VIEW vw_cafe_email AS 
 SELECT pf.cpf,
    p.email_secundario
   FROM edu_aluno e,
    pessoa_fisica pf,
    pessoa p
  WHERE e.situacao_id = 1 AND e.pessoa_fisica_id = pf.pessoa_ptr_id AND p.id = pf.pessoa_ptr_id AND pf.cpf::text <> ''::text AND pf.cpf::text <> '   .   .   -  '::text AND pf.cpf::text <> '___.___.___-__'::text AND p.email_secundario::text <> ''::text AND p.email_secundario IS NOT NULL
UNION
 SELECT pf.cpf,
    p.email AS email_secundario
   FROM servidor s
     LEFT JOIN pessoa_fisica pf ON s.funcionario_ptr_id = pf.pessoa_ptr_id
     LEFT JOIN pessoa p ON pf.pessoa_ptr_id = p.id
  WHERE p.email_secundario::text <> ''::text AND p.email_secundario IS NOT NULL AND pf.cpf::text <> ''::text AND pf.cpf::text <> '   .   .   -  '::text AND pf.cpf::text <> '___.___.___-__'::text;

ALTER TABLE vw_cafe_email
  OWNER TO suap;
</code>


===== vw_cafe_identificacao =====

<code>
CREATE OR REPLACE VIEW vw_cafe_identificacao AS 
 SELECT p.nome,
    pf.cpf,
    pf.nascimento_data
   FROM edu_aluno e,
    pessoa_fisica pf,
    pessoa p
  WHERE e.situacao_id = 1 AND e.pessoa_fisica_id = pf.pessoa_ptr_id AND p.id = pf.pessoa_ptr_id AND pf.cpf::text <> ''::text AND pf.cpf::text <> '   .   .   -  '::text AND pf.cpf::text <> '___.___.___-__'::text AND p.email_secundario::text <> ''::text AND p.email_secundario IS NOT NULL
UNION
 SELECT p.nome,
    pf.cpf,
    pf.nascimento_data
   FROM servidor s
     LEFT JOIN pessoa_fisica pf ON s.funcionario_ptr_id = pf.pessoa_ptr_id
     LEFT JOIN pessoa p ON pf.pessoa_ptr_id = p.id
  WHERE p.email_secundario::text <> ''::text AND p.email_secundario IS NOT NULL AND pf.cpf::text <> ''::text AND pf.cpf::text <> '   .   .   -  '::text AND pf.cpf::text <> '___.___.___-__'::text;

ALTER TABLE vw_cafe_identificacao
  OWNER TO suap;
</code>

===== vw_cafe_suap =====

<code>
CREATE OR REPLACE VIEW vw_cafe_suap AS 
 SELECT s.matricula,
    p.nome,
    pf.cpf,
    ce.nome AS cargo,
    g.categoria,
    s.cargo_emprego_data_ocupacao AS dt_admissao,
    s.cargo_emprego_data_saida AS dt_afastamento,
    t.nome AS titulacao,
    s.nivel_padrao AS nivel,
    st.nome AS situacao,
    pf.rg,
    pf.rg_orgao,
    pf.rg_uf,
    pf.sexo,
    m.nome AS naturalidade,
    pf.nascimento_data,
    pf.nome_mae,
    pf.nome_pai,
    au.password AS senha,
    'md5' AS algoritmo,
    'IFCE' AS dominio
   FROM servidor s
     LEFT JOIN pessoa_fisica pf ON s.funcionario_ptr_id = pf.pessoa_ptr_id
     LEFT JOIN auth_user au ON au.id = pf.user_id
     LEFT JOIN comum_municipio m ON m.id = pf.nascimento_municipio_id
     LEFT JOIN pessoa p ON pf.pessoa_ptr_id = p.id
     LEFT JOIN situacao st ON s.situacao_id = st.id
     LEFT JOIN cargo_emprego ce ON s.cargo_emprego_id = ce.id
     LEFT JOIN grupo_cargo_emprego g ON g.id = ce.grupo_cargo_emprego_id
     LEFT JOIN titulacao t ON s.titulacao_id = t.id;

ALTER TABLE vw_cafe_suap
  OWNER TO suap;
</code>