Merge branch 'develop'
diff --git a/erpnext/__version__.py b/erpnext/__version__.py
index 5a3d5c3..192f4a7 100644
--- a/erpnext/__version__.py
+++ b/erpnext/__version__.py
@@ -1,2 +1,2 @@
from __future__ import unicode_literals
-__version__ = '6.8.0'
+__version__ = '6.8.1'
diff --git a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
index 13b8f02..f9d7312 100644
--- a/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
+++ b/erpnext/accounts/doctype/payment_reconciliation/payment_reconciliation.py
@@ -6,6 +6,7 @@
from frappe.utils import flt
from frappe import msgprint, _
from frappe.model.document import Document
+from erpnext.accounts.utils import get_outstanding_invoices
class PaymentReconciliation(Document):
def get_unreconciled_entries(self):
@@ -17,7 +18,7 @@
dr_or_cr = "credit_in_account_currency" if self.party_type == "Customer" \
else "debit_in_account_currency"
- cond = self.check_condition(dr_or_cr)
+ cond = self.check_condition()
bank_account_condition = "t2.against_account like %(bank_cash_account)s" \
if self.bank_cash_account else "1=1"
@@ -65,65 +66,11 @@
def get_invoice_entries(self):
#Fetch JVs, Sales and Purchase Invoices for 'invoices' to reconcile against
- non_reconciled_invoices = []
- dr_or_cr = "debit_in_account_currency" if self.party_type == "Customer" else "credit_in_account_currency"
- cond = self.check_condition(dr_or_cr)
- invoice_list = frappe.db.sql("""
- select
- voucher_no, voucher_type, posting_date,
- ifnull(sum({dr_or_cr}), 0) as invoice_amount
- from
- `tabGL Entry`
- where
- party_type = %(party_type)s and party = %(party)s
- and account = %(account)s and {dr_or_cr} > 0 {cond}
- and (CASE
- WHEN voucher_type = 'Journal Entry'
- THEN ifnull(against_voucher, '') = ''
- ELSE 1=1
- END)
- group by voucher_type, voucher_no
- """.format(**{
- "cond": cond,
- "dr_or_cr": dr_or_cr
- }), {
- "party_type": self.party_type,
- "party": self.party,
- "account": self.receivable_payable_account,
- }, as_dict=True)
+ condition = self.check_condition()
- for d in invoice_list:
- payment_amount = frappe.db.sql("""
- select
- ifnull(sum(ifnull({0}, 0)), 0)
- from
- `tabGL Entry`
- where
- party_type = %(party_type)s and party = %(party)s
- and account = %(account)s and {0} > 0
- and against_voucher_type = %(against_voucher_type)s
- and ifnull(against_voucher, '') = %(against_voucher)s
- """.format("credit_in_account_currency" if self.party_type == "Customer"
- else "debit_in_account_currency"), {
- "party_type": self.party_type,
- "party": self.party,
- "account": self.receivable_payable_account,
- "against_voucher_type": d.voucher_type,
- "against_voucher": d.voucher_no
- }
- )
-
- payment_amount = payment_amount[0][0] if payment_amount else 0
-
- if d.invoice_amount - payment_amount > 0.005:
- 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),
- 'outstanding_amount': flt(d.invoice_amount - payment_amount, 2)
- })
+ non_reconciled_invoices = get_outstanding_invoices(self.party_type, self.party,
+ self.receivable_payable_account, condition=condition)
self.add_invoice_entries(non_reconciled_invoices)
@@ -210,13 +157,18 @@
if not invoices_to_reconcile:
frappe.throw(_("Please select Allocated Amount, 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 ""
+ def check_condition(self):
+ cond = " and posting_date >= {0}".format(frappe.db.escape(self.from_date)) if self.from_date else ""
+ cond += " and posting_date <= {0}".format(frappe.db.escape(self.to_date)) if self.to_date else ""
+
+ if self.party_type == "Customer":
+ dr_or_cr = "debit_in_account_currency"
+ else:
+ dr_or_cr = "credit_in_account_currency"
if self.minimum_amount:
- cond += " and {0} >= %s".format(dr_or_cr) % self.minimum_amount
+ cond += " and `{0}` >= {1}".format(dr_or_cr, flt(self.minimum_amount))
if self.maximum_amount:
- cond += " and {0} <= %s".format(dr_or_cr) % self.maximum_amount
+ cond += " and `{0}` <= {1}".format(dr_or_cr, flt(self.maximum_amount))
return cond
diff --git a/erpnext/accounts/doctype/payment_tool/payment_tool.py b/erpnext/accounts/doctype/payment_tool/payment_tool.py
index 6cf2b6d..b4e5f89 100644
--- a/erpnext/accounts/doctype/payment_tool/payment_tool.py
+++ b/erpnext/accounts/doctype/payment_tool/payment_tool.py
@@ -63,20 +63,18 @@
party_account_currency = get_account_currency(args.get("party_account"))
company_currency = frappe.db.get_value("Company", args.get("company"), "default_currency")
- if args.get("party_type") == "Customer" and args.get("received_or_paid") == "Received":
- amount_query = "ifnull(debit_in_account_currency, 0) - ifnull(credit_in_account_currency, 0)"
- elif args.get("party_type") == "Supplier" and args.get("received_or_paid") == "Paid":
- amount_query = "ifnull(credit_in_account_currency, 0) - ifnull(debit_in_account_currency, 0)"
- else:
+ if ((args.get("party_type") == "Customer" and args.get("received_or_paid") == "Paid")
+ or (args.get("party_type") == "Supplier" and args.get("received_or_paid") == "Received")):
+
frappe.throw(_("Please enter the Against Vouchers manually"))
# Get all outstanding sales /purchase invoices
- outstanding_invoices = get_outstanding_invoices(amount_query, args.get("party_account"),
- args.get("party_type"), args.get("party"))
+ outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"))
# Get all SO / PO which are not fully billed or aginst which full advance not paid
orders_to_be_billed = get_orders_to_be_billed(args.get("party_type"), args.get("party"),
party_account_currency, company_currency)
+
return outstanding_invoices + orders_to_be_billed
def get_orders_to_be_billed(party_type, party, party_account_currency, company_currency):
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 88b1ed4..5d0403e 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -211,7 +211,7 @@
if d['allocated_amt'] < d['unadjusted_amt']:
jvd = frappe.db.sql("""
- select cost_center, balance, against_account, is_advance,
+ select cost_center, balance, against_account, is_advance,
account_type, exchange_rate, account_currency
from `tabJournal Entry Account` where name = %s
""", d['voucher_detail_no'], as_dict=True)
@@ -415,47 +415,63 @@
# Amount should be credited
return flt(stock_rbnb) + flt(sys_bal)
-def get_outstanding_invoices(amount_query, account, party_type, party):
- all_outstanding_vouchers = []
- outstanding_voucher_list = frappe.db.sql("""
- select
- voucher_no, voucher_type, posting_date,
- ifnull(sum({amount_query}), 0) as invoice_amount
+def get_outstanding_invoices(party_type, party, account, condition=None):
+ outstanding_invoices = []
+ precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
+
+ if party_type=="Customer":
+ dr_or_cr = "ifnull(debit_in_account_currency, 0) - ifnull(credit_in_account_currency, 0)"
+ payment_dr_or_cr = "ifnull(payment_gl_entry.credit_in_account_currency, 0) - ifnull(payment_gl_entry.debit_in_account_currency, 0)"
+ else:
+ dr_or_cr = "ifnull(credit_in_account_currency, 0) - ifnull(debit_in_account_currency, 0)"
+ payment_dr_or_cr = "ifnull(payment_gl_entry.debit_in_account_currency, 0) - ifnull(payment_gl_entry.credit_in_account_currency, 0)"
+
+ invoice_list = frappe.db.sql("""select
+ voucher_no, voucher_type, posting_date,
+ ifnull(sum({dr_or_cr}), 0) as invoice_amount,
+ (
+ select
+ ifnull(sum(ifnull({payment_dr_or_cr}, 0)), 0)
+ from `tabGL Entry` payment_gl_entry
+ where
+ payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
+ and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
+ and payment_gl_entry.party_type = invoice_gl_entry.party_type
+ and payment_gl_entry.party = invoice_gl_entry.party
+ and payment_gl_entry.account = invoice_gl_entry.account
+ and {payment_dr_or_cr} > 0
+ ) as payment_amount
from
- `tabGL Entry`
+ `tabGL Entry` invoice_gl_entry
where
- account = %s and party_type=%s and party=%s and {amount_query} > 0
- and (CASE
- WHEN voucher_type = 'Journal Entry'
- THEN ifnull(against_voucher, '') = ''
- ELSE 1=1
- END)
+ party_type = %(party_type)s
+ and party = %(party)s
+ and account = %(account)s
+ and {dr_or_cr} > 0
+ {condition}
+ and ((voucher_type = 'Journal Entry'
+ and (against_voucher = ''
+ or against_voucher is null))
+ or (voucher_type != 'Journal Entry'))
group by voucher_type, voucher_no
- """.format(amount_query = amount_query), (account, party_type, party), as_dict = True)
+ having (invoice_amount - payment_amount) > 0.005""".format(
+ dr_or_cr = dr_or_cr,
+ payment_dr_or_cr = payment_dr_or_cr,
+ condition = condition or ""
+ ), {
+ "party_type": party_type,
+ "party": party,
+ "account": account,
+ }, as_dict=True)
- for d in outstanding_voucher_list:
- payment_amount = frappe.db.sql("""
- select ifnull(sum({amount_query}), 0)
- from
- `tabGL Entry`
- where
- account = %s and party_type=%s and party=%s and {amount_query} < 0
- and against_voucher_type = %s and ifnull(against_voucher, '') = %s
- """.format(**{
- "amount_query": amount_query
- }), (account, party_type, party, d.voucher_type, d.voucher_no))
+ for d in invoice_list:
+ outstanding_invoices.append({
+ 'voucher_no': d.voucher_no,
+ 'voucher_type': d.voucher_type,
+ 'posting_date': d.posting_date,
+ 'invoice_amount': flt(d.invoice_amount),
+ 'payment_amount': flt(d.payment_amount),
+ 'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision)
+ })
- payment_amount = -1*payment_amount[0][0] if payment_amount else 0
- precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
-
- if d.invoice_amount > payment_amount:
-
- all_outstanding_vouchers.append({
- 'voucher_no': d.voucher_no,
- 'voucher_type': d.voucher_type,
- 'posting_date': d.posting_date,
- 'invoice_amount': flt(d.invoice_amount, precision),
- 'outstanding_amount': flt(d.invoice_amount - payment_amount, precision)
- })
-
- return all_outstanding_vouchers
+ return outstanding_invoices
diff --git a/erpnext/hooks.py b/erpnext/hooks.py
index a94511d..a3c0194 100644
--- a/erpnext/hooks.py
+++ b/erpnext/hooks.py
@@ -29,7 +29,7 @@
"""
app_icon = "icon-th"
app_color = "#e74c3c"
-app_version = "6.8.0"
+app_version = "6.8.1"
app_email = "info@erpnext.com"
app_license = "GNU General Public License (v3)"
source_link = "https://github.com/frappe/erpnext"
diff --git a/setup.py b/setup.py
index 4feb46d..2fbd4d3 100644
--- a/setup.py
+++ b/setup.py
@@ -1,6 +1,6 @@
from setuptools import setup, find_packages
-version = "6.8.0"
+version = "6.8.1"
with open("requirements.txt", "r") as f:
install_requires = f.readlines()