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

CREATE PROCEDURE SPED_CONTABIL_BALANCETE (
   @P_NRSEQARQMAG  VARCHAR(10),
   @P_CDEMPRESA    VARCHAR(02),
   @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);

   /* VARIAVEIS DO CURSOR C_I300 */
   DECLARE @C_I300_DSLINHA VARCHAR(1000);

   DECLARE @V_ULTIMODIA   VARCHAR(10);

   /* VARIAVEIS DO CURSOR C_I355 */
   DECLARE @C_I355_DSLINHA VARCHAR(1000);
BEGIN
   IF (@P_IDSPED = 'S') BEGIN
      SET @V_NRORDREGARQ = @P_NRINIORDENA;
      DECLARE C_I300 CURSOR FOR
         SELECT DSLINHA
           FROM (SELECT DISTINCT DTLANCCONT,
                        '0000000000' AS CDCONTCTBL,
                        '|I300|' +
                        SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103) , 01, 02) +
                        SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103) , 04, 02) +
                        SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103) , 07, 04) +
                        '|' AS DSLINHA
                   FROM LANCCONT L, CONTCTBL C
                  WHERE L.CDEMPRESA = @P_CDEMPRESA
                    AND SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 07, 04) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04)
                    AND L.CDCONTCTBL = C.CDCONTCTBL
                    AND C.IDCONTCCUS <> 'R'
                 UNION ALL
                 SELECT LANC.DTLANCCONT,
                        LANC.CDCONTCTBL,
                        '|I310|' +
                        LANC.CDCONTCTBL +
                        '||' +
                        REPLACE(LTRIM(CONVERT(VARCHAR, ABS(ISNULL(DEBITO.DEBITO,0)))),'.',',')  + '|' +
                        REPLACE(LTRIM(CONVERT(VARCHAR, ISNULL(CREDITO.CREDITO,0))),'.',',')  + '|' AS DSLINHA
                   FROM 
                        
                        (SELECT DISTINCT
                                L.DTLANCCONT,
                                L.CDCONTCTBL
                           FROM LANCCONT L, CONTCTBL C
                          WHERE L.CDEMPRESA = @P_CDEMPRESA
                            AND SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 07, 04) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04)
                            AND L.CDCONTCTBL = C.CDCONTCTBL
                            AND C.IDCONTCCUS <> 'R') LANC LEFT JOIN (SELECT L.DTLANCCONT,
                                                                      L.CDCONTCTBL,
                                                                      SUM(ROUND(L.VRLANCAMENT,2)) AS DEBITO
                                                                     FROM LANCCONT L, CONTCTBL C
                                                                      WHERE L.VRLANCAMENT < 0
                                                                      AND L.CDEMPRESA = @P_CDEMPRESA
                                                                      AND SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 07, 04) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04)
                                                                      AND L.CDCONTCTBL = C.CDCONTCTBL
                                                                      AND C.IDCONTCCUS <> 'R'
                                                                     GROUP BY L.DTLANCCONT, L.CDCONTCTBL) DEBITO
                                                                     ON LANC.CDCONTCTBL = DEBITO.CDCONTCTBL
                                                                   AND LANC.DTLANCCONT = DEBITO.DTLANCCONT
                                                          
                                                           LEFT JOIN (SELECT L.DTLANCCONT,
                                                                        L.CDCONTCTBL,
                                                                        SUM(ROUND(L.VRLANCAMENT,2)) AS CREDITO
                                                                      FROM LANCCONT L, CONTCTBL C
                                                                      WHERE L.VRLANCAMENT > 0
                                                                        AND L.CDEMPRESA = @P_CDEMPRESA
                                                                        AND SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, L.DTLANCCONT, 103), 07, 04) = SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 04, 02) + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 07, 04)
                                                                        AND L.CDCONTCTBL = C.CDCONTCTBL
                                                                        AND C.IDCONTCCUS <> 'R'
                                                                       GROUP BY L.DTLANCCONT, L.CDCONTCTBL) CREDITO
                                                                       ON LANC.CDCONTCTBL = CREDITO.CDCONTCTBL
                                                                        AND LANC.DTLANCCONT = CREDITO.DTLANCCONT) L

                                                          
         ORDER BY DTLANCCONT, CDCONTCTBL;
      OPEN C_I300;
      FETCH NEXT FROM C_I300 INTO @C_I300_DSLINHA;

      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_I300_DSLINHA, @P_NRSEQARQMAG);

           FETCH NEXT FROM C_I300 INTO @C_I300_DSLINHA;
      END;
      CLOSE C_I300;
      DEALLOCATE C_I300;

      SET @V_NRORDREGARQ = @V_NRORDREGARQ + 1;

      SET @V_AUXILIAR    = '|I350|' + '3112' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) + '|';

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

      DECLARE C_I355 CURSOR FOR
         SELECT '|I355|' + T.CDCONTCTBL + '|' + '' + '|' +
                 REPLACE(LTRIM(CONVERT(VARCHAR, CAST(ROUND(ISNULL(ABS(T.VRSALDOTRANSF), 0), 2) AS MONEY))), '.', ',') + '|' +
                 CASE T.VRSALDOTRANSF WHEN ABS(VRSALDOTRANSF) THEN 'C' ELSE 'D' END + '|' AS DSLINHA
           FROM SALDOTRANSF T, CONTCTBL C
          WHERE T.CDEMPRESA   = @P_CDEMPRESA
            AND DTSALDOTRANSF = @P_DTREFERENCIA
            AND T.CDCONTCTBL  = C.CDCONTCTBL
            AND C.IDTIPOCONT  = 'A'
            AND C.IDNATUCONT  = '04'
          ORDER BY T.CDCONTCTBL ASC, DTSALDOTRANSF;

          OPEN C_I355;
          FETCH NEXT FROM C_I355 INTO @C_I355_DSLINHA;
          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_I355_DSLINHA, @P_NRSEQARQMAG);
             FETCH NEXT FROM C_I355 INTO @C_I355_DSLINHA;
	  END;
          CLOSE C_I355;
          DEALLOCATE C_I355;
      SET @P_NRORDREGARQ = @V_NRORDREGARQ;
   END
   ELSE IF (@P_IDSPED = 'F') BEGIN
      SET @V_NRORDREGARQ = @P_NRINIORDENA;
      SET @V_AUXILIAR    = '|I350|' + SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 1, 2) +
                                      SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 4, 2) +
                                      SUBSTRING(CONVERT(VARCHAR, @P_DTREFERENCIA, 103), 7, 4) + '|';
      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_I355 CURSOR FOR
          SELECT '|I355|'  +  S.CDCONTCTBL  +  '|'  +  ''  +  '|'  +
                  REPLACE(LTRIM(CAST(CONVERT(VARCHAR, ABS(ISNULL(ISNULL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0))) AS MONEY)), '.', ',') +  '|'  +
                  CASE ISNULL(ISNULL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0) WHEN ABS(ISNULL(ISNULL(ST.VRSALDOTRANSF, S.VRSALDEMPR), 0)) THEN 'C' ELSE 'D' END  +  '|' AS DSLINHA
            FROM SALDEMPR S LEFT JOIN SALDOTRANSF ST
                                   ON S.DTSALDEMPR = ST.DTSALDOTRANSF
                                  AND S.CDEMPRESA  = ST.CDEMPRESA
                                  AND S.CDCONTCTBL = ST.CDCONTCTBL,
                 CONTCTBL C
           WHERE S.CDEMPRESA   = @P_CDEMPRESA
             AND S.DTSALDEMPR  = @P_DTREFERENCIA
             AND S.CDCONTCTBL  = C.CDCONTCTBL
             AND C.IDTIPOCONT  = 'A'
             AND C.IDNATUCONT  = '04'
             AND C.IDCONTCCUS  <> 'R'
           ORDER BY S.CDCONTCTBL ASC, S.DTSALDEMPR, ST.CDCONTCTBL ASC, ST.DTSALDOTRANSF;
          OPEN C_I355;
          FETCH NEXT FROM C_I355 INTO @C_I355_DSLINHA;
          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_I355_DSLINHA, @P_NRSEQARQMAG);
             FETCH NEXT FROM C_I355 INTO @C_I355_DSLINHA;
          END;
          CLOSE C_I355;
          DEALLOCATE C_I355;
       SET @P_NRORDREGARQ = @V_NRORDREGARQ;
   END;
END;
GO

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