The Scheduler

The Scheduler was born out of the need for a more robust volunteer scheduling system for my church. The app manages the scheduling of the volunteers by enabling a user to add, modify, and delete a volunteer or schedule, and query the schedule by date or username. Ideally only people with admin privileges will be able to make any changes to the schedule, but everyone will be able to query the schedule. Currently there are no admin privileges set for the app so everyone has full access.

The Scheduler consist of 4 tables:

• Volunteer
• Schedule
• Role
• Volunteers_Role

Diagram table relationships:

fig(1)

Schedule

class Schedule(Base):
    __tablename__ = 'schedules'

    id = Column(Integer, primary_key=True)
    date = Column(DATE, nullable=False)
    swappout_id = Column(Integer,nullable=True)

    vol_id = Column(Integer, ForeignKey('volunteers.id'))
    role_id = Column(Integer, ForeignKey('roles.id'))

fig(2)

Because Schedule has a one-to-many relationship with Volunter and Role, foreign keys were created for both tables to manage the relationship

Example One-to-Many

ipdb>    schedule = session.query(Schedule).filter(Schedule.date == "2023-08-16").all()

ipdb> schedule
[Schedule: 3, Swapped: 19, Volunteer: 5, Role: 4, Date: 2023-08-16 , Schedule: 29, Swapped: , Volunteer: 7, Role: 2, Date: 2023-08-16 ]

ipdb> [sched.vol_id for sched in schedule]
[5, 7]

ipdb>  [sched.role_id for sched in schedule]
[4, 2]

Because schedule object returned a list (one-to-many) I iterated over the list using a list comprehension to pull out the volunteer ids. Notice that one instance of schedule also return multiple role ids.

Volunteer

fig(3)

class Volunteer(Base,Validate):
    __tablename__ = "volunteers"

    id = Column(Integer, primary_key=True)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)

    email = Column(String, unique=True)
    phone = Column(String, nullable=False)
    username = Column(String, unique=True)
    floater = Column (Boolean, nullable=False)
    week = Column(Integer, nullable=False)
    assigned = Column(String, nullable=False)

    roles = relationship('Role', secondary='volunteer_role',
                          back_populates='volunteers')
    schedules = relationship('Schedule', backref=backref('volunteer'))

Besides a primary key the volunteer table has two relational fields. roles is used for managing the relationship between the Role and Volunteer tables through the association table, Volunteers_Role. This is necessary because of the many-to-many relationship between Volunteer and Role. When dealing with a many-to-many relationship it is necessary to have an intermediary table to connect the two tables. back_populates is a relational parameter that tells sqlalchemy to link the volunteer table to the role table.

Schedule has a one-to-many relationship with Volunteer so the schedules relational field is used to manage that relationship.

fig(4)

Role

fig(5)

class Role(Base):
    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True)
    position = Column(String, nullable=False)

    volunteers = relationship('Volunteer', secondary='volunteer_role',
                          back_populates='roles')
    schedules = relationship('Schedule', backref=backref('role'))

Like the Volunteer table the Role table has two relational fields. volunteers which manages the relatationship between Role and Volunteer through the association table Volunteer_Role. Also like the Volunteer table sqlalchemy uses the back_populates parameter to point roles back to volunteers.

Also like the Volunteer table, schedules is used to manage the one-to-many relationship between Schedule and the Role tables.

fig(6)

Volunter_Role

volunteer_role = Table (
     'volunteer_role',
     Base.metadata,
     Column('vol_id', ForeignKey('volunteers.id')),
     Column('role_id', ForeignKey('roles.id'))
)

Because the volunteer_role table is the association table that manages the many-to-many relationship between volunteers and roles you will see that some volunteers (vol_ids) are in the table more than once. You can also role_ids are in this table multiple times.

So in the Volunteer table back_populates links role back to volunteer and in the Role table back_populate links volunteer to role. They have what we call a bi-directional relationship with each returning a collection of the other.

fig(7)

Example: Many-to-Many
fig(8)

ipdb> volunteer.username
'Laura_Beltran'

ipdb> volunteer.roles
[Role id: 2, Position: usher , Role id: 4, Position: prayer ]
ipdb>

An instance of volunteer can have many roles. Notice volunteer is using the roles relational attribute to list all the roles pertaining to the volunteer Lauar_Beltran.

A role can have many volunteers
fig(9)

ipdb> role.position
'greeter'
ipdb>
ipdb> [role.username for role in role.volunteers]
['Victoria_Romero', 'Samuel_Mcclain', 'Emily_Wallace', 'Rodney_Woods']
ipdb>

As you can see an instance of role can return many volunteers. Notice role is using the volunteers relational attribute to list all the volunteers who are prayers.

Now that we see how the tables relate to each other, lets take a closer look at The Scheduler

The Scheduler

The Scheduler is a Command Line Interface or CLI app, enabling the user to interact with it from the command line. The cli file that envokes the scheduler is...wait for it...scheduling.py

fig(10)

Ok, I can see you're not impressed, so lets take a look at what it does and see if we can change your mind!

fig(11)

Because the The Scheduler is a cli the interface is very simple but effective. It begins with a menu that allows you to select the action you want to perform.

Add Volunteer

fig(12)

When the user selects Add Volunteer they are taken to the "Add Volunteer" screen where they are prompted to enter specific information about the volunteer. Notice at every prompt is the opportunity to quit the application by hitting x except the last prompt, that's because by then the user is most likely commited to the process.

It finishes by displaying a message containing the user's full name, username which is created internally, and the role they'll be serving in. The user then hits enter to exit and return to the menu.

Notice Tom Cruise #41 in the volunteer database:

fig(13)

The Scheduler uses the ORM (Object Relational Mapper), SQLAlchemy with Python to make this all happen. Now let's take a look at the code that makes up the Add Volunteer method.

Note: The CLI code for this app is pretty big because of all the validation that takes place, so because of that I'm going to limit the code shown to just the sqlalchemy.

With that said lets get to it!!!

The CLI does the job of validating each attribute below. Once it's satisfied all the data entered is valid it calls the add_volunteer method. The usernamne is generated by putting an "_" between the first and last name. As you'll see later on the usernamne is used extensivley to identify the volunteer so it needs to be unique. Because the possibility of having more than one person with the same first and last name is very high, the add_volunteer method checks to see if it exist, if it does it appends a random interger between 1-50 to the username.

fig(14)

def add_volunteer(fname, lname, email, phone, floater, week, position="prayer"):
            username = f"{fname}_{lname}"
            username_exist = session.query(Volunteer).filter(Volunteer.username == username).first()
            if username_exist:
                username = username + "_" + str(random.randint(1,50))  
            assigned = "No"  
            role = session.query(Role).filter(Role.position == position).first()
            volunteer = Volunteer(
                     first_name = fname, 
                     last_name = lname,
                     email = email,
                     phone = phone,
                     username = username,
                     floater = floater,
                     week = week,
                     assigned = assigned
            )
            session.add(volunteer)
            volunteer.roles.append(role)
            session.commit()
            return volunteer

volunteer.roles.append(role)
Using the relationship attributes on the volunteer and role tables the Volunteer_Role table is populated.

Delete Volunteer

Tom Cruise has become very busy working on "Mission Impossible: Maverick" so we're going to remove him and give him the space he needs to film the movie.

fig(15)

As you can see Tom has been removed and free to persue his acting career.

fig(16)

After all the validation is done the cli calls the delete_volunteer method.

• Get Tom's information from the volunteer table,
• Tom role association is removed from Volunteer_Role
• if Tom is on the schedule that schedule instance is removed.
• Tom is removed from the volunteer table

fig(17)

def delete_volunteer(username):
        volunteer = session.query(Volunteer).filter(
                    Volunteer.username == username).first()

        # Delete Volunteer _Role association from volunteer_role table
        [volunteer.roles.remove(role) for role in volunteer.roles]

        # Delete volunteer from schedule
        if volunteer.schedules:
            [session.delete(schedule) for schedule in volunteer.schedules]

        # Delete Volunteer
        session.delete(volunteer)
        session.commit() 

fig(18)

if volunteer.schedules:
            [session.delete(schedule) for schedule in volunteer.schedules]

Fig(18) shows the one-to-many relationship between the volunteer and the schedule. The schedules side of the relationship produces a collection, in this case is a list, that's then iterated over to get the needed information.

Modify Volunteer

Mary Spalding has just been married and wants to change her last name, email handle and role.

What you see below is the purpose of the Volunteer_Role table. Mary's id is 44 and right now she's an usher and the usher id is 2. So the association table (Volunteer_Role) is associating Mary's id with the usher id.

fig(19)

fig(20)

Here we see Mary stepping through the prompts. We her changing her last name to Spalding-Wilson and her email to spalding-wilson@gmail.com

fig(21)

Notice Mary's current role as usher is listed. It's important to remember there is no foreign key for role.id in the Volunteer table. The Scheduler is able to list the usher in association with Mary because of the Volunteer_Role table and the relational parameters provided to both tables.

fig(22)

The Scheduler is processing the changes and prints the message "Change was successful". The user then hits enter to exit and return to the menu.

Now let's take a look at the database.

Volunteer_Role:
Mary's role association has changed from 2 (usher) to 1 (greeter)

fig(23)

Volunteer
Mary's last name changed to Spalding-Wilson and her email changed to spalding_wilson@gmail.com

fig(24)

All the changes have been persisted to the database!

fig(25)

Ahem...the code...

During the validation phase the cli collects all the validate information into an object called changes using "role" as the key and "value" as the value. The object is then passed to the modify_volunteer method along with the usernamne. Using the items() object method, it's loop through changing the information based on the role passed in. If the role is being changed then we have to modify the Volunteer_Role table by removing the association to the old role and adding an association to the new role. This is how we're able to see the role changed from 2 to 1 in the fig(19) and fig(23) above.

 def modify_volunteer(username, changes):
        volunteer=session.query(Volunteer).filter(Volunteer.username == username).first()
        for key,value in changes.items():
            if key == "role":
               role = session.query(Role).filter(Role.position == value).first()
               volunteer.roles.append(role)
            if key == 'old':
               for role in volunteer.roles:
                   if role.position == value:
                      volunteer.roles.remove(role)
            setattr(volunteer,key,value)
        session.commit()
        return volunteer

Add to Schedule

Let's add Mary to the schedule.

fig(26)

Hmmm... looks like someone forgot Mary is not longer an usher . Remember she changed her role to greeter fig(23).

fig(27)

Mary Spalding has been successfully added to the schedule.
Let's check the Schedule table to make sure change was persisted to the database

fig(28)

Remember the Schedule contains two foreign keys; one for Role and one for Volunteer fig(2). Mary's id, 44 and the role id for greeter which is 1. We see the date is the date entered, 2023-09-03.

CLI validates information entered by user and then passes it to the add_to_schedule method. The datetime.strptim method is used to convert the date to a python format. The method get an instance of th volunteer and role and useed them to create an instance of a schedule thats then added to the schedule table.

fig(29)

def add_to_schedule(username,position,input_date):

        schedule_date = datetime.strptime(input_date, '%Y-%m-%d').date()

        volunteer = session.query(Volunteer).filter(Volunteer.username == username).one()
        role = session.query(Role).filter(Role.position == position).one()

        schedule = Schedule(
            date = schedule_date,
            vol_id = volunteer.id,
            role_id = role.id
        )
        volunteer.assigned = "Yes"
        session.commit()
        session.add(schedule)
        session.commit()

Modify Schedule

Mary has decided she will not be in church on Sep 3 so she'll need to change the date.

fig(30)

Now let's check the Schedule to make sure the date has been changed.

fig(31)

Yes! The date was changed from 2023-09-03 to 2023-09-10.
Make note of the shedule id 54

Once again CLI validation is complete and informaion is passed to the modify_schedule.

• date is converted to python
• get an instance of the role passed in
• get an instance of the volunteer passed in
• filter on the above information we get an schedule object

Just like we did for modify_volunteer we use the object changes to store the "change data" in key value pairs. Using a for loop we interate over the object using the items() method to seperate the key value pairs. Changes are made based on the key and value stored.

fig(32)

def modify_schedule(username, input_date, role, changes):

        schedule_date = datetime.strptime(input_date, '%Y-%m-%d').date()
        role = session.query(Role).filter(Role.position == role).first()
        volunteer=session.query(Volunteer).filter(Volunteer.username == username).first()

        schedule = session.query(Schedule).filter(Schedule.vol_id == volunteer.id,
               Schedule.date == schedule_date, Schedule.role_id == role.id).first()

        for key,value in changes.items():
            if key == "username":
               user = session.query(Volunteer).filter(Volunteer.username == value ).first()
               print(green(f'\nchanging username from {username} to {value}...\n')) 
               schedule.vol_id=user.id
            elif key == "role":
                new_role = session.query(Role).filter(Role.position == value ).first()
                print(green(f'\nchanging role from {role.position} to {new_role.position}...\n')) 
                schedule.role_id=new_role.id
            else:
                new_date = datetime.strptime(value,'%Y-%m-%d').date()
                print(green(f'\nchanging date from {schedule_date} to {new_date}...\n')) 
                schedule.date=new_date   

Delete Schedule

Mary decided she wants to be removed from the schedule for that day.

fig(33)

Someone forgot Mary had previously changed her date from Sept 3 to Sept 10. fig(31)

fig(34)

Schedule was removed for Mary for Sep 10, 2023.

fig(35)

Notice the schedule id 54 has been removede from the schedule table.

As my husband likes to say...Good Stuff!!!

You see the CLI validation at work in fig(33). The CLI first checked to see if Mary had a schedule for that date. Using a list comprehension we look over the list produced by volunteer.schedules checking the date and removing it when we find a match. Because a volunteer can have more than one schedule we check to see if this is the only schedule by checking the size of the list. If the size = 1 then we know thats the only schedule and we can change the volunteer's assigned status to "No".

fig(36)

def delete_schedule(username, input_date):
        schedule_date = datetime.strptime(input_date, '%Y-%m-%d').date()
        volunteer = session.query(Volunteer).filter(Volunteer.username == username).first()
        [session.delete(schedule) for schedule in volunteer.schedules if schedule.date == schedule_date]
        if(len(volunteer.schedules) == 1 ):
            volunteer.assigned = "No"
        print(f"Removing {volunteer.first_name} {volunteer.last_name} from the schedule for {input_date}... ")
        session.commit()

## Query Schedule by Date

Let's see if anyone else is scheduled for Sep 10, 2023:

fig(37)

After validation the date is passed into query_by_date(). The date is pythonized (I'm sure that's not a word). We get a list of all the schedules that match that date. If the list size == 0 then we know there are no schedules for that date else we loop through the list creating a volunteer and role object for each schedule in the list and print them out.

fig(38)

    def query_by_date(input_date):
        schedule_date = datetime.strptime(input_date, '%Y-%m-%d').date()
        schedules = session.query(Schedule).filter(Schedule.date == schedule_date).all()

        if len(schedules) == 0:
            print (red(f"\nSchedule date: {input_date}\n"))
            print (red("<---No schedule--->"))
        else:  
            print (green(f"\nSchedule date: {input_date}\n"))  
            for schedule in schedules:
                volunteer = session.query(Volunteer).filter(Volunteer.id == schedule.vol_id).first()
                role = session.query(Role).filter(Role.id == schedule.role_id).first()
                print (green(f"{volunteer.first_name} {volunteer.last_name}: {role.position}"))

Let's use a date that actually has somone scheduled:

fig(39)

Query Schedule by Name

Now let see if Mary is on the schedule

fig(40)

Oops! I misspelled her name but the app allowed me to re-enter. As we see Mary does is not on the schedule at all.

Again we see CLI validation at work. I misspelled her name and the CLI caught by checking if the user existed.

Lets see if Kara Rivera is scheduled to serve:

Here the one-to-many relationship is clearly seen between the volunteer and the schedule and the role and the schedule . Kara is serving in multiple roles on multiple dates.

We saw the CLI doing it job validating the information enter in fig(40). We create an object for the username and an object for all volunteers. If the volunteer is not assigned, no need to go any further and we print the full name and the message "No schedule" else let's see if I remember what I did here and why:
for each schedule in the list of schedules for the username were going to
going loop through the roles to see if the role_id matchs the schedule.role_id
Then we check if there is a swappout_id (I'll get to that)
if there is a swappout_id were going to loop through all the volunteers to get the name that matches the swappout.id and print that information out with swappout information
if no swappout_id print without the swappout information.

def query_by_name(username):
        volunteer = session.query(Volunteer).filter(Volunteer.username == username).first()  
        volunteers = session.query(Volunteer).all()
        roles = session.query(Role).all()

        if volunteer.assigned == "No":
            print(red(f"\n{volunteer.first_name} {volunteer.last_name} \n"))
            print (red("<---No schedule--->"))
        else:
            print(green(f"\n{volunteer.first_name} {volunteer.last_name} \n"))
            for schedule in volunteer.schedules:
                for role in roles:
                    if role.id == schedule.role_id:
                        if schedule.swappout_id:
                            for vol in volunteers:
                                if vol.id == schedule.swappout_id:
                                    print(schedule.date.strftime("%B %d, %Y"),":" + role.position, "<" + vol.username + ">")
                        else:
                            print(schedule.date.strftime("%B %d, %Y"), ":" + role.position)  

Swap - Notice the schedule has a swapp_id field. Sometimes volunteers swap dates. For historical purposes swapp0ut_id holds the id of the volunteer who was swapped out.

Giselle Smith

Giselle is a Software Development Engineer FullStack studying at FlatIron School.

Resources:
Disappointed Face: https://gifdb.com/gif/disappointed-face-boy-blink-g97pz8zfml7oypt1.html

Carlton Dance: https://tenor.com/view/carlton-dance-ya-yes-fresh-prince-of-bel-air-carlton-banks-gif-17061920