feat: sales partner reports
diff --git a/erpnext/selling/report/sales_partner_commission_summary/__init__.py b/erpnext/selling/report/sales_partner_commission_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_commission_summary/__init__.py
diff --git a/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.js b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.js
new file mode 100644
index 0000000..63d930c
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.js
@@ -0,0 +1,54 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Sales Partner Commission Summary"] = {
+ "filters": [
+
+ {
+ fieldname: "sales_partner",
+ label: __("Sales Partner"),
+ fieldtype: "Link",
+ options: "Sales Partner"
+ },
+ {
+ fieldname: "doctype",
+ label: __("Document Type"),
+ fieldtype: "Select",
+ options: "Sales Order\nDelivery Note\nSales Invoice",
+ default: "Sales Order"
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.get_today()
+ },
+ {
+ fieldname:"company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company")
+ },
+ {
+ fieldname:"customer",
+ label: __("Customer"),
+ fieldtype: "Link",
+ options: "Customer",
+ },
+ {
+ fieldname:"territory",
+ label: __("Territory"),
+ fieldtype: "Link",
+ options: "Territory",
+ },
+
+ ]
+}
diff --git a/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.json b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.json
new file mode 100644
index 0000000..79b82b9
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.json
@@ -0,0 +1,28 @@
+{
+ "add_total_row": 1,
+ "creation": "2019-03-15 15:22:15.012318",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2019-03-15 15:22:15.012318",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Sales Partner Commission Summary",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Sales Partner Commission Summary",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales Manager"
+ },
+ {
+ "role": "Maintenance User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.py b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.py
new file mode 100644
index 0000000..66f9aae
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_commission_summary/sales_partner_commission_summary.py
@@ -0,0 +1,113 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import msgprint, _
+from frappe.utils import flt
+
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ columns = get_columns(filters)
+ data = get_entries(filters)
+
+ return columns, data
+
+def get_columns(filters):
+ if not filters.get("doctype"):
+ msgprint(_("Please select the document type first"), raise_exception=1)
+
+ columns =[
+ {
+ "label": _(filters["doctype"]),
+ "options": filters["doctype"],
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Customer"),
+ "options": "Customer",
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Territory"),
+ "options": "Territory",
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "width": 100
+ },
+ {
+ "label": _("Posting Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Sales Partner"),
+ "options": "Sales Partner",
+ "fieldname": "sales_partner",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Commission Rate %"),
+ "fieldname": "commission_rate",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": _("Total Commission"),
+ "fieldname": "total_commission",
+ "fieldtype": "Currency",
+ "width": 120
+ }
+ ]
+
+ return columns
+
+def get_entries(filters):
+ date_field = ("transaction_date" if filters.get('doctype') == "Sales Order"
+ else "posting_date")
+
+ conditions = get_conditions(filters, date_field)
+ entries = frappe.db.sql("""
+ SELECT
+ name, customer, territory, {0} as posting_date, base_net_total as amount,
+ sales_partner, commission_rate, total_commission
+ FROM
+ `tab{1}`
+ WHERE
+ {2} and docstatus = 1 and sales_partner is not null
+ and sales_partner != '' order by name desc, sales_partner
+ """.format(date_field, filters.get('doctype'), conditions), filters, as_dict=1)
+
+ return entries
+
+def get_conditions(filters, date_field):
+ conditions = "1=1"
+
+ for field in ["company", "customer", "territory"]:
+ if filters.get(field):
+ conditions += " and {0} = %({1})s".format(field, field)
+
+ if filters.get("sales_partner"):
+ conditions += " and sales_partner = %(sales_partner)s"
+
+ if filters.get("from_date"):
+ conditions += " and {0} >= %(from_date)s".format(date_field)
+
+ if filters.get("to_date"):
+ conditions += " and {0} <= %(to_date)s".format(date_field)
+
+ return conditions
\ No newline at end of file
diff --git a/erpnext/selling/report/sales_partner_wise_transaction_summary/__init__.py b/erpnext/selling/report/sales_partner_wise_transaction_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_wise_transaction_summary/__init__.py
diff --git a/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.js b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.js
new file mode 100644
index 0000000..7e99dd4
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.js
@@ -0,0 +1,71 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Sales Partner-wise Transaction Summary"] = {
+ "filters": [
+ {
+ fieldname: "sales_partner",
+ label: __("Sales Partner"),
+ fieldtype: "Link",
+ options: "Sales Partner"
+ },
+ {
+ fieldname: "doctype",
+ label: __("Document Type"),
+ fieldtype: "Select",
+ options: "Sales Order\nDelivery Note\nSales Invoice",
+ default: "Sales Order"
+ },
+ {
+ fieldname: "from_date",
+ label: __("From Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ },
+ {
+ fieldname:"to_date",
+ label: __("To Date"),
+ fieldtype: "Date",
+ default: frappe.datetime.get_today()
+ },
+ {
+ fieldname:"company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company"),
+ reqd: 1
+ },
+ {
+ fieldname:"item_group",
+ label: __("Item Group"),
+ fieldtype: "Link",
+ options: "Item Group",
+ },
+ {
+ fieldname:"brand",
+ label: __("Brand"),
+ fieldtype: "Link",
+ options: "Brand",
+ },
+ {
+ fieldname:"customer",
+ label: __("Customer"),
+ fieldtype: "Link",
+ options: "Customer",
+ },
+ {
+ fieldname:"territory",
+ label: __("Territory"),
+ fieldtype: "Link",
+ options: "Territory",
+ },
+ {
+ fieldname:"show_return_entries",
+ label: __("Show Return Entries"),
+ fieldtype: "Check",
+ default: 0,
+ },
+ ]
+}
diff --git a/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.json b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.json
new file mode 100644
index 0000000..9384bfb
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.json
@@ -0,0 +1,37 @@
+{
+ "add_total_row": 1,
+ "creation": "2019-03-15 16:21:16.088831",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "Gadgets International",
+ "modified": "2019-03-15 16:21:16.088831",
+ "modified_by": "Administrator",
+ "module": "Selling",
+ "name": "Sales Partner-wise Transaction Summary",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Sales Order",
+ "report_name": "Sales Partner-wise Transaction Summary",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Sales User"
+ },
+ {
+ "role": "Sales Manager"
+ },
+ {
+ "role": "Maintenance User"
+ },
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Stock User"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.py b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.py
new file mode 100644
index 0000000..5356028
--- /dev/null
+++ b/erpnext/selling/report/sales_partner_wise_transaction_summary/sales_partner_wise_transaction_summary.py
@@ -0,0 +1,168 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import msgprint, _
+from frappe.utils import flt
+
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ columns = get_columns(filters)
+ data = get_entries(filters)
+
+ return columns, data
+
+def get_columns(filters):
+ if not filters.get("doctype"):
+ msgprint(_("Please select the document type first"), raise_exception=1)
+
+ columns =[
+ {
+ "label": _(filters["doctype"]),
+ "options": filters["doctype"],
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Customer"),
+ "options": "Customer",
+ "fieldname": "customer",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Territory"),
+ "options": "Territory",
+ "fieldname": "territory",
+ "fieldtype": "Link",
+ "width": 100
+ },
+ {
+ "label": _("Posting Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 100
+ },
+ {
+ "label": _("Item Code"),
+ "fieldname": "item_code",
+ "fieldtype": "Link",
+ "options": "Item",
+ "width": 100
+ },
+ {
+ "label": _("Item Group"),
+ "fieldname": "item_group",
+ "fieldtype": "Link",
+ "options": "Item Group",
+ "width": 100
+ },
+ {
+ "label": _("Brand"),
+ "fieldname": "brand",
+ "fieldtype": "Link",
+ "options": "Brand",
+ "width": 100
+ },
+ {
+ "label": _("Quantity"),
+ "fieldname": "qty",
+ "fieldtype": "Float",
+ "width": 120
+ },
+ {
+ "label": _("Rate"),
+ "fieldname": "rate",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Amount"),
+ "fieldname": "amount",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Sales Partner"),
+ "options": "Sales Partner",
+ "fieldname": "sales_partner",
+ "fieldtype": "Link",
+ "width": 140
+ },
+ {
+ "label": _("Commission Rate %"),
+ "fieldname": "commission_rate",
+ "fieldtype": "Data",
+ "width": 100
+ },
+ {
+ "label": _("Commission"),
+ "fieldname": "commission",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Currency"),
+ "fieldname": "currency",
+ "fieldtype": "Link",
+ "options": "Currency",
+ "width": 120
+ }
+ ]
+
+ return columns
+
+def get_entries(filters):
+ date_field = ("transaction_date" if filters.get('doctype') == "Sales Order"
+ else "posting_date")
+
+ conditions = get_conditions(filters, date_field)
+ entries = frappe.db.sql("""
+ SELECT
+ dt.name, dt.customer, dt.territory, dt.{date_field} as posting_date, dt.currency,
+ dt_item.base_net_rate as rate, dt_item.qty, dt_item.base_net_amount as amount,
+ ((dt_item.base_net_amount * dt.commission_rate) / 100) as commission,
+ dt_item.brand, dt.sales_partner, dt.commission_rate, dt_item.item_group, dt_item.item_code
+ FROM
+ `tab{doctype}` dt, `tab{doctype} Item` dt_item
+ WHERE
+ {cond} and dt.name = dt_item.parent and dt.docstatus = 1
+ and dt.sales_partner is not null and dt.sales_partner != ''
+ order by dt.name desc, dt.sales_partner
+ """.format(date_field=date_field, doctype=filters.get('doctype'),
+ cond=conditions), filters, as_dict=1)
+
+ return entries
+
+def get_conditions(filters, date_field):
+ conditions = "1=1"
+
+ for field in ["company", "customer", "territory", "sales_partner"]:
+ if filters.get(field):
+ conditions += " and dt.{0} = %({1})s".format(field, field)
+
+ if filters.get("from_date"):
+ conditions += " and dt.{0} >= %(from_date)s".format(date_field)
+
+ if filters.get("to_date"):
+ conditions += " and dt.{0} <= %(to_date)s".format(date_field)
+
+ if not filters.get('show_return_entries'):
+ conditions += " and dt_item.qty > 0.0"
+
+ if filters.get('brand'):
+ conditions += " and dt_item.brand = %(brand)s"
+
+ if filters.get('item_group'):
+ lft, rgt = frappe.get_cached_value('Item Group',
+ filters.get('item_group'), ['lft', 'rgt'])
+
+ conditions += """ and dt_item.item_group in (select name from
+ `tabItem Group` where lft >= %s and rgt <= %s)""" % (lft, rgt)
+
+
+ return conditions
\ No newline at end of file