<?php
$hostname = "localhost";
$username = "manase";
$password = "2014manase";

$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

$selected = mysql_select_db("man2014",$dbhandle)
  or die("Could not select man2014");

date_default_timezone_set('Africa/Gaborone');
$entry_date = date("Y-m-d H:i:s"); 

$comp_date = date("Y-m-d");
//$comp_date = '2014-08-05';
if (date("l", strtotime($comp_date))=='Monday') {
  $select_date1 = strtotime(date("Y-m-d", strtotime($comp_date)) . "-3 day");
  $select_date2 = strtotime(date("Y-m-d", strtotime($comp_date)) . "-1 day");
  $select_date1 = date("Y-m-d", $select_date1);
  $select_date2 = date("Y-m-d", $select_date2);
}
else {
   $select_date = strtotime(date("Y-m-d", strtotime($comp_date)) . "-1 day");
   $select_date1 = date("Y-m-d", $select_date);
   $select_date2 = date("Y-m-d", $select_date);
}

$table_name2 = "man_quotations";
$quotation_query = "SELECT quote_no, status, cust_id, type, project, quotation_date, required_date, company_contact, contact_phone, project, project_no, project_place, seller, value, vat_text FROM $table_name2 WHERE quotation_date BETWEEN '".$select_date1."' AND '".$select_date2."' ORDER BY quote_no DESC ";
$quotation_result = mySQL_query($quotation_query);
$quotation_num = mysql_num_rows($quotation_result);


$table_name2 = "man_quotations";
$table_name3 = "man_compiled_jobs";
$success_query = "SELECT job_no, date, $table_name3.job_card_no, $table_name3.cust_id, $table_name3.project, $table_name3.project_no, $table_name3.project_place, $table_name3.value, company_contact, contact_phone, seller FROM $table_name3, $table_name2 WHERE date BETWEEN '".$select_date1."' AND '".$select_date2."' AND quote_no=job_no ORDER BY DATE DESC";
$success_result = mySQL_query($success_query);
$success_num = mysql_num_rows($success_result);	


$i=0;
$a7tot=0;
$price=0;
$quotations_content = '';
while ($i<$quotation_num) {
	$a1 = mysql_result($quotation_result, $i, "quote_no");
	$a2 = mysql_result($quotation_result, $i, "cust_id");
		 if ($a2==0) {
			    $a2a = 'Bill of quantity';
		 				 }
		 else {			 
	     $table_name3 = "man_customer";
         $customer_query = "SELECT comp_name, status, bag_box, box_no, postal_place, phone, contact_1 FROM $table_name3 WHERE cust_id = '".$a2."' ";
         $customer_result = mySQL_query($customer_query);
		 $customer_num = mysql_num_rows($customer_result);
		 if ($customer_num!=0) {
         $a2a = mysql_result($customer_result, 0, "comp_name");
		 }
		 }
	
	$a5 = mysql_result($quotation_result, $i, "quotation_date");
	//$a6 = mysql_result($quotation_result, $i, "required_date");
	$a7 = mysql_result($quotation_result, $i, "value");	
	$a9 = mysql_result($quotation_result, $i, "company_contact");
	$a13 = mysql_result($quotation_result, $i, "project");
	$a12 = mysql_result($quotation_result, $i, "project_no").' '.mysql_result($quotation_result, $i, "project_place");
	$a10 = mysql_result($quotation_result, $i, "contact_phone");
	$a8 = mysql_result($quotation_result, $i, "seller");
		
$quotations_content = $quotations_content.'<tr><td>'.$a1.'</td><td>'.date("d-m-Y", strtotime($a5)).'</td><td>'.$a13.'<br>'.$a12.'</td><td>'.$a2a.'</td><td>'.$a9.'<br>'.$a10.'</td><td align=right>'.number_format($a7, 2, '.', ',').'</td><td>'.$a8.'</td></tr>';	
    
  $i++;
}


$i=0;
$a11tot=0;
$price=0;
$sales_content = '';
while ($i<$success_num) {
	$a1 = mysql_result($success_result, $i, "job_no");
		
	     $a2 = mysql_result($success_result, $i, "cust_id");
	     $table_name3 = "man_customer";
         $customer_query = "SELECT comp_name, status, bag_box, box_no, postal_place, phone, contact_1 FROM $table_name3 WHERE cust_id = '".$a2."' ";
		 
         $customer_result = mySQL_query($customer_query);
         $a2a = mysql_result($customer_result, 0, "comp_name");
		
	$a4  = mysql_result($success_result, $i, "job_card_no");
	$date = mysql_result($success_result, $i, "date");
	
	$a8 = mysql_result($success_result, $i, "seller");
	$a9 = mysql_result($success_result, $i, "company_contact");
	$a10 = mysql_result($success_result, $i, "contact_phone");
	$a11 = mysql_result($success_result, $i, "value");
	$a12 = mysql_result($success_result, $i, "project_no").' '.mysql_result($success_result, $i, "project_place");
		
$sales_content = $sales_content.'<tr><td>'.$a4.'</td><td>'.$a1.'</td><td>'.date("d-m-Y", strtotime($date)).'</td><td>'.$a13.'<br>'.$a12.'</td><td>'.$a2a.'</td><td>'.$a9.'<br>'.$a10.'</td><td align=right>'.number_format($a11, 2, '.', ',').'</td><td>'.$a8.'</td></tr>';	
  

  $i++;
}



//
$report_text = 'Daily sales report. (Compiled on '.date("d-m-Y", strtotime($comp_date)).')<br><br>Quotations for '.date("l, d-m-Y", strtotime($select_date1)).'<br>
<table border=1 width=650 cellpadding=1 style=border-collapse:collapse; font-size:10px; >
<tr valign="top">
<td width=75 valign="top">Quotation no</td>
<td width=80>Date</td>
<td width=200 valign="top">Project</td>
<td width=150 valign="top">Customer</td>
<td width=100 valign="top">Contact</td>
<td width=75 valign="top" >Value</td>
<td width=75 valign="top">Sales rep</td>
</tr>
'.$quotations_content.'
</table>
<br><br>
Sales for '
.date("l, d-m-Y", strtotime($select_date1)).'
<br>
<table border=1 width=650 cellpadding=1 style=border-collapse:collapse; font-size:10px;>
<tr>
<td width=50>Job<br>card</td>
<td width=50>Quote<br> no</td>
<td width=80>Date</td>
<td width=200>Project</td>
<td width=150>Customer</td>
<td width=100>Contact</td>
<td width=75>Value</td>
<td width=75>Sales<br>rep</td>
</tr>
'.$sales_content.'
</table>'; 

$table_name = "man_reports";
	$report_query = "SELECT user_name, send_report, send_email FROM $table_name WHERE report_name = 'daily sales' ";
	$report_result = mySQL_query($report_query);
	$report_num = mysql_num_rows($report_result);
		
	$i=0;
	$email_address = '';
	while ($i<$report_num) {
		$user_name = mysql_result($report_result, $i, "user_name");
		$send_alert = mysql_result($report_result, $i, "send_report");
		$send_email = mysql_result($report_result, $i, "send_email");
		
		$table_name = "man_saved_sales_reports";
		$result = mysql_query( "INSERT INTO $table_name (department, saved_report_name, saved_report_date, saved_report_text, user_name ) VALUES ('Sales', 'Daily sales report', '".$entry_date."', '".			$report_text."', '".$user_name."' ") ;
			
		
		if ($send_email=='yes') {
			$table_name = "man_users";
			$address_query = "SELECT email FROM $table_name WHERE username = '".$user_name."' ";
			$address_result = mySQL_query($address_query);
			$email_address = mysql_result($address_result, 0, "email").','.$email_address;
		}
		$i++;
	}


if ($email_address!='') {	
$subject = 'Daily sales';
			$message = '
<body style=font-face=Calibri; font-size=10px;><font size=14px;><b>Daily sales report</b></font><br><br>
'.$report_text.'
<br><br>
For more info, login at <a href=man.bwcustomized.com/login.php?e= >Manase Management Program</a>.<br><br><font color=red><b>This message comes from a non-responsive address, please do not reply</b></font><body>';


$to = $email_address;
$subject = $subject;
$message = $message;
$from = 'manase-management@bwcustomized.com';
$headers = "From:" . $from. "\r\n";
$headers  .= 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
mail($to,$subject,$message,$headers);
}

			
?>
done