<?php

include('../include/session.php');
$today = date("Y-m-d");

$report_year = $_GET['report_year_select'];
$report_type = $_GET['report_type'];


$filename = "Product_distribution_".$report_year."_(".$today.").csv";
$fp = fopen('php://output', 'w');	

//$table_name = $_SESSION['db_prefix']."_permissions_2";
//$permission_query = "SELECT access_id  FROM  $table_name  WHERE access_id ='". $_SESSION['access_id']."' AND EM_VM = '1' ";
//$permission_result = mySQL_query($permission_query);
//$man_permission_num = mysql_num_rows($permission_result);


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);

$p=0;
while ($p<12) {
	$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']."_distribution2_csv";
$temp_query = "INSERT INTO $table_name (Months, Total, Double_Roman, DR_perc, Slate, SL_perc, Venetian, VN_perc, Accessories, Acc_perc) VALUES ('".$order_month."', '".$all_total."', '".$double_roman_quantity."', '".$double_roman_perc."', '".$slate_quantity."', '".$slate_perc."', '".$venetian_quantity."', '".$venetian_perc."', '".$accessories_quantity."', '".$accessories_perc."') ";
execsql($temp_query);


$p++;
}


$table_name = $_SESSION['db_prefix']."_distribution2_csv";
$query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='tocotex_web15db2' AND TABLE_NAME='$table_name'";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {
	$header[] = $row[0];
}	
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);

$num_column = count($header);		
$query = "SELECT Months, Total, Double_Roman, DR_perc, Slate, SL_perc, Venetian, VN_perc, Accessories, Acc_perc FROM $table_name";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
	fputcsv($fp, $row);
}

$table_name = $_SESSION['db_prefix']."_distribution2_csv";
$delete_query = "DELETE FROM $table_name "; 
execsql($delete_query);

exit;
?>