Course name clean-up

UPDATE `courses`

SET code= CONCAT('DEACTIVATED-',LPAD(id,4,'0'))

WHERE is_active = 0

UPDATE courses

SET name = REPLACE(name, '_Deactivated', ' (DEACTIVATED)')

WHERE is_active = 0

AND name LIKE '%_Deactivated'

UPDATE courses

SET name = REPLACE(name, 'Deactivated', '(DEACTIVATED)')

WHERE is_active = 0

AND name LIKE '% Deactivated'

UPDATE `structures`

SET code= CONCAT('DEACTIVATED-',LPAD(id,3,'0'))

WHERE is_active = 0

UPDATE `structures`

SET code= CONCAT('DEACTIVATED-',LPAD(id,3,'0'))

, is_active = 0

WHERE is_active = 1

AND course_id IN

(

SELECT id

FROM courses

WHERE is_active = 0

)

UPDATE `structures`

SET name = CONCAT(name, ' (DEACTIVATED)')

WHERE is_active = 0

AND name NOT LIKE '%Deactivated%'

UPDATE structures

SET name = REPLACE(name, '_DEACTIVATED', ' (DEACTIVATED)')

WHERE is_active = 0

AND (name LIKE '%_Deactivated%'

OR name LIKE '%_DEACTIVATED%')

UPDATE structures

SET name = REPLACE(name, 'DEACTIVATED', ' (DEACTIVATED)')

WHERE is_active = 0

AND (name LIKE '%Deactivated'

OR name LIKE '%DEACTIVATED')

UPDATE `curriculums`

SET is_visible = 0

WHERE structure_id IN (

SELECT id

FROM structures

WHERE is_active = 0

)

UPDATE `curriculums`

SET name = CONCAT(name, ' (DEACTIVATED)')

WHERE is_visible = 0

AND name NOT LIKE '%DEACTIVATED%'

UPDATE curriculums

SET name = REPLACE(name, 'DEACTIVATED', ' (DEACTIVATED)')

WHERE is_visible = 0

AND name LIKE '%_DEACTIVATED'

UPDATE curriculums

SET name = REPLACE(name, 'Deactivated', ' (DEACTIVATED)')

WHERE is_visible = 0

AND name LIKE '%Deactivated'

UPDATE `films_classes`

SET is_active = 0

WHERE id NOT IN

(

SELECT films_class_id

FROM curriculums

WHERE is_visible = 1

)

UPDATE films_classes

SET name = CONCAT(name, ' (DEACTIVATED)')

WHERE is_active = 0

AND name NOT LIKE '%DEACTIVATED%'

UPDATE films_classes

SET name = REPLACE(name, '_DEACTIVATED', ' (DEACTIVATED)')

WHERE is_active = 0

AND name LIKE '%_DEACTIVATED'

UPDATE films_classes

SET name = REPLACE(name, 'DEACTIVATED', ' (DEACTIVATED)')

WHERE is_active = 0

AND name LIKE '%DEACTIVATED'

/* Remove outdated AutoEnrollments */

DELETE

FROM fm_autoenroll_assn

WHERE curriculum_id IN

(

SELECT id

FROM curriculums

WHERE is_visible = 0

)

/* Remove outdated enrollments created by AutoEnrollments */

DELETE IGNORE

FROM enrollments

WHERE visits = 0

AND curriculum_id IN

(

SELECT id

FROM curriculums

WHERE is_visible = 0

)

AND id NOT IN

(

SELECT enrollment_id

FROM results

)

AND id NOT IN

(

SELECT enrollment_id

FROM enrollment_earned_cert_assn

)