428 lines
19 KiB
PHP
428 lines
19 KiB
PHP
<?php
|
||
// report.php
|
||
session_start();
|
||
|
||
// Проверка пароля
|
||
$correct_password = 'sdf7-jKl89w'; // Замените на ваш пароль
|
||
|
||
if (!isset($_SESSION['authenticated']) || $_SESSION['authenticated'] !== true) {
|
||
if (isset($_POST['password'])) {
|
||
if ($_POST['password'] === $correct_password) {
|
||
$_SESSION['authenticated'] = true;
|
||
} else {
|
||
$error = "Неверный пароль";
|
||
}
|
||
}
|
||
|
||
if (!isset($_SESSION['authenticated']) || $_SESSION['authenticated'] !== true) {
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html lang="ru">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>Доступ к отчету</title>
|
||
<style>
|
||
body { font-family: Arial, sans-serif; background: #f4f4f4; display: flex; justify-content: center; align-items: center; height: 100vh; }
|
||
.login-form { background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); text-align: center; }
|
||
input[type="password"] { padding: 10px; margin: 10px 0; width: 200px; border: 1px solid #ddd; border-radius: 4px; }
|
||
button { background: #3498db; color: white; border: none; padding: 10px 20px; border-radius: 4px; cursor: pointer; }
|
||
.error { color: #e74c3c; margin-top: 10px; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="login-form">
|
||
<h2>🔒 Доступ к отчету</h2>
|
||
<form method="POST">
|
||
<input type="password" name="password" placeholder="Введите пароль" required>
|
||
<br>
|
||
<button type="submit">Войти</button>
|
||
<?php if (isset($error)): ?>
|
||
<div class="error"><?= $error ?></div>
|
||
<?php endif; ?>
|
||
</form>
|
||
</div>
|
||
</body>
|
||
</html>
|
||
<?php
|
||
exit;
|
||
}
|
||
}
|
||
|
||
// Основной код отчета
|
||
header('Content-Type: text/html; charset=utf-8');
|
||
|
||
// Подключение к базе данных
|
||
try {
|
||
|
||
$pdo = new PDO('mysql:host=87.249.36.139;dbname=argumentiru;charset=utf8mb4', 'argumentiru', 'hjYu78kl*90-Uio23');
|
||
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
} catch (PDOException $e) {
|
||
die('Database connection failed: ' . $e->getMessage());
|
||
}
|
||
|
||
// Обработка параметров периода
|
||
$start_date = $_GET['start_date'] ?? date('Y-m-01');
|
||
$end_date = $_GET['end_date'] ?? date('Y-m-d');
|
||
$adv_id = $_GET['adv_id'] ?? null;
|
||
$space_id = $_GET['space_id'] ?? null;
|
||
|
||
// Валидация дат
|
||
if (!strtotime($start_date) || !strtotime($end_date)) {
|
||
$start_date = date('Y-m-01');
|
||
$end_date = date('Y-m-d');
|
||
}
|
||
|
||
// Получение статистики - ТОЛЬКО баннеры с показами за период
|
||
function getAdvStats($pdo, $start_date, $end_date, $adv_id = null, $space_id = null) {
|
||
$sql = "
|
||
SELECT
|
||
ai.item_id,
|
||
ai.itemname,
|
||
ai.erid,
|
||
ai.adv_link,
|
||
ai.space_id,
|
||
ai.adv_file,
|
||
ai.desktop,
|
||
ai.phone,
|
||
ai.tablet,
|
||
ai.android,
|
||
ai.adv_active,
|
||
COUNT(av.view_id) as views_count,
|
||
COUNT(DISTINCT av.ip_address) as unique_views,
|
||
MIN(av.viewed_at) as first_view,
|
||
MAX(av.viewed_at) as last_view
|
||
FROM adv_views av
|
||
INNER JOIN adv_items2 ai ON av.adv_id = ai.item_id
|
||
WHERE av.viewed_at BETWEEN :start_date AND DATE_ADD(:end_date, INTERVAL 1 DAY)
|
||
";
|
||
|
||
$params = [
|
||
'start_date' => $start_date,
|
||
'end_date' => $end_date
|
||
];
|
||
|
||
// Фильтр по ID баннера
|
||
if ($adv_id) {
|
||
$sql .= " AND ai.item_id = :adv_id";
|
||
$params['adv_id'] = $adv_id;
|
||
}
|
||
|
||
// Фильтр по space_id
|
||
if ($space_id) {
|
||
$sql .= " AND ai.space_id = :space_id";
|
||
$params['space_id'] = $space_id;
|
||
}
|
||
|
||
$sql .= " GROUP BY ai.item_id, ai.itemname, ai.erid
|
||
HAVING views_count > 0
|
||
ORDER BY views_count DESC, ai.itemname ASC";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute($params);
|
||
|
||
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
// Получение списка баннеров для фильтра - ТОЛЬКО те, у которых есть показы за период
|
||
function getAdvList($pdo, $start_date, $end_date) {
|
||
$sql = "
|
||
SELECT DISTINCT
|
||
ai.item_id,
|
||
ai.itemname,
|
||
ai.erid,
|
||
ai.space_id
|
||
FROM adv_views av
|
||
INNER JOIN adv_items2 ai ON av.adv_id = ai.item_id
|
||
WHERE av.viewed_at BETWEEN :start_date AND DATE_ADD(:end_date, INTERVAL 1 DAY)
|
||
ORDER BY ai.itemname
|
||
";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute([
|
||
'start_date' => $start_date,
|
||
'end_date' => $end_date
|
||
]);
|
||
|
||
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
// Получение списка space_id для фильтра - ТОЛЬКО те, у которых есть показы за период
|
||
function getSpaceList($pdo, $start_date, $end_date) {
|
||
$sql = "
|
||
SELECT DISTINCT ai.space_id
|
||
FROM adv_views av
|
||
INNER JOIN adv_items2 ai ON av.adv_id = ai.item_id
|
||
WHERE av.viewed_at BETWEEN :start_date AND DATE_ADD(:end_date, INTERVAL 1 DAY)
|
||
ORDER BY ai.space_id
|
||
";
|
||
|
||
$stmt = $pdo->prepare($sql);
|
||
$stmt->execute([
|
||
'start_date' => $start_date,
|
||
'end_date' => $end_date
|
||
]);
|
||
|
||
return $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
}
|
||
|
||
$stats = getAdvStats($pdo, $start_date, $end_date, $adv_id, $space_id);
|
||
$adv_list = getAdvList($pdo, $start_date, $end_date);
|
||
$space_list = getSpaceList($pdo, $start_date, $end_date);
|
||
|
||
// Общая статистика
|
||
$total_views = array_sum(array_column($stats, 'views_count'));
|
||
$total_unique = array_sum(array_column($stats, 'unique_views'));
|
||
$total_banners = count($stats);
|
||
|
||
// Топ баннеры
|
||
$top_banners = array_slice($stats, 0, 5);
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html lang="ru">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>Отчет по показам баннеров</title>
|
||
<style>
|
||
* { box-sizing: border-box; margin: 0; padding: 0; }
|
||
body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; background: #f4f4f4; padding: 20px; }
|
||
.container { max-width: 1400px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
|
||
h1 { color: #2c3e50; margin-bottom: 20px; border-bottom: 2px solid #3498db; padding-bottom: 10px; }
|
||
.filters { background: #f8f9fa; padding: 20px; border-radius: 6px; margin-bottom: 20px; }
|
||
.form-group { margin-bottom: 15px; }
|
||
label { display: block; margin-bottom: 5px; font-weight: bold; color: #555; }
|
||
input, select, button { padding: 8px 12px; border: 1px solid #ddd; border-radius: 4px; font-size: 14px; }
|
||
button { background: #3498db; color: white; border: none; cursor: pointer; transition: background 0.3s; }
|
||
button:hover { background: #2980b9; }
|
||
.stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin-bottom: 20px; }
|
||
.stat-card { background: white; padding: 15px; border-radius: 6px; box-shadow: 0 1px 3px rgba(0,0,0,0.1); text-align: center; border-left: 4px solid #3498db; }
|
||
.top-banners { background: #fff3cd; padding: 15px; border-radius: 6px; margin-bottom: 20px; border-left: 4px solid #ffc107; }
|
||
.top-banner-item { padding: 5px 0; border-bottom: 1px solid #ffeaa7; }
|
||
.stat-number { font-size: 24px; font-weight: bold; color: #2c3e50; }
|
||
.stat-label { font-size: 14px; color: #7f8c8d; }
|
||
.table-container { overflow-x: auto; }
|
||
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
|
||
th, td { padding: 12px; text-align: left; border-bottom: 1px solid #ddd; }
|
||
th { background: #34495e; color: white; position: sticky; top: 0; }
|
||
tr:hover { background: #f5f5f5; }
|
||
.status-active { color: #27ae60; font-weight: bold; }
|
||
.status-inactive { color: #e74c3c; font-weight: bold; }
|
||
.device-icon { display: inline-block; width: 20px; text-align: center; }
|
||
.no-data { text-align: center; padding: 40px; color: #7f8c8d; font-style: italic; }
|
||
.export-btn { background: #27ae60; margin-left: 10px; }
|
||
.export-btn:hover { background: #219a52; }
|
||
.logout-btn { background: #e74c3c; margin-left: 10px; }
|
||
.logout-btn:hover { background: #c0392b; }
|
||
.filter-row { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; }
|
||
.header-actions { display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; }
|
||
.erid-column { max-width: 150px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="container">
|
||
<div class="header-actions">
|
||
<h1>📊 Отчет по показам баннеров</h1>
|
||
<div>
|
||
<button type="button" class="export-btn" onclick="exportToCSV()">Экспорт в CSV</button>
|
||
<button type="button" class="logout-btn" onclick="logout()">Выйти</button>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Фильтры -->
|
||
<div class="filters">
|
||
<form method="GET" action="">
|
||
<div class="filter-row">
|
||
<div class="form-group">
|
||
<label for="start_date">Дата с:</label>
|
||
<input type="date" id="start_date" name="start_date" value="<?= htmlspecialchars($start_date) ?>" required>
|
||
</div>
|
||
|
||
<div class="form-group">
|
||
<label for="end_date">Дата по:</label>
|
||
<input type="date" id="end_date" name="end_date" value="<?= htmlspecialchars($end_date) ?>" required>
|
||
</div>
|
||
|
||
<div class="form-group">
|
||
<label for="adv_id">Баннер:</label>
|
||
<select id="adv_id" name="adv_id">
|
||
<option value="">Все баннеры</option>
|
||
<?php foreach ($adv_list as $adv): ?>
|
||
<option value="<?= $adv['item_id'] ?>" <?= $adv_id == $adv['item_id'] ? 'selected' : '' ?>>
|
||
<?= htmlspecialchars($adv['item_id'] . ' - ' . $adv['itemname'] . ($adv['erid'] ? ' (' . $adv['erid'] . ')' : '')) ?>
|
||
</option>
|
||
<?php endforeach; ?>
|
||
</select>
|
||
</div>
|
||
|
||
<div class="form-group">
|
||
<label for="space_id">ID пространства:</label>
|
||
<select id="space_id" name="space_id">
|
||
<option value="">Все пространства</option>
|
||
<?php foreach ($space_list as $space): ?>
|
||
<option value="<?= $space['space_id'] ?>" <?= $space_id == $space['space_id'] ? 'selected' : '' ?>>
|
||
<?= $space['space_id'] ?>
|
||
</option>
|
||
<?php endforeach; ?>
|
||
</select>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="form-group" style="margin-top: 15px;">
|
||
<button type="submit">Применить фильтры</button>
|
||
<button type="button" onclick="resetFilters()">Сбросить</button>
|
||
</div>
|
||
</form>
|
||
</div>
|
||
|
||
<!-- Общая статистика -->
|
||
<div class="stats-grid">
|
||
<div class="stat-card">
|
||
<div class="stat-number"><?= number_format($total_banners) ?></div>
|
||
<div class="stat-label">Баннеров с показами</div>
|
||
</div>
|
||
<div class="stat-card">
|
||
<div class="stat-number"><?= number_format($total_views) ?></div>
|
||
<div class="stat-label">Всего показов</div>
|
||
</div>
|
||
<div class="stat-card">
|
||
<div class="stat-number"><?= number_format($total_unique) ?></div>
|
||
<div class="stat-label">Уникальные показы</div>
|
||
</div>
|
||
<div class="stat-card">
|
||
<div class="stat-number">
|
||
<?= $total_banners > 0 ? number_format($total_views / $total_banners, 1) : 0 ?>
|
||
</div>
|
||
<div class="stat-label">Среднее на баннер</div>
|
||
</div>
|
||
</div>
|
||
|
||
<!-- Топ баннеры -->
|
||
<?php if (!empty($top_banners)): ?>
|
||
<div class="top-banners">
|
||
<h3>🏆 Топ-5 баннеров по показам</h3>
|
||
<?php foreach ($top_banners as $index => $banner): ?>
|
||
<div class="top-banner-item">
|
||
<strong>#<?= $index + 1 ?></strong>
|
||
ID <?= $banner['item_id'] ?> - <?= htmlspecialchars($banner['itemname']) ?>
|
||
<?php if ($banner['erid']): ?>
|
||
(<?= htmlspecialchars($banner['erid']) ?>)
|
||
<?php endif; ?>
|
||
<span style="float: right; color: #e74c3c; font-weight: bold;">
|
||
<?= number_format($banner['views_count']) ?> показов
|
||
</span>
|
||
</div>
|
||
<?php endforeach; ?>
|
||
</div>
|
||
<?php endif; ?>
|
||
|
||
<!-- Таблица с данными -->
|
||
<div class="table-container">
|
||
<?php if (!empty($stats)): ?>
|
||
<table>
|
||
<thead>
|
||
<tr>
|
||
<th>ID</th>
|
||
<th>Название</th>
|
||
<th>ERID</th>
|
||
<th>Ссылка</th>
|
||
<th>ID пространства</th>
|
||
<th>Устройства</th>
|
||
<th>Статус</th>
|
||
<th>Показы</th>
|
||
<th>Уникальные</th>
|
||
<th>Первый показ</th>
|
||
<th>Последний показ</th>
|
||
<th>CTR</th>
|
||
</tr>
|
||
</thead>
|
||
<tbody>
|
||
<?php
|
||
$days_diff = max(1, (strtotime($end_date) - strtotime($start_date)) / (60 * 60 * 24) + 1);
|
||
foreach ($stats as $row):
|
||
$avg_per_day = $row['views_count'] / $days_diff;
|
||
$ctr = $row['views_count'] > 0 ? ($row['unique_views'] / $row['views_count'] * 100) : 0;
|
||
?>
|
||
<tr>
|
||
<td><?= $row['item_id'] ?></td>
|
||
<td><strong><?= htmlspecialchars($row['itemname']) ?></strong></td>
|
||
<td class="erid-column"><?= htmlspecialchars($row['erid'] ?: '—') ?></td>
|
||
<td style="max-width: 200px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap;">
|
||
<?= htmlspecialchars($row['adv_link']) ?>
|
||
</td>
|
||
<td><?= $row['space_id'] ?></td>
|
||
<td>
|
||
<?php if ($row['desktop']): ?><span class="device-icon" title="Desktop">🖥️</span><?php endif; ?>
|
||
<?php if ($row['phone']): ?><span class="device-icon" title="Phone">📱</span><?php endif; ?>
|
||
<?php if ($row['tablet']): ?><span class="device-icon" title="Tablet">📟</span><?php endif; ?>
|
||
<?php if ($row['android']): ?><span class="device-icon" title="Android">🤖</span><?php endif; ?>
|
||
</td>
|
||
<td class="<?= $row['adv_active'] ? 'status-active' : 'status-inactive' ?>">
|
||
<?= $row['adv_active'] ? 'Активен' : 'Неактивен' ?>
|
||
</td>
|
||
<td><span style="color: #e74c3c; font-weight: bold;"><?= number_format($row['views_count']) ?></span></td>
|
||
<td><?= number_format($row['unique_views']) ?></td>
|
||
<td><?= $row['first_view'] ? date('d.m.Y H:i', strtotime($row['first_view'])) : '—' ?></td>
|
||
<td><?= $row['last_view'] ? date('d.m.Y H:i', strtotime($row['last_view'])) : '—' ?></td>
|
||
<td><?= number_format($ctr, 1) ?>%</td>
|
||
</tr>
|
||
<?php endforeach; ?>
|
||
</tbody>
|
||
</table>
|
||
<?php else: ?>
|
||
<div class="no-data">
|
||
<h3>📭 Нет данных за выбранный период</h3>
|
||
<p>Попробуйте изменить параметры фильтрации</p>
|
||
</div>
|
||
<?php endif; ?>
|
||
</div>
|
||
</div>
|
||
|
||
<script>
|
||
// Экспорт в CSV
|
||
function exportToCSV() {
|
||
const params = new URLSearchParams(window.location.search);
|
||
params.set('export', 'csv');
|
||
window.location.href = 'report_export.php?' + params.toString();
|
||
}
|
||
|
||
// Сброс фильтров
|
||
function resetFilters() {
|
||
document.getElementById('start_date').value = '<?= date('Y-m-01') ?>';
|
||
document.getElementById('end_date').value = '<?= date('Y-m-d') ?>';
|
||
document.getElementById('adv_id').value = '';
|
||
document.getElementById('space_id').value = '';
|
||
}
|
||
|
||
// Выход из системы
|
||
function logout() {
|
||
if (confirm('Вы уверены, что хотите выйти?')) {
|
||
window.location.href = '?logout=1';
|
||
}
|
||
}
|
||
|
||
// Автоматическое ограничение дат
|
||
document.getElementById('start_date').addEventListener('change', function() {
|
||
const endDate = document.getElementById('end_date');
|
||
if (this.value > endDate.value) {
|
||
endDate.value = this.value;
|
||
}
|
||
});
|
||
|
||
document.getElementById('end_date').addEventListener('change', function() {
|
||
const startDate = document.getElementById('start_date');
|
||
if (this.value < startDate.value) {
|
||
startDate.value = this.value;
|
||
}
|
||
});
|
||
</script>
|
||
</body>
|
||
</html>
|
||
<?php
|
||
// Обработка выхода
|
||
if (isset($_GET['logout'])) {
|
||
session_destroy();
|
||
header('Location: report.php');
|
||
exit;
|
||
}
|
||
?>
|