fix: covert Program wise Fee Collection to a Script Report
diff --git a/erpnext/education/dashboard_chart/program_wise_fee_collection/program_wise_fee_collection.json b/erpnext/education/dashboard_chart/program_wise_fee_collection/program_wise_fee_collection.json
index 62f453d..38c1b6d 100644
--- a/erpnext/education/dashboard_chart/program_wise_fee_collection/program_wise_fee_collection.json
+++ b/erpnext/education/dashboard_chart/program_wise_fee_collection/program_wise_fee_collection.json
@@ -1,16 +1,17 @@
{
"chart_name": "Program wise Fee Collection",
"chart_type": "Report",
- "creation": "2020-07-27 16:19:03.077317",
+ "creation": "2020-08-05 16:19:53.398335",
+ "custom_options": "",
"docstatus": 0,
"doctype": "Dashboard Chart",
- "dynamic_filters_json": "{}",
+ "dynamic_filters_json": "{\"from_date\":\"frappe.datetime.add_months(frappe.datetime.get_today(), -1)\",\"to_date\":\"frappe.datetime.nowdate()\"}",
"filters_json": "{}",
"group_by_type": "Count",
"idx": 0,
"is_public": 1,
"is_standard": 1,
- "modified": "2020-07-27 16:19:11.333532",
+ "modified": "2020-08-05 16:20:47.436847",
"modified_by": "Administrator",
"module": "Education",
"name": "Program wise Fee Collection",
@@ -21,11 +22,7 @@
"timeseries": 0,
"timespan": "Last Year",
"type": "Bar",
- "use_report_chart": 0,
- "x_field": "Program",
- "y_axis": [
- {
- "y_field": "Fees Collected"
- }
- ]
+ "use_report_chart": 1,
+ "x_field": "",
+ "y_axis": []
}
\ No newline at end of file
diff --git a/erpnext/education/education_dashboard/education/education.json b/erpnext/education/education_dashboard/education/education.json
index 6d9f2de..41d3375 100644
--- a/erpnext/education/education_dashboard/education/education.json
+++ b/erpnext/education/education_dashboard/education/education.json
@@ -54,7 +54,7 @@
"idx": 0,
"is_default": 0,
"is_standard": 1,
- "modified": "2020-07-27 18:42:42.872547",
+ "modified": "2020-08-05 16:22:17.428101",
"modified_by": "Administrator",
"module": "Education",
"name": "Education",
diff --git a/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.js b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.js
new file mode 100644
index 0000000..72e8f12
--- /dev/null
+++ b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.js
@@ -0,0 +1,22 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Program wise Fee Collection"] = {
+ "filters": [
+ {
+ "fieldname": "from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1),
+ "reqd": 1
+ },
+ {
+ "fieldname": "to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ "reqd": 1
+ }
+ ]
+};
diff --git a/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.json b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.json
index 6b7a975..ee5c0de 100644
--- a/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.json
+++ b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.json
@@ -8,7 +8,7 @@
"idx": 0,
"is_standard": "Yes",
"json": "{}",
- "modified": "2020-07-27 18:00:22.852723",
+ "modified": "2020-08-05 14:14:12.410515",
"modified_by": "Administrator",
"module": "Education",
"name": "Program wise Fee Collection",
@@ -17,7 +17,7 @@
"query": "SELECT \n FeesCollected.program AS \"Program:Link/Program:200\",\n FeesCollected.paid_amount AS \"Fees Collected:Currency:150\",\n FeesCollected.outstanding_amount AS \"Outstanding Amount:Currency:150\",\n FeesCollected.grand_total \"Grand Total:Currency:150\"\nFROM (\n SELECT \n sum(grand_total) - sum(outstanding_amount) AS paid_amount, program,\n sum(outstanding_amount) AS outstanding_amount,\n sum(grand_total) AS grand_total\n FROM `tabFees`\n WHERE docstatus = 1\n GROUP BY program\n) AS FeesCollected\nORDER BY FeesCollected.paid_amount DESC",
"ref_doctype": "Fees",
"report_name": "Program wise Fee Collection",
- "report_type": "Query Report",
+ "report_type": "Script Report",
"roles": [
{
"role": "Academics User"
diff --git a/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.py b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.py
new file mode 100644
index 0000000..c145359
--- /dev/null
+++ b/erpnext/education/report/program_wise_fee_collection/program_wise_fee_collection.py
@@ -0,0 +1,124 @@
+# 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 _
+
+def execute(filters=None):
+ if not filters:
+ filters = {}
+
+ columns = get_columns(filters)
+ data = get_data(filters)
+ chart = get_chart_data(data)
+
+ return columns, data, None, chart
+
+def get_columns(filters=None):
+ return [
+ {
+ 'label': _('Program'),
+ 'fieldname': 'program',
+ 'fieldtype': 'Link',
+ 'options': 'Program',
+ 'width': 300
+ },
+ {
+ 'label': _('Fees Collected'),
+ 'fieldname': 'fees_collected',
+ 'fieldtype': 'Currency',
+ 'width': 200
+ },
+ {
+ 'label': _('Outstanding Amount'),
+ 'fieldname': 'outstanding_amount',
+ 'fieldtype': 'Currency',
+ 'width': 200
+ },
+ {
+ 'label': _('Grand Total'),
+ 'fieldname': 'grand_total',
+ 'fieldtype': 'Currency',
+ 'width': 200
+ }
+ ]
+
+
+def get_data(filters=None):
+ data = []
+
+ conditions = get_filter_conditions(filters)
+
+ fee_details = frappe.db.sql(
+ """
+ SELECT
+ FeesCollected.program,
+ FeesCollected.paid_amount,
+ FeesCollected.outstanding_amount,
+ FeesCollected.grand_total
+ FROM (
+ SELECT
+ sum(grand_total) - sum(outstanding_amount) AS paid_amount, program,
+ sum(outstanding_amount) AS outstanding_amount,
+ sum(grand_total) AS grand_total
+ FROM `tabFees`
+ WHERE
+ docstatus = 1 and
+ program IS NOT NULL
+ %s
+ GROUP BY program
+ ) AS FeesCollected
+ ORDER BY FeesCollected.paid_amount DESC
+ """ % (conditions)
+ , as_dict=1)
+
+ for entry in fee_details:
+ data.append({
+ 'program': entry.program,
+ 'fees_collected': entry.paid_amount,
+ 'outstanding_amount': entry.outstanding_amount,
+ 'grand_total': entry.grand_total
+ })
+
+ return data
+
+def get_filter_conditions(filters):
+ conditions = ''
+
+ if filters.get('from_date') and filters.get('to_date'):
+ conditions += " and posting_date BETWEEN '%s' and '%s'" % (filters.get('from_date'), filters.get('to_date'))
+
+ return conditions
+
+
+def get_chart_data(data):
+ if not data:
+ return
+
+ labels = []
+ fees_collected = []
+ outstanding_amount = []
+
+ for entry in data:
+ labels.append(entry.get('program'))
+ fees_collected.append(entry.get('fees_collected'))
+ outstanding_amount.append(entry.get('outstanding_amount'))
+
+ return {
+ 'data': {
+ 'labels': labels,
+ 'datasets': [
+ {
+ 'name': _('Fees Collected'),
+ 'values': fees_collected
+ },
+ {
+ 'name': _('Outstanding Amt'),
+ 'values': outstanding_amount
+ }
+ ]
+ },
+ 'type': 'bar'
+ }
+