<?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='overview';
$table="newsletters".$config["table_prefix"];
$table_u='users'.$config["table_prefix"];
$table_g='mailing_groups'.$config["table_prefix"];
$table_sg='subscribers_group'.$config["table_prefix"];
$table_stat='stat'.$config["table_prefix"];
$table_sl='stat_clicks'.$config["table_prefix"];
$access=check_level("Statistics");
$dt=86400;

$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(
	'subscribers'		=> '{rs:STATISTICS_GRAPH_SOURCE_SUBSCRIBERS}',
	'sent'			=> '{rs:STATISTICS_GRAPH_SOURCE_SENT}',
	'click'			=> '{rs:STATISTICS_GRAPH_SOURCE_CLICK_THROUGHS}',
);

$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(
	'subscribers'		=> '{rs:TREND_SUBSCRIBERS}',
	'unsubscribers'		=> '{rs:TREND_UNSUBSCRIBES}',
	'click'			=> '{rs:TREND_TOTAL_CLICKS}',
	'sent'			=> '{rs:TREND_SENT}',
	'deliveries'		=> '{rs:TREND_DELIVERIES}',
);

$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(
	'delivery'		=> '{rs:STATISTICS_RATE_SOURCE_DELIVERY}',
	'click'			=> '{rs:STATISTICS_RATE_SOURCE_CLICK_THROUGH}',
	'bounced'		=> '{rs:STATISTICS_RATE_SOURCE_BOUNCED}',
);

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

$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 : 'subscribers');
$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 : 'delivery');
$rate_view=(isset($rate_view_list[$rate_view]) ? $rate_view : 'pie');

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

$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['subscribers']=		"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].SUBSCRIBES' and
						date_created>='%t0%' and
						date_created<='%t1%'
					group by code";
	$sql['unsubscribers']=		"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].UNSUBSCRIBES' and
						date_created>='%t0%' and
						date_created<='%t1%'
					group by code";
	$sql['click']=		"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].CLICK' and
						date_created>='%t0%' and
						date_created<='%t1%'
					group by code";
	$sql['sent']=		"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].SEND' and
						date_created>='%t0%' and
						date_created<='%t1%'
					group by code";
	$sql['deliveries']=		"select sum(count) from $table_stat
					where
						code='NEWSLETTER[{$n_id}].DELIVERIES' 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), ',', ' '),
		));
	}
	if ($graph_source=='sent') {
		$prev=$db->count(str_replace(array('%t0%', '%t1%'), array($pt0, $pt1), $sql[$graph_source]), true);
		$trend_count=$db->count(str_replace(array('%t0%', '%t1%'), array($t0, $t1), $sql[$graph_source]), true)-$prev;
		$trend_percent=($trend_count/($prev ? $prev : $trend_count))*100;
	}

	// Statistics info

	$sql="select
		n.id,
		n.subject,
		n.groups,
		n.date_created,
		n.modify_time,
		n.last_sent,
		n.deliveries_count,
		n.last_delivery,
		n.last_delivery_user,
		if(u.name,u.name,u.login) as last_delivery_name,
		n.owner_id
	from
		$table n
	left join $table_u u on u.id=n.last_delivery_user
	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();
	}
	$sql="select subscribers_id from $table_sg where
		'{$data['groups']}' like concat(group_id) or 
		'{$data['groups']}' like concat(group_id, ',%') or
		'{$data['groups']}' like concat('%,', group_id) or
		'{$data['groups']}' like concat('%,', group_id, ',%')
	group by subscribers_id";

	$data['subscribers']=		$db->count($sql);
	$data['total_sent']=		$db->get_val($table_stat, 'count', "where code='NEWSLETTER[{$data['id']}].SEND.ALL'");
	$data['total_undelivered']=	$db->get_val($table_stat, 'count', "where code='NEWSLETTER[{$data['id']}].UNDELIVERED.ALL'");
	$data['delivery_rate']=		100-($data['total_undelivered']/$data['total_sent'])*100;
	$data['total_click']=		$db->get_val($table_stat, 'count', "where code like 'NEWSLETTER[{$data['id']}].CLICK.ALL.UNIQUE'");
	$data['total_bounced']=		$db->get_val($table_stat, 'count', "where code='NEWSLETTER[{$data['id']}].BOUNCED.ALL'");
	$data['click_rate']=		($data['total_click']/$data['total_sent'])*100;
	$data['bounced_rate']=		($data['total_bounced']/$data['total_sent'])*100;

	$data['subject']=		shortStr($data['subject']);
	$data['subscribers']=		number_format($data['subscribers'], 0, ',', ' ');
	$data["date_created"]=		date('F j, Y, h:ia', $data["date_created"]);
	$data["modify_time"]=		date('F j, Y, h:ia', $data["modify_time"]);
	$data['deliveries_count']=	number_format($data['deliveries_count'], 0, ',', ' ');
	$data["last_delivery"]=		($data["last_delivery"] ? date('F j, Y, h:ia', $data["last_delivery"]) : '');
	$data['click_rate']=		number_format($data['click_rate'], (($data['click_rate']>0 and $data['click_rate']<1) ? 2 : 0), ',', ' ');
	$data['bounced_rate']=		number_format($data['bounced_rate'], (($data['bounced_rate']>0 and $data['bounced_rate']<1) ? 2 : 0), ',', ' ');
	$data['delivery_rate']=		number_format($data['delivery_rate'], (($data['delivery_rate']>0 and $data['delivery_rate']<1) ? 2 : 0), ',', ' ');

	// Mailing groups
	$sql="select g.id, g.title, count(sg.subscribers_id) as count from mailing_groups{$config["table_prefix"]} g
	left join $table_sg sg on sg.group_id=g.id
	where g.id in ({$data['groups']})
	group by g.id";
	$rs=$db->execute($sql);
	$i=0;
	while ($row=$db->get_row($rs)) {
		$i++;
		$row['n']=$i;
		$row['count']=number_format($row['count'], 0, ',', ' ');
		$page->addrow('GROUPS_LIST', $row);
	}

	// Accessed by
	$sql="select u.id, u.login as title from users{$config["table_prefix"]} u
		left join users_newsletter{$config["table_prefix"]} un on un.newsletter_id='{$data['id']}' or un.newsletter_id='-1'
		where (u.id='{$data['owner_id']}' or u.system_status=1 or un.user_id=u.id) and u.status=1
		group by u.id
		order by u.login";
	get_row_from_sql('ACCESSED_BY_LIST', $sql);

	// 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);
	$page->assign($data);
        $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,
		'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).'}',
		'topChartSettings'	=> "{$PATH}statistics/{$module_name}/{$sub_module}/topChartSettings.php?n_id={$n_id}&graph_source={$graph_source}",
		'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,
        ));
} 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,
	'SUB_MODULE'		=> $sub_module,
        "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_OVERVIEW}",
));
print $page->evaluate();
?>