| Nabin Hait | 362455e | 2013-02-13 15:50:39 +0530 | [diff] [blame] | 1 | def execute(): |
| 2 | import webnotes |
| 3 | from webnotes.utils import flt |
| Nabin Hait | 66ea086 | 2013-02-19 15:13:23 +0530 | [diff] [blame] | 4 | records = webnotes.conn.sql(""" |
| 5 | select against_voucher_type, against_voucher, |
| 6 | sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) as outstanding from `tabGL Entry` |
| 7 | where ifnull(is_cancelled, 'No') = 'No' |
| 8 | and against_voucher_type in ("Sales Invoice", "Purchase Invoice") |
| 9 | and ifnull(against_voucher, '') != '' |
| 10 | group by against_voucher_type, against_voucher""", as_dict=1) |
| 11 | for r in records: |
| 12 | outstanding = webnotes.conn.sql("""select name, outstanding_amount from `tab%s` |
| 13 | where name = %s and docstatus = 1""" % |
| 14 | (r["against_voucher_type"], '%s'), (r["against_voucher"])) |
| 15 | |
| 16 | if outstanding and abs(flt(r["outstanding"])) != flt(outstanding[0][1]): |
| 17 | if ((r["against_voucher_type"]=='Sales Invoice' and flt(r["outstanding"]) >= 0) \ |
| 18 | or (r["against_voucher_type"]=="Purchase Invoice" and flt(["outstanding"]) <= 0)): |
| 19 | webnotes.conn.set_value(r["against_voucher_type"], r["against_voucher"], |
| Nabin Hait | 97f6843 | 2013-04-09 17:43:36 +0530 | [diff] [blame] | 20 | "outstanding_amount", abs(flt(r["outstanding"]))) |