<?php
if(stripos($_SERVER['SCRIPT_NAME'],"/admin/")===false?false:true){
	include_once("../connect.php");
	include_once("../session.php");
}else{
	include_once("connect.php");
	include_once("session.php");
}
function getGroupName($code){
	global $con;
	$query = "SELECT * FROM `GROUP` WHERE Code = '$code'";
	$result = mysqli_query($con, $query);
	if(mysqli_num_rows($result) > 0){
		$row = mysqli_fetch_array($result);
		return $row['Name'];	
	}else{
		return $code;
	}
}
function getLineName($code){
	global $con;
	$query = "SELECT * FROM `LINE` WHERE Code = '$code'";
	
	$result = mysqli_query($con, $query);
	if(mysqli_num_rows($result) > 0){
		$row = mysqli_fetch_array($result);
		return $row['Name'];	
	}else{
		return $code;
	}
	
}
?>
 
 <!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">
     <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>
       <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/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>
				 <script src="../js/functions.js"></script>
           <script type="text/javascript">
             $(document).ready(function(){
       //        $('.grpline').css("width", "100px");
             });
           </script>

     <![endif]-->
	<link rel="stylesheet" href="../css/styles.css"/>
	<link href="../css/fileinput.min.css" media="all" rel="stylesheet" type="text/css" />
		<script src="../js/fileinput.js" type="text/javascript"></script>
   </head>
   <body>
   
       <?php
		include_once("./navbar.php");						
		?>     

   
   <!-- Stack the columns on mobile by making one full-width and the other half-width -->
<div class="container" style="padding-top: 10px;">
	<div class="panel panel-default">
	  <div class="panel-heading form-inline">
		<h3 class="panel-title">Zeroed Out Items</h3>
		<?php
		
			$query = "SELECT count(*) Count, ORDER_DETAIL.`Group`, ORDER_DETAIL.Line, ORDER_DETAIL.SKU FROM ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID) JOIN INVENTORY ON(INVENTORY.Group = ORDER_DETAIL.Group AND INVENTORY.Line = ORDER_DETAIL.Line AND INVENTORY.SKU = ORDER_DETAIL.SKU) WHERE OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -1 YEAR) AND NOW() AND ORDER_DETAIL.Line NOT LIKE 'MIS' GROUP BY `Group`, Line, SKU  ORDER BY `Group`, Line, SKU";
			$result = mysqli_query($con, $query);
			$totalSoldItems = mysqli_num_rows($result);
			$query = "SELECT count(*) Count, ORDER_DETAIL.`Group`, ORDER_DETAIL.Line, ORDER_DETAIL.SKU FROM ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID) JOIN INVENTORY ON(INVENTORY.Group = ORDER_DETAIL.Group AND INVENTORY.Line = ORDER_DETAIL.Line AND INVENTORY.SKU = ORDER_DETAIL.SKU) WHERE OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -1 YEAR) AND NOW() AND ORDER_DETAIL.Line NOT LIKE 'MIS' and INVENTORY.Qty <=0 GROUP BY `Group`, Line, SKU  ORDER BY `Group`, Line, SKU";
			$result = mysqli_query($con, $query);
			$totalZeroItems = mysqli_num_rows($result);
			
			
		?>
		<span></span>
		<h3 class="pull-right">Percent: <?= round($totalZeroItems/$totalSoldItems*100,2) . "%" ?></h3>
		<br>
		<span class="pull-left">Total Sold Distinct Items during last 365 days more than once (1): <?= $totalSoldItems ?>
		<br>
		Total Items zeroed out (only sold during last year more than once):  <?= $totalZeroItems ?></span>
		
		<br><br>
		
	  </div>
	  <div class="panel-body">
	  	<table class="table table-hover table-condensed" id="itemOrderHistoryTable">
	  		<thead>
	  			<tr>
					<th>Sold Count</th>
					<th>Percent</th>
	  				<th>Line</th>
					<th>Group</th>
	  				
					
					
					
	  			</tr>
	  		</thead>
			<tbody>
			<?php
				
				$query = "SELECT count(DISTINCT(ORDER_DETAIL.SKU)) Count,count(DISTINCT(ORDER_DETAIL.SKU))/$totalZeroItems * 100 Percent, ORDER_DETAIL.`Group`, ORDER_DETAIL.Line FROM ORDER_HEAD JOIN ORDER_DETAIL USING(OrderID) JOIN INVENTORY ON(INVENTORY.Group = ORDER_DETAIL.Group AND INVENTORY.Line = ORDER_DETAIL.Line AND INVENTORY.SKU = ORDER_DETAIL.SKU) WHERE OrderDate BETWEEN DATE_ADD(NOW(), INTERVAL -1 YEAR) AND NOW() AND ORDER_DETAIL.Line NOT LIKE 'MIS' and INVENTORY.Qty <=0 GROUP BY `Group`, Line  ORDER BY Count DESC, `Group`, Line";
				$result = mysqli_query($con, $query);
				while($row = mysqli_fetch_array($result)){
					if(!isset($Line[$row['Line']])){
						$Line[$row['Line']] = getLineName($row['Line']);
					}
					if(!isset($Group[$row['Group']])){
						$Group[$row['Group']] = getGroupName($row['Group']);
					}
					
					$groupText = $Group[$row['Group']];
					$lineText = $Line[$row['Line']];
			?>
				<tr onclick="fillZeroedOutModalTable('<?= $row['Line'] ?>', '<?= $row['Group'] ?>')">
					<td><?= $row['Count'] ?></td>
					<td><?= round($row['Percent'],3) . "%" ?></td>
					<td><?= $lineText ?>
					<td><?= $groupText ?></td>
					
					
				</tr>
			<?php
				}
			
			?>
			</tbody>
	     </table>
	  </div>
	  <div class="panel-footer" style="height: 100px;" >
	  </div>
	</div>
</div>

<div class="modal fade" tabindex="-1" role="dialog" id="zeroedDetailModal">
  <div class="modal-dialog modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <h4 class="modal-title">Zeroed Out Items Details</h4>
      </div>
      <div class="modal-body">
        <table class="table">
			<thead>
				<th>Count</th>
				<th>Group<br>Line</th>
				<th>SKU<br>Description</th>
				<th>Last Sale</th>
				<th>Qty Avail</th>
				<th>Qty Sold</th>
			</thead>
			<tbody id="zeroedDetailTbody"></tbody>
		</table>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
      </div>
    </div><!-- /.modal-content -->
  </div><!-- /.modal-dialog -->
</div><!-- /.modal -->
<script type="text/javascript">
	function clearZeroedDetailModal(){
		$('#zeroedDetailTbody').html('');
	}
	function fillZeroedOutModalTable(Line, Group){
		clearZeroedDetailModal();
		$.get("mod.php?p=zeroedoutitems&a=functions&f=getZeroedOutDetail&line=" + Line + "&group=" + Group,function(data){
			data = (JSON.parse(data));
			for(var i = 0; i < data.length; i++){
				$('#zeroedDetailTbody').append("<tr><td>" + data[i].Count + "</td><td>" + data[i].Group + "<br>" + data[i].Line + "</td><td>" + data[i].SKU + "<br>" + data[i].Description + " " + data[i].Notes + "</td><td>" + data[i].OrderDate + "</td><td>" + data[i].Qty + "</td><td>" + data[i].Ordered + "</td>" + "</tr>");
			}
			$("#zeroedDetailModal").modal('toggle');	
		});
		
	}
	
</script>

   </body>
   
 </html>