&from=YYYY-MM-DD&to=YYYY-MM-DD
* Доступ: админ — любой кабинет; клиент — только свои.
*/
declare(strict_types=1);
session_start();
function db_main(): PDO {
static $pdo = null;
if ($pdo === null) {
$pdo = new PDO('sqlite:' . __DIR__ . '/data.db', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 30]);
}
return $pdo;
}
function db_users(): PDO {
static $pdo = null;
if ($pdo === null) {
$pdo = new PDO('sqlite:' . __DIR__ . '/users.db', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 30]);
}
return $pdo;
}
$me = $_SESSION['user'] ?? null;
if (!$me) die('Доступ запрещён');
$is_admin = $me['role'] === 'admin';
$cab = (int)($_GET['cab'] ?? 0);
$from = $_GET['from'] ?? '';
$to = $_GET['to'] ?? '';
if ($cab <= 0 || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $from) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $to) || $from > $to) {
die('Неверные параметры');
}
$db = db_main();
// права: админ — любой, клиент — только свои кабинеты
if (!$is_admin) {
$mine = $me['client_ids'] ?? [];
if (!in_array($cab, $mine)) die('Доступ запрещён');
}
$client = $db->prepare('SELECT name, note FROM clients WHERE client_id = ?');
$client->execute([$cab]);
$cl = $client->fetch(PDO::FETCH_ASSOC);
if (!$cl) die('Кабинет не найден');
$rows = $db->prepare("SELECT date, SUM(cost) AS cost, SUM(leads) AS leads
FROM daily_stats
WHERE campaign_id IN (SELECT campaign_id FROM campaigns WHERE client_id = ?)
AND date BETWEEN ? AND ?
GROUP BY date ORDER BY date");
$rows->execute([$cab, $from, $to]);
$rows = $rows->fetchAll(PDO::FETCH_ASSOC);
$tcost = 0; $tleads = 0;
foreach ($rows as $r) { $tcost += (float)$r['cost']; $tleads += (int)$r['leads']; }
/* ── сборка .xlsx ─────────────────────────────────────── */
function xlsx_escape(string $s): string {
return htmlspecialchars($s, ENT_XML1 | ENT_QUOTES, 'UTF-8');
}
$header = ''
. '' . xlsx_escape($cl['name'] . ' (' . $cl['note'] . ') · ' . date('d.m.Y', strtotime($from)) . ' — ' . date('d.m.Y', strtotime($to))) . ''
. '
';
$head = ''
. 'Дата'
. 'Расход'
. 'Заявки'
. 'CPL
';
$total = ''
. 'ИТОГО'
. '' . round($tcost, 2) . ''
. '' . (int)$tleads . ''
. '' . ($tleads > 0 ? round($tcost / $tleads, 2) : 0) . '
';
$body = '';
$r = 4;
foreach (array_reverse($rows) as $row) {
$cost = (float)$row['cost'];
$leads = (int)$row['leads'];
$body .= ''
. '' . xlsx_escape(date('d.m.Y', strtotime($row['date']))) . ''
. '' . round($cost, 2) . ''
. '' . $leads . ''
. '' . ($leads > 0 ? round($cost / $leads, 2) : 0) . '
';
$r++;
}
$sheet_xml = ''
. ''
. ''
. ''
. ''
. '' . $header . $head . $total . $body . ''
. '';
$styles_xml = ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. ''
. '';
$workbook_xml = ''
. ''
. ''
. '';
$content_types = ''
. ''
. ''
. ''
. ''
. ''
. ''
. '';
$rels = ''
. ''
. ''
. '';
$wb_rels = ''
. ''
. ''
. ''
. '';
$zip = new ZipArchive();
$tmp = tempnam(sys_get_temp_dir(), 'xlsx');
if ($zip->open($tmp, ZipArchive::OVERWRITE) !== true) die('Ошибка создания файла');
$zip->addFromString('[Content_Types].xml', $content_types);
$zip->addFromString('_rels/.rels', $rels);
$zip->addFromString('xl/workbook.xml', $workbook_xml);
$zip->addFromString('xl/_rels/workbook.xml.rels', $wb_rels);
$zip->addFromString('xl/worksheets/sheet1.xml', $sheet_xml);
$zip->addFromString('xl/styles.xml', $styles_xml);
$zip->close();
$fname = 'stats_' . $cab . '_' . $from . '_' . $to . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $fname . '"');
header('Content-Length: ' . filesize($tmp));
readfile($tmp);
unlink($tmp);