<?php include('mod/_mod_security.php'); ?>
<?php
	$criteria = '';
	$filter = '';
	if (empty($_POST['eac_event_date_start'])) :
		$_POST['eac_event_date_start'] = date('Y-m-d', strtotime('monday last week'));
	endif;
	if (empty($_POST['eac_date_start'])) :
		$_POST['eac_date_start'] = date('Y-m-d', strtotime('monday last week'));
	endif;
	if (empty($_POST['eac_date_end'])) :
		$_POST['eac_date_end'] = date('Y-m-d', strtotime('sunday last week'));
	endif;
	if (!empty($_POST['eac_event_date_start']) && is_date($_POST['eac_event_date_start'])) :
		$criteria .= " AND e.start_time_local >= " . nzdate($_POST['eac_event_date_start']) . " ";
		$filter .= '<br> Event start date (on or after): ' . nzdate_display($_POST['eac_event_date_start']);
	endif;
	if (!empty($_POST['eac_date_start']) && is_date($_POST['eac_date_start'])) :
		$criteria .= " AND a.create_date >= " . nzdate($_POST['eac_date_start']) . " ";
		$filter .= '<br> Analytics start date: ' . nzdate_display($_POST['eac_date_start']);
	endif;
	if (!empty($_POST['eac_date_end']) && is_date($_POST['eac_date_end'])) :
		$criteria .= " AND a.create_date <= " . nzdatetime($_POST['eac_date_end'] . ' 23:59:59') . " ";
		$filter .= '<br> Analytics end date: ' . nzdate_display($_POST['eac_date_end']);
	endif;
	if (!empty($_POST['eac_category_id'])) :
		$criteria .= " AND c.id = " . nz($_POST['eac_category_id'], '0') . " ";
		$filter .= '<br> Event category: ' . lookup_db_field('categories', 'id', $_POST['eac_category_id'], 'name');
	endif;
	if (!empty($_POST['eac_include_location'])) :
		$criteria .= " AND c.is_location IN (0, 1) ";
		$filter .= '<br> Location categories: Included';
	else :
		$criteria .= " AND c.is_location IN (0) ";
		$filter .= '<br> Location categories: Excluded';
	endif;
	if (!empty($_POST['eac_include_free'])) :
		$filter .= '<br> Free category: Included';
	else :
		$criteria .= " AND c.slug <> 'free' ";
		$filter .= '<br> Free category: Excluded';
	endif;
	$slug_start_position = strlen(APP_BASE_USER_SITE_SECURE . 'events/') + 1;
	$sql = "SELECT
		c.id,
		c.name,
		c.slug,
		COUNT(DISTINCT e.id) AS event_count,
		COUNT(DISTINCT CASE WHEN a.category = 'page_view' AND a.action = 'view' THEN a.id ELSE NULL END) AS view_count,
		COUNT(DISTINCT CASE WHEN a.category = 'outbound_ticket_link' AND a.action = 'click' THEN a.id ELSE NULL END) AS click_count
		FROM categories c
		LEFT JOIN events_categories ec ON (ec.category_id = c.id)
		LEFT JOIN events e ON (ec.event_id = e.id AND e.status = 1)
		LEFT JOIN analytics a ON (a.reference_type = 'event' AND a.reference_id = e.id)
		WHERE c.active = 1
		$criteria
		GROUP BY c.id, c.name
		ORDER BY c.name ASC
	";
	//error_log($sql);
	$result = $db->query($sql) or die('Database Error!');
	$row_count = $result->rowCount();
	if ($row_count <= 0) :
		echo 'No data for report!';
		exit();
	endif;
	
	$rpt = get_report_class($_POST['output_format']);
	$rpt->set_report_title('Event Analytics by Category');
	$rpt->report_start();
	
	$rpt->header_start();
	if (!empty($filter)) :
		$rpt->header_row_start();
		$rpt->header_cell_add('Filters: '.$filter, ['colspan'=>'7', 'class'=>'large']);
		$rpt->header_row_end();
	endif;
	$rpt->header_row_start();
	$rpt->header_cell_add('Category', ['width'=>'220']);
	$rpt->header_cell_add('# of Events', ['width'=>'100']);
	$rpt->header_cell_add('Views', ['width'=>'100']);
	$rpt->header_cell_add('% of total views', ['width'=>'120']);
	$rpt->header_cell_add('Avg. Views', ['width'=>'100']);
	$rpt->header_cell_add('Clicks', ['width'=>'100']);
	$rpt->header_cell_add('% Clicks', ['width'=>'110']);
	$rpt->header_row_end();
	$rpt->header_end();
	
	$rpt->data_start();
	$rows = $result->fetchAll(PDO::FETCH_OBJ);
	$total_events = array_sum(array_column($rows, 'event_count'));
	$total_views = array_sum(array_column($rows, 'view_count'));
	$total_clicks = array_sum(array_column($rows, 'click_count'));
	foreach ($rows as $row) :
		$link_data = [];
		$link_data['eae_event_date_start'] = $_POST['eac_event_date_start'];
		$link_data['eae_date_start'] = $_POST['eac_date_start'];
		$link_data['eae_date_end'] = $_POST['eac_date_end'];
		$link_data['eae_category_id'] = $row->id;
		$link_data['output_format'] = 'html';
		$link_data['orientation'] = 'P';
		$link_data['email_to'] = '';
		$deeplink_data = urlencode(json_encode($link_data));
		$rpt->data_row_start();
		//$rpt->data_cell_add('<a href="' . APP_BASE_USER_SITE_SECURE . 'events/categories/' . $row->slug . '" target="_blank">' . $row->name . '</a>');
		$rpt->data_cell_add('<a href="' . APP_BASE_SECURE . 'report.php?rpt=analytics_by_event&type=oo_report&deeplink=true&deeplink_data=' . $deeplink_data . '" target="_blank">' . $row->name . '</a>');
		$rpt->data_cell_add($row->event_count, ['align'=>'right']);
		$rpt->data_cell_add($row->view_count, ['align'=>'right']);
		$rpt->data_cell_add(number_format(($row->view_count / $total_views) * 100, 1), ['align'=>'right']);
		$rpt->data_cell_add(number_format($row->view_count / $row->event_count, 1), ['align'=>'right']);
		$rpt->data_cell_add($row->click_count, ['align'=>'right']);
		$rpt->data_cell_add(number_format($row->view_count > 0 ? (($row->click_count / $row->view_count) * 100) : 0, 2), ['align'=>'right']);
		$rpt->data_row_end();
	endforeach;
	$rpt->data_row_start();
	$rpt->data_cell_add('Totals', ['style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add($total_events, ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add($total_views, ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add(number_format(100, 1), ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add(number_format($total_views / $total_events, 1), ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add($total_clicks, ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_cell_add(number_format($total_views > 0 ? (($total_clicks / $total_views) * 100) : 0, 2), ['align'=>'right', 'style' => 'font-size:125%; font-weight:bold;']);
	$rpt->data_row_end();
	$rpt->data_end();
	
	$rpt->report_end();
	$rpt->output_report();