Soma em cascata na consulta sql

ROGERIO MOREIRA · Novembro 22, 2022

Olá galera

estou desenvolvendo um relatório que simula contabilidade e preciso criar uma consulta com soma progressiva

das contas com mesmo prefixo, por exemplo:

CONTA SOMA

==================

1 1.000,00

11 700,00

111 300,00

112 400,00

121 300,00

teoricamente seria assim:

$sql = mysql_query("sum(valor) as total

from lancamentos

left join contas

on lancamentos.conta = contas. conta

where conta like 'conta%' <- essa linha é imaginária, mas seria a ideia

group by conta

tetsuo · Novembro 22, 2022

o where não vai ter força suficiente pra sobrescrever o join
tente usar o like no join
... on lancamentos.conta like '$conta%'...
pra ver se funciona

ROGERIO MOREIRA · Novembro 22, 2022

boa ideia, e obrigado pela resposta,

porém a string $conta não existe, explico:

tenho um plano de contas assim

1

11

111 <- somente este ultimo recebe lancamentos, os demais sao cabecalho

e como funciona na contabilidade eu precisaria somar em cascata

todas as contas que começam com 1

depois todas que começam com 11

e depois as contas que começam com 111

e assim por diante

sendo que somente as contas do terceiro grau recebem lancamento

as outras são cabeçalho de contas

Frank K Hosaka · Novembro 22, 2022

Ao invés de fazer o balancete no PHP, eu fiz no MySQL assim:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_bal1` AS
    SELECT 
        `tbconta`.`conta` AS `conta`,
        `tbconta`.`descricao` AS `descricao`,
        (COALESCE(SUM((CASE
                    WHEN (`tbdiario`.`contad` = `tbconta`.`conta`) THEN `tbdiario`.`valor`
                    ELSE 0
                END)),
                0) - COALESCE(SUM((CASE
                    WHEN (`tbdiario`.`contac` = `tbconta`.`conta`) THEN `tbdiario`.`valor`
                    ELSE 0
                END)),
                0)) AS `saldo`
    FROM
        ((`tbsupervariavel`
        JOIN `tbconta`)
        LEFT JOIN `tbdiario` ON ((`tbdiario`.`dia` < `tbsupervariavel`.`primeirodia`)))
    GROUP BY `tbconta`.`conta`
    ORDER BY 'tbconta'.'conta'
            --------------------------------------------------------                                                                           
                                                                                       
    CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_bal2` AS
    SELECT 
        `tbconta`.`conta` AS `conta`,
        SUM((CASE
            WHEN (`tbdiario`.`contad` = `tbconta`.`conta`) THEN `tbdiario`.`valor`
            ELSE 0
        END)) AS `debito`,
        SUM((CASE
            WHEN (`tbdiario`.`contac` = `tbconta`.`conta`) THEN `tbdiario`.`valor`
            ELSE 0
        END)) AS `credito`
    FROM
        ((`tbsupervariavel`
        JOIN `tbconta`)
        JOIN `tbdiario` ON ((`tbdiario`.`dia` BETWEEN `tbsupervariavel`.`primeirodia` AND `tbsupervariavel`.`ultimodia`)))
    GROUP BY `tbconta`.`conta`
    ORDER BY `tbconta`.`conta`
               ----------------------------------------------------                                                                        
                                                                                       
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_balancete` AS
    SELECT 
        `vw_bal1`.`conta` AS `conta`,
        `vw_bal1`.`descricao` AS `descricao`,
        `vw_bal1`.`saldo` AS `saldo`,
        `vw_bal2`.`debito` AS `debito`,
        `vw_bal2`.`credito` AS `credito`,
        ((`vw_bal1`.`saldo` + `vw_bal2`.`debito`) - `vw_bal2`.`credito`) AS `final`
    FROM
        (`vw_bal1`
        JOIN `vw_bal2` ON ((`vw_bal1`.`conta` = `vw_bal2`.`conta`)))
    GROUP BY `vw_bal1`.`conta`
    ORDER BY `vw_bal1`.`conta`
    ORDER BY `tbconta`.`conta`

Claro que inventei uma tremenda gambiarra, onde inventei uma tabela que só tem um registro e que chamei de tbsupervariavel:

CREATE TABLE `tbsupervariavel` (
  `codsuper` int NOT NULL,
  `docto` int DEFAULT NULL,
  `anoapurado` int DEFAULT NULL,
  `dia` date DEFAULT NULL,
  `codprod` int DEFAULT NULL,
  `lcto` int DEFAULT NULL,
  `mesapurado` int DEFAULT NULL,
  `mesextenso` varchar(10) CHARACTER SET utf8 COLLATE utf8_swedish_ci DEFAULT NULL,
  `primeirodia` date DEFAULT NULL,
  `ultimodia` datetime 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

Já o balancete.php só tem que apresentar as informações de forma mais amigável, por exemplo, calculando o resultado em qualquer perído de apuração assim:

<?php
$menu="Balancete";
include('menu.php');
echo "<script>document.title='Balancete'</script>";

if (isset($_POST['balancete'])) {
	$receita=0; $despesa=0; 
	$query = $mysqli->query("select * from tbsupervariavel");
	$linha = $query->fetch_assoc();
	$mes=$linha['mesapurado'];
	$ano=$linha['anoapurado'];
	$ultimo="$ano-$mes-1";$ultimo=strtotime($ultimo); $primeiro=date('Y-m-d',$ultimo);
	$penultimo=$ultimo-1;$penultimo=date('Y-m-d',$penultimo);
	$ultimo=date('Y-m-t',$ultimo);
	$mesextenso=$linha['mesextenso'];
	echo "<div class=divteste><table class='table table-striped linha'><tr><th><th align=left>";
	echo "<a onclick=inpg.name='apuracao';inpg.value=1;frmg.submit()>$mesextenso de $ano</a>";
	echo "</th><th align=right>Anterior<th align=right>Débito";
	echo "<th align=right>Crédito<th align=right>Saldo Atual";
	$query = $mysqli->query("select * from vw_balancete");
	$anterior=0;$receita=0;$despesa=0;
	while($linha = $query->fetch_assoc()){
		$conta=$linha['conta'];
		$conta2 = "<a onclick=inpg.name='razao';inpg.value=$conta;frmg.submit() class=azul>$conta</a>";
		$descricao=$linha['descricao'];
		$saldo=$linha['saldo'];
		$debito=$linha['debito'];
		$credito=$linha['credito'];
		$final=$linha['final'];
		echo "<tr><td>$conta2</td><td>$descricao</td><td align=right>".dec($saldo);
		echo "<td align=right>".dec($debito)."<td align=right>".dec($credito)."<td align=right>";
		echo dec($final);
		$anterior+=$saldo;
		if ($conta>=301 and $conta<=304){$receita+=$final;}
		if ($conta>304){$despesa+=$final;}
		if ($conta==304){echo "<tr><td><td><td><td><td align=right><b>Receita<td align=right><b>".dec($receita);}}
		echo "<tr><td><td><td><td><td align=right><b>Despesa<td align=right><b>".dec($despesa);
		if(abs($receita)>$despesa){$resultado="Lucro";} else {$resultado="Prejuizo";}
		echo "<tr><td><td><td align=right><b>".dec($anterior)."<td><td align=right><b>$resultado<td align=right><b>".dec($despesa+$receita);
		echo "</table></div>"; exit;}

if (isset($_POST['voltadiario'])) {
	$diar=$_POST['voltadiario']; 
	$diar=implode('-',array_reverse(explode('/',$diar))); 
	$query=$mysqli->query("update tbsupervariavel set dia='$diar'");
	echo "<script>location.replace('diario.php')</script>";}

if (isset($_POST['razao'])) { // razão
$conta = $_POST['razao'];
$query = $mysqli->query("select descricao from tbconta where conta=$conta");
$descricao = $query->fetch_row()[0];
$query = $mysqli->query("select * from tbsupervariavel");
$linha = $query->fetch_assoc(); $mes=$linha['mesapurado']; $ano=$linha['anoapurado'];$mesextenso=$linha['mesextenso'];
$ultimo="$ano-$mes-1";$ultimo=strtotime($ultimo); $primeiro=date('Y-m-d',$ultimo);
$penultimo=$ultimo-1;$penultimo=date('Y-m-d',$penultimo);$ultimo=date('Y-m-t',$ultimo);
$query2 = $mysqli->query("select * from vw_balancete where conta=$conta");
$linha2=$query2->fetch_assoc(); $saldo = $linha2['saldo'];
if ($saldo>0) {$saldo= dec($saldo);} else {$saldo="<font color=red>".dec($saldo)."</font>";}
echo "<div><table class='table table-striped linha'><tr><th>Docto<th><a onclick=inpg.name='apuracao';inpg.value=$conta;frmg.submit()>$ano</a><th>";
echo "<th align=left nowrap><a onclick=inpg.name='balancete';frmg.submit()>$conta $descricao</a>";
echo "<tr><td></td><td></td><td align=right><b>$saldo</td><td><b>Saldo anterior</td></tr>";
$query=$mysqli->query("select * from tbdiario where (dia between '$primeiro' and '$ultimo' and contad=$conta)
	or (dia between '$primeiro' and '$ultimo' and contac=$conta)");
while ($linha=$query->fetch_assoc()){ 
	$diar = df($linha['dia']); $diar2=df3($linha['dia']);
	$valor = $linha['valor'];
	$contad=$linha['contad'];
	$lcto = $linha['lcto'];$lcto="<a onclick='detalhar($lcto)'>$lcto</a>";
	$diar2="<a onclick=inpg.name='voltadiario';inpg.value='$diar';frmg.submit() style=color:blue>$diar2</a>";
	if ($contad==$conta) {$valor = dec($valor);} else {$valor="<font color=red>".dec($valor)."</font>";}
	echo "<tr><td valign='top'>$lcto<td>$diar2<td align=right valign='top'>$valor<td valign='top'>".$linha['hist'];}	
$tdebito=dec($linha2['debito']);
echo "<tr><td><td><td align=right><b>$tdebito<td><b>Total de débito";
$tcredito=dec($linha2['credito']);
echo "<tr ><td><td><td align=right><font color=red><b>$tcredito<td><b>Total de crédito</font></tr>";
$sql="select (sum(ifnull(debito,0))-sum(ifnull(credito,0))) as saldo from tbdiario where dia<='$ultimo' and conta='$conta'";
$saldo = $linha2['final'];
if ($saldo>0) {$saldo= dec($saldo);} else {$saldo="<font color=red>".dec($saldo)."</font>";}
echo "<tr><td><td><td align=right><b>$saldo<td><b>Saldo atual</table></div>";exit;}

if (isset($_POST['apuracao'])) {
	$voltarpara=$_POST['apuracao'];
if (isset($_POST['escolhido'])) {
	// atualiza o período de apuração para balancete e razão
	$apuracao=$_POST['escolhido'];
	$apuracao = strtotime($apuracao);
	$format = new IntlDateFormatter('pt_BR', IntlDateFormatter::NONE, IntlDateFormatter::NONE, NULL, NULL, "MMMM");
	$mesextenso = datefmt_format($format, $apuracao); $mesextenso = ucfirst($mesextenso);
	$ano=date('Y',$apuracao);
	$mes=date('m',$apuracao);
	$primeirodia=date('Y-m-1',$apuracao);
	$ultimodia=date('Y-m-t',$apuracao);
	$query=$mysqli->query("update tbsupervariavel set ultimodia='$ultimodia', primeirodia='$primeirodia',
		anoapurado=$ano, mesapurado=$mes, mesextenso='$mesextenso' where codsuper=1");
	if ($voltarpara==1) {
		echo "<script>inpg.name='balancete';frmg.submit()</script>";} else {
		echo "<script>inpg.name='razao';inpg.value=$voltarpara;frmg.submit()</script>";}}
$sql="Select * from vw_apuracao";
$query = $mysqli->query($sql);
echo "<table><tr height=50px><tr>";
while($linha = $query->fetch_assoc()) {
	$apuracao = $linha['apuracao'];
    echo "<form method='post'><input type=hidden name=apuracao value=$voltarpara>";
	echo "<td><button name=escolhido value=$apuracao>".df2($apuracao)."</button></td>";
	$mes=substr($apuracao,5,2);if (($mes=='01') or ($mes=='07')) {echo "</tr><tr>";}}
echo "</table>"; exit;}
?>

ROGERIO MOREIRA · Novembro 22, 2022

cara, não sei nem o que dizer, muiitissimo obrigado, vou apreciar esse codigo!

:thumbsup:

Jack Oliveira · Novembro 22, 2022

Usar gambiarras não é recomendável se pode usar o método prático e eficaz, nada contra quem usa a gambiarra.

O que o colega @tetsuo informa é você colocar na prática

Ou segue um exemplo aqui

Frank K Hosaka · Novembro 22, 2022

21 minutos atrás, Jack Oliveira disse:

Usar gambiarras não é recomendável se pode usar o método prático e eficaz, nada contra quem usa a gambiarra.

O que o colega @tetsuo informa é você colocar na prática

Ou segue um exemplo aqui

Hahahaha... eu concordo. Quando eu criei a tbsupervariável ele só tinha quatro campos, hoje já chega a 20 campos. É a única tabela no mundo inteiro que só tem um registro. Ele me ajuda a mudar o prazo de apuração. Ele me ajuda a escolher um cliente num orçamento. Ele me ajuda a escolher um produto. Claro que o PHP tem variáveis globais que podem fazer o mesmo serviço, mas como eu sou péssimo programador, eu apelei para o MySQL fazer esse serviço por mim. Melhor do que ninguém, eu sei que a gambiarra não compensa nos países desenvolvidos onde os desenvolvedores levam a programação como coisa séria. Mas como vivemos no paraíso da impunidade, não vejo porque eu devo abrir mãos desses atalhos bem espertos. Afinal se os vereadores, prefeitos, deputados estaduais, deputados federais, senadores não se dão o trabalho de mostrar o bom exemplo de respeitar a lógica e a razão, por que eu devo seguir um caminho diferente?

ROGERIO MOREIRA · Novembro 23, 2022

16 horas atrás, Jack Oliveira disse:

Usar gambiarras não é recomendável se pode usar o método prático e eficaz, nada contra quem usa a gambiarra.

O que o colega @tetsuo informa é você colocar na prática

Ou segue um exemplo aqui

valeu, me ajude como otimizar essa operação, por favor:

foreach($contas as &$conta) {

$sql = mysql_query("sum(valor) as total from lancamentos where conta like '$conta%'");

}

esse processo até funcionaria, o que eu não quero é gerar uma busca para cada registro.

basicamente o sistema criaria uma somatoria escalonada das contas,

como um balancete de contabilidade

1 = R$ xxxxxxx (soma de todas as contas que começam com 1)

11 = R$ xxxxxxxx (soma de todas as contas que começam com 11)

111 = R$ xxxxxxxx (soma de todas as contas que começam com 111)

obs: as contas estão em uma tabela e os lançamentos em outra

ROGERIO MOREIRA · Novembro 23, 2022

17 horas atrás, Jack Oliveira disse:

Usar gambiarras não é recomendável se pode usar o método prático e eficaz, nada contra quem usa a gambiarra.

O que o colega @tetsuo informa é você colocar na prática

Ou segue um exemplo aqui

isso aqui não tá errado?

Frank K Hosaka · Novembro 23, 2022

29 minutos atrás, ROGERIO MOREIRA disse:

valeu, me ajude como otimizar essa operação, por favor:

foreach($contas as &$conta) {

$sql = mysql_query("sum(valor) as total from lancamentos where conta like '$conta%'");

}

esse processo até funcionaria, o que eu não quero é gerar uma busca para cada registro.

basicamente o sistema criaria uma somatoria escalonada das contas,

como um balancete de contabilidade

1 = R$ xxxxxxx (soma de todas as contas que começam com 1)

11 = R$ xxxxxxxx (soma de todas as contas que começam com 11)

111 = R$ xxxxxxxx ( soma de todas as contas que começam com 111)

Eu não me preocupo com a otimização, mas sim com o resultado.
Primeiro eu começo com as coisas mais simples.
Vou no terminal do MySQL, e peço para executar "Select * from tblancamentos", e ele me devolve um monte de registros com data, contadevedora, contacredora, histórico e valor.
Como eu faço para separar a conta Enel (4111)?
Então eu mudo a requisição assim "Select * from tblancamentos where contadevedora=4111 or contacredor=4111", aqui ele filtra todos os débitos e créditos da Enel lá de 1975 até 2022.
Como eu faço para filtrar por data?
"Select * from tblancamentos where (contadevedora=4111 or contacredor=4111) and dia between '2022-11-01' and '2022-11-30'
Como eu faço para somar todos os débitos de 4111?
"Select sum(case when contadevedora=4111 valor else 0 end) as debito where dia between '2022-11-01' and '2022-11-30'"
Você precisa se familiarizar com as ferramentas mais simples para chegar no resultado que você espera. Um sistema otimizado é quando o programa faz o que você quer, pouco importa quantas vezes o programa precisa ver os dados.

Motta · Novembro 24, 2022

Tabela de parâmetros , não é "gambi" desde que bem documentada.

Em tese permite mudar o comportamento do Sistema sem precisar compilar por exemplo.

Fiz um balancete para Oracle usando analytic functions , mais ou menos na linha do solução do Hosaka.

Frank K Hosaka · Novembro 25, 2022

5 horas atrás, Motta disse:

Tabela de parâmetros , não é "gambi" desde que bem documentada.

Em tese permite mudar o comportamento do Sistema sem precisar compilar por exemplo.

Fiz um balancete para Oracle usando analytic functions , mais ou menos na linha do solução do Hosaka.

Esse analytic functions chamou a minha atenção. Dentro da rotina do balancete eu criei as instruções para mostrar ao usuário todas as apurações que existem nos registros contábeis, ele escolhe uma, a apuração escolhida é gravada na tbsupervariável, depois ele volta no balancete mas usando o período de apuração que foi gravado na tbsupervariável. É possível fazer o mesmo roteiro sem a necessidade de usar o MySQL como "variável global" do PHP?

Frank K Hosaka · Novembro 25, 2022

5 horas atrás, Motta disse:

Tabela de parâmetros , não é "gambi" desde que bem documentada.

Em tese permite mudar o comportamento do Sistema sem precisar compilar por exemplo.

Fiz um balancete para Oracle usando analytic functions , mais ou menos na linha do solução do Hosaka.

Esse analytic functions chamou a minha atenção. Dentro da rotina do balancete eu criei as instruções para mostrar ao usuário todas as apurações que existem nos registros contábeis, ele escolhe uma, a apuração escolhida é gravada na tbsupervariável, depois ele volta no balancete mas usando o período de apuração que foi gravado na tbsupervariável. É possível fazer o mesmo roteiro sem a necessidade de usar o MySQL como "variável global" do PHP? Eu não tive como imaginar outra solução, pois o período de apuração também é utilizado no livro Razão, e assim usei a tbsupervariável para gravar o endereço da rotina que chamou a apuração.

Soma em cascata na consulta sql

Recommended Posts

ROGERIO MOREIRA 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

tetsuo 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

ROGERIO MOREIRA 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Frank K Hosaka 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

ROGERIO MOREIRA 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Jack Oliveira 1

Compartilhar este post

Link para o post

Compartilhar em outros sites

Frank K Hosaka 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

ROGERIO MOREIRA 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

ROGERIO MOREIRA 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Frank K Hosaka 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Motta 645

Compartilhar este post

Link para o post

Compartilhar em outros sites

Frank K Hosaka 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Frank K Hosaka 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Crie uma conta ou entre para comentar

Criar uma conta

Entrar

Javascript - Arredondar Valor para cima

PHP - Atualizar INPUT com função moeda

PHP - Atualizar INPUT com função moeda

Conteúdo Similar

Fóruns

Tempo Real

Informação importante