328 lines
10 KiB
PHP
328 lines
10 KiB
PHP
|
#!/usr/bin/env php
|
||
|
|
<?php
|
||
|
|
|
||
|
|
declare(strict_types=1);
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Debug script untuk cek kenapa total_amount tidak terhitung di hourly summary
|
||
|
|
*
|
||
|
|
* Usage:
|
||
|
|
* php bin/debug_hourly_amount.php [date] [hour]
|
||
|
|
*
|
||
|
|
* Examples:
|
||
|
|
* php bin/debug_hourly_amount.php today 14
|
||
|
|
* php bin/debug_hourly_amount.php 2025-12-17 13
|
||
|
|
*/
|
||
|
|
|
||
|
|
// Get project root directory
|
||
|
|
$rootPath = dirname(__DIR__);
|
||
|
|
|
||
|
|
// Load autoloader
|
||
|
|
require $rootPath . '/vendor/autoload.php';
|
||
|
|
|
||
|
|
use App\Config\AppConfig;
|
||
|
|
use App\Support\Database;
|
||
|
|
|
||
|
|
// Load environment variables
|
||
|
|
AppConfig::loadEnv($rootPath);
|
||
|
|
|
||
|
|
// Get date and hour from command line
|
||
|
|
$dateInput = $argv[1] ?? 'today';
|
||
|
|
$hourInput = isset($argv[2]) ? (int) $argv[2] : null;
|
||
|
|
|
||
|
|
// Handle special keywords
|
||
|
|
if ($dateInput === 'today') {
|
||
|
|
$date = date('Y-m-d');
|
||
|
|
} elseif ($dateInput === 'yesterday') {
|
||
|
|
$date = date('Y-m-d', strtotime('-1 day'));
|
||
|
|
} else {
|
||
|
|
$date = $dateInput;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Validate date format
|
||
|
|
$dateTime = DateTime::createFromFormat('Y-m-d', $date);
|
||
|
|
if ($dateTime === false || $dateTime->format('Y-m-d') !== $date) {
|
||
|
|
echo "Error: Invalid date format. Expected Y-m-d (e.g., 2025-01-01) or 'today'/'yesterday'\n";
|
||
|
|
exit(1);
|
||
|
|
}
|
||
|
|
|
||
|
|
try {
|
||
|
|
// Get database connection
|
||
|
|
$dbHost = AppConfig::get('DB_HOST', 'localhost');
|
||
|
|
$dbName = AppConfig::get('DB_NAME', '');
|
||
|
|
$dbUser = AppConfig::get('DB_USER', '');
|
||
|
|
$dbPass = AppConfig::get('DB_PASS', '');
|
||
|
|
|
||
|
|
if (empty($dbName) || empty($dbUser)) {
|
||
|
|
echo "Error: Database configuration not found in .env\n";
|
||
|
|
exit(1);
|
||
|
|
}
|
||
|
|
|
||
|
|
$db = Database::getConnection($dbHost, $dbName, $dbUser, $dbPass);
|
||
|
|
|
||
|
|
echo "=== Debug Hourly Amount Calculation ===\n\n";
|
||
|
|
echo "Date: {$date}\n";
|
||
|
|
if ($hourInput !== null) {
|
||
|
|
echo "Hour: {$hourInput}\n";
|
||
|
|
} else {
|
||
|
|
echo "Hour: All hours\n";
|
||
|
|
}
|
||
|
|
echo "\n";
|
||
|
|
|
||
|
|
// 1. Cek entry_events untuk tanggal/jam tersebut
|
||
|
|
$eventsSql = "
|
||
|
|
SELECT
|
||
|
|
DATE(e.event_time) as event_date,
|
||
|
|
HOUR(e.event_time) as event_hour,
|
||
|
|
e.location_code,
|
||
|
|
e.gate_code,
|
||
|
|
e.category,
|
||
|
|
COUNT(*) as event_count
|
||
|
|
FROM entry_events e
|
||
|
|
WHERE DATE(e.event_time) = ?
|
||
|
|
";
|
||
|
|
|
||
|
|
$eventsParams = [$date];
|
||
|
|
if ($hourInput !== null) {
|
||
|
|
$eventsSql .= " AND HOUR(e.event_time) = ?";
|
||
|
|
$eventsParams[] = $hourInput;
|
||
|
|
}
|
||
|
|
|
||
|
|
$eventsSql .= " GROUP BY DATE(e.event_time), HOUR(e.event_time), e.location_code, e.gate_code, e.category";
|
||
|
|
|
||
|
|
$stmt = $db->prepare($eventsSql);
|
||
|
|
$stmt->execute($eventsParams);
|
||
|
|
$events = $stmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
if (empty($events)) {
|
||
|
|
echo "❌ Tidak ada event untuk tanggal/jam tersebut\n";
|
||
|
|
exit(0);
|
||
|
|
}
|
||
|
|
|
||
|
|
echo "1. Entry Events Found:\n";
|
||
|
|
echo str_repeat("-", 80) . "\n";
|
||
|
|
foreach ($events as $event) {
|
||
|
|
echo sprintf(
|
||
|
|
" Date: %s, Hour: %02d, Location: %s, Gate: %s, Category: %s, Count: %d\n",
|
||
|
|
$event['event_date'],
|
||
|
|
$event['event_hour'],
|
||
|
|
$event['location_code'],
|
||
|
|
$event['gate_code'],
|
||
|
|
$event['category'],
|
||
|
|
$event['event_count']
|
||
|
|
);
|
||
|
|
}
|
||
|
|
echo "\n";
|
||
|
|
|
||
|
|
// 2. Cek apakah tariff ada untuk setiap kombinasi
|
||
|
|
echo "2. Tariff Check:\n";
|
||
|
|
echo str_repeat("-", 80) . "\n";
|
||
|
|
$missingTariffs = [];
|
||
|
|
$foundTariffs = [];
|
||
|
|
|
||
|
|
foreach ($events as $event) {
|
||
|
|
$tariffSql = "
|
||
|
|
SELECT
|
||
|
|
location_code,
|
||
|
|
gate_code,
|
||
|
|
category,
|
||
|
|
price
|
||
|
|
FROM tariffs
|
||
|
|
WHERE location_code = ?
|
||
|
|
AND gate_code = ?
|
||
|
|
AND category = ?
|
||
|
|
";
|
||
|
|
|
||
|
|
$tariffStmt = $db->prepare($tariffSql);
|
||
|
|
$tariffStmt->execute([
|
||
|
|
$event['location_code'],
|
||
|
|
$event['gate_code'],
|
||
|
|
$event['category']
|
||
|
|
]);
|
||
|
|
$tariff = $tariffStmt->fetch(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
$key = sprintf("%s|%s|%s", $event['location_code'], $event['gate_code'], $event['category']);
|
||
|
|
|
||
|
|
if ($tariff === false || empty($tariff)) {
|
||
|
|
$missingTariffs[] = [
|
||
|
|
'location_code' => $event['location_code'],
|
||
|
|
'gate_code' => $event['gate_code'],
|
||
|
|
'category' => $event['category'],
|
||
|
|
'event_count' => $event['event_count']
|
||
|
|
];
|
||
|
|
echo sprintf(
|
||
|
|
" ❌ TARIF TIDAK DITEMUKAN: Location: %s, Gate: %s, Category: %s\n",
|
||
|
|
$event['location_code'],
|
||
|
|
$event['gate_code'],
|
||
|
|
$event['category']
|
||
|
|
);
|
||
|
|
} else {
|
||
|
|
$foundTariffs[$key] = $tariff;
|
||
|
|
$expectedAmount = $event['event_count'] * (int) $tariff['price'];
|
||
|
|
echo sprintf(
|
||
|
|
" ✅ Tarif ditemukan: Location: %s, Gate: %s, Category: %s, Price: %s, Expected Amount: %s\n",
|
||
|
|
$tariff['location_code'],
|
||
|
|
$tariff['gate_code'],
|
||
|
|
$tariff['category'],
|
||
|
|
number_format($tariff['price'], 0, ',', '.'),
|
||
|
|
number_format($expectedAmount, 0, ',', '.')
|
||
|
|
);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
echo "\n";
|
||
|
|
|
||
|
|
// 3. Cek data di hourly_summary
|
||
|
|
echo "3. Hourly Summary Data:\n";
|
||
|
|
echo str_repeat("-", 80) . "\n";
|
||
|
|
$summarySql = "
|
||
|
|
SELECT
|
||
|
|
summary_date,
|
||
|
|
summary_hour,
|
||
|
|
location_code,
|
||
|
|
gate_code,
|
||
|
|
category,
|
||
|
|
total_count,
|
||
|
|
total_amount
|
||
|
|
FROM hourly_summary
|
||
|
|
WHERE summary_date = ?
|
||
|
|
";
|
||
|
|
|
||
|
|
$summaryParams = [$date];
|
||
|
|
if ($hourInput !== null) {
|
||
|
|
$summarySql .= " AND summary_hour = ?";
|
||
|
|
$summaryParams[] = $hourInput;
|
||
|
|
}
|
||
|
|
|
||
|
|
$summaryStmt = $db->prepare($summarySql);
|
||
|
|
$summaryStmt->execute($summaryParams);
|
||
|
|
$summaries = $summaryStmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
if (empty($summaries)) {
|
||
|
|
echo " ⚠️ Belum ada data di hourly_summary untuk tanggal/jam tersebut\n";
|
||
|
|
} else {
|
||
|
|
foreach ($summaries as $summary) {
|
||
|
|
$key = sprintf("%s|%s|%s", $summary['location_code'], $summary['gate_code'], $summary['category']);
|
||
|
|
$tariff = $foundTariffs[$key] ?? null;
|
||
|
|
$expectedAmount = $tariff ? $summary['total_count'] * (int) $tariff['price'] : 0;
|
||
|
|
|
||
|
|
$status = ($summary['total_amount'] == $expectedAmount) ? '✅' : '❌';
|
||
|
|
echo sprintf(
|
||
|
|
" %s Date: %s, Hour: %02d, Location: %s, Gate: %s, Category: %s\n",
|
||
|
|
$status,
|
||
|
|
$summary['summary_date'],
|
||
|
|
$summary['summary_hour'],
|
||
|
|
$summary['location_code'],
|
||
|
|
$summary['gate_code'],
|
||
|
|
$summary['category']
|
||
|
|
);
|
||
|
|
echo sprintf(
|
||
|
|
" Count: %d, Amount: %s (Expected: %s)\n",
|
||
|
|
$summary['total_count'],
|
||
|
|
number_format($summary['total_amount'], 0, ',', '.'),
|
||
|
|
number_format($expectedAmount, 0, ',', '.')
|
||
|
|
);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
echo "\n";
|
||
|
|
|
||
|
|
// 4. Summary dan rekomendasi
|
||
|
|
echo "4. Summary:\n";
|
||
|
|
echo str_repeat("-", 80) . "\n";
|
||
|
|
|
||
|
|
if (!empty($missingTariffs)) {
|
||
|
|
echo "❌ MASALAH DITEMUKAN:\n";
|
||
|
|
echo " Ada " . count($missingTariffs) . " kombinasi yang TIDAK punya tarif:\n";
|
||
|
|
foreach ($missingTariffs as $missing) {
|
||
|
|
echo sprintf(
|
||
|
|
" - Location: %s, Gate: %s, Category: %s (Event count: %d)\n",
|
||
|
|
$missing['location_code'],
|
||
|
|
$missing['gate_code'],
|
||
|
|
$missing['category'],
|
||
|
|
$missing['event_count']
|
||
|
|
);
|
||
|
|
}
|
||
|
|
echo "\n";
|
||
|
|
echo "💡 SOLUSI:\n";
|
||
|
|
echo " 1. Tambahkan tarif untuk kombinasi yang missing di tabel tariffs\n";
|
||
|
|
echo " 2. Atau pastikan location_code, gate_code, category match dengan entry_events\n";
|
||
|
|
echo " 3. Setelah tambah tarif, jalankan lagi:\n";
|
||
|
|
echo " php bin/hourly_summary.php {$date} " . ($hourInput ?? '') . "\n";
|
||
|
|
} else {
|
||
|
|
echo "✅ Semua kombinasi punya tarif\n";
|
||
|
|
echo "✅ Jika total_amount masih 0, kemungkinan:\n";
|
||
|
|
echo " 1. Tarif price = 0 (cek di tabel tariffs)\n";
|
||
|
|
echo " 2. Data belum di-rekap (jalankan hourly_summary.php)\n";
|
||
|
|
}
|
||
|
|
|
||
|
|
echo "\n";
|
||
|
|
|
||
|
|
// 5. Test query aggregation
|
||
|
|
echo "5. Test Aggregation Query:\n";
|
||
|
|
echo str_repeat("-", 80) . "\n";
|
||
|
|
$testSql = "
|
||
|
|
SELECT
|
||
|
|
DATE(e.event_time) as summary_date,
|
||
|
|
HOUR(e.event_time) as summary_hour,
|
||
|
|
e.location_code,
|
||
|
|
e.gate_code,
|
||
|
|
e.category,
|
||
|
|
COUNT(*) as total_count,
|
||
|
|
COALESCE(t.price, 0) as tariff_amount,
|
||
|
|
COUNT(*) * COALESCE(t.price, 0) as calculated_total_amount
|
||
|
|
FROM entry_events e
|
||
|
|
INNER JOIN locations l ON e.location_code = l.code AND l.is_active = 1
|
||
|
|
INNER JOIN gates g ON e.location_code = g.location_code
|
||
|
|
AND e.gate_code = g.gate_code
|
||
|
|
AND g.is_active = 1
|
||
|
|
LEFT JOIN tariffs t ON e.location_code = t.location_code
|
||
|
|
AND e.gate_code = t.gate_code
|
||
|
|
AND e.category = t.category
|
||
|
|
WHERE DATE(e.event_time) = ?
|
||
|
|
";
|
||
|
|
|
||
|
|
$testParams = [$date];
|
||
|
|
if ($hourInput !== null) {
|
||
|
|
$testSql .= " AND HOUR(e.event_time) = ?";
|
||
|
|
$testParams[] = $hourInput;
|
||
|
|
}
|
||
|
|
|
||
|
|
$testSql .= "
|
||
|
|
GROUP BY
|
||
|
|
DATE(e.event_time),
|
||
|
|
HOUR(e.event_time),
|
||
|
|
e.location_code,
|
||
|
|
e.gate_code,
|
||
|
|
e.category,
|
||
|
|
COALESCE(t.price, 0)
|
||
|
|
";
|
||
|
|
|
||
|
|
$testStmt = $db->prepare($testSql);
|
||
|
|
$testStmt->execute($testParams);
|
||
|
|
$testResults = $testStmt->fetchAll(PDO::FETCH_ASSOC);
|
||
|
|
|
||
|
|
foreach ($testResults as $result) {
|
||
|
|
$tariffStatus = ($result['tariff_amount'] > 0) ? '✅' : '❌';
|
||
|
|
echo sprintf(
|
||
|
|
" %s Location: %s, Gate: %s, Category: %s\n",
|
||
|
|
$tariffStatus,
|
||
|
|
$result['location_code'],
|
||
|
|
$result['gate_code'],
|
||
|
|
$result['category']
|
||
|
|
);
|
||
|
|
echo sprintf(
|
||
|
|
" Count: %d, Tariff: %s, Calculated Amount: %s\n",
|
||
|
|
$result['total_count'],
|
||
|
|
number_format($result['tariff_amount'], 0, ',', '.'),
|
||
|
|
number_format($result['calculated_total_amount'], 0, ',', '.')
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
echo "\n=== Debug Complete ===\n";
|
||
|
|
|
||
|
|
} catch (Exception $e) {
|
||
|
|
echo "Error: " . $e->getMessage() . "\n";
|
||
|
|
echo $e->getTraceAsString() . "\n";
|
||
|
|
exit(1);
|
||
|
|
}
|
||
|
|
|