feat: navigate to stock ledger from batch report
diff --git a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.js b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
index b23c908..23700c9 100644
--- a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
+++ b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.js
@@ -16,6 +16,29 @@
"fieldtype": "Date",
"width": "80",
"default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname": "item",
+ "label": __("Item"),
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": "80"
}
- ]
+ ],
+ "formatter": function (value, row, column, data, default_formatter) {
+ if (column.fieldname == "Batch" && data && !!data["Batch"]) {
+ value = data["Batch"];
+ column.link_onclick = "frappe.query_reports['Batch-Wise Balance History'].set_batch_route_to_stock_ledger(" + JSON.stringify(data) + ")";
+ }
+
+ value = default_formatter(value, row, column, data);
+ return value;
+ },
+ "set_batch_route_to_stock_ledger": function (data) {
+ frappe.route_options = {
+ "batch_no": data["Batch"]
+ };
+
+ frappe.set_route("query-report", "Stock Ledger");
+ }
}
\ No newline at end of file
diff --git a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
index 7f7835f..2c95084 100644
--- a/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
+++ b/erpnext/stock/report/batch_wise_balance_history/batch_wise_balance_history.py
@@ -2,9 +2,11 @@
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
+
import frappe
from frappe import _
-from frappe.utils import flt, cint, getdate
+from frappe.utils import cint, flt, getdate
+
def execute(filters=None):
if not filters: filters = {}
@@ -17,29 +19,31 @@
data = []
for item in sorted(iwb_map):
- for wh in sorted(iwb_map[item]):
- for batch in sorted(iwb_map[item][wh]):
- qty_dict = iwb_map[item][wh][batch]
- if qty_dict.opening_qty or qty_dict.in_qty or qty_dict.out_qty or qty_dict.bal_qty:
- data.append([item, item_map[item]["item_name"], item_map[item]["description"], wh, batch,
- flt(qty_dict.opening_qty, float_precision), flt(qty_dict.in_qty, float_precision),
- flt(qty_dict.out_qty, float_precision), flt(qty_dict.bal_qty, float_precision),
- item_map[item]["stock_uom"]
- ])
+ if not filters.get("item") or filters.get("item") == item:
+ for wh in sorted(iwb_map[item]):
+ for batch in sorted(iwb_map[item][wh]):
+ qty_dict = iwb_map[item][wh][batch]
+ if qty_dict.opening_qty or qty_dict.in_qty or qty_dict.out_qty or qty_dict.bal_qty:
+ data.append([item, item_map[item]["item_name"], item_map[item]["description"], wh, batch,
+ flt(qty_dict.opening_qty, float_precision), flt(qty_dict.in_qty, float_precision),
+ flt(qty_dict.out_qty, float_precision), flt(qty_dict.bal_qty, float_precision),
+ item_map[item]["stock_uom"]
+ ])
return columns, data
+
def get_columns(filters):
"""return columns based on filters"""
columns = [_("Item") + ":Link/Item:100"] + [_("Item Name") + "::150"] + [_("Description") + "::150"] + \
- [_("Warehouse") + ":Link/Warehouse:100"] + [_("Batch") + ":Link/Batch:100"] + [_("Opening Qty") + ":Float:90"] + \
- [_("In Qty") + ":Float:80"] + [_("Out Qty") + ":Float:80"] + [_("Balance Qty") + ":Float:90"] + \
- [_("UOM") + "::90"]
-
+ [_("Warehouse") + ":Link/Warehouse:100"] + [_("Batch") + ":Link/Batch:100"] + [_("Opening Qty") + ":Float:90"] + \
+ [_("In Qty") + ":Float:80"] + [_("Out Qty") + ":Float:80"] + [_("Balance Qty") + ":Float:90"] + \
+ [_("UOM") + "::90"]
return columns
+
def get_conditions(filters):
conditions = ""
if not filters.get("from_date"):
@@ -52,7 +56,8 @@
return conditions
-#get all details
+
+# get all details
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
return frappe.db.sql("""
@@ -63,6 +68,7 @@
order by item_code, warehouse""" %
conditions, as_dict=1)
+
def get_item_warehouse_batch_map(filters, float_precision):
sle = get_stock_ledger_entries(filters)
iwb_map = {}
@@ -90,6 +96,7 @@
return iwb_map
+
def get_item_details(filters):
item_map = {}
for d in frappe.db.sql("select name, item_name, description, stock_uom from tabItem", as_dict=1):
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.js b/erpnext/stock/report/stock_ledger/stock_ledger.js
index 3fab327..df3bba5 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.js
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.js
@@ -77,7 +77,15 @@
"fieldtype": "Link",
"options": "UOM"
}
- ]
+ ],
+ "formatter": function (value, row, column, data, default_formatter) {
+ value = default_formatter(value, row, column, data);
+ if (column.fieldname == "out_qty" && data.out_qty < 0) {
+ value = "<span style='color:red'>" + value + "</span>";
+ }
+
+ return value;
+ },
}
// $(function() {
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index db7f6ad..dd53a00 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -2,9 +2,11 @@
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
+
import frappe
-from frappe import _
from erpnext.stock.utils import update_included_uom_in_report
+from frappe import _
+
def execute(filters=None):
include_uom = filters.get("include_uom")
@@ -36,7 +38,22 @@
sle.update({
"qty_after_transaction": actual_qty,
- "stock_value": stock_value
+ "stock_value": stock_value,
+ "in_qty": max(sle.actual_qty, 0),
+ "out_qty": min(sle.actual_qty, 0)
+ })
+
+ # get the name of the item that was produced using this item
+ if sle.voucher_type == "Stock Entry":
+ purpose, work_order, fg_completed_qty = frappe.db.get_value(sle.voucher_type, sle.voucher_no, ["purpose", "work_order", "fg_completed_qty"])
+
+ if purpose == "Manufacture" and work_order:
+ finished_product = frappe.db.get_value("Work Order", work_order, "item_name")
+ finished_qty = fg_completed_qty
+
+ sle.update({
+ "finished_product": finished_product,
+ "finished_qty": finished_qty,
})
data.append(sle)
@@ -47,53 +64,74 @@
update_included_uom_in_report(columns, data, include_uom, conversion_factors)
return columns, data
+
def get_columns():
columns = [
- {"label": _("Date"), "fieldname": "date", "fieldtype": "Datetime", "width": 95},
- {"label": _("Item"), "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 130},
+ {"label": _("Date"), "fieldname": "date", "fieldtype": "Datetime", "width": 150},
+ {"label": _("Item"), "fieldname": "item_code", "fieldtype": "Link", "options": "Item", "width": 100},
{"label": _("Item Name"), "fieldname": "item_name", "width": 100},
+ {"label": _("Stock UOM"), "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 90},
+ {"label": _("In Qty"), "fieldname": "in_qty", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("Out Qty"), "fieldname": "out_qty", "fieldtype": "Float", "width": 80, "convertible": "qty"},
+ {"label": _("Balance Qty"), "fieldname": "qty_after_transaction", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Finished Product"), "fieldname": "finished_product", "width": 100},
+ {"label": _("Finished Qty"), "fieldname": "finished_qty", "fieldtype": "Float", "width": 100, "convertible": "qty"},
+ {"label": _("Voucher #"), "fieldname": "voucher_no", "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 150},
+ {"label": _("Warehouse"), "fieldname": "warehouse", "fieldtype": "Link", "options": "Warehouse", "width": 150},
{"label": _("Item Group"), "fieldname": "item_group", "fieldtype": "Link", "options": "Item Group", "width": 100},
{"label": _("Brand"), "fieldname": "brand", "fieldtype": "Link", "options": "Brand", "width": 100},
{"label": _("Description"), "fieldname": "description", "width": 200},
- {"label": _("Warehouse"), "fieldname": "warehouse", "fieldtype": "Link", "options": "Warehouse", "width": 100},
- {"label": _("Stock UOM"), "fieldname": "stock_uom", "fieldtype": "Link", "options": "UOM", "width": 100},
- {"label": _("Qty"), "fieldname": "actual_qty", "fieldtype": "Float", "width": 50, "convertible": "qty"},
- {"label": _("Balance Qty"), "fieldname": "qty_after_transaction", "fieldtype": "Float", "width": 100, "convertible": "qty"},
- {"label": _("Incoming Rate"), "fieldname": "incoming_rate", "fieldtype": "Currency", "width": 110,
- "options": "Company:company:default_currency", "convertible": "rate"},
- {"label": _("Valuation Rate"), "fieldname": "valuation_rate", "fieldtype": "Currency", "width": 110,
- "options": "Company:company:default_currency", "convertible": "rate"},
- {"label": _("Balance Value"), "fieldname": "stock_value", "fieldtype": "Currency", "width": 110,
- "options": "Company:company:default_currency"},
+ {"label": _("Incoming Rate"), "fieldname": "incoming_rate", "fieldtype": "Currency", "width": 110, "options": "Company:company:default_currency", "convertible": "rate"},
+ {"label": _("Valuation Rate"), "fieldname": "valuation_rate", "fieldtype": "Currency", "width": 110, "options": "Company:company:default_currency", "convertible": "rate"},
+ {"label": _("Balance Value"), "fieldname": "stock_value", "fieldtype": "Currency", "width": 110, "options": "Company:company:default_currency"},
{"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 110},
{"label": _("Voucher #"), "fieldname": "voucher_no", "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 100},
{"label": _("Batch"), "fieldname": "batch_no", "fieldtype": "Link", "options": "Batch", "width": 100},
- {"label": _("Serial #"), "fieldname": "serial_no", "width": 100},
+ {"label": _("Serial #"), "fieldname": "serial_no", "fieldtype": "Link", "options": "Serial No", "width": 100},
{"label": _("Project"), "fieldname": "project", "fieldtype": "Link", "options": "Project", "width": 100},
{"label": _("Company"), "fieldname": "company", "fieldtype": "Link", "options": "Company", "width": 110}
]
return columns
+
def get_stock_ledger_entries(filters, items):
item_conditions_sql = ''
if items:
item_conditions_sql = 'and sle.item_code in ({})'\
.format(', '.join([frappe.db.escape(i) for i in items]))
- return frappe.db.sql("""select concat_ws(" ", posting_date, posting_time) as date,
- item_code, warehouse, actual_qty, qty_after_transaction, incoming_rate, valuation_rate,
- stock_value, voucher_type, voucher_no, batch_no, serial_no, company, project, stock_value_difference
- from `tabStock Ledger Entry` sle
- where company = %(company)s and
- posting_date between %(from_date)s and %(to_date)s
- {sle_conditions}
- {item_conditions_sql}
- order by posting_date asc, posting_time asc, creation asc"""\
- .format(
- sle_conditions=get_sle_conditions(filters),
- item_conditions_sql = item_conditions_sql
- ), filters, as_dict=1)
+ sl_entries = frappe.db.sql("""
+ SELECT
+ concat_ws(" ", posting_date, posting_time) AS date,
+ item_code,
+ warehouse,
+ actual_qty,
+ qty_after_transaction,
+ incoming_rate,
+ valuation_rate,
+ stock_value,
+ voucher_type,
+ voucher_no,
+ batch_no,
+ serial_no,
+ company,
+ project,
+ stock_value_difference
+ FROM
+ `tabStock Ledger Entry` sle
+ WHERE
+ company = %(company)s
+ AND posting_date BETWEEN %(from_date)s AND %(to_date)s
+ {sle_conditions}
+ {item_conditions_sql}
+ ORDER BY
+ posting_date asc, posting_time asc, creation asc
+ """.format(sle_conditions=get_sle_conditions(filters), item_conditions_sql=item_conditions_sql),
+ filters, as_dict=1)
+
+ return sl_entries
+
def get_items(filters):
conditions = []
@@ -111,6 +149,7 @@
.format(" and ".join(conditions)), filters)
return items
+
def get_item_details(items, sl_entries, include_uom):
item_details = {}
if not items:
@@ -140,6 +179,7 @@
return item_details
+
def get_sle_conditions(filters):
conditions = []
if filters.get("warehouse"):
@@ -155,6 +195,7 @@
return "and {}".format(" and ".join(conditions)) if conditions else ""
+
def get_opening_balance(filters, columns):
if not (filters.item_code and filters.warehouse and filters.from_date):
return
@@ -166,13 +207,17 @@
"posting_date": filters.from_date,
"posting_time": "00:00:00"
})
- row = {}
- row["item_code"] = _("'Opening'")
- for dummy, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'), (12, 'stock_value')):
- row[v] = last_entry.get(v, 0)
+
+ row = {
+ "item_code": _("'Opening'"),
+ "qty_after_transaction": last_entry.get("qty_after_transaction", 0),
+ "valuation_rate": last_entry.get("valuation_rate", 0),
+ "stock_value": last_entry.get("stock_value", 0)
+ }
return row
+
def get_warehouse_condition(warehouse):
warehouse_details = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"], as_dict=1)
if warehouse_details:
@@ -182,6 +227,7 @@
return ''
+
def get_item_group_condition(item_group):
item_group_details = frappe.db.get_value("Item Group", item_group, ["lft", "rgt"], as_dict=1)
if item_group_details: