Merge pull request #7138 from rohitwaghchaure/so_po_status_issue
[Fix] Status of sales order and purchase order
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 30e8e03..b578ac2 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -349,4 +349,5 @@
erpnext.patches.v7_1.update_invoice_status
erpnext.patches.v7_0.po_status_issue_for_pr_return
erpnext.patches.v7_1.update_missing_salary_component_type
-erpnext.patches.v7_0.update_autoname_field
\ No newline at end of file
+erpnext.patches.v7_0.update_autoname_field
+erpnext.patches.v7_0.update_status_of_po_so
\ No newline at end of file
diff --git a/erpnext/patches/v7_0/update_status_of_po_so.py b/erpnext/patches/v7_0/update_status_of_po_so.py
new file mode 100644
index 0000000..0e2dd74
--- /dev/null
+++ b/erpnext/patches/v7_0/update_status_of_po_so.py
@@ -0,0 +1,62 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe.utils import cint, flt
+
+def execute():
+ update_po_per_received_per_billed()
+ update_so_per_delivered_per_billed()
+ update_status()
+
+def update_po_per_received_per_billed():
+ frappe.db.sql("""
+ update
+ `tabPurchase Order`
+ set
+ `tabPurchase Order`.per_received = round((select sum(if(qty > ifnull(received_qty, 0),
+ ifnull(received_qty, 0), qty)) / sum(qty) *100 from `tabPurchase Order Item`
+ where parent = `tabPurchase Order`.name), 2),
+ `tabPurchase Order`.per_billed = round((select sum( if(amount > ifnull(billed_amt, 0),
+ ifnull(billed_amt, 0), amount)) / sum(amount) *100 from `tabPurchase Order Item`
+ where parent = `tabPurchase Order`.name), 2)""")
+
+def update_so_per_delivered_per_billed():
+ frappe.db.sql("""
+ update
+ `tabSales Order`
+ set
+ `tabSales Order`.per_delivered = round((select sum( if(qty > ifnull(delivered_qty, 0),
+ ifnull(delivered_qty, 0), qty)) / sum(qty) *100 from `tabSales Order Item`
+ where parent = `tabSales Order`.name), 2),
+ `tabSales Order`.per_billed = round((select sum( if(amount > ifnull(billed_amt, 0),
+ ifnull(billed_amt, 0), amount)) / sum(amount) *100 from `tabSales Order Item`
+ where parent = `tabSales Order`.name), 2)""")
+
+def update_status():
+ frappe.db.sql("""
+ update
+ `tabSales Order`
+ set status = (Case when status = 'Closed' then 'Closed'
+ When per_delivered < 100 and per_billed < 100 and docstatus = 1 then 'To Deliver and Bill'
+ when per_delivered = 100 and per_billed < 100 and docstatus = 1 then 'To Bill'
+ when per_delivered < 100 and per_billed = 100 and docstatus = 1 then 'To Deliver'
+ when per_delivered = 100 and per_billed = 100 and docstatus = 1 then 'Completed'
+ when order_type = 'Maintenance' and per_billed = 100 and docstatus = 1 then 'Completed'
+ when docstatus = 2 then 'Cancelled'
+ else 'Draft'
+ End)""")
+
+ frappe.db.sql("""
+ update
+ `tabPurchase Order`
+ set status = (Case when status = 'Closed' then 'Closed'
+ when status = 'Delivered' then 'Delivered'
+ When per_received < 100 and per_billed < 100 and docstatus = 1 then 'To Receive and Bill'
+ when per_received = 100 and per_billed < 100 and docstatus = 1 then 'To Bill'
+ when per_received < 100 and per_billed = 100 and docstatus = 1 then 'To Receive'
+ when per_received = 100 and per_billed = 100 and docstatus = 1 then 'Completed'
+ when docstatus = 2 then 'Cancelled'
+ else 'Draft'
+ End)""")
\ No newline at end of file