[report] Trial Balance for Party
diff --git a/erpnext/accounts/report/trial_balance_for_party/__init__.py b/erpnext/accounts/report/trial_balance_for_party/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/trial_balance_for_party/__init__.py
diff --git a/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.js b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.js
new file mode 100644
index 0000000..45d7bc0
--- /dev/null
+++ b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.js
@@ -0,0 +1,59 @@
+// Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Trial Balance for Party"] = {
+ "filters": [
+ {
+ "fieldname": "company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("company"),
+ "reqd": 1
+ },
+ {
+ "fieldname": "fiscal_year",
+ "label": __("Fiscal Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1,
+ "on_change": function(query_report) {
+ var fiscal_year = query_report.get_values().fiscal_year;
+ if (!fiscal_year) {
+ return;
+ }
+ frappe.model.with_doc("Fiscal Year", fiscal_year, function(r) {
+ var fy = frappe.model.get_doc("Fiscal Year", fiscal_year);
+ query_report.filters_by_name.from_date.set_input(fy.year_start_date);
+ query_report.filters_by_name.to_date.set_input(fy.year_end_date);
+ query_report.trigger_refresh();
+ });
+ }
+ },
+ {
+ "fieldname": "from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_start_date"),
+ },
+ {
+ "fieldname": "to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_end_date"),
+ },
+ {
+ "fieldname":"party_type",
+ "label": __("Party Type"),
+ "fieldtype": "Select",
+ "options": ["Customer", "Supplier"],
+ "default": "Customer"
+ },
+ {
+ "fieldname": "show_zero_values",
+ "label": __("Show zero values"),
+ "fieldtype": "Check"
+ }
+ ]
+}
diff --git a/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.json b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.json
new file mode 100644
index 0000000..630462d
--- /dev/null
+++ b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.json
@@ -0,0 +1,17 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2015-09-22 10:28:45.762272",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "modified": "2015-09-22 10:28:45.762272",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Trial Balance for Party",
+ "owner": "Administrator",
+ "ref_doctype": "GL Entry",
+ "report_name": "Trial Balance for Party",
+ "report_type": "Script Report"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.py b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.py
new file mode 100644
index 0000000..2a47ce8
--- /dev/null
+++ b/erpnext/accounts/report/trial_balance_for_party/trial_balance_for_party.py
@@ -0,0 +1,195 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import flt, cint
+from erpnext.accounts.report.trial_balance.trial_balance import validate_filters
+
+
+def execute(filters=None):
+ validate_filters(filters)
+
+ show_party_name = is_party_name_visible(filters)
+
+ columns = get_columns(filters, show_party_name)
+ data = get_data(filters, show_party_name)
+
+ return columns, data
+
+def get_data(filters, show_party_name):
+ party_name_field = "customer_name" if filters.get("party_type")=="Customer" else "supplier_name"
+ parties = frappe.get_all(filters.get("party_type"), fields = ["name", party_name_field], order_by="name")
+
+ opening_balances = get_opening_balances(filters)
+ balances_within_period = get_balances_within_period(filters)
+
+ data = []
+ total_debit, total_credit = 0, 0
+ for party in parties:
+ row = { "party": party.name }
+ if show_party_name:
+ row["party_name"] = party.get(party_name_field)
+
+ # opening
+ opening_debit, opening_credit = opening_balances.get(party.name, [0, 0])
+ row.update({
+ "opening_debit": opening_debit,
+ "opening_credit": opening_credit
+ })
+
+ # within period
+ debit, credit = balances_within_period.get(party.name, [0, 0])
+ row.update({
+ "debit": debit,
+ "credit": credit
+ })
+
+ # totals
+ total_debit += debit
+ total_credit += credit
+
+ # closing
+ closing_debit, closing_credit = toggle_debit_credit(opening_debit + debit, opening_credit + credit)
+ row.update({
+ "closing_debit": closing_debit,
+ "closing_credit": closing_credit
+ })
+
+ has_value = False
+ if (opening_debit or opening_credit or debit or credit or closing_debit or closing_credit):
+ has_value =True
+
+ if cint(filters.show_zero_values) or has_value:
+ data.append(row)
+
+ # Add total row
+ if total_debit or total_credit:
+ data.append({
+ "party": "'" + _("Totals") + "'",
+ "debit": total_debit,
+ "credit": total_credit
+ })
+
+ return data
+
+def get_opening_balances(filters):
+ gle = frappe.db.sql("""
+ select party, sum(ifnull(debit, 0)) as opening_debit, sum(ifnull(credit, 0)) as opening_credit
+ from `tabGL Entry`
+ where company=%(company)s
+ and ifnull(party_type, '') = %(party_type)s and ifnull(party, '') != ''
+ and (posting_date < %(from_date)s or ifnull(is_opening, 'No') = 'Yes')
+ group by party""", {
+ "company": filters.company,
+ "from_date": filters.from_date,
+ "party_type": filters.party_type
+ }, as_dict=True)
+
+ opening = frappe._dict()
+ for d in gle:
+ opening_debit, opening_credit = toggle_debit_credit(d.opening_debit, d.opening_credit)
+ opening.setdefault(d.party, [opening_debit, opening_credit])
+
+ return opening
+
+def get_balances_within_period(filters):
+ gle = frappe.db.sql("""
+ select party, sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit
+ from `tabGL Entry`
+ where company=%(company)s
+ and ifnull(party_type, '') = %(party_type)s and ifnull(party, '') != ''
+ and posting_date >= %(from_date)s and posting_date <= %(to_date)s
+ and ifnull(is_opening, 'No') = 'No'
+ group by party""", {
+ "company": filters.company,
+ "from_date": filters.from_date,
+ "to_date": filters.to_date,
+ "party_type": filters.party_type
+ }, as_dict=True)
+
+ balances_within_period = frappe._dict()
+ for d in gle:
+ balances_within_period.setdefault(d.party, [d.debit, d.credit])
+
+ return balances_within_period
+
+def toggle_debit_credit(debit, credit):
+ if flt(debit) > flt(credit):
+ debit = flt(debit) - flt(credit)
+ credit = 0.0
+ else:
+ credit = flt(credit) - flt(debit)
+ debit = 0.0
+
+ return debit, credit
+
+def get_columns(filters, show_party_name):
+ columns = [
+ {
+ "fieldname": "party",
+ "label": _(filters.party_type),
+ "fieldtype": "Link",
+ "options": filters.party_type,
+ "width": 200
+ },
+ {
+ "fieldname": "opening_debit",
+ "label": _("Opening (Dr)"),
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "fieldname": "opening_credit",
+ "label": _("Opening (Cr)"),
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "fieldname": "debit",
+ "label": _("Debit"),
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "fieldname": "credit",
+ "label": _("Credit"),
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "fieldname": "closing_debit",
+ "label": _("Closing (Dr)"),
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "fieldname": "closing_credit",
+ "label": _("Closing (Cr)"),
+ "fieldtype": "Currency",
+ "width": 120
+ }
+ ]
+
+ if show_party_name:
+ columns.insert(1, {
+ "fieldname": "party_name",
+ "label": _(filters.party_type) + " Name",
+ "fieldtype": "Data",
+ "width": 200
+ })
+
+ return columns
+
+def is_party_name_visible(filters):
+ show_party_name = False
+ if filters.get("party_type") == "Customer":
+ party_naming_by = frappe.db.get_single_value("Selling Settings", "cust_master_name")
+ else:
+ party_naming_by = frappe.db.get_single_value("Buying Settings", "supp_master_name")
+
+ if party_naming_by == "Naming Series":
+ show_party_name = True
+
+ return show_party_name
\ No newline at end of file
diff --git a/erpnext/change_log/current/trial_balance_for_party.md b/erpnext/change_log/current/trial_balance_for_party.md
new file mode 100644
index 0000000..3d93e25
--- /dev/null
+++ b/erpnext/change_log/current/trial_balance_for_party.md
@@ -0,0 +1 @@
+- Trial Balance for Customer and Supplier
\ No newline at end of file
diff --git a/erpnext/config/accounts.py b/erpnext/config/accounts.py
index 71a2d70..7fca256 100644
--- a/erpnext/config/accounts.py
+++ b/erpnext/config/accounts.py
@@ -189,6 +189,12 @@
},
{
"type": "report",
+ "name": "Trial Balance for Party",
+ "doctype": "GL Entry",
+ "is_query_report": True,
+ },
+ {
+ "type": "report",
"name": "Gross Profit",
"doctype": "Sales Invoice",
"is_query_report": True