How to make the system realize that the RFID card is read for 2nd time for sign out?

Question

I am a beginner and I need some help/guidance! So basically I am trying to make an attendance system using Raspberry Pi 4 and RFID reader and writer. So far, everything is working well but I am struggling with sending data for sign_out. For now, the same timestamp that is in sign_in get marked in signed_out in the database also, but here I would like to make that when the RFID card is read for the 2nd time the data get filled in sign_out column. If you have any other suggestion I will happy to hear/learn, thanks. EDIT: "the whole code was deleted from this question"

Answer 1

Have you considered treating sign-in/sign-out as a switch? The default state is sign-in set to 0 or false, sign-out set to 1 or true. Then, when the card is read, it checks the value of sign-in. If it's 0/false it gets flipped to 1/true, and does the opposite flip to sign-out. When the card is read the second time, it reads sign-out and if 0/false, it sets it to 1/true, and does the opposite to sign-in.

For clarity:

current state: 
    sign-in = 0
    sign-out = 1

---> card is read for the first time
sign-in = (sign-in + 1) % 2 
sign-out = (sign-out + 1) %2

state now:
    sign-in = 1
    sign-out = 0

---> card is read for the second time, consider previous state
sign-in = (sign-i + 1) % 2
sign-out = (sign-out + 1) % 2

state:
    sign-in = 0
    sign-out = 1

This could all be simplified further by reducing the 2 states to a single one, signed-in.

initial state:
    signed-in = 0

---> card is read for entry
signed-in = (signed-in + 1) % 2

state:
    signed-in = 1

---> card is read for exit
signed-out = (signed-in + 1) % 2

state:
    signed-in = 0

Simply put, you want to consider this as a simple state machine with two transitions: from In to Out and from Out to In

Does this help point you in the right direction?

Timestamp question

So, it seems you initialize the timestamp once, before entering the loop. Try moving

ts = time.time()
timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d     %H:%M:%S'

after the following line:

id, text = reader.read()

Does that solve your problem?

General style comments

You could simplify the following:

if sign_in == 0:
    sign_in = (sign_in + 1) % 2
    sign_out = (sign_out + 1) % 2
    #id, text = reader.read()
    cursor.execute("INSERT INTO attendance (user_id, clock_in) VALUES (%s, %s)", (result[0], timestamp,) )
    lcd.lcd_display_string("Sign in " + result[1])

  elif sign_in == 1:
    sign_out = (sign_out + 1) % 2
    sign_in = (sign_in + 1) % 2
    #id, text = reader.read()
    cursor.execute("INSERT INTO attendance (user_id, clock_out) VALUES (%s, %s)", (result[0], timestamp,) )

    lcd.lcd_display_string("Sign out " + result[1])

to something more like

sign_in = (sign_in + 1) % 2
sign_out = (sign_out + 1) % 2
#id, text = reader.read()
field_in_or_out = 'in' if sign_in == 1 else 'out'
cursor.execute(f"INSERT INTO attendance (user_id, clock_{field_in_or_out}) VALUES (%s, %s)", (result[0], timestamp,) )
lcd.lcd_display_string(f"Sign {field_in_or_out} " + result[1])

How I would simplify the logic further

#!/usr/bin/env python
import time
import datetime
import RPi.GPIO as GPIO
from mfrc522 import SimpleMFRC522
import mysql.connector
import I2C_LCD_driver

db = mysql.connector.connect(
    host="localhost",
    user="admin",
    passwd="*******",
    database="attendancesystem"
)

cursor = db.cursor()
reader = SimpleMFRC522()
lcd = I2C_LCD_driver.lcd()
#redLED = 4
#yellowLED = 17
#greenLED = 27
#GPIO.setmode(GPIO.BCM)
signed_in = 0
try:
    while True:
        lcd.lcd_clear()
        lcd.lcd_display_string('Place Card to')
        lcd.lcd_display_string('record attendance', 2)
        id, text = reader.read()

        ts = time.time()
        timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d     %H:%M:%S')

        cursor.execute("Select id, name FROM users WHERE rfid_uid="+str(id))
        result = cursor.fetchone()

        lcd.lcd_clear()

        if cursor.rowcount >= 1:
            lcd.lcd_display_string("Welcome")
            lcd.lcd_display_string(""+ result[1], 2)
            #GPIO.output(greenLED,GPIO.HIGH)
            #time.sleep(3)
            #GPIO.output(greenLED,GPIO.LOW)
            cursor.execute("INSERT INTO attendance (user_id) VALUES (%s)", (result[0],) )

            signed_in = (signed_in + 1) % 2
            #id, text = reader.read()
            cursor.execute(f"INSERT INTO attendance (user_id, read_at) VALUES (%s, %s)", (result[0], timestamp,) )
            lcd.lcd_display_string(f"Card read " + result[1])
            db.commit()

        else:
            lcd.lcd_display_string("User does not")
            lcd.lcd_display_string("exist !!", 2)
            #GPIO.output(yellowLED,GPIO.HIGH)
            #time.sleep(3)
            #GPIO.output(yellowLED,GPIO.LOW)
        time.sleep(2)
finally:
    GPIO.cleanup()

And update the database schema to only have a user_id and a read_at time column for when the card was read. Change your sign_in/sign_out logic to only have a signed_in field that expresses both states, 1 for when the user is signed in, 0 for when it's not.

The other change in the code is pulling the timestamp logic into the while loop.

Then, your expected database will have a line for each time the card was touched to the reader, with the time of the event, and you won't have to update an already existing entry.