fixed delivery/billing status issue and patched for old records
diff --git a/crm/doctype/sales_common/sales_common.py b/crm/doctype/sales_common/sales_common.py
index f99853b..68afed4 100644
--- a/crm/doctype/sales_common/sales_common.py
+++ b/crm/doctype/sales_common/sales_common.py
@@ -707,7 +707,7 @@
set
%(target_field)s = (select sum(qty) from `tab%(source_dt)s` where `%(join_field)s`="%(detail_id)s" and (docstatus=1 %(cond)s))
where
- name="%(detail_id)s"
+ name="%(detail_id)s"
""" % args)
# get unique transactions to update
@@ -716,33 +716,15 @@
args['name'] = name
# update percent complete in the parent table
- if args.get('source_dt')!='Installed Item Details':
- sql("""
- update
- `tab%(target_parent_dt)s`
- set
- %(target_parent_field)s = (
- (select sum(amount) from `tab%(source_dt)s` where `%(percent_join_field)s`="%(name)s" and (docstatus=1 %(cond)s))
- / net_total
- * 100
- )
- where
- name="%(name)s"
- """ % args)
- else:
- sql("""
- update
- `tab%(target_parent_dt)s`
- set
- %(target_parent_field)s = (
- (select sum(qty) from `tab%(source_dt)s` where `%(percent_join_field)s`="%(name)s" and (docstatus=1 %(cond)s))
- / (select sum(qty) from `tab%(target_dt)s` where parent="%(name)s" and docstatus=1)
- * 100
- )
- where
- name="%(name)s"
- """ % args)
-
+ sql("""
+ update
+ `tab%(target_parent_dt)s`
+ set
+ %(target_parent_field)s =
+ (select sum(if(qty > ifnull(%(target_field)s, 0), %(target_field)s, qty))/sum(qty)*100 from `tab%(target_dt)s` where parent="%(name)s")
+ where
+ name="%(name)s"
+ """ % args)
# update field
if args['status_field']:
@@ -756,4 +738,3 @@
where
name="%(name)s"
""" % args)
-
diff --git a/patches/delivery_billing_status_patch.py b/patches/delivery_billing_status_patch.py
new file mode 100644
index 0000000..2f95013
--- /dev/null
+++ b/patches/delivery_billing_status_patch.py
@@ -0,0 +1,43 @@
+import webnotes
+sql = webnotes.conn.sql
+
+# update SO
+#---------------
+def update_percent():
+ so = sql("select name from `tabSales Order` where docstatus = 1")
+ for d in so:
+ sql("""
+ update
+ `tabSales Order`
+ set
+ per_delivered = (select sum(if(qty > ifnull(delivered_qty, 0), delivered_qty, qty))/sum(qty)*100 from `tabSales Order Detail` where parent='%s'),
+ per_billed = (select sum(if(qty > ifnull(billed_qty, 0), billed_qty, qty))/sum(qty)*100 from `tabSales Order Detail` where parent='%s')
+ where
+ name='%s'""" % (d[0], d[0], d[0]))
+
+ # update DN
+ # ---------
+ dn = sql("select name from `tabDelivery Note` where docstatus = 1")
+ for d in dn:
+ sql("""
+ update
+ `tabDelivery Note`
+ set
+ per_billed = (select sum(if(qty > ifnull(billed_qty, 0), billed_qty, qty))/sum(qty)*100 from `tabDelivery Note Detail` where parent='%s')
+ where
+ name='%s'""" % (d[0], d[0]))
+
+
+# update delivery/billing status
+#-------------------------------
+def update_status():
+ sql("""update `tabSales Order` set delivery_status = if(ifnull(per_delivered,0) < 0.001, 'Not Delivered',
+ if(per_delivered >= 99.99, 'Fully Delivered', 'Partly Delivered'))""")
+ sql("""update `tabSales Order` set billing_status = if(ifnull(per_billed,0) < 0.001, 'Not Billed',
+ if(per_billed >= 99.99, 'Fully Billed', 'Partly Billed'))""")
+ sql("""update `tabDelivery Note` set billing_status = if(ifnull(per_billed,0) < 0.001, 'Not Billed',
+ if(per_billed >= 99.99, 'Fully Billed', 'Partly Billed'))""")
+
+def run_patch():
+ update_percent()
+ update_status()
diff --git a/patches/patch.py b/patches/patch.py
index 111d2e8..e23eeca 100644
--- a/patches/patch.py
+++ b/patches/patch.py
@@ -1,6 +1,6 @@
# REMEMBER to update this
# ========================
-last_patch = 299
+last_patch = 300
#-------------------------------------------
@@ -1192,3 +1192,6 @@
sql("update `tabDocField` set options = 'Link:Company' where parent = 'Appraisal' and fieldname = 'company'")
elif patch_no == 299:
sql("update `tabDocPerm` set `match` = NULL where parent = 'Employee' and role = 'Employee'")
+ elif patch_no == 300:
+ from patches.delivery_billing_status_patch import run_patch
+ run_patch()