How to SUM Quantity from database with GROUP BY function using php
Posted: 25 Feb 2016, 09:07
Code: Select all
<?php
mysql_connect('localhost', 'root');
mysql_select_db('inventory');
if(isset($_GET['from_date']) && isset($_GET['to_date']) && $_GET['from_date']!='' && $_GET['to_date']!='')
{
error_reporting (E_ALL ^ E_NOTICE);
$selected_date=$_GET['from_date'];
$selected_date=strtotime( $selected_date );
$mysqldate = date( 'Y-m-d H:i:s', $selected_date );
$fromdate=$mysqldate;
$selected_date=$_GET['to_date'];
$selected_date=strtotime( $selected_date );
$mysqldate = date( 'Y-m-d H:i:s', $selected_date );
$todate=$mysqldate;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Inventory Report</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<style type="text/css" media="print">
.hide{display:none}
</style>
<script type="text/javascript">
function printpage() {
document.getElementById('printButton').style.visibility="hidden";
window.print();
document.getElementById('printButton').style.visibility="visible";
}
</script>
<body>
<input name="print" type="button" value="Print" id="printButton" onClick="printpage()">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center">
<img src="images/logo.jpg" width="80px" align="left" height="80px">
<div align="center">
<h1 style="font-family: Arial; font-size: 25px; margin-top: 2px;">LM Cereal Corporation (LMCC)</h1>
<p style="font-family: Arial; font-size: 12px; margin-top: 2px;">First Tondo Complex 2150 Velasquez St., Brgy. 94 Zone 08 Dist. I Tondo Manila<br>
Reg. TIN: 004-516-647-000<br>
Tel: 255-2225 Fax: 255-6565</p>
</div>
<tr>
<?php
$selecteddate = mysql_query("SELECT YEAR( `cr_date` ) AS YEAR
FROM orders WHERE `cr_date` BETWEEN '$fromdate' AND '$todate' GROUP BY YEAR");
$row = mysql_fetch_array($selecteddate);
?>
<td align="center"><strong><?php echo $row['YEAR'] ?> Inventory Report</strong></td>
</tr>
<table border="1" align="center" width="100%" cellspacing="0" cellpadding="0">
<thead align="center" style="font-weight: bold;">
<th>Items</th>
<th>Total in</th>
<?php
$query = "SELECT DISTINCT DATE_FORMAT( cr_date, '%b' ) AS MONTH FROM orders WHERE cr_date BETWEEN '$fromdate' AND '$todate' ";
$queryMonth = mysql_query($query) or die(mysql_error());
?>
<?php while ($rowD = mysql_fetch_array($queryMonth )) { ?>
<th><?php echo $rowD['MONTH']; ?></th><?php } ?>
<th>Total out</th>
<th>Balance</th>
</thead>
<tbody style="font-family: Verdana; font-size:11px;">
<?php
$viewProd = "SELECT prod_mixcode FROM products";
$queryProd = mysql_query($viewProd) or die(mysql_error());
?>
<?php while ($rowP = mysql_fetch_array($queryProd )) { ?>
<tr>
<td><?php echo $rowP['prod_mixcode']; ?></td>
<?php
$products=$rowP['prod_mixcode'];
$viewTqty = "SELECT SUM(prod_qty) as totalQty FROM dr_manila WHERE prod_mixcode='$products' AND `dr_date` BETWEEN '$fromdate' AND '$todate'";
$queryTqty = mysql_query($viewTqty) or die(mysql_error());
?>
<?php while ($rows = mysql_fetch_array($queryTqty )) { ?>
<td><?php echo $rows['totalQty']; ?></td><?php } ?>
<?php
$sql_qtypermonth ="SELECT SUM( `prod_qty` ) AS totalQtybymonth, MONTHNAME( `dr_date` ) AS
MONTH FROM `dr_manila` WHERE `dr_date` BETWEEN '$fromdate' AND '$todate' GROUP BY MONTH ";
$queryqtypermonth = mysql_query($sql_qtypermonth) or die(mysql_error());
?>
<?php while ($row = mysql_fetch_array($queryqtypermonth )) { ?>
<td><?php echo $row['totalQtybymonth']; ?></td>
<td><?php echo $row['totalQtybymonth']-$row['totalQtybymonth']; ?></td>
<?php } ?><?php } ?>
<?php
/** start get the sum **/
$sql_order = mysql_query("SELECT prod_mixcode, sum(prod_qty), MONTHNAME( `cr_date` ) AS MONTH FROM orders WHERE cr_date >= '$fromdate' AND cr_date <= '$todate'");
$data_order = mysql_fetch_array($sql_order);
echo $data_order[0];
echo $data_order[1];
echo $data_order[2];
/** end get the sum **/
?>
<?php
function getSumQtybyDateFromOrders($startDate,$endDate){
$sql_qtybydate = mysql_query("SELECT MONTHNAME( `cr_date` ) AS MONTH FROM `orders` WHERE `cr_date` BETWEEN '$startDate' AND '$endDate'");
$data_qtybydate = mysql_fetch_array($sql_qtybydate);
echo $data_qtybydate['MONTH'];
}
getSumQtybyDateFromOrders($fromdate,$todate);
?>
</tr>
<tbody>
</table>
</td>
</tr>
</table>
</body>
<footer>
<p align="center"> Developed By: <strong>A2P2 Systems</strong> </br>
Email:systemsa2p2@gmail.com</p>
</footer>
</html>
<?php
}
else{
echo "Please set from and to date to process inventory report";
}
?>
Monthly and Yearly inventory report..