 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].UFD_BC_LANC_CONCILIACAO_MANUAL') 
			AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].UFD_BC_LANC_CONCILIACAO_MANUAL
GO
GO
GO
 -- DROP FUNCTION UFD_BC_LANC_CONCILIACAO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--   SELECT CD_FILIAL, * FROM UFD_BC_LANC_CONCILIACAO_MANUAL (1,'11','20210401','20210610',0,0,'0,1',0) Order By DT_LANCTO,CD_BC_LANC

--Select CD_EMP,CD_BC_LANC,DT_LANCTO,HIST_LANC,VLR_LANC,DT_COMPES,NR_CNT,DS_CNT,CD_CHQ_LANC,TP_LANC,ORIG_LANC,TIPO_CONC 
--From UFD_BC_LANC_CONCILIACAO_MANUAL(1,'90','07/24/2020','07/24/2020',0,0,'0,1',0) Order By DT_LANCTO,CD_BC_LANC

CREATE FUNCTION [dbo].UFD_BC_LANC_CONCILIACAO_MANUAL (
										@CD_EMP			INT,
										@CD_CNT			VARCHAR(50),
										@DT_INI			DATETIME,
										@DT_FIM			DATETIME,
										@SOMENTE_CHQ	INT,          -- 0 = NAO 1, = SIM
										@TP_COMPES		INT,          -- 0 Não Conciliado; 1 - Conciliado
										@TP_LANC		VARCHAR(500), -- TIPO DE LANÇAMENTO: CRÉDITO OU DÉBITO
										@PESQ_TIPO_CONCILIACAO INT)   -- 0 = SEM CRITERIO,  1 = SOMENTE MANUAL, 2 SOMENTE AUTOMATICO
									 
RETURNS @BC_LANC_CONC TABLE(
							CD_EMP				INT,
							CD_BC_LANC			INT,
							DT_LANCTO			DATETIME,
							HIST_LANC			VARCHAR(500) COLLATE SQL_LATIN1_GENERAL_CP1251_CI_AS,
							VLR_LANC			MONEY,
							DT_COMPES			DATETIME,
							NR_CNT				VARCHAR(100),
							DS_CNT				VARCHAR(100),
							CD_CHQ_LANC			INT,
							TP_LANC				INT,
							ORIG_LANC           INT,
							LANC_CHQ			INT,
							TIPO_CONC           INT,  -- 0 = MANUAL; 1= ARQUIVO
							REG_CONC            INT, -- REGISTRO CONCILIADO 0 = NAO 1 = SIM
							CD_FILIAL           INT) 
AS
BEGIN

INSERT INTO @BC_LANC_CONC

--DECLARE @CD_EMP         INT
--DECLARE @CD_CNT			VARCHAR(50)
--DECLARE @DT_INI			DATETIME
--DECLARE @DT_FIM			DATETIME
--DECLARE @SOMENTE_CHQ	INT
--DECLARE @TP_COMPES		INT          --0 Não Conciliado; 1 - Conciliado
--DECLARE @TP_LANC		VARCHAR(500) --TIPO DE LANÇAMENTO: CRÉDITO OU DÉBITO
--DECLARE @SOMENTE_MANUAL INT   
 
--SET @CD_EMP         = 1       SET @CD_CNT  = 16     SET @DT_INI= '20181126'      SET @DT_FIM ='20181126' 
--SET @SOMENTE_CHQ	= 0       SET @TP_COMPES= 0     SET @TP_LANC= '0,1'          SET @SOMENTE_MANUAL = 0 

--CHEQUES CONCILIACAO MANUAL
SELECT
	BC_LANC.CD_EMP,
	BC_LANC.CD_BC_LANC, 
	BC_LANC.DT_LANCTO, 
	BC_LANC.HIST_LANC, 
	BC_CHQ_LANC.VLR_CHQ AS VLR_LANC,
    BC_CHQ_LANC_COMPES.DT_COMPES,
	BC_CONTAS.NR_CNT,  
	BC_CONTAS.DS_CNT, 
	BC_CHQ_LANC.CD_CHQ_LANC,
	BC_LANC.TP_LANC,
	0 AS ORIG_LANC,
	1 AS LANC_CHQ,
    0 AS TIPO_CONC,
	CASE WHEN ISNULL(BC_CHQ_LANC_COMPES.DT_COMPES,0) = 0 THEN
		0 
	ELSE
		1
	END AS REG_CONC ,
   V_BC_LANC_FILIAL.CD_FILIAL
FROM  
	BC_CHQ_LANC 
	INNER JOIN BC_LANC_BC_CHQ_LANC ON
		BC_CHQ_LANC.CD_EMP = BC_LANC_BC_CHQ_LANC.CD_EMP AND   
		BC_CHQ_LANC.CD_CHQ_LANC = BC_LANC_BC_CHQ_LANC.CD_CHQ_LANC  
	INNER JOIN BC_LANC ON
		BC_LANC_BC_CHQ_LANC.CD_EMP = BC_LANC.CD_EMP AND   
		BC_LANC_BC_CHQ_LANC.CD_BC_LANC = BC_LANC.CD_BC_LANC  
	
	LEFT OUTER JOIN BC_CHQ_LANC_COMPES ON
		BC_CHQ_LANC.CD_EMP = BC_CHQ_LANC_COMPES.CD_EMP AND   
		BC_CHQ_LANC.CD_CHQ_LANC = BC_CHQ_LANC_COMPES.CD_CHQ_LANC 
	LEFT OUTER JOIN BC_LANC_CONC ON
		BC_LANC.CD_EMP = BC_LANC_CONC.CD_EMP AND   
		BC_LANC.CD_BC_LANC = BC_LANC_CONC.CD_BC_LANC 
	INNER JOIN BC_CONTAS ON
		BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND    
		BC_LANC.CD_CNT = BC_CONTAS.CD_CNT 
	LEFT JOIN V_BC_LANC_FILIAL ON 
	 BC_LANC.CD_EMP = V_BC_LANC_FILIAL.CD_EMP
	 AND BC_LANC.CD_BC_LANC = V_BC_LANC_FILIAL.CD_BC_LANC
 
WHERE  
	BC_LANC.CD_EMP = @CD_EMP 
	AND BC_LANC.CD_CNT = @CD_CNT  
	AND BC_LANC.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM
	AND BC_LANC.TP_LANC IN (SELECT * FROM UFD_PRM_IN_STRING(@TP_LANC))
 
 UNION ALL
 
--CONCILIACAO MANUAL e AUTOMATICO	
	SELECT
		BC_LANC.CD_EMP,
		BC_LANC.CD_BC_LANC,
		BC_LANC.DT_LANCTO,
		BC_LANC.HIST_LANC,
		BC_LANC.VLR_LANC,
		BC_LANC_CONC.DT_COMPES,
		BC_CONTAS.NR_CNT,
		BC_CONTAS.DS_CNT,
		0 AS CD_CHQ_LANC,
		BC_LANC.TP_LANC,
		(CASE WHEN BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO.CD_BC_LANC_CRED IS NULL THEN 0 ELSE 1 END) AS ORIG_LANC,
		0 AS LANC_CHQ,
	    CASE WHEN ISNULL(BC_LANC_CONC.TP_CONC,0) = 0 THEN
			0 
		ELSE
			1
		END AS TIPO_CONC,
				
		CASE WHEN ISNULL(BC_LANC_CONC.DT_COMPES,0) = 0 THEN
			0 
		ELSE
			1
		END AS REG_CONC ,
		V_BC_LANC_FILIAL.CD_FILIAL 
	FROM  
		BC_LANC  
		LEFT OUTER JOIN BC_LANC_CONC ON
			BC_LANC.CD_EMP = BC_LANC_CONC.CD_EMP AND   
			BC_LANC.CD_BC_LANC = BC_LANC_CONC.CD_BC_LANC 
		INNER JOIN BC_CONTAS ON
			BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND    
			BC_LANC.CD_CNT = BC_CONTAS.CD_CNT 
		LEFT OUTER JOIN BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO ON
	    BC_LANC.CD_EMP     = BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO.CD_EMP AND
	    BC_LANC.CD_BC_LANC = BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO.CD_BC_LANC_CRED  
		LEFT OUTER JOIN BC_LANC_BC_CHQ_LANC ON
		BC_LANC.CD_EMP = BC_LANC_BC_CHQ_LANC.CD_EMP AND   
		BC_LANC.CD_BC_LANC = BC_LANC_BC_CHQ_LANC.CD_BC_LANC  
 	    LEFT JOIN V_BC_LANC_FILIAL ON 
	    BC_LANC.CD_EMP = V_BC_LANC_FILIAL.CD_EMP
	    AND BC_LANC.CD_BC_LANC = V_BC_LANC_FILIAL.CD_BC_LANC		
	WHERE  
		
		BC_LANC.CD_EMP = @CD_EMP 
		AND BC_LANC.CD_CNT = @CD_CNT  
		AND BC_LANC.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM
		AND BC_LANC.TP_LANC IN (SELECT * FROM UFD_PRM_IN_STRING(@TP_LANC))
		AND BC_LANC_BC_CHQ_LANC.CD_BC_LANC IS NULL

		--		BC_LANC.CD_EMP = 1 
		--AND BC_LANC.CD_CNT = 11  
		--AND BC_LANC.DT_LANCTO BETWEEN '20210423' AND '20210423'
		--AND BC_LANC.TP_LANC IN (SELECT * FROM UFD_PRM_IN_STRING('0,1'))
		--AND BC_LANC_BC_CHQ_LANC.CD_BC_LANC IS NULL

		--SELECT * FROM V_BC_LANC_FILIAL WHERE CD_BC_LANC = 141 
 
--===APAGANDO REGISTROS=====

--Apagando lançamentos que não são cheques

IF @SOMENTE_CHQ = 1 
BEGIN

	DELETE FROM		
		@BC_LANC_CONC
	WHERE
		LANC_CHQ = 0    --  1 = CHEQUES
END
----------------------------------------------------------------------
 
--PEGANDO SOMENTE OS NÃO CONCILIADOS
IF @TP_COMPES = 0   -- deleta os conciliados
	BEGIN
		DELETE FROM		
			@BC_LANC_CONC
		WHERE REG_CONC = 1    --valor 0 = NAO,  1= SIM
			 
	END

--PEGANDO SOMENTE OS CONCILIADOS
IF @TP_COMPES = 1   -- -- deleta os não conciliados
	BEGIN
		DELETE FROM		
			@BC_LANC_CONC
		WHERE REG_CONC = 0    --valor 0 = NAO,  1= SIM
	END
 
 IF @PESQ_TIPO_CONCILIACAO = 1   -- APAGA OS REGISTROS AUTOMATICOS (TIPO_CONC = 1)
	BEGIN
		DELETE FROM		
			@BC_LANC_CONC
		WHERE
			 TIPO_CONC = 1 
	END

 IF   @PESQ_TIPO_CONCILIACAO = 2   -- APAGA OS REGISTROS MANUAIS (TIPO_CONC = 0)
	BEGIN
		DELETE FROM		
			@BC_LANC_CONC
		WHERE
			 TIPO_CONC =0
	END
  

RETURN
END
GO


