O balancete

[Frank K Hosaka]

Eu estou criando um balancete no Laravel, mas eu percebi que o Controller iria ficar bem grande e difícil de manusear. Assim decidi montar o balancete no MySQL para o Laravel já pegar quase tudo mastigado. Aqui as principais tabelas e views que eu criei:

CREATE TABLE `tbsupervariavel` (
  `codsuper` int NOT NULL,
  `docto` int DEFAULT NULL,
  `dia` date DEFAULT NULL,
  `codprod` int DEFAULT NULL,
  `anoapurado` int DEFAULT NULL,
  `mesapurado` int DEFAULT NULL,
  `mesextenso` varchar(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `primeirodia` date DEFAULT NULL,
  `end` varchar(45) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `senha` varchar(20) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `ped` mediumint DEFAULT NULL,
  `codp` mediumint DEFAULT NULL,
  `pessoa` varchar(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `criterio` varchar(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT 'null',
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`codsuper`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_swedish_ci

CREATE TABLE `tbdiario` (
  `docto` mediumint NOT NULL AUTO_INCREMENT,
  `dia` date DEFAULT NULL,
  `conta` mediumint DEFAULT NULL,
  `debito` decimal(13,2) DEFAULT NULL,
  `credito` decimal(13,2) DEFAULT NULL,
  `hist` varchar(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`docto`),
  KEY `Conta_idx` (`conta`),
  CONSTRAINT `Conta` FOREIGN KEY (`conta`) REFERENCES `tbconta` (`conta`)
) ENGINE=InnoDB AUTO_INCREMENT=14489 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_swedish_ci

CREATE TABLE `tbconta` (
  `conta` mediumint NOT NULL,
  `descricao` varchar(50) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `tipoconta` varchar(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `ordemconta` int DEFAULT NULL,
  PRIMARY KEY (`conta`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_swedish_ci

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_movimento` AS
    SELECT 
        `diario`.`tbdiario`.`conta` AS `conta`,
        SUM(`diario`.`tbdiario`.`debito`) AS `debito`,
        SUM(`diario`.`tbdiario`.`credito`) AS `credito`
    FROM
        ((`diario`.`tbconta`
        JOIN `diario`.`tbdiario`)
        JOIN `diario`.`tbsupervariavel`)
    WHERE
        ((YEAR(`diario`.`tbdiario`.`dia`) = `diario`.`tbsupervariavel`.`anoapurado`)
            AND (MONTH(`diario`.`tbdiario`.`dia`) = `diario`.`tbsupervariavel`.`mesapurado`))
    GROUP BY `diario`.`tbdiario`.`conta`
    ORDER BY `diario`.`tbdiario`.`conta`

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_anterior` AS
    SELECT 
        `diario`.`tbdiario`.`conta` AS `conta`,
        `diario`.`tbconta`.`descricao` AS `descricao`,
        (SUM(COALESCE(`diario`.`tbdiario`.`debito`, 0)) - SUM(COALESCE(`diario`.`tbdiario`.`credito`, 0))) AS `saldo`
    FROM
        ((`diario`.`tbdiario`
        JOIN `diario`.`tbsupervariavel`)
        JOIN `diario`.`tbconta` ON ((`diario`.`tbdiario`.`conta` = `diario`.`tbconta`.`conta`)))
    WHERE
        (`diario`.`tbdiario`.`dia` < `diario`.`tbsupervariavel`.`primeirodia`)
    GROUP BY `diario`.`tbdiario`.`conta`
    ORDER BY `diario`.`tbdiario`.`conta`
                                                                              
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_balanco` AS
    SELECT 
        `diario`.`vw_anterior`.`conta` AS `conta`,
        `diario`.`vw_anterior`.`descricao` AS `descricao`,
        `diario`.`vw_anterior`.`saldo` AS `saldo`,
        `diario`.`vw_movimento`.`debito` AS `debito`,
        `diario`.`vw_movimento`.`credito` AS `credito`
    FROM
        (`diario`.`vw_anterior`
        LEFT JOIN `diario`.`vw_movimento` ON ((`diario`.`vw_anterior`.`conta` = `diario`.`vw_movimento`.`conta`)))

 

[lowys]

Existe um canal no telegram, onde as pessoas se ajudam muito com laravel: https://t.me/laravelbr

[Frank K Hosaka]

Para consertar as consultas, fiz o seguinte

vw_bal1 separa apenas os registros da tbdiario que fazem parte da apuração

vw_bal2 soma os débitos e créditos do vw_bal1

vw_bal3 calcula o saldo anterior à apuração

vw_balanço ajunta vw_bal2 e vw_bal3

Acho que essa é a única solução em qualquer banco de dados, para cálculos complexos, o melhor é fazer em partes menores e ajuntar tudo lá no final.

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_bal1` AS
    SELECT 
        `diario`.`tbdiario`.`dia` AS `dia`,
        `diario`.`tbdiario`.`docto` AS `docto`,
        `diario`.`tbdiario`.`conta` AS `conta`,
        `diario`.`tbdiario`.`debito` AS `debito`,
        `diario`.`tbdiario`.`credito` AS `credito`,
        `diario`.`tbdiario`.`hist` AS `hist`
    FROM
        (`diario`.`tbdiario`
        JOIN `diario`.`tbsupervariavel`)
    WHERE
        ((YEAR(`diario`.`tbdiario`.`dia`) = `diario`.`tbsupervariavel`.`anoapurado`)
            AND (MONTH(`diario`.`tbdiario`.`dia`) = `diario`.`tbsupervariavel`.`mesapurado`))
    ORDER BY `diario`.`tbdiario`.`dia` , `diario`.`tbdiario`.`docto`

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_bal2` AS
    SELECT 
        `diario`.`vw_bal1`.`conta` AS `conta`,
        SUM(`diario`.`vw_bal1`.`debito`) AS `debito`,
        SUM(`diario`.`vw_bal1`.`credito`) AS `credito`
    FROM
        `diario`.`vw_bal1`
    GROUP BY `diario`.`vw_bal1`.`conta`
    ORDER BY `diario`.`vw_bal1`.`conta`

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_bal3` AS
    SELECT 
        `diario`.`tbdiario`.`conta` AS `conta`,
        `diario`.`tbconta`.`descricao` AS `descricao`,
        (SUM(COALESCE(`diario`.`tbdiario`.`debito`, 0)) - SUM(COALESCE(`diario`.`tbdiario`.`credito`, 0))) AS `saldo`
    FROM
        ((`diario`.`tbdiario`
        JOIN `diario`.`tbsupervariavel`)
        JOIN `diario`.`tbconta` ON ((`diario`.`tbdiario`.`conta` = `diario`.`tbconta`.`conta`)))
    WHERE
        (`diario`.`tbdiario`.`dia` < `diario`.`tbsupervariavel`.`primeirodia`)
    GROUP BY `diario`.`tbdiario`.`conta`
    ORDER BY `diario`.`tbdiario`.`conta`
                                                                              
                                                                              
                                                                              
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `diario`.`vw_balanco` AS
    SELECT 
        `diario`.`vw_bal3`.`conta` AS `conta`,
        `diario`.`vw_bal3`.`descricao` AS `descricao`,
        `diario`.`vw_bal3`.`saldo` AS `saldo`,
        `diario`.`vw_bal2`.`debito` AS `debito`,
        `diario`.`vw_bal2`.`credito` AS `credito`
    FROM
        (`diario`.`vw_bal3`
        LEFT JOIN `diario`.`vw_bal2` ON ((`diario`.`vw_bal3`.`conta` = `diario`.`vw_bal2`.`conta`)))

 

Editado Outubro 18, 2022 por Frank K Hosaka