Merge pull request #36069 from GursheenK/purchase-sales-register-with-PE/JE
feat: include payments in purchase / sales register report
diff --git a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
index 050e6bc..8b929bf 100644
--- a/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
+++ b/erpnext/accounts/report/item_wise_purchase_register/item_wise_purchase_register.py
@@ -309,7 +309,8 @@
def get_items(filters, additional_query_columns):
conditions = get_conditions(filters)
-
+ if additional_query_columns:
+ additional_query_columns = "," + ",".join(additional_query_columns)
return frappe.db.sql(
"""
select
diff --git a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
index 4d24dd9..1e7ac33 100644
--- a/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
+++ b/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py
@@ -381,7 +381,8 @@
def get_items(filters, additional_query_columns, additional_conditions=None):
conditions = get_conditions(filters, additional_conditions)
-
+ if additional_query_columns:
+ additional_query_columns = "," + ",".join(additional_query_columns)
return frappe.db.sql(
"""
select
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.js b/erpnext/accounts/report/purchase_register/purchase_register.js
index aaf76c4..57cb703 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.js
+++ b/erpnext/accounts/report/purchase_register/purchase_register.js
@@ -52,6 +52,12 @@
"label": __("Item Group"),
"fieldtype": "Link",
"options": "Item Group"
+ },
+ {
+ "fieldname": "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 69827ac..c7b7e2f 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -4,13 +4,22 @@
import frappe
from frappe import _, msgprint
-from frappe.utils import flt
+from frappe.query_builder.custom import ConstantColumn
+from frappe.utils import flt, getdate
+from pypika import Order
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
- get_accounting_dimensions,
- get_dimension_with_children,
+from erpnext.accounts.party import get_party_account
+from erpnext.accounts.report.utils import (
+ get_advance_taxes_and_charges,
+ get_conditions,
+ get_journal_entries,
+ get_opening_row,
+ get_party_details,
+ get_payment_entries,
+ get_query_columns,
+ get_taxes_query,
+ get_values_for_columns,
)
-from erpnext.accounts.report.utils import get_query_columns, get_values_for_columns
def execute(filters=None):
@@ -21,9 +30,15 @@
if not filters:
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"):
+ invoice_list += get_payments(filters)
+
columns, expense_accounts, tax_accounts, unrealized_profit_loss_accounts = get_columns(
- invoice_list, additional_table_columns
+ invoice_list, additional_table_columns, include_payments
)
if not invoice_list:
@@ -33,14 +48,28 @@
invoice_expense_map = get_invoice_expense_map(invoice_list)
internal_invoice_map = get_internal_invoice_map(invoice_list)
invoice_expense_map, invoice_tax_map = get_invoice_tax_map(
- invoice_list, invoice_expense_map, expense_accounts
+ invoice_list, invoice_expense_map, expense_accounts, include_payments
)
invoice_po_pr_map = get_invoice_po_pr_map(invoice_list)
suppliers = list(set(d.supplier for d in invoice_list))
- supplier_details = get_supplier_details(suppliers)
+ supplier_details = get_party_details("Supplier", suppliers)
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]
+ res.append(
+ {
+ "payable_account": opening_row.account,
+ "debit": flt(opening_row.debit),
+ "credit": flt(opening_row.credit),
+ "balance": flt(opening_row.balance),
+ }
+ )
+
data = []
for inv in invoice_list:
# invoice details
@@ -48,24 +77,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.name,
- inv.posting_date,
- inv.supplier,
- inv.supplier_name,
- *get_values_for_columns(additional_table_columns, inv).values(),
- supplier_details.get(inv.supplier), # supplier_group
- inv.tax_id,
- inv.credit_to,
- inv.mode_of_payment,
- ", ".join(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
@@ -75,14 +103,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
@@ -90,45 +120,190 @@
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,
+ "grand_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})
data.append(row)
- return columns, data
+ res += sorted(data, key=lambda x: x["posting_date"])
+
+ if include_payments:
+ running_balance = flt(opening_row.balance)
+ for row in range(1, len(res)):
+ running_balance += res[row]["debit"] - res[row]["credit"]
+ res[row].update({"balance": running_balance})
+
+ return columns, res, None, None, None, include_payments
-def get_columns(invoice_list, additional_table_columns):
+def get_columns(invoice_list, additional_table_columns, include_payments=False):
"""return columns based on filters"""
columns = [
- _("Invoice") + ":Link/Purchase Invoice:120",
- _("Posting Date") + ":Date:80",
- _("Supplier Id") + "::120",
- _("Supplier Name") + "::120",
+ {
+ "label": _("Voucher Type"),
+ "fieldname": "voucher_type",
+ "width": 120,
+ },
+ {
+ "label": _("Voucher"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 120,
+ },
+ {"label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 80},
+ {
+ "label": _("Supplier"),
+ "fieldname": "supplier_id",
+ "fieldtype": "Link",
+ "options": "Supplier",
+ "width": 120,
+ },
+ {"label": _("Supplier Name"), "fieldname": "supplier_name", "fieldtype": "Data", "width": 120},
]
- if additional_table_columns:
+ if additional_table_columns and not include_payments:
columns += additional_table_columns
- columns += [
- _("Supplier Group") + ":Link/Supplier Group:120",
- _("Tax Id") + "::80",
- _("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 += [
+ {
+ "label": _("Supplier Group"),
+ "fieldname": "supplier_group",
+ "fieldtype": "Link",
+ "options": "Supplier Group",
+ "width": 120,
+ },
+ {"label": _("Tax Id"), "fieldname": "tax_id", "fieldtype": "Data", "width": 80},
+ {
+ "label": _("Payable Account"),
+ "fieldname": "payable_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": _("Bill No"), "fieldname": "bill_no", "fieldtype": "Data", "width": 120},
+ {"label": _("Bill Date"), "fieldname": "bill_date", "fieldtype": "Date", "width": 80},
+ {
+ "label": _("Purchase Order"),
+ "fieldname": "purchase_order",
+ "fieldtype": "Link",
+ "options": "Purchase Order",
+ "width": 100,
+ },
+ {
+ "label": _("Purchase Receipt"),
+ "fieldname": "purchase_receipt",
+ "fieldtype": "Link",
+ "options": "Purchase Receipt",
+ "width": 100,
+ },
+ {"fieldname": "currency", "label": _("Currency"), "fieldtype": "Data", "width": 80},
+ ]
+ else:
+ columns += [
+ {
+ "fieldname": "payable_account",
+ "label": _("Payable Account"),
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 120,
+ },
+ {"fieldname": "debit", "label": _("Debit"), "fieldtype": "Currency", "width": 120},
+ {"fieldname": "credit", "label": _("Credit"), "fieldtype": "Currency", "width": 120},
+ {"fieldname": "balance", "label": _("Balance"), "fieldtype": "Currency", "width": 120},
+ ]
+ account_columns, accounts = get_account_columns(invoice_list, include_payments)
+
+ columns = (
+ columns
+ + account_columns[0]
+ + account_columns[1]
+ + [
+ {
+ "label": _("Net Total"),
+ "fieldname": "net_total",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ ]
+ + account_columns[2]
+ + [
+ {
+ "label": _("Total Tax"),
+ "fieldname": "total_tax",
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ ]
+ )
+
+ if not include_payments:
+ 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 += [{"label": _("Remarks"), "fieldname": "remarks", "fieldtype": "Data", "width": 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 = []
+ expense_columns = []
+ tax_columns = []
+ unrealized_profit_loss_account_columns = []
+
if invoice_list:
expense_accounts = frappe.db.sql_list(
"""select distinct expense_account
@@ -139,15 +314,18 @@
tuple([inv.name for inv in invoice_list]),
)
- tax_accounts = frappe.db.sql_list(
- """select distinct account_head
- from `tabPurchase Taxes and Charges` where parenttype = 'Purchase Invoice'
- and docstatus = 1 and (account_head is not null and account_head != '')
- and category in ('Total', 'Valuation and Total')
- and parent in (%s) order by account_head"""
- % ", ".join(["%s"] * len(invoice_list)),
- tuple(inv.name for inv in invoice_list),
+ purchase_taxes_query = get_taxes_query(
+ invoice_list, "Purchase Taxes and Charges", "Purchase Invoice"
)
+ purchase_tax_accounts = purchase_taxes_query.run(as_dict=True, pluck="account_head")
+ tax_accounts = purchase_tax_accounts
+
+ if include_payments:
+ advance_taxes_query = get_taxes_query(
+ invoice_list, "Advance Taxes and Charges", "Payment Entry"
+ )
+ advance_tax_accounts = advance_taxes_query.run(as_dict=True, pluck="account_head")
+ tax_accounts = set(tax_accounts + advance_tax_accounts)
unrealized_profit_loss_accounts = frappe.db.sql_list(
"""SELECT distinct unrealized_profit_loss_account
@@ -158,107 +336,102 @@
tuple(inv.name for inv in invoice_list),
)
- expense_columns = [(account + ":Currency/currency:120") for account in expense_accounts]
- unrealized_profit_loss_account_columns = [
- (account + ":Currency/currency:120") for account in unrealized_profit_loss_accounts
- ]
- tax_columns = [
- (account + ":Currency/currency:120")
- for account in tax_accounts
- if account not in expense_accounts
- ]
+ for account in expense_accounts:
+ expense_columns.append(
+ {
+ "label": account,
+ "fieldname": frappe.scrub(account),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ )
- 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",
- ]
- )
+ for account in tax_accounts:
+ if account not in expense_accounts:
+ tax_columns.append(
+ {
+ "label": account,
+ "fieldname": frappe.scrub(account),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ )
- return columns, expense_accounts, tax_accounts, unrealized_profit_loss_accounts
+ for account in unrealized_profit_loss_accounts:
+ unrealized_profit_loss_account_columns.append(
+ {
+ "label": account,
+ "fieldname": frappe.scrub(account),
+ "fieldtype": "Currency",
+ "options": "currency",
+ "width": 120,
+ }
+ )
+ columns = [expense_columns, unrealized_profit_loss_account_columns, tax_columns]
+ accounts = [expense_accounts, unrealized_profit_loss_accounts, tax_accounts]
-def get_conditions(filters):
- conditions = ""
-
- if filters.get("company"):
- conditions += " and company=%(company)s"
- if filters.get("supplier"):
- conditions += " and supplier = %(supplier)s"
-
- if filters.get("from_date"):
- conditions += " and posting_date>=%(from_date)s"
- if filters.get("to_date"):
- conditions += " and posting_date<=%(to_date)s"
-
- if filters.get("mode_of_payment"):
- conditions += " and ifnull(mode_of_payment, '') = %(mode_of_payment)s"
-
- if filters.get("cost_center"):
- conditions += """ and exists(select name from `tabPurchase Invoice Item`
- where parent=`tabPurchase Invoice`.name
- and ifnull(`tabPurchase Invoice Item`.cost_center, '') = %(cost_center)s)"""
-
- if filters.get("warehouse"):
- conditions += """ and exists(select name from `tabPurchase Invoice Item`
- where parent=`tabPurchase Invoice`.name
- and ifnull(`tabPurchase Invoice Item`.warehouse, '') = %(warehouse)s)"""
-
- if filters.get("item_group"):
- conditions += """ and exists(select name from `tabPurchase Invoice Item`
- where parent=`tabPurchase Invoice`.name
- and ifnull(`tabPurchase Invoice Item`.item_group, '') = %(item_group)s)"""
-
- accounting_dimensions = get_accounting_dimensions(as_list=False)
-
- if accounting_dimensions:
- common_condition = """
- and exists(select name from `tabPurchase Invoice Item`
- where parent=`tabPurchase Invoice`.name
- """
- for dimension in accounting_dimensions:
- if filters.get(dimension.fieldname):
- if frappe.get_cached_value("DocType", dimension.document_type, "is_tree"):
- filters[dimension.fieldname] = get_dimension_with_children(
- dimension.document_type, filters.get(dimension.fieldname)
- )
-
- conditions += (
- common_condition
- + "and ifnull(`tabPurchase Invoice`.{0}, '') in %({0})s)".format(dimension.fieldname)
- )
- else:
- conditions += (
- common_condition
- + "and ifnull(`tabPurchase Invoice`.{0}, '') in %({0})s)".format(dimension.fieldname)
- )
-
- return conditions
+ return columns, accounts
def get_invoices(filters, additional_query_columns):
- conditions = get_conditions(filters)
- return frappe.db.sql(
- """
- select
- name, posting_date, credit_to, supplier, supplier_name, tax_id, bill_no, bill_date,
- remarks, base_net_total, base_grand_total, outstanding_amount,
- mode_of_payment {0}
- from `tabPurchase Invoice`
- where docstatus = 1 {1}
- order by posting_date desc, name desc""".format(
- additional_query_columns, conditions
- ),
- filters,
- as_dict=1,
+ pi = frappe.qb.DocType("Purchase Invoice")
+ invoice_item = frappe.qb.DocType("Purchase Invoice Item")
+ query = (
+ frappe.qb.from_(pi)
+ .inner_join(invoice_item)
+ .on(pi.name == invoice_item.parent)
+ .select(
+ ConstantColumn("Purchase Invoice").as_("doctype"),
+ pi.name,
+ pi.posting_date,
+ pi.credit_to,
+ pi.supplier,
+ pi.supplier_name,
+ pi.tax_id,
+ pi.bill_no,
+ pi.bill_date,
+ pi.remarks,
+ pi.base_net_total,
+ pi.base_grand_total,
+ pi.outstanding_amount,
+ pi.mode_of_payment,
+ )
+ .where((pi.docstatus == 1))
+ .orderby(pi.posting_date, pi.name, order=Order.desc)
)
+ if additional_query_columns:
+ for col in additional_query_columns:
+ query = query.select(col)
+ if filters.get("supplier"):
+ query = query.where(pi.supplier == filters.supplier)
+ query = get_conditions(
+ filters, query, doctype="Purchase Invoice", child_doctype="Purchase 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
+
+
+def get_payments(filters):
+ args = frappe._dict(
+ account="credit_to",
+ account_fieldname="paid_to",
+ party="supplier",
+ party_name="supplier_name",
+ party_account=get_party_account(
+ "Supplier", filters.supplier, filters.company, include_advance=True
+ ),
+ )
+ payment_entries = get_payment_entries(filters, args)
+ journal_entries = get_journal_entries(filters, args)
+ return payment_entries + journal_entries
def get_invoice_expense_map(invoice_list):
@@ -300,7 +473,9 @@
return internal_invoice_map
-def get_invoice_tax_map(invoice_list, invoice_expense_map, expense_accounts):
+def get_invoice_tax_map(
+ invoice_list, invoice_expense_map, expense_accounts, include_payments=False
+):
tax_details = frappe.db.sql(
"""
select parent, account_head, case add_deduct_tax when "Add" then sum(base_tax_amount_after_discount_amount)
@@ -315,6 +490,9 @@
as_dict=1,
)
+ if include_payments:
+ tax_details += get_advance_taxes_and_charges(invoice_list)
+
invoice_tax_map = {}
for d in tax_details:
if d.account_head in expense_accounts:
@@ -382,17 +560,3 @@
account_map[acc.name] = acc.parent_account
return account_map
-
-
-def get_supplier_details(suppliers):
- supplier_details = {}
- for supp in frappe.db.sql(
- """select name, supplier_group from `tabSupplier`
- where name in (%s)"""
- % ", ".join(["%s"] * len(suppliers)),
- tuple(suppliers),
- as_dict=1,
- ):
- supplier_details.setdefault(supp.name, supp.supplier_group)
-
- return supplier_details
diff --git a/erpnext/accounts/report/purchase_register/test_purchase_register.py b/erpnext/accounts/report/purchase_register/test_purchase_register.py
new file mode 100644
index 0000000..6903662
--- /dev/null
+++ b/erpnext/accounts/report/purchase_register/test_purchase_register.py
@@ -0,0 +1,128 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
+# MIT License. See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_months, getdate, today
+
+from erpnext.accounts.report.purchase_register.purchase_register import execute
+
+
+class TestPurchaseRegister(FrappeTestCase):
+ def test_purchase_register(self):
+ frappe.db.sql("delete from `tabPurchase Invoice` where company='_Test Company 6'")
+ frappe.db.sql("delete from `tabGL Entry` where company='_Test Company 6'")
+
+ filters = frappe._dict(
+ company="_Test Company 6", from_date=add_months(today(), -1), to_date=today()
+ )
+
+ pi = make_purchase_invoice()
+
+ report_results = execute(filters)
+ first_row = frappe._dict(report_results[1][0])
+ self.assertEqual(first_row.voucher_type, "Purchase Invoice")
+ self.assertEqual(first_row.voucher_no, pi.name)
+ self.assertEqual(first_row.payable_account, "Creditors - _TC6")
+ self.assertEqual(first_row.net_total, 1000)
+ self.assertEqual(first_row.total_tax, 100)
+ self.assertEqual(first_row.grand_total, 1100)
+
+ def test_purchase_register_ledger_view(self):
+ frappe.db.sql("delete from `tabPurchase Invoice` where company='_Test Company 6'")
+ frappe.db.sql("delete from `tabGL Entry` where company='_Test Company 6'")
+
+ filters = frappe._dict(
+ company="_Test Company 6",
+ from_date=add_months(today(), -1),
+ to_date=today(),
+ include_payments=True,
+ supplier="_Test Supplier",
+ )
+
+ pi = make_purchase_invoice()
+ pe = make_payment_entry()
+
+ report_results = execute(filters)
+ first_row = frappe._dict(report_results[1][2])
+ self.assertEqual(first_row.voucher_type, "Payment Entry")
+ self.assertEqual(first_row.voucher_no, pe.name)
+ self.assertEqual(first_row.payable_account, "Creditors - _TC6")
+ self.assertEqual(first_row.debit, 0)
+ self.assertEqual(first_row.credit, 600)
+ self.assertEqual(first_row.balance, 500)
+
+
+def make_purchase_invoice():
+ from erpnext.accounts.doctype.account.test_account import create_account
+ from erpnext.accounts.doctype.cost_center.test_cost_center import create_cost_center
+ from erpnext.stock.doctype.warehouse.test_warehouse import create_warehouse
+
+ gst_acc = create_account(
+ account_name="GST",
+ account_type="Tax",
+ parent_account="Duties and Taxes - _TC6",
+ company="_Test Company 6",
+ account_currency="INR",
+ )
+ create_warehouse(warehouse_name="_Test Warehouse - _TC6", company="_Test Company 6")
+ create_cost_center(cost_center_name="_Test Cost Center", company="_Test Company 6")
+ pi = create_purchase_invoice_with_taxes()
+ pi.submit()
+ return pi
+
+
+def create_purchase_invoice_with_taxes():
+ return frappe.get_doc(
+ {
+ "doctype": "Purchase Invoice",
+ "posting_date": today(),
+ "supplier": "_Test Supplier",
+ "company": "_Test Company 6",
+ "cost_center": "_Test Cost Center - _TC6",
+ "taxes_and_charges": "",
+ "currency": "INR",
+ "credit_to": "Creditors - _TC6",
+ "items": [
+ {
+ "doctype": "Purchase Invoice Item",
+ "cost_center": "_Test Cost Center - _TC6",
+ "item_code": "_Test Item",
+ "qty": 1,
+ "rate": 1000,
+ "expense_account": "Stock Received But Not Billed - _TC6",
+ }
+ ],
+ "taxes": [
+ {
+ "account_head": "GST - _TC6",
+ "cost_center": "_Test Cost Center - _TC6",
+ "add_deduct_tax": "Add",
+ "category": "Valuation and Total",
+ "charge_type": "Actual",
+ "description": "Shipping Charges",
+ "doctype": "Purchase Taxes and Charges",
+ "parentfield": "taxes",
+ "rate": 100,
+ "tax_amount": 100.0,
+ }
+ ],
+ }
+ )
+
+
+def make_payment_entry():
+ frappe.set_user("Administrator")
+ from erpnext.accounts.doctype.payment_entry.test_payment_entry import create_payment_entry
+
+ return create_payment_entry(
+ company="_Test Company 6",
+ party_type="Supplier",
+ party="_Test Supplier",
+ payment_type="Pay",
+ paid_from="Cash - _TC6",
+ paid_to="Creditors - _TC6",
+ paid_amount=600,
+ save=1,
+ submit=1,
+ )
diff --git a/erpnext/accounts/report/sales_register/sales_register.js b/erpnext/accounts/report/sales_register/sales_register.js
index 2c9b01b..1a41172 100644
--- a/erpnext/accounts/report/sales_register/sales_register.js
+++ b/erpnext/accounts/report/sales_register/sales_register.js
@@ -64,6 +64,12 @@
"label": __("Item Group"),
"fieldtype": "Link",
"options": "Item Group"
+ },
+ {
+ "fieldname": "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 ab62654..35d8d16 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -5,13 +5,22 @@
import frappe
from frappe import _, msgprint
from frappe.model.meta import get_field_precision
-from frappe.utils import flt
+from frappe.query_builder.custom import ConstantColumn
+from frappe.utils import flt, getdate
+from pypika import Order
-from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
- get_accounting_dimensions,
- get_dimension_with_children,
+from erpnext.accounts.party import get_party_account
+from erpnext.accounts.report.utils import (
+ get_advance_taxes_and_charges,
+ get_conditions,
+ get_journal_entries,
+ get_opening_row,
+ get_party_details,
+ get_payment_entries,
+ get_query_columns,
+ get_taxes_query,
+ get_values_for_columns,
)
-from erpnext.accounts.report.utils import get_query_columns, get_values_for_columns
def execute(filters=None):
@@ -22,9 +31,15 @@
if not filters:
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"):
+ invoice_list += get_payments(filters)
+
columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts = get_columns(
- invoice_list, additional_table_columns
+ invoice_list, additional_table_columns, include_payments
)
if not invoice_list:
@@ -34,13 +49,29 @@
invoice_income_map = get_invoice_income_map(invoice_list)
internal_invoice_map = get_internal_invoice_map(invoice_list)
invoice_income_map, invoice_tax_map = get_invoice_tax_map(
- invoice_list, invoice_income_map, income_accounts
+ invoice_list, invoice_income_map, income_accounts, include_payments
)
# Cost Center & Warehouse Map
invoice_cc_wh_map = get_invoice_cc_wh_map(invoice_list)
invoice_so_dn_map = get_invoice_so_dn_map(invoice_list)
company_currency = frappe.get_cached_value("Company", filters.get("company"), "default_currency")
mode_of_payments = get_mode_of_payments([inv.name for inv in invoice_list])
+ 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.account,
+ "debit": flt(opening_row.debit),
+ "credit": flt(opening_row.credit),
+ "balance": flt(opening_row.balance),
+ }
+ )
data = []
for inv in invoice_list:
@@ -51,14 +82,15 @@
warehouse = list(set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
row = {
- "invoice": inv.name,
+ "voucher_type": inv.doctype,
+ "voucher_no": inv.name,
"posting_date": inv.posting_date,
"customer": inv.customer,
"customer_name": inv.customer_name,
**get_values_for_columns(additional_table_columns, inv),
- "customer_group": inv.get("customer_group"),
- "territory": inv.get("territory"),
- "tax_id": inv.get("tax_id"),
+ "customer_group": customer_details.get(inv.customer).get("customer_group"),
+ "territory": customer_details.get(inv.customer).get("territory"),
+ "tax_id": customer_details.get(inv.customer).get("tax_id"),
"receivable_account": inv.debit_to,
"mode_of_payment": ", ".join(mode_of_payments.get(inv.name, [])),
"project": inv.project,
@@ -116,19 +148,36 @@
}
)
+ 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})
data.append(row)
- return columns, data
+ res += sorted(data, key=lambda x: x["posting_date"])
+
+ if include_payments:
+ running_balance = flt(opening_row.balance)
+ for row in range(1, len(res)):
+ running_balance += res[row]["debit"] - res[row]["credit"]
+ res[row].update({"balance": running_balance})
+
+ return columns, res, None, None, None, include_payments
-def get_columns(invoice_list, additional_table_columns):
+def get_columns(invoice_list, additional_table_columns, include_payments=False):
"""return columns based on filters"""
columns = [
{
- "label": _("Invoice"),
- "fieldname": "invoice",
- "fieldtype": "Link",
- "options": "Sales Invoice",
+ "label": _("Voucher Type"),
+ "fieldname": "voucher_type",
+ "width": 120,
+ },
+ {
+ "label": _("Voucher"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
"width": 120,
},
{"label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 80},
@@ -142,83 +191,156 @@
{"label": _("Customer Name"), "fieldname": "customer_name", "fieldtype": "Data", "width": 120},
]
- if additional_table_columns:
+ if additional_table_columns and not include_payments:
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 += [
+ {
+ "fieldname": "receivable_account",
+ "label": _("Receivable Account"),
+ "fieldtype": "Link",
+ "options": "Account",
+ "width": 120,
+ },
+ {"fieldname": "debit", "label": _("Debit"), "fieldtype": "Currency", "width": 120},
+ {"fieldname": "credit", "label": _("Credit"), "fieldtype": "Currency", "width": 120},
+ {"fieldname": "balance", "label": _("Balance"), "fieldtype": "Currency", "width": 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": 120},
- {
- "label": _("Receivable Account"),
- "fieldname": "receivable_account",
- "fieldtype": "Link",
- "options": "Account",
- "width": 80,
- },
- {
- "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": 150},
- {"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:
@@ -230,14 +352,16 @@
tuple(inv.name for inv in invoice_list),
)
- tax_accounts = frappe.db.sql_list(
- """select distinct account_head
- from `tabSales Taxes and Charges` where parenttype = 'Sales Invoice'
- and docstatus = 1 and base_tax_amount_after_discount_amount != 0
- and parent in (%s) order by account_head"""
- % ", ".join(["%s"] * len(invoice_list)),
- tuple(inv.name for inv in invoice_list),
- )
+ sales_taxes_query = get_taxes_query(invoice_list, "Sales Taxes and Charges", "Sales Invoice")
+ sales_tax_accounts = sales_taxes_query.run(as_dict=True, pluck="account_head")
+ tax_accounts = sales_tax_accounts
+
+ if include_payments:
+ advance_taxes_query = get_taxes_query(
+ invoice_list, "Advance Taxes and Charges", "Payment Entry"
+ )
+ advance_tax_accounts = advance_taxes_query.run(as_dict=True, pluck="account_head")
+ tax_accounts = set(tax_accounts + advance_tax_accounts)
unrealized_profit_loss_accounts = frappe.db.sql_list(
"""SELECT distinct unrealized_profit_loss_account
@@ -283,133 +407,71 @@
}
)
- 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
-
-
-def get_conditions(filters):
- conditions = ""
-
- accounting_dimensions = get_accounting_dimensions(as_list=False) or []
- accounting_dimensions_list = [d.fieldname for d in accounting_dimensions]
-
- if filters.get("company"):
- conditions += " and company=%(company)s"
-
- if filters.get("customer") and "customer" not in accounting_dimensions_list:
- conditions += " and customer = %(customer)s"
-
- if filters.get("from_date"):
- conditions += " and posting_date >= %(from_date)s"
- if filters.get("to_date"):
- conditions += " and posting_date <= %(to_date)s"
-
- if filters.get("owner"):
- conditions += " and owner = %(owner)s"
-
- def get_sales_invoice_item_field_condition(field, table="Sales Invoice Item") -> str:
- if not filters.get(field) or field in accounting_dimensions_list:
- return ""
- return f""" and exists(select name from `tab{table}`
- where parent=`tabSales Invoice`.name
- and ifnull(`tab{table}`.{field}, '') = %({field})s)"""
-
- conditions += get_sales_invoice_item_field_condition("mode_of_payment", "Sales Invoice Payment")
- conditions += get_sales_invoice_item_field_condition("cost_center")
- conditions += get_sales_invoice_item_field_condition("warehouse")
- conditions += get_sales_invoice_item_field_condition("brand")
- conditions += get_sales_invoice_item_field_condition("item_group")
-
- if accounting_dimensions:
- common_condition = """
- and exists(select name from `tabSales Invoice Item`
- where parent=`tabSales Invoice`.name
- """
- for dimension in accounting_dimensions:
- if filters.get(dimension.fieldname):
- if frappe.get_cached_value("DocType", dimension.document_type, "is_tree"):
- filters[dimension.fieldname] = get_dimension_with_children(
- dimension.document_type, filters.get(dimension.fieldname)
- )
-
- conditions += (
- common_condition
- + "and ifnull(`tabSales Invoice`.{0}, '') in %({0})s)".format(dimension.fieldname)
- )
- else:
- conditions += (
- common_condition
- + "and ifnull(`tabSales Invoice`.{0}, '') in %({0})s)".format(dimension.fieldname)
- )
-
- return conditions
+ return columns, accounts
def get_invoices(filters, additional_query_columns):
- conditions = get_conditions(filters)
- return frappe.db.sql(
- """
- select name, posting_date, debit_to, project, customer,
- customer_name, owner, remarks, territory, tax_id, customer_group,
- base_net_total, base_grand_total, base_rounded_total, outstanding_amount,
- is_internal_customer, represents_company, company {0}
- from `tabSales Invoice`
- where docstatus = 1 {1}
- order by posting_date desc, name desc""".format(
- additional_query_columns, conditions
- ),
- filters,
- as_dict=1,
+ si = frappe.qb.DocType("Sales Invoice")
+ invoice_item = frappe.qb.DocType("Sales Invoice Item")
+ invoice_payment = frappe.qb.DocType("Sales Invoice Payment")
+ query = (
+ frappe.qb.from_(si)
+ .inner_join(invoice_item)
+ .on(si.name == invoice_item.parent)
+ .left_join(invoice_payment)
+ .on(si.name == invoice_payment.parent)
+ .select(
+ ConstantColumn("Sales Invoice").as_("doctype"),
+ si.name,
+ si.posting_date,
+ si.debit_to,
+ si.project,
+ si.customer,
+ si.customer_name,
+ si.owner,
+ si.remarks,
+ si.territory,
+ si.tax_id,
+ si.customer_group,
+ si.base_net_total,
+ si.base_grand_total,
+ si.base_rounded_total,
+ si.outstanding_amount,
+ si.is_internal_customer,
+ si.represents_company,
+ si.company,
+ )
+ .where((si.docstatus == 1))
+ .orderby(si.posting_date, si.name, order=Order.desc)
)
+ if additional_query_columns:
+ for col in additional_query_columns:
+ query = query.select(col)
+ if filters.get("customer"):
+ query = query.where(si.customer == filters.customer)
+ query = get_conditions(
+ filters, query, doctype="Sales Invoice", child_doctype="Sales Invoice Item"
+ )
+ invoices = query.run(as_dict=True)
+ return invoices
+
+
+def get_payments(filters):
+ args = frappe._dict(
+ account="debit_to",
+ account_fieldname="paid_from",
+ party="customer",
+ party_name="customer_name",
+ party_account=get_party_account(
+ "Customer", filters.customer, filters.company, include_advance=True
+ ),
+ )
+ payment_entries = get_payment_entries(filters, args)
+ journal_entries = get_journal_entries(filters, args)
+ return payment_entries + journal_entries
def get_invoice_income_map(invoice_list):
@@ -447,7 +509,7 @@
return internal_invoice_map
-def get_invoice_tax_map(invoice_list, invoice_income_map, income_accounts):
+def get_invoice_tax_map(invoice_list, invoice_income_map, income_accounts, include_payments=False):
tax_details = frappe.db.sql(
"""select parent, account_head,
sum(base_tax_amount_after_discount_amount) as tax_amount
@@ -457,6 +519,9 @@
as_dict=1,
)
+ if include_payments:
+ tax_details += get_advance_taxes_and_charges(invoice_list)
+
invoice_tax_map = {}
for d in tax_details:
if d.account_head in income_accounts:
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index 7ea1fac..0753fff 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -1,8 +1,16 @@
import frappe
+from frappe.query_builder.custom import ConstantColumn
+from frappe.query_builder.functions import Sum
from frappe.utils import flt, formatdate, get_datetime_str, get_table_name
+from pypika import Order
from erpnext import get_company_currency, get_default_company
+from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
+ get_accounting_dimensions,
+ 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 = {}
@@ -165,7 +173,7 @@
else:
columns.append(fieldname)
- return ", " + ", ".join(columns)
+ return columns
def get_values_for_columns(report_columns, report_row):
@@ -179,3 +187,200 @@
values[fieldname] = report_row.get(fieldname)
return values
+
+
+def get_party_details(party_type, party_list):
+ party_details = {}
+ party = frappe.qb.DocType(party_type)
+ query = frappe.qb.from_(party).select(party.name, party.tax_id).where(party.name.isin(party_list))
+ if party_type == "Supplier":
+ query = query.select(party.supplier_group)
+ else:
+ query = query.select(party.customer_group, party.territory)
+
+ party_detail_list = query.run(as_dict=True)
+ for party_dict in party_detail_list:
+ party_details[party_dict.name] = party_dict
+ return party_details
+
+
+def get_taxes_query(invoice_list, doctype, parenttype):
+ taxes = frappe.qb.DocType(doctype)
+
+ query = (
+ frappe.qb.from_(taxes)
+ .select(taxes.account_head)
+ .distinct()
+ .where(
+ (taxes.parenttype == parenttype)
+ & (taxes.docstatus == 1)
+ & (taxes.account_head.isnotnull())
+ & (taxes.parent.isin([inv.name for inv in invoice_list]))
+ )
+ .orderby(taxes.account_head)
+ )
+
+ if doctype == "Purchase Taxes and Charges":
+ return query.where(taxes.category.isin(["Total", "Valuation and Total"]))
+ elif doctype == "Sales Taxes and Charges":
+ return query
+ return query.where(taxes.charge_type.isin(["On Paid Amount", "Actual"]))
+
+
+def get_journal_entries(filters, args):
+ je = frappe.qb.DocType("Journal Entry")
+ journal_account = frappe.qb.DocType("Journal Entry Account")
+ query = (
+ frappe.qb.from_(je)
+ .inner_join(journal_account)
+ .on(je.name == journal_account.parent)
+ .select(
+ je.voucher_type.as_("doctype"),
+ je.name,
+ je.posting_date,
+ journal_account.account.as_(args.account),
+ journal_account.party.as_(args.party),
+ journal_account.party.as_(args.party_name),
+ je.bill_no,
+ je.bill_date,
+ je.remark.as_("remarks"),
+ je.total_amount.as_("base_net_total"),
+ je.total_amount.as_("base_grand_total"),
+ je.mode_of_payment,
+ journal_account.project,
+ )
+ .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, doctype="Journal Entry", payments=True)
+ journal_entries = query.run(as_dict=True)
+ return journal_entries
+
+
+def get_payment_entries(filters, args):
+ pe = frappe.qb.DocType("Payment Entry")
+ query = (
+ frappe.qb.from_(pe)
+ .select(
+ ConstantColumn("Payment Entry").as_("doctype"),
+ pe.name,
+ pe.posting_date,
+ pe[args.account_fieldname].as_(args.account),
+ pe.party.as_(args.party),
+ pe.party_name.as_(args.party_name),
+ pe.remarks,
+ pe.paid_amount.as_("base_net_total"),
+ pe.paid_amount_after_tax.as_("base_grand_total"),
+ pe.mode_of_payment,
+ pe.project,
+ pe.cost_center,
+ )
+ .where(
+ (pe.party == filters.get(args.party)) & (pe[args.account_fieldname].isin(args.party_account))
+ )
+ .orderby(pe.posting_date, pe.name, order=Order.desc)
+ )
+ query = get_conditions(filters, query, doctype="Payment Entry", payments=True)
+ payment_entries = query.run(as_dict=True)
+ return payment_entries
+
+
+def get_conditions(filters, query, doctype, child_doctype=None, payments=False):
+ parent_doc = frappe.qb.DocType(doctype)
+ if child_doctype:
+ child_doc = frappe.qb.DocType(child_doctype)
+
+ if parent_doc.get_table_name() == "tabSales Invoice":
+ if filters.get("owner"):
+ query = query.where(parent_doc.owner == filters.owner)
+ if filters.get("mode_of_payment"):
+ payment_doc = frappe.qb.DocType("Sales Invoice Payment")
+ query = query.where(payment_doc.mode_of_payment == filters.mode_of_payment)
+ if not payments:
+ if filters.get("brand"):
+ query = query.where(child_doc.brand == filters.brand)
+ else:
+ if filters.get("mode_of_payment"):
+ query = query.where(parent_doc.mode_of_payment == filters.mode_of_payment)
+
+ if filters.get("company"):
+ query = query.where(parent_doc.company == filters.company)
+ if filters.get("from_date"):
+ query = query.where(parent_doc.posting_date >= filters.from_date)
+ if filters.get("to_date"):
+ query = query.where(parent_doc.posting_date <= filters.to_date)
+
+ if payments:
+ if filters.get("cost_center"):
+ query = query.where(parent_doc.cost_center == filters.cost_center)
+ else:
+ if filters.get("cost_center"):
+ query = query.where(child_doc.cost_center == filters.cost_center)
+ if filters.get("warehouse"):
+ query = query.where(child_doc.warehouse == filters.warehouse)
+ if filters.get("item_group"):
+ query = query.where(child_doc.item_group == filters.item_group)
+
+ if parent_doc.get_table_name() != "tabJournal Entry":
+ query = filter_invoices_based_on_dimensions(filters, query, parent_doc)
+ return query
+
+
+def get_advance_taxes_and_charges(invoice_list):
+ adv_taxes = frappe.qb.DocType("Advance Taxes and Charges")
+ return (
+ frappe.qb.from_(adv_taxes)
+ .select(
+ adv_taxes.parent,
+ adv_taxes.account_head,
+ (
+ frappe.qb.terms.Case()
+ .when(adv_taxes.add_deduct_tax == "Add", Sum(adv_taxes.base_tax_amount))
+ .else_(Sum(adv_taxes.base_tax_amount) * -1)
+ ).as_("tax_amount"),
+ )
+ .where(
+ (adv_taxes.parent.isin([inv.name for inv in invoice_list]))
+ & (adv_taxes.charge_type.isin(["On Paid Amount", "Actual"]))
+ & (adv_taxes.base_tax_amount != 0)
+ )
+ .groupby(adv_taxes.parent, adv_taxes.account_head, adv_taxes.add_deduct_tax)
+ ).run(as_dict=True)
+
+
+def filter_invoices_based_on_dimensions(filters, query, parent_doc):
+ accounting_dimensions = get_accounting_dimensions(as_list=False)
+ if accounting_dimensions:
+ for dimension in accounting_dimensions:
+ if filters.get(dimension.fieldname):
+ if frappe.get_cached_value("DocType", dimension.document_type, "is_tree"):
+ filters[dimension.fieldname] = get_dimension_with_children(
+ dimension.document_type, filters.get(dimension.fieldname)
+ )
+ fieldname = dimension.fieldname
+ query = query.where(parent_doc[fieldname] == filters.fieldname)
+ return query
+
+
+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.party == party)
+ & (gle.posting_date < from_date)
+ & (gle.is_cancelled == 0)
+ )
+ ).run(as_dict=True)