def execute(filters=None): 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 = get_fifo_queue(filters) 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 in warehouse else [0.00] total_stock_value += qty_dict.bal_val if wh.name in 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 iteritems(item_balance): 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=None): if not filters: filters = {} validate_filters(filters) from_date = filters.get('from_date') to_date = filters.get('to_date') 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 = get_fifo_queue(filters, sle) # 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 = lambda x: x[1] 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'] = round(get_average_age(fifo_queue, to_date),2) 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) filter_company = filters.get("company") current_fiscal_year = frappe.defaults.get_user_default("fiscal_year") from_date_fiscal = frappe.db.get_value("Fiscal Year",current_fiscal_year,"year_start_date") for row in data: has_batch_no = frappe.db.get_value("Item",row['item_code'],"has_batch_no") item_code = row['item_code'] warehouse = row['warehouse'] if has_batch_no: row['batch_wise'] = f"""<button style='margin-left:5px;border:none;color: #fff; background-color: #5e64ff; padding: 3px 5px;border-radius: 5px;' target="_blank" item_code='{item_code}' company='{filter_company}' from_date='{from_date}' to_date='{to_date}' warehouse='{warehouse}' onClick=view_batch_wise_report(this.getAttribute('item_code'),this.getAttribute('company'),this.getAttribute('to_date'),this.getAttribute('warehouse'))>View Batch Detail</button>""" row['stock_ledger'] = f"""<button style='margin-left:5px;border:none;color: #fff; background-color: #5e64ff; padding: 3px 5px;border-radius: 5px;' target="_blank" item_code='{item_code}' company='{filter_company}' from_date='{from_date_fiscal}' to_date='{to_date}' warehouse='{warehouse}' onClick=view_stock_leder_report(this.getAttribute('item_code'),this.getAttribute('company'),this.getAttribute('from_date'),this.getAttribute('to_date'),this.getAttribute('warehouse'))>View Stock Ledger</button>""" chart_data = get_chart_data(data, filters) return columns, data, None, chart_data
def execute(filters=None): is_reposting_item_valuation_in_progress() if not filters: filters = {} validate_filters(filters) from_date = filters.get('from_date') to_date = filters.get('to_date') 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 = get_fifo_queue(filters, sle) # 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 = {} def _func(x): return x[1] 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 execute(filters=None): if not filters: filters = {} validate_filters(filters) stock_value_in_sc = 0 from_date = filters.get('from_date') to_date = filters.get('to_date') 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 = get_fifo_queue(filters, sle) if not sle: return columns, [] if filters.get("currency"): columns.extend([{ "label": _("Currency"), "fieldname": "sc_currency", "fieldtype": "Data", "width": 80 }, { "label": _("Balance Value"), "fieldname": "sc_value", "fieldtype": "Float", "width": 100 }]) if filters.get('warehouse'): filters.warehouse = frappe.parse_json(filters.get('warehouse')) 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 = [] test = [] conversion_factors = {} _func = lambda x: x[1] item_head = [] for (company, item, warehouse) in sorted(iwb_map): if not item in item_head: item_info = get_parent_details(item, filters) data.append({ 'item_code': frappe.get_value("Item", item, "item_name"), 'bal_qty': item_info.bal_qty, 'indent': 0 }) item_head.append(item) 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"] supplier = supplier_part_no = '' supplier_info = frappe.get_value('Item Supplier', {'parent': item}, ['supplier', 'supplier_part_no']) if supplier_info: supplier = supplier_info[0] supplier_part_no = supplier_info[1] report_data = { 'item_code': item, # 'item_name':'test', 'warehouse': warehouse, 'company': company, 'supplier': supplier, # 'indent': 1, 'supplier_part_no': supplier_part_no, 'bal_val_cur': frappe.db.get_value('Company', company, "default_currency"), 'country': frappe.get_value('Warehouse', warehouse, 'country'), 'reorder_level': item_reorder_level, 'reorder_qty': item_reorder_qty, 'indent': 1 } 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) sc_val_cur = '' sc_val = 0 for d in data: status = 'Ideal Quantity' if d['indent'] != 0: if int(d['bal_qty']) <= int(d['reorder_level']): status = 'Low Quantity' frozen = frappe.get_value('Item', d['item_code'], 'disabled') if frozen: status = 'Frozen Item' if filters.get("currency"): default_currency = d['bal_val_cur'] selected_currency = filters.currency exchange_rate = frappe.get_value( 'Currency Exchange', { 'from_currency': default_currency, 'to_currency': selected_currency }, ['exchange_rate']) if filters.get('currency') == default_currency: exchange_rate = 1 sc_val_cur = frappe.db.get_value('Currency', selected_currency, ['name']) sc_val = d['bal_val'] * flt(exchange_rate) # if no stock ledger entry found return converted_value = { 'sc_currency': sc_val_cur, 'sc_value': sc_val, 'status': status } d.update(converted_value) frappe.errprint(data) return columns, data
def execute(filters=None): if not filters: filters = {} validate_filters(filters) from_date = filters.get('from_date') to_date = filters.get('to_date') 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 = get_fifo_queue(filters, sle) # 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 = {} 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 = { 'item_code': item, 'warehouse': warehouse, 'company': company, 'reorder_level': item_reorder_qty, '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(fifo_queue, key=lambda fifo_data: fifo_data[1]) 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 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