<meta charset="utf-8">

<meta name="viewport" content="width=device-width initial-scale=1.0">

<meta http-equiv="X-UA-Compatible" content="IE=edge">

<meta name="description" content="Wholesale Manager Account">

<title>All Transactions</title>

<link rel="icon" type="image/png" href="images/caricon.png">

<link rel="stylesheet" type=text/css href="styles/main.css">

<link rel="stylesheet" type="text/css" href="styles/bootstrap4/bootstrap.min.css">

<link rel="stylesheet" type="text/css" href="styles/util.css">

<link rel="stylesheet" type="text/css" href="styles/style.css">

<link rel="stylesheet" type="text/css" href="styles/table.css">

<?php session_start(); ?>

<header>

<div id="logo"><a><img src="images/logo.png"></a></div>

<nav>

<ul>

<?php

if(isset($_SESSION['customer']))

{

echo '<li><a href="logout.php">Logout</a></li>';

echo '<li><a href="custAccount.html" style=text-decoration:none;>Welcome ' . $_SESSION['fname'] . '</a></li>';

} else if(isset($_SESSION['wsmanager']) || isset($_SESSION['ldmanager1']) || isset($_SESSION['ldmanager2']) || isset($_SESSION['salesrep1']) || isset($_SESSION['salesrep2']))

{

echo '<li><a href="logout.php">Logout</a></li>';

echo '<li><a href="empAccount.html" style=text-decoration:none;>Welcome ' . $_SESSION['fname'] . '</a></li>';

} else

{

echo '<li>';

echo '<a style=color:red;>Sign In</a>';

echo '<ul class="dropdown">';

echo '<li><a href="signIn.html">Customers</a></li>';

echo '<li><a href="empSignin.html">Employees</a></li>';

echo '</ul>';

echo '</li>';

echo '<li><a href="registerPage.html">Register</a></li>';

}

?>

<li><a>|</a></li>

<li><a href="tabVehicles.html">Vehicles</a></li>

<li><a href="tabPackages.html">Packages</a></li>

<li><a href="tabRebates.html">Rebates</a></li>

<li><a href="index.html">Home</a></li>

</ul>

</nav>

<div class="services">

<div class="container">

<div class="row">

<div class="col text-center">

<div class="section_title">View All Transactions</div>

<div class="section_subtitle">It's all in the tables</div>

</div>

</div>

</div>

</div>

</header>

<!-- Cars in the database for dealer -->

<?php

if(isset($_SESSION['wsmanager']))

{

$dbconnect = mysqli_connect("127.0.0.1", "root", "", "globalviews");

}

//Select everything from sales global view to display in table

$query = "SELECT * FROM sales";

$output = $dbconnect->query($query);

//Find the customer with most purchases to display and dealer with most sales

$query1 = "SELECT `customer_no`, COUNT(`customer_no`) AS `value_occurrence` FROM `sales` GROUP BY `customer_no` ORDER BY

`value_occurrence` DESC LIMIT 1";

$output1 = $dbconnect->query($query1);

$query2 = "SELECT `dealer`, COUNT(`dealer`) AS `value_occurrence` FROM `sales` GROUP BY `dealer` ORDER BY

`value_occurrence` DESC LIMIT 1";

$output2 = $dbconnect->query($query2);

if(!empty($output1) && !empty($output2))

{

while($result = mysqli_fetch_assoc($output1))

{

$cust_no = $result['customer_no'];

}

while($result = mysqli_fetch_assoc($output2))

{

$dealer = $result['dealer'];

}

}

//Find the top N most popular column input

if(isset($_POST['top']) && isset($_POST['column']))

{

$top = $_POST['top'];

$column = $_POST['column'];

$query3 = "SELECT `$column`, COUNT(`$column`) AS `value_occurrence`

FROM (SELECT sales.serial_no, purchased.model, purchased.color

FROM sales INNER JOIN purchased ON sales.serial_no = purchased.serial_no) AS T

GROUP BY `$column` ORDER BY `value_occurrence` DESC LIMIT $top";

$output3 = $dbconnect->query($query3);

$popular = "";

if(!empty($output3))

{

while($result = mysqli_fetch_assoc($output3))

{

if($column == "model")

{

$popular .= $result['model'] . " - ";

} else

{

$popular .= $result['color'] . " - ";

}

}

}

}

?>

<div class="limiter">

<div class="container-table100">

<div class="wrap-table100">

<?php if($output->num_rows != 0){ ?>

<span class="login100-form-title" style="text-align:center;padding:0px;">Dealership with most customers:

<?php echo $dealer?></span>

<span class="login100-form-title" style="text-align:center;">Customer with most purchases:

<?php echo "<a href = 'viewTrans.html?cust_no=$cust_no'>$cust_no</a>"?></span>

<form method="POST" style="text-align: center">

<div style="width:30%;display:inline-block" class="wrap-input100">

<input class="input100" type="text" name="top" maxlength="1" placeholder="Return top N popular" required>

<span class="focus-input100-1"></span>

<span class="focus-input100-2"></span>

</div>

<select style="width:30%;display:inline-block" class="wrap-input100" name="column" required>

<option value="" disabled selected>Select Column</option>

<option value="model">Model</option>

<option value="color">Color</option>

<span class="focus-input100-1"></span>

<span class="focus-input100-2"></span>

</select>

<input style="padding: 10px;" type="submit" name="submit" value="Submit" class="btn-submit">

</form>

<?php if(isset($output3)){ ?>

<br>

<span class="login100-form-title" style="text-align:center;"><?php echo " - " . $popular?>

</span>

<?php } ?>

<br>

<div class="table100 ver2 m-b-110">

<div class="table100-head">

<table>

<thead>

<tr>

<th class="cell100 column1">Dealer</th>

<th class="cell100 column2">Date</th>

<th class="cell100 column3">Transaction No</th>

<th class="cell100 column4">Representative No</th>

<th class="cell100 column5">Customer No</th>

<th class="cell100 column6">Serial No</th>

<th class="cell100 column7">Amount</th>

<th class="cell100 column8">Rebate No</th>

<th class="cell100 column9">Package No</th>

</tr>

</thead>

</table>

</div>

<div class="table100-body js-pscroll ps ps--active-y">

<table>

<tbody>

<?php while($result = mysqli_fetch_assoc($output)){ ?>

<tr>

<td class="cell100 column1"><?php echo $result['dealer']?></td>

<td class="cell100 column2"><?php echo $result['date']?></td>

<td class="cell100 column3"><?php echo $result['transaction_no']?></td>

<td class="cell100 column4"><?php echo $result['rep_no']?></td>

<td class="cell100 column5"><?php echo $result['customer_no']?></td>

<td class="cell100 column6"><?php echo $result['serial_no']?></td>

<td class="cell100 column7"><?php echo $result['amount']?></td>

<td class="cell100 column8"><?php echo $result['rebate_no']?></td>

<td class="cell100 column9"><?php echo $result['package_no']?></td>

</tr>

<?php } ?>

</tbody>

</table>

</div>

</div>

<?php } else { ?>

<section>

<h1 id="page_title">No Vehicles Available</h1>

</section>

<?php } ?>

</div>

</div>

</div>

<footer>

<h3>All Rights Reserved, Copyright &copy; 2020</h3>

</footer>