Report to Track Expiring Memberships - Monthwise (#14207)
diff --git a/erpnext/non_profit/report/__init__.py b/erpnext/non_profit/report/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/non_profit/report/__init__.py
diff --git a/erpnext/non_profit/report/expiring_memberships/__init__.py b/erpnext/non_profit/report/expiring_memberships/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/non_profit/report/expiring_memberships/__init__.py
diff --git a/erpnext/non_profit/report/expiring_memberships/expiring_memberships.js b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.js
new file mode 100644
index 0000000..be3a243
--- /dev/null
+++ b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.js
@@ -0,0 +1,24 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Expiring Memberships"] = {
+ "filters": [
+ {
+ "fieldname": "fiscal_year",
+ "label": __("Fiscal Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"month",
+ "label": __("Month"),
+ "fieldtype": "Select",
+ "options": "Jan\nFeb\nMar\nApr\nMay\nJun\nJul\nAug\nSep\nOct\nNov\nDec",
+ "default": ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov",
+ "Dec"][frappe.datetime.str_to_obj(frappe.datetime.get_today()).getMonth()],
+ }
+ ]
+}
diff --git a/erpnext/non_profit/report/expiring_memberships/expiring_memberships.json b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.json
new file mode 100644
index 0000000..c311057
--- /dev/null
+++ b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.json
@@ -0,0 +1,27 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2018-05-24 11:44:08.942809",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "ERPNext Foundation",
+ "modified": "2018-05-24 11:44:08.942809",
+ "modified_by": "Administrator",
+ "module": "Non Profit",
+ "name": "Expiring Memberships",
+ "owner": "Administrator",
+ "ref_doctype": "Membership",
+ "report_name": "Expiring Memberships",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Non Profit Manager"
+ },
+ {
+ "role": "Non Profit Member"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/non_profit/report/expiring_memberships/expiring_memberships.py b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.py
new file mode 100644
index 0000000..122db45
--- /dev/null
+++ b/erpnext/non_profit/report/expiring_memberships/expiring_memberships.py
@@ -0,0 +1,33 @@
+# 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
+
+def execute(filters=None):
+ columns = get_columns(filters)
+ data = get_data(filters)
+ return columns, data
+
+def get_columns(filters):
+ return [
+ _("Membership Type") + ":Link/Membership Type:100", _("Membership ID") + ":Link/Membership:140",
+ _("Member ID") + ":Link/Member:140", _("Member Name") + ":Data:140", _("Email") + ":Data:140",
+ _("Expiring On") + ":Date:120"
+ ]
+
+def get_data(filters):
+
+ filters["month"] = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"].index(filters.month) + 1
+
+ return frappe.db.sql("""
+ select ms.membership_type,ms.name,m.name,m.member_name,m.email,ms.max_membership_date
+ from `tabMember` m
+ inner join (select name,membership_type,max(to_date) as max_membership_date,member
+ from `tabMembership`
+ where paid = 1
+ group by member
+ order by max_membership_date asc) ms
+ on m.name = ms.member
+ where month(max_membership_date) = %(month)s and year(max_membership_date) = %(year)s """,{'month': filters.get('month'),'year':filters.get('fiscal_year')})