wX5VuGJy

1import cv2
2import numpy as np
3import os
4import pandas as pd
5from io import BytesIO
6import mysql.connector
7import time
8import io
9from datetime import datetime, timedelta
10from flask import Flask, render_template, request, redirect, url_for, session, Response, send_from_directory, jsonify, send_file
11from db_config import get_db_connection
12from openpyxl import Workbook
13from ultralytics import YOLO
14from tracker import CentroidTracker
15import threading
16
17# Ganti source ke webcam (0) atau RTSP string
18CAMERA_SOURCE = 0  # atau "rtsp://user:pass@ip:554/..."
19SNAPSHOT_DIR = 'static/snapshots'
20os.makedirs(SNAPSHOT_DIR, exist_ok=True)
21app = Flask(__name__)
22app.secret_key = 'your_secret_key'
23
24# Untuk anti double counting
25recent_faces = []
26TIMEOUT_SEC = 30
27DIST_THRESHOLD = 50
28
29# Load model
30age_net = cv2.dnn.readNet('models/age_googlenet.onnx')
31gender_net = cv2.dnn.readNet('models/gender_googlenet.onnx')
32face_model = YOLO("models/best2.pt")
33face_cascade = cv2.CascadeClassifier(cv2.data.haarcascades + "haarcascade_frontalface_default.xml")
34AGE_LIST = ['(0-2)', '(4-6)', '(8-12)', '(15-20)', '(25-32)', '(38-43)', '(48-53)', '(60-100)']
35GENDER_LIST = ['Male', 'Female']
36
37# --- Tracker ---
38tracker = CentroidTracker(max_disappeared=30, dist_threshold=50)
39recent_ids = {}
40RECENT_ID_TIMEOUT = 30  # detik, cache ID agar tidak double insert
41
42def insert_detection_async(*args):
43    # Insert ke DB jalan di thread baru
44    threading.Thread(target=insert_detection, args=args, daemon=True).start()
45
46def insert_detection(name, gender, age_predict, timestamp, snapshot_path):
47    conn = get_db_connection()
48    cursor = conn.cursor()
49    sql = """
50        INSERT INTO detections (name, gender, age_predict, timestamp, snapshots)
51        VALUES (%s, %s, %s, %s, %s)
52    """
53    cursor.execute(sql, (name, gender, age_predict, timestamp, snapshot_path))
54    conn.commit()
55    cursor.close()
56    conn.close()
57
58def gen_frames():
59    cap = cv2.VideoCapture(CAMERA_SOURCE)
60    cap.set(cv2.CAP_PROP_BUFFERSIZE, 1)
61    if not cap.isOpened():
62        print("Gagal membuka kamera/video.")
63        return
64
65    frame_count = 0
66    FRAME_SKIP = 3
67    recent_ids = {}  # object_id: last_saved_time
68
69    while True:
70        ret, frame = cap.read()
71        if not ret:
72            print("Frame tidak terbaca!")
73            break
74        frame_count += 1
75        if frame_count % FRAME_SKIP != 0:
76            continue
77
78        now = datetime.now()
79        ts = now.strftime('%Y-%m-%d %H:%M:%S')
80        now_ts = time.time()
81
82        # YOLO detection
83        results = face_model(frame, verbose=False)
84        boxes = []
85        for r in results:
86            for box, conf, cls in zip(r.boxes.xyxy.cpu().numpy(), r.boxes.conf.cpu().numpy(), r.boxes.cls.cpu().numpy()):
87                if int(cls) == 0 and conf >= 0.45:
88                    x1, y1, x2, y2 = map(int, box)
89                    boxes.append((x1, y1, x2, y2))
90
91        # Tracking!
92        objects = tracker.update(boxes)
93
94        for object_id, centroid in objects.items():
95            # Get corresponding bbox for this ID (find the nearest one)
96            bbox = None
97            min_dist = float("inf")
98            for (x1, y1, x2, y2) in boxes:
99                cx, cy = (x1 + x2) // 2, (y1 + y2) // 2
100                dist = np.linalg.norm(np.array([cx, cy]) - np.array(centroid))
101                if dist < min_dist:
102                    min_dist = dist
103                    bbox = (x1, y1, x2, y2)
104            if bbox is None:
105                continue
106
107            x1, y1, x2, y2 = bbox
108            face_img = frame[y1:y2, x1:x2]
109            if face_img.size == 0 or (x2-x1)<40 or (y2-y1)<40:
110                continue
111
112            # Anti double insert for object_id
113            last_time = recent_ids.get(object_id, 0)
114            if now_ts - last_time > TIMEOUT_SEC:
115                # Predict gender/age
116                blob = cv2.dnn.blobFromImage(face_img, 1.0, (224, 224), (104, 117, 123), swapRB=True)
117                gender_net.setInput(blob)
118                gender_preds = gender_net.forward()
119                gender = GENDER_LIST[gender_preds[0].argmax()]
120                age_net.setInput(blob)
121                age_preds = age_net.forward()
122                age_predict = AGE_LIST[age_preds[0].argmax()]
123                # Save snapshot
124                snap_name = f'snap_{now.strftime("%Y%m%d%H%M%S%f")}.jpg'
125                snap_path = os.path.join(SNAPSHOT_DIR, snap_name)
126                cv2.imwrite(snap_path, face_img)
127                insert_detection_async("Unknown", gender, age_predict, ts, snap_name)
128                recent_ids[object_id] = now_ts
129
130            # Draw box & id
131            cv2.rectangle(frame, (x1, y1), (x2, y2), (0, 255, 0), 2)
132            cv2.putText(frame, f'ID:{object_id}', (x1, y1-20), cv2.FONT_HERSHEY_SIMPLEX, 0.7, (0,255,0), 2)
133
134        # Remove expired IDs from cache
135        for oid in list(recent_ids):
136            if oid not in objects:
137                if now_ts - recent_ids[oid] > TIMEOUT_SEC:
138                    del recent_ids[oid]
139
140        # Streaming to browser
141        ret, buffer = cv2.imencode('.jpg', frame)
142        frame_bytes = buffer.tobytes()
143        yield (b'--frame\r\n'
144               b'Content-Type: image/jpeg\r\n\r\n' + frame_bytes + b'\r\n')
145
146        continue
147
148
149@app.route('/login', methods=['GET', 'POST'])
150def login():
151    if request.method == 'POST':
152        username = request.form['username']
153        password = request.form['password']
154        conn = get_db_connection()
155        cursor = conn.cursor(dictionary=True)
156        cursor.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password))
157        user = cursor.fetchone()
158        cursor.close()
159        conn.close()
160        if user:
161            session['logged_in'] = True
162            session['username'] = username
163            return redirect(url_for('dashboard'))
164        else:
165            return render_template('login.html', error="Invalid credentials")
166    return render_template('login.html')
167
168@app.route('/logout')
169def logout():
170    session.clear()
171    return redirect(url_for('login'))
172
173@app.route('/export-hourly-gender')
174def export_hourly_gender():
175    # --- Dapatkan data yang sama persis dengan halaman hourly-gender ---
176    conn = mysql.connector.connect(
177        host='localhost', user='root', password='', database='bec_counting')
178    cursor = conn.cursor(dictionary=True)
179    cursor.execute("""
180        SELECT 
181            HOUR(timestamp) AS hour,
182            gender,
183            COUNT(*) AS total
184        FROM detections
185        WHERE DATE(timestamp) = CURDATE()
186        GROUP BY hour, gender
187    """)
188    results = cursor.fetchall()
189    conn.close()
190
191    # Siapkan data: urut jam 0-23, selalu ada (meski kosong)
192    hours = {h: {'Male': 0, 'Female': 0} for h in range(24)}
193    for row in results:
194        h = row['hour']
195        g = row['gender']
196        if g in ('Male', 'Female'):
197            hours[h][g] = row['total']
198
199    table = []
200    for h in range(24):
201        label = datetime.strptime(f"{h:02d}:00", "%H:%M").strftime("%I:00 %p")
202        table.append({
203            'hour': label,
204            'male': hours[h]['Male'],
205            'female': hours[h]['Female']
206        })
207
208    # --- Buat file XLSX dengan openpyxl ---
209    output = io.BytesIO()
210    wb = Workbook()
211    ws = wb.active
212    ws.title = "People Counting per Hour"
213    ws.append(["Date/Time", "Male", "Female"])
214    for row in table:
215        ws.append([row['hour'], row['male'], row['female']])
216    # Formatting lebar kolom
217    for col in ws.columns:
218        max_length = 0
219        for cell in col:
220            try:
221                if len(str(cell.value)) > max_length:
222                    max_length = len(str(cell.value))
223            except:
224                pass
225        col_letter = col[0].column_letter
226        ws.column_dimensions[col_letter].width = max_length + 2
227    for cell in ws[1]:
228        cell.font = cell.font.copy(bold=True)
229    wb.save(output)
230    output.seek(0)
231    today = datetime.now().strftime("%Y-%m-%d")
232    return send_file(output,
233        download_name=f"people_counting_hourly_{today}.xlsx",
234        as_attachment=True,
235        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
236
237@app.route('/hourly-gender')
238def hourly_gender():
239    conn = mysql.connector.connect(
240        host='localhost', user='root', password='', database='bec_counting')
241    cursor = conn.cursor(dictionary=True)
242    cursor.execute("""
243        SELECT 
244            HOUR(timestamp) AS hour,
245            gender,
246            COUNT(*) AS total
247        FROM detections
248        WHERE DATE(timestamp) = CURDATE()
249        GROUP BY hour, gender
250        ORDER BY hour DESC
251    """)
252    results = cursor.fetchall()
253    conn.close()
254
255    # Siapkan dict kosong: {hour: {Male: n, Female: m}}
256    hours = {}
257    for row in results:
258        h = row['hour']
259        g = row['gender']
260        if h not in hours:
261            hours[h] = {'Male': 0, 'Female': 0}
262        hours[h][g] = row['total']
263
264    # Konversi ke list untuk tabel, urut dari jam 23 ke 0
265    table = []
266    for h in reversed(range(24)):
267        male = hours.get(h, {}).get('Male', 0)
268        female = hours.get(h, {}).get('Female', 0)
269        table.append({
270            'hour': f"{h:02d}:00",
271            'male': male,
272            'female': female
273        })
274    date_str = datetime.now().strftime('%d %B %Y')
275    return render_template('hourly_gender.html', table=table)
276
277@app.route('/chartdata')
278def chartdata():
279    conn = get_db_connection()
280    cursor = conn.cursor(dictionary=True)
281
282    # Donut: Gender hari ini
283    cursor.execute("""
284        SELECT gender, COUNT(*) as count
285        FROM detections
286        WHERE DATE(timestamp) = CURDATE()
287        GROUP BY gender
288    """)
289    donut = cursor.fetchall()
290
291    # Bar: Per jam hari ini (Male)
292    cursor.execute("""
293        SELECT HOUR(timestamp) AS hour, COUNT(*) as count
294        FROM detections
295        WHERE DATE(timestamp) = CURDATE() AND gender='Male'
296        GROUP BY hour
297    """)
298    hourly_male = cursor.fetchall()
299
300    # Bar: Per jam hari ini (Female)
301    cursor.execute("""
302        SELECT HOUR(timestamp) AS hour, COUNT(*) as count
303        FROM detections
304        WHERE DATE(timestamp) = CURDATE() AND gender='Female'
305        GROUP BY hour
306    """)
307    hourly_female = cursor.fetchall()
308
309    # Bar: Per hari (mingguan, 7 hari terakhir)
310    cursor.execute("""
311        SELECT DATE(timestamp) AS date,
312        SUM(gender='Male') as male,
313        SUM(gender='Female') as female
314        FROM detections
315        WHERE timestamp >= CURDATE() - INTERVAL 6 DAY
316        GROUP BY date
317        ORDER BY date
318    """)
319    weekly = cursor.fetchall()
320
321    # Bar: Per hari di bulan berjalan
322    cursor.execute("""
323        SELECT DAY(timestamp) AS day,
324        SUM(gender='Male') as male,
325        SUM(gender='Female') as female
326        FROM detections
327        WHERE MONTH(timestamp) = MONTH(CURDATE()) AND YEAR(timestamp) = YEAR(CURDATE())
328        GROUP BY day
329        ORDER BY day
330    """)
331    monthly = cursor.fetchall()
332
333    cursor.close()
334    conn.close()
335    return jsonify({
336        "donut": donut,
337        "hourly_male": hourly_male,
338        "hourly_female": hourly_female,
339        "weekly": weekly,
340        "monthly": monthly
341    })
342
343@app.route('/')
344def dashboard():
345    if not session.get('logged_in'):
346        return redirect(url_for('login'))
347    conn = get_db_connection()
348    cursor = conn.cursor(dictionary=True)
349    cursor.execute("SELECT COUNT(*) as total FROM detections")
350    total = cursor.fetchone()['total']
351    cursor.execute("SELECT gender, COUNT(*) as count FROM detections GROUP BY gender")
352    gender_stats = cursor.fetchall()
353    cursor.execute("SELECT age_predict, COUNT(*) as count FROM detections GROUP BY age_predict")
354    age_stats = cursor.fetchall()
355    cursor.close()
356    conn.close()
357    return render_template('dashboard.html', total=total, gender_stats=gender_stats, age_stats=age_stats)
358
359@app.route('/stats')
360def stats():
361    conn = get_db_connection()
362    cursor = conn.cursor(dictionary=True)
363    cursor.execute("SELECT COUNT(*) as total FROM detections")
364    total = cursor.fetchone()['total']
365    cursor.execute("SELECT gender, COUNT(*) as count FROM detections GROUP BY gender")
366    gender_stats = cursor.fetchall()
367    cursor.execute("SELECT age_predict, COUNT(*) as count FROM detections GROUP BY age_predict")
368    age_stats = cursor.fetchall()
369    cursor.close()
370    conn.close()
371    return jsonify({
372        "total": total,
373        "gender": gender_stats,
374        "age": age_stats
375    })
376
377@app.route('/logs')
378def logs():
379    if not session.get('logged_in'):
380        return redirect(url_for('login'))
381    conn = get_db_connection()
382    cursor = conn.cursor(dictionary=True)
383    cursor.execute("SELECT * FROM detections ORDER BY timestamp DESC")
384    detections = cursor.fetchall()
385    cursor.close()
386    conn.close()
387    return render_template('logs.html', detections=detections)
388
389@app.route('/video_feed')
390def video_feed():
391    return Response(gen_frames(), mimetype='multipart/x-mixed-replace; boundary=frame')
392
393@app.route('/static/snapshots/<filename>')
394def snapshot(filename):
395    return send_from_directory(SNAPSHOT_DIR, filename)
396
397@app.route("/export/<string:mode>")
398def export_xlsx(mode):
399    conn = get_db_connection()
400    cursor = conn.cursor(dictionary=True)
401
402    today = datetime.now().strftime('%Y-%m-%d')
403
404    if mode == "today":
405        cursor.execute("""
406            SELECT gender, COUNT(*) as count
407            FROM detections
408            WHERE DATE(timestamp) = CURDATE()
409            GROUP BY gender
410        """)
411        rows = cursor.fetchall()
412        df = pd.DataFrame(rows)
413        df = df.rename(columns={"gender": "Gender", "count": "Total"})
414
415    elif mode == "hourly":
416        cursor.execute("""
417            SELECT HOUR(timestamp) as hour, gender, COUNT(*) as count
418            FROM detections
419            WHERE DATE(timestamp) = CURDATE()
420            GROUP BY hour, gender
421            ORDER BY hour
422        """)
423        # Format tabel: jam, Male, Female
424        results = cursor.fetchall()
425        data = []
426        for h in range(24):
427            row_m = next((r for r in results if r["hour"] == h and r["gender"] == "Male"), None)
428            row_f = next((r for r in results if r["hour"] == h and r["gender"] == "Female"), None)
429            data.append({
430                "Hour": f"{h:02d}:00",
431                "Male": row_m["count"] if row_m else 0,
432                "Female": row_f["count"] if row_f else 0
433            })
434        df = pd.DataFrame(data)
435
436    elif mode == "weekly":
437        cursor.execute("""
438            SELECT DATE(timestamp) AS date,
439            SUM(gender='Male') as Male,
440            SUM(gender='Female') as Female
441            FROM detections
442            WHERE timestamp >= CURDATE() - INTERVAL 6 DAY
443            GROUP BY date
444            ORDER BY date
445        """)
446        rows = cursor.fetchall()
447        df = pd.DataFrame(rows)
448        df = df.rename(columns={"date": "Date"})
449
450    elif mode == "monthly":
451        cursor.execute("""
452            SELECT WEEK(timestamp, 1) AS week, 
453            SUM(gender='Male') as Male,
454            SUM(gender='Female') as Female
455            FROM detections
456            WHERE YEAR(timestamp) = YEAR(CURDATE())
457                AND MONTH(timestamp) = MONTH(CURDATE())
458            GROUP BY week
459            ORDER BY week
460        """)
461        rows = cursor.fetchall()
462        df = pd.DataFrame(rows)
463        df = df.rename(columns={"week": "Week"})
464
465    else:
466        cursor.close()
467        conn.close()
468        return "Invalid mode", 400
469
470    cursor.close()
471    conn.close()
472
473    # Konversi DataFrame ke XLSX (in-memory)
474    output = BytesIO()
475    df.to_excel(output, index=False)
476    output.seek(0)
477    return send_file(
478        output,
479        download_name=f"people_counting_{mode}_{today}.xlsx",
480        as_attachment=True,
481        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
482    )
483
484@app.route("/export/monthly")
485def export_xlsx_monthly():
486    conn = get_db_connection()
487    cursor = conn.cursor(dictionary=True)
488    # Hitung jumlah total Male & Female dalam bulan berjalan
489    cursor.execute("""
490        SELECT 
491            DATE_FORMAT(timestamp, '%M %Y') AS Month,
492            SUM(gender='Male') as Male,
493            SUM(gender='Female') as Female
494        FROM detections
495        WHERE YEAR(timestamp) = YEAR(CURDATE())
496          AND MONTH(timestamp) = MONTH(CURDATE())
497    """)
498    row = cursor.fetchone()  # Hanya satu baris (total bulan ini)
499
500    df = pd.DataFrame([row])
501    output = BytesIO()
502    df.to_excel(output, index=False)
503    output.seek(0)
504    cursor.close()
505    conn.close()
506    today = datetime.now().strftime('%Y-%m-%d')
507    return send_file(
508        output,
509        download_name=f"people_counting_monthly_{today}.xlsx",
510        as_attachment=True,
511        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
512    )
513
514if __name__ == '__main__':
515    app.run(debug=True, threaded=True)
516