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 = frappe.get_cached_value('Company', filters.get('company'), 'default_currency') item_list = get_items(filters, additional_query_columns) if item_list: itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency) mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list])) so_dn_map = get_delivery_notes_against_sales_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, 'Sales Invoice') customer_details = get_customer_details() item_details = get_item_details() for d in item_list: customer_record = customer_details.get(d.customer) item_record = item_details.get(d.item_code) delivery_note = None if d.delivery_note: delivery_note = d.delivery_note elif d.so_detail: delivery_note = ", ".join(so_dn_map.get(d.so_detail, [])) if not delivery_note and d.update_stock: delivery_note = d.parent 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, 'customer': d.customer, 'customer_name': customer_record.customer_name, 'customer_group': customer_record.customer_group, } if additional_query_columns: for col in additional_query_columns: row.update({col: d.get(col)}) row.update({ 'debit_to': d.debit_to, 'mode_of_payment': ", ".join(mode_of_payments.get(d.parent, [])), 'territory': d.territory, 'project': d.project, 'company': d.company, 'sales_order': d.sales_order, 'delivery_note': d.delivery_note, 'income_account': d.unrealized_profit_loss_account or d.income_account, 'cost_center': d.cost_center, 'stock_qty': d.stock_qty, 'stock_uom': d.stock_uom }) if d.stock_uom != d.uom and d.stock_qty: row.update({ 'rate': (d.base_net_rate * d.qty) / d.stock_qty, 'amount': d.base_net_amount }) else: row.update({'rate': d.base_net_rate, '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 = frappe.get_cached_value("Company", filters.get("company"), "default_currency") item_list = get_items(filters, additional_query_columns) if item_list: itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency) mode_of_payments = get_mode_of_payments(set(d.parent for d in item_list)) so_dn_map = get_delivery_notes_against_sales_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, "Sales Invoice") customer_details = get_customer_details() item_details = get_item_details() for d in item_list: customer_record = customer_details.get(d.customer) item_record = item_details.get(d.item_code) delivery_note = None if d.delivery_note: delivery_note = d.delivery_note elif d.so_detail: delivery_note = ", ".join(so_dn_map.get(d.so_detail, [])) if not delivery_note and d.update_stock: delivery_note = d.parent 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, "customer": d.customer, "customer_name": customer_record.customer_name, "customer_group": customer_record.customer_group, } if additional_query_columns: for col in additional_query_columns: row.update({col: d.get(col)}) row.update( { "debit_to": d.debit_to, "mode_of_payment": ", ".join(mode_of_payments.get(d.parent, [])), "territory": d.territory, "project": d.project, "company": d.company, "sales_order": d.sales_order, "delivery_note": d.delivery_note, "income_account": d.unrealized_profit_loss_account if d.is_internal_customer == 1 else d.income_account, "cost_center": d.cost_center, "stock_qty": d.stock_qty, "stock_uom": d.stock_uom, } ) if d.stock_uom != d.uom and d.stock_qty: row.update({"rate": (d.base_net_rate * d.qty) / d.stock_qty, "amount": d.base_net_amount}) else: row.update({"rate": d.base_net_rate, "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 = {} 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