nabinhait | 690c697 | 2011-06-28 14:42:07 +0530 | [diff] [blame] | 1 | import webnotes |
| 2 | sql = webnotes.conn.sql |
| 3 | |
Nabin Hait | f2718d1 | 2011-09-14 11:40:01 +0530 | [diff] [blame^] | 4 | test=1 |
| 5 | |
| 6 | # Update SO and DN Detail |
| 7 | #-------------------------- |
| 8 | def update_delivered_billed_qty(): |
| 9 | # update billed amt in item table in so and dn |
| 10 | sql(""" update `tabSales Order Detail` so |
| 11 | set billed_amt = (select sum(amount) from `tabRV Detail` where `so_detail`= so.name and docstatus=1 and parent not like 'old%%'), |
| 12 | delivered_qty = (select sum(qty) from `tabDelivery Note Detail` where `prevdoc_detail_docname`= so.name and docstatus=1 and parent not like 'old%%'), |
| 13 | modified = now() |
| 14 | where docstatus = 1 |
| 15 | """) |
| 16 | |
| 17 | sql(""" update `tabDelivery Note Detail` dn |
| 18 | set billed_amt = (select sum(amount) from `tabRV Detail` where `dn_detail`= dn.name and docstatus=1 and parent not like 'old%%'), |
| 19 | modified = now() |
| 20 | where docstatus = 1 |
| 21 | """) |
| 22 | |
nabinhait | 690c697 | 2011-06-28 14:42:07 +0530 | [diff] [blame] | 23 | # update SO |
| 24 | #--------------- |
| 25 | def update_percent(): |
Nabin Hait | f2718d1 | 2011-09-14 11:40:01 +0530 | [diff] [blame^] | 26 | # calculate % billed based on item table |
| 27 | sql(""" update `tabSales Order` so |
| 28 | set per_delivered = (select sum(if(qty > ifnull(delivered_qty, 0), delivered_qty, qty))/sum(qty)*100 from `tabSales Order Detail` where parent=so.name), |
| 29 | per_billed = (select sum(if(amount > ifnull(billed_amt, 0), billed_amt, amount))/sum(amount)*100 from `tabSales Order Detail` where parent = so.name), |
| 30 | modified = now() |
| 31 | where docstatus = 1 |
| 32 | """) |
| 33 | |
nabinhait | 690c697 | 2011-06-28 14:42:07 +0530 | [diff] [blame] | 34 | # update DN |
Nabin Hait | f2718d1 | 2011-09-14 11:40:01 +0530 | [diff] [blame^] | 35 | # --------- |
| 36 | sql(""" update `tabDelivery Note` dn |
| 37 | set per_billed = (select sum(if(amount > ifnull(billed_amt, 0), billed_amt, amount))/sum(amount)*100 from `tabDelivery Note Detail` where parent = dn.name), |
| 38 | modified = now() |
| 39 | where docstatus=1 |
| 40 | """) |
nabinhait | 690c697 | 2011-06-28 14:42:07 +0530 | [diff] [blame] | 41 | |
| 42 | # update delivery/billing status |
| 43 | #------------------------------- |
| 44 | def update_status(): |
| 45 | sql("""update `tabSales Order` set delivery_status = if(ifnull(per_delivered,0) < 0.001, 'Not Delivered', |
| 46 | if(per_delivered >= 99.99, 'Fully Delivered', 'Partly Delivered'))""") |
| 47 | sql("""update `tabSales Order` set billing_status = if(ifnull(per_billed,0) < 0.001, 'Not Billed', |
| 48 | if(per_billed >= 99.99, 'Fully Billed', 'Partly Billed'))""") |
| 49 | sql("""update `tabDelivery Note` set billing_status = if(ifnull(per_billed,0) < 0.001, 'Not Billed', |
| 50 | if(per_billed >= 99.99, 'Fully Billed', 'Partly Billed'))""") |
| 51 | |
| 52 | def run_patch(): |
Nabin Hait | f2718d1 | 2011-09-14 11:40:01 +0530 | [diff] [blame^] | 53 | update_delivered_billed_qty() |
nabinhait | 690c697 | 2011-06-28 14:42:07 +0530 | [diff] [blame] | 54 | update_percent() |
| 55 | update_status() |