diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py index 8fdaa9a5ee51d24908ef7925d47a82825b2d4772..07d01c3b3106e3f16db410d724dd7392d44d5347 100644 --- a/erpnext/stock/report/stock_ledger/stock_ledger.py +++ b/erpnext/stock/report/stock_ledger/stock_ledger.py @@ -27,7 +27,11 @@ def execute(filters=None): items = get_items(filters) sl_entries = get_stock_ledger_entries(filters, items) item_details = get_item_details(items, sl_entries, include_uom) - opening_row = get_opening_balance(filters, columns, sl_entries) + if filters.get("batch_no"): + opening_row = get_opening_balance_from_batch(filters, columns, sl_entries) + else: + opening_row = get_opening_balance(filters, columns, sl_entries) + precision = cint(frappe.db.get_single_value("System Settings", "float_precision")) bundle_details = {} @@ -48,13 +52,16 @@ def execute(filters=None): available_serial_nos = {} inventory_dimension_filters_applied = check_inventory_dimension_filters_applied(filters) - batch_balance_dict = defaultdict(float) + batch_balance_dict = frappe._dict({}) + if actual_qty and filters.get("batch_no"): + batch_balance_dict[filters.batch_no] = [actual_qty, stock_value] + for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) if bundle_info := bundle_details.get(sle.serial_and_batch_bundle): - data.extend(get_segregated_bundle_entries(sle, bundle_info, batch_balance_dict)) + data.extend(get_segregated_bundle_entries(sle, bundle_info, batch_balance_dict, filters)) continue if filters.get("batch_no") or inventory_dimension_filters_applied: @@ -90,7 +97,7 @@ def execute(filters=None): return columns, data -def get_segregated_bundle_entries(sle, bundle_details, batch_balance_dict): +def get_segregated_bundle_entries(sle, bundle_details, batch_balance_dict, filters): segregated_entries = [] qty_before_transaction = sle.qty_after_transaction - sle.actual_qty stock_value_before_transaction = sle.stock_value - sle.stock_value_difference @@ -109,9 +116,19 @@ def get_segregated_bundle_entries(sle, bundle_details, batch_balance_dict): } ) - if row.batch_no: - batch_balance_dict[row.batch_no] += row.qty - new_sle.update({"qty_after_transaction": batch_balance_dict[row.batch_no]}) + if filters.get("batch_no") and row.batch_no: + if not batch_balance_dict.get(row.batch_no): + batch_balance_dict[row.batch_no] = [0, 0] + + batch_balance_dict[row.batch_no][0] += row.qty + batch_balance_dict[row.batch_no][1] += row.stock_value_difference + + new_sle.update( + { + "qty_after_transaction": batch_balance_dict[row.batch_no][0], + "stock_value": batch_balance_dict[row.batch_no][1], + } + ) qty_before_transaction += row.qty stock_value_before_transaction += new_sle.stock_value_difference @@ -504,6 +521,62 @@ def get_sle_conditions(filters): return "and {}".format(" and ".join(conditions)) if conditions else "" +def get_opening_balance_from_batch(filters, columns, sl_entries): + query_filters = { + "batch_no": filters.batch_no, + "docstatus": 1, + "posting_date": ("<", filters.from_date), + } + + for fields in ["item_code", "warehouse"]: + if filters.get(fields): + query_filters[fields] = filters.get(fields) + + opening_data = frappe.get_all( + "Stock Ledger Entry", + fields=["sum(actual_qty) as qty_after_transaction", "sum(stock_value_difference) as stock_value"], + filters=query_filters, + )[0] + + for field in ["qty_after_transaction", "stock_value", "valuation_rate"]: + if opening_data.get(field) is None: + opening_data[field] = 0.0 + + query_filters = [ + ["Serial and Batch Entry", "batch_no", "=", filters.batch_no], + ["Serial and Batch Bundle", "docstatus", "=", 1], + ["Serial and Batch Bundle", "posting_date", "<", filters.from_date], + ] + + for fields in ["item_code", "warehouse"]: + if filters.get(fields): + query_filters.append(["Serial and Batch Bundle", fields, "=", filters.get(fields)]) + + bundle_data = frappe.get_all( + "Serial and Batch Bundle", + fields=[ + "sum(`tabSerial and Batch Entry`.`qty`) as qty", + "sum(`tabSerial and Batch Entry`.`stock_value_difference`) as stock_value", + ], + filters=query_filters, + ) + + if bundle_data: + opening_data.qty_after_transaction += flt(bundle_data[0].qty) + opening_data.stock_value += flt(bundle_data[0].stock_value) + if opening_data.qty_after_transaction: + opening_data.valuation_rate = flt(opening_data.stock_value) / flt( + opening_data.qty_after_transaction + ) + + return { + "item_code": _("'Opening'"), + "qty_after_transaction": opening_data.qty_after_transaction, + "valuation_rate": opening_data.valuation_rate, + "stock_value": opening_data.stock_value, + } + + def get_opening_balance(filters, columns, sl_entries): if not (filters.item_code and filters.warehouse and filters.from_date): return diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py index 55b076fa396d07129a604ffae2d08877dbec407e..69019b3fbc728237f0c78ba67d61aa4765201604 100644 --- a/erpnext/stock/stock_ledger.py +++ b/erpnext/stock/stock_ledger.py @@ -1439,7 +1439,7 @@ def get_previous_sle_of_current_voucher(args, operator="<", exclude_current_vouc voucher_condition = f"and voucher_no != '{voucher_no}'" sle = frappe.db.sql( - """ + f""" select *, posting_datetime as "timestamp" from `tabStock Ledger Entry` where item_code = %(item_code)s @@ -1451,11 +1451,12 @@ def get_previous_sle_of_current_voucher(args, operator="<", exclude_current_vouc ) order by posting_datetime desc, creation desc limit 1 - for update""".format( - operator=operator, - voucher_condition=voucher_condition, - ), - args, + for update""", + { + "item_code": args.get("item_code"), + "warehouse": args.get("warehouse"), + "posting_datetime": args.get("posting_datetime"), + }, as_dict=1, )