Script para inserção de dados nas tabelas dos clientes.

inserção_concedidos_consignados.sql

select * from sys.tables where create_date >= '2013-11-13' and name like '%consignados%'
select * from temp_inss_concedidos_agosto2013


select *   
into temp_inss_concedidos 
from temp_inss_concedidos_jan
union 

select *, NULL
from temp_inss_concedidos_fev
union

select *, NULL 
from temp_inss_concedidos_março
union

select *, NULL 
from temp_inss_concedidos_maio
union

select *, NULL 
from temp_inss_concedidos_maio2013
union

select *, NULL 
from temp_inss_concedidos_outubro2012
union

select *, NULL 
from temp_inss_concedidos_dezembro2011
union

select *, NULL
from temp_inss_concedidos_junho2013
union

select *
from temp_inss_concedidos_agosto2013
drop table temp_inss_consignados

select count(*) from temp_inss_concedidos


alter table temp_inss_concedidos alter column cep bigint
alter table temp_inss_concedidos alter column cep varchar(255)

alter table temp_inss_concedidos alter column ddd bigint
alter table temp_inss_concedidos alter column ddd varchar(255)

alter table temp_inss_concedidos alter column fone bigint
alter table temp_inss_concedidos alter column fone varchar(255)

--Verificar tamanho dos campos
select distinct LEN(cpf) from temp_inss_concedidos 
select distinct LEN(digitocpf) from temp_inss_concedidos 
select distinct LEN(matricula) from temp_inss_concedidos 
select distinct LEN(cep) from temp_inss_concedidos 


--Junto a coluna cpf e o digitocpf
update temp_inss_concedidos
set cpf = cpf + digitocpf


--Verifica os cep que são menores que 8
select distinct cep from temp_inss_concedidos where LEN(cep) < 8

--Adiciona zero a esquerda nos ceps menores que 8
update temp_inss_concedidos
set cep = dbo.fn_strzero(cep, 8) 
where LEN(cep) < 8 


--Verifica se existe algum cpf igual a 0
select COUNT(*) from temp_inss_concedidos where cpf = '00000000000'

--Deleta cpfs igual a 0
delete temp_inss_concedidos 
where cpf = '00000000000'


--Criar a temp de telefones
select distinct cpf, ddd, fone
into temp_fone_inss_concedidos
from temp_inss_concedidos 
where LEN(fone) > 7 and ISNULL(fone, '') <> ''


--Verificar o tamanho do ddd e fone
select distinct LEN(ddd),LEN(fone) from temp_fone_inss_concedidos


--Atualiza para null com tamanho diferente de 2
update temp_fone_inss_concedidos
set ddd = null 
where LEN(ddd) <> 2

--eliminar duplicidade
with dups as(
 select *, ROW_NUMBER() over (PARTITION BY cpf, matricula order by cpf desc, matricula desc, nome desc, dt_nasc desc, esp desc, salario desc, admissao desc, id_banco desc, endereco desc, bairro desc, cidade desc, uf desc, cep desc) row
 from temp_inss_concedidos --nome da tabela
)
delete dups 
where row > 1

select COUNT(*), cpf, matricula 
from temp_inss_concedidos 
group by cpf, matricula
having count(*) > 1

select count(*)
from cliente_televenda
join temp_inss_concedidos on clt_cpf = cpf and clt_matricula = matricula
and orgav_codigo = 1

--incluir na tabela cliente_televenda
--OBS: Se o orgão for INSS incluir no where not exists a condição and clt_matricula = matricula

insert cliente_televenda(clt_cpf, clt_matricula, clt_nome, clt_data_nascimento, clt_especie, clt_salario, clt_data_admissao, bcolib_codigo_conta, clt_endereco, clt_bairro,
       clt_cidade, clt_uf, clt_cep, tst_codigo, orgav_codigo, fil_codigo)
select cpf, matricula, nome, dbo.fn_converte_data(dt_nasc, 'yyyymmdd'), esp, salario, dbo.fn_converte_data(admissao, 'yyyymmdd'), id_banco, endereco, bairro, cidade, uf, cep, 1, 1, 1  
from temp_inss_concedidos--nome da tabela
where not exists (select 1 
     from cliente_televenda
     where clt_cpf = cpf 
     and orgav_codigo = 1 and clt_matricula = matricula)

create index i_cpf on temp_inss_concedidos(cpf)

update temp_inss_concedidos 
set id_banco = NULL
where not exists (select 1
					from bancolib
					where bcolib_codigo = id_banco )

----incluir na telefone_adicional_televenda
insert telefone_adicional_televenda(clt_codigo, tat_telefone)
select clt_codigo, dbo.fn_formata_telefone(ddd, fone)
from cliente_televenda a, temp_fone_inss_concedidos --nome da tabela
where clt_cpf = cpf
      and not exists(select 1
                  from telefone_adicional_televenda b
      where a.clt_codigo = b.clt_codigo
      and b.tat_telefone = dbo.fn_formata_telefone( ddd, fone))


--Consignados normalizar dados
--verificar tamanho dos valores das colunas
select * from temp_inss_consignados

--Alterar coluna para vchar quando a matricula estiver com tipo float
alter table temp_inss_consignados alter column matricula bigint
alter table temp_inss_consignados alter column matricula varchar(255)

--Alterar colunas cpf e digitocpf para varchar tamanho 255
alter table temp_inss_consignados alter column cpf varchar(255)
alter table temp_inss_consignados alter column digitocpf varchar(255)

--Verificar tamanho dos valores nos campos.
select distinct LEN(cpf) from temp_inss_consignados
select distinct LEN(digitocpf) from temp_inss_consignados
select distinct LEN(matricula) from temp_inss_consignados
select distinct LEN(cep) from temp_inss_consignados

--Unir os campos cpf e digito-cpf
update temp_inss_consignados
set cpf = cpf + digitocpf

--Verificar ceps menores que 8

select distinct COUNT(*) from temp_inss_consignados where LEN(cep) < 8
select * from temp_inss_consignados

--Adicionar zero a esquerda em ceps menores que 8 numeros

update temp_inss_consignados
set cep = dbo.fn_strzero(cep, 8) 
where LEN(cep) < 8 

--Verifica se exite alguma cpf igual a 0
select distinct COUNT(*) from temp_inss_consignados where cpf = '00000000000'

--Deleta cpfs iguais a zero
delete temp_inss_consignados
where cpf = '00000000000'

--Seleciona fones da tabela para inserção na temp_fone
select distinct cpf, ddd, fone
into temp_fone_inss_consignados
from temp_inss_consignados
where LEN(fone) > 7 and ISNULL(fone,'') <> ''

--Verificar tamanho do ddd e fone
select distinct LEN(ddd),LEN(fone) from temp_fone_inss_consignados

--Atualizar campo para null cujo DDD for diferente de 2

update temp_fone_inss_consignados
set ddd = null
where len(ddd) <> '2'

select distinct len(fone) from temp_fone_inss_consignados

select distinct cpf, matricula, cod_banco, id_contrato,vlr_emp, data_inicial, quant_parc, vlr_parc
into temp_contrato_inss_consignados
from temp_inss_consignados
where situacao = 0 

--eliminar duplicidade
with dups as(
 select *, ROW_NUMBER() over (PARTITION BY cpf, matricula order by cpf desc, matricula desc, nome desc, dt_nasc desc, endereco desc, bairro desc, cidade desc, uf desc, cep desc, situacao, salario desc, [id_banco_pagto] desc) row
 from temp_inss_consignados--nome da tabela
)
delete dups
where row > 1

--incluir na tabela cliente_televenda
--OBS: Se o orgão for INSS incluir no where not exists a condição and clt_matricula = matricula
select * from temp_inss_consignados

insert cliente_televenda(clt_cpf, clt_matricula, clt_nome, clt_data_nascimento, clt_salario, clt_endereco, clt_bairro,
       clt_cidade, clt_uf, clt_cep, tst_codigo, orgav_codigo, fil_codigo, bcolib_codigo_conta, clt_agencia, clt_conta_corrente)
select cpf, matricula, nome, dbo.fn_converte_data(dt_nasc, 'yyyymmdd'), salario, endereco, bairro, cidade, uf, cep, 1, 1, 1,
		id_banco_pagto, id_agencia_banco, nu_conta_corrente
from temp_inss_consignados --nome da tabela
where not exists (select 1 
     from cliente_televenda
     where clt_cpf = cpf 
     and orgav_codigo = 1 
	 and clt_matricula = matricula)
	 and exists (select 1 from bancolib
				 where bcolib_codigo = id_banco_pagto)
				 
insert cliente_televenda(clt_cpf, clt_matricula, clt_nome, clt_data_nascimento, clt_salario, clt_endereco, clt_bairro,
       clt_cidade, clt_uf, clt_cep, tst_codigo, orgav_codigo, fil_codigo)
select cpf, matricula, nome, dbo.fn_converte_data(dt_nasc, 'yyyymmdd'), salario, endereco, bairro, cidade, uf, cep, 1, 1, 1
from temp_inss_consignados --nome da tabela
where not exists (select 1 
     from cliente_televenda
     where clt_cpf = cpf 
     and orgav_codigo = 1 
	 and clt_matricula = matricula)
				 
-- refin_televenda

INSERT INTO refin_televenda(rft_cpf, rft_matricula, rft_contrato, rft_parcelas_contrato, rft_valor_parcela,rft_data_primeiro_vencimento, rft_data_ultimo_vencimento,
                            bcolib_codigo, rft_importado, orgav_codigo, rft_data_atualizacao)
select cpf, matricula, id_contrato, quant_parc, vlr_parc, dbo.fn_converte_data(data_inicial + '01', 'yyyymmdd'), dateadd(month,convert(int,quant_parc)-1,dbo.fn_converte_data(data_inicial+'01', 'yyyymmdd')), cod_banco, 0, 1,'20131119'
from temp_contrato_inss_consignados--nome da tabela temporaria de contratos
where not exists(select 1
     from refin_televenda
     where rft_cpf = cpf
     and orgav_codigo =  1 --orgao
     and rft_valor_parcela = vlr_parc
     and bcolib_codigo = cod_banco)
and exists (select 1 from bancolib
			where bcolib_codigo = cod_banco)

select distinct cod_banco from temp_contrato_inss_consignados
where exists (select 1 from bancolib 
				  where bcolib_codigo = cod_banco)
				
----incluir na telefone_adicional_televenda
insert telefone_adicional_televenda(clt_codigo, tat_telefone)
select clt_codigo, dbo.fn_formata_telefone(ddd, fone)
from cliente_televenda a, temp_fone_inss_consignados --nome da tabela
where clt_cpf = cpf
      and not exists(select 1
                  from telefone_adicional_televenda b
      where a.clt_codigo = b.clt_codigo
      and b.tat_telefone = dbo.fn_formata_telefone( ddd, fone))



select * from temp_contrato_inss_consignados



--consignados Union--

select matricula, nome, dt_nasc, cpf, digitocpf, [vl-beneficio] salario, [id-banco-pagto] id_banco_pagto, 
       [id-agencia-banco] id_agencia_banco, [nu-conta-corrente] nu_conta_corrente, [id-orgao-pagador], 
	   [cs-meio-pagto] cs_meio_pagto, cod_banco, id_contrato, vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, 
	   bairro, cidade, uf, cep, ddd, fone, situacao
into temp_inss_consignados
from temp_inss_consignados_agosto2013
union

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador], NULL, 
       cod_banco, id_contrato, vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_setembro2012
union 

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador], NULL, cod_banco, id_contrato, 
       vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_outubro2012
union

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador], NULL, cod_banco, id_contrato, vlr_emp,
       data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_marco2013
union


select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador], NULL, cod_banco, id_contrato, vlr_emp,
       data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_maio2013
union


select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador], NULL,
       cod_banco, id_contrato, vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_junho2012
union

select matricula, nome, dt_nasc, cpf, digitocpf,[vl-beneficio],[id-banco-pagto],[id-agencia-banco],[nu-conta-corrente],[id-orgao-pagador],
     NULL, cod_banco, id_contrato, vlr_emp,data_inicial,quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_Julho2013
union 

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL,NULL, [id-orgao-pagador], NULL, cod_banco, 
      id_contrato, vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_jan2013
union

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL,[id-orgao-pagador],NULL,cod_banco,id_contrato,vlr_emp,
	data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_fevereiro2013
union

select matricula, nome, dt_nasc, cpf, digitocpf, NULL, [id-banco-pagto], NULL, NULL, [id-orgao-pagador],NULL,  cod_banco, id_contrato, 
       vlr_emp, data_inicial, quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_abril2013
union

select matricula, nome, dt_nasc,cpf, codigocpf, NULL, NULL, NULL, NULL, [id-orgao-pagador],NULL,cod_banco,id_contrato,vlr_emp, data_inicial, 
	   quant_parc, vlr_parc, endereco, bairro, cidade, uf, cep, ddd, fone, situacao
from temp_inss_consignados_dezembro2012