#! python3
#Appointments_Training_Reports.py
#A tool to process Compass Records and produce something human readable using the appointments report
#Licence: GPL-3.0-or-later
#Written by Stuart Griffiths, stuart.griffiths@birminghamscouts.org.uk
#Started 02/10/2022
#Version:0.1
#Released: 02/10/2022
#Status: Working - outputs to text files
#Inputs: CSV
#Outputs: multiple text files
#Next Steps: 
#Issues:  
#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

#1. Set up output files
#text files
member_file = open('member_list.txt', 'w')
gsl_file = open('gsl_list.txt', 'w')
squirrels_file = open('squirrels_list.txt', 'w')
beavers_file = open('beavers_list.txt', 'w')
cubs_file = open('cubs_list.txt', 'w')
scouts_file = open('scouts_list.txt', 'w')
explorers_file = open('explorers_list.txt', 'w')
network_file = open('network_list.txt', 'w')

def reports_gen():
    #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
    #create lists to process
    member_list = []
    gsl_list = []
    squirrels_list = []
    beavers_list = []
    cubs_list = []
    scouts_list = []
    explorers_list = []
    network_list = []

    while line_num < (len(input_data)-1): #while there is an entry to handle, do this
        
        #3.1 Member list - need to exclude occasional helpers and avoid duplicates
        #Required to ensure only the primary role is being processed
        line_num_next = line_num + 1

        #Read email to help filter for duplicates

        email = input_data[line_num][4]

        current_line_membership = input_data[line_num][0]
        try:
            next_line_membership = input_data[line_num_next][0]
        except:
            a= 1
        #If membership numbers are equal, we are not at primary role so want to skip to next line
        #if they are not equal, we are at primary role and so want to process
        if next_line_membership != current_line_membership:
            #check if role contains the word occasional
            role = input_data[line_num][8]
            if role.find("Occasional") == -1 and email.find('@') != -1:
                #Only add when occasional is not in role title
                #Add member e-mail to the list
                member_list.append(input_data[line_num][4])

        #3.2 GSL list
        
        role = input_data[line_num][8]
        if role.find("Group Scout Leader") != -1 and email.find('@') != -1 and email not in gsl_list:
            #Only add when title contains "Group Scout Leader"
            #Add member e-mail to the list
            gsl_list.append(input_data[line_num][4])

        #3.3 Squirrels list
        
        role = input_data[line_num][8]
        if role.find("Squirrel") != -1 and email.find('@') != -1 and email not in squirrels_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            squirrels_list.append(input_data[line_num][4])

        #3.4 Beavers list
        
        role = input_data[line_num][8]
        if role.find("Beaver") != -1 and email.find('@') != -1 and email not in beavers_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            beavers_list.append(input_data[line_num][4])

        #3.5 Cubs list
        
        role = input_data[line_num][8]
        if role.find("Cub") != -1 and email.find('@') != -1 and email not in cubs_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            cubs_list.append(input_data[line_num][4])

        #3.6 Scouts list
        
        role = input_data[line_num][8]
        #filter gets tricky here, ensure no mention of squirrels, beavers, cubs, explorers, network or gsl
        if role.find("Scouts") != -1 and role.find("Group") == -1 and role.find("Squirrel") == -1 and role.find("Beaver") == -1 and role.find("Cub") == -1 and role.find("Explorer") == -1 and role.find("Network") == -1 and email.find('@') != -1 and email not in scouts_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            scouts_list.append(input_data[line_num][4])           

        #3.7 Explorer list
        
        role = input_data[line_num][8]
        if role.find("Explorer") != -1 and email.find('@') != -1 and email not in explorers_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            explorers_list.append(input_data[line_num][4])

        #3.8 Network list
        
        role = input_data[line_num][8]
        if role.find("Network") != -1 and email.find('@') != -1 and email not in network_list:
            #Only add when title contains variable in ""
            #Add member e-mail to the list
            network_list.append(input_data[line_num][4])

        #to continue to next line
        line_num = line_num + 1
    
    #4 Output file generation

    member_file.write('Member List For Birmingham County\n')
    member_file.write('=================================\n')
    member_file.write('\n')
    member_file.write('Number of members = ' + str(len(member_list)) + '\n')
    member_file.write('\n')
    for x in range(len(member_list)):
        member_file.write(member_list[x])
        member_file.write('\n')

    gsl_file.write('GSL List For Birmingham County\n')
    gsl_file.write('==============================\n')
    gsl_file.write('\n')
    gsl_file.write('Number of members = ' + str(len(gsl_list)) + '\n')
    gsl_file.write('\n')
    for x in range(len(gsl_list)):
        gsl_file.write(gsl_list[x])
        gsl_file.write('\n')

    squirrels_file.write('Squirrel Leader List For Birmingham County\n')
    squirrels_file.write('==========================================\n')
    squirrels_file.write('\n')
    squirrels_file.write('Number of members = ' + str(len(squirrels_list)) + '\n')
    squirrels_file.write('\n')
    for x in range(len(squirrels_list)):
        squirrels_file.write(squirrels_list[x])
        squirrels_file.write('\n')

    beavers_file.write('Beaver Leader List For Birmingham County\n')
    beavers_file.write('========================================\n')
    beavers_file.write('\n')
    beavers_file.write('Number of members = ' + str(len(beavers_list)) + '\n')
    beavers_file.write('\n')
    for x in range(len(beavers_list)):
        beavers_file.write(beavers_list[x])
        beavers_file.write('\n')
    
    cubs_file.write('Cub Leader List For Birmingham County\n')
    cubs_file.write('=====================================\n')
    cubs_file.write('\n')
    cubs_file.write('Number of members = ' + str(len(cubs_list)) + '\n')
    cubs_file.write('\n')
    for x in range(len(cubs_list)):
        cubs_file.write(cubs_list[x])
        cubs_file.write('\n')

    scouts_file.write('Scout Leaders List For Birmingham County\n')
    scouts_file.write('========================================\n')
    scouts_file.write('\n')
    scouts_file.write('Number of members = ' + str(len(scouts_list)) + '\n')
    scouts_file.write('\n')
    for x in range(len(scouts_list)):
        scouts_file.write(scouts_list[x])
        scouts_file.write('\n')

    explorers_file.write('Explorer Leader List For Birmingham County\n')
    explorers_file.write('==========================================\n')
    explorers_file.write('\n')
    explorers_file.write('Number of members = ' + str(len(explorers_list)) + '\n')
    explorers_file.write('\n')
    for x in range(len(explorers_list)):
        explorers_file.write(explorers_list[x])
        explorers_file.write('\n')

    network_file.write('Network Leaders List For Birmingham County\n')
    network_file.write('==========================================\n')
    network_file.write('\n')
    network_file.write('Number of members = ' + str(len(network_list)) + '\n')
    network_file.write('\n')
    for x in range(len(network_list)):
        network_file.write(network_list[x])
        network_file.write('\n')

    member_file.close()
    gsl_file.close()
    squirrels_file.close()
    beavers_file.close()
    cubs_file.close()
    scouts_file.close()
    explorers_file.close()
    network_file.close()
    print("Finished producing lists.")
    
reports_gen()