Husky Retail & Commercial Online Learning
Husky Retail & Commercial Online Learning (FiLMS)
Admin
Update Sites using spreadsheet
Upload list to temp table
- Clean up user list
- CSV format
- Headers: Site, Site Name, MSO, Email
- No blank rows or columns
From Husky
Cleaned CSV
Drop existing `users_temp` and recreate
DROP TABLE IF EXISTS `users_temp`;
CREATE TABLE IF NOT EXISTS `users_temp` (
`Site` varchar(50) collate utf8_unicode_ci NOT NULL,
`Site Name` varchar(50) collate utf8_unicode_ci NOT NULL,
`MSO` varchar(50) collate utf8_unicode_ci NOT NULL,
`Email` varchar(200) collate utf8_unicode_ci NOT NULL
);
Import into users_temp table
Discover new Sites
Steps here
SELECT
*
FROM `users_temp`
WHERE UCASE(TRIM(`Site`)) NOT IN
(
SELECT code
FROM organizations
)
Discover new HKs
Steps here
SELECT site
,
(
SELECT email
FROM users_temp AS ut2
WHERE ut.site = ut2.site
AND ut2.email <> ''
ORDER BY site
LIMIT 1
) AS email
FROM `users_temp` AS ut
WHERE CONCAT('HK',LPAD(site,4,'0')) NOT IN
(
SELECT login
FROM users
)
Discover & new MSOs
Steps here
INSERT INTO users(organization_id,role_id,login,name,password,password_hint)
SELECT
DISTINCT ut.organization_id
, 7
, CONCAT('MSO',ut.MSO)
, CONCAT('Admin MSO#',ut.MSO)
, 'password'
, 'Generated 2016-12-16'
FROM users_temp AS ut
LEFT JOIN users AS u
ON CONCAT('MSO',ut.MSO) = u.login
WHERE u.id IS NULL
Purge existing MSO/Org associations
Delete all existing assocations between MSOs and Organizations so we can remake them:
DELETE
FROM `organization_admin_assn`
WHERE user_id IN
(
SELECT id
FROM users AS u
WHERE u.role_id = 7
)
Remake the associations:
INSERT INTO organization_admin_assn(user_id,organization_id)
SELECT
(
SELECT id
FROM users AS u
WHERE u.login = CONCAT('MSO',ut.MSO)
LIMIT 1
)
, ut.organization_id
FROM `users_temp` AS ut
Double-check for orphans
SELECT *
FROM organizations
WHERE id NOT IN
(
SELECT oaa.organization_id
FROM organization_admin_assn AS oaa
LEFT JOIN users AS u
ON u.id = oaa.user_id
WHERE u.role_id = 7
)
Deployment
The client does not want users printing a certificate before the course is completed. To ensure this doesn't happen all conclusion lessons have been marked:
Not required
