/***** select from users for spc_users. *****/
/*NOTE for go-live - if dupe emails are not allowed, some data will need to be left behind. Check out
	Kelly Leggett and Duane Ford. Both have 2 records w/dupe emails. Their user_ids are (184, 265, 743, 836)*/
SELECT user_id user_id,
	fname first_name,
	lname last_name,
	org_id organizations_id,
	building_id buildings_id,
	position,
	user_name,
	email_address email,	
	(CASE audit_reports WHEN 'Y' THEN 'Y' ELSE 'N' END) audit_reports,	
	/*security_profile,	*/
	date_created created_date,
	1271 created_by,
	date_created modified_date,
	1271 modified_by,
	
	/*If active = 0, then add a soft delete date*/
	(CASE active WHEN 0 THEN NOW() ELSE NULL END) deleted_at
	
FROM users AS spc_users /*Aliasing it will make the insert into statements, in the export, insert into people*/
WHERE org_id IS NOT NULL; /*This is because there were 2 records w/no org_id (16, 1386) on migration date 2/1/22*/




	/*password,
	user_level,
	org_user_level,
	(CASE change_password_on_login WHEN 'Y' THEN 'Y' ELSE 'N' END) change_password_on_login,
	(CASE hover_enabled WHEN 'Y' THEN 'Y' ELSE 'N' END) hover_enabled,
	(CASE primary_user WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE 'N' END) primary_user,
	(CASE primary_contact WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE 'N' END) primary_contact,
	(CASE active WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE 'N' END) active,
	(CASE toner_alert WHEN 'Y' THEN 'Y' ELSE 'N' END) toner_alert,
	(CASE service_alert WHEN 'Y' THEN 'Y' ELSE 'N' END) service_alert,
	(CASE test_user WHEN 'Y' THEN 'Y' ELSE 'N' END) test_user,
	admin_photo admin_photo_url,*/
	
	
	
/*************************************NEW QUERY FOR THE COMBINED SPC_USERS AND CONTACTS TABLE*********************************/
SELECT user_id user_id,
	fname first_name,
	lname last_name,
	org_id organizations_id,
	building_id buildings_id,
	position,
	NULL org_job_title,
	NULL spc_contact,
	'Y' spc_user,
	NULL notes,
	NULL email_group_id,
	
	email_address email,	
	(CASE audit_reports WHEN 'Y' THEN 'Y' ELSE 'N' END) audit_reports,	
	
	date_created created_date,
	1271 created_by,
	date_created modified_date,
	1271 modified_by,
	
	/*If active = 0, then add a soft delete date*/
	(CASE active WHEN 0 THEN NOW() ELSE NULL END) deleted_at
	
FROM users AS spc_users /*Aliasing it will make the insert into statements, in the export, insert into people*/
WHERE org_id IS NOT NULL; /*This is because there were 2 records w/no org_id (16, 1386) on migration date 2/1/22*/

INSERT INTO user_positions (position_name, created_by, modified_by) VALUES('President', 1271, 1271);
INSERT INTO user_positions (position_name, created_by, modified_by) VALUES('Secretary', 1271, 1271);
INSERT INTO user_positions (position_name, created_by, modified_by) VALUES('Administrator', 1271, 1271);
INSERT INTO user_positions (position_name, created_by, modified_by) VALUES('Systems Support', 1271, 1271);
#UPDATE spc_users SET user_position = 1 WHERE user_position = 'Accounts Payable';
#UPDATE spc_users SET user_position = 2 WHERE user_position = 'Building Key Operator';
#UPDATE spc_users SET user_position = 3 WHERE user_position = 'Business Manager';
#UPDATE spc_users SET user_position = 5 WHERE user_position = 'IT Manager';
#UPDATE spc_users SET user_position = 6 WHERE user_position = 'IT Support';
#UPDATE spc_users SET user_position = 7 WHERE user_position = 'Materials Manager';
#UPDATE spc_users SET user_position = 9 WHERE user_position = 'Superintendent';
#UPDATE spc_users SET user_position = 8 WHERE user_position = 'Principal';
#UPDATE spc_users SET user_position = 10 WHERE user_position = 'Town Manager';
#UPDATE spc_users SET user_position = 11 WHERE user_position = 'President';
#UPDATE spc_users SET user_position = 12 WHERE user_position = 'Secretary';
#UPDATE spc_users SET user_position = 14 WHERE user_position = 'Systems Support';