<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<?php
include ("../navigation/header.php");
$page_name = "Production-costing";
include ("../navigation/access.php");
$today = date("Y-m-d");
$permission_num=1;
if ($permission_num==1) {

	include ("../navigation/production_sidebar.php");
    include ("../include/date_set.php"); 

$table_name2 = "tlh_captured_jobs";
$display_query = "
	SELECT job_no,  cust_id, del_date, value, captured_value, cap_result, designer
FROM $table_name2  
WHERE (delivered = 'yes')
AND job_type = 'Project'
AND del_date BETWEEN '".$start."' AND '".$end."' 
GROUP BY job_no
";
$display_result = execsql($display_query);
$display_num = mysql_num_rows($display_result);

		
		 
		?>
<!--style sheets--> 
<link rel="stylesheet" type="text/css" href="../css/tooltip.css" />

</head>

<body>
<div class="main_frame" style="height:auto;">
  <div class="page_head_frame">
    <label class="label_page_head">Job costing completed</label>
 </div>
  <br />
  <div class="page_middle_frame">
  <form action="costing_job_report_list.php" method="get" name="dateform" class="today" id="dateform">
    <table width="645" border="0" align="left" cellpadding="0" cellspacing="0" id="table_calendar">
      <tr>
        <td width="39"><label class="label_std">from:</label></td>
        <td width="244"><?php
		$myCalendar = new tc_calendar("date1", true);
		$myCalendar->setIcon("../images/iconCalendar.gif");
		$myCalendar->setDate($ds, $ms, $ys);
		$myCalendar->setPath("./");
		$myCalendar->setYearInterval(2000, 2030);
	 	$myCalendar->writeScript();?></td>
        <td width="30"><label class="label_std">to:</label></td>
        <td width="280"><?php
		$myCalendar = new tc_calendar("date2", true);
		$myCalendar->setIcon("../images/iconCalendar.gif");
		$myCalendar->setDate($de, $me, $ye);
		$myCalendar->setPath("./");
		$myCalendar->setYearInterval(2000, 2030);
		$myCalendar->writeScript();?>
          <input name="date" type="hidden" id="date" value="1" /></td>
        <td width="52"><label>
            <input name="button5" type="submit" class="linkbutton"  id="button5" value="Select" />
          </label></td>
      </tr>
    </table>
    &nbsp;&nbsp;&nbsp;&nbsp;
  </form>
  </div>
  <br />
	

</head>

<body>

<div class="page_middle_frame" style="width:710px;" >
<?php if ($display_num!=0) { ?>
<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" language="javascript" src="../jscript/formatted-numbers.js"></script>



<script type="text/javascript" charset="utf-8">
			$(document).ready(function() {
				$('#scroll_table').dataTable( {
					"sScrollY": "500px",
					"bPaginate": false,
					"bScrollCollapse": true,
					"aaSorting": [[ 0, "desc" ]],
					       
						
     				
					"aoColumns": [
								{"iDataSort": 1},
                              	{"bVisible": false},
								{"iDataSort": 3},
                              	{"bVisible": false},
								{"bSortable": true},
								{"iDataSort": 6},
                              	{"bVisible": false},
                             	{"iDataSort": 8},
                              	{"bVisible": false},
								{"bSortable": true},
								{"iDataSort": 11},
                              	{"bVisible": false},
								{"iDataSort": 13},
                              	{"bVisible": false},
								{"bSortable": true},
								{"bSortable": true},
								{"bSortable": true}
								 ]
								 
							 
						 
				} );
			} );
		</script>
  <table width="710" id="scroll_table" class="data_display">
  <thead>
    <tr>
      <th width="35" align="center">Job no</th>
      <th width="35" align="center">&nbsp;</th>
      <th width="35" align="center">Date</th>
      <th width="35" align="center">&nbsp;</th>
      <th width="169" align="center">Customer</th>
      <th width="55" align="center">Job value</th>
      <th width="17" align="center">&nbsp;</th>
      <th width="55" align="center">Captured<br />
Quote value</th>
      <th width="21" align="center">&nbsp;</th>
      <th width="62" align="center">Prognose<br />
        in %</th>
      <th width="65" align="center">Actual<br />
        Costing</th>
      <th width="17" align="center">&nbsp;</th>
      <th width="44" align="center">Result</th>
      <th width="18" align="center">&nbsp;</th>
      <th width="44" align="center">in %</th>
      <th width="52" align="center">Designer</th>
      <th width="52" align="center">Sales rep</th>
    </tr>
    </thead>
    
<?php

$job_value_total=0;
$quoted_value_total=0;
$final_value_total=0; 
$result_total=0;
$z=0;
while ($z<$display_num) {

$job_no = mysql_result($display_result, $z, "job_no");
$cust_id = mysql_result($display_result, $z, "cust_id");
$delivery_date = mysql_result($display_result, $z, "del_date");
$job_value  = mysql_result($display_result, $z, "value");
$quoted_value  = mysql_result($display_result, $z, "captured_value");
$prognose_perc  = mysql_result($display_result, $z, "cap_result");
$final_value  = '0';
$result  = '0';
$result_perc  = '0';

$designer  = mysql_result($display_result, $z, "designer");	

$table_name = "tlh_quotations";
$designer_query = "SELECT seller, designer  FROM $table_name WHERE quote_no = '".$job_no."' ";
$designer_result = mySQL_query($designer_query);
$designer_num = mysql_num_rows($designer_result);
$seller = mysql_result($designer_result, 0, "seller");
$designer2 = mysql_result($designer_result, 0, "designer");

include('../include/prog_result_calculate.php');  

$table_name = "tlh_cutting_bill";
$cutting_query = "SELECT * FROM $table_name WHERE job_no = '".$job_no."' ";
$cutting_result = mySQL_query($cutting_query);
$cutting_num = mysql_num_rows($cutting_result);
if ($cutting_num!=0) {
		$bill_id = mysql_result($cutting_result, 0, "bill_id");
}
else {
	$bill_id='';
}

/* =====   truss charges */
$table_name = "tlh_captured_jobs";
$quote_press_points_query = "SELECT press_points, press_charge FROM $table_name WHERE job_no = '".$job_no."' ";
$quote_press_points_result = mySQL_query($quote_press_points_query);
$quote_press_points = mysql_result($quote_press_points_result, 0, "press_points");
$quote_press_charge = mysql_result($quote_press_points_result, 0, "press_charge");
$quote_press_point_total = $quote_press_points * $quote_press_charge; 

$table_name = "tlh_cutting_bill_lines_done";


$produced_press_point_total = 0;
$table_name = "tlh_production_bill_lines";
$produced_delivered_query = "SELECT delivered, press_points, press_charge FROM $table_name WHERE job_no = '".$job_no."' ";
$produced_delivered_result = mySQL_query($produced_delivered_query);
$produced_delivered_num = mysql_num_rows($produced_delivered_result);

$i=0;
while ($i<$produced_delivered_num) {
	$produced_delivered = mysql_result($produced_delivered_result, $i, "delivered");
	$produced_press_points = mysql_result($produced_delivered_result, $i, "press_points");
	$produced_press_charge = mysql_result($produced_delivered_result, $i, "press_charge");
	$produced_press_point_sub_total = $produced_delivered * $produced_press_points * $produced_press_charge;
	$produced_press_point_total = $produced_press_point_total + $produced_press_point_sub_total;
	$i++;
}

$table_name = "tlh_captured_timber_done";
$quoted_timber_query = "SELECT * FROM $table_name WHERE cap_timber_group = 'Trusses' AND cap_job = '".$job_no."'  ";
$quoted_timber_result = mySQL_query($quoted_timber_query);
$quoted_timber_num = mysql_num_rows($quoted_timber_result);



$truss_timber_query = "SELECT cap_timber, cap_timber_length, auth_timber_length, cap_timber_price, auth_timber_price FROM $table_name WHERE cap_timber_name = 'Trusses' AND cap_job = '".$job_no."'  ";
$truss_timber_result = mySQL_query($truss_timber_query);
$truss_timber_num = mysql_num_rows($truss_timber_result);

$quoted_truss_timber_total=0;
$i=0;
while ($i<$quoted_timber_num) {
	$quoted_truss_timber_length = mysql_result($truss_timber_result, $i, "cap_timber_length");
	$quoted_truss_timber_price = mysql_result($truss_timber_result, $i, "cap_timber_price");
	$quoted_truss_timber_charge = $quoted_truss_timber_length * $quoted_truss_timber_price;	
	$quoted_truss_timber_total = $quoted_truss_timber_total + $quoted_truss_timber_charge;
	$i++;
}

$table_name = "tlh_cutting_bill_lines_done";
$cutting_truss_query = "SELECT quantity, price FROM $table_name WHERE type = 'timber' AND bill_id = '".$bill_id."' GROUP BY store_item_id";
$cutting_truss_result = mySQL_query($cutting_truss_query);
$cutting_truss_num = mysql_num_rows($cutting_truss_result);

$cutting_truss_timber_total=0;
$i=0;
while ($i<$cutting_truss_num) {
	$cutting_truss_timber_quantity = mysql_result($cutting_truss_result, $i, "quantity");
	$cutting_truss_timber_price = mysql_result($cutting_truss_result, $i, "price");
	$cutting_truss_timber_charge = $cutting_truss_timber_quantity * $cutting_truss_timber_price;	
	$cutting_truss_timber_total = $cutting_truss_timber_total + $cutting_truss_timber_charge;
	$i++;
}
/* =====   end truss timber charges */

/* =====   begin truss plate charges */
$table_name = "tlh_captured_jobs";
$quote_plates_query = "SELECT plate_area, plate_charge FROM $table_name WHERE job_no = '".$job_no."' ";
$quote_plates_result = mySQL_query($quote_plates_query);
$quote_plates_area = mysql_result($quote_plates_result, 0, "plate_area");
$quote_plates_charge = mysql_result($quote_plates_result, 0, "plate_charge");
$quote_plates_charge_total = $quote_plates_area * $quote_plates_charge; 

$table_name = "tlh_cutting_bill_lines_done";
$cutting_plate_query = "SELECT quantity, price FROM $table_name WHERE type = 'plate' AND bill_id = '".$bill_id."' GROUP BY store_item_id";
$cutting_plate_result = mySQL_query($cutting_plate_query);
$cutting_plate_num = mysql_num_rows($cutting_plate_result);

$cutting_plate_total=0;
$i=0;
while ($i<$cutting_plate_num) {
	$cutting_plate_quantity = mysql_result($cutting_plate_result, $i, "quantity");
	$cutting_plate_price = mysql_result($cutting_plate_result, $i, "price");
	$cutting_plate_charge = $cutting_plate_quantity * $cutting_plate_price;	
	$cutting_plate_total = $cutting_plate_total + $cutting_plate_charge;
	$i++;
}


/* =====   end truss plate charges */

/* =====   begin mitec products */
$table_name = "tlh_captured_mitek_done";
$mitek_query = "SELECT cap_item_id, cap_quantity, cap_price,  auth_quantity, del_price FROM $table_name WHERE cap_job = '".$job_no."'  GROUP BY cap_item_id";
$mitek_result = mySQL_query($mitek_query);
$mitek_num = mysql_num_rows($mitek_result);

$cap_mitek_total=0;
$auth_mitek_total=0;
$i=0;
while ($i<$mitek_num) {
	$cap_quantity = mysql_result($mitek_result, $i, "cap_quantity");
	$cap_price = mysql_result($mitek_result, $i, "cap_price");
	$auth_quantity = mysql_result($mitek_result, $i, "auth_quantity");
	$auth_price = mysql_result($mitek_result, $i, "del_price");

	$cap_total = $cap_quantity * $cap_price;
	$cap_mitek_total = $cap_mitek_total + $cap_total;
	$auth_total = $auth_quantity * $auth_price;
	$auth_mitek_total = $auth_mitek_total + $auth_total;
	
	$i++;
}


$table_name = "tlh_captured_timber_done";
$bracing_timber_query = "SELECT cap_timber, cap_timber_length, cap_timber_price, auth_timber_length, auth_timber_price  FROM $table_name WHERE cap_timber_group = 'Bracing' AND cap_job = '".$job_no."'  "; 
$bracing_timber_result = mySQL_query($bracing_timber_query);
$bracing_timber_num = mysql_num_rows($bracing_timber_result);

$cap_bracing_total=0;
$auth_bracing_total=0;
$i=0;
while ($i<$bracing_timber_num) {
	$cap_timber_length = mysql_result($bracing_timber_result, $i, "cap_timber_length");
	$cap_timber_price = mysql_result($bracing_timber_result, $i, "cap_timber_price");
	$auth_timber_length = mysql_result($bracing_timber_result, $i, "auth_timber_length");
	$auth_timber_price = mysql_result($bracing_timber_result, $i, "auth_timber_price");

	$cap_total = $cap_timber_length * $cap_timber_price;
	$cap_bracing_total = $cap_bracing_total + $cap_total;
	$auth_total = $auth_timber_length * $auth_timber_price;
	$auth_bracing_total = $auth_bracing_total + $auth_total;
	
	$i++;
}

/* =====  end bracing timber  */

/* =====  begin sundries timber  */
$table_name = "tlh_captured_timber_done";
$sundries_timber_query = "SELECT cap_timber, cap_timber_length, cap_timber_price, auth_timber_length, auth_timber_price  FROM $table_name WHERE cap_timber_group = 'Sundries' AND cap_job = '".$job_no."'  "; 
$sundries_timber_result = mySQL_query($sundries_timber_query);
$sundries_timber_num = mysql_num_rows($sundries_timber_result);

$cap_sundries_total=0;
$auth_sundries_total=0;
$i=0;
while ($i<$sundries_timber_num) {
	$cap_timber_length = mysql_result($sundries_timber_result, $i, "cap_timber_length");
	$cap_timber_price = mysql_result($sundries_timber_result, $i, "cap_timber_price");
	$auth_timber_length = mysql_result($sundries_timber_result, $i, "auth_timber_length");
	$auth_timber_price = mysql_result($sundries_timber_result, $i, "auth_timber_price");

	$cap_total = $cap_timber_length * $cap_timber_price;
	$cap_sundries_total = $cap_sundries_total + $cap_total;
	$auth_total = $auth_timber_length * $auth_timber_price;
	$auth_sundries_total = $auth_sundries_total + $auth_total;
	
	$i++;
}

/* =====  end sundries timber  */


/* =====   begin additional sundries products */
$table_name = "tlh_captured_sundries_done";
$add_sundries_query = "SELECT cap_item_id, cap_quantity, cap_price,  auth_quantity, del_price FROM $table_name WHERE cap_job = '".$job_no."'  GROUP BY cap_item_id";
$add_sundries_result = mySQL_query($add_sundries_query);
$add_sundries_num = mysql_num_rows($add_sundries_result);

$cap_add_sundries_total=0;
$auth_add_sundries_total=0;
$i=0;
while ($i<$add_sundries_num) {
	$cap_quantity = mysql_result($add_sundries_result, $i, "cap_quantity");
	$cap_price = mysql_result($add_sundries_result, $i, "cap_price");
	$auth_quantity = mysql_result($add_sundries_result, $i, "auth_quantity");
	$auth_price = mysql_result($add_sundries_result, $i, "del_price");

	$cap_total = $cap_quantity * $cap_price;
	$cap_add_sundries_total = $cap_add_sundries_total + $cap_total;
	$auth_total = $auth_quantity * $auth_price;
	$auth_add_sundries_total = $auth_add_sundries_total + $auth_total;
	
	$i++;
}

/* =====  end additional sundries products  */

/* =====  begin variations  */

$table_name6 = "tlh_variations";
$variations_query = "SELECT delivered, price FROM $table_name6 WHERE job_no = '".$job_no."' ";
$variations_result = mySQL_query($variations_query);
$variations_num = mysql_num_rows($variations_result);

$variations_total = 0;
$i = 0;
while ($i<$variations_num) {
	$var_quantity = mysql_result($variations_result, $i, "delivered");
	$var_price = mysql_result($variations_result, $i, "price");
	
	$var_total = $var_quantity * $var_price;
	$variations_total = $variations_total + $var_total;
	$i++;
}

/* =====  end variations  */


$final_value = $produced_press_point_total + $cutting_truss_timber_total + $cutting_plate_total + $auth_mitek_total + $auth_bracing_total + $auth_sundries_total + $auth_add_sundries_total + $variations_total;

$result = $job_value - $final_value;


$job_value_total = $job_value_total + $job_value;
$quoted_value_total = $quoted_value_total + $captured_total;
$final_value_total = $final_value_total + $final_value;
$result_total = $result_total + $result;


if ($job_value>0) {
$result_perc = ($result/$job_value) * 100;
}
else {
	$result_perc = 0;
}

$table_name3 = "tlh_customer";
         			$customer_query = "SELECT comp_name, status, bag_box, box_no, postal_place, phone, contact_1 FROM $table_name3 WHERE cust_id = '".$cust_id."' ";
		 			$customer_result = mySQL_query($customer_query);
         			$a2a = mysql_result($customer_result, 0, "comp_name");
		 			$a2b = mysql_result($customer_result, 0, "bag_box");
		 			$a2c = mysql_result($customer_result, 0, "box_no");
		 			$a2d = mysql_result($customer_result, 0, "postal_place");
		 			$a2e = mysql_result($customer_result, 0, "phone");
		 			$a2f = mysql_result($customer_result, 0, "contact_1");
										
					$table_name = "tlh_captured_jobs";
					$cust_id_query = "SELECT date, cust_id, project_no, project_place, project_description, designer FROM $table_name WHERE job_no = '".$job_no."'  ";
					$cust_id_result = mySQL_query($cust_id_query);
					
					$b1 = mysql_result($cust_id_result, 0, "date");
					$b9 = mysql_result($cust_id_result, 0, "project_no");
					$b10 = mysql_result($cust_id_result, 0, "project_place");
					$b11 = mysql_result($cust_id_result, 0, "project_description");
					$b12 = mysql_result($cust_id_result, 0, "designer");
										
		$customerTT ="<table id=table_tool_tip width=250 ><tr><td  width=75>Company:</td><td width=175>".$a2a."</td></tr><tr><td>Address:</td><td>".$a2b."&nbsp;".$a2c."<br>".$a2d."</td></tr><tr><td>Phone:</td><td>".$a2e."</td></tr><tr><td>Contact:</td><td>".$a2f."</td></tr></table><br><b>Click for details</b>";
		
		$jobTT ="<table id=table_tool_tip width=300 ><tr><td  width=100>Job no:</td><td width=175>".$job_no."</td></tr><tr><td valign=top>Project no /place:</td><td>".$b9."&nbsp;".$b10."</td></tr><tr><td>Project description:</td><td>".$b11."</td></tr><tr><td>Designer:</td><td>".$b12."</td></tr></table><br><b>Click for details</b>";		
?>

    
    
    
    <tr>
      <td><a href="costing_job.php?job_no=<?php echo $job_no ?>&amp;load=0 " onmouseover="ddrivetip('<?php echo$jobTT?>', 300)"; onmouseout="hideddrivetip()"><?php echo 'J'.$job_no?></a></td>
      <td><a href="costing_job.php?job_no=<?php echo $job_no ?>&amp;load=0 " onmouseover="ddrivetip('<?php echo$jobTT?>', 300)"; onmouseout="hideddrivetip()"><?php echo $job_no ?></a></td>
      <td><?php echo date("d-m-Y", strtotime($delivery_date))?></td>
      <td><?php echo $delivery_date ?></td>
      <td><a href="../sales/customer_details.php?cust_id=<?php echo $cust_id ?>" onmouseover="ddrivetip('<?php echo $customerTT?>', 300)"; onmouseout="hideddrivetip()"><?php echo $a2a?></a>&nbsp;</td>
      <td align="right"><?php echo number_format($job_value, 2, '.', ',') ?></td>
      <td align="right"><?php echo $job_value ?></td>
      <td align="right"><?php echo number_format($captured_total, 2, '.', ',') ?></td>
      <td align="right"><?php echo $quoted_value ?></td>
      <td align="right"><?php echo number_format($prog_result, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($final_value, 2, '.', ',') ?></td>
      <td align="right"><?php echo $final_value ?></td>
      <td align="right"><?php echo number_format($result, 2, '.', ',') ?></td>
      <td align="right"><?php echo $result ?></td>
      <td align="right"><?php if ($prog_result>$result_perc) {  echo "<font color=#FF3300>".number_format($result_perc, 2, '.', ',')."</font>"; } else { echo number_format($result_perc, 2, '.', ','); } ?></td>
      <td><?php echo $designer ?>&nbsp;</td>
      <td><?php echo $seller ?></td>
    </tr>
    <?php
	$z++;
}
$prognose_perc_average = (($job_value_total-$quoted_value_total)/$job_value_total)*100;
$result_perc_average = ($result_total/$job_value_total) * 100;
?>
<tfoot>
    <tr>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right"><?php echo number_format($job_value_total, 2, '.', ',') ?></td>
      <td align="right">&nbsp;</td>
      <td align="right"><?php echo number_format($quoted_value_total, 2, '.', ',') ?></td>
      <td align="right">&nbsp;</td>
      <td align="right"><?php echo number_format($prognose_perc_average, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($final_value_total, 2, '.', ',') ?></td>
      <td align="right">&nbsp;</td>
      <td align="right"><?php echo number_format($result_total, 2, '.', ',') ?></td>
      <td align="right">&nbsp;</td>
      <td align="right"><?php echo number_format($result_perc_average, 2, '.', ',') ?></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    </tfoot>
  </table>
<?php } else { ?>
No data available in this report for the selected time period.
<?php } ?>
  </div>
 
  </div>
  


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




</body>

<?php } else {
$_SESSION['denied_return'] = "production/production_main.php";	
include ("../navigation/production_sidebar.php");?>
<body onload="accessdenied(); return false">
</body>
<?php }
?>

</html>