Ohio Purge Watch Gets a Facelift, “The Owl” Gets Retired

Published / by Scott Dahlstrom / Leave a Comment

I stopped developing, artistically speaking, somewhere around age 10 or 11. Maybe that’s because by the time I was 12 I was diving into the BASIC programming language on my Atari 400 home computer (yeah, I’m that old) and rewiring my brain to predominantly work with logical structures as opposed to creating things that were more oriented toward aesthetics. No matter the reason, whatever biological or psychological trait one needs to look at a blank page and see what could be, I don’t have it.

Ohio + binoculars = owl

Case in point: When I originally set out to come up with some sort of logo for Ohio Purge Watch, I thought I’d just take an image of Ohio and combine it with binoculars.  The result was something resembling the face of an owl.  Sadly, it was the best I could come up with at the time, and the owl was allowed to perch at the top of my site for several weeks.

Today, however, thanks to the generosity of artists who publish their work for the public to use freely,  I am pleased to announce the retirement of the owl.  May this blog post be its final public viewing.  Now, onto the owl’s replacement…

This before and after image gives you a full view of the transformation:

Old site header vs. the new design. Say goodbye to the owl!

Like I said, I’m rather bad at this “making things look pretty” part of web site development, so anytime I’m able to actually overcome my artistic challenges and create something that falls more on the non-hideous side of the spectrum, it’s grounds for a happy dance.  Excuse me while I go privately put my white-boy rhythm on display!


Millersburg – Where More Voters Have the Same Name than Any Other Zipcode in Ohio

Published / by Scott Dahlstrom / Leave a Comment

While working on my Ohio Purge Watch project, I wanted to determine an efficient way for someone to search for their voter registration record without having to type in too much information. The goal was to find a field combination of hopefully no more than 3 fields (2 of those of course being first and last name) that would provide few enough results so they could be displayed on a single page.

I opted to try a zip code, last name, and first name combination, but needed to find out if this would get the number of search results down to a manageable 1-page selection. Running a query which counted voters grouped in such a way, I discovered there is one zip code in Ohio, 44654, which currently has 24 men named David Miller who are registered to vote.

One would assume this anomaly would occur in one of the more highly-populated urban areas of the state, but you’d be wrong. Millersburg, located in Eastern Ohio, has a population of around 3,100 people.

Odds are, if you visit Millersburg and ask any of the residents if they know David Miller, they’ll answer “yes”!

Working with Ohio Voter Data (A Preview of My Latest Project)

Published / by Scott Dahlstrom / Leave a Comment

This line chart, showing the number of weekly purged and added voters to Ohio’s voter roles, is one of many stats available at ohiovoterstats.webscott.com.

My latest project is a public information site which displays data regarding purged voters in my home state of Ohio.

The data is derived using the downloadable voter files available on the Ohio Secretary of State’s website.  The files are updated weekly, and by comparing the voter data in the files for the current week with the ones from the previous week, new and removed voters can be identified.  The simplicity of this concept didn’t exactly equate into a simple implementation as I had initially estimated.  However, the project so far has been as rewarding as it has been challenging and educational.

Challenges of Building the Stats Page

The statistics page (currently the only page available at the time this post was written) shows summaries of all the collected data for the year in various chart and table data formats.  Chart data can be updated to show either statewide data, or specific to a particular county or political district.

The first challenge involved getting the data into a database.  First of all, the files are too big to simply upload through phpMyAdmin.  PHP code was written to handle the insertion.  Second of all, even though this data was being provided by the state, I quickly discovered that the data had some “irregularities”, things like extra quotation marks and backslashes that were causing fields to be improperly parsed when read as csv data.  Additional PHP scripts needed to be written to clean up the data before it could be used.  I should note that much of these irregularities have been corrected in the most recent data downloads offered by the state, so the IT gurus in Columbus have apparently done a little cleanup.

Once I had all the voter data stored in a database, the next challenge was figuring out how to display it.  I’d never built a site with dynamic charts before, and settled on using Chart.js for my first go at it.  I’m still second guessing that decision.  I love the way you can remove sections of a chart.  This feature works great for getting a visual of small slices of a pie chart that are too insignificant to display otherwise.  However, I’d like to have percentages displayed over each slice, rather than just total numbers displayed on a mouseover of each slice, and I’ve not yet figured out a way to do that with Chart.js.  Perhaps a migration to Google charts or some other chart library will be a future upgrade.

Dynamically updating the charts proved to be a fairly painless experience, thanks to some decent documentation by the Chart.js folks, and 1 or 2 supplemental youtube videos.  However, doing so quickly was a bit more problematic.  Querying the data from the database for each chart update, despite my best efforts at optimization, proved to be a method that almost guaranteed sluggish performance.  I had one advantage though, and that was that I had a finite number of sql queries that needed to be run to generate the chart data for all counties and districts.  I decided to run all those queries in a batch process, and store the results in json files.  When a user selects a county or district, the appropriate json file is retrieved, and the database is left out of the process.

The next improvement on the to-do list for the stats page will be a better user interface.  Currently, the charts are updated by clicking on one of the counties or districts located in the tables below the chart.  In other words, the user interface is located on a separate portion of the page than the data results.  I need to create something in the chart area that will give this functionality without the need to scroll down the page. 

Update 3/20: The user interface has been implemented!

Further features planned for this project include a “purge alert” service, where Ohio voters can sign up for an email alert in the event they are purged from the voter rolls.  Once that’s developed, I’ll need to create a regular home page, contact page, maybe some faq’s, a nav menu to link them all together, and in the end I should have a real website!


Preventing Backorders on Product Variants in Sparkpay using PHP

Published / by Scott Dahlstrom / Leave a Comment
Discontinued Product with Variants on Sparkpay

If both the Discontinued and Enable variant level inventory boxes are checked, this probably won’t work the way you want it to.

Sparkpay (formerly Americommerce) has an option to prevent backorders on the products you list on your store.  Unfortunately, it has some flaws. For example:

Let’s say one of your items is a t-shirt with 3 sizes: small, medium, and large.  You only have a limited number of each size available, and don’t want to oversell them.  In order to prevent this, you check the Discontinued box on the inventory page. This will change the product status to discontinued once the inventory reaches zero.

The problem with this method is that it is based on product inventory, not variant inventory.  In other words, if you have 3 large, no medium and no small shirts, the medium and small shirts will still show as available to purchase until the total of all 3 variants reaches zero.  This also means that if you sell 3 of the sizes you don’t have, the large shirts will become unavailable as well, even though they are still in stock.

For stores that sell items with limited availability, Sparkpay’s limited ability to prevent overselling of those items can cause a lot of unnecessary customer service problems.  Fortunately, there is a way to correct the issue of product vs. variant inventory using the Sparkpay REST API and a little PHP.

In order for this process to work, however, you’ll need to have 2 order statuses for new orders.  For this example, I’ll be using “Submitted” and “Pending Processing”.  Submitted will be the default status for new orders, and the scripts we’ll be using will look for orders with this status.  Once those orders have been checked, their order status will be changed to Pending Processing.

You will also need to create an access token with, at a minimum, permission to view and change order data, and to view and change catalog data.  Instructions for doing so can be found in the Sparkpay documentation here: https://github.com/SparkPay/rest-api/blob/master/authentication.md#generating-a-token-from-the-admin-console

While the following scripts will not prevent overselling altogether, they will help to minimize it.  In other words, we can’t prevent someone from adding 5 small t-shirts to their order when we only have 4, but we can prevent the next person from ordering more.

So let’s get started!  Below is the class we’ll use to connect to the Sparkpay REST API.  Keep in mind, this was originally written when Sparkpay was still Americommerce – hence, all the “AC” prefixes.

Our class contains 3 methods: a constructor, sendGetRequest, and sendPutRequest.  Get and Put are the only 2 request types we’ll be sending, but you could easily add a sendPostRequest method if you needed to send a Post request.

The constructor assigns the access token to the class property token.  You can set this via the constant at the top of the script, or you can pass a token into the constructor which will override the default token set in the constant.  If you plan on using this class for several API functions, and want each function to use a separate token, passing the token to the constructor is the way to go.

Next is the sendGetRequest method, which takes up to 3 parameters.  The first, $resource, is used to identify the resource you wish to access.  This will generally be set to either products or orders.  Next is the $query parameter, which should receive an array containing key=>value pairs for any search criteria in the request.  These array elements will be url-encoded and added as a querystring to the url.  The last parameter, $fields, is used to specify which fields you wish to be returned in your request, and should be passed as an array containing valid field names.

The sendPutRequest method also takes 3 parameters.  However, unlike the sendGetRequest method, which will work with only the resource specified, this method requires all 3 parameters.  The first parameter, $resource, specifies the resource you’re trying to update (products, orders, etc.).  The next parameter is the identifier for the specific object within that resource, and $data holds an array containing the data we will specify to update the object.

You may notice in both the sendGetRequest and sendPutRequest methods, I’ve used a couple of not-so-common elements of PHP, namely the sleep function and the dreaded goto operator.  These come into play in the event of a 429 error, which means that we’ve exceeded our limit on the number of requests sent to the API within a given time frame.  In the case of Sparkpay, this time frame is 10 seconds.  I’ve set the sleep function for 11 seconds for good measure, but you could probably reduce it to 10 without issue.  After the sleep function, I’ve used the goto operator to ensure that we’re sending the same request as the one that timed out.

So that’s our connector.  Now, on to the backorder blocker!  Here’s the class for that:

Here, we instantiate the Ac_rest class, and assign the object to the ACRest property.  From there, everything is done via the runDiscontinuedCheck method.  The logic of this method is fairly straightforward and can be explained like this:

  1. Collect the order id’s of all new orders (indicated by their order status).
  2. Collect the product and variant id’s of all items in those orders that are marked as discontinued and have a quantity on hand equal to or less than zero.
  3. Update the status of those products and/or variants to a discontinued status so that they can no longer be added to a customer’s cart.
  4. Update the status of the new orders so they are not collected the next time the script runs.

There are 3 lines in this class where your statuses may differ from what I’ve used.  The first is line 19, where the status for new orders is specified.  If you’re using something other than Submitted for your status, simply change the parameter passed to the getOrderStatusByName method here.

The second place where your order status may differ is on line 43.  This is the status you’ll use for orders that have been checked for discontinued items.  This way, they won’t be pulled into the process the next time.

The third, and least likely place you’ll need to change the status is on line 130.  This line retrieves the discontinued product status. If you’re using something other than Discontinued (the Sparkpay default) for your discontinued products, you’ll change that here.

The Ac_rest and Backorder_blocker classes contain all the functionality you’ll need to stop excessive backorders from occurring, and the process is launched with just a few quick lines of code as shown in the file below:

Once you have all 3 files available on a public web server, there’s just one more step.  You’ll need to set up a rule in Sparkpay to call Update_discontinued_variants.php whenever a new order is placed or changed.  The instructions for setting up a new order rule can be found here: https://support.sparkpay.com/hc/en-us/articles/201903920-Rule-Engine-What-are-Order-Events-and-How-is-it-Setup-

For this order rule, you’ll want to use the following settings:

  1. In the “Respond To” section, check the boxes for “A new order is placed or created” and “An existing order is updated”.
  2. For Conditions, you want to test if the order status is equal to “Submitted” or whatever status you used for new orders.
  3. Under Actions, you’ll select “Request Url”, and then enter the Url to call the Update_discontinued_variants.php script.

That’s it!  Now, whenever a new order is placed or updated (and set to a Submitted status), the script will be called to check if any of the items in the order are sold out and should be discontinued.