Esempio n. 1
0
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
Esempio n. 2
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
Esempio n. 3
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
Esempio n. 6
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>&nbsp;</td><td><b><tt>''' \
        + season_label + '''</tt></b></td>
   </tr>
   <tr>
    <td></td><td></td><td></td>
   </tr>
   <tr>
    <td>Team Name:</td><td>&nbsp;</td><td><tt>{}</tt></td>
   </tr>
   <tr>
    <td>EDJBA Name:</td><td>&nbsp;</td><td><tt>{}</tt></td>
   </tr>
   <tr>
    <td>EDJBA Code:</td><td>&nbsp;</td><td><tt>{}</tt></td>
   </tr>
{}{}{}\
   <tr>
     <td>Rego Link:</td><td>&nbsp;</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
Esempio n. 8
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