I need weekly attendance report based on working hours

ERPNext Reports
,
1

Hi Community,
I need a Weekly Attendance report based on working hours , for example: Employee A how much total hours worked for a week for that i need a report.
Thanks in Advance

2

Weekly Total working hours report for employees
Thanks in Advance

3

For this, you have to create a custom script report.

4 
def get_data(filters):
    from_date = filters.get("from_date")
    to_date = filters.get("to_date")
    employee = filters.get("employee")  # Get the employee filter

    # Build the SQL query dynamically based on the employee filter
    conditions = "attendance_date BETWEEN %(from_date)s AND %(to_date)s"
    if employee:
        conditions += " AND employee = %(employee)s"

    data = frappe.db.sql(f"""
        SELECT 
            employee, 
            employee_name, 
            WEEK(attendance_date) AS week_number,
            SUM(working_hours) AS total_working_hours
        FROM 
            `tabAttendance`
        WHERE 
            {conditions}
        GROUP BY 
            employee, WEEK(attendance_date)
        HAVING total_working_hours > 0
        ORDER BY 
            employee, week_number
        """, 
        {"from_date": from_date, "to_date": to_date, "employee": employee}, as_dict=1)

    return data

i tried this code but not working can u please help me out for the solution
Thanks in Advance

5

I tried this query, and it’s working for me.

SELECT 
    employee, 
    employee_name, 
    WEEK(attendance_date) AS week_number,
    SUM(working_hours) AS total_working_hours
FROM 
    `tabAttendance`
WHERE 
    attendance_date BETWEEN '2024-01-01' AND '2024-10-01'
    AND docstatus = 1
GROUP BY 
    employee, WEEK(attendance_date)
HAVING 
    total_working_hours > 0
ORDER BY 
    employee, week_number;

image
image1688×1320 58.7 KB

1 Like
6

Please try it.

def get_data(filters):
    from_date = filters.get("from_date")
    to_date = filters.get("to_date")
    employee = filters.get("employee")

    conditions = "attendance_date BETWEEN %(from_date)s AND %(to_date)s AND docstatus = 1"
    params = {"from_date": from_date, "to_date": to_date}

    if employee:
        conditions += " AND employee = %(employee)s"
        params["employee"] = employee

    data = frappe.db.sql(f"""
        SELECT 
            employee, 
            employee_name, 
            WEEK(attendance_date) AS week_number,
            SUM(working_hours) AS total_working_hours
        FROM 
            `tabAttendance`
        WHERE 
            {conditions}
        GROUP BY 
            employee, WEEK(attendance_date)
        HAVING 
            total_working_hours > 0
        ORDER BY 
            employee, week_number
        """, params, as_dict=1)

    return data
2 Likes