IF EXISTS( SELECT 1 FROM sys.procedures WHERE name = 'p_plug_Farma_tipoRegistro_seis' )
    BEGIN
        DROP PROCEDURE p_plug_Farma_tipoRegistro_seis;
    END;
GO

--p_plug_Farma_tipoRegistro_seis 21,'20191031'
CREATE PROCEDURE p_plug_Farma_tipoRegistro_seis( @filial INT , @data   DATE , @cd_emp INT  = 1 )
AS
/*
--Esse SQL tem como objetivo trazer os dados de todas as pessoas que apareceram no movimento do dia
*/     

set nocount on 
IF Object_id('tempDB..#TmpVendas', 'U') IS NOT NULL
	DROP TABLE #TmpVendas;


	CREATE TABLE #TmpVendas (cod_cli INT);	
	CREATE TABLE #TmpCli (cod_cli INT);
	/*
	CREATE TABLE #RcCli (	 tipoRegistro					int
							,numeroRegistro					int
							,tipoPessoa						int
							,tipoId							int
							,cpfCnpj						varchar(50)
							,tipoDocumento					int
							,orgaoExpedidorDocumento		varchar(40)
							,rgDocumento					varchar(50)
							,nome							varchar(255)
							,dataNascimento					date
							,codigoIbgeCidade				int
							,enderecoLogradouro				varchar(150)
							,enderecoNumero					varchar(15)
							,enderecoComplemento			varchar(50)
							,cep							varchar(50)
							,telefone						varchar(50)
							,celula							varchar(50)
							,email							varchar(50)
							,sexo							tinyint
							,cd_cli							int)

							*/
	declare @CdCid int;

	set @CdCid = isnull((select cd_cid from prc_filial where cd_filial =@filial and cd_emp=@cd_emp),1)

	

	INSERT INTO #TmpCli
	SELECT CD_CLI
	FROM
		 pdv_vd 
	WHERE cd_emp = @cd_emp
		  AND cd_filial = @filial
		  AND dt_vd = @data  ;
	
	INSERT INTO #TmpCli
	SELECT SngpcRec.CD_CLI
	FROM
		pdv_vd_it IT
		INNER JOIN pdv_vd_it_est_sngpc_saida ITSNGPC ON ITSNGPC.cd_emp = IT.cd_emp
														AND ITSNGPC.cd_it = IT.cd_it
														AND ITSNGPC.cd_vd = IT.cd_vd
														AND ITSNGPC.cd_filial = IT.cd_filial

		INNER JOIN est_sngpc_saida SNGPC ON SNGPC.cd_sai_sngpc = ITSNGPC.cd_sai_sngpc
		INNER JOIN est_prod_est_sngpc_lote LOTE ON LOTE.cd_emp = SNGPC.cd_emp
													AND LOTE.cd_lote = SNGPC.cd_lote
													 
		JOIN est_sngpc_saida_receita Sr ON SNGPC.cd_sai_sngpc = SR.cd_sai_sngpc
		JOIN est_sngpc_receita SngpcRec ON sR.cd_receita = SngpcRec.cd_receita

	WHERE   it.cd_emp = @cd_emp
		AND it.cd_filial = @filial
		AND it.dt_vd = @data  ;

	INSERT INTO #TmpCli
	SELECT  s.cd_cli AS COD_CLI 
	FROM
		est_nf_sai s 
		INNER JOIN GLB_MOV_OP o ON s.CD_NT_MOV = o.CD_MOV_OP 
	WHERE s.cd_emp = @cd_emp
		AND s.cd_filial = @filial
		AND s.dt_emi_nf = @data
		AND s.STS_NF = 0
		and o.tp_mov_op_transa =1
		and o.tp_mov =0;  


	if exists(select * from #TmpCli)
	begin							
		INSERT INTO #TmpCli
		SELECT 1
	end

	insert into	#TmpVendas 
	select distinct cod_cli
	from #TmpCli

/***********************************************************************************************/
	--INSERT INTO #RcCli
	;With Cli as (SELECT
		   6 AS tipoRegistro
		 , ROW_NUMBER() OVER(ORDER BY CLI.cd_cli ASC) AS numeroRegistro
		 , 01 AS tipoPessoa
		 , IIF(CLI.pessoa = 0, 01, 02) AS tipoId
		 --, CASE WHEN DBO.VALIDA_CPF_CNPJ( dbo.F_LIMPA_NUMEROS (CLI.cgc_cpf))= 'N' THEN '00000000000' ELSE dbo.F_LIMPA_NUMEROS (CLI.cgc_cpf) END  AS cpfCnpj
		 ,CLI.cgc_cpf AS cpfCnpj
		 , 99 AS tipoDocumento
		 , IIF(CLI.rg_org_exp = '', 'NENHUM', COALESCE(CLI.rg_org_exp, 'NENHUM')) AS orgaoExpedidorDocumento
		 , IIF(CLI.rg = '', 'NENHUM', COALESCE(CLI.rg, 'NENHUM')) AS rgDocumento
		 , CLI.rz_cli AS nome
		 , COALESCE(dt_nasc, '01/01/1970') AS dataNascimento
		 , CID.cd_ibge AS codigoIbgeCidade
		 , CLI.end_cli AS enderecoLogradouro
		 , CLI.numero AS enderecoNumero
		 , CLI.complemento AS enderecoComplemento
		 , CLI.cep AS cep
		 , CLI.tel AS telefone
		 , CLI.telefone1 AS celula
		 , CLI.email AS email
		 , CASE CLI.sexo_cli
			   WHEN 0 THEN 1
			   WHEN 1 THEN 2
		   END AS sexo
		 , CLI.cd_cli
	FROM
		 rc_cli CLI
		 join #TmpVendas v on 
		 	CLI.cd_cli = v.cod_cli
		 INNER JOIN glb_cid CID ON 
			CID.cd_cid = isnull(CLI.cd_cid,@CdCid )
	WHERE 
		CLI.CD_emp = @cd_emp)
	select 
		 tipoRegistro				
		,numeroRegistro				
		,tipoPessoa					
		,case when len(cpfCnpj) = 14 then 2 else 1 end tipoId						
		,CASE WHEN DBO.VALIDA_CPF_CNPJ(cpfCnpj)= 'N' THEN '00000000000' ELSE cpfCnpj END AS cpfCnpj
		,tipoDocumento				
		,orgaoExpedidorDocumento	
		,rgDocumento				
		,nome						
		,dataNascimento				
		,codigoIbgeCidade			
		,enderecoLogradouro			
		,enderecoNumero				
		,enderecoComplemento		
		,cep						
		,telefone					
		,celula						
		,email						
		,sexo						
		,cd_cli						
	from Cli;

/*	UPDATE 
		#RcCli
		SET cpfCnpj=dbo.F_LIMPA_NUMEROS (cpfCnpj);

	UPDATE 
		#RcCli
		SET cpfCnpj= CASE WHEN DBO.VALIDA_CPF_CNPJ( cpfCnpj)= 'N' THEN '00000000000'
				ELSE cpfCnpj END ;

	SELECT * FROM #RcCli;
	
	*/