<?php
//die(print_r($_SESSION, true));
//error_reporting(E_ALL);
//echo "test";
$fromDate = $_REQUEST['fromDate'];
$toDate = $_REQUEST['toDate'];
$city = $_REQUEST['city'];
?>


 <!DOCTYPE html>
 <html lang="en">
   <head>
     <meta charset="utf-8">
     <meta http-equiv="X-UA-Compatible" content="IE=edge">
     <!--<meta name="viewport" content="width=device-width, initial-scale=1">-->
     <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
     <title><?= $config['CompanyName'] ?></title>
 
     <!-- Bootstrap -->
     <link href="../css/bootstrap.min.css" rel="stylesheet">
     <link rel="stylesheet" href="../css/bootstrap-combobox.css" />
 
     <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
     <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
     <!--[if lt IE 9]>
       <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
       <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
       <script src="../js/functions.js"></script>
       <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
       <script src="../js/functions.js"></script>
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>
           <!-- Include all compiled plugins (below), or include individual files as needed -->
           <script src="../js/bootstrap.min.js"></script>
       	 <script src="../js/bootstrap-combobox.js"></script>
       	 <script src="../js/excellentexport.js"></script>
				 <!-- Include the plugin's CSS and JS: -->
				<script type="text/javascript" src="../js/bootstrap-multiselect.js"></script>
				<link rel="stylesheet" href="../css/bootstrap-multiselect.css" type="text/css"/>
           <script type="text/javascript">
             $(document).ready(function(){
             
             });
           </script>
					<link rel="stylesheet" href="../css/jquery.filthypillow.css" type="text/css" />
					<script src="../js/jquery.filthypillow.min.js" type="text/javascript"></script>
					<script src="../js/moment.js" type="text/javascript"></script>
     <![endif]-->
	<link rel="stylesheet" href="../css/styles.css"/>
   </head>
   <body>
<?php

	include("./navbar.php");
	
?>
        <br /><br />
   <!-- Stack the columns on mobile by making one full-width and the other half-width -->
    
	<div class="container" style="padding-top: 10px;">
		<link href="../css/fileinput.min.css" media="all" rel="stylesheet" type="text/css" />
		<script src="../js/fileinput.js" type="text/javascript"></script>
		
		<div class="panel panel-default">
			<div class="panel-heading form-inline">
				<a class="btn btn-success" role="button" style="float: right;" href="#" download="DeliverySummaryReport<?= "(" . date("m.d.Y") . ")" ?>.xls" href="#" onclick="return ExcellentExport.excel(this, 'DeliveryMethodCountTable', 'DeliveryMethodCountTable');" data-toggle="tooltip" data-placement="left" title="Does not work with Internet Explorer">Export To Excel</a>
				<div style="margin-right: 10px; float: right;">
					<div class="input-group">
						<div id="clearButton" style="display: none;" class="input-group-addon" onclick="clearFilter()">
							<span class="glyphicon glyphicon-remove" aria-hidden="true"></span>
						</div>
						<input type="text"   class="form-control" name="filter" placeholder="Filter" value="" />
						<div class="input-group-addon" onclick="filter()">
							<span class="glyphicon glyphicon-search" aria-hidden="true"></span>
						</div>
					</div>
					
				</div>
				<h3 class="panel-title">Delivery Summary Report - City: <?= $city=="%"?"ALL":$city ?><br>
				From: <?= date("m/d/Y", strtotime($fromDate)) ?> To: <?= date("m/d/Y", strtotime($toDate)) ?></h3>
				
				
			</div>
			<div class="panel-body">
				<table class="table table-hover" id="DeliveryMethodCountTable">
					<thead>
						<tr>
							<?php
							
							if($city == "%"){
								$arrayDeliveryMethods = array();
								$query = "SELECT DeliveryMethod FROM (ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID)) JOIN CUSTOMER USING(AccountNumber) WHERE DeleteOrder = 0 AND OrderDate BETWEEN '$fromDate' AND '$toDate 23:59' AND City LIKE '$city' GROUP BY DeliveryMethod";
								$result = mysqli_query($con, $query);
								$arrayTotals = array();
								$arrayTotals['Orders'] = 0;
								$arrayTotals['Items'] = 0;
								$arrayTotals['Amount'] = 0;
								?>
							<th style="width: 20%;">City</th>
							<?php
								$i = 1;
								while($row = mysqli_fetch_array($result)){
									$arrayTotals[$i] = 0;
									$i++;
									array_push($arrayDeliveryMethods, $row['DeliveryMethod']);
								?>
									<th style="width: 10%;"><?= $row['DeliveryMethod'] ?></th>
								<?php
									
								}
							?>
								<th style="width: 20%;">Order Count</th>
								<th style="width: 20%;">Item Count</th>
								<th style="width: 25%;">Order Totals</th>
							<?php
							}else{
							
							?>
							<th style="width: 35%;">Delivery Method</th>
							<th style="width: 20%;">Order Count</th>
							<th style="width: 20%;">Item Count</th>
							<th style="width: 25%;">Order Totals<small>(Incl. Fees)</small></th>
							<?php
							}
							?>
						</tr>
					</thead>
					<tbody>
						<?php
						
							if($city == "%"){
								$cols = implode("', OrderID,NULL))), COUNT(DISTINCT(IF(DeliveryMethod LIKE '", $arrayDeliveryMethods);
								$cols = "COUNT(DISTINCT(IF(DeliveryMethod LIKE '" . $cols . "', OrderID,NULL)))";
								//$query = "SELECT DeliveryMethod, COUNT(DISTINCT(ORDER_HEAD.OrderID)) AS TotalOrders, COUNT(OrderID) AS TotalItems, SUM(Price*OrderQty) AS TotalAmount FROM (ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID)) JOIN CUSTOMER USING(AccountNumber) WHERE DeleteOrder = 0 AND OrderDate BETWEEN '$fromDate' AND '$toDate 23:59' AND $city GROUP BY DeliveryMethod";
								$query = "SELECT City, $cols, COUNT(DISTINCT(ORDER_HEAD.OrderID)) AS TotalOrders, COUNT(OrderID) AS TotalItems, SUM(Price*OrderQty) AS TotalAmount FROM (ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID)) JOIN CUSTOMER USING(AccountNumber) WHERE DeleteOrder = 0 AND OrderDate BETWEEN '$fromDate' AND '$toDate 23:59' AND City != '' GROUP BY City ORDER BY COUNT(DISTINCT(ORDER_HEAD.OrderID)) DESC, COUNT(OrderID) DESC ";
								//echo $query;
								
								
								$result = mysqli_query($con, $query);
								$colCount = mysqli_field_count($con);
								$colCount = $colCount-4;
								while ($row = mysqli_fetch_array($result)) {
									$arrayTotals['Orders'] += $row['TotalOrders'];
									$arrayTotals['Items'] += $row['TotalItems'];
									$arrayTotals['Amount'] += $row['TotalAmount'];
								 //var_dump($row[1]);
							 ?>
									<tr>
										<td><?= $row['City'] ?></td>
										<?php
										for($i = 1; $i <= $colCount; $i++){
											$arrayTotals[$i] += $row[$i];
										 echo "<td>" . $row[$i] . (($row[$i] != 0)?" <small><i>(" . round($row[$i]/$row['TotalOrders'],3)*100 . "%)</i></small>":"") . "</td>\n";
										}
										?>
										
										<td><?= $row['TotalOrders'] ?></td>
										<td><?= $row['TotalItems'] ?></td>
										<td><?= moneyFormat($row['TotalAmount']) ?></td>
									</tr>
							 <?php		
								}
							}else{
							$city = explode(",", $city);
							
							$city = implode("' OR City LIKE '", $city);
							$city = "(City LIKE '" . $city . "')";
						//	echo $city;
						 $query = "SELECT DeliveryMethod, COUNT(DISTINCT(ORDER_HEAD.OrderID)) AS TotalOrders, COUNT(OrderID) AS TotalItems, SUM(Price*OrderQty) AS TotalAmount FROM (ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID)) JOIN CUSTOMER USING(AccountNumber) WHERE DeleteOrder = 0 AND OrderDate BETWEEN '$fromDate' AND '$toDate 23:59' AND $city GROUP BY DeliveryMethod";
						 //echo $query;
						 $result = mysqli_query($con, $query);
						 while ($row = mysqli_fetch_array($result)) {
							
						?>
							 <tr>
								 <td><?= $row['DeliveryMethod'] ?></td>
								 <td><?= $row['TotalOrders'] ?></td>
								 <td><?= $row['TotalItems'] ?></td>
								 <td><?= moneyFormat($row['TotalAmount']) ?></td>
							 </tr>
						<?php		
						 }
							}
							
							if($city == "%"){
								
								?>
								<tr>
										<td><strong>Totals</strong></td>
										
									
								
								<?php
								for($i = 1; $i <= $colCount; $i++){
											
										 echo "<td><strong>" . $arrayTotals[$i] . "</strong></td>\n";
										}
								
							}
						 ?>
										<td><strong><?= $arrayTotals['Orders'] ?></strong></td>
										<td><strong><?= $arrayTotals['Items'] ?></strong></td>
										<td><strong><?= moneyFormat($arrayTotals['Amount']) ?></strong></td>
						 </tr>
					</tbody>
				</table>
			</div>
		</div>
	</div>
	
	
		 
	   <script type="text/javascript">
				$(document).ready(function() {
						
				});
				
		   $('[name=filter]').on('keypress', function(e) {
			   if (e.which == 13) {
				   filter();
			   }
		   
		   });
		   
		   function clearFilter() {
			   $('[name=filter]').val("");
			   $('#clearButton').css('display', 'none');
			   $('tr').css('display', '');
			   
		   }
		   function filter() {
			   var	name = $('[name=filter]').val();
			   filterByName(name);
		   }
		   function filterByName(name) {
			   $('#clearButton').css('display', '');
			   for (var i = 0; i < $('tr').length; i++) {
				   if (i != 0) {
					   var result = $('tr')[i].innerText.toUpperCase().indexOf(name.toUpperCase());
					   if (result < 0) {
						   $($('tr')[i]).css('display', 'none');
					   }else {
						   $($('tr')[i]).css('display', '');
					   }
				   }
			   }
		   }
			 
				var fp1 = $( "#BillboardStartDate" ),
				now = moment().subtract(1,"seconds"),
				oneweek = moment().add(1, "weeks");
				fp1.filthypillow( { 
					initialDateTime: function( ) {
						return moment();
					}
				} );
				fp1.on( "focus", function( ) {
					fp1.filthypillow( "show" );
				} );
				fp1.on( "fp:save", function( e, dateObj ) {
					fp1.val( dateObj.format( "MM/DD/YYYY hh:mm A" ) );
					fp1.filthypillow( "hide" );
				} );
				
				
				var fp2 = $( "#BillboardEndDate" );
				fp2.filthypillow( { 
					initialDateTime: function( ) {
						return oneweek;
					}
				} );
				fp2.on( "focus", function( ) {
					fp2.filthypillow( "show" );
				} );
				fp2.on( "fp:save", function( e, dateObj ) {
					fp2.val( dateObj.format( "MM/DD/YYYY hh:mm A" ) );
					fp2.filthypillow( "hide" );
				} );
				
				
		   </script>
	
	 </div>    
     
   </body>
 </html>