<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php 
include ("../navigation/header.php");

$page_name = "Stores-main";
include ("../navigation/access.php");

if ($permission_num==1) {

	 include ("../navigation/store_sidebar.php");
	 include('../include/period_select_date.php');	
	
	$date = $_GET['date'];
	$sub_cat = $_GET['subCatSelect'];
	
			
	if ($sub_cat=="Complete") {
	$table_name = $_SESSION['db_prefix']."_stockloss";
	$stock_query = "SELECT store_item_id, amount, date, quantity, total, unit_price  FROM $table_name WHERE date BETWEEN '".$start."' AND '".$end."'  " ;
	$stock_result = execsql($stock_query);
	$stock_num = mysql_num_rows($stock_result);
	}
	else {
	$table_name = $_SESSION['db_prefix']."_stockloss";
	$stock_query = "SELECT store_item_id, amount, date, quantity, total, unit_price  FROM $table_name WHERE sub_category = '".$sub_cat."' AND  date BETWEEN '".$start."' AND '".$end."'  " ;
	$stock_result = execsql($stock_query);
	$stock_num = mysql_num_rows($stock_result);
	}

$table_name = $_SESSION['db_prefix']."_stockloss";
$date_query = "SELECT date FROM $table_name ORDER BY date DESC LIMIT 1 ";
$date_result = mySQL_query($date_query);
$date_num = mysql_num_rows($date_result);
$stock_date = mysql_result($date_result, 0, "date");
	
if ($sub_cat=="Complete") { 
$table_name = $_SESSION['db_prefix']."_stockloss";
$count_average_query = "
SELECT 
store_item_id, 
ABS(SUM(((total - quantity)*unit_price))) AS difference_2, 
SUM(((total - quantity)*unit_price)) AS difference 
FROM $table_name
WHERE DATE BETWEEN '".$start."' AND '".$end."'
GROUP BY store_item_id
ORDER BY difference_2 DESC 
LIMIT 1
";
}
else {
$table_name = $_SESSION['db_prefix']."_stockloss";
$count_average_query = "
SELECT 
store_item_id, 
ABS(SUM(((total - quantity)*unit_price))) AS difference_2, 
SUM(((total - quantity)*unit_price)) AS difference 
FROM $table_name 
WHERE sub_category = '".$sub_cat."' 
AND DATE BETWEEN '".$start."' AND '".$end."'
GROUP BY store_item_id
ORDER BY difference_2 DESC 
LIMIT 1";
}


$count_average_result = mySQL_query($count_average_query);
$count_average_num = mysql_num_rows($count_average_result);

if ($count_average_num>0) {

$store_item_id = mysql_result($count_average_result, 0, "store_item_id");
$difference = mysql_result($count_average_result, 0, "difference");

$table_name = $_SESSION['db_prefix']."_store_items";
$name_query = "SELECT item_name FROM $table_name WHERE store_item_id = '".$store_item_id."'  ";
$name_result = mySQL_query($name_query);
$item_name = mysql_result($name_result, 0, "item_name");
}
else
{
	$difference = '0';
	$item_name = '';
}
	
	?> 
 
<!--style sheets-->
<link rel="stylesheet" type="text/css" href="../css/tooltip.css" /> 
<link rel="stylesheet" type="text/css" href="../css/scroll_table.css" />   
 
<!--jscript-->
<script language="JavaScript" src="../jscript/tooltip.js"  type="text/javascript"></script>
<script type="text/javascript" language="javascript" src="../jscript/jquery.dataTables.js"></script>
<script type="text/javascript" charset="utf-8">
			$(document).ready(function() {
				$('#scroll_table').dataTable( {
					"sScrollY": "500px",
					"bPaginate": false,
					"bScrollCollapse": true,
					"aaSorting": [[ 0, "asc" ]],
					"aoColumns": [
								{"iDataSort": 1},
								{"bVisible": false},
                              	{"bSortable": true},
                             	{"bSortable": true},
								{"bSortable": true},
								{"iDataSort": 6},
								{"bVisible": false},
								{"bSortable": true},
								{"iDataSort": 9},
								{"bVisible": false},
								{"bSortable": true}
								 ]
				} );
			} );
		</script>
</head>

<body>

<div class="main_frame">
<div class="page_head_frame"><label class="label_page_head">Stock Count Report <?php echo $sub_cat?> </label></div><br />
<br /><br />
<br />
<div class="page_middle_frame">
<?php

$page='stockLoss.php';
$reference_name='subCatSelect';
$reference=$sub_cat;
$reference_name_2='subCatSelect';
$reference_2=$sub_cat;
include('../include/period_select_block_2.php');

?>
</div>
<br /><br />
<div class="page_middle_frame">
<?php
if ($count_average_num > 0) { ?>
<img src="../images/exclamation.png" width="24" height="24">Item: <?php echo $item_name ?> is having the highest difference in value with P<?php echo $difference ?>. 
<?php
}
?>

<table width="845" id="scroll_table" class="data_display" >
    <thead>
      <tr>
        <th align="center">Product Name</th>
        <th align="center">&nbsp;</th>
        <th align="center">Stock</th>
        <th align="center">Date</th>
        <th align="center">Count</th>
        <th align="center">+/-</th>
        <th align="center">&nbsp;</th>
        <th align="center">Unit cost</th>
        <th align="center">Difference<br>value&nbsp;+/-</th>
        <th align="center">&nbsp;</th>  
        <th align="center">Sub category</th>
      </tr>
    </thead>
    <tbody>
    
    <?php
$i = 0;
$amount=0;
$b7_total=0;
	while ($i < $stock_num) {
		
		$b1 = mysql_result($stock_result, $i, "store_item_id");
		
		$dateTT_line='';
		$table_name = $_SESSION['db_prefix']."_stockloss";
		$stock_date_query = "SELECT date, quantity, total, amount, unit_price FROM $table_name WHERE date BETWEEN '".$start."' AND '".$end."' AND store_item_id = '".$b1."'" ;
		$stock_date_result = execsql($stock_date_query);
		$stock_date_num = mysql_num_rows($stock_date_result);
				
		
		  $table_name = $_SESSION['db_prefix']."_store_items";
		  $item_query = "SELECT item_name,category, subcategory FROM $table_name WHERE store_item_id ='".$b1."' ";
		  $item_result = execsql($item_query);
		  $b1a = mysql_result($item_result, 0, "item_name");
		  $b1b = mysql_result($item_result, 0, "category");
		  $b1c = mysql_result($item_result, 0, "subcategory");
		
		$b10 = mysql_result($stock_result, $i, "date");
			
		$b4 = mysql_result($stock_result, $i, "quantity");
		$b5 = mysql_result($stock_result, $i, "total");
		$b2 = $b5 - $b4;
		
		
        $b6 = mysql_result($stock_result, $i, "unit_price");
		$b7 = $b2*$b6;
		$b7a = $b7;
		$b7_total = $b7_total + $b7;
		
				
		$k=0;
		$stock_difference_total=0;
		while ($k<$stock_date_num) {
					$stock_date = mysql_result($stock_date_result, $k, "date");
					$stock_quantity = mysql_result($stock_date_result, $k, "quantity");
					$stock_total = mysql_result($stock_date_result, $k, "total");
					$stock_amount = mysql_result($stock_date_result, $k, "amount");
					$stock_unit_price = mysql_result($stock_date_result, $k, "unit_price");
					$stock_difference_value = $stock_unit_price * $stock_amount;
					$stock_difference_total = $stock_difference_total + $stock_difference_value;
					$dateTT_line = $dateTT_line.'<tr><td>'.date("d-m-Y", strtotime($stock_date)).'</td><td align=right>'.$stock_quantity.'</td><td align=right>'.$stock_total.'</td><td align=right>'.$stock_amount.'</td><td align=right>'.number_format($stock_difference_value, 2, '.', ',').'</td></tr>';
					$k++;
		}
		$dateTT_header = 'Stock takes included:<br><table border=1 style=border-collapse:collapse;><tr><td width=75px>Date</td><td width=50px>Stock</td><td width=50px>Count</td><td width=50px>+/-</td><td width=75px>Difference<br>Value</td></tr>';
		$dateTT_footer = '</table>';
		
		$dateTT = $dateTT_header.$dateTT_line.$dateTT_footer;
			
?>
     <tr class="alt">
        <td><a href="store_item_history.php?store_item_id=<?php echo $b1 ?>&date=0" onMouseOver="ddrivetip('<?php           
$table_name3 = $_SESSION['db_prefix']."_item_history";
$history_query = "SELECT entry, quantity, total, date FROM $table_name3 WHERE store_item_id = '".$b1."' ORDER BY history_id DESC LIMIT 10";
$history_result = mySQL_query($history_query);
$history_num = mysql_num_rows($history_result); 
?><b>Recent history:</b><br><br><table id=table_tool_tip width=250 border=1 cellspacing=0 cellpadding=0><tr><td width=75>date</td><td width=75>action</td><td width=50>qty</td><td width=50>stock qty</td></tr><?php
$k=0;
$h2="0";
$h2a="";
while ($k<$history_num) {
          $h1 = mysql_result($history_result, $k, "date");
		  $h2 = mysql_result($history_result, $k, "entry");
		  if ($h2=="5") {$h2a='stock count';}
		  if ($h2=="10") {$h2a='new';}
		  if ($h2=="20") {$h2a='received';}
		  if ($h2=="30") {$h2a='delivered';}
		  $h3 = mysql_result($history_result, $k, "quantity");
		  $h4 = mysql_result($history_result, $k, "total");?>
<tr><td><?php echo date("d-m-Y", strtotime($h1)) ?></td><td><?php echo $h2a ?></td><td><?php echo $h3 ?></td><td><?php echo $h4 ?></td><?php 
$k++;
}
?></tr></table><br><b>Click for full history</b>', 260)"; onMouseOut="hideddrivetip()"><?php echo $b1a?></a></td>
        <td><?php echo $b1a?>&nbsp;</td>
        <td align="right"><?php echo $b4 ?></td>
       <td align="left"><?php echo date("d-m-Y", strtotime($b10)) ?></td>
        <td align="right"><?php echo $b5 ?></td>
        <td align="right"><?php if ($b2<0) { echo '<font color=red>'.$b2.'</font>'; } else { echo $b2; } ?></td>
        <td ><?php echo $b2 ?></td>
        <td align="right"><?php echo number_format($b6, 2, '.', ',') ?></td>
        <td align="right"><a href=" " onMouseOver="ddrivetip('<?php echo $dateTT ?>', 350)"; onMouseOut="hideddrivetip()">
          <?php if ($b7<0) { echo '<font color=red>'.number_format($b7, 2, '.', '').'</font>'; } else { echo number_format($b7, 2, '.', ''); } ?>
        </a></td>
        <td><?php echo $b7a ?></td>
        <td><?php echo $b1c ?></td>
       
      </tr>
    <?php
    $i++;
	}
	?>  
     <tfoot>
     <tr >
       <td colspan="7" align="right">total difference:</td>
       <td align="right">&nbsp;</td>
       <td align="right"><?php echo number_format($b7_total, 2, '.', ',') ?></td>
       <td>&nbsp;</td>
       <td>&nbsp;</td>
     </tr> 
     </tfoot>

	</tbody>
  </table>

<br />
</div>
</div>
<?php include ('../navigation/side_bar.php'); ?>
</body>

<?php } else { 
    $_SESSION['denied_return'] = "store/store_main.php";	?>
<body onLoad="accessdenied(); return false">
</body>
<?php }
?>


