<?php
require ("config.php");
$dbDatabase = "contactus"; 

$sConn = mysql_connect($dbServer, $dbUser, $dbPass) 
or die("Couldn't connect to database server"); 

$dConn = mysql_select_db($dbDatabase, $sConn) 
or die("Couldn't connect to database $dbDatabase");

$current_date = date("Y-m-d");

$query = "select * from admin_settings order by admin_settings_id desc limit 0,1";
$result = mysql_query($query,$sConn);
$row = mysql_fetch_array($result);

$admin_settings = $row;

$start_date = "";
$end_date = "";

if(isset($_POST['start_date']) && isset($_POST['end_date']) && $_POST['start_date']!='' && $_POST['end_date']!=''){
	$start_date = explode("-",$_POST['start_date']);
	$start_date = $start_date[2]."-".$start_date[1]."-".$start_date[0];
	
	$end_date = explode("-",$_POST['end_date']);
	$end_date = $end_date[2]."-".$end_date[1]."-".$end_date[0];

$report_dates = array();
$report_weeks = array();
	
	$query = "select distinct WEEK(report_date) AS weeks from website_sales where report_date>='$start_date' && report_date<='$end_date'";

$query2 = "SELECT DISTINCT EXTRACT(YEAR FROM `report_date`) AS `year` FROM website_sales WHERE report_date>='$start_date' && report_date<='$end_date' ORDER BY report_date ASC";
}else{
	$query = "select distinct WEEK(report_date) AS weeks from website_sales";
	//echo $query;
	$query2 = "SELECT DISTINCT EXTRACT(YEAR FROM `report_date`) AS `year` FROM website_sales ORDER BY report_date ASC";
}

$result = mysql_query($query,$sConn);
while($row = mysql_fetch_array($result)){
	$report_weeks[] = $row['weeks'];
}

$result2 = mysql_query($query2,$sConn);
while($row = mysql_fetch_array($result2)){
	$report_dates[] = $row['report_date'];
	$year = $row['year'];
	//echo $year;
}

//Sort an array in reverse order
rsort($report_weeks);

//get the retailers
$select_query = "select * from shared_retailers";
$result = mysql_query($select_query,$sConn);
$retailers = array();
while($row = mysql_fetch_array($result)){
	$retailers[] = $row;
}

$weekly_website_sales = array();
$weekly_shared_retailers = array();
$weekly_phone_sales = array();

foreach($report_weeks as $report_week){

	$query = "select * from website_sales where WEEK(report_date) = '$report_week' order by website_sales_id desc";
	$result = mysql_query($query,$sConn);
	while ($row = mysql_fetch_array($result)) {
	$weekly_website_sales[$report_week] += $row['total_amount'];
	$admin_settings_id = $row['admin_settings_id'];
	}

	//Admin Settings Variables
	$select_query = "select * from admin_settings where WEEK(report_date) = '$report_week' limit 0,1";
	//echo $select_query;
	$result = mysql_query($select_query,$sConn);
	if (mysql_num_rows($result) < 1)
	{
	$query = "select * from admin_settings where admin_settings_id=". $admin_settings_id ." limit 0,1";
	$result = mysql_query($query,$sConn);
	}
	$row = mysql_fetch_array($result);
	
	$websales_target = $row['websales_target'];
	$phone_target = $row['phone_target'];
	$shared_target = $row['shared_target'];
	//$total_monthly_target = $row['total_monthly_target'];
	$total_monthly_target = $websales_target + $phone_target + $shared_target;
    $target_revenue[$report_week] = $total_monthly_target/4.3;
	$websales_target = $websales_target/4.3;
	$phone_target = $phone_target/4.3;
	$shared_target = $shared_target/4.3;
	
	foreach($retailers as $retailer){
		$query = "select * from shared_retailers_trans where WEEK(report_date)='$report_week' and shared_retailers_id=".$retailer['shared_retailers_id']." order by shared_retailers_trans_id desc";
		//echo $query;
		$result = mysql_query($query,$sConn);
		while ($row = mysql_fetch_array($result)) {
		$weekly_shared_retailers[$report_week][$retailer['shared_retailers_name']] += $row['amount'];
		}
	}

	$query = "select * from phone_sales where WEEK(report_date) = '$report_week' order by phone_sales_id desc";
	//echo $query;
	$result = mysql_query($query,$sConn);
	while ($row = mysql_fetch_array($result)) {
	$weekly_phone_sales[$report_week] += $row['total_amount'];
	}
}

$total_amount = array();
$total_shared = array();
foreach($report_weeks as $report_week){
	$total_amount[$report_week] = $weekly_website_sales[$report_week];
	foreach($retailers as $retailer){
	    $total_shared[$report_week] += $weekly_shared_retailers[$report_week][$retailer['shared_retailers_name']];
		$total_amount[$report_week] += $weekly_shared_retailers[$report_week][$retailer['shared_retailers_name']];
	}
	$total_amount[$report_week] += $weekly_phone_sales[$report_week];
}

$header1 = '';
$data = ''; //start clean data

$header1 = "The Week Start	Week Total $	Weekly Target	Website Sales	Website Target	Shared Total $	Shared Target	Phone Sales	Phone Target"; //start line for excell output. 

foreach($report_weeks as $report_week){
$line = ''; //start line for excell output.
$line = date("Y-m-d", StartOfWeek($year,$report_week)) . "	" . "$" . number_format($total_amount[$report_week],'2','.',',') . "	" . "$" . number_format($target_revenue[$report_week],'2','.',',') . "	" . "$" . number_format($weekly_website_sales[$report_week],'2','.',',') . "	" . "$" . number_format($websales_target,'2','.',',') . "	" . "$" . number_format($total_shared[$report_week],'2','.',',') . "	" .  "$" . number_format($shared_target,'2','.',',') . "	" . "$" . number_format($weekly_phone_sales[$report_week],'2','.',',') . "	" . "$" . number_format($phone_target,'2','.',',');

$data .= trim($line)."\n"; //add the line to the data
}
 
header("Content-type: application/x-msdownload");
header( 'Cache-Control: must-revalidate, post-check=0, pre-check=0' );
header( 'Content-Transfer-Encoding: binary' );
//  header("Content-Disposition attachment; filename=".$filename);
header("Content-Disposition: attachment; filename=weeklyReport.xls");
header("Pragma: public");
header("Expires: 0");

print $header1 . "\n" . $data;

// Added _much_ later....
function StartOfWeek($year, $week)
{
    $Jan1 = mktime(1,1,1,1,1,$year);
    $MondayOffset = (11-date('w',$Jan1))%7-3;
    $desiredMonday = strtotime(($week-1) . ' weeks '.$MondayOffset.' days', $Jan1);
    return $desiredMonday;
} 
?>