Merge branch 'develop' into purchase-sales-register-with-PE/JE
diff --git a/erpnext/accounts/report/purchase_register/purchase_register.js b/erpnext/accounts/report/purchase_register/purchase_register.js
index aaf76c4..ddf84d0 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": __("Include Payments"),
+ "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..226447c 100644
--- a/erpnext/accounts/report/purchase_register/purchase_register.py
+++ b/erpnext/accounts/report/purchase_register/purchase_register.py
@@ -10,6 +10,14 @@
get_accounting_dimensions,
get_dimension_with_children,
)
+
+from erpnext.accounts.report.utils import (
+ get_journal_entries,
+ get_party_details,
+ get_payment_entries,
+ get_taxes_query,
+)
+
from erpnext.accounts.report.utils import get_query_columns, get_values_for_columns
@@ -21,9 +29,15 @@
if not filters:
filters = {}
+ include_payments = filters.get("include_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_query_columns)
+
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,11 +47,11 @@
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")
@@ -49,16 +63,17 @@
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), # supplier_group
- inv.tax_id,
+ 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),
+ ", ".join(project) if inv.doctype == "Purchase Invoice" else inv.project,
inv.bill_no,
inv.bill_date,
inv.remarks,
@@ -96,13 +111,14 @@
row += [total_tax, inv.base_grand_total, flt(inv.base_grand_total, 0), inv.outstanding_amount]
data.append(row)
- return columns, data
+ return columns, sorted(data, key=lambda x: x[2])
-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",
+ _("Voucher Type") + ":Data:120",
+ _("Voucher No") + ":Dynamic Link/voucher_type:120",
_("Posting Date") + ":Date:80",
_("Supplier Id") + "::120",
_("Supplier Name") + "::120",
@@ -113,7 +129,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",
@@ -139,15 +155,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
@@ -185,13 +204,13 @@
return columns, expense_accounts, tax_accounts, unrealized_profit_loss_accounts
-def get_conditions(filters):
+def get_conditions(filters, payments=False):
conditions = ""
if filters.get("company"):
conditions += " and company=%(company)s"
if filters.get("supplier"):
- conditions += " and supplier = %(supplier)s"
+ conditions += " and party = %(supplier)s" if payments else " and supplier = %(supplier)s"
if filters.get("from_date"):
conditions += " and posting_date>=%(from_date)s"
@@ -202,16 +221,19 @@
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 payments:
+ conditions += " and cost_center = %(cost_center)s"
+ else:
+ 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"):
+ if filters.get("warehouse") and not payments:
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"):
+ if filters.get("item_group") and not payments:
conditions += """ and exists(select name from `tabPurchase Invoice Item`
where parent=`tabPurchase Invoice`.name
and ifnull(`tabPurchase Invoice Item`.item_group, '') = %(item_group)s)"""
@@ -248,7 +270,7 @@
return frappe.db.sql(
"""
select
- name, posting_date, credit_to, supplier, supplier_name, tax_id, bill_no, bill_date,
+ 'Purchase Invoice' as doctype, 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`
@@ -261,6 +283,23 @@
)
+def get_payments(filters, additional_query_columns):
+ if additional_query_columns:
+ additional_query_columns = ", " + ", ".join(additional_query_columns)
+
+ conditions = get_conditions(filters, payments=True)
+ args = frappe._dict(
+ account="credit_to",
+ party="supplier",
+ party_name="supplier_name",
+ additional_query_columns="" if not additional_query_columns else additional_query_columns,
+ 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):
expense_details = frappe.db.sql(
"""
@@ -300,7 +339,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 +356,22 @@
as_dict=1,
)
+ if include_payments:
+ advance_tax_details = frappe.db.sql(
+ """
+ select parent, account_head, case add_deduct_tax when "Add" then sum(base_tax_amount)
+ else sum(base_tax_amount) * -1 end as tax_amount
+ from `tabAdvance Taxes and Charges`
+ where parent in (%s) and charge_type in ('On Paid Amount', 'Actual')
+ and base_tax_amount != 0
+ group by parent, account_head, add_deduct_tax
+ """
+ % ", ".join(["%s"] * len(invoice_list)),
+ tuple(inv.name for inv in invoice_list),
+ as_dict=1,
+ )
+ tax_details += advance_tax_details
+
invoice_tax_map = {}
for d in tax_details:
if d.account_head in expense_accounts:
@@ -382,17 +439,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/sales_register/sales_register.js b/erpnext/accounts/report/sales_register/sales_register.js
index 2c9b01b..3c879ca 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": __("Include Payments"),
+ "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 291c7d9..8de9bb2 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -11,6 +11,14 @@
get_accounting_dimensions,
get_dimension_with_children,
)
+
+from erpnext.accounts.report.utils import (
+ get_journal_entries,
+ get_party_details,
+ get_payment_entries,
+ get_taxes_query,
+)
+
from erpnext.accounts.report.utils import get_query_columns, get_values_for_columns
@@ -22,9 +30,15 @@
if not filters:
filters = frappe._dict({})
+ include_payments = filters.get("include_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_query_columns)
+
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 +48,15 @@
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)
data = []
for inv in invoice_list:
@@ -51,14 +67,15 @@
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,
"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,
@@ -118,17 +135,22 @@
data.append(row)
- return columns, data
+ return columns, sorted(data, key=lambda x: x["posting_date"])
-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"),
+ "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},
@@ -160,13 +182,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"),
@@ -181,7 +203,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"),
@@ -230,14 +252,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
@@ -336,7 +360,7 @@
return columns, income_accounts, tax_accounts, unrealized_profit_loss_accounts
-def get_conditions(filters):
+def get_conditions(filters, payments=False):
conditions = ""
accounting_dimensions = get_accounting_dimensions(as_list=False) or []
@@ -346,7 +370,7 @@
conditions += " and company=%(company)s"
if filters.get("customer") and "customer" not in accounting_dimensions_list:
- conditions += " and customer = %(customer)s"
+ conditions += " and party = %(customer)s" if payments else " and customer = %(customer)s"
if filters.get("from_date"):
conditions += " and posting_date >= %(from_date)s"
@@ -398,7 +422,7 @@
conditions = get_conditions(filters)
return frappe.db.sql(
"""
- select name, posting_date, debit_to, project, customer,
+ select 'Sales Invoice' as doctype, 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}
@@ -412,6 +436,23 @@
)
+def get_payments(filters, additional_query_columns):
+ if additional_query_columns:
+ additional_query_columns = ", " + ", ".join(additional_query_columns)
+
+ conditions = get_conditions(filters, payments=True)
+ args = frappe._dict(
+ account="debit_to",
+ party="customer",
+ party_name="customer_name",
+ additional_query_columns="" if not additional_query_columns else additional_query_columns,
+ 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):
income_details = frappe.db.sql(
"""select parent, income_account, sum(base_net_amount) as amount
@@ -447,7 +488,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 +498,22 @@
as_dict=1,
)
+ if include_payments:
+ advance_tax_details = frappe.db.sql(
+ """
+ select parent, account_head, case add_deduct_tax when "Add" then sum(base_tax_amount)
+ else sum(base_tax_amount) * -1 end as tax_amount
+ from `tabAdvance Taxes and Charges`
+ where parent in (%s) and charge_type in ('On Paid Amount', 'Actual')
+ and base_tax_amount != 0
+ group by parent, account_head, add_deduct_tax
+ """
+ % ", ".join(["%s"] * len(invoice_list)),
+ tuple(inv.name for inv in invoice_list),
+ as_dict=1,
+ )
+ tax_details += advance_tax_details
+
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..8711e7e 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -152,6 +152,88 @@
return result
+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.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='{4}' {5}
+ order by je.posting_date desc, je.name desc""".format(
+ args.account,
+ args.party,
+ args.party_name,
+ args.additional_query_columns,
+ filters.get(args.party),
+ 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='{4}' {5}
+ order by posting_date desc, name desc""".format(
+ args.account,
+ args.party,
+ args.party_name,
+ args.additional_query_columns,
+ filters.get(args.party),
+ args.conditions,
+ ),
+ filters,
+ as_dict=1,
+ )
+
+
def get_query_columns(report_columns):
if not report_columns:
return ""