VirtualAge-ETL-SQL-padrao » Histórico » Versão 9

« Anterior - Versão 9/50 (diff) - Próximo » - Versão atual
paulo, 08/12/2022 18:32 h

Virtual Age API V1 - ETL¶

Índice

GEOvendas
Força de Vendas
Business to Business (B2B)
CRM

GEO - Representantes¶

select
        '1' as codEmpresa
        , rep.cdpessoa as codRepres 
        , rep.nmpessoa as nome
        , regexp_replace(pt.nrtelefone, '[^0-9]', '', 'g') as telefone
        , pm.dsemail as email    
        , pe.nmlogradouro as endereco
        , pe.dsbairro as bairro
        , pe.cdcep as CEP
        , pe.NMMUNICIPIO as Cidade
        , pe.ufsigla as UF
        , coalesce(im.codibge,'1200351') as codIBGE --definir um codIbge padrão com o cliente para quando for NULL
        , case when (rep.ininativo = 'TRUE') then 0 else 1 end as situacao
        , null as CodRepresAgrupador
        , null as telefone2
        , null as telefone3
        , null as codigoAdministrador
        , null as cnpj
        , null as codAgrupadorEscritorio
        , null as flagAgrupadorMaster
        , null as percComissa
        , null as codigoPessoaFisica
        , rep.nmpessoa as nomePessoaFisica
        , null as senhaAPi
        , null as Origem
        , rep.nmpessoa as fantasia
        , null as empresaIntegracaoPedido
        , null as marcas
        , null as flagPermiteVendaPorFamilia    
from    representantevaentity rep
left join 
        pessoaenderecovaentity pe 
on pe.cdpessoa = rep.cdpessoa 
left join 
        vpesconsultavaentity vpc 
on vpc.cdpessoa = rep.cdpessoa
left join 
        etlcidade im 
on UPPER(im.siglaUF) = UPPER(pe.ufsigla) 
and (
       (REPLACE(REPLACE(UPPER(im.nome), ' ', ''), '''', '') = REPLACE(REPLACE(UPPER(pe.nmmunicipio), ' ', ''), '''', '')) 
    OR (REPLACE(REPLACE(UPPER(im.nome), ' ', ''), '''', 'O') = REPLACE(REPLACE(UPPER(pe.nmmunicipio), ' ', ''), '''', ''))
        )
left join 
        pessoatelefonevaentity pt 
on pt.cdpessoa = rep.cdpessoa and pt.inpadrao = 'TRUE'
left join 
        pessoaemailvaentity pm 
on pm.cdpessoa = rep.cdpessoa and pm.inpadrao = 'TRUE'

GEO - Representante Gerente¶

Documentação: Representante Gerente

Adicionar SQL

GEO - Municípios do Representante¶

Documentação: Municípios do Representante

Adicionar SQL

GEO - Municípios do ERP¶

Documentação: Municípios do ERP

Adicionar o sql

GEO - Clientes e Prospects¶

Documentação: Clientes e Prospects

select 
        '1' codEmpresa
        , COALESCE(im.codibge, '4127403') as codIBGE --CONFIRMAR COM O CLIENTE UM IBGE PADRÃO
        , p.cdpessoa as codCliente
        , 1 as tipo /* 1 - Cliente, 2 - Prospect*/
        , p.nmpessoa as nome
        , (case when (p.ininativo = 'TRUE') then 0 else 1 end) as situacao
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1), '0') as codRepres1
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1 offset 1), '0') as codRepres2
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1 offset 2), '0') as codRepres3
        , regexp_replace(pt.nrtelefone, '[^0-9]', '', 'g') as telefone
        , pm.dsemail as email
        , pe.nmlogradouro as endereco
        , pe.dsbairro as bairro
        , pe.cdcep as CEP
        , (case when v.dataCadastro is null then p.dtcadastro else v.dataCadastro end) as dataCadastro
        , coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj) as cnpjCpf
        , pmc.dsemail as emailCobranca
        , pe.dsbairro as nomeDistritoBairro
        , pts.nrtelefone as celular
        , pj.nrinscestl as inscricaoEstadual
        , pe.nrlogradouro as enderecoNumero
        , pe.dscomplemento as complemento
        , null as IndicePontualidade
        , null as telefone2
        , null as telefoneCobranca
        , case when vpc.cdcoligador = '0' then null else vpc.cdcoligador end as grupoCliente
        , case when vpc.cdcoligador = '0' then null else vpc.cdcoligador end as codClientePrincipal
        , null as situacaoEdt
        , (select pg.nmpessoa from pessoavaentity pg where pg.cdempresa = vpc.cdempresacad and pg.cdpessoa = vpc.cdcoligador) as nomeGrupoCliente
        , (case when coalesce(titulos_em_atraso,0) > 0 then 1 else 0 end) as flagInadimplente
        , (pe.cdmunicipio || ' - ' || pe.nmmunicipio) as cidade
        , pe.ufsigla as uf
        , pj.nmfantasia as nomeFantasia
        , null as ramoAtividade
        , null as dataPrimeiraCompra
        , null as dataUltimaCompra
        , null as maiorCompra
        , null as maiorAcumulado
        , null as codClienteERP
        , null as codRamoAtividade
        , null as limiteCredito
        , null as ultimoFaturamento
        , null as dataReativacao
        , null as codSitCreditoERP
        , null as descSitCreditoERP
        , null as motivoInativo
        , null as diasVencimentoTitulo
        , null as socios
        , null as cidadeGrupoCliente
        , null as classificacaoERP
        , null as instagram
        , null as facebook
        , null as marcasLiberadas
        , pmc.dsemail as emailNfe
FROM    pessoavaentity p
inner join 
        pessoaenderecovaentity pe 
on      pe.cdempresa = p.cdempresa and pe.cdpessoa = p.cdpessoa 
inner join 
        vpesconsultavaentity vpc 
on      vpc.cdpessoa = p.cdpessoa     
LEFT join 
        ETLCidade im 
on      im.codCidade = pe.cdMunicipio
LEFT join 
        pessoatelefonevaentity pt 
on      pt.cdempresa = p.cdempresa 
AND     pt.cdpessoa = p.cdpessoa 
AND     pt.id = (       SELECT  MIN(pt2.id) 
                        FROM    pessoatelefonevaentity pt2 
                        WHERE   pt.cdempresa = pt2.cdempresa 
                        AND     pt.cdpessoa = pt2.cdpessoa
                )
left join 
        pessoaemailvaentity pm 
on      pm.cdempresa = p.cdempresa 
and     pm.cdpessoa = p.cdpessoa 
and     pm.inpadrao = 'TRUE'
left join 
        pessoaemailvaentity pmc 
on      pmc.cdempresa = p.cdempresa 
and     pmc.cdpessoa = p.cdpessoa 
and     pmc.cdtipoemail = 3 -- CONFIRMAR QUAL TIPO DE E-MAIL É O EMAIL DE NFE/COBRANCA
left join 
        pessoatelefonevaentity pts 
on      pts.cdempresa = p.cdempresa 
and     pts.cdpessoa = p.cdpessoa 
and     pts.cdtipofone = 4 -- CONFIRMAR COM O CLIENTE QUAL É O CÓDIGO DO TELEFONE CELULAR
left join 
        pessoajuridicavaentity pj 
on      pj.cdempresa = p.cdempresa 
and     pj.cdpessoa = p.cdpessoa     
left join 
        (select codcliente::int as codcliente
                , codempresa::int as codempresa
                , count(*) as titulos_em_atraso 
        from etltitulo 
        where situacao = '1' 
        and (dataVencimento::date + 28) < (current_date) -- CONFIRMAR COM O CLIENTE QUANTOS DIAS CONSIDERA INADIMPLENTE
        group by codcliente
                , codempresa having sum(valor) > 3000) as inad -- CONFIRMAR COM O CLIENTE SE INADIMPLENTE É ACIMA DE 3 MIL REAIS DEVENDO
on      inad.codcliente = p.cdpessoa 
and     inad.codempresa = p.cdempresa
left join 
        (SELECT v.cdcliente, MIN(v.dtpedido) as dataCadastro FROM vendavaentity v GROUP BY v.cdcliente) as v 
on      v.cdcliente = p.cdpessoa 
where   vpc.incliente = 'TRUE'
AND     coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1), '0') is not null
and     coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj) is not null
AND     pe.cdcep <> '99999999'
AND     length(coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj)) >= 14 -- CONFIRMAR COM O CLIENTE SE É PARA PUXAR SÓ CNPJ

GEO - Cliente Dos Representantes¶

Documentação: Cliente Dos Representantes

select '1' codempresa,  
       cdpessoa,
       cdrepresentant 
from pessoareprclientevaentity

GEO - Marcas do Cliente¶

Documentação: Marcas do Cliente

Adicionar SQL

GEO - Coleções¶

Documentação: Coleções

select 
        '1' as codEmpresa
        , cdclassificacao as codColecao
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE COLEÇÃO COM O CLIENTE
order by dsclassificacao

GEO - Linhas de Produto¶

Documentação: Linhas de Produto

select 
        '1' as codEmpresa
        , cdclassificacao as codLinha
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE LINHA COM O CLIENTE
order by dsclassificacao

GEO - Marcas¶

Documentação: Marcas

select 
        '1' as codEmpresa
        , cdclassificacao as codMarca
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE MARCA COM O CLIENTE
order by dsclassificacao