<!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");
	
		$width=$_GET['width'];
		$depth=$_GET['depth'];	
		$class=$_GET['class'];		  
		$subCat='timber';
								
		$subCatText = $subCat;	
		$table_name2 = $_SESSION['db_prefix']."_store_items"; 		 
		
		if ($width=="all") {
		$items_query = "SELECT * FROM $table_name2 WHERE subCategory = '".$subCat."' ORDER BY item_name ASC";
		}
		else {
			$items_query = "SELECT * FROM $table_name2 WHERE subCategory = '".$subCat."' AND width = '".$width."' AND depth = '".$depth."' AND  class = '".$class."'  ORDER BY item_name ASC";
		}
		$items_result = execsql($items_query);
    	$i = 0;
    	$items_num = mysql_num_rows($items_result);
		
		
$single = $_GET['single'];		

		$table_name5 = $_SESSION['db_prefix']."_captured_timber";
		$reserved_query = "SELECT  SUM(prereserved) AS tot_prereserved, SUM(reserved) AS tot_reserved FROM $table_name5 WHERE cap_timber = '".$depth."x".$width." (".$class.") ' ";
		$reserved_result = mySQL_query($reserved_query);
		$tot_prereserved = mysql_result($reserved_result, 0, "tot_prereserved");
		$tot_reserved = mysql_result($reserved_result, 0, "tot_reserved");
		
		$table_name = $_SESSION['db_prefix']."_store_items";
   	 	$stock_query = "SELECT  quantity, length, minimumStock FROM $table_name WHERE width = '".$width."' and depth = '".$depth."'  AND class='".$class."'  ";
		$stock_result = mySQL_query($stock_query);
		$stock_num = mysql_num_rows($stock_result);
		$k=0;
		$length_tot=0;
		$min_stock_length=0;
		while ($k<$stock_num) {
			$length = mysql_result($stock_result, $k, "length");
			$quantity = mysql_result($stock_result, $k, "quantity");
			$minimum = mysql_result($stock_result, $k, "minimumStock");
			$length_tot=$length_tot + ($length*$quantity);
			$min_stock_length =  $min_stock_length + ($length*$minimum);
		$k++;
		}	
    	
?>
<!--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": [[ 1, "asc" ]],
					"aoColumns": [
								{"iDataSort": 1},
								{"bVisible": false},
                              	{"iDataSort": 3},
								{"bVisible": false},
                             	{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true}								
								 ]
				} );
			} );
		</script>
</head>

<body>
<div class="main_frame">
  <div class="page_head_frame">
    <table width="656" border="0" >
      <tr>
        <td width="537"><label class="label_page_head">Store Timber List</label>
          &nbsp;&nbsp;
          <label class="label_page_head_2">
            <?php if ($width!='all') { echo $depth." x ".$width." (".$class.")" ; } ?>
          </label></td>
        <td width="103" onClick="MM_goToURL('parent','store_timber_list.php?width=all&depth=&class=&single=');return document.MM_returnValue">&nbsp;</td>
      </tr>
    </table>
  </div>
  <div class="page_middle_frame">
    <table width="662" id="scroll_table" class="data_display"  >
      <thead>
        <tr>
          <th width="213">Size</th>
          <th width="31">&nbsp;</th>
          <th width="76"><span class="sortable-numeric">Stock (no)</span></th>
          <th width="23">&nbsp;</th>
          <th width="96"><span class="sortable-numeric"><span class="\&quot;sortable-numeric&quot;">Reserved (no) </span></span></th>
          <th width="92">Available (no)</th>
          <th width="99">Min Stock (no)</th>
        </tr>
      </thead>
      <tbody>
        <?php 
    $s1tot=0;
	$s2tot=0;
	$r1tot=0;
	$tip_reserved_text = '';
	$tip_reserved_text2 = '';
	$tip_reserved_text3 = '';
    while($i < $items_num){
	       $b1 = mysql_result($items_result, $i, "store_item_id");
	       $b2 = mysql_result($items_result, $i, "item_name");
	       $b3 = mysql_result($items_result, $i, "location");
		   $b4 = mysql_result($items_result, $i, "subCategory");
	       $b5 = mysql_result($items_result, $i, "quantity");
	       $b6 = mysql_result($items_result, $i, "minimumStock");
	       $b7 = mysql_result($items_result, $i, "price");
		   $b8 = mysql_result($items_result, $i, "unit");
		   $b9 = mysql_result($items_result, $i, "supplier_id");
		   $b10 = mysql_result($items_result, $i, "price_2");
		   
		  	   
		    
			$table_name20 = $_SESSION['db_prefix']."_cutting_bill_lines";
		    $reserved0_query = "SELECT  SUM(quantity-not_delivered) AS summed_quantity FROM $table_name20 WHERE store_item_id = '".$b1."' GROUP BY store_item_id ";
			$reserved0_result = mySQL_query($reserved0_query);
			$reserved0_num = mysql_num_rows($reserved0_result);
			if ($reserved0_num!=0) {
		    $reserved0 = mysql_result($reserved0_result, 0, "summed_quantity");
			}
			else {
				$reserved0 = 0;
			}
		    
			$table_name21 = $_SESSION['db_prefix']."_cutting_supplies_lines";
		    $reserved1_query = "SELECT  SUM(quantity) AS summed_quantity FROM $table_name21 WHERE store_item_id = '".$b1."' GROUP BY store_item_id ";
			$reserved1_result = mySQL_query($reserved1_query);
			$reserved1_num = mysql_num_rows($reserved1_result);
		    if ($reserved1_num!=0) {
		    $reserved1 = mysql_result($reserved1_result, 0, "summed_quantity");
			}
			else {
				$reserved1 = 0;
			}
		  
		   	   
		    $table_name23 = $_SESSION['db_prefix']."_authorised_timber";
		   	$reserved4_query = "SELECT SUM(reserved-delivered-not_delivered-de_authorised) AS reserved4 FROM $table_name23 WHERE store_item_id = '".$b1."' GROUP BY store_item_id ";
			$reserved4_result = mySQL_query($reserved4_query);
			$reserved4_num = mysql_num_rows($reserved4_result);
			if ($reserved4_num!=0) {
				  $reserved4 = mysql_result($reserved4_result, 0, "reserved4");
			}
			else {
				$reserved4 = 0;
			}
			
		   $b11 = ($reserved0 - $reserved1) + $reserved4;
		   $b12 = $b5-$b11;
		 
		   $a1 = mysql_result($items_result, $i, "width");
		   $a2 = mysql_result($items_result, $i, "depth");
		   $a3 = mysql_result($items_result, $i, "length");
		   		   
		   $table_name2 = $_SESSION['db_prefix']."_suppliers";
           $suppliers_query = "SELECT account_name   FROM $table_name2 WHERE supplier_id = '".$b9."' ";
           $suppliers_result = mySQL_query($suppliers_query);
		   $suppliers_num = mysql_num_rows($suppliers_result);
           if ($suppliers_num!=0) {
		   $b9a = mysql_result($suppliers_result, 0, "account_name");
		   }
		   else {
			   $b9a="";
		   }
		 		  		   
		   	$table_name20 = $_SESSION['db_prefix']."_cutting_bill_lines";
			$job_query = "SELECT job_no FROM $table_name20 WHERE store_item_id = '".$b1."'";
			
			$job_result = mySQL_query($job_query);
			$job_num = mysql_num_rows($job_result);
			
			$reserved_qty='';
			$tip_reserved_text='';		   
		    $k=0; 
		    while ($k<$job_num) {
				$job_no = mysql_result($job_result, $k, "job_no");
				
				$table_name22 = $_SESSION['db_prefix']."_captured_jobs";
				$project_query = "SELECT cust_id, project_no, project_place, project_description FROM $table_name22 WHERE job_no = '".$job_no."' ";
				$project_result = mySQL_query($project_query);
				$cust_id = mysql_result($project_result, 0, "cust_id");
				$project = mysql_result($project_result, 0, "project_no").' '.mysql_result($project_result, 0, "project_place");
				
				$table_name2 = $_SESSION['db_prefix']."_customer";
				$customer_query = "SELECT * FROM $table_name2 WHERE cust_id ='".$cust_id."' ";
				$customer_result = mySQL_query($customer_query);
				$customer_num = mysql_num_rows($customer_result);	
				$a1 = mysql_result($customer_result, 0, "account_type");
					if ($a1=="corporate") { 
						$name = mysql_result($customer_result, 0, "comp_name"); 
					} 
					else {
						$name = mysql_result($customer_result, 0, "title")." ".mysql_result($customer_result, 0, "name_2")." ".mysql_result($customer_result, 0, "comp_name");
 					}
		   
		    $table_name20 = $_SESSION['db_prefix']."_cutting_bill_lines";
		    $tip_reserved_query = "SELECT SUM(quantity-not_delivered) AS qty_1 FROM $table_name20 WHERE store_item_id = '".$b1."' AND job_no = '".$job_no."' ";
			$tip_reserved_result = mySQL_query($tip_reserved_query);
			$tip_reserved_num = mysql_num_rows($tip_reserved_result);
			$qty_1 = mysql_result($tip_reserved_result, 0, "qty_1");
			
			$table_name21 = $_SESSION['db_prefix']."_cutting_supplies_lines";
			$tip_reserved2_query = "SELECT SUM(quantity) AS qty_2 FROM $table_name21 WHERE store_item_id = '".$b1."' AND job_no = '".$job_no."' ";
			$tip_reserved2_result = mySQL_query($tip_reserved2_query);
			$tip_reserved2_num = mysql_num_rows($tip_reserved2_result);
			$qty_2 = mysql_result($tip_reserved2_result, 0, "qty_2");
			
			$table_name23 = $_SESSION['db_prefix']."_authorised_timber";
			$tip_reserved3_query = "SELECT job_no, SUM(reserved-delivered-not_delivered-de_authorised) as qty_3 FROM $table_name23 WHERE store_item_id = '".$b1."' AND job_no = '".$job_no."' ";
			$tip_reserved3_result = mySQL_query($tip_reserved3_query);
			$tip_reserved3_num = mysql_num_rows($tip_reserved3_result);
			if ($tip_reserved3_num>0) {
			       $qty_3 = mysql_result($tip_reserved3_result, 0, "qty_3");
			}
			else {
				$qty_3 = 0;
			}
			
			$reserved_qty = ($qty_1 - $qty_2);
			
			$k++;
			
			if ($reserved_qty != 0) {			
			$tip_reserved_text =  '<tr><td><label class=tip_text_label>'.$job_no.'</label></td><td><label class=tip_text_label>'.$name.'</label</td><td><label class=tip_text_label>'.$project.'</label></td><td align=right><label class=tip_text_label>'.$reserved_qty.'</label></td></tr>'.$tip_reserved_text;
			}
			
		  	}
		
		    $table_name23 = $_SESSION['db_prefix']."_authorised_timber";
			$reserved3_query = "SELECT job_no, SUM(reserved-delivered-not_delivered-de_authorised) as qty_2 FROM $table_name23 WHERE store_item_id = '".$b1."' AND auth_timber_group !='Cash sale' GROUP BY job_no ";
			$reserved3_result = mySQL_query($reserved3_query);
			$reserved3_num = mysql_num_rows($reserved3_result);
			$m=0;
			$tip_reserved_text2='';
			while ($m<$reserved3_num) {
			$job_no = mysql_result($reserved3_result, $m, "job_no");
			
			    if ($job_no!='') {
				$table_name = $_SESSION['db_prefix']."_authorised_timber";
				$type_query = "SELECT auth_timber_group FROM $table_name WHERE job_no = '".$job_no."'  ";
				$type_result = mySQL_query($type_query);
				$type = mysql_result($type_result, 0, "auth_timber_group");
				
				$table_name22 = $_SESSION['db_prefix']."_captured_jobs";
				$project_query = "SELECT cust_id,  project_no, project_place, project_description FROM $table_name22 WHERE job_no = '".$job_no."' ";
				$project_result = mySQL_query($project_query);
				$cust_id = mysql_result($project_result, 0, "cust_id");
				$project = mysql_result($project_result, 0, "project_no").' '.mysql_result($project_result, 0, "project_place");
				
				}
				else {
					$project='';
				}
				
			$table_name2 = $_SESSION['db_prefix']."_customer";
				$customer_query = "SELECT * FROM $table_name2 WHERE cust_id ='".$cust_id."' ";
				$customer_result = mySQL_query($customer_query);
				$customer_num = mysql_num_rows($customer_result);	
				$a1 = mysql_result($customer_result, 0, "account_type");
					if ($a1=="corporate") { 
						$name = mysql_result($customer_result, 0, "comp_name"); 
					} 
					else {
						$name = mysql_result($customer_result, 0, "title")." ".mysql_result($customer_result, 0, "name_2")." ".mysql_result($customer_result, 0, "comp_name");
 					}
			
			$qty_2 = mysql_result($reserved3_result, $m, "qty_2");
			
			$m++;
			
			if ($qty_2!=0) {
			$tip_reserved_text2 =  '<tr><td><label class=tip_text_label>'.$job_no.'</label></td><td><label class=tip_text_label>'.$name.'</label</td><td><label class=tip_text_label>'.$project.'</label></td><td align=right><label class=tip_text_label>'.$qty_2.'</label></td></tr>'.$tip_reserved_text2;
			}
		  	
		}
		
$table_name23 = $_SESSION['db_prefix']."_authorised_timber";
			$reserved4_query = "SELECT job_no, SUM(reserved-delivered-not_delivered-de_authorised) as qty_2 FROM $table_name23 WHERE store_item_id = '".$b1."' AND auth_timber_group ='Cash sale' GROUP BY job_no ";
			$reserved4_result = mySQL_query($reserved4_query);
			$reserved4_num = mysql_num_rows($reserved4_result);
			$m=0;
			$tip_reserved_text3='';
			while ($m<$reserved4_num) {
			$job_no = mysql_result($reserved4_result, $m, "job_no");
			
			    if ($job_no!='') {
				$table_name = $_SESSION['db_prefix']."_authorised_timber";
				$type_query = "SELECT auth_timber_group FROM $table_name WHERE job_no = '".$job_no."'  ";
				$type_result = mySQL_query($type_query);
				$type = mysql_result($type_result, 0, "auth_timber_group");
				$table_name22 = $_SESSION['db_prefix']."_captured_cash_sale";
								
				$project_query = "SELECT cust_id,  project_no, project_place, project_description FROM $table_name22 WHERE job_no = '".$job_no."' ";
				$project_result = mySQL_query($project_query);
				$cust_id = mysql_result($project_result, 0, "cust_id");
				$project = mysql_result($project_result, 0, "project_no").' '.mysql_result($project_result, 0, "project_place");
				
				}
				else {
					$project='';
				}
				
			$table_name2 = $_SESSION['db_prefix']."_customer";
				$customer_query = "SELECT * FROM $table_name2 WHERE cust_id ='".$cust_id."' ";
				$customer_result = mySQL_query($customer_query);
				$customer_num = mysql_num_rows($customer_result);	
				$a1 = mysql_result($customer_result, 0, "account_type");
					if ($a1=="corporate") { 
						$name = mysql_result($customer_result, 0, "comp_name"); 
					} 
					else {
						$name = mysql_result($customer_result, 0, "title")." ".mysql_result($customer_result, 0, "name_2")." ".mysql_result($customer_result, 0, "comp_name");
 					}
			
			$qty_2 = mysql_result($reserved4_result, $m, "qty_2");
			
			$m++;
			
			if ($qty_2!=0) {
			$tip_reserved_text3 =  '<tr><td><label class=tip_text_label>'.$job_no.'</label></td><td><label class=tip_text_label>'.$name.'</label</td><td><label class=tip_text_label>'.$project.'</label></td><td align=right><label class=tip_text_label>'.$qty_2.'</label></td></tr>'.$tip_reserved_text3;
			}
		  	
		}		
		
		
			$tip_reserved_head='<b>Quantity reserved on trusses:</b><br><table border=1 cellpadding=5 style=border-collapse:collapse; ><tr><td width=50px;><label class=tip_text_label_bold>Job no</label></td><td width=150px;><label class=tip_text_label_bold>Customer</label></td><td width=150px;><label class=tip_text_label_bold>Location</label></td><td width=10px;><label class=tip_text_label_bold>Actual</label></td></tr>';
			$tip_reserved_head2='<b>Quantity reserved on bracing and sundries:</b><br><table border=1 cellpadding=5 style=border-collapse:collapse; ><tr><td width=50px;><label class=tip_text_label_bold>Job no</label></td><td width=150px;><label class=tip_text_label_bold>Customer</label></td><td width=150px;><label class=tip_text_label_bold>Location</label></td><td width=10px;><label class=tip_text_label_bold>Actual</label></td></tr>';
			$tip_reserved_head3='<b>Quantity reserved for cash sale:</b><br><table border=1 cellpadding=5 style=border-collapse:collapse; ><tr><td width=50px;><label class=tip_text_label_bold>Job no</label></td><td width=150px;><label class=tip_text_label_bold>Customer</label></td><td width=150px;><label class=tip_text_label_bold>Location</label></td><td width=10px;><label class=tip_text_label_bold>Actual</label></td></tr>';
			
			$tip_reserved_foot='</table>';
			$reservedTT = $tip_reserved_head.$tip_reserved_text.$tip_reserved_foot.$tip_reserved_head2.$tip_reserved_text2.$tip_reserved_foot.$tip_reserved_head3.$tip_reserved_text3.$tip_reserved_foot;
		   
		      
    ?>
        <tr class="alt">
          <td align="left"><a href="store_item_display.php?store_item_id=<?php echo $b1 ?> " onMouseover="ddrivetip('', 300)"; onMouseout="hideddrivetip()">&nbsp;&nbsp;</a><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=300 border=1 cellspacing=0 cellpadding=0><tr><td width=50>date</td><td width=100>action</td><td width=40>qty</td><td width=40>stock qty</td></tr><?php
$k=0;
while ($k<$history_num) {
          $h1 = mysql_result($history_result, $k, "date");
		  $h2 = mysql_result($history_result, $k, "entry");
		  if ($h2=="") {$h2a='';}
		  if ($h2=="5") {$h2a='stock count';}
		  if ($h2=="10") {$h2a='new';}
		  if ($h2=="20") {$h2a='received';}
		  if ($h2=="30") {$h2a='issued department';}
		  if ($h2=="31") {$h2a='issued employee';}
		  if ($h2=="39") {$h2a='cash delivery';}
		  if ($h2=="40") {$h2a='job_delivery';}
		  if ($h2=="41") {$h2a='issued to job';}
		  if ($h2=="42") {$h2a='cash sale to job';}
		  if ($h2=="43") {$h2a='cash sale';}
		  if ($h2=="44") {$h2a='return on job';}
		  if ($h2=="45") {$h2a='cash return';}
		  $h3 = mysql_result($history_result, $k, "quantity");
		  $h4 = mysql_result($history_result, $k, "total");
          if ($h2==5) {if ($h3>0) {$h3='+'.$h3;} }
			if (($h2==20) OR ($h2==44) OR ($h2==45) ) {$h3='+'.$h3;}
			if (($h2==30) OR ($h2==31) OR ($h2==39) OR ($h2==40) OR ($h2==41) OR ($h2==42) OR ($h2==43)) {$h3='-'.$h3;}?>
<tr><td><?php echo date("d-m-Y", strtotime($h1)) ?></td><td><?php echo $h2a ?></td><td align=right><?php echo $h3 ?></td><td align=right><?php echo $h4 ?></td><?php 
$k++;
}
?></tr></table><br><b>Click for full history</b>', 310)"; onMouseout="hideddrivetip()"><?php echo $b2 ?></a></td>
          <td align="left"><?php echo $b2 ?></td>
          <td align="right"><?php
         
			if($b5<0){
				echo "<font color=\"red\">".number_format($b5 , 0, '.', ',')."</font>";
				}
			else {
			 if($b5<$b6){
				echo "<font color=\"orange\">".number_format($b5 , 0, '.', ',')."</font>";
				}
		       else{
			     echo number_format($b5);
		     }	
		   }
			
			?></td>
          <td align="right"><?php echo $b5?></td>
          <td align="right"><a href="" onMouseover="ddrivetip('<?php echo $reservedTT?>  ', 450)"; onMouseout="hideddrivetip()"> <?php echo $b11 ?></a></td>
          <td align="right"><?php echo $b5-$b11 ?></td>
          <td align="right"><?php echo $b6 ?></td>
        </tr>
        <?php 
		$i++;
    	}
    	?>
      </tbody>
    </table>
    <div align="left">
     
    </div>
    <br>
    <div align="left">
      <?php if ($single == 'yes') {	
	  ?>
      <table width="355" align="left" id="table_details_display">
        <tr>
          <td colspan="3"><b><?php echo 'Stock summary for: '.$depth.'x'.$width.'('.$class.')';   ?></b></td>
        </tr>
        <tr>
          <td width="118" rowspan="2">Reserved</td>
          <td width="118">Estimated:</td>
          <td width="97" align="right"><?php echo $tot_prereserved ?> m</td>
        </tr>
        <tr>
          <td>Actual:</td>
          <td align="right"><?php echo $tot_reserved ?> m</td>
        </tr>
        <tr>
          <td rowspan="2">Stock</td>
          <td>Total stock:</td>
          <td align="right"><?php echo number_format($length_tot/1000, 2, '.', ',') ?> m</td>
        </tr>
        <tr>
          <td>Min stock</td>
          <td align="right"><?php echo number_format($min_stock_length/1000, 2, '.', ',') ?> m</td>
        </tr>
      </table>
      <?php
		
}
       ?>
    </div>
  </div>
</div>
</body>
<?php } else { 
    $_SESSION['denied_return'] = "store/store_main.php";	?>
<body onLoad="accessdenied(); return false">
</body>
<?php }
?>
<?php include ('../navigation/side_bar.php'); ?>
