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 = frappe._dict()
filters.currency = frappe.get_cached_value(
"Company", filters.company, "default_currency"
)
gross_profit_data = GrossProfitGenerator(filters)
data = []
group_wise_columns = frappe._dict(
{
"invoice": [
"parent",
"customer",
"customer_group",
"posting_date",
"item_code",
"item_name",
"item_group",
"brand",
"description",
"warehouse",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
"project",
],
"item_code": [
"item_code",
"item_name",
"brand",
"description",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
"total_purchase_qty",
"total_purchase_amount",
"available_qty",
"available_valuation_rate",
"available_buying_amount"
],
"warehouse": [
"warehouse",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
"brand": [
"brand",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
"total_purchase_qty",
"total_purchase_amount",
"available_qty",
"available_valuation_rate",
"available_buying_amount"
],
"item_group": [
"item_group",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
"total_purchase_qty",
"total_purchase_amount",
"available_qty",
"available_valuation_rate",
"available_buying_amount"
],
"customer": [
"customer",
"customer_group",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
"customer_group": [
"customer_group",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
"sales_person": [
"sales_person",
"allocated_amount",
"qty",
"base_rate",
"buying_rate",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
"project": [
"project",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
"territory": [
"territory",
"base_amount",
"buying_amount",
"gross_profit",
"gross_profit_percent",
],
}
)
columns = get_columns(group_wise_columns, filters)
if filters.group_by == 'Item Code':
for src in gross_profit_data.grouped_data:
custom_filters = {'company': filters['company'], 'from_date': filters['from_date'], 'to_date': filters['to_date']}
res = get_stock_ledger_entries(custom_filters, [src['item_code']])
stock_ledger_res = fetch_stock_ledger(custom_filters, [src['item_code']])
stock_bal_res = get_item_warehouse_map(custom_filters, stock_ledger_res)
if res:
total_purchase_qty = sum([row['actual_qty'] for row in res if row['actual_qty'] > 0])
src['available_valuation_rate'] = res[-1]['valuation_rate']
src['total_purchase_qty'] = total_purchase_qty
src['total_purchase_amount'] = total_purchase_qty * res[-1]['valuation_rate']
if stock_ledger_res and stock_bal_res:
src['available_qty'] = sum([stock_bal_res[row]['bal_qty'] for row in sorted(stock_bal_res) if row])
src['available_buying_amount'] = src['available_qty'] * src['buying_rate']
if filters.group_by == 'Item Group':
for src in gross_profit_data.grouped_data:
total_purchase_qty = 0
total_available_qty = 0
custom_filters = {'company': filters['company'], 'from_date': filters['from_date'], 'to_date': filters['to_date']}
item_list = frappe.db.get_all('Item',{'item_group':src['item_group']},'name')
for item in item_list:
res = get_stock_ledger_entries(custom_filters, [item['name']])
stock_ledger_res = fetch_stock_ledger(custom_filters, [item['name']])
stock_bal_res = get_item_warehouse_map(custom_filters, stock_ledger_res)
if res:
total_purchase_qty += sum([row['actual_qty'] for row in res if row['actual_qty'] > 0])
if stock_ledger_res and stock_bal_res:
total_available_qty += sum([stock_bal_res[row]['bal_qty'] for row in sorted(stock_bal_res) if row])
src['available_valuation_rate'] = src['buying_rate']
src['available_qty'] = total_available_qty
src['total_purchase_qty'] = total_purchase_qty
src['total_purchase_amount'] = total_purchase_qty * src['buying_rate']
src['available_buying_amount'] = total_available_qty * src['buying_rate']
if filters.group_by == 'Brand':
for src in gross_profit_data.grouped_data:
total_purchase_qty = 0
total_available_qty = 0
custom_filters = {'company': filters['company'], 'from_date': filters['from_date'], 'to_date': filters['to_date']}
item_list = frappe.db.get_all('Item',{'brand':src['brand']},'name')
for item in item_list:
res = get_stock_ledger_entries(custom_filters, [item['name']])
stock_ledger_res = fetch_stock_ledger(custom_filters, [item['name']])
stock_bal_res = get_item_warehouse_map(custom_filters, stock_ledger_res)
if res:
total_purchase_qty += sum([row['actual_qty'] for row in res if row['actual_qty'] > 0])
if stock_ledger_res and stock_bal_res:
total_available_qty += sum([stock_bal_res[row]['bal_qty'] for row in sorted(stock_bal_res) if row])
src['available_valuation_rate'] = src['buying_rate']
src['available_qty'] = total_available_qty
src['total_purchase_qty'] = total_purchase_qty
src['total_purchase_amount'] = total_purchase_qty * src['buying_rate']
src['available_buying_amount'] = total_available_qty * src['buying_rate']
for src in gross_profit_data.grouped_data:
row = []
for col in group_wise_columns.get(scrub(filters.group_by)):
row.append(src.get(col))
row.append(src.get("parenttype"))
row.append(filters.currency)
data.append(row)
return columns, data