fix: Query enhancement, cleanup, added extra filter
- Query changes as requested
- Moved chart generation from js to py
- Added Supplier Multiselect filter
diff --git a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.js b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.js
index fe4abd8..a76ffee 100644
--- a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.js
+++ b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.js
@@ -16,7 +16,7 @@
default: "",
options: "Item",
label: __("Item"),
- fieldname: "item",
+ fieldname: "item_code",
fieldtype: "Link",
get_query: () => {
let quote = frappe.query_report.get_filter_value('supplier_quotation');
@@ -37,6 +37,14 @@
}
},
{
+ fieldname: "supplier",
+ label: __("Supplier"),
+ fieldtype: "MultiSelectList",
+ get_data: function(txt) {
+ return frappe.db.get_link_options('Supplier', txt);
+ }
+ },
+ {
fieldtype: "Link",
label: __("Supplier Quotation"),
options: "Supplier Quotation",
@@ -58,60 +66,6 @@
}
],
- prepare_chart_data: (result) => {
- let supplier_wise_map = {}, data_points_map = {};
- let qty_list = result.map(res => res.qty);
- qty_list.sort();
- qty_list = new Set(qty_list);
-
- // create supplier wise map like in Report
- for (let res of result) {
- if (!(res.supplier in supplier_wise_map)) {
- supplier_wise_map[res.supplier] = {};
- }
- supplier_wise_map[res.supplier][res.qty] = res.price;
- }
-
- // create datapoints for each qty
- for (let supplier of Object.keys(supplier_wise_map)) {
- let row = supplier_wise_map[supplier];
- for (let qty of qty_list) {
- if (!data_points_map[qty]) {
- data_points_map[qty] = [];
- }
- if (row[qty]) {
- data_points_map[qty].push(row[qty]);
- }
- else {
- data_points_map[qty].push(null);
- }
- }
- }
-
- let dataset = [];
- qty_list.forEach((qty) => {
- let datapoints = {
- 'name': __('Price for Qty ') + qty,
- 'values': data_points_map[qty]
- }
- dataset.push(datapoints);
- });
- return dataset;
- },
-
- get_chart_data: function (columns, result) {
- let suppliers = result.filter(d => d.supplier_name).map(res => res.supplier_name);
- let dataset = frappe.query_reports["Quoted Item Comparison"].prepare_chart_data(result);
-
- return {
- data: {
- labels: suppliers,
- datasets: dataset
- },
- type: 'bar'
- }
- },
-
onload: (report) => {
// Create a button for setting the default supplier
report.page.add_inner_button(__("Select Default Supplier"), () => {
diff --git a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
index fd7a731..a33867a 100644
--- a/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
+++ b/erpnext/buying/report/quoted_item_comparison/quoted_item_comparison.py
@@ -9,21 +9,33 @@
from erpnext.setup.utils import get_exchange_rate
def execute(filters=None):
+ if not filters:
+ return [], []
+
conditions = get_conditions(filters)
- data = get_data(filters, conditions)
+ supplier_quotation_data = get_data(filters, conditions)
columns = get_columns()
- return columns, data
+
+ data, chart_data = prepare_data(supplier_quotation_data)
+
+ return columns, data, None, chart_data
+
+def get_conditions(filters):
+ conditions = ""
+ if filters.get("supplier_quotation"):
+ conditions += " AND sqi.parent = %(supplier_quotation)s"
+
+ if filters.get("request_for_quotation"):
+ conditions += " AND sqi.request_for_quotation = %(request_for_quotation)s"
+
+ if filters.get("supplier"):
+ conditions += " AND sq.supplier in %(supplier)s"
+ return conditions
def get_data(filters, conditions):
- out, suppliers = [], []
- item = filters.get("item")
-
- if not item:
+ if not filters.get("item_code"):
return []
- company_currency = frappe.db.get_default("currency")
- float_precision = cint(frappe.db.get_default("float_precision")) or 2
-
supplier_quotation_data = frappe.db.sql("""SELECT
sqi.parent, sqi.qty, sqi.rate, sqi.uom, sqi.request_for_quotation,
sq.supplier
@@ -31,17 +43,27 @@
`tabSupplier Quotation Item` sqi,
`tabSupplier Quotation` sq
WHERE
- sqi.item_code = '{0}'
+ sqi.item_code = %(item_code)s
AND sqi.parent = sq.name
AND sqi.docstatus < 2
- AND sq.company = '{1}'
+ AND sq.company = %(company)s
AND sq.status != 'Expired'
- {2}""".format(item, filters.get("company"), conditions), as_dict=1)
+ {0}""".format(conditions), filters, as_dict=1)
+ return supplier_quotation_data
+
+def prepare_data(supplier_quotation_data):
+ out, suppliers, qty_list = [], [], []
supplier_wise_map = defaultdict(list)
+ supplier_qty_price_map = {}
+
+ company_currency = frappe.db.get_default("currency")
+ float_precision = cint(frappe.db.get_default("float_precision")) or 2
for data in supplier_quotation_data:
+ supplier = data.get("supplier")
supplier_currency = frappe.db.get_value("Supplier", data.get("supplier"), "default_currency")
+
if supplier_currency:
exchange_rate = get_exchange_rate(supplier_currency, company_currency)
else:
@@ -53,29 +75,64 @@
"price": flt(data.get("rate") * exchange_rate, float_precision),
"uom": data.get("uom"),
"request_for_quotation": data.get("request_for_quotation"),
- "supplier": data.get("supplier") # used for chart generation
}
- supplier_wise_map[data.supplier].append(row)
- suppliers.append(data.supplier)
+ # map for report view of form {'supplier1':[{},{},...]}
+ supplier_wise_map[supplier].append(row)
- suppliers = set(suppliers)
+ # map for chart preparation of the form {'supplier1': {'qty': 'price'}}
+ if not supplier in supplier_qty_price_map:
+ supplier_qty_price_map[supplier] = {}
+ supplier_qty_price_map[supplier][row["qty"]] = row["price"]
+ suppliers.append(supplier)
+ qty_list.append(data.get("qty"))
+
+ suppliers = list(set(suppliers))
+ qty_list = list(set(qty_list))
+
+ # final data format for report view
for supplier in suppliers:
supplier_wise_map[supplier][0].update({"supplier_name": supplier})
for entry in supplier_wise_map[supplier]:
out.append(entry)
- return out
+ chart_data = prepare_chart_data(suppliers, qty_list, supplier_qty_price_map)
-def get_conditions(filters):
- conditions = ""
+ return out, chart_data
- if filters.get("request_for_quotation"):
- conditions += " AND sqi.request_for_quotation = '{0}' ".format(filters.get("request_for_quotation"))
+def prepare_chart_data(suppliers, qty_list, supplier_qty_price_map):
+ data_points_map = {}
+ qty_list.sort()
- return conditions
+ # create qty wise values map of the form {'qty1':[value1, value2]}
+ for supplier in suppliers:
+ entry = supplier_qty_price_map[supplier]
+ for qty in qty_list:
+ if not qty in data_points_map:
+ data_points_map[qty] = []
+ if qty in entry:
+ data_points_map[qty].append(entry[qty])
+ else:
+ data_points_map[qty].append(None)
+ dataset = []
+ for qty in qty_list:
+ datapoints = {
+ "name": _("Price for Qty ") + str(qty),
+ "values": data_points_map[qty]
+ }
+ dataset.append(datapoints)
+
+ chart_data = {
+ "data": {
+ "labels": suppliers,
+ "datasets": dataset
+ },
+ "type": "bar"
+ }
+
+ return chart_data
def get_columns():
columns = [{