#! python3 #wood_badge_reports.py #A tool to process Compass Records and produce something human readable #Licence: GPL-3.0-or-later #Written by Stuart Griffiths, stuart.griffiths@birminghamscouts.org.uk #Started 31/07/2022 #Version:0.1 #Released: 31/07/2022 #Status: Working #Inputs: CSV #Outputs: multiple CSV #Next Steps: #Issues: #Background IP: https://automatetheboringstuff.com/, Chapter 16 #Notes - CSV Column Numbers #Membership_Number = 0 #Surname = 2 #Known_As = 3 #Email = 4 #Member_Role = 5 #Roll_Status = 6 #Roll_Start_Date = 7 #Review_Date = 8 #Wood_badge_received_date = 9 #District = 12 #Scout_Group = 14 #Training_Module = 16 #Module_Validated_On = 17 #Module_Renewal_Date = 20 #Libraries import os #Enables file IO from pathlib import Path #to work out current working directory import csv #required to handle csv files import datetime #required to deal with dates for due items from dateutil.relativedelta import relativedelta #required to calculate 6 months later #1. Set up Output files #CSV woodbadge_file = open('woodbadge_report.csv', 'w', newline='') woodbadge = csv.writer(woodbadge_file) woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) #text files woodbadge_statistics_file = open('_woodbadge_statistics.txt', 'w') #Modules def training_report(): #2. Open the file file_name = str(Path.cwd()) + '/' + str('CountyTrainingv1.csv') input_file = open(file_name) #assumes file is in working directory #Create a list using the CSV file input_data_reader = csv.reader(input_file) input_data = list(input_data_reader) #Set up lists total_woodbadge_list = [] county_woodbadge_list = [] CVS_woodbadge_list = [] rea_woodbadge_list = [] spitfire_woodbadge_list = [] SCE_woodbadge_list = [] SCW_woodbadge_list = [] tame_woodbadge_list = [] role_woodbadge = ["District Explorer Scout Commissioner", "Assistant Group Scout Leader", "Group Scout Leader", "Section Leader - Beaver Scouts", "Section Leader - Cub Scouts", "Section Leader - Scouts", "Section Leader - Squirrel Scouts", "District Section Leader - Explorer Scouts", "Section Leader - Explorer Scouts", "Assistant Section Leader - Beaver Scouts", "Assistant Section Leader - Cub Scouts", "Assistant Section Leader - Scouts", "District Assitant Section Leader - Explorer Scouts", "Assitant Section Leader - Explorer Scouts", "Assitant Section Leader - Squirrel Scouts", "District Deputy Commissioner", "District Commissioner", "County Deputy Commissioner", "County Commissioner", "District Section Leader - Explorer Scouts (Yng Leader)", "District Assistant Commissioner - Special Needs", "Assistant Section Leader", "District Asst Commiss (Section) - Beaver Scouts", "District Deputy Commissioner", "Assistant County Commissioner International", "Assistant District Commissioner (Section Support) - Beaver Scouts", "Assistant District Commissioner (Section Support) - Cub Scouts", "Assistant District Commissioner (Section Support) - Squirrel Scouts", "Assistant District Commissioner (Section)", "Assistant District Commissioner (Section) - Beaver Scouts", "Assistant District Commissioner (Section) - Scouts", "Section Assistant - Cub Scouts, Beaver Scouts", "District Section Leader", "District Section Leader - Squirrel Scouts", "District Section Leader - Beaver Scouts", "District Section Leader - Cub Scouts", "District Section Leader - Scouts", "District Leader", "District Deputy Commissioner - Adult Support", "District Commissioner", "District Asst Commissioner (Section) - Cub Scouts", "District Asst Commiss (Section) - Beaver Scouts", "District Assistant Commissioner (Section) - Scouts", "District Assistant Commissioner - Support", "District Assistant Commissioner - Special Needs", "District Assistant Commissioner - Special Needs", "District Assistant Commissioner", "Deputy Group Scout Leader", "County Scouter - Scouts", "County Scouter - Explorer Scouts", "County Scouter", "County Leader - Squirrel Scouts", "County Deputy Commissioner - Programme", "County Deputy Commissioner - Adult Support", "County Deputy Commissioner", "County Commissioner", "County Assistant Commissioner International", "County Assistant Commissioner (Section) - Scouts", "County Assistant Commissioner (Section) - Scout Network", "County Assistant Commissioner (Section) - Explorer Scouts", "County Assistant Commissioner (Section) - Cub Scouts", "County Assistant Commissioner (Section) - Beaver Scouts", "County Assistant Commissioner - Support, Communications", "County Assistant Commissioner - Support", "County Assistant Commissioner - Programme", "County Assistant Commissioner - Inclusion", "County Assistant Commissioner - Communications", "County Assistant Commissioner - Activities", "Assistant Section Leader", "Assistant Group Scout Leader - Support", "Assistant Group Scout Leader"] #3. Process the data #Start at row 4 for the OSM output line_num = int(4) #strips header out #Ensure we start writing at row 2 row_num_missing = 2 row_num_late = 2 row_num_due = 2 row_num_started = 2 while line_num < (len(input_data)-4): #while there is an entry to handle, do this #3.1. Test 1 - Should the role have a Woodbadge? #Read the column role = input_data[line_num][5] #Check - Is it in the list? if role in role_woodbadge: #check if wood badge awarded woodbadge_awarded = input_data[line_num][9] if woodbadge_awarded == "": woodbadge_missing = True else: woodbadge_missing = False #check if over 3 years since role start date #Determine role start date role_start = datetime.datetime.strptime(input_data[line_num][7], "%d/%m/%Y") #Establish comparative date - set at 3 years as per POR _3years_later = role_start + relativedelta(years=+3) #Get today's date today1 = str(datetime.date.today()) today = datetime.datetime.strptime(today1, "%Y-%m-%d") #dodgy work around comparing datetime to date object error #Compare the role start if today >_3years_later: #if 3 years after role start is less than today, must be more than 3 years since role start role_late = True else: role_late = False #We now need to do stuff as we have identified if the wood badge is late if woodbadge_missing == True and role_late == True: #check if already flagged member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = total_woodbadge_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list total_woodbadge_list.append(member_number) #now we can add details to the list #If blank, add to list using known_name, surname, email, member_role, group, district, training module woodbadge.writerow([input_data[line_num][0], input_data[line_num][3], input_data[line_num][2], input_data[line_num][4], input_data[line_num][5], input_data[line_num][14], input_data[line_num][12], input_data[line_num][7], input_data[line_num][9]]) #Split into districts district_name = input_data[line_num][12] if district_name == '': county_woodbadge_list.append(member_number) if district_name == 'Cole Valley South': CVS_woodbadge_list.append(member_number) if district_name == 'Rea Valley': rea_woodbadge_list.append(member_number) if district_name == 'Birmingham Spitfire District Scout Association': spitfire_woodbadge_list.append(member_number) if district_name == 'Sutton Coldfield East': SCE_woodbadge_list.append(member_number) if district_name == 'Sutton Coldfield West': SCW_woodbadge_list.append(member_number) if district_name == 'Tame Valley Birmingham': tame_woodbadge_list.append(member_number) line_num = line_num + 1 #continues the loop #3.3 Produce the statistics file woodbadge_statistics_file.write('Statistics File for Overdue Wood Badges Reports\n') woodbadge_statistics_file.write('====================================\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(total_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('County Roles\n') woodbadge_statistics_file.write('============\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(county_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Cole Valley South Roles\n') woodbadge_statistics_file.write('=======================\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(CVS_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Rea Valley Roles\n') woodbadge_statistics_file.write('================\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(rea_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Spitfire Roles\n') woodbadge_statistics_file.write('==============\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(spitfire_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Sutton Coldfield East Roles\n') woodbadge_statistics_file.write('===========================\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(SCE_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Sutton Coldfield West Roles\n') woodbadge_statistics_file.write('===========================\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(SCW_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') woodbadge_statistics_file.write('Tame Valley Roles\n') woodbadge_statistics_file.write('===========================\n') woodbadge_statistics_file.write('Leaders which need to complete at least 1 wood badge: ' + str(len(tame_woodbadge_list))+'\n') woodbadge_statistics_file.write('\n') #4: Close files to show they are done woodbadge_file.close() print("Finished wood badge reports!") def districts_reports(): #Runs after missing and soon due training has been listed. Produces district reports #County county_woodbadge_file = open('county_woodbadge_training.csv', 'w', newline='') county_woodbadge = csv.writer(county_woodbadge_file) county_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) #Districts CVS_woodbadge_file = open('CVS_woodbadge_training.csv', 'w', newline='') CVS_woodbadge = csv.writer(CVS_woodbadge_file) CVS_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) spitfire_woodbadge_file = open('spitfire_woodbadge_training.csv', 'w', newline='') spitfire_woodbadge = csv.writer(spitfire_woodbadge_file) spitfire_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) SCE_woodbadge_file = open('SCE_woodbadge_training.csv', 'w', newline='') SCE_woodbadge = csv.writer(SCE_woodbadge_file) SCE_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) SCW_woodbadge_file = open('SCW_woodbadge_training.csv', 'w', newline='') SCW_woodbadge = csv.writer(SCW_woodbadge_file) SCW_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) rea_woodbadge_file = open('rea_woodbadge_training.csv', 'w', newline='') rea_woodbadge = csv.writer(rea_woodbadge_file) rea_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) tame_woodbadge_file = open('tame_woodbadge_training.csv', 'w', newline='') tame_woodbadge = csv.writer(tame_woodbadge_file) tame_woodbadge.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Wood Badge Received Date']) #6. reports #Open the file file_name = str(Path.cwd()) + '/' + str('woodbadge_report.csv') input_file = open(file_name) #assumes file is in working directory #Create a list using the CSV file input_data_reader = csv.reader(input_file) input_data = list(input_data_reader) #6.2 Process the data #start at row 2 to strip header out line_num = int(1) while line_num < (len(input_data)-1): #While there is an entry to handle, do something #read District info (6) district = input_data[line_num][6] if district == '': #If no district, must be county county_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Cole Valley South': CVS_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Birmingham Spitfire District Scout Association': spitfire_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Sutton Coldfield East': SCE_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Sutton Coldfield West': SCW_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Rea Valley': rea_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) if district == 'Tame Valley Birmingham': tame_woodbadge.writerow([input_data[line_num][0], input_data[line_num][1], input_data[line_num][2], input_data[line_num][3], input_data[line_num][4], input_data[line_num][5], input_data[line_num][6], input_data[line_num][7]]) line_num = line_num + 1 #8: Close the files county_woodbadge_file.close() CVS_woodbadge_file.close() spitfire_woodbadge_file.close() SCE_woodbadge_file.close() SCW_woodbadge_file.close() rea_woodbadge_file.close() tame_woodbadge_file.close() print("Wood badge district reports finished!") #Program training_report() districts_reports()