https://www.bikesaviours.org/

Random Notes

• Grants
  • The Downtown Phoenix library has access to Foundation Directory Online: https://candid.org/find-us
  • Supposedly you can access it here as well: https://fconline.foundationcenter.org/welcome/quick-start
  • More Sources: https://getfullyfunded.com/5-places-to-find-grants-for-your-nonprofit-for-free/
• SubReddit: https://old.reddit.com/r/BikeSaviours/

Database Notes

Updating Sign-Ins

1. Update the spreadsheet: sign-ins_2021.ods in Marketing > Market Data > Sign-Ins: https://drive.google.com/drive/folders/1WUrkLeC3LK7cjh1Me8UiMOWWSohZLWaU?usp=sharing
2. Export the month's sheet as a CSV.
3. $ ./parse-sign-in-csv.pl 2021-12 > sql/2021-12.sql
4. $ mariadb bsbc < sql/2021-12.sql
5. $ mariadb bsbc
6. > -- only safe because we only had one each Shamus, Nicole, Denzel, or Adam
7. > update bsbc.staff_sign_in i set i.email = 'shamus@bikesaviours.org' where i.short_name = 'Shamus';
8. > update bsbc.staff_sign_in i set i.email = 'nicole@bikesaviours.org' where i.short_name = 'Nicole';
9. > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Denzel';
10. > update bsbc.staff_sign_in i set i.email = 'intern' where i.short_name = 'Adam';
11. > -- this is dangerous
12. > set @date = '2021-12-02';
13. > select i.shop_date, i.short_name, i.email from bsbc.staff_sign_in i where i.shop_date = @date;

Find Dates Missing Assignments

select shop_date, count(1) num_staff from bsbc.staff_sign_in
where email is null group by shop_date order by num_staff asc;

Set Target Date

set @date = '2021-12-02';

Find Possible Matches

select i.shop_date, i.short_name, i.email, n.email, n.first_name, n.last_name, n.time_range
from bsbc.staff_sign_in i
join bsbc.staff_sign_up_name n on n.shop_date = i.shop_date
where i.shop_date = @date
and i.email is null
;

Find Proposed Matches

select i.shop_date, i.short_name, i.email, n.email, n.first_name, n.last_name, n.time_range
from bsbc.staff_sign_in i
join bsbc.staff_sign_up_name n on n.shop_date = i.shop_date
join bsbc.staff_short_name s on s.email = n.email and s.short_name = i.short_name
where i.shop_date = @date
;

Add Short Name Link

Update the short name table statement that looks like this:

truncate table bsbc.staff_short_name;
insert into bsbc.staff_short_name (email, short_name)
values ('nicole@bikesaviours.org', 'Nicole'),
...
       ('shamus@bikesaviours.org', 'Shamus');

Create Short Name Links

After previewing the proposed assignments for correctness.

update bsbc.staff_sign_in i
join bsbc.staff_sign_up_name n on n.shop_date = i.shop_date
join bsbc.staff_short_name s on s.email = n.email and s.short_name = i.short_name
set i.email = n.email
where i.shop_date = @date
;

Non-Signup Linked Update

WARNING: High Voltage: Validate Data Extremely Carefully

There is a high risk of duplicate short names causing data corruption. Validate this data with extreme scrutiny, armed with a knowledge of recent volunteers.

-- Non-Signup-Linked Update, All Dates (Dangerous) --
select i.shop_date, i.short_name, i.email, s.email, v.first_name, v.last_name
from bsbc.staff_sign_in i
join bsbc.staff_short_name s on s.short_name = i.short_name
join bsbc.volunteer v on v.email = s.email
where i.email is null
and substr(i.short_name, 1) = substr(s.short_name, 1)
;

WARNING WARNING: Seriously, Do Not Run This Unless You Have Checked Carefully

update bsbc.staff_sign_in i
join bsbc.staff_short_name s on s.short_name = i.short_name
set i.email = s.email
where i.email is null
;