PC Toledo

Código: Selecionar todos

CREATE PROCEDURE ze_ContabilBalancete( dInicial DATE, dFinal DATE, nFechamento INT(11), nGrau INT(11 ) )

BEGIN

DECLARE dFechamento DATE;
DECLARE nAno, nMes, nFechaOficial INT(11);

SET nFechaOficial := ( SELECT GREATEST( COALESCE( EMFECHA, 12 ), 1 ) FROM JPEMPRESA WHERE IDEMPRESA = 1 );

SET nAno := YEAR( dInicial );

SET dFechamento := CONCAT( LPAD( nAno - 1, 4, '0' ), '-12-31' );
IF nFechamento != 12 THEN
   SET nMes := MONTH( dInicial );
   SET nMes := FLOOR( ( nMes - 1 ) / nFechaOficial ) * nFechaOficial;
   IF nMes <> 0 THEN
      SET dFechamento := CONCAT( LPAD( nAno, 4, '0' ), '-', LPAD( nMes, 2, '0' ), '-01' );
      SET dFechamento := LAST_DAY( dFechamento );
   END IF;
END IF;

SELECT
   JPCTCONTA.IDCTCONTA, JPCTCONTA.CPCODIGO, JPCTCONTA.CPGRAU, JPCTCONTA.CPTIPO, JPCTCONTA.CPGRUPO,
	JPCTCONTA.CPNOME, SUM( b.saldo ) AS anterior, SUM( b.deb ) as entrada, SUM( b.cred ) AS saida,
   nFechaOficial, nFechamento, dFechamento
FROM JPCTCONTA
LEFT JOIN
( SELECT A.CPCODIGO as analitica,
  ze_ContabilSaldoExercicio( A.IDCTCONTA, DATE( DATE_ADD( dInicial, INTERVAL -1 DAY ) ), dFechamento ) AS saldo,
  ze_ContabilSaldoDebito( A.IDCTCONTA, dInicial, dFinal ) AS deb,
  ze_ContabilSaldoCredito( A.IDCTCONTA, dInicial, dFinal ) AS cred
  FROM JPCTCONTA AS A
  WHERE A.CPTIPO = 'A'
) AS b ON TRIM( JPCTCONTA.CPCODIGO ) = SUBSTR( b.analitica, 1, LENGTH( TRIM( JPCTCONTA.CPCODIGO ) ) )
WHERE JPCTCONTA.CPGRAU <= nGrau
GROUP BY JPCTCONTA.IDCTCONTA
HAVING anterior != 0 OR entrada != 0 OR saida != 0
ORDER BY CPCODIGO;

END

Código: Selecionar todos

CREATE FUNCTION ze_ContabilSaldoDebito( nIdCtConta INT(11), dInicial DATE, dFinal DATE )
RETURNS DECIMAL(16,2)

BEGIN

DECLARE nSaldo DECIMAL(16,2);

SELECT COALESCE( SUM( CTVALOR ), 0 )
   FROM JPCONTABIL
   WHERE CTCTADEB = nIdCtConta
      AND DATE( CTDATA ) BETWEEN DATE( dInicial ) AND DATE( dFinal )
INTO nSaldo;

RETURN nSaldo;

END

Código: Selecionar todos

CREATE FUNCTION ze_ContabilSaldoCredito( nIdCtConta INT(11), dInicial DATE, dFinal DATE )
RETURNS DECIMAL(16,2)

BEGIN

DECLARE nSaldo DECIMAL(16,2);

SELECT COALESCE( SUM( CTVALOR ), 0 )
   FROM JPCONTABIL
   WHERE CTCTACRE = nIdCtConta
      AND DATE( CTDATA ) BETWEEN DATE( dInicial ) AND DATE( dFinal )
INTO nSaldo;

RETURN nSaldo;

END