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
