def generate_item_and_item_wh_wise_slots(filters, sle): "Return results with and without 'show_warehouse_wise_stock'" item_wise_slots = FIFOSlots(filters, sle).generate() filters.show_warehouse_wise_stock = True item_wh_wise_slots = FIFOSlots(filters, sle).generate() filters.show_warehouse_wise_stock = False return item_wise_slots, item_wh_wise_slots
def test_insufficient_balance(self): "Reference: Case 3 in stock_ageing_fifo_logic.md (same wh)" sle = [ frappe._dict( name="Flask Item", actual_qty=(-30), qty_after_transaction=(-30), warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=20, qty_after_transaction=(-10), warehouse="WH 1", posting_date="2021-12-02", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=20, qty_after_transaction=10, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="003", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=10, qty_after_transaction=20, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="004", has_serial_no=False, serial_no=None, ), ] slots = FIFOSlots(self.filters, sle).generate() result = slots["Flask Item"] queue = result["fifo_queue"] self.assertEqual(result["qty_after_transaction"], result["total_qty"]) self.assertEqual(queue[0][0], 10.0) self.assertEqual(queue[1][0], 10.0)
def test_repack_entry_same_item_overproduce(self): """ Under consume item and have more repacked item qty (same warehouse). Ledger: Item | Qty | Voucher ------------------------ Item 1 | 500 | 001 Item 1 | -50 | 002 (repack) Item 1 | 100 | 002 (repack) Case most likely for batch items. Test time bucket computation. """ sle = [ frappe._dict( # stock up item name="Flask Item", actual_qty=500, qty_after_transaction=500, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=(-50), qty_after_transaction=450, warehouse="WH 1", posting_date="2021-12-04", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=100, qty_after_transaction=550, warehouse="WH 1", posting_date="2021-12-04", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), ] slots = FIFOSlots(self.filters, sle).generate() item_result = slots["Flask Item"] queue = item_result["fifo_queue"] self.assertEqual(item_result["total_qty"], 550.0) self.assertEqual(queue[0][0], 450.0) self.assertEqual(queue[1][0], 50.0) self.assertEqual(queue[2][0], 50.0) # check if time buckets add up to balance qty self.assertEqual(sum([i[0] for i in queue]), 550.0)
def test_basic_stock_reconciliation(self): """ Ledger (same wh): [+30, reco reset >> 50, -10] Bal: 40 """ sle = [ frappe._dict( name="Flask Item", actual_qty=30, qty_after_transaction=30, warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=0, qty_after_transaction=50, warehouse="WH 1", posting_date="2021-12-02", voucher_type="Stock Reconciliation", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=(-10), qty_after_transaction=40, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="003", has_serial_no=False, serial_no=None, ), ] slots = FIFOSlots(self.filters, sle).generate() result = slots["Flask Item"] queue = result["fifo_queue"] self.assertEqual(result["qty_after_transaction"], result["total_qty"]) self.assertEqual(result["total_qty"], 40.0) self.assertEqual(queue[0][0], 20.0) self.assertEqual(queue[1][0], 20.0)
def test_sequential_stock_reco_same_warehouse(self): """ Test back to back stock recos (same warehouse). Ledger: [reco opening >> +1000, reco reset >> 400, -10] Bal: 390 """ sle = [ frappe._dict( name="Flask Item", actual_qty=0, qty_after_transaction=1000, warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Reconciliation", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=0, qty_after_transaction=400, warehouse="WH 1", posting_date="2021-12-02", voucher_type="Stock Reconciliation", voucher_no="003", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=(-10), qty_after_transaction=390, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="003", has_serial_no=False, serial_no=None, ), ] slots = FIFOSlots(self.filters, sle).generate() result = slots["Flask Item"] queue = result["fifo_queue"] self.assertEqual(result["qty_after_transaction"], result["total_qty"]) self.assertEqual(result["total_qty"], 390.0) self.assertEqual(queue[0][0], 390.0)
def test_precision(self): "Test if final balance qty is rounded off correctly." sle = [ frappe._dict( # stock up item name="Flask Item", actual_qty=0.3, qty_after_transaction=0.3, warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( # stock up item name="Flask Item", actual_qty=0.6, qty_after_transaction=0.9, warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), ] slots = FIFOSlots(self.filters, sle).generate() report_data = format_report_data(self.filters, slots, self.filters["to_date"]) row = report_data[0] # first row in report bal_qty = row[5] range_qty_sum = sum([i for i in row[7:11]]) # get sum of range balance # check if value of Available Qty column matches with range bucket post format self.assertEqual(bal_qty, 0.9) self.assertEqual(bal_qty, range_qty_sum)
def execute(filters=None): is_reposting_item_valuation_in_progress() if not filters: filters = {} validate_filters(filters) columns = get_columns(filters) items = get_items(filters) sle = get_stock_ledger_entries(filters, items) item_map = get_item_details(items, sle, filters) iwb_map = get_item_warehouse_map(filters, sle) warehouse_list = get_warehouse_list(filters) item_ageing = FIFOSlots(filters).generate() data = [] item_balance = {} item_value = {} for (company, item, warehouse) in sorted(iwb_map): if not item_map.get(item): continue row = [] qty_dict = iwb_map[(company, item, warehouse)] item_balance.setdefault((item, item_map[item]["item_group"]), []) total_stock_value = 0.00 for wh in warehouse_list: row += [qty_dict.bal_qty] if wh.name == warehouse else [0.00] total_stock_value += qty_dict.bal_val if wh.name == warehouse else 0.00 item_balance[(item, item_map[item]["item_group"])].append(row) item_value.setdefault((item, item_map[item]["item_group"]), []) item_value[(item, item_map[item]["item_group"])].append(total_stock_value) # sum bal_qty by item for (item, item_group), wh_balance in item_balance.items(): if not item_ageing.get(item): continue total_stock_value = sum(item_value[(item, item_group)]) row = [item, item_group, total_stock_value] fifo_queue = item_ageing[item]["fifo_queue"] average_age = 0.00 if fifo_queue: average_age = get_average_age(fifo_queue, filters["to_date"]) row += [average_age] bal_qty = [sum(bal_qty) for bal_qty in zip(*wh_balance)] total_qty = sum(bal_qty) if len(warehouse_list) > 1: row += [total_qty] row += bal_qty if total_qty > 0: data.append(row) elif not filters.get("filter_total_zero_qty"): data.append(row) add_warehouse_column(columns, warehouse_list) return columns, data
def execute(filters: Optional[StockBalanceFilter] = None): is_reposting_item_valuation_in_progress() if not filters: filters = {} if filters.get("company"): company_currency = erpnext.get_company_currency(filters.get("company")) else: company_currency = frappe.db.get_single_value("Global Defaults", "default_currency") include_uom = filters.get("include_uom") columns = get_columns(filters) items = get_items(filters) sle = get_stock_ledger_entries(filters, items) if filters.get("show_stock_ageing_data"): filters["show_warehouse_wise_stock"] = True item_wise_fifo_queue = FIFOSlots(filters, sle).generate() # if no stock ledger entry found return if not sle: return columns, [] iwb_map = get_item_warehouse_map(filters, sle) item_map = get_item_details(items, sle, filters) item_reorder_detail_map = get_item_reorder_details(item_map.keys()) data = [] conversion_factors = {} _func = itemgetter(1) to_date = filters.get("to_date") for (company, item, warehouse) in sorted(iwb_map): if item_map.get(item): qty_dict = iwb_map[(company, item, warehouse)] item_reorder_level = 0 item_reorder_qty = 0 if item + warehouse in item_reorder_detail_map: item_reorder_level = item_reorder_detail_map[ item + warehouse]["warehouse_reorder_level"] item_reorder_qty = item_reorder_detail_map[ item + warehouse]["warehouse_reorder_qty"] report_data = { "currency": company_currency, "item_code": item, "warehouse": warehouse, "company": company, "reorder_level": item_reorder_level, "reorder_qty": item_reorder_qty, } report_data.update(item_map[item]) report_data.update(qty_dict) if include_uom: conversion_factors.setdefault(item, item_map[item].conversion_factor) if filters.get("show_stock_ageing_data"): fifo_queue = item_wise_fifo_queue[( item, warehouse)].get("fifo_queue") stock_ageing_data = { "average_age": 0, "earliest_age": 0, "latest_age": 0 } if fifo_queue: fifo_queue = sorted(filter(_func, fifo_queue), key=_func) if not fifo_queue: continue stock_ageing_data["average_age"] = get_average_age( fifo_queue, to_date) stock_ageing_data["earliest_age"] = date_diff( to_date, fifo_queue[0][1]) stock_ageing_data["latest_age"] = date_diff( to_date, fifo_queue[-1][1]) report_data.update(stock_ageing_data) data.append(report_data) add_additional_uom_columns(columns, data, include_uom, conversion_factors) return columns, data
def test_negative_stock_same_voucher(self): """ Test negative stock scenario in transfer bucket via repack entry (same wh). Ledger: Item | Qty | Voucher ------------------------ Item 1 | -50 | 001 Item 1 | -50 | 001 Item 1 | 30 | 001 Item 1 | 80 | 001 """ sle = [ frappe._dict( # stock up item name="Flask Item", actual_qty=(-50), qty_after_transaction=(-50), warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( # stock up item name="Flask Item", actual_qty=(-50), qty_after_transaction=(-100), warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( # stock up item name="Flask Item", actual_qty=30, qty_after_transaction=(-70), warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), ] fifo_slots = FIFOSlots(self.filters, sle) slots = fifo_slots.generate() item_result = slots["Flask Item"] # check transfer bucket transfer_bucket = fifo_slots.transferred_item_details[("001", "Flask Item", "WH 1")] self.assertEqual(transfer_bucket[0][0], 20) self.assertEqual(transfer_bucket[1][0], 50) self.assertEqual(item_result["fifo_queue"][0][0], -70.0) sle.append( frappe._dict( name="Flask Item", actual_qty=80, qty_after_transaction=10, warehouse="WH 1", posting_date="2021-12-01", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, )) fifo_slots = FIFOSlots(self.filters, sle) slots = fifo_slots.generate() item_result = slots["Flask Item"] transfer_bucket = fifo_slots.transferred_item_details[("001", "Flask Item", "WH 1")] self.assertFalse(transfer_bucket) self.assertEqual(item_result["fifo_queue"][0][0], 10.0)
def test_repack_entry_same_item_overproduce_with_split_rows(self): """ Over consume item and have less repacked item qty (same warehouse). Ledger: Item | Qty | Voucher ------------------------ Item 1 | 20 | 001 Item 1 | -50 | 002 (repack) Item 1 | 50 | 002 (repack) Item 1 | 50 | 002 (repack) """ sle = [ frappe._dict( # stock up item name="Flask Item", actual_qty=20, qty_after_transaction=20, warehouse="WH 1", posting_date="2021-12-03", voucher_type="Stock Entry", voucher_no="001", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=(-50), qty_after_transaction=(-30), warehouse="WH 1", posting_date="2021-12-04", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=50, qty_after_transaction=20, warehouse="WH 1", posting_date="2021-12-04", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), frappe._dict( name="Flask Item", actual_qty=50, qty_after_transaction=70, warehouse="WH 1", posting_date="2021-12-04", voucher_type="Stock Entry", voucher_no="002", has_serial_no=False, serial_no=None, ), ] fifo_slots = FIFOSlots(self.filters, sle) slots = fifo_slots.generate() item_result = slots["Flask Item"] queue = item_result["fifo_queue"] self.assertEqual(item_result["total_qty"], 70.0) self.assertEqual(queue[0][0], 20.0) self.assertEqual(queue[1][0], 50.0) # check transfer bucket transfer_bucket = fifo_slots.transferred_item_details[("002", "Flask Item", "WH 1")] self.assertFalse(transfer_bucket)