Added Warehouse, Cost Center, Owner filters to Sales Register (#11600)
* [fix] #3668
* fix codacy space issue
diff --git a/erpnext/accounts/report/sales_register/sales_register.js b/erpnext/accounts/report/sales_register/sales_register.js
index 0495976..db74626 100644
--- a/erpnext/accounts/report/sales_register/sales_register.js
+++ b/erpnext/accounts/report/sales_register/sales_register.js
@@ -34,6 +34,24 @@
"label": __("Mode of Payment"),
"fieldtype": "Link",
"options": "Mode of Payment"
+ },
+ {
+ "fieldname":"owner",
+ "label": __("Owner"),
+ "fieldtype": "Link",
+ "options": "User"
+ },
+ {
+ "fieldname":"cost_center",
+ "label": __("Cost Center"),
+ "fieldtype": "Link",
+ "options": "Cost Center"
+ },
+ {
+ "fieldname":"warehouse",
+ "label": __("Warehouse"),
+ "fieldtype": "Link",
+ "options": "Warehouse"
}
]
}
diff --git a/erpnext/accounts/report/sales_register/sales_register.py b/erpnext/accounts/report/sales_register/sales_register.py
index c471b8b..ace8d54 100644
--- a/erpnext/accounts/report/sales_register/sales_register.py
+++ b/erpnext/accounts/report/sales_register/sales_register.py
@@ -22,7 +22,8 @@
invoice_income_map = get_invoice_income_map(invoice_list)
invoice_income_map, invoice_tax_map = get_invoice_tax_map(invoice_list,
invoice_income_map, income_accounts)
-
+ #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)
customers = list(set([inv.customer for inv in invoice_list]))
customer_map = get_customer_details(customers)
@@ -34,6 +35,8 @@
# invoice details
sales_order = list(set(invoice_so_dn_map.get(inv.name, {}).get("sales_order", [])))
delivery_note = list(set(invoice_so_dn_map.get(inv.name, {}).get("delivery_note", [])))
+ cost_center = list(set(invoice_cc_wh_map.get(inv.name, {}).get("cost_center", [])))
+ warehouse = list(set(invoice_cc_wh_map.get(inv.name, {}).get("warehouse", [])))
customer_details = customer_map.get(inv.customer, {})
row = [
@@ -48,8 +51,9 @@
customer_details.get("customer_group"),
customer_details.get("territory"),
inv.debit_to, ", ".join(mode_of_payments.get(inv.name, [])),
- inv.project, inv.remarks,
- ", ".join(sales_order), ", ".join(delivery_note), company_currency
+ inv.project, inv.owner, inv.remarks,
+ ", ".join(sales_order), ", ".join(delivery_note),", ".join(cost_center),
+ ", ".join(warehouse), company_currency
]
# map income values
base_net_total = 0
@@ -89,8 +93,9 @@
columns +=[
_("Customer Group") + ":Link/Customer Group:120", _("Territory") + ":Link/Territory:80",
_("Receivable Account") + ":Link/Account:120", _("Mode of Payment") + "::120",
- _("Project") +":Link/Project:80", _("Remarks") + "::150",
+ _("Project") +":Link/Project:80", _("Owner") + "::150", _("Remarks") + "::150",
_("Sales Order") + ":Link/Sales Order:100", _("Delivery Note") + ":Link/Delivery Note:100",
+ _("Cost Center") + ":Link/Cost Center:100", _("Warehouse") + ":Link/Warehouse:100",
{
"fieldname": "currency",
"label": _("Currency"),
@@ -133,11 +138,23 @@
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"
+
if filters.get("mode_of_payment"):
conditions += """ and exists(select name from `tabSales Invoice Payment`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
+ if filters.get("cost_center"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)"""
+
+ if filters.get("warehouse"):
+ conditions += """ and exists(select name from `tabSales Invoice Item`
+ where parent=`tabSales Invoice`.name
+ and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
+
return conditions
def get_invoices(filters, additional_query_columns):
@@ -145,7 +162,7 @@
additional_query_columns = ', ' + ', '.join(additional_query_columns)
conditions = get_conditions(filters)
- return frappe.db.sql("""select name, posting_date, debit_to, project, customer, customer_name, remarks,
+ return frappe.db.sql("""select name, posting_date, debit_to, project, customer, customer_name, owner, remarks,
base_net_total, base_grand_total, base_rounded_total, outstanding_amount {0}
from `tabSales Invoice`
where docstatus = 1 %s order by posting_date desc, name desc""".format(additional_query_columns or '') %
@@ -206,6 +223,24 @@
return invoice_so_dn_map
+def get_invoice_cc_wh_map(invoice_list):
+ si_items = frappe.db.sql("""select parent, cost_center, warehouse
+ from `tabSales Invoice Item` where parent in (%s)
+ and (ifnull(cost_center, '') != '' or ifnull(warehouse, '') != '')""" %
+ ', '.join(['%s']*len(invoice_list)), tuple([inv.name for inv in invoice_list]), as_dict=1)
+
+ invoice_cc_wh_map = {}
+ for d in si_items:
+ if d.cost_center:
+ invoice_cc_wh_map.setdefault(d.parent, frappe._dict()).setdefault(
+ "cost_center", []).append(d.cost_center)
+
+ if d.warehouse:
+ invoice_cc_wh_map.setdefault(d.parent, frappe._dict()).setdefault(
+ "warehouse", []).append(d.warehouse)
+
+ return invoice_cc_wh_map
+
def get_customer_details(customers):
customer_map = {}
for cust in frappe.db.sql("""select name, territory, customer_group from `tabCustomer`
@@ -225,4 +260,4 @@
for d in inv_mop:
mode_of_payments.setdefault(d.parent, []).append(d.mode_of_payment)
- return mode_of_payments
+ return mode_of_payments
\ No newline at end of file