-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathStep05_munge_211_data.houston.php
205 lines (160 loc) · 6.34 KB
/
Step05_munge_211_data.houston.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
<?php
//this allows us to call the run_sql_loop function later on
//it also loads our SQL connection...
require_once('util/run_sql_loop.function.php');
//get the name of the database and the file from the argument list..
if(!isset($argv[3])){
echo "usage Step10_munge_211_data.php {database} {tablename} {file_to_import.csv}\n";
exit();
}else{
$database = $argv[1];
$tablename = $argv[2];
$filename = $argv[3];
}
if(!file_exists($filename)){
echo "We could not find $filename\n";
exit();
}
$filename = realpath($filename); //to be specific
$sql = [];
//lets build a basic need table
$sql["drop the need table"] = "DROP TABLE IF EXISTS $databae.$tablename"."_need";
$sql["create need table"] = "
CREATE TABLE $database.$tablename"."_need (
`id` int(11) NOT NULL,
`need_taxonomy` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`need_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["primary key need table"] = "
ALTER TABLE $database.$tablename"."_need
ADD PRIMARY KEY (`id`);
";
$sql["auto increment need table"] = "
ALTER TABLE $database.$tablename"."_need
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
";
$sql["drop need link table"] = "DROP TABLE IF EXISTS $database.$tablename"."_to_need";
$sql["create need link table"] = "
CREATE TABLE $database.$tablename"."_to_need (
`call_id` int(11) NOT NULL,
`need_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["index need link table"] = "
ALTER TABLE $database.$tablename"."_to_need
ADD PRIMARY KEY (`call_id`,`need_id`);
";
//and a basic agency table
$sql["drop the the agency table"] = "DROP TABLE IF EXISTS $database.$tablename"."_agency";
$sql["create the agency table"] = "
CREATE TABLE $database.$tablename"."_agency (
`id` int(11) NOT NULL,
`agency_identifier` int(11) NOT NULL,
`agency_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["primary key agency table"] = "
ALTER TABLE $database.$tablename"."_agency
ADD PRIMARY KEY (`id`);
";
$sql["auto increment agency table"] = "
ALTER TABLE $database.$tablename"."_agency
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
";
$sql["drop agency link table"] = "DROP TABLE IF EXISTS $database.$tablename"."_to_agency";
$sql["create agency link table"] = "
CREATE TABLE $database.$tablename"."_to_agency (
`call_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["index agency link table"] = "
ALTER TABLE $database.$tablename"."_to_agency
ADD PRIMARY KEY (`call_id`,`agency_id`);
";
//and a basic referral table
$sql["drop the the referral table"] = "DROP TABLE IF EXISTS $database.$tablename"."_referral";
$sql["create the referral table"] = "
CREATE TABLE $database.$tablename"."_referral (
`id` int(11) NOT NULL,
`referral_identifier` int(11) NOT NULL,
`referral_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["primary key referral table"] = "
ALTER TABLE $database.$tablename"."_referral
ADD PRIMARY KEY (`id`);
";
$sql["auto increment referral table"] = "
ALTER TABLE $database.$tablename"."_referral
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
";
$sql["drop referral link table"] = "DROP TABLE IF EXISTS $database.$tablename"."_to_referral";
$sql["create referral link table"] = "
CREATE TABLE $database.$tablename"."_to_referral (
`call_id` int(11) NOT NULL,
`referral_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
";
$sql["index referral link table"] = "
ALTER TABLE $database.$tablename"."_to_referral
ADD PRIMARY KEY (`call_id`,`referral_id`);
";
//then the main data table..
$sql["dropping the table"] = "DROP TABLE IF EXISTS $database.$tablename\n";
$sql["creating the table structure"] = "
CREATE TABLE $database.$tablename (
`report_number` int(11) DEFAULT NULL,
`date_of_call_start_text` varchar(25) DEFAULT NULL,
`date_of_call_end_text` varchar(25) DEFAULT NULL,
`length_of_call_minutes` int(11) DEFAULT NULL,
`age` varchar(7) DEFAULT NULL,
`gender` varchar(55) DEFAULT NULL,
`zip_code` varchar(50) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`county` varchar(255) DEFAULT NULL,
`preferred_language` varchar(255) DEFAULT NULL,
`other_language` varchar(255) DEFAULT NULL,
`military_status` varchar(55) DEFAULT NULL,
`military_branch_veteran` varchar(55) DEFAULT NULL,
`military_branch_active_duty` varchar(55) DEFAULT NULL,
`need_name` varchar(1000) DEFAULT NULL,
`referral_identifier` varchar(1000) DEFAULT NULL,
`referral_name` varchar(1000) DEFAULT NULL,
`parent_agency_identifier` varchar(1000) DEFAULT NULL,
`parent_agency_name` varchar(1000) DEFAULT NULL,
`need_taxonomy_code` varchar(1000) DEFAULT NULL,
`met_or_reason_unmet` varchar(1000) DEFAULT NULL,
`benefits_information_call` varchar(1000) DEFAULT NULL,
`benefits_referral_call` varchar(1000) DEFAULT NULL,
`benefits_transfer_call` varchar(1000) DEFAULT NULL,
`first_time_user` varchar(1000) DEFAULT NULL,
`referral_source_first_time_user` varchar(1000) DEFAULT NULL,
`referral_source_possible_first_time_user` varchar(1000) DEFAULT NULL,
`other_referral_source_first_time_user` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
";
//loops every every SQL command one at a time...
$sql["Load data statement to move the CSV data into the table"] = "
LOAD DATA INFILE '$filename'
INTO TABLE $database.$tablename
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\\n'
IGNORE 1 ROWS
";
$sql["create uninque id per row for further data operations"] = "
ALTER TABLE $database.$tablename ADD `id` INT(11) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
";
$sql["add time cols"] = "
ALTER TABLE $database.$tablename
ADD `year` INT(11) NOT NULL AFTER `report_number`,
ADD `week_number` INT(11) NOT NULL AFTER `year`,
ADD `week_sunday` VARCHAR(15) NOT NULL AFTER `week_number`,
ADD `call_time_block` VARCHAR(25) NOT NULL AFTER `week_sunday`,
ADD `call_order` INT(11) NOT NULL AFTER `call_time_block`,
ADD `is_weekday` TINYINT(1) NOT NULL AFTER `call_order`,
ADD `age_bucket` VARCHAR(25) NOT NULL AFTER `age`;
";
run_sql_loop($sql,true);