IF EXISTS(SELECT NAME
            FROM SYSOBJECTS
           WHERE NAME = 'SPED_CONTABIL_SALDO')
   DROP PROCEDURE SPED_CONTABIL_SALDO
GO

CREATE PROCEDURE SPED_CONTABIL_SALDO (
   @P_NRSEQARQMAG  CHAR(10),
   @P_CDEMPRESA    CHAR(2),
   @P_DTREFERENCIA DATETIME,
   @P_NRINIORDENA  INTEGER,
   @P_IDSPED       VARCHAR(01), /* PARAMETRO QUE DEFINE SE SERA GERADO SPED OU FCONT */
   @P_NRORDREGARQ  INTEGER OUTPUT)
AS
   DECLARE @V_NRORDREGARQ            INTEGER;
   DECLARE @V_AUXILIAR               VARCHAR(200);
   DECLARE @V_DTULTDIAREFE           DATETIME;
   DECLARE @V_STULTDIAREFE           VARCHAR(10);
   DECLARE @V_DATA                   VARCHAR(10);
   DECLARE @V_ULTIMODIA              DATETIME;
   DECLARE @V_LANCPERIODO            VARCHAR(01);

   DECLARE @V_VRINICIAL              DOUBLE PRECISION;
   DECLARE @V_VRDEBITO               DOUBLE PRECISION;
   DECLARE @V_VRCREDITO              DOUBLE PRECISION;
   DECLARE @V_VRFINAL                DOUBLE PRECISION;
   DECLARE @V_I155                   VARCHAR(200);

   /* VARIAVEIS DO CURSOR C_I155 */
   DECLARE @C_I155_AUX_CDCONTCTBL     VARCHAR(20);
   DECLARE @C_I155_AUX_CDCONTPLANREFE VARCHAR(20);
   DECLARE @C_I155_DSLINHA            VARCHAR(1000);
   DECLARE @C_I155_CREDITO            DOUBLE PRECISION;
   DECLARE @C_I155_DEBITO             DOUBLE PRECISION;

   /* VARIAVEIS DO CURSOR C_CONTA */
   DECLARE @C_CONTA_CDCONTCTBL        VARCHAR(20);

BEGIN
   IF (@P_IDSPED = 'S') BEGIN
     SET @V_NRORDREGARQ = @P_NRINIORDENA;
     DECLARE C_DATA CURSOR FOR
         SELECT DISTINCT SUBSTRING(CONVERT(VARCHAR, DTDATA, 103),4, 8) DTDATA
           FROM DATA
          WHERE SUBSTRING(CONVERT(VARCHAR, DTMES, 103), 7, 4) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4)
          ORDER BY 1;
        OPEN C_DATA
        FETCH NEXT FROM C_DATA INTO @V_DATA;
        WHILE @@FETCH_STATUS = 0 BEGIN
           SET @V_LANCPERIODO = 'S';

           DECLARE C_CONTA CURSOR FOR
              SELECT CDCONTCTBL
                FROM CONTCTBL
               WHERE IDCONTCCUS <> 'R'
                 AND IDTIPOCONT = 'A'
               ORDER BY CDCONTCTBL;
              OPEN C_CONTA;
              FETCH NEXT FROM C_CONTA INTO @C_CONTA_CDCONTCTBL;
              WHILE @@FETCH_STATUS = 0 BEGIN
                 SET @V_STULTDIAREFE = (SELECT CONVERT(VARCHAR, MAX(DTDATA), 103)
                                           FROM DATA
                                          WHERE DTMES = '01/' + @V_DATA);

                 SELECT @V_VRINICIAL = S.VRSALDEMPR
                   FROM SALDEMPR S
                  WHERE S.CDEMPRESA  = @P_CDEMPRESA
                    AND S.DTSALDEMPR = DATEADD(d, -1, CONVERT(DATETIME, '01/' + @V_DATA))
                    AND S.CDCONTCTBL = @C_CONTA_CDCONTCTBL;

                 SELECT @V_VRDEBITO = SUM(ROUND(VRLANCAMENT,2))
                   FROM LANCCONT
                  WHERE CDEMPRESA  = @P_CDEMPRESA
                    AND CDCONTCTBL = @C_CONTA_CDCONTCTBL
                    AND DTLANCCONT BETWEEN CONVERT(DATETIME, '01/' + @V_DATA) AND CONVERT(DATETIME, @V_STULTDIAREFE)
                    AND VRLANCAMENT < 0;

                 SELECT @V_VRCREDITO = SUM(ROUND(VRLANCAMENT,2))
                   FROM LANCCONT
                  WHERE CDEMPRESA  = @P_CDEMPRESA
                    AND CDCONTCTBL = @C_CONTA_CDCONTCTBL
                    AND DTLANCCONT BETWEEN CONVERT(DATETIME, '01/' + @V_DATA) AND CONVERT(DATETIME, @V_STULTDIAREFE)
                    AND VRLANCAMENT > 0;

                 SELECT @V_VRFINAL = S.VRSALDEMPR
                   FROM SALDEMPR S
                  WHERE S.CDEMPRESA  = @P_CDEMPRESA
                    AND S.CDCONTCTBL = @C_CONTA_CDCONTCTBL
                    AND S.DTSALDEMPR = CONVERT(DATETIME, @V_STULTDIAREFE);


                 IF (ABS(ISNULL(@V_VRINICIAL, 0)) + ABS(ISNULL(@V_VRDEBITO, 0)) + ABS(ISNULL(@V_VRCREDITO, 0))) <> 0 BEGIN
                    IF @V_LANCPERIODO = 'S' BEGIN
                       SET @V_STULTDIAREFE = SUBSTRING(@V_STULTDIAREFE, 1, 2) + SUBSTRING(@V_STULTDIAREFE, 4, 2) + SUBSTRING(@V_STULTDIAREFE, 7, 4);
                       SET @V_AUXILIAR    = '|I150|' + '01' + SUBSTRING(@V_DATA, 1, 2) + SUBSTRING(@V_DATA, 4, 4) + '|' + @V_STULTDIAREFE + '|';
                       SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;

                       INSERT INTO ARQMAGNETICO
                          (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                       VALUES
                          (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

                       SET @V_LANCPERIODO = 'N';
                     END;
                     SELECT @V_I155 = '|I155|' +
                                       @C_CONTA_CDCONTCTBL +
                                       '||'    +
                                       REPLACE(LTRIM(CONVERT(VARCHAR, ABS(CAST(ISNULL(@V_VRINICIAL, 0) AS MONEY)))            + '|' + (CASE @V_VRINICIAL WHEN ABS(@V_VRINICIAL) THEN 'C' ELSE  'D' END)),'.',',')  + '|' +
                                       REPLACE(LTRIM(CONVERT(VARCHAR, ABS(CAST(ISNULL(@V_VRDEBITO,  0) AS MONEY)))),'.',',')  + '|' +
                                       REPLACE(LTRIM(CONVERT(VARCHAR, ABS(CAST(ISNULL(@V_VRCREDITO, 0) AS MONEY)))),'.',',')  + '|' +
                                       REPLACE(LTRIM(CONVERT(VARCHAR, ABS(CAST(ISNULL(@V_VRFINAL,   0) AS MONEY)))            + '|' + (CASE @V_VRFINAL WHEN ABS(@V_VRFINAL) THEN 'C' ELSE  'D' END)),'.',','  )  + '|';

                     SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
                     INSERT INTO ARQMAGNETICO
                        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                     VALUES
                        (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_I155, @P_NRSEQARQMAG);

                 END;
                 FETCH NEXT FROM C_CONTA INTO @C_CONTA_CDCONTCTBL;
              END;
              CLOSE C_CONTA;
              DEALLOCATE C_CONTA;
           FETCH NEXT FROM C_DATA INTO @V_DATA;
        END;
        CLOSE C_DATA;
        DEALLOCATE C_DATA;
     SET @P_NRORDREGARQ = @V_NRORDREGARQ
   END
   ELSE IF (@P_IDSPED = 'F') BEGIN
      SET @V_NRORDREGARQ = @P_NRINIORDENA;
      SET @V_STULTDIAREFE = CONVERT(VARCHAR, @P_DTREFERENCIA, 103)
      SET @V_ULTIMODIA = @V_STULTDIAREFE;
      SET @V_STULTDIAREFE = SUBSTRING(@V_STULTDIAREFE, 1, 2) + SUBSTRING(@V_STULTDIAREFE, 4, 2) + SUBSTRING(@V_STULTDIAREFE, 7, 4);

      SET @V_AUXILIAR = '|I150|' + '0101' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04) + '|' + @V_STULTDIAREFE + '|';

      SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);

      DECLARE C_I155_AUX CURSOR FOR
         SELECT CDCONTCTBL, CDCONTPLANREFE
           FROM CONTCTBL
          WHERE CDCONTPLANREFE IS NOT NULL
            AND IDNATUCONT IN ('01', '02', '03')
            AND IDCONTCCUS <> 'R'
            AND IDTIPOCONT = 'A'
          ORDER BY 1, 2;
         OPEN C_I155_AUX
         FETCH NEXT FROM C_I155_AUX INTO @C_I155_AUX_CDCONTCTBL, @C_I155_AUX_CDCONTPLANREFE;
         WHILE @@FETCH_STATUS = 0 BEGIN
            DECLARE C_I155 CURSOR FOR
               SELECT '|I155|' +
                      SALDFIN.CDCONTCTBL +
                      '||' +
                      REPLACE(LTRIM(CONVERT(VARCHAR, ABS(ISNULL(SALDINI.VRSALDINI,0))) + '|' + (CASE SALDINI.VRSALDINI WHEN ABS(SALDINI.VRSALDINI) THEN 'C' ELSE  'D' END)),'.',',')  + '|' +
                      REPLACE(LTRIM(CONVERT(VARCHAR, ABS(ISNULL(DEBITO.DEBITO,0)))),'.',',')  + '|' +
                      REPLACE(LTRIM(CONVERT(VARCHAR, ISNULL(CREDITO.CREDITO,0))),'.',',')  + '|' +
                      REPLACE(LTRIM(CONVERT(VARCHAR, ABS(ISNULL(SALDFIN.VRSALDFIN,0))) + '|' + (CASE SALDFIN.VRSALDFIN WHEN ABS(SALDFIN.VRSALDFIN) THEN 'C' ELSE 'D' END)),'.',',')  + '|' AS DSLINHA,
                      CREDITO, DEBITO
                 FROM (SELECT S.CDCONTCTBL, S.VRSALDEMPR AS VRSALDFIN
                         FROM SALDEMPR S
                        WHERE S.CDEMPRESA  = @P_CDEMPRESA
                          AND S.DTSALDEMPR = @P_DTREFERENCIA
                          AND S.CDCONTCTBL = @C_I155_AUX_CDCONTCTBL) SALDFIN LEFT JOIN (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                                                                                         FROM LANCCONT L
                                                                                        WHERE L.VRLANCAMENT < 0
                                                                                          AND L.CDEMPRESA = @P_CDEMPRESA
                                                                                          AND L.DTLANCCONT BETWEEN '01/01/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) AND @V_ULTIMODIA
                                                                                          AND L.CDCONTCTBL = @C_I155_AUX_CDCONTCTBL
                                                                                        GROUP BY L.CDCONTCTBL) DEBITO
                                                                                   ON SALDFIN.CDCONTCTBL = DEBITO.CDCONTCTBL

                                                                            LEFT JOIN (SELECT L.CDCONTCTBL, SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                                                                                         FROM LANCCONT L
                                                                                        WHERE L.VRLANCAMENT > 0
                                                                                          AND L.CDEMPRESA = @P_CDEMPRESA
                                                                                          AND L.DTLANCCONT BETWEEN '01/01/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) AND @V_ULTIMODIA
                                                                                          AND L.CDCONTCTBL = @C_I155_AUX_CDCONTCTBL
                                                                                        GROUP BY L.CDCONTCTBL) CREDITO
                                                                                   ON SALDFIN.CDCONTCTBL = CREDITO.CDCONTCTBL

                                                                            LEFT JOIN (SELECT S.CDCONTCTBL, ISNULL(ST.VRSALDOTRANSF, S.VRSALDEMPR) AS VRSALDINI
                                                                                         FROM SALDEMPR S LEFT JOIN SALDOTRANSF ST
                                                                                                                ON S.DTSALDEMPR = ST.DTSALDOTRANSF
                                                                                                               AND S.CDEMPRESA  = ST.CDEMPRESA
                                                                                                               AND S.CDCONTCTBL = ST.CDCONTCTBL
                                                                                        WHERE S.CDEMPRESA  = @P_CDEMPRESA
                                                                                          AND S.DTSALDEMPR = DATEADD(d, -1, CONVERT(DATE, '01/01/' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4)))
                                                                                          AND S.CDCONTCTBL = @C_I155_AUX_CDCONTCTBL
                                                                                          AND S.VRSALDEMPR <> 0) SALDINI
                                                                                   ON SALDFIN.CDCONTCTBL = SALDINI.CDCONTCTBL

                WHERE (ABS(ISNULL(SALDINI.VRSALDINI,0)) + ABS(ISNULL(DEBITO.DEBITO,0)) + ABS(ISNULL(CREDITO.CREDITO,0))) <> 0
                ORDER BY SALDFIN.CDCONTCTBL;
            OPEN C_I155;
            FETCH NEXT FROM C_I155 INTO @C_I155_DSLINHA, @C_I155_CREDITO, @C_I155_DEBITO;
            WHILE (@@FETCH_STATUS = 0) BEGIN
               SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;
               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @C_I155_DSLINHA, @P_NRSEQARQMAG);

               SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;

               SET @V_AUXILIAR = '|I156|' + @C_I155_AUX_CDCONTPLANREFE + '|' +
                                 REPLACE(LTRIM(CONVERT(VARCHAR, (ABS(ISNULL(@C_I155_DEBITO, 0))))),'.',',')  + '|' +
                                 REPLACE(LTRIM(CONVERT(VARCHAR, (ABS(ISNULL(@C_I155_CREDITO,0))))),'.',',')  + '|';
                 INSERT INTO ARQMAGNETICO
                    (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                 VALUES
                    (RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, @V_NRORDREGARQ), 10), @V_AUXILIAR, @P_NRSEQARQMAG);
               FETCH NEXT FROM C_I155 INTO @C_I155_DSLINHA, @C_I155_CREDITO, @C_I155_DEBITO;
            END;
            CLOSE C_I155;
            DEALLOCATE C_I155;
            FETCH NEXT FROM C_I155_AUX INTO @C_I155_AUX_CDCONTCTBL, @C_I155_AUX_CDCONTPLANREFE;
         END;
         CLOSE C_I155_AUX;
         DEALLOCATE C_I155_AUX;
      SET @P_NRORDREGARQ = @V_NRORDREGARQ;
   END;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'SPED_CONTABIL_SALDO','5.45.000';
GO