Fixes in Payment Reconciliation
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index 2d844bb..c114c52 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -18,29 +18,23 @@
def get_jv_entries(self):
self.check_mandatory_to_fetch()
dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
- if self.party_type=="Customer":
- amount_query = "ifnull(t2.credit, 0) - ifnull(t2.debit, 0)"
- else:
- amount_query = "ifnull(t2.debit, 0) - ifnull(t2.credit, 0)"
-
- cond = self.check_condition(amount_query)
+
+ 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, {amount_query} as payment_amount, t2.is_advance
+ t1.name as voucher_no, t1.posting_date, t1.remark, t2.account,
+ t2.name as voucher_detail_no, {dr_or_cr} as payment_amount, t2.is_advance
from
`tabJournal Voucher` t1, `tabJournal Voucher Detail` t2
where
t1.name = t2.parent and t1.docstatus = 1 and t2.docstatus = 1
- and t2.account = %(party_account)s and {amount_query} > 0
- and ifnull((select ifnull(sum(ifnull(credit, 0) - ifnull(debit, 0)), 0) from `tabJournal Voucher Detail`
- where parent=t1.name and account=t2.account and docstatus=1 group by account), 0) > 0
- and ifnull(t2.against_voucher, '')='' and ifnull(t2.against_invoice, '')=''
- and ifnull(t2.against_jv, '')='' {cond}
+ and t2.account = %(party_account)s and {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
@@ -50,7 +44,6 @@
"dr_or_cr": dr_or_cr,
"cond": cond,
"bank_account_condition": bank_account_condition,
- "amount_query": amount_query
}), {
"party_account": self.party_account,
"bank_cash_account": "%%%s%%" % self.bank_cash_account
@@ -73,49 +66,42 @@
#Fetch JVs, Sales and Purchase Invoices for 'payment_reconciliation_invoices' to reconcile against
non_reconciled_invoices = []
dr_or_cr = "debit" if self.party_type == "Customer" else "credit"
- if self.party_type=="Customer":
- amount_query = "ifnull(debit, 0) - ifnull(credit, 0)"
- else:
- amount_query = "ifnull(credit, 0) - ifnull(debit, 0)"
-
- cond = self.check_condition(amount_query)
+ cond = self.check_condition(dr_or_cr)
invoice_list = frappe.db.sql("""
select
- voucher_no, voucher_type, posting_date,
- ifnull(sum({amount_query}), 0) as invoice_amount
+ voucher_no, voucher_type, posting_date,
+ ifnull(sum({dr_or_cr}), 0) as invoice_amount
from
`tabGL Entry`
where
- account = %s and {amount_query} > 0 {cond}
+ account = %s and {dr_or_cr} > 0 {cond}
group by voucher_type, voucher_no
""".format(**{
"cond": cond,
- "amount_query": amount_query
+ "dr_or_cr": dr_or_cr
}), (self.party_account), as_dict=True)
for d in invoice_list:
payment_amount = frappe.db.sql("""
select
- ifnull(sum(ifnull({amount_query}, 0)), 0)
+ ifnull(sum(ifnull({0}, 0)), 0)
from
`tabGL Entry`
where
- account = %s and {amount_query} < 0
+ account = %s and {0} > 0
and against_voucher_type = %s and ifnull(against_voucher, '') = %s
- """.format(**{
- "cond": cond,
- "amount_query": amount_query
- }), (self.party_account, d.voucher_type, d.voucher_no))
-
+ """.format("credit" if self.party_type == "Customer" else "debit"),
+ (self.party_account, d.voucher_type, d.voucher_no))
+
payment_amount = -1*payment_amount[0][0] if payment_amount else 0
if d.invoice_amount > payment_amount:
non_reconciled_invoices.append({
- 'voucher_no': d.voucher_no,
- 'voucher_type': d.voucher_type,
- 'posting_date': d.posting_date,
- 'invoice_amount': flt(d.invoice_amount),
+ 'voucher_no': d.voucher_no,
+ 'voucher_type': d.voucher_type,
+ 'posting_date': d.posting_date,
+ 'invoice_amount': flt(d.invoice_amount),
'outstanding_amount': d.invoice_amount - payment_amount})
self.add_invoice_entries(non_reconciled_invoices)
@@ -123,7 +109,7 @@
def add_invoice_entries(self, non_reconciled_invoices):
#Populate 'payment_reconciliation_invoices' with JVs and Invoices to reconcile against
self.set('payment_reconciliation_invoices', [])
-
+
for e in non_reconciled_invoices:
ent = self.append('payment_reconciliation_invoices', {})
ent.invoice_type = e.get('voucher_type')
@@ -166,14 +152,14 @@
def validate_invoice(self):
if not self.get("payment_reconciliation_invoices"):
frappe.throw(_("No records found in the Invoice table"))
-
+
if not self.get("payment_reconciliation_payments"):
frappe.throw(_("No records found in the Payment table"))
-
+
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:
@@ -189,13 +175,13 @@
if not invoices_to_reconcile:
frappe.throw(_("Please select Invoice Type and Invoice Number in atleast one row"))
- def check_condition(self, amount_query):
+ 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 {0} >= %s".format(amount_query) % self.minimum_amount
+ cond += " and {0} >= %s".format(dr_or_cr) % self.minimum_amount
if self.maximum_amount:
- cond += " and {0} <= %s".format(amount_query) % self.maximum_amount
+ cond += " and {0} <= %s".format(dr_or_cr) % self.maximum_amount
- return cond
\ No newline at end of file
+ return cond