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