Category and Subcategory Sum with PHP and MYSQL

Asked

Viewed 404 times

-2

I already broke my head and I’m lost in finding the solution to my problem, I’m developing a financial report that has categories and subcategory (7 levels) Below is the structure of part of my table and how is some data:

inserir a descrição da imagem aqui

The report works "almost" correctly, but this "almost" is tormenting me, as I do to add the levels. By reporting I can only add up where you received the information.

DICOR-->Revenue from Capital->Increase of Patrimony->Contributions from the Treasury

I only have release of "Contributions of treasure" as I do for the query go adding from the lowest level to the highest level.

insira o código aqui


//pega empenhos
mysql_select_db($database_codfor, $codfor);
$query_sql_empenho = "SELECT num_nivel, lancamento, categoria.num_categoria, categoria.des_categoria, (SELECT SUM(val_aprovado) FROM aprovado, categoria cat1 WHERE cat1.seq_categoria = aprovado.seq_categoria AND cat1.seq_categoria = categoria.seq_categoria AND YEAR(aprovado.dt_aprovado) = YEAR('".$ano."-01-01') GROUP BY cat1.seq_categoria) as val_aprovado, (SELECT SUM(empenho.val_empenho) FROM empenho, categoria cat2 WHERE cat2.seq_categoria = empenho.seq_categoria AND cat2.seq_categoria = categoria.seq_categoria AND MONTH(empenho.dt_empenho) = MONTH('".$ano."-".$mes."-01') AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') GROUP BY cat2.seq_categoria) as val_empenho_no_mes, (SELECT SUM(empenho.val_empenho) FROM empenho, categoria cat3 WHERE cat3.seq_categoria = empenho.seq_categoria AND cat3.seq_categoria = categoria.seq_categoria AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') AND MONTH(empenho.dt_empenho) <= MONTH('".$ano."-".$mes."-01')  GROUP BY cat3.seq_categoria) as val_empenho_no_ano, (SELECT SUM(empenho.val_empenho) FROM empenho, categoria cat4 WHERE cat4.seq_categoria = empenho.seq_categoria AND cat4.seq_categoria = categoria.seq_categoria AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') AND MONTH(empenho.dt_empenho) < MONTH('".$ano."-".$mes."-01') GROUP BY cat4.seq_categoria) as val_empenho_ate_mes FROM categoria WHERE num_categoria LIKE '2%' ORDER BY categoria.num_categoria ASC";
$sql_empenho = mysql_query($query_sql_empenho, $codfor) or die(mysql_error());
$row_sql_empenho = mysql_fetch_assoc($sql_empenho);
$totalRows_sql_empenho = mysql_num_rows($sql_empenho);

inserir a descrição da imagem aqui

Here is an example of how the report works today. I have to generate a similar report in my system.

Follow the sources I am using: [link] (drive.google.com/open?id=1OCRb_eaHGeLwO1N-Qwy6lobbvbbaxoo8) . In this folder you have the following files: categoria_lh.pdf - Report of the Categories / categoria_lh.php - Report of the Categories (Source Code) / category.sql - SQL Code of the creation and the data of the table Category / Empenho_l_mesxxxx.pdf - Report of Commitments in the Month (That’s the "goat" I want to make it work) / Empenho_l_mesxxxx.php - Commitment Report Source Code "goat". Thank you for your attention!!!! A big hug.

php mysql select report hierarchical-data

3 answers

0

Sum the query with SUM

Example: 

mysql_query("SELECT SUM(Value) as total FROM Codes");

or in php:

$result += $soma;

  • The problem is that the sum is up and not down, could do as if it were a counter (n = n + 1), but the sum goes first and then appears the data.

  • You’ve already solved?

  • Nothing, I don’t know what to do. The scheme is similar to accounting accounts I see this a lot in desktop applications, but on the web I saw some cases but in jsp. In PHP I don’t know what to do. It would be like this.

  • is that I’m not understanding your logic... Calm down there...

  • You have posted print of other things and nothing you do in the query, so it may be that some query command is with the wrong syntax tries to do, all separate and see if it solves your problem, because the right one would be this...

  • Hello, I’m sending you some files for a better understanding of the situation: [link] (https://drive.google.com/open?id=1OCRb_eaHGeLwO1N-QwY6lobbvBbaXOo8) . In this folder you have the following files: categoria_lh.pdf - Report of the Categories / categoria_lh.php - Report of the Categories (Source Code) / category.sql - SQL Code of the creation and the data of the table Category / Empenho_l_mesxxxx.pdf - Report of Commitments in the Month (That’s the "goat" I want to make it work) / Empenho_l_mesxxxx.php - Commitment Report Source Code "goat". Thank you for your attention!!!! A big hug.

Show 1 more comment

0

I managed to solve the situation using a little logic.

Example: Category 1 = 2,000,000,000 => Sum of Categories 2 to 7 Category 2 = 2.100.000.000 => Sum of Categories 3 to 7 Category 3 = 2.110.000.000 => Sum of Categories 4 to 7 Category 4 = 2.111.000.000 => Sum of Categories 5 to 7 Category 5 = 2.111.100.000 => Sum of Categories 6 to 7 Category 6 = 2.111.110.000 => Sum of Category 7 Category 7 = 2.111.111.000 => Data ....


Código Inicial:
//pega categorias
mysql_select_db($database_codfor, $codfor);
$query_sql_categorias = "SELECT categoria.num_nivel, categoria.lancamento, categoria.num_categoria, categoria.des_categoria, categoria.seq_categoria FROM categoria WHERE num_categoria LIKE '2%' ORDER BY categoria.num_categoria ASC";
$sql_categorias = mysql_query($query_sql_categorias, $codfor) or die(mysql_error());
$row_sql_categorias = mysql_fetch_assoc($sql_categorias);
$totalRows_sql_categorias = mysql_num_rows($sql_categorias);

of {

$nivel = $row_sql_categorias['num_nivel'];

echo '<tr ';
  if ($row_sql_empenho['lancamento'] == 'S') { echo 'bgcolor="#F1F1F1"'; }  
echo ' >';
echo '  <td class="texto">';

  if ($nivel == 1 ){
      echo '<b> ';
  }
  if ($nivel == 2 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  }  
  if ($nivel == 3 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  }  
  if ($nivel == 4 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  } 
  if ($nivel == 5 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  } 
  if ($nivel == 6 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  } 
  if ($nivel == 7 ){
      echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp';
  } 

  $array = str_split($row_sql_categorias['num_categoria'], 1);

  echo $array[0].".".$array[1].$array[2].$array[3].".".$array[4].$array[5].$array[6].".".$array[7].$array[8].$array[9]; 
  echo ' - '.$row_sql_categorias['des_categoria'];

  if ($nivel == 1 ){
      echo '</b>';
  } 

echo '</td>';   

$categoria = $row_sql_categorias['num_categoria'];

$rest9     = substr($categoria, 1); /// 2000000000 => 000000000
$rest8     = substr($categoria, 2); /// 2000000000 => 00000000
$rest7     = substr($categoria, 3); /// 2000000000 => 0000000
$rest6     = substr($categoria, 4); /// 2000000000 => 000000
$rest5     = substr($categoria, 5); /// 2000000000 => 00000
$rest4     = substr($categoria, 6); /// 2000000000 => 0000
$rest3     = substr($categoria, 7); /// 2000000000 => 000
$rest2     = substr($categoria, 8); /// 2000000000 => 00
$rest1     = substr($categoria, 9); /// 2000000000 => 0

if     ($rest9 == '000000000') { $categoria_final = substr($categoria, 0, 1); } /// 2
elseif ($rest8 == '00000000')  { $categoria_final = substr($categoria, 0, 2); } /// 20
elseif ($rest7 == '0000000')   { $categoria_final = substr($categoria, 0, 3); } /// 200
elseif ($rest6 == '000000')    { $categoria_final = substr($categoria, 0, 4); } /// 2000
elseif ($rest5 == '00000')     { $categoria_final = substr($categoria, 0, 5); } /// 20000
elseif ($rest4 == '0000')      { $categoria_final = substr($categoria, 0, 6); } /// 200000
elseif ($rest3 == '000')       { $categoria_final = substr($categoria, 0, 7); } /// 2000000
elseif ($rest2 == '00')        { $categoria_final = substr($categoria, 0, 8); } /// 20000000
elseif ($rest1 == '0')         { $categoria_final = substr($categoria, 0, 9); } /// 200000000

/// Aprovado

mysql_select_db($database_codfor, $codfor);
$query_sql_aprovado = "SELECT SUM(val_aprovado) as val_aprovado FROM aprovado, categoria WHERE categoria.seq_categoria = aprovado.seq_categoria AND categoria.num_categoria LIKE  '".$categoria_final."%' AND YEAR(aprovado.dt_aprovado) = YEAR('".$ano."-01-01') GROUP BY categoria.seq_categoria";
$sql_aprovado = mysql_query($query_sql_aprovado, $codfor) or die(mysql_error());
$row_sql_aprovado = mysql_fetch_assoc($sql_aprovado);
$totalRows_sql_aprovado = mysql_num_rows($sql_aprovado);

$val_aprovado = $row_sql_aprovado['val_aprovado'];

echo '  <td class="numero">';
if ($val_aprovado != 0) { echo number_format($val_aprovado,2,",","."); }
echo '</td>';

/// Emprenho Até o mês    

mysql_select_db($database_codfor, $codfor);
$query_sql_empenho_ate_mes = "SELECT SUM(empenho.val_empenho) as val_empenho_ate_mes FROM empenho, categoria WHERE categoria.seq_categoria = empenho.seq_categoria AND categoria.num_categoria LIKE  '".$categoria_final."%' AND MONTH(empenho.dt_empenho) < MONTH('".$ano."-".$mes."-01') AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') GROUP BY categoria.seq_categoria";
$sql_empenho_ate_mes = mysql_query($query_sql_empenho_ate_mes, $codfor) or die(mysql_error());
$row_sql_empenho_ate_mes = mysql_fetch_assoc($sql_empenho_ate_mes);
$totalRows_sql_empenho_ate_mes = mysql_num_rows($sql_empenho_ate_mes);

$val_empenho_ate_mes = $row_sql_empenho_ate_mes['val_empenho_ate_mes'];

echo '  <td class="numero">';
if ($val_empenho_ate_mes != 0) { echo number_format($val_empenho_ate_mes,2,",","."); }
echo '</td>';

/// Emprenho No mês 
mysql_select_db($database_codfor, $codfor);
$query_sql_empenho_no_mes = "SELECT SUM(empenho.val_empenho) as val_empenho_no_mes FROM empenho, categoria WHERE categoria.seq_categoria = empenho.seq_categoria AND categoria.num_categoria LIKE  '".$categoria_final."%' AND MONTH(empenho.dt_empenho) = MONTH('".$ano."-".$mes."-01') AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') GROUP BY categoria.seq_categoria";
$sql_empenho_no_mes = mysql_query($query_sql_empenho_no_mes, $codfor) or die(mysql_error());
$row_sql_empenho_no_mes = mysql_fetch_assoc($sql_empenho_no_mes);
$totalRows_sql_empenho_no_mes = mysql_num_rows($sql_empenho_no_mes);

$val_empenho_no_mes = $row_sql_empenho_no_mes['val_empenho_no_mes'];

echo '  <td class="numero">';
if ($val_empenho_no_mes != 0) { echo number_format($val_empenho_no_mes,2,",","."); }
echo '</td>';


/// Emprenho No Ano 
mysql_select_db($database_codfor, $codfor);
$query_sql_empenho_no_ano = "SELECT SUM(empenho.val_empenho) as val_empenho_no_ano FROM empenho, categoria WHERE categoria.seq_categoria = empenho.seq_categoria AND categoria.num_categoria LIKE  '".$categoria_final."%' AND YEAR(empenho.dt_empenho) = YEAR('".$ano."-01-01') GROUP BY categoria.seq_categoria";
$sql_empenho_no_ano = mysql_query($query_sql_empenho_no_ano, $codfor) or die(mysql_error());
$row_sql_empenho_no_ano = mysql_fetch_assoc($sql_empenho_no_ano);
$totalRows_sql_empenho_no_ano = mysql_num_rows($sql_empenho_no_ano);

$val_empenho_no_ano = $row_sql_empenho_no_ano['val_empenho_no_ano'];

echo '  <td class="numero">';
if ($val_empenho_no_ano != 0) { echo number_format($val_empenho_no_ano,2,",","."); }
echo '</td>';


$saldo_corrente = $val_aprovado - $val_empenho_no_ano ;   
echo '</td>';
echo '  <td class="numero">'.number_format($saldo_corrente,2,",",".").'</td>';
echo '</tr>';

}
while ($row_sql_categorias = mysql_fetch_assoc($sql_categorias));
?>

0

Man, I think I understand your problem, I do something like this here, maybe it can help you.

First I take the values with a while, then I use an array. You can do the same thing with your select, assign each sum value to a variable and do the test if it is null or not and can by an echo to show the values you pass. I believe it will solve your problem/

while (($array = oci_fetch_array($stmt, OCI_BOTH)) != false) {

        $nroempresa = $array["NROEMPRESA"];
        $nomereduzido = $array["NOMEREDUZIDO"];
        $porcentagem = $array["PORCENTAGEM"];

  }      

        $mediaQualidade = $porcentagem; //os outros medias são iguais...



    $aux = array ($mediaQualidade ,$mediaSupervisores ,$mediaProcessos,$mediaDpp);//aqui eu pego os valores que filtrei com o select
    $valores = array();

 //aqui você procura pelos valores nulos e percorre cada posição do vetor
     for($i=0 ; $i < count($aux);$i++)
                                  {
   //Nesse IF você pega apenas os valores que não são NULL ou seja 0
     if($aux[$i] != 0)
     {
          $valores[$i] = $aux[$i]; // VOCÊ PODE POR UM ECHO AQUI, ELE VAI LISTAR OS VALORES QUE PASSAM.
     }
  }
 //A partir dai basta você utilizar a função array_sum que vai somar todos os itens do array $valores e dividir por 4
 $divisor = count ($valores); // essa parte você não utiliza :)
 $mediaTotal = ((array_sum($valores))/$divisor) ; // aqui a soma que você precisa com os valores usados.

Browser other questions tagged php mysql select report hierarchical-data

You are not signed in. Login or sign up in order to post.