def main():
parser = ArgumentParser()
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
config = load_config()
if args.verbose:
print('processing file {}'.format(config['file']), file=sys.stderr)
book = open_workbook(config['file'])
sheet = book.sheet_by_name(config['sheet'])
rows = sheet.get_rows()
_ = next(rows)
for r in rows:
num, paid, parent, mobile, name, *days = map(lambda c: c.value, r)
print('num={},paid={},parent={},mobile={},name={},days={}'.format(
num, paid, parent, mobile, name, days))
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--partreport', '-p', default=None, metavar='F',
help='specify participant report file to use')
parser.add_argument('--tbreport', '-t', default=None, metavar='F',
help='specify trybooking report file to use')
parser.add_argument('--verbose', '-v', action='store_true',
help='print verbose messages')
args = parser.parse_args()
config = load_config()
teams = fetch_teams()
fetch_participants(teams, args.partreport, args.verbose)
tb = fetch_trybooking(config['tbmap'], args.tbreport, args.verbose)
def match_names(fn1, fn2):
if fn1.lower() == fn2.lower():
return True
else:
return False
unpaid = []
paid = []
for t in teams.values():
for p in t.players:
e = find_in_tb(tb, to_fullname(p['first name'], p['last name']))
if e is None:
unpaid.append(p)
else:
paid.append(p)
if paid:
print('Paid: ({})'.format(len(paid)))
for p in sorted(
paid, key=lambda p: to_fullname(p['first name'], p['last name'])
):
print(' {}'.format(to_fullname(p['first name'], p['last name'])))
if unpaid:
print('Unpaid: ({})'.format(len(unpaid)))
for p in sorted(
unpaid, key=lambda p: to_fullname(p['first name'], p['last name'])
):
print(' {}'.format(to_fullname(p['first name'], p['last name'])))
if tb['by-name']:
print('Unknown: ({})'.format(len(tb['by-name'])))
for fn, e in sorted(tb['by-name'].items(), key=lambda e: e[0]):
print(' {}'.format(fn))
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--reportdir',
default='reports',
metavar='D',
help='directory containing report files')
parser.add_argument('--partfile',
default=None,
metavar='F',
help='csv file containing program participant report')
parser.add_argument('--merchfile',
default=None,
metavar='F',
help='csv file containing merchandise orders report')
parser.add_argument('--reffile',
default=None,
metavar='F',
help='file to use as reference for last run')
parser.add_argument('--refdt',
default=None,
metavar='T',
help='datetime to use as reference for last run')
parser.add_argument('--notouch',
action='store_true',
help='do not touch the reference file')
parser.add_argument('--basename',
default='-',
metavar='N',
help='basename of output file (- = stdout)')
parser.add_argument('--asxls',
action='store_true',
help='output excel data')
parser.add_argument('--email',
action='store_true',
help='print a list of email addresses')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
reportdir = args.reportdir
if not os.path.isdir(reportdir):
reportdir = os.path.join(clinicdir, args.reportdir)
if not os.path.isdir(reportdir):
raise RuntimeError('cannot locate reports directory!')
if args.verbose:
print('[reports found in directory {} (realpath={})]'.format(
reportdir, os.path.realpath(reportdir)),
file=sys.stderr)
partfile = args.partfile
if partfile is None:
partfile, _ = latest_report('program_participant',
reportdir,
verbose=args.verbose)
if partfile is None:
raise RuntimeError('cannot locate program participant file!')
if args.verbose:
print('[program participant report file = {} (realpath={})]'.format(
partfile, os.path.realpath(partfile)),
file=sys.stderr)
merchfile = args.merchfile
if merchfile is None:
merchfile, _ = latest_report(
'merchandiseorders', reportdir,
r'^merchandiseorders_(\d{8})\.csv$',
lambda m: datetime.strptime(m.group(1), '%Y%m%d'), args.verbose)
if merchfile is None:
raise RuntimeError('cannot locate merchandise order file!')
if args.verbose:
print('[merchandise orders report file = {} (realpath={})]'.format(
merchfile, os.path.realpath(merchfile)),
file=sys.stderr)
reffile = args.reffile
if reffile is None:
reffile = '.reffile'
if not os.path.exists(reffile):
reffile = os.path.join(clinicdir, reffile)
if args.verbose:
print('[reference file = {} (realpath={})]'.format(
reffile, os.path.realpath(reffile)),
file=sys.stderr)
if args.refdt is not None:
refdt = dateutil_parse(args.refdt, dayfirst=True, fuzzy=True)
else:
if os.path.exists(reffile):
refdt = datetime.fromtimestamp(os.stat(reffile).st_mtime)
else:
refdt = None
if args.verbose:
if refdt is not None:
print('[reference datetime = {}]'.format(refdt), file=sys.stderr)
else:
print('[No reference datetime available!]', file=sys.stderr)
config = load_config(prefix=clinicdir)
with open(partfile, 'r', newline='') as infile:
reader = DictReader(infile)
orecs = {}
for inrec in reader:
if inrec['role'] != 'Player' or inrec['status'] != 'Active':
if args.verbose:
print(
'ignore Non-Player or Inactive rec: {}'.format(inrec),
file=sys.stderr)
continue
school_term = inrec['season']
if school_term != config['label']:
raise RuntimeError('School Term mismatch! ({}!={})'.format(
school_term, config['label']))
name = inrec['first name'] + ' ' + inrec['last name']
date_of_birth = to_date(inrec['date of birth'], '%d/%m/%Y')
email = inrec['email']
if not email:
email = inrec['parent/guardian1 email']
phone = inrec['mobile number']
if not phone:
phone = inrec['parent/guardian1 mobile number']
parent = inrec['parent/guardian1 first name'] + ' ' + \
inrec['parent/guardian1 last name']
regodt = to_datetime(inrec['registration date'], '%d/%m/%Y')
if refdt is not None and refdt < regodt:
new = '*'
else:
new = ''
orecs[name] = dict(
new=new,
name=name,
date_of_birth=date_of_birth,
parent=parent,
email=email,
phone=make_phone(phone),
prepaid=[],
paid=' ',
)
if len(orecs) == 0:
print('No CSV records in "{}"'.format(partfile), file=sys.stderr)
sys.exit(0)
with open(merchfile, 'r', newline='') as infile:
reader = DictReader(infile)
inrecs = []
for inrec in reader:
orderdt = to_datetime(inrec['Order Date'], '%d/%m/%Y')
name = inrec['First Name'] + ' ' + inrec['Last Name']
quantity = int(inrec['Quantity'])
sku = inrec['Merchandise SKU']
inrecs.append((orderdt, name, quantity, sku))
for data in sorted(inrecs, key=lambda t: t[0]):
orderdt, name, quantity, sku = data
if name not in orecs:
print('unknown participant {}!'.format(name), file=sys.stderr)
continue
if sku == 'FULLTERM':
if quantity != 1:
raise RuntimeError(
'quantity for FULLTERM is not 1 ({:d})'.format(
quantity))
quantity = len(config['dates'])
orecs[name]['paid'] = 'Full'
elif sku != 'SINGLE':
raise RuntimeError('Unknown SKU {}!'.format(sku))
if refdt is not None and refdt < orderdt:
val = 'new'
else:
val = 'old'
for _ in range(quantity):
orecs[name]['prepaid'].append(val)
if args.email:
emails = set() # using a set() will remove duplicates
for outrec in orecs.values():
emails.add(outrec['email'].strip().lower())
for email in sorted(emails):
print(email, file=sys.stderr)
if args.asxls:
from xlwt import Workbook
from xlwt.Style import easyxf
headings = [
'#',
'Paid',
'Parent/Guardian Contact Details',
'DoB/Mobile',
'Name',
]
headings.extend(config['dates'])
styles = {
'heading':
easyxf(
'font: name Arial, height 280, bold on; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'normal':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'right':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
'normal_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'right_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime_highlighted':
easyxf(
'font: name Arial, height 280, colour red; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
}
# Paid Parent/Guardian Contact Details Mobile Name
col1styles = {
'parent': 'normal',
'date_of_birth': 'date',
'name': 'normal',
}
col2styles = {
'email': 'normal',
'phone': 'centred',
}
book = Workbook()
sheet = book.add_sheet(config['label'])
r = 0
for c, v in enumerate(headings):
sheet.write(r, c, ensure_str(v), styles['heading'])
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
sheet.set_remove_splits(True)
ndates = len(config['dates'])
pnum = 0
for outrec in sorted(orecs.values(), key=lambda d: d['name'].lower()):
pnum += 1
r += 1
if outrec['new'] == '*':
normal_style = styles['normal_highlighted']
centred_style = styles['centred_highlighted']
right_style = styles['right_highlighted']
ssuf = '_highlighted'
else:
normal_style = styles['normal']
centred_style = styles['centred']
right_style = styles['right']
ssuf = ''
sheet.write(r, 0, str(pnum), right_style)
sheet.write(r, 1, outrec['paid'], centred_style)
for c, (k, s) in enumerate(col1styles.items()):
v = outrec[k]
s = col1styles[k]
sheet.write(r, 2 + c, v, styles[s + ssuf])
i = 0
for v in outrec['prepaid']:
if v == 'old':
ppstyle = styles['centred']
else:
ppstyle = styles['centred_highlighted']
sheet.write(r, 2 + c + 1 + i, 'PP', ppstyle)
i += 1
while i < ndates - 1:
sheet.write(r, 2 + c + 2 + i, ' ', centred_style)
i += 1
r += 1
sheet.write(r, 0, ' ', normal_style)
sheet.write(r, 1, ' ', normal_style)
for c, (k, s) in enumerate(col2styles.items()):
v = outrec[k]
s = col2styles[k]
sheet.write(r, 2 + c, v, styles[s + ssuf])
c += 1
sheet.write(r, 2 + c, ' ', normal_style)
c += 1
for i in range(len(config['dates'])):
sheet.write(r, 2 + c + i, ' ', normal_style)
book.save(sys.stdout.buffer)
if not args.notouch:
Path(reffile).touch()
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--csvfile',
default=None,
metavar='F',
help='csv file containing trybooking report')
parser.add_argument('--reffile',
default=None,
metavar='F',
help='file to use as reference for last run')
parser.add_argument('--refdt',
default=None,
metavar='D',
help='datetime to use as reference for last run')
parser.add_argument('--basename',
default='-',
metavar='S',
help='basename of output file (- = stdout)')
parser.add_argument('--ascsv',
action='store_true',
help='output csv data (no highlighting)')
parser.add_argument('--ashtml',
action='store_true',
help='output html data')
parser.add_argument('--asxls',
action='store_true',
help='output excel data')
parser.add_argument('--email',
action='store_true',
help='print a list of email addresses')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
if args.csvfile is not None:
csvfile = args.csvfile
reffile = args.reference
else:
def repkey(e):
_, m = e
dstr, suff = m.groups()
dt = datetime.strptime(dstr, '%d%m%Y')
if suff is not None:
dt += datetime.timedelta(seconds=int(suff[1:]))
return dt
rlist = sorted(
get_reports(None, clinicdir, r'^(\d{8})(-\d)?.csv$', args.verbose),
key=repkey,
)
try:
csvfile = os.path.join(clinicdir, rlist.pop()[0])
except IndexError:
raise RuntimeError('No Trybooking reports found!')
try:
reffile = os.path.join(clinicdir, rlist.pop()[0])
except IndexError:
reffile = None
if args.verbose:
print('[trybooking report file: {}]'.format(csvfile), file=sys.stderr)
if reffile is not None:
print('[reference datetime file: {}]'.format(reffile),
file=sys.stderr)
if args.refdt is not None:
refdt = dateutil_parse(args.refdt, dayfirst=True, fuzzy=True)
elif reffile is not None:
refdt = datetime.fromtimestamp(os.stat(reffile).st_mtime)
else:
refdt = None
if refdt is not None and args.verbose:
print('[reference datetime: {}]'.format(refdt), file=sys.stderr)
config = load_config(prefix=clinicdir)
with open(csvfile, 'r', newline='') as infile:
_ = infile.read(1)
reader = DictReader(infile)
orecs = []
for inrec in reader:
if to_bool(inrec['Void']):
if args.verbose:
print('ignore VOID record: {}'.format(inrec),
file=sys.stderr)
continue
school_term = inrec['Ticket Data: School Term']
if school_term != config['label']:
raise RuntimeError('School Term mismatch! ({}!={})'.format(
school_term, clinicterm))
name = inrec['Ticket Data: Player\'s First Name'] + ' ' + \
inrec['Ticket Data: Player\'s Surname']
date_of_birth = to_date(
inrec['Ticket Data: Player\'s Date-of-Birth'], '%Y-%m-%d')
paid = float(inrec['Net Booking'])
medical = inrec[
'Ticket Data: Special Requirements/Medical Conditions'].strip(
)
isparent = to_bool(
inrec['Ticket Data: Is Purchaser the child\'s Parent/Guardian']
)
if isparent:
parent_data = booking_data(inrec)
else:
parent_data = ticket_data(inrec)
if not any(parent_data):
# they answered No to isparent, but did not fill in
# parent ticket data - use the booking data instead ...
parent_data = booking_data(inrec)
print('empty ticket data - using booking data ({})'.format(
parent_data),
file=sys.stderr)
parent, address, phone, email = parent_data
# "27Apr21","1:58:48 PM"
dbdt = to_datetime(inrec['Date Booked (UTC+10)'], '%d%b%y')
tbt = to_time(inrec['Time Booked'], '%I:%M:%S %p')
booked = dbdt + timedelta(
hours=tbt.hour, minutes=tbt.minute, seconds=tbt.second)
if refdt is None or refdt < booked:
new = '*'
else:
new = ''
orecs.append(
OrderedDict(
new=new,
paid=paid,
name=name,
date_of_birth=date_of_birth,
parent=parent,
email=email,
phone=make_phone(phone),
address=address.title().replace('\n', ', '),
medical=medical,
booked=booked,
))
if args.email:
emails = set()
for outrec in orecs:
emails.add(outrec['email'].strip().lower())
for email in sorted(emails):
print(email)
if len(orecs) == 0:
print('No CSV records in "{}"'.format(csvfile))
sys.exit(0)
if args.ascsv:
from csv import DictWriter
with TextIOWrapper(sys.stdout.buffer, newline='') as outfile:
writer = DictWriter(outfile, fieldnames=orecs[0].keys())
writer.writeheader()
for outrec in orecs:
writer.writerow(outrec)
if args.ashtml:
raise NotImplementedError('html output not implemented!')
if args.asxls:
from xlwt import Workbook
from xlwt.Style import easyxf
styles = {
'heading':
easyxf(
'font: name Arial, height 280, bold on; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'normal':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime':
easyxf(
'font: name Arial, height 280; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
'normal_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal left; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'centred_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='@',
),
'currency_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal right; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='$#,##0.00',
),
'date_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD',
),
'datetime_highlighted':
easyxf(
'font: name Arial, height 280; '
'pattern: pattern solid, back_colour light_yellow; '
'align: wrap off, vertical centre, horizontal centre; '
'borders: left thin, right thin, top thin, bottom thin',
num_format_str='YYYY-MM-DD HH:MM:SS AM/PM',
),
}
colstyles = {
'new': 'centred',
'paid': 'currency',
'name': 'normal',
'date_of_birth': 'date',
'parent': 'normal',
'email': 'normal',
'phone': 'centred',
'address': 'normal',
'medical': 'normal',
'booked': 'datetime',
}
book = Workbook()
sheet = book.add_sheet(config['label'])
r = 0
for c, v in enumerate(orecs[0].keys()):
sheet.write(r, c, ensure_str(v), styles['heading'])
sheet.set_panes_frozen(True)
sheet.set_horz_split_pos(1)
sheet.set_remove_splits(True)
for outrec in orecs:
r += 1
is_new = outrec['new'] == '*'
for c, (k, v) in enumerate(outrec.items()):
if k == 'address':
v = v.replace('\n', ', ')
s = colstyles[k]
if is_new:
s += '_highlighted'
sheet.write(r, c, v, styles[s])
book.save(sys.stdout.buffer)
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--details',
'-d',
action='store_true',
help='print player details')
parser.add_argument('--report',
default=None,
metavar='F',
help='specify participant report file to use')
parser.add_argument('--square',
default=None,
metavar='F',
help='write csv upload file of Square customers')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
config = load_config(verbose=args.verbose)
roles = fetch_program_participants(args.report, args.verbose)
if args.square is not None:
fieldnames = [
'First Name',
'Surname',
'Company Name',
'Email Address',
'Phone Number',
'Street Address 1',
'Street Address 2',
'City',
'State',
'Postal Code',
'Reference ID',
'Birthday',
'Email Subscription Status',
]
with open(args.square, 'w', newline='') as outfile:
writer = DictWriter(outfile, fieldnames=fieldnames)
writer.writeheader()
for player in roles['Player']:
if player['season'] != config['label']:
if args.verbose:
print('ignore out-of-season player: {}'.format(player))
continue
outrec = dict(map(lambda k: (k, None), fieldnames))
outrec['First Name'] = player['first name']
outrec['Surname'] = player['last name']
outrec['Email Address'] = first_not_empty(
player['email'],
player['parent/guardian1 email'],
player['parent/guardian2 email'],
)
outrec['Phone Number'] = first_not_empty(
player['mobile number'],
player['parent/guardian1 mobile number'],
player['parent/guardian2 mobile number'],
)
outrec['Street Address 1'] = player['address']
outrec['City'] = player['suburb/town']
outrec['State'] = player['state/province/region']
outrec['Postal Code'] = player['postcode']
outrec['Reference ID'] = player['profile id']
outrec['Birthday'] = \
to_date(player['date of birth']).strftime('%Y-%m-%d')
outrec['Email Subscription Status'] = \
'subscribed' if to_bool(player['opted-in to marketing']) \
else 'unsubscribed'
writer.writerow(outrec)
return 0
def main():
parser = ArgumentParser(formatter_class=ArgumentDefaultsHelpFormatter)
parser.add_argument('--auth',
'-a',
action='store_true',
help='authenticate to the email server')
parser.add_argument('--coaches',
'-c',
action='store_true',
help='send to coaches instead of team managers')
parser.add_argument('--details',
'-d',
action='store_true',
help='include coach and player details')
parser.add_argument('--nocoach',
'-N',
action='store_true',
help='do not include coach details')
parser.add_argument('--dryrun',
'-n',
action='store_true',
help='dont actually send email')
parser.add_argument('--pause',
'-p',
action='store_true',
help='pause for 5 secs between messages')
parser.add_argument('--partreport',
default=None,
metavar='F',
help='specify participants report file to use')
parser.add_argument('--testing',
'-t',
action='store_true',
help='just send one test email to a test address')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
parser.add_argument('--writefiles',
'-w',
action='store_true',
help='write to files instead of sending email')
parser.add_argument('--tbreport',
default=None,
metavar='F',
help='specify trybooking report file to use')
parser.add_argument('--trybooking',
'-T',
action='store_true',
help='check trybooking payment and include in details')
parser.add_argument('--prepend',
'-P',
default=None,
metavar='F',
help='specify file to prepend to html body')
parser.add_argument('--append',
'-A',
default=None,
metavar='F',
help='specify file to append to html body')
parser.add_argument('--relay',
'-R',
default='smtp-relay.gmail.com:587',
metavar='H[:P]',
help='specify relay host and port')
parser.add_argument('--fqdn',
'-F',
default=None,
metavar='H',
help='specify the fqdn for the EHLO request')
args = parser.parse_args()
config = load_config()
if ':' in args.relay:
smtp_host, smtp_port = args.relay.split(':')
else:
smtp_host = args.relay
smtp_port = 587
if args.auth:
if 'email_auth' in config:
smtp_user, smtp_pass = config['email_auth']
else:
smtp_user = input('SMTP User: '******'SMTP Password: '******'email_fqdn' in config:
smtp_fqdn = config['email_fqdn']
if args.writefiles:
smtp_class = SMTP_dummy
else:
smtp_class = SMTP
admin_email = '*****@*****.**'
testing_email = '*****@*****.**'
subject_fmt = 'Registration info for {}'
season_label = config.get('season', season.replace('-', ' ').title())
body_fmt = '''\
<html>
<head>
<style>
.pt {}
border: 1px solid black;
margin-top: 1em;
{}
</style>
</head>
<body>
<p><i>[automated email - send queries to: {}]</i></p>
{}\
<table>
<tr>
<td>Season:</td><td> </td><td><b><tt>''' \
+ season_label + '''</tt></b></td>
</tr>
<tr>
<td></td><td></td><td></td>
</tr>
<tr>
<td>Team Name:</td><td> </td><td><tt>{}</tt></td>
</tr>
<tr>
<td>EDJBA Name:</td><td> </td><td><tt>{}</tt></td>
</tr>
<tr>
<td>EDJBA Code:</td><td> </td><td><tt>{}</tt></td>
</tr>
{}{}{}\
<tr>
<td>Rego Link:</td><td> </td><td><a href="{}"><tt>{}</tt></a></td>
</tr>
</table>
{}{}\
<body>
</html>'''
teams = fetch_teams()
if args.details:
player_keys = [
('last name', 'surname'),
('first name', 'firstname'),
('date of birth', 'd.o.b'),
('parent/guardian1 first name', 'parent firstname'),
('parent/guardian1 last name', 'parent surname'),
('parent/guardian1 mobile number', 'parent mobile'),
('parent/guardian1 email', 'parent email'),
]
fetch_participants(teams, args.partreport, args.verbose)
if args.trybooking:
tbmap = config['tbmap']
tb = fetch_trybooking(tbmap, args.tbreport, args.verbose)
if len(tb) == 0:
raise RuntimeError('no trybooking data in {}'.format(
args.tbreport))
# used this:
# https://stackoverflow.com/a/60301124
with smtp_class(smtp_host, int(smtp_port), smtp_fqdn) as smtp:
# smtp.set_debuglevel(99)
if smtp_class is SMTP:
smtp.starttls(context=create_default_context(
purpose=Purpose.CLIENT_AUTH))
if args.auth:
smtp.login(smtp_user, smtp_pass)
for t in teams.values():
print('{} [{}, {}]:'.format(t.name, t.edjba_id, t.edjba_code),
file=sys.stderr)
recips = []
if args.coaches:
if t.co_email:
recips.append(t.co_email)
if t.ac_email:
recips.append(t.ac_email)
else:
recips.append(t.tm_email)
if not recips:
print('\tSKIPPING (no recipients).', file=sys.stderr)
continue
prepend_html = []
if args.prepend:
with open(args.prepend, 'r') as fd:
for line in fd.read().splitlines():
prepend_html.append('{}\n'.format(line))
pt = [] # player table
if args.details:
pt.append(' <table class="pt">\n')
pt.append(' <thead>\n')
pt.append(' <tr>\n')
pt.append(' <th class="pt">#</th>\n')
for _, h in player_keys:
pt.append(' <th class="pt">{}</th>\n'.format(
nesc(h.title())))
if args.trybooking:
pt.append(' <th class="pt">{}</th>\n'.format(
nesc('Ticket#')))
pt.append(' </tr>\n')
pt.append(' </thead>\n')
pt.append(' <tbody>\n')
for n, p in enumerate(t.players):
pt.append(' <tr>\n')
pt.append(' <td class="pt">{}</td>\n'.format(n + 1))
for k, _ in player_keys:
pt.append(' <td class="pt">{}</td>\n'.format(
nesc(p[k])))
if args.trybooking:
e = find_in_tb(
tb, to_fullname(p['first name'], p['last name']))
pt.append(' <td class="pt">{}</td>\n'.format(
'unpaid' if e is None else nesc(e['Ticket Number']
)))
pt.append(' </tr>\n')
pt.append(' </tbody>\n')
pt.append(' </table>\n')
append_html = []
if args.append:
with open(args.append, 'r') as fd:
for line in fd.read().splitlines():
append_html.append('{}\n'.format(line))
msg = MIMEText(
body_fmt.format(
'{',
'}',
nesc(admin_email),
''.join(prepend_html),
nesc(t.name),
nesc(t.edjba_id),
nesc(t.edjba_code),
person_tr('Team Manager', t.tm_name, t.tm_email,
t.tm_mobile),
person_tr('Coach', t.co_name, t.co_email, t.co_mobile)
if args.details and not args.nocoach else '',
person_tr('Asst Coach', t.ac_name, t.ac_email, t.ac_mobile)
if args.details and not args.nocoach else '',
nesc(t.regurl),
nesc(t.regurl),
''.join(pt),
''.join(append_html),
),
'html',
)
msg['From'] = admin_email
msg['To'] = ', '.join(recips)
msg['Cc'] = admin_email
msg['Subject'] = subject_fmt.format(t.name)
msg['Return-Path'] = admin_email
print('\tsending to {}...'.format(recips), end='', file=sys.stderr)
if args.testing:
recips = [testing_email]
else:
recips.append(admin_email)
try:
if args.dryrun:
print('\n*****|{}|{}|\n{}'.format(admin_email, recips,
msg.as_string()),
file=sys.stderr)
else:
smtp.sendmail(admin_email, recips, msg.as_string())
print('done.', file=sys.stderr)
except KeyboardInterrupt:
print('\nInterrupted!', file=sys.stderr)
sys.exit(0)
except SMTPException as e:
if hasattr(e, 'smtp_error'):
m = e.smtp_error
else:
m = repr(e)
print('exception - {}'.format(m), file=sys.stderr)
if args.testing:
break
if args.pause:
sleep(5)
if args.details and args.trybooking:
if tb['by-name']:
print('{} trybooking tickets unmatched'.format(len(tb['by-name'])),
file=sys.stderr)
for name, elist in tb['by-name'].items():
print('\t{} [{}]'.format(
name, ','.join(e['Ticket Number'] for e in elist)),
file=sys.stderr)
if tb['by-tnum']:
print('{} trybooking tickets unused'.format(len(tb['by-tnum'])),
file=sys.stderr)
for tnum, elist in tb['by-tnum'].items():
if len(elist) != 1:
raise RuntimeError('huh? (1)')
entry = elist[0]
if entry['Ticket Number'] != tnum:
raise RuntimeError('huh? (2)')
name = to_fullname(
entry['Ticket Data: Player First Name'],
entry['Ticket Data: Player Family Name'],
)
print('\t{} [{}]'.format(name, tnum), file=sys.stderr)
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--report',
'-r',
default=None,
metavar='FILE',
help='specify report file to use')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
parser.add_argument('--younger',
'-y',
action='store_true',
help='print players younger than their age group')
args = parser.parse_args()
config = load_config()
teams = fetch_teams()
fetch_participants(teams, args.report, args.verbose)
def pcmp(what, sname, code, p, name, email, mobile, wwcnum, wwcexp):
pname = p['first name'] + ' ' + p['last name']
if name is None:
print('***** {:12} [{}] : {} name is None! ({})'.format(
sname, code, what, pname))
elif name.strip().lower() != pname.strip().lower():
print('***** {:12} [{}] : {} name mismatch! ({}!={})'.format(
sname, code, what, name, pname))
if email is None:
print('***** {:12} [{}] : [{}] : {} email is None! ({})'.format(
sname, code, pname, what, p['email']))
elif email.strip().lower() != p['email'].strip().lower():
print(
'***** {:12} [{}] : [{}] : {} email mismatch! ({}!={})'.format(
sname, code, pname, what, email, p['email']))
if mobile is None:
print('***** {:12} [{}] : [{}] : {} mobile is None! ({})'.format(
sname, code, pname, what, p['mobile number']))
else:
pmobile = p['mobile number'].strip().lower()
if not pmobile.startswith('0'):
pmobile = '0' + pmobile
if mobile.strip().lower() != pmobile:
print('***** {:12} [{}] : [{}] : {} mobile mismatch! '
'({}!={})'.format(sname, code, pname, what, mobile,
pmobile))
if wwcnum is None:
print('***** {:12} [{}] : [{}] : {} wwcnum is None! ({})'.format(
sname, code, pname, what, p['wwc number']))
else:
pwwcnum = p['wwc number'].strip().lower()
if pwwcnum.startswith('vit '):
if len(pwwcnum) == 11 and pwwcnum[8] == '-':
pwwcnum = pwwcnum[:8] + pwwcnum[9:]
else:
if len(pwwcnum) == 8:
pwwcnum += '-01'
elif (len(pwwcnum) == 10 and pwwcnum[8].isdigit()
and pwwcnum[9].isdigit()):
pwwcnum = pwwcnum[:8] + '-' + pwwcnum[8:]
if wwcnum.strip().lower() != pwwcnum:
print('***** {:12} [{}] : [{}] : {} wwc number mismatch! '
'({}!={})'.format(sname, code, pname, what, wwcnum,
pwwcnum))
if wwcexp is None:
print('***** {:12} [{}] : [{}] : {} wwcexp is None! ({})'.format(
sname, code, pname, what, p['wwc expiry date']))
else:
pwwcexp = p['wwc expiry date'].strip().lower()
if not pwwcexp.endswith(' 00:00:00.000'):
pwwcexp += ' 00:00:00.000'
if wwcexp.strip().lower() != pwwcexp:
print('***** {:12} [{}] : [{}] : {} wwcexp mismatch! '
'({}!={})'.format(sname, code, pname, what, wwcexp,
pwwcexp))
for t in teams.values():
ag_start_s, ag_end_s = config['age_groups']['U{:d}'.format(
t.age_group)]
ag_start = to_date(ag_start_s)
ag_end = to_date(ag_end_s)
for p in t.players:
dob = to_date(p['date of birth'])
if dob < ag_start or (args.younger and ag_end and dob > ag_end):
print('***** {:12} [{}] : {} - {}'.format(
t.sname,
t.edjba_code,
to_fullname(p['first name'], p['last name']),
p['date of birth'],
))
# print('S={},E={},D={}'.format(ag_start, ag_end, dob))
if len(t.managers) == 0:
print('***** {:12} [{}] : No Team Managers!'.format(
t.sname, t.edjba_code))
else:
pcmp('Team Manager', t.sname, t.edjba_code, t.managers[0],
t.tm_name, t.tm_email, t.tm_mobile, t.tm_wwcnum, t.tm_wwcexp)
if len(t.managers) > 1:
print('***** {:12} [{}] : more than 1 team manager!'.format(
t.sname, t.edjba_code))
n = 2
for m in t.managers[1:]:
print('***** {:12} [{}] : T/M{} = {}'.format(n, m))
n += 1
if len(t.coaches) == 0:
print('***** {:12} [{}] : No Coaches!'.format(
t.sname, t.edjba_code))
else:
pcmp('Coach', t.sname, t.edjba_code, t.coaches[0], t.co_name,
t.co_email, t.co_mobile, t.co_wwcnum, t.co_wwcexp)
if len(t.coaches) > 1:
pcmp('Assistant Coach', t.sname, t.edjba_code, t.coaches[1],
t.ac_name, t.ac_email, t.ac_mobile, t.ac_wwcnum,
t.ac_wwcexp)
if len(t.coaches) > 2:
print('***** {:12} [{}] : more than 2 coaches!'.format(
t.sname, t.edjba_code))
n = 3
for m in t.managers[1:]:
print('***** {:12} [{}] : Coach{} = {}'.format(n, m))
n += 1
return 0
def main():
parser = ArgumentParser()
parser.add_argument('--reportdir',
default='reports',
metavar='D',
help='directory containing report files')
parser.add_argument(
'--xactfile',
default=None,
metavar='F',
help='csv file containing financial transaction report')
parser.add_argument('--xerofile',
default=None,
metavar='F',
help='output csv file for xero pre-coded transactions')
parser.add_argument('--pupdbfile',
default=None,
metavar='F',
help='json file for pupdb key-value store')
parser.add_argument('--dryrun',
'-n',
action='store_true',
help='dont make any actual changes - just run through')
parser.add_argument('--verbose',
'-v',
action='store_true',
help='print verbose messages')
args = parser.parse_args()
reportdir = args.reportdir
if not os.path.isdir(reportdir):
reportdir = os.path.join(xerodir, args.reportdir)
if not os.path.isdir(reportdir):
raise RuntimeError('cannot locate reports directory!')
if args.verbose:
print('[reports found in directory {} (realpath={})]'.format(
reportdir, os.path.realpath(reportdir)),
file=sys.stderr)
xactfile = args.xactfile
if xactfile is None:
xactfile, _ = latest_report(
'transactions', reportdir, r'^transactions_(\d{8})\.csv$',
lambda m: datetime.strptime(m.group(1), '%Y%m%d'), args.verbose)
if xactfile is None:
raise RuntimeError('cannot locate transaction file!')
if args.verbose:
print('[transaction report file = {} (realpath={})]'.format(
xactfile, os.path.realpath(xactfile)),
file=sys.stderr)
xerofile = args.xerofile
if xerofile is None:
xerofile = os.path.join(
xerodir,
datetime.now().strftime('xero-upload-%Y%m%d.csv'))
if args.verbose:
print('[Xero output csv file = {} (realpath={})]'.format(
xerofile, os.path.realpath(xerofile)),
file=sys.stderr)
pupdbfile = args.pupdbfile
if pupdbfile is None:
pupdbfile = os.path.join(xerodir, 'uploaded.json')
if args.verbose:
print('[pupdb json file = {} (realpath={})]'.format(
pupdbfile, os.path.realpath(pupdbfile)),
file=sys.stderr)
config = load_config(prefix=xerodir)
db = PupDB(pupdbfile)
fieldnames = [
'Date',
'Amount',
'Payee',
'Description',
'Reference',
'Cheque Number',
'Account code',
'Tax Rate (Display Name)',
'Tracking1',
'Tracking2',
'Transaction Type',
'Analysis code',
]
voucher_desc = 'Get Active Kids Voucher Program'
with open(xactfile, 'r', newline='') as infile:
reader = DictReader(infile)
output_records = {}
already_uploaded = {}
order_numbers = []
order_item_ids = []
total_netamount = Decimal('0.00')
total_phqfee = Decimal('0.00')
total_subtotal = Decimal('0.00')
total_pending = Decimal('0.00')
total_gvapplied = Decimal('0.00')
for inrec in reader:
org = inrec['Organisation']
role = inrec['Role']
org_to = inrec['Organisation Registering To']
pstatus = inrec['Payout Status']
netamount = Decimal(inrec['Net Amount'][1:])
if (org != 'Shooters Basketball Club' or role != 'Player'
or org_to != 'Shooters Basketball Club'
or pstatus != 'DISBURSED'):
if args.verbose:
print('skip (bad rec): org={}, role={}, org_to={}, '
'pstatus={}'.format(org, role, org_to, pstatus),
file=sys.stderr)
if pstatus == 'DISBURSEMENT_PENDING':
total_pending += netamount
continue
rtype = inrec['Type of Registration']
rname = inrec['Registration']
ptype = inrec['Product Type']
for rdesc in config['types']:
if (rdesc['rtype'] == rtype and rdesc['rname'] == rname
and rdesc['ptype'] == ptype):
break
else:
raise RuntimeError(
'type not found: rtype={}, rname={}, ptype={}'.format(
rtype, rname, ptype))
dfmt = '%d/%m/%Y'
sname = inrec['Season Name']
xdate = to_date(inrec['Date'], dfmt)
name = inrec['Name']
onum = inrec['Order Number']
oid = inrec['Order Item ID']
oprice = Decimal(inrec['Order Item Price'][1:])
gvname = inrec['Government Voucher Name']
if gvname != '':
if gvname != 'Get Active Kids':
raise RuntimeError('bad gov voucher: {}'.format(gvname))
sgva = inrec['Government Voucher Amount']
gvamount = Decimal(sgva[1:])
if gvamount != Decimal('200.00'):
raise RuntimeError(
'GAK voucher not $200: {:.2f}'.format(gvamount))
sgvaa = inrec['Government Voucher Amount Applied']
gvapplied = Decimal(sgvaa[1:])
else:
gvamount = Decimal('0.00')
gvapplied = Decimal('0.00')
product = inrec['Product Name']
quantity = int(inrec['Quantity'])
subtotal = Decimal(inrec['Subtotal'][1:])
phqfee = Decimal(inrec['PlayHQ Fee'][1:])
pdate = to_date(inrec['Payout Date'], '%Y-%m-%d')
pid = inrec['Payout ID']
if rdesc['rid'] == 'clinic':
sku = inrec['Merchandise SKU']
if sku not in rdesc['skus']:
raise RuntimeError('sku not found: {} not in {}'.format(
sku, rdesc['skus']))
# Term N, YYYY
m = re.match(r'^Term ([1-4]), (\d{4})$', sname)
if m is None:
raise RuntimeError(
'clinic record has bad season name ({})'.format(sname))
tracking1 = rdesc['tracking1'].format(*m.groups())
tracking2 = rdesc['tracking2']
elif rdesc['rid'] == 'registration':
feename = inrec['Fee Name']
for fdesc in rdesc['fees']:
if fdesc['name'] == feename:
famount = Decimal(fdesc['amount'])
break
else:
raise RuntimeError(
'fee not found: rtype={}, rname={}, ptype={}'.format(
rtype, rname, ptype))
if quantity != 1:
raise RuntimeError('registration with quantity != 1!')
if famount != oprice:
raise RuntimeError(
'fee amount mismatch ({:.2f}!={:.2f})'.format(
famount, oprice))
# (Winter|Summer) YYYY
m = re.match(r'^(Winter|Summer) (\d{4})$', sname)
if m is None:
raise RuntimeError(
'rego record has bad season name ({})'.format(sname))
wors, year = m.groups()
if wors == 'Summer':
year = '{}/{:02d}'.format(year, int(year) - 2000 + 1)
tracking1 = rdesc['tracking1'].format(year, wors)
tracking2 = rdesc['tracking2']
else:
raise RuntimeError('bad rego id {}!'.format(rdesc['rid']))
if (oprice - gvapplied) * quantity != subtotal:
raise RuntimeError(
'oprice({:.2f})*quantity({})!=subtotal({:.2f})'.format(
oprice, quantity, subtotal))
if subtotal != netamount + phqfee:
raise RuntimeError(
'subtotal({:.2f})!=netamount({:.2f})+phqfee({:.2f})'.
format(
subtotal,
netamount,
phqfee,
))
if onum in order_numbers:
raise RuntimeError('duplicate order number {}!'.format(onum))
order_numbers.append(onum)
if oid in order_item_ids:
raise RuntimeError('duplicate order item id {}!'.format(oid))
order_item_ids.append(oid)
if db.get(pid) is not None:
is_already_uploaded = True
if pid not in already_uploaded:
already_uploaded[pid] = []
if args.verbose:
print('already uploaded: name={}, pdate={}, pid={}'.format(
name, pdate, pid),
file=sys.stderr)
else:
is_already_uploaded = False
if pid not in output_records:
output_records[pid] = []
total_netamount += netamount
total_phqfee += phqfee
total_subtotal += subtotal
total_gvapplied += gvapplied
desc = '{} - ${:.2f} x {:d}'.format(product, oprice, quantity)
orec = {
'Date': xdate.strftime(dfmt),
'Amount': '{:.2f}'.format(subtotal),
'Payee': name,
'Description': '{}: subtotal'.format(desc),
'Reference': '{} on {}'.format(pid, pdate.strftime(dfmt)),
'Cheque Number': onum,
'Account code': config['sales_account'],
'Tax Rate (Display Name)': config['taxrate'],
'Tracking1': tracking1,
'Tracking2': tracking2,
'Transaction Type': 'credit',
'Analysis code': oid,
}
if is_already_uploaded:
already_uploaded[pid].append(orec)
else:
output_records[pid].append(orec)
if phqfee != Decimal('0.00'):
orec = {
'Date': xdate.strftime(dfmt),
'Amount': '-{:.2f}'.format(phqfee),
'Payee': name,
'Description': '{}: playhq fees'.format(desc),
'Reference': '{} on {}'.format(pid, pdate.strftime(dfmt)),
'Cheque Number': onum,
'Account code': config['fees_account'],
'Tax Rate (Display Name)': config['taxrate'],
'Tracking1': tracking1,
'Tracking2': tracking2,
'Transaction Type': 'debit',
'Analysis code': oid,
}
if is_already_uploaded:
already_uploaded[pid].append(orec)
else:
output_records[pid].append(orec)
if gvapplied != Decimal('0.00'):
orec = {
'Date': xdate.strftime(dfmt),
'Amount': '{:.2f}'.format(gvapplied),
'Payee': voucher_desc,
'Description': '{}: get active for {}'.format(desc, name),
'Reference': '{} on {}'.format(pid, pdate.strftime(dfmt)),
'Cheque Number': onum,
'Account code': config['other_revenue_account'],
'Tax Rate (Display Name)': config['taxrate'],
'Tracking1': tracking1,
'Tracking2': tracking2,
'Transaction Type': 'credit',
'Analysis code': oid,
}
if is_already_uploaded:
already_uploaded[pid].append(orec)
else:
output_records[pid].append(orec)
for pid, oreclist1 in already_uploaded.items():
total_amount1 = sum(
Decimal(orec['Amount']) for orec in oreclist1
if orec['Payee'] != voucher_desc)
dbval = db.get(pid)
if dbval is None:
raise RuntimeError('db get of pid {} failed!'.format(pid))
oreclist2 = loads(dbval)
if not isinstance(oreclist2, list):
if args.verbose:
print('cannot check old record: pid={}, amount=${:.2f}'.format(
pid, total_amount1),
file=sys.stderr)
continue
total_amount2 = sum(
Decimal(orec['Amount']) for orec in oreclist2
if orec['Payee'] != voucher_desc)
if total_amount1 != total_amount2:
raise RuntimeError(
'pid {} total amount mismatch (${:.2f} != ${:.2f})!'.format(
pid, total_amount1, total_amount2))
if args.verbose:
print('checked already uploaded: pid={}, amount=${:.2f}'.format(
pid, total_amount1),
file=sys.stderr)
if args.verbose and total_pending > 0:
print('total pending = ${:.2f}'.format(total_pending), file=sys.stderr)
if len(output_records) == 0:
print('No records were collected.', file=sys.stderr)
return 0
if total_subtotal - total_phqfee != total_netamount:
raise RuntimeError('total({:.2f})-fees({:.2f})!=net({:.2f})'.format(
total_subtotal, total_phqfee, total_netamount))
if args.verbose:
print('{} payment ids were collected.'.format(len(output_records)),
file=sys.stderr)
print('subtotal = ${:.2f}'.format(total_subtotal), file=sys.stderr)
print('phqfee = ${:.2f}'.format(total_phqfee), file=sys.stderr)
print('netamount = ${:.2f}'.format(total_netamount), file=sys.stderr)
print('gov vouchers = ${:.2f}'.format(total_gvapplied),
file=sys.stderr)
for pid, oreclist in output_records.items():
amount = Decimal(0.0)
for outrec in oreclist:
if outrec['Payee'] != voucher_desc:
amount += Decimal(outrec['Amount'])
print(' Payment Id {} = ${:.2f}'.format(pid, amount),
file=sys.stderr)
if args.dryrun:
return 0
if os.path.exists(xerofile):
raise RuntimeError('will not overwrite file {}'.format(xerofile))
with open(xerofile, 'w', newline='') as outfile:
writer = DictWriter(outfile, fieldnames=fieldnames)
writer.writeheader()
for pid, oreclist in output_records.items():
for outrec in oreclist:
writer.writerow(outrec)
for pid, oreclist in output_records.items():
db.set(pid, dumps(oreclist))
return 0