Zoo, Aquarium & Garden Buyers GroupDue to the sheltering orders and travel restrictions placed in response to the COVID-19 pandemic, the ZAG Board has proposed the amendment posted here. It is available in both .pdf and .docx formats.
ZAG members should return this document with their vote to Judy Rancour by October 17, 2020.
This post will help to document the order processing cycle for the ZAG website. Unless you have an insatiable interest in data procedures or are following in my footsteps, you can safely ignore the rest of this post.
For the purposes of this post, I assume you have at least an introductory familiarity with email, spreadsheets, and the WordPress dashboard. I usually work with 2 side-by-side browser windows: one for email and one for WordPress. Opening the master spreadsheet in a half-height window makes it easy to copy and verify between the order and the spreadsheet.
In the instructions below, I usually open links in new tabs. This is not strictly necessary, but it helps me to be able to switch views in the middle of processing without losing my place or any incomplete work.
Please note that some tab or field names may change in future plugin updates.
After a buyer or sponsor completes an order, the WooCommerce plugin sends an email to ZAG Board members and the website administrator.
On the WordPress dashboard, you will find orders under WooCommerce. There are four status flags of interest:
The steps to add a sponsorship are nearly identical to those for handling buyers with the following additions or exceptions:
Once the record is completed on the spreadsheet, copy the formula from another sponsor in column U. Here is the current formula for a sponsor record in row 8:
=CONCATENATE("<p><strong>",H8,"</strong><br><em>",T8,"</em><br>",Q8,"<br><a rel=",CHAR(34),"noreferrer noopener",CHAR(34)," aria-label=",CHAR(34),S8," (opens in a new tab)",CHAR(34),"Beginning in January 2020, the sponsor formula will become:
=CONCATENATE("<p><strong>",H8,"</strong><br><em>",T8,"</em><br>",Q8,"<br><a rel=",CHAR(34),"noreferrer noopener",CHAR(34)," aria-label=",CHAR(34),S8," (opens in a new tab)",CHAR(34)," href=",CHAR(34),"http://",S8,CHAR(34)," target=",CHAR(34),"_blank",CHAR(34),">",S8,"</a><br><em>Sponosr through ",TEXT(G8,"mmm YYYY"),"</em></p>")Edit the ZAG Sponsors page and add a block in the correct location. The page lists sponsors alphabetically in two lists: Gold Level and Annual. In the new block:
This will give you a consistently formatted record for the sponsors with a website link that opens in a new tab.
Occasionally, someone will create a new record for themselves. Since email addresses are attached to the user ids, this can only happen if there is a new email address or there was a typo in the original record. This happens often enough that I always check for other buyer records from the same institution.
If there is an existing record in the spreadsheet, I update that row with the new information, including the new user record number in column A. In WordPress Users, I delete the record that isn’t needed, setting the “Attribute all content to:” selection to the user id we’re keeping.
Sponsors may have multiple team members who need access to the ZAG website. To handle these with the fewest complications, I’ve created a “secondary” membership without a WooCommerce product. This has several benefits:
For secondary memberships, you can create the records manually from a list provided by the sponsor. You could also ask them to create a free buyer membership order, but fulfill it with a secondary membership instead.
The key issues here are:
This was presented at the 2019 ZAG meeting, as well as at the September 2019 AZA meeting.
Starting today, I will be migrating the ZAG website to a new theme. While I have done most of the work in a staging site, there may be some issues while the update is in progress.
Executive Summary:
We are moving from an outdated commercial theme. We cannot update this theme unless we purchase a new subscription. Additionally, the commercial theme has several dependencies that have their own subscription costs.
The new theme is based on Twenty Twenty, the new default WordPress theme. Because Twenty Twenty is the new default theme, there are no licensing fees.
After the new theme has been live for a few weeks, I will back up the website and delete all the outdated plugins and themes.
Administrator Notes:
In addition to the substantial reduction in subscription costs, the new theme allows us to remove several plugins from the site. This greatly simplifies troubleshooting and site maintenance.
Another benefit is that the new theme was designed for the Gutenberg block editor, with an eye toward responsive design and Google’s AMP initiative. This allows us to remove several CSS overrides needed to make the previous theme work better on tablets and phones.
The new twenty-twenty-zag child theme only modifies 4 source files. I have included diffs, along with the original versions, in the child theme directories.
There is also a modified source file for The Events Calender list widget.
I will provide more thorough documentation of the code changes in a future website design post.
Quick links:
You may think it’s EAV, but it’s not…
TL;DR – Straight to the SQL queries
… and into a spreadsheet
When I became the website administrator in November 2018, my first task was to produce an updated buyer list for our sponsors. You would think this should be a relatively easy task because all of the data we need is in the website database.
That’s what I thought. I was wrong.
The most recent buyer list was a CSV exported from WooCommerce Memberships. This was unsatisfactory for two reasons:
I naively assumed that I should be able to write a simple SQL query to the database, something along the lines of:
SELECT * FROM customers, members
WHERE customers.id = members.customer_idAlas, ’twas not to be.
I discovered that the WordPress user data is organized into an Entity – Attribute – Value scheme. Except that it isn’t a pure EAV model.
Ideally, an EAV database has three tables:
EAV tables tend to be narrow, but long. They are perfect for cases where not every record has the same attributes. A traditional 2-dimensional table such as a spreadsheet would be very wide and have many empty fields. EAV models are compact, and flexible. Adding a new attribute just requires a new record in the attribute table. You don’t rewrite the schema or waste space adding empty fields to every preceding record.
Unfortunately, the WooCommerce contact information isn’t stored in a pure EAV scheme. There is no attribute table: instead the attribute name is stored in the value table itself.
This does have the advantage in that it greatly simplifies the query logic. EAV databases are complex to query because you need to join 3 tables. But working with 2 tables in an e-commerce environment, you can quickly code for each field in a form:
SELECT * from customers
WHERE userid = n
AND attribute = "billing_address_1"On the other hand, excluding the attribute table wastes a lot of space: the attribute name is repeated every time it is used. (For example, the attribute name “billing_address_1” requires 16 characters, while a typical integer index requires only 4 bytes.)
(Granted, I’m showing my age here. I started programming when both RAM and disk space were limited. While you can argue that modern tools make my conservative approach to memory management obsolete, I still feel a great deal of personal satisfaction in efficient, lean code.)
In the end, the quickest way to generate the buyer list from the website tables was to use two SQL queries, then manipulate the data offline as needed. Here are the queries, using generic table names for security:
SELECT ID, user_login, user_email, user_url, display_name
FROM `wp_users`SELECT user_id,
MAX(CASE WHEN meta_key = 'billing_company' THEN meta_value END) AS company,
MAX(CASE WHEN meta_key = 'billing_first_name' THEN meta_value END) AS first_name,
MAX(CASE WHEN meta_key = 'billing_last_name' THEN meta_value END) AS last_name,
MAX(CASE WHEN meta_key = 'billing_address_1' THEN meta_value END) AS address_1,
MAX(CASE WHEN meta_key = 'billing_address_2' THEN meta_value END) AS address_2,
MAX(CASE WHEN meta_key = 'billing_city' THEN meta_value END) AS city,
MAX(CASE WHEN meta_key = 'billing_state' THEN meta_value END) AS state,
MAX(CASE WHEN meta_key = 'billing_postcode' THEN meta_value END) AS postcode,
MAX(CASE WHEN meta_key = 'billing_country' THEN meta_value END) AS country,
MAX(CASE WHEN meta_key = 'billing_phone' THEN meta_value END) AS phone,
MAX(CASE WHEN meta_key = 'billing_email' THEN meta_value END) AS email
FROM `wp_usermeta`
GROUP BY user_idFull disclosure: my database chops were a bit rusty. Fortunately, we live in an age of search engines and I was able to find an example which let me quickly write the above query.
With these simple 2-dimensional tables in hand, I was able to quickly create a combined table in a spreadsheet. This spreadsheet is now the master buyer and sponsor list for the ZAG website. There are of course, pros and cons to this process:
Pro
Con
While it would be easier to let our members update their contact information themselves, the presence of many hands without validation rules guarantees inconsistencies in the presentation of the data.
In the end, for flexibility and data integrity, we’re using an offline spreadsheet to maintain our member and sponsor data.
In the next post, I intend to document the order processing cycle.
This is the first in a series of posts which will attempt to document the design decisions and administrative processes for the ZAG website. The goal of these posts is to allow a new administrator to pick up where I left off – to make the site self-documenting.
By sharing the thought processes behind these decisions, I hope to help my successors to avoid pitfalls I’ve already encountered and to make choices that are at least as good as what I’ve done, if not better.
Hopefully, other WordPress users might find value in these posts as well.
In any well-designed product, form follows function. In a perfect world, you’d have a prioritized list of goals and desires. So, even though I took over a site that was already in production, let’s attempt to identify the goals for the ZAG website:
Our first constraint is the triad: “Good, fast, or cheap: pick two.”
Given that the ZAG website was already running, my main task in the winter of 2018-2019 was to get up to speed with what was already in place and to process the backlog of memberships and sponsorships. “Fast” was the order of the day. Going forward, however, my choice from the triad is “Good and Cheap”.
Our next constraints are due to the prerequisites for ZAG membership:
The net result is that we have a complex set of criteria that are not easily reduced to a simple algorithm for automated membership activation.
In the next post, I’ll document the generation of the membership lists.
