IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_BC_LANC_EXTRATO_DDA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_BC_LANC_EXTRATO_DDA]
GO


CREATE PROCEDURE [dbo].[P_BC_LANC_EXTRATO_DDA](@CD_EMP INT,
									      @CD_CNT INT,
										  @DT_INI DATETIME,
										  @DT_FIM	DATETIME,
										  @TP_COMPES INT,
										  @TP_LANC_DEB_CRED INT) --0 N?o Compensado; 1 - Compensado
										  										  												
AS
SET NOCOUNT ON

CREATE TABLE #BC_LANC_EXTRATO ( ORDEM		INT IDENTITY (0, 1),
								CD_EMP		INT,
								NR_CNT		VARCHAR(100),
								DS_BANC		VARCHAR(100),
								DS_CNT		VARCHAR(100),
								AGC_CNT		VARCHAR(100),
								CD_CNT 		INT,
								CD_BC_LANC	INT,
								CD_DOC		INT,
								DT_LANCTO	DATETIME,
								HIST_LANC	VARCHAR(500),
								VLR_DEB		MONEY,
								VLR_CRED	MONEY,
								VLR_SLD     MONEY,
								CD_CHQ_LANC_EMP INT,
								TP_ORIG_LANC INT,
								CD_CHQ_LANC INT,
								FLAG_CONS_FECHA_CAIXA INT,
								PEND_ENVIO_DOC_TED INT,
								FILIAL_DEPOSITO INT,
								CD_DEPOSITO INT)


--Matriz final
CREATE TABLE #RS_BC_LANC_EXTRATO	(
									CD_EMP		INT,
									NR_CNT		VARCHAR(100),
									DS_BANC		VARCHAR(100),
									DS_CNT		VARCHAR(100),
									AGC_CNT		VARCHAR(100),
									CD_CNT 		INT,
									CD_BC_LANC	INT,
									CD_DOC		INT,
									DT_LANCTO	DATETIME,
									HIST_LANC	VARCHAR(500),
									VLR_DEB		MONEY,
									VLR_CRED	MONEY,
									VLR_SLD     MONEY,
									CD_CHQ_LANC_EMP INT,
									TP_ORIG_LANC INT,
									CD_CHQ_LANC INT,
									FLAG_CONS_FECHA_CAIXA INT,
									PEND_ENVIO_DOC_TED INT,
									FILIAL_DEPOSITO INT,
									CD_DEPOSITO INT)

--Usada para auxiliar o calculo do saldo
DECLARE @VLR_SLD	MONEY
DECLARE @SLD_INI	MONEY
DECLARE @VLR_CRED MONEY
DECLARE @VLR_DEB MONEY
DECLARE @ORDEM INT

SET @SLD_INI=0

---Gerando lancamento Saldo Inicial------------------------------
IF @TP_COMPES = 0
begin
SET @SLD_INI = (SELECT DBO.UFD_BC_LANC_EXTRATO_SLD(@CD_EMP,@CD_CNT,@DT_INI,0))

INSERT INTO #RS_BC_LANC_EXTRATO
SELECT
	@CD_EMP,
	BC_CONTAS.NR_CNT,
	BC_BANCO.DS_BC,
	BC_CONTAS.DS_CNT,
	BC_CONTAS.AGC_CNT,
	@CD_CNT,
	0,
	0,
	@DT_INI -1,
	'SALDO ANTERIOR',
	CASE WHEN @SLD_INI < 0 THEN
		ABS(@SLD_INI)
	ELSE
		0
	END,
	CASE WHEN @SLD_INI >= 0 THEN
		@SLD_INI
	ELSE
		0
	END,
	CASE WHEN @SLD_INI >= 0 THEN
		0
	ELSE
		1
	END,
	0,
	0,
	0,
	0,
	0,
	0,
	0
FROM
	BC_CONTAS 
	INNER JOIN BC_BANCO ON
		BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP
		AND BC_CONTAS.CD_BC = BC_BANCO.CD_BC
WHERE
	BC_CONTAS.CD_EMP = @CD_EMP
	AND BC_CONTAS.CD_CNT = @CD_CNT
end
-------------------------------------------------------------------------------

--Inserindo os lan?amentos de cheque
INSERT INTO #RS_BC_LANC_EXTRATO
SELECT
	BC_LANC.CD_EMP,
	BC_CONTAS.NR_CNT,
	BC_BANCO.DS_BC,
	BC_CONTAS.DS_CNT,
	BC_CONTAS.AGC_CNT,
	BC_LANC.CD_CNT,
	BC_LANC.CD_BC_LANC,
	BC_LANC.CD_DOC,
	BC_LANC.DT_LANCTO,
	BC_LANC.HIST_LANC + '   ' + ISNULL(BC_LANC.HIST_LANC_CPL,'') ,
	CASE WHEN BC_LANC.TP_LANC = 1 THEN VLR_LANC ELSE 0 END AS VLR_DEB,
	CASE WHEN BC_LANC.TP_LANC = 0 THEN VLR_LANC ELSE 0 END AS VLR_CRED,
	0,
	ISNULL(V_BC_CHQ_LANC_COMPENSADO.CD_BC_LANC,0) AS CD_BC_LANC,
	1 AS TP_ORIG_LANC,
	BC_LANC_BC_CHQ_LANC.CD_CHQ_LANC,
	0,
	CASE WHEN ISNULL(BC_LANC_CNAB_PEND.CD_BC_LANC,0)= 0  THEN 0 ELSE 1 END ,
	0 AS FILIAL_DEPOSITO,
	0 AS CD_DEPOSITO 
FROM
	BC_LANC 
	INNER JOIN  BC_CONTAS ON 
		BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND
		BC_LANC.CD_CNT = BC_CONTAS.CD_CNT 

	INNER JOIN BC_BANCO ON 
		BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP AND
		BC_CONTAS.CD_BC = BC_BANCO.CD_BC 

	INNER JOIN GLB_DOC ON
		BC_LANC.CD_EMP = GLB_DOC.CD_EMP AND
		BC_LANC.CD_DOC = GLB_DOC.CD_DOC

	INNER 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 OUTER JOIN BC_LANC_TRANSF ON
		BC_LANC_BC_CHQ_LANC.CD_EMP = BC_LANC_TRANSF.CD_EMP AND
		BC_LANC_BC_CHQ_LANC.CD_BC_LANC = BC_LANC_TRANSF.CD_BC_LANC_ORIG
	LEFT OUTER JOIN (
		SELECT
			CD_EMP,
			CD_BC_LANC,
			CD_CNT,
			DT_COMPES
		FROM
			V_BC_CHQ_LANC_COMPENSADO
		WHERE
			DT_COMPES BETWEEN @DT_INI AND @DT_FIM) V_BC_CHQ_LANC_COMPENSADO ON

		V_BC_CHQ_LANC_COMPENSADO.CD_EMP = BC_LANC.CD_EMP AND
		V_BC_CHQ_LANC_COMPENSADO.CD_BC_LANC = BC_LANC.CD_BC_LANC AND
		V_BC_CHQ_LANC_COMPENSADO.CD_CNT = BC_LANC.CD_CNT
	
	LEFT JOIN BC_LANC_CNAB_PEND ON

		BC_LANC_CNAB_PEND.CD_EMP = BC_LANC.CD_EMP AND
		BC_LANC_CNAB_PEND.CD_BC_LANC = BC_LANC.CD_BC_LANC 

WHERE
	BC_LANC.CD_EMP = @CD_EMP
	AND BC_CONTAS.CD_CNT = @CD_CNT
	AND BC_LANC.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM
	AND GLB_DOC.TP_DOC = 6
	AND BC_LANC_TRANSF.CD_BC_LANC_ORIG IS NULL

--Inserindo os demais lan?amentos banc?rios
INSERT INTO #RS_BC_LANC_EXTRATO
SELECT
	BC_LANC.CD_EMP,
	BC_CONTAS.NR_CNT,
	BC_BANCO.DS_BC,
	BC_CONTAS.DS_CNT,
	BC_CONTAS.AGC_CNT,
	BC_LANC.CD_CNT,
	BC_LANC.CD_BC_LANC,
	BC_LANC.CD_DOC,
	BC_LANC.DT_LANCTO,
	BC_LANC.HIST_LANC + '   ' + ISNULL(BC_LANC.HIST_LANC_CPL,'') ,
	CASE WHEN BC_LANC.TP_LANC = 1 THEN VLR_LANC ELSE 0 END AS VLR_DEB,
	CASE WHEN BC_LANC.TP_LANC = 0 THEN VLR_LANC ELSE 0 END AS VLR_CRED,
	0,
	ISNULL(BC_LANC_CONC.CD_BC_LANC,0) AS CD_CHQ_LANC,
	2 AS TP_ORIG_LANC,
	0 AS CD_CHQ_LANC,
	CASE WHEN ISNULL(CD_BC_LANC_CRED,0) > 0 THEN
		1
	ELSE
		0
	END AS FLAG_CONS_FECHA_CAIXA,
	CASE WHEN ISNULL(BC_LANC_CNAB_PEND.CD_BC_LANC,0)= 0  THEN 0 ELSE 1 END,
	ISNULL(BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO.CD_FILIAL,0) FILIAL_DEPOSITO,
	ISNULL( BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO.CD_DEPOSITO,0) CD_DEPOSITO
FROM
	BC_LANC 
	INNER JOIN  BC_CONTAS ON 
		BC_LANC.CD_EMP = BC_CONTAS.CD_EMP AND
		BC_LANC.CD_CNT = BC_CONTAS.CD_CNT

	INNER JOIN GLB_DOC ON
		BC_LANC.CD_EMP = GLB_DOC.CD_EMP AND
		BC_LANC.CD_DOC = GLB_DOC.CD_DOC

	INNER JOIN BC_BANCO ON 
		BC_CONTAS.CD_EMP = BC_BANCO.CD_EMP AND
		BC_CONTAS.CD_BC = BC_BANCO.CD_BC   --  SELECT * FROM BC_LANC_PDV_VD_FECHA_MOV_TES_DEPOSITO

	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 (
		SELECT
			CD_EMP,
			CD_BC_LANC,
			DT_COMPES
		FROM
			BC_LANC_CONC
		WHERE
			DT_COMPES BETWEEN @DT_INI AND @DT_FIM) 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
	
	LEFT JOIN BC_LANC_CNAB_PEND ON

		BC_LANC_CNAB_PEND.CD_EMP = BC_LANC.CD_EMP AND
		BC_LANC_CNAB_PEND.CD_BC_LANC = BC_LANC.CD_BC_LANC 
WHERE
	BC_LANC.CD_EMP = @CD_EMP
	AND BC_CONTAS.CD_CNT = @CD_CNT
	AND BC_LANC.DT_LANCTO BETWEEN @DT_INI AND @DT_FIM
	AND NOT EXISTS (
		SELECT
			CD_BC_LANC
		FROM
			#RS_BC_LANC_EXTRATO RS_BC_LANC_EXTRATO 
		WHERE
			RS_BC_LANC_EXTRATO.CD_EMP = BC_LANC.CD_EMP AND
			RS_BC_LANC_EXTRATO.CD_BC_LANC = BC_LANC.CD_BC_LANC)

--Retirando D?bitos n?o compensados
IF @TP_COMPES = 1
	BEGIN
		DELETE FROM
			#RS_BC_LANC_EXTRATO
		WHERE
			CD_CHQ_LANC_EMP = 0
	END

--Somente n?o compensados
IF @TP_COMPES = 2
	BEGIN
		DELETE FROM
			#RS_BC_LANC_EXTRATO
		WHERE
			CD_CHQ_LANC_EMP > 0
	END
------------------------------------------------------------------------------------------------

----------somente creditos
if @TP_LANC_DEB_CRED = 1
	BEGIN
		DELETE FROM
			#RS_BC_LANC_EXTRATO
		WHERE
			VLR_DEB > 0
	END
--------- somente debitos
if @TP_LANC_DEB_CRED = 2
	BEGIN
		DELETE FROM
			#RS_BC_LANC_EXTRATO
		WHERE
			VLR_CRED > 0
	END

------------------------
SET @VLR_SLD = 0

	INSERT INTO #BC_LANC_EXTRATO 
	SELECT * FROM #RS_BC_LANC_EXTRATO ORDER BY  DT_LANCTO,VLR_CRED,VLR_DEB

	DECLARE CursorExtrato CURSOR FAST_FORWARD FOR 
	SELECT 
		ORDEM,
		VLR_CRED,
		VLR_DEB
	FROM 
		#BC_LANC_EXTRATO 
	ORDER BY 
		DT_LANCTO,CD_BC_LANC
	
	OPEN CursorExtrato
	
	FETCH NEXT FROM CursorExtrato INTO @ORDEM,@VLR_CRED,@VLR_DEB
	WHILE @@FETCH_STATUS=0
	  BEGIN
		UPDATE
			rs
		SET
			@VLR_SLD = @VLR_SLD + (@VLR_CRED - @VLR_DEB),
			rs.VLR_SLD = @VLR_SLD
		FROM
			#BC_LANC_EXTRATO rs
		WHERE
			rs.ORDEM = @ORDEM

		FETCH NEXT FROM CursorExtrato INTO @ORDEM,@VLR_CRED,@VLR_DEB
	  END

	CLOSE CursorExtrato
	DEALLOCATE CursorExtrato


--Gerando Saldo da conta corrente

--Fim

SELECT * FROM #BC_LANC_EXTRATO 
ORDER BY DT_LANCTO,ORDEM


