[new] Leader board
diff --git a/erpnext/utilities/page/leaderboard/leaderboard.py b/erpnext/utilities/page/leaderboard/leaderboard.py
index 6e7eabc..6db40ab 100644
--- a/erpnext/utilities/page/leaderboard/leaderboard.py
+++ b/erpnext/utilities/page/leaderboard/leaderboard.py
@@ -13,135 +13,145 @@
def get_leaderboard(doctype, timespan, field, start=0):
"""return top 10 items for that doctype based on conditions"""
- filters = {"modified":(">=", get_date_from_string(timespan))}
+ filters = get_date_from_string(timespan)
items = []
if doctype == "Customer":
- items = get_all_customers(doctype, filters, [], field)
+ items = get_all_customers(filters, [], field)
elif doctype == "Item":
- items = get_all_items(doctype, filters, [], field)
+ items = get_all_items(filters, [], field)
elif doctype == "Supplier":
- items = get_all_suppliers(doctype, filters, [], field)
+ items = get_all_suppliers(filters, [], field)
elif doctype == "Sales Partner":
- items = get_all_sales_partner(doctype, filters, [], field)
+ items = get_all_sales_partner(filters, [], field)
+ elif doctype == "Sales Person":
+ items = get_all_sales_person(filters, [], field)
if len(items) > 0:
return items
return []
-def get_all_customers(doctype, filters, items, field, start=0, limit=20):
+def get_all_customers(filters, items, field, start=0, limit=20):
"""return all customers"""
+ if field == "total_sales_amount":
+ select_field = "sum(sales_order_item.base_net_amount)"
+ elif field == "total_item_purchased_qty":
+ select_field = "count(sales_order_item.name)"
+ elif field == "receivable_amount_outstanding_amount":
+ return frappe.db.sql("""
+ select sales_invoice.customer as name, sum(sales_invoice.outstanding_amount) as value
+ FROM `tabSales Invoice` as sales_invoice
+ where sales_invoice.docstatus = 1 and sales_invoice.modified >= "{0}"
+ group by sales_invoice.customer
+ order by value DESC
+ limit {1}""".format(filters, limit), as_dict=1)
- x = frappe.get_list(doctype, filters=filters, limit_start=start, limit_page_length=limit)
+ return frappe.db.sql("""
+ select sales_order.customer as name, {0} as value
+ FROM `tabSales Order` as sales_order LEFT JOIN `tabSales Order Item`
+ as sales_order_item ON sales_order.name = sales_order_item.parent
+ where sales_order.docstatus = 1 and sales_order.modified >= "{1}"
+ group by sales_order.customer
+ order by value DESC
+ limit {2}""".format(select_field, filters, limit), 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))
- value = 0
- if(field=="total_amount"):
- value = sum(y.values())
- elif(field=="total_item_purchased"):
- value = sum(destructure_tuple_of_tuples(item_count))
- 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)
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
-
-def get_all_items(doctype, filters, items, field, start=0, limit=20):
+def get_all_items(filters, items, field, start=0, limit=20):
"""return all items"""
+ if field == "total_sales_amount":
+ select_field = "sum(B.amount)"
+ select_doctype = "`tabSales Order Item`"
+ if field == "total_purchase_amount":
+ select_field = "sum(B.amount)"
+ select_doctype = "`tabPurchase Order Item`"
+ if field == "total_sold_qty":
+ select_field = "sum(B.qty)"
+ select_doctype = "`tabSales Order Item`"
+ if field == "total_purchased_qty":
+ select_field = "sum(B.qty)"
+ select_doctype = "`tabPurchase Order Item`"
+ if field == "available_stock_qty":
+ select_field = "sum(B.actual_qty)"
+ select_doctype = "`tabBin`"
+ return frappe.db.sql("""select
+ item.name as name , {0} as value
+ from `tabItem` as item join {1} as B on item.name = B.item_code and item.modified >= "{2}"
+ group by item.name""".format(select_field, select_doctype, filters), as_dict=1)
- 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)
-
- 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)
-
- items.sort(key=lambda k: k['value'], reverse=True)
- return items
-
-def get_all_suppliers(doctype, filters, items, field, start=0, limit=20):
+def get_all_suppliers(filters, items, field, start=0, limit=20):
"""return all suppliers"""
- x = frappe.get_list(doctype, filters=filters, limit_start=start, limit_page_length=limit)
+ if field == "total_purchase_amount":
+ select_field = "sum(purchase_order_item.base_net_amount)"
+ elif field == "total_item_sold_qty":
+ select_field = "count(purchase_order_item.name)"
+ elif field == "payable_amount_outstanding_amount":
+ return frappe.db.sql("""
+ select purchase_invoice.supplier as name, sum(purchase_invoice.outstanding_amount) as value
+ FROM `tabPurchase Invoice` as purchase_invoice
+ where purchase_invoice.docstatus = 1 and purchase_invoice.modified >= "{0}"
+ group by purchase_invoice.supplier
+ order by value DESC
+ limit {1}""".format(filters, limit), as_dict=1)
- for val in x:
+ 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 >= "{1}"
+ group by purchase_order.supplier
+ order by value DESC
+ limit {2}""".format(select_field, filters, limit), as_dict=1)
- 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):
+def get_all_sales_partner(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])
+ if field == "commission_rate":
+ select_field = "sales_partner.commission_rate"
+ elif field == "target_qty":
+ select_field = "target_detail.target_qty"
+ elif field == "target_amount":
+ select_field = "target_detail.target_amount"
+ elif field == "total_sales_amount":
+ select_field = "sum(sales_invoice.total_commission)"
- 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
+ return frappe.db.sql("""select sales_partner.partner_name as name, {0} as value
+ from
+ `tabSales Partner` as sales_partner inner join `tabTarget Detail` as target_detail ON sales_partner.name = target_detail.parent
+ inner join
+ `tabSales Invoice` as sales_invoice ON sales_invoice.sales_partner = sales_partner.name
+ where
+ sales_invoice.docstatus = 1 and sales_invoice.modified >= "{1}"
+ group by
+ sales_partner.partner_name
+ order by value DESC
+ limit {2}""".format(select_field, filters, limit), as_dict=1)
-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_all_sales_person(filters, items, field, start=0, limit=20):
+ """return all sales partner"""
+ if field == "target_qty":
+ select_field = "target_detail.target_qty"
+ elif field == "target_amount":
+ select_field = "target_detail.target_amount"
+ elif field == "total_sales_amount":
+ select_field = "sum(sales_team.allocated_amount)"
+
+ return frappe.db.sql("""select sales_person.name as name, {0} as value
+ from
+ `tabSales Person` as sales_person
+ inner join
+ `tabTarget Detail` as target_detail ON sales_person.name = target_detail.parent
+ inner join
+ `tabSales Team` as sales_team ON sales_team.sales_person = sales_person.name
+ where sales_person.is_group = 0 and sales_team.modified >= "{1}"
+ group by sales_person.name
+ order by value DESC
+ limit {2}""".format(select_field,filters,limit), as_dict=1)
+
def get_date_from_string(seleted_timespan):
"""return string for ex:this week as date:string"""
@@ -155,24 +165,4 @@
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