Simplest version of a shift report template

[simonw]

A text field on the Team model which has text that is pre-filled in the shift report textarea when it is displayed. Can then have key/value fielded prompts that can be extracted later.

[simonw]

with parsed as (
  select
    date(shifts_shift.shift_start) as date,
    coalesce(profiles_userprofile.display_name, auth_user.username) as docent_name,
    '' as shift_report_rcvd,
    shifts_shift.shift_start::time as start_time,
    shifts_shift.shift_end::time as end_time,
    (regexp_match(report, '1\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as cars,
    (regexp_match(report, '2\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as visitors_engaged,
    (regexp_match(report, '3\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as violations_reported,
    (regexp_match(report, '4\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as total_violations_observed,
    (regexp_match(report, '5\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as biota_handling,
    (regexp_match(report, '6\.(\s.*?)([\r\n]+)(\d+?)(?=[\r\n]|$)'))[3]::integer as violations_redirected,
    (regexp_match(report, '7\.\s.*?[\r\n]+(.*?)(?=\d+\.|$)', 's'))[1] as comments
from shifts_shiftreport
join shifts_shift on shifts_shift.id = shifts_shiftreport.shift_id
join auth_user on shifts_shiftreport.user_id = auth_user.id
left join profiles_userprofile on profiles_userprofile.user_id = auth_user.id
)
select * from parsed where cars is not null order by date desc

Against this template:

1. # Cars

2. Number Visitors Engaged

3. Violations reported to CDFW, MC Parks

4. Total Violations Observed (includes MPA Watch data, dogs offleash, boat fishing w/in 1,000ft)

5. Hand collection or handling of biota

6. Violations redirected (successful engagement)

7. Comments

[simonw]

Documentation: https://docs.google.com/document/d/1l7A9AuxIFTTBJrYhorcOLPNpNjtOw-YjyC9-SWitOAk/edit?tab=t.0