[reports] general ledger: grid report to script report
diff --git a/accounts/page/accounts_home/accounts_home.js b/accounts/page/accounts_home/accounts_home.js
index 69c7da1..db7a20c 100644
--- a/accounts/page/accounts_home/accounts_home.js
+++ b/accounts/page/accounts_home/accounts_home.js
@@ -157,7 +157,8 @@
items: [
{
"label":wn._("General Ledger"),
- page: "general-ledger"
+ doctype: "GL Entry",
+ route: "query-report/General Ledger"
},
{
"label":wn._("Trial Balance"),
diff --git a/accounts/report/general_ledger/general_ledger.js b/accounts/report/general_ledger/general_ledger.js
index b0cd485..7985277 100644
--- a/accounts/report/general_ledger/general_ledger.js
+++ b/accounts/report/general_ledger/general_ledger.js
@@ -23,6 +23,12 @@
"fieldtype": "Data",
},
{
+ "fieldname":"group_by",
+ "label": wn._("Group by"),
+ "fieldtype": "Select",
+ "options": "\nGroup by Account\nGroup by Voucher"
+ },
+ {
"fieldtype": "Break",
},
{
@@ -40,12 +46,6 @@
"default": wn.datetime.get_today(),
"reqd": 1,
"width": "60px"
- },
- {
- "fieldname":"group_by",
- "label": wn._("Group by"),
- "fieldtype": "Select",
- "options": "\nAccount\nVoucher"
}
]
}
\ 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 23c79d8..fcf8010 100644
--- a/accounts/report/general_ledger/general_ledger.py
+++ b/accounts/report/general_ledger/general_ledger.py
@@ -4,16 +4,28 @@
from __future__ import unicode_literals
import webnotes
from webnotes.utils import flt
+from webnotes import _
def execute(filters=None):
+ validate_filters(filters)
columns = get_columns()
+
if filters.get("group_by"):
data = get_grouped_gle(filters)
else:
data = get_gl_entries(filters)
+ if data:
+ data.append(get_total_row(data))
return columns, data
+def validate_filters(filters):
+ if filters.get("account") and filters.get("group_by") == "Group by Account":
+ 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":
+ webnotes.throw(_("Can not filter based on Voucher No, if grouped by Voucher"))
+
def get_columns():
return ["Posting Date:Date:100", "Account:Link/Account:200", "Debit:Currency:100",
"Credit:Currency:100", "Voucher Type::120", "Voucher No::160",
@@ -27,24 +39,41 @@
and posting_date between %(from_date)s and %(to_date)s
{conditions}
order by posting_date, account"""\
- .format(conditions=get_conditions(filters)), filters)
+ .format(conditions=get_conditions(filters)), filters, as_list=1)
def get_conditions(filters):
- return " and account=%(account)s" if filters.get("account") else ""
+ conditions = []
+ if filters.get("account"):
+ conditions.append("account=%(account)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"]=="Account" else 5], []).append(d)
+ gle_map.setdefault(d[1 if filters["group_by"]=="Group by Account" else 5], []).append(d)
data = []
for entries in gle_map.values():
- total_debit = total_credit = 0.0
+ subtotal_debit = subtotal_credit = 0.0
for entry in entries:
data.append(entry)
- total_debit += flt(entry[2])
- total_credit += flt(entry[3])
-
- data.append(["", "Total", total_debit, total_credit, "", "", ""])
- return data
\ No newline at end of file
+ subtotal_debit += flt(entry[2])
+ subtotal_credit += flt(entry[3])
+
+ data.append(["", "Total", subtotal_debit, subtotal_credit, "", "", ""])
+
+ if data:
+ data.append(get_total_row(gle))
+ return data
+
+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])
+
+ return ["", "Total Debit/Credit", total_debit, total_credit, "", "", ""]
\ No newline at end of file
diff --git a/stock/report/stock_ageing/stock_ageing.py b/stock/report/stock_ageing/stock_ageing.py
index fa05205..defe724 100644
--- a/stock/report/stock_ageing/stock_ageing.py
+++ b/stock/report/stock_ageing/stock_ageing.py
@@ -20,8 +20,8 @@
earliest_age = date_diff(to_date, fifo_queue[0][1])
latest_age = date_diff(to_date, fifo_queue[-1][1])
- data.append([item, details.item_name, details.description, details.brand,
- average_age, earliest_age, latest_age, details.stock_uom])
+ data.append([item, details.item_name, details.description, details.item_group,
+ details.brand, average_age, earliest_age, latest_age, details.stock_uom])
return columns, data
@@ -36,8 +36,8 @@
def get_columns():
return ["Item Code:Link/Item:100", "Item Name::100", "Description::200",
- "Brand:Link/Brand:100", "Average Age:Float:100", "Earliest:Int:80",
- "Latest:Int:80", "UOM:Link/UOM:100"]
+ "Item Group:Link/Item Group:100", "Brand:Link/Brand:100", "Average Age:Float:100",
+ "Earliest:Int:80", "Latest:Int:80", "UOM:Link/UOM:100"]
def get_fifo_queue(filters):
item_details = {}
@@ -64,7 +64,8 @@
def get_stock_ledger_entries(filters):
return webnotes.conn.sql("""select
- item.name, item.item_name, brand, description, item.stock_uom, actual_qty, posting_date
+ item.name, item.item_name, item_group, brand, description, item.stock_uom,
+ actual_qty, posting_date
from `tabStock Ledger Entry` sle,
(select name, item_name, description, stock_uom, brand
from `tabItem` {item_conditions}) item
diff --git a/stock/report/stock_projected_qty/stock_projected_qty.py b/stock/report/stock_projected_qty/stock_projected_qty.py
index 232c744..11d5195 100644
--- a/stock/report/stock_projected_qty/stock_projected_qty.py
+++ b/stock/report/stock_projected_qty/stock_projected_qty.py
@@ -8,13 +8,14 @@
columns = get_columns()
data = webnotes.conn.sql("""select
- item.name, item.item_name, description, brand, warehouse, item.stock_uom,
+ item.name, item.item_name, description, item_group, brand, warehouse, item.stock_uom,
actual_qty, planned_qty, indented_qty, ordered_qty, reserved_qty,
projected_qty, item.re_order_level, item.re_order_qty
from `tabBin` bin,
(select name, company from tabWarehouse
where company=%(company)s {warehouse_conditions}) wh,
- (select name, item_name, description, stock_uom, brand, re_order_level, re_order_qty
+ (select name, item_name, description, stock_uom, item_group,
+ brand, re_order_level, re_order_qty
from `tabItem` {item_conditions}) item
where item_code = item.name and warehouse = wh.name
order by item.name, wh.name"""\