Merge pull request #28160 from resilient-tech/fix-invoice-statuses
refactor: `update_invoice_status` with query builder
diff --git a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
index 6a488ea..c02c80a 100644
--- a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
@@ -20,6 +20,7 @@
from erpnext.accounts.utils import PaymentEntryUnlinkError
from erpnext.assets.doctype.asset.depreciation import post_depreciation_entries
from erpnext.assets.doctype.asset.test_asset import create_asset, create_asset_data
+from erpnext.controllers.accounts_controller import update_invoice_status
from erpnext.controllers.taxes_and_totals import get_itemised_tax_breakup_data
from erpnext.exceptions import InvalidAccountCurrency, InvalidCurrency
from erpnext.regional.india.utils import get_ewb_data
@@ -2385,6 +2386,41 @@
si.reload()
self.assertEqual(si.status, "Paid")
+ def test_update_invoice_status(self):
+ today = nowdate()
+
+ # Sales Invoice without Payment Schedule
+ si = create_sales_invoice(posting_date=add_days(today, -5))
+
+ # Sales Invoice with Payment Schedule
+ si_with_payment_schedule = create_sales_invoice(do_not_submit=True)
+ si_with_payment_schedule.extend("payment_schedule", [
+ {
+ "due_date": add_days(today, -5),
+ "invoice_portion": 50,
+ "payment_amount": si_with_payment_schedule.grand_total / 2
+ },
+ {
+ "due_date": add_days(today, 5),
+ "invoice_portion": 50,
+ "payment_amount": si_with_payment_schedule.grand_total / 2
+ }
+ ])
+ si_with_payment_schedule.submit()
+
+
+ for invoice in (si, si_with_payment_schedule):
+ invoice.db_set("status", "Unpaid")
+ update_invoice_status()
+ invoice.reload()
+ self.assertEqual(invoice.status, "Overdue")
+
+ invoice.db_set("status", "Unpaid and Discounted")
+ update_invoice_status()
+ invoice.reload()
+ self.assertEqual(invoice.status, "Overdue and Discounted")
+
+
def test_sales_commission(self):
si = frappe.copy_doc(test_records[0])
item = copy.deepcopy(si.get('items')[0])
diff --git a/erpnext/controllers/accounts_controller.py b/erpnext/controllers/accounts_controller.py
index c862774..c5d8f09 100644
--- a/erpnext/controllers/accounts_controller.py
+++ b/erpnext/controllers/accounts_controller.py
@@ -7,6 +7,7 @@
import frappe
from frappe import _, throw
from frappe.model.workflow import get_workflow_name, is_transition_condition_satisfied
+from frappe.query_builder.functions import Sum
from frappe.utils import (
add_days,
add_months,
@@ -1686,58 +1687,69 @@
def update_invoice_status():
"""Updates status as Overdue for applicable invoices. Runs daily."""
today = getdate()
-
+ payment_schedule = frappe.qb.DocType("Payment Schedule")
for doctype in ("Sales Invoice", "Purchase Invoice"):
- frappe.db.sql("""
- UPDATE `tab{doctype}` invoice SET invoice.status = 'Overdue'
- WHERE invoice.docstatus = 1
- AND invoice.status REGEXP '^Unpaid|^Partly Paid'
- AND invoice.outstanding_amount > 0
- AND (
- {or_condition}
- (
- (
- CASE
- WHEN invoice.party_account_currency = invoice.currency
- THEN (
- CASE
- WHEN invoice.disable_rounded_total
- THEN invoice.grand_total
- ELSE invoice.rounded_total
- END
- )
- ELSE (
- CASE
- WHEN invoice.disable_rounded_total
- THEN invoice.base_grand_total
- ELSE invoice.base_rounded_total
- END
- )
- END
- ) - invoice.outstanding_amount
- ) < (
- SELECT SUM(
- CASE
- WHEN invoice.party_account_currency = invoice.currency
- THEN ps.payment_amount
- ELSE ps.base_payment_amount
- END
- )
- FROM `tabPayment Schedule` ps
- WHERE ps.parent = invoice.name
- AND ps.due_date < %(today)s
- )
- )
- """.format(
- doctype=doctype,
- or_condition=(
- "invoice.is_pos AND invoice.due_date < %(today)s OR"
- if doctype == "Sales Invoice"
- else ""
- )
- ), {"today": today}
+ invoice = frappe.qb.DocType(doctype)
+
+ consider_base_amount = invoice.party_account_currency != invoice.currency
+ payment_amount = (
+ frappe.qb.terms.Case()
+ .when(consider_base_amount, payment_schedule.base_payment_amount)
+ .else_(payment_schedule.payment_amount)
)
+ payable_amount = (
+ frappe.qb.from_(payment_schedule)
+ .select(Sum(payment_amount))
+ .where(
+ (payment_schedule.parent == invoice.name)
+ & (payment_schedule.due_date < today)
+ )
+ )
+
+ total = (
+ frappe.qb.terms.Case()
+ .when(invoice.disable_rounded_total, invoice.grand_total)
+ .else_(invoice.rounded_total)
+ )
+
+ base_total = (
+ frappe.qb.terms.Case()
+ .when(invoice.disable_rounded_total, invoice.base_grand_total)
+ .else_(invoice.base_rounded_total)
+ )
+
+ total_amount = (
+ frappe.qb.terms.Case()
+ .when(consider_base_amount, base_total)
+ .else_(total)
+ )
+
+ is_overdue = total_amount - invoice.outstanding_amount < payable_amount
+
+ conditions = (
+ (invoice.docstatus == 1)
+ & (invoice.outstanding_amount > 0)
+ & (
+ invoice.status.like("Unpaid%")
+ | invoice.status.like("Partly Paid%")
+ )
+ & (
+ ((invoice.is_pos & invoice.due_date < today) | is_overdue)
+ if doctype == "Sales Invoice"
+ else is_overdue
+ )
+ )
+
+ status = (
+ frappe.qb.terms.Case()
+ .when(invoice.status.like("%Discounted"), "Overdue and Discounted")
+ .else_("Overdue")
+ )
+
+ frappe.qb.update(invoice).set("status", status).where(conditions).run()
+
+
@frappe.whitelist()
def get_payment_terms(terms_template, posting_date=None, grand_total=None, base_grand_total=None, bill_date=None):
if not terms_template: