def member_etl(rec):
member = Member(
number=int(rec['Member ID'][2:]), # drop "0-"
sex=Sex.male
if rec['Sex'] == 'M' else Sex.female if rec['Sex'] == 'F' else None,
title=Title[rec['Prefix']] if rec['Prefix'] != '' else None,
first_name=rec['First Name'],
last_name=rec['Last Name'],
birth_date=parse_date(rec['Birthdate'],
sep='/',
reverse=True,
default=None),
email=rec['Email Address'],
home_phone=rec['Home Phone'],
mobile_phone=rec['Other Phone'],
status=status_etl[rec['Status Code']],
start_date=parse_date(rec['Start Date'], sep='/', reverse=True),
end_date=parse_date(rec['End Date'], sep='/', reverse=True),
last_updated=parse_date(rec['Updated'], sep='/', reverse=True),
last_payment_method=PaymentMethod[rec['Direct Debit']]
if rec['Direct Debit'] != '' else None,
external_access=external_access_etl(rec),
comms=CommsType.email if rec['Use email'] == 'yes' else CommsType.post,
comms_status=CommsStatus.email_fail if (rec['Email Address'] != '') and
(rec['Use email'] == 'bounced') else CommsStatus.all_ok,
address=address_etl(rec),
actions=actions_etl(rec))
member.member_type = type_etl(member, rec['Status Code'],
rec['Concession Type'])
if member.member_type == MembershipType.junior:
member.junior = Junior(email=rec['Junior email'],
gift=junior_gift_etl[rec['Junior Gift']]
if rec['Junior Gift'] != '' else None)
return member
def comment_etl(rec):
member_id = get_member_id(int(rec['Member ID'][2:])) # drop "0-"
recs = parse_comments(rec['Comments'].strip())
objects = []
for rec in recs:
if rec['Comment'] == 'dd payment made':
agedate = date(2019, 8, 1)
paydate = parse_date(rec['Date'], sep='/', reverse=True)
payment = Payment(member_id=member_id,
date=paydate,
amount=db_session.query(Member).filter_by(
id=member_id).first().dues(agedate,
default=True),
type=PaymentType.dues,
method=PaymentMethod.dd,
comment=None)
objects.append(payment)
else:
comment = Comment(member_id=member_id,
date=parse_date(rec['Date'],
sep='/',
reverse=True),
comment=rec['Comment'])
objects.append(comment)
return objects
def _test_etl_bookings(self):
year = '2018'
event = 'event14.csv'
event_id = 381
booking_file = os.path.join(TestData.data_location, year, event)
old = Table(*get_all_records(booking_file))
for old_booking in old.rows():
member_name = old_booking['name']
member = get_member_by_name(member_name)
booking = get_booking(event_id, member.id)
booking.date = parse_date(old_booking['date'], reverse=True)
booking.playing = old_booking['playing'] == '1'
booking.comment = old_booking['comment']
number = int(old_booking['number'])
guests = []
for count in range(1, number):
name = old_booking['guest{}'.format(count)]
hcap = parse_float(old_booking['guest{}_hcap'.format(count)])
guest = first_or_default([g for g in booking.guests if g.name == name], Guest(name=name, booking=booking))
known_player = get_player_by_name(name)
if known_player:
state = known_player.state_as_of(booking.date)
if state.status == PlayerStatus.member:
hcap = state.handicap
guest.handicap = hcap
guests.append(guest)
booking.guests = guests
save_booking(booking, True)
self.assertEqual(1, 1)
def get_minutes(type, date):
files = os.listdir(minutes_location)
minutes = [
Minutes(Minutes.map_file_to_type(f[:3]), parse_date(f[-14:-4], ' '), f[-3:])
for f in files
if not os.path.isdir(f) and f[:3] in Minutes.map_type_to_file(type)
]
minutes = [m for m in minutes if m.date == date]
if len(minutes) == 1:
return minutes[0]
def get_all_minutes(type=None, year=None):
if not type:
type = [t for t in MinutesType][1:]
type = [Minutes.map_type_to_file(t)[:3] for t in force_list(type)]
files = os.listdir(minutes_location)
minutes = [
Minutes(Minutes.map_file_to_type(f[:3]), parse_date(f[-14:-4], ' '), f[-3:])
for f in files
if not os.path.isdir(f) and f[:3] in type
]
if year:
minutes = [m for m in minutes if m.date.year in force_list(year)]
return sorted(minutes, key=Minutes.take_date, reverse=True)
def payment_etl(rec):
header = [
'Member ID', 'Dues Date Posted', 'Dues Amount Posted',
'Dues Posting Type', 'Dues Cheque Nbr', 'Dues Comments'
]
payment = Payment(
member_id=get_member_id(int(rec['Member ID'][2:])), # drop "0-"
date=parse_date(rec['Dues Date Posted'], sep='/', reverse=True),
amount=float(rec['Dues Amount Posted']),
type=PaymentType.dues,
method=payment_method_etl(rec['Dues Cheque Nbr']),
comment=rec['Dues Comments'])
return payment
def from_dict(self, data):
for field in self.dict_fields:
if field in data:
if 'date' in field:
setattr(self, field, parse_date(data[field], reverse=True))
elif field == 'address':
self.address.from_dict(data[field])
else:
if data[field] and isinstance(
getattr(Member, field).property.columns[0].type,
EnumType):
field_type = type(getattr(self, field))
value = field_type.from_name(data[field])
else:
value = data[field]
setattr(self, field, value)
def populate_account(self, member_id, year):
member = get_member(member_id)
self.title.data = '{} - Account information {}'.format(
member.player.full_name(), year)
balance = 0
account = get_member_account(member.player.full_name(), year)
for item in account.rows():
item_form = AccountItemForm()
item_form.date = fmt_date(parse_date(item['date'], reverse=True))
item_form.item = item['item']
debit = parse_float(item['debit'])
item_form.debit = fmt_curr(debit)
credit = parse_float(item['credit'])
item_form.credit = fmt_curr(credit)
self.items.append_entry(item_form)
balance += (credit or 0) - (debit or 0)
self.balance.data = fmt_curr(balance)
self.negative_balance.data = balance < 0
def populate_news_day(self, news_date):
is_new = news_date == 'new'
self.is_new.data = is_new
if is_new:
news_day = NewsDay(date=datetime.date.today())
self.save.label.text = 'Publish'
else:
news_day = News().get_news_day(news_date)
self.save.label.text = 'Save'
self.date.data = parse_date(news_day.date)
self.message.data = news_day.message
for i in range(5):
if i < len(news_day.items):
item = news_day.items[i]
else:
item = NewsItem()
item_form = NewsItemForm()
item_form.text = item.text
item_form.link = item.link
item_form.title = item.title
self.items.append_entry(item_form)
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
def update_member_payment(rec, payment_method, save=True):
# rec is line of payments file with keys id, date, amount and note
message = []
number = int(rec['id'][4:])
date = parse_date(rec['date'], sep='/', reverse=True)
amount = float(rec['amount'])
member = get_member(number)
pending = first_or_default([
p for p in member.payments
if p.type == PaymentType.pending and p.method == payment_method
], None)
payment_comment = 'from payments file'
if pending:
comment = ['']
if pending.amount != amount:
comment = [
"Expected amount {}, got {}".format(pending.amount, amount)
]
message += comment
pending.type = PaymentType.dues
pending.date = date
pending.amount = amount
pending.comment = payment_comment + ' ' + comment[0]
else:
dues = first_or_default([
p for p in member.payments
if p.type == PaymentType.dues and p.method == payment_method
], None)
if dues and dues.amount == amount and dues.date == date and dues.comment.startswith(
payment_comment):
message += ['Payment already processed']
return message
else:
message += ["no pending payment: adding one"]
pending = Payment(member_id=member.id,
date=date,
amount=amount,
type=PaymentType.dues,
method=payment_method,
comment=payment_comment)
member.payments.append(pending)
action = first_or_default([
a for a in member.actions
if a.action == MemberAction.card and a.status == ActionStatus.open
], None)
if not action:
action = Action(member_id=member.id,
date=datetime.date.today(),
action=MemberAction.card,
status=ActionStatus.open,
comment=payment_comment)
member.actions.append(action)
upgrade = amount in [20.0, 30.0, 45.0
] and member.status != MemberStatus.plus
action = first_or_default([
a for a in member.actions
if a.action == MemberAction.upgrade and a.status == ActionStatus.open
], None)
if action:
if upgrade:
member.status = MemberStatus.plus
action.status = ActionStatus.closed
# else:
# message += ['expected an upgrade payment, upgrade action removed']
# member.actions.remove(action)
else:
if upgrade:
action = Action(member_id=member.id,
date=date,
action=MemberAction.upgrade,
status=ActionStatus.closed,
comment=payment_comment)
member.actions.append(action)
member.end_date = new_end_date
member.last_payment_method = payment_method
if save:
save_member(member)
return message