Page 1 of 1

How to SUM Quantity from database with GROUP BY function using php

Posted: 25 Feb 2016, 09:07
by frecia18

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 &nbsp; 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";
}
?>
HELP ME Please :) Monthly and Yearly inventory report..