fix: modify rows and columns for ledger view
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.js b/erpnext/accounts/report/purchase_register/purchase_register.js
index ddf84d0..57cb703 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.js
+++ b/erpnext/accounts/report/purchase_register/purchase_register.js
@@ -55,7 +55,7 @@
},
{
"fieldname": "include_payments",
- "label": __("Include Payments"),
+ "label": __("Show Ledger View"),
"fieldtype": "Check",
"default": 0
}
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.py b/erpnext/accounts/report/purchase_register/purchase_register.py
index 8e733cc..46bfa43 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -5,17 +5,19 @@
import frappe
from frappe import _, msgprint
from frappe.query_builder.custom import ConstantColumn
-from frappe.utils import flt
+from frappe.utils import flt, getdate
from pypika import Order
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
get_accounting_dimensions,
)
+from erpnext.accounts.party import get_party_account
from erpnext.accounts.report.utils import (
filter_invoices_based_on_dimensions,
get_advance_taxes_and_charges,
get_conditions,
get_journal_entries,
+ get_opening_row,
get_party_details,
get_payment_entries,
get_query_columns,
@@ -33,10 +35,10 @@
filters = {}
include_payments = filters.get("include_payments")
+ if filters.get("include_payments") and not filters.get("supplier"):
+ frappe.throw(_("Please select a supplier for fetching payments."))
invoice_list = get_invoices(filters, get_query_columns(additional_table_columns))
if filters.get("include_payments"):
- if not filters.get("supplier"):
- frappe.throw(_("Please select a supplier for fetching payments."))
invoice_list += get_payments(filters, additional_table_columns)
accounting_dimensions = get_accounting_dimensions(as_list=False)
@@ -62,6 +64,21 @@
company_currency = frappe.get_cached_value("Company", filters.company, "default_currency")
+ res = []
+ if include_payments:
+ opening_row = get_opening_row(
+ "Supplier", filters.supplier, getdate(filters.from_date), filters.company
+ )[0]
+ print(opening_row)
+ res.append(
+ {
+ "payable_account": opening_row.account,
+ "debit": flt(opening_row.debit),
+ "credit": flt(opening_row.credit),
+ "balance": flt(opening_row.balance),
+ }
+ )
+ running_balance = flt(opening_row.balance)
data = []
for inv in invoice_list:
# invoice details
@@ -69,25 +86,23 @@
purchase_receipt = list(set(invoice_po_pr_map.get(inv.name, {}).get("purchase_receipt", [])))
project = list(set(invoice_po_pr_map.get(inv.name, {}).get("project", [])))
- row = [
- inv.doctype,
- inv.name,
- inv.posting_date,
- inv.supplier,
- inv.supplier_name,
- *get_values_for_columns(additional_table_columns, inv).values(),
- supplier_details.get(inv.supplier).get("supplier_group"),
- supplier_details.get(inv.supplier).get("tax_id"),
- inv.credit_to,
- inv.mode_of_payment,
- ", ".join(project) if inv.doctype == "Purchase Invoice" else inv.project,
- inv.bill_no,
- inv.bill_date,
- inv.remarks,
- ", ".join(purchase_order),
- ", ".join(purchase_receipt),
- company_currency,
- ]
+ row = {
+ "voucher_type": inv.doctype,
+ "voucher_no": inv.name,
+ "posting_date": inv.posting_date,
+ "supplier_id": inv.supplier,
+ "supplier_name": inv.supplier_name,
+ **get_values_for_columns(additional_table_columns, inv),
+ "supplier_group": supplier_details.get(inv.supplier).get("supplier_group"),
+ "tax_id": supplier_details.get(inv.supplier).get("tax_id"),
+ "payable_account": inv.credit_to,
+ "mode_of_payment": inv.mode_of_payment,
+ "project": ", ".join(project) if inv.doctype == "Purchase Invoice" else inv.project,
+ "remarks": inv.remarks,
+ "purchase_order": ", ".join(purchase_order),
+ "purchase_receipt": ", ".join(purchase_receipt),
+ "currency": company_currency,
+ }
# map expense values
base_net_total = 0
@@ -97,14 +112,16 @@
else:
expense_amount = flt(invoice_expense_map.get(inv.name, {}).get(expense_acc))
base_net_total += expense_amount
- row.append(expense_amount)
+ row.update({frappe.scrub(expense_acc): expense_amount})
# Add amount in unrealized account
for account in unrealized_profit_loss_accounts:
- row.append(flt(internal_invoice_map.get((inv.name, account))))
+ row.update(
+ {frappe.scrub(account + "_unrealized"): flt(internal_invoice_map.get((inv.name, account)))}
+ )
# net total
- row.append(base_net_total or inv.base_net_total)
+ row.update({"net_total": base_net_total or inv.base_net_total})
# tax account
total_tax = 0
@@ -112,13 +129,29 @@
if tax_acc not in expense_accounts:
tax_amount = flt(invoice_tax_map.get(inv.name, {}).get(tax_acc))
total_tax += tax_amount
- row.append(tax_amount)
+ row.update({frappe.scrub(tax_acc): tax_amount})
# total tax, grand total, rounded total & outstanding amount
- row += [total_tax, inv.base_grand_total, flt(inv.base_grand_total, 0), inv.outstanding_amount]
+ row.update(
+ {
+ "total_tax": total_tax,
+ "garnd_total": inv.base_grand_total,
+ "rounded_total": inv.base_rounded_total,
+ "outstanding_amount": inv.outstanding_amount,
+ }
+ )
+
+ if inv.doctype == "Purchase Invoice":
+ row.update({"debit": inv.base_grand_total, "credit": 0.0})
+ else:
+ row.update({"debit": 0.0, "credit": inv.base_grand_total})
+ if include_payments:
+ running_balance += row["debit"] - row["credit"]
+ row.update({"balance": running_balance})
data.append(row)
- return columns, sorted(data, key=lambda x: x[2])
+ res += sorted(data, key=lambda x: x["posting_date"])
+ return columns, res, None, None, None, include_payments
def get_columns(invoice_list, additional_table_columns, include_payments=False):
@@ -134,20 +167,48 @@
if additional_table_columns:
columns += additional_table_columns
- columns += [
- _("Supplier Group") + ":Link/Supplier Group:120",
- _("Tax Id") + "::50",
- _("Payable Account") + ":Link/Account:120",
- _("Mode of Payment") + ":Link/Mode of Payment:80",
- _("Project") + ":Link/Project:80",
- _("Bill No") + "::120",
- _("Bill Date") + ":Date:80",
- _("Remarks") + "::150",
- _("Purchase Order") + ":Link/Purchase Order:100",
- _("Purchase Receipt") + ":Link/Purchase Receipt:100",
- {"fieldname": "currency", "label": _("Currency"), "fieldtype": "Data", "width": 80},
- ]
+ if not include_payments:
+ columns += [
+ _("Supplier Group") + ":Link/Supplier Group:120",
+ _("Tax Id") + "::50",
+ _("Payable Account") + ":Link/Account:120",
+ _("Mode of Payment") + ":Link/Mode of Payment:80",
+ _("Project") + ":Link/Project:80",
+ _("Bill No") + "::120",
+ _("Bill Date") + ":Date:80",
+ _("Purchase Order") + ":Link/Purchase Order:100",
+ _("Purchase Receipt") + ":Link/Purchase Receipt:100",
+ {"fieldname": "currency", "label": _("Currency"), "fieldtype": "Data", "width": 80},
+ ]
+ else:
+ columns += [
+ _("Payable Account") + ":Link/Account:120",
+ _("Debit") + ":Currency/currency:120",
+ _("Credit") + ":Currency/currency:120",
+ _("Balance") + ":Currency/currency:120",
+ ]
+ account_columns, accounts = get_account_columns(invoice_list, include_payments)
+
+ columns = (
+ columns
+ + account_columns[0]
+ + account_columns[1]
+ + [_("Net Total") + ":Currency/currency:120"]
+ + account_columns[2]
+ + [_("Total Tax") + ":Currency/currency:120"]
+ )
+
+ if not include_payments:
+ columns += [
+ _("Rounded Total") + ":Currency/currency:120",
+ _("Outstanding Amount") + ":Currency/currency:120",
+ ]
+ columns += [_("Remarks") + "::120"]
+ return columns, accounts[0], accounts[2], accounts[1]
+
+
+def get_account_columns(invoice_list, include_payments):
expense_accounts = []
tax_accounts = []
unrealized_profit_loss_accounts = []
@@ -194,21 +255,10 @@
if account not in expense_accounts
]
- columns = (
- columns
- + expense_columns
- + unrealized_profit_loss_account_columns
- + [_("Net Total") + ":Currency/currency:120"]
- + tax_columns
- + [
- _("Total Tax") + ":Currency/currency:120",
- _("Grand Total") + ":Currency/currency:120",
- _("Rounded Total") + ":Currency/currency:120",
- _("Outstanding Amount") + ":Currency/currency:120",
- ]
- )
+ columns = [expense_columns, unrealized_profit_loss_account_columns, tax_columns]
+ accounts = [expense_accounts, unrealized_profit_loss_accounts, tax_accounts]
- return columns, expense_accounts, tax_accounts, unrealized_profit_loss_accounts
+ return columns, accounts
def get_invoices(filters, additional_query_columns):
@@ -240,6 +290,11 @@
if filters.get("supplier"):
query = query.where(pi.supplier == filters.supplier)
query = get_conditions(filters, query, [pi, invoice_item])
+ if filters.get("include_payments"):
+ party_account = get_party_account(
+ "Supplier", filters.get("supplier"), filters.get("company"), include_advance=True
+ )
+ query = query.where(pi.credit_to.isin(party_account))
invoices = query.run(as_dict=True)
return invoices
@@ -252,6 +307,9 @@
account="credit_to",
party="supplier",
party_name="supplier_name",
+ party_account=get_party_account(
+ "Supplier", filters.supplier, filters.company, include_advance=True
+ ),
additional_query_columns="" if not additional_query_columns else additional_query_columns,
)
payment_entries = get_payment_entries(filters, args)
diff --git a/erpnext/accounts/report/sales_register/sales_register.js b/erpnext/accounts/report/sales_register/sales_register.js
index 3c879ca..1a41172 100644
--- a/erpnext/accounts/report/sales_register/sales_register.js
+++ b/erpnext/accounts/report/sales_register/sales_register.js
@@ -67,7 +67,7 @@
},
{
"fieldname": "include_payments",
- "label": __("Include Payments"),
+ "label": __("Show Ledger View"),
"fieldtype": "Check",
"default": 0
}
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index fd82555..6112219 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -6,17 +6,19 @@
from frappe import _, msgprint
from frappe.model.meta import get_field_precision
from frappe.query_builder.custom import ConstantColumn
-from frappe.utils import flt
+from frappe.utils import flt, getdate
from pypika import Order
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
get_accounting_dimensions,
)
+from erpnext.accounts.party import get_party_account
from erpnext.accounts.report.utils import (
filter_invoices_based_on_dimensions,
get_advance_taxes_and_charges,
get_conditions,
get_journal_entries,
+ get_opening_row,
get_party_details,
get_payment_entries,
get_query_columns,
@@ -34,10 +36,10 @@
filters = frappe._dict({})
include_payments = filters.get("include_payments")
+ if filters.get("include_payments") and not filters.get("customer"):
+ frappe.throw(_("Please select a customer for fetching payments."))
invoice_list = get_invoices(filters, get_query_columns(additional_table_columns))
if filters.get("include_payments"):
- if not filters.get("customer"):
- frappe.throw(_("Please select a customer for fetching payments."))
invoice_list += get_payments(filters, additional_table_columns)
accounting_dimensions = get_accounting_dimensions(as_list=False)
@@ -65,6 +67,20 @@
customers = list(set(d.customer for d in invoice_list))
customer_details = get_party_details("Customer", customers)
+ res = []
+ if include_payments:
+ opening_row = get_opening_row(
+ "Customer", filters.customer, getdate(filters.from_date), filters.company
+ )[0]
+ res.append(
+ {
+ "receivable_account": opening_row,
+ "debit": flt(opening_row.debit),
+ "credit": flt(opening_row.credit),
+ "balance": flt(opening_row.balance),
+ }
+ )
+ running_balance = flt(opening_row.balance)
data = []
for inv in invoice_list:
# invoice details
@@ -75,7 +91,7 @@
row = {
"voucher_type": inv.doctype,
- "invoice": inv.name,
+ "voucher_no": inv.name,
"posting_date": inv.posting_date,
"customer": inv.customer,
"customer_name": inv.customer_name,
@@ -140,9 +156,17 @@
}
)
+ if inv.doctype == "Sales Invoice":
+ row.update({"debit": inv.base_grand_total, "credit": 0.0})
+ else:
+ row.update({"debit": 0.0, "credit": inv.base_grand_total})
+ if include_payments:
+ running_balance += row["debit"] - row["credit"]
+ row.update({"balance": running_balance})
data.append(row)
- return columns, sorted(data, key=lambda x: x["posting_date"])
+ res += sorted(data, key=lambda x: x["posting_date"])
+ return columns, res, None, None, None, include_payments
def get_columns(invoice_list, additional_table_columns, include_payments=False):
@@ -155,7 +179,7 @@
},
{
"label": _("Voucher"),
- "fieldname": "invoice",
+ "fieldname": "voucher_no",
"fieldtype": "Dynamic Link",
"options": "voucher_type",
"width": 120,
@@ -174,80 +198,147 @@
if additional_table_columns:
columns += additional_table_columns
- columns += [
+ if not include_payments:
+ columns += [
+ {
+ "label": _("Customer Group"),
+ "fieldname": "customer_group",
+ "fieldtype": "Link",
+ "options": "Customer Group",
+ "width": 120,
+ },
+ {
+ "label": _("Territory"),
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "options": "Territory",
+ "width": 80,
+ },
+ {"label": _("Tax Id"), "fieldname": "tax_id", "fieldtype": "Data", "width": 80},
+ {
+ "label": _("Receivable Account"),
+ "fieldname": "receivable_account",
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 100,
+ },
+ {
+ "label": _("Mode Of Payment"),
+ "fieldname": "mode_of_payment",
+ "fieldtype": "Data",
+ "width": 120,
+ },
+ {
+ "label": _("Project"),
+ "fieldname": "project",
+ "fieldtype": "Link",
+ "options": "Project",
+ "width": 80,
+ },
+ {"label": _("Owner"), "fieldname": "owner", "fieldtype": "Data", "width": 100},
+ {
+ "label": _("Sales Order"),
+ "fieldname": "sales_order",
+ "fieldtype": "Link",
+ "options": "Sales Order",
+ "width": 100,
+ },
+ {
+ "label": _("Delivery Note"),
+ "fieldname": "delivery_note",
+ "fieldtype": "Link",
+ "options": "Delivery Note",
+ "width": 100,
+ },
+ {
+ "label": _("Cost Center"),
+ "fieldname": "cost_center",
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ "width": 100,
+ },
+ {
+ "label": _("Warehouse"),
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": 100,
+ },
+ {"fieldname": "currency", "label": _("Currency"), "fieldtype": "Data", "width": 80},
+ ]
+ else:
+ columns += [
+ _("Receivable Account") + ":Link/Account:120",
+ _("Debit") + ":Currency/currency:120",
+ _("Credit") + ":Currency/currency:120",
+ _("Balance") + ":Currency/currency:120",
+ ]
+
+ account_columns, accounts = get_account_columns(invoice_list, include_payments)
+
+ net_total_column = [
{
- "label": _("Customer Group"),
- "fieldname": "customer_group",
- "fieldtype": "Link",
- "options": "Customer Group",
+ "label": _("Net Total"),
+ "fieldname": "net_total",
+ "fieldtype": "Currency",
+ "options": "currency",
"width": 120,
- },
- {
- "label": _("Territory"),
- "fieldname": "territory",
- "fieldtype": "Link",
- "options": "Territory",
- "width": 80,
- },
- {"label": _("Tax Id"), "fieldname": "tax_id", "fieldtype": "Data", "width": 80},
- {
- "label": _("Receivable Account"),
- "fieldname": "receivable_account",
- "fieldtype": "Link",
- "options": "Account",
- "width": 100,
- },
- {
- "label": _("Mode Of Payment"),
- "fieldname": "mode_of_payment",
- "fieldtype": "Data",
- "width": 120,
- },
- {
- "label": _("Project"),
- "fieldname": "project",
- "fieldtype": "Link",
- "options": "Project",
- "width": 80,
- },
- {"label": _("Owner"), "fieldname": "owner", "fieldtype": "Data", "width": 100},
- {"label": _("Remarks"), "fieldname": "remarks", "fieldtype": "Data", "width": 150},
- {
- "label": _("Sales Order"),
- "fieldname": "sales_order",
- "fieldtype": "Link",
- "options": "Sales Order",
- "width": 100,
- },
- {
- "label": _("Delivery Note"),
- "fieldname": "delivery_note",
- "fieldtype": "Link",
- "options": "Delivery Note",
- "width": 100,
- },
- {
- "label": _("Cost Center"),
- "fieldname": "cost_center",
- "fieldtype": "Link",
- "options": "Cost Center",
- "width": 100,
- },
- {
- "label": _("Warehouse"),
- "fieldname": "warehouse",
- "fieldtype": "Link",
- "options": "Warehouse",
- "width": 100,
- },
- {"fieldname": "currency", "label": _("Currency"), "fieldtype": "Data", "width": 80},
+ }
]
+ total_columns = [
+ {
+ "label": _("Tax Total"),
+ "fieldname": "tax_total",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ ]
+ if not include_payments:
+ total_columns += [
+ {
+ "label": _("Grand Total"),
+ "fieldname": "grand_total",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ },
+ {
+ "label": _("Rounded Total"),
+ "fieldname": "rounded_total",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ },
+ {
+ "label": _("Outstanding Amount"),
+ "fieldname": "outstanding_amount",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ },
+ ]
+
+ columns = (
+ columns
+ + account_columns[0]
+ + account_columns[2]
+ + net_total_column
+ + account_columns[1]
+ + total_columns
+ )
+ columns += [{"label": _("Remarks"), "fieldname": "remarks", "fieldtype": "Data", "width": 150}]
+ return columns, accounts[0], accounts[1], accounts[2]
+
+
+def get_account_columns(invoice_list, include_payments):
income_accounts = []
tax_accounts = []
+ unrealized_profit_loss_accounts = []
+
income_columns = []
tax_columns = []
- unrealized_profit_loss_accounts = []
unrealized_profit_loss_account_columns = []
if invoice_list:
@@ -314,57 +405,10 @@
}
)
- net_total_column = [
- {
- "label": _("Net Total"),
- "fieldname": "net_total",
- "fieldtype": "Currency",
- "options": "currency",
- "width": 120,
- }
- ]
+ columns = [income_columns, unrealized_profit_loss_account_columns, tax_columns]
+ accounts = [income_accounts, unrealized_profit_loss_accounts, tax_accounts]
- total_columns = [
- {
- "label": _("Tax Total"),
- "fieldname": "tax_total",
- "fieldtype": "Currency",
- "options": "currency",
- "width": 120,
- },
- {
- "label": _("Grand Total"),
- "fieldname": "grand_total",
- "fieldtype": "Currency",
- "options": "currency",
- "width": 120,
- },
- {
- "label": _("Rounded Total"),
- "fieldname": "rounded_total",
- "fieldtype": "Currency",
- "options": "currency",
- "width": 120,
- },
- {
- "label": _("Outstanding Amount"),
- "fieldname": "outstanding_amount",
- "fieldtype": "Currency",
- "options": "currency",
- "width": 120,
- },
- ]
-
- columns = (
- columns
- + income_columns
- + unrealized_profit_loss_account_columns
- + net_total_column
- + tax_columns
- + total_columns
- )
-
- return columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts
+ return columns, accounts
def get_invoices(filters, additional_query_columns):
@@ -416,6 +460,9 @@
account="debit_to",
party="customer",
party_name="customer_name",
+ party_account=get_party_account(
+ "Customer", filters.customer, filters.company, include_advance=True
+ ),
additional_query_columns="" if not additional_query_columns else additional_query_columns,
)
payment_entries = get_payment_entries(filters, args)
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index 32ca9fa..6c7338e 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -9,6 +9,7 @@
get_dimension_with_children,
)
from erpnext.accounts.doctype.fiscal_year.fiscal_year import get_from_and_to_date
+from erpnext.accounts.party import get_party_account
from erpnext.setup.utils import get_exchange_rate
__exchange_rates = {}
@@ -247,7 +248,11 @@
je.mode_of_payment,
journal_account.project,
)
- .where((je.voucher_type == "Journal Entry") & (journal_account.party == filters.get(args.party)))
+ .where(
+ (je.voucher_type == "Journal Entry")
+ & (journal_account.party == filters.get(args.party))
+ & (journal_account.account.isin(args.party_account))
+ )
.orderby(je.posting_date, je.name, order=Order.desc)
)
query = get_conditions(filters, query, [je], payments=True)
@@ -273,7 +278,7 @@
pe.project,
pe.cost_center,
)
- .where((pe.party == filters.get(args.party)))
+ .where((pe.party == filters.get(args.party)) & (pe.paid_to.isin(args.party_account)))
.orderby(pe.posting_date, pe.name, order=Order.desc)
)
query = get_conditions(filters, query, [pe], payments=True)
@@ -356,3 +361,18 @@
else:
invoices_with_acc_dimensions.append(inv)
return invoices_with_acc_dimensions
+
+
+def get_opening_row(party_type, party, from_date, company):
+ party_account = get_party_account(party_type, party, company, include_advance=True)
+ gle = frappe.qb.DocType("GL Entry")
+ return (
+ frappe.qb.from_(gle)
+ .select(
+ ConstantColumn("Opening").as_("account"),
+ Sum(gle.debit).as_("debit"),
+ Sum(gle.credit).as_("credit"),
+ (Sum(gle.debit) - Sum(gle.credit)).as_("balance"),
+ )
+ .where((gle.account.isin(party_account)) & (gle.posting_date < from_date))
+ ).run(as_dict=True)