<?php
#########################################################################################################
#  expiry-report-sample.php
#
#  Copyright (C) Softnik Technologies. All rights reserved.
#
#  Use this for generating email alerts.
#  You can use this in your crontab to generate daily/weekly/monthly domain expiry reports.
#  Remember to copy and or rename this file before making changes. This will protect your modifications 
#  from getting over-written. The best place to keep custom scripts is the 'lib/php/custom/' folder.
#
#  Before using this
#  -----------------
#  The script will require that you login if you have password protected the application. However this
#  will be a problem when running this script as a cronjob from a remote server. To fix this we 
#  use the 'CLI Authorization Key'. The 'CLI Authorization Key' is a unique string / password you set  
#  from the 'Application Settings > Authentication' tab.
#  After you have specified the key, use it in your cron entry as follows.
#
#  wget http://server/path/ssl-expiry-report.php?key=xxxxxx
#
#  The CLI key is not required if you are running this as a PHP command line application.
#
#  You can set your email preferences in 'Application Settings > Email' tab.
#########################################################################################################

# Specify email address, columns to show, etc.

$recipients = array(
   'person1@example.com' => 'Person One',
   # Add more if you want to...
);

$emailsubject = "SSL Certificate Expiry Report";

$sendemail    = false;  
$screendump   = true;   
 
$savefolder   = ""; 		# Specify a "writable" folder path if you want to save the report to disk.
$reportprefix = "ssl-expiry";   # File name prefix added to the report.

							# customer_name is a custom column.
$columns      = "sid,subdomain,ip,ssl_valid_from,ssl_valid_to,ssl_checked_at,ssl_issued_to,ssl_issued_by";

$showdays     = true;   	# Show days left for expiry (in addition to actual dates)
$expirydays   = 180;
$sorton       = "ssl_valid_to";	# Default column for sorting.

$htmlheadfile = "reports-htmlhead-sample.htm";

$header       = $emailsubject;

$footer       = "";			# may contain simple HTML

#########################################################################################################
# You probably won't need to edit below this line, unless you want to modify the data display formating.
#########################################################################################################

require_once(dirname(__FILE__) . "/../basic.php");
require_once(dirname(__FILE__) . "/../config.check.php");
require_once(dirname(__FILE__) . "/../version.php");
require_once(dirname(__FILE__) . "/../mailer.inc.php");
require_once(dirname(__FILE__) . "/../pdo/dbinit.php");
require_once(dirname(__FILE__) . "/reports.helper.php");

#########################################################################################################
# Parse command line if running in CLI

if(is_cli())
	parse_str(implode('&', array_slice($argv, 1)), $_REQUEST);

#########################################################################################################

loginIfRequired(true);

#########################################################################################################

$pdo = init_db(false);
if($pdo === false)
{
	echo "<p>Unable to connect to database</p>";
	exit;
}

#########################################################################################################

if(isset($_REQUEST['noemail']))
	$sendemail = false;

#########################################################################################################

$expirydays = getNumericRequestVariable("d", $expirydays);
$required = array("sid,subdomain,ip,ssl_valid_to");
$query = "ssl_valid_to <= [TODAY+$expirydays] ORDER BY $sorton ASC";

#########################################################################################################

$dinfo = $pdo->getSubdomainDataFromQuery($columns, $query, $required);
if($dinfo !== false)
{
	if($showdays)
	{
		$dinfo['data'] = $pdo->insertDaysToExpireIntoArray($dinfo['data']);
		$dinfo['data'] = cleanupSSLRows($dinfo['data']);
		for($i = 0; $i < count($dinfo['columns']); $i++)
		{
			if(strtolower($dinfo['columns'][$i]) == "sid")
			{
				$dinfo['columns'][$i] = "Domain";
				break;
			}
		}
		$dinfo['columns'][] = "Days";
		$dinfo['fields'][] = "expiry_days";
		$sorton = "expiry_days";
	}
	$tablehtml = $pdo->convertToTable($dinfo, $sorton, "domaintable", "colorCodeExpiryRows");
	
	$message  = "<html>\n" . file_get_contents(get_install_folder_path() . "lib/php/custom/" . $htmlheadfile);
	$message .= "<h1 class=\"header\">$header</h1>\n";
	$message .= $tablehtml;
	if($footer == "")
		$footer = getSimpleFooterForReports($emailsubject);
	$message .= $footer . "\n" . "</body>\n</html>\n";
	
	if($screendump)
		echo($message);
	else
		echo "<p>On-screen display is disabled in the script. Open in a text editor and enable if you want to see the report on screen.</p>";
	
	if($sendemail)
	{
		$estatus = send_email($message, $emailsubject, $recipients);
		if($estatus !== true)
			echo "<p>Email was not sent.</p>";
		else
			echo "<p>Email was sent.</p>";
	}
	else
		echo "<p class=\"infomsg\">Emailing is disabled in the script. Open in a text editor and enable the email option.</p>";
	
	#########################################################################################################

	if($savefolder != "")
	{
		if(!saveReportFile($savefolder, $reportprefix, $filename, $message))
			echo "<p>File was not saved.</p>";
		else
			echo "<p>File saved to $filename.</p>";
	}
}

#########################################################################################################

function cleanupSSLRows($sslrows)
{
	global $pdo;
	foreach($sslrows as &$row)
	{
		$sid = "";
		foreach($row as $key=>&$value)
		{
			if($key == "sid")
			{
				$sid = $value;
				$value = $pdo->getSingleEntry($pdo->getDomainTableName(), "sid", $value, "domain");
			}
			if($key == "ssl_checked_at")
				$value = $pdo->getSingleEntry($pdo->getDomainTableName(), "sid", $sid, "ssl_checked_at");
		}
	}
	return $sslrows;
}

#########################################################################################################

?>
