<?php

#########################################################################################################
#  ereport-sample.php
#
#  Copyright (C) Softnik Technologies. All rights reserved.
#
#  Simple PHP script to run a custom SQL query and email the results. 
#  Use this in your crontab to generate daily/weekly/monthly email 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. 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/ereport.php key=xxxxxx
#  OR
#  wget http://server/path/ereport.php?key=xxxxxx
#
#  You can set your email preferences in 'Basic App Setup > Email options'
#########################################################################################################

# Change these three sql query components to modify as you wish. The default sample will
# find all domains that are set to expire within the next 30 days (registry expiry).
# Please note that it ignores domains that haven't been looked up. You may change the
# $whereSQL to include domains that have an empty (or NULL) registry_expiry too (uncomment the
# extra $whereSQL line).

$selectSQL    = "domain,registry_expiry,registrar_expiry,lookedup,status";
$whereSQL     = "(registry_expiry < [TODAY+30] AND registry_expiry IS NOT NULL and registry_expiry <> '0000-00-00')";
#$whereSQL    .= " OR registry_expiry IS NULL OR registry_expiry = '0000-00-00'";
$sortSQL      = "registry_expiry asc";

# Specify an email address and name to send the report to. You may need to specify the
# mailer settings in 'lib/php/config/app.conf.php'

$toemail      = "example@example.com";
$toname       = "Domain Portfolio Manager";
$emailsubject = "Domain 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.

#########################################################################################################
# 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__) . "/../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();

/////////////////////////////////////////////////////////////////////////////////////////////////////////
# Execute SQL

connect_to_mysql_and_select_db();
global $db_config_domain_table;
$sql  = "SELECT " . $selectSQL . " FROM " . $db_config_domain_table . " a WHERE ";
$sql .= $whereSQL . " ORDER BY " . $sortSQL;
# expand any [TODAY...] entries in the SQL.
$sql = parse_custom_sql_query($sql);
$result = mysql_query($sql) or die("Couldn't execute your query.".mysql_error());

/////////////////////////////////////////////////////////////////////////////////////////////////////////
# Construct the data table. 
# We will be emailing this, we can't use any awesome CSS. Simple table works with all email clients.
# You will need to modify this if you have changed the $selectSQL above.

$message  = "<head>\n<style>table.domaintable td {border-bottom: 1px dotted #AAA;}\n";
$message .= "table.domaintable th {border-bottom: 1px solid #AAA; background-color:#EEE; text-align:left;}\n";
$message .= ".infomsg {text-align: center; margin-top: 20px; margin-bottom:20px; background-color: #FFE0E0; padding:4px;}</style></head>\n";

$message .= '<table class="domaintable" border="0" cellspacing="0" cellpadding="4"  width="100%">' . "\n";

# The heading row
$message .= "<tr>";
$message .= "<th><p><b>Domain</b></p></th>\n";
$message .= "<th><p><b>Registry Expiry</b></p></th>\n";
$message .= "<th><p><b>Registrar Expiry</b></p></th>\n";
$message .= "<th><p><b>Last Lookup At</b></p></th>\n";
$message .= "<th><p><b>Status</b></p></th>\n";
$message .= "</tr>";

# The actual data
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{
	$domain   = $row['domain'];
	$edate01  = $row['registry_expiry'];
	$edate02  = $row['registrar_expiry'];
	$lookedup = $row['lookedup'];
	$status   = str_replace(",", ", ", $row['status']);
	$message .= "<tr>";
	$message .= "<td><p>" . $domain . "</p></td>\n";
	$message .= "<td><p>" . $edate01 . "</p></td>\n";
	$message .= "<td><p>" . $edate02 . "</p></td>\n";
	$message .= "<td><p>" . $lookedup . "</p></td>\n";
	$message .= "<td><p>" . $status . "</p></td>\n";
	$message .= "</tr>";
}
$message .= "</table>\n";

/////////////////////////////////////////////////////////////////////////////////////////////////////////
# Send email and dump to screen.

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>";
	
/////////////////////////////////////////////////////////////////////////////////////////////////////////

?>
