feat: added cost center filter in AR/AP reports
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
index 5c0e8fa..ac2ce8e 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
@@ -510,6 +510,15 @@
}
}
}
+
+ frm.set_query("cost_center", function() {
+ return {
+ filters: {
+ company: frm.doc.company,
+ is_group: 0
+ }
+ };
+ });
},
onload: function(frm) {
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
index a4a5940..9a40d2b 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.js
@@ -564,6 +564,15 @@
};
});
+ frm.set_query("cost_center", function() {
+ return {
+ filters: {
+ company: frm.doc.company,
+ is_group: 0
+ }
+ };
+ });
+
frm.custom_make_buttons = {
'Delivery Note': 'Delivery',
'Sales Invoice': 'Sales Return',
diff --git a/erpnext/accounts/report/accounts_payable/accounts_payable.js b/erpnext/accounts/report/accounts_payable/accounts_payable.js
index 0a025f6..9dd552f 100644
--- a/erpnext/accounts/report/accounts_payable/accounts_payable.js
+++ b/erpnext/accounts/report/accounts_payable/accounts_payable.js
@@ -51,6 +51,20 @@
"options": "Finance Book"
},
{
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ 'company': company
+ }
+ }
+ }
+ },
+ {
"fieldname":"supplier",
"label": __("Supplier"),
"fieldtype": "Link",
diff --git a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
index 7823cac..31c0193 100644
--- a/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
+++ b/erpnext/accounts/report/accounts_payable_summary/accounts_payable_summary.js
@@ -51,6 +51,20 @@
"options": "Finance Book"
},
{
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ 'company': company
+ }
+ }
+ }
+ },
+ {
"fieldname":"supplier",
"label": __("Supplier"),
"fieldtype": "Link",
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
index bbfee11..dce7e75 100644
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.js
@@ -51,6 +51,20 @@
"options": "Finance Book"
},
{
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ 'company': company
+ }
+ }
+ }
+ },
+ {
"fieldname":"customer",
"label": __("Customer"),
"fieldtype": "Link",
diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
index 0e3317d..4932ae1 100755
--- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py
@@ -537,6 +537,13 @@
where supplier_group=%s)""")
values.append(self.filters.get("supplier_group"))
+ if self.filters.get("cost_center"):
+ lft, rgt = frappe.get_cached_value("Cost Center",
+ self.filters.get("cost_center"), ['lft', 'rgt'])
+
+ conditions.append("""cost_center in (select name from `tabCost Center` where
+ lft >= {0} and rgt <= {1})""".format(lft, rgt))
+
accounts = [d.name for d in frappe.get_all("Account",
filters={"account_type": account_type, "company": self.filters.company})]
conditions.append("account in (%s)" % ','.join(['%s'] *len(accounts)))
diff --git a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
index a6f1457..47b087d 100644
--- a/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
+++ b/erpnext/accounts/report/accounts_receivable_summary/accounts_receivable_summary.js
@@ -51,6 +51,20 @@
"options": "Finance Book"
},
{
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ 'company': company
+ }
+ }
+ }
+ },
+ {
"fieldname":"customer",
"label": __("Customer"),
"fieldtype": "Link",
diff --git a/erpnext/selling/doctype/customer/customer.py b/erpnext/selling/doctype/customer/customer.py
index a8fae7b..ec27498 100644
--- a/erpnext/selling/doctype/customer/customer.py
+++ b/erpnext/selling/doctype/customer/customer.py
@@ -260,12 +260,21 @@
throw(_("Please contact to the user who have Sales Master Manager {0} role")
.format(" / " + credit_controller if credit_controller else ""))
-def get_customer_outstanding(customer, company, ignore_outstanding_sales_order=False):
+def get_customer_outstanding(customer, company, ignore_outstanding_sales_order=False, cost_center=None):
# Outstanding based on GL Entries
+
+ cond = ""
+ if cost_center:
+ lft, rgt = frappe.get_cached_value("Cost Center",
+ cost_center, ['lft', 'rgt'])
+
+ cond = """ and cost_center in (select name from `tabCost Center` where
+ lft >= {0} and rgt <= {1})""".format(lft, rgt)
+
outstanding_based_on_gle = frappe.db.sql("""
select sum(debit) - sum(credit)
- from `tabGL Entry`
- where party_type = 'Customer' and party = %s and company=%s""", (customer, company))
+ from `tabGL Entry` where party_type = 'Customer'
+ and party = %s and company=%s {0}""".format(cond), (customer, company), debug=1)
outstanding_based_on_gle = flt(outstanding_based_on_gle[0][0]) if outstanding_based_on_gle else 0
diff --git a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.js b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.js
index 3a99eb0..de8abdc 100644
--- a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.js
+++ b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.js
@@ -16,6 +16,20 @@
"label": __("Customer"),
"fieldtype": "Link",
"options": "Customer"
- }
+ },
+ {
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center",
+ get_query: () => {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ 'company': company
+ }
+ }
+ }
+ },
]
}
diff --git a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
index fe58af6..a57d975 100644
--- a/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
+++ b/erpnext/selling/report/customer_credit_balance/customer_credit_balance.py
@@ -21,9 +21,10 @@
row = []
outstanding_amt = get_customer_outstanding(d.name, filters.get("company"),
- ignore_outstanding_sales_order=d.bypass_credit_limit_check_at_sales_order)
+ ignore_outstanding_sales_order=d.bypass_credit_limit_check_at_sales_order,
+ cost_center=filters.get("cost_center"))
- credit_limit = get_credit_limit(d.name, filters.get("company"))
+ credit_limit = get_credit_limit(d.name, filters.get("company"))
bal = flt(credit_limit) - flt(outstanding_amt)