def renew_recent_resumers():
resume_date = fmt_date(datetime.date(current_year_end().year, 4, 1))
end_date = fmt_date(datetime.date(current_year_end().year, 8, 1))
query_clauses = [
('Payment', 'date', resume_date, '>=', None),
('Payment', 'type', PaymentType.dues, '=', None),
('Member', 'end_date', end_date, '=', None),
]
members = get_members_for_query(query_clauses)
count = 0
message = []
for member in members:
if member.is_recent_resume():
member.end_date = new_end_date
item = first_or_default([
a for a in member.actions if a.action == MemberAction.card
and a.status == ActionStatus.open
], None)
if not item:
item = Action(member_id=member.id,
date=datetime.date.today(),
action=MemberAction.card,
comment='auto renew recent resumer',
status=ActionStatus.open)
member.actions.append(item)
message += [member.dt_number()]
save_member(member)
count += 1
return '\n'.join(['{} members updated'.format(count)] + message)
def is_recent_resume(self):
if self.status == MemberStatus.life:
return False
last = self.last_payment()
if not last:
resume = False
else:
resume = last.comment == 'resume lapsed'
date = self.last_payment('date')
not_dd_pending = self.last_payment_method != PaymentMethod.dd_pending
return resume and not_dd_pending and date >= datetime(
current_year_end().year, 4, 1).date()
def upgrade_dues(self, as_of=None):
if self.status == [MemberStatus.life]:
return 0
if not as_of:
as_of = current_year_end()
type = self.member_type_at_renewal(as_of)
if type in MembershipType.concessions():
return PlusUpgradeDues.concession.value
if type == MembershipType.intermediate:
return PlusUpgradeDues.intermediate.value
if type == MembershipType.senior:
return PlusUpgradeDues.senior.value
return PlusUpgradeDues.standard.value
def extract_renewals():
# for renewal notices at membership year end
end_date = fmt_date(current_year_end())
query_clauses = [('Member', 'end_date', end_date, '=', None),
('Member', 'status',
[s.value
for s in MemberStatus.all_active()], 'in', None)]
display_fields = \
['number', 'id number', 'full name', 'address (line 1)', 'address (line 2)', 'address (line 3)',
'city', 'county', 'state', 'post code', 'country for post', 'fan id', 'status', 'member type',
'type at renewal', 'email', 'comms', 'payment method', 'renewal notes', 'home phone', 'mobile phone',
'birth date', 'junior email', 'AFCW access', '3rd pty access', 'recent new', 'recent resume']
return Query.show_found_do(query_clauses, display_fields)
def member_type_at_renewal(self, as_of=None):
if self.member_type in MembershipType.concessions():
return self.member_type
if not as_of:
as_of = current_year_end()
age = self.age(as_of, True)
if age <= AgeBand.junior.upper:
return MembershipType.junior
if age <= AgeBand.intermediate.upper:
return MembershipType.intermediate
if age < AgeBand.senior.lower:
return MembershipType.standard
if age >= AgeBand.senior.lower:
return MembershipType.senior
return MembershipType.standard
def extract_debits():
end_date = fmt_date(current_year_end())
query_clauses = [
('Member', 'end_date', end_date, '=', None),
('Member', 'last_payment_method',
[PaymentMethod.dd.value,
PaymentMethod.dd_pending.value], 'in', None),
('Member', 'status', [s.value for s in MemberStatus.all_active()],
'in', None)
]
display_fields = \
['number', 'type at renewal', 'full name', 'dues pending', 'email', 'phone',
'address (line 1)', 'address (line 2)', 'address (line 3)', 'city', 'county', 'state', 'post code',
'country for post', 'title', 'first name', 'last name']
return Query.show_found_do(query_clauses, display_fields)
def set_region():
query_clauses = [
('Member', 'status', [s.value
for s in MemberStatus.all_active()], 'in', None),
('Member', 'end_date', fmt_date(current_year_end()), '=', None)
]
members = get_members_for_query(query_clauses)
count = 0
for member in members:
region = get_region(member.address.country, member.address.post_code)
if region:
member.address.region = region
save_member(member)
count += 1
return '{} records processed'.format(count)
def base_dues(self, as_of=None):
if not as_of:
as_of = current_year_end()
if self.status == MemberStatus.life or self.is_recent_resume(
) or self.is_recent_new():
return 0
type = self.member_type_at_renewal(as_of)
if type in MembershipType.concessions():
return Dues.concession.value
if type == MembershipType.junior:
return Dues.junior.value
if type == MembershipType.intermediate:
return Dues.intermediate.value
if type == MembershipType.senior:
return Dues.senior.value
return Dues.standard.value
def extract_debits_for_ptx():
end_date = fmt_date(current_year_end())
query_clauses = [
('Member', 'end_date', end_date, '=', None),
('Member', 'last_payment_method',
[PaymentMethod.dd.value,
PaymentMethod.dd_pending.value], 'in', None),
('Member', 'status', [s.value for s in MemberStatus.all_active()],
'in', None)
]
display_fields = [(None, 'Contact Reference'),
('fmt id number', 'Mandate Reference'),
(None, 'Mandate Status'), ('title for ptx', 'Title'),
('first name', 'First Name'),
('last name', 'Last Name'), (None, 'Company'),
('address (line 1)', 'Street1'),
('address (line 2)', 'Street2'),
('address (line 3)', 'Street3'), ('city', 'City'),
('post code', 'Post Code'), ('county', 'County'),
('country for post', 'Country'),
('phone', 'Telephone'), ('email', 'Email'),
('number at renewal', 'Alternative Reference'),
(None, 'Account Name'), (None, 'Sort Code'),
(None, 'Account Number'), (None, 'Plan Index'),
(None, 'Frequency Type', '{YEARLY}'),
(None, 'Start Date', '{2021-08-11}'),
(None, 'End Date'), (None, 'Number of Occurrences'),
(None, 'Recurrence'),
(None, 'Frequency Details1', '{DAY11}'),
(None, 'Frequency Details2', '{AUGUST}'),
('fmt dues pending', 'Regular Amount'),
(None, 'First Amount'), (None, 'Last Amount'),
(None, 'Total Amount'),
('extended type at renewal', 'Comments'),
(None, 'Profile Name'),
('comms for ptx', 'Communication Preference')]
return Query.show_found_do(query_clauses, display_fields)
def age_at_renewal(self, default=False):
next_renewal_date = current_year_end()
return self.age(next_renewal_date, default)
def is_recent_new(self):
return self.start_date >= datetime(current_year_end().year, 2,
1).date()
def get_members_for_query(query_clauses, default_table='Member', limit=None):
clauses = []
tables = tables_needed_for_query(default_table, query_clauses)
engine = db.session.bind.engine.name
for field in query_clauses:
if len(field) == 5:
field = field + (default_table,)
table, column, value, condition, func, field_name = field
type, values = field_type(table, column)
table = class_name_to_table_name(table)
null = value == 'null'
if null:
c = condition
if condition == '=':
c = 'is'
elif condition == '!=':
c = 'is not'
condition = c
s = '{}.{} {} {}'.format(table, column, condition, value)
elif type == 'string':
if condition == '=':
s = 'lower({}.{}) like lower("%{}%")'.format(table, column, value)
else:
s = '{}.{} {} "{}"'.format(table, column, condition, value)
elif type == 'enum':
if field_name == 'status' and condition == '<' and value == 4: # status = all active
condition = 'in'
value = [s.value for s in MemberStatus.all_active()]
s = '{}.{} {} {}'.format(table, column, condition, value)
elif type == 'date':
if not func:
date = sql_fmt_date(parse_date(value, reverse=True))
s = '{}.{} {} {}'.format(table, column, condition, date)
if func == 'birth_month()':
if engine == 'sqlite':
s = 'strftime("%m", {}.{}){} "{:02}"'.format(table, column, condition, value)
elif engine == 'mysql':
s = 'MONTH({}.{}){}{}'.format(table, column, condition, value)
else:
s = 'Unknown engine: ' + engine
if func in ['age()', 'age_at_renewal()']:
if func == 'age()':
date = 'current_date()'
else:
date = sql_fmt_date(current_year_end())
if '>' in condition:
condition = condition.replace('>', '<')
elif '<' in condition:
condition = condition.replace('<', '>')
if engine == 'sqlite':
s = '{}.{} {} date("now", "-{} years")'.format(table, column, condition, value)
elif engine == 'mysql':
s = '{}.{} {} date_add({}, interval -{} year)'.format(table, column, condition, date, value)
else:
s = 'Unknown engine: ' + engine
else:
s = '{}.{} {} {}'.format(table, column, condition, value)
if isinstance(value, list):
s = s.replace('[', '(').replace(']', ')')
clauses.append(s)
q = db.session.query(tables[0])
for table in tables[1:]:
q = q.join(table)
if len(clauses) > 0:
statement = ' and '.join(clauses)
q = q.filter(text(statement))
if globals()['Member'] in tables:
q = q.order_by('number')
if limit:
q = q.limit(limit)
return q
from globals import config
from globals.enumerations import MemberAction, MemberStatus, MembershipType, ActionStatus, PaymentType, PaymentMethod
from back_end.interface import get_members_for_query, get_member, save_member, get_region
from back_end.data_utilities import fmt_date, current_year_end, previous_year_end, first_or_default, file_delimiter, parse_date
from models.dt_db import Action, Payment
import datetime
import csv
from os import path
new_end_date = datetime.date(current_year_end().year + 1, 8, 1)
def renew_recent_joiners():
start_date = fmt_date(datetime.date(current_year_end().year, 2, 1))
end_date = fmt_date(datetime.date(current_year_end().year, 8, 1))
query_clauses = [
('Member', 'start_date', start_date, '>=', None),
('Member', 'end_date', end_date, '=', None),
]
members = get_members_for_query(query_clauses)
count = 0
message = []
for member in members:
member.end_date = new_end_date
item = first_or_default([
a for a in member.actions
if a.action == MemberAction.card and a.status == ActionStatus.open
], None)
if not item:
item = Action(member_id=member.id,