f0QCLYJv

1<?php
2
3use yii\db\Migration;
4
5class m160512_055401_users_delete extends Migration
6{
7    public function safeUp()
8    {
9        $sql = <<<SQL
10SET @acolinEmail = 'acollin@hartlandinternational.com';
11SET @coachId = (SELECT id FROM user_account WHERE email = @acolinEmail);
12
13DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete,
14                      activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete;
15
16CREATE TEMPORARY TABLE IF NOT EXISTS coachGroups AS (SELECT id FROM `group` WHERE `group`.ownerId = @coachId);
17CREATE TEMPORARY TABLE IF NOT EXISTS notCoachGroup AS (SELECT id FROM `group` WHERE `group`.ownerId != @coachId);
18CREATE TEMPORARY TABLE IF NOT EXISTS coachUsers AS (SELECT gs.studentId FROM group_student gs WHERE gs.groupId IN (SELECT * FROM coachGroups));
19CREATE TEMPORARY TABLE IF NOT EXISTS usersForDelete AS (SELECT id FROM user_account WHERE id NOT IN (SELECT * FROM coachUsers) AND id NOT IN (SELECT @coachId UNION SELECT 1 UNION SELECT 2 UNION SELECT 95)); #1 - root, 2 - student, 95 - coach
20
21CREATE TEMPORARY TABLE IF NOT EXISTS activityUploadForDelete AS (SELECT id FROM activity_upload WHERE coachId IN (SELECT id FROM usersForDelete));
22CREATE TEMPORARY TABLE IF NOT EXISTS activityForDelete AS (SELECT id FROM activity WHERE activityUploadId IN (SELECT id FROM activityUploadForDelete) OR studentId IN (SELECT id FROM usersForDelete) OR groupId IN (SELECT id FROM notCoachGroup));
23CREATE TEMPORARY TABLE IF NOT EXISTS eventCodeFaildForDelete AS (SELECT id FROM event_code_failed_attempt ecfa WHERE ecfa.userId IN (SELECT id FROM usersForDelete));
24CREATE TEMPORARY TABLE IF NOT EXISTS groupForDelete AS (SELECT id FROM `group` WHERE `group`.ownerId IN (SELECT id FROM usersForDelete));
25CREATE TEMPORARY TABLE IF NOT EXISTS groupStudentForDelete AS (SELECT id FROM group_student WHERE studentId IN (SELECT id FROM usersForDelete));
26CREATE TEMPORARY TABLE IF NOT EXISTS userProfileForDelete AS (SELECT id FROM user_profile WHERE ownerId IN (SELECT id FROM usersForDelete));
27
28DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete);
29DELETE FROM activity WHERE id IN (SELECT * FROM activityForDelete);
30DELETE FROM activity_upload WHERE id IN (SELECT * FROM activityUploadForDelete);
31DELETE FROM group_student WHERE id IN (SELECT * FROM groupStudentForDelete);
32DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete);
33DELETE FROM `group` WHERE id IN (SELECT * FROM groupForDelete);
34DELETE FROM user_profile WHERE id IN (SELECT * FROM userProfileForDelete);
35DELETE FROM user_account WHERE id IN (SELECT * FROM usersForDelete);
36
37
38DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete,
39                      activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete;
40SQL;
41        $this->execute($sql);
42    }
43
44    public function safeDown()
45    {
46        return;
47    }
48}