<?php
////////////////////////////////////////////////////////////////////////////////////////
// A set of API related functions that generates emails summarising total data used by an agent
//
// Daily - send email at 6:30AM with data used in last 24 hours for each agent
// Monthly - send email at 6:30AM on the first of the month with data used in last 30 days for each agent
// Hourly - check a data limit (e.g. 200MB) has not been breached.  Send email if it has.
//
// Pre-requisites:
// A network agent monitoring inbound and outbound data
// You must create at least one custom report for the network agent
// Each report can only have two line items (OctetsIn and OctetsOut)
// The period the report covers is not important as we change this below
// The report must be assigned to a group with at least one email contact
// sendmail() must be correctly configured on your server
//
// Name this file "functions_mailreports.php", store it in the "include" directory,
// and add the following lines to "/include/functions_api.php" :
//
// include_once($config['homedir'] . "/include/functions_mailreports.php");
// include_once($config['homedir'] . "/include/functions_reporting.php");
// 
// You will need to set the From email address in "api_get_mailreport" and "api_get_adminmail"
// You will need to set the To address in "api_get_adminmail"
//
// Usage:
// Create crontab entries for a suitable user as follows -
// 00 * * * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=mailreport&id2=hourly"
// 30 06 * * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=mailreport&id2=daily"
// 30 06 1 * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=mailreport&id2=monthly"
// 00 * * * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=adminmail&id2=hourly"
// 30 06 * * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=adminmail&id2=daily"
// 30 06 1 * * curl -o temp.txt "http://127.0.0.1/pandora_console/include/api.php?apipass=<apipass>&user=<user>&pass=<password>&op=get&op2=adminmail&id2=monthly"
//
// These functions are provided as is and without warranty.
// They work for me as of version 6.0 and do only as much as I need. YMMV.
// If you improve upon this code (which won't be difficult) then share the love back to the community
////////////////////////////////////////////////////////////////////////////////////////

/////////////////////////////////////////////
// Run reports that we send to our Clients //
/////////////////////////////////////////////

//get report output
//$thrash1 -> id_report
//$thrash2 -> $id2 -> monthly/daily/hourly)
function mr_get_reportarray($thrash1, $thrash2, $other, $returnType, $user_in_db) {
	if (defined ('METACONSOLE')) {
		return;
	}
	
	if ($other['type'] == 'string') {
		if ($other['data'] != '') {
			returnError('error_parameter', 'Error in the parameters.');
			return;
		}
		else {//Default values
			$separator = ';';
		}
	}
	else if ($other['type'] == 'array') {
		$separator = $other['data'][0];
	}
	
	$id_report = $thrash1;

	//statically set initial date (e.g. 1st of month) and period (e.g. 1 month)
	//1 month = 2592000 seconds
	//1 day = 86400 seconds
	//1 hour = 3600 seconds
	//date format is 2015-11-30
	if ($thrash2 == 'monthly') {
		$period = 2592000;
		$time = "06:30AM";
		$date = date('Y-m-d');
	} elseif ($thrash2 == 'weekly') {
		$period = 604800;
		$time = "06:30AM";
		$date = date('Y-m-d');
	} elseif ($thrash2 == 'hourly') {
		$period = 3600;
		$time = date('H:i');
		$date = date('Y-m-d');
	} else {//daily
		$period = 86400;
		$time = "06:30AM";
		$date = date('Y-m-d');
	}

	//run the report
	$reports = reporting_make_reporting_data(null, $id_report, $date, $time, $period, 'static');
	
	//extract relevant data from the report
	$group_id = $reports["id_group"];
	$reportname = $reports["name"];
	//set the OctetsIn value
	$value1 = (int) $reports["contents"]["0"]["data"]["value"];
	//set the OctetsOut value
	$value2 = (int) $reports["contents"]["1"]["data"]["value"];
	//total the Octets
	$sum_value = $value1 + $value2;
	
	//format the total value into GB or MB
	$formatted_value = format_for_graph($sum_value,2);

	//only return data in an hourly report if data usage exceeds 200MB
	if ($thrash2 == "hourly") {
		if ($sum_value < 209715200) {
			$reportdata = "";
		} else {
			$reportdata = $reportname." has exceeded 200MB in the past hour!\n\nIt has used ".$formatted_value;
		}
	} else {
		$reportdata = $reportname."  total data for this period = ".$formatted_value;
	}
	return $reportdata;
}

//get groups that are children of the group "Clients" (in this case "Clients" has ID of 15)
function mr_get_clientgroups ($thrash1, $thrash2, $other, $returnType) {
	$sql = "SELECT id_grupo FROM tgrupo WHERE parent = 15";
	$clientgroups = db_process_sql($sql);
	return $clientgroups;
}

//get list of reports for this group
function mr_get_reportsofgroup ($thrash1, $thrash2, $other, $returnType) {
	$sql = "SELECT id_report, name FROM treport WHERE id_group = ".$thrash1;
	$reports = db_process_sql($sql);
	return $reports;
}

//get list of email addresses for this group
function mr_get_groupemail ($thrash1, $thrash2, $other, $returnType) {
	$sql = "SELECT id_usuario FROM tusuario_perfil WHERE id_grupo = ".$thrash1;
	$userids = db_process_sql($sql);
	foreach ($userids as $key => $value) {
		$sql2 = "SELECT email FROM tusuario WHERE id_user = '".$value['id_usuario']."'";
		$emails = db_process_sql($sql2);
		foreach($emails as $key => $value) {
			$emailaddresses .= $value['email'].", ";
		}
	}
	return $emailaddresses;
}

//bring it all together and send emails out
function api_get_mailreport ($thrash1, $id2, $other, $returnType) {
	//get client group IDs
	$clientgroups = mr_get_clientgroups();
	
	foreach ($clientgroups AS $key => $value) {
		//get the report IDs for each group
		$reports = mr_get_reportsofgroup($value['id_grupo']);
			//check if there are any reports returned
			$reportcount = count($reports);
			if ($reportcount > 0) {
				//get the email contacts for this group
				$emailaddresses = mr_get_groupemail($value['id_grupo']);
				foreach ($reports AS $key1 => $value1) {
					//get the stats for each report
					$emailbody .= html_entity_decode(mr_get_reportarray($value1['id_report'], $id2))."\n\n";
				}
				//send the email if needed
				//check the output isn't just whitespace
				if (!ctype_space($emailbody)) { 
					//format email addresses ready for mailing
					$emailaddresses = rtrim($emailaddresses, ", \t\n");
				
					$bodystart = "Hi,\n\nHere is your Data Usage Report\n\n";
					$bodyend = "\nIf you have any questions about your report please contact your Account Manager.\n\nWe cannot be held responsible for the accuracy of data contained in this automatically generated report.";
					$bodyfinal = $bodystart.$emailbody.$bodyend;
					$to = $emailaddresses;
					$subject = $id2." Data Usage Report";
					$headers = "From: a_user@ourdomain.com" . "\r\n";
				
					$do_mail = mail($to,$subject,$bodyfinal,$headers);
				} else {
					//no email to send
					return;
				}
			}
			else {
				//No reports to return
				return;
			}
	}
}
/////////////////////////////////////////////////////////////////////
// Send a summary of all agents total data usage to our admin user //
/////////////////////////////////////////////////////////////////////

//get all reports regardless of group
function mr_get_allreports ($thrash1, $thrash2, $other, $returnType) {
	$sql = "SELECT id_report, name FROM treport";
	$reports = db_process_sql($sql);
	//print_r($reports);
	return $reports;
}

//bring it together and send to admin
function api_get_adminmail ($thrash1, $id2, $other, $returnType){
	$reports = mr_get_allreports();
	//check if there are any reports returned
	$reportcount = count($reports);
	if ($reportcount > 0) {
		//email address for admin
		$emailaddresses = "pandora_admin@ourdomain.com";
		foreach ($reports AS $key1 => $value1) {
			//get the stats for each report
			$emailbody .= html_entity_decode(mr_get_reportarray($value1['id_report'], $id2))."\n\n";
		}

		//check the returned output is not just whitespace
		if (!ctype_space($emailbody)) {
			//format email addresses ready to send
			$emailaddresses = rtrim($emailaddresses, ", \t\n");
		
			$bodystart = "Hi,\n\nHere is the Admin Data Usage Report\n\n";
			$bodyend = "\nWe cannot be held responsible for the accuracy of data contained in this automatically generated report.";
			$bodyfinal = $bodystart.$emailbody.$bodyend;
			$to = $emailaddresses;

			$subject = "Admin ".$id2." Data Usage Report";
			$headers = "From: a_user@ourdomain.com" . "\r\n";
		
			$do_mail = mail($to,$subject,$bodyfinal,$headers);
			
		} else {
			//no email to send
			return;
		}
	}
	else {
		//No reports to return
		return;
	}
}
?>
