| Rushabh Mehta | ad45e31 | 2013-11-20 12:59:58 +0530 | [diff] [blame] | 1 | # Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors |
| Rushabh Mehta | e67d1fb | 2013-08-05 14:59:54 +0530 | [diff] [blame] | 2 | # License: GNU General Public License v3. See license.txt |
| 3 | |
| Nabin Hait | 362455e | 2013-02-13 15:50:39 +0530 | [diff] [blame] | 4 | def execute(): |
| 5 | import webnotes |
| 6 | from webnotes.utils import flt |
| Nabin Hait | 66ea086 | 2013-02-19 15:13:23 +0530 | [diff] [blame] | 7 | records = webnotes.conn.sql(""" |
| 8 | select against_voucher_type, against_voucher, |
| 9 | sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) as outstanding from `tabGL Entry` |
| 10 | where ifnull(is_cancelled, 'No') = 'No' |
| 11 | and against_voucher_type in ("Sales Invoice", "Purchase Invoice") |
| 12 | and ifnull(against_voucher, '') != '' |
| 13 | group by against_voucher_type, against_voucher""", as_dict=1) |
| 14 | for r in records: |
| 15 | outstanding = webnotes.conn.sql("""select name, outstanding_amount from `tab%s` |
| 16 | where name = %s and docstatus = 1""" % |
| 17 | (r["against_voucher_type"], '%s'), (r["against_voucher"])) |
| 18 | |
| 19 | if outstanding and abs(flt(r["outstanding"])) != flt(outstanding[0][1]): |
| 20 | if ((r["against_voucher_type"]=='Sales Invoice' and flt(r["outstanding"]) >= 0) \ |
| 21 | or (r["against_voucher_type"]=="Purchase Invoice" and flt(["outstanding"]) <= 0)): |
| 22 | webnotes.conn.set_value(r["against_voucher_type"], r["against_voucher"], |
| Nabin Hait | 97f6843 | 2013-04-09 17:43:36 +0530 | [diff] [blame] | 23 | "outstanding_amount", abs(flt(r["outstanding"]))) |