<?php
###############################################################################
# ReportManager.php
#
# @author Anil Kumar <akumar@codepunch.com>
# @link   https://codepunch.com
#
############################################################################### 

namespace 	CodePunch\UI;
use 		CodePunch\Base\Util as UTIL;
use			CodePunch\Base\Text as TEXT;
use			CodePunch\DB\Audit as AUDIT;

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

class ReportManager {
	
	private $authentication 	= null;
	
	const REPORT_FORMAT_NONE	= 0;
	const REPORT_FORMAT_HTML	= 1;
	const REPORT_FORMAT_TEXT	= 2;
	const REPORT_FORMAT_CSV		= 3;
	const REPORT_FORMAT_PDF		= 4;
	
	const SEND_EMAIL_YES		= 1;
	const SEND_EMAIL_NO			= 2;
	const SEND_EMAIL_AUTO		= 3;
	
	###########################################################################
	
	public function __construct($auth=null) {
		$this->authentication = $auth;
	}
	
	###########################################################################
	
	public function getAuthentication() {
		return $this->authentication;
	}
	
	###############################################################################
	
	private function getReportRowClass($thedate)
	{
		$class = null;
		$cutoffs = array(0, 15, 30, 45, 60, 90);
		if(UTIL::is_a_date($thedate)) {
			$timediff = UTIL::get_date_difference(strtotime($thedate));
			for($idx = 1; $idx < count($cutoffs); $idx++) {
				$co01 = $cutoffs[$idx-1];
				$co02 = $cutoffs[$idx];
				if($timediff < $co01) {
					$class = "level$co01";
					break;
				}
				else if($timediff > $co01 && $timediff <= $co02) {
					$class = "level$co02";
					break;
				}
			}
		}
		return $class;
	}
	
	###############################################################################

	public function setDaysToExpiryAndStatus($data)
	{
		$db = $this->getAuthentication()->getDatabase();
		if(count($data)) {
			$columns = array_keys($data[0]);
			$expandStatus = in_array("status", $columns) ? true : false;
			$expandDays = in_array("days_to_expiry", $columns) ? true : false;
			if($expandStatus || $expandDays) {
				foreach($data as &$d) {
					if($expandDays)
						$d['days_to_expiry'] = $db->findDaysToExpiry($d);
					if($expandStatus)
						$d['status'] = str_replace(",", ", ", $d['status']);
				}
			}
		}
		return $data;
	}

	###############################################################################
	
	public function createReport($name, $data, $columns, $sorton, $sortord, $opmode)
	{
		$db = $this->getAuthentication()->getDatabase();
		$columns = explode(",", $columns);
		$tablerows = array();
		$trow = "<tr>";
		$trow .= "<th>#</th>";
		foreach($columns as $column) {
			$ci = $db->getReportColumnInfo($column);
			$clsname = str_ireplace("d.", "d_", str_ireplace("s.", "s_", $column));
			$trow .= "<th class=\"$clsname\">";
			if($ci !== false) 
				$trow .= $ci['label'];
			else
				$trow .= $column;
			$trow .= "</th>";
		}
		$trow .= "</tr>\n";
		$headerrow = $trow;

		$expiryadjust = false;
		if(($sorton == "d.registry_expiry" || $sorton == "d.registrar_expiry") && strtolower($sortord) == "asc");
			$expiryadjust = true;
		$levelzerodone = false;
		$index = 1;
		foreach($data as $row) {
			$class = null;
			if(isset($row['registry_expiry']) || isset($row['registrar_expiry']) || isset($row['s.ssl_valid_to']) || isset($row['valid_to'])) {
				if(isset($row['s.ssl_valid_to']) && $sorton == 's.ssl_valid_to' && $class == null) 
					$class = $this->getReportRowClass($row['s.ssl_valid_to']);
				if(isset($row['registry_expiry']) && isset($row['registrar_expiry'])) {
					if(UTIL::is_a_date($row['registry_expiry']) && UTIL::is_a_date($row['registrar_expiry'])) {
						if($row['registry_expiry'] > $row['registrar_expiry'] && $class == null)
							$class = $this->getReportRowClass($row['registrar_expiry']);
					}
				}
				if(isset($row['registry_expiry']) && $class == null) 
					$class = $this->getReportRowClass($row['registry_expiry']);
				if(isset($row['registrar_expiry']) && $class == null) 
					$class = $this->getReportRowClass($row['registrar_expiry']);
				if(isset($row['s.ssl_valid_to']) && $class == null) 
					$class = $this->getReportRowClass($row['s.ssl_valid_to']);
				if(isset($row['valid_to']) && $class == null) 
					$class = $this->getReportRowClass($row['valid_to']);
			}
			if($class != "level0" && $class != null && $levelzerodone === false)
				$levelzerodone = true;
			
			$trow = "";
			$has_days_column = in_array("d.days_to_expiry", $columns) | in_array("days_to_expiry", $columns);
			foreach($columns as $column) {
				$clsname = str_ireplace("d.", "d_", str_ireplace("s.", "s_", $column));
				if($class == null) $cclass = $clsname;
				else $cclass = "$class $clsname";
				$trow .= "<td class=\"$cclass\">";
				$cs = substr($column, 0, 2);
				if($cs == "d.")
					$column = substr($column, 2);
				if($column == "registry_expiry" || $column == "registrar_expiry" || $column == "s.ssl_valid_to") {
					if(UTIL::is_a_date($row[$column]) && !$has_days_column) {
						$timediff = UTIL::get_date_difference(strtotime($row[$column]));
						if($timediff <= 60) {
							$tf = sprintf("%02d", $timediff);
							if($timediff > 0)
								$tf = "+$tf";
							$trow .= " [ $tf ] ";
						}
					}
				}
				if(isset($row[$column]))
					$trow .= $row[$column];
				else if(isset($row["s.".$column]))
					$trow .= $row["s.".$column];
				$trow .= "</td>";
			}
			
			// Already expired domains will have registry expiry one year ahead.
			// Move such rows to top.
			if($class == "level0" && $class != null && $levelzerodone && $expiryadjust)
				array_unshift($tablerows, $trow);
			else
				$tablerows[] = $trow;
			$index++;
		}
		
		$index = 1;
		$rowend = "</tr>\n";
		foreach($tablerows as &$trow) {
			$class = null;
			$rowstart  = "<tr>" . ($class == null ? "<td>" : "<td class=\"$class\">") . $index . "</td>";
			$trow = $rowstart . $trow . $rowend;
			$index++;
		}
		
		array_unshift($tablerows, $headerrow);

		$setup = new \CodePunch\Config\Settings($this->getAuthentication());
		$cssdata = $setup->getText("reports_css", "");
		if($cssdata == "") {
			$folder = UTIL::get_install_folder_path();
			$datafile = $folder . "lib/layouts/css/reports/default.css";
			$cssdata = file_get_contents($datafile);
			$setup->setText("reports_css", $cssdata);
		}
		$layout = new \CodePunch\UI\Layout($this->authentication);
		$cssdata = $layout->getCSSData($cssdata);
		$head = "<html>\n<head>\n<style>$cssdata</style>\n</head>\n<body>\n";
		$heading = "<h1>$name</h1>\n";
		$table = "<table class=\"report\">\n" . implode("\n", $tablerows) . "</table>\n";
		$tail = "</body>\n</html>";
		return $head . $heading . $table . $tail;
	}
	
	###############################################################################
	
	public function formatForEmail($thereport)
	{
		$replace = array(
			'0' => '#FFB1B1',
			'15' => '#FECDCD',
			'30' => '#FEE494',
			'45' => '#FEEBB1',
			'60' => '#FEF2CD',
			'90' => '#FFF9E9'
		);
		foreach($replace as $k=>$v) {
			$lf = "<td class=\"level$k ";
			$rp = "<td bgcolor=\"$v\" class=\"";
			$thereport = str_ireplace($lf, $rp, $thereport);
		}
		return $thereport;
	}

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

	public function saveReportDataToFile($reportdata, $outfile, $delimiter=",")
	{
		$buffer = false;
		if($outfile == "") {
			$outfile = "php://temp";
			$buffer = true;
		}
		$output = fopen($outfile,'r+');
		$csvdata = "";
		$labels = array();
		$row = 0;
		if($output && $reportdata !== false && is_array($reportdata)) {
			foreach($reportdata as $d) {
				$values = array();
				foreach($d as $label=>$value) {
					if($row == 0) 
						$labels[] = $label;
					$values[] = $value;
				}		
				if(!$row)
					fputcsv($output, $labels, $delimiter);
				fputcsv($output, $values, $delimiter);
				$row++;
			}
			if($buffer === true) {
				rewind($output);
				$csvdata = stream_get_contents($output);
			}
			fclose($output);
		}
		return $buffer ? $csvdata : $row;
	}
	
	###############################################################################
	
	public function createSSLBrowserReport() {
		$db = $this->getAuthentication()->getDatabase();
		$setup = new \CodePunch\Config\Settings($this->authentication);
		$columns = $setup->getOption("sslcert_columns", "issued_to;issued_by;valid_from;valid_to;added_on;serial;subject_alt_name;thumbprint;base_domain;notes_a;notes_b;notes_c;notes_d");
		$columns = explode(";", $columns);
		$columns = array_diff($columns, ["subject_alt_name", "base_domain", "thumbprint"]);
		$columns = implode(",", $columns);
		$days = 45;
		$compdate = date("Y-m-d H:i:s", time() + ($days*24*3600));
		$rows = $db->getFromTable("*", $db->getSSLCertificateTableName(), "valid_to < ?", array($compdate), "valid_to", "asc");
		foreach($rows as &$row)
			$row = array_change_key_case($row, CASE_LOWER);
		$thereport = $this->createReport("SSL Vault Report", $rows, $columns, "valid_to", "asc", true);
		return $thereport;
	}
	
	###############################################################################
	
	public function getSSLCertReport(&$responce, $doreport=false, $doemail=self::SEND_EMAIL_AUTO) {
		$reportname = "SSL Vault Report";
		$rinfo = array('next_email_after'=>'','emailed'=>0, 'email_ready_check'=>'no', 'last_emailed_at'=>'');
		$db = $this->getAuthentication()->getDatabase();
		$setup = new \CodePunch\Config\Settings($this->getAuthentication());
		$sslinfo = $setup->getSSLCertSettings();
		$lufreq = $sslinfo['frequency'];
		$runits  = $sslinfo['runits'];
		$query  = "";
		$params = "";
		$lastrun = $sslinfo['lastrun'];
		if($lastrun == null || $lastrun == "")
			$lastrun = "2018-2-1 0:0:0";
		$rinfo['last_emailed_at'] = $lastrun;
		$opmode = $sslinfo['autorun'];
		$refresh = \CodePunch\LU\LookupManager::refreshIntervalInMinutes($lufreq, ($runits&0xF));
		$compdate = date("Y-m-d H:i:s", time() - ($refresh*60));
		$rinfo['next_email_after'] = $compdate;
		$save_last_autorun_at = false;
		if($doemail == self::SEND_EMAIL_AUTO) {
			$save_last_autorun_at = true;
			$doemail = self::SEND_EMAIL_NO;
			// Check when this report was last emailed.
			if(strtotime($lastrun) < strtotime($compdate) && $lufreq) {
				$rinfo['email_ready_check'] = 'yes';
				// Check for "run at" conditions
				$unitype = $runits&0xF;
				$hour = ($runits >> 4) & 255;
				$date = ($runits >> 12) & 255;
				$week = ($runits >> 20) & 255;
				$ch = intval(date("H"));
				$cd = intval(date("d"));
				$cw = intval(date("w"));
				if($unitype == \CodePunch\LU\LookupManager::LU_DAY) {
					// Only hour is important
					if($ch >= $hour)
						$doemail = self::SEND_EMAIL_YES;
				}
				else if($unitype == \CodePunch\LU\LookupManager::LU_WEEK) {
					// Only hour and week is important
					if($ch == $hour && $cw == $week)
						$doemail = self::SEND_EMAIL_YES;
				}
				else if($unitype == \CodePunch\LU\LookupManager::LU_MONTH) {
					// Only hour and date is important
					if($ch == $hour && $cd == $date)
						$doemail = self::SEND_EMAIL_YES;
				}// Check for "run at" conditions
				$unitype = $runits&0xF;
				$hour = ($runits >> 4) & 255;
				$date = ($runits >> 12) & 255;
				$week = ($runits >> 20) & 255;
				$ch = intval(date("H"));
				$cd = intval(date("d"));
				$cw = intval(date("w"));
				if($unitype == \CodePunch\LU\LookupManager::LU_DAY) {
					// Only hour is important
					if($ch >= $hour || ($hour == 24 && $ch == 0))
						$doemail = self::SEND_EMAIL_YES;
				}
				else if($unitype == \CodePunch\LU\LookupManager::LU_WEEK) {
					// Only hour and week is important
					if($ch == $hour && $cw == $week)
						$doemail = self::SEND_EMAIL_YES;
				}
				else if($unitype == \CodePunch\LU\LookupManager::LU_MONTH) {
					// Only hour and date is important
					if($ch == $hour && $cd == $date)
						$doemail = self::SEND_EMAIL_YES;
				}
			}
		}
		if($doemail == self::SEND_EMAIL_YES) {
			$doreport = true;
		}
		
		if(!isset($responce['attempted']))
			$responce['attempted'] = 0;
		if(!isset($responce['report']))
			$responce['report'] = "";
		if(!isset($responce['emailed']))
			$responce['emailed'] = 0;
		$responce['attempted']++;
		$thereport = $this->createSSLBrowserReport();
			
		if($doreport) {
			$recipients = $sslinfo['emails'];
			if($recipients == "") 
				$recipients = $setup->getOption("email_recipients", "");
			if($recipients == "") {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error("No email recipients found for " . $reportname . " Mailing");
			}
			else {
				$mailto = "Recipients: " . implode(", ", explode("\n", $recipients));
				try{
					$mailer = new \CodePunch\Base\Mailer($this->getAuthentication());
					$recipients = $mailer->formatEmailRecipients($recipients);
					$status = $mailer->send($recipients, $reportname, $this->formatForEmail($thereport));
					if($status) {
						$lastrun = date("Y-m-d H:i:s");
						if($save_last_autorun_at)
							$setup->setOption("sslbrowse-lastrun", $lastrun);
						AUDIT::add($db, AUDIT::REPORT_EMAILED, $reportname, $mailto);
						$rinfo['emailed'] = 1;
					}
					else {
						AUDIT::add($db, AUDIT::EMAIL_ATTEMPT_FAIL, $reportname, $mailto);
					}
				}
				catch(\Exception $e) {
					$logger = new \CodePunch\Base\CPLogger();
					$logger->error($e->getMessage());
					AUDIT::add($db, AUDIT::EMAIL_ATTEMPT_FAIL, $reportname, $mailto);
				}
			}
		}
		
		$responce['emailed'] += $rinfo['emailed'];
		$responce[$reportname] = $rinfo;
		return $responce;
	}

	###############################################################################
	
	public function getReport($name, $doemail, $doreport, &$responce)
	{
		if(!isset($responce['report']))
			$responce['report'] = "";
		if(!isset($responce['attempted']))
			$responce['attempted'] = 0;
		if(!isset($responce['processed']))
			$responce['processed'] = 0;
		if(!isset($responce['emailed']))
			$responce['emailed'] = 0;
		$responce[$name] = array();
		$setup = new \CodePunch\Config\Settings($this->getAuthentication());
		$db = $this->getAuthentication()->getDatabase();
		$table = $db->getReportSchedulerTableName();
		$rows = $db->getFromTable("*", $table, "name=?", $name);
		$emailed = 0;
		if($rows !== false && is_array($rows) && count($rows) == 1) {
			$row = $db->fetchRow($rows, 0);
			$lufreq = $row['frequency'];
			$runits  = $row['runits'];
			$query  = $row['query'];
			$params = $row['params'];
			$lastrun = $row['last_runat'];
			if($lastrun == null || $lastrun == "")
				$lastrun = "2018-2-1 0:0:0";
			$columns = $row['columns'];
			$opmode = $row['autorun'];
			$sorton = $row['sortcolumn'];
			$sortord = $row['descending'] ? "DESC" : "ASC";
			if($sorton == "" || $sorton == null)
				$sorton = "d.sid";
			$refresh = \CodePunch\LU\LookupManager::refreshIntervalInMinutes($lufreq, ($runits&0xF));
			$compdate = date("Y-m-d H:i:s", time() - ($refresh*60));
			$responce[$name]['next_email_after'] = date("Y-m-d H:i:s", strtotime($lastrun)+($refresh*60));
			$save_last_autorun_at = false;
			if($doemail == self::SEND_EMAIL_AUTO) {
				$save_last_autorun_at = true;
				$doemail = self::SEND_EMAIL_NO;
				// Check when this report was last emailed.
				if(strtotime($lastrun) < strtotime($compdate) && $lufreq) {
					// Check for "run at" conditions
					$unitype = $runits&0xF;
					$hour = ($runits >> 4) & 255;
					$date = ($runits >> 12) & 255;
					$week = ($runits >> 20) & 255;
					$ch = intval(date("H"));
					$cd = intval(date("d"));
					$cw = intval(date("w"));
					if($unitype == \CodePunch\LU\LookupManager::LU_DAY) {
						// Only hour is important
						if($ch >= $hour || ($hour == 24 && $ch == 0))
							$doemail = self::SEND_EMAIL_YES;
					}
					else if($unitype == \CodePunch\LU\LookupManager::LU_WEEK) {
						// Only hour and week is important
						if($ch == $hour && $cw == $week)
							$doemail = self::SEND_EMAIL_YES;
					}
					else if($unitype == \CodePunch\LU\LookupManager::LU_MONTH) {
						// Only hour and date is important
						if($ch == $hour && $cd == $date)
							$doemail = self::SEND_EMAIL_YES;
					}
				}
			}
			
			if($doemail == self::SEND_EMAIL_YES)
				$doreport = true;
			
			if($doreport) {
				// Do category checks only when not in CLI mode and CLI key is not provided
				if(!UTIL::is_cli()) {
					$remotekey = UTIL::get_sanitized_request_string("key", "");
					if(!$this->getAuthentication()->isValidRemoteKey($remotekey)) {
						$categoryQuery = $db->getCategoryQueryForCurrentUser(0, $this->getAuthentication());
						if($categoryQuery != "")
							$query = "($query) AND ($categoryQuery)";
					}
				}
				
				$columns = implode(",", explode(";", $columns));
				if($columns == "")
					$columns = "s.hid,s.hostname,s.ip,s.record_type,s.ptr,s.added_on,d.domain,s.ssl_valid_to";
				$select = $columns;
				if(stristr($query, "s.") !== false)
					$select .= ",hid";
				$data = $db->getDomainReportRows($query, $params, $select, $sorton, $sortord);
				if($data !== false && is_array($data) && count($data)) {
					$data = $this->setDaysToExpiryAndStatus($data);
					
					// Replace the s_ in alias names with s.
					foreach($data as &$drow) {
						$keys = array_keys($drow);
						foreach($keys as $k) {
							if(UTIL::starts_with($k, "s_")) {
								$k2 = "s." . substr($k, 2);
								$drow[$k2] = $drow[$k];
								unset($drow[$k]);
							}
						}
					}
					
					$responce['attempted']++;
					$thereport = $this->createReport($row['name'], $data, $columns, $sorton, $sortord, $opmode);
					$responce['report'] .= $thereport;
					
					if($doemail == self::SEND_EMAIL_YES) {
						// Find Report Recipients
						$recipients = $row['emails'];
						if($recipients == "") 
							$recipients = $setup->getOption("email_recipients", "");
						if($recipients == "") {
							$logger = new \CodePunch\Base\CPLogger();
							$logger->error("No email recipients found for Report Mailing");
						}
						else {
							$mailto = "Recipients: " . implode(", ", explode("\n", $recipients));
							try{
								$mailer = new \CodePunch\Base\Mailer($this->getAuthentication());
								$recipients = $mailer->formatEmailRecipients($recipients);
								$status = $mailer->send($recipients, $row['name'], $this->formatForEmail($thereport));
								if($status) {
									$emailed++;
									$lastrun = date("Y-m-d H:i:s");
									if($save_last_autorun_at)
										$db->updateTable($table, array('last_runat'=>$lastrun), "id=?", array($row['id']));
									AUDIT::add($db, AUDIT::REPORT_EMAILED, $row['name'], $mailto);
								}
								else {
									AUDIT::add($db, AUDIT::EMAIL_ATTEMPT_FAIL, $row['name'], $mailto);
								}
							}
							catch(\Exception $e) {
								$logger = new \CodePunch\Base\CPLogger();
								$logger->error($e->getMessage());
								AUDIT::add($db, AUDIT::EMAIL_ATTEMPT_FAIL, $row['name'], $mailto);
							}
						}
					}
					$responce['processed']++;
				}
			}
			$responce[$name]['emailed'] = $emailed;
			$responce[$name]['email_ready_check'] = ($doemail == self::SEND_EMAIL_YES ? 'yes' : 'no');
			$responce[$name]['last_emailed_at'] = $lastrun;
		}
		$responce['emailed'] += $emailed;
		return $responce;
	}
	
	###############################################################################
	
	public function runReports($responce, $doemail=self::SEND_EMAIL_AUTO)
	{
		$db = $this->getAuthentication()->getDatabase();
		$table = $db->getReportSchedulerTableName();
		$rows = $db->getFromTable("name", $table, "autorun != 0");
		if($rows !== false && is_array($rows) && count($rows)) {
			foreach($rows as $row) {
				$row = array_change_key_case($row, CASE_LOWER);
				$this->getReport($row['name'], $doemail, false, $responce);
			}
		}
		$this->getSSLCertReport($responce, false, $doemail);
		$setup = new \CodePunch\Config\Settings($this->getAuthentication());
		$setup->setOption("report_scheduler_run", date("Y-m-d H:i:s"));
		return $responce;
	}
	
	###########################################################################
	# Run Report Scheduler every 1/2 hour or immediately (if $force is true)
	# Returns false if not run, otherwise return $responce 
	
	public static function run($auth, &$responce, $force=false)
	{
		if(UTIL::is_cli()) {
			$runreports = ($force ? true : false);
			$email = self::SEND_EMAIL_NO;
			if(!$force) {
				// Run Report Scheduler once every 30 minutes.
				$setup = new \CodePunch\Config\Settings($auth);
				$lastreprun = $setup->getOption("report_scheduler_run", "");
				if($lastreprun != "" && UTIL::is_a_date($lastreprun)) {
					if((time() - strtotime($lastreprun)) > (1800)) {
						$runreports = true;
						$email = self::SEND_EMAIL_AUTO;
					}
				}
				else {
					$runreports = true;
					$email = self::SEND_EMAIL_AUTO;
				}
			}
			else
				$email = self::SEND_EMAIL_AUTO;
			if($runreports) {
				if($responce['validate'] != \CodePunch\Config\Auth::INVALID) {
					$reportManager = new \CodePunch\UI\ReportManager($auth);
					$responce = $reportManager->runReports($responce, $email);
					$responce['type'] |= \CodePunch\Config\Cron::REPORT_PROCESSING;
					$responce['time'] = $auth->getElapsedTime();
					return true;
				}
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function runCSVReport($uid=0, $columns=array("domain"), $query="", $params=array(), $name="", $sidx="domain", $sord="ASC", $delimiter=",", $description="")
	{
		if(UTIL::is_cli()) {
			$db = $this->getAuthentication()->getDatabase();
			// Delete reports older than specified days.
			$setup = new \CodePunch\Config\Settings($this->getAuthentication());
			$deletedays = intval($setup->getOption("days_to_report_purge", 30));
			$deletedays = $deletedays < 7 ? 7 : $deletedays;
			$db->deleteFromTable($db->getReportsTableName(), "created_at < ?", array(date("Y-m-d", time()-($deletedays*24*3600))));

			$data = $db->saveDomainRowsToCSVFile("", $columns, $query, $params, $sidx, $sord, 500, 0, $delimiter);
			if($data != "") {
				$user = $db->getUserNameFromId($uid);
				$name = trim(($name == "") ? "Scheduled CSV Report" : $name);
				$reportname = $name;
				$rindex = 1;
				while(1) {
					if(!$db->hasRow($db->getReportsTableName(), "name", $reportname))
						break;
					$reportname = "$name-$rindex";
					$rindex++;
				}
				$data = mb_convert_encoding($data, 'UTF-8', 'ISO-8859-1');
				$report = array('name'=>$reportname, 'report'=>$data, 'created_at'=>date("Y-m-d H:i:s"), 'userid'=>$uid, 'description'=>$description);
				return $db->insertIntoTable($db->getReportsTableName(), $report) ? 1 : 0;
			}
		}
		return 0;
	}
}
