<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php 
include('../include/session.php');
$today = date("Y-m-d");
	
$report_year = $_GET['report_year_select'];
$report_type = $_GET['report_type'];



if ($report_type==1) {
$report_cat = 'Tiles and Accessories';
$table_name = $_SESSION['db_prefix']."_order_lines";
$total_query = "SELECT order_date, SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' GROUP BY EXTRACT(month FROM order_date) ";
$total_result = mySQL_query($total_query);
$total_num = mysql_num_rows($total_result);
}

if ($report_type==2) {
$report_cat = 'Tiles';
$table_name = $_SESSION['db_prefix']."_order_lines";
$total_query = "SELECT order_date, SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND sub_category = 'Tiles'  GROUP BY EXTRACT(month FROM order_date) ";
$total_result = mySQL_query($total_query);
$total_num = mysql_num_rows($total_result);
}

if ($report_type==3) {
$report_cat = 'Accessories';
$table_name = $_SESSION['db_prefix']."_order_lines";
$total_query = "SELECT order_date, SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND sub_category = 'Accessories'  GROUP BY EXTRACT(month FROM order_date) ";
$total_result = mySQL_query($total_query);
$total_num = mysql_num_rows($total_result);
}

$table_name = $_SESSION['db_prefix']."_order_lines";
$table_name2 = $_SESSION['db_prefix']."_store_items";
$double_roman_query = "SELECT SUM($table_name.quantity) AS total_quantity FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."'  AND $table_name.store_item_id = $table_name2.store_item_id AND $table_name2.item_type = 'Double Roman'  GROUP BY EXTRACT(month FROM order_date) ";
$double_roman_result = mySQL_query($double_roman_query);
$double_roman_num = mysql_num_rows($double_roman_result);


$table_name = $_SESSION['db_prefix']."_order_lines";
$table_name2 = $_SESSION['db_prefix']."_store_items";
$slate_query = "SELECT SUM($table_name.quantity) AS total_quantity FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."'  AND $table_name.store_item_id = $table_name2.store_item_id AND $table_name2.item_type = 'Slate'  GROUP BY EXTRACT(month FROM order_date) ";
$slate_result = mySQL_query($slate_query);
$slate_num = mysql_num_rows($slate_result);


$table_name = $_SESSION['db_prefix']."_order_lines";
$table_name2 = $_SESSION['db_prefix']."_store_items";
$venetian_query = "SELECT SUM($table_name.quantity) AS total_quantity FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."'  AND $table_name.store_item_id = $table_name2.store_item_id AND $table_name2.item_type = 'Venetian'  GROUP BY EXTRACT(month FROM order_date) ";
$venetian_result = mySQL_query($venetian_query);
$venetian_num = mysql_num_rows($venetian_result);

$table_name = $_SESSION['db_prefix']."_order_lines";
$accessories_query = "SELECT SUM(quantity) AS total_quantity FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."'  AND sub_category = 'Accessories'  GROUP BY EXTRACT(month FROM order_date) ";
$accessories_result = mySQL_query($accessories_query);
$accessories_num = mysql_num_rows($accessories_result);

$report_name = 'Product distribution on distributor orders '.$report_year; 
$report_no = '';
?>
<!--style sheets-->
<link href="../css/print.css" rel="stylesheet" type="text/css" />
<link rel="shortcut icon" type="image/x-icon" href="../images/favicon.ico">
<title>Product distribution on distributor orders <?php echo $report_year.' ('.$today.')' ?> </title>


</head>

<body>

<script>
window.onload = function () {
  window.print();
  setTimeout(function(){window.close();}, 100);
}
</script>


<div style="float:left;"><img src="../images/radstone_medium_logo.png" width="144" height="54"></div>
<div style="float:left; margin-left:20px;"><label style="font-size:18px;"><b> <?php echo $report_name ?> </b></label><br>
<br>
Report date: <?php echo date("d-m-Y", strtotime($today)) ?> </div> 
<br>
<br>

<div class="print_standard_frame">

 Tiles and Accssories distribution on distributor orders <?php echo $report_year ?> 
<table width="600" border="1" cellspacing="3" cellpadding="3" style="border-collapse:collapse;" >
<thead>
  <tr>
    <td align="center">Month</td>
    <td align="center">Total</td>
    <td align="center">Double Roman</td>
    <td align="center">in %</td>
    <td align="center">Slate</td>
    <td align="center">in %</td>
    <td align="center">Venetian</td>
    <td align="center">in %</td>
    <td align="center">Accessories</td>
    <td align="center">in %</td>
    </tr>
</thead>
<?php 
$p=0;
while ($p<$total_num) {
	$order_month =  mysql_result($total_result, $p, "order_date");
	$order_month = date("F", strtotime($order_month));
	$all_total = mysql_result($total_result, $p, "total_quantity");
	$month_no = date("m", strtotime($order_month));
	
	$double_roman_quantity = mysql_result($double_roman_result, $p, "total_quantity");
	$slate_quantity = mysql_result($slate_result, $p, "total_quantity");
	$venetian_quantity = mysql_result($venetian_result, $p, "total_quantity");
	$accessories_quantity = mysql_result($accessories_result, $p, "total_quantity");
	
	$double_roman_perc = ($double_roman_quantity / $all_total) * 100;
	$slate_perc = ($slate_quantity / $all_total) * 100;
	$venetian_perc = ($venetian_quantity / $all_total) * 100;
	$accessories_perc = ($accessories_quantity / $all_total) * 100;
	
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$full_total_query = "SELECT SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' ";
	$full_total_result = mySQL_query($full_total_query);
	$full_total = mysql_result($full_total_result, 0, "total_quantity");
	
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$table_name2 = $_SESSION['db_prefix']."_store_items";
	$double_roman_total_query = "SELECT SUM($table_name.quantity) AS total_quantity  FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND $table_name.store_item_id = $table_name2.store_item_id AND item_type = 'Double Roman' ";
	$double_roman_total_result = mySQL_query($double_roman_total_query);
	$double_roman_total = mysql_result($double_roman_total_result, 0, "total_quantity");
	
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$table_name2 = $_SESSION['db_prefix']."_store_items";
	$slate_total_query = "SELECT SUM($table_name.quantity) AS total_quantity  FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND $table_name.store_item_id = $table_name2.store_item_id AND item_type = 'Slate' ";
	$slate_total_result = mySQL_query($slate_total_query);
	$slate_total = mysql_result($slate_total_result, 0, "total_quantity");
	
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$table_name2 = $_SESSION['db_prefix']."_store_items";
	$venetian_total_query = "SELECT SUM($table_name.quantity) AS total_quantity  FROM $table_name, $table_name2 WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND $table_name.store_item_id = $table_name2.store_item_id AND item_type = 'Venetian' ";
	$venetian_total_result = mySQL_query($venetian_total_query);
	$venetian_total = mysql_result($venetian_total_result, 0, "total_quantity");
	
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$accessories_total_query = "SELECT SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."'  AND sub_category = 'Accessories' ";
	$accessories_total_result = mySQL_query($accessories_total_query);
	$accessories_total = mysql_result($accessories_total_result, 0, "total_quantity");
	
	$double_roman_full_perc = ($double_roman_total / $full_total) * 100;
	$slate_full_perc = ($slate_total / $full_total) * 100;
	$venetian_full_perc = ($venetian_total / $full_total) * 100;
	$accessories_full_perc = ($accessories_total / $full_total) * 100;
?>	    
  <tr>
    <td><?php echo $order_month ?></td>
    <td align="right"><?php echo $all_total ?></td>
    <td align="right"><?php echo $double_roman_quantity ?></td>
    <td align="right"><?php echo number_format($double_roman_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $slate_quantity ?></td>
    <td align="right"><?php echo number_format($slate_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $venetian_quantity ?></td>
    <td align="right"><?php echo number_format($venetian_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $accessories_quantity ?></td>
    <td align="right"><?php echo number_format($accessories_perc, 1, '.', ',') ?></td>
  </tr>
<?php
$p++;
}
?>  
<tfoot>
	<tr>
    <td>&nbsp;</td>
    <td align="right"><?php echo $full_total ?> </td>
    <td align="right"><?php echo $double_roman_total ?></td>
    <td align="right"><?php echo number_format($double_roman_full_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $slate_total ?></td>
    <td align="right"><?php echo number_format($slate_full_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $venetian_total ?></td>
    <td align="right"><?php echo number_format($venetian_full_perc, 1, '.', ',') ?></td>
    <td align="right"><?php echo $accessories_total ?></td>
    <td align="right"><?php echo number_format($accessories_full_perc, 1, '.', ',') ?></td>
  </tr>
</tfoot>
</table>

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

</body>


