<!--style sheets-->
<link rel="stylesheet" type="text/css" href="../css/scroll_table.css" />
<link href="../css/main.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" type="text/css" href="../css/tabs.css" />

<!--jscript-->
<script language="javascript" src="../jscript/calendar.js"></script>
<script language="JavaScript" src="../jscript/tooltip.js"  type="text/javascript"></script> 

  
<?php 
$sub_cat = $_GET['subCatSelect'];
include('../include/period_select_date.php');
$date = $_GET['date'];
$ftw_last_stock_date = $_GET['ftw_last_stock_date'];


	$sub_cat = $_GET['subCatSelect'];
	
	$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 branch = 'Gaborone' AND  date BETWEEN '".$start."' AND '".$end."'  " ;
	
	$stock_result = execsql($stock_query);
	$stock_num = mysql_num_rows($stock_result);
	
//if ($sub_cat=='Tiles') {	
	
$table_name = $_SESSION['db_prefix']."_stockloss";
$tiles_gab_date_query = "SELECT date FROM $table_name WHERE sub_category = 'Tiles' AND branch = 'Gaborone'  ORDER BY date DESC LIMIT 1 ";

$tiles_gab_date_result = mySQL_query($tiles_gab_date_query);
$tiles_gab_date_num = mysql_num_rows($tiles_gab_date_result);
if ($tiles_gab_date_num!=0) {
$tiles_gab_stock_date = mysql_result($tiles_gab_date_result, 0, "date");

$table_name = $_SESSION['db_prefix']."_stockloss";
$tiles_gab_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 = 'Tiles' 
AND DATE = '".$tiles_gab_stock_date."'
AND branch = 'Gaborone'

ORDER BY difference_2 DESC
LIMIT 1
"
;

$tiles_gab_result = mySQL_query($tiles_gab_query);
$tiles_gab_num = mysql_num_rows($tiles_gab_result);
if ($tiles_gab_num>0) {
$store_item_id = mysql_result($tiles_gab_result, 0, "store_item_id");
$tiles_gab_difference = mysql_result($tiles_gab_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);
$tiles_gab_name = mysql_result($name_result, 0, "item_name");
}
else {
	$tiles_gab_difference = '0';
	$tiles_gab_name = '';
}
}
else {
	$tiles_gab_difference = '0';
	$tiles_gab_name = '';
	$tiles_gab_stock_date = $today;
	
}
//}

//if ($sub_cat=='Accessories') {

$table_name = $_SESSION['db_prefix']."_stockloss";
$acc_gab_date_query = "SELECT date FROM $table_name WHERE sub_category = 'Accessories' AND branch = 'Gaborone'  ORDER BY date DESC LIMIT 1 ";
$acc_gab_date_result = mySQL_query($acc_gab_date_query);
$acc_gab_date_num = mysql_num_rows($acc_gab_date_result);
if ($acc_gab_date_num!=0) {
$acc_gab_stock_date = mysql_result($acc_gab_date_result, 0, "date");

$acc_gab_name = $_SESSION['db_prefix']."_stockloss";
$acc_gab_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 = 'Accessories' 
AND DATE = '".$acc_gab_stock_date."'
AND branch = 'Gaborone'

ORDER BY difference_2 DESC
LIMIT 1
"
;
$acc_gab_result = mySQL_query($acc_gab_query);
$acc_gab_num = mysql_num_rows($acc_gab_result);

if ($acc_gab_num>0) {
$store_item_id = mysql_result($acc_gab_result, 0, "store_item_id");
$acc_gab_difference = mysql_result($acc_gab_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);
$acc_gab_name = mysql_result($name_result, 0, "item_name");
}
else { 
$acc_gab_difference = '0';
$acc_gab_name = '';
}
}
else { 
$acc_gab_difference = '0';
$acc_gab_name = '';
$acc_gab_stock_date = $today;
}
//}


$table_name = $_SESSION['db_prefix']."_stockloss";
$other_gab_date_query = "SELECT date FROM $table_name WHERE sub_category = '".$sub_cat."' AND branch = 'Gaborone'  ORDER BY date DESC LIMIT 1 ";

$other_gab_date_result = mySQL_query($other_gab_date_query);
$other_gab_date_num = mysql_num_rows($other_gab_date_result);
if ($other_gab_date_num!=0) {
$other_gab_stock_date = mysql_result($other_gab_date_result, 0, "date");

$table_name = $_SESSION['db_prefix']."_stockloss";
$other_gab_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 = '".$other_gab_stock_date."'
AND branch = 'Gaborone'

ORDER BY difference_2 DESC
LIMIT 1
"
;

$other_gab_result = mySQL_query($other_gab_query);
$other_gab_num = mysql_num_rows($other_gab_result);
if ($other_gab_num>0) {
$store_item_id = mysql_result($other_gab_result, 0, "store_item_id");
$other_gab_difference = mysql_result($other_gab_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);
$other_gab_name = mysql_result($name_result, 0, "item_name");
}
else {
	$other_gab_difference = '0';
	$other_gab_name = '';
}
}
else {
	$other_gab_difference = '0';
	$other_gab_name = '';
	$other_gab_stock_date = $today;
	
}


//if ($sub_cat=="Cleaning materials") { 
$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 ver_stockloss 
WHERE DATE BETWEEN '".$start."' AND '".$end."'
AND sub_category = '".$sub_cat."'
AND branch = 'Gaborone'
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 ver_stockloss 
//WHERE sub_category = '".$sub_cat."' 
//AND DATE BETWEEN '".$start."' AND '".$end."'
//AND branch = 'Gaborone'
//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 = '';
}

if ($sub_cat=='Tiles') { $last_gab_stock_date = $tiles_gab_stock_date; }
else {
  if ($sub_cat=='Accessories') { $last_gab_stock_date = $acc_gab_stock_date; 
}
else {
$last_gab_stock_date = $other_gab_stock_date; 
}
}
?> 
 
<div class="page_middle_frame" style="height:705px; ">

<ul class="tabs" >
      <li>
          <input type="radio" checked name="tabs" id="tab1">
          
          <label class="tab" for="tab1"><a class="tabs" href="" >Gaborone</a></label>
<div id="tab-content1" class="tab-content animated fadeIn" style="height:660px;">


<?php
$page='';
$reference_name='branch';
$reference=$branch;
$reference_name_2='subCatSelect';
$reference_2= $sub_cat;
$reference_name_3='ftw_last_stock_date';
$reference_3 = $ftw_last_stock_date;
include('../include/period_select_block_small.php');
?>


<label class="label_std"><?php echo $sub_cat ?></label><br />

<?php

if ($count_average_num > 0) { ?>
<label class="label_std">
<img src="../images/exclamation_mark.png" width="24" height="24">Item: <?php echo $item_name ?> is having the highest difference in value with P <?php echo number_format($difference, 2, '.', ',') ?>. 
</label>
<?php
}
?>
<!--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": "400px",
					"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>
<table width="650" id="scroll_table" class="data_display" style="background-color:#FFF">
    <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>

        </li>
        <li>
          <input type="radio" name="tabs" id="tab2">
          <label  class="tab" for="tab2"><a class="tabs" href="stockLoss.php?branch=Francistown&subCatSelect=<?php echo $sub_cat ?>&date=1&date1=<?php echo $ftw_last_stock_date?>&date2=<?php echo $ftw_last_stock_date?>&gab_last_stock_date=<?php echo $last_gab_stock_date ?>">Francistown</a></label>
<div id="tab-content2" class="tab-content animated fadeIn">
        
 
</div>
        </li>
        <li>
          <input type="radio" name="tabs" id="tab3" >
          <label class="tab"for="tab3"><a class="tabs" href="stockLoss.php?branch=Combined&date=&subCatSelect=<?php echo $sub_cat ?>&ftw_last_stock_date=<?php echo $ftw_last_stock_date?>&gab_last_stock_date=<?php echo $tiles_gab_stock_date ?>">Combined</a></label>
          <div id="tab-content3" class="tab-content animated fadeIn">


</div>
        </li>
</ul>

</div>
<br />
</div>




