p7XQcdSd

1drop table entity;
2
3alter table resident change id id int(11) AUTO_INCREMENT, ADD PRIMARY KEY (id), DROP KEY id;
4alter table resident add column location_id int(11) NOT NULL AFTER id;
5delete from resident where building IS NULL;
6delete from resident where unit IS NULL;
7delete from resident where res IS NULL;
8alter table resident modify building varchar(14) NOT NULL;
9alter table resident modify unit varchar(8) NOT NULL;
10alter table resident change res resident varchar(4) NOT NULL;
11alter table resident drop `key`;
12alter table resident drop `send_movein`;
13alter table resident modify opt_out tinyint(1) NOT NULL DEFAULT 0;
14alter table resident drop `mi-date`;
15alter table resident drop `name`;
16alter table resident add column send_emails tinyint(1) NOT NULL DEFAULT 0 AFTER email;
17update resident set send_emails = 1 where email IS NOT NULL;
18alter table resident add column created_at datetime AFTER send_emails;
19alter table resident add column updated_at datetime AFTER created_at;
20
21alter table s_movein change sid id int(11) AUTO_INCREMENT, ADD PRIMARY KEY (id), DROP KEY sid;
22alter table s_movein add column survey_id int(11) NOT NULL AFTER id;
23alter table s_movein add column resident_id int(11) NOT NULL AFTER id;
24alter table s_movein change sent sent_at datetime AFTER post_email_sent;
25alter table s_movein change completed completed_at datetime AFTER sent_at;
26alter table s_movein drop `Phone`;
27alter table s_movein drop `Q8`;
28alter table s_movein drop `Q8F`;
29alter table s_movein drop `Q9`;
30alter table s_movein drop `Q9F`;
31alter table s_movein drop `Q10`;
32alter table s_movein drop `Q10F`;
33update s_movein set post_email_sent = 0 WHERE post_email_sent IS NULL;
34alter table s_movein modify post_email_sent tinyint(1) NOT NULL DEFAULT 0;
35alter table s_movein modify reminder_sent tinyint(1) NOT NULL DEFAULT 0 AFTER post_email_sent;
36update s_movein m set m.survey_id = 1;
37update s_movein m, surveys s set m.resident_id = s.resident where m.id = s.id;
38
39alter table s_movein add column `hash_key` varchar(32) AFTER survey_id;
40update s_movein m, surveys s set m.hash_key = s.key where m.id = s.id;
41delete from s_movein where resident_id = '0';
42alter table s_movein modify `hash_key` varchar(32) NOT NULL;
43
44DROP TABLE IF EXISTS `answers`;
45
46CREATE TABLE `answers` (
47  `id` int(11) NOT NULL AUTO_INCREMENT,
48  `question_id` int(11) NOT NULL,
49  `invitation_id` int(11) NOT NULL,
50  `choice_id` int(11) DEFAULT NULL,
51  `answer` text,
52  `created_at` datetime DEFAULT NULL,
53  `updated_at` datetime DEFAULT NULL,
54  PRIMARY KEY (`id`)
55) ENGINE=InnoDB DEFAULT CHARSET=latin1;
56
57insert into answers (question_id, invitation_id, choice_id, answer)  (select '1', `id`, CASE `Q1` WHEN 1 THEN 1 ELSE 2 END as choice_id, CONVERT(`Q1F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
58insert into answers (question_id, invitation_id, choice_id, answer)  (select '2', `id`, CASE `Q2` WHEN 1 THEN 1 ELSE 2 END as choice_id, CONVERT(`Q2F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
59insert into answers (question_id, invitation_id, choice_id, answer)  (select '3', `id`, CASE `Q3` WHEN 1 THEN 1 ELSE 2 END as choice_id, CONVERT(`Q3F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
60insert into answers (question_id, invitation_id, choice_id, answer)  (select '4', `id`, CASE `Q4` WHEN 1 THEN 1 ELSE 2 END as choice_id, CONVERT(`Q4F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
61insert into answers (question_id, invitation_id, choice_id, answer)  (select '5', `id`, NULL, CONVERT(`Q5F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
62update s_movein SET Q6 = ' ' WHERE Q6 = '0';
63update s_movein SET Q6 = ' ' WHERE Q6 = '1';
64update s_movein SET Q6 = ' ' WHERE Q6 IS NULL;
65insert into answers (question_id, invitation_id, choice_id, answer)  (select '6', `s_movein`.`id`, `survey_choices`.`id`, NULL as answer FROM s_movein, survey_choices WHERE s_movein.Q6 = survey_choices.name AND s_movein.completed_at IS NOT NULL);
66insert into answers (question_id, invitation_id, choice_id, answer)  (select '7', `id`, NULL, CONVERT(`Q7F` USING ascii) as answer FROM s_movein WHERE completed_at IS NOT NULL);
67drop table survey_choices;
68
69alter table s_movein drop `Q1`;
70alter table s_movein drop `Q1F`;
71alter table s_movein drop `Q2`;
72alter table s_movein drop `Q2F`;
73alter table s_movein drop `Q3`;
74alter table s_movein drop `Q3F`;
75alter table s_movein drop `Q4`;
76alter table s_movein drop `Q4F`;
77alter table s_movein drop `Q5`;
78alter table s_movein drop `Q5F`;
79alter table s_movein drop `Q6`;
80alter table s_movein drop `Q6F`;
81alter table s_movein drop `Q7`;
82alter table s_movein drop `Q7F`;
83
84alter table followup change fuid id int(11) AUTO_INCREMENT;
85alter table followup change sid invitation_id int(11) NOT NULL, DROP KEY sid;
86alter table followup change date created_at datetime;
87
88rename table answers to survey_answers;
89rename table s_movein to survey_invitations;
90rename table resident to survey_residents;
91rename table followup to survey_followups;
92drop table surveys;