Trial balance and payable accounts reports and sales return fixes (#15000)
* adding supplier type filter to payable accounts and payable accounts summary reports
adding supplier type filter to payable accounts and payable accounts
summary reports as customer group at receivable accounts and summary
reports as its important and easier to put this filter
* fix trial balance opening and closing values
the removed mathematical operations was causing showing incorrect values
at opening and closing columns where the total row was correct so the
operation deleted to view the correct values to be equal to the total
row and to stop confusing the user due to different values between the
total row and the the values in front of each account
* fix for supplier type filter field on payable accounts reports
rename the supplier type filter field to supplier group in payable
accounts and summary reports
* fix for paid amount in returned sales invoice
the current paid amount not change from source value if you deleted any
item if the invoice contain for example 3 items and you only need to
return on item the current steps is to open the original invoice and
press on make return then you delete the unwanted items but in this case
the paid amount will not change and you will get an alert from the
system until you change it manually but with this modification it works
very well.
* fixing trial balance values and totals
fixing trial balance opening and closing ( DR - CR ) values and totals
diff --git a/erpnext/accounts/report/accounts_payable/accounts_payable.js b/erpnext/accounts/report/accounts_payable/accounts_payable.js
index d859d59..9370f04 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.js
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.js
@@ -29,6 +29,12 @@
}
},
{
+ "fieldname":"supplier_group",
+ "label": __("Supplier Group"),
+ "fieldtype": "Link",
+ "options": "Supplier Group"
+ },
+ {
"fieldname":"report_date",
"label": __("As on Date"),
"fieldtype": "Date",
diff --git a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
index 6277cc7..77b099f 100644
--- a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
+++ b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
@@ -17,6 +17,12 @@
"options": "Supplier"
},
{
+ "fieldname":"supplier_group",
+ "label": __("Supplier Group"),
+ "fieldtype": "Link",
+ "options": "Supplier Group"
+ },
+ {
"fieldname":"report_date",
"label": __("Date"),
"fieldtype": "Date",
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 28543b5..d4822e6 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -380,6 +380,13 @@
conditions.append("""party in (select parent
from `tabSales Team` where sales_person=%s and parenttype = 'Customer')""")
values.append(self.filters.get("sales_person"))
+
+ if party_type_field=="supplier":
+ if self.filters.get("supplier_group"):
+ conditions.append("""party in (select name from tabSupplier
+ where supplier_group=%s)""")
+ values.append(self.filters.get("supplier_group"))
+
return " and ".join(conditions), values
def get_gl_entries_for(self, party, party_type, against_voucher_type, against_voucher):
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index 657523b..0a3f56a 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -51,8 +51,9 @@
filters.to_date = filters.year_end_date
def get_data(filters):
- accounts = frappe.db.sql("""
- select name, account_number, parent_account, account_name, root_type, report_type, lft, rgt
+
+ accounts = frappe.db.sql("""select name, account_number, parent_account, account_name, root_type, report_type, lft, rgt
+
from `tabAccount` where company=%s order by lft""", filters.company, as_dict=True)
company_currency = erpnext.get_company_currency(filters.company)
@@ -75,9 +76,9 @@
accumulate_values_into_parents(accounts, accounts_by_name)
data = prepare_data(accounts, filters, total_row, parent_children_map, company_currency)
- data = filter_out_zero_value_rows(data, parent_children_map,
+ data = filter_out_zero_value_rows(data, parent_children_map,
show_zero_values=filters.get("show_zero_values"))
-
+
return data
def get_opening_balances(filters):
@@ -159,10 +160,27 @@
d["debit"] += flt(entry.debit)
d["credit"] += flt(entry.credit)
+ d["closing_debit"] = d["opening_debit"] + d["debit"]
+ d["closing_credit"] = d["opening_credit"] + d["credit"]
total_row["debit"] += d["debit"]
total_row["credit"] += d["credit"]
- total_row["opening_debit"] += d["opening_debit"]
- total_row["opening_credit"] += d["opening_credit"]
+
+ if d["root_type"] == "Asset" or d["root_type"] == "Equity" or d["root_type"] == "Expense":
+ d["opening_debit"] -= d["opening_credit"]
+ d["opening_credit"] = 0.0
+ total_row["opening_debit"] += d["opening_debit"]
+ if d["root_type"] == "Liability" or d["root_type"] == "Income":
+ d["opening_credit"] -= d["opening_debit"]
+ d["opening_debit"] = 0.0
+ total_row["opening_credit"] += d["opening_credit"]
+ if d["root_type"] == "Asset" or d["root_type"] == "Equity" or d["root_type"] == "Expense":
+ d["closing_debit"] -= d["closing_credit"]
+ d["closing_credit"] = 0.0
+ total_row["closing_debit"] += d["closing_debit"]
+ if d["root_type"] == "Liability" or d["root_type"] == "Income":
+ d["closing_credit"] -= d["closing_debit"]
+ d["closing_debit"] = 0.0
+ total_row["closing_credit"] += d["closing_credit"]
return total_row
@@ -174,11 +192,6 @@
def prepare_data(accounts, filters, total_row, parent_children_map, company_currency):
data = []
- tmpaccnt = sorted(accounts, key = lambda account: account.name)
- if not (accounts[0].account_number is None):
- accounts = tmpaccnt
-
- total_row["closing_debit"] = total_row["closing_credit"] = 0
for d in accounts:
has_value = False
@@ -197,18 +210,14 @@
for key in value_fields:
row[key] = flt(d.get(key, 0.0), 3)
-
+
if abs(row[key]) >= 0.005:
# ignore zero values
has_value = True
row["has_value"] = has_value
data.append(row)
-
- if not d.parent_account:
- total_row["closing_debit"] += (d["debit"] - d["credit"]) if (d["debit"] - d["credit"]) > 0 else 0
- total_row["closing_credit"] += abs(d["debit"] - d["credit"]) if (d["debit"] - d["credit"]) < 0 else 0
-
+
data.extend([{},total_row])
return data
@@ -277,18 +286,18 @@
d["closing_debit"] = d["opening_debit"] + d["debit"]
d["closing_credit"] = d["opening_credit"] + d["credit"]
- if d["closing_debit"] > d["closing_credit"]:
- d["closing_debit"] -= d["closing_credit"]
- d["closing_credit"] = 0.0
-
- else:
- d["closing_credit"] -= d["closing_debit"]
- d["closing_debit"] = 0.0
-
- if d["opening_debit"] > d["opening_credit"]:
+ if d["root_type"] == "Asset" or d["root_type"] == "Equity" or d["root_type"] == "Expense":
d["opening_debit"] -= d["opening_credit"]
d["opening_credit"] = 0.0
- else:
+ if d["root_type"] == "Liability" or d["root_type"] == "Income":
d["opening_credit"] -= d["opening_debit"]
d["opening_debit"] = 0.0
+
+ if d["root_type"] == "Asset" or d["root_type"] == "Equity" or d["root_type"] == "Expense":
+ d["closing_debit"] -= d["closing_credit"]
+ d["closing_credit"] = 0.0
+
+ if d["root_type"] == "Liability" or d["root_type"] == "Income":
+ d["closing_credit"] -= d["closing_debit"]
+ d["closing_debit"] = 0.0
diff --git a/erpnext/controllers/sales_and_purchase_return.py b/erpnext/controllers/sales_and_purchase_return.py
index 372de84..ed80bd2 100644
--- a/erpnext/controllers/sales_and_purchase_return.py
+++ b/erpnext/controllers/sales_and_purchase_return.py
@@ -220,14 +220,19 @@
tax.tax_amount = -1 * tax.tax_amount
if doc.get("is_return"):
- if doc.doctype == 'Sales Invoice':
+ if doc.doctype == 'Sales Invoice':
doc.set('payments', [])
for data in source.payments:
+ paid_amount = 0.00
+ base_paid_amount = 0.00
+ data.base_amount = flt(data.amount*source.conversion_rate, source.precision("base_paid_amount"))
+ paid_amount += data.amount
+ base_paid_amount += data.base_amount
doc.append('payments', {
'mode_of_payment': data.mode_of_payment,
'type': data.type,
- 'amount': -1 * data.amount,
- 'base_amount': -1 * data.base_amount
+ 'paid_amount': -1 * paid_amount,
+ 'base_paid_amount': -1 * base_paid_amount
})
elif doc.doctype == 'Purchase Invoice':
doc.paid_amount = -1 * source.paid_amount