fix: Stock Analytics and Warehouse wise Item Balance Age and Value issue
diff --git a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js
index 6e90884..5c807a8 100644
--- a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js
+++ b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.js
@@ -4,6 +4,7 @@
frappe.ui.form.on("Closing Stock Balance", {
refresh(frm) {
frm.trigger("generate_closing_balance");
+ frm.trigger("regenerate_closing_balance");
},
generate_closing_balance(frm) {
@@ -19,5 +20,20 @@
})
})
}
+ },
+
+ regenerate_closing_balance(frm) {
+ if (frm.doc.status == "Completed") {
+ frm.add_custom_button(__("Regenerate Closing Stock Balance"), () => {
+ frm.call({
+ method: "regenerate_closing_balance",
+ doc: frm.doc,
+ freeze: true,
+ callback: () => {
+ frm.reload_doc();
+ }
+ })
+ })
+ }
}
});
diff --git a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py
index 1fbba27..a796372 100644
--- a/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py
+++ b/erpnext/stock/doctype/closing_stock_balance/closing_stock_balance.py
@@ -4,6 +4,7 @@
import json
import frappe
+from frappe import _
from frappe.core.doctype.prepared_report.prepared_report import create_json_gz_file
from frappe.desk.form.load import get_attachments
from frappe.model.document import Document
@@ -57,7 +58,7 @@
if query and query[0].name:
name = get_link_to_form("Closing Stock Balance", query[0].name)
msg = f"Closing Stock Balance {name} already exists for the selected date range"
- frappe.throw(msg, title="Duplicate Closing Stock Balance")
+ frappe.throw(_(msg), title=_("Duplicate Closing Stock Balance"))
def on_submit(self):
self.set_status(save=True)
@@ -65,11 +66,23 @@
def on_cancel(self):
self.set_status(save=True)
+ self.clear_attachment()
@frappe.whitelist()
def enqueue_job(self):
+ self.db_set("status", "In Progress")
+ self.clear_attachment()
enqueue(prepare_closing_stock_balance, name=self.name, queue="long", timeout=1500)
+ @frappe.whitelist()
+ def regenerate_closing_balance(self):
+ self.enqueue_job()
+
+ def clear_attachment(self):
+ if attachments := get_attachments(self.doctype, self.name):
+ attachment = attachments[0]
+ frappe.delete_doc("File", attachment.name)
+
def create_closing_stock_balance_entries(self):
columns, data = execute(
filters=frappe._dict(
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index 2fa97ae..d3f1f31 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -281,7 +281,7 @@
# consume transfer data and add stock to fifo queue
self.__adjust_incoming_transfer_qty(transfer_data, fifo_queue, row)
else:
- if not serial_nos:
+ if not serial_nos and not row.get("has_serial_no"):
if fifo_queue and flt(fifo_queue[0][0]) <= 0:
# neutralize 0/negative stock by adding positive stock
fifo_queue[0][0] += flt(row.actual_qty)
diff --git a/erpnext/stock/report/stock_analytics/stock_analytics.py b/erpnext/stock/report/stock_analytics/stock_analytics.py
index 27b94ab..6c5b58c 100644
--- a/erpnext/stock/report/stock_analytics/stock_analytics.py
+++ b/erpnext/stock/report/stock_analytics/stock_analytics.py
@@ -5,15 +5,13 @@
import frappe
from frappe import _, scrub
+from frappe.query_builder.functions import CombineDatetime
from frappe.utils import get_first_day as get_first_day_of_month
from frappe.utils import get_first_day_of_week, get_quarter_start, getdate
+from frappe.utils.nestedset import get_descendants_of
from erpnext.accounts.utils import get_fiscal_year
-from erpnext.stock.report.stock_balance.stock_balance import (
- get_item_details,
- get_items,
- get_stock_ledger_entries,
-)
+from erpnext.stock.doctype.warehouse.warehouse import apply_warehouse_filter
from erpnext.stock.utils import is_reposting_item_valuation_in_progress
@@ -231,7 +229,7 @@
data = []
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
- item_details = get_item_details(items, sle, filters)
+ item_details = get_item_details(items, sle)
periodic_data = get_periodic_data(sle, filters)
ranges = get_period_date_ranges(filters)
@@ -265,3 +263,109 @@
chart["type"] = "line"
return chart
+
+
+def get_items(filters):
+ "Get items based on item code, item group or brand."
+ if item_code := filters.get("item_code"):
+ return [item_code]
+ else:
+ item_filters = {}
+ if item_group := filters.get("item_group"):
+ children = get_descendants_of("Item Group", item_group, ignore_permissions=True)
+ item_filters["item_group"] = ("in", children + [item_group])
+ if brand := filters.get("brand"):
+ item_filters["brand"] = brand
+
+ return frappe.get_all("Item", filters=item_filters, pluck="name", order_by=None)
+
+
+def get_stock_ledger_entries(filters, items):
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ sle.warehouse,
+ sle.posting_date,
+ sle.actual_qty,
+ sle.valuation_rate,
+ sle.company,
+ sle.voucher_type,
+ sle.qty_after_transaction,
+ sle.stock_value_difference,
+ sle.item_code.as_("name"),
+ sle.voucher_no,
+ sle.stock_value,
+ sle.batch_no,
+ )
+ .where((sle.docstatus < 2) & (sle.is_cancelled == 0))
+ .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
+ .orderby(sle.creation)
+ .orderby(sle.actual_qty)
+ )
+
+ if items:
+ query = query.where(sle.item_code.isin(items))
+
+ query = apply_conditions(query, filters)
+ return query.run(as_dict=True)
+
+
+def apply_conditions(query, filters):
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ warehouse_table = frappe.qb.DocType("Warehouse")
+
+ if not filters.get("from_date"):
+ frappe.throw(_("'From Date' is required"))
+
+ if to_date := filters.get("to_date"):
+ query = query.where(sle.posting_date <= to_date)
+ else:
+ frappe.throw(_("'To Date' is required"))
+
+ if company := filters.get("company"):
+ query = query.where(sle.company == company)
+
+ if filters.get("warehouse"):
+ query = apply_warehouse_filter(query, sle, filters)
+ elif warehouse_type := filters.get("warehouse_type"):
+ query = (
+ query.join(warehouse_table)
+ .on(warehouse_table.name == sle.warehouse)
+ .where(warehouse_table.warehouse_type == warehouse_type)
+ )
+
+ return query
+
+
+def get_item_details(items, sle):
+ item_details = {}
+ if not items:
+ items = list(set(d.item_code for d in sle))
+
+ if not items:
+ return item_details
+
+ item_table = frappe.qb.DocType("Item")
+
+ query = (
+ frappe.qb.from_(item_table)
+ .select(
+ item_table.name,
+ item_table.item_name,
+ item_table.description,
+ item_table.item_group,
+ item_table.brand,
+ item_table.stock_uom,
+ )
+ .where(item_table.name.isin(items))
+ )
+
+ result = query.run(as_dict=1)
+
+ for item_table in result:
+ item_details.setdefault(item_table.name, item_table)
+
+ return item_details
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index a757add..68df918 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -137,6 +137,7 @@
def get_item_warehouse_map(self):
item_warehouse_map = {}
+ self.opening_vouchers = self.get_opening_vouchers()
for entry in self.sle_entries:
group_by_key = self.get_group_by_key(entry)
@@ -159,20 +160,18 @@
return item_warehouse_map
def prepare_item_warehouse_map(self, item_warehouse_map, entry, group_by_key):
- opening_vouchers = self.get_opening_vouchers()
-
qty_dict = item_warehouse_map[group_by_key]
for field in self.inventory_dimensions:
qty_dict[field] = entry.get(field)
- if entry.voucher_type == "Stock Reconciliation" and not entry.batch_no:
+ if entry.voucher_type == "Stock Reconciliation" and (not entry.batch_no or entry.serial_no):
qty_diff = flt(entry.qty_after_transaction) - flt(qty_dict.bal_qty)
else:
qty_diff = flt(entry.actual_qty)
value_diff = flt(entry.stock_value_difference)
- if entry.posting_date < self.from_date or entry.voucher_no in opening_vouchers.get(
+ if entry.posting_date < self.from_date or entry.voucher_no in self.opening_vouchers.get(
entry.voucher_type, []
):
qty_dict.opening_qty += qty_diff
@@ -271,6 +270,7 @@
sle.voucher_no,
sle.stock_value,
sle.batch_no,
+ sle.serial_no,
item_table.item_group,
item_table.stock_uom,
item_table.item_name,
@@ -475,7 +475,10 @@
table = frappe.qb.DocType("UOM Conversion Detail")
query = (
frappe.qb.from_(table)
- .select(table.conversion_factor)
+ .select(
+ table.conversion_factor,
+ table.parent,
+ )
.where((table.parenttype == "Item") & (table.uom == self.filters.include_uom))
)
@@ -553,14 +556,16 @@
return opening_fifo_queue
-def filter_items_with_no_transactions(iwb_map, float_precision: float, inventory_dimensions: list):
+def filter_items_with_no_transactions(
+ iwb_map, float_precision: float, inventory_dimensions: list = None
+):
pop_keys = []
for group_by_key in iwb_map:
qty_dict = iwb_map[group_by_key]
no_transactions = True
for key, val in qty_dict.items():
- if key in inventory_dimensions:
+ if inventory_dimensions and key in inventory_dimensions:
continue
if key in [
diff --git a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
index abbb33b..5dbdcef 100644
--- a/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
+++ b/erpnext/stock/report/warehouse_wise_item_balance_age_and_value/warehouse_wise_item_balance_age_and_value.py
@@ -8,15 +8,15 @@
import frappe
from frappe import _
from frappe.query_builder.functions import Count
-from frappe.utils import flt
+from frappe.utils import cint, flt, getdate
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
-from erpnext.stock.report.stock_balance.stock_balance import (
+from erpnext.stock.report.stock_analytics.stock_analytics import (
get_item_details,
- get_item_warehouse_map,
get_items,
get_stock_ledger_entries,
)
+from erpnext.stock.report.stock_balance.stock_balance import filter_items_with_no_transactions
from erpnext.stock.utils import is_reposting_item_valuation_in_progress
@@ -32,7 +32,7 @@
items = get_items(filters)
sle = get_stock_ledger_entries(filters, items)
- item_map = get_item_details(items, sle, filters)
+ item_map = get_item_details(items, sle)
iwb_map = get_item_warehouse_map(filters, sle)
warehouse_list = get_warehouse_list(filters)
item_ageing = FIFOSlots(filters).generate()
@@ -128,3 +128,59 @@
for wh in warehouse_list:
columns += [_(wh.name) + ":Int:100"]
+
+
+def get_item_warehouse_map(filters, sle):
+ iwb_map = {}
+ from_date = getdate(filters.get("from_date"))
+ to_date = getdate(filters.get("to_date"))
+ float_precision = cint(frappe.db.get_default("float_precision")) or 3
+
+ for d in sle:
+ group_by_key = get_group_by_key(d)
+ if group_by_key not in iwb_map:
+ iwb_map[group_by_key] = frappe._dict(
+ {
+ "opening_qty": 0.0,
+ "opening_val": 0.0,
+ "in_qty": 0.0,
+ "in_val": 0.0,
+ "out_qty": 0.0,
+ "out_val": 0.0,
+ "bal_qty": 0.0,
+ "bal_val": 0.0,
+ "val_rate": 0.0,
+ }
+ )
+
+ qty_dict = iwb_map[group_by_key]
+ if d.voucher_type == "Stock Reconciliation" and not d.batch_no:
+ qty_diff = flt(d.qty_after_transaction) - flt(qty_dict.bal_qty)
+ else:
+ qty_diff = flt(d.actual_qty)
+
+ value_diff = flt(d.stock_value_difference)
+
+ if d.posting_date < from_date:
+ qty_dict.opening_qty += qty_diff
+ qty_dict.opening_val += value_diff
+
+ elif d.posting_date >= from_date and d.posting_date <= to_date:
+ if flt(qty_diff, float_precision) >= 0:
+ qty_dict.in_qty += qty_diff
+ qty_dict.in_val += value_diff
+ else:
+ qty_dict.out_qty += abs(qty_diff)
+ qty_dict.out_val += abs(value_diff)
+
+ qty_dict.val_rate = d.valuation_rate
+ qty_dict.bal_qty += qty_diff
+ qty_dict.bal_val += value_diff
+
+ iwb_map = filter_items_with_no_transactions(iwb_map, float_precision)
+
+ return iwb_map
+
+
+def get_group_by_key(row) -> tuple:
+ return (row.company, row.item_code, row.warehouse)