Rewritten General Ledger report with grouping functions
diff --git a/accounts/report/general_ledger/general_ledger.js b/accounts/report/general_ledger/general_ledger.js
index 7985277..f2e60b6 100644
--- a/accounts/report/general_ledger/general_ledger.js
+++ b/accounts/report/general_ledger/general_ledger.js
@@ -12,26 +12,6 @@
"reqd": 1
},
{
- "fieldname":"account",
- "label": wn._("Account"),
- "fieldtype": "Link",
- "options": "Account"
- },
- {
- "fieldname":"voucher_no",
- "label": wn._("Voucher No"),
- "fieldtype": "Data",
- },
- {
- "fieldname":"group_by",
- "label": wn._("Group by"),
- "fieldtype": "Select",
- "options": "\nGroup by Account\nGroup by Voucher"
- },
- {
- "fieldtype": "Break",
- },
- {
"fieldname":"from_date",
"label": wn._("From Date"),
"fieldtype": "Date",
@@ -46,6 +26,40 @@
"default": wn.datetime.get_today(),
"reqd": 1,
"width": "60px"
+ },
+ {
+ "fieldtype": "Break",
+ },
+ {
+ "fieldname":"account",
+ "label": wn._("Account"),
+ "fieldtype": "Link",
+ "options": "Account",
+ "get_query": function() {
+ var company = wn.query_report.filters_by_name.company.get_value();
+ return {
+ "query": "accounts.utils.get_account_list",
+ "filters": {
+ "company": company,
+ }
+ }
+ }
+ },
+ {
+ "fieldname":"voucher_no",
+ "label": wn._("Voucher No"),
+ "fieldtype": "Data",
+ },
+ {
+ "fieldname":"group_by_voucher",
+ "label": wn._("Group by Voucher"),
+ "fieldtype": "Check",
+ "default": 1
+ },
+ {
+ "fieldname":"group_by_account",
+ "label": wn._("Group by Account"),
+ "fieldtype": "Check",
}
]
}
\ No newline at end of file
diff --git a/accounts/report/general_ledger/general_ledger.py b/accounts/report/general_ledger/general_ledger.py
index b88d5bc..2efc824 100644
--- a/accounts/report/general_ledger/general_ledger.py
+++ b/accounts/report/general_ledger/general_ledger.py
@@ -3,109 +3,170 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import flt, add_days
+from webnotes.utils import cstr, flt
from webnotes import _
-from accounts.utils import get_balance_on
def execute(filters=None):
- account_details = webnotes.conn.get_value("Account", filters["account"],
- ["debit_or_credit", "group_or_ledger"], as_dict=True) if filters.get("account") else None
+ account_details = {}
+ for acc in webnotes.conn.sql("""select name, debit_or_credit, group_or_ledger
+ from tabAccount""", as_dict=1):
+ account_details.setdefault(acc.name, acc)
+
validate_filters(filters, account_details)
columns = get_columns()
- data = []
- if filters.get("group_by"):
- data += get_grouped_gle(filters)
- else:
- data += get_gl_entries(filters)
- if data:
- data.append(get_total_row(data))
+
+ res = get_result(filters, account_details)
- if account_details:
- data = [get_opening_balance_row(filters, account_details.debit_or_credit)] + data + \
- [get_closing_balance_row(filters, account_details.debit_or_credit)]
-
- return columns, data
+ return columns, res
def validate_filters(filters, account_details):
- if account_details and account_details.group_or_ledger == "Ledger" \
- and filters.get("group_by") == "Group by Account":
+ if filters.get("account") and filters.get("group_by_account") \
+ and account_details[filters.account].group_or_ledger == "Ledger":
webnotes.throw(_("Can not filter based on Account, if grouped by Account"))
- if filters.get("voucher_no") and filters.get("group_by") == "Group by Voucher":
+ if filters.get("voucher_no") and filters.get("group_by_voucher"):
webnotes.throw(_("Can not filter based on Voucher No, if grouped by Voucher"))
+
+ if filters.from_date > filters.to_date:
+ webnotes.throw(_("From Date must be before To Date"))
def get_columns():
return ["Posting Date:Date:100", "Account:Link/Account:200", "Debit:Float:100",
"Credit:Float:100", "Voucher Type::120", "Voucher No::160", "Link::20",
- "Cost Center:Link/Cost Center:100", "Remarks::200"]
+ "Against Account::120", "Cost Center:Link/Cost Center:100", "Remarks::200"]
-def get_opening_balance_row(filters, debit_or_credit):
- opening_balance = get_balance_on(filters["account"], add_days(filters["from_date"], -1))
- return get_balance_row(opening_balance, debit_or_credit, "Opening Balance")
-
-def get_closing_balance_row(filters, debit_or_credit):
- closing_balance = get_balance_on(filters["account"], filters["to_date"])
- return get_balance_row(closing_balance, debit_or_credit, "Closing Balance")
-
-def get_balance_row(balance, debit_or_credit, balance_label):
- if debit_or_credit == "Debit":
- return ["", balance_label, balance, 0.0, "", "", ""]
- else:
- return ["", balance_label, 0.0, balance, "", "", ""]
+def get_result(filters, account_details):
+ gl_entries = get_gl_entries(filters)
+
+ data = get_data_with_opening_closing(filters, account_details, gl_entries)
+ result = get_result_as_list(data)
+
+ return result
+
def get_gl_entries(filters):
- gl_entries = webnotes.conn.sql("""select
- posting_date, account, debit, credit, voucher_type, voucher_no, cost_center, remarks
- from `tabGL Entry`
- where company=%(company)s
- and posting_date between %(from_date)s and %(to_date)s
- {conditions}
- order by posting_date, account"""\
- .format(conditions=get_conditions(filters)), filters, as_list=1)
+ group_by_condition = "group by voucher_type, voucher_no, account" \
+ if filters.get("group_by_voucher") else "group by name"
- for d in gl_entries:
- icon = """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \
- % ("/".join(["#Form", d[4], d[5]]),)
- d.insert(6, icon)
+ gl_entries = webnotes.conn.sql("""select posting_date, account,
+ sum(ifnull(debit, 0)) as debit, sum(ifnull(credit, 0)) as credit,
+ voucher_type, voucher_no, cost_center, remarks, is_advance, against
+ from `tabGL Entry`
+ where company=%(company)s {conditions}
+ {group_by_condition}
+ order by posting_date, account"""\
+ .format(conditions=get_conditions(filters), group_by_condition=group_by_condition),
+ filters, as_dict=1)
return gl_entries
-
+
def get_conditions(filters):
conditions = []
if filters.get("account"):
lft, rgt = webnotes.conn.get_value("Account", filters["account"], ["lft", "rgt"])
conditions.append("""account in (select name from tabAccount
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
+ else:
+ conditions.append("posting_date between %(from_date)s and %(to_date)s")
+
if filters.get("voucher_no"):
conditions.append("voucher_no=%(voucher_no)s")
return "and {}".format(" and ".join(conditions)) if conditions else ""
-
-def get_grouped_gle(filters):
- gle_map = {}
- gle = get_gl_entries(filters)
- for d in gle:
- gle_map.setdefault(d[1 if filters["group_by"]=="Group by Account" else 5], []).append(d)
-
+
+def get_data_with_opening_closing(filters, account_details, gl_entries):
data = []
- for entries in gle_map.values():
- subtotal_debit = subtotal_credit = 0.0
- for entry in entries:
- data.append(entry)
- subtotal_debit += flt(entry[2])
- subtotal_credit += flt(entry[3])
-
- data.append(["", "Total", subtotal_debit, subtotal_credit, "", "", ""])
+ gle_map = initialize_gle_map(gl_entries)
- if data:
- data.append(get_total_row(gle))
+ opening, total_debit, total_credit, gle_map = get_accountwise_gle(filters, gl_entries, gle_map)
+
+ # Opening for filtered account
+ if filters.get("account"):
+ data += [get_balance_row("Opening", account_details[filters.account].debit_or_credit,
+ opening), {}]
+
+ for acc, acc_dict in gle_map.items():
+ if acc_dict.entries:
+ # Opening for individual ledger, if grouped by account
+ if filters.get("group_by_account"):
+ data.append(get_balance_row("Opening", account_details[acc].debit_or_credit,
+ acc_dict.opening))
+
+ data += acc_dict.entries
+
+ # Totals and closing for individual ledger, if grouped by account
+ if filters.get("group_by_account"):
+ data += [{"account": "Totals", "debit": acc_dict.total_debit,
+ "credit": acc_dict.total_credit},
+ get_balance_row("Closing (Opening + Totals)",
+ account_details[acc].debit_or_credit, (acc_dict.opening
+ + acc_dict.total_debit - acc_dict.total_credit)), {}]
+
+ # Total debit and credit between from and to date
+ if total_debit or total_credit:
+ data.append({"account": "Totals", "debit": total_debit, "credit": total_credit})
+
+ # Closing for filtered account
+ if filters.get("account"):
+ data.append(get_balance_row("Closing (Opening + Totals)",
+ account_details[filters.account].debit_or_credit,
+ (opening + total_debit - total_credit)))
+
return data
+
+def initialize_gle_map(gl_entries):
+ gle_map = webnotes._dict()
+ for gle in gl_entries:
+ gle_map.setdefault(gle.account, webnotes._dict({
+ "opening": 0,
+ "entries": [],
+ "total_debit": 0,
+ "total_credit": 0,
+ "closing": 0
+ }))
+ return gle_map
+
+def get_accountwise_gle(filters, gl_entries, gle_map):
+ opening, total_debit, total_credit = 0, 0, 0
-def get_total_row(gle):
- total_debit = total_credit = 0.0
- for d in gle:
- total_debit += flt(d[2])
- total_credit += flt(d[3])
+ for gle in gl_entries:
+ amount = flt(gle.debit) - flt(gle.credit)
+ if filters.get("account") and (gle.posting_date < filters.from_date
+ or cstr(gle.is_advance) == "Yes"):
+ gle_map[gle.account].opening += amount
+ opening += amount
+ elif gle.posting_date < filters.to_date:
+ gle_map[gle.account].entries.append(gle)
+ gle_map[gle.account].total_debit += flt(gle.debit)
+ gle_map[gle.account].total_credit += flt(gle.credit)
+
+ total_debit += flt(gle.debit)
+ total_credit += flt(gle.credit)
+
+ return opening, total_debit, total_credit, gle_map
+
+def get_balance_row(label, debit_or_credit, balance):
+ return {
+ "account": label,
+ "debit": balance if debit_or_credit=="Debit" else 0,
+ "credit": -1*balance if debit_or_credit=="Credit" else 0,
+ }
+
+def get_result_as_list(data):
+ result = []
+ for d in data:
+ result.append([d.get("posting_date"), d.get("account"), d.get("debit"),
+ d.get("credit"), d.get("voucher_type"), d.get("voucher_no"),
+ get_voucher_link(d.get("voucher_type"), d.get("voucher_no")),
+ d.get("against"), d.get("cost_center"), d.get("remarks")])
+
+ return result
+
+def get_voucher_link(voucher_type, voucher_no):
+ icon = ""
+ if voucher_type and voucher_no:
+ icon = """<a href="%s"><i class="icon icon-share" style="cursor: pointer;">
+ </i></a>""" % ("/".join(["#Form", voucher_type, voucher_no]))
- return ["", "Total Debit/Credit", total_debit, total_credit, "", "", ""]
\ No newline at end of file
+ return icon
\ No newline at end of file