def main(): parser = ArgumentParser() parser.add_argument('--verbose', '-v', action='store_true', help='print verbose messages') parser.add_argument('teams', nargs='*', help='limit checks to specified teams') args = parser.parse_args() teams = fetch_teams() if args.teams: newteams = {} for name in args.teams: if name not in teams: print('unknown team name: {}'.format(name), file=sys.stderr) sys.exit(1) newteams[name] = teams[name] teams = newteams for t in teams.values(): if args.verbose: print('{} [{}]:'.format(t.sname, t.edjba_id)) for label, attrs in descs: ident, name, dobstr, wwcnum, wwcname = map( lambda a: getattr(t, a), attrs ) dob = to_date(dobstr, '%Y-%m-%d %H:%M:%S.%f') res = wwc_check( WWCCheckPerson(ident, name, wwcnum, dob, wwcname), args.verbose ) # result is a WWCCheckStatus (IntEnum) and can be one of: # NONE, UNKNOWN, EMPTY, UNDER18, TEACHER, BADNUMBER, # FAILED, SUCCESS, EXPIRED, INVALID, BADRESPONSE if args.verbose: print('\t{}: {}\t\t- {}'.format(label, name, res)) if res.status not in ( WWCCheckStatus.NONE, WWCCheckStatus.UNKNOWN, WWCCheckStatus.SUCCESS, WWCCheckStatus.UNDER18, WWCCheckStatus.TEACHER, ): print( '{} [{}]: {} ({}): {}'.format( t.sname, t.edjba_id, name, label, res ), file=sys.stderr ) 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() 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