PageRenderTime 73ms CodeModel.GetById 29ms RepoModel.GetById 0ms app.codeStats 1ms

/includes/query.php

https://bitbucket.org/kandsten/hitta.sverok.se
PHP | 2573 lines | 1924 code | 495 blank | 154 comment | 317 complexity | 2cd567f03bb9b98a6a909f898dff6d25 MD5 | raw file
Possible License(s): GPL-3.0, MIT
  1. <?php if (! constant("INSYSTEM")) { echo "Permission denied"; exit; } ?>
  2. <?php
  3. /*
  4. hitta.sverok.se site code
  5. http://hitta.sverok.se
  6. Copyright (c) 2010 Kriss Andsten
  7. Dual licensed under the MIT and GPL licenses.
  8. http://hitta.sverok.se/License
  9. */
  10. /* ===== Validated and in use ===== */
  11. include('polygon.php');
  12. function updateEntity($userid, $data)
  13. {
  14. global $dbh;
  15. //if (!$userid) { return; }
  16. /*
  17. Build us a query to insert/update the entry table
  18. */
  19. $sql = '';
  20. $id = false;
  21. $cols = array();
  22. $vals = array();
  23. $ph = array();
  24. $got_pk = false;
  25. if (array_key_exists('id', $data) && $data['id'] != '') { $got_pk = true; }
  26. /*
  27. Prepare the data that goes into the database;
  28. */
  29. $columnData = array();
  30. $entryMapper = entityFieldMap();
  31. foreach ($entryMapper as $key => $column) { if (array_key_exists($key, $data)) { $columnData[$column] = $data[$key]; } }
  32. $etype = entityShortTypeToType( $data['type'] );
  33. if ($etype == false) { return false; }
  34. $columnData['etype'] = $etype;
  35. $columnData['updatedBy'] = $userid;
  36. /* Fix the website address - if it doesn't contain the protocol, it should.. */
  37. $columnData['website'] = qualifyURL($columnData['website']);
  38. foreach ($columnData as $column => $value)
  39. {
  40. if ($column == 'active') { continue; }
  41. $cols[] = $column;
  42. $vals[] = $value;
  43. $ph[] = '?';
  44. }
  45. if (! $got_pk)
  46. {
  47. $cols[] = 'active';
  48. $ph[] = '?';
  49. $vals[] = 'yes';
  50. $sql = 'INSERT INTO entity (' . join(',', $cols) . ') VALUES (' . join(',', $ph) . ')';
  51. }
  52. else
  53. {
  54. $hasPermission = verifyEntityUserPermission($userid, $data['id']);
  55. $anyPermission = verifyEntityHasPermissions($data['id']);
  56. if (! $hasPermission && $anyPermission > 0) { return false; }
  57. $defs = array();
  58. $vals[] = $data['id'];
  59. foreach ($cols as $colname) { $defs[] = "$colname = ?"; };
  60. $sql = 'UPDATE entity SET ' . join(', ', $defs) . ' WHERE ID = ?';
  61. }
  62. /* Execute the query, figure out the ID if we don't already have it */
  63. $q = $dbh->prepare($sql);
  64. $q->execute( $vals );
  65. if (! $got_pk) { $id = $dbh->lastInsertId(); }
  66. else { $id = $data['id']; }
  67. if (! $id) { return false; }
  68. /*
  69. Figure out the location
  70. */
  71. $locationData['latitude'] = $data['latitude'];
  72. $locationData['longitude'] = $data['longitude'];
  73. $locationData['region'] = $data['region'];
  74. $locationData['city'] = $data['city'];
  75. $locationData['address'] = $data['address'];
  76. $location = getLocationFor($locationData);
  77. updateEntityLocation($id, $location);
  78. updateEntityTags($id, $data['tags']);
  79. if ($userid != 0) { updateEntityPermissions( $userid, $id ); }
  80. return $id;
  81. }
  82. function updateEntityPermissions( $userid, $entity )
  83. {
  84. global $dbh;
  85. $query = $dbh->prepare("SELECT id, permission FROM user_entity WHERE entity = ?");
  86. $query->execute(array($entity));
  87. $row = $query->fetch();
  88. if (! $row['id'])
  89. {
  90. $insert = $dbh->prepare("INSERT INTO user_entity (user, entity, permission) VALUES (?, ?, 'bureaucrat')");
  91. $insert->execute(array($userid, $entity));
  92. }
  93. }
  94. /*
  95. START functions related to event data input/output
  96. */
  97. /*
  98. This is the main event update routine. It contains some checks and balances
  99. that need to be kept in place - as such, do not update events by other means
  100. than calling this routine unless you're really comfortable with what you're
  101. doing.
  102. Mainly we don't want archived events to change.
  103. */
  104. function updateEvent($userid, $data)
  105. {
  106. global $dbh;
  107. if (!$userid) { return false; }
  108. $hasPermission = verifyEntityUserPermission($userid, $data['entity']);
  109. $got_pk = false;
  110. if (array_key_exists('id', $data) && $data['id']) { $got_pk = true; }
  111. /* Is this event archived or otherwise impossible to update? */
  112. if ($got_pk && ! canUpdateEvent($data['id'])) { return false; }
  113. /*
  114. Build us a query to insert/update the entry table
  115. */
  116. $sql = '';
  117. $cols = array();
  118. $vals = array();
  119. $ph = array();
  120. /*
  121. Prepare the data that goes into the database;
  122. */
  123. $columnData = array();
  124. $entryMapper = eventFieldMap();
  125. foreach ($entryMapper as $key => $column) { if (array_key_exists($key, $data)) { $columnData[$column] = $data[$key]; } }
  126. $columnData['updatedBy'] = $userid;
  127. /* Fix the website address - if it doesn't contain the protocol, it should.. */
  128. $columnData['website'] = qualifyURL($columnData['website']);
  129. foreach ($columnData as $column => $value)
  130. {
  131. if ($column == 'active') { continue; }
  132. # Fulhack, should be rewritten at some point (along with this routine + editor.php)
  133. if ($column == 'path') { continue; }
  134. if ($column == 'filename') { continue; }
  135. $cols[] = $column;
  136. $vals[] = $value;
  137. $ph[] = '?';
  138. }
  139. if (! $got_pk) {
  140. /* Is this added by a user without permissions? If so, make it an inactive event... */
  141. $cols[] = 'active';
  142. $ph[] = '?';
  143. $vals[] = ($hasPermission) ? 'yes' : 'no';
  144. $cols[] = 'created';
  145. $ph[] = 'now()';
  146. $sql = 'INSERT INTO event (' . join(',', $cols) . ') VALUES (' . join(',', $ph) . ')';
  147. }
  148. else
  149. {
  150. /* No updates for users lacking permissions */
  151. if (! $hasPermission) { return false; }
  152. $defs = array();
  153. $vals[] = $data['id'];
  154. foreach ($cols as $colname) { $defs[] = "$colname = ?"; };
  155. $sql = 'UPDATE event SET ' . join(', ', $defs) . ' WHERE ID = ?';
  156. }
  157. /* Execute the query, figure out the ID if we don't already have it */
  158. $q = $dbh->prepare($sql);
  159. $q->execute( $vals );
  160. if (! $got_pk) { $id = $dbh->lastInsertId(); }
  161. else { $id = $data['id']; }
  162. if (! $id) { return false; }
  163. /*
  164. Figure out the location
  165. */
  166. $locationData['latitude'] = $data['latitude'];
  167. $locationData['longitude'] = $data['longitude'];
  168. $locationData['region'] = $data['region'];
  169. $locationData['city'] = $data['city'];
  170. $locationData['address'] = $data['address'];
  171. $location = getLocationFor($locationData);
  172. /*
  173. If we didn't get an ID for this event, it's either new or cloned. Either how, we
  174. want updateEventLocation() to register this as a new entry for the given location.
  175. */
  176. if (! $got_pk)
  177. { updateEventLocation($id, $location, true); }
  178. else
  179. { updateEventLocation($id, $location); }
  180. updateEventTags($id, $data['tags']);
  181. updateEventGrants($id, $data['grants']);
  182. updateBanner('event', $id, $data['filename'], $data['path']);
  183. return $id;
  184. }
  185. function canUpdateEvent($eventId)
  186. {
  187. global $dbh;
  188. $query = array();
  189. $query['columns'] = array('id', 'archived');
  190. $query['tables'] = array('event');
  191. $query['where'] = array('id = ?');
  192. $q = $dbh->prepare( makeQuerySelect($query) );
  193. $q->execute(array( $eventId ));
  194. $row = $q->fetch();
  195. if (isset($row['archived']) && $row['archived'] == 'false') { return true; }
  196. return false;
  197. }
  198. function updateBanner($type, $id, $filename, $path)
  199. {
  200. global $dbh, $site;
  201. if ($type != 'event' && $type != 'entity' && $type != 'user') { return false; }
  202. if (! file_exists($site['basePath'] . $path)) { return false; }
  203. $fileHash = md5_file($site['basePath'] . $path);
  204. $extension = getExtensionByFilename($filename);
  205. if ($extension == '') { return false; }
  206. if ($fileHash == '') { return false; }
  207. $q = $dbh->prepare("SELECT id FROM banner WHERE hash = ?");
  208. $q->execute(array( $fileHash ));
  209. $row = $q->fetch();
  210. $bannerId = $row['id'];
  211. /* Store new banner? */
  212. if (! $row['id'])
  213. {
  214. $q = $dbh->prepare("INSERT INTO banner (path, hash, filename) VALUES ('temp', ?, ?)");
  215. $q->execute(array( $fileHash, $filename ));
  216. $bannerId = $dbh->lastInsertId();
  217. $newPath = 'banners/raw/' . $bannerId . '.' . $extension;
  218. rename($path, $site['basePath'] . $newPath);
  219. chmod($newPath, 0755);
  220. $dbh->query("UPDATE banner SET path = '$newPath' WHERE id = $bannerId");
  221. sendBannerUpdateNotification($bannerId, $newPath);
  222. }
  223. $q = $dbh->prepare("UPDATE $type SET banner = ? WHERE id = ?");
  224. $q->execute(array( $bannerId, $id ));
  225. }
  226. function sendBannerUpdateNotification($id, $path)
  227. {
  228. global $dbh, $site;
  229. $q = $dbh->prepare("SELECT email FROM user WHERE maintainer = 'true'");
  230. $q->execute();
  231. $maint = array();
  232. foreach ($q->fetchAll() as $row)
  233. {
  234. $maint[] = $row['email'];
  235. }
  236. $header = "MIME-Version: 1.0\n";
  237. $header .= "Content-Type: multipart/alternative; boundary=\"==Hitta-robot\"\n";
  238. #$header .= "To: \"Inget svar\" <noreply@shortpacket.org>\n";
  239. $header .= "From: \"Hitta Robotpost\" <hitta@sverok.se>\n";
  240. $to = join(', ', $maint);
  241. $body = "
  242. This is a multi-part message in MIME format.
  243. --==Hitta-robot
  244. Content-Type: text/plain; charset=UTF-8
  245. Content-Transfer-Encoding: 8bit
  246. Besök " . $site['baseHref'] . "maintain/ för att godkänna eller avslå bannern.
  247. --==Hitta-robot
  248. Content-Type: text/html; charset=UTF-8
  249. Content-Transfer-Encoding: 8bit
  250. <html>
  251. <head>
  252. <title>Ny banner på hitta ($id)</title>
  253. </head>
  254. <body>
  255. <p>Besök <a href=\"" . $site['baseHref'] . "maintain/\">Hitta Underhåll</a> för att godkänna eller avslå bannern.</p>
  256. <img src=\"" . $site['baseHref'] . $path . "\" />
  257. </body>
  258. </html>
  259. --==Hitta-robot--
  260. ";
  261. $rv = mail(
  262. $to,
  263. "Ny banner på hitta ($id)",
  264. $body,
  265. $header
  266. );
  267. };
  268. function approveBanner($params)
  269. {
  270. global $dbh;
  271. $validGrades = array('excellent','good','poor','bad');
  272. $id = $params[0];
  273. $grade = $params[1];
  274. // We got sane input and the user is logged in and a maintainer, lets go!
  275. if ($id !== '' && $id > 0 && \ /* Banner ID 0 is magical, leave it be. */
  276. in_array($grade, $validGrades) && \
  277. array_key_exists('maintainer', $_SESSION) && $_SESSION['maintainer'] == true
  278. ) {
  279. $q = $dbh->prepare("UPDATE banner SET approved = 'true', quality = ? where id = ?");
  280. $q->execute(array($grade, $id));
  281. return array(true);
  282. } else {
  283. return array(false);
  284. }
  285. }
  286. function deleteBanner($id)
  287. {
  288. global $dbh;
  289. if ($id == 0 || $id == '') { return array(false); } /* Banner ID 0 is magical, we should leave it be */
  290. if (! array_key_exists('maintainer', $_SESSION) || $_SESSION['maintainer'] != true) { return array(false); };
  291. /*
  292. If we don't reset the banners in the event table before removing
  293. the banner from the banners table, some event queries won't return any
  294. results.
  295. */
  296. $q = $dbh->prepare("UPDATE event SET banner = 0 WHERE banner = ?");
  297. $q->execute($id);
  298. $q = $dbh->prepare("DELETE FROM banner where id = ?");
  299. $q->execute($id);
  300. return array(true);
  301. }
  302. /* Get some xml, turn into banner, put in right places, ..., profit. */
  303. function importBanner($args) {
  304. global $dbh, $site;
  305. $xml = new SimpleXMLElement($args[0]);
  306. $fn = basename($xml['filename']); //Somekind of security. Avoids ../-attacks.
  307. if(!($bin = base64_decode($xml[0], true))) {
  308. return array('FAIL - encoding error - image data contains letters outside the base64 alphabet.');
  309. }
  310. if(!($img = imagecreatefromstring($bin))) {
  311. return array("FAIL - couldn't create image from input data.");
  312. }
  313. if(imagesx($img) != 1200 && imagesy($img) != 1000) {
  314. return array('FAIL - weird size. Allowed size is 1200x1000. Your size is '.imagesx($img).'x'.imagesy($img).'.');
  315. }
  316. $hash = md5($bin);
  317. $q = $dbh->prepare("SELECT count(id) as count FROM banner WHERE hash = ? OR filename = ?");
  318. $q->execute(array( $hash, $fn ));
  319. $row = $q->fetch();
  320. if($row['count'] > 0) {
  321. return array('FAIL - file already uploaded.');
  322. }
  323. // Insert to get an id to use as filename.
  324. $q = $dbh->prepare("INSERT INTO banner (path, hash, filename, website, created) VALUES ('temp', ?, ?, ?, NOW())");
  325. $q->execute(array( $hash, $fn, $xml['website'] ));
  326. $bannerId = $dbh->lastInsertId();
  327. // Write file to disk.
  328. $newPath = "banners/raw/$bannerId.".getExtensionByFilename($fn);
  329. $fh = fopen($site['basePath'].$newPath, 'wb');
  330. fwrite($fh, $bin);
  331. fclose($fh);
  332. //Update with new path
  333. $dbh->query("UPDATE banner SET path = '$newPath' WHERE id = $bannerId");
  334. // Notify maintainers
  335. sendBannerUpdateNotification($bannerId, $newPath);
  336. return array('OK');
  337. }
  338. function getUnapprovedBanners()
  339. {
  340. global $dbh;
  341. $q = $dbh->prepare("SELECT id, filename, path FROM banner WHERE approved = 'false' AND hash IS NOT NULL AND id > 0");
  342. $q->execute();
  343. return $q->fetchAll();
  344. }
  345. function getExtensionByFilename($filename)
  346. {
  347. $eman = strrev($filename);
  348. $lastpos = strlen($eman) - strpos($eman, '.');
  349. if ($lastpos == 0) { return false; }
  350. $extn = substr($filename, $lastpos );
  351. $extn = strtolower($extn);
  352. return $extn;
  353. }
  354. function qualifyURL( $website )
  355. {
  356. if ($website == '') { return ''; }
  357. if (preg_match('|^https:|i', $website) || preg_match('|http:|i', $website)) { return $website; }
  358. return "http://" . $website;
  359. }
  360. /*
  361. See if we already have a location on file.
  362. Primarily, see if the address/city/region matches - if so, use that.
  363. As a fallback, check the coordinates (and curse any rounding errors) - if found, use that.
  364. Finally, create a new location.
  365. */
  366. function getLocationFor($location)
  367. {
  368. global $dbh;
  369. $q = $dbh->prepare("SELECT id FROM location WHERE address = ? AND city = ? AND region = ?");
  370. $q->execute(array( $location['address'], $location['city'], $location['region'] ));
  371. $row = $q->fetch();
  372. if (!isset($row['id']))
  373. {
  374. $q = $dbh->prepare("SELECT id FROM location WHERE latitude = ? AND longitude = ?");
  375. $q->execute(array( $location['latitude'], $location['longitude'] ));
  376. $row = $q->fetch();
  377. }
  378. /* PDO is not really useful here. What's wrong with a way of counting results? */
  379. if (isset($row['id']))
  380. {
  381. return $row['id'];
  382. }
  383. else
  384. {
  385. $location['municipality'] = getMunicipality($location['latitude'], $location['longitude'], $location['city']);
  386. $q = $dbh->prepare("INSERT INTO location (address, city, region, municipality, active, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?, ?)");
  387. if (! array_key_exists('active', $location)) { $location['active'] = 'false'; }
  388. $q->execute(array(
  389. $location['address'],
  390. $location['city'],
  391. $location['region'],
  392. $location['municipality'],
  393. $location['active'],
  394. $location['latitude'],
  395. $location['longitude'],
  396. ));
  397. return $dbh->lastInsertId();
  398. }
  399. }
  400. function getLocation($id)
  401. {
  402. global $dbh;
  403. $q = $dbh->prepare("SELECT address, id, latitude, longitude, city, region FROM location WHERE id=?");
  404. $q->execute(array( $id ));
  405. $row = $q->fetch();
  406. $return = array();
  407. $return['latitude'] = $row['latitude'];
  408. $return['longitude'] = $row['longitude'];
  409. $return['city'] = $row['city'];
  410. $return['region'] = $row['region'];
  411. $return['address'] = $row['address'];
  412. return $return;
  413. }
  414. function minusLocation($locationId)
  415. {
  416. global $dbh;
  417. if ($locationId == 0) { return; }
  418. $q = $dbh->prepare("UPDATE location SET weight = weight - 1 WHERE id = ? AND weight > 0");
  419. $q->execute(array(
  420. $locationId
  421. ));
  422. $q = $dbh->prepare("UPDATE location SET active = 'false' WHERE id = ? AND weight = 0 AND active = 'true'");
  423. $q->execute(array(
  424. $locationId
  425. ));
  426. /* Did we actually flip this location from active to inactive? */
  427. if ($q->rowCount() == 0) { return; }
  428. $marker_q = $dbh->prepare("SELECT marker, zoom FROM location_markers WHERE location = ?");
  429. $marker_q->execute(array(
  430. $locationId
  431. ));
  432. $markers = array();
  433. foreach ($marker_q->fetchAll() as $row)
  434. {
  435. $markers[] = $row['marker'];
  436. }
  437. $markerString = join(',', $markers);
  438. $marker_weight_q = $dbh->prepare("SELECT markers.id, count(markers.id) as weight FROM markers, location_markers WHERE location_markers.marker = markers.id AND location_markers.marker IN ($markerString) GROUP BY markers.id");
  439. $marker_weight_q->execute();
  440. foreach ($marker_weight_q->fetchAll() as $row)
  441. {
  442. /* We're about to remove the last location <-> marker connection for this marker, zap the marker */
  443. if ($row['weight'] == 1) { $dbh->exec("DELETE from markers WHERE id = " . $row['id']); }
  444. }
  445. #error_log("Deleting location markers for $locationId");
  446. $q = $dbh->prepare("DELETE FROM location_markers WHERE location = ?");
  447. $q->execute(array(
  448. $locationId
  449. ));
  450. }
  451. function plusLocation($locationId)
  452. {
  453. global $dbh;
  454. $q = $dbh->prepare("UPDATE location SET weight = weight + 1 WHERE id = ?");
  455. $q->execute(array(
  456. $locationId
  457. ));
  458. $q = $dbh->prepare("UPDATE location SET active = 'true' WHERE id = ? AND active = 'false'");
  459. $q->execute(array(
  460. $locationId
  461. ));
  462. /* Did we actually flip this location from inactive to active? */
  463. if ($q->rowCount() == 0) { return; }
  464. $loc = $dbh->prepare("SELECT latitude, longitude FROM location WHERE id = ?");
  465. $loc->execute(array(
  466. $locationId
  467. ));
  468. $row = $loc->fetch();
  469. $lat = $row['latitude'];
  470. $lng = $row['longitude'];
  471. /* Okay, this is where the real fun begins.. */
  472. $q = $dbh->prepare("SELECT zoomLevel, kilometers FROM marker_aggregation");
  473. $q->execute();
  474. foreach ($q->fetchAll() as $row)
  475. {
  476. /* See if we have any marker within our zoomLevel distance using the great circle... */
  477. $dist = $dbh->prepare("SELECT latitude, longitude, id, ( 6371 * acos( cos( radians(latitude) ) * cos( radians( $lat ) ) * cos( radians( $lng ) - radians(longitude) ) + sin( radians(latitude) ) * sin( radians( $lat ) ) ) ) AS distance FROM markers WHERE zoom = ? ORDER BY distance ASC LIMIT 1");
  478. $dist->execute(array(
  479. $row['zoomLevel']
  480. ));
  481. $marker = false;
  482. $distrow = $dist->fetch();
  483. if ($distrow['distance'] <= $row['kilometers']) {
  484. /* We did have a marker? Okay, attach us to the closest one */
  485. $marker = $distrow['id'];
  486. }
  487. else
  488. {
  489. /* We didn't have a marker? Okay, create a new one */
  490. $marker_q = $dbh->prepare("INSERT INTO markers (zoom, latitude, longitude) VALUES (?, ?, ?)");
  491. $marker_q->execute(array(
  492. $row['zoomLevel'], $lat, $lng
  493. ));
  494. $marker = $dbh->lastinsertid();
  495. }
  496. if ($marker != false)
  497. {
  498. /* Add location <-> marker glue */
  499. #error_log("Connecting $locationId to $marker");
  500. $lm_update = $dbh->prepare("INSERT INTO location_markers (location, marker, zoom) VALUES (?, ?, ?)");
  501. $lm_update->execute(array(
  502. $locationId,
  503. $marker,
  504. $row['zoomLevel']
  505. ));
  506. }
  507. }
  508. }
  509. function updateEventLocation($id, $newLocation, $forceNew = false)
  510. {
  511. global $dbh;
  512. $q = $dbh->prepare("SELECT location FROM event WHERE id=?");
  513. $q->execute(array($id));
  514. $row = $q->fetch();
  515. $oldLocation = $row['location'];
  516. /* No change? Do nothing. Unless told to do so explictly */
  517. if ($oldLocation == $newLocation && ! $forceNew) { return; }
  518. /* We should do minusLocation if the old and new locations differ */
  519. if ($oldLocation != $newLocation) {
  520. $q = $dbh->prepare("UPDATE event SET location = ? WHERE id = ?");
  521. $q->execute(array(
  522. $newLocation,
  523. $id
  524. ));
  525. if ($oldLocation) { minusLocation($oldLocation); }
  526. }
  527. /* We should always do plusLocation */
  528. plusLocation($newLocation);
  529. }
  530. function updateEntityLocation($id, $newLocation)
  531. {
  532. global $dbh;
  533. $q = $dbh->prepare("SELECT location FROM entity WHERE id=?");
  534. $q->execute(array(
  535. $id
  536. ));
  537. $row = $q->fetch();
  538. $oldLocation = $row['location'];
  539. /* No change? Do nothing */
  540. if ($oldLocation == $newLocation) { return; }
  541. minusLocation($oldLocation);
  542. plusLocation($newLocation);
  543. $q = $dbh->prepare("UPDATE entity SET location = ? WHERE id = ?");
  544. $q->execute(array(
  545. $newLocation,
  546. $id
  547. ));
  548. }
  549. function verifyEntityUserPermission($userid, $entity)
  550. {
  551. global $dbh;
  552. $q = $dbh->prepare("SELECT user, entity FROM user_entity WHERE entity = ? AND user = ? AND (permission = 'bureaucrat' OR permission = 'editor')");
  553. $q->execute(array($entity, $userid));
  554. $row = $q->fetch();
  555. if ($row['user'] == $userid && $row['user'] != '') { return true; }
  556. return false;
  557. }
  558. function verifyEntityHasPermissions($entity)
  559. {
  560. global $dbh;
  561. $q = $dbh->prepare("SELECT count(user) as hits FROM user_entity WHERE entity = ?");
  562. $q->execute(array($entity));
  563. $row = $q->fetch();
  564. return $row['hits'];
  565. }
  566. function updateEventTags($id, $taglist)
  567. {
  568. global $dbh;
  569. $targetTags = array();
  570. $currentTags = array();
  571. foreach (getTagsByEvent($id) as $tag) { $currentTags[ $tag ] = $tag; }
  572. $changes = array(
  573. 'added' => array(),
  574. 'removed' => array()
  575. );
  576. if ($taglist != '') {
  577. foreach (split(',', $taglist) as $tag) { $targetTags[ $tag ] = $tag; }
  578. }
  579. /* Remove and add the relevant tag entries */
  580. foreach ($currentTags as $tag)
  581. {
  582. if (array_key_exists($tag, $targetTags)) { continue; }
  583. $changes['removed'][$tag] = $tag;
  584. detachTagFromEvent($tag, $id);
  585. }
  586. foreach ($targetTags as $tag)
  587. {
  588. if (array_key_exists($tag, $currentTags)) { continue; }
  589. $changes['added'][$tag] = $tag;
  590. attachTagToEvent($tag, $id);
  591. }
  592. adjustTagTagRelation($changes, $currentTags, $targetTags);
  593. }
  594. function updateEntityTags($id, $taglist)
  595. {
  596. global $dbh;
  597. $targetTags = array();
  598. $currentTags = array();
  599. foreach (getTagsByEntity($id) as $tag) { $currentTags[ $tag ] = $tag; }
  600. $changes = array(
  601. 'added' => array(),
  602. 'removed' => array()
  603. );
  604. if ($taglist != '') {
  605. foreach (split(',', $taglist) as $tag) { $targetTags[ $tag ] = $tag; }
  606. }
  607. /* Remove and add the relevant tag entries */
  608. foreach ($currentTags as $tag)
  609. {
  610. if (array_key_exists($tag, $targetTags)) { continue; }
  611. $changes['removed'][$tag] = $tag;
  612. detachTagFromEntity($tag, $id);
  613. }
  614. foreach ($targetTags as $tag)
  615. {
  616. if (array_key_exists($tag, $currentTags)) { continue; }
  617. $changes['added'][$tag] = $tag;
  618. attachTagToEntity($tag, $id);
  619. }
  620. adjustTagTagRelation($changes, $currentTags, $targetTags);
  621. }
  622. function adjustTagTagRelation($changes, $current, $target)
  623. {
  624. global $dbh;
  625. /* Strengthen the relation between the added tags and all the tags in the target set */
  626. $values = array();
  627. foreach ($changes['added'] as $tag)
  628. {
  629. foreach ($target as $relation)
  630. {
  631. if ($tag == $relation) { continue; }
  632. error_log("++ $tag $relation");
  633. $values[] = '(' . intval($tag) . ',' . intval($relation) . ')';
  634. }
  635. }
  636. if (count($values) > 0)
  637. {
  638. $query = "INSERT INTO tag_tag (t_tag, t_related) VALUES " . join(',', $values) . " ON DUPLICATE KEY UPDATE t_weight = t_weight + 1";
  639. $dbh->exec($query);
  640. }
  641. /* Weaken the relation between the removed tags and the tags in the current set */
  642. $tagArray = array();
  643. $relationArray = array();
  644. foreach ($changes['removed'] as $tag)
  645. {
  646. foreach ($current as $relation)
  647. {
  648. if ($tag == $relation) { continue; }
  649. $tagArray[$tag] = $tag;
  650. $relationArray[$relation] = $relation;
  651. error_log("-- $tag $relation");
  652. }
  653. }
  654. $tagString = join(',', $tagArray);
  655. $relationString = join(',', $relationArray);
  656. if (count($tagArray) > 0 && count($relationArray) > 0)
  657. {
  658. $query = "UPDATE LOW_PRIORITY tag_tag SET t_weight=t_weight-1 WHERE t_weight > 0 AND
  659. ((t_tag IN ($tagString) AND t_related IN ($relationString)) OR (t_tag IN ($relationString) AND t_related IN ($tagString)))";
  660. #error_log($query);
  661. $dbh->exec($query);
  662. }
  663. /* Housekeeping */
  664. $dbh->exec("DELETE LOW_PRIORITY FROM tag_tag WHERE t_weight = 0");
  665. }
  666. function updateEventGrants($id, $grantlist)
  667. {
  668. global $dbh;
  669. $targetGrants = array();
  670. $currentGrants = array();
  671. foreach (getGrantsByEvent($id) as $grant) { $currentGrants[ $grant ] = $grant; }
  672. if ($grantlist != '') {
  673. foreach (split(',', $grantlist) as $grant) { $targetGrants[ $grant ] = $grant; }
  674. }
  675. /* Remove and add the relevant tag entries */
  676. foreach ($currentGrants as $grant)
  677. {
  678. if (array_key_exists($grant, $targetGrants)) { continue; }
  679. detachGrantFromEvent($grant, $id);
  680. }
  681. foreach ($targetGrants as $grant)
  682. {
  683. if (array_key_exists($grant, $currentGrants)) { continue; }
  684. attachGrantToEvent($grant, $id);
  685. }
  686. }
  687. function eventFieldMap()
  688. {
  689. /* Map the entry description keys to the relevant SQL columns. We do this for two reasons:
  690. One, to provide a mapping if we need to change the data structure at some point.
  691. Two, to ensure that we only pass relevant keys from the entry desc.
  692. */
  693. return array(
  694. 'active' => 'active',
  695. 'fulldesc' => 'fullDesc',
  696. 'shortdesc' => 'shortDesc',
  697. 'name' => 'name',
  698. 'location' => 'location',
  699. 'locationdesc' => 'locationDesc',
  700. 'source' => 'source',
  701. 'end' => 'end',
  702. 'start' => 'start',
  703. 'starttime' => 'startTime',
  704. 'phone' => 'phone',
  705. 'id' => 'id',
  706. 'filename' => 'filename',
  707. 'path' => 'path',
  708. 'entity' => 'entity',
  709. 'age' => 'targetAge',
  710. 'website' => 'website',
  711. 'email' => 'email',
  712. 'attendance' => 'attendance',
  713. 'contact' => 'contactDesc',
  714. 'parent' => 'instanceOf',
  715. 'updated' => 'updated',
  716. 'archived' => 'archived'
  717. );
  718. }
  719. function entityShortTypeToType($short)
  720. {
  721. global $dbh;
  722. $q = $dbh->prepare("SELECT id, name FROM entity_type WHERE short = ?");
  723. $q->execute(array( $short ));
  724. $row = $q->fetch();
  725. if (! $row['id']) { return false; }
  726. return $row['id'];
  727. }
  728. function entityFieldMap()
  729. {
  730. /* Map the entry description keys to the relevant SQL columns. We do this for two reasons:
  731. One, to provide a mapping if we need to change the data structure at some point.
  732. Two, to ensure that we only pass relevant keys from the entry desc.
  733. */
  734. return array(
  735. 'active' => 'active',
  736. 'fulldesc' => 'fullDesc',
  737. 'shortdesc' => 'shortDesc',
  738. 'name' => 'name',
  739. 'location' => 'location',
  740. 'locationdesc' => 'locationDesc',
  741. 'id' => 'id',
  742. 'fid' => 'groupKey',
  743. 'age' => 'targetAge',
  744. 'website' => 'website',
  745. 'email' => 'email',
  746. 'attendance' => 'attendance',
  747. 'contact' => 'contactDesc',
  748. #'filename' => 'filename',
  749. 'bannerpath' => 'bannerpath',
  750. 'updated' => 'updated'
  751. );
  752. }
  753. function attachTagToEntity($tag, $event)
  754. {
  755. global $dbh;
  756. $q = $dbh->prepare("INSERT INTO org_tag (org, tag) VALUES (?, ?)");
  757. return $q->execute(array( $event, $tag ));
  758. }
  759. function detachTagFromEntity($tag, $event)
  760. {
  761. global $dbh;
  762. $q = $dbh->prepare("DELETE FROM org_tag WHERE org = ? AND tag = ?");
  763. return $q->execute(array( $event, $tag ));
  764. }
  765. function attachTagToEvent($tag, $event)
  766. {
  767. global $dbh;
  768. $q = $dbh->prepare("INSERT INTO event_tag (event, tag) VALUES (?, ?)");
  769. return $q->execute(array( $event, $tag ));
  770. }
  771. function detachTagFromEvent($tag, $event)
  772. {
  773. global $dbh;
  774. $q = $dbh->prepare("DELETE FROM event_tag WHERE event = ? AND tag = ?");
  775. return $q->execute(array( $event, $tag ));
  776. }
  777. function attachGrantToEvent($grant, $event)
  778. {
  779. global $dbh;
  780. $q = $dbh->prepare("INSERT INTO event_grant (event, grantid) VALUES (?, ?)");
  781. return $q->execute(array( $event, $grant ));
  782. }
  783. function detachGrantFromEvent($grant, $event)
  784. {
  785. global $dbh;
  786. $q = $dbh->prepare("DELETE FROM event_grant WHERE event = ? AND grantid = ?");
  787. return $q->execute(array( $event, $grant ));
  788. }
  789. function getTagsByEntity($entity)
  790. {
  791. global $dbh;
  792. $tags = array();
  793. $q = $dbh->prepare("SELECT tag FROM org_tag WHERE org = ?");
  794. $q->execute(array($entity));
  795. foreach ($q->fetchAll() as $row) { $tags[] = $row['tag']; }
  796. return $tags;
  797. }
  798. function getTagsByEvent($event)
  799. {
  800. global $dbh;
  801. $tags = array();
  802. $q = $dbh->prepare("SELECT tag FROM event_tag WHERE event = ?");
  803. $q->execute(array($event));
  804. foreach ($q->fetchAll() as $row) { $tags[] = $row['tag']; }
  805. return $tags;
  806. }
  807. function getGrantsByEvent($event)
  808. {
  809. global $dbh;
  810. $grants = array();
  811. $q = $dbh->prepare("SELECT grantid FROM event_grant WHERE event = ?");
  812. $q->execute(array($event));
  813. foreach ($q->fetchAll() as $row) { $grants[] = $row['grantid']; }
  814. return $grants;
  815. }
  816. function getNodeContent($type, $id)
  817. {
  818. global $dbh;
  819. $query = array();
  820. $tags = array();
  821. $return = array();
  822. $q = $dbh->prepare("INSERT into stats (type, id, hits) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE hits = hits + 1");
  823. $q->execute(array($type, $id));
  824. if ($type == 'en')
  825. {
  826. $query['columns'] = array(
  827. 'attendance','targetAge','website', 'email', 'location.city', 'entity.name', 'shortDesc',
  828. 'fullDesc', 'contactDesc', 'groupKey', 'entity.id', 'entity.name as ename', 'entity.id as eid',
  829. 'entity.name as eName', 'entity.id as eId');
  830. $query['tables'] = array('entity', 'location');
  831. $query['where'] = array("entity.active = 'yes'", 'entity.location = location.id', 'entity.id = ?');
  832. $tagQuery = "SELECT tag.id, name FROM tag, org_tag WHERE active = 1 AND org_tag.tag = tag.id AND org_tag.org = ? ORDER BY name";
  833. }
  834. else if ($type == 'ev')
  835. {
  836. $query['columns'] = array(
  837. 'event.attendance','event.targetAge','event.website', 'event.email', 'location.city',
  838. 'event.name', 'event.shortDesc', 'event.fullDesc', 'event.contactDesc', 'start', 'end',
  839. 'event.id', 'entity.name as eName', 'entity.id as eId',
  840. 'DATE_FORMAT(starttime, "%H:%i") as startTime', 'event.contactDesc',
  841. 'location.address as address', 'event.phone', 'banner.approved as bannerApproved', 'banner.path');
  842. $query['tables'] = array('event', 'location', 'entity', 'banner');
  843. $query['where'] = array(
  844. "event.active = 'yes'", 'event.location = location.id', 'event.id = ?',
  845. 'event.entity = entity.id', 'event.banner = banner.id');
  846. $tagQuery = "SELECT tag.id, name FROM tag, event_tag WHERE active = 1 AND event_tag.tag = tag.id AND event_tag.event = ? ORDER BY name";
  847. }
  848. else
  849. {
  850. return false;
  851. }
  852. $rs = $dbh->prepare(makeQuerySelect($query));
  853. $rs->execute(array( $id ));
  854. $row = $rs->fetch();
  855. $tq = $dbh->prepare($tagQuery);
  856. $tq->execute(array( $id ));
  857. $return = $row;
  858. $return['tags'] = array();
  859. $tagNames = array();
  860. foreach ($tq->fetchAll() as $row)
  861. {
  862. $return['tags'][] = intval($row['id']);
  863. $tagNames[ $row['id'] ] = $row['name'];
  864. }
  865. return array($type, $tagNames, $return);
  866. }
  867. function getEntityByGroupKey($gk)
  868. {
  869. global $dbh;
  870. $q = $dbh->prepare("
  871. SELECT entity.id as id FROM entity WHERE groupKey = ? AND groupKey != ''
  872. ");
  873. $q->execute(array($gk));
  874. $row = $q->fetch();
  875. return getEntity( $row['id'] );
  876. }
  877. function getEntity($id)
  878. {
  879. global $dbh;
  880. $q = $dbh->prepare("
  881. SELECT entity.id as id, entity.name as name, groupKey as fid, location, etype as entityTypeid,
  882. entity_type.name as type, entity_type.short as typeShort, location.city,
  883. shortDesc, fullDesc, contactDesc, locationDesc, email, website , attendance, targetAge
  884. FROM entity, entity_type, location
  885. WHERE entity.etype = entity_type.id
  886. AND location.id = entity.location
  887. AND entity.id = ?");
  888. $result = array();
  889. if (! $q->execute(array($id))) { return false; };
  890. $row = $q->fetch();
  891. $result['id'] = intval($row['id']);
  892. $result['fid'] = $row['fid'];
  893. $result['name'] = $row['name'];
  894. $result['type'] = $row['type'];
  895. $result['shortdesc'] = $row['shortDesc'];
  896. $result['fulldesc'] = $row['fullDesc'];
  897. $result['contact'] = $row['contactDesc'];
  898. $result['email'] = $row['email'];
  899. $result['website'] = $row['website'];
  900. $result['age'] = $row['targetAge'];
  901. $result['attendance'] = $row['attendance'];
  902. $result['locationdesc'] = $row['locationDesc'];
  903. $result['typeid'] = intval($row['entityTypeid']);
  904. $result['tags'] = join(',', getTagsByEntity($id));
  905. $result['type'] = $row['typeShort'];
  906. $locationData = getLocation($row['location']);
  907. $result['city'] = $locationData['city'];
  908. $result['region'] = $locationData['region'];
  909. $result['address'] = $locationData['address'];
  910. $result['latitude'] = $locationData['latitude'];
  911. $result['longitude'] = $locationData['longitude'];
  912. return $result;
  913. }
  914. function getEntityTypes()
  915. {
  916. global $dbh;
  917. $q = $dbh->prepare("SELECT id, name, short, private, sortOrder FROM entity_type ORDER BY sortOrder ASC");
  918. $q->execute();
  919. return $q->fetchAll();
  920. }
  921. function deleteEvent($userid, $id)
  922. {
  923. global $dbh, $site;
  924. $q = $dbh->prepare("SELECT entity,location FROM event WHERE id = ?");
  925. if (! $q->execute(array($id))) { return false; }
  926. $row = $q->fetch();
  927. if (!verifyEntityUserPermission($userid, $row['entity'])) { header('Location: ' . $site['baseHref'] . 'fail'); exit; }
  928. minusLocation($row['location']);
  929. updateEventTags($id, '');
  930. updateEventGrants($id, '');
  931. $deleteQuery = $dbh->prepare("DELETE from event WHERE id = ?");
  932. $deleteQuery->execute(array( $id ));
  933. }
  934. function getEvent($id)
  935. {
  936. global $dbh;
  937. $q = $dbh->prepare("SELECT * FROM banner,event WHERE event.id = ? AND banner.id = event.banner");
  938. $result = array();
  939. if (! $q->execute(array($id))) { return false; };
  940. $q->execute(array( $id ));
  941. $fieldMap = eventFieldMap();
  942. $reverseMap = array();
  943. foreach ($fieldMap as $field => $column)
  944. {
  945. $reverseMap[ $column ] = $field;
  946. }
  947. $row = $q->fetch();
  948. foreach ($row as $col => $value)
  949. {
  950. if (array_key_exists($col, $reverseMap))
  951. {
  952. $result[ $reverseMap[$col] ] = $value;
  953. }
  954. }
  955. $result['starttime'] = substr($result['starttime'],0,5);
  956. $result['tags'] = join(',', getTagsByEvent($id));
  957. $result['grants'] = join(',', getGrantsByEvent($id));
  958. $locationData = getLocation($result['location']);
  959. $result['city'] = $locationData['city'];
  960. $result['region'] = $locationData['region'];
  961. $result['address'] = $locationData['address'];
  962. $result['latitude'] = $locationData['latitude'];
  963. $result['longitude'] = $locationData['longitude'];
  964. return $result;
  965. }
  966. function getEventInstances($id)
  967. {
  968. global $dbh;
  969. $query['columns'] = array('id', 'start');
  970. $query['tables'] = array('event', );
  971. $query['where'] = array('instanceOf = ?');
  972. $q = $dbh->prepare( makeQuerySelect($query) );
  973. $q->execute(array( $id ));
  974. return $q->fetchAll();
  975. }
  976. /* END event functions */
  977. function getOrCreateTagByName($tag)
  978. {
  979. $tagEntry = getTagByName($tag);
  980. if ($tagEntry == false)
  981. {
  982. if (createTag($tag))
  983. {
  984. $tagEntry = getTagByName($tag);
  985. }
  986. }
  987. return $tagEntry;
  988. }
  989. function createTag($tag)
  990. {
  991. global $dbh;
  992. /*
  993. Keep the names somewhat sane - we don't want any funky stuff in the tag names and Unicode is a bit permissive.
  994. This Regex limits the tag names to (the unicode representation of) characters present in the ISO-8859-1 charset.
  995. */
  996. $tagSan = preg_replace('|[^a-z0-9 _.\-@\'\"\[\]ÀÁÂÄÅÆÇÈÉÊËÌÍÎÏ?ÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ]|i', '', $tag);
  997. if ($tagSan == '') { return false; }
  998. $ip = $_SERVER['REMOTE_ADDR'];
  999. if ($ip == '::1') { $ip = '127.0.0.1'; };
  1000. $q = $dbh->prepare("INSERT INTO tag (name, lcname, adder_ip, adder_date) VALUES (?, ?, INET_ATON(?), NOW())");
  1001. if (! $q->execute(array($tagSan, strtolower($tagSan), $ip))) { error_log("Execution failed in addTag"); return false; };
  1002. return true;
  1003. }
  1004. function getTagById($tagid)
  1005. {
  1006. global $dbh;
  1007. $results = array();
  1008. $q = $dbh->prepare("SELECT id, name, weight FROM tag WHERE id = ?");
  1009. if (! $q->execute(array($tagid))) { error_log("Execution failed in getTagById"); return false; };
  1010. $row = $q->fetch();
  1011. #if (count($row) == 1) { return false; }
  1012. $results[ $row['id'] ] = array(
  1013. 'name' => $row['name'],
  1014. 'weight' => $row['weight'],
  1015. 'id' => $row['id']
  1016. );
  1017. return array($results);
  1018. }
  1019. function getTagByName($tag)
  1020. {
  1021. global $dbh;
  1022. $results = array();
  1023. $q = $dbh->prepare("SELECT id, name, weight FROM tag WHERE lcname = ?");
  1024. if (! $q->execute(array(strtolower($tag)))) { error_log("Execution failed in getTagByName"); return false; };
  1025. $row = $q->fetch();
  1026. if (count($row) == 1) { return false; }
  1027. $results[ $row['id'] ] = array(
  1028. 'name' => $row['name'],
  1029. 'weight' => $row['weight'],
  1030. 'id' => $row['id']
  1031. );
  1032. return array($results);
  1033. }
  1034. function getTaglistByTags($tags, $exclude = null)
  1035. {
  1036. global $dbh;
  1037. $ptq = $dbh->prepare("SELECT distinct(t_related) as tag FROM tag_tag WHERE t_tag IN ($tags)");
  1038. $ptq->execute();
  1039. $relatedArray = explode(',', $tags);
  1040. foreach ($ptq->fetchAll() as $row) { $relatedArray[] = $row['tag']; }
  1041. if ($exclude != null)
  1042. {
  1043. $newRelatedArray = array();
  1044. foreach ($relatedArray as $item)
  1045. {
  1046. if (! array_key_exists($item, $exclude))
  1047. {
  1048. $newRelatedArray[] = $item;
  1049. }
  1050. }
  1051. $relatedArray = $newRelatedArray;
  1052. }
  1053. $relatedTags = implode(',', $relatedArray);
  1054. return $relatedTags;
  1055. }
  1056. function getNodesByMarkers($def, $markers)
  1057. {
  1058. global $dbh, $settings;
  1059. /* Double check that we're looking at numeric markers */
  1060. foreach ($markers as $m) { if (! is_numeric($m)) { return -1; } }
  1061. if (count($markers) > 3) { return -2; } # Keep this low for now. It'd be ugly if we requested a few thousand of these at once..
  1062. $markerString = join(',', $markers);
  1063. $entityString = '';
  1064. $tagArray = array();
  1065. $tagNames = array();
  1066. $locationNames = array();
  1067. $categories = array();
  1068. $node = array();
  1069. $query['columns'] = array('city', 'address');
  1070. $query['tables'] = array('location_markers', 'location');
  1071. $query['where'] = array('location_markers.location = location.id', "location_markers.marker IN ($markerString)", 'entity.active="yes"');
  1072. $query['args'] = array();
  1073. /* Include restrictQueryByDef in case we want to restrict by city/region/tags.. */
  1074. restrictQueryByDef($query, $def, array('tags')); // Removed deprecated &, weird effects may happen.
  1075. $markerDef = $query['def'];
  1076. /* Fetch us a list of locations */
  1077. if ($markerDef['section'] == 'entities')
  1078. {
  1079. $query['columns'] = array_merge($query['columns'], array('entity.location', 'entity.id', 'entity_type.name as ename', 'entity.name', 'etype', 'entity.shortDesc'));
  1080. $query['tables'][] = 'entity';
  1081. $query['tables'][] = 'entity_type';
  1082. $query['where'][] = 'entity_type.id = entity.etype';
  1083. $query['where'][] = "location.id = entity.location";
  1084. if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
  1085. {
  1086. /* Pre-fetch a list of related tags */
  1087. #$relatedTags = getTaglistByTags($markerDef['tags']);
  1088. $query['tables'][] = 'org_tag';
  1089. $query['where'][] = 'org_tag.tag IN (' . $markerDef['tags'] . ')';
  1090. $query['where'][] = 'org_tag.org = entity.id';
  1091. $query['group'][] = 'entity.id';
  1092. }
  1093. }
  1094. else if ($markerDef['section'] == 'events')
  1095. {
  1096. $query['columns'] = array_merge($query['columns'], array(
  1097. 'entity.etype', 'event.entity', 'event.location', 'event.id',
  1098. 'event.name', 'start', 'event.shortDesc', 'entity_type.name as ename'
  1099. ));
  1100. $query['tables'][] = 'event';
  1101. $query['tables'][] = 'entity';
  1102. $query['tables'][] = 'entity_type';
  1103. $query['where'][] = 'event.entity = entity.id';
  1104. $query['where'][] = 'entity.etype = entity_type.id';
  1105. $query['where'][] = "location.id = event.location";
  1106. $query['where'][] = 'start >= DATE(' . getNowValue() . ')';
  1107. $query['order'] = array('start ASC');
  1108. if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
  1109. {
  1110. /* Pre-fetch a list of related tags */
  1111. $relatedTags = getTaglistByTags($markerDef['tags']);
  1112. $query['tables'][] = 'event_tag';
  1113. $query['where'][] = 'event.id = event_tag.event';
  1114. $query['where'][] = 'event_tag.tag IN (' . $relatedTags . ')';
  1115. $query['group'][] = 'id';
  1116. }
  1117. }
  1118. else
  1119. {
  1120. return -3;
  1121. }
  1122. $rs = $dbh->prepare(makeQuerySelect($query));
  1123. $rs->execute($query['args']);
  1124. foreach ($rs->fetchAll() as $row)
  1125. {
  1126. $r_array = array(
  1127. 'name' => $row['name'],
  1128. 'id' => $row['id'],
  1129. 'tags' => array(),
  1130. 'location' => intval($row['location']),
  1131. 'shortDesc' => $row['shortDesc'],
  1132. 'category' => $row['etype']
  1133. );
  1134. $categories[ $row['etype'] ] = $row['ename'];
  1135. if ($markerDef['section'] == 'events')
  1136. {
  1137. $r_array['entity'] = $row['entity'];
  1138. $r_array['type'] = 'event';
  1139. $r_array['shortType'] = 'ev';
  1140. $r_array['start'] = $row['start'];
  1141. }
  1142. else if ($markerDef['section'] == 'entities')
  1143. {
  1144. $r_array['entity'] = $row['id'];
  1145. $r_array['type'] = 'entity';
  1146. $r_array['shortType'] = 'en';
  1147. }
  1148. $node[ $row['id'] ] = $r_array;
  1149. $locationNames[ $row['location'] ] = $row['city'];
  1150. $entityString .= $row['id'] . ',';
  1151. }
  1152. $entityString = chop($entityString, ',');
  1153. /* ..and a list of tags based on the entities */
  1154. if ($markerDef['section'] == 'entities')
  1155. {
  1156. $tag_query = "SELECT org, tag from org_tag where org IN ($entityString)";
  1157. }
  1158. else if ($markerDef['section'] == 'events')
  1159. {
  1160. $tag_query = "SELECT event, tag from event_tag where event IN ($entityString)";
  1161. }
  1162. $rs = $dbh->prepare($tag_query);
  1163. $rs->execute();
  1164. foreach ($rs->fetchAll() as $row)
  1165. {
  1166. /* A hash might be faster. Investigate at some point.. */
  1167. if (! in_array($row['tag'], $tagArray)) { $tagArray[] = $row['tag']; }
  1168. $key = isset($row['org']) ? $row['org'] : $row['event'];
  1169. $node[ $key ]['tags'][] = intval($row['tag']);
  1170. }
  1171. /* Finally, score a tag list for good measure... */
  1172. if (count($tagArray))
  1173. {
  1174. $tag_query = "SELECT id, name from tag WHERE active = 1 AND id IN (" . join(',', $tagArray) . ")";
  1175. $rs = $dbh->prepare($tag_query);
  1176. $rs->execute();
  1177. foreach ($rs->fetchAll() as $row)
  1178. {
  1179. $tagNames[ $row['id'] ] = $row['name'];
  1180. }
  1181. };
  1182. return array($node, $tagNames, $locationNames, $categories);
  1183. }
  1184. function splitMarkerDefs($defs)
  1185. {
  1186. $parts = explode(':', $defs);
  1187. $return = array();
  1188. $return['section'] = array_shift($parts);
  1189. foreach ($parts as $keyvaluestring)
  1190. {
  1191. $keyval = explode('=', $keyvaluestring);
  1192. if (count($keyval) == 1)
  1193. {
  1194. $return[ $keyval[0] ] = true;
  1195. }
  1196. else
  1197. {
  1198. $return[ $keyval[0] ] = $keyval[1];
  1199. }
  1200. }
  1201. return $return;
  1202. }
  1203. function verifyNumberList($list)
  1204. {
  1205. if (preg_match('|[^0-9,]|', $list)) { return false; }
  1206. return true;
  1207. }
  1208. /*
  1209. This function is used by map.js to fetch the markers for a given zoom level, including
  1210. tag references.
  1211. */
  1212. function getMarkers($def, $zoom)
  1213. {
  1214. global $dbh, $settings;
  1215. if ($zoom > $settings['map_max_zoom_aggregation']) { $zoom = $settings['map_max_zoom_aggregation']; }
  1216. if ($zoom < $settings['map_min_zoom_aggregation']) { $zoom = $settings['map_min_zoom_aggregation']; }
  1217. $funnel_lookahead = 10;
  1218. $query = array();
  1219. /* NOTE: We use sum() + bit_xor() rather than crc32() since mysql 5.1 seem to give us incorrect data when using crc2() or md5().. */
  1220. $query['columns'] = array(
  1221. 'location_markers.zoom',
  1222. 'marker',
  1223. 'markers.latitude',
  1224. 'markers.longitude',
  1225. 'count(marker) as weight',
  1226. 'concat(sum(distinct(location.id)), bit_xor(location.id)) as csum',
  1227. 'avg(location.latitude) as avgLatitude',
  1228. 'avg(location.longitude) as avgLongitude' );
  1229. $query['tables'] = array('location_markers', 'location', 'markers');
  1230. $query['where'] = array(
  1231. 'location_markers.marker = markers.id',
  1232. 'location_markers.zoom >= ?',
  1233. 'location_markers.zoom <= (? + ?)');
  1234. $query['group'] = array('marker');
  1235. $query['order'] = array('location_markers.zoom DESC');
  1236. $query['args'] = array();
  1237. $tagq = array();
  1238. $tagq['columns'] = array('marker','tag','count(tag) as weight');
  1239. $tagq['tables'] = array('location_markers');
  1240. $tagq['where'] = array('zoom = ?');
  1241. $tagq['group'] = array('tag','marker');
  1242. /* Include restrictQueryByDef in case we want to restrict by city/region/tags.. */
  1243. restrictQueryByDef($query, $def); // Removed deprecated &, weird effects may happen.
  1244. $markerDef = $query['def'];
  1245. if ($markerDef['section'] == 'entities')
  1246. {
  1247. $query['tables'][] = 'entity';
  1248. $query['where'][] = 'location_markers.location = entity.location';
  1249. $query['where'][] = 'entity.location = location.id';
  1250. $query['where'][] = 'entity.active = "yes"';
  1251. $tagq['tables'][] = 'entity';
  1252. $tagq['tables'][] = 'org_tag';
  1253. $tagq['where'][] = 'entity.location = location_markers.location';
  1254. $tagq['where'][] = 'entity.id = org_tag.org';
  1255. if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
  1256. {
  1257. $tagList = getTaglistByTags($markerDef['tags']);
  1258. //$tagq['tables'][] = 'tag_tag';
  1259. //$tagq['where'][] = 'org_tag.tag = t_related';
  1260. $tagq['where'][] = 'org_tag.tag IN (' . $tagList . ')';
  1261. }
  1262. }
  1263. else if ($markerDef['section'] == 'events')
  1264. {
  1265. $query['tables'][] = 'event';
  1266. $query['where'][] = 'location_markers.location = event.location';
  1267. $query['where'][] = 'event.location = location.id';
  1268. $query['where'][] = 'event.active = "yes"';
  1269. $query['where'][] = 'start >= DATE(' . getNowValue() . ')';
  1270. $tagq['tables'][] = 'event';
  1271. $tagq['tables'][] = 'event_tag';
  1272. $tagq['where'][] = 'event.location = location_markers.location';
  1273. $tagq['where'][] = 'event.id = event_tag.event';
  1274. if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
  1275. {
  1276. /*
  1277. We don't include the tags in the request in this lookup. I.e if you ask for events:tags=19,
  1278. you'll get everything *related* to 19, but not 19 itself.
  1279. */
  1280. //$tagq['tables'][] = 'tag_tag';
  1281. //$tagq['where'][] = 'event_tag.tag = t_related';
  1282. $exclude = explode(',', $markerDef['tags']);
  1283. $tagList = getTaglistByTags($markerDef['tags'], $exclude);
  1284. $tagq['where'][] = 'event_tag.tag IN (' . $tagList . ')';
  1285. }
  1286. }
  1287. else
  1288. {
  1289. return false;
  1290. }
  1291. $qs = makeQuerySelect($query);
  1292. error_log($qs);
  1293. $q = $dbh->prepare( $qs );
  1294. $a = array_merge(array( $zoom, $zoom, $funnel_lookahead ), $query['args']);
  1295. error_log(var_export($zoom, true) . ' ' . var_export($zoom, true) . ' ' . var_export($funnel_lookahead, true) . ' ' . var_export($query['args'], true));
  1296. # error_log(var_export($a, true));
  1297. $q->execute( $a );
  1298. $return = array();
  1299. $funnel = array();
  1300. foreach ($q->fetchAll() as $row)
  1301. {
  1302. $csum = $row['csum'];
  1303. if (! array_key_exists($csum, $funnel)) {
  1304. $funnel[$csum]['zoom'] = $row['zoom'];
  1305. }
  1306. /*
  1307. We'll iterate through several levels worth of zoom in one query (in order to figure
  1308. out whether a marker is a funnel or not) - so for purposes other than figuring out
  1309. the funnel depth, just iterate through the loop if we're looking at a row with a zoom
  1310. level other than the one we're returning to the user.
  1311. */
  1312. if ($row['zoom'] != $zoom) { continue; }
  1313. $entry = array();
  1314. $entry['latitude'] = $row['latitude'];
  1315. $entry['longitude'] = $row['longitude'];
  1316. $entry['zoom'] = $row['zoom'];
  1317. $entry['weight'] = $row['weight'];
  1318. $entry['id'] = $row['marker'];
  1319. $entry['tag'] = array();
  1320. $entry['funnel'] = 0;
  1321. if (array_key_exists($csum, $funnel))
  1322. {
  1323. $funLevel = $funnel[$csum]['zoom'];
  1324. if (! ($funLevel == $zoom + $funnel_lookahead || $funLevel >= $settings['map_max_zoom_aggregation'] ))
  1325. {
  1326. $entry['funnel'] = $funLevel + 1;
  1327. if ($funLevel > $zoom + 1)
  1328. {
  1329. /*
  1330. If we have a long'ish funnel, we want to recenter the map to where the
  1331. actual locations are found, not to where the marker position is at.
  1332. */
  1333. $entry['recenterLatitude'] = $row['avgLatitude'];
  1334. $entry['recenterLongitude'] = $row['avgLongitude'];
  1335. }
  1336. }
  1337. }
  1338. $return[$zoom][ $row['marker'] ] = $entry;
  1339. }
  1340. error_log(var_export($return, true));
  1341. $qs = makeQuerySelect($tagq);
  1342. error_log($qs);
  1343. $q = $dbh->prepare($qs);
  1344. $q->execute(array( $zoom ));
  1345. $tagIndex = array();
  1346. if (array_key_exists($zoom, $return))
  1347. {
  1348. foreach ($q->fetchAll() as $row)
  1349. {
  1350. /*
  1351. We seem to be getting slightly more tag results than we bargain for. Do this check for
  1352. now, investigate properly later. Doesn't seem like a problem.
  1353. */
  1354. if (! array_key_exists($row['marker'], $return[$zoom])) { continue; }
  1355. $return[$zoom][ $row['marker'] ]['tag'][$row['tag']] = intval($row['weight']);
  1356. $tagIndex[ $row['tag'] ] = $row['tag'];
  1357. }
  1358. }
  1359. /* Fetch tag names while we're at it */
  1360. $tagList = join(',', array_keys($tagIndex));
  1361. $q = $dbh->prepare("SELECT id, name, is_category FROM tag WHERE id IN($tagList)");
  1362. $q->execute();
  1363. foreach ($q->fetchAll() as $row)
  1364. {
  1365. /* If we got a tag to match this to in the markerDef, don't list category tags in the output */
  1366. if (array_key_exists('tags', $markerDef)) {
  1367. if ($row['is_category']) { unset($tagIndex[ $row['id'] ]); continue; }
  1368. }
  1369. $tagIndex[ $row['id'] ] = $row['name'];
  1370. }
  1371. # error_log(var_export($return, true));
  1372. # error_log(var_export($tagIndex, true));
  1373. return array($return, $tagIndex);
  1374. }
  1375. /* We use this instead of NOW() in order to get query cache hits. */
  1376. function getNowValue()
  1377. {
  1378. return('"' . date('Y-m-d H:00:01') . '"');
  1379. }
  1380. function getMenuItems($which)
  1381. {
  1382. global $dbh, $site, $settings;
  1383. $q = $dbh->prepare('SELECT menu, align, title, url, visible, combination FROM menu WHERE visible="yes" ORDER BY sortorder,title ASC');
  1384. $q->execute(array( $which ));
  1385. $items = Array();
  1386. $lookupBySub = Array();
  1387. foreach ($q->fetchAll() as $row) {
  1388. $items[] = $row;
  1389. if ($row['menu'] == 'sub') { $lookupBySub[ $row['url'] ] = $row; }
  1390. }
  1391. $return = array();
  1392. foreach ($items as $row) {
  1393. if ($row['menu'] != $which) { continue; }
  1394. $i = array(
  1395. 'title' => $row['title'],
  1396. 'selected' => false
  1397. );
  1398. $i['path'] = $site['base'];
  1399. $p1 = isset($site['requestPath'][1]) ? $site['requestPath'][1] : '';
  1400. if ($row['combination'] == 'yes')
  1401. {
  1402. if ($which == 'main') {
  1403. if (array_key_exists($p1, $lookupBySub)) {
  1404. $i['path'] .= $row['url'] . '/' . $p1;
  1405. }
  1406. else
  1407. {
  1408. $i['path'] .= $row['url'] . '/';
  1409. }
  1410. }
  1411. else { $i['path'] .= $site['selection'] . '/' . $row['url']; }
  1412. }
  1413. else
  1414. {
  1415. $i['path'] .= $row['url'];
  1416. }
  1417. if ($which == 'main')
  1418. {
  1419. if ($site['selection'] == $row['url']) { $i['selected'] = true; }
  1420. }
  1421. else
  1422. {
  1423. if ($p1 == $row['url']) { $i['selected'] = true; }
  1424. }
  1425. $i['align'] = $row['align'];
  1426. $return[] = $i;
  1427. }
  1428. if ($which == 'main' && $site['userid'])
  1429. {
  1430. $return[] = array(
  1431. 'title' => 'Logga ut ' . $site['username'],
  1432. 'selected' => false,
  1433. 'align' => 'right',
  1434. 'path' => 'logout'
  1435. );
  1436. }
  1437. if ($which == 'main' && $site['maintainer'])
  1438. {
  1439. $return[] = array(
  1440. 'title' => 'Underhåll',
  1441. 'selected' => false,
  1442. 'align' => 'right',
  1443. 'path' => 'maintain'
  1444. );
  1445. }
  1446. return $return;
  1447. }
  1448. function getTagsByTags($tagList)
  1449. {
  1450. global $dbh;
  1451. $tagidString = join(',', $tagList);
  1452. $q = $dbh->prepare("SELECT tag.id, tag.name, t_weight as weight FROM tag, tag_tag WHERE t_related = tag.id AND tag.is_category = 0 AND t_tag IN ($tagidString)");
  1453. $q->execute();
  1454. $results = array();
  1455. foreach ($q->fetchAll() as $row) {
  1456. $results[ $row['id'] ] = array(
  1457. 'name' => $row['name'],
  1458. 'weight' => $row['weight'],
  1459. 'id' => $row['id']
  1460. );
  1461. }
  1462. return $results;
  1463. }
  1464. function getTagsByPartial($partial)
  1465. {
  1466. global $dbh;
  1467. $tags = array();
  1468. $q = $dbh->prepare("SELECT name, weight FROM tag WHERE active > 0 AND lcname LIKE ? ORDER BY weight DESC");
  1469. if (! $q->execute(array("$partial%"))) { error_log("Execution failed in getTagsByPartial"); return false; };
  1470. foreach ($q->fetchAll() as $row) { $tags[] = $row['name']; }
  1471. return array($tags);
  1472. }
  1473. function loginSession($args)
  1474. {
  1475. global $site;
  1476. $username = $args[0];
  1477. $password = $args[1];
  1478. $redirect = $args[2];
  1479. if ($username == 'sso' && substr($password, 0, 4) == 'sso-')
  1480. {
  1481. if (loginSSO(substr($password, 4)) == false) { return false; }
  1482. header('Location: ' . $site['baseHref'] . $redirect);
  1483. return true;
  1484. }
  1485. else
  1486. {
  1487. if (! doCommunityLogin($username, $password)) { return false; }
  1488. header('Location: ' . $site['baseHref'] . $redirect);
  1489. return true;
  1490. }
  1491. return false;
  1492. }
  1493. function doCommunityLogin($username, $password)
  1494. {
  1495. global $settings;
  1496. $api_req = $settings['community_api_private_url']. '?cmd=login' .
  1497. "&username=" . urlencode($username) .
  1498. "&password=" . urlencode($password);
  1499. $req = curl_init($api_req);
  1500. curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
  1501. $response = curl_exec($req);
  1502. curl_close($req);
  1503. if ($response == "") { return false; }
  1504. $xml = new SimpleXMLElement($response);
  1505. $usernode = $xml->user[0];
  1506. if (gettype($usernode) != 'object') { return false; }
  1507. if ($usernode->attributes()->type != 'active') { return false; }
  1508. $maintainer = false;
  1509. if (array_key_exists('community_api_maintainer_group', $settings))
  1510. {
  1511. foreach ($usernode->xpath('./group') as $groupElt)
  1512. {
  1513. if ($groupElt->attributes()->name == $settings['community_api_maintainer_group'])
  1514. {
  1515. $maintainer = true;
  1516. }
  1517. }
  1518. }
  1519. $remote_username = $usernode->attributes()->username . '';
  1520. $remote_email = $usernode->attributes()->email . '';
  1521. provisionUser($remote_username, $remote_email, $maintainer);
  1522. return true;
  1523. }
  1524. function getRecentEventsByEntity($entity)
  1525. {
  1526. global $dbh;
  1527. $query = array();
  1528. $query['columns'] = array('banner.approved','event.banner', 'event.id', 'event.name', 'shortDesc', 'weekday(start) as weekday', 'start', 'end', 'event.active', 'source', 'location.city', 'DATE_FORMAT(updated, "%Y-%m-%d %H:%i") as updated', 'username', 'archived');
  1529. $query['tables'] = array('event', 'location', 'user');
  1530. $query['where'] = array('event.location = location.id', 'instanceOf IS NULL', 'event.entity = ?', 'updatedBy = user.id', 'event.banner = banner.id');
  1531. $query['order'] = array('event.updated DESC');
  1532. $query['limit'] = 5;
  1533. $q = makeQuerySelect($query);
  1534. $q = $dbh->prepare( makeQuerySelect($query) );
  1535. $q->execute(array( $entity ));
  1536. $result = fillGrantsForEvents($q->fetchAll());
  1537. return $result;
  1538. }
  1539. function getEventsByEntity($entity)
  1540. {
  1541. global $dbh;
  1542. $query = array();
  1543. $query['columns'] = array('banner.approved','event.banner', 'event.id', 'event.name', 'shortDesc', 'weekday(start) as weekday', 'start', 'end', 'event.active', 'source', 'location.city', 'DATE_FORMAT(updated, "%Y-%m-%d %H:%i") as updated', 'username', 'archived');
  1544. $query['tables'] = array('event', 'location', 'user', 'banner');
  1545. $query['where'] = array('event.location = location.id', 'event.entity = ?', 'updatedBy = user.id', 'event.banner = banner.id');
  1546. $query['order'] = array('event.start DESC');
  1547. $q = $dbh->prepare( makeQuerySelect($query) );
  1548. $q->execute(array( $entity ));
  1549. $result = fillGrantsForEvents($q->fetchAll());
  1550. return $result;
  1551. }
  1552. function fillGrantsForEvents($data)
  1553. {
  1554. global $dbh;
  1555. $return = array();
  1556. $eids = array();
  1557. foreach ($data as $row)
  1558. {
  1559. $eids[] = $row['id'];
  1560. }
  1561. $query['columns'] = array('event','grantid','submitted');
  1562. $query['tables'] = array('event_grant');
  1563. $query['where'] = array('event IN (' . join(",", $eids) . ')');
  1564. $q = $dbh->prepare( makeQuerySelect($query) );
  1565. $q->execute();
  1566. $lut = array();
  1567. foreach ($q->fetchAll() as $row)
  1568. {
  1569. $lut[$row['event']][$row['grantid']]['submitted'] = $row['submitted'];
  1570. }
  1571. foreach ($data as $row)
  1572. {
  1573. if (array_key_exists($row['id'], $lut))
  1574. {
  1575. $row['grant'] = $lut[ $row['id'] ];
  1576. }
  1577. $return[] = $row;
  1578. }
  1579. return $return;
  1580. }
  1581. /* ===== Not yet validated, might not be in use ===== */
  1582. function provisionUser($username, $email, $maintainer_bool)
  1583. {
  1584. global $dbh;
  1585. $maintainer = 'false';
  1586. if ($maintainer_bool) { $maintainer = 'true'; }
  1587. $resultSet = $dbh->prepare('SELECT id FROM user WHERE lcusername = ?');
  1588. $resultSet->execute(array(strtolower($username)));
  1589. $row = $resultSet->fetch();
  1590. if (! $row['id'])
  1591. {
  1592. $newUser = $dbh->prepare('INSERT INTO user (username, lcusername, maintainer) VALUES (?, ?, ?)');
  1593. if (! $newUser->execute( array($username, strtolower($username), $maintainer) )) { return false; }
  1594. $userid = $dbh->lastInsertId();
  1595. }
  1596. else
  1597. {
  1598. $userid = $row['id'];
  1599. }
  1600. $q = $dbh->prepare("UPDATE user SET email=?, maintainer=? WHERE id = ?");
  1601. $q->execute(array($email, $maintainer, $userid));
  1602. $_SESSION['userid'] = $userid;
  1603. $_SESSION['username'] = $username;
  1604. $_SESSION['maintainer'] = $maintainer_bool;
  1605. }
  1606. function loginSSO($hash)
  1607. {
  1608. global $settings, $site;
  1609. $sitecookie = hash('sha256', $settings['community_api_secret'] . $settings['community_api_sitename'] . $hash);
  1610. $api_req = $settings['community_api_private_url']. '?cmd=verify_cookie&authcookie=' . urlencode($hash) .
  1611. "&site=" . urlencode($settings['community_api_sitename']) .
  1612. "&sitecookie=" . urlencode($sitecookie);
  1613. $req = curl_init($api_req);
  1614. curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
  1615. curl_setopt($req, CURLOPT_HTTPHEADER, array("Content-type: text/xml;charset=UTF-8"));
  1616. $response = curl_exec($req);
  1617. curl_close($req);
  1618. if ($response == '') { $site['attempt_sso'] = false; return false; }
  1619. $xml = simplexml_load_string($response);
  1620. $usernode = $xml->user[0];
  1621. if (gettype($usernode) != 'object') { $site['attempt_sso'] = false; return false; }
  1622. $username = $usernode->attributes()->username;
  1623. $username = (string)$username;
  1624. $email = $usernode->attributes()->email;
  1625. $email = (string)$email;
  1626. $maintainer = false;
  1627. if (array_key_exists('community_api_maintainer_group', $settings))
  1628. {
  1629. foreach ($usernode->xpath('./group') as $groupElt)
  1630. {
  1631. if ($groupElt->attributes()->name == $settings['community_api_maintainer_group'])
  1632. {
  1633. $maintainer = true;
  1634. }
  1635. }
  1636. }
  1637. if ($username != '' && $usernode->attributes()->type == 'active') {
  1638. provisionUser($username, $email, $maintainer);
  1639. return $username;
  1640. };
  1641. $site['attempt_sso'] = false;
  1642. return false;
  1643. }
  1644. function userInEntity($user, $entity)
  1645. {
  1646. global $dbh;
  1647. $q = $dbh->prepare("SELECT id, permission FROM user_entity WHERE user = ?");
  1648. $q->execute(array($user));
  1649. $row = $q->fetch();
  1650. if (! $row['id']) { return false; }
  1651. return $row['permission'];
  1652. }
  1653. function userEntities($userid)
  1654. {
  1655. global $dbh;
  1656. $userid = intval($userid);
  1657. $q = $dbh->prepare("
  1658. SELECT city, entity_type.name as etype, user_entity.entity, user_entity.permission, entity.name
  1659. FROM entity,user_entity,entity_type,location
  1660. WHERE entity.etype = entity_type.id
  1661. AND location.id = entity.location
  1662. AND user_entity.entity = entity.id
  1663. AND entity.active = 'yes'
  1664. AND user = ?
  1665. GROUP BY user_entity.entity");
  1666. $q->execute(array($userid));
  1667. $return = array();
  1668. foreach ($q->fetchAll() as $row)
  1669. {
  1670. $r = array();
  1671. $r['name'] = $row['name'];
  1672. $r['city'] = $row['city'];
  1673. $r['id'] = $row['entity'];
  1674. $r['type'] = $row['etype'];
  1675. $r['permission'] = $row['permission'];
  1676. $r['weight'] = 10;
  1677. $return[] = $r;
  1678. }
  1679. return $return;
  1680. }
  1681. function getCategoryTags()
  1682. {
  1683. global $dbh;
  1684. $tagids = array();
  1685. $results = array();
  1686. $masterResultSet = $dbh->prepare("SELECT id, name, weight from tag WHERE is_category = 1");
  1687. $masterResultSet->execute();
  1688. foreach ($masterResultSet as $row) {
  1689. $results[ $row['id'] ] = array(
  1690. 'name' => $row['name'],
  1691. 'weight' => $row['weight'],
  1692. 'id' => $row['id']
  1693. );
  1694. }
  1695. return array($results);
  1696. }
  1697. function getTagIDsByTags($tagList)
  1698. {
  1699. global $dbh;
  1700. $cleanTags = array();
  1701. foreach ($tagList as $tag) { $cleanTags[$tag] = $dbh->quote(strtolower($tag)); }
  1702. $tagNameString = join(',', $cleanTags);
  1703. $tagids = array();
  1704. $masterResultSet = $dbh->prepare("SELECT id, name from tag WHERE lcname IN ($tagNameString)");
  1705. $masterResultSet->execute();
  1706. foreach ($masterResultSet as $row) { $tagids[] = $row[0]; }
  1707. return $tagids;
  1708. }
  1709. function usernameExists($username)
  1710. {
  1711. global $settings;
  1712. $api_req = $settings['community_api_private_url']. '?cmd=exists&username=' . urlencode($username);
  1713. $req = curl_init($api_req);
  1714. curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
  1715. curl_setopt($req, CURLOPT_HTTPHEADER, array("Content-type: text/xml;charset=UTF-8"));
  1716. $response = curl_exec($req);
  1717. curl_close($req);
  1718. $xml = simplexml_load_string($response);
  1719. $usernode = $xml->user[0];
  1720. if (gettype($usernode) != 'object') { return false; }
  1721. $username = $usernode->attributes()->username;
  1722. if ($username != '' && $usernode->attributes()->type == 'active') { return intval($usernode->attributes()->id); };
  1723. return false;
  1724. }
  1725. function userlistByEntity($entity)
  1726. {
  1727. global $dbh;
  1728. if (! userInEntity($_SESSION['userid'], $entity))
  1729. {
  1730. return false;
  1731. }
  1732. $results = array();
  1733. $resultSet = $dbh->prepare("SELECT user.id as userid, user_entity.id, username, callsign, permission FROM user, user_entity WHERE user_entity.user = user.id AND entity = ?");
  1734. $resultSet->execute($entity);
  1735. foreach ($resultSet->fetchAll() as $row) {
  1736. $results[] = array(
  1737. 'callsign' => $row['callsign'],
  1738. 'id' => $row['userid'],
  1739. 'username' => $row['username'],
  1740. 'permission' => $row['permission']
  1741. );
  1742. }
  1743. return array($results);
  1744. }
  1745. function makeQueryUpdate($query)
  1746. {
  1747. $string = 'UPDATE ' . $query['table'] . ' SET ';
  1748. $string .= join(', ', $query['update']);
  1749. $string .= ' WHERE ' . join(' AND ', $query['where']);
  1750. return $string;
  1751. }
  1752. function makeQuerySelect($query)
  1753. {
  1754. $string = 'SELECT ' . join(', ', $query['columns']);
  1755. $string .= ' FROM ' . join(', ', $query['tables']);
  1756. if (array_key_exists('left-join', $query)) { $string .= ' LEFT JOIN ' . join(', ', $query['left-join']); };
  1757. if (array_key_exists('on', $query)) { $string .= ' ON ' . join(' AND ', $query['on']); };
  1758. $string .= ' WHERE ' . join(' AND ', $query['where']);
  1759. if (array_key_exists('group', $query)) { $string .= ' GROUP BY ' . join(', ', $query['group']); };
  1760. if (array_key_exists('order', $query)) { $string .= ' ORDER BY ' . join(', ', $query['order']); };
  1761. if (array_key_exists('limit', $query)) { $string .= ' LIMIT ' . $query['limit']; };
  1762. return $string;
  1763. }
  1764. function archiveEvents()
  1765. {
  1766. global $dbh;
  1767. $query['update'] = array('archived = "true"');
  1768. $query['where'] = array("DATE(start) < NOW()", "archived = 'false'");
  1769. $query['table'] = 'event';
  1770. $q = $dbh->prepare( makeQueryUpdate($query) );
  1771. $q->execute();
  1772. return true;
  1773. }
  1774. function exportGrant($args)
  1775. {
  1776. global $dbh;
  1777. archiveEvents();
  1778. $grantId = $args[0];
  1779. $startdate = $args[1];
  1780. $enddate = $args[2];
  1781. $query = array();
  1782. $query['columns'] =
  1783. array('event.id', 'event.name', 'event.shortDesc',
  1784. 'event.fullDesc', 'startTime', 'event.attendance', 'event.targetAge',
  1785. 'weekday(start) as weekday', 'start', 'end', 'event.email', 'event.phone',
  1786. 'event.active', 'source', 'location.city',
  1787. 'DATE_FORMAT(updated, "%Y-%m-%d %H:%i") as updated',
  1788. 'latitude', 'longitude', 'municipality.name AS muni',
  1789. 'region', 'country', 'groupKey', 'entity.name AS "entity.name"', 'event.created', 'event.attended', 'event_grant.approved', 'event.paid');
  1790. $query['tables'] =
  1791. array('event', 'location', 'event_grant', 'municipality', 'entity');
  1792. $query['where'] =
  1793. array('event.id = event_grant.event', 'event_grant.grantid = ?',
  1794. 'event.location = location.id', "event.archived = 'true'",
  1795. 'municipality.id = location.municipality', 'event.entity = entity.id');
  1796. $query['order'] = array('event.start DESC');
  1797. $query_args = array($grantId);
  1798. // Both start date and end date should be set.
  1799. if($startdate !== '' && $enddate !== '') {
  1800. $query['where'][] = 'event.start BETWEEN ? AND ?';
  1801. $query_args[] = $startdate;
  1802. $query_args[] = $enddate;
  1803. } else {
  1804. return array(array());
  1805. }
  1806. $q = $dbh->prepare( makeQuerySelect($query) );
  1807. $q->execute($query_args);
  1808. $set = $q->fetchAll();
  1809. /* Use the same data to make an update query to signify that the data has been submitted.. */
  1810. $ids = array();
  1811. foreach ($set as $row) { $ids[] = $row['id']; }
  1812. $qs2 = "UPDATE event_grant SET submitted = 'true' where event IN (" . join(',', $ids) . ') AND grantId = ?';
  1813. if($startdate !== '' && $enddate !== '') {
  1814. $qs2 .= 'AND event.start BETWEEN ? AND ?';
  1815. }
  1816. $g = $dbh->prepare($qs2);
  1817. $g->execute($query_args);
  1818. return array($set);
  1819. }
  1820. function restrictQueryByDef(&$query, $def, $ignore = array() )
  1821. {
  1822. $markerDef = splitMarkerDefs($def);
  1823. $query['def'] = $markerDef;
  1824. if (! array_key_exists('args', $query)) { $query['args'] = array(); }
  1825. if (! array_key_exists('tables', $query)) { $query['tables'] = array(); }
  1826. #if (! array_key_exists('group', $query)) { $query['group'] = array(); }
  1827. $what = $markerDef['section'];
  1828. if ($what != 'events' && $what != 'entities') { return false; }
  1829. // Removed deprecated &s in the restrict calls, weird effects may happen.
  1830. if (array_key_exists('entity', $markerDef) && ! in_array('entity', $ignore)) { restrict('entity.id', $markerDef['entity'], $query); }
  1831. if (array_key_exists('groupKey', $markerDef) && ! in_array('groupKey', $ignore)) { restrict('entity.groupKey', $markerDef['groupKey'], $query); }
  1832. if (array_key_exists('city', $markerDef) && ! in_array('city', $ignore)) { restrict('location.city', $markerDef['city'], $query); }
  1833. if (array_key_exists('municipality', $markerDef) && ! in_array('municipality', $ignore)) { restrict('location.municipality', $markerDef['municipality'], $query); }
  1834. if (array_key_exists('region', $markerDef) && ! in_array('region', $ignore)) { restrict('location.region', $markerDef['region'], $query); }
  1835. if (array_key_exists('type', $markerDef) && ! in_array('type', $ignore)) { restrict('entity.etype', $markerDef['type'], $query); }
  1836. if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']) && ! in_array('tags', $ignore))
  1837. {
  1838. if ($what == 'events')
  1839. {
  1840. $query['tables'][] = 'event_tag';
  1841. $query['where'][] = 'event_tag.event = event.id';
  1842. restrict('event_tag.tag', $markerDef['tags'], $query); // Removed deprecated &, weird effects may happen.
  1843. }
  1844. else if ($what == 'entities')
  1845. {
  1846. $query['tables'][] = 'org_tag';
  1847. $query['where'][] = 'org_tag.org = entity.id';
  1848. restrict('org_tag.tag', $markerDef['tags'], $query); // Removed deprecated &, weird effects may happen.
  1849. }
  1850. }
  1851. if (array_key_exists('withBanners', $markerDef) && ! in_array('type', $ignore)) {
  1852. restrict('banner.approved', 'true', $query); // Removed deprecated &, weird effects may happen.
  1853. if ($markerDef['withBanners'] === 'unique') { $query['group'][] = 'banner.hash'; }
  1854. }
  1855. return true;
  1856. }
  1857. function getMunicipality($latitude, $longitude, $city = null)
  1858. {
  1859. global $dbh;
  1860. $q = $dbh->prepare("SELECT id, name from municipality where north > ? and south < ? and west < ? and east > ?");
  1861. $q->execute(array($latitude, $latitude, $longitude, $longitude));
  1862. $muniHits = $q->fetchAll();
  1863. /* One hit? Return that one (no need to check the bbox) */
  1864. if (count($muniHits) == 1)
  1865. {
  1866. return $muniHits[0]['id'];
  1867. }
  1868. /* No hits whatsoever? */
  1869. if (count($muniHits) == 0)
  1870. {
  1871. $muniHit = null;
  1872. if ($city != null)
  1873. {
  1874. /* Do we have a muni with the same name as this city? Use that */
  1875. $qc = $dbh->prepare("SELECT id, name FROM municipality WHERE name = ?");
  1876. $qc->execute(array($city));
  1877. $muniRow = $qc->fetch();
  1878. if ($muniRow['id'])
  1879. {
  1880. $muniHit = $muniRow['id'];
  1881. }
  1882. else
  1883. {
  1884. error_log("Fail ($city -- " . $muniRow['name'] . ")");
  1885. exit;
  1886. }
  1887. }
  1888. return $muniHit;
  1889. }
  1890. /* More than one hit? Check for name matching */
  1891. foreach ($muniHits as $row)
  1892. {
  1893. if ($row['name'] == $city) { return $row['id']; }
  1894. }
  1895. /* More than one hit and no name match? Check bboxes */
  1896. foreach ($muniHits as $row)
  1897. {
  1898. $isInPoly = inMunicipalityPolygon($row['id'], $latitude, $longitude);
  1899. if ($isInPoly) { return $row['id']; }
  1900. }
  1901. return false;
  1902. }
  1903. function inMunicipalityPolygon($polyId, $latitude, $longitude)
  1904. {
  1905. global $dbh;
  1906. $q = $dbh->prepare("SELECT lat, lng from municipality_borders WHERE id = ? ORDER BY point");
  1907. $q->execute(array($polyId));
  1908. $points = array();
  1909. foreach ($q->fetchAll() as $row)
  1910. {
  1911. $points[] = $row['lat'] . " " . $row['lng'];
  1912. }
  1913. $location = new pointLocation();
  1914. $isIn = $location->pointInPolygon($latitude . " " . $longitude, $points);
  1915. if ($isIn == 'outside') { return false; }
  1916. return true;
  1917. }
  1918. function resizeBanner($id, $width, $outputExtn, $directOutput = false)
  1919. {
  1920. global $site, $dbh;
  1921. $outputExtn = strtolower($outputExtn);
  1922. $q = $dbh->prepare("SELECT id, path FROM banner WHERE id = ?");
  1923. $q->execute(array( $id ));
  1924. $row = $q->fetch();
  1925. $bannerPath = $row['path'];
  1926. $id = $row['id']; /* Important - otherwise we're open to all sorts of SQL crapiness */
  1927. if (! $id) { return false; }
  1928. $eman = strrev($bannerPath);
  1929. $lastpos = strlen($eman) - strpos($eman, '.');
  1930. if ($lastpos == 0) { break; }
  1931. $bannerExt = strtolower(substr($bannerPath, $lastpos ));
  1932. $originalPath = $site['basePath'] . $bannerPath;
  1933. list($origWidth, $origHeight) = getimagesize($originalPath);
  1934. if ($width > $origWidth) { return false; }
  1935. $ratio = $width / $origWidth;
  1936. $height = round($origHeight * $ratio);
  1937. switch ($bannerExt)
  1938. {
  1939. case 'png':
  1940. $source = imagecreatefrompng($originalPath);
  1941. break;
  1942. case 'jpg':
  1943. $source = imagecreatefromjpeg($originalPath);
  1944. break;
  1945. case 'gif':
  1946. $source = imagecreatefromgif($originalPath);
  1947. break;
  1948. default:
  1949. return false;
  1950. }
  1951. $targetPath = $site['basePath'] . 'banners/' . $id . '_' . $width . '.' . $outputExtn;
  1952. $targetImage = imagecreatetruecolor($width, $height);
  1953. imagecopyresampled($targetImage, $source, 0, 0, 0, 0, $width, $height, $origWidth, $origHeight);
  1954. switch ($outputExtn)
  1955. {
  1956. case 'jpg':
  1957. imagejpeg($targetImage, $targetPath);
  1958. if ($directOutput) {
  1959. header('Content-Type: image/jpeg');
  1960. imagejpeg($targetImage, null);
  1961. }
  1962. break;
  1963. case 'png':
  1964. imagepng($targetImage, $targetPath);
  1965. if ($directOutput) {
  1966. header('Content-Type: image/png');
  1967. imagepng($targetImage, null);
  1968. }
  1969. break;
  1970. case 'gif':
  1971. imagegif($targetImage, $targetPath);
  1972. if ($directOutput) {
  1973. header('Content-Type: image/gif');
  1974. imagegif($targetImage, null);
  1975. }
  1976. break;
  1977. default:
  1978. return false;
  1979. }
  1980. chmod($targetPath, 0755);
  1981. return true;
  1982. }
  1983. function restrict($column, $value, &$query)
  1984. {
  1985. global $dbh;
  1986. if ($value != '')
  1987. {
  1988. if (strpos($value, ','))
  1989. {
  1990. $items = explode(',', $value);
  1991. $safe_items = array();
  1992. foreach ($items as $item) { $safe_items[] = $dbh->quote(urldecode($item)); }
  1993. $query['where'][] = "$column IN (" . implode(',', $safe_items) . ')';
  1994. }
  1995. else
  1996. {
  1997. $query['where'][] = "$column = ?";
  1998. $query['args'][] = urldecode($value);
  1999. }
  2000. }
  2001. }
  2002. function authenticateApiUser($user, $secret)
  2003. {
  2004. global $dbh;
  2005. $query = array();
  2006. $query['columns'] = array('id');
  2007. $query['tables'] = array('api_user');
  2008. $query['where'] = array('secret = sha1(concat(salt, ' . $dbh->quote($secret) . '))');
  2009. $q = $dbh->prepare( makeQuerySelect($query) );
  2010. $q->execute();
  2011. $row = $q->fetch();
  2012. if ($row['id'] == '') { return false; }
  2013. $userid = $row['id'];
  2014. $q = $dbh->prepare("UPDATE api_user set accessed = NOW() where id = ?");
  2015. $q->execute( array($userid) );
  2016. $query = array();
  2017. $query['columns'] = array('api_permission');
  2018. $query['tables'] = array('api_permission');
  2019. $query['where'] = array('api_user_id = ?');
  2020. $q = $dbh->prepare( makeQuerySelect($query) );
  2021. $q->execute( array($userid ));
  2022. $permlist = array();
  2023. foreach ($q->fetchAll() as $perm)
  2024. {
  2025. $permlist[] = $perm['api_permission'];
  2026. }
  2027. return $permlist;
  2028. }
  2029. function getStatTest()
  2030. {
  2031. global $dbh;
  2032. $q = $dbh->prepare("select count(event.id) AS hits,municipality from event,location where event.location = location.id and location.municipality != 0 group by municipality");
  2033. $q->execute();
  2034. $events = array();
  2035. foreach ($q->fetchAll() as $row)
  2036. {
  2037. $events[ $row['municipality'] ] = $row['hits'];
  2038. }
  2039. return array($events);
  2040. }
  2041. function pathResizeBanner(&$node, $width) {
  2042. /* Sätt bredden på bilden */
  2043. if(array_key_exists('path', $node) && $width !== '') { /* Le hack. */
  2044. $path = array_pop(explode('/', $node['path'])); // Plocka bort allt utom filnamnet.
  2045. $parts = explode('.', $path);
  2046. $parts[count($parts)-2] .= "_$width"; /* Vi vill lägga till bredden på näst sista elementet, för det sista är filsuffixet. */
  2047. $node['path'] = 'banners/' . implode('.', $parts);
  2048. }
  2049. }
  2050. ?>