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


CREATE PROCEDURE [dbo].[P_PG_CRED_DEB_FORN_EXTRATO](@CD_EMP       INT,
                                                    @DT_INI       DATETIME,
                                                    @DT_FIM       DATETIME,
                                                    @CD_FILIAL    VARCHAR(max),
                                                    @CD_FORN_PESQ INT = 0)
AS
  BEGIN
      --Desabilitando a qtde de registros afetados a cada instrução para que a procedure retorne dados para o Visual Basic  
      SET nocount ON

      CREATE TABLE #rs_mov
        (
           ordem      INT IDENTITY(1, 1),
           cd_emp     INT,
           cd_forn    INT,
           rz_forn    VARCHAR(50),
           cd_lanc    INT,
           vlr_cred   MONEY,
           vlr_deb    MONEY,
           vlr_sld    MONEY,
           tipo       INT,
           hist_lanc  VARCHAR(200),
           dt_lancto  DATETIME,
           cd_fil_nf  INT,
           cd_nf      INT,
           registro   VARCHAR(50),
           conciliado INT,
           nr_nf      VARCHAR(30)
        )

      CREATE TABLE #forn_lanc_extrato
        (
           ordem      INT,
           cd_emp     INT,
           cd_forn    INT,
           rz_forn    VARCHAR(50),
           cd_lanc    INT,
           vlr_cred   MONEY,
           vlr_deb    MONEY,
           vlr_sld    MONEY,
           tipo       INT,
           hist_lanc  VARCHAR(200),
           dt_lancto  DATETIME,
           cd_fil_nf  INT,
           cd_nf      INT,
           registro   VARCHAR(50),
           conciliado INT,
           nr_nf      VARCHAR(30)
        )

      DECLARE @VLR_SLD MONEY
      DECLARE @CD_FORN INT
      DECLARE @TT_FORN INT
      DECLARE @N INT
      --tabela temporaria com os fornecedores que serão processados        

      CREATE TABLE #rspdv
        (
           cd_forn INT
        )

      INSERT INTO #rspdv
      SELECT cd_forn
      FROM   pg_forn
      WHERE  cd_forn = CASE
                         WHEN @CD_FORN_PESQ > 0 THEN @CD_FORN_PESQ
                         ELSE cd_forn
                       END

      SELECT *
      INTO   #consulta
      FROM   (SELECT pg_cred_forn.cd_emp,
                     pg_forn.cd_forn,
                     LEFT(pg_forn.rz_forn, 50)                        AS RZ_FORN
                     ,
                     pg_cred_forn.cd_cred_forn
                     AS LANC,
                     pg_cred_forn.vlr_cred                            AS
                     VLR_CRED,
                     0                                                AS VLR_DEB
                     ,
                     0
                     AS VLR_SLD,
                     0                                                AS TIPO,
                     'Credito por dev. da filial.: '
                     + CONVERT( VARCHAR(5), est_nf_sai.cd_filial )
                     + ' nota fiscal  nr.: '
                     + CONVERT( VARCHAR(20), est_nf_sai.nf_nf )
                     + ' emitida em '
                     + CONVERT( CHAR(10), est_nf_sai.dt_emi_nf, 105 ) AS
                     HIST_LANC
                     ,
                     pg_cred_forn.dt_cred
                     AS DT_LANCTO,
                     pg_cred_forn_est_nf_sai.cd_filial                AS
                     CD_FIL_NF
                     ,
                     pg_cred_forn_est_nf_sai.cd_nf,
                     '1 - CREDITO VINCULO COM NOTAS'                  AS
                     REGISTRO,
                     Isnull(pg_cred_forn.conciliado, 0)               AS
                     CONCILIADO,
                     est_nf_sai.nf_nf
              FROM   pg_cred_forn
                     INNER JOIN pg_forn
                             ON pg_cred_forn.cd_emp = pg_forn.cd_emp
                                AND pg_cred_forn.cd_forn = pg_forn.cd_forn
                     LEFT OUTER JOIN pg_cred_forn_est_nf_sai
                                  ON pg_cred_forn.cd_emp =
                                     pg_cred_forn_est_nf_sai.cd_emp
                                     AND pg_cred_forn.cd_cred_forn =
                                         pg_cred_forn_est_nf_sai.cd_cred_forn
                     LEFT OUTER JOIN est_nf_sai
                                  ON pg_cred_forn_est_nf_sai.cd_emp =
                                     est_nf_sai.cd_emp
                                     AND pg_cred_forn_est_nf_sai.cd_filial =
                                         est_nf_sai.cd_filial
                                     AND pg_cred_forn_est_nf_sai.cd_nf =
                                         est_nf_sai.cd_nf
              WHERE  pg_cred_forn.cd_emp = @CD_EMP
                     AND pg_cred_forn_est_nf_sai.cd_filial IN (SELECT id
                                                               FROM
                         Ufd_prm_in_string(@CD_FILIAL))
                     AND pg_cred_forn.vlr_cred > 0
					 AND PG_CRED_FORN.DT_CRED <= @DT_FIM 
				UNION ALL
              SELECT pg_deb_forn.cd_emp,
                     pg_forn.cd_forn,
                     LEFT(pg_forn.rz_forn, 50),
                     pg_deb_forn.cd_deb_forn                            AS LANC,
                     0                                                  AS
                     VLR_CRED,
                     CASE
                       WHEN Isnull(pg_deb_forn_est_nf_sai.vlr_baixado, 0) > 0
                     THEN
                       Isnull(
                       pg_deb_forn_est_nf_sai.vlr_baixado, 0)
                       ELSE pg_deb_forn.vlr_deb
                     END                                                AS
                     VLR_DEB
                     ,
                     0
                     AS VLR_SLD,
                     1                                                  AS TIPO,
                     'Debito por abatimento da duplicata.: '
                     + CONVERT( VARCHAR(5), Isnull(
                     pg_deb_forn_pg_cred.cd_filial,
                     ''
                     ) )
                     + '-'
                     + CONVERT( VARCHAR(20), Isnull(pg_cred.ano, '') )
                     + '-'
                     + CONVERT( VARCHAR(20), Isnull(pg_cred.nr_dp, '') )
                     + '-'
                     + CONVERT( VARCHAR(20), Isnull(pg_cred.parc, '') ) AS
                     HIST_LANC,
                     pg_deb_forn.dt_deb                                 AS
                     DT_LANCTO,
                     pg_cred.cd_filial                                  AS
                     CD_FIL_NF,
                     pg_cred.cd_pg_cred                                 AS CD_NF
                     ,
                     '2 - DEBITO VINCULO COM DUPLICATA'
                     AS
                     REGISTRO,
                     Isnull(pg_deb_forn.conciliado, 0),
                     Isnull(est_nf_sai.nf_nf, '')                       AS NF_NF
              FROM   pg_deb_forn
                     INNER JOIN pg_forn
                             ON pg_deb_forn.cd_emp = pg_forn.cd_emp
                                AND pg_deb_forn.cd_forn = pg_forn.cd_forn
                     INNER JOIN pg_deb_forn_pg_cred
                             ON pg_deb_forn.cd_emp = pg_deb_forn_pg_cred.cd_emp
                                AND pg_deb_forn.cd_deb_forn =
                                    pg_deb_forn_pg_cred.cd_deb_forn
                     INNER JOIN pg_cred
                             ON pg_cred.cd_emp = pg_deb_forn_pg_cred.cd_emp
                                AND pg_cred.cd_filial =
                                    pg_deb_forn_pg_cred.cd_filial
                                AND pg_cred.cd_pg_cred =
                                    pg_deb_forn_pg_cred.cd_pg_cred
                     LEFT OUTER JOIN pg_deb_forn_est_nf_sai
                                  ON pg_deb_forn.cd_emp =
                                     pg_deb_forn_est_nf_sai.cd_emp
                                     AND pg_deb_forn.cd_deb_forn =
                                         pg_deb_forn_est_nf_sai.cd_deb_forn
                     LEFT OUTER JOIN est_nf_sai
                                  ON pg_deb_forn_est_nf_sai.cd_emp =
                                     est_nf_sai.cd_emp
                                     AND pg_deb_forn_est_nf_sai.cd_filial =
                                         est_nf_sai.cd_filial
                                     AND pg_deb_forn_est_nf_sai.cd_nf =
                                         est_nf_sai.cd_nf
              WHERE  pg_deb_forn.cd_emp = @CD_EMP 
                     AND pg_deb_forn_pg_cred.cd_filial IN (SELECT id
                                                           FROM
                         Ufd_prm_in_string(@CD_FILIAL))
						AND PG_DEB_FORN.DT_DEB <= @DT_FIM 
				UNION ALL
              SELECT pg_cred_forn.cd_emp,
                     pg_forn.cd_forn,
                     LEFT(pg_forn.rz_forn, 50),
                     pg_cred_forn.cd_cred_forn           AS LANC,
                     pg_cred_forn.vlr_cred               AS VLR_CRED,
                     0                                   AS VLR_DEB,
                     0                                   AS VLR_SLD,
                     0                                   AS TIPO,
                     CASE
                       WHEN ds_hist IS NULL THEN 'lancamento manual'
                       ELSE ds_hist
                     END                                 AS HIST_LANC,
                     pg_cred_forn.dt_cred                AS DT_LANCTO,
                     0                                   AS CD_FIL_NF,
                     0                                   AS CD_NF,
                     '3 - CREDITO SEM VINCULO COM NOTAS' AS REGISTRO,
                     Isnull(pg_cred_forn.conciliado, 0),
                     ''                                  AS NF_NF
              FROM   pg_cred_forn
                     INNER JOIN pg_forn
                             ON pg_cred_forn.cd_emp = pg_forn.cd_emp
                                AND pg_cred_forn.cd_forn = pg_forn.cd_forn
              WHERE  pg_cred_forn.cd_emp = @CD_EMP
                     AND NOT EXISTS (SELECT cd_emp
                                     FROM   pg_cred_forn_est_nf_sai
                                     WHERE  pg_cred_forn.cd_emp =
                                            pg_cred_forn_est_nf_sai.cd_emp
                                            AND pg_cred_forn.cd_cred_forn = pg_cred_forn_est_nf_sai.cd_cred_forn)
					AND pg_cred_forn.vlr_cred > 0
			AND PG_CRED_FORN.DT_CRED <= @DT_FIM 

				UNION ALL
			  SELECT pg_deb_forn.cd_emp,
				   pg_forn.cd_forn,
				   LEFT(pg_forn.rz_forn, 50),
				   pg_deb_forn.cd_deb_forn            AS LANC,
				   0                                  AS VLR_CRED,
				   CASE
					 WHEN Isnull(pg_deb_forn_est_nf_sai.vlr_baixado, 0) > 0 THEN Isnull(
					 pg_deb_forn_est_nf_sai.vlr_baixado, 0)
					 ELSE pg_deb_forn.vlr_deb
				   END                                AS VLR_DEB,
				   0                                  AS VLR_SLD,
				   1                                  AS TIPO,
				   CASE
					 WHEN ds_hist IS NULL THEN 'lancamento manual'
					 ELSE ds_hist
				   END                                AS HIST_LANC,
				   pg_deb_forn.dt_deb                 AS DT_LANCTO,
				   0                                  AS CD_FIL_NF,
				   0                                  AS CD_NF,
				   '4 - DEBITO SEM VINCULO COM NOTAS' AS REGISTRO,
				   Isnull(pg_deb_forn.conciliado, 0),
				   Isnull(est_nf_sai.nf_nf, '')       AS NF_NF
			FROM   pg_deb_forn
				   INNER JOIN pg_forn
						   ON pg_deb_forn.cd_emp = pg_forn.cd_emp
							  AND pg_deb_forn.cd_forn = pg_forn.cd_forn
				   LEFT OUTER JOIN pg_deb_forn_est_nf_sai
								ON pg_deb_forn.cd_emp = pg_deb_forn_est_nf_sai.cd_emp
								   AND pg_deb_forn.cd_deb_forn =
									   pg_deb_forn_est_nf_sai.cd_deb_forn
				   LEFT OUTER JOIN est_nf_sai
								ON pg_deb_forn_est_nf_sai.cd_emp = est_nf_sai.cd_emp
								   AND pg_deb_forn_est_nf_sai.cd_filial =
									   est_nf_sai.cd_filial
								   AND pg_deb_forn_est_nf_sai.cd_nf = est_nf_sai.cd_nf
			WHERE  pg_deb_forn.cd_emp = @CD_EMP
				   AND PG_DEB_FORN.DT_DEB <= @DT_FIM 
				   AND NOT EXISTS (SELECT cd_emp
								   FROM   pg_deb_forn_est_nf_ent
								   WHERE  pg_deb_forn.cd_emp = pg_deb_forn_est_nf_ent.cd_emp
										  AND pg_deb_forn.cd_deb_forn =
											  pg_deb_forn_est_nf_ent.cd_deb_forn)
				   AND NOT EXISTS (SELECT cd_emp
								   FROM   pg_deb_forn_pg_cred
								   WHERE  pg_deb_forn.cd_emp = pg_deb_forn_pg_cred.cd_emp
										  AND pg_deb_forn.cd_deb_forn =
											  pg_deb_forn_pg_cred.cd_deb_forn
									  )
				UNION ALL
			SELECT pg_deb_forn.cd_emp,
				   pg_forn.cd_forn,
				   LEFT(pg_forn.rz_forn, 50),
				   pg_deb_forn.cd_deb_forn                         AS LANC,
				   0                                               AS VLR_CRED,
				   pg_deb_forn.vlr_deb,
				   0                                               AS VLR_SLD,
				   1                                               AS TIPO,
				   CASE
					 WHEN ds_hist IS NULL THEN 'lancamento manual'
					 ELSE ds_hist
				   END                                             AS HIST_LANC,
				   pg_deb_forn.dt_deb                              AS DT_LANCTO,
				   0                                               AS CD_FIL_NF,
				   0                                               AS CD_NF,
				   '4 - DEBITO POR ABATIMENTO NOTA FISCAL ENTRADA' AS REGISTRO,
				   Isnull(pg_deb_forn.conciliado, 0),
				   Isnull(est_nf_sai.nf_nf, '')                    AS NF_NF
			FROM   pg_deb_forn
				   INNER JOIN pg_forn
						   ON pg_deb_forn.cd_emp = pg_forn.cd_emp
							  AND pg_deb_forn.cd_forn = pg_forn.cd_forn
				   LEFT OUTER JOIN pg_deb_forn_est_nf_sai
								ON pg_deb_forn.cd_emp = pg_deb_forn_est_nf_sai.cd_emp
								   AND pg_deb_forn.cd_deb_forn =
									   pg_deb_forn_est_nf_sai.cd_deb_forn
				   LEFT OUTER JOIN est_nf_sai
								ON pg_deb_forn_est_nf_sai.cd_emp = est_nf_sai.cd_emp
								   AND pg_deb_forn_est_nf_sai.cd_filial =
									   est_nf_sai.cd_filial
								   AND pg_deb_forn_est_nf_sai.cd_nf = est_nf_sai.cd_nf
			WHERE  pg_deb_forn.cd_emp = @CD_EMP  
				   AND PG_DEB_FORN.DT_DEB <= @DT_FIM 
				   AND EXISTS (SELECT cd_emp
							   FROM   pg_deb_forn_est_nf_ent
							   WHERE  pg_deb_forn.cd_emp = pg_deb_forn_est_nf_ent.cd_emp
									  AND pg_deb_forn.cd_deb_forn =
										  pg_deb_forn_est_nf_ent.cd_deb_forn)
				   AND EXISTS (SELECT cd_emp
							   FROM   pg_deb_forn_pg_cred_forn
							   WHERE  pg_deb_forn.cd_emp = pg_deb_forn_pg_cred_forn.cd_emp
									  AND pg_deb_forn.cd_deb_forn =
										  pg_deb_forn_pg_cred_forn.cd_deb_forn)
				   AND NOT EXISTS (SELECT cd_emp
								   FROM   pg_deb_forn_pg_cred
								   WHERE  pg_deb_forn.cd_emp = pg_deb_forn_pg_cred.cd_emp
										  AND pg_deb_forn.cd_deb_forn =
											  pg_deb_forn_pg_cred.cd_deb_forn
									  ) ) Q
			  ORDER  BY dt_lancto,
			  tipo

      DECLARE fornecedores CURSOR fast_forward FOR
        SELECT cd_forn
        FROM   #rspdv

      OPEN fornecedores

      FETCH next FROM fornecedores INTO @CD_FORN

      WHILE @@FETCH_STATUS = 0
        BEGIN ----INICIO PROCESSO GERAL
            SET @N = 1

            INSERT INTO #rs_mov
            SELECT cd_emp,
                   cd_forn,
                   rz_forn,
                   0                                         AS LANC,
                   CASE
                     WHEN ( Sum(vlr_cred) - Sum(vlr_deb) ) > 0 THEN
                     Sum(vlr_cred) - Sum(vlr_deb)
                     ELSE 0
                   END                                       AS VLR_CRED,
                   CASE
                     WHEN ( Sum(vlr_cred) - Sum(vlr_deb) ) < 0 THEN
                     ( Sum(vlr_cred) - Sum(vlr_deb) ) * -1
                     ELSE 0
                   END                                       AS VLR_DEB,
                   0                                         AS VLR_SLD,
                   CASE
                     WHEN ( Sum(vlr_cred) - Sum(vlr_deb) ) > 0 THEN 0
                     ELSE 1
                   END                                       AS TIPO,
                   'SALDO ANTERIOR À DATA INICIAL DIGITADA' AS HIST_LANC,
                   @DT_INI - 1                               AS DT_LANCTO,
                   0                                         AS CD_FIL_NF,
                   0                                         CD_NF,
                   'SALDO INICIAL'                           AS REGISTRO,
                   1                                         CONCILIADO,
                   ''                                        AS NR_NF
            FROM   #consulta
            WHERE  cd_forn = @CD_FORN
                   AND dt_lancto < @DT_INI --Saldo Anterior
            GROUP  BY cd_emp,
                      cd_forn,
                      rz_forn
            UNION
            SELECT *
            FROM   #consulta
            WHERE  cd_forn = @CD_FORN
                   AND dt_lancto BETWEEN @DT_INI AND @DT_FIM --Periodo atual
            ORDER  BY dt_lancto,
                      tipo
            ------------------------------------------------------------------------------------

            SET @TT_FORN = (SELECT Count(*)
                            FROM   #rs_mov)
            SET @VLR_SLD = 0

            WHILE @N <= @TT_FORN
              BEGIN
                  UPDATE #rs_mov
                  SET    @VLR_SLD = ( CASE
                                        WHEN registro <> 'SALDO INICIAL' THEN
                                          CASE
                                            WHEN tipo = 0 THEN
                                            @VLR_SLD + vlr_cred
                                            WHEN tipo = 1 THEN
                                            @VLR_SLD - vlr_deb
                                          END
                                        ELSE
                                          CASE
                                            WHEN tipo = 0 THEN vlr_cred
                                            WHEN tipo = 1 THEN
                                            @VLR_SLD - vlr_deb
                                          END
                                      END ),
                         vlr_sld = @VLR_SLD
                  WHERE  cd_forn = @CD_FORN
                         AND ordem = @N

                  SET @N = @N + 1
              END

            INSERT INTO #forn_lanc_extrato
                        (ordem,
                         cd_emp,
                         cd_forn,
                         rz_forn,
                         cd_lanc,
                         vlr_cred,
                         vlr_deb,
                         vlr_sld,
                         tipo,
                         hist_lanc,
                         dt_lancto,
                         cd_fil_nf,
                         cd_nf,
                         registro,
                         conciliado,
                         nr_nf)
            SELECT ordem,
                   cd_emp,
                   cd_forn,
                   rz_forn,
                   cd_lanc,
                   vlr_cred,
                   vlr_deb,
                   vlr_sld,
                   tipo,
                   hist_lanc,
                   dt_lancto,
                   cd_fil_nf,
                   cd_nf,
                   registro,
                   conciliado,
                   nr_nf
            FROM   #rs_mov
            WHERE  cd_forn = @CD_FORN
            ORDER  BY ordem

            TRUNCATE TABLE #rs_mov;

            ----FIM PROCESSO GERAL
            FETCH next FROM fornecedores INTO @CD_FORN
        END --Fechando cursos

      CLOSE fornecedores;

      --Desalocando cursos da memoria
      DEALLOCATE fornecedores;

      --Apagando a tabela temporaria
      DROP TABLE #rspdv;

      SELECT ordem,
             cd_emp,
             cd_forn,
             rz_forn,
             cd_lanc,
             vlr_cred,
             CASE
               WHEN @CD_FORN_PESQ > 0 THEN vlr_deb
               ELSE vlr_deb * -1
             END              AS VLR_DEB,
             vlr_sld,
             tipo,
             Upper(hist_lanc) AS HIST_LANC,
             dt_lancto,
             cd_fil_nf,
             cd_nf,
             registro,
             nr_nf
      FROM   #forn_lanc_extrato
      ORDER  BY dt_lancto

      DROP TABLE #forn_lanc_extrato;

      DROP TABLE #rs_mov;
  END 