#! python3 #Appointments_Training_Reports.py #A tool to process Compass Records and produce something human readable using the appointments report #Licence: #Written by Stuart Griffiths, stuart.griffiths@birminghamscouts.org.uk #Started 19/05/2022 #Version:0.1 #Released: #Status: Working - outputs to 2 seperate CSV files #Inputs: CSV #Outputs: 2 x CSV #Next Steps: generate list of unique entries, upload to Drive?, retrive automatically, send e-mails, working directory selection, #Issues: Lots of repeats (can be filtered for), #Background IP: https://automatetheboringstuff.com/, Chapter 16 #Notes - CSV Column Numbers #Membership_Number = 0 #Surname = 2 #Known_As = 3 #Email = 4 #Telephone = 5 #Member_Role = 8 #Roll_Status = 11 #Roll_Start_Date = 9 #Line_Manager = 13 #Review_Date = 14 #County Section = 18 #District = 19 #District section = 20 #Scout_Group = 21 #CE check = 23 #Advisory committee approval = 24 #commissioner approval = 25 #Committee approval = 26 #References = 27 #Wood badge = 35 #Safety Completed = 36 #Safety Due = 37 #Safeguarding completed = 38 #Safeguarding due = 39 #First Aid Completed = 40 #First Aid Due = 41 #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 #import openpyxl #CHANGE ALL OF THIS #1. Set up Output files #CSV files safety_late_mandatory_file = open('safety_late_mandatory_training.csv', 'w', newline='') safety_late_mandatory = csv.writer(safety_late_mandatory_file) safety_late_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) safeguarding_late_mandatory_file = open('safeguarding_late_mandatory_training.csv', 'w', newline='') safeguarding_late_mandatory = csv.writer(safeguarding_late_mandatory_file) safeguarding_late_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) firstaid_late_mandatory_file = open('firstaid_late_mandatory_training.csv', 'w', newline='') firstaid_late_mandatory = csv.writer(firstaid_late_mandatory_file) firstaid_late_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) safety_due_mandatory_file = open('safety_due_mandatory_training.csv', 'w', newline='') safety_due_mandatory = csv.writer(safety_due_mandatory_file) safety_due_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) safeguarding_due_mandatory_file = open('safeguarding_due_mandatory_training.csv', 'w', newline='') safeguarding_due_mandatory = csv.writer(safeguarding_due_mandatory_file) safeguarding_due_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) firstaid_due_mandatory_file = open('firstaid_due_mandatory_training.csv', 'w', newline='') firstaid_due_mandatory = csv.writer(firstaid_due_mandatory_file) firstaid_due_mandatory.writerow(['Membership Number', 'Known_Name', 'Surname', 'E-Mail', 'Telephone', 'Role', 'Line Manager', 'Group', 'District', 'Safety', 'Safeguarding', 'First Aid']) #text files statistics_file = open('_training_statistics_county.txt', 'w') #Modules def training_report(): #2. Open the file file_name = str(Path.cwd()) + '/' + str('County Appointments Report (Beta).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) #3. Process the data #Start at row 1 for the OSM output line_num = int(1) #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 #create lists for counting purposes safety_missing_list = [] safeguarding_missing_list = [] firstaid_missing_list = [] safety_due_list = [] safeguarding_due_list = [] firstaid_due_list = [] while line_num < (len(input_data)-4): #while there is an entry to handle, do this #3.1 Test 1 - Is Safety Complete? #Read the column safety = input_data[line_num][37] #Check - Is it not blank? if safety != "": #Convert string to date object, has to be done here for the blanks! safety = datetime.datetime.strptime(input_data[line_num][37], "%d/%m/%Y") #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 #today = now.strftime("%d/%m/%Y") #Check is it late? #If date is less than today when program is ran, add to list using membership no, known_name, surname, email, telephone, member_role, manager, group, district, safety due, safeguarding due, first aid due if safety < today: safety_late_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], input_data[line_num][37], '', '']) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = safety_missing_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list safety_missing_list.append(member_number) #Establish comparative date - set at 6 months Xmonths_later = datetime.datetime.now() + relativedelta(months=+6) #If date is less than 6 momths from when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if safety < Xmonths_later and safety >= today: safety_due_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], input_data[line_num][37], '', '']) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = safety_due_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list safety_due_list.append(member_number) #3.2 Test 2 - Is Safeguarding Complete? #Read the column safeguarding = input_data[line_num][39] #Check - Is it not blank? if safeguarding != "": #Convert string to date object, has to be done here for the blanks! safeguarding = datetime.datetime.strptime(input_data[line_num][39], "%d/%m/%Y") #Already got today's date #Check is it late? #If date is less than today when program is ran, add to list using membership no, known_name, surname, email, telephone, member_role, manager, group, district, safety due, safeguarding due, first aid due if safeguarding < today: safeguarding_late_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], '', input_data[line_num][39], '']) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = safeguarding_missing_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list safeguarding_missing_list.append(member_number) #Check - Is it due soon? #Already established comparative date - set at 6 months #If date is less than 6 months from when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if safeguarding < Xmonths_later and safeguarding >= today: safeguarding_due_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], '', input_data[line_num][39], '']) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = safeguarding_due_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list safeguarding_due_list.append(member_number) #3.3 Test 3 - Is First Aid Complete? #Read the column firstaid = input_data[line_num][41] #Check - Is it not blank? if firstaid != "": #Convert string to date object, has to be done here for the blanks! firstaid = datetime.datetime.strptime(input_data[line_num][41], "%d/%m/%Y") #Already got today's date #Check is it late? #If date is less than today when program is ran, add to list using membership no, known_name, surname, email, telephone, member_role, manager, group, district, safety due, safeguarding due, first aid due if firstaid < today: firstaid_late_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], '', '', input_data[line_num][41]]) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = firstaid_missing_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list firstaid_missing_list.append(member_number) #Check - Is it due soon? #Already established comparative date - set at 6 months #If date is less than 6 months from when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if firstaid < Xmonths_later and firstaid >= today: firstaid_due_mandatory.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][8], input_data[line_num][13], input_data[line_num][21], input_data[line_num][19], '', '', input_data[line_num][41]]) member_number = input_data[line_num][0] #check whether membership number is in the index and add it if not try: x = firstaid_due_list.index(member_number) #if it is, nothing happens except: #this executes if the membership number is not in the list firstaid_due_list.append(member_number) line_num = line_num + 1 #continues the loop #4: Statistics Generation Time statistics_file.write('Statistics File for Training Reports\n') statistics_file.write('====================================\n') statistics_file.write('\n') statistics_file.write('Leaders who are late with Safety module: ' + str(len(safety_missing_list))+'\n') statistics_file.write('Leaders who need to complete the Safety module within 6 months: ' + str(len(safety_due_list))+'\n') statistics_file.write('Leaders who are late with Safeguarding module: ' + str(len(safeguarding_missing_list))+'\n') statistics_file.write('Leaders who need to complete the Safeguarding module within 6 months: ' + str(len(safeguarding_due_list))+'\n') statistics_file.write('Leaders who are late with First Aid module: ' + str(len(firstaid_missing_list))+'\n') statistics_file.write('Leaders who need to complete the First Aid module within 6 months: ' + str(len(firstaid_due_list))+'\n') #5: Close files to show they are done safety_late_mandatory_file.close() safeguarding_late_mandatory_file.close() firstaid_late_mandatory_file.close() safety_due_mandatory_file.close() safeguarding_due_mandatory_file.close() firstaid_due_mandatory_file.close() statistics_file.close() print("Finished!") #Program training_report()