Calculate sandwich absent before and after weekly off

Frappe HR
1

I want to calculate sandwich absent here in my report but I don’t know how to calculate it with the correct fields:

import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    return columns, data

def get_columns():
    return [
        {"label": _("Employee ID"), "fieldname": "employee", "fieldtype": "Link", "options": "Employee", "width": 100},
        {"label": _("Employee Name"), "fieldname": "employee_name", "fieldtype": "Data", "width": 150},
        {"label": _("Total Absents"), "fieldname": "total_absents", "fieldtype": "Int", "width": 100},
        {"label": _("Sandwich Absents"), "fieldname": "sandwich_absents", "fieldtype": "Int", "width": 100},
    ]

def get_data(filters=None):
    conditions = ""
    if filters:
        if filters.get("employee"):
            conditions += " AND a.employee = %(employee)s"

    return frappe.db.sql(f"""
        SELECT
            a.employee,
            a.employee_name,
            COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) AS total_absents,
            COUNT(
            ((CASE WHEN a.status = 'Absent' AND attendance_date) > hl.weekly_off ) 
            AND
            ((CASE WHEN a.status = 'Absent' AND attendance_date) > hl.weekly_off ) AS sandwich_absents
        FROM
            `tabAttendance` a
        JOIN
            `tabEmployee` e ON e.name = a.employee  
        JOIN
            `tabHoliday List` hl ON hl.name = e.holiday_list
        WHERE
            a.docstatus = 1 
            {conditions}
        GROUP BY
            a.employee
    """, filters, as_dict=1)
2

A sandwich absent is when an employee is absent on the day before and after of weekly_off day. For example weekly_off is on Sun and an employee is absent on both Fri and Sat

3 
COUNT(CASE WHEN 
            (a.status = 'Absent') AND 
            (DATE_ADD(a.attendance_date, INTERVAL 1 DAY) IN (select h.holiday_date from `tabHoliday` as h where h.weekly_off = 1)) AND 
            (DATE_ADD(a.attendance_date, INTERVAL 2 DAY) IN (select a.attendance_date from `tabAttendance` a where a.status='Absent'))  THEN 1 END) AS sandwich_leave

This is a limited case based on your example. This checks for a day the employee was absent and checks the next 2 days for

  1. If the subsequent day was wekly off
  2. If the day after the weekly off , the employee was absent

You can modify the query to include holidays other than weekly off. But if you want to query case where there are multiple weekly offs, then that would be different.

1 Like
4

Thanks @rahib this is very helpful

5

Through some logic changes I was able to make my case where an employee was absent before and after the weekly_off, here you can see the dates when the employee was absent:

image
image1289×579 66.1 KB

Now there are two Sandwich Absents here according to my case, so my report also shows that:

image
image705×331 15.1 KB

1 Like
6

Here is the code If anyone wants to use the same logic for sandwich absents:

import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    return columns, data

def get_columns():
    return [
        {"label": _("Employee ID"), "fieldname": "id", "fieldtype": "Link", "options": "Employee", "width": 150},
        {"label": _("Employee Name"), "fieldname": "name", "fieldtype": "Data", "width": 150},
        {"label": _("Total Absents"), "fieldname": "total_absents", "fieldtype": "Data", "width": 150},
        {"label": _("Sandwich Absents"), "fieldname": "sandwich_absents", "fieldtype": "Data", "width": 150},
    ]

def get_data(filters=None):
    return frappe.db.sql(f"""
    SELECT
        a.employee AS id,
        a.employee_name AS name,
        COUNT(CASE WHEN a.status = 'Absent' THEN 1 END) AS total_absents,
        COUNT(CASE WHEN a.status = 'Absent' 
            AND 
            EXISTS (
                SELECT 1 
                FROM `tabHoliday` h 
                JOIN `tabHoliday List` hl ON h.parent = hl.name
                WHERE hl.name = e.holiday_list
                AND h.weekly_off = 1 
                AND DATE_ADD(a.attendance_date, INTERVAL 1 DAY) = h.holiday_date
                AND EXISTS (
                    SELECT 1 
                    FROM `tabAttendance` a2 
                    WHERE a2.employee = a.employee
                    AND a2.attendance_date = DATE_ADD(h.holiday_date, INTERVAL 1 DAY)
                    AND a2.status = 'Absent'
                )
            )
        THEN 1 END) AS sandwich_absents
    FROM
        `tabEmployee` e
    LEFT JOIN
        `tabAttendance` a ON a.employee = e.name
    WHERE
        a.attendance_date IS NOT NULL
    GROUP BY
        a.employee, a.employee_name
    """, filters, as_dict=1)
1 Like