if exists(select  1 from sys.procedures where name = 'p_plug_Farma_tipoRegistro_nove')
    begin 
		drop procedure p_plug_Farma_tipoRegistro_nove;
    end;
go
--p_plug_Farma_tipoRegistro_nove 4,'2019-07-16'
create procedure p_plug_Farma_tipoRegistro_nove (@filial int, @data  date, @cd_emp INT= 1, @totaliza bit =0)
as
--declare @filial int =5, @data date='2019-07-16';

set nocount on;
/************************************************************************************************************************************/
CREATE TABLE #TmpVendas ( cd_vd                  INT
							, tipoRegistro           INT
							, dataReferenciaVenda    DATETIME
							, valorTotalVendaLiquido MONEY
							, horaReferenciaVenda    DATETIME
							, numeroNota             INT
							, tipoPagamento          INT
							, cd_cli                 INT
							, COD_CLI                INT
							, tipoNotaVenda          INT
							, serieNota              VARCHAR(50)
							, serieEcf               VARCHAR(50)
							, uf                     CHAR(2) 
							, cd_vend				 int);
/************************************************************************************************************************************/				 
	Declare @sql varchar(8000);

	Set @Sql = 'insert into #TmpVendas
	SELECT 
			ECF.cd_vd
			, 9				 AS tipoRegistro
			, ECF.dt_vd		 AS dataReferenciaVenda
			, ECF.vlr_liq_vd AS valorTotalVendaLiquido
			, ECF.hr_vd		 AS horaReferenciaVenda
			, ECF.nr_ecf	 AS numeroNota
			,CASE WHEN
				(	   CASE WHEN ECF.vlr_dinh > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vl_chqs_a_prz > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vl_chqs_a_vista > 0	THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_chqs > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_cartao > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_conv > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_crediario > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_conv_pbm > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_dup > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_ncc > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN ECF.vlr_tickets > 0		THEN 1 ELSE 0 END 
					
					) = 1 then 
					(
						CASE WHEN ECF.vlr_dinh > 0			THEN 1 ELSE 0 END 
						+ CASE WHEN ECF.vl_chqs_a_prz > 0	THEN 2 ELSE 0 END
						+ CASE WHEN ECF.vl_chqs_a_vista > 0 THEN 2 ELSE 0 END
						+ CASE WHEN ECF.vlr_chqs > 0		THEN 2 ELSE 0 END 
						+ CASE WHEN ECF.vlr_cartao > 0		THEN 3 ELSE 0 END
						+ CASE WHEN ECF.vlr_conv > 0		THEN 6 ELSE 0 END 
						+ CASE WHEN ECF.vlr_crediario > 0	THEN 7 ELSE 0 END
						+ CASE WHEN ECF.vlr_conv_pbm > 0	THEN 8 ELSE 0 END 
						+ CASE WHEN ECF.vlr_dup > 0			THEN 8 ELSE 0 END
						+ CASE WHEN ECF.vlr_ncc > 0			THEN 8 ELSE 0 END
						+ CASE WHEN ECF.vlr_tickets > 0		THEN 8 ELSE 0 END
					)
				else  00 end AS tipoPagamento


			, ECF.cd_cli AS COD_CLI
			, ECF.cd_cli
			, 3 AS tipoNotaVenda
			, COALESCE(SERIE.serie, ''999'') AS serieNota
			, cx.nr_serie_imp AS serieEcf
			, CID.uf 
			, 0 cd_vend
	FROM
			pdv_vd ECF
			INNER JOIN pdv_cx CX ON CX.cd_emp = ECF.cd_emp
									AND CX.cd_filial = ECF.cd_filial
									AND CX.cd_cx = ECF.cd_cx
			INNER JOIN prc_filial FILIAL ON FILIAL.cd_emp = ECF.cd_emp
											AND FILIAL.cd_filial = ECF.cd_filial
 
			LEFT JOIN glb_cid CID ON CID.cd_cid = FILIAL.cd_cid
			LEFT JOIN pdv_cx_est_nf_serie CXSERIE ON CXSERIE.cd_ctr = CX.cd_ctr_serie
			LEFT JOIN est_nf_serie SERIE ON SERIE.cd_emp = CXSERIE.cd_emp
											AND SERIE.cd_nf_serie = CXSERIE.cd_nf_serie
	WHERE ECF.cd_emp = ' + convert(varchar, @cd_emp) + 'AND ECF.cd_filial = ' + convert(varchar, @filial)  + ' AND ECF.st_vd=0 AND ECF.dt_vd = ''' +  convert(varchar, @data,112)  + ''' AND ECF.FLAG_NFCE =0;';

	Exec(@Sql);

	/************************************************************************************************************************************/
 

	Set @Sql = 'insert into #TmpVendas
	SELECT  
			VD.cd_vd
			, 9 AS tipoRegistro
			, VD.dt_vd AS dataReferenciaVenda
			, VD.vlr_liq_vd AS valorTotalVendaLiquido
			, VD.hr_vd AS horaReferenciaVenda
			, VD.nr_ecf AS numeroNota 
			,CASE WHEN
				(	   CASE WHEN vd.vlr_dinh > 0				THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vl_chqs_a_prz > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vl_chqs_a_vista > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_chqs > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_cartao > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_conv > 0			THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_crediario > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_conv_pbm > 0		THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_dup > 0				THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_ncc > 0				THEN 1 ELSE 0 END  
						+  CASE WHEN vd.vlr_tickets > 0			THEN 1 ELSE 0 END 
					
					) = 1 then 
					(
						CASE WHEN vd.vlr_dinh > 0			THEN 1 ELSE 0 END 
						+ CASE WHEN vd.vl_chqs_a_prz > 0	THEN 2 ELSE 0 END
						+ CASE WHEN vd.vl_chqs_a_vista > 0	THEN 2 ELSE 0 END
						+ CASE WHEN vd.vlr_chqs > 0			THEN 2 ELSE 0 END 
						+ CASE WHEN vd.vlr_cartao > 0		THEN 3 ELSE 0 END
						+ CASE WHEN vd.vlr_conv > 0			THEN 6 ELSE 0 END 
						+ CASE WHEN vd.vlr_crediario > 0	THEN 7 ELSE 0 END
						+ CASE WHEN vd.vlr_conv_pbm > 0		THEN 8 ELSE 0 END 
						+ CASE WHEN vd.vlr_dup > 0			THEN 8 ELSE 0 END
						+ CASE WHEN vd.vlr_ncc > 0			THEN 8 ELSE 0 END
						+ CASE WHEN vd.vlr_tickets > 0		THEN 8 ELSE 0 END
					)
				else  00 end AS tipoPagamento


			, VD.cd_cli AS COD_CLI
			, VD.cd_cli
			, 5 AS tipoNotaVenda
			, ISNULL(NFCE.serie, VD.cd_cx) AS serieNota
			, '''' AS serieEcf
			, CID.uf
			, 0 cd_vend
	FROM
			pdv_vd VD 
			JOIN pdv_vd_pdv_vd_nfce VDNFCE ON VD.cd_emp = VDNFCE.cd_emp
													AND VD.cd_filial = VDNFCE.cd_filial
													AND VD.cd_vd = VDNFCE.cd_vd

			JOIN pdv_vd_nfce NFCE ON VDNFCE.cd_emp = NFCE.cd_emp
										AND VDNFCE.cd_filial = NFCE.cd_filial
										AND VDNFCE.cd_nfce = NFCE.cd_nfce

			INNER JOIN prc_filial FILIAL ON FILIAL.cd_emp = VD.cd_emp
											AND FILIAL.cd_filial = VD.cd_filial
			LEFT JOIN glb_cid CID ON CID.cd_cid = FILIAL.cd_cid
			LEFT JOIN pdv_vd_nfce_cancel CANCEL ON CANCEL.cd_nfce = VDNFCE.cd_nfce 
	WHERE VD.cd_emp ='+ convert(varchar, @cd_emp) + ' AND VD.cd_filial =  ' + convert(varchar, @filial) + ' AND VD.dt_vd= ''' +  convert(varchar, @data,112)  + '''  AND VD.st_vd=0 ;'
	 
	Exec(@Sql); 

	/************************************************************************************************************************************/
	 --Remove as vendas com controle de receita, mas sem receita
		DELETE FROM vd
		FROM 
			#TmpVendas vd
			JOIN pdv_vd_it it ON vd.cd_vd = it.cd_vd
								AND it.cd_emp    = @cd_emp
								AND it.cd_filial = @filial
								AND it.dt_vd	 = @data
			JOIN est_prod prod ON prod.cd_emp = it.cd_emp
								AND prod.cd_prod = it.cd_prod
			LEFT JOIN pdv_vd_it_rec rec ON	rec.cd_vd		= vd.cd_vd 
										AND rec.cd_filial	= @filial
										AND rec.cd_emp		= @cd_emp
		WHERE
			it.ST_IT = 1 
			AND prod.controle_sngpc = 1
			AND rec.cd_vd IS NULL;

	/*Verifica se todos os itens da venda estao cancelados e a venda ativa*/
	;With Cancelados as(select 
							cd_emp
							,cd_filial
							,cd_vd
							, sum (case when st_it=2 then 1 else 0 end) cancelado
							, sum (1) totalItens
						from pdv_vd_it it
						where
							it.cd_emp =1
							AND it.cd_filial = @filial
							AND it.dt_vd = @data
						group by 
							 cd_emp
							,cd_filial
							,cd_vd)
	delete from vd 
	from 
		cancelados c
		join #TmpVendas vd on 
		c.cd_vd = vd.cd_vd
	where	
		cancelado= totalItens; 
	
	--Remove vendas sem itens--
	DELETE FROM vd
	FROM 
		#TmpVendas vd
		LEFT JOIN pdv_vd_it it ON vd.cd_vd = it.cd_vd
							AND it.cd_emp    = @cd_emp
							AND it.cd_filial = @filial
							AND it.dt_vd	 = @data
	WHERE
		it.cd_emp is null;

/************************************************************************************************************************************/
--Notas fiscais de venda

	insert into #TmpVendas
	SELECT
		   s.cd_nf
		 , 9 AS tipoRegistro
		 , s.dt_emi_nf AS dataReferenciaVenda
		 , s.VLR_TOT_PROD AS valorTotalVendaLiquido
		 , convert(datetime, left(convert(varchar, s.dt_emi_nf,120),10) + ' ' + convert(varchar, s.HR_EMI_NF,108)+ '.000') horaReferenciaVenda
		 , s.nf_nf AS numeroNota
		 , 8 AS tipoPagamento
		 , s.cd_cli AS COD_CLI
		 , s.cd_cli
		 , 4 AS tipoNotaVenda
		 , sr.serie AS serieNota
		 , '' AS serieEcf
		 , CID.uf
		 , case when s.cd_vend = 0 then 1 else s.cd_vend end cd_vend
	FROM
		 est_nf_sai s
		 INNER JOIN prc_filial FILIAL ON FILIAL.cd_emp = s.cd_emp
										 AND FILIAL.cd_filial = s.cd_filial
	 
		 INNER JOIN GLB_MOV_OP o ON s.CD_NT_MOV = o.CD_MOV_OP
		
		 JOIN est_nf_serie sr ON s.cd_emp = sr.cd_emp
								 AND s.cd_nf_serie = sr.CD_NF_SERIE
		 LEFT JOIN glb_cid CID ON CID.cd_cid = FILIAL.cd_cid
	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;  

	--Remove vendas sem itens--
	DELETE FROM vd
	FROM 
		#TmpVendas vd
		LEFT JOIN est_nf_sai_it it ON vd.cd_vd = it.cd_nf
							AND it.cd_emp    = @cd_emp
							AND it.cd_filial = @filial
							AND it.dt_emi_nf = @data
	WHERE
		it.cd_emp is null
		and vd.tipoNotaVenda= 4;

/************************************************************************************************************************************/

	--remove clientes sem cadastro
	update vd
	set
		  cd_cli  =1		
		  , COD_CLI   =1
	from 
		#TmpVendas vd  
		left join rc_cli c on 
		c.cd_cli = vd.cd_cli
		and c.cd_emp=@cd_emp
	where	
		  c.cd_emp is null; 



/************************************************************************************************************************************/
/************************************************************************************************************************************/
if @totaliza =0
begin
	SELECT
		   tiporegistro
		 , ROW_NUMBER() OVER(ORDER BY cd_vd ASC) AS numeroRegistroVenda
		 , datareferenciavenda
		 , valortotalvendaliquido
		 , horareferenciavenda
		 , numeronota
		 , tipopagamento
		 , cli.numeroreg AS numeroRegistroPessoa
		 , tiponotavenda
		 , serienota
		 , serieecf
		 , cd_vd
		 , uf
		 , cod_cli 
		 , cd_vend
	FROM
		#TmpVendas v
		join (SELECT
			   ROW_NUMBER() OVER(ORDER BY CD_CLI ASC) numeroReg
			 , cd_cli FROM ( SELECT DISTINCT CD_CLI
								FROM #TMPVENDAS) V ) cli on 
		v.cd_cli = cli.cd_cli
	
end
else
begin
	select sum (valortotalvendaliquido)  valortotalvendaliquido from #TmpVendas

end

drop table #TMPVENDAS

/************************************************************************************************************************************/