def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) for sle in sl_entries: item_detail = item_details[sle.item_code] data.append([ sle.date, sle.item_code, item_detail.item_name, item_detail.item_group, item_detail.brand, item_detail.description, sle.warehouse, item_detail.stock_uom, sle.actual_qty, sle.qty_after_transaction, sle.voucher_type, sle.voucher_no, sle.batch_no, sle.serial_no, sle.project, sle.company ]) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns(filters) 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) actual_qty = stock_value = 0 for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) concentration = sle.concentration or 100 if item_detail.maintain_as_is_stock: sle.update({ 'as_is_qty': flt(sle.actual_qty), 'actual_qty': (flt(sle.actual_qty) * flt(concentration))/100, 'incoming_rate': (flt(sle.incoming_rate) * 100)/flt(concentration), 'valuation_rate': (flt(sle.valuation_rate) * 100)/flt(concentration), 'as_is_balance_qty': flt(sle.qty_after_transaction), 'qty_after_transaction': flt(sle.qty_after_transaction * flt(concentration))/100 }) else: sle.update({ 'as_is_qty': flt(sle.actual_qty), 'actual_qty': flt(sle.actual_qty), 'incoming_rate': flt(sle.incoming_rate), 'valuation_rate': flt(sle.valuation_rate), 'as_is_balance_qty': flt(sle.qty_after_transaction) }) #frappe.msgprint(str(sle)) if filters.get("batch_no") or (filters.get("item_code") and filters.get("warehouse")): actual_qty += sle.actual_qty stock_value += sle.stock_value_difference if sle.voucher_type == 'Stock Reconciliation': actual_qty = sle.qty_after_transaction stock_value = sle.stock_value sle.update({ "qty_after_transaction": actual_qty, "stock_value": stock_value }) #frappe.msgprint(str(sle)) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) for sle in sl_entries: item_detail = item_details[sle.item_code] data.append([sle.date, sle.item_code, item_detail.item_name, item_detail.item_group, item_detail.brand, item_detail.description, sle.warehouse, item_detail.stock_uom, sle.actual_qty, sle.qty_after_transaction, (sle.incoming_rate if sle.actual_qty > 0 else 0.0), sle.valuation_rate, sle.stock_value, sle.voucher_type, sle.voucher_no, sle.batch_no, sle.serial_no, sle.project, sle.company]) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): is_reposting_item_valuation_in_progress() filters = frappe._dict(filters or {}) include_uom = filters.get("include_uom") columns = get_columns() bin_list = get_bin_list(filters) item_map = get_item_map(filters.get("item_code"), include_uom) warehouse_company = {} data = [] conversion_factors = [] for bin in bin_list: item = item_map.get(bin.item_code) if not item: # likely an item that has reached its end of life continue # item = item_map.setdefault(bin.item_code, get_item(bin.item_code)) company = warehouse_company.setdefault( bin.warehouse, frappe.db.get_value("Warehouse", bin.warehouse, "company")) if filters.brand and filters.brand != item.brand: continue elif filters.item_group and filters.item_group != item.item_group: continue elif filters.company and filters.company != company: continue re_order_level = re_order_qty = 0 for d in item.get("reorder_levels"): if d.warehouse == bin.warehouse: re_order_level = d.warehouse_reorder_level re_order_qty = d.warehouse_reorder_qty shortage_qty = 0 if (re_order_level or re_order_qty) and re_order_level > bin.projected_qty: shortage_qty = re_order_level - flt(bin.projected_qty) data.append([ item.name, item.item_name, item.description, item.item_group, item.brand, bin.warehouse, item.stock_uom, bin.actual_qty, bin.planned_qty, bin.indented_qty, bin.ordered_qty, bin.reserved_qty, bin.reserved_qty_for_production, bin.reserved_qty_for_sub_contract, bin.projected_qty, re_order_level, re_order_qty, shortage_qty ]) if include_uom: conversion_factors.append(item.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): if not filters: filters = {} validate_filters(filters) include_uom = filters.get("include_uom") columns = get_columns() items = get_items(filters) sle = get_stock_ledger_entries(filters, items) # 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, item_map[item]["item_name"], item_map[item]["item_group"], item_map[item]["brand"], item_map[item]["description"], warehouse, item_map[item]["stock_uom"], qty_dict.opening_qty, qty_dict.opening_val, qty_dict.in_qty, qty_dict.in_val, qty_dict.out_qty, qty_dict.out_val, qty_dict.bal_qty, qty_dict.bal_val, qty_dict.val_rate, item_reorder_level, item_reorder_qty, company ] if filters.get('show_variant_attributes', 0) == 1: variants_attributes = get_variants_attributes() report_data += [ item_map[item].get(i) for i in variants_attributes ] if include_uom: conversion_factors.append(item_map[item].conversion_factor) data.append(report_data) if filters.get('show_variant_attributes', 0) == 1: columns += ["{}:Data:100".format(i) for i in get_variants_attributes()] update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) actual_qty = stock_value = 0 for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) if filters.get("batch_no"): actual_qty += sle.actual_qty stock_value += sle.stock_value_difference if sle.voucher_type == 'Stock Reconciliation': actual_qty = sle.qty_after_transaction stock_value = sle.stock_value sle.update({ "qty_after_transaction": actual_qty, "stock_value": stock_value, "in_qty": max(sle.actual_qty, 0), "out_qty": min(sle.actual_qty, 0) }) # get the name of the item that was produced using this item if sle.voucher_type == "Stock Entry": purpose, work_order, fg_completed_qty = frappe.db.get_value( sle.voucher_type, sle.voucher_no, ["purpose", "work_order", "fg_completed_qty"]) if purpose == "Manufacture" and work_order: finished_product = frappe.db.get_value("Work Order", work_order, "item_name") finished_qty = fg_completed_qty sle.update({ "finished_product": finished_product, "finished_qty": finished_qty, }) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): if not filters: filters = {} validate_filters(filters) include_uom = filters.get("include_uom") columns = get_columns() items = get_items(filters) sle = get_stock_ledger_entries(filters, items) # 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, item_map[item]["item_name"], item_map[item]["item_group"], item_map[item]["brand"], item_map[item]["description"], warehouse, item_map[item]["stock_uom"], qty_dict.opening_qty, qty_dict.opening_val, qty_dict.in_qty, qty_dict.in_val, qty_dict.out_qty, qty_dict.out_val, qty_dict.bal_qty, qty_dict.bal_val, qty_dict.val_rate, item_reorder_level, item_reorder_qty, company ] if filters.get('show_variant_attributes', 0) == 1: variants_attributes = get_variants_attributes() report_data += [item_map[item].get(i) for i in variants_attributes] if include_uom: conversion_factors.append(item_map[item].conversion_factor) data.append(report_data) if filters.get('show_variant_attributes', 0) == 1: columns += ["{}:Data:100".format(i) for i in get_variants_attributes()] update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() items = get_items(filters) sl_entries = get_stock_ledger_entries(filters, items) #print "sl_entries---------------",sl_entries item_details = get_item_details(items, sl_entries, include_uom) opening_row = get_opening_balance(filters, columns) data = [] ''' for sle in sl_entries: print "voucher type--------------",sle.voucher_type print "voucher no--------------",sle.voucher_no print "item code --------------",sle.item_code ''' conversion_factors = [] if opening_row: data.append(opening_row) for sle in sl_entries: item_detail = item_details[sle.item_code] voucher_type = sle.voucher_type voucher_no = sle.voucher_no voucher_details = get_rarb_vouchers(voucher_type, voucher_no, sle.item_code) for voucher in voucher_details: if voucher.pch_rarb_location_src is not None and voucher.s_warehouse == sle.warehouse: data.append([ sle.date, sle.item_code, item_detail.item_name, item_detail.item_group, voucher.pch_rarb_location_src, item_detail.brand, item_detail.description, sle.warehouse, item_detail.stock_uom, sle.actual_qty, sle.qty_after_transaction, (sle.incoming_rate if sle.actual_qty > 0 else 0.0), sle.valuation_rate, sle.stock_value, sle.voucher_type, sle.voucher_no, sle.batch_no, sle.serial_no, sle.project, sle.company ]) elif voucher.pch_rarb_location_trg is not None and voucher.t_warehouse == sle.warehouse: data.append([ sle.date, sle.item_code, item_detail.item_name, item_detail.item_group, voucher.pch_rarb_location_trg, item_detail.brand, item_detail.description, sle.warehouse, item_detail.stock_uom, sle.actual_qty, sle.qty_after_transaction, (sle.incoming_rate if sle.actual_qty > 0 else 0.0), sle.valuation_rate, sle.stock_value, sle.voucher_type, sle.voucher_no, sle.batch_no, sle.serial_no, sle.project, sle.company ]) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): is_reposting_item_valuation_in_progress() include_uom = filters.get("include_uom") columns = get_columns() 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) precision = cint( frappe.db.get_single_value("System Settings", "float_precision")) data = [] conversion_factors = [] if opening_row: data.append(opening_row) conversion_factors.append(0) actual_qty = stock_value = 0 available_serial_nos = {} for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) if filters.get("batch_no"): actual_qty += flt(sle.actual_qty, precision) stock_value += sle.stock_value_difference if sle.voucher_type == 'Stock Reconciliation' and not sle.actual_qty: actual_qty = sle.qty_after_transaction stock_value = sle.stock_value sle.update({ "qty_after_transaction": actual_qty, "stock_value": stock_value }) sle.update({ "in_qty": max(sle.actual_qty, 0), "out_qty": min(sle.actual_qty, 0) }) if sle.serial_no: update_available_serial_nos(available_serial_nos, sle) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) actual_qty = stock_value = stock_value_in_sc = 0 for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) if filters.get("batch_no"): actual_qty += sle.actual_qty stock_value += sle.stock_value_difference if sle.voucher_type == 'Stock Reconciliation': actual_qty = sle.qty_after_transaction stock_value = sle.stock_value sle.update({ "qty_after_transaction": actual_qty, "stock_value": stock_value }) if filters.get("currency"): default_currency = frappe.get_value('Company',filters.company,'default_currency') 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 stock_value_in_sc += sle.stock_value * flt(exchange_rate) sle.update({ "stock_value_in_sc": stock_value_in_sc }) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) data = [] conversion_factors = [] if opening_row: data.append(opening_row) actual_qty = stock_value = 0 for sle in sl_entries: item_detail = item_details[sle.item_code] sle.update(item_detail) if filters.get("batch_no"): actual_qty += sle.actual_qty stock_value += sle.stock_value_difference if sle.voucher_type == 'Stock Reconciliation': actual_qty = sle.qty_after_transaction stock_value = sle.stock_value sle.update({ "qty_after_transaction": actual_qty, "stock_value": stock_value }) sle.update({ "in_qty": max(sle.actual_qty, 0), "out_qty": min(sle.actual_qty, 0) }) data.append(sle) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): if not filters: filters = {} validate_filters(filters) include_uom = filters.get("include_uom") columns = get_columns() items = get_items(filters) sle = get_stock_ledger_entries(filters, items) # 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()) if filters.get("report_style") == "Minimal": columns = [ _("Item") + ":Link/Item:300", # _("Item Name")+"::150", _("Item Group") + ":Link/Item Group:300", # _("Brand")+"::90", # _("Description")+"::140", _("Warehouse") + ":Link/Warehouse:200", _("Stock UOM") + ":Link/UOM:50", # _("Opening Qty")+":Float:100", # _("Opening Value")+":Float:60", # _("In Qty")+":Float:100", # _("In Value")+":Float:80", # _("Out Qty")+":Float:100", # _("Out Value")+":Float:80", _("Balance Qty") + ":Float:100", # _("Balance Value")+":Float:100", # _("Valuation Rate")+":Float:100" # ,_("Reorder Level")+":Float:80", # _("Reorder Qty")+":Float:80", # _("Company")+":Link/Company:100" ] data = [] conversion_factors = [] for (company, item, warehouse) in sorted(iwb_map): if not company == filters.get("company"): continue 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"] if filters.get("report_style") == "Minimal": report_data = [ item, # item_map[item]["item_name"], item_map[item]["item_group"], # item_map[item]["brand"], # item_map[item]["description"], warehouse, item_map[item]["stock_uom"], # qty_dict.opening_qty, # qty_dict.opening_val, # qty_dict.in_qty, # qty_dict.in_val, # qty_dict.out_qty, # qty_dict.out_val, qty_dict.bal_qty, # qty_dict.bal_val, # qty_dict.val_rate # item_reorder_level, # item_reorder_qty, # company ] else: report_data = [ item, # item_map[item]["item_name"], item_map[item]["item_group"], # item_map[item]["brand"], # item_map[item]["description"], warehouse, item_map[item]["stock_uom"], qty_dict.opening_qty, # qty_dict.opening_val, qty_dict.in_qty, # qty_dict.in_val, qty_dict.out_qty, # qty_dict.out_val, qty_dict.bal_qty, qty_dict.bal_val, qty_dict.val_rate, # item_reorder_level, # item_reorder_qty, # company ] if filters.get('show_variant_attributes', 0) == 1: variants_attributes = get_variants_attributes() report_data += [item_map[item].get(i) for i in variants_attributes] data.append(report_data) if include_uom: conversion_factors.append(item_map[item].conversion_factor) data.append(report_data) if filters.get('show_variant_attributes', 0) == 1: columns += ["{}:Data:100".format(i) for i in get_variants_attributes()] update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): filters = frappe._dict(filters or {}) include_uom = filters.get("include_uom") columns = get_columns() bin_list = get_bin_list(filters) item_map = get_item_map(filters.get("item_code"), include_uom) warehouse_company = {} data = [] conversion_factors = [] row = {} duplicate = [] month = 1 if filters.get("range"): month = int(filters.get("range")) / 30 for bin in bin_list: item = item_map.get(bin.item_code) if not item: # likely an item that has reached its end of life continue # item = item_map.setdefault(bin.item_code, get_item(bin.item_code)) company = warehouse_company.setdefault( bin.warehouse, frappe.db.get_value("Warehouse", bin.warehouse, "company")) if filters.item_group and filters.item_group != item.item_group: continue elif filters.company and filters.company != company: continue re_order_level = re_order_qty = 0 for d in item.get("reorder_levels"): if d.warehouse == bin.warehouse: re_order_level = d.warehouse_reorder_level re_order_qty = d.warehouse_reorder_qty shortage_qty = 0 if (re_order_level or re_order_qty) and re_order_level > bin.projected_qty: shortage_qty = re_order_level - flt(bin.projected_qty) if item.name not in duplicate: selling = 0 buying = frappe.db.get_value('Item', item.name, 'valuation_rate') for ip in frappe.get_all('Item Price', {'item_code': item.name}, ['selling', 'price_list_rate']): if ip.selling == 1: selling = ip.price_list_rate row[item.name] = { 'item_code': item.name, 'item_name': item.item_name, 'rate': buying, 'item_group': item.item_group, bin.warehouse: bin.actual_qty, 'stock_uom': item.stock_uom, 'actual_qty': bin.actual_qty, 'ordered_qty': bin.ordered_qty, 'reserved_qty': bin.reserved_qty, 'projected_qty': bin.projected_qty, 'profit': (selling - buying) * 0.01, 'average': (bin.actual_qty) / month, } for d in frappe.get_all('Warehouse'): if d.name != bin.warehouse: row[item.name].update({d.name: 0}) duplicate.append(item.name) else: for total_qty in [ 'actual_qty', 'ordered_qty', 'reserved_qty', 'projected_qty' ]: row[item.name].update({ total_qty: bin.get(total_qty) + row[item.name].get(total_qty) }) row[item.name].update( {'average': row[item.name].get('actual_qty') / month}) row[item.name].update({bin.warehouse: bin.actual_qty}) if include_uom: conversion_factors.append(item.conversion_factor) for d in duplicate: data.append(row[d]) update_included_uom_in_report(columns, data, include_uom, conversion_factors) return columns, data
def execute(filters=None): include_uom = filters.get("include_uom") columns = get_columns() 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) sales_data = sales(filters) data = [] conversion_factors = [] if opening_row: data.append(opening_row) for sle in sl_entries: #print(sl_entries) item_detail = item_details[sle.item_code] item = sle.item_code + ":" + item_detail.item_name stock_uom = item_detail.stock_uom party = '' rate = 0.00 party_code = '' if sales_data.get(sle.voucher_no): try: party = sales_data[sle.voucher_no][sle.item_code][1] rate = sales_data[sle.voucher_no][sle.item_code][2] party_code = sales_data[sle.voucher_no][sle.item_code][0] except: pass if sle.actual_qty < 0: qty_recvd = 0.00 rate_recvd = 0.00 qty_issued = sle.actual_qty rate_issued = rate or sle.valuation_rate else: qty_recvd = sle.actual_qty rate_recvd = rate or sle.valuation_rate qty_issued = 0.00 rate_issued = 0.00 if 'Accounts Manager' not in frappe.get_roles(frappe.session.user) and sle.warehouse == 'Store No 6 - GE': rate_recvd = 0.00 rate_issued = 0.00 sle.stock_value = 0.00 sle.valuation_rate = 0.00 party = '' sle.voucher_type = '' sle.voucher_no = '' if filters.get('party'): if party_code == filters.get('party').split(',')[0]: data.append([sle.date,item,sle.qty_after_transaction,rate_recvd,rate_issued, qty_recvd,qty_issued,sle.warehouse,party, sle.voucher_type,sle.voucher_no,rate_issued, sle.stock_value, sle.valuation_rate]) else: data.append([sle.date,item,sle.qty_after_transaction,rate_recvd,rate_issued, qty_recvd,qty_issued,sle.warehouse,party,sle.voucher_type,sle.voucher_no, rate_issued, sle.stock_value, sle.valuation_rate]) if include_uom: conversion_factors.append(item_detail.conversion_factor) update_included_uom_in_report( columns, data, include_uom, conversion_factors) return columns, data