Merge branch 'vishdha-leaderboard' into hotfix
diff --git a/erpnext/utilities/page/leaderboard/leaderboard.js b/erpnext/utilities/page/leaderboard/leaderboard.js
index 7e70f76..746ac79 100644
--- a/erpnext/utilities/page/leaderboard/leaderboard.js
+++ b/erpnext/utilities/page/leaderboard/leaderboard.js
@@ -17,14 +17,15 @@
this.$sidebar_list = this.page.sidebar.find('ul');
// const list of doctypes
- this.doctypes = ["Customer", "Item", "Supplier", "Sales Partner"];
+ this.doctypes = ["Customer", "Item", "Supplier", "Sales Partner","Sales Person"];
this.timespans = ["Week", "Month", "Quarter", "Year"];
- this.desc_fields = ["total_amount", "total_request", "annual_billing", "commission_rate"];
this.filters = {
- "Customer": ["total_amount", "total_item_purchased"],
- "Item": ["total_request", "total_purchase", "avg_price"],
- "Supplier": ["annual_billing", "total_unpaid"],
- "Sales Partner": ["commission_rate", "target_qty", "target_amount"],
+ "Customer": ["total_sales_amount", "total_qty_sold", "outstanding_amount", ],
+ "Item": ["total_sales_amount", "total_qty_sold", "total_purchase_amount",
+ "total_qty_purchased", "available_stock_qty", "available_stock_value"],
+ "Supplier": ["total_purchase_amount", "total_qty_purchased", "outstanding_amount"],
+ "Sales Partner": ["total_sales_amount", "total_commision"],
+ "Sales Person": ["total_sales_amount"],
};
// for saving current selected filters
@@ -58,14 +59,24 @@
this.get_sidebar_item(doctype).appendTo(this.$sidebar_list);
});
+ this.company_select = this.page.add_field({
+ fieldname: 'company',
+ label: __('Company'),
+ fieldtype:'Link',
+ options:'Company',
+ default:frappe.defaults.get_default('company'),
+ reqd: 1,
+ change: function() {
+ me.options.selected_company = this.value;
+ me.make_request($container);
+ }
+ });
this.timespan_select = this.page.add_select(__("Timespan"),
this.timespans.map(d => {
return {"label": __(d), value: d }
})
);
- // this.timespan_select.val(this.timespans[1]);
-
this.type_select = this.page.add_select(__("Type"),
me.options.selected_filter.map(d => {
return {"label": __(frappe.model.unscrub(d)), value: d }
@@ -76,6 +87,7 @@
let $li = $(this);
let doctype = $li.find('span').html();
+ me.options.selected_company = frappe.defaults.get_default('company');
me.options.selected_doctype = doctype;
me.options.selected_filter = me.filters[doctype];
me.options.selected_filter_item = me.filters[doctype][0];
@@ -114,16 +126,18 @@
});
},
- get_leaderboard: function (notify, $container, start=0) {
+ get_leaderboard: function (notify, $container) {
var me = this;
-
+ if(!me.options.selected_company) {
+ frappe.throw(__("Please select Company"));
+ }
frappe.call({
method: "erpnext.utilities.page.leaderboard.leaderboard.get_leaderboard",
args: {
doctype: me.options.selected_doctype,
timespan: me.options.selected_timespan,
+ company: me.options.selected_company,
field: me.options.selected_filter_item,
- start: start
},
callback: function (r) {
let results = r.message || [];
@@ -256,28 +270,27 @@
get_item_html: function (item) {
var me = this;
- const _selected_filter = me.options.selected_filter
- .map(i => frappe.model.unscrub(i));
- const fields = ['name', me.options.selected_filter_item];
+ const company = me.options.selected_company;
+ const currency = frappe.get_doc(":Company", company).default_currency;
+ const fields = ['name','value'];
const html =
`<div class="list-item">
${
- fields.map(filter => {
- const col = frappe.model.unscrub(filter);
- let val = item[filter];
- if (col === "Modified") {
- val = comment_when(val);
+ fields.map(col => {
+ let val = item[col];
+ if(col=="name") {
+ var formatted_value = `<a class="grey list-id ellipsis"
+ href="#Form/${me.options.selected_doctype}/${item["name"]}"> ${val} </a>`
+ } else {
+ var formatted_value = `<span class="text-muted ellipsis">
+ ${(me.options.selected_filter_item.indexOf('qty') == -1) ? format_currency(val, currency) : val}</span>`
}
+
return (
`<div class="list-item_content ellipsis list-item__content--flex-2
- ${(col !== "Name" && col !== "Modified") ? "hidden-xs" : ""}
- ${(col && _selected_filter.indexOf(col) !== -1) ? "text-right" : ""}">
- ${
- col === "Name"
- ? `<a class="grey list-id ellipsis" href="${item["href"]}"> ${val} </a>`
- : `<span class="text-muted ellipsis"> ${val}</span>`
- }
+ ${(col == "value") ? "text-right" : ""}">
+ ${formatted_value}
</div>`);
}).join("")
}
diff --git a/erpnext/utilities/page/leaderboard/leaderboard.py b/erpnext/utilities/page/leaderboard/leaderboard.py
index 6e7eabc..87cf2a4 100644
--- a/erpnext/utilities/page/leaderboard/leaderboard.py
+++ b/erpnext/utilities/page/leaderboard/leaderboard.py
@@ -3,147 +3,141 @@
from __future__ import unicode_literals, print_function
import frappe
-import json
-from operator import itemgetter
-from frappe.utils import add_to_date, fmt_money
-from erpnext.accounts.party import get_dashboard_info
-from erpnext.accounts.utils import get_currency_precision
+from frappe.utils import add_to_date
@frappe.whitelist()
-def get_leaderboard(doctype, timespan, field, start=0):
+def get_leaderboard(doctype, timespan, company, field):
"""return top 10 items for that doctype based on conditions"""
-
- filters = {"modified":(">=", get_date_from_string(timespan))}
- items = []
+ from_date = get_from_date(timespan)
+ records = []
if doctype == "Customer":
- items = get_all_customers(doctype, filters, [], field)
+ records = get_all_customers(from_date, company, field)
elif doctype == "Item":
- items = get_all_items(doctype, filters, [], field)
+ records = get_all_items(from_date, company, field)
elif doctype == "Supplier":
- items = get_all_suppliers(doctype, filters, [], field)
+ records = get_all_suppliers(from_date, company, field)
elif doctype == "Sales Partner":
- items = get_all_sales_partner(doctype, filters, [], field)
+ records = get_all_sales_partner(from_date, company, field)
+ elif doctype == "Sales Person":
+ records = get_all_sales_person(from_date, company)
- if len(items) > 0:
- return items
- return []
+ return records
-def get_all_customers(doctype, filters, items, field, start=0, limit=20):
- """return all customers"""
+def get_all_customers(from_date, company, field):
+ if field == "outstanding_amount":
+ return frappe.db.sql("""
+ select customer as name, sum(outstanding_amount) as value
+ FROM `tabSales Invoice`
+ where docstatus = 1 and posting_date >= %s and company = %s
+ group by customer
+ order by value DESC
+ limit 20
+ """, (from_date, company), as_dict=1)
+ else:
+ if field == "total_sales_amount":
+ select_field = "sum(so_item.base_net_amount)"
+ elif field == "total_qty_sold":
+ select_field = "sum(so_item.stock_qty)"
- x = frappe.get_list(doctype, filters=filters, limit_start=start, limit_page_length=limit)
+ return frappe.db.sql("""
+ select so.customer as name, {0} as value
+ FROM `tabSales Order` as so JOIN `tabSales Order Item` as so_item
+ ON so.name = so_item.parent
+ where so.docstatus = 1 and so.transaction_date >= %s and so.company = %s
+ group by so.customer
+ order by value DESC
+ limit 20
+ """.format(select_field), (from_date, company), as_dict=1)
- for val in x:
- y = dict(frappe.db.sql('''select name, grand_total from `tabSales Invoice` where customer = %s''', (val.name)))
- invoice_list = y.keys()
- if len(invoice_list) > 0:
- item_count = frappe.db.sql('''select count(name) from `tabSales Invoice Item` where parent in (%s)''' % ", ".join(
- ['%s'] * len(invoice_list)), tuple(invoice_list))
+def get_all_items(from_date, company, field):
+ if field in ("available_stock_qty", "available_stock_value"):
+ return frappe.db.sql("""
+ select item_code as name, {0} as value
+ from tabBin
+ group by item_code
+ order by value desc
+ limit 20
+ """.format("sum(actual_qty)" if field=="available_stock_qty" else "sum(stock_value)"), as_dict=1)
+ else:
+ if field == "total_sales_amount":
+ select_field = "sum(order_item.base_net_amount)"
+ select_doctype = "Sales Order"
+ elif field == "total_purchase_amount":
+ select_field = "sum(order_item.base_net_amount)"
+ select_doctype = "Purchase Order"
+ elif field == "total_qty_sold":
+ select_field = "sum(order_item.stock_qty)"
+ select_doctype = "Sales Order"
+ elif field == "total_qty_purchased":
+ select_field = "sum(order_item.stock_qty)"
+ select_doctype = "Purchase Order"
- value = 0
- if(field=="total_amount"):
- value = sum(y.values())
- elif(field=="total_item_purchased"):
- value = sum(destructure_tuple_of_tuples(item_count))
+ return frappe.db.sql("""
+ select order_item.item_code as name, {0} as value
+ from `tab{1}` sales_order join `tab{1} Item` as order_item
+ on sales_order.name = order_item.parent
+ where sales_order.docstatus = 1
+ and sales_order.company = %s and sales_order.transaction_date >= %s
+ group by order_item.item_code
+ order by value desc
+ limit 20
+ """.format(select_field, select_doctype), (company, from_date), as_dict=1)
- item_obj = {"name": val.name,
- "total_amount": get_formatted_value(sum(y.values())),
- "total_item_purchased": sum(destructure_tuple_of_tuples(item_count)),
- "href":"#Form/Customer/" + val.name,
- "value": value}
- items.append(item_obj)
+def get_all_suppliers(from_date, company, field):
+ if field == "outstanding_amount":
+ return frappe.db.sql("""
+ select supplier as name, sum(outstanding_amount) as value
+ FROM `tabPurchase Invoice`
+ where docstatus = 1 and posting_date >= %s and company = %s
+ group by supplier
+ order by value DESC
+ limit 20""", (from_date, company), as_dict=1)
+ else:
+ if field == "total_purchase_amount":
+ select_field = "sum(purchase_order_item.base_net_amount)"
+ elif field == "total_qty_purchased":
+ select_field = "sum(purchase_order_item.stock_qty)"
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
+ return frappe.db.sql("""
+ select purchase_order.supplier as name, {0} as value
+ FROM `tabPurchase Order` as purchase_order LEFT JOIN `tabPurchase Order Item`
+ as purchase_order_item ON purchase_order.name = purchase_order_item.parent
+ where purchase_order.docstatus = 1 and purchase_order.modified >= %s
+ and purchase_order.company = %s
+ group by purchase_order.supplier
+ order by value DESC
+ limit 20""".format(select_field), (from_date, company), as_dict=1)
-def get_all_items(doctype, filters, items, field, start=0, limit=20):
- """return all items"""
+def get_all_sales_partner(from_date, company, field):
+ if field == "total_sales_amount":
+ select_field = "sum(base_net_total)"
+ elif field == "total_commission":
+ select_field = "sum(total_commission)"
- x = frappe.get_list(doctype, filters=filters, limit_start=start, limit_page_length=limit)
- for val in x:
- data = frappe.db.sql('''select item_code from `tabMaterial Request Item` where item_code = %s''', (val.name), as_list=1)
- requests = destructure_tuple_of_tuples(data)
- data = frappe.db.sql('''select price_list_rate from `tabItem Price` where item_code = %s''', (val.name), as_list=1)
- avg_price = get_avg(destructure_tuple_of_tuples(data))
- data = frappe.db.sql('''select item_code from `tabPurchase Invoice Item` where item_code = %s''', (val.name), as_list=1)
- purchases = destructure_tuple_of_tuples(data)
+ return frappe.db.sql("""
+ select sales_partner as name, {0} as value
+ from `tabSales Order`
+ where ifnull(sales_partner, '') != '' and docstatus = 1
+ and transaction_date >= %s and company = %s
+ group by sales_partner
+ order by value DESC
+ limit 20
+ """.format(select_field), (from_date, company), as_dict=1)
- value = 0
- if(field=="total_request"):
- value = len(requests)
- elif(field=="total_purchase"):
- value = len(purchases)
- elif(field=="avg_price"):
- value=avg_price
- item_obj = {"name": val.name,
- "total_request":len(requests),
- "total_purchase": len(purchases),
- "avg_price": get_formatted_value(avg_price),
- "href":"#Form/Item/" + val.name,
- "value": value}
- items.append(item_obj)
+def get_all_sales_person(from_date, company):
+ return frappe.db.sql("""
+ select sales_team.sales_person as name, sum(sales_order.base_net_total) as value
+ from `tabSales Order` as sales_order join `tabSales Team` as sales_team
+ on sales_order.name = sales_team.parent and sales_team.parenttype = 'Sales Order'
+ where sales_order.docstatus = 1
+ and sales_order.transaction_date >= %s
+ and sales_order.company = %s
+ group by sales_team.sales_person
+ order by value DESC
+ limit 20
+ """, (from_date, company), as_dict=1)
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
-
-def get_all_suppliers(doctype, filters, items, field, start=0, limit=20):
- """return all suppliers"""
-
- x = frappe.get_list(doctype, filters=filters, limit_start=start, limit_page_length=limit)
-
- for val in x:
-
- info = get_dashboard_info(doctype, val.name)
- value = 0
- if(field=="annual_billing"):
- value = info["billing_this_year"]
- elif(field=="total_unpaid"):
- value = abs(info["total_unpaid"])
-
- item_obj = {"name": val.name,
- "annual_billing": get_formatted_value(info["billing_this_year"]),
- "total_unpaid": get_formatted_value(abs(info["total_unpaid"])),
- "href":"#Form/Supplier/" + val.name,
- "value": value}
- items.append(item_obj)
-
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
-
-def get_all_sales_partner(doctype, filters, items, field, start=0, limit=20):
- """return all sales partner"""
-
- x = frappe.get_list(doctype, fields=["name", "commission_rate", "modified"], filters=filters, limit_start=start, limit_page_length=limit)
- for val in x:
- y = frappe.db.sql('''select target_qty, target_amount from `tabTarget Detail` where parent = %s''', (val.name), as_dict=1)
- target_qty = sum([f["target_qty"] for f in y])
- target_amount = sum([f["target_amount"] for f in y])
-
- value = 0
- if(field=="commission_rate"):
- value = val.commission_rate
- elif(field=="target_qty"):
- value = target_qty
- elif(field=="target_amount"):
- value = target_qty
-
- item_obj = {"name": val.name,
- "commission_rate": get_formatted_value(val.commission_rate, False),
- "target_qty": target_qty,
- "target_amount": get_formatted_value(target_qty),
- "href":"#Form/Sales Partner/" + val.name,
- "value": value}
- items.append(item_obj)
-
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
-
-
-def destructure_tuple_of_tuples(tup_of_tup):
- """return tuple(tuples) as list"""
- return [y for x in tup_of_tup for y in x]
-
-def get_date_from_string(seleted_timespan):
+def get_from_date(seleted_timespan):
"""return string for ex:this week as date:string"""
days = months = years = 0
if "month" == seleted_timespan.lower():
@@ -155,24 +149,5 @@
else:
days = -7
- return add_to_date(None, years=years, months=months, days=days, as_string=True, as_datetime=True)
-
-def get_filter_list(selected_filter):
- """return list of keys"""
- return map((lambda y : y["field"]), filter(lambda x : not (x["field"] == "name" or x["field"] == "modified"), selected_filter))
-
-def get_avg(items):
- """return avg of list items"""
- length = len(items)
- if length > 0:
- return sum(items) / length
- return 0
-
-def get_formatted_value(value, add_symbol=True):
- """return formatted value"""
- if not add_symbol:
- return '{:.{pre}f}'.format(value, pre=(get_currency_precision() or 2))
- currency_precision = get_currency_precision() or 2
- company = frappe.db.get_default("company")
- currency = frappe.get_doc("Company", company).default_currency or frappe.boot.sysdefaults.currency
- return fmt_money(value, currency_precision, currency)
+ return add_to_date(None, years=years, months=months, days=days,
+ as_string=True, as_datetime=True)
\ No newline at end of file