Hi All,
i need Script report of Attedance with salary report for checking the amount of salary to cross-verify before creating payroll i need report like below image
def execute(filters=None):
columns, data = ,
# Define columns
columns = [
{"label": "Employee", "fieldname": "employee", "fieldtype": "Link", "options": "Employee", "width": 120},
{"label": "Employee Name", "fieldname": "employee_name", "fieldtype": "Data", "width": 200},
{"label": "Total Present", "fieldname": "total_present", "fieldtype": "Int", "width": 80},
{"label": "Total Absent", "fieldname": "total_absent", "fieldtype": "Int", "width": 80},
{"label": "Total Holidays", "fieldname": "total_holidays", "fieldtype": "Int", "width": 80},
{"label": "Total Late Entries", "fieldname": "total_late_entries", "fieldtype": "Int", "width": 100},
{"label": "Total Early Exits", "fieldname": "total_early_exits", "fieldtype": "Int", "width": 100},
{"label": "Total No of Days", "fieldname": "total_days", "fieldtype": "Int", "width": 100},
{"label": "Net Payable Salary", "fieldname": "net_payable_salary", "fieldtype": "Currency", "width": 150},
{"label": "Advance Paid", "fieldname": "advance_paid", "fieldtype": "Currency", "width": 120},
{"label": "Net Payable After Advance", "fieldname": "net_payable_after_advance", "fieldtype": "Currency", "width": 180}
]
# Default date range for testing (August 2024)
filters = {
"from_date": "2024-08-01",
"to_date": "2024-08-31"
}
# Query attendance data
attendance_records = frappe.db.sql("""
SELECT
att.employee,
emp.employee_name,
SUM(CASE WHEN att.status = 'Present' THEN 1 ELSE 0 END) AS total_present,
SUM(CASE WHEN att.status = 'Absent' THEN 1 ELSE 0 END) AS total_absent,
SUM(CASE WHEN att.holiday = 1 THEN 1 ELSE 0 END) AS total_holidays,
SUM(att.late_entry) AS total_late_entries,
SUM(att.early_exit) AS total_early_exits
FROM
`tabAttendance` att
JOIN
`tabEmployee` emp ON att.employee = emp.name
WHERE
att.attendance_date BETWEEN %(from_date)s AND %(to_date)s
GROUP BY
att.employee
ORDER BY
att.employee ASC
""", filters, as_dict=1)
# Ensure attendance records are found
if not attendance_records:
frappe.msgprint("No attendance records found for the selected period.")
return columns, data
# Calculate totals and handle salary
for record in attendance_records:
total_days = 31 # Assuming August has 31 days
record["total_days"] = total_days
# Fetch salary details and advance payments
salary_structure = frappe.get_value("Salary Structure Assignment", {"employee": record.employee}, "base")
advance_paid = frappe.db.sql("""
SELECT SUM(advance_paid)
FROM `tabSalary Advance`
WHERE employee = %s AND docstatus = 1
""", record.employee)[0][0] or 0
record["net_payable_salary"] = salary_structure
record["advance_paid"] = advance_paid
record["net_payable_after_advance"] = flt(salary_structure) - flt(advance_paid)
data.append(record)
return columns, data
i tried this code but not workjing can anyone please help me out!
Thanks in Advance