Merge pull request #4699 from neilLasrado/tl-fix
Fixes in Time Log list view
diff --git a/erpnext/controllers/trends.py b/erpnext/controllers/trends.py
index 16387a5..6be8d96 100644
--- a/erpnext/controllers/trends.py
+++ b/erpnext/controllers/trends.py
@@ -31,10 +31,10 @@
for f in ["Fiscal Year", "Based On", "Period", "Company"]:
if not filters.get(f.lower().replace(" ", "_")):
frappe.throw(_("{0} is mandatory").format(f))
-
+
if not frappe.db.exists("Fiscal Year", filters.get("fiscal_year")):
frappe.throw(_("Fiscal Year: {0} does not exists").format(filters.get("fiscal_year")))
-
+
if filters.get("based_on") == filters.get("group_by"):
frappe.throw(_("'Based On' and 'Group By' can not be same"))
@@ -98,7 +98,8 @@
(filters.get("company"), filters.get("fiscal_year"), row[i][0],
data1[d][0]), as_list=1)
- des[ind] = row[i]
+ des[ind] = row[i][0]
+
for j in range(1,len(conditions["columns"])-inc):
des[j+inc] = row1[0][j]
@@ -213,7 +214,7 @@
elif based_on == "Customer":
based_on_details["based_on_cols"] = ["Customer:Link/Customer:120", "Territory:Link/Territory:120"]
based_on_details["based_on_select"] = "t1.customer_name, t1.territory, "
- based_on_details["based_on_group_by"] = 't1.customer_name'
+ based_on_details["based_on_group_by"] = 't1.customer'
based_on_details["addl_tables"] = ''
elif based_on == "Customer Group":
diff --git a/erpnext/selling/report/sales_order_trends/sales_order_trends.py b/erpnext/selling/report/sales_order_trends/sales_order_trends.py
index 79e7381..c0a0f08 100644
--- a/erpnext/selling/report/sales_order_trends/sales_order_trends.py
+++ b/erpnext/selling/report/sales_order_trends/sales_order_trends.py
@@ -10,5 +10,4 @@
data = []
conditions = get_columns(filters, "Sales Order")
data = get_data(filters, conditions)
-
- return conditions["columns"], data
\ No newline at end of file
+ return conditions["columns"], data
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..d27816c 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
@@ -61,39 +61,65 @@
#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]
+ 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,33 +127,32 @@
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 = {}
+ sales_person_achievement_dict = {}
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, {})\
- .setdefault(month, frappe._dict({
- "target": 0.0, "achieved": 0.0
- }))
+ sales_person_achievement_dict.setdefault(sd.name, {}).setdefault(sd.item_group, {})\
+ .setdefault(month, frappe._dict({
+ "target": 0.0, "achieved": 0.0
+ }))
- tav_dict = sim_map[sd.name][sd.item_group][month]
+ sales_target_achieved = sales_person_achievement_dict[sd.name][sd.item_group][month]
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"):
+ sales_target_achieved.target = flt(sd.target_qty) * month_percentage / 100
+ else:
+ sales_target_achieved.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
+ sales_target_achieved.achieved = achieved_details.get(sd.item_group, frappe._dict()).\
+ get(month, frappe._dict()).get(filters["target_on"].lower())
- return sim_map
+ return sales_person_achievement_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=True))
diff --git a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.json b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.json
index dabf604..0612dc0 100644
--- a/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.json
+++ b/erpnext/selling/report/sales_person_wise_transaction_summary/sales_person_wise_transaction_summary.json
@@ -1,12 +1,13 @@
{
- "add_total_row": 1,
+ "add_total_row": 0,
"apply_user_permissions": 1,
"creation": "2013-05-03 11:31:05",
+ "disabled": 0,
"docstatus": 0,
"doctype": "Report",
"idx": 1,
"is_standard": "Yes",
- "modified": "2014-06-03 07:18:17.312328",
+ "modified": "2016-01-28 04:22:49.476068",
"modified_by": "Administrator",
"module": "Selling",
"name": "Sales Person-wise Transaction Summary",
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..e9930f3 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
@@ -4,6 +4,7 @@
from __future__ import unicode_literals
import frappe
from frappe import msgprint, _
+from frappe.utils import flt
def execute(filters=None):
if not filters: filters = {}
@@ -12,13 +13,22 @@
entries = get_entries(filters)
item_details = get_item_details()
data = []
+ total_contribution_amount = 0
for d in entries:
+ total_contribution_amount += flt(d.contribution_amt)
+
data.append([
d.name, d.customer, d.territory, d.posting_date, d.item_code,
item_details.get(d.item_code, {}).get("item_group"), item_details.get(d.item_code, {}).get("brand"),
d.qty, d.base_net_amount, d.sales_person, d.allocated_percentage, d.contribution_amt
])
+ if data:
+ total_row = [""]*len(data[0])
+ total_row[0] = _("Total")
+ total_row[-1] = total_contribution_amount
+ data.append(total_row)
+
return columns, data
def get_columns(filters):
@@ -35,34 +45,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"])
-
+ lft, rgt = frappe.get_value("Sales Person", filters.get("sales_person"), ["lft", "rgt"])
+ conditions.append("exists(select name from `tabSales Person` where lft >= {0} and rgt <= {1} and name=st.sales_person)".format(lft, rgt))
+
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 7921f3e..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
@@ -13,10 +13,10 @@
columns = get_columns(filters)
period_month_ranges = get_period_month_ranges(filters["period"], filters["fiscal_year"])
- tim_map = get_territory_item_month_map(filters)
+ territory_item_group_dict = get_territory_item_month_map(filters)
data = []
- for territory, territory_items in tim_map.items():
+ for territory, territory_items in territory_item_group_dict.items():
for item_group, monthwise_data in territory_items.items():
row = [territory, item_group]
totals = [0, 0, 0]
@@ -59,38 +59,63 @@
#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):
+def get_achieved_details(filters, territory, 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,
- so.territory, MONTHNAME(so.transaction_date) as month_name
- from `tabSales Order Item` soi, `tabSales Order` so
- where soi.parent=so.name and so.docstatus=1 and so.transaction_date>=%s and
- so.transaction_date<=%s""" % ('%s', '%s'),
- (start_date, end_date), as_dict=1)
+ lft, rgt = frappe.db.get_value("Territory", territory, ["lft", "rgt"])
+
+ item_details = frappe.db.sql("""
+ select
+ soi.item_code, sum(soi.qty) as qty, sum(soi.base_net_amount) as amount,
+ MONTHNAME(so.transaction_date) as month_name
+ from
+ `tabSales Order Item` soi, `tabSales Order` so
+ where
+ soi.parent=so.name and so.docstatus=1
+ and so.transaction_date>=%s and so.transaction_date<=%s
+ and exists(select name from `tabTerritory` where lft >=%s and rgt <= %s and name=so.territory)
+ group by
+ month_name, item_code
+ """, (start_date, end_date, lft, rgt), as_dict=1)
item_actual_details = {}
for d in item_details:
- item_actual_details.setdefault(d.territory, {}).setdefault(\
- get_item_group(d.item_code), []).append(d)
+ item_group = item_groups[d.item_code]
+ 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
@@ -98,35 +123,35 @@
import datetime
territory_details = get_territory_details(filters)
tdd = get_target_distribution_details(filters)
- achieved_details = get_achieved_details(filters)
+ item_groups = get_item_groups()
- tim_map = {}
+ territory_item_group_dict = {}
for td in territory_details:
+ 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')
- tim_map.setdefault(td.name, {}).setdefault(td.item_group, {})\
+ territory_item_group_dict.setdefault(td.name, {}).setdefault(td.item_group, {})\
.setdefault(month, frappe._dict({
"target": 0.0, "achieved": 0.0
}))
- tav_dict = tim_map[td.name][td.item_group][month]
+ target_achieved = territory_item_group_dict[td.name][td.item_group][month]
month_percentage = tdd.get(td.distribution_id, {}).get(month, 0) \
if td.distribution_id else 100.0/12
- for ad in achieved_details.get(td.name, {}).get(td.item_group, []):
- if (filters["target_on"] == "Quantity"):
- tav_dict.target = flt(td.target_qty) * month_percentage / 100
- if ad.month_name == month:
- tav_dict.achieved += ad.qty
- if (filters["target_on"] == "Amount"):
- tav_dict.target = flt(td.target_amount) * month_percentage / 100
- if ad.month_name == month:
- tav_dict.achieved += ad.base_net_amount
+ if (filters["target_on"] == "Quantity"):
+ target_achieved.target = flt(td.target_qty) * month_percentage / 100
+ else:
+ target_achieved.target = flt(td.target_amount) * month_percentage / 100
- return tim_map
+ target_achieved.achieved = achieved_details.get(td.item_group, {}).get(month, {})\
+ .get(filters["target_on"].lower())
-def get_item_group(item_name):
- return frappe.db.get_value("Item", item_name, "item_group")
+ return territory_item_group_dict
+
+def get_item_groups():
+ return dict(frappe.get_all("Item", fields=["name", "item_group"], as_list=1))
diff --git a/erpnext/selling/sales_common.js b/erpnext/selling/sales_common.js
index 850e229..ce64f32 100644
--- a/erpnext/selling/sales_common.js
+++ b/erpnext/selling/sales_common.js
@@ -80,10 +80,6 @@
}
});
}
-
- if(this.frm.fields_dict.sales_team && this.frm.fields_dict.sales_team.grid.get_field("sales_person")) {
- this.frm.set_query("sales_person", "sales_team", erpnext.queries.not_a_group_filter);
- }
},
refresh: function() {