Merge pull request #40062 from GursheenK/sales-person-target-contributions
fix: sales person / partner achieved targets in report
diff --git a/erpnext/selling/report/sales_partner_target_variance_based_on_item_group/item_group_wise_sales_target_variance.py b/erpnext/selling/report/sales_partner_target_variance_based_on_item_group/item_group_wise_sales_target_variance.py
index 7d28f2b..f2f1e4c 100644
--- a/erpnext/selling/report/sales_partner_target_variance_based_on_item_group/item_group_wise_sales_target_variance.py
+++ b/erpnext/selling/report/sales_partner_target_variance_based_on_item_group/item_group_wise_sales_target_variance.py
@@ -206,42 +206,36 @@
def get_actual_data(filters, sales_users_or_territory_data, date_field, sales_field):
fiscal_year = get_fiscal_year(fiscal_year=filters.get("fiscal_year"), as_dict=1)
- dates = [fiscal_year.year_start_date, fiscal_year.year_end_date]
- select_field = "`tab{0}`.{1}".format(filters.get("doctype"), sales_field)
- child_table = "`tab{0}`".format(filters.get("doctype") + " Item")
+ parent_doc = frappe.qb.DocType(filters.get("doctype"))
+ child_doc = frappe.qb.DocType(filters.get("doctype") + " Item")
+ sales_team = frappe.qb.DocType("Sales Team")
+
+ query = (
+ frappe.qb.from_(parent_doc)
+ .inner_join(child_doc)
+ .on(child_doc.parent == parent_doc.name)
+ .inner_join(sales_team)
+ .on(sales_team.parent == parent_doc.name)
+ .select(
+ child_doc.item_group,
+ (child_doc.stock_qty * sales_team.allocated_percentage / 100).as_("stock_qty"),
+ (child_doc.base_net_amount * sales_team.allocated_percentage / 100).as_("base_net_amount"),
+ sales_team.sales_person,
+ parent_doc[date_field],
+ )
+ .where(
+ (parent_doc.docstatus == 1)
+ & (parent_doc[date_field].between(fiscal_year.year_start_date, fiscal_year.year_end_date))
+ )
+ )
if sales_field == "sales_person":
- select_field = "`tabSales Team`.sales_person"
- child_table = "`tab{0}`, `tabSales Team`".format(filters.get("doctype") + " Item")
- cond = """`tabSales Team`.parent = `tab{0}`.name and
- `tabSales Team`.sales_person in ({1}) """.format(
- filters.get("doctype"), ",".join(["%s"] * len(sales_users_or_territory_data))
- )
+ query = query.where(sales_team.sales_person.isin(sales_users_or_territory_data))
else:
- cond = "`tab{0}`.{1} in ({2})".format(
- filters.get("doctype"), sales_field, ",".join(["%s"] * len(sales_users_or_territory_data))
- )
+ query = query.where(parent_doc[sales_field].isin(sales_users_or_territory_data))
- return frappe.db.sql(
- """ SELECT `tab{child_doc}`.item_group,
- `tab{child_doc}`.stock_qty, `tab{child_doc}`.base_net_amount,
- {select_field}, `tab{parent_doc}`.{date_field}
- FROM `tab{parent_doc}`, {child_table}
- WHERE
- `tab{child_doc}`.parent = `tab{parent_doc}`.name
- and `tab{parent_doc}`.docstatus = 1 and {cond}
- and `tab{parent_doc}`.{date_field} between %s and %s""".format(
- cond=cond,
- date_field=date_field,
- select_field=select_field,
- child_table=child_table,
- parent_doc=filters.get("doctype"),
- child_doc=filters.get("doctype") + " Item",
- ),
- tuple(sales_users_or_territory_data + dates),
- as_dict=1,
- )
+ return query.run(as_dict=True)
def get_parents_data(filters, partner_doctype):
diff --git a/erpnext/selling/report/sales_person_target_variance_based_on_item_group/test_sales_person_target_variance_based_on_item_group.py b/erpnext/selling/report/sales_person_target_variance_based_on_item_group/test_sales_person_target_variance_based_on_item_group.py
new file mode 100644
index 0000000..4ae5d2b
--- /dev/null
+++ b/erpnext/selling/report/sales_person_target_variance_based_on_item_group/test_sales_person_target_variance_based_on_item_group.py
@@ -0,0 +1,84 @@
+import frappe
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import flt, nowdate
+
+from erpnext.accounts.utils import get_fiscal_year
+from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+from erpnext.selling.report.sales_person_target_variance_based_on_item_group.sales_person_target_variance_based_on_item_group import (
+ execute,
+)
+
+
+class TestSalesPersonTargetVarianceBasedOnItemGroup(FrappeTestCase):
+ def setUp(self):
+ self.fiscal_year = get_fiscal_year(nowdate())[0]
+
+ def tearDown(self):
+ frappe.db.rollback()
+
+ def test_achieved_target_and_variance(self):
+ # Create a Target Distribution
+ distribution = frappe.new_doc("Monthly Distribution")
+ distribution.distribution_id = "Target Report Distribution"
+ distribution.fiscal_year = self.fiscal_year
+ distribution.get_months()
+ distribution.insert()
+
+ # Create sales people with targets
+ person_1 = create_sales_person_with_target("Sales Person 1", self.fiscal_year, distribution.name)
+ person_2 = create_sales_person_with_target("Sales Person 2", self.fiscal_year, distribution.name)
+
+ # Create a Sales Order with 50-50 contribution
+ so = make_sales_order(
+ rate=1000,
+ qty=20,
+ do_not_submit=True,
+ )
+ so.set(
+ "sales_team",
+ [
+ {
+ "sales_person": person_1.name,
+ "allocated_percentage": 50,
+ "allocated_amount": 10000,
+ },
+ {
+ "sales_person": person_2.name,
+ "allocated_percentage": 50,
+ "allocated_amount": 10000,
+ },
+ ],
+ )
+ so.submit()
+
+ # Check Achieved Target and Variance
+ result = execute(
+ frappe._dict(
+ {
+ "fiscal_year": self.fiscal_year,
+ "doctype": "Sales Order",
+ "period": "Yearly",
+ "target_on": "Quantity",
+ }
+ )
+ )[1]
+ row = frappe._dict(result[0])
+ self.assertSequenceEqual(
+ [flt(value, 2) for value in (row.total_target, row.total_achieved, row.total_variance)],
+ [50, 10, -40],
+ )
+
+
+def create_sales_person_with_target(sales_person_name, fiscal_year, distribution_id):
+ sales_person = frappe.new_doc("Sales Person")
+ sales_person.sales_person_name = sales_person_name
+ sales_person.append(
+ "targets",
+ {
+ "fiscal_year": fiscal_year,
+ "target_qty": 50,
+ "target_amount": 30000,
+ "distribution_id": distribution_id,
+ },
+ )
+ return sales_person.insert()
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 9f3ba0d..847488f 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
@@ -36,6 +36,7 @@
d.base_net_amount,
d.sales_person,
d.allocated_percentage,
+ (d.stock_qty * d.allocated_percentage / 100),
d.contribution_amt,
company_currency,
]
@@ -103,7 +104,7 @@
"fieldtype": "Link",
"width": 140,
},
- {"label": _("Qty"), "fieldname": "qty", "fieldtype": "Float", "width": 140},
+ {"label": _("SO Total Qty"), "fieldname": "qty", "fieldtype": "Float", "width": 140},
{
"label": _("Amount"),
"options": "currency",
@@ -120,6 +121,12 @@
},
{"label": _("Contribution %"), "fieldname": "contribution", "fieldtype": "Float", "width": 140},
{
+ "label": _("Contribution Qty"),
+ "fieldname": "contribution_qty",
+ "fieldtype": "Float",
+ "width": 140,
+ },
+ {
"label": _("Contribution Amount"),
"options": "currency",
"fieldname": "contribution_amt",