<?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.

							# Data from the sub-domain table. Please don't include any columns that
							# would come from the main domain table here.
$columns      = "sid,subdomain,ip,ssl_valid_from,ssl_valid_to,ssl_issued_to,ssl_issued_by";

							# Specify any extra data. The numbers correspond to the column index in the 
							# final table. These are data from the main domain table and not the 
							# sub-domain table, hence have to be added separately.
							# the column index for host name is ignored because host name column is 
							# always added as one of the first columns.
$extracolumns = array("hostname"=>7, "ssl_checked_at"=>8, "company"=>9);

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

$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)
{
	// Insert the extra columns
	$dinfo['data'] = insertExtraDomainColumns($dinfo['data']);
	foreach($extracolumns as $ek=>$ev)
	{
		array_splice($dinfo['fields'], $ev, 0, $ek);
		if($pdo->getColumnLabelAndWidthForDomain($ek, $label, $width) === false)
			$label = getLabelFromFieldName($ek);
		array_splice($dinfo['columns'], $ev, 0, $label);
	}
	
	// Replace the "sid"column data with actual domain names
	$dinfo['data'] = cleanupSSLRows($dinfo['data']);
	if($showdays)
	{
		$dinfo['data'] = $pdo->insertDaysToExpireIntoArray($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";
	}
	
	if(isset($_REQUEST['sort'])) {
		$newsortcol = strtolower($_REQUEST['sort']);
		$newsortcol = str_replace(" ", "_", $newsortcol);
		if(in_array($newsortcol, $dinfo['fields']))
			$sorton = $newsortcol;
	}
	if(isset($_REQUEST['desc'])) 
		$sortorder = SORT_DESC;
	
	$domaindex = array_search("sid", $dinfo['fields']);
	$hostindex = array_search("hostname", $dinfo['fields']);
	if($hostindex !== false && $domaindex !== false)
	{
		unset($dinfo['fields'][$hostindex]);
		unset($dinfo['columns'][$hostindex]);
		$dinfo['fields'][$domaindex] = "hostname";
		$dinfo['columns'][$domaindex] = "Host Name";
	}
	$sdindex = array_search("subdomain", $dinfo['fields']);
	if($sdindex !== false)
	{
		unset($dinfo['fields'][$sdindex]);
		unset($dinfo['columns'][$sdindex]);
	}
	
	$tablehtml = $pdo->convertToTable($dinfo, $sorton, "domaintable", "colorCodeExpiryRows", $sortorder);
	
	$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");
			}
		}
	}
	return $sslrows;
}

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

function insertExtraDomainColumns($tdata)
	{
		if($tdata !== false)
		{
			global $pdo, $extracolumns;
			foreach($tdata as &$row)
			{
				$subdomain = "";
				$domain = "";
				$sid = 0;
				foreach($row as $key=>$value)
				{
					if((strtolower($key) == 'subdomain') && $value != "@" && $value != "")
						$subdomain = $value . ".";
					else if((strtolower($key) == 'sid'))
					{
						$domain = $pdo->getSingleEntry($pdo->getDomainTableName(), "sid", $value, "domain");
						$sid = $value;
					}
				}
				foreach($extracolumns as $ek=>$ev)
				{
					// Hostname column doesn't actually exist in the domain table.
					// So we treat that as a special case and construct it.
					if(strtolower($ek) == "hostname")
						$row[$ek] = "{$subdomain}{$domain}";
					else
						$row[$ek] = $pdo->getSingleEntry($pdo->getDomainTableName(), "sid", $sid, $ek);
				}
			}
		}
		return $tdata;
	}

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

?>
