Hi all.I found this code on forum.But dont know how to alter this to show the records datewise.I want to display sales and purchase for given period of time and show the balance quantity by deducting total sales from the total purchase.
Here is my code:
<?PHP
if(isset($_POST['Submit']))
{
//if isset
$to = $_POST['to'];
$from = $_POST['from'];
$sql = "SELECT
sales_dy.item_id, sales_dy.qty as sales_quantity, item_name.name, sales.inv_date
FROM sales_dy
INNER JOIN item_name ON sales_dy.item_id=item_name.item_id
INNER JOIN sales ON sales.number=sales_dy.number
WHERE sales.inv_date BETWEEN '".$to."' AND '".$from."'
ORDER BY sales.inv_date";
$sales = mysql_query($sql) or die(mysql_error());
$sql = "SELECT
purchase_dy.item_id, purchase_dy.qty as purchase_quantity, item_name.name, purchase.inv_date
FROM purchase_dy
INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id
INNER JOIN purchase ON purchase.number=purchase_dy.number
WHERE purchase.inv_date BETWEEN '".$to."' AND '".$from."'
ORDER BY purchase.inv_date";
$purchase = mysql_query($sql) or die(mysql_error());
//query to fetch total purchase quantity so as to be used to calculate total balance available
$sql = "SELECT
item_id,
SUM(qty) as total_purchase_quantity
FROM
purchase_dy
GROUP BY
item_id";
$total_purchase = mysql_query($sql) or die(mysql_error());
//query to fetch total sales quantity so as to be used to calculate total balance available
$sql = "SELECT
item_id,
SUM(qty) as total_sales_quantity
FROM
sales_dy
GROUP BY
item_id";
$total_sale = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($sales) > 0 || mysql_num_rows($purchase) > 0)
{
//if num rows
echo '<div align="center">
<table border="0" width="40%">
<tr>
<th scope="col">Date</th>
<th scope="col">Name</th>
<th scope="col">Purchase</th>
<th scope="col">Sales</th>
<th scope="col">Balance</th>
</tr>';
for($i = 0; $i < mysql_num_rows($purchase); $i++){
mysql_field_seek($sales, $i);
mysql_field_seek($purchase, $i);
mysql_field_seek($total_purchase, $i);
mysql_field_seek($total_sale, $i);
$sales_qty = mysql_fetch_array($sales);
$purchase_qty = mysql_fetch_array($purchase);
$total_sales_qty = mysql_fetch_array($total_sale);
$total_purchase_qty = mysql_fetch_array($total_purchase);
$balance = $total_purchase_qty['total_purchase_quantity'] - $total_sales_qty['total_sales_quantity'];
if(empty($sales_qty['inv_date'])){
$inv_date=(date('d-m-Y', strtotime($purchase_qty['inv_date'])));
}
else{
$inv_date=(date('d-m-Y', strtotime($sales_qty['inv_date'])));
}
echo '<tr>
<td>' . $inv_date. '</td>
<td>' . $purchase_qty['name'] . '</td>
<td>' . $purchase_qty['purchase_quantity'] . '</td>
<td>' . $sales_qty['sales_quantity'] . '</td>
<td>' . $balance . '</td>
</tr>';
}
echo "</table>
</div>";
}//if num rows
else
{ //else num rows
echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";
} //else num rows
} //if isset
?>It displays result as follows:
Date Purchase Sales Balance
23-12-2011 10 1 13
25-12-2011 10 1 9
25-12-2011 5 1 -1
Where it has to show balance 9 on 23-12-2011 and 13 on 25-12-2011.Please tell me where I am going wrong?Is this possible through query?