how can i fixing the Name Of columns in UI script report and display it in the report
criteria = frappe.db.sql("""
SELECT
sict.select_field,
sict.dynamic_options
FROM `tabSales Incentives Criteria Tab` sict
INNER JOIN `tabSales Incentives Criteria` sic
ON sic.name = sict.parent
""", as_dict=True)
dynamic_columns = []
for criterion in criteria:
select_field = criterion.get('select_field')
dynamic_options = criterion.get('dynamic_options')
if select_field and dynamic_options:
formatted_field = select_field.replace(" ", "_").lower()
dynamic_options_list = dynamic_options.split(",")
dynamic_options_sql = ", ".join([f"'{option.strip()}'" for option in dynamic_options_list])
if 'item group' in select_field.lower():
dynamic_columns.append(f"SUM(CASE WHEN sii.item_group IN ({dynamic_options_sql}) THEN sii.qty ELSE 0 END) AS total_qty_{formatted_field}")
dynamic_columns.append(f"SUM(CASE WHEN sii.item_group IN ({dynamic_options_sql}) THEN sii.amount ELSE 0 END) AS total_value_{formatted_field}")
elif 'item' in select_field.lower():
dynamic_columns.append(f"SUM(CASE WHEN sii.item_code IN ({dynamic_options_sql}) THEN sii.qty ELSE 0 END) AS total_qty_{formatted_field}")
dynamic_columns.append(f"SUM(CASE WHEN sii.item_code IN ({dynamic_options_sql}) THEN sii.amount ELSE 0 END) AS total_value_{formatted_field}")
elif 'category' in select_field.lower():
dynamic_columns.append(f"SUM(CASE WHEN sii.category IN ({dynamic_options_sql}) THEN sii.qty ELSE 0 END) AS total_qty_{formatted_field}")
dynamic_columns.append(f"SUM(CASE WHEN sii.category IN ({dynamic_options_sql}) THEN sii.amount ELSE 0 END) AS total_value_{formatted_field}")
dynamic_columns_sql = ", ".join(dynamic_columns) if dynamic_columns else "NULL AS total_qty_default, NULL AS total_value_default"
sql_query = f"""
SELECT
e.employee,
e.employee_name,
e.designation,
si.related_business,
so.owner AS sales_order_owner,
{dynamic_columns_sql}
FROM `tabSales Invoice` si
INNER JOIN `tabSales Invoice Item` sii ON si.name = sii.parent
LEFT JOIN `tabSales Order` so ON sii.sales_order = so.name
LEFT JOIN `tabEmployee` e ON e.user_id = so.owner
GROUP BY so.owner
"""
results = frappe.db.sql(sql_query, as_dict=True)