feat: fetch JV with PE
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.py b/erpnext/accounts/report/purchase_register/purchase_register.py
index bd92826..8a845cf 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -10,7 +10,12 @@
get_accounting_dimensions,
get_dimension_with_children,
)
-from erpnext.accounts.report.utils import get_party_details, get_taxes_query
+from erpnext.accounts.report.utils import (
+ get_journal_entries,
+ get_party_details,
+ get_payment_entries,
+ get_taxes_query,
+)
def execute(filters=None):
@@ -51,7 +56,7 @@
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]
+ row = [inv.doctype, inv.name, inv.posting_date, inv.supplier, inv.supplier_name]
if additional_query_columns:
for col in additional_query_columns:
@@ -100,13 +105,14 @@
row += [total_tax, inv.base_grand_total, flt(inv.base_grand_total, 0), inv.outstanding_amount]
data.append(row)
- return columns, sorted(data, key=lambda x: x[1])
+ return columns, sorted(data, key=lambda x: x[2])
def get_columns(invoice_list, additional_table_columns, include_payments=False):
"""return columns based on filters"""
columns = [
- _("Invoice") + ":Link/Purchase Invoice:120",
+ _("Voucher Type") + ":Data:120",
+ _("Voucher No") + ":Dynamic Link/voucher_type:120",
_("Posting Date") + ":Date:80",
_("Supplier Id") + "::120",
_("Supplier Name") + "::120",
@@ -117,7 +123,7 @@
columns += [
_("Supplier Group") + ":Link/Supplier Group:120",
- _("Tax Id") + "::80",
+ _("Tax Id") + "::50",
_("Payable Account") + ":Link/Account:120",
_("Mode of Payment") + ":Link/Mode of Payment:80",
_("Project") + ":Link/Project:80",
@@ -280,20 +286,17 @@
additional_query_columns = ", " + ", ".join(additional_query_columns)
conditions = get_conditions(filters, payments=True)
- return frappe.db.sql(
- """
- select 'Payment Entry' as doctype, name, posting_date, paid_to as credit_to,
- party as supplier, party_name as supplier_name, remarks, paid_amount as base_net_total, paid_amount_after_tax as base_grand_total,
- mode_of_payment {0}, project
- from `tabPayment Entry`
- where party_type = 'Supplier' %s
- order by posting_date desc, name desc""".format(
- additional_query_columns or ""
- )
- % conditions,
- filters,
- as_dict=1,
+ args = frappe._dict(
+ account="credit_to",
+ party="supplier",
+ party_name="supplier_name",
+ additional_query_columns="" if not additional_query_columns else additional_query_columns,
+ party_type="Supplier",
+ conditions=conditions,
)
+ 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):
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index a13354b..5965a1a 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -11,7 +11,12 @@
get_accounting_dimensions,
get_dimension_with_children,
)
-from erpnext.accounts.report.utils import get_party_details, get_taxes_query
+from erpnext.accounts.report.utils import (
+ get_journal_entries,
+ get_party_details,
+ get_payment_entries,
+ get_taxes_query,
+)
def execute(filters=None):
@@ -56,6 +61,7 @@
warehouse = list(set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
row = {
+ "voucher_type": inv.doctype,
"invoice": inv.name,
"posting_date": inv.posting_date,
"customer": inv.customer,
@@ -138,10 +144,15 @@
"""return columns based on filters"""
columns = [
{
- "label": _("Invoice"),
+ "label": _("Voucher Type"),
+ "fieldname": "voucher_type",
+ "width": 120,
+ },
+ {
+ "label": _("Voucher"),
"fieldname": "invoice",
- "fieldtype": "Link",
- "options": "Sales Invoice",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
"width": 120,
},
{"label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 80},
@@ -173,13 +184,13 @@
"options": "Territory",
"width": 80,
},
- {"label": _("Tax Id"), "fieldname": "tax_id", "fieldtype": "Data", "width": 120},
+ {"label": _("Tax Id"), "fieldname": "tax_id", "fieldtype": "Data", "width": 80},
{
"label": _("Receivable Account"),
"fieldname": "receivable_account",
"fieldtype": "Link",
"options": "Account",
- "width": 80,
+ "width": 100,
},
{
"label": _("Mode Of Payment"),
@@ -194,7 +205,7 @@
"options": "Project",
"width": 80,
},
- {"label": _("Owner"), "fieldname": "owner", "fieldtype": "Data", "width": 150},
+ {"label": _("Owner"), "fieldname": "owner", "fieldtype": "Data", "width": 100},
{"label": _("Remarks"), "fieldname": "remarks", "fieldtype": "Data", "width": 150},
{
"label": _("Sales Order"),
@@ -435,21 +446,17 @@
additional_query_columns = ", " + ", ".join(additional_query_columns)
conditions = get_conditions(filters, payments=True)
- return frappe.db.sql(
- """
- select 'Payment Entry' as doctype, name, posting_date, paid_to as debit_to,
- party as customer, party_name as customer_name, remarks,
- paid_amount as base_net_total, paid_amount_after_tax as base_grand_total,
- mode_of_payment {0}, project, cost_center
- from `tabPayment Entry`
- where party_type = 'Customer' %s
- order by posting_date desc, name desc""".format(
- additional_query_columns or ""
- )
- % conditions,
- filters,
- as_dict=1,
+ args = frappe._dict(
+ account="debit_to",
+ party="customer",
+ party_name="customer_name",
+ additional_query_columns="" if not additional_query_columns else additional_query_columns,
+ party_type="Customer",
+ conditions=conditions,
)
+ 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):
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index e049301..c6b98835 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -189,3 +189,47 @@
elif doctype == "Sales Taxes and Charges":
return query.where(taxes.charge_type.isin(["Total", "Valuation and Total"]))
return query.where(taxes.charge_type.isin(["On Paid Amount", "Actual"]))
+
+
+def get_journal_entries(filters, args):
+ return frappe.db.sql(
+ """
+ select je.voucher_type as doctype, je.name, je.posting_date,
+ jea.account as {0}, jea.party as {1}, jea.party as {2},
+ je.bill_no, je.bill_date, je.remark, je.total_amount as base_net_total,
+ je.total_amount as base_grand_total, je.mode_of_payment, jea.project {3}
+ from `tabJournal Entry` je left join `tabJournal Entry Account` jea on jea.parent=je.name
+ where je.voucher_type='Journal Entry' and jea.party_type='{4}' {5}
+ order by je.posting_date desc, je.name desc""".format(
+ args.account,
+ args.party,
+ args.party_name,
+ args.additional_query_columns,
+ args.party_type,
+ args.conditions,
+ ),
+ filters,
+ as_dict=1,
+ )
+
+
+def get_payment_entries(filters, args):
+ return frappe.db.sql(
+ """
+ select 'Payment Entry' as doctype, name, posting_date, paid_to as {0},
+ party as {1}, party_name as {2}, remarks,
+ paid_amount as base_net_total, paid_amount_after_tax as base_grand_total,
+ mode_of_payment, project, cost_center {3}
+ from `tabPayment Entry`
+ where party_type='{4}' {5}
+ order by posting_date desc, name desc""".format(
+ args.account,
+ args.party,
+ args.party_name,
+ args.additional_query_columns,
+ args.party_type,
+ args.conditions,
+ ),
+ filters,
+ as_dict=1,
+ )