#! python3 #Training_Reports.py #A tool to process Compass Records and produce something human readable #Licence: #Written by Stuart Griffiths, stuart.griffiths@birminghamscouts.org.uk #Started 27/03/2022 #Version:0.2 #Released: #Status: Working - outputs to 4 seperate CSV files #Inputs: CSV #Outputs: 4 x CSV #Next Steps: generate list of unique entries, upload to Drive?, retrive automatically, send e-mails, working directory selection, #Issues: View for mandatory learning is missing district and group info, need to cross reference #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 #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 #import openpyxl #1. Set up Output files #CSV missing_file = open('missing_training.csv', 'w', newline='') missing = csv.writer(missing_file) missing.writerow(['Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module']) late_mandatory_file = open('late_mandatory_training.csv', 'w', newline='') late_mandatory = csv.writer(late_mandatory_file) late_mandatory.writerow(['Known_Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module', 'Due_Date']) due_mandatory_file = open('due_mandatory_training.csv', 'w', newline='') due_mandatory = csv.writer(due_mandatory_file) due_mandatory.writerow(['Known_Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module', 'Due_Date']) getting_started_file = open('getting_started_training.csv', 'w', newline='') getting_started = csv.writer(getting_started_file) getting_started.writerow(['Known_Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module', 'Role_Start_Date']) #text files #missing_training_list = open('_missing_training.txt', 'w') #late_mandatory_list = open('_late_mandatory_training.txt', 'w') #due_mandatory_list = open('_due_mandatory_training.txt', 'w') #getting_started_list = open('_getting_started_training.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) #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 - Are there training modules left to do? #Read the column role_training = input_data[line_num][17] #Check - Is it blank? Means not completed so of interest if role_training == "": #If blank, add to list using known_name, surname, email, member_role, group, district, training module missing.writerow([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][16]]) #missing_training_list.write(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][16]) #missing_training_list.write('\n') #adds new line #3.2 Test 2 - Are there any core modules to renew? #Read the column core_training = input_data[line_num][20] #Check - Is it not blank? if core_training != "": #Slice the chracters to get days, months and year (remember starts 0) dt = datetime.datetime.now() #need to call this to get current date core_training_year = int(core_training[6:]) core_training_month1 = core_training[3:] #removes first 3 characters core_training_month = int(core_training_month1[:2]) #removes characters after first 2 core_training_day = int(core_training[:2]) #Check is it late? #If year is less than year when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_year < dt.year: late_mandatory.writerow([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][16], input_data[line_num][20]]) #late_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #late_mandatory_list.write('\n') #adds new line #Else if month is less than month when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_month <= dt.month: late_mandatory.writerow([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][16], input_data[line_num][20]]) #late_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #late_mandatory_list.write('\n') #adds new line #Else if year is less than year when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_day <= dt.day: late_mandatory.writerow([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][16], input_data[line_num][20]]) #late_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #late_mandatory_list.write('\n') #adds new line #Check - Is it due soon? #Establish comparative date - set at 6 months Xmonths_later = str(datetime.datetime.now() + relativedelta(months=+6)) Xmonths_later_year = int(Xmonths_later[:4]) Xmonths_later_month1 = Xmonths_later[5:] #removes first 3 characters Xmonths_later_month = int(Xmonths_later_month1[:2]) #removes characters after first 2 Xmonths_later_day1 = Xmonths_later[8:] Xmonths_later_day = int(Xmonths_later_day1[:2]) #If year is less than year when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_year < Xmonths_later_year: due_mandatory.writerow([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][16], input_data[line_num][20]]) #due_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #due_mandatory_list.write('\n') #adds new line #Else if month is less than month when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_month <= Xmonths_later_month: due_mandatory.writerow([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][16], input_data[line_num][20]]) #due_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #due_mandatory_list.write('\n') #adds new line #Else if year is less than year when program is ran, add to list using known_name, surname, email, member_role, group, district, training module if core_training_day <= Xmonths_later_day: due_mandatory.writerow([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][16], input_data[line_num][20]]) #due_mandatory_list.write(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][16] + ' ' + input_data[line_num][20]) #due_mandatory_list.write('\n') #adds new line #3.3 Test 3 - Have Getting Started Modules been completed? #GDPR, Essential Information, Trustee Introduction, Tools for the Role Section Leaders, Managers and Supporters #Check if Module is one we are interested in module_name = str(input_data[line_num][16]) if module_name == 'Essential Information': complete_check = str(input_data[line_num][17]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line if module_name == 'General Data Protection Regulations': complete_check = str(input_data[line_num][17]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line if module_name == 'Trustee Introduction': complete_check = str(input_data[line_num][17]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line if module_name == 'Tools for the Role (Managers and Supporters)': complete_check = str(input_data[line_num][17]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line if module_name == 'Tools for the Role (Section Leaders)': complete_check = str(input_data[line_num][17]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line #Safety and Safeguarding module_name = str(input_data[16]) if module_name == 'MOGL: Safeguarding': complete_check = str(input_data[line_num][20]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line if module_name == 'MOGL: Safety': complete_check = str(input_data[line_num][20]) #Check: is the date blank? if complete_check == '': getting_started.writerow([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][16], input_data[line_num][7]]) #getting_started_list.write(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][16] + ' ' + input_data[line_num][7]) #getting_started_list.write('\n') #adds new line line_num = line_num + 1 #continues the loop #4: Close files to show they are done #missing_training_list.close() #late_mandatory_list.close() #due_mandatory_list.close() #getting_started_list.close() missing_file.close() late_mandatory_file.close() due_mandatory_file.close() getting_started_file.close() print("Finished!") #Program training_report()