?
Solved

Report grouped by days and months

Posted on 2011-10-31
13
Medium Priority
?
203 Views
Last Modified: 2012-05-12
Good personal days,

Damn I have a question and do not know how to solve!

Well need to build a report that is grouped by day and month must first be grouped by day with calculation of all amounts due in one day. Example I present all data from 10.1 and somo days after the end of the day and somo 02/10 everything in the end and finally have the sum total of the month. Here my script below:


<?php

$a = mysql_connect("127.0.0.1","root","");
$b = mysql_select_db("erp2");

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style6 {font-family: Arial; font-size: 12px; font-weight: bold; }
-->
</style>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="11%" align="center"><span class="style6">Mes/ano</span></td>
    <td width="19%" align="center"><span class="style6">Valor do doc.</span></td>
    <td width="25%" align="center"><span class="style6">Cliente</span></td>
    <td width="45%" align="center"><span class="style6">Vencimento</span></td>
  </tr>
</table>
<?php



$sql = mysql_query("SELECT DAY(VENCIMENTO) as DIA FROM conta group by DAY(VENCIMENTO)");
$valor_total = 0;
while($resposta = mysql_fetch_array($sql)){

        $sql2 = mysql_query("SELECT MONTH(VENCIMENTO) AS MES, YEAR(VENCIMENTO) AS ANO, VENCIMENTO, VALOR_DOC, CLEINTE_FORNECEDOR 
                                                            FROM conta WHERE DAY(VENCIMENTO) = '".$resposta['DIA']."' ORDER BY VENCIMENTO ASC");
        
                while($rec = mysql_fetch_array($sql2)){
?>
<table width="100%" border="0" cellpadding="0" cellspacing="0" >
  <tr>
    <td width="11%" align="center"><?php echo $rec['MES']."/".$rec['ANO']; ?></td>
    <td width="19%" align="center"><?php echo $rec['VALOR_DOC']; $valor_total += $rec['VALOR_DOC']; ?></td>
    <td width="25%" align="center"><?php echo utf8_encode($rec['CLEINTE_FORNECEDOR']); ?></td>
    <td width="45%" align="center"><?php echo $rec['VENCIMENTO']; ?></td>
  </tr>
</table>
<p>
  <?php

        }
        
        echo '<table width="63%" border="0" cellspacing="0" cellpadding="0" bgcolor="#969696">
                  <tr>
                        <td width="17%" align="center">&nbsp;</td>
                        <td width="30%" align="center" class="style6" >TOTAL:&nbsp;&nbsp;'.$valor_total.'</td>
                        <td width="53%" align="center">&nbsp;</td>
                  </tr>
                </table>';
}

?>
</p>
<p>&nbsp; </p>
</body>
</html>

Open in new window

0
Comment
Question by:eduardo12fox
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 10

Expert Comment

by:ienaxxx
ID: 37056134
Do you want to obtain Monthly amount due GrandTotal or the GrandTotal of the amount due in THAT PARTICULAR day of the months?

i mean: you want the month grandtotal or the total of each 27th day of all months of a particular year?

Cause your code seems to be structured to obtain data for the second case...
 It's important to know befor start coding.
0
 

Author Comment

by:eduardo12fox
ID: 37056190
I need the monthly amount due grandtotal
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 37056403
which field you need to get sum
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37056521
Your instincts are correct to SELECT all the rows.  You might find it easier to do the summations in PHP as you retrieve the rows.  This is just pidgin code but hopefully it will illustrate the idea.  The WHERE clause might be used to limit the selections to a single month.

$sql = "SELECT my_date, my_amount FROM my_table ORDER BY my_date";
$res = mysql_query($sql);
$daily_total = 0;
$monthly_total = 0;
$current = "?"
while ($row = mysql_fetch_assoc($res))
{
    if ($my_date != $current) // START A NEW DAY
    {
        echo $daily_total;
        $current = $my_date;
        $daily_total = 0;
    }
    // ADD THE AMOUNTS TO THE DAILY AND MONTHLY TOTALS
    $daily_total = $daily_total + $my_amount;
    $monthly_total = $monthly_total + $my_amount;
}
// LAST ROW WAS SELECTED
echo $daily_total;
echo $monthly_total;
0
 
LVL 10

Expert Comment

by:ienaxxx
ID: 37056794
here is the code:

<?php

$a = mysql_connect("127.0.0.1","root","");
$b = mysql_select_db("erp2");

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style6 {font-family: Arial; font-size: 12px; font-weight: bold; }
-->
</style>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="11%" align="center"><span class="style6">Mes/ano</span></td>
    <td width="19%" align="center"><span class="style6">Valor do doc.</span></td>
    <td width="25%" align="center"><span class="style6">Cliente</span></td>
    <td width="45%" align="center"><span class="style6">Vencimento</span></td>
  </tr>

<?php
$sql = mysql_query("SELECT DAY(VENCIMENTO) AS DIA, MONTH(VENCIMENTO) AS MES, YEAR(VENCIMENTO) AS ANO, VENCIMENTO, SUM(VALOR_DOC) as TOTALE_DIA, CLEINTE_FORNECEDOR 
                                                          FROM conta GROUP BY ANO, MES, DIA ORDER BY VENCIMENTO ASC ");


$data = array();
															
while($resposta = mysql_fetch_array($sql)){
	global $data;
	$data[$resposta["MES"]]["TOTALE_MES"]+=floatval($resposta["TOTALE_DIA"]);
	$data[$resposta["MES"]]["ANO"]=$resposta["ANO"];
	$data[$resposta["MES"]][$resposta["DIA"]]["TOTALE_DIA"]=floatval($resposta["TOTALE_DIA"]);
	$data[$resposta["MES"]][$resposta["DIA"]]["VENCIMENTO"]=$resposta["VENCIMENTO"];
	$data[$resposta["MES"]][$resposta["DIA"]]["CLEINTE_FORNECEDOR"]=$resposta["CLEINTE_FORNECEDOR"];
	
}														
	
foreach($data as $mes=>$arr_mes){
	$toOutPut.="
	<tr style=\"background-color: #ddd\">
    <td width=\"11%\" align=\"center\">".$arr_mes["ANO"]."-".$mes."</td>
    <td width=\"19%\" align=\"center\">totale mes: ".$arr_mes["TOTALE_MES"]."</td>
    <td width=\"25%\" align=\"center\">N/A</td>
    <td width=\"45%\" align=\"center\">N/A</td>
	</tr>";
	foreach($arr_mes as $dia=>$arr_dia){
		if(is_array($arr_dia)){
			$toOutPut.="
	<tr style=\"background-color: #fff\">
    <td width=\"11%\" align=\"center\">".$arr_mes["ANO"]."-".$mes."-".$dia."</td>
    <td width=\"19%\" align=\"center\">".$arr_dia["TOTALE_DIA"]."</td>
    <td width=\"25%\" align=\"center\">".utf8_encode($arr_dia["CLEINTE_FORNECEDOR"])."</td>
    <td width=\"45%\" align=\"center\">".$arr_dia["VENCIMENTO"]."</td>
	</tr>";
		
		}
	}

}
echo $toOutPut;
?>

</table>
<p>

</p>
<p>&nbsp; </p>
</body>
</html>

Open in new window



but keep in mind that if you have more than a document per day, the field CLEINTE_FORNECEDOR, could be wrong (i think it's picked from the first row of that day's result set).

here is the result using a query on a table in a database of mine:
 Result-view
0
 
LVL 10

Expert Comment

by:ienaxxx
ID: 37056829
... anyway, i think the problem on CLEINTE_FORNECEDOR could be solved changing

GROUP BY ANO, MES, DIA ORDER BY VENCIMENTO ASC

into

GROUP BY CLEINTE_FORNECEDOR, ANO, MES, DIA ORDER BY VENCIMENTO ASC

HTH.
Bye!
0
 

Author Comment

by:eduardo12fox
ID: 37057458
So boys,

I need to know how to get the month because I would like to insert a line like the daily separations but a separation from month to month grandtotal bringing the number of months and each day
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37057474
How to "get" the month?  You can either SELECT it or you can use the PHP date() function.

This article tells a bit about how DATETIME values work in PHP and MySQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
LVL 10

Expert Comment

by:ienaxxx
ID: 37057486
i can't understand.
Sorry
0
 

Author Comment

by:eduardo12fox
ID: 37057627
Sorry, I should not be able to explain it. Let us by:
I need to build a report that is grouped by day giving me the total calculated daily after the closing per month with the total per month so my idea was to build something like this:


Value_doc              Client               maturity
5.68                        Paul                   2011-10-01
687.98                    Julie                   2011-10-01
45.78                      Richard              2011-10-01
________________________________________
739.41          total                    Day01

Day 02 Month 10 Year 2011

Value_doc              Client               maturity
5.68                        Paul                   2011-10-02
687.98                    Julie                   2011-10-02
45.78                      Richard              2011-10-02
________________________________________
739.41          total                    Day02

...

end of the month

________________________________________
1478.82          total  Month                   Month 10
0
 
LVL 10

Accepted Solution

by:
ienaxxx earned 2000 total points
ID: 37058050
Understood.

Here it go:
<?php

$a = mysql_connect("127.0.0.1","root","");
$b = mysql_select_db("erp2");

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style6 {font-family: Arial; font-size: 12px; font-weight: bold; }
-->
</style>
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="11%" align="center"><span class="style6">Mes/ano</span></td>
    <td width="19%" align="center"><span class="style6">Valor do doc.</span></td>
    <td width="25%" align="center"><span class="style6">Cliente</span></td>
    <td width="45%" align="center"><span class="style6">Vencimento</span></td>
  </tr>

<?php
$sql = mysql_query("SELECT DAY(VENCIMENTO) AS DIA, MONTH(VENCIMENTO) AS MES, YEAR(VENCIMENTO) AS ANO, VENCIMENTO, VALOR_DOC as Valor_doc, CLEINTE_FORNECEDOR 
                                                          FROM conta ORDER BY VENCIMENTO ASC ");
;

$data = array();
															
while($resposta = mysql_fetch_array($sql)){
	$data[$resposta["ANO"]][$resposta["MES"]]["TOTALE_MES"]+=floatval($resposta["Valor_doc"]);
	$data[$resposta["ANO"]][$resposta["MES"]][$resposta["DIA"]]["TOTALE_DIA"]+=floatval($resposta["Valor_doc"]);
	$data[$resposta["ANO"]][$resposta["MES"]][$resposta["DIA"]][]["VALOR_DOC"]=$resposta["Valor_doc"];
	$data[$resposta["ANO"]][$resposta["MES"]][$resposta["DIA"]][]["CLEINTE_FORNECEDOR"]=$resposta["CLEINTE_FORNECEDOR"];
	$data[$resposta["ANO"]][$resposta["MES"]][$resposta["DIA"]][]["VENCIMENTO"]=$resposta["VENCIMENTO"];
	
}														

foreach($data as $ano=>$arr_ano){
foreach($arr_ano as $mes=>$arr_mes){
	
	foreach($arr_mes as $dia=>$arr_dia){
		if(is_array($arr_dia)){
			foreach($arr_dia as $doc=>$arr_doc){
			$toOutPut.="
	<tr style=\"background-color: #fff\">
    <td width=\"11%\" align=\"center\">".$ano."-".$mes."-".$dia."</td>
    <td width=\"19%\" align=\"center\">".$arr_doc["VALOR_DOC"]."</td>
    <td width=\"25%\" align=\"center\">".utf8_encode($arr_doc["CLEINTE_FORNECEDOR"])."</td>
    <td width=\"45%\" align=\"center\">".$arr_doc["VENCIMENTO"]."</td>
	</tr>";
			}
		
		}
		$toOutPut.="
	<tr style=\"background-color: #eee\">
    <td width=\"11%\" align=\"center\">Totale DIA</td>
    <td width=\"19%\" align=\"center\">totale dia: ".$arr_dia["TOTALE_DIA"]."</td>
    <td width=\"25%\" align=\"center\">N/A</td>
    <td width=\"45%\" align=\"center\">N/A</td>
	</tr>";
		
	}
	$toOutPut.="
	<tr style=\"background-color: #ddd\">
    <td width=\"11%\" align=\"center\">".$ano."-".$mes."</td>
    <td width=\"19%\" align=\"center\">totale mes: ".$arr_mes["TOTALE_MES"]."</td>
    <td width=\"25%\" align=\"center\">N/A</td>
    <td width=\"45%\" align=\"center\">N/A</td>
	</tr>";

}
}

echo $toOutPut;
?>

</table>
<p>

</p>
<p>&nbsp; </p>
</body>
</html>

Open in new window

0
 

Author Closing Comment

by:eduardo12fox
ID: 37058550
Yes!!!! VERY GOOD!

is exactly what I need! thank you
0
 
LVL 10

Expert Comment

by:ienaxxx
ID: 37068199
You are welcome.
Glad to be of help! :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question