Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 1 | # Copyright (c) 2017, Frappe Technologies Pvt. Ltd. and Contributors |
| 2 | # MIT License. See license.txt |
| 3 | |
| 4 | from __future__ import unicode_literals, print_function |
| 5 | import frappe |
| 6 | import json |
| 7 | from operator import itemgetter |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 8 | from frappe.utils import add_to_date, fmt_money |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 9 | from erpnext.accounts.party import get_dashboard_info |
| 10 | from erpnext.accounts.utils import get_currency_precision |
| 11 | |
| 12 | @frappe.whitelist() |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 13 | def get_leaderboard(doctype, timespan, field, start=0): |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 14 | """return top 10 items for that doctype based on conditions""" |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 15 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 16 | filters = get_date_from_string(timespan) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 17 | items = [] |
| 18 | if doctype == "Customer": |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 19 | items = get_all_customers(filters, [], field) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 20 | elif doctype == "Item": |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 21 | items = get_all_items(filters, [], field) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 22 | elif doctype == "Supplier": |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 23 | items = get_all_suppliers(filters, [], field) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 24 | elif doctype == "Sales Partner": |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 25 | items = get_all_sales_partner(filters, [], field) |
| 26 | elif doctype == "Sales Person": |
| 27 | items = get_all_sales_person(filters, [], field) |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 28 | |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 29 | if len(items) > 0: |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 30 | return items |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 31 | return [] |
| 32 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 33 | def get_all_customers(filters, items, field, start=0, limit=20): |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 34 | """return all customers""" |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 35 | if field == "total_sales_amount": |
| 36 | select_field = "sum(sales_order_item.base_net_amount)" |
| 37 | elif field == "total_item_purchased_qty": |
| 38 | select_field = "count(sales_order_item.name)" |
| 39 | elif field == "receivable_amount_outstanding_amount": |
| 40 | return frappe.db.sql(""" |
| 41 | select sales_invoice.customer as name, sum(sales_invoice.outstanding_amount) as value |
| 42 | FROM `tabSales Invoice` as sales_invoice |
| 43 | where sales_invoice.docstatus = 1 and sales_invoice.modified >= "{0}" |
| 44 | group by sales_invoice.customer |
| 45 | order by value DESC |
| 46 | limit {1}""".format(filters, limit), as_dict=1) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 47 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 48 | return frappe.db.sql(""" |
| 49 | select sales_order.customer as name, {0} as value |
| 50 | FROM `tabSales Order` as sales_order LEFT JOIN `tabSales Order Item` |
| 51 | as sales_order_item ON sales_order.name = sales_order_item.parent |
| 52 | where sales_order.docstatus = 1 and sales_order.modified >= "{1}" |
| 53 | group by sales_order.customer |
| 54 | order by value DESC |
| 55 | limit {2}""".format(select_field, filters, limit), as_dict=1) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 56 | |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 57 | |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 58 | |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 59 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 60 | def get_all_items(filters, items, field, start=0, limit=20): |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 61 | """return all items""" |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 62 | if field == "total_sales_amount": |
| 63 | select_field = "sum(B.amount)" |
| 64 | select_doctype = "`tabSales Order Item`" |
| 65 | if field == "total_purchase_amount": |
| 66 | select_field = "sum(B.amount)" |
| 67 | select_doctype = "`tabPurchase Order Item`" |
| 68 | if field == "total_sold_qty": |
| 69 | select_field = "sum(B.qty)" |
| 70 | select_doctype = "`tabSales Order Item`" |
| 71 | if field == "total_purchased_qty": |
| 72 | select_field = "sum(B.qty)" |
| 73 | select_doctype = "`tabPurchase Order Item`" |
| 74 | if field == "available_stock_qty": |
| 75 | select_field = "sum(B.actual_qty)" |
| 76 | select_doctype = "`tabBin`" |
| 77 | return frappe.db.sql("""select |
| 78 | item.name as name , {0} as value |
| 79 | from `tabItem` as item join {1} as B on item.name = B.item_code and item.modified >= "{2}" |
| 80 | group by item.name""".format(select_field, select_doctype, filters), as_dict=1) |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 81 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 82 | def get_all_suppliers(filters, items, field, start=0, limit=20): |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 83 | """return all suppliers""" |
| 84 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 85 | if field == "total_purchase_amount": |
| 86 | select_field = "sum(purchase_order_item.base_net_amount)" |
| 87 | elif field == "total_item_sold_qty": |
| 88 | select_field = "count(purchase_order_item.name)" |
| 89 | elif field == "payable_amount_outstanding_amount": |
| 90 | return frappe.db.sql(""" |
| 91 | select purchase_invoice.supplier as name, sum(purchase_invoice.outstanding_amount) as value |
| 92 | FROM `tabPurchase Invoice` as purchase_invoice |
| 93 | where purchase_invoice.docstatus = 1 and purchase_invoice.modified >= "{0}" |
| 94 | group by purchase_invoice.supplier |
| 95 | order by value DESC |
| 96 | limit {1}""".format(filters, limit), as_dict=1) |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 97 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 98 | return frappe.db.sql(""" |
| 99 | select purchase_order.supplier as name, {0} as value |
| 100 | FROM `tabPurchase Order` as purchase_order LEFT JOIN `tabPurchase Order Item` |
| 101 | as purchase_order_item ON purchase_order.name = purchase_order_item.parent |
| 102 | where purchase_order.docstatus = 1 and purchase_order.modified >= "{1}" |
| 103 | group by purchase_order.supplier |
| 104 | order by value DESC |
| 105 | limit {2}""".format(select_field, filters, limit), as_dict=1) |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 106 | |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 107 | |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 108 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 109 | def get_all_sales_partner(filters, items, field, start=0, limit=20): |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 110 | """return all sales partner""" |
| 111 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 112 | if field == "commission_rate": |
| 113 | select_field = "sales_partner.commission_rate" |
| 114 | elif field == "target_qty": |
| 115 | select_field = "target_detail.target_qty" |
| 116 | elif field == "target_amount": |
| 117 | select_field = "target_detail.target_amount" |
| 118 | elif field == "total_sales_amount": |
| 119 | select_field = "sum(sales_invoice.total_commission)" |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 120 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 121 | return frappe.db.sql("""select sales_partner.partner_name as name, {0} as value |
| 122 | from |
| 123 | `tabSales Partner` as sales_partner inner join `tabTarget Detail` as target_detail ON sales_partner.name = target_detail.parent |
| 124 | inner join |
| 125 | `tabSales Invoice` as sales_invoice ON sales_invoice.sales_partner = sales_partner.name |
| 126 | where |
| 127 | sales_invoice.docstatus = 1 and sales_invoice.modified >= "{1}" |
| 128 | group by |
| 129 | sales_partner.partner_name |
| 130 | order by value DESC |
| 131 | limit {2}""".format(select_field, filters, limit), as_dict=1) |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 132 | |
| 133 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 134 | def get_all_sales_person(filters, items, field, start=0, limit=20): |
| 135 | """return all sales partner""" |
| 136 | if field == "target_qty": |
| 137 | select_field = "target_detail.target_qty" |
| 138 | elif field == "target_amount": |
| 139 | select_field = "target_detail.target_amount" |
| 140 | elif field == "total_sales_amount": |
| 141 | select_field = "sum(sales_team.allocated_amount)" |
| 142 | |
| 143 | return frappe.db.sql("""select sales_person.name as name, {0} as value |
| 144 | from |
| 145 | `tabSales Person` as sales_person |
| 146 | inner join |
| 147 | `tabTarget Detail` as target_detail ON sales_person.name = target_detail.parent |
| 148 | inner join |
| 149 | `tabSales Team` as sales_team ON sales_team.sales_person = sales_person.name |
| 150 | where sales_person.is_group = 0 and sales_team.modified >= "{1}" |
| 151 | group by sales_person.name |
| 152 | order by value DESC |
| 153 | limit {2}""".format(select_field,filters,limit), as_dict=1) |
| 154 | |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 155 | |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 156 | def get_date_from_string(seleted_timespan): |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 157 | """return string for ex:this week as date:string""" |
| 158 | days = months = years = 0 |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 159 | if "month" == seleted_timespan.lower(): |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 160 | months = -1 |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 161 | elif "quarter" == seleted_timespan.lower(): |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 162 | months = -3 |
Prateeksha Singh | 9b4f3cf | 2017-09-18 16:41:04 +0530 | [diff] [blame] | 163 | elif "year" == seleted_timespan.lower(): |
Ayush Shukla | a111f78 | 2017-06-20 13:04:45 +0530 | [diff] [blame] | 164 | years = -1 |
| 165 | else: |
| 166 | days = -7 |
| 167 | |
vishdha | 09acb77 | 2018-02-20 12:44:11 +0530 | [diff] [blame^] | 168 | return add_to_date(None, years=years, months=months, days=days, as_string=True, as_datetime=True) |