refactor: convert raw sql to query_builder
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index eed74a5..5c9b0dd 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -705,72 +705,87 @@
frappe.get_doc("Journal Entry", doc[0]).cancel()
-def unlink_ref_doc_from_payment_entries(ref_doc):
- remove_ref_doc_link_from_jv(ref_doc.doctype, ref_doc.name)
- remove_ref_doc_link_from_pe(ref_doc.doctype, ref_doc.name)
+def update_accounting_ledgers_after_reference_removal(ref_type: str = None, ref_no: str = None):
+ # General Ledger
+ gle = qb.DocType("GL Entry")
+ qb.update(gle).set(gle.against_voucher_type, None).set(gle.against_voucher, None).set(
+ gle.modified, now()
+ ).set(gle.modified_by, frappe.session.user).where(
+ (gle.against_voucher_type == ref_type) & (gle.against_voucher == ref_no)
+ ).run()
- frappe.db.sql(
- """update `tabGL Entry`
- set against_voucher_type=null, against_voucher=null,
- modified=%s, modified_by=%s
- where against_voucher_type=%s and against_voucher=%s
- and voucher_no != ifnull(against_voucher, '')""",
- (now(), frappe.session.user, ref_doc.doctype, ref_doc.name),
- )
-
+ # Payment Ledger
ple = qb.DocType("Payment Ledger Entry")
-
qb.update(ple).set(ple.against_voucher_type, ple.voucher_type).set(
ple.against_voucher_no, ple.voucher_no
).set(ple.modified, now()).set(ple.modified_by, frappe.session.user).where(
- (ple.against_voucher_type == ref_doc.doctype)
- & (ple.against_voucher_no == ref_doc.name)
- & (ple.delinked == 0)
+ (ple.against_voucher_type == ref_type) & (ple.against_voucher_no == ref_no) & (ple.delinked == 0)
).run()
+
+def remove_ref_from_advance_section(ref_doc: object = None):
if ref_doc.doctype in ("Sales Invoice", "Purchase Invoice"):
ref_doc.set("advances", [])
+ adv_type = qb.DocType(f"{ref_doc.doctype} Advance")
+ qb.from_(adv_type).delete().where(adv_type.parent == ref_doc.name).run()
- frappe.db.sql(
- """delete from `tab{0} Advance` where parent = %s""".format(ref_doc.doctype), ref_doc.name
- )
+
+def unlink_ref_doc_from_payment_entries(ref_doc):
+ remove_ref_doc_link_from_jv(ref_doc.doctype, ref_doc.name)
+ remove_ref_doc_link_from_pe(ref_doc.doctype, ref_doc.name)
+ update_accounting_ledgers_after_reference_removal(ref_doc.doctype, ref_doc.name)
def remove_ref_doc_link_from_jv(ref_type, ref_no):
- linked_jv = frappe.db.sql_list(
- """select parent from `tabJournal Entry Account`
- where reference_type=%s and reference_name=%s and docstatus < 2""",
- (ref_type, ref_no),
+ jea = qb.DocType("Journal Entry Account")
+
+ linked_jv = (
+ qb.from_(jea)
+ .select(jea.parent)
+ .select(
+ (jea.reference_type == ref_type) & (jea.reference_name == ref_no) & (jea.docstatus.lt(2))
+ )
+ .run(as_list=1)
)
+ linked_jv = convert_to_list(linked_jv)
if linked_jv:
- frappe.db.sql(
- """update `tabJournal Entry Account`
- set reference_type=null, reference_name = null,
- modified=%s, modified_by=%s
- where reference_type=%s and reference_name=%s
- and docstatus < 2""",
- (now(), frappe.session.user, ref_type, ref_no),
- )
+ qb.update(jea).set(jea.reference_type, None).set(jea.reference_name, None).set(
+ jea.modified, now()
+ ).set(jea.modified_by, frappe.session.user).where(
+ (jea.reference_type == ref_type) & (jea.reference_name == ref_no)
+ ).run()
frappe.msgprint(_("Journal Entries {0} are un-linked").format("\n".join(linked_jv)))
+def convert_to_list(result):
+ """
+ Convert tuple to list
+ """
+ return [x[0] for x in result]
+
+
def remove_ref_doc_link_from_pe(ref_type, ref_no):
- linked_pe = frappe.db.sql_list(
- """select parent from `tabPayment Entry Reference`
- where reference_doctype=%s and reference_name=%s and docstatus < 2""",
- (ref_type, ref_no),
+ per = qb.DocType("Payment Entry Reference")
+ pay = qb.DocType("Payment Entry")
+
+ linked_pe = (
+ qb.from_(per)
+ .select(per.parent)
+ .where(
+ (per.reference_doctype == ref_type) & (per.reference_name == ref_no) & (per.docstatus.lt(2))
+ )
+ .run(as_list=1)
)
+ linked_pe = convert_to_list(linked_pe)
if linked_pe:
- frappe.db.sql(
- """update `tabPayment Entry Reference`
- set allocated_amount=0, modified=%s, modified_by=%s
- where reference_doctype=%s and reference_name=%s
- and docstatus < 2""",
- (now(), frappe.session.user, ref_type, ref_no),
- )
+ qb.update(per).set(per.allocated_amount, 0).set(per.modified, now()).set(
+ per.modified_by, frappe.session.user
+ ).where(
+ (per.docstatus.lt(2) & (per.reference_doctype == ref_type) & (per.reference_name == ref_no))
+ ).run()
for pe in linked_pe:
try:
@@ -785,19 +800,13 @@
msg += _("Please cancel payment entry manually first")
frappe.throw(msg, exc=PaymentEntryUnlinkError, title=_("Payment Unlink Error"))
- frappe.db.sql(
- """update `tabPayment Entry` set total_allocated_amount=%s,
- base_total_allocated_amount=%s, unallocated_amount=%s, modified=%s, modified_by=%s
- where name=%s""",
- (
- pe_doc.total_allocated_amount,
- pe_doc.base_total_allocated_amount,
- pe_doc.unallocated_amount,
- now(),
- frappe.session.user,
- pe,
- ),
- )
+ qb.update(pay).set(pay.total_allocated_amount, pe_doc.total_allocated_amount).set(
+ pay.base_total_allocated_amount, pe_doc.base_total_allocated_amount
+ ).set(pay.unallocated_amount, pe_doc.unallocated_amount).set(pay.modified, now()).set(
+ pay.modified_by, frappe.session.user
+ ).where(
+ pay.name == pe
+ ).run()
frappe.msgprint(_("Payment Entries {0} are un-linked").format("\n".join(linked_pe)))