<?php
include_once "../../../../tools/inc.php";
$page = include("../../../common.php");
include("../tools/function.string.php");
include("../tools/function.sql.php");
include("../tools/FusionCharts.php");
$module_name='campaigns';
$sub_module='bounced_emails';
$table="newsletters".$config["table_prefix"];
$table_stat='stat'.$config["table_prefix"];
$table_a='pop3_accounts'.$config["table_prefix"];
$table_b='stat_bounced'.$config["table_prefix"];
$table_s='subscribers'.$config["table_prefix"];
$table_pop3='pop3_accounts'.$config["table_prefix"];
$access=check_level("Statistics");
$dt=86400;
$sort_d=(isset($sort_d) ? 1 : (isset($sort) ? 0 : 1));
$sort=(in_array($sort, array('mail', 'name', 'date_created', 'ip', 'browser', 'os')) ? $sort : "date_created");
$n=$settings["rec_count"];

$fields_list=array(
	'overview'		=> '{rs:STATISTICS_TYPE_OVERVIEW}',
	'delivery_stats'	=> '{rs:STATISTICS_TYPE_DELIVERY_STATS}',
	'click_through'		=> '{rs:STATISTICS_TYPE_CLICK_THROUGH}',
	'bounced_emails'	=> '{rs:STATISTICS_TYPE_BOUNCED_EMAILS}',
	'unsubscribe_stats'	=> '{rs:STATISTICS_TYPE_UNSUBSCRIBE_STATS}',
);

$period_list=array(
	'today'			=> '{rs:STATISTICS_PERIOD_LIST_TODAY}',
	'yesterday'		=> '{rs:STATISTICS_PERIOD_LIST_YESTERDAY}',
	'last_7_days'		=> '{rs:STATISTICS_PERIOD_LIST_LAST_7_DAYS}',
	'last_week'		=> '{rs:STATISTICS_PERIOD_LIST_LAST_WEEK}',
	'last_business_week'	=> '{rs:STATISTICS_PERIOD_LIST_LAST_BUSINESS_WEEK}',
	'this_month'		=> '{rs:STATISTICS_PERIOD_LIST_THIS_MONTH}',
	'last_month'		=> '{rs:STATISTICS_PERIOD_LIST_LAST_MONTH}',
	'all_time'		=> '{rs:STATISTICS_PERIOD_LIST_ALL_TIME}',
);

$graph_source_list=array(
	'bounced'		=> '{rs:STATISTICS_GRAPH_SOURCE_BOUNCED_EMAILS}',
);

$graph_by_list=array(
	'day'			=> '{rs:STATISTICS_GRAPH_BY_DAY}',
	'week'			=> '{rs:STATISTICS_GRAPH_BY_WEEK}',
	'month'			=> '{rs:STATISTICS_GRAPH_BY_MONTH}',
);

$trend_list=array(
	'total'			=> '{rs:TREND_TOTAL_BOUNCED_EMAILS}',
);

$trend_by_list=array(
	'day'			=> '{rs:STATISTICS_GRAPH_BY_DAY}',
	'week'			=> '{rs:STATISTICS_GRAPH_BY_WEEK}',
	'month'			=> '{rs:STATISTICS_GRAPH_BY_MONTH}',
);

$rate_source_list=array(
	'bounced'		=> '{rs:STATISTICS_RATE_SOURCE_BOUNCED}',
);

$rate_view_list=array(
	'column'		=> '{rs:STATISTICS_RATE_VIEW_COLUMN}',
	'pie'			=> '{rs:STATISTICS_RATE_VIEW_PIE}',
);

$log_type_list=array(
	'0'			=> '{rs:STATISTICS_BOUNCED_STATS_LOG_ALL}',
	'1'			=> '{rs:STATISTICS_BOUNCED_STATS_LOG_IGNORED}',
	'2'			=> '{rs:STATISTICS_BOUNCED_STATS_LOG_DEACTIVATED}',
	'3'			=> '{rs:STATISTICS_BOUNCED_STATS_LOG_DELETED}',
	'4'			=> '{rs:STATISTICS_BOUNCED_STATS_LOG_BLACKLISTED}',
);

$period_type=(isset($period_type) ? $period_type : 1);
$period=(isset($period_list[$period]) ? $period : 'last_7_days');
$graph_source=(isset($graph_source_list[$graph_source]) ? $graph_source : 'bounced');
$graph_by=(isset($graph_by_list[$graph_by]) ? $graph_by : 'day');
$trend_by=(isset($trend_by_list[$trend_by]) ? $trend_by : 'day');
$rate_source=(isset($rate_source_list[$rate_source]) ? $rate_source : 'bounced');
$rate_view=(isset($rate_view_list[$rate_view]) ? $rate_view : 'pie');
$log_type=(isset($log_type_list[$log_type]) ? $log_type : 0);

$n_id=intval($n_id);
$link->add('n_id');
$link->add('period_type', intval($period_type));

$offset_link=new Link($link->get());
$offset_link->add('graph_source');
$offset_link->add('graph_by');
$offset_link->add('trend_by');
$offset_link->add('rate_source');
$offset_link->add('rate_view');
$offset_link->add('log_type');

$sort_link=$offset_link->get();

$link->add("sort");
$link->add("sort_d");
$link->add('offset');
$offset_link->add("sort");
$offset_link->add("sort_d");

$date_created=$db->get_val($table, 'date_created', "where id='$n_id'");

if (isset($period_type) and !$period_type) {
	$link->add('date_start');
	$link->add('date_end');
	$date_start=check_date($date_start, $settings["date_format"]);
	if ($date_start>time()) $date_start=mktime(0, 0, 0, date('m'), date('d'), date('Y'));

	if (!$date_end=check_date($date_end, $settings["date_format"])) $date_end=time();
	if ($date_start>$date_end) $date_end=$date_start;
	if ($date_end>time()) $date_end=time();
	$date_end=mktime(23, 59, 59, date('m', $date_end), date('d', $date_end), date('Y', $date_end));
}

if ($period_type) {
	if ($period=='today') {
		$date_start=mktime(0, 0, 0, date('m'), date('d'), date('Y'));
		$date_end=mktime(23, 59, 59, date('m'), date('d'), date('Y'));
	} elseif ($period=='yesterday') {
		$date_start=mktime(0, 0, 0, date('m'), date('d')-1, date('Y'));
		$date_end=mktime(23, 59, 59, date('m', $date_start), date('d', $date_start), date('Y', $date_start));
	} elseif ($period=='last_7_days') {
		$date_start=mktime(0, 0, 0, date('m'), date('d')-6, date('Y'));
		$date_end=mktime(23, 59, 59, date('m'), date('d'), date('Y'));
	} elseif ($period=='last_week') {
		$wd=date('w');
		$wd=($wd ? $wd-1 : 6)+7;
		$date_start=mktime(0, 0, 0, date('m'), date('d')-$wd, date('Y'));
		$date_end=mktime(23, 59, 59, date('m', $date_start), date('d', $date_start)+6, date('Y', $date_start));
	} elseif ($period=='last_business_week') {
		$wd=date('w');
		$wd=($wd ? $wd-1 : 6)+7;
		$date_start=mktime(0, 0, 0, date('m'), date('d')-$wd, date('Y'));
		$date_end=mktime(23, 59, 59, date('m', $date_start), date('d', $date_start)+4, date('Y', $date_start));
	} elseif ($period=='this_month') {
		$date_start=mktime(0, 0, 0, date('m'), 1, date('Y'));
		$date_end=mktime(23, 59, 59, date('m'), date('d'), date('Y'));
	} elseif ($period=='last_month') {
		$date_start=mktime(0, 0, 0, date('m')-1, 1, date('Y'));
		$date_end=mktime(0, 0, 0, date('m'), 1, date('Y'));
	} elseif ($period=='all_time') {
		if ($date_start<$date_created) $date_start=$date_created;
		$date_end=mktime(23, 59, 59, date('m'), date('d'), date('Y'));
	}
	$link->add('period');
}

$graph_link=new Link();
$graph_link->array=$link->array;
$graph_link->add('graph_source');
$graph_link->add('graph_by');
$graph_link->add('xml', 1);

$error=new Message("error");
if (isset($_r)) $message->add($message_list[$_r]);

if (isset($access["view"]) or isset($access["admin"])) {
	$page->section(strtoupper('statistics_'.$module_name.'_'.$sub_module));

	// Trends

	$t1=$date_end;
	if ($trend_by=='day') {
		$t0=mktime(0, 0, 0, date('m', $t1), date('d', $t1), date('Y', $t1));
		$pt1=mktime(23, 59, 59, date('m', $t1), date('d', $t1)-1, date('Y', $t1));
		$pt0=mktime(0, 0, 0, date('m', $pt1), date('d', $pt1), date('Y', $pt1));
	} elseif ($trend_by=='week') {
		$wd=date('w', $t1);
		$wd=($wd ? $wd-1 : 6);
		$t0=mktime(0, 0, 0, date('m', $t1), date('d', $t1)-$wd, date('Y', $t1));
		$pt1=mktime(23, 59, 59, date('m', $t0), date('d', $t0)-1, date('Y', $t0));
		$pt0=mktime(0, 0, 0, date('m', $t0), date('d', $t0)-7, date('Y', $t0));
	} else {
		$t0=mktime(0, 0, 0, date('m', $t1), 1, date('Y', $t1));
		$pt1=mktime(23, 59, 59, date('m', $t0), date('d', $t0)-1, date('Y', $t0));
		$pt0=mktime(0, 0, 0, date('m', $pt1), 1, date('Y', $pt1));
	}

	$sql=array();
	$sql['bounced']=	"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].BOUNCED' and
						date_created>='%t0%' and
						date_created<='%t1%'
					group by code";
	foreach ($trend_list as $key => $val) {
		$prev=$db->count(str_replace(array('%t0%', '%t1%'), array($pt0, $pt1), $sql[$key]), true);
		$current=$db->count(str_replace(array('%t0%', '%t1%'), array($t0, $t1), $sql[$key]), true)-$prev;
		$current_percent=($current ? ($current/($prev ? $prev : $current))*100 : 0);
		if ($graph_source==$key) {
			$trend_count=$current;
			$trend_percent=$current_percent;
		}
		$page->addrow('TRENDS', array(
			'id'		=> $key,
			'class'		=> ($current>0 ? 'up' : ($current<0 ? 'down' : 'none')),
			'title'		=> $val,
			'count'		=> ($current>0 ? '+' : '').number_format($current, 0, ',', ' '),
			'percent'	=> ($current>0 ? '+' : '').number_format($current_percent, (($current_percent<1 and $current_percent>0) ? 2 : 0), ',', ' '),
		));
	}

	// Statistics info

	$sql="select
		n.id,
		n.subject,
		n.last_bounced,
		n.pop3,
		a.id as account_id,
		a.title as account_title
	from
		$table n
	left join $table_a a on a.id=n.pop3
	where n.id='$n_id'".(isset($user_permissions['newsletters_list']) ? " and n.id in (".$user_permissions['newsletters_list'].")" : '');
	list($data)=$db->select($sql);
	if (!isset($data['id'])) {
		$db->close();
		header("Location: {$PATH}statistics/campaigns/");
		exit();
	}

	$data['total_sent']=$db->get_val($table_stat, 'count', "where code='NEWSLETTER[{$data['id']}].SEND.ALL'");

	// Most recent bounced emails
	$sql="select max(b.date_created) as date_created, s.mail from $table_b b
	left join {$table_s} s on s.id=b.subscriber_id
	where newsletter_id='{$n_id}'
	group by s.mail
	order by date_created desc limit 5";
	$rs=$db->execute($sql);
	$i=0;
	while ($row=$db->get_row($rs)) {
		$i++;
		$row['n']=$i;
		$row['title']=shortStr($row['mail'], 20);
		$row['date_created']=date('F j, Y, h:ia', $row['date_created']);
		$page->addrow('RECENT', $row);
	}

	$data['processed_last_bounced']=	$db->get_val($table_b, 'count(*)', "where date_created>='".$data['last_bounced']."'");
	$data['total_bounced']=			$db->get_val($table_stat, 'count', "where code='NEWSLETTER[{$data['id']}].BOUNCED.ALL'");
	$data['delivered_fine']=		$data['total_sent']-$data['total_bounced'];
	$data['pop3_title']=			$db->get_val($table_pop3, 'title', "where id='{$data[pop3]}'");

	$data['subject']=			shortStr($data['subject']);
	$data['last_check']=			($data['last_bounced'] ? date('F j, Y, h:ia', $data['last_bounced']) : '-');
	$data['processed_last_bounced']=	number_format($data['processed_last_bounced'], 0, ',', ' ');
	$data['total_bounced']=			number_format($data['total_bounced'], 0, ',', ' ');
	$data['delivered_fine']=		number_format($data['delivered_fine'], 0, ',', ' ');
	$data['pop3_name']=			shortStr($data['pop3_title']);


	// Filters and other
	get_row_from_array('PERIOD_SELECT', $period_list, $period);
	get_row_from_array('TOP_FIELDS', $fields_list, $sub_module);
	get_fields_from_array('GRAPH_SOURCE_SELECT', $graph_source_list, $graph_source);
	get_fields_from_array('GRAPH_BY_SELECT', $graph_by_list, $graph_by);
	get_fields_from_array('TREND_BY_SELECT', $trend_by_list, $trend_by);
	get_row_from_array('RATE_SOURCE', $rate_source_list, $rate_source);
	get_fields_from_array('RATE_VIEW', $rate_view_list, $rate_view);
	get_row_from_array('LOG_TYPE', $log_type_list, $log_type);
	$page->assign($data);

	$where="where newsletter_id={$n_id}".($log_type ? " and type='".($log_type-1)."'" : '');
	$sql="select count(*) from $table_b $where";
	$count=$db->count($sql, true);
        $i=ceil($count/$n)-1;
        $offset=(($offset>$i) ? $i : ($offset<0 ? 0 : $offset));
	$sql="select s.id as s_id, s.mail, s.name, b.* from {$table_b} b
	left join {$table_s} s on s.id=b.subscriber_id
	{$where}
	order by {$sort} ".($sort_d ? " DESC" : "")."
	limit ".($offset*$n). ", $n";
	$rs = $db->execute($sql);
        while ($row=$db->get_row($rs)) {
		$row['date_created']=date($settings["date_format"]." [H:i]", $row["date_created"]);
                $page->addrow("LOG_LINE", $row);
        }

        $page->assign(array(
		'period_title'		=> '{rs:STATISTICS_PERIOD'.($period_type ? '_'.strtoupper($period) : '').'}',
		'period_date_start'	=> date('M j, Y', $date_start),
		'period_date_end'	=> date('M j, Y', $date_end),
		'period_type'		=> $period_type,
		'graph_source'		=> $graph_source,
		'graph_by'		=> $graph_by,
		'trend_by'		=> $trend_by,
		'rate_source'		=> $rate_source,
		'rate_view'		=> $rate_view,
		'log_type'		=> $log_type,
		'is_'.$rate_view	=> 1,
		'graph_'.$graph_view	=> 1,
		'trend_title'		=> $graph_source_list[$graph_source],
		'trend_count'		=> ($trend_count>0 ? '+' : '').number_format($trend_count, 0, ',', ' '),
		'trend_percent'		=> ($trend_count>0 ? '+' : '').number_format($trend_percent, (($trend_percent<1 and $trend_percent>0) ? 2 : 0), ',', ' '),
		'date_start'		=> ((!$period_type and $date_start)? date($settings["date_format"], $date_start) : ''),
		'date_end'		=> ((!$period_type and $date_end) ? date($settings["date_format"], $date_end) : ''),
                "BACK_LINK"             => "{$PATH}statistics/{$module_name}/",
                "BACK_TITLE"            => '{rs:'.strtoupper('statistics_'.$module_name).'}',
		'xml_data_url'		=> urlencode("{$PATH}statistics/{$module_name}/{$sub_module}/".$graph_link->get()),
		'rate_xml_data_url'	=> urlencode("{$PATH}statistics/{$module_name}/{$sub_module}/?n_id={$n_id}&xml=1&type=rate&rate_source={$rate_source}&rate_view={$rate_view}"),
		'n_id'			=> $n_id,
		'offset'		=> get_new_offsets($count, $n, $offset_link->get(), $offset),
		'sort_by_mail'		=> $sort_link.($sort_link ? '&' : '?')."sort=mail".($sort=="mail" ? ($sort_d ? "" : "&sort_d=1") : ""),
		'sort_by_name'		=> $sort_link.($sort_link ? '&' : '?')."sort=name".($sort=="name" ? ($sort_d ? "" : "&sort_d=1") : ""),
		'sort_by_date_created'	=> $sort_link.($sort_link ? '&' : '?')."sort=date_created".($sort=="date_created" ? ($sort_d ? "" : "&sort_d=1") : "&sort_d=1"),
		'sort_by_ip'		=> $sort_link.($sort_link ? '&' : '?')."sort=ip".($sort=="ip" ? ($sort_d ? "" : "&sort_d=1") : ""),
		'sort_by_browser'	=> $sort_link.($sort_link ? '&' : '?')."sort=browser".($sort=="browser" ? ($sort_d ? "" : "&sort_d=1") : ""),
		'sort_by_os'		=> $sort_link.($sort_link ? '&' : '?')."sort=os".($sort=="os" ? ($sort_d ? "" : "&sort_d=1") : ""),

		'sort_mail_img'		=> ($sort=="mail" ? ($sort_d ? "_asc" : "_desc") : ""),
		'sort_name_img'		=> ($sort=="name" ? ($sort_d ? "_asc" : "_desc") : ""),
		'sort_date_created_img'	=> ($sort=="date_created" ? ($sort_d ? "_asc" : "_desc") : ""),
		'sort_ip_img'		=> ($sort=="ip" ? ($sort_d ? "_asc" : "_desc") : ""),
		'sort_browser_img'	=> ($sort=="browser" ? ($sort_d ? "_asc" : "_desc") : ""),
		'sort_os_img'		=> ($sort=="os" ? ($sort_d ? "_asc" : "_desc") : ""),
		'log_count'		=> $count,
		'bounced_account'	=> $data['pop3'],
        ));
} else {
        $page->section("ACCESS_DENIED");
        $error->add("{rs:ERROR_ACCESS_RESTRICTED}");
}
$sql="select id, name as title from $table".(isset($user_permissions['newsletters_list']) ? " where id in (".$user_permissions['newsletters_list'].")" : '')." order by title";
$rs = $db->execute($sql);
while ($row=$db->get_row($rs)) {
	$row['name']=shortStr($row['title'], 30);
	$row['selected']=($row['id']==$n_id);
	$page->addrow("NEWSLETTER_LIST", $row);
}
add_to_tree("{rs:TITLE_STATISTICS}", "statistics/");
add_to_tree('{rs:'.strtoupper('statistics_'.$module_name).'}', "statistics/{$module_name}/");
add_to_tree('{rs:'.strtoupper('statistics_type_'.$sub_module).'}', "statistics/{$module_name}/{$sub_module}/");
$db->close();
$page->assign(array(
	'MODULE_NAME'		=> $module_name,
        "TITLE"                 => "{rs:STATISTICS_CAMPAIGNS}",
        "ERROR"                 => ($error->count ? $error->get() : ""),
        "MESSAGE"               => ($message->count ? $message->get() : ""),
        "LINK"                  => $link->get(),
 	"is_statistics"		=> 1,
        "IMG"                   => "statistics.gif",
	"WINDOW_TITLE"		=> "{rs:STATISTICS_TYPE_BOUNCED_EMAILS}",
));
print $page->evaluate();
?>