<?php
#########################################################################################################
#  expiry-report-sample.php
#
#  Copyright (C) Softnik Technologies. All rights reserved.
#
#  Simple PHP script to list SSL expiry dates and email the results. 
#  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 cron job. To overcome this we use the
#  'CLI Authorization Key'. The 'CLI Authorization Key' is a unique string / password you set from the 
#  'Basic App Setup > Authentication' screen.
#  After you have specified the key, use it in your cron entry as follows.
#
#  php /path/to/script/ssl-expiry-report.php key=xxxxxx
#  OR
#  wget http://server/path/ssl-expiry-report.php?key=xxxxxx
#
#  You can set your email preferences in 'Basic App Setup > Email options'
#########################################################################################################

# Specify email address, columns to show in table, etc.

$toemail      = "example@example.com";
$toname       = "Domain Portfolio Manager";
$emailsubject = "Domain SSL Expiry Report";

$sendemail    = false;  # Specify proper email settings and change to true.
$screendump   = true;  # true to see on screen. Password protect this folder if you set this to true.

# specify a "writable" folder path. Empty if you don't want to save the report to disk.
$savefolder   = ""; 
$reportprefix = "expiry";  # file name prefix added to the report.

# customer_name is a custom column. You can replace it with your own custom column name.
$columns      = "sid,subdomain,ip,ssl_valid_from,ssl_valid_to,ssl_checked_at,ssl_issued_to,ssl_issued_by";

$showdays     = false;   # false if you want the actual date to be shown (instead of days left/past)
$expirydays   = 90;

$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__) . "/../../../config.php");
require_once(dirname(__FILE__) . "/../base.php");
require_once(dirname(__FILE__) . "/../version.php");
require_once(dirname(__FILE__) . "/../mailer.inc.php");

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

# Parse command line if running in CLI
if(is_cli())
	parse_str(implode('&', array_slice($argv, 1)), $_REQUEST);

session_start();
cli_mode_authentication_check();

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

$expirydays = get_numeric_request_variable("d", 90);

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

function get_base_dir()
{
	return realpath(dirname(__FILE__)) . DIRECTORY_SEPARATOR;
}

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

connect_to_mysql_and_select_db();
global $db_config_domain_table, $db_allowed_domain_columns, $domain_table_date_fields, $db_config_subdomain_table, $db_allowed_subdomain_columns;

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

$colarray = explode(",", strtolower($columns));
$required_columns = array("ssl_valid_to");
foreach($required_columns as $entry)
{
	if(!in_array($entry, $colarray))
		array_unshift($colarray, $entry);
}
SetupAllowableFields();

#########################################################################################################
# Do SQL Query

$sql = "SELECT ";
$fields = array();
$labels = array();
foreach($colarray as $c)
{
	if(in_array($c, $db_allowed_subdomain_columns) || $c == "sid" || $c == 'ssl_checked_at')
	{
		$sql .= " $c,";
		$fields[] = $c;
		$label = getLabelFromFieldName($c);
		get_column_label_and_width($c, $label, $width);
		$labels[] = $label;
	}
}
$sql = trim($sql, ",");
$sql .= " FROM $db_config_subdomain_table WHERE ssl_valid_to <= [TODAY+$expirydays] ORDER BY ssl_valid_to asc";
$sql  = parse_custom_sql_query($sql);
$result = mysql_query($sql) or die("Couldn't execute your query.".mysql_error());

#########################################################################################################
# Construct the data table. 

$message  = file_get_contents(get_base_dir() . $htmlheadfile);
$message .= "<h1 class=\"header\">$header</h1>\n";
$message .= '<table class="domaintable" border="0" cellspacing="0" cellpadding="4"  width="100%">' . "\n";

#########################################################################################################
# The heading row

$message .= "<tr>\n";
foreach($labels as $l)
{
	if(strtolower($l) == "sid")
		$message .= "<th class=\"domain\"><p><b>Domain</b></p></th>\n";
	else
		$message .= "<th><p><b>$l</b></p></th>\n";
}
$message .= "</tr>\n";

#########################################################################################################
# The actual data

$tabsep = array('7'=>0, '30'=>0, '60'=>0, '90'=>0);
$num_rows = mysql_num_rows($result);
$rindex = 0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{	
	$sid = 0;
	$trclass = "";
	$tdentry = "";
	$expdays = 99999;
	foreach($fields as $f)
	{
		$is_an_expiry_date = false;
		if(isset($row[$f]))
		{
			if(in_array($f, $subdomain_table_datetime_fields))
			{
				if(strtolower($f) == "ssl_valid_to")
					$is_an_expiry_date = true;
					
				$days = get_date_difference(strtotime($row[$f]));					
				if($is_an_expiry_date)
					$expdays = min($expdays, $days);
				
				$v = $row[$f];
				if($showdays)
				{
					if($days >= 0)
						$v = "in $days days";
					else if($days < 0)
					{
						$d = $days*-1;
						$v = "$d days ago";
					}
				}
			}
			else
			{
				$v = $row[$f];
				if(strtolower($f) == "status")
					$v = str_replace(",", ", ", $v);
				else if(strtolower($f) == "sid")
				{
					$v = get_domain_name_from_sid($row[$f]);
					$sid = $row[$f];
				}
			}
		}
		else if(strtolower($f) == "ssl_checked_at" && $sid > 0)
		{
			$sql = "select sid," . $f . " from " . $db_config_domain_table . " where sid = $sid";
			$sresult = mysql_query( $sql ) or die("Couldn't execute your query.".mysql_error());
			$srow = mysql_fetch_array($sresult, MYSQL_ASSOC);
			if($row['sid'] > 0)
				$v = $srow['ssl_checked_at'];
		}
		else
			$v = "-";
		$tdentry .= "<td><p>$v</p></td>\n";
	}
	if($expdays <= 0)
		$trclass .= " bgalert";
	foreach($tabsep as $k=>$v)
	{
		if($expdays >= intval($k) && $tabsep[$k] == 0)
		{
			$trclass .= " groupstart";
			$tabsep[$k] = 1;
		}
	}
	
	if($rindex == ($num_rows-1))
		$trclass .= " lastrow";
	$rindex++;
	
	$trclass = trim($trclass);
	$message .= "<tr";
	if($trclass != "")
		$message .= " class=\"$trclass\">\n";
	else
		$message .= ">\n";
	$message .= $tdentry;
	$message .= "</tr>\n";
}

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

$message .= "</table>\n";

if($footer == "")
	$footer = "<br><br><p class=\"footer\"><a href=\"" . get_base_path() . "\" target=\"_blank\">$emailsubject</a> " . get_version() . ", Build: " . get_build_date() . "</p>";
$message .= $footer . "\n";

#########################################################################################################
# Send email, dump to screen or save to folder.

if($screendump)
	echo $message;
else
	echo "<p class=\"infomsg\">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, $toemail, $toname);
	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(strstr($savefolder, DIRECTORY_SEPARATOR) === false)
	{
		$basefolder = get_base_dir();
		$basefolder .= $savefolder;
		$savefolder = $basefolder;
	}
	if(substr($savefolder, -1) != DIRECTORY_SEPARATOR)
		$savefolder .= DIRECTORY_SEPARATOR;
	$savepath = $savefolder . "$reportprefix-" . date("Y-m-d") . ".htm";  
	$findex = 1;
	while(is_file($savepath) && $findex < 20)
	{
		$savepath = $savefolder . "$reportprefix-" . date("Y-m-d") . "-$findex" . ".htm";  
		$findex++;
	}
	if(file_put_contents($savepath, $message) === false)
		echo "<p>File was not saved.</p>";
}
	
#########################################################################################################

?>
