BMLT Root Server
NAWSImport.php
Go to the documentation of this file.
1 <?php
2 
3 defined('BMLT_EXEC') or die('Cannot Execute Directly');
4 
5 require_once(__DIR__ . '/../../vendor/autoload.php');
6 require_once(__DIR__ . '/../../server/c_comdef_server.class.php');
7 require_once(__DIR__ . '/NAWSImportMeetingsExistException.php');
8 require_once(__DIR__ . '/NAWSImportServiceBodiesExistException.php');
9 
10 // phpcs:disable PSR1.Classes.ClassDeclaration.MissingNamespace
12 {
13  // phpcs:enable PSR1.Classes.ClassDeclaration.MissingNamespace
14  private $nawsExportRows = null;
15  private $expectedColumns = array(
16  'delete', 'parentname', 'committee', 'committeename', 'arearegion', 'day', 'time', 'place',
17  'address', 'city', 'locborough', 'state', 'zip', 'country', 'directions', 'closed', 'wheelchr',
18  'format1', 'format2', 'format3', 'format4', 'format5', 'longitude', 'latitude', 'room'
19  );
20  private $server = null;
21  private $areas = array();
22  private $deleteIndex = null;
23  private $areaNameIndex = null;
24  private $areaWorldIdIndex = null;
25  private $columnNames = null;
26  private $worldIdIndex = null;
27  private $numServiceBodiesCreated = 0;
28  private $numUsersCreated = 0;
29  private $numMeetingsCreated = 0;
30 
31  public function __construct($importFilePath, $initialValueForPublished)
32  {
33  try {
34  $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($importFilePath);
35  $spreadsheet = $reader->load($importFilePath);
36  $this->nawsExportRows = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
37  $this->initialValueForPublished = $initialValueForPublished;
38 
39  // If the last row is all nulls, remove it
40  $lastRow = $this->nawsExportRows[count($this->nawsExportRows)];
41  $allNulls = true;
42  foreach ($lastRow as $columnValue) {
43  if ($columnValue != null) {
44  $allNulls = false;
45  break;
46  }
47  }
48  if ($allNulls) {
49  array_pop($this->nawsExportRows);
50  }
51  } catch (Exception $e) {
52  throw new Exception('There was a problem reading the spreadsheet: ' . $e->getMessage());
53  }
54 
55 
56  // Lowercase all of the headings for case insensitive string matching
57  foreach ($this->nawsExportRows[1] as $key => $value) {
58  if ($value) {
59  $this->nawsExportRows[1][$key] = strtolower($value);
60  }
61  }
62 
63  // Validate there are no missing columns, and set the $this->columnNames map
64  $missingValues = array();
65  foreach ($this->expectedColumns as $expectedColumnName) {
66  $idx = array_search($expectedColumnName, $this->nawsExportRows[1]);
67  if (is_bool($idx)) {
68  array_push($missingValues, $expectedColumnName);
69  } else {
70  $this->columnNames[$idx] = $expectedColumnName;
71  }
72  }
73  if (count($missingValues) > 0) {
74  throw new Exception('NAWS export is missing required columns: ' . implode(', ', $missingValues));
75  }
76 
77  // Store the column index for these important columns
78  $this->deleteIndex = array_search('delete', $this->columnNames);
79  $this->areaNameIndex = array_search('parentname', $this->columnNames);
80  $this->areaWorldIdIndex = array_search('arearegion', $this->columnNames);
81  $this->worldIdIndex = array_search('committee', $this->columnNames);
82  }
83 
84  public function import($failOnDuplicates = false)
85  {
86  set_time_limit(1200); // 20 minutes
87  require_once(__DIR__ . '/../../server/classes/c_comdef_meeting.class.php');
88  require_once(__DIR__ . '/../../server/classes/c_comdef_service_body.class.php');
89  require_once(__DIR__ . '/../../server/classes/c_comdef_user.class.php');
90  $this->server = c_comdef_server::MakeServer();
92  try {
93  if ($failOnDuplicates) {
94  $this->throwIfDuplicateServiceBodies();
95  $this->throwIfDuplicateMeetings();
96  }
97  $this->createServiceBodiesAndUsers();
98  $this->createMeetings();
101  throw $e;
102  } catch (NAWSImportMeetingsExistException $e) {
104  throw $e;
105  } catch (Exception $e) {
107  throw new Exception($e->getMessage());
108  }
110  }
111 
112  private function createServiceBodiesAndUsers()
113  {
114  // Create the service bodies
115  for ($i = 1; $i <= count($this->nawsExportRows); $i++) {
116  $row = $this->nawsExportRows[$i];
117 
118  if ($i == 1) {
119  continue;
120  }
121 
122  if ($row[$this->deleteIndex] == 'D') {
123  continue;
124  }
125 
126  $areaName = trim($row[$this->areaNameIndex]);
127  $areaWorldId = trim($row[$this->areaWorldIdIndex]);
128  if (!$areaName) {
129  continue;
130  }
131  $this->areas[$areaWorldId] = $areaName;
132  }
133 
134  foreach ($this->areas as $areaWorldId => $areaName) {
135  $userName = preg_replace("/[^A-Za-z0-9]/", '', $areaName);
136  $user = new c_comdef_user(
137  null,
138  0,
140  '',
141  $userName,
142  '',
143  $this->server->GetLocalLang(),
144  $userName,
145  'User automatically created for ' . $areaName,
146  1
147  );
148  $user->SetPassword($this->generateRandomString(30));
149  $user->UpdateToDB();
150  $this->numUsersCreated++;
151 
152  $serviceBody = new c_comdef_service_body;
153  $serviceBody->SetLocalName($areaName);
154  $serviceBody->SetWorldID($areaWorldId);
155  $serviceBody->SetLocalDescription($areaName);
156  $serviceBody->SetPrincipalUserID($user->GetID());
157  $serviceBody->SetEditors(array($user->GetID()));
158  if (substr($areaWorldId, 0, 2) == 'AR') {
159  $serviceBody->SetSBType(c_comdef_service_body__ASC__);
160  } else {
161  $serviceBody->SetSBType(c_comdef_service_body__RSC__);
162  }
163  $serviceBody->UpdateToDB();
164  $this->numServiceBodiesCreated++;
165  $this->areas[$areaWorldId] = $serviceBody;
166  }
167 
168  reset($this->nawsExportRows);
169  }
170 
171  private function createMeetings()
172  {
173  $defaultDurationTime = $this->server->GetLocalStrings()['default_duration_time'];
174 
175  $formats = array();
176  foreach ($this->server->GetFormatsObj()->GetFormatsArray()['en'] as $format) {
177  if ($format instanceof c_comdef_format) {
178  $world_id = $format->GetWorldID();
179  $shared_id = $format->GetSharedID();
180  if ($world_id && $shared_id) {
181  if (is_array($formats) && array_key_exists($world_id, $formats)) {
182  array_push($formats[$world_id], $shared_id);
183  } else {
184  $formats[$world_id] = array($shared_id);
185  }
186  }
187  }
188  }
189 
190  $ajaxHandler = new c_comdef_admin_ajax_handler(null);
191  $nawsDays = array(null, 'sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday');
192  // State is not a required column, because it is not always filled out for foreign countries
193  $requiredColumns = array('committeename', 'arearegion', 'day', 'time', 'address', 'city');
194  for ($i = 1; $i <= count($this->nawsExportRows); $i++) {
195  $row = $this->nawsExportRows[$i];
196  if ($i == 1) {
197  continue;
198  }
199 
200  if ($row[$this->deleteIndex] == 'D') {
201  continue;
202  }
203 
204  $meetingData = array();
205  $meetingData['published'] = $this->initialValueForPublished;
206  $meetingData['lang_enum'] = $this->server->GetLocalLang();
207  $meetingData['duration_time'] = $defaultDurationTime;
208  $meetingData['format_shared_id_list'] = array();
209  $skipMeeting = false;
210  foreach ($this->columnNames as $columnIndex => $columnName) {
211  $value = trim($row[$columnIndex]);
212 
213  // NAWS exports sometimes contain deleted meetings, and will have empty cells
214  // for those meetings. Just skip them.
215  if (!is_bool(array_search($columnName, $requiredColumns)) && !$value) {
216  $skipMeeting = true;
217  break;
218  }
219 
220  switch ($columnName) {
221  case 'committee':
222  $meetingData['worldid_mixed'] = $value;
223  break;
224  case 'committeename':
225  $meetingData['meeting_name'] = $value;
226  break;
227  case 'arearegion':
228  $meetingData['service_body_bigint'] = $this->areas[$row[$this->areaWorldIdIndex]]->GetID();
229  break;
230  case 'day':
231  $value = strtolower($value);
232  $value = array_search($value, $nawsDays);
233  if ($value == false) {
234  throw new Exception('Invalid value in column \'' . $columnName . '\'');
235  }
236  $meetingData['weekday_tinyint'] = $value;
237  break;
238  case 'time':
239  $time = abs(intval($value));
240  $hours = min(23, $time / 100);
241  $minutes = min(59, ($time - (intval($time / 100) * 100)));
242  $meetingData['start_time'] = sprintf("%d:%02d:00", $hours, $minutes);
243  break;
244  case 'place':
245  $meetingData['location_text'] = $value;
246  break;
247  case 'address':
248  $meetingData['location_street'] = $value;
249  break;
250  case 'city':
251  $meetingData['location_municipality'] = $value;
252  break;
253  case 'locborough':
254  $meetingData['location_neighborhood'] = $value;
255  break;
256  case 'state':
257  $meetingData['location_province'] = $value;
258  break;
259  case 'zip':
260  $meetingData['location_postal_code_1'] = $value;
261  break;
262  case 'country':
263  $meetingData['location_nation'] = $value;
264  break;
265  case 'room':
266  case 'directions':
267  if ($meetingData['location_info']) {
268  if ($value) {
269  if ($columnName == 'directions') {
270  $meetingData['location_info'] .= ', ' . $value;
271  } else {
272  $meetingData['location_info'] = $value . ', ' . $meetingData['location_info'];
273  }
274  }
275  } else {
276  $meetingData['location_info'] = $value;
277  }
278  break;
279  case 'wheelchr':
280  $value = strtolower($value);
281  if ($value == 'true' || $value == '1') {
282  $value = $formats['WCHR'];
283  if ($value) {
284  $meetingData['format_shared_id_list'] = array_merge($meetingData['format_shared_id_list'], $value);
285  }
286  }
287  break;
288  case 'closed':
289  case 'format1':
290  case 'format2':
291  case 'format3':
292  case 'format4':
293  case 'format5':
294  $value = $formats[$value];
295  if ($value) {
296  $meetingData['format_shared_id_list'] = array_merge($meetingData['format_shared_id_list'], $value);
297  }
298  break;
299  case 'longitude':
300  $meetingData['longitude'] = $value;
301  break;
302  case 'latitude':
303  $meetingData['latitude'] = $value;
304  break;
305  case 'unpublished':
306  if ($value == '1') {
307  $meetingData['published'] = false;
308  }
309  break;
310  }
311  }
312 
313  if ($skipMeeting) {
314  continue;
315  }
316 
317  $meetingData['format_shared_id_list'] = implode(',', $meetingData['format_shared_id_list']);
318  $ajaxHandler->SetMeetingDataValues($meetingData, false);
319  $this->numMeetingsCreated++;
320  }
321  }
322 
323  private function throwIfDuplicateServiceBodies()
324  {
325  $duplicates = array();
326  $existingWorldIds = array();
327  $select = "SELECT DISTINCT `worldid_mixed` FROM `" . c_comdef_server::GetServiceBodiesTableName_obj() . "`;";
328  $rows = c_comdef_dbsingleton::preparedQuery($select);
329  if (is_array($rows) && count($rows)) {
330  foreach ($rows as $row) {
331  if (!$row['worldid_mixed']) {
332  continue;
333  }
334  $worldId = strtoupper(trim($row['worldid_mixed']));
335  if ($worldId) {
336  $existingWorldIds[$worldId] = null;
337  }
338  }
339  }
340 
341  for ($i = 1; $i <= count($this->nawsExportRows); $i++) {
342  $row = $this->nawsExportRows[$i];
343  if ($i == 1) {
344  continue;
345  }
346 
347  if ($row[$this->deleteIndex] == 'D') {
348  continue;
349  }
350 
351  $worldId = $row[$this->areaWorldIdIndex];
352  if (!$worldId) {
353  continue;
354  }
355 
356  $worldId = strtoupper(trim($worldId));
357  if (!$worldId) {
358  continue;
359  }
360 
361  if (array_key_exists($worldId, $existingWorldIds)) {
362  $duplicates[$worldId] = null;
363  }
364  }
365 
366  $duplicates = array_keys($duplicates);
367  if (count($duplicates)) {
368  throw new NAWSImportServiceBodiesExistException($duplicates);
369  }
370 
371  reset($this->nawsExportRows);
372  }
373 
374  private function throwIfDuplicateMeetings()
375  {
376  $duplicates = array();
377  $existingWorldIds = array();
378  $select = "SELECT DISTINCT `worldid_mixed` FROM `" . c_comdef_server::GetMeetingTableName_obj() . "_main`;";
379  $rows = c_comdef_dbsingleton::preparedQuery($select);
380  if (is_array($rows) && count($rows)) {
381  foreach ($rows as $row) {
382  if (!$row['worldid_mixed']) {
383  continue;
384  }
385  $worldId = strtoupper(trim($row['worldid_mixed']));
386  if ($worldId) {
387  $existingWorldIds[$worldId] = null;
388  }
389  }
390  }
391 
392  for ($i = 1; $i <= count($this->nawsExportRows); $i++) {
393  $row = $this->nawsExportRows[$i];
394  if ($i == 1) {
395  continue;
396  }
397 
398  if ($row[$this->deleteIndex] == 'D') {
399  continue;
400  }
401 
402  $worldId = $row[$this->worldIdIndex];
403  if (!$worldId) {
404  continue;
405  }
406 
407  $worldId = strtoupper(trim($worldId));
408  if (!$worldId) {
409  continue;
410  }
411 
412  if (array_key_exists($worldId, $existingWorldIds)) {
413  $duplicates[$worldId] = null;
414  }
415  }
416 
417  $duplicates = array_keys($duplicates);
418  if (count($duplicates)) {
419  throw new NAWSImportMeetingsExistException($duplicates);
420  }
421 
422  reset($this->nawsExportRows);
423  }
424 
425  private function generateRandomString($length = 10)
426  {
427  return substr(str_shuffle(str_repeat($x = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil($length/strlen($x)))), 1, $length);
428  }
429 
430  public function getNumServiceBodiesCreated()
431  {
432  return $this->numServiceBodiesCreated;
433  }
434 
435  public function getNumUsersCreated()
436  {
437  return $this->numUsersCreated;
438  }
439 
440  public function getNumMeetingsCreated()
441  {
442  return $this->numMeetingsCreated;
443  }
444 }
SetLocalName($in_name_string)
Accessor -Sets the _local_type_name_string data member.
A Class for Service Body Objects.
getNumMeetingsCreated()
Definition: NAWSImport.php:440
SetPassword($in_password_string)
Accessor - Sets the password, as an encrypted string.
This class handles BMLT users. One instance is created for each user on the server.
static GetMeetingTableName_obj()
Simply returns the name of the meetings table.
static GetServiceBodiesTableName_obj()
Simply returns the name of the service bodies table.
function sprintf()
Definition: installer.js:873
const _USER_LEVEL_SERVICE_BODY_ADMIN
static preparedQuery($sql, $params=array(), $fetchKeyPair=false)
Wrapper for preparing and executing a PDOStatement that returns a resultset e.g. SELECT SQL statement...
throwIfDuplicateMeetings()
Definition: NAWSImport.php:374
__construct($importFilePath, $initialValueForPublished)
Definition: NAWSImport.php:31
A Class for Format Codes.
getNumUsersCreated()
Definition: NAWSImport.php:435
const c_comdef_service_body__RSC__
const c_comdef_service_body__ASC__
throwIfDuplicateServiceBodies()
Definition: NAWSImport.php:323
$server
Definition: GetLangs.php:25
static MakeServer()
This is the factory for the server instantiation. It makes sure that only one instance exists...
This class is the main server class. It instantiates a PDO database object, and is the starting point...
defined('BMLT_EXEC') or define('BMLT_EXEC'
Definition: index.php:3
generateRandomString($length=10)
Definition: NAWSImport.php:425
createServiceBodiesAndUsers()
Definition: NAWSImport.php:112
getNumServiceBodiesCreated()
Definition: NAWSImport.php:430