<?php
#########################################################################################################
#  expiry-report-username-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.
#
#  This will list the users who can view/edit each domain as a column. You have to use the column name
#  auth_user_name as one of the columns in the $columns variable below.
#
#  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/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 = "Domain Expiry Report";

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

							# customer_name is a custom column, auth_user_name is a special column
							# that will list users who can view/edit each domain.
$columns      = "domain,auth_user_name,registry_expiry,registrar_expiry,lookedup,customer_name,status";

$showdays     = true;   	# Show days left for expiry (in addition to actual dates)
$expirydays   = 90;
$sorton       = "registry_expiry";	# Default column for sorting.
$userclause	  = "WHERE readwrite=1";

$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 formatting.
#########################################################################################################

if(array_search('auth_user_name', explode(",", $columns)) !== false)
	define("USE_AUTH_DB", 1);

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

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;

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

$required = array("registrar_expiry", "registry_expiry", "domain");
$query = "registry_expiry <= [TODAY+$expirydays] OR registrar_expiry <= [TODAY+$expirydays] ORDER BY $sorton ASC";

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

$dinfo = $pdo->getDomainDataFromQuery($columns, $query, $required);
if($dinfo !== false)
{
	$ucpos = array_search('auth_user_name', explode(",", $columns));
	if($ucpos !== false)
	{
		$userlist = $pdo->getTableData($pdo->getUserTableName(), "id,name", $userclause);
		foreach($userlist as &$user)
		{
			$uid = $user['id'];
			$user['cid'] = $pdo->getTableData($pdo->getUserCategoryAccessTableName(), "cat_id", "WHERE user_id = ?", array($uid));
		}
		
		foreach($dinfo['data'] as &$row)
		{
			$domain = "";
			foreach($row as $key=>$value)
			{
				if($key == "domain")
				{
					$domain = $value;
					break;
				}
			}
			if($domain != "")
			{
				$insert = array('auth_user_name'=>'xxx');
				$users = getAuthenticatedUsersForDomain($pdo, $domain, $userlist);
				$row['auth_user_name'] = $users;
			}
		}
		array_splice($dinfo['columns'], $ucpos, 0, 'Auth Users');
		array_splice($dinfo['fields'], $ucpos, 0, 'auth_user_name');
	}
	
	if($showdays)
	{
		$dinfo['data'] = $pdo->insertDaysToExpireIntoArray($dinfo['data']);
		$dinfo['data'] = customFormatDatesInData($dinfo['data']);
		$dinfo['columns'][] = "Days";
		$dinfo['fields'][] = "expiry_days";
		$sorton = "expiry_days";
		if(isset($dinfo['data'][0]['domain_age']))
		{
			$dinfo['columns'][] = "Domain Age";
			$dinfo['fields'][] = "domain_age";
		}
	}
	else
		$dinfo['data'] = customFormatDatesInData($dinfo['data']);
	
	$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 customFormatDatesInData($tdata)
{
	if($tdata !== false)
	{
		foreach($tdata as &$row)
		{
			$dateformat = get_config_data('cell_date_format');
			foreach($row as $key=>&$value)
			{
				if(($key == 'registry_expiry' || $key == 'registrar_expiry' || $key == 'ssl_valid_to' || $key == 'created_on') && isADate($value))
				{
					$dtime = strtotime($value);
					$value = date($dateformat, $dtime);
				}
			}
		}
	}
	
	return $tdata;
}

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

function getAuthenticatedUsersForDomain($pdo, $domain, $userlist)
{
	// Find valid categories this domain belongs to
	$sid = $pdo->getDomainID($domain);
	$catids = $pdo->getCategoryIdsForDomain($sid);
	
	// Get the user names the accessible to these categories
	$unames = array();
	if(is_array($userlist) && is_array($catids))
	{
		foreach($userlist as $user)
		{
			$cids = $user['cid'];
			foreach($catids as $cid)
			{
				if(in_array($cid, $cids))
				{
					if(!in_array($user['name'], $unames))
						$unames[] = $user['name'];
				}
			}
		}
	}
	return implode(",", $unames);
}

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

?>
