Patch for total quantity (#14458)
* Add path to patches.txt
* Modify patch
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 658b7e7..eb29528 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -546,4 +546,5 @@
erpnext.patches.v10_0.update_status_in_purchase_receipt
erpnext.patches.v11_0.inter_state_field_for_gst
erpnext.patches.v11_0.rename_members_with_naming_series #04-06-2018
-erpnext.patches.v11_0.set_update_field_and_value_in_workflow_state
\ No newline at end of file
+erpnext.patches.v11_0.set_update_field_and_value_in_workflow_state
+erpnext.patches.v11_0.update_total_qty_field
diff --git a/erpnext/patches/v11_0/update_total_qty_field.py b/erpnext/patches/v11_0/update_total_qty_field.py
index e618593..6f78d8a 100644
--- a/erpnext/patches/v11_0/update_total_qty_field.py
+++ b/erpnext/patches/v11_0/update_total_qty_field.py
@@ -9,19 +9,26 @@
frappe.reload_doc('stock', 'doctype', 'purchase_receipt')
frappe.reload_doc('accounts', 'doctype', 'sales_invoice')
frappe.reload_doc('accounts', 'doctype', 'purchase_invoice')
-
+
doctypes = ["Sales Order", "Sales Invoice", "Delivery Note",\
"Purchase Order", "Purchase Invoice", "Purchase Receipt", "Quotation", "Supplier Quotation"]
for doctype in doctypes:
+ total_qty = frappe.db.sql('''
+ SELECT
+ parent, SUM(qty) as qty
+ FROM
+ `tab%s Item`
+ GROUP BY parent
+ ''' % (doctype), as_dict = True)
+
+ when_then = []
+ for d in total_qty:
+ when_then.append("""
+ when dt.name = '{0}' then {1}
+ """.format(frappe.db.escape(d.get("parent")), d.get("qty")))
+
frappe.db.sql('''
UPDATE
- `tab%s` dt SET dt.total_qty =
- (
- SELECT SUM(dt_item.qty)
- FROM
- `tab%s Item` dt_item
- WHERE
- dt_item.parent=dt.name
- )
- ''' % (doctype, doctype))
+ `tab%s` dt SET dt.total_qty = CASE %s END
+ ''' % (doctype, " ".join(when_then)))
\ No newline at end of file