PC Toledo

Código: Selecionar todos

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

BEGIN

DECLARE dFechamento DATE;
DECLARE nAno, nMes, nFechaOficial INT(11);
DECLARE nDebito, nCredito DECIMAL(16,2);

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

SET nAno := YEAR( dInicial );

SET dFechamento := DATE( 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 := DATE( CONCAT( LPAD( nAno, 4, '0' ), '-', LPAD( nMes, 2, '0' ), '-01' ) );
      SET dFechamento := DATE( LAST_DAY( dFechamento ) );
   END IF;
END IF;

DROP TEMPORARY TABLE IF EXISTS SALDOS;

CREATE TEMPORARY TABLE SALDOS AS
SELECT CPCODIGO, CPGRUPO,
  ze_ContabilSaldoExercicio( IDCTCONTA, DATE( DATE_ADD( dInicial, INTERVAL -1 DAY ) ), dFechamento ) AS SALDOANTERIOR,
  ze_ContabilSaldoDebito( IDCTCONTA, dInicial, dFinal ) AS SALDOENTRADA,
  ze_ContabilSaldoCredito( IDCTCONTA, dInicial, dFinal ) AS SALDOSAIDA,
  0 AS SALDOATUAL
  FROM JPCTCONTA
  WHERE CPTIPO = 'A';

UPDATE TEMPORARY SALDOS SET SALDOATUAL = SALDOANTERIOR + SALDOENTRADA - SALDOSAIDA;

IF nFechado = 1 THEN
   IF DATE_ADD( dFechamento, INTERVAL nFechaOficial DAY ) = dFinal THEN
      SELECT SUM( SALDOATUAL ) FROM SALDOS WHERE CPGRUPO = 'R' AND SALDOATUAL > 0 INTO nDebito;
      SELECT SUM( SALDOATUAL ) FROM SALDOS WHERE CPGRUPO - 'R' AND SALDOATUAL < 0 INTO nCredito;
      UPDATE TEMPORARY SALDOS SET
         SALDOENTRADA = SALDOENTRADA + nDebito, SALDOSAIDA = SALDOSAIDA + nCredito, SALDOATUAL = SALDOATUAL + nDebito - nCredito
         WHERE CPCODIGO = ( SELECT EMLUCRO FROM JPEMPRESA );
      UPDATE TEMPORARY SALDOS SET SALDOENTRADA = SALDOENTRADA + ABS( SALDOATUAL ), SALDOATUAL = 0
         WHERE CPGRUPO = 'R' AND SALDOATUAL < 0;
      UPDATE TEMPORARY SALDOS SET SALDOSAIDA = SALDOSAIDA + ABS( SALDOATUAL ), SALDOATUAL = 0
         WHERE CPGRUPO = 'R' AND SALDOATUAL > 0;
   END IF;
END IF;

SELECT
   JPCTCONTA.IDCTCONTA, JPCTCONTA.CPCODIGO, JPCTCONTA.CPGRAU, JPCTCONTA.CPTIPO, JPCTCONTA.CPGRUPO,
	JPCTCONTA.CPNOME, SUM( SALDOS.SALDOANTERIOR ) AS ANTERIOR, SUM( SALDOS.SALDOENTRADA ) AS ENTRADA,
   SUM( SALDOS.SALDOSAIDA ) AS SAIDA, SUM( SALDOS.SALDOATUAL ) AS ATUAL
FROM JPCTCONTA
LEFT JOIN SALDOS ON TRIM( JPCTCONTA.CPCODIGO ) = SUBSTR( SALDOS.CPCODIGO, 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;

DROP TEMPORARY TABLE SALDOS;

END