*---------------------------------------------------------------*
CADASTRO DE CIDADÃO POR MICRO ÁREA
AUTOR: SÉRGIO SCHÜTZ
*---------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_CIDADAO_AREA_ESUS AS SELECT
  tb_cds_cad_individual.co_unico_ficha_origem, 
  tb_cds_cad_individual.nu_cns_cidadao, 
  tb_cds_cad_individual.no_cidadao, 
  tb_cds_cad_individual.dt_nascimento, 
  tb_cds_cad_individual.no_mae_cidadao, 
  tb_cds_cad_individual.nu_micro_area

FROM 
  public.tb_cds_cad_individual

ORDER BY
  tb_cds_cad_individual.no_cidadao ASC, 
  tb_cds_cad_individual.nu_micro_area ASC;

COPY CADASTRO_CIDADAO_AREA_ESUS
TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAO_MICRO_AREA_ACS.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO DE CIDADÃO POR ORDEM ALFABÉTICA/COM BASTANTE DADOS
AUTOR: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_CIDADAO_ESUS_COMPLETO AS SELECT  
  tb_cidadao.co_seq_cidadao, 
  tb_cidadao.nu_cpf, 
  tb_cidadao.nu_cns, 
  tb_cidadao.nu_nis_pis_pasep,
  tb_cidadao.no_cidadao, 
  tb_cidadao.dt_nascimento, 
  tb_sexo.no_sexo,  
  tb_cidadao.no_mae, 
  tb_cidadao.no_pai, 
  tb_cidadao.st_unificado 
FROM 
  public.tb_cidadao, 
  public.tb_sexo
WHERE 
  tb_cidadao.co_sexo = tb_sexo.co_sexo
ORDER BY
  tb_cidadao.no_cidadao ASC, 
  tb_cidadao.st_unificado ASC;

COPY CADASTRO_CIDADAO_ESUS_COMPLETO
TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAO_ESUS_COMPLETO.csv'
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO DE CIDADÃO POR ENDEREÇO
AUTOR: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_CIDADAO_ESUS_ENDERECO AS SELECT  
  tb_cidadao.co_seq_cidadao, 
  tb_cidadao.nu_cns, 
  tb_cidadao.nu_cpf, 
  tb_cidadao.no_cidadao, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae,
  tb_cidadao.ds_logradouro,
  tb_cidadao.nu_numero,
  tb_cidadao.no_bairro,
  tb_cidadao.nu_cns_responsavel,
  tb_cidadao.no_responsavel,  
  tb_cidadao.st_unificado
FROM 
  public.tb_cidadao
ORDER BY
  tb_cidadao.no_cidadao ASC, 
  tb_cidadao.st_unificado ASC;

COPY CADASTRO_CIDADAO_ESUS_ENDERECO
TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAO_ESUS_ENDERECO.csv'
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO DE DOMICILIOS POR MICRO ÁREA
AUTOR: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_DOMICILIOS_MICRO_AREA AS SELECT
  tb_cds_cad_domiciliar.tp_logradouro, 
  tb_cds_cad_domiciliar.no_logradouro, 
  tb_cds_cad_domiciliar.nu_domicilio, 
  tb_cds_cad_domiciliar.dt_cad_domiciliar, 
  tb_cds_cad_domiciliar.nu_micro_area
FROM 
  public.tb_cds_cad_domiciliar
ORDER BY
  tb_cds_cad_domiciliar.nu_micro_area ASC, 
  tb_cds_cad_domiciliar.no_logradouro ASC;

COPY CADASTRO_DOMICILIOS_MICRO_AREA
TO 'C:/RELATORIOS_ESUS/CADASTRO_DOMICILIOS_MICRO_AREA.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO DE FAMILIAS E INDIVIDUOS CADASTRADOS
AUTOR: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_FAMILIAS_INDIVIDUOS AS SELECT  
  tb_cidadao.co_seq_cidadao, 
  tb_cidadao.nu_cns, 
  tb_cidadao.nu_cpf, 
  tb_cidadao.no_cidadao, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae, 
  tb_cidadao.st_unificado
FROM 
  public.tb_cidadao
ORDER BY
  tb_cidadao.no_cidadao ASC, 
  tb_cidadao.st_unificado ASC;

COPY CADASTRO_FAMILIAS_INDIVIDUOS
TO 'C:/RELATORIOS_ESUS/CADASTRO_FAMILIAS_INDIVIDUOS.csv'
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO INDIVIDUAL ---> ANALISAR
AUTOR: SÉRGIO SCHÜTZ
ADAPTAÇÃO:
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CAD_INDIVIDUAL_BC AS SELECT 
tb_fat_cad_individual.nu_cns,
tb_fat_cad_individual.nu_nis,
tb_fat_cad_individual.no_nome,
tb_fat_cad_individual.co_dim_faixa_etaria,
tb_fat_cad_individual.dt_nascimento,
tb_fat_cad_individual.no_nome_mae,
tb_fat_cad_individual.no_nome_pai,
tb_fat_cad_individual.co_dim_tipo_ficha,
tb_fat_cad_individual.co_dim_municipio,
tb_fat_cad_individual.co_dim_unidade_saude,
tb_fat_cad_individual.co_dim_equipe,
tb_fat_cad_individual.co_dim_sexo,
tb_fat_cad_individual.co_dim_raca_cor,
tb_fat_cad_individual.st_gestante,
tb_fat_cad_individual.st_fumante,
tb_fat_cad_individual.st_alcool,
tb_fat_cad_individual.st_hipertensao_arterial,
tb_fat_cad_individual.st_diabete,
tb_fat_cad_individual.st_hanseniase,
tb_fat_cad_individual.st_recebe_beneficio,
tb_fat_cad_individual.co_dim_tipo_escolaridade,
tb_fat_cad_individual.co_dim_situacao_trabalho,
tb_fat_cad_individual.co_dim_tipo_orientacao_sexual,
tb_fat_cad_individual.co_dim_tipo_saida_cadastro,
tb_fat_cad_individual.co_dim_tipo_condicao_peso,
tb_fat_cad_individual.nu_uuid_dado_transp,
tb_fat_cad_individual.nu_micro_area
FROM
public.tb_fat_cad_individual

COPY CAD_INDIVIDUAL_BC
TO 'C:/RELATORIOS_ESUS/CAD_INDIVIDUAL_BC.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO INDIVIDUAL ---> ANALISAR
AUTOR: SÉRGIO SCHÜTZ
ADAPTAÇÃO:
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
CREATE TEMP TABLE CIDADAO_ESUS_TESTE AS SELECT  
  tb_fat_cad_individual.co_seq_fat_cad_individual, 
  tb_fat_cad_individual.nu_cns, 
  tb_fat_cad_individual.dt_nascimento,
  tb_fat_cad_individual.co_dim_profissional, 
  tb_fat_cad_individual.co_dim_unidade_saude,
  tb_fat_cad_individual.co_dim_sexo, 
  tb_fat_cad_individual.st_deficiencia, 
  tb_fat_cad_individual.st_gestante,
  tb_fat_cad_individual.st_hipertensao_arterial,
  tb_fat_cad_individual.st_diabete,
  tb_fat_cad_individual.st_morador_rua
  
FROM 
  public.tb_fat_cad_individual

ORDER BY
  tb_fat_cad_individual.nu_cns ASC, 
  tb_fat_cad_individual.dt_nascimento ASC;

COPY CIDADAO_ESUS_TESTE

TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAOS_TESTE.csv'
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO CIDADÃOS DUPLICADOS - 28/01/2020
AUTOR: DIEGO RODRIGUES RIBEIRO
ADAPTAÇÃO: SÉRGIO ROSA SCHÜTZ
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
ESTE SCRIPT GERA UMA PLANILHA NO EXCEL COM OS CIDADÃOS QUE
POSSUEM CADASTRO MAIOR OU IGUAL 2 VEZES DENTRO DO SISTEMA.
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_CIDADAO_DUPLICADO AS
select * from

(
SELECT
count(tb_cidadao.no_cidadao) as qtd,
  tb_cidadao.no_cidadao, 
  tb_cidadao.nu_cpf,
  tb_cidadao.nu_cns, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae, 
  tb_cidadao.st_unificado 

FROM
tb_cidadao

GROUP BY 
  tb_cidadao.no_cidadao, 
  tb_cidadao.nu_cpf,
  tb_cidadao.nu_cns, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae, 
  tb_cidadao.st_unificado 

ORDER BY
tb_cidadao.no_cidadao ASC
) A

where A.qtd >=2;

COPY CADASTRO_CIDADAO_DUPLICADO
TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAO_DUPLICADO.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO CIDADÃOS DUPLICADOS - 29/01/2020
AUTOR: DIEGO RODRIGUES RIBEIRO
ADAPTAÇÃO: SÉRGIO ROSA SCHÜTZ/ITALO RODRIGO
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
ESTE SCRIPT GERA UMA PLANILHA NO EXCEL COM OS CIDADÃOS QUE
POSSUEM CADASTRO MAIOR OU IGUAL 2 VEZES DENTRO DO SISTEMA.

* RETIRADO OS CAMPOS CPF E CNS, PORQUE TEM CADASTROS REPETIDOS,
MAS UNS COM O CNS E OUTROS SEM, O MESMO VALE PARA O CPF.

* WHERE ST_UNIFICADO = 0 --> NÃO MOSTRA OS QUE JÁ FORAM UNIFICADOS
*--------------------------------------------------------------*
CREATE TEMP TABLE CADASTRO_CIDADAO_DUPLICADO AS
select * from

(
SELECT
count(tb_cidadao.no_cidadao) as qtd,
  tb_cidadao.no_cidadao, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae 
  
FROM
tb_cidadao

where st_unificado = 0

GROUP BY 
  tb_cidadao.no_cidadao, 
  tb_cidadao.dt_nascimento, 
  tb_cidadao.no_mae 
  
ORDER BY
tb_cidadao.no_cidadao ASC
) A

where A.qtd >=2;

COPY CADASTRO_CIDADAO_DUPLICADO
TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAO_DUPLICADO.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
CADASTRO DE HIPERTENSOS, GESTANTES - 30/01/2020
AUTOR: AURELIO CHAVES
ADAPTAÇÃO: SÉRGIO ROSA SCHÜTZ
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
esta consulta retorna a lista de pessoas com hipertensao, gestantes, diabeticos e situcao de rua
*--------------------------------------------------------------*
CREATE TEMP TABLE CONDICIONALIDADES AS 
SELECT distinct tb_cidadao.nu_cns ,
tb_cidadao.no_cidadao,
tb_fat_cad_individual.dt_nascimento,
tb_fat_cad_individual.co_dim_sexo,
tb_fat_cad_individual.co_dim_unidade_saude,
tb_fat_cad_individual.co_dim_profissional,
tb_fat_cad_individual.nu_micro_area,
tb_fat_cad_individual.st_gestante,
tb_fat_cad_individual.st_hipertensao_arterial,
tb_fat_cad_individual.st_diabete,
tb_fat_cad_individual.st_hanseniase,
tb_fat_cad_individual.st_tuberculose,
tb_fat_cad_individual.st_acamado,
tb_fat_cad_individual.st_domiciliado,
tb_fat_cad_individual.co_dim_tipo_condicao_peso,
tb_fat_cad_individual.st_fumante,
tb_fat_cad_individual.st_alcool,
tb_fat_cad_individual.co_dim_tipo_saida_cadastro

FROM
public.tb_fat_cad_individual,
public.tb_cidadao

where
tb_cidadao.nu_cns  = tb_fat_cad_individual.nu_cns;

COPY CONDICIONALIDADES

TO 'C:/RELATORIOS_ESUS/CONDICIONALIDADES.csv' 
DELIMITER ';'
CSV HEADER
*--------------------------------------------------------------*

*--------------------------------------------------------------*
                    ATUALIZAÇÃO 03/02/2020
*--------------------------------------------------------------*
ERRO AO UNIFICAR CADASTRO - NullPointerException
AUTOR: BRIDGE
REVISÃO: SÉRGIO SCHÜTZ
*--------------------------------------------------------------*
1 - Rodar o script pelo pgadmin;
2 - Após, isso, se lotar como administrador da instalação e realizar o envio manual de fichas ao Centralizador Nacional 
(PEC > Administração > Transmissão de dados > Controle de envio 
de fichas);
3 - Após a transmissão das fichas, se lotar como coordenador da unidade e realizar a unificação de cadastros (PEC > Gestão de cadastros > Unificação de cadastros de cidadão).
*--------------------------------------------------------------*
update tb_cds_cad_individual
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_cad_individual.co_unico_ficha );

update tb_cds_cad_domiciliar
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_cad_domiciliar.co_unico_ficha );

update tb_cds_ficha_atend_individual
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_atend_individual.co_unico_ficha );

update tb_cds_ficha_atend_odonto
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_atend_odonto.co_unico_ficha );

update tb_cds_ficha_ativ_col
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_ativ_col.co_unico_ficha );

update tb_cds_ficha_proced
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_proced.co_unico_ficha );

update tb_cds_ficha_visita_domiciliar
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_visita_domiciliar.co_unico_ficha );

update tb_cds_ficha_consumo_alimentar
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_consumo_alimentar.co_unico_ficha );

update tb_cds_aval_elegibilidade
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_aval_elegibilidade.co_unico_ficha );

update tb_cds_ficha_atend_domiciliar
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_atend_domiciliar.co_unico_ficha );

update tb_cds_ficha_zika_microcefalia
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_zika_microcefalia.co_unico_ficha );

update tb_cds_ficha_vacinacao
set st_envio = 0
where not exists (select * from tb_dado_transp dt where dt.co_unico_dado_serializado = tb_cds_ficha_vacinacao.co_unico_ficha );

*--------------------------------------------------------------*

*--------------------------------------------------------------*

MULTIPLAS OCORRENCIAS DE CADASTROS (COM DOCUMENTOS) - 03/02/2020

AUTOR: DIEGO RODRIGUES RIBEIRO
ADAPTAÇÃO: GEDEAN DIAS (gedean.dias@akapu.com.br)

*-------------------------------------------------------------*

ESTE SCRIPT GERA UM ARQUIVO 'CSV' (QUE PODE SER VISUALIZADO NO 
EXCEL)
CONTENDO INFORMAÇÕES DE PESSOAS (NOME, NOME MAE, DATA 
NASCIMENTO, CPF, CNS)
COM MAIS DE UMA OCORRENCIA DOS CAMPOS NOME, 
NOME MAE E DATA DE NASCIMENTO
INDICANDO POSSÍVEL DUPLICIDADE DE 
REGIRSTROS NA BASE DE DADOS DO E-SUS



* WHERE ST_UNIFICADO = 0 --> NÃO MOSTRA OS QUE JÁ FORAM UNIFICADOS

** NAO GERA TABELA TEMPORARIA NO BD


*-------------------------------------------------------------*



COPY (



WITH
    
pessoas_duplicadas AS (
     

SELECT
         
tb_cidadao.no_cidadao,
         
tb_cidadao.no_mae,
         
tb_cidadao.dt_nascimento
     

FROM
         
tb_cidadao
     

WHERE st_unificado = 0
     

GROUP BY
         
tb_cidadao.no_cidadao,
         
tb_cidadao.no_mae,
         
tb_cidadao.dt_nascimento
     

HAVING
         
Count(tb_cidadao.no_cidadao) > 1
    
),
    

docs_pessoas_duplicadas AS (
     

SELECT
         
tb_cidadao.no_cidadao,
         
tb_cidadao.no_mae,
         
tb_cidadao.dt_nascimento,
         
tb_cidadao.nu_cpf,
         
tb_cidadao.nu_cns
     

FROM
         
tb_cidadao
    
)


SELECT
    
pessoas_duplicadas.no_cidadao AS nome,
    pessoas_duplicadas.no_mae AS nome_mae,
    pessoas_duplicadas.dt_nascimento AS data_nascimento,
    docs_pessoas_duplicadas.nu_cpf as cpf,
    docs_pessoas_duplicadas.nu_cns as cartao_sus


FROM
    
pessoas_duplicadas LEFT JOIN
    
docs_pessoas_duplicadas ON docs_pessoas_duplicadas.no_cidadao = pessoas_duplicadas.no_cidadao AND

docs_pessoas_duplicadas.no_mae = pessoas_duplicadas.no_mae AND
     docs_pessoas_duplicadas.dt_nascimento = pessoas_duplicadas.dt_nascimento


ORDER BY pessoas_duplicadas.no_cidadao
) 

TO 'C:\RELATORIOS_ESUS\CADASTRO_CIDADAO_DUPLICADO.csv' 

DELIMITER ';'

CSV HEADER
*---------------------------------------------------------------*

*--------------------------------------------------------------

CADASTRO CIDADÃO SIMPLES

AUTOR: SÉRGIO SCHÜTZ

ADAPTAÇÃO:

REVISÃO: SÉRGIO SCHÜTZ

DATA: 18/02/2020

*--------------------------------------------------------------*

CREATE TEMP TABLE CIDADAO_ESUS_SIMPLES AS SELECT  
  
tb_cidadao.no_cidadao, 
  
tb_cidadao.ds_logradouro, 
  
tb_cidadao.nu_numero, 
  
tb_cidadao.no_bairro, 
  
tb_cidadao.nu_telefone_residencial, 
  
tb_cidadao.nu_telefone_celular, 
  
tb_cidadao.nu_telefone_contato, 
  
tb_cidadao.ds_email


FROM 
  
public.tb_cidadao


ORDER BY
  tb_cidadao.no_cidadao ASC;



COPY CIDADAO_ESUS_SIMPLES


TO 'C:/RELATORIOS_ESUS/CADASTRO_CIDADAOS_SIMPLES.csv'

DELIMITER ';'
CSV HEADER

*-------------------------------------------------------------*