[eluser]akien[/eluser]
This is my code:
Code:
<?php
// db connection
$db = "mds_reports";
if($connect = mysql_connect("172.16.8.32", "mds_reports", "password"))
$connect = mysql_select_db($db);
else die("Unable to connect".mysql_error());
$date_from = $_POST['dfrom'];
$date_to = $_POST['dto'];
$name = $_POST['name'];
?>
<html>
<h1> PMIX Quantity Sold base on Category 2 </h1>
<title>PMIX!</title>
<body>
<form name="PMIX" method="POST" action="PMIX.php">
Date From <input type="text" name="dfrom" id="dfrom">
To <input type="text" name="dto" id="dto"><br /><br />
Restaurant Name <input type="text" name="name" id="name">
<input type="submit" name="submit" value="submit">
</form>
<table border="1">
<tr>
<th>Restaurant Code</th>
<th>Restaurant Name</th>
<th>Chicken Alc</th>
<th>Chicken Evm</th>
<th>Burger Alc</th>
<th>Burger Evm</th>
<th>Breakfast Alc</th>
<th>Breakfast Evm</th>
<th>Desserts</th>
<th>Fries</th>
<th>Evrdy McSavers</th>
<th>Happy Meal</th>
<th>Beverages</th>
<th>Breakfast_Ba_P50</th>
<th>Breakfast_Ba</th>
<th>McSavers_P50</th>
<th>Extras</th>
<th>Upsize</th>
</tr>
<?php
if(isset($_POST['submit'])){
$sql = "SELECT restaurant_master.code, restaurant_master.name,
SUM(CASE WHEN ItemMaster.Category2 = 'CHICKEN ALC'
THEN OrderItemHistory.Quantity END) AS CHICKEN_ALC,
SUM(CASE WHEN ItemMaster.Category2 = 'CHICKEN EVM'
THEN OrderItemHistory.Quantity END) AS CHICKEN_EVM,
SUM(CASE WHEN ItemMaster.Category2 = 'BURGER ALC'
THEN OrderItemHistory.Quantity END) AS BURGER_ALC,
SUM(CASE WHEN ItemMaster.Category2 = 'BURGER EVM'
THEN OrderItemHistory.Quantity END) AS BURGER_EVM,
SUM(CASE WHEN ItemMaster.Category2 = 'BREAKFAST ALC'
THEN OrderItemHistory.Quantity END) AS BREAKFAST_ALC,
SUM(CASE WHEN ItemMaster.Category2 = 'BREAKFAST EVM'
THEN OrderItemHistory.Quantity END) AS BREAKFAST_EVM,
SUM(CASE WHEN ItemMaster.Category2 = 'DESSERTS'
THEN OrderItemHistory.Quantity END) AS DESSERTS,
SUM(CASE WHEN ItemMaster.Category2 = 'FRIES'
THEN OrderItemHistory.Quantity END) AS FRIES,
SUM(CASE WHEN ItemMaster.Category2 = 'EVRDY MCSAVERS'
THEN OrderItemHistory.Quantity END) AS EVRDY_MCSAVERS,
SUM(CASE WHEN ItemMaster.Category2 = 'HAPPY MEAL'
THEN OrderItemHistory.Quantity END) AS HAPPY_MEAL,
SUM(CASE WHEN ItemMaster.Category2 = 'BEVERAGES'
THEN OrderItemHistory.Quantity END) AS BEVERAGES,
SUM(CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA P50'
THEN OrderItemHistory.Quantity END) AS BREAKFAST_BA_P50,
SUM(CASE WHEN ItemMaster.Category2 = 'BREAKFAST BA'
THEN OrderItemHistory.Quantity END) AS BREAKFAST_BA,
SUM(CASE WHEN ItemMaster.Category2 = 'MCSAVERS P50'
THEN OrderItemHistory.Quantity END) AS MCSAVERS_P50,
SUM(CASE WHEN ItemMaster.Category2 = 'EXTRAS'
THEN OrderItemHistory.Quantity END) AS EXTRAS,
SUM(CASE WHEN ItemMaster.Category2 = 'UPSIZE'
THEN OrderItemHistory.Quantity END) AS UPSIZE
FROM mds_orders
INNER JOIN OrderItemHistory
ON mds_orders.PKID = OrderItemHistory.OrderFKID
INNER JOIN ItemMaster
ON OrderItemHistory.ItemFKID = ItemMaster.PKID
INNER JOIN restaurant_master
ON mds_orders.RestaurantID = restaurant_master.pkid
WHERE mds_orders.OrderDate BETWEEN '".$date_from." 00:00:00' AND '".$date_to." 23:59:59'
AND restaurant_master.name = '$name'
GROUP BY restaurant_master.code, restaurant_master.name";
echo "Date selected: ".$date_from." - ".$date_to."</p>";
echo "Restaurant name: ".$name."</p>";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
echo "The number of rows is ".$num_rows."</p>";
$i = 0;
if (mysql_num_rows($result) >0) {
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)){
$csv .= $row['code'].",".
$row['name'].",".
$row['CHICKEN_ALC'].",".
$row['CHICKEN_EVM'].",".
$row['BURGER_ALC'].",".
$row['BURGER_EVM'].",".
$row['BREAKFAST_ALC'].",".
$row['BREAKFAST_EVM'].",".
$row['DESSERTS'].",".
$row['FRIES'].",".
$row['EVRDY_MCSAVERS'].",".
$row['HAPPY_MEAL'].",".
$row['BEVERAGES'].",".
$row['BREAKFAST_BA_P50'].",".
$row['BREAKFAST_BA'].",".
$row['MCSAVERS_P50'].",".
$row['EXTRAS'].",".
$row['UPSIZE'].","."<br>";
?>
<tr>
<td><?php echo $row['code'];?></td>
<td><?php echo $row['name'];?></td>
<td><?php echo $row['CHICKEN_ALC'];?></td>
<td><?php echo $row['CHICKEN_EVM'];?></td>
<td><?php echo $row['BURGER_ALC'];?></td>
<td><?php echo $row['BURGER_EVM'];?></td>
<td><?php echo $row['BREAKFAST_ALC'];?></td>
<td><?php echo $row['BREAKFAST_EVM'];?></td>
<td><?php echo $row['DESSERTS'];?></td>
<td><?php echo $row['FRIES'];?></td>
<td><?php echo $row['EVRDY_MCSAVERS'];?></td>
<td><?php echo $row['HAPPY_MEAL'];?></td>
<td><?php echo $row['BEVERAGES'];?></td>
<td><?php echo $row['BREAKFAST_BA_P50'];?></td>
<td><?php echo $row['BREAKFAST_BA'];?></td>
<td><?php echo $row['MCSAVERS_P50'];?></td>
<td><?php echo $row['EXTRAS'];?></td>
<td><?php echo $row['UPSIZE'];?></td>
<tr>
<?php
}}}
?>
</table>
<form name="extract" method="POST" action="export_PMIX.php">
<input type="hidden" name="export_PMIX_dfrom" id="data" value="<?=$date_from;?>">
<input type="hidden" name="export_PMIX_dto" id="data" value="<?=$date_to;?>">
<input type="hidden" name="export_PMIX_name" id="data" value="<?=$name;?>">
<input type="submit" name="extract" value="extract">
</form>
</body>
</html>
