234 lines
13 KiB
Python
234 lines
13 KiB
Python
#! 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()
|