Advanced usage of map & filter in Python
Tags: python, functional
Published: 2023-07-27 16:44:32


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:
```mermaid
  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**
```python
get_attendance('emp01', '2020-04-01')
```
on the sample data should return the following dictionary:
```python
{
    "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:
```python
{
    "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
```python
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'))
```