Add Payment Reconciliation Feature/Tool - Complete
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
index cf62fd3..c2ba1e6 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.js
@@ -20,19 +20,38 @@
get_unreconciled_entries: function() {
var me = this;
- if(!this.frm.doc.company || !this.frm.doc.party_account) {
- if(!this.frm.doc.company) {
- msgprint(__("Please enter Company"));
- } else {
- msgprint(__("Please enter Party Account"));
+ return this.frm.call({
+ doc: me.frm.doc,
+ method: 'get_unreconciled_entries',
+ callback: function(r, rt) {
+ var invoices = [];
+
+ $.each(me.frm.doc.payment_reconciliation_invoices || [], function(i, row) {
+ if (row.invoice_number && !inList(invoices, row.invoice_number))
+ invoices.push(row.invoice_number);
+ });
+
+ frappe.meta.get_docfield("Payment Reconciliation Payment", "invoice_number",
+ me.frm.doc.name).options = invoices.join("\n");
+
+ $.each(me.frm.doc.payment_reconciliation_payments || [], function(i, p) {
+ if(!inList(invoices, cstr(p.invoice_number))) p.invoice_number = null;
+ });
+
+ refresh_field("payment_reconciliation_payments");
}
- } else {
- return this.frm.call({
- doc: me.frm.doc,
- method: 'get_unreconciled_entries'
- });
- }
+ });
+
+ },
+
+ reconcile: function() {
+ var me = this;
+ return this.frm.call({
+ doc: me.frm.doc,
+ method: 'reconcile'
+ });
}
+
});
$.extend(cur_frm.cscript, new erpnext.accounts.PaymentReconciliationController({frm: cur_frm}));
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
index 12253d0..ce0d731 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.json
@@ -1,4 +1,5 @@
{
+ "allow_copy": 1,
"creation": "2014-07-09 12:04:51.681583",
"custom": 0,
"docstatus": 0,
@@ -27,11 +28,12 @@
{
"fieldname": "party_type",
"fieldtype": "Select",
- "hidden": 0,
+ "hidden": 1,
"in_list_view": 1,
"label": "Party Type",
"options": "Customer\nSupplier",
"permlevel": 0,
+ "read_only": 1,
"reqd": 0
},
{
@@ -118,8 +120,9 @@
"read_only": 1
}
],
+ "hide_toolbar": 1,
"issingle": 1,
- "modified": "2014-07-11 15:01:47.133918",
+ "modified": "2014-07-17 19:07:34.385854",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation",
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index fc1ff24..be53aca 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -12,32 +12,42 @@
class PaymentReconciliation(Document):
def get_unreconciled_entries(self):
- jv_entries = self.get_jv_entries()
- self.add_payment_entries(jv_entries)
- invoice_entries = self.get_invoice_entries()
-
- self.add_invoice_entries(invoice_entries)
+ self.get_jv_entries()
+ self.get_invoice_entries()
def get_jv_entries(self):
- self.check_mandatory()
-
- dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
-
+ self.check_mandatory_to_fetch()
+ dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
cond = self.check_condition(dr_or_cr)
+ bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
+ if self.bank_cash_account else "1=1"
+
jv_entries = frappe.db.sql("""
select
t1.name as voucher_no, t1.posting_date, t1.remark, t2.account,
- t2.name as voucher_detail_no, t2.%s, t2.is_advance
+ t2.name as voucher_detail_no, t2.{dr_or_cr}, t2.is_advance
from
`tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
where
- t1.name = t2.parent and t1.docstatus = 1 and t2.account = %s
- and t2.%s > 0 and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
- and ifnull(t2.against_jv, '')='' %s
- group by t1.name, t2.name """ % (dr_or_cr, '%s', dr_or_cr, cond), (self.party_account),
- as_dict = True)
- return jv_entries
+ t1.name = t2.parent and t1.docstatus = 1 and t2.account = %(party_account)s
+ and t2.{dr_or_cr} > 0 and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
+ and ifnull(t2.against_jv, '')='' {cond}
+ and (CASE
+ WHEN t1.voucher_type in ('Debit Note', 'Credit Note')
+ THEN 1=1
+ ELSE {bank_account_condition}
+ END)
+ group by t1.name, t2.name """.format(**{
+ "dr_or_cr": dr_or_cr,
+ "cond": cond,
+ "bank_account_condition": bank_account_condition
+ }), {
+ "party_account": self.party_account,
+ "bank_cash_account": "%%%s%%" % self.bank_cash_account
+ }, as_dict=1)
+
+ self.add_payment_entries(jv_entries)
def add_payment_entries(self, jv_entries):
self.set('payment_reconciliation_payments', [])
@@ -48,14 +58,12 @@
ent.amount = flt(e.get('credit')) or flt(e.get('debit'))
ent.remark = e.get('remark')
ent.voucher_detail_number = e.get('voucher_detail_no')
+ ent.is_advance = e.get('is_advance')
def get_invoice_entries(self):
#Fetch JVs, Sales and Purchase Invoices for 'payment_reconciliation_invoices' to reconcile against
non_reconciled_invoices = []
- self.check_mandatory()
-
dr_or_cr = "debit" if self.party_type == "Customer" else "credit"
-
cond = self.check_condition(dr_or_cr)
invoice_list = frappe.db.sql("""
@@ -65,7 +73,7 @@
`tabGL Entry`
where
account = %s and ifnull(%s, 0) > 0 %s
- group by voucher_no, voucher_type""" % (dr_or_cr, "%s",
+ group by voucher_no, voucher_type""" % (dr_or_cr, '%s',
dr_or_cr, cond), (self.party_account), as_dict=True)
for d in invoice_list:
@@ -75,27 +83,30 @@
from
`tabGL Entry`
where
- account = %s and against_voucher_type = %s and ifnull(against_voucher, '') = %s""",
- (("credit" if self.party_type == "Customer" else "debit"), self.party_account,
- d.voucher_type, d.voucher_no))
-
+ account = %s and against_voucher_type = %s and ifnull(against_voucher, '') = %s""" %
+ (("credit" if self.party_type == "Customer" else "debit"), '%s', '%s', '%s'),
+ (self.party_account, d.voucher_type, d.voucher_no))
+
payment_amount = payment_amount[0][0] if payment_amount else 0
if d.amount > payment_amount:
- non_reconciled_invoices.append({'voucher_no': d.voucher_no,
+ non_reconciled_invoices.append({
+ 'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'amount': flt(d.amount),
'outstanding_amount': d.amount - payment_amount})
- return non_reconciled_invoices
+ self.add_invoice_entries(non_reconciled_invoices)
def add_invoice_entries(self, non_reconciled_invoices):
#Populate 'payment_reconciliation_invoices' with JVs and Invoices to reconcile against
self.set('payment_reconciliation_invoices', [])
if not non_reconciled_invoices:
- return
+ frappe.throw(_("No invoices found to be reconciled"))
+
+
for e in non_reconciled_invoices:
ent = self.append('payment_reconciliation_invoices', {})
ent.invoice_type = e.get('voucher_type')
@@ -104,16 +115,64 @@
ent.amount = flt(e.get('amount'))
ent.outstanding_amount = e.get('outstanding_amount')
- def check_mandatory(self):
- pass
+ def reconcile(self, args):
+ self.get_invoice_entries()
+ self.validate_invoice()
+ dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
+ lst = []
+ for e in self.get('payment_reconciliation_payments'):
+ lst.append({
+ 'voucher_no' : e.journal_voucher,
+ 'voucher_detail_no' : e.voucher_detail_number,
+ 'against_voucher_type' : e.invoice_type,
+ 'against_voucher' : e.invoice_number,
+ 'account' : self.party_account,
+ 'is_advance' : e.is_advance,
+ 'dr_or_cr' : dr_or_cr,
+ 'unadjusted_amt' : flt(e.amount),
+ 'allocated_amt' : flt(e.amount)
+ })
+
+ if lst:
+ from erpnext.accounts.utils import reconcile_against_document
+ reconcile_against_document(lst)
+ self.get_unreconciled_entries()
+ msgprint(_("Successfully Reconciled"))
+
+
+ def check_mandatory_to_fetch(self):
+ for fieldname in ["company", "party_account"]:
+ if not self.get(fieldname):
+ frappe.throw(_("Please select {0} first").format(self.meta.get_label(fieldname)))
+
+
+ def validate_invoice(self):
+ unreconciled_invoices = frappe._dict()
+ for d in self.get("payment_reconciliation_invoices"):
+ unreconciled_invoices.setdefault(d.invoice_type, {}).setdefault(d.invoice_number, d.outstanding_amount)
+
+ invoices_to_reconcile = []
+ for p in self.get("payment_reconciliation_payments"):
+ if p.invoice_type and p.invoice_number:
+ invoices_to_reconcile.append(p.invoice_number)
+
+ if p.invoice_number not in unreconciled_invoices.get(p.invoice_type):
+ frappe.throw(_("{0}: {1} not found in Invoice Details table")
+ .format(p.invoice_type, p.invoice_number))
+
+ if p.amount > unreconciled_invoices.get(p.invoice_type).get(p.invoice_number):
+ frappe.throw(_("Row {0}: Payment amount must be less than or equals to invoice outstanding amount").format(p.idx))
+
+ if not invoices_to_reconcile:
+ frappe.throw(_("Please select Invoice Type and Invoice Number in atleast one row"))
def check_condition(self, dr_or_cr):
cond = self.from_date and " and posting_date >= '" + self.from_date + "'" or ""
cond += self.to_date and " and posting_date <= '" + self.to_date + "'" or ""
if self.minimum_amount:
- cond += (" and ifnull(%s), 0) >= %s") % (dr_or_cr, self.minimum_amount)
+ cond += " and ifnull(%s, 0) >= %s" % (dr_or_cr, self.minimum_amount)
if self.maximum_amount:
cond += " and ifnull(%s, 0) <= %s" % (dr_or_cr, self.maximum_amount)
- return cond
\ No newline at end of file
+ return cond
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
index 008b326..fba5f4e 100644
--- a/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
+++ b/erpnext/accounts/doctype/payment_reconciliation_invoice/payment_reconciliation_invoice.json
@@ -18,6 +18,7 @@
"fieldtype": "Data",
"in_list_view": 1,
"label": "Invoice Number",
+ "options": "",
"permlevel": 0,
"read_only": 1
},
@@ -47,7 +48,7 @@
}
],
"istable": 1,
- "modified": "2014-07-09 17:15:00.069551",
+ "modified": "2014-07-17 15:52:28.820965",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation Invoice",
diff --git a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
index 231dfae..e939b02 100644
--- a/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
+++ b/erpnext/accounts/doctype/payment_reconciliation_payment/payment_reconciliation_payment.json
@@ -43,17 +43,18 @@
},
{
"fieldname": "invoice_number",
- "fieldtype": "Data",
+ "fieldtype": "Select",
"in_list_view": 1,
"label": "Invoice Number",
+ "options": "",
"permlevel": 0,
"reqd": 1
},
{
- "fieldname": "remark",
- "fieldtype": "Text",
- "in_list_view": 1,
- "label": "Remark",
+ "fieldname": "is_advance",
+ "fieldtype": "Data",
+ "hidden": 1,
+ "label": "Is Advance",
"permlevel": 0,
"read_only": 1
},
@@ -65,10 +66,18 @@
"label": "Voucher Detail Number",
"permlevel": 0,
"read_only": 1
+ },
+ {
+ "fieldname": "remark",
+ "fieldtype": "Small Text",
+ "in_list_view": 1,
+ "label": "Remark",
+ "permlevel": 0,
+ "read_only": 1
}
],
"istable": 1,
- "modified": "2014-07-14 16:48:45.875052",
+ "modified": "2014-07-17 18:32:25.230146",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Reconciliation Payment",