320 lines
18 KiB
Python
320 lines
18 KiB
Python
#! python3
|
|
#Training_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 27/03/2022
|
|
#Version:0.6
|
|
#Released:
|
|
#Status: Working
|
|
#Inputs: CSV
|
|
#Outputs: multiple CSV
|
|
#Next Steps: generate list of unique entries, upload to Drive?, retrive automatically, send e-mails, working directory selection,
|
|
#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
|
|
#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(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted_Training_Module'])
|
|
|
|
GDPR_file = open('GDPR_training.csv', 'w', newline='')
|
|
GDPR = csv.writer(GDPR_file)
|
|
GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
#text files
|
|
GDPR_statistics_file = open('_GDPR_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_GDPR_list = []
|
|
county_GDPR_list = []
|
|
CVS_GDPR_list = []
|
|
rea_GDPR_list = []
|
|
spitfire_GDPR_list = []
|
|
SCE_GDPR_list = []
|
|
SCW_GDPR_list = []
|
|
tame_GDPR_list = []
|
|
|
|
#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][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][16]])
|
|
|
|
|
|
#3.2 Test 2 - Missing GDPR?
|
|
#GDPR
|
|
#Check if Module is one we are interested in
|
|
|
|
module_name = str(input_data[line_num][16])
|
|
if module_name == 'General Data Protection Regulations':
|
|
complete_check = str(input_data[line_num][17])
|
|
member_number = input_data[line_num][0]
|
|
#Check: is the date blank?
|
|
if complete_check == '':
|
|
GDPR.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][16], input_data[line_num][7]])
|
|
try:
|
|
x = total_GDPR_list.index(member_number)
|
|
#if it is, nothing happens
|
|
except:
|
|
#this executes if the membership number is not in the list
|
|
#Statistics
|
|
total_GDPR_list.append(member_number)
|
|
district_name = input_data[line_num][12]
|
|
if district_name == '':
|
|
county_GDPR_list.append(member_number)
|
|
if district_name == 'Cole Valley South':
|
|
CVS_GDPR_list.append(member_number)
|
|
if district_name == 'Rea Valley':
|
|
rea_GDPR_list.append(member_number)
|
|
if district_name == 'Birmingham Spitfire District Scout Association':
|
|
spitfire_GDPR_list.append(member_number)
|
|
if district_name == 'Sutton Coldfield East':
|
|
SCE_GDPR_list.append(member_number)
|
|
if district_name == 'Sutton Coldfield West':
|
|
SCW_GDPR_list.append(member_number)
|
|
if district_name == 'Tame Valley Birmingham':
|
|
tame_GDPR_list.append(member_number)
|
|
line_num = line_num + 1 #continues the loop
|
|
|
|
#3.3 Produce the statistics file
|
|
GDPR_statistics_file.write('Statistics File for Training Reports\n')
|
|
GDPR_statistics_file.write('====================================\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(total_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('County Roles\n')
|
|
GDPR_statistics_file.write('============\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(county_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Cole Valley South Roles\n')
|
|
GDPR_statistics_file.write('=======================\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(CVS_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Rea Valley Roles\n')
|
|
GDPR_statistics_file.write('================\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(rea_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Spitfire Roles\n')
|
|
GDPR_statistics_file.write('==============\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(spitfire_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Sutton Coldfield East Roles\n')
|
|
GDPR_statistics_file.write('===========================\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(SCE_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Sutton Coldfield West Roles\n')
|
|
GDPR_statistics_file.write('===========================\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(SCW_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
GDPR_statistics_file.write('Tame Valley Roles\n')
|
|
GDPR_statistics_file.write('===========================\n')
|
|
GDPR_statistics_file.write('Leaders who need to complete GDPR module: ' + str(len(tame_GDPR_list))+'\n')
|
|
GDPR_statistics_file.write('\n')
|
|
#4: Close files to show they are done
|
|
|
|
missing_file.close()
|
|
GDPR_file.close()
|
|
print("Finished Total Reports!")
|
|
|
|
def districts_reports():
|
|
#Runs after missing and soon due training has been listed. Produces district reports
|
|
|
|
#County
|
|
|
|
county_late_mandatory_file = open('county_missing_training.csv', 'w', newline='')
|
|
county_late_mandatory = csv.writer(county_late_mandatory_file)
|
|
county_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
county_GDPR_file = open('county_GDPR.csv', 'w', newline='')
|
|
county_GDPR = csv.writer(county_GDPR_file)
|
|
county_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
|
|
#Districts
|
|
|
|
CVS_late_mandatory_file = open('CVS_missing_training.csv', 'w', newline='')
|
|
CVS_late_mandatory = csv.writer(CVS_late_mandatory_file)
|
|
CVS_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
CVS_GDPR_file = open('CVS_GDPR.csv', 'w', newline='')
|
|
CVS_GDPR = csv.writer(CVS_GDPR_file)
|
|
CVS_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
spitfire_late_mandatory_file = open('spitfire_missing_training.csv', 'w', newline='')
|
|
spitfire_late_mandatory = csv.writer(spitfire_late_mandatory_file)
|
|
spitfire_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
spitfire_GDPR_file = open('spitfire_GDPR.csv', 'w', newline='')
|
|
spitfire_GDPR = csv.writer(spitfire_GDPR_file)
|
|
spitfire_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
SCE_late_mandatory_file = open('SCE_missing_training.csv', 'w', newline='')
|
|
SCE_late_mandatory = csv.writer(SCE_late_mandatory_file)
|
|
SCE_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
SCE_GDPR_file = open('SCE_GDPR.csv', 'w', newline='')
|
|
SCE_GDPR = csv.writer(SCE_GDPR_file)
|
|
SCE_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
SCW_late_mandatory_file = open('SCW_missing_training.csv', 'w', newline='')
|
|
SCW_late_mandatory = csv.writer(SCW_late_mandatory_file)
|
|
SCW_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
SCW_GDPR_file = open('SCW_GDPR.csv', 'w', newline='')
|
|
SCW_GDPR = csv.writer(SCW_GDPR_file)
|
|
SCW_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
rea_late_mandatory_file = open('rea_missing_training.csv', 'w', newline='')
|
|
rea_late_mandatory = csv.writer(rea_late_mandatory_file)
|
|
rea_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
rea_GDPR_file = open('rea_GDPR.csv', 'w', newline='')
|
|
rea_GDPR = csv.writer(rea_GDPR_file)
|
|
rea_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
tame_late_mandatory_file = open('tame_missing_training.csv', 'w', newline='')
|
|
tame_late_mandatory = csv.writer(tame_late_mandatory_file)
|
|
tame_late_mandatory.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Role Start Date', 'Uncompleted Training Module'])
|
|
|
|
tame_GDPR_file = open('tame_GDPR.csv', 'w', newline='')
|
|
tame_GDPR = csv.writer(tame_GDPR_file)
|
|
tame_GDPR.writerow(['Membership Number', 'Known Name', 'Surname', 'E-Mail', 'Role', 'Group', 'District', 'Uncompleted_Training_Module'])
|
|
|
|
#6. late reports
|
|
#Open the file
|
|
file_name = str(Path.cwd()) + '/' + str('missing_training.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_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Cole Valley South':
|
|
CVS_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Birmingham Spitfire District Scout Association':
|
|
spitfire_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Sutton Coldfield East':
|
|
SCE_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Sutton Coldfield West':
|
|
SCW_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Rea Valley':
|
|
rea_late_mandatory.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], input_data[line_num][8]])
|
|
if district == 'Tame Valley Birmingham':
|
|
tame_late_mandatory.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], input_data[line_num][8]])
|
|
|
|
line_num = line_num + 1
|
|
|
|
#7. late reports
|
|
#Open the file
|
|
file_name = str(Path.cwd()) + '/' + str('GDPR_training.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)
|
|
|
|
#7.2 Process the data
|
|
#start at row 2 to strip header out
|
|
line_num = int(1)
|
|
#list of written membership numbers
|
|
GDPR_list = []
|
|
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]
|
|
member_number = input_data[line_num][0]
|
|
try:
|
|
x = GDPR_list.index(member_number)
|
|
#if it is, nothing happens
|
|
except:
|
|
#this executes if the membership number is not in the list
|
|
GDPR_list.append(member_number)
|
|
if district == '':
|
|
#If no district, must be county
|
|
county_GDPR.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_GDPR.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_GDPR.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_GDPR.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_GDPR.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_GDPR.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_GDPR.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 #continues the loop
|
|
|
|
#8: Close the files
|
|
county_late_mandatory_file.close()
|
|
county_GDPR_file.close()
|
|
CVS_late_mandatory_file.close()
|
|
CVS_GDPR_file.close()
|
|
spitfire_late_mandatory_file.close()
|
|
spitfire_GDPR_file.close()
|
|
SCE_late_mandatory_file.close()
|
|
SCE_GDPR_file.close()
|
|
SCW_late_mandatory_file.close()
|
|
SCW_GDPR_file.close()
|
|
rea_late_mandatory_file.close()
|
|
rea_GDPR_file.close()
|
|
tame_late_mandatory_file.close()
|
|
tame_GDPR_file.close()
|
|
|
|
print("District reports finished!")
|
|
|
|
#Program
|
|
training_report()
|
|
districts_reports()
|