Revisar detalhes base CNPJ

[tigreped]

Revisando os dados, foram notados os seguintes comportamento que necessitam de verificação:

Para o CNPJ: "motivo_situacao_cadastral": "00" → Não tem descrição completa por extenso, apenas código. Verificar se há tabela para conversão. "ddd_telefone_1": "43218765" → Os números de telefone estão sempre sem DDD "pais": null, → Deveria constar como "Brasil" "ente_federativo_responsavel": verificar se há valores diferentes de null válidos

Para os Sócios: "numero_cpf_representante_legal": "***000000**" → Nota-se um padrão repetitivo que talvez não seja correto "qualificacao_representante_legal": "00" → apenas código, verificar se tem tabela de referência

Quanto à carga, a tabela estabelecimento no banco está validada, mas a de resposta_cnpj ainda precisa consolidar.

Ex.: procurando pelo cnpj_basico = '00000000' na tabela estabelecimento tem várias entradas, mas na resposta_cnpj só tem uma.

Na base, temos os seguintes números de registros por tabela: estabelecimento: 53.212.841 empresa: 50.424.904 resposta_cnpj: 50.424.904

Investigar o motivo de não ter carregado por completo todos os estabelecimentos, apenas as empresas.

[tigreped]

A abordagem atual consiste na refatoração do código de raspagem e processamento dos dados da receita de modo a incluir uma contagem mais precisa do número de registros de cada categoria de informação (estabelecimento, cnae, empresa, pais, etc.), uma vez que são necessários vários arquivos compactados para representar uma categoria. Normalmente, empresa e estabelecimento tem o mesmo número de arquivos .zip mas, ao que parece, os números de linhas nos .CSVs são distintos, não representando necessariamente uma relação de 1 empresa para 1 estabelecimento.

Outro problema é que os arquivos .CSV para empresa possuem apenas o campo cnpj_basico de 8 dígitos para relacioná-lo com a tabela principal de estabelecimentos (que contém, além desse campo, os campos ordem e dv). Entretanto, o cnpj_basico não é viável como identificador único para estabelecimentos e causa choque, uma vez que há múltiplas linhas com um mesmo cnpj_basico, o que seria impossível se representasse um campo do tipo UNIQUE. Está em aberto encontrar uma solução para relacionar inequivocamente uma empresa a um estabelecimento.

Além disso, uma vez que podem ocorrer problemas e erros durante o processamento e a carga dos arquivos CSV, podem ser perdidos registros durante o processamento que não constarão portanto na base final, causando assim uma discrepância entre o número de registros esperado, que corresponde ao número de linhas do .csv menos o possível cabeçalho e o número de registros de fato salvos no banco de dados.

Espera-se que ao realizar e concluir o processo de carga novamente após a refatoração, as contagens auxiliem a identificar e apontar com precisão a quantidade de registros que há de diferença entre as empresas e os estabelecimentos carregados.

[tigreped]

Analisando os dados no servidor:

Na base testes/homologação/produção da receita na DigitalOcean do QD/OK(16/11/22):

a) Total de registros na tabela empresa: 50.424.904 select count(cnpj) from empresa;

b) Total de registros na tabela estabelecimento: 53.212.841 select count(cnpj_basico) from estabelecimento;

c) Total de CNPJs únicos na lista de estabelecimentos: 50.464.903 select count(subquery.estabelecimento_cnpj_basico) from (select estabelecimento.cnpj_basico as estabelecimento_cnpj_basico from estabelecimento group by estabelecimento.cnpj_basico) as subquery;

d) Total de CNPJs correspondentes entre as tabelas empresa e estabelecimento: 53.170.876 select count(estabelecimento.cnpj_basico) from estabelecimento inner join empresa on (estabelecimento.cnpj_basico=empresa.cnpj);

Tais números sugerem assim como observado na base local com apenas um recorte dos dados carregados que de fato que há para cada registro da tabela EMPRESA um registro único na tabela estabelecimento correspondente ao registro matriz. Há na tabela de estabelecimentos mais registros que na tabela de empresas, como seria esperado, uma vez que uma empresa pode ter mais de um estabelecimento(1..N).

Em especial, em c) o número dinstinto de cnpjs únicos na tabela estabelecimento (50.464.964) está muito próximo do número de empresas em a) (50.424.904), o que permite produzir:

e) 50.464.964 - 50.424.904 = 40.060

O número acima, 40.060, nos sugere que há 40.060 registros distintos(únicos para o cnpj) na tabela estabelecimento que não possuem, por algum motivo, um cnpj correspondente existente na base empresa. Isso permanece um mistério a elucidar, que sugere que apesar da relação das entidades ser de 1 Empresa para N Estabelecimentos, é possível registrar um estabelecimento sem que ele tenha necessariamente uma empresa, ou seja, de 1 Estabelecimento para 0~1 Empresas, quando a intuição sugere que deveria ser de 1 Estabelecimento pertencer a 1 Empresa.

Além disso, podemos verificar que do total de registros da base ESTABELECIMENTO em a) 53.212.841 e do valor de d) registros que encontram de fato correspondente na base EMPRESA sugere que:

f) 53.212.841 - 53.170.876 = 41.965

Pelo menos 41.965 registros da base estabelecimento não encontram, de fato, correspondência na base empresa, via cnpj, estando de certa forma, "órfãos". Comparando o valor encontrado no raciocínio anterior e) com f), temos: 41.965 - 40.060 = 1.905

Provavelmente esses 40.060 são os mesmos registros da base estabelecimento que não encontram correspondência na base empresa.

De fato, ao executar a consulta:

select count(es.cnpj_basico) from estabelecimento es left outer join empresa em on (es.cnpj_basico=em.cnpj) where em.cnpj is null;

g) Total de CNPJs da base estabelecimento que não tem correspondência na base empresa:

São retornados exatamente 41.965 registros da tabela estabelecimento que não encontram correspondentes válidos na tabela empresa.

Enquanto executar a consulta inversa:

select count(em.cnpj) from empresa em left outer join estabelecimento es on (em.cnpj=es.cnpj_basico) where es.cnpj_basico is null;

h)  Total de CNPJs da base empresa que não tem correspondência na base estabelecimento:

Não retorna nenhum resultado que esteja na tabela empresa que não encontre ao menos um correspondente na tabela estabelecimento.

Assim, a partir de g) e h) é possível deduzir que a informação está estruturada de modo que a relação entre as entidades Empresa e Estabelecimento ocorre com uma relação obrigatória de pertencimento do registro da base estabelecimento a inequivocamente e unicamente um registro da base de empresas. Além disso, entende-se que não há nenhuma empresa cujo cnpj_basico não corresponda a pelo menos um registro da base de estabelecimentos.

A consulta: select count(id) from resposta_cnpj; count i) 50.424.904

Nos indica que a resposta i) é equivalente ao número total de registros da base Empresa averiguado em a).

Dessa forma, é possível confirmar que o processo de consolidação da resposta de cnpjs está atrelada ao número de empresas disponíveis a partir do processamento inicial da base.

• Dúvida: é correto e/ou sequer faz sentido publicar as informações dos 41.965 estabelecimentos "órfãos", que não possuem informação de empresas?

Assim, tudo indica que o problema atual com a consolidação da base é que apenas os registros correspondentes aos registros das empresas foram consolidados como parte da resposta_cnpj quando deveriam ser consolidados a partir dos estabelecimentos.

Atualmente, temos o método get_all_cnpj_ids() pegando a lista de cnpjs a partir da base empresa, quando deveria ser da base estabelecimentos. Para corrigir a situação, deve-se esperar do processamento:

1) Um registro na tabela resposta_cnpj para cada registro da tabela estabalecimento, somados aos dados da empresa correspondente.

[ogecece]

@tigreped pra fechar esse registro aqui diria que os estabelecimentos "órfãos" inicialmente não precisariam entrar pois é uma fatia minúscula e geraria uma confusão no esquema da resposta_cnpj. Mas acho que vale adicionar como issue no repositório da receita pra ser melhor estudado depois. O que acha?