[Partial]Fixed Sales person target variance report
diff --git a/erpnext/selling/report/sales_person_target_variance_item_group_wise/sales_person_target_variance_item_group_wise.py b/erpnext/selling/report/sales_person_target_variance_item_group_wise/sales_person_target_variance_item_group_wise.py
index f7aa70f..1d910e1 100644
--- a/erpnext/selling/report/sales_person_target_variance_item_group_wise/sales_person_target_variance_item_group_wise.py
+++ b/erpnext/selling/report/sales_person_target_variance_item_group_wise/sales_person_target_variance_item_group_wise.py
@@ -2,7 +2,7 @@
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
-import frappe
+import frappe, pprint
from frappe import _, msgprint
from frappe.utils import flt
from erpnext.accounts.utils import get_fiscal_year
@@ -61,39 +61,66 @@
#Get sales person & item group details
def get_salesperson_details(filters):
- return frappe.db.sql("""select sp.name, td.item_group, td.target_qty,
- td.target_amount, sp.distribution_id
- from `tabSales Person` sp, `tabTarget Detail` td
- where td.parent=sp.name and td.fiscal_year=%s order by sp.name""",
- (filters["fiscal_year"]), as_dict=1)
+ return frappe.db.sql("""
+ select
+ sp.name, td.item_group, td.target_qty, td.target_amount, sp.distribution_id
+ from
+ `tabSales Person` sp, `tabTarget Detail` td
+ where
+ td.parent=sp.name and td.fiscal_year=%s order by sp.name
+ """, (filters["fiscal_year"]), as_dict=1)
#Get target distribution details of item group
def get_target_distribution_details(filters):
target_details = {}
- for d in frappe.db.sql("""select md.name, mdp.month, mdp.percentage_allocation
- from `tabMonthly Distribution Percentage` mdp, `tabMonthly Distribution` md
- where mdp.parent=md.name and md.fiscal_year=%s""", (filters["fiscal_year"]), as_dict=1):
+ for d in frappe.db.sql("""
+ select
+ md.name, mdp.month, mdp.percentage_allocation
+ from
+ `tabMonthly Distribution Percentage` mdp, `tabMonthly Distribution` md
+ where
+ mdp.parent=md.name and md.fiscal_year=%s
+ """, (filters["fiscal_year"]), as_dict=1):
target_details.setdefault(d.name, {}).setdefault(d.month, flt(d.percentage_allocation))
return target_details
#Get achieved details from sales order
-def get_achieved_details(filters):
+def get_achieved_details(filters, sales_person, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:]
- item_details = frappe.db.sql("""select soi.item_code, soi.qty, soi.base_net_amount, so.transaction_date,
- st.sales_person, MONTHNAME(so.transaction_date) as month_name
- from `tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
- where soi.parent=so.name and so.docstatus=1 and
- st.parent=so.name and so.transaction_date>=%s and
- so.transaction_date<=%s""" % ('%s', '%s'),
- (start_date, end_date), as_dict=1)
+ lft, rgt = frappe.get_value("Sales Person", sales_person, ["lft", "rgt"])
+
+ item_details = frappe.db.sql("""
+ select
+ soi.item_code, sum(soi.qty * (st.allocated_percentage/100)) as qty,
+ sum(soi.base_net_amount * (st.allocated_percentage/100)) as amount,
+ st.sales_person, MONTHNAME(so.transaction_date) as month_name
+ from
+ `tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
+ where
+ soi.parent=so.name and so.docstatus=1 and st.parent=so.name
+ and so.transaction_date>=%s and so.transaction_date<=%s
+ and exists(select name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person)
+ group by
+ sales_person, item_code, month_name
+ """,
+ (start_date, end_date, lft, rgt), as_dict=1)
item_actual_details = {}
for d in item_details:
- item_actual_details.setdefault(d.sales_person, {}).setdefault(\
- get_item_group(d.item_code), []).append(d)
+ item_group = item_groups[d.item_code]
+ print item_group
+ item_actual_details.setdefault(item_group, frappe._dict()).setdefault(d.month_name,\
+ frappe._dict({
+ "quantity" : 0,
+ "amount" : 0
+ }))
+
+ value_dict = item_actual_details[item_group][d.month_name]
+ value_dict.quantity += flt(d.qty)
+ value_dict.amount += flt(d.amount)
return item_actual_details
@@ -101,10 +128,12 @@
import datetime
salesperson_details = get_salesperson_details(filters)
tdd = get_target_distribution_details(filters)
- achieved_details = get_achieved_details(filters)
+ item_groups = get_item_groups()
sim_map = {}
for sd in salesperson_details:
+ achieved_details = get_achieved_details(filters, sd.name, item_groups)
+
for month_id in range(1, 13):
month = datetime.date(2013, month_id, 1).strftime('%B')
sim_map.setdefault(sd.name, {}).setdefault(sd.item_group, {})\
@@ -116,18 +145,15 @@
month_percentage = tdd.get(sd.distribution_id, {}).get(month, 0) \
if sd.distribution_id else 100.0/12
- for ad in achieved_details.get(sd.name, {}).get(sd.item_group, []):
- if (filters["target_on"] == "Quantity"):
- tav_dict.target = flt(sd.target_qty) * month_percentage / 100
- if ad.month_name == month:
- tav_dict.achieved += ad.qty
+ if (filters["target_on"] == "Quantity"):
+ tav_dict.target = flt(sd.target_qty) * month_percentage / 100
+ else:
+ tav_dict.target = flt(sd.target_amount) * month_percentage / 100
- if (filters["target_on"] == "Amount"):
- tav_dict.target = flt(sd.target_amount) * month_percentage / 100
- if ad.month_name == month:
- tav_dict.achieved += ad.base_net_amount
+ tav_dict.achieved = achieved_details.get(sd.item_group, frappe._dict()).get(month,\
+ frappe._dict()).get(filters["target_on"].lower())
return sim_map
-def get_item_group(item_name):
- return frappe.db.get_value("Item", item_name, "item_group")
+def get_item_groups():
+ return dict(frappe.get_all("Item", fields=["name", "item_group"], as_list=True))
diff --git a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
index 65b0c08..5599579 100644
--- a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
+++ b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.py
@@ -35,34 +35,38 @@
def get_entries(filters):
date_field = filters["doc_type"] == "Sales Order" and "transaction_date" or "posting_date"
conditions, values = get_conditions(filters, date_field)
- entries = frappe.db.sql("""select dt.name, dt.customer, dt.territory, dt.%s as posting_date,
- dt_item.item_code, dt_item.qty, dt_item.base_net_amount, st.sales_person,
- st.allocated_percentage, dt_item.base_net_amount*st.allocated_percentage/100 as contribution_amt
- from `tab%s` dt, `tab%s Item` dt_item, `tabSales Team` st
- where st.parent = dt.name and dt.name = dt_item.parent and st.parenttype = %s
- and dt.docstatus = 1 %s order by st.sales_person, dt.name desc""" %
- (date_field, filters["doc_type"], filters["doc_type"], '%s', conditions),
- tuple([filters["doc_type"]] + values), as_dict=1)
+ entries = frappe.db.sql("""
+ select
+ dt.name, dt.customer, dt.territory, dt.%s as posting_date, dt_item.item_code,
+ dt_item.qty, dt_item.base_net_amount, st.sales_person, st.allocated_percentage,
+ dt_item.base_net_amount*st.allocated_percentage/100 as contribution_amt
+ from
+ `tab%s` dt, `tab%s Item` dt_item, `tabSales Team` st
+ where
+ st.parent = dt.name and dt.name = dt_item.parent and st.parenttype = %s
+ and dt.docstatus = 1 %s order by st.sales_person, dt.name desc
+ """ %(date_field, filters["doc_type"], filters["doc_type"], '%s', conditions),
+ tuple([filters["doc_type"]] + values), as_dict=1)
return entries
def get_conditions(filters, date_field):
conditions = [""]
values = []
-
+
for field in ["company", "customer", "territory"]:
if filters.get(field):
conditions.append("dt.{0}=%s".format(field))
values.append(filters[field])
-
+
if filters.get("sales_person"):
conditions.append("st.sales_person=%s")
values.append(filters["sales_person"])
-
+
if filters.get("from_date"):
conditions.append("dt.{0}>=%s".format(date_field))
values.append(filters["from_date"])
-
+
if filters.get("to_date"):
conditions.append("dt.{0}<=%s".format(date_field))
values.append(filters["to_date"])
diff --git a/erpnext/selling/report/territory_target_variance_item_group_wise/territory_target_variance_item_group_wise.py b/erpnext/selling/report/territory_target_variance_item_group_wise/territory_target_variance_item_group_wise.py
index 3248a1d..dd34333 100644
--- a/erpnext/selling/report/territory_target_variance_item_group_wise/territory_target_variance_item_group_wise.py
+++ b/erpnext/selling/report/territory_target_variance_item_group_wise/territory_target_variance_item_group_wise.py
@@ -59,25 +59,33 @@
#Get territory & item group details
def get_territory_details(filters):
- return frappe.db.sql("""select t.name, td.item_group, td.target_qty,
- td.target_amount, t.distribution_id
- from `tabTerritory` t, `tabTarget Detail` td
- where td.parent=t.name and td.fiscal_year=%s order by t.name""",
- (filters["fiscal_year"]), as_dict=1)
+ return frappe.db.sql("""
+ select
+ t.name, td.item_group, td.target_qty, td.target_amount, t.distribution_id
+ from
+ `tabTerritory` t, `tabTarget Detail` td
+ where
+ td.parent=t.name and td.fiscal_year=%s order by t.name
+ """, (filters["fiscal_year"]), as_dict=1)
#Get target distribution details of item group
def get_target_distribution_details(filters):
target_details = {}
- for d in frappe.db.sql("""select md.name, mdp.month, mdp.percentage_allocation
- from `tabMonthly Distribution Percentage` mdp, `tabMonthly Distribution` md
- where mdp.parent=md.name and md.fiscal_year=%s""", (filters["fiscal_year"]), as_dict=1):
+ for d in frappe.db.sql("""
+ select
+ md.name, mdp.month, mdp.percentage_allocation
+ from
+ `tabMonthly Distribution Percentage` mdp, `tabMonthly Distribution` md
+ where
+ mdp.parent=md.name and md.fiscal_year=%s
+ """, (filters["fiscal_year"]), as_dict=1):
target_details.setdefault(d.name, {}).setdefault(d.month, flt(d.percentage_allocation))
return target_details
#Get achieved details from sales order
-def get_achieved_details(filters, territory):
+def get_achieved_details(filters, territory, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:]
lft, rgt = frappe.db.get_value("Territory", territory, ["lft", "rgt"])
@@ -98,7 +106,7 @@
item_actual_details = {}
for d in item_details:
- item_group = get_item_group(d.item_code)
+ item_group = item_groups[d.item_code]
item_actual_details.setdefault(item_group, frappe._dict())\
.setdefault(d.month_name, frappe._dict({
"quantity": 0,
@@ -115,11 +123,12 @@
import datetime
territory_details = get_territory_details(filters)
tdd = get_target_distribution_details(filters)
+ item_groups = get_item_groups()
territory_item_group_dict = {}
for td in territory_details:
- achieved_details = get_achieved_details(filters, td.name)
+ achieved_details = get_achieved_details(filters, td.name, item_groups)
for month_id in range(1, 13):
month = datetime.date(2013, month_id, 1).strftime('%B')
@@ -144,5 +153,5 @@
return territory_item_group_dict
-def get_item_group(item_name):
- return frappe.db.get_value("Item", item_name, "item_group")
+def get_item_groups():
+ return dict(frappe.get_all("Item", fields=["name", "item_group"], as_list=1))