[minor] stock ledger report as script report
Issue #1121
diff --git a/patches/patch_list.py b/patches/patch_list.py
index 52277a8..437f322 100644
--- a/patches/patch_list.py
+++ b/patches/patch_list.py
@@ -255,4 +255,5 @@
"patches.1311.p05_website_brand_html",
"patches.1311.p06_fix_report_columns",
"execute:webnotes.delete_doc('DocType', 'Documentation Tool')",
+ "execute:webnotes.delete_doc('Report', 'Stock Ledger') #2013-11-29",
]
\ No newline at end of file
diff --git a/stock/page/stock_home/stock_home.js b/stock/page/stock_home/stock_home.js
index cbfc3fc..4be5a46 100644
--- a/stock/page/stock_home/stock_home.js
+++ b/stock/page/stock_home/stock_home.js
@@ -138,7 +138,8 @@
items: [
{
"label":wn._("Stock Ledger"),
- page: "stock-ledger"
+ doctype: "Delivery Note",
+ route: "query-report/Stock Ledger"
},
{
"label":wn._("Stock Balance"),
@@ -171,11 +172,6 @@
icon: "icon-list",
items: [
{
- "label":wn._("Stock Ledger"),
- route: "Report/Stock Ledger Entry/Stock Ledger",
- doctype: "Stock Ledger Entry"
- },
- {
"label":wn._("Ordered Items To Be Delivered"),
route: "query-report/Ordered Items To Be Delivered",
doctype: "Delivery Note"
diff --git a/stock/report/stock_ledger/stock_ledger.js b/stock/report/stock_ledger/stock_ledger.js
new file mode 100644
index 0000000..0e323eb
--- /dev/null
+++ b/stock/report/stock_ledger/stock_ledger.js
@@ -0,0 +1,58 @@
+// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+// License: GNU General Public License v3. See license.txt
+
+wn.query_reports["Stock Ledger"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": wn._("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": wn.defaults.get_user_default("company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_date",
+ "label": wn._("From Date"),
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_start_date"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_date",
+ "label": wn._("To Date"),
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_end_date"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"warehouse",
+ "label": wn._("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse"
+ },
+ {
+ "fieldname":"item_code",
+ "label": wn._("Item"),
+ "fieldtype": "Link",
+ "options": "Item"
+ },
+ {
+ "fieldname":"brand",
+ "label": wn._("Brand"),
+ "fieldtype": "Link",
+ "options": "Brand"
+ },
+ {
+ "fieldname":"voucher_no",
+ "label": wn._("Voucher #"),
+ "fieldtype": "Data"
+ }
+ ]
+}
+
+// $(function() {
+// $(wrapper).bind("show", function() {
+// wn.query_report.load();
+// });
+// });
\ No newline at end of file
diff --git a/stock/report/stock_ledger/stock_ledger.py b/stock/report/stock_ledger/stock_ledger.py
new file mode 100644
index 0000000..3ae9135
--- /dev/null
+++ b/stock/report/stock_ledger/stock_ledger.py
@@ -0,0 +1,49 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import webnotes
+
+def execute(filters=None):
+ columns = ["Date:Datetime:95", "Item:Link/Item:100", "Item Name::100",
+ "Item Group:Link/Item Group:100", "Brand:Link/Brand:100",
+ "Description::200", "Warehouse:Link/Warehouse:100",
+ "Stock UOM:Link/UOM:100", "Qty:Float:50", "Balance Qty:Float:80",
+ "Balance Value:Currency:100", "Voucher Type::100", "Voucher #::100",
+ "Batch:Link/Batch:100", "Serial #:Link/Serial No:100", "Company:Link/Company:100"]
+
+ data = webnotes.conn.sql("""select concat_ws(" ", posting_date, posting_time),
+ item.name, item.item_name, item.item_group, brand, description, warehouse, sle.stock_uom,
+ actual_qty, qty_after_transaction, stock_value, voucher_type, voucher_no,
+ batch_no, serial_no, company
+ from `tabStock Ledger Entry` sle,
+ (select name, item_name, description, stock_uom, brand, item_group
+ from `tabItem` {item_conditions}) item
+ where item_code = item.name and
+ company = %(company)s and
+ posting_date between %(from_date)s and %(to_date)s
+ {sle_conditions}
+ order by posting_date desc, posting_time desc, sle.name desc"""\
+ .format(item_conditions=get_item_conditions(filters),
+ sle_conditions=get_sle_conditions(filters)),
+ filters)
+
+ return columns, data
+
+def get_item_conditions(filters):
+ conditions = []
+ if filters.get("item_code"):
+ conditions.append("item_code=%(item_code)s")
+ if filters.get("brand"):
+ conditions.append("brand=%(brand)s")
+
+ return "where {}".format(" and ".join(conditions)) if conditions else ""
+
+def get_sle_conditions(filters):
+ conditions = []
+ if filters.get("warehouse"):
+ conditions.append("warehouse=%(warehouse)s")
+ if filters.get("voucher_no"):
+ conditions.append("voucher_no=%(voucher_no)s")
+
+ return "and {}".format(" and ".join(conditions)) if conditions else ""
\ No newline at end of file
diff --git a/stock/report/stock_ledger/stock_ledger.txt b/stock/report/stock_ledger/stock_ledger.txt
index a40be1d..aadf323 100644
--- a/stock/report/stock_ledger/stock_ledger.txt
+++ b/stock/report/stock_ledger/stock_ledger.txt
@@ -1,19 +1,18 @@
[
{
- "creation": "2013-01-14 15:26:21",
+ "creation": "2013-11-29 17:08:23",
"docstatus": 0,
- "modified": "2013-08-20 11:53:43",
+ "modified": "2013-11-29 17:28:15",
"modified_by": "Administrator",
"owner": "Administrator"
},
{
"doctype": "Report",
"is_standard": "Yes",
- "json": "{\"filters\":[],\"columns\":[[\"item_code\",\"Stock Ledger Entry\"],[\"warehouse\",\"Stock Ledger Entry\"],[\"posting_date\",\"Stock Ledger Entry\"],[\"posting_time\",\"Stock Ledger Entry\"],[\"actual_qty\",\"Stock Ledger Entry\"],[\"qty_after_transaction\",\"Stock Ledger Entry\"],[\"voucher_type\",\"Stock Ledger Entry\"],[\"voucher_no\",\"Stock Ledger Entry\"]],\"sort_by\":\"Stock Ledger Entry.posting_date\",\"sort_order\":\"desc\",\"sort_by_next\":\"Stock Ledger Entry.posting_time\",\"sort_order_next\":\"desc\"}",
"name": "__common__",
"ref_doctype": "Stock Ledger Entry",
"report_name": "Stock Ledger",
- "report_type": "Report Builder"
+ "report_type": "Script Report"
},
{
"doctype": "Report",
diff --git a/stock/stock_ledger.py b/stock/stock_ledger.py
index 469fa53..8702744 100644
--- a/stock/stock_ledger.py
+++ b/stock/stock_ledger.py
@@ -1,5 +1,6 @@
# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
+from __future__ import unicode_literals
import webnotes
from webnotes import msgprint