A small project to get a digital punch clock where the face serves as fist

timetrack.py

from PIL import Image
import face_recognition

import time
import datetime
import sqlite3
from sqlite3 import Error
import os
import logging
import subprocess

timestamp = datetime.datetime.now()
date = timestamp.strftime('%d-%m-%Y')

webcam_file = os.path.join(os.path.dirname(os.path.realpath(__file__)), "webcam.jpg")
#~ save_file = os.path.join(os.path.dirname(os.path.realpath(__file__)), "sf_")

log_file = os.path.join(os.path.dirname(os.path.realpath(__file__)), "timetrack.log")
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger()
handler = logging.FileHandler(log_file)
handler.setLevel(logging.DEBUG)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)

db_file = os.path.join(os.path.dirname(os.path.realpath(__file__)), "timetrack.db")
sql_create_table = """ CREATE TABLE IF NOT EXISTS entries (
                   id integer PRIMARY KEY,
                   date text NOT NULL,
                   timestamp text,
                   comment text); """
                   
try:
    conn = sqlite3.connect(db_file)
    try:
        c = conn.cursor()
        c.execute(sql_create_table)
    except Error as e:
        print(e)
except Error as e:
    logger.debug(e)
finally:
    conn.close()                   

subprocess.call("fswebcam -q -r 640x480 --jpeg 85 {0}".format(webcam_file), shell=True, stderr=subprocess.STDOUT)

# Load the jpg file into a numpy array
image = face_recognition.load_image_file(webcam_file)

# Find all the faces in the image using a pre-trained convolutional neural network.
# This method is more accurate than the default HOG model, but it's slower
# unless you have an nvidia GPU and dlib compiled with CUDA extensions. But if you do,
# this will use GPU acceleration and perform well.
# See also: find_faces_in_picture.py
face_locations = face_recognition.face_locations(image, number_of_times_to_upsample=0, model="cnn")

#print("I found {} face(s) in this photograph.".format(len(face_locations)))

for face_location in face_locations:

    #~ # Print the location of each face in this image
    #~ top, right, bottom, left = face_location
    #~ # print("A face is located at pixel location Top: {}, Left: {}, Bottom: {}, Right: {}".format(top, left, bottom, right))

    #~ # You can access the actual face itself like this:
    #~ face_image = image[top:bottom, left:right]
    #~ pil_image = Image.fromarray(face_image)
    #~ pil_image.save(str(save_file) + str(int(time.time())) + ".jpg")
    
    comment = "user present"
    
    logger.info('DB INSERT: user present')
    data = [date, timestamp, comment]
    sql_new_entry = """INSERT INTO entries(date, timestamp, comment)
                       VALUES (?,?,?); """
    conn = sqlite3.connect(db_file)
    try:
        c = conn.cursor()
        c.execute(sql_new_entry, data)
        conn.commit()
    except Error as e:
        logger.debug(e)
    finally:
        conn.close()
   
if len(face_locations) == 0:
    comment = "empty"
    
    logger.info('DB INSERT: place is empty')
    data = [date, timestamp, comment]
    sql_new_entry = """INSERT INTO entries(date, timestamp, comment)
                       VALUES (?,?,?); """
    conn = sqlite3.connect(db_file)
    try:
        c = conn.cursor()
        c.execute(sql_new_entry, data)
        conn.commit()
    except Error as e:
        logger.debug(e)
    finally:
        conn.close()    

timetrack_export.sh

sqlite3 -header -csv timetrack.db < timetrack_export.sql > out.csv

timetrack_export.sql

with xxx as (
select date as break_date, count(comment), min(time(datetime(timestamp))) as "break_in", max(time(datetime(timestamp))) as "break_out" from entries
where comment like 'empty'
and (time(datetime(timestamp)) >= '11:50:00' and time(datetime(timestamp)) <= '13:00:00')
group by date
)
select  date,round(substr(min(strftime('%H:%M',datetime(timestamp))),1,2)  + (CAST(substr(min(strftime('%H:%M',datetime(timestamp))),4,2) as float)/60),1) as "in", 
round(substr(xxx.break_in,1,2) + (CAST(substr(xxx.break_in,4,2) as float)/60),1) as break_in,
round(substr(xxx.break_out,1,2) + (CAST(substr(xxx.break_out,4,2) as float)/60),1) as break_out,
round(substr(max(strftime('%H:%M',datetime(timestamp))),1,2)  + (CAST(substr(max(strftime('%H:%M',datetime(timestamp))),4,2) as float)/60),1) as "out"
from entries
left join xxx
on xxx.break_date = date
where comment like 'user present'
group by date
order by datetime(substr(date, 7,4) || '-' || substr(date, 4, 2)|| '-' || substr(date, 1, 2)); --order by date