Fix Timesheet Billing Status

system_console.py

# When a Sales Invoice is submitted, amended and cancelled multiple times, Timesheets might end up in the "Submitted" status
# while they are in fact billed.
# 
# This script iterates over submitted, unbilled Timesheets. If the Timesheet is linked to a paid Sales Invoice, it
# will be marked as fully billed.
# 
# This script can be run in the ERPNext System Console. First, make a dry run with the "Commit" checkbox deactivated. This
# will show all affected timesheets. Then activate "Commit", if you want to persist the fixes.

log("Fixed Timesheets:")

for ts in frappe.get_all(
    "Timesheet",
    filters={"total_billable_hours": (">", 0), "docstatus": 1, "total_billed_hours": 0},
    fields=["name", "total_billable_hours", "total_billable_amount"]
):
    if frappe.get_list(
        "Sales Invoice",
        filters=[
            ["status", "=", "Paid"],
            ["docstatus", "=", 1],
            ["Sales Invoice Timesheet", "time_sheet", "=", ts.name]
        ]
    ):
        log(ts.name)
        frappe.db.set_value("Timesheet", ts.name, "total_billed_hours", ts.total_billable_hours)
        frappe.db.set_value("Timesheet", ts.name, "total_billed_amount", ts.total_billable_amount)
        frappe.db.set_value("Timesheet", ts.name, "status", "Billed")
        frappe.db.set_value("Timesheet", ts.name, "per_billed", 100)