187 lines
4.4 KiB
Markdown
187 lines
4.4 KiB
Markdown
# Penjelasan Behavior Upsert di Hourly Summary
|
||
|
||
## Jawaban Singkat
|
||
|
||
**Data DIGANTI (REPLACE), bukan ditambah.**
|
||
|
||
## Detail Penjelasan
|
||
|
||
### Query Upsert yang Digunakan
|
||
|
||
```sql
|
||
INSERT INTO hourly_summary
|
||
(summary_date, summary_hour, location_code, gate_code, category, total_count, total_amount)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON DUPLICATE KEY UPDATE
|
||
total_count = VALUES(total_count),
|
||
total_amount = VALUES(total_amount)
|
||
```
|
||
|
||
### Primary Key
|
||
|
||
```sql
|
||
PRIMARY KEY (summary_date, summary_hour, location_code, gate_code, category)
|
||
```
|
||
|
||
### Behavior: ON DUPLICATE KEY UPDATE
|
||
|
||
**Jika row BELUM ADA:**
|
||
- ✅ **INSERT** → Tambah row baru
|
||
|
||
**Jika row SUDAH ADA:**
|
||
- ✅ **UPDATE** → Ganti `total_count` dan `total_amount` dengan nilai baru
|
||
- ❌ **BUKAN** tambah ke nilai lama
|
||
|
||
## Contoh Skenario
|
||
|
||
### Skenario 1: Cron Pertama Kali (Row Belum Ada)
|
||
|
||
**Jam 2:00, cron jalan untuk update jam 1:00:**
|
||
|
||
```
|
||
1. Query entry_events untuk jam 1:00:
|
||
- location_code: kerkof_01
|
||
- gate_code: gate01
|
||
- category: motor
|
||
- COUNT: 10 events
|
||
- total_amount: 10 × 2000 = 20000
|
||
|
||
2. INSERT ke hourly_summary:
|
||
✅ Row baru dibuat:
|
||
summary_date: 2025-12-17
|
||
summary_hour: 1
|
||
location_code: kerkof_01
|
||
gate_code: gate01
|
||
category: motor
|
||
total_count: 10
|
||
total_amount: 20000
|
||
```
|
||
|
||
### Skenario 2: Cron Kedua Kali (Row Sudah Ada)
|
||
|
||
**Jam 2:30, ada event baru masuk (terlambat):**
|
||
- Event jam 1:00 masuk di jam 2:30 (terlambat)
|
||
|
||
**Jam 3:00, cron jalan lagi untuk update jam 1:00:**
|
||
|
||
```
|
||
1. Query entry_events untuk jam 1:00 (SEKARANG):
|
||
- location_code: kerkof_01
|
||
- gate_code: gate01
|
||
- category: motor
|
||
- COUNT: 11 events (termasuk yang terlambat)
|
||
- total_amount: 11 × 2000 = 22000
|
||
|
||
2. ON DUPLICATE KEY UPDATE:
|
||
✅ Row LAMA diganti:
|
||
- total_count: 10 → 11 (DIGANTI, bukan 10 + 11 = 21)
|
||
- total_amount: 20000 → 22000 (DIGANTI, bukan 20000 + 22000 = 42000)
|
||
```
|
||
|
||
## Mengapa Diganti, Bukan Ditambah?
|
||
|
||
### 1. **Akurasi Data**
|
||
- Summary harus selalu mencerminkan data aktual di `entry_events`
|
||
- Jika ditambah, data akan double-count jika cron jalan berulang
|
||
|
||
### 2. **Idempotent**
|
||
- Cron bisa jalan berulang tanpa merusak data
|
||
- Hasil selalu sama, tidak peduli berapa kali dijalankan
|
||
|
||
### 3. **Event Terlambat**
|
||
- Jika ada event yang masuk terlambat, akan otomatis terhitung saat rekap ulang
|
||
- Tidak perlu manual correction
|
||
|
||
## Contoh Timeline Lengkap
|
||
|
||
### Hari: 2025-12-17
|
||
|
||
**Jam 1:00 - 1:59:**
|
||
```
|
||
Event masuk ke entry_events:
|
||
- 1:05 → motor (kerkof_01, gate01)
|
||
- 1:15 → motor (kerkof_01, gate01)
|
||
- 1:30 → motor (kerkof_01, gate01)
|
||
Total: 3 events
|
||
```
|
||
|
||
**Jam 2:00 - Cron jalan:**
|
||
```
|
||
1. Query entry_events untuk jam 1:00:
|
||
→ COUNT: 3 events
|
||
→ total_amount: 3 × 2000 = 6000
|
||
|
||
2. INSERT ke hourly_summary:
|
||
✅ Row baru: total_count = 3, total_amount = 6000
|
||
```
|
||
|
||
**Jam 2:30 - Event terlambat masuk:**
|
||
```
|
||
Event masuk ke entry_events (terlambat):
|
||
- 2:30 → motor (kerkof_01, gate01) dengan event_time = 1:45
|
||
Total sekarang: 4 events untuk jam 1:00
|
||
```
|
||
|
||
**Jam 3:00 - Cron jalan lagi (opsional, jika rekap ulang):**
|
||
```
|
||
1. Query entry_events untuk jam 1:00 (SEKARANG):
|
||
→ COUNT: 4 events (termasuk yang terlambat)
|
||
→ total_amount: 4 × 2000 = 8000
|
||
|
||
2. ON DUPLICATE KEY UPDATE:
|
||
✅ Row LAMA diganti:
|
||
- total_count: 3 → 4 (DIGANTI)
|
||
- total_amount: 6000 → 8000 (DIGANTI)
|
||
```
|
||
|
||
## Verifikasi di Database
|
||
|
||
### Cek Data Sebelum Cron:
|
||
|
||
```sql
|
||
SELECT * FROM hourly_summary
|
||
WHERE summary_date = '2025-12-17'
|
||
AND summary_hour = 1
|
||
AND location_code = 'kerkof_01'
|
||
AND gate_code = 'gate01'
|
||
AND category = 'motor';
|
||
|
||
-- Hasil:
|
||
-- total_count: 3
|
||
-- total_amount: 6000
|
||
```
|
||
|
||
### Jalankan Cron:
|
||
|
||
```bash
|
||
php bin/hourly_summary.php today 1
|
||
```
|
||
|
||
### Cek Data Setelah Cron:
|
||
|
||
```sql
|
||
SELECT * FROM hourly_summary
|
||
WHERE summary_date = '2025-12-17'
|
||
AND summary_hour = 1
|
||
AND location_code = 'kerkof_01'
|
||
AND gate_code = 'gate01'
|
||
AND category = 'motor';
|
||
|
||
-- Hasil (jika ada event baru):
|
||
-- total_count: 4 (DIGANTI dari 3, bukan 3 + 4 = 7)
|
||
-- total_amount: 8000 (DIGANTI dari 6000, bukan 6000 + 8000 = 14000)
|
||
```
|
||
|
||
## Kesimpulan
|
||
|
||
| Aspek | Behavior |
|
||
|-------|----------|
|
||
| **Jika row belum ada** | ✅ INSERT (tambah baru) |
|
||
| **Jika row sudah ada** | ✅ UPDATE (ganti nilai) |
|
||
| **Apakah ditambah?** | ❌ TIDAK, diganti |
|
||
| **Apakah diganti?** | ✅ YA, diganti dengan nilai baru |
|
||
| **Nilai lama** | ❌ Tidak dipertahankan, diganti dengan hasil rekap ulang |
|
||
|
||
**Intinya:** Data selalu di-rekap ulang dari `entry_events`, bukan ditambah ke nilai lama. Ini memastikan akurasi dan konsistensi data summary.
|
||
|