<?php

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

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


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


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


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


$m=0;
while ($m<20) {
	${'qty'.$m} = '';
	${'perc'.$m} = '';
	$m++;
}


$columns = 0;
	$k=0;
	while ($k<$line_num) {
	
	 
	  		$cust_id = $_SESSION['cust_id'.$k];
						
				$table_name2 = $_SESSION['db_prefix']."_customer";
				$customer_query = "SELECT account_type, title, name_2, comp_name FROM $table_name2 WHERE cust_id ='".$cust_id."' ";
				$customer_result = mySQL_query($customer_query);
				$customer_num = mysql_num_rows($customer_result);	
				$account_type = mysql_result($customer_result, 0, "account_type");
				if ($account_type=="corporate") { 
					$cust_name = mysql_result($customer_result, 0, "comp_name"); 
				} 
				else {
					$cust_name = mysql_result($customer_result, 0, "title")." ".mysql_result($customer_result, 0, "name_2")." ".mysql_result($customer_result, 0, "comp_name");
 				}
			
			${'column_cust_id'.$columns} = $cust_id;
			
			$columns = $columns + 1;
					  	
	${'qty'.$k} = $cust_name;
	
	$k++;
	}
	
$table_name = $_SESSION['db_prefix']."_distribution1_csv";
$temp_query = "INSERT INTO $table_name (Months, total_sales, qty_1, perc_1, qty_2, perc_2, qty_3, perc_3, qty_4, perc_4, qty_5, perc_5, qty_6, perc_6) VALUES ('--', '--', '".$qty0."', '%', '".$qty1."', '%', '".$qty2."', '%', '".$qty3."', '%', '".$qty4."', '%', '".$qty5."', '%') ";
//echo '<br>*****'.$temp_query;
execsql($temp_query);	


$i=0;
while ($i<$total_num) {
	$order_month =  mysql_result($total_result, $i, "order_date");
	$order_month = date("F", strtotime($order_month));
	$all_total = mysql_result($total_result, $i, "total_quantity");
	$month_no = date("m", strtotime($order_month));

	
	
	$k=0;
	while ($k<$columns) {
	
	$cust_id = $_SESSION['cust_id'.$k];	
	$month_period = $month_no;
	
	
if ($report_type==1) {
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$cust_total_query = "SELECT SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND EXTRACT(month FROM order_date) = '".$month_period."' AND cust_id = '".$cust_id."'  GROUP BY EXTRACT(month FROM order_date) ";
	
	$cust_total_result = mySQL_query($cust_total_query);
	$cust_total_num = mysql_num_rows($cust_total_result);
	if ($cust_total_num>0) {
	$cust_total = mysql_result($cust_total_result, 0, "total_quantity");
	}
	else {
    $cust_total = 0;
	}
	$perc = ($cust_total / $all_total) * 100;
}

if ($report_type==2) {
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$cust_total_query = "SELECT SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND EXTRACT(month FROM order_date) = '".$month_period."' AND cust_id = '".$cust_id."' AND sub_category = 'Tiles'  GROUP BY EXTRACT(month FROM order_date) ";
	$cust_total_result = mySQL_query($cust_total_query);
	$cust_total_num = mysql_num_rows($cust_total_result);
	if ($cust_total_num>0) {
	$cust_total = mysql_result($cust_total_result, 0, "total_quantity");
	}
	else {
    $cust_total = 0;
	}
	$perc = ($cust_total / $all_total) * 100;
}

if ($report_type==3) {
	$table_name = $_SESSION['db_prefix']."_order_lines";
	$cust_total_query = "SELECT SUM(quantity) AS total_quantity  FROM $table_name WHERE EXTRACT(YEAR FROM order_date) = '".$report_year."' AND EXTRACT(month FROM order_date) = '".$month_period."' AND cust_id = '".$cust_id."' AND sub_category = 'Accessories'  GROUP BY EXTRACT(month FROM order_date) ";
	$cust_total_result = mySQL_query($cust_total_query);
	$cust_total_num = mysql_num_rows($cust_total_result);
	if ($cust_total_num>0) {
	$cust_total = mysql_result($cust_total_result, 0, "total_quantity");
	}
	else {
    $cust_total = 0;
	}
	$perc = ($cust_total / $all_total) * 100;
}


//echo '<br>*****'.$i.' '.$k.'='.$cust_total.' '.number_format($perc, 1, '.', ',');
${'qty'.$k} = $cust_total;
${'perc'.$k} = number_format($perc, 2, '.', ',');

$k++;
	}
	
$table_name = $_SESSION['db_prefix']."_distribution1_csv";
$temp_query = "INSERT INTO $table_name (Months, total_sales, qty_1, perc_1, qty_2, perc_2, qty_3, perc_3, qty_4, perc_4, qty_5, perc_5, qty_6, perc_6) VALUES ('".$order_month."', '".$all_total."', '".$qty0."', '".$perc0."', '".$qty1."', '".$perc1."', '".$qty2."', '".$perc2."', '".$qty3."', '".$perc3."', '".$qty4."', '".$perc4."', '".$qty5."', '".$perc5."') ";
//echo '<br>*****'.$temp_query;
execsql($temp_query);	

$i++;
}

$table_name = $_SESSION['db_prefix']."_distribution1_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_sales, qty_1, perc_1, qty_2, perc_2, qty_3, perc_3, qty_4, perc_4, qty_5, perc_5, qty_6, perc_6 FROM $table_name";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
	fputcsv($fp, $row);
}

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

exit;
?>