def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
filters.update({"from_date": filters.get("date_range")[0], "to_date": filters.get("date_range")[1]})
columns = get_columns(additional_table_columns, filters)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency,
doctype='Purchase Invoice', tax_doctype='Purchase Taxes and Charges')
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ''
if filters.get('group_by'):
grand_total = get_grand_total(filters, 'Purchase Invoice')
item_details = get_item_details()
for d in item_list:
if not d.stock_qty:
continue
item_record = item_details.get(d.item_code)
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = {
'item_code': d.item_code,
'item_name': item_record.item_name if item_record else d.item_name,
'item_group': item_record.item_group if item_record else d.item_group,
'description': d.description,
'invoice': d.parent,
'posting_date': d.posting_date,
'supplier': d.supplier,
'supplier_name': d.supplier_name
}
if additional_query_columns:
for col in additional_query_columns:
row.update({
col: d.get(col)
})
row.update({
'credit_to': d.credit_to,
'mode_of_payment': d.mode_of_payment,
'project': d.project,
'company': d.company,
'purchase_order': d.purchase_order,
'purchase_receipt': d.purchase_receipt,
'expense_account': expense_account,
'stock_qty': d.stock_qty,
'stock_uom': d.stock_uom,
'rate': d.base_net_amount / d.stock_qty,
'amount': d.base_net_amount
})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update({
frappe.scrub(tax + ' Rate'): item_tax.get('tax_rate', 0),
frappe.scrub(tax + ' Amount'): item_tax.get('tax_amount', 0),
})
total_tax += flt(item_tax.get('tax_amount'))
row.update({
'total_tax': total_tax,
'total': d.base_net_amount + total_tax,
'currency': company_currency
})
if filters.get('group_by'):
row.update({'percent_gt': flt(row['total']/grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(filters)
data, prev_group_by_value = add_total_row(data, filters, prev_group_by_value, d, total_row_map,
group_by_field, subtotal_display_field, grand_total, tax_columns)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ''), tax_columns)
data.append(row)
if filters.get('group_by') and item_list:
total_row = total_row_map.get(prev_group_by_value or d.get('item_name'))
total_row['percent_gt'] = flt(total_row['total']/grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, 'total_row', tax_columns)
data.append(total_row_map.get('total_row'))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters:
filters = {}
columns = get_columns(additional_table_columns, filters)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(
item_list,
columns,
company_currency,
doctype="Purchase Invoice",
tax_doctype="Purchase Taxes and Charges",
)
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ""
if filters.get("group_by"):
grand_total = get_grand_total(filters, "Purchase Invoice")
item_details = get_item_details()
for d in item_list:
if not d.stock_qty:
continue
item_record = item_details.get(d.item_code)
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = (d.unrealized_profit_loss_account
or d.expense_account
or aii_account_map.get(d.company))
row = {
"item_code": d.item_code,
"item_name": item_record.item_name if item_record else d.item_name,
"item_group":
item_record.item_group if item_record else d.item_group,
"description": d.description,
"invoice": d.parent,
"posting_date": d.posting_date,
"supplier": d.supplier,
"supplier_name": d.supplier_name,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update({
"credit_to": d.credit_to,
"mode_of_payment": d.mode_of_payment,
"project": d.project,
"company": d.company,
"purchase_order": d.purchase_order,
"purchase_receipt": d.purchase_receipt,
"expense_account": expense_account,
"stock_qty": d.stock_qty,
"stock_uom": d.stock_uom,
"rate": d.base_net_amount / d.stock_qty,
"amount": d.base_net_amount,
})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update({
frappe.scrub(tax + " Rate"):
item_tax.get("tax_rate", 0),
frappe.scrub(tax + " Amount"):
item_tax.get("tax_amount", 0),
})
total_tax += flt(item_tax.get("tax_amount"))
row.update({
"total_tax": total_tax,
"total": d.base_net_amount + total_tax,
"currency": company_currency
})
if filters.get("group_by"):
row.update({"percent_gt": flt(row["total"] / grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(
filters)
data, prev_group_by_value = add_total_row(
data,
filters,
prev_group_by_value,
d,
total_row_map,
group_by_field,
subtotal_display_field,
grand_total,
tax_columns,
)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ""),
tax_columns)
data.append(row)
if filters.get("group_by") and item_list:
total_row = total_row_map.get(prev_group_by_value
or d.get("item_name"))
total_row["percent_gt"] = flt(total_row["total"] / grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, "total_row", tax_columns)
data.append(total_row_map.get("total_row"))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row