Author: Paul Ericksen

Hello and welcome to Mr Ericksen’s home on the web. This isn’t so much a portfolio of my work as it is a place for me to archive my projects and processes while providing a convenient way for me to share them with others who may be interested in using them.

If you’re just browsing around, Technotes are tech-related projects I’ve worked on as an IT Director and EdSys Admin, Travel Hacks is a collection of things I’ve found helpful during since moving abroad in 2006, and Pro Tips are some short tips for making daily life a little less stressful.

Long story short? I made an RPi sound clip player using a tft touchscreen, pygame, and movie quotes found on YouTube. It starts with a small, 3.5″ touchscreen whose drivers are installed using the instructions here. For your convenience, they are replicated below.

Just enter these lines into Terminal to get the screen set up.

sudo rm -rf LCD-show
git clone https://github.com/goodtft/LCD-show.git
chmod -R 755 LCD-show
cd LCD-show/
sudo ./LCD35-show

This should get the screen running properly. If you have issues with axes being reversed on the screen (like you touch the right side and the cursor shows up on the left) the page linked above has some suggestions. If those don’t help, check out Teng Fone’s post on Medium.

OK, now (hopefully) the screen is working and we can work on the code. I used code from Garth Vander Houwen’s Pi tft menu project as a starting point, added some audio files, and came up with a project that presently shows this screen on my Raspberry Pi:

You might guess some (or all) of the audio files I used. If not, you can download them from here.

The Python script to get it all running is below (or access it here). Connect the audio output to your speakers or headphones and click away. Examining the code should make it relatively easy to modify it to change the sounds, number of options, etc.

import sys, pygame, time, subprocess, os
from pygame.locals import *
from pygame import mixer
from subprocess import *

os.environ["SDL_FBDEV"] = "/dev/fb1"
os.environ["SDL_MOUSEDEV"] = "/dev/input/touchscreen"
os.environ["SDL_MOUSEDRV"] = "TSLIB"

# Initialize pygame modules individually (to avoid ALSA errors) and hide mouse
pygame.font.init()
pygame.display.init()
pygame.mouse.set_visible(0)
pygame.display.toggle_fullscreen

# define function for printing text in a specific place with a specific width and height with a specific colour and border
def make_button(text, xpo, ypo, height, width, colour):
    font=pygame.font.Font(None,42)
    label=font.render(str(text), 1, (colour))
    screen.blit(label,(xpo,ypo))
    pygame.draw.rect(screen, blue, (xpo-10,ypo-10,width,height),3)

# define function for printing text in a specific place with a specific colour
def make_label(text, xpo, ypo, fontsize, colour):
    font=pygame.font.Font(None,fontsize)
    label=font.render(str(text), 1, (colour))
    screen.blit(label,(xpo,ypo))

# define function that checks for touch location
def on_touch():
    # get the position that was touched
    touch_pos = (pygame.mouse.get_pos() [0], pygame.mouse.get_pos() [1])
    #  x_min                 x_max   y_min                y_max
    # button 1 event
    if 30 <= touch_pos[0] <= 240 and 30 <= touch_pos[1] <=85:
            button(1)
    # button 2 event
    if 260 <= touch_pos[0] <= 470 and 30 <= touch_pos[1] <=85:
            button(2)
    # button 3 event
    if 30 <= touch_pos[0] <= 240 and 105 <= touch_pos[1] <=160:
            button(3)
    # button 4 event
    if 260 <= touch_pos[0] <= 470 and 105 <= touch_pos[1] <=160:
            button(4)
    # button 5 event
    if 30 <= touch_pos[0] <= 240 and 180 <= touch_pos[1] <=235:
            button(5)
    # button 6 event
    if 260 <= touch_pos[0] <= 470 and 180 <= touch_pos[1] <=235:
            button(6)
    # button 7 event
    if 30 <= touch_pos[0] <= 240 and 255 <= touch_pos[1] <=310:
            button(7)
    # button 8 event
    if 260 <= touch_pos[0] <= 470 and 255 <= touch_pos[1] <=310:
            button(8)

# Define each button press action
def button(number):
    print("You pressed button", number)

    if number == 1:
        #time.sleep(0.2) #do something interesting here
	mixer.init()
	mixer.music.load('Illbeback.mp3')
	mixer.music.set_volume(1)
	mixer.music.play()
	time.sleep(5)
        #sys.exit()

    if number == 2:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('wickedsmart.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 3:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('thatlldopig.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 4:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('killingmesmalls.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 5:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('failuretocommunicate.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 6:
        #time.sleep(5) #do something interesting here
        #sys.exit()
	mixer.init()
        mixer.music.load('goodafternoon.mp3')
        mixer.music.set_volume(1)
        mixer.music.play()
        time.sleep(5)

    if number == 7:
        time.sleep(2) #do something interesting here
        #sys.exit()

    if number == 8:
        time.sleep(5) #do something interesting here
        sys.exit()

#colors     R    G    B
white   = (255, 255, 255)
red     = (255,   0,   0)
green   = (  0, 255,   0)
blue    = (  0,   0, 255)
black   = (  0,   0,   0)
cyan    = ( 50, 255, 255)
magenta = (255,   0, 255)
yellow  = (255, 255,   0)
orange  = (255, 127,   0)

# Set up the base menu you can customize your menu with the colors above

#set size of the screen
size = width, height = 480, 320
screen = pygame.display.set_mode(size)

# Background Color
screen.fill(black)

# Outer Border
pygame.draw.rect(screen, blue, (0,0,480,320),10)

# Buttons and labels
# First Row
make_button("I'll be back", 30, 30, 55, 210, blue)
make_button("Wicked Smart", 260, 30, 55, 210, blue)
# Second Row
make_button("That'll Do", 30, 105, 55, 210, blue)
make_button("Killing me", 260, 105, 55, 210, blue)
# Third Row
make_button("Failure", 30, 180, 55, 210, blue)
make_button("Good Night", 260, 180, 55, 210, blue)
# Fourth Row
make_button("Don't do nothin", 30, 255, 55, 210, blue)
make_button("Exit", 260, 255, 55, 210, blue)

#While loop to manage touch screen inputs
while 1:
    for event in pygame.event.get():
        if event.type == pygame.MOUSEBUTTONDOWN:
            pos = (pygame.mouse.get_pos() [0], pygame.mouse.get_pos() [1])
            on_touch()

        #ensure there is always a safe way to end the program if the touch screen fails
        if event.type == KEYDOWN:
            if event.key == K_ESCAPE:
                sys.exit()
    pygame.display.update()
    ## Reduce CPU utilisation
    time.sleep(0.1)

We had tried a few years ago to get some ancient MacBooks up and running with various Linux distros. But our particular model had challenging WiFi hardware which made it impractical.

I recently took another shot at reinvigorating our Early 2011 MacBook Pros with Ubuntu and found that a few terminal commands would get things working. I used Andy Bleaden’s steps from this AskUbuntu answer. I’ve pasted the steps below to ensure I always have ready access to it.

From Andy Bleaden:
I always recommend removing and reinstalling the broadcom drivers using your terminal

In a terminal type the following command

sudo apt-get purge bcmwl-kernel-source

then

sudo apt-get install bcmwl-kernel-source

This will then rebuild your driver.

You can either restart your pc or if this is a pain type the following commands in the terminal which will ‘switch on’ your wireless

sudo modprobe -r b43 ssb wl

then

sudo modprobe wl 

Suppose a student tests positive for COVID-19. Here’s how we quickly identify potentially exposed classmates. There’s a sample spreadsheet here that you can use. Just make a copy, follow the steps below and paste the export into cell A1 of the PS_DataDump tab. If that link breaks, you can download an Excel version here.

1. In DDE, select the CC Table
2. Search for current TermID and StudentID
3. Switch over to Sections Table
4. Search for Sections in current term
5. Match Selection on CC table
6. Switch to CC Table
7. Select all enrollments from current term
8. Match Selection with Sections Table
9. Export Records with the following records:

[1]Student_Number
[1]ID
SectionID
Course_Number
Expression
[2]Course_Name
[1]First_Name
[1]Last_Name
[1]Grade_Level
[1]Gender
[1]Home_Room
[183]Student_EMail
[5]First_Name
[5]Last_Name
[5]Email_Addr
[1]MotherDayPhone
[1]FatherDayPhone
[1]cnt1_email
[1]cnt2_email
[1]GuardianEmail

In our ever increasing efforts to protect student confidentiality and personal information, we password protect student report cards and test results when emailing them to parents. This helps protect the information in the unlikely event that an email gets sent to the incorrect address.

To do this, we generate the reports from PowerSchool or Google Docs, typically in a Firstname Lastname grade # Progress Report.pdf format. These are put into a folder (in this case, the folder is ES_T3_PDFs) there is also a filedata.csv file that has each file’s password in the first column, the original filename in column 8, and the new filename in column 9.

The python script below then runs, opens each report, creates a new file object, password protects it, and writes it to a new folder (Secure_ES_T3_PDFs).

import PyPDF2
import csv
import sys
#Open csv with password,filename,newfilename
c = open('filedata.csv', 'r')
# Create a reader object to store the data in filedata.csv
reader = csv.reader(c, delimiter=',')
# Process each row of data
count = 0
for row in reader:
    # The password located in the first column
    password = str(row[0])
    # The current (original) filename in "firstname lastname grade # Progress Report - Student_Number" format
    currFileName = row[7]
    # New filename is the same as original but without the Student_Number 
    newFileName = row[8]
    # Skip the header row
    if (password != "Password"): # Skip the first row with "Password" in first column
        # print row # every 10th row - just to monitor progress
        if (count % 10 == 0):
            print(count)
        # Open non-encrypted file
        pdfFile = open("PasswordProtect/ES_T3_PDFs/"+currFileName, 'rb')
        #coverLetter = open("PasswordProtect/coverLetter.pdf", 'rb')
        # Create reader and writer objects
        pdfReader = PyPDF2.PdfFileReader(pdfFile)
        #pdfReader02 = PyPDF2.PdfFileReader(coverLetter)
        pdfWriter = PyPDF2.PdfFileWriter()
        # The next 2 lines put the welcome letter at the beginning of the new file
        #print("Inserting Cover Letter")
        #for pageNum in range(pdfReader02.numPages):
        #    pdfWriter.addPage(pdfReader02.getPage(pageNum))
        # Add all pages to writer for each page in input file, add it to the output file
        for pageNum in range(pdfReader.numPages):
            pdfWriter.addPage(pdfReader.getPage(pageNum))
        # Encrypt with password
        pdfWriter.encrypt(password)
        # Write it to an output file
        resultPdf = open("PasswordProtect/Secure_ES_T3_PDFs/"+newFileName, 'wb')
        pdfWriter.write(resultPdf)
        resultPdf.close()
    count += 1

Peculiar enrollment situations can lead to issues when permanently storing grades at the end of a term. For instance, if a student changes from one section of a course to another during a term, one grade could get stored for each section if the “Exclude enrollments” dates are not selected wisely.

While we keep a file of “Special Cases” to review at the end of each term, but there are often such cases that sneak by us. To catch them, I track down all enrollment anomalies before storing grades.

To do this, I use DDE (…/admin/tech/dde/):

In DDE:

• Select the CC table
• TermID >= current year term (2900, 3000, 3001, etc.)
• SchoolID = 200 for MS or 300 for HS (in our case)
• DateEnrolled >= “A couple weeks after the start of the term”
• Export to a spreadsheet
• Sort by course name and delete non-courses: clubs, sports, A-Block, etc.
• Sort by DateEnrolled and look for any peculiar dates later in the term
• Decide appropriate cutoff date for “enrolled… after”
• Note any enrollments that may be improperly included or rejected for later followup
• Repeat above steps changing DateEnrolled to DateLeft and find an appropriate time for “dropped… before”

I typically check “Exclude … dropped before…” about a week before the end of the current term. Checking for outlier dates can help identify individual students and enrollments that may need attention.

NOTE: This project started out small, simple, easy-peasy. It has expanded well beyond what can comfortably be contained in a single blog post. If you’re looking into setting up air monitoring on your campus, I would be very happy to help you as much as you’d like – from a general “Here’s a website to look at” to a complete “Here’s a fully functional pm2.5/pm10/temp/humidity/barometric pressure sensor and dashboard”, I’m probably the guy you want to talk to.
My current setups use $75 DYI sensors as well as a multi-thousand dollar commercial sensor, Python scripts, api calls, HTML, CSS, Google Sheets, and JavaScript to create responsive displays showing current and recent AQI readings. Let me know if you have any questions or would like some help. Otherwise, feel free to continue on to the post I made a few years back . . .

South Korea doesn’t have the worst air in the world, but it’s also no Chippewa Falls, Wisconsin. Recognizing that very bad air days are a threat to the health of our students and staff, our school developed a systematic plan to monitor the air we breath and to filter bad air when necessary. As part of this effort, several staff, faculty and students worked together to build a network of air quality monitoring stations around campus to collect timely data, share current data with stakeholders, and record data long-term for research and future planning. These devices are surprisingly accurate except during times of high humidity when the SDS011’s readings become unreliable. Ultimately, the school’s brass decided to go with a commercially available system for it’s needs. But I have preserved the following explanation of how we built the devices and installed the software to disseminate current data and stored data for future use.

We built a variation of the Sensor.Community’s project. This uses the Plantar SDS011 PM2.5/PM10 particulate matter detector, BME280 temperature/humidity/pressure monitor, the WEMOS D1 Mini ESP8266 development board and a super small breadboard to bring it all together. Note that these items can also be commonly found virtually anywhere that sells microelectronics components and they’re inexpensive. The SDS011 sensor is around $17, the others are a few dollars each.

Setup is fairly straightforward. We attach headers to the Wemos D1 Mini and the BME280. Then we connect the 5V pin of the D1 to the 5V pin of the SDS011,

Wiring Connections:

Once everything is connected, we’ll want to flash the Wemos D1 Mini’s code. Visit the Sensor.Community’s download page to retrieve the appropriate version. Connect the Wemos D1 to your computer, and run the flashing program to set it up. If your computer does not recognize the Wemos D1, you may need to install the driver to see it. The driver page is mostly in Chinese, so you will need to look closely to discern which version is needed for your OS.

Once flashed, you’ll need to reboot the device by disconnecting from your computer and then reconnecting – either to your computer or another micro-USB power source.

The first time it restarts, it won’t know how to connect to your local WiFi, so it creates its own WiFi network. On your computer, look for a new SSID that begins with Feinstaubsensor-ID or airRohr-ID followed by a chip ID such as 13597771. Connect to that SSID and note the chip ID as it will be needed later. Once connected, point your browser to 192.168.4.1 and you should see the device setup page. From here, you can select your preferred language, enter your local WiFi network name and password, and identify the sensors connected to the device. Be sure to click the “Zurük zur Starseite” button to save the settings and restart the device.

There are are a couple ways to check your sensor readings. You can access your device directly on your local network if you can identify and visit its local ip address. Alternatively, you can create an account at https://sensor.community to conveniently access your device data online. You’ll need the Chip ID you wrote down earlier to find your sensors. But once your account is set up, you’ll be easily able to bookmark your device to see your local air quality, temperature and humidity conditions.

We put together a disc golf tournament scoring system using a Google form and some spreadsheet magic for golf tournament organizers. The organizer can enter the number of holes, teams, and golfer names into a spreadsheet which creates a convenient alphabetized list of golfers’ names. This list of names can be copied and pasted into a pre-made Google form.

The form allows golfers or managers to select a golfer and enter score(s) for any hole(s) that have been completed. The form’s spreadsheet automagically tallies the scores for each player and team, creating tables for both individual and team leaderboards. If an error is made entering any score(s), one can just go back and re-enter the score for the affected hole(s) and all is good again. The spreadsheet uses the most recent score for each golfer on each hole and ignores previous entries.

Instructions are found below and can be found in this Google doc.

Note: do not edit anything except what is in the instructions below. There are a few extra sheets (Leaderboard, CurrentUnsortedScores, SubmissionsByReverseTime) that are made up entirely of formulas using data from the ManagementData and Form Response tabs. If anything is wrong, it is almost certainly in the ManagementData tab not in the other sheets.

1. Click this link to make a copy of the form
2. Click this link to make a copy of the spreadsheet
3. In the form, click on the “Responses” link, then the green spreadsheet icon on the right, and click “Select response destination”.
   
4. Select the spreadsheet that you copied from the link above
5. When you selected the response spreadsheet in the previous step, a new tab was added. In my example below, it is named “Form Responses 2”.In order to get the formulas in the several tabs to work with your data, we need to copy the data from the new tab (Form Responses 2 in my case) to the “Form Responses 1” tab. This is how to do this:
   
   1. 1. Open the “Form Responses 1” tab
      2. Delete all the data in this tab. Just click in a cell, Command-A and hit the delete button.
      3. In cell A1 of the Form Responses 1 tab, enter the following. If necessary, update the ‘Form Responses 2’ part with the name of the new tab in Step #3 above:
         
         =QUERY(‘Form Responses 2’!A1:T,”SELECT *”)

   
   This formula imports everything from the Form Responses 2 tab into the Form Responses 1 tab from which all other formulas pull data.

6. Now, in the spreadsheet’s “ManagementData” tab, type the number of holes for your course in B1.
7. Still in “ManagementData” type team names in cells B2:K2 (Currently Group One, Group Two…) up to 10 teams, deleting any leftovers.
8. Under each team name, type each team’s member names (Currently Dave Archer, Matt Quade…)up to 10 golfers per team, deleting any leftovers:
   
9. Once all players names have been entered, copy the alphabetized list in column M of the ManagementData tab and in the form, paste these into the answers list for the “Name” question. This is critical!:
   1. Copy all of the names in the alphabetized list (M2:M? in ManagementData)
   2. In the form, delete all but the last name listed in the Name question.
   3. Highlight that one remaining name and “Paste” the names you just copied.
   4. The only choosable responses to the Name question should be the golfers you just pasted. If not, edit them as needed.
10. Your tournament should now be ready to roll.
11. “Send” the form to your golfers and watch the results in the “Leaderboard” tab of the spreadsheet.
12. If you share the spreadsheet with your golfers, PLEASE only share it as “read-only”. If people who don’t know better start going in trying to correct scores on their own, they’ll screw it up in an unrecoverable fashion.

Troubleshooting

Problems? Let’s look at what might be happening.

1. If you have extra names showing up in your form, you prolly didn’t follow steps 9.1-9.4 exactly right. The short version is that in column M of the ManagementData tab, you should see an alphabetized list of your golfers’ names. An alternative method of getting where you need to be is to copy this list to the clipboard, go to edit your form, click in the first name in your “Name” choices and “paste” your names. Now, just go through your list and delete any duplicates or incorrect golfer names.
2. Still stuck? Well, just leave a comment on this post. If I’m able, I’ll get back to you asap. If you’re willing to make me an editor on your form and sheet, I can probably help fix it for you.