Skip to content
Go to file
Cannot retrieve contributors at this time
145 lines (131 sloc) 7.97 KB
<link rel="stylesheet" href="../style.css">
<div id="header">
<?php writeMenuAdm("Reports.php"); ?>
<div id='contentwrapper'>
if (isset($_GET['Category'])){ //Check if category has already been set
$query = "SELECT p.ProductName, cat.CategoryName, cp.CompanyName as `Supplier's Name`, c.CompanyName as `Customer's Name`,
od.UnitPrice * od.Quantity as `Dollar Value`, od.UnitPrice, od.Quantity, o.OrderDate, p.UnitsInStock,
c.Address as `Customer's City`, c.Region as `Customer's Region`, c.Country as `Customer's Country`,
cp.Address as `Supplier's City`, cp.Region as `Supplier's Region`, cp.Country as `Supplier's Country`
FROM ((((((((orders o LEFT JOIN `Order Details` od ON o.OrderID = od.OrderID)
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID)
LEFT JOIN shipments sh ON o.OrderID = sh.OrderID)
LEFT JOIN shipaddresses sa ON sh.ShipmentsID = sa.ShipAddrID)
LEFT JOIN Products p ON od.ProductID = p.ProductID)
LEFT JOIN Suppliers s ON p.SupplierID = s.SupplierID)
LEFT JOIN Company cp ON s.CompanyID = cp.CompanyID)
LEFT JOIN Categories cat ON p.CategoryID = cat.CategoryID)
WHERE cat.CategoryID = ?
AND p.ProductName LIKE ?
AND cp.CompanyName LIKE ?
AND ? <= p.UnitsInStock AND p.UnitsInStock <= ?
AND ? <= o.OrderDate <= ?
AND c.CompanyName LIKE ?
AND c.Address LIKE ?
AND c.Region LIKE ?
AND c.Country LIKE ?
AND cp.Address LIKE ?
AND cp.Region LIKE ?
AND cp.Country LIKE ?
" ; //'?' in place of variable
$ProductName = "%" . $_GET['ProductName'] . "%";
$CustomerName = "%" . $_GET['CustomerName'] . "%";
$CustomerAddress = "%" . $_GET['CustomerAddress'] . "%";
$CustomerRegion = "%" . $_GET['CustomerRegion'] . "%";
$CustomerCountry = "%" . $_GET['CustomerCountry'] . "%";
$SupplierName = "%" . $_GET['SupplierName'] . "%";
$SupplierAddress = "%" . $_GET['SupplierAddress'] . "%";
$SupplierRegion = "%" . $_GET['SupplierRegion'] . "%";
$SupplierCountry = "%" . $_GET['SupplierCountry'] . "%";
//stock ranges
if (empty($_GET["InStockStart"])){
$InStockStart = 0;
$InStockStart = $_GET['InStockStart'];
if (empty($_GET["InStockEnd"])){
$InStockEnd = 102984573293485490;
$InStockEnd = $_GET['InStockEnd'];
//to and from duration
if (empty($_GET["DurTo"])){
$DurTo = "9999-12-12";
$DurTo = $_GET['DurTo'];
if (empty($_GET["DurFrom"])){
$DurFrom = "0000-01-01";
$DurFrom = $_GET['DurFrom'];
$stmt = $con->prepare($query);
$stmt ->bind_param("dssddsssssssss", $_GET['Category'], $SupplierName, $ProductName, $InStockStart,
$InStockEnd, $DurFrom, $DurTo, $CustomerName, $CustomerAddress, $CustomerRegion,
$CustomerCountry, $SupplierAddress, $SupplierRegion, $SupplierCountry
); //Bind category to query, category is taken in as CategoryID, so it's a digit
$stmt ->execute();
$searchResult = $stmt->get_result(); //Get results
makeTable($searchResult); //Make table from results, makeTable code is in commonFunctions.php
}else{ //If category has not been set, show HTML form to pick category
<form action = "Report_Sales.php" method = "get">
<h1>Sales Report</h1>
<div class="form-group">
<label>Select Product Category</label>
<select name="Category" id="Category">
<?php //Populate category drop down by getting categories from DB
$query ="SELECT * FROM categories"; //Query
$stmt = $con->prepare($query); //'con' is name of DB connection, defined in dbCon.php (which is loaded at the top of this file)
$stmt ->execute(); //Run query
$result = $stmt->get_result(); //Put query results into $results array
while($row = mysqli_fetch_array($result)) { //Iterate through each row of results
echo "<option value='" . $row['CategoryID'] . "'>" . $row['CategoryName'] . "</option>"; //'echo' outputs the given line to the HTML, so this creates a new row in the drop-down for each category
<label>Product Name</label>
<input type="text" name="ProductName" id="ProductName">
<label>Supplier's Name</label>
<input type="text" name="SupplierName" id="SupplierName">
<label>In Stock: Start</label>
<input type="text" name="InStockStart" id="InStockStart">
<label>In Stock: End</label>
<input type="text" name="InStockEnd" id="InStockEnd">
<label>Duration Choices: To:</label>
<input type="text" name="DurTo" id="DurTo">
<label>Duration Choices: From:</label>
<input type="text" name="DurFrom" id="DurFrom">
<label>Customer Name:</label>
<input type="text" name="CustomerName" id="CustomerName">
<label>Customer's City:</label>
<input type="text" name="CustomerAddress" id="CustomerAddress">
<label>Customer's State:</label>
<input type="text" name="CustomerRegion" id="CustomerRegion">
<label>Customer's Country:</label>
<input type="text" name="CustomerCountry" id="CustomerCountry">
<label>Supplier's City:</label>
<input type="text" name="SupplierAddress" id="SupplierAddress">
<label>Supplier's State:</label>
<input type="text" name="SupplierRegion" id="SupplierRegion">
<label>Supplier's Country:</label>
<input type="text" name="SupplierCountry" id="SupplierCountry">
<input type="submit" value="View Report">
} //Close bracket from 'else' above
You can’t perform that action at this time.