retroactively add db entries to group

readme.md

Update Moodle DB Entries to Retroactively Use Group ID

We want to add semester-based groups to the internships courses and turn on group mode in the "enter your hours" mod_data activity so we can filter the entries based on the semester group, but entries only have one group ID and it's added at the time of its creation (being set to 0 if no group). Here's how we retroactively add groups to these data entries in the mdl_data_records table:

1. Go to the course > Participants > Groups > Create a new group
2. Name the group like "Fall 2023" with an idnumber of form "INTER-INTRN-2023FA" i.e. the course shortname and then the semester code
3. Add all the current students to the Fall 2023 group
4. Create a group with analogous settings for Spring 2024
5. Create a Grouping called "Semester Groups" and add these two groups to the grouping
6. Backup the mdl_data_record database table (see gcloud command)
7. Connect to the Moodle database (e.g. by running a shell on the pod and then moosh -n sql-cli)
8. Edit the correct course name, e.g. "Interior Design Internships", into the @coursename variable in the SQL command and also edit in the correct groupd idnumber from step 2
9. Execute the SQL command
10. Open the hours mod_data activity in a browser, edit its settings
11. Under Common module settings set Group mode to be "separate groups" and set Grouping to be "Semester Groups"
12. Save and view the entries list, confirm that you can filter with the semester groups

backup-db-table.sh

# backup the mdl_data_records table before we modify it
gcloud sql export sql mysql-prod-1 gs://cca-manual-db-dumps/(dt)-mdl_data_records.sql -d m_prod1 -t mdl_data_records

update_data_records.sql

-- update data entries to use groupid, edit exact course name below
SET @coursename = 'Interior Design Internship';
UPDATE mdl_data_records
SET groupid = (
    SELECT g.id
    FROM mdl_groups g
    JOIN mdl_course c ON g.courseid = c.id
    WHERE g.idnumber = 'INTER-INTRN-2023FA'
    AND c.fullname = @coursename
)
WHERE groupid = 0
AND dataid = (
    SELECT d.id
    FROM mdl_data d
    JOIN mdl_course c
    ON d.course = c.id
    WHERE c.fullname = @coursename
)