fixes in payment reconciliation
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.js b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.js
index f719db9..17b42be 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.js
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.js
@@ -30,19 +30,43 @@
}
}
+// cur_frm.fields_dict.voucher_no.get_query = function(doc) {
+// if (!doc.account) msgprint("Please select Account first");
+// else {
+// return repl("select gle.voucher_no, gle.posting_date \
+// from `tabGL Entry` gle where gle.account = '%(acc)s' \
+// and gle.voucher_type = '%(dt)s' \
+// and gle.voucher_no LIKE '%s' \
+// and ifnull(gle.is_cancelled, 'No') = 'No'\
+// and (ifnull(against_voucher_type, '') = '' or \
+// (select sum(debit) - sum(credit) from `tabGL Entry` \
+// where against_voucher_type = '%(dt)s' and against_voucher = gle.voucher_no \
+// and ifnull(is_cancelled, 'No') = 'No') != 0 )\
+// ORDER BY gle.posting_date DESC, gle.voucher_no DESC LIMIT 50 \
+// ", {dt:doc.voucher_type, acc:doc.account});
+// }
+// }
+
cur_frm.fields_dict.voucher_no.get_query = function(doc) {
if (!doc.account) msgprint("Please select Account first");
else {
- return repl("select gle.voucher_no, gle.posting_date \
- from `tabGL Entry` gle where gle.account = '%(acc)s' \
- and gle.voucher_type = '%(dt)s' \
- and gle.voucher_no LIKE '%s' \
- and ifnull(gle.is_cancelled, 'No') = 'No'\
- and (select sum(debit) - sum(credit) from `tabGL Entry` \
- where against_voucher_type = '%(dt)s' and against_voucher = gle.voucher_no \
- and ifnull(is_cancelled, 'No') = 'No') != 0 \
- ORDER BY gle.posting_date DESC, gle.voucher_no DESC LIMIT 50 \
- ", {dt:doc.voucher_type, acc:doc.account});
+ return repl("select gle.voucher_no, gle.posting_date, gle.%(account_type)s \
+ from `tabGL Entry` gle \
+ where gle.account = '%(acc)s' \
+ and gle.voucher_type = '%(dt)s' \
+ and gle.voucher_no like '%s' \
+ and ifnull(gle.is_cancelled, 'No') = 'No' \
+ and (ifnull(gle.against_voucher, '') = '' \
+ or ifnull(gle.against_voucher, '') = gle.voucher_no ) \
+ and ifnull(gle.%(account_type)s, 0) > 0 \
+ and (select ifnull(abs(sum(debit) - sum(credit)), 0) from `tabGL Entry` \
+ where against_voucher_type = '%(dt)s' \
+ and against_voucher = gle.voucher_no \
+ and voucher_no != gle.voucher_no \
+ and ifnull(is_cancelled, 'No') = 'No') != \
+ abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 0)) \
+ ORDER BY gle.posting_date DESC, gle.voucher_no DESC LIMIT 50",
+ {dt:doc.voucher_type, acc:doc.account, account_type: doc.account_type});
}
}
@@ -50,3 +74,12 @@
get_server_fields('get_voucher_details', '', '', doc, cdt, cdn, 1)
}
+cur_frm.cscript.account = function(doc, cdt, cdn) {
+ wn.call({
+ doc: this.frm.doc,
+ method: "set_account_type",
+ callback: function(r) {
+ if(!r.exc) refresh_field("account_type");
+ }
+ });
+}
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
index ee33c3a..3978688 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.py
@@ -27,32 +27,32 @@
def __init__(self, doc, doclist):
self.doc = doc
self.doclist = doclist
- self.acc_type = self.doc.account and webnotes.conn.sql("""select debit_or_credit
- from `tabAccount` where name = %s""", self.doc.account)[0][0].lower() or ''
- self.dt = {
- 'Sales Invoice': 'Sales Invoice',
- 'Purchase Invoice': 'Purchase Invoice',
- 'Journal Voucher': 'Journal Voucher'
- }
+
+ def set_account_type(self):
+ self.doc.account_type = self.doc.account and \
+ webnotes.conn.get_value("Account", self.doc.account, "debit_or_credit").lower() or ""
def get_voucher_details(self):
- tot_amt = webnotes.conn.sql("""
- select sum(%s) from `tabGL Entry` where
- voucher_type = %s and voucher_no = %s
+ total_amount = webnotes.conn.sql("""select %s from `tabGL Entry`
+ where voucher_type = %s and voucher_no = %s
and account = %s and ifnull(is_cancelled, 'No') = 'No'""" %
- (self.acc_type, '%s', '%s', '%s'),
- (self.dt[self.doc.voucher_type], self.doc.voucher_no, self.doc.account))
+ (self.doc.account_type, '%s', '%s', '%s'),
+ (self.doc.voucher_type, self.doc.voucher_no, self.doc.account))
+
+ total_amount = total_amount and flt(total_amount[0][0]) or 0
- outstanding = webnotes.conn.sql("""
+ reconciled_payment = webnotes.conn.sql("""
select sum(%s) - sum(%s) from `tabGL Entry` where
against_voucher = %s and voucher_no != %s
and account = %s and ifnull(is_cancelled, 'No') = 'No'""" %
- ((self.acc_type == 'debit' and 'credit' or 'debit'), self.acc_type, '%s', '%s', '%s'),
- (self.doc.voucher_no, self.doc.voucher_no, self.doc.account))
+ ((self.doc.account_type == 'debit' and 'credit' or 'debit'), self.doc.account_type,
+ '%s', '%s', '%s'), (self.doc.voucher_no, self.doc.voucher_no, self.doc.account))
+
+ reconciled_payment = reconciled_payment and flt(reconciled_payment[0][0]) or 0
ret = {
- 'total_amount': flt(tot_amt[0][0]) or 0,
- 'pending_amt_to_reconcile': flt(tot_amt[0][0]) - flt(outstanding[0][0]) or 0
+ 'total_amount': total_amount,
+ 'pending_amt_to_reconcile': total_amount - reconciled_payment
}
return ret
@@ -69,7 +69,7 @@
def get_gl_entries(self):
self.validate_mandatory()
- dc = self.acc_type == 'debit' and 'credit' or 'debit'
+ dc = self.doc.account_type == 'debit' and 'credit' or 'debit'
cond = self.doc.from_date and " and t1.posting_date >= '" + self.doc.from_date + "'" or ""
cond += self.doc.to_date and " and t1.posting_date <= '" + self.doc.to_date + "'"or ""
@@ -97,7 +97,7 @@
'Payment to Invoice Matching Tool Detail', self.doclist)
ch.voucher_no = d.get('voucher_no')
ch.posting_date = d.get('posting_date')
- ch.amt_due = self.acc_type == 'debit' and flt(d.get('amt_due')) \
+ ch.amt_due = self.doc.account_type == 'debit' and flt(d.get('amt_due')) \
or -1*flt(d.get('amt_due'))
ch.total_amt = flt(d.get('total_amt'))
ch.against_account = d.get('against_account')
@@ -116,7 +116,7 @@
3. submit payment voucher
"""
if not self.doc.voucher_no or not webnotes.conn.sql("""select name from `tab%s`
- where name = %s""" % (self.dt[self.doc.voucher_type], '%s'), self.doc.voucher_no):
+ where name = %s""" % (self.doc.voucher_type, '%s'), self.doc.voucher_no):
msgprint("Please select valid Voucher No to proceed", raise_exception=1)
lst = []
@@ -125,11 +125,11 @@
args = {
'voucher_no' : d.voucher_no,
'voucher_detail_no' : d.voucher_detail_no,
- 'against_voucher_type' : self.dt[self.doc.voucher_type],
+ 'against_voucher_type' : self.doc.voucher_type,
'against_voucher' : self.doc.voucher_no,
'account' : self.doc.account,
'is_advance' : 'No',
- 'dr_or_cr' : self.acc_type=='debit' and 'credit' or 'debit',
+ 'dr_or_cr' : self.doc.account_type=='debit' and 'credit' or 'debit',
'unadjusted_amt' : flt(d.amt_due),
'allocated_amt' : flt(d.amt_to_be_reconciled)
}
diff --git a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.txt b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.txt
index 5a83314..6f5bdb4 100644
--- a/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.txt
+++ b/accounts/doctype/payment_to_invoice_matching_tool/payment_to_invoice_matching_tool.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-01-21 18:19:17",
+ "creation": "2013-01-23 12:23:46",
"docstatus": 0,
- "modified": "2013-01-22 14:56:41",
+ "modified": "2013-01-23 12:26:49",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -31,7 +31,7 @@
"parenttype": "DocType",
"permlevel": 0,
"read": 1,
- "report": 1,
+ "report": 0,
"submit": 0,
"write": 1
},
@@ -49,6 +49,14 @@
},
{
"doctype": "DocField",
+ "fieldname": "account_type",
+ "fieldtype": "Data",
+ "hidden": 1,
+ "label": "Account Type",
+ "read_only": 1
+ },
+ {
+ "doctype": "DocField",
"fieldname": "voucher_type",
"fieldtype": "Select",
"label": "Voucher Type",