Advanced usage of map & filter in Python

#python #functional

In 2 different job interviews I got asked to solve the following question using python. so I thought it would be good to publish the solution for the question.

You have the following relation:

  erDiagram
    AttendanceActions ||--o{ Attendance : AttendanceId
    AttendanceActions {
        int id
        int AttendanceId
        datetime ActionTime
        text Action
    }
    Attendance {
        int id
        date day
        text employee
    }

and the following tables:

Attendance

id	day	employee
1	2020-04-01	EMP01
2	2020-04-01	EMP02
3	2020-04-02	EMP01
4	2020-04-03	EMP01
5	2020-04-02	EMP02
6	2020-04-03	EMP02

AttendanceActions

id	AttendanceId	ActionTime	Action
1	1	2020-04-01 12:00 AM	CheckIn
2	1	2020-04-01 12:00 PM	CheckOut
3	2	2020-04-01 12:05 PM	CheckIn
4	2	2020-04-01 11:50 PM	CheckOut
5	3	2020-04-02 12:05 AM	CheckIn
6	3	2020-04-02 11:50 AM	CheckOut
7	3	2020-04-02 11:50 PM	CheckIn
8	4	2020-04-03 12:05 PM	CheckOut

Overview

You are given a simple SQLITE3 database for an attendance system with a small set of data.
An employee can perform one of two actions: CheckIn or CheckOut. When an action is performed, it is stored at 2 levels:

An attendance entry is created for the day if no actions were previously performed on that day
An attendance action entry is created recording the time for this specific action and linked to the day’s attendance record

For example, if EMP01 checks in at midnight of 2020-04-01, two records are created:

A record in the Attendance table with date 2020-04-01 and employee EMP01
A record in the AttendanceActions table referencing the previous attendance record with time 2020-04-01 12:00 AM

The time stamps are stored in local server time, which is in the Cairo timezone (UTC + 2). This is not a good practice, but it’s based on a real-world case.

Requirements

01 Check day attendance for employee

Write a function that accepts an employee code (e.g. EMP01) and a date (e.g. 2020-04-01) and reports whether the employee has attended that day and how long.
Examples

get_attendance('emp01', '2020-04-01')

on the sample data should return the following dictionary:

{
    "attended": True,
    "duration": "12:00"
}

The employee attended from 12:00 AM to 12:00 PM, which is exactly 12 hours.
For 2020-04-02, the employee attended from 12:05 AM to 11:50 AM (11:45 night shift), then checked in again for his next shift at 11:50 PM (so 10 minutes before midnight the next day) for a total of 11:55:

{
    "attended": True,
    "duration": "11:55"
}

For 2020-04-03, the employee checked out at 12:05 PM for a duration of 12:05 (12 hours 5 minutes) calculated from midnight since his last check-in was the previous day.

Solution

from datetime import datetime, timedelta
import sqlite3

def _convert_string_datetime(time):
    return datetime.strptime(time, '%Y-%m-%d %I:%M %p')


def get_attendance(employee: str, tdate: str):
    emp = employee
    start_time = datetime.strptime(tdate, '%Y-%m-%d') - timedelta(minutes=15)
    end_time = (start_time + timedelta(days=1, minutes=15))
    query = '''
    SELECT *, et.ActionTime forwardTime FROM
        (SELECT
            ac.id,
            ac.ActionTime,
            ac.Action,
            ac.Attendanceid,
            a.employee,
            (LEAD  (ac.id, 1,0) OVER (ORDER BY ac.id)) forwardID 
        FROM AttendanceActions ac
        INNER JOIN Attendance a ON ac.Attendanceid = a.id
        WHERE a.employee = '{}' ORDER BY ac.id) ee 
    LEFT JOIN AttendanceActions et ON ee.forwardID = et.id
    '''.format(emp)
    with sqlite3.connect('attendance.db') as con:
        cur = con.cursor()
        db_res = cur.execute(query)
        p_res = db_res.fetchall()
        pt_res = list(map(lambda x: x[1] - x[0], filter(
            lambda x: start_time < x[0] < end_time and x[2] == 'CheckIn', map(
                lambda x: ((_convert_string_datetime(x[1]) if x[1] else 0),
                           (_convert_string_datetime(x[8]) if x[8] else 0), x[2]), p_res))))
        if not pt_res:
            return {
                "attended": False,
                "duration": "00:00"
            }
        else:
            return {
                "attended": True,
                "duration": str(pt_res[0])
            }


print(get_attendance('EMP01', '2020-04-01'))
print(get_attendance('EMP01', '2020-04-02'))
print(get_attendance('EMP01', '2020-04-03'))
print(get_attendance('EMP02', '2020-04-01'))
print(get_attendance('EMP02', '2020-04-02'))
print(get_attendance('EMP02', '2020-04-03'))