Files
exdv/report.php

428 lines
19 KiB
PHP
Raw Permalink Normal View History

2025-10-17 12:23:41 +03:00
<?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;
}
?>