<?php 
/**
 * Example page for how to use the Excel Writer XML
 * @package ExcelWriterXML
 * @subpackage examples
 * @filesource
 */

/**
 * Include the required Class file
 */
include('classes/ExcelWriterXML.php');

require_once 'configuration.php';
require_once 'includes/common.functions.php';
require_once ('classes/database.php');
require_once ('classes/functions.php');
$sqlObj = new mysqlClass;
$funcObj = new funcClass;

if (is_numeric($_GET['id']))
	$activeCampaignID	= intval($_GET['id']);
else
	$activeCampaignID	= base64_decode(base64_decode(base64_decode($_GET['id'])));

$type				= $_GET['type'];

/*
Query for VIEWS
*/
$query 				= "SELECT EMAIL, USER_IP, OS, DATE_TIME FROM ##report WHERE CAMPAIGN_ID = '{$activeCampaignID}' AND ACTION='view' AND LENGTH(EMAIL) > 0 ORDER BY ID ASC";
$viewResultSet		= array();
$viewResultSet		= $funcObj->CustomQuery($query);

/*
Query for CLICKS
*/

$query 				= "SELECT EMAIL, USER_IP, OS, DATE_TIME, REDIRECT_URL FROM ##report WHERE CAMPAIGN_ID = '{$activeCampaignID}' AND ACTION='track' AND LENGTH(EMAIL) > 0 ORDER BY ID ASC";
$clickResultSet		= array();
$clickResultSet		= $funcObj->CustomQuery($query);

/*
Query for ONLINE VIEWS (CAN'T READ CLICKS)
*/

$query 				= "SELECT EMAIL, USER_IP, OS, DATE_TIME, REDIRECT_URL FROM ##report WHERE CAMPAIGN_ID = '{$activeCampaignID}' AND ACTION='cantread' AND LENGTH(EMAIL) > 0 ORDER BY ID ASC";
$cantReadResultSet	= array();
$cantReadResultSet	= $funcObj->CustomQuery($query);

/*
Query for tracking hard and soft bounces
*/

$query 				= "SELECT DISTINCT(EMAIL) AS EMAIL, DATE_TIME, UPPER(action) AS ACTION FROM ##report WHERE CAMPAIGN_ID = '{$activeCampaignID}' AND ACTION IN ('hardbounce', 'softbounce') AND LENGTH(EMAIL) > 0 ORDER BY ID ASC";
$bounceResultSet	= array();
$bounceResultSet	= $funcObj->CustomQuery($query);


$query 				= "SELECT CAMPAIGN_NAME FROM ##campaigns WHERE ID = '{$activeCampaignID}' LIMIT 1";
$cmpNameResultSet	= array();
$cmpNameResultSet	= $funcObj->CustomQuery($query);
$campaignName		= $cmpNameResultSet[0]['CAMPAIGN_NAME'];

/**
 * @source
 */

switch ($type)
{ 
	case 'excel': 
		//$xml = new ExcelWriterXML;

		$xml = new ExcelWriterXML('Report.xls');
		$xml->docAuthor('Segnant Technologies');
		
		$format = $xml->addStyle('StyleHeader');
		$format->fontBold();
		$format->fontColor('#000000');
		$format->fontSize('11');
		$format->fontName('Tahoma');
		$format->border('All', '2', '#000000');
		
		$format2 = $xml->addStyle('Text');
		$format2->fontColor('#000000');
		$format2->fontName('Tahoma');
		$format2->border('All', '1', '#000000');
		
		########################### SHEET 1 #######################
		$sheet = $xml->addSheet('Views');
		
		$sheet->columnWidth(1,'130');
		$sheet->columnWidth(2,'190');
		$sheet->columnWidth(3,'100');
		$sheet->columnWidth(4,'170');
		
		$sheet->writeString(1,1,'Date','StyleHeader');
		$sheet->writeString(1,2,'E-mail','StyleHeader');
		$sheet->writeString(1,3,'IP','StyleHeader');
		$sheet->writeString(1,4,'Operating System','StyleHeader');
		
		$startCount = 2;
		foreach ($viewResultSet as $row)
		{
			
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= ' ' . date("g:i:s a", strtotime($row['DATE_TIME']));
			$sheet->writeString($startCount, 1, $dateFormat, 'Text');
			$sheet->writeString($startCount, 2, $row['EMAIL'], 'Text');
			$sheet->writeString($startCount, 3, $row['USER_IP'], 'Text');
			$sheet->writeString($startCount, 4, $row['OS'], 'Text');	
			$startCount++;
		}
		
		## Add a new sheet.
		########################### SHEET 2 #######################
		$sheet2 = $xml->addSheet('Clicks');
		$sheet2->columnWidth(1,'130');
		$sheet2->columnWidth(2,'190');
		$sheet2->columnWidth(3,'100');
		$sheet2->columnWidth(4,'170');
		$sheet2->columnWidth(5,'300');
		
		$sheet2->writeString(1,1,'Date','StyleHeader');
		$sheet2->writeString(1,2,'E-mail','StyleHeader');
		$sheet2->writeString(1,3,'IP','StyleHeader');
		$sheet2->writeString(1,4,'Operating System','StyleHeader');
		$sheet2->writeString(1,5,'Redirect URL','StyleHeader');
		
		$startCount = 2;
		foreach ($clickResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= ' ' . date("g:i:s a", strtotime($row['DATE_TIME']));
			$sheet2->writeString($startCount, 1, $dateFormat, 'Text');
			$sheet2->writeString($startCount, 2, $row['EMAIL'], 'Text');
			$sheet2->writeString($startCount, 3, $row['USER_IP'], 'Text');
			$sheet2->writeString($startCount, 4, $row['OS'], 'Text');	
			$sheet2->writeString($startCount, 5, $row['REDIRECT_URL'], 'Text');	
			$startCount++;
		}
		
		## Add a new sheet.
		########################### SHEET 3 #######################
		$sheet3 = $xml->addSheet('Online Views');
		$sheet3->columnWidth(1,'130');
		$sheet3->columnWidth(2,'190');
		$sheet3->columnWidth(3,'100');
		$sheet3->columnWidth(4,'170');
		$sheet3->columnWidth(5,'300');
		
		$sheet3->writeString(1,1,'Date','StyleHeader');
		$sheet3->writeString(1,2,'E-mail','StyleHeader');
		$sheet3->writeString(1,3,'IP','StyleHeader');
		$sheet3->writeString(1,4,'Operating System','StyleHeader');
		$sheet3->writeString(1,5,'Redirect URL','StyleHeader');
		
		$startCount = 2;
		foreach ($cantReadResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= ' ' . date("g:i:s a", strtotime($row['DATE_TIME']));
			$sheet3->writeString($startCount, 1, $dateFormat, 'Text');
			$sheet3->writeString($startCount, 2, $row['EMAIL'], 'Text');
			$sheet3->writeString($startCount, 3, $row['USER_IP'], 'Text');
			$sheet3->writeString($startCount, 4, $row['OS'], 'Text');	
			$sheet3->writeString($startCount, 5, $row['REDIRECT_URL'], 'Text');	
			$startCount++;
		}
		
		## Add a new sheet.
		########################### SHEET 4 #######################
		$sheet4 = $xml->addSheet('Email Bounce');
		$sheet4->columnWidth(1,'130');
		$sheet4->columnWidth(2,'190');
		$sheet4->columnWidth(3,'130');
		
		$sheet4->writeString(1,1,'Date','StyleHeader');
		$sheet4->writeString(1,2,'E-mail','StyleHeader');
		$sheet4->writeString(1,3,'Bounce Type','StyleHeader');
		
		$startCount = 2;
		foreach ($bounceResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= ' ' . date("g:i:s a", strtotime($row['DATE_TIME']));
			$sheet4->writeString($startCount, 1, $dateFormat, 'Text');
			$sheet4->writeString($startCount, 2, $row['EMAIL'], 'Text');
			$sheet4->writeString($startCount, 3, $row['ACTION'], 'Text');
			$startCount++;
		}
		
		
		$xml->sendHeaders();
		$xml->writeData();
	
		break;
	case 'html':
		// Global variable from configuration.php file.
		global $absPath;
		
		$startCount = 1;
		$viewReportContent = "";
		foreach ($viewResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= '<br /><small style="color: #696969">' . date("g:i:s a", strtotime($row['DATE_TIME'])) . '</small>';
			
			if(($startCount%2)==0)
			$className="class='row0'";
			else
			$className="class='row1'";
			$viewReportContent .= "<tr $className>";
			$viewReportContent .= 
			'<td align="right">'.$startCount.'</td>
			<td>'.$dateFormat.'</td>
			<td>'.$row['EMAIL'].'&nbsp;</td>
			<td>'.$row['USER_IP'].'&nbsp;</td>
			<td>'.$row['OS'].'&nbsp;</td>
			</tr>';
			$startCount++;
		}
		
		$startCount = 1;
		$clickReportContent = "";
		foreach ($clickResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= '<br /><small style="color: #696969">' . date("g:i:s a", strtotime($row['DATE_TIME'])) . '</small>';
			$wrapURL = wordwrap($row['REDIRECT_URL'], 42, "<br />\n", true);
			if(($startCount%2)==0)
			$className="class='row0'";
			else
			$className="class='row1'";
			$clickReportContent .= "<tr $className>";
			$clickReportContent .= 
			'<td align="right">'.$startCount.'</td>
			<td>'.$dateFormat.'</td>
			<td>'.$row['EMAIL'].'&nbsp;</td>
			<td>'.$row['USER_IP'].'&nbsp;</td>
			<td>'.$row['OS'].'&nbsp;</td>
			<td><a href="'.$row['REDIRECT_URL'].'">'.$wrapURL.'</a>&nbsp;</td>
			</tr>';
			$startCount++;
		}
		
		$startCount = 1;
		$cantReadReportContent = "";
		foreach ($cantReadResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= '<br /><small style="color: #696969">' . date("g:i:s a", strtotime($row['DATE_TIME'])) . '</small>';
			$wrapURL = wordwrap($row['REDIRECT_URL'], 42, "<br />\n", true);
			
			if(($startCount%2)==0)
			$className="class='row0'";
			else
			$className="class='row1'";
			$cantReadReportContent .= "<tr $className>";
			$cantReadReportContent .= 
			'<td align="right">'.$startCount.'</td>
			<td>'.$dateFormat.'</td>
			<td>'.$row['EMAIL'].'&nbsp;</td>
			<td>'.$row['USER_IP'].'&nbsp;</td>
			<td>'.$row['OS'].'&nbsp;</td>
			<td><a href="'.$row['REDIRECT_URL'].'">'.$wrapURL.'</a>&nbsp;</td>
			</tr>';
			$startCount++;
		}
		$bounceReportContent = "";
		foreach ($bounceResultSet as $row)
		{
			$dateFormat = date("M j, Y", strtotime($row['DATE_TIME']));
			$dateFormat .= '<br /><small style="color: #696969">' . date("g:i:s a", strtotime($row['DATE_TIME'])) . '</small>';
			
			if(($startCount%2)==0)
			$className="class='row0'";
			else
			$className="class='row1'";
			$bounceReportContent .= "<tr $className>";
			$bounceReportContent .= 
			'<td align="right">'.$startCount.'</td>
			<td>'.$dateFormat.'</td>
			<td>'.$row['EMAIL'].'&nbsp;</td>
			<td>'.$row['ACTION'].'&nbsp;</td>
			</tr>';
			$startCount++;
		}
		
		
		//$replaceContent = array();
		$replaceContent = array(
		'[ABSPATH]' 			=> $absPath, 
		'[DATE]' 				=> date("M j, Y"),	 
		'[VIEWSREPORT]'			=> $viewReportContent,
		'[CLICKREPORT]'			=> $clickReportContent,
		'[ONLINEVIEWSREPORT]'	=> $cantReadReportContent,
		'[BOUNCEREPORT]'	=> $bounceReportContent,
		'[CAMPAIGNNAME]'		=> $campaignName	 
		);
		
		/*
		Read the template file and replace variables.
		*/
		$contents		= file_get_contents ('templates/admin.campaignreport.tpl');
		$liveContent 	= $funcObj->replaceAll($replaceContent, $contents);
		//echo $liveContent;
		dl_file($liveContent);
		
	
		break;		
}
?>