Category:BikeSaviours

From Traxel Wiki
Jump to navigation Jump to search


https://www.bikesaviours.org/

Random Notes

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
;

Pages in category "BikeSaviours"

The following 2 pages are in this category, out of 2 total.