<?php
$today = date("Y-m-d");
$day = date("l", strtotime($today));

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

$report_day = strtotime(date("Y-m-d", strtotime($today)) . " -1 day"); $report_day = date("Y-m-d", $report_day); 
if ( $day=='Sunday') { $report_day = strtotime(date("Y-m-d", strtotime($today)) . " -2 day"); $report_day = date("Y-m-d", $report_day); }
if ( $day=='Monday') { $report_day = strtotime(date("Y-m-d", strtotime($today)) . " -3 day");  $report_day = date("Y-m-d", $report_day);}

$_SESSION['db_prefix']="tlh";


$table_name = $_SESSION['db_prefix']."_alerts";
	$alert_query = "SELECT user_name, send_alert, send_email FROM $table_name WHERE alert_name = 'sales report' ";
	$alert_result = mySQL_query($alert_query);
	$alert_num = mysql_num_rows($alert_result);
	
	if ($alert_num>0) {		
	$table_name = $_SESSION['db_prefix']."_captured_jobs";
	$new_job_query = "SELECT date, job_no, cust_id, project_no, project_place, designer, value  FROM $table_name WHERE date = '".$report_day."' ";
	
	$new_job_result = mySQL_query($new_job_query);
	$new_job_num = mysql_num_rows($new_job_result);
	$new_job_text='';
	
	$k=0;
	while ($k<$new_job_num) {
	$job_no = mysql_result($new_job_result, $k, "job_no");
	$cust_id = mysql_result($new_job_result, $k, "cust_id");
		$table_name2 = $_SESSION['db_prefix']."_customer";
		$customer_query = "SELECT * FROM $table_name2 WHERE cust_id ='".$cust_id."' ";
		$customer_result = mySQL_query($customer_query);
		$customer_num = mysql_num_rows($customer_result);	
		$a1 = mysql_result($customer_result, 0, "account_type");
			if ($a1=="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");
 			}
	$project_no = mysql_result($new_job_result, $k, "project_no");
	$project_place = mysql_result($new_job_result, $k, "project_place");
	$designer = mysql_result($new_job_result, $k, "designer");
	$value = mysql_result($new_job_result, $k, "value");
	$new_job_text = $new_job_text.'<tr><td>J'.$job_no.'</td><td>'.$cust_name.'</td><td>'.$project_no.' '.$project_place.'</td><td align=right>'.number_format($value, 2, '.', ',').'</td></tr>';
	$k++;
	}
	
	$new_job_header = '<font face=Calibri size=4><b>New jobs registered on: '.date("l, d-m-Y", strtotime($report_day)).'.</b><br><br><table border=1 cellpadding=2 style=border-collapse:collapse><tr><td width=50>Job no<?td><td width=100>Customer</td><td width=150>Project</td><td width=75>Value</td></tr>';
	$new_job_footer = '</table></font><br><br><font face=Calibri size=3><br>This report was generated on '.date("d-m-Y, H:i", strtotime($entry_date )).'.<br></font>';
	if ($new_job_text=='') {
		$new_job_text='<tr><td>No new jobs recorded.</td></tr>';
	}
	
	$new_job_report = $new_job_header.$new_job_text.$new_job_footer;
	
	$table_name = $_SESSION['db_prefix']."_reports_text";
	$text_query = "INSERT INTO $table_name (report, report_text ) VALUES ( '10', '".$new_job_report."' ) ";
	execsql($text_query);
	
	$table_name = $_SESSION['db_prefix']."_reports_text";
	$id_query = "SELECT report_text_id FROM $table_name  ORDER BY report_text_id DESC LIMIT 1 ";
	$id_result = mySQL_query($id_query);
	$report_text_id = mysql_result($id_result, 0, "report_text_id");
	
	$email_address = '';
	$i=0;
	while ($i<$alert_num) {
		$user_name = mysql_result($alert_result, $i, "user_name");
		$send_alert = mysql_result($alert_result, $i, "send_alert");
		$send_email = mysql_result($alert_result, $i, "send_email");
		
		
		if ($send_alert=='yes') {
			$table_name = $_SESSION['db_prefix']."_reports";
			$report_query = "INSERT INTO $table_name (user_name, generated, report, text_id) VALUES ('".$user_name."', '".$entry_date."', '10', '".$report_text_id."' ) ";
			execsql($report_query);
		}
		
		if ($send_email=='yes') {
			$table_name = $_SESSION['db_prefix']."_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 = 'Report from Tlhomeso Management';
		$message = '<body><br>'.$new_job_report.'<br><font face=Calibri size=3>For more info, login at <a href=costing.tocotex.com/mainmenu.php>Tlhomeso Management Program</a>.<br><br><font face=Calibri size=3 color=red><b>This message comes from a non-responsive address, please do not reply</b></font><body>';
		//include ('alert_to_email.php');
		
	}
	}
	
	$table_name = $_SESSION['db_prefix']."_login_check";
	$login_query = "INSERT INTO $table_name (log_in_date, user) VALUES ('".$today."', '".$_SESSION['username']."'  ) ";
	execsql($login_query);
?>
