IF OBJECT_ID('p_plug_Farma_tipoRegistro_nove') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_nove
	END
 
GO 

CREATE procedure [dbo].[p_plug_Farma_tipoRegistro_nove] (@filial int, @data  date, @cd_emp INT= 1, @totaliza bit =0)
as
BEGIN
	--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
								, teleentrega	varchar(2)
								, ecommerce		varchar(2)
								, canalEcommerce varchar(10)
								, numeroCaixa varchar(10));
	/************************************************************************************************************************************/				 
		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
				, 5 AS tipoNotaVenda
				, COALESCE(SERIE.serie, ''999'') AS serieNota
				, '''' AS serieEcf
				, CID.uf 
				, 0 cd_vend
				, ''2''
				, ''2''
				, ''''
				, ecf.cd_cx
		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
				, ''2''
				, ''2''
				, ''''
				, vd.cd_cx
		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 and vd.flag_nfce = 1 ;'
		Exec(@Sql); 
		/************************************************************************************************************************************/
		

		 UPDATE VD
		 SET ecommerce = case when isnull(ifood.valor,0) = 1 or isnull(Ecommerce.valor,0) = 1 then '1' else '2' end,
			 canalEcommerce = case when isnull(ifood.valor,0) = 1 then '02'
								   when isnull(Ecommerce.valor,0) = 1 then '01'
							  else '' END,
			 teleentrega = case when isnull(delivery.valor,0) = 1 or isnull(ifood.valor,0) = 1 then '1' else '2' end
		 FROM #TmpVendas VD
		 INNER JOIN PDV_VD V ON V.CD_EMP = 1
								AND V.CD_VD = VD.CD_VD
								AND V.CD_FILIAL = @filial
		 OUTER APPLY (SELECT top 1 1 as valor
					  from integra_ecommerce_pedido p
					  where (p.CD_ORC = v.nr_ped_televd
							and p.CD_ORC <> 0)) as Ifood
		 OUTER APPLY (SELECT top 1 1 as valor
							  from INTEGRADOR_LINX_ECOMMERCE_PEDIDO p
							  where p.CODIGO_TELEVENDAS = v.nr_ped_televd) as Ecommerce
		 OUTER APPLY (SELECT TOP 1 1 as valor
					  FROM TELE_ORC O
					  WHERE O.CD_ORC = V.NR_PED_TELEVD
					  AND O.CD_FILIAL = @filial
					  AND O.CD_EMP = 1
					  AND O.ENTREGA_BALCAO = 0 /*Delivery*/
					  ) Delivery

		 --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
			 , '2'
			  , '2'
			  , ''
			  , 0 as cd_cx
		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;  

		 UPDATE VD
		 SET ecommerce = case when isnull(ifood.valor,0) = 1 or isnull(Ecommerce.valor,0) = 1 then '1' else '2' end,
			 canalEcommerce = case when isnull(ifood.valor,0) = 1 then '02'
								   when isnull(Ecommerce.valor,0) = 1 then '01'
							  else '' END,
			 teleentrega = case when isnull(delivery.valor,0) = 1 or isnull(ifood.valor,0) = 1 then '1' else '2' end
		 FROM #TmpVendas VD
		 INNER JOIN EST_NF_SAI S ON S.CD_NF = VD.cd_vd AND S.CD_EMP = 1 AND S.CD_FILIAL = @filial
		 INNER JOIN EST_PED_VD P ON P.CD_PED = S.CD_PED AND P.CD_FILIAL = S.CD_FILIAL AND P.CD_EMP = S.CD_EMP
		 INNER JOIN TELE_ORC_EST_PED_VD OT ON OT.CD_EMP = P.CD_EMP AND OT.CD_FILIAL = P.CD_FILIAL AND OT.CD_PED = P.CD_PED
		 INNER JOIN TELE_ORC O ON O.CD_ORC = OT.CD_ORC
		 OUTER APPLY (SELECT top 1 1 as valor
					  from integra_ecommerce_pedido p2
					  where (p2.CD_ORC = O.CD_ORC
						and p2.CD_ORC <> 0)) as Ifood
		 OUTER APPLY (SELECT top 1 1 as valor
							  from INTEGRADOR_LINX_ECOMMERCE_PEDIDO p2
							  where p2.CODIGO_TELEVENDAS = O.CD_ORC) as Ecommerce
		 OUTER APPLY (SELECT TOP 1 1 as valor
					  FROM TELE_ORC O2
					  WHERE O2.CD_ORC = O.CD_ORC
					  AND O2.CD_FILIAL = @filial
					  AND O2.CD_EMP = 1
					  AND O2.ENTREGA_BALCAO = 0 /*Delivery*/
					  ) Delivery
		WHERE VD.tipoNotaVenda = 4


		--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
			 , v.teleentrega as delivery
			 , v.ecommerce as ecommerce
			 , v.canalEcommerce
			 , '' as detalheCanalEcommerce
			 , v.numeroCaixa
		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
	/************************************************************************************************************************************/
END
GO


