/includes/query.php
PHP | 2573 lines | 1924 code | 495 blank | 154 comment | 317 complexity | 2cd567f03bb9b98a6a909f898dff6d25 MD5 | raw file
Possible License(s): GPL-3.0, MIT
- <?php if (! constant("INSYSTEM")) { echo "Permission denied"; exit; } ?>
- <?php
- /*
- hitta.sverok.se site code
- http://hitta.sverok.se
-
- Copyright (c) 2010 Kriss Andsten
- Dual licensed under the MIT and GPL licenses.
- http://hitta.sverok.se/License
- */
- /* ===== Validated and in use ===== */
- include('polygon.php');
- function updateEntity($userid, $data)
- {
- global $dbh;
-
- //if (!$userid) { return; }
-
- /*
- Build us a query to insert/update the entry table
- */
- $sql = '';
- $id = false;
- $cols = array();
- $vals = array();
- $ph = array();
- $got_pk = false;
- if (array_key_exists('id', $data) && $data['id'] != '') { $got_pk = true; }
-
- /*
- Prepare the data that goes into the database;
- */
- $columnData = array();
- $entryMapper = entityFieldMap();
- foreach ($entryMapper as $key => $column) { if (array_key_exists($key, $data)) { $columnData[$column] = $data[$key]; } }
-
- $etype = entityShortTypeToType( $data['type'] );
- if ($etype == false) { return false; }
- $columnData['etype'] = $etype;
- $columnData['updatedBy'] = $userid;
-
- /* Fix the website address - if it doesn't contain the protocol, it should.. */
- $columnData['website'] = qualifyURL($columnData['website']);
-
- foreach ($columnData as $column => $value)
- {
- if ($column == 'active') { continue; }
- $cols[] = $column;
- $vals[] = $value;
- $ph[] = '?';
- }
-
-
- if (! $got_pk)
- {
- $cols[] = 'active';
- $ph[] = '?';
- $vals[] = 'yes';
-
- $sql = 'INSERT INTO entity (' . join(',', $cols) . ') VALUES (' . join(',', $ph) . ')';
- }
- else
- {
- $hasPermission = verifyEntityUserPermission($userid, $data['id']);
- $anyPermission = verifyEntityHasPermissions($data['id']);
-
- if (! $hasPermission && $anyPermission > 0) { return false; }
-
- $defs = array();
- $vals[] = $data['id'];
- foreach ($cols as $colname) { $defs[] = "$colname = ?"; };
-
- $sql = 'UPDATE entity SET ' . join(', ', $defs) . ' WHERE ID = ?';
- }
-
- /* Execute the query, figure out the ID if we don't already have it */
- $q = $dbh->prepare($sql);
- $q->execute( $vals );
-
- if (! $got_pk) { $id = $dbh->lastInsertId(); }
- else { $id = $data['id']; }
- if (! $id) { return false; }
-
- /*
- Figure out the location
- */
- $locationData['latitude'] = $data['latitude'];
- $locationData['longitude'] = $data['longitude'];
- $locationData['region'] = $data['region'];
- $locationData['city'] = $data['city'];
- $locationData['address'] = $data['address'];
- $location = getLocationFor($locationData);
-
- updateEntityLocation($id, $location);
- updateEntityTags($id, $data['tags']);
- if ($userid != 0) { updateEntityPermissions( $userid, $id ); }
-
- return $id;
- }
- function updateEntityPermissions( $userid, $entity )
- {
- global $dbh;
-
- $query = $dbh->prepare("SELECT id, permission FROM user_entity WHERE entity = ?");
- $query->execute(array($entity));
- $row = $query->fetch();
-
- if (! $row['id'])
- {
- $insert = $dbh->prepare("INSERT INTO user_entity (user, entity, permission) VALUES (?, ?, 'bureaucrat')");
- $insert->execute(array($userid, $entity));
- }
- }
- /*
- START functions related to event data input/output
- */
- /*
- This is the main event update routine. It contains some checks and balances
- that need to be kept in place - as such, do not update events by other means
- than calling this routine unless you're really comfortable with what you're
- doing.
-
- Mainly we don't want archived events to change.
- */
- function updateEvent($userid, $data)
- {
- global $dbh;
-
- if (!$userid) { return false; }
- $hasPermission = verifyEntityUserPermission($userid, $data['entity']);
- $got_pk = false;
- if (array_key_exists('id', $data) && $data['id']) { $got_pk = true; }
-
- /* Is this event archived or otherwise impossible to update? */
- if ($got_pk && ! canUpdateEvent($data['id'])) { return false; }
-
- /*
- Build us a query to insert/update the entry table
- */
- $sql = '';
- $cols = array();
- $vals = array();
- $ph = array();
-
-
- /*
- Prepare the data that goes into the database;
- */
- $columnData = array();
- $entryMapper = eventFieldMap();
- foreach ($entryMapper as $key => $column) { if (array_key_exists($key, $data)) { $columnData[$column] = $data[$key]; } }
-
- $columnData['updatedBy'] = $userid;
-
- /* Fix the website address - if it doesn't contain the protocol, it should.. */
- $columnData['website'] = qualifyURL($columnData['website']);
-
- foreach ($columnData as $column => $value)
- {
- if ($column == 'active') { continue; }
-
- # Fulhack, should be rewritten at some point (along with this routine + editor.php)
- if ($column == 'path') { continue; }
- if ($column == 'filename') { continue; }
-
- $cols[] = $column;
- $vals[] = $value;
- $ph[] = '?';
- }
-
-
- if (! $got_pk) {
- /* Is this added by a user without permissions? If so, make it an inactive event... */
- $cols[] = 'active';
- $ph[] = '?';
- $vals[] = ($hasPermission) ? 'yes' : 'no';
-
- $cols[] = 'created';
- $ph[] = 'now()';
-
- $sql = 'INSERT INTO event (' . join(',', $cols) . ') VALUES (' . join(',', $ph) . ')';
- }
- else
- {
- /* No updates for users lacking permissions */
- if (! $hasPermission) { return false; }
- $defs = array();
- $vals[] = $data['id'];
- foreach ($cols as $colname) { $defs[] = "$colname = ?"; };
-
- $sql = 'UPDATE event SET ' . join(', ', $defs) . ' WHERE ID = ?';
- }
-
- /* Execute the query, figure out the ID if we don't already have it */
- $q = $dbh->prepare($sql);
- $q->execute( $vals );
-
- if (! $got_pk) { $id = $dbh->lastInsertId(); }
- else { $id = $data['id']; }
- if (! $id) { return false; }
-
- /*
- Figure out the location
- */
- $locationData['latitude'] = $data['latitude'];
- $locationData['longitude'] = $data['longitude'];
- $locationData['region'] = $data['region'];
- $locationData['city'] = $data['city'];
- $locationData['address'] = $data['address'];
- $location = getLocationFor($locationData);
-
- /*
- If we didn't get an ID for this event, it's either new or cloned. Either how, we
- want updateEventLocation() to register this as a new entry for the given location.
- */
- if (! $got_pk)
- { updateEventLocation($id, $location, true); }
- else
- { updateEventLocation($id, $location); }
-
- updateEventTags($id, $data['tags']);
- updateEventGrants($id, $data['grants']);
- updateBanner('event', $id, $data['filename'], $data['path']);
-
- return $id;
- }
- function canUpdateEvent($eventId)
- {
- global $dbh;
-
- $query = array();
- $query['columns'] = array('id', 'archived');
- $query['tables'] = array('event');
- $query['where'] = array('id = ?');
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute(array( $eventId ));
- $row = $q->fetch();
-
- if (isset($row['archived']) && $row['archived'] == 'false') { return true; }
- return false;
- }
- function updateBanner($type, $id, $filename, $path)
- {
- global $dbh, $site;
-
- if ($type != 'event' && $type != 'entity' && $type != 'user') { return false; }
-
- if (! file_exists($site['basePath'] . $path)) { return false; }
- $fileHash = md5_file($site['basePath'] . $path);
- $extension = getExtensionByFilename($filename);
- if ($extension == '') { return false; }
- if ($fileHash == '') { return false; }
- $q = $dbh->prepare("SELECT id FROM banner WHERE hash = ?");
- $q->execute(array( $fileHash ));
- $row = $q->fetch();
-
- $bannerId = $row['id'];
-
- /* Store new banner? */
- if (! $row['id'])
- {
- $q = $dbh->prepare("INSERT INTO banner (path, hash, filename) VALUES ('temp', ?, ?)");
- $q->execute(array( $fileHash, $filename ));
- $bannerId = $dbh->lastInsertId();
-
- $newPath = 'banners/raw/' . $bannerId . '.' . $extension;
- rename($path, $site['basePath'] . $newPath);
- chmod($newPath, 0755);
-
- $dbh->query("UPDATE banner SET path = '$newPath' WHERE id = $bannerId");
- sendBannerUpdateNotification($bannerId, $newPath);
- }
-
- $q = $dbh->prepare("UPDATE $type SET banner = ? WHERE id = ?");
- $q->execute(array( $bannerId, $id ));
-
- }
- function sendBannerUpdateNotification($id, $path)
- {
- global $dbh, $site;
-
- $q = $dbh->prepare("SELECT email FROM user WHERE maintainer = 'true'");
- $q->execute();
-
- $maint = array();
- foreach ($q->fetchAll() as $row)
- {
- $maint[] = $row['email'];
- }
-
- $header = "MIME-Version: 1.0\n";
- $header .= "Content-Type: multipart/alternative; boundary=\"==Hitta-robot\"\n";
- #$header .= "To: \"Inget svar\" <noreply@shortpacket.org>\n";
- $header .= "From: \"Hitta Robotpost\" <hitta@sverok.se>\n";
- $to = join(', ', $maint);
-
- $body = "
- This is a multi-part message in MIME format.
-
- --==Hitta-robot
- Content-Type: text/plain; charset=UTF-8
- Content-Transfer-Encoding: 8bit
- Besök " . $site['baseHref'] . "maintain/ för att godkänna eller avslå bannern.
- --==Hitta-robot
- Content-Type: text/html; charset=UTF-8
- Content-Transfer-Encoding: 8bit
- <html>
- <head>
- <title>Ny banner på hitta ($id)</title>
- </head>
- <body>
- <p>Besök <a href=\"" . $site['baseHref'] . "maintain/\">Hitta Underhåll</a> för att godkänna eller avslå bannern.</p>
- <img src=\"" . $site['baseHref'] . $path . "\" />
- </body>
- </html>
- --==Hitta-robot--
- ";
-
- $rv = mail(
- $to,
- "Ny banner på hitta ($id)",
- $body,
- $header
- );
- };
- function approveBanner($params)
- {
- global $dbh;
- $validGrades = array('excellent','good','poor','bad');
- $id = $params[0];
- $grade = $params[1];
- // We got sane input and the user is logged in and a maintainer, lets go!
- if ($id !== '' && $id > 0 && \ /* Banner ID 0 is magical, leave it be. */
- in_array($grade, $validGrades) && \
- array_key_exists('maintainer', $_SESSION) && $_SESSION['maintainer'] == true
- ) {
- $q = $dbh->prepare("UPDATE banner SET approved = 'true', quality = ? where id = ?");
- $q->execute(array($grade, $id));
-
- return array(true);
- } else {
- return array(false);
- }
- }
- function deleteBanner($id)
- {
- global $dbh;
-
- if ($id == 0 || $id == '') { return array(false); } /* Banner ID 0 is magical, we should leave it be */
- if (! array_key_exists('maintainer', $_SESSION) || $_SESSION['maintainer'] != true) { return array(false); };
-
- /*
- If we don't reset the banners in the event table before removing
- the banner from the banners table, some event queries won't return any
- results.
- */
- $q = $dbh->prepare("UPDATE event SET banner = 0 WHERE banner = ?");
- $q->execute($id);
-
- $q = $dbh->prepare("DELETE FROM banner where id = ?");
- $q->execute($id);
-
- return array(true);
- }
- /* Get some xml, turn into banner, put in right places, ..., profit. */
- function importBanner($args) {
- global $dbh, $site;
- $xml = new SimpleXMLElement($args[0]);
- $fn = basename($xml['filename']); //Somekind of security. Avoids ../-attacks.
- if(!($bin = base64_decode($xml[0], true))) {
- return array('FAIL - encoding error - image data contains letters outside the base64 alphabet.');
- }
- if(!($img = imagecreatefromstring($bin))) {
- return array("FAIL - couldn't create image from input data.");
- }
- if(imagesx($img) != 1200 && imagesy($img) != 1000) {
- return array('FAIL - weird size. Allowed size is 1200x1000. Your size is '.imagesx($img).'x'.imagesy($img).'.');
- }
- $hash = md5($bin);
- $q = $dbh->prepare("SELECT count(id) as count FROM banner WHERE hash = ? OR filename = ?");
- $q->execute(array( $hash, $fn ));
- $row = $q->fetch();
- if($row['count'] > 0) {
- return array('FAIL - file already uploaded.');
- }
- // Insert to get an id to use as filename.
- $q = $dbh->prepare("INSERT INTO banner (path, hash, filename, website, created) VALUES ('temp', ?, ?, ?, NOW())");
- $q->execute(array( $hash, $fn, $xml['website'] ));
- $bannerId = $dbh->lastInsertId();
- // Write file to disk.
- $newPath = "banners/raw/$bannerId.".getExtensionByFilename($fn);
- $fh = fopen($site['basePath'].$newPath, 'wb');
- fwrite($fh, $bin);
- fclose($fh);
- //Update with new path
- $dbh->query("UPDATE banner SET path = '$newPath' WHERE id = $bannerId");
- // Notify maintainers
- sendBannerUpdateNotification($bannerId, $newPath);
- return array('OK');
- }
- function getUnapprovedBanners()
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT id, filename, path FROM banner WHERE approved = 'false' AND hash IS NOT NULL AND id > 0");
- $q->execute();
-
- return $q->fetchAll();
- }
- function getExtensionByFilename($filename)
- {
- $eman = strrev($filename);
- $lastpos = strlen($eman) - strpos($eman, '.');
- if ($lastpos == 0) { return false; }
- $extn = substr($filename, $lastpos );
- $extn = strtolower($extn);
-
- return $extn;
- }
- function qualifyURL( $website )
- {
- if ($website == '') { return ''; }
-
- if (preg_match('|^https:|i', $website) || preg_match('|http:|i', $website)) { return $website; }
-
- return "http://" . $website;
- }
- /*
- See if we already have a location on file.
- Primarily, see if the address/city/region matches - if so, use that.
- As a fallback, check the coordinates (and curse any rounding errors) - if found, use that.
-
- Finally, create a new location.
- */
- function getLocationFor($location)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT id FROM location WHERE address = ? AND city = ? AND region = ?");
- $q->execute(array( $location['address'], $location['city'], $location['region'] ));
- $row = $q->fetch();
-
- if (!isset($row['id']))
- {
- $q = $dbh->prepare("SELECT id FROM location WHERE latitude = ? AND longitude = ?");
- $q->execute(array( $location['latitude'], $location['longitude'] ));
- $row = $q->fetch();
- }
-
- /* PDO is not really useful here. What's wrong with a way of counting results? */
- if (isset($row['id']))
- {
- return $row['id'];
- }
- else
- {
- $location['municipality'] = getMunicipality($location['latitude'], $location['longitude'], $location['city']);
- $q = $dbh->prepare("INSERT INTO location (address, city, region, municipality, active, latitude, longitude) VALUES (?, ?, ?, ?, ?, ?, ?)");
-
- if (! array_key_exists('active', $location)) { $location['active'] = 'false'; }
-
- $q->execute(array(
- $location['address'],
- $location['city'],
- $location['region'],
- $location['municipality'],
- $location['active'],
- $location['latitude'],
- $location['longitude'],
- ));
-
- return $dbh->lastInsertId();
- }
- }
- function getLocation($id)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT address, id, latitude, longitude, city, region FROM location WHERE id=?");
- $q->execute(array( $id ));
- $row = $q->fetch();
-
- $return = array();
- $return['latitude'] = $row['latitude'];
- $return['longitude'] = $row['longitude'];
- $return['city'] = $row['city'];
- $return['region'] = $row['region'];
- $return['address'] = $row['address'];
-
- return $return;
- }
- function minusLocation($locationId)
- {
- global $dbh;
-
- if ($locationId == 0) { return; }
-
- $q = $dbh->prepare("UPDATE location SET weight = weight - 1 WHERE id = ? AND weight > 0");
- $q->execute(array(
- $locationId
- ));
-
- $q = $dbh->prepare("UPDATE location SET active = 'false' WHERE id = ? AND weight = 0 AND active = 'true'");
- $q->execute(array(
- $locationId
- ));
-
- /* Did we actually flip this location from active to inactive? */
- if ($q->rowCount() == 0) { return; }
-
-
- $marker_q = $dbh->prepare("SELECT marker, zoom FROM location_markers WHERE location = ?");
- $marker_q->execute(array(
- $locationId
- ));
-
- $markers = array();
- foreach ($marker_q->fetchAll() as $row)
- {
- $markers[] = $row['marker'];
- }
-
- $markerString = join(',', $markers);
-
- $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");
- $marker_weight_q->execute();
-
- foreach ($marker_weight_q->fetchAll() as $row)
- {
- /* We're about to remove the last location <-> marker connection for this marker, zap the marker */
- if ($row['weight'] == 1) { $dbh->exec("DELETE from markers WHERE id = " . $row['id']); }
- }
-
- #error_log("Deleting location markers for $locationId");
- $q = $dbh->prepare("DELETE FROM location_markers WHERE location = ?");
- $q->execute(array(
- $locationId
- ));
- }
- function plusLocation($locationId)
- {
- global $dbh;
- $q = $dbh->prepare("UPDATE location SET weight = weight + 1 WHERE id = ?");
- $q->execute(array(
- $locationId
- ));
-
- $q = $dbh->prepare("UPDATE location SET active = 'true' WHERE id = ? AND active = 'false'");
- $q->execute(array(
- $locationId
- ));
-
- /* Did we actually flip this location from inactive to active? */
- if ($q->rowCount() == 0) { return; }
-
- $loc = $dbh->prepare("SELECT latitude, longitude FROM location WHERE id = ?");
- $loc->execute(array(
- $locationId
- ));
- $row = $loc->fetch();
-
- $lat = $row['latitude'];
- $lng = $row['longitude'];
-
- /* Okay, this is where the real fun begins.. */
- $q = $dbh->prepare("SELECT zoomLevel, kilometers FROM marker_aggregation");
- $q->execute();
-
- foreach ($q->fetchAll() as $row)
- {
- /* See if we have any marker within our zoomLevel distance using the great circle... */
- $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");
- $dist->execute(array(
- $row['zoomLevel']
- ));
-
- $marker = false;
- $distrow = $dist->fetch();
- if ($distrow['distance'] <= $row['kilometers']) {
- /* We did have a marker? Okay, attach us to the closest one */
- $marker = $distrow['id'];
- }
- else
- {
- /* We didn't have a marker? Okay, create a new one */
- $marker_q = $dbh->prepare("INSERT INTO markers (zoom, latitude, longitude) VALUES (?, ?, ?)");
- $marker_q->execute(array(
- $row['zoomLevel'], $lat, $lng
- ));
-
- $marker = $dbh->lastinsertid();
- }
-
- if ($marker != false)
- {
- /* Add location <-> marker glue */
- #error_log("Connecting $locationId to $marker");
- $lm_update = $dbh->prepare("INSERT INTO location_markers (location, marker, zoom) VALUES (?, ?, ?)");
- $lm_update->execute(array(
- $locationId,
- $marker,
- $row['zoomLevel']
- ));
- }
- }
- }
- function updateEventLocation($id, $newLocation, $forceNew = false)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT location FROM event WHERE id=?");
- $q->execute(array($id));
- $row = $q->fetch();
- $oldLocation = $row['location'];
-
- /* No change? Do nothing. Unless told to do so explictly */
- if ($oldLocation == $newLocation && ! $forceNew) { return; }
-
- /* We should do minusLocation if the old and new locations differ */
- if ($oldLocation != $newLocation) {
- $q = $dbh->prepare("UPDATE event SET location = ? WHERE id = ?");
- $q->execute(array(
- $newLocation,
- $id
- ));
-
- if ($oldLocation) { minusLocation($oldLocation); }
- }
-
- /* We should always do plusLocation */
- plusLocation($newLocation);
-
- }
- function updateEntityLocation($id, $newLocation)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT location FROM entity WHERE id=?");
- $q->execute(array(
- $id
- ));
- $row = $q->fetch();
- $oldLocation = $row['location'];
-
- /* No change? Do nothing */
- if ($oldLocation == $newLocation) { return; }
-
- minusLocation($oldLocation);
- plusLocation($newLocation);
-
- $q = $dbh->prepare("UPDATE entity SET location = ? WHERE id = ?");
- $q->execute(array(
- $newLocation,
- $id
- ));
- }
- function verifyEntityUserPermission($userid, $entity)
- {
- global $dbh;
- $q = $dbh->prepare("SELECT user, entity FROM user_entity WHERE entity = ? AND user = ? AND (permission = 'bureaucrat' OR permission = 'editor')");
- $q->execute(array($entity, $userid));
-
- $row = $q->fetch();
- if ($row['user'] == $userid && $row['user'] != '') { return true; }
-
- return false;
- }
- function verifyEntityHasPermissions($entity)
- {
- global $dbh;
- $q = $dbh->prepare("SELECT count(user) as hits FROM user_entity WHERE entity = ?");
- $q->execute(array($entity));
- $row = $q->fetch();
-
- return $row['hits'];
- }
- function updateEventTags($id, $taglist)
- {
- global $dbh;
-
- $targetTags = array();
- $currentTags = array();
- foreach (getTagsByEvent($id) as $tag) { $currentTags[ $tag ] = $tag; }
- $changes = array(
- 'added' => array(),
- 'removed' => array()
- );
-
- if ($taglist != '') {
- foreach (split(',', $taglist) as $tag) { $targetTags[ $tag ] = $tag; }
- }
-
-
- /* Remove and add the relevant tag entries */
- foreach ($currentTags as $tag)
- {
- if (array_key_exists($tag, $targetTags)) { continue; }
- $changes['removed'][$tag] = $tag;
- detachTagFromEvent($tag, $id);
- }
-
- foreach ($targetTags as $tag)
- {
- if (array_key_exists($tag, $currentTags)) { continue; }
- $changes['added'][$tag] = $tag;
- attachTagToEvent($tag, $id);
- }
-
- adjustTagTagRelation($changes, $currentTags, $targetTags);
- }
- function updateEntityTags($id, $taglist)
- {
- global $dbh;
-
- $targetTags = array();
- $currentTags = array();
- foreach (getTagsByEntity($id) as $tag) { $currentTags[ $tag ] = $tag; }
- $changes = array(
- 'added' => array(),
- 'removed' => array()
- );
-
- if ($taglist != '') {
- foreach (split(',', $taglist) as $tag) { $targetTags[ $tag ] = $tag; }
- }
-
-
- /* Remove and add the relevant tag entries */
- foreach ($currentTags as $tag)
- {
- if (array_key_exists($tag, $targetTags)) { continue; }
- $changes['removed'][$tag] = $tag;
- detachTagFromEntity($tag, $id);
- }
-
- foreach ($targetTags as $tag)
- {
- if (array_key_exists($tag, $currentTags)) { continue; }
- $changes['added'][$tag] = $tag;
- attachTagToEntity($tag, $id);
- }
-
- adjustTagTagRelation($changes, $currentTags, $targetTags);
- }
- function adjustTagTagRelation($changes, $current, $target)
- {
- global $dbh;
-
- /* Strengthen the relation between the added tags and all the tags in the target set */
- $values = array();
- foreach ($changes['added'] as $tag)
- {
- foreach ($target as $relation)
- {
- if ($tag == $relation) { continue; }
- error_log("++ $tag $relation");
- $values[] = '(' . intval($tag) . ',' . intval($relation) . ')';
- }
- }
- if (count($values) > 0)
- {
- $query = "INSERT INTO tag_tag (t_tag, t_related) VALUES " . join(',', $values) . " ON DUPLICATE KEY UPDATE t_weight = t_weight + 1";
- $dbh->exec($query);
- }
-
-
- /* Weaken the relation between the removed tags and the tags in the current set */
- $tagArray = array();
- $relationArray = array();
- foreach ($changes['removed'] as $tag)
- {
- foreach ($current as $relation)
- {
- if ($tag == $relation) { continue; }
- $tagArray[$tag] = $tag;
- $relationArray[$relation] = $relation;
- error_log("-- $tag $relation");
- }
- }
- $tagString = join(',', $tagArray);
- $relationString = join(',', $relationArray);
- if (count($tagArray) > 0 && count($relationArray) > 0)
- {
- $query = "UPDATE LOW_PRIORITY tag_tag SET t_weight=t_weight-1 WHERE t_weight > 0 AND
- ((t_tag IN ($tagString) AND t_related IN ($relationString)) OR (t_tag IN ($relationString) AND t_related IN ($tagString)))";
- #error_log($query);
- $dbh->exec($query);
- }
-
-
- /* Housekeeping */
- $dbh->exec("DELETE LOW_PRIORITY FROM tag_tag WHERE t_weight = 0");
- }
- function updateEventGrants($id, $grantlist)
- {
- global $dbh;
-
- $targetGrants = array();
- $currentGrants = array();
- foreach (getGrantsByEvent($id) as $grant) { $currentGrants[ $grant ] = $grant; }
-
- if ($grantlist != '') {
- foreach (split(',', $grantlist) as $grant) { $targetGrants[ $grant ] = $grant; }
- }
-
- /* Remove and add the relevant tag entries */
- foreach ($currentGrants as $grant)
- {
- if (array_key_exists($grant, $targetGrants)) { continue; }
- detachGrantFromEvent($grant, $id);
- }
-
- foreach ($targetGrants as $grant)
- {
- if (array_key_exists($grant, $currentGrants)) { continue; }
- attachGrantToEvent($grant, $id);
- }
- }
- function eventFieldMap()
- {
- /* Map the entry description keys to the relevant SQL columns. We do this for two reasons:
- One, to provide a mapping if we need to change the data structure at some point.
- Two, to ensure that we only pass relevant keys from the entry desc.
- */
-
- return array(
- 'active' => 'active',
- 'fulldesc' => 'fullDesc',
- 'shortdesc' => 'shortDesc',
- 'name' => 'name',
- 'location' => 'location',
- 'locationdesc' => 'locationDesc',
- 'source' => 'source',
- 'end' => 'end',
- 'start' => 'start',
- 'starttime' => 'startTime',
- 'phone' => 'phone',
- 'id' => 'id',
- 'filename' => 'filename',
- 'path' => 'path',
- 'entity' => 'entity',
- 'age' => 'targetAge',
- 'website' => 'website',
- 'email' => 'email',
- 'attendance' => 'attendance',
- 'contact' => 'contactDesc',
- 'parent' => 'instanceOf',
- 'updated' => 'updated',
- 'archived' => 'archived'
- );
- }
- function entityShortTypeToType($short)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT id, name FROM entity_type WHERE short = ?");
- $q->execute(array( $short ));
- $row = $q->fetch();
-
- if (! $row['id']) { return false; }
- return $row['id'];
- }
- function entityFieldMap()
- {
- /* Map the entry description keys to the relevant SQL columns. We do this for two reasons:
- One, to provide a mapping if we need to change the data structure at some point.
- Two, to ensure that we only pass relevant keys from the entry desc.
- */
-
- return array(
- 'active' => 'active',
- 'fulldesc' => 'fullDesc',
- 'shortdesc' => 'shortDesc',
- 'name' => 'name',
- 'location' => 'location',
- 'locationdesc' => 'locationDesc',
- 'id' => 'id',
- 'fid' => 'groupKey',
- 'age' => 'targetAge',
- 'website' => 'website',
- 'email' => 'email',
- 'attendance' => 'attendance',
- 'contact' => 'contactDesc',
- #'filename' => 'filename',
- 'bannerpath' => 'bannerpath',
- 'updated' => 'updated'
- );
- }
- function attachTagToEntity($tag, $event)
- {
- global $dbh;
-
- $q = $dbh->prepare("INSERT INTO org_tag (org, tag) VALUES (?, ?)");
- return $q->execute(array( $event, $tag ));
- }
- function detachTagFromEntity($tag, $event)
- {
- global $dbh;
-
- $q = $dbh->prepare("DELETE FROM org_tag WHERE org = ? AND tag = ?");
- return $q->execute(array( $event, $tag ));
- }
- function attachTagToEvent($tag, $event)
- {
- global $dbh;
-
- $q = $dbh->prepare("INSERT INTO event_tag (event, tag) VALUES (?, ?)");
- return $q->execute(array( $event, $tag ));
- }
- function detachTagFromEvent($tag, $event)
- {
- global $dbh;
-
- $q = $dbh->prepare("DELETE FROM event_tag WHERE event = ? AND tag = ?");
- return $q->execute(array( $event, $tag ));
- }
- function attachGrantToEvent($grant, $event)
- {
- global $dbh;
- $q = $dbh->prepare("INSERT INTO event_grant (event, grantid) VALUES (?, ?)");
- return $q->execute(array( $event, $grant ));
- }
- function detachGrantFromEvent($grant, $event)
- {
- global $dbh;
- $q = $dbh->prepare("DELETE FROM event_grant WHERE event = ? AND grantid = ?");
- return $q->execute(array( $event, $grant ));
- }
- function getTagsByEntity($entity)
- {
- global $dbh;
-
- $tags = array();
- $q = $dbh->prepare("SELECT tag FROM org_tag WHERE org = ?");
- $q->execute(array($entity));
-
- foreach ($q->fetchAll() as $row) { $tags[] = $row['tag']; }
-
- return $tags;
- }
- function getTagsByEvent($event)
- {
- global $dbh;
-
- $tags = array();
- $q = $dbh->prepare("SELECT tag FROM event_tag WHERE event = ?");
- $q->execute(array($event));
-
- foreach ($q->fetchAll() as $row) { $tags[] = $row['tag']; }
-
- return $tags;
- }
- function getGrantsByEvent($event)
- {
- global $dbh;
-
- $grants = array();
- $q = $dbh->prepare("SELECT grantid FROM event_grant WHERE event = ?");
- $q->execute(array($event));
-
- foreach ($q->fetchAll() as $row) { $grants[] = $row['grantid']; }
-
- return $grants;
- }
- function getNodeContent($type, $id)
- {
- global $dbh;
- $query = array();
- $tags = array();
- $return = array();
-
- $q = $dbh->prepare("INSERT into stats (type, id, hits) VALUES (?, ?, 1) ON DUPLICATE KEY UPDATE hits = hits + 1");
- $q->execute(array($type, $id));
-
- if ($type == 'en')
- {
- $query['columns'] = array(
- 'attendance','targetAge','website', 'email', 'location.city', 'entity.name', 'shortDesc',
- 'fullDesc', 'contactDesc', 'groupKey', 'entity.id', 'entity.name as ename', 'entity.id as eid',
- 'entity.name as eName', 'entity.id as eId');
- $query['tables'] = array('entity', 'location');
- $query['where'] = array("entity.active = 'yes'", 'entity.location = location.id', 'entity.id = ?');
-
- $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";
- }
- else if ($type == 'ev')
- {
- $query['columns'] = array(
- 'event.attendance','event.targetAge','event.website', 'event.email', 'location.city',
- 'event.name', 'event.shortDesc', 'event.fullDesc', 'event.contactDesc', 'start', 'end',
- 'event.id', 'entity.name as eName', 'entity.id as eId',
- 'DATE_FORMAT(starttime, "%H:%i") as startTime', 'event.contactDesc',
- 'location.address as address', 'event.phone', 'banner.approved as bannerApproved', 'banner.path');
- $query['tables'] = array('event', 'location', 'entity', 'banner');
- $query['where'] = array(
- "event.active = 'yes'", 'event.location = location.id', 'event.id = ?',
- 'event.entity = entity.id', 'event.banner = banner.id');
-
- $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";
- }
- else
- {
- return false;
- }
-
- $rs = $dbh->prepare(makeQuerySelect($query));
- $rs->execute(array( $id ));
- $row = $rs->fetch();
-
- $tq = $dbh->prepare($tagQuery);
- $tq->execute(array( $id ));
-
- $return = $row;
- $return['tags'] = array();
- $tagNames = array();
-
- foreach ($tq->fetchAll() as $row)
- {
- $return['tags'][] = intval($row['id']);
- $tagNames[ $row['id'] ] = $row['name'];
- }
-
- return array($type, $tagNames, $return);
- }
- function getEntityByGroupKey($gk)
- {
- global $dbh;
- $q = $dbh->prepare("
- SELECT entity.id as id FROM entity WHERE groupKey = ? AND groupKey != ''
- ");
-
- $q->execute(array($gk));
- $row = $q->fetch();
-
-
- return getEntity( $row['id'] );
- }
- function getEntity($id)
- {
- global $dbh;
-
- $q = $dbh->prepare("
- SELECT entity.id as id, entity.name as name, groupKey as fid, location, etype as entityTypeid,
- entity_type.name as type, entity_type.short as typeShort, location.city,
- shortDesc, fullDesc, contactDesc, locationDesc, email, website , attendance, targetAge
- FROM entity, entity_type, location
- WHERE entity.etype = entity_type.id
- AND location.id = entity.location
- AND entity.id = ?");
-
- $result = array();
- if (! $q->execute(array($id))) { return false; };
- $row = $q->fetch();
-
- $result['id'] = intval($row['id']);
- $result['fid'] = $row['fid'];
- $result['name'] = $row['name'];
- $result['type'] = $row['type'];
- $result['shortdesc'] = $row['shortDesc'];
- $result['fulldesc'] = $row['fullDesc'];
- $result['contact'] = $row['contactDesc'];
- $result['email'] = $row['email'];
- $result['website'] = $row['website'];
- $result['age'] = $row['targetAge'];
- $result['attendance'] = $row['attendance'];
- $result['locationdesc'] = $row['locationDesc'];
- $result['typeid'] = intval($row['entityTypeid']);
- $result['tags'] = join(',', getTagsByEntity($id));
- $result['type'] = $row['typeShort'];
-
- $locationData = getLocation($row['location']);
- $result['city'] = $locationData['city'];
- $result['region'] = $locationData['region'];
- $result['address'] = $locationData['address'];
- $result['latitude'] = $locationData['latitude'];
- $result['longitude'] = $locationData['longitude'];
- return $result;
- }
- function getEntityTypes()
- {
- global $dbh;
- $q = $dbh->prepare("SELECT id, name, short, private, sortOrder FROM entity_type ORDER BY sortOrder ASC");
- $q->execute();
- return $q->fetchAll();
- }
- function deleteEvent($userid, $id)
- {
- global $dbh, $site;
-
- $q = $dbh->prepare("SELECT entity,location FROM event WHERE id = ?");
- if (! $q->execute(array($id))) { return false; }
- $row = $q->fetch();
-
- if (!verifyEntityUserPermission($userid, $row['entity'])) { header('Location: ' . $site['baseHref'] . 'fail'); exit; }
-
- minusLocation($row['location']);
- updateEventTags($id, '');
- updateEventGrants($id, '');
-
- $deleteQuery = $dbh->prepare("DELETE from event WHERE id = ?");
- $deleteQuery->execute(array( $id ));
-
-
- }
- function getEvent($id)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT * FROM banner,event WHERE event.id = ? AND banner.id = event.banner");
- $result = array();
-
- if (! $q->execute(array($id))) { return false; };
- $q->execute(array( $id ));
-
- $fieldMap = eventFieldMap();
- $reverseMap = array();
- foreach ($fieldMap as $field => $column)
- {
- $reverseMap[ $column ] = $field;
- }
-
-
- $row = $q->fetch();
- foreach ($row as $col => $value)
- {
- if (array_key_exists($col, $reverseMap))
- {
- $result[ $reverseMap[$col] ] = $value;
- }
- }
-
- $result['starttime'] = substr($result['starttime'],0,5);
-
- $result['tags'] = join(',', getTagsByEvent($id));
- $result['grants'] = join(',', getGrantsByEvent($id));
-
- $locationData = getLocation($result['location']);
- $result['city'] = $locationData['city'];
- $result['region'] = $locationData['region'];
- $result['address'] = $locationData['address'];
- $result['latitude'] = $locationData['latitude'];
- $result['longitude'] = $locationData['longitude'];
-
- return $result;
- }
- function getEventInstances($id)
- {
- global $dbh;
-
- $query['columns'] = array('id', 'start');
- $query['tables'] = array('event', );
- $query['where'] = array('instanceOf = ?');
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute(array( $id ));
-
- return $q->fetchAll();
- }
- /* END event functions */
- function getOrCreateTagByName($tag)
- {
- $tagEntry = getTagByName($tag);
-
- if ($tagEntry == false)
- {
- if (createTag($tag))
- {
- $tagEntry = getTagByName($tag);
- }
- }
-
- return $tagEntry;
- }
- function createTag($tag)
- {
- global $dbh;
-
- /*
- Keep the names somewhat sane - we don't want any funky stuff in the tag names and Unicode is a bit permissive.
- This Regex limits the tag names to (the unicode representation of) characters present in the ISO-8859-1 charset.
- */
- $tagSan = preg_replace('|[^a-z0-9 _.\-@\'\"\[\]ÀÁÂÄÅÆÇÈÉÊËÌÍÎÏ?ÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ]|i', '', $tag);
- if ($tagSan == '') { return false; }
-
- $ip = $_SERVER['REMOTE_ADDR'];
- if ($ip == '::1') { $ip = '127.0.0.1'; };
-
- $q = $dbh->prepare("INSERT INTO tag (name, lcname, adder_ip, adder_date) VALUES (?, ?, INET_ATON(?), NOW())");
- if (! $q->execute(array($tagSan, strtolower($tagSan), $ip))) { error_log("Execution failed in addTag"); return false; };
-
- return true;
- }
- function getTagById($tagid)
- {
- global $dbh;
- $results = array();
-
- $q = $dbh->prepare("SELECT id, name, weight FROM tag WHERE id = ?");
- if (! $q->execute(array($tagid))) { error_log("Execution failed in getTagById"); return false; };
-
-
- $row = $q->fetch();
- #if (count($row) == 1) { return false; }
-
- $results[ $row['id'] ] = array(
- 'name' => $row['name'],
- 'weight' => $row['weight'],
- 'id' => $row['id']
- );
-
-
- return array($results);
- }
- function getTagByName($tag)
- {
- global $dbh;
- $results = array();
-
- $q = $dbh->prepare("SELECT id, name, weight FROM tag WHERE lcname = ?");
- if (! $q->execute(array(strtolower($tag)))) { error_log("Execution failed in getTagByName"); return false; };
-
-
- $row = $q->fetch();
- if (count($row) == 1) { return false; }
-
- $results[ $row['id'] ] = array(
- 'name' => $row['name'],
- 'weight' => $row['weight'],
- 'id' => $row['id']
- );
-
-
- return array($results);
- }
- function getTaglistByTags($tags, $exclude = null)
- {
- global $dbh;
- $ptq = $dbh->prepare("SELECT distinct(t_related) as tag FROM tag_tag WHERE t_tag IN ($tags)");
- $ptq->execute();
- $relatedArray = explode(',', $tags);
- foreach ($ptq->fetchAll() as $row) { $relatedArray[] = $row['tag']; }
-
- if ($exclude != null)
- {
- $newRelatedArray = array();
- foreach ($relatedArray as $item)
- {
- if (! array_key_exists($item, $exclude))
- {
- $newRelatedArray[] = $item;
- }
- }
-
- $relatedArray = $newRelatedArray;
- }
-
- $relatedTags = implode(',', $relatedArray);
-
- return $relatedTags;
- }
- function getNodesByMarkers($def, $markers)
- {
- global $dbh, $settings;
-
- /* Double check that we're looking at numeric markers */
- foreach ($markers as $m) { if (! is_numeric($m)) { return -1; } }
- 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..
-
-
- $markerString = join(',', $markers);
-
- $entityString = '';
- $tagArray = array();
- $tagNames = array();
- $locationNames = array();
- $categories = array();
- $node = array();
-
-
- $query['columns'] = array('city', 'address');
- $query['tables'] = array('location_markers', 'location');
- $query['where'] = array('location_markers.location = location.id', "location_markers.marker IN ($markerString)", 'entity.active="yes"');
- $query['args'] = array();
- /* Include restrictQueryByDef in case we want to restrict by city/region/tags.. */
- restrictQueryByDef($query, $def, array('tags')); // Removed deprecated &, weird effects may happen.
- $markerDef = $query['def'];
-
- /* Fetch us a list of locations */
- if ($markerDef['section'] == 'entities')
- {
- $query['columns'] = array_merge($query['columns'], array('entity.location', 'entity.id', 'entity_type.name as ename', 'entity.name', 'etype', 'entity.shortDesc'));
- $query['tables'][] = 'entity';
- $query['tables'][] = 'entity_type';
- $query['where'][] = 'entity_type.id = entity.etype';
- $query['where'][] = "location.id = entity.location";
-
- if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
- {
- /* Pre-fetch a list of related tags */
- #$relatedTags = getTaglistByTags($markerDef['tags']);
-
- $query['tables'][] = 'org_tag';
- $query['where'][] = 'org_tag.tag IN (' . $markerDef['tags'] . ')';
- $query['where'][] = 'org_tag.org = entity.id';
- $query['group'][] = 'entity.id';
- }
- }
- else if ($markerDef['section'] == 'events')
- {
- $query['columns'] = array_merge($query['columns'], array(
- 'entity.etype', 'event.entity', 'event.location', 'event.id',
- 'event.name', 'start', 'event.shortDesc', 'entity_type.name as ename'
- ));
- $query['tables'][] = 'event';
- $query['tables'][] = 'entity';
- $query['tables'][] = 'entity_type';
- $query['where'][] = 'event.entity = entity.id';
- $query['where'][] = 'entity.etype = entity_type.id';
- $query['where'][] = "location.id = event.location";
- $query['where'][] = 'start >= DATE(' . getNowValue() . ')';
- $query['order'] = array('start ASC');
-
- if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
- {
- /* Pre-fetch a list of related tags */
- $relatedTags = getTaglistByTags($markerDef['tags']);
-
- $query['tables'][] = 'event_tag';
- $query['where'][] = 'event.id = event_tag.event';
- $query['where'][] = 'event_tag.tag IN (' . $relatedTags . ')';
- $query['group'][] = 'id';
- }
- }
- else
- {
- return -3;
- }
-
- $rs = $dbh->prepare(makeQuerySelect($query));
- $rs->execute($query['args']);
- foreach ($rs->fetchAll() as $row)
- {
- $r_array = array(
- 'name' => $row['name'],
- 'id' => $row['id'],
- 'tags' => array(),
- 'location' => intval($row['location']),
- 'shortDesc' => $row['shortDesc'],
- 'category' => $row['etype']
- );
-
- $categories[ $row['etype'] ] = $row['ename'];
-
- if ($markerDef['section'] == 'events')
- {
- $r_array['entity'] = $row['entity'];
- $r_array['type'] = 'event';
- $r_array['shortType'] = 'ev';
- $r_array['start'] = $row['start'];
- }
- else if ($markerDef['section'] == 'entities')
- {
- $r_array['entity'] = $row['id'];
- $r_array['type'] = 'entity';
- $r_array['shortType'] = 'en';
- }
-
- $node[ $row['id'] ] = $r_array;
- $locationNames[ $row['location'] ] = $row['city'];
- $entityString .= $row['id'] . ',';
- }
- $entityString = chop($entityString, ',');
-
- /* ..and a list of tags based on the entities */
- if ($markerDef['section'] == 'entities')
- {
- $tag_query = "SELECT org, tag from org_tag where org IN ($entityString)";
- }
- else if ($markerDef['section'] == 'events')
- {
- $tag_query = "SELECT event, tag from event_tag where event IN ($entityString)";
- }
-
- $rs = $dbh->prepare($tag_query);
- $rs->execute();
- foreach ($rs->fetchAll() as $row)
- {
- /* A hash might be faster. Investigate at some point.. */
- if (! in_array($row['tag'], $tagArray)) { $tagArray[] = $row['tag']; }
-
- $key = isset($row['org']) ? $row['org'] : $row['event'];
-
- $node[ $key ]['tags'][] = intval($row['tag']);
- }
-
- /* Finally, score a tag list for good measure... */
- if (count($tagArray))
- {
- $tag_query = "SELECT id, name from tag WHERE active = 1 AND id IN (" . join(',', $tagArray) . ")";
- $rs = $dbh->prepare($tag_query);
- $rs->execute();
- foreach ($rs->fetchAll() as $row)
- {
- $tagNames[ $row['id'] ] = $row['name'];
- }
- };
-
- return array($node, $tagNames, $locationNames, $categories);
- }
- function splitMarkerDefs($defs)
- {
- $parts = explode(':', $defs);
-
- $return = array();
- $return['section'] = array_shift($parts);
-
- foreach ($parts as $keyvaluestring)
- {
- $keyval = explode('=', $keyvaluestring);
- if (count($keyval) == 1)
- {
- $return[ $keyval[0] ] = true;
- }
- else
- {
- $return[ $keyval[0] ] = $keyval[1];
- }
- }
-
- return $return;
- }
- function verifyNumberList($list)
- {
- if (preg_match('|[^0-9,]|', $list)) { return false; }
- return true;
- }
- /*
- This function is used by map.js to fetch the markers for a given zoom level, including
- tag references.
-
- */
- function getMarkers($def, $zoom)
- {
- global $dbh, $settings;
- if ($zoom > $settings['map_max_zoom_aggregation']) { $zoom = $settings['map_max_zoom_aggregation']; }
- if ($zoom < $settings['map_min_zoom_aggregation']) { $zoom = $settings['map_min_zoom_aggregation']; }
- $funnel_lookahead = 10;
-
- $query = array();
- /* NOTE: We use sum() + bit_xor() rather than crc32() since mysql 5.1 seem to give us incorrect data when using crc2() or md5().. */
- $query['columns'] = array(
- 'location_markers.zoom',
- 'marker',
- 'markers.latitude',
- 'markers.longitude',
- 'count(marker) as weight',
- 'concat(sum(distinct(location.id)), bit_xor(location.id)) as csum',
- 'avg(location.latitude) as avgLatitude',
- 'avg(location.longitude) as avgLongitude' );
- $query['tables'] = array('location_markers', 'location', 'markers');
- $query['where'] = array(
- 'location_markers.marker = markers.id',
- 'location_markers.zoom >= ?',
- 'location_markers.zoom <= (? + ?)');
- $query['group'] = array('marker');
- $query['order'] = array('location_markers.zoom DESC');
- $query['args'] = array();
-
- $tagq = array();
- $tagq['columns'] = array('marker','tag','count(tag) as weight');
- $tagq['tables'] = array('location_markers');
- $tagq['where'] = array('zoom = ?');
- $tagq['group'] = array('tag','marker');
-
- /* Include restrictQueryByDef in case we want to restrict by city/region/tags.. */
- restrictQueryByDef($query, $def); // Removed deprecated &, weird effects may happen.
- $markerDef = $query['def'];
-
- if ($markerDef['section'] == 'entities')
- {
- $query['tables'][] = 'entity';
- $query['where'][] = 'location_markers.location = entity.location';
- $query['where'][] = 'entity.location = location.id';
- $query['where'][] = 'entity.active = "yes"';
-
- $tagq['tables'][] = 'entity';
- $tagq['tables'][] = 'org_tag';
-
- $tagq['where'][] = 'entity.location = location_markers.location';
- $tagq['where'][] = 'entity.id = org_tag.org';
-
- if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
- {
- $tagList = getTaglistByTags($markerDef['tags']);
- //$tagq['tables'][] = 'tag_tag';
- //$tagq['where'][] = 'org_tag.tag = t_related';
- $tagq['where'][] = 'org_tag.tag IN (' . $tagList . ')';
- }
- }
- else if ($markerDef['section'] == 'events')
- {
- $query['tables'][] = 'event';
- $query['where'][] = 'location_markers.location = event.location';
- $query['where'][] = 'event.location = location.id';
- $query['where'][] = 'event.active = "yes"';
- $query['where'][] = 'start >= DATE(' . getNowValue() . ')';
-
- $tagq['tables'][] = 'event';
- $tagq['tables'][] = 'event_tag';
- $tagq['where'][] = 'event.location = location_markers.location';
- $tagq['where'][] = 'event.id = event_tag.event';
-
- if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']))
- {
- /*
- We don't include the tags in the request in this lookup. I.e if you ask for events:tags=19,
- you'll get everything *related* to 19, but not 19 itself.
- */
- //$tagq['tables'][] = 'tag_tag';
- //$tagq['where'][] = 'event_tag.tag = t_related';
- $exclude = explode(',', $markerDef['tags']);
- $tagList = getTaglistByTags($markerDef['tags'], $exclude);
- $tagq['where'][] = 'event_tag.tag IN (' . $tagList . ')';
- }
- }
- else
- {
- return false;
- }
- $qs = makeQuerySelect($query);
- error_log($qs);
- $q = $dbh->prepare( $qs );
- $a = array_merge(array( $zoom, $zoom, $funnel_lookahead ), $query['args']);
- error_log(var_export($zoom, true) . ' ' . var_export($zoom, true) . ' ' . var_export($funnel_lookahead, true) . ' ' . var_export($query['args'], true));
- # error_log(var_export($a, true));
- $q->execute( $a );
-
-
- $return = array();
- $funnel = array();
- foreach ($q->fetchAll() as $row)
- {
- $csum = $row['csum'];
- if (! array_key_exists($csum, $funnel)) {
- $funnel[$csum]['zoom'] = $row['zoom'];
- }
-
- /*
- We'll iterate through several levels worth of zoom in one query (in order to figure
- out whether a marker is a funnel or not) - so for purposes other than figuring out
- the funnel depth, just iterate through the loop if we're looking at a row with a zoom
- level other than the one we're returning to the user.
-
-
- */
- if ($row['zoom'] != $zoom) { continue; }
-
- $entry = array();
- $entry['latitude'] = $row['latitude'];
- $entry['longitude'] = $row['longitude'];
- $entry['zoom'] = $row['zoom'];
- $entry['weight'] = $row['weight'];
- $entry['id'] = $row['marker'];
- $entry['tag'] = array();
- $entry['funnel'] = 0;
-
- if (array_key_exists($csum, $funnel))
- {
- $funLevel = $funnel[$csum]['zoom'];
- if (! ($funLevel == $zoom + $funnel_lookahead || $funLevel >= $settings['map_max_zoom_aggregation'] ))
- {
- $entry['funnel'] = $funLevel + 1;
- if ($funLevel > $zoom + 1)
- {
- /*
- If we have a long'ish funnel, we want to recenter the map to where the
- actual locations are found, not to where the marker position is at.
- */
- $entry['recenterLatitude'] = $row['avgLatitude'];
- $entry['recenterLongitude'] = $row['avgLongitude'];
- }
- }
- }
-
- $return[$zoom][ $row['marker'] ] = $entry;
- }
- error_log(var_export($return, true));
-
- $qs = makeQuerySelect($tagq);
- error_log($qs);
- $q = $dbh->prepare($qs);
- $q->execute(array( $zoom ));
- $tagIndex = array();
- if (array_key_exists($zoom, $return))
- {
- foreach ($q->fetchAll() as $row)
- {
- /*
- We seem to be getting slightly more tag results than we bargain for. Do this check for
- now, investigate properly later. Doesn't seem like a problem.
- */
- if (! array_key_exists($row['marker'], $return[$zoom])) { continue; }
-
- $return[$zoom][ $row['marker'] ]['tag'][$row['tag']] = intval($row['weight']);
- $tagIndex[ $row['tag'] ] = $row['tag'];
- }
- }
-
- /* Fetch tag names while we're at it */
- $tagList = join(',', array_keys($tagIndex));
- $q = $dbh->prepare("SELECT id, name, is_category FROM tag WHERE id IN($tagList)");
- $q->execute();
- foreach ($q->fetchAll() as $row)
- {
- /* If we got a tag to match this to in the markerDef, don't list category tags in the output */
- if (array_key_exists('tags', $markerDef)) {
- if ($row['is_category']) { unset($tagIndex[ $row['id'] ]); continue; }
- }
-
- $tagIndex[ $row['id'] ] = $row['name'];
- }
-
- # error_log(var_export($return, true));
- # error_log(var_export($tagIndex, true));
- return array($return, $tagIndex);
- }
- /* We use this instead of NOW() in order to get query cache hits. */
- function getNowValue()
- {
- return('"' . date('Y-m-d H:00:01') . '"');
- }
- function getMenuItems($which)
- {
- global $dbh, $site, $settings;
-
- $q = $dbh->prepare('SELECT menu, align, title, url, visible, combination FROM menu WHERE visible="yes" ORDER BY sortorder,title ASC');
- $q->execute(array( $which ));
-
- $items = Array();
- $lookupBySub = Array();
- foreach ($q->fetchAll() as $row) {
- $items[] = $row;
-
- if ($row['menu'] == 'sub') { $lookupBySub[ $row['url'] ] = $row; }
- }
-
- $return = array();
- foreach ($items as $row) {
- if ($row['menu'] != $which) { continue; }
-
- $i = array(
- 'title' => $row['title'],
- 'selected' => false
- );
-
- $i['path'] = $site['base'];
- $p1 = isset($site['requestPath'][1]) ? $site['requestPath'][1] : '';
-
- if ($row['combination'] == 'yes')
- {
- if ($which == 'main') {
- if (array_key_exists($p1, $lookupBySub)) {
- $i['path'] .= $row['url'] . '/' . $p1;
- }
- else
- {
- $i['path'] .= $row['url'] . '/';
- }
- }
- else { $i['path'] .= $site['selection'] . '/' . $row['url']; }
- }
- else
- {
- $i['path'] .= $row['url'];
- }
-
- if ($which == 'main')
- {
- if ($site['selection'] == $row['url']) { $i['selected'] = true; }
- }
- else
- {
- if ($p1 == $row['url']) { $i['selected'] = true; }
- }
-
- $i['align'] = $row['align'];
- $return[] = $i;
- }
-
- if ($which == 'main' && $site['userid'])
- {
- $return[] = array(
- 'title' => 'Logga ut ' . $site['username'],
- 'selected' => false,
- 'align' => 'right',
- 'path' => 'logout'
- );
- }
-
- if ($which == 'main' && $site['maintainer'])
- {
- $return[] = array(
- 'title' => 'Underhåll',
- 'selected' => false,
- 'align' => 'right',
- 'path' => 'maintain'
- );
- }
-
- return $return;
- }
- function getTagsByTags($tagList)
- {
- global $dbh;
-
- $tagidString = join(',', $tagList);
- $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)");
- $q->execute();
-
- $results = array();
- foreach ($q->fetchAll() as $row) {
- $results[ $row['id'] ] = array(
- 'name' => $row['name'],
- 'weight' => $row['weight'],
- 'id' => $row['id']
- );
- }
- return $results;
- }
- function getTagsByPartial($partial)
- {
- global $dbh;
- $tags = array();
-
- $q = $dbh->prepare("SELECT name, weight FROM tag WHERE active > 0 AND lcname LIKE ? ORDER BY weight DESC");
- if (! $q->execute(array("$partial%"))) { error_log("Execution failed in getTagsByPartial"); return false; };
-
- foreach ($q->fetchAll() as $row) { $tags[] = $row['name']; }
-
- return array($tags);
- }
- function loginSession($args)
- {
- global $site;
- $username = $args[0];
- $password = $args[1];
- $redirect = $args[2];
-
- if ($username == 'sso' && substr($password, 0, 4) == 'sso-')
- {
- if (loginSSO(substr($password, 4)) == false) { return false; }
- header('Location: ' . $site['baseHref'] . $redirect);
- return true;
- }
- else
- {
- if (! doCommunityLogin($username, $password)) { return false; }
- header('Location: ' . $site['baseHref'] . $redirect);
- return true;
- }
-
- return false;
- }
- function doCommunityLogin($username, $password)
- {
- global $settings;
-
- $api_req = $settings['community_api_private_url']. '?cmd=login' .
- "&username=" . urlencode($username) .
- "&password=" . urlencode($password);
-
- $req = curl_init($api_req);
- curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
- $response = curl_exec($req);
- curl_close($req);
-
- if ($response == "") { return false; }
- $xml = new SimpleXMLElement($response);
- $usernode = $xml->user[0];
-
-
- if (gettype($usernode) != 'object') { return false; }
- if ($usernode->attributes()->type != 'active') { return false; }
- $maintainer = false;
-
- if (array_key_exists('community_api_maintainer_group', $settings))
- {
- foreach ($usernode->xpath('./group') as $groupElt)
- {
- if ($groupElt->attributes()->name == $settings['community_api_maintainer_group'])
- {
- $maintainer = true;
- }
- }
- }
-
- $remote_username = $usernode->attributes()->username . '';
- $remote_email = $usernode->attributes()->email . '';
-
- provisionUser($remote_username, $remote_email, $maintainer);
-
- return true;
- }
- function getRecentEventsByEntity($entity)
- {
- global $dbh;
-
- $query = array();
- $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');
- $query['tables'] = array('event', 'location', 'user');
- $query['where'] = array('event.location = location.id', 'instanceOf IS NULL', 'event.entity = ?', 'updatedBy = user.id', 'event.banner = banner.id');
- $query['order'] = array('event.updated DESC');
- $query['limit'] = 5;
-
-
- $q = makeQuerySelect($query);
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute(array( $entity ));
-
- $result = fillGrantsForEvents($q->fetchAll());
- return $result;
- }
- function getEventsByEntity($entity)
- {
- global $dbh;
-
- $query = array();
- $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');
- $query['tables'] = array('event', 'location', 'user', 'banner');
- $query['where'] = array('event.location = location.id', 'event.entity = ?', 'updatedBy = user.id', 'event.banner = banner.id');
- $query['order'] = array('event.start DESC');
-
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute(array( $entity ));
-
- $result = fillGrantsForEvents($q->fetchAll());
- return $result;
- }
- function fillGrantsForEvents($data)
- {
- global $dbh;
-
- $return = array();
- $eids = array();
- foreach ($data as $row)
- {
- $eids[] = $row['id'];
- }
-
- $query['columns'] = array('event','grantid','submitted');
- $query['tables'] = array('event_grant');
- $query['where'] = array('event IN (' . join(",", $eids) . ')');
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute();
-
- $lut = array();
- foreach ($q->fetchAll() as $row)
- {
- $lut[$row['event']][$row['grantid']]['submitted'] = $row['submitted'];
- }
-
- foreach ($data as $row)
- {
- if (array_key_exists($row['id'], $lut))
- {
- $row['grant'] = $lut[ $row['id'] ];
- }
- $return[] = $row;
- }
-
- return $return;
- }
- /* ===== Not yet validated, might not be in use ===== */
- function provisionUser($username, $email, $maintainer_bool)
- {
- global $dbh;
-
- $maintainer = 'false';
- if ($maintainer_bool) { $maintainer = 'true'; }
-
- $resultSet = $dbh->prepare('SELECT id FROM user WHERE lcusername = ?');
- $resultSet->execute(array(strtolower($username)));
- $row = $resultSet->fetch();
-
- if (! $row['id'])
- {
- $newUser = $dbh->prepare('INSERT INTO user (username, lcusername, maintainer) VALUES (?, ?, ?)');
- if (! $newUser->execute( array($username, strtolower($username), $maintainer) )) { return false; }
-
- $userid = $dbh->lastInsertId();
- }
- else
- {
- $userid = $row['id'];
- }
-
- $q = $dbh->prepare("UPDATE user SET email=?, maintainer=? WHERE id = ?");
- $q->execute(array($email, $maintainer, $userid));
- $_SESSION['userid'] = $userid;
- $_SESSION['username'] = $username;
- $_SESSION['maintainer'] = $maintainer_bool;
- }
- function loginSSO($hash)
- {
- global $settings, $site;
-
- $sitecookie = hash('sha256', $settings['community_api_secret'] . $settings['community_api_sitename'] . $hash);
- $api_req = $settings['community_api_private_url']. '?cmd=verify_cookie&authcookie=' . urlencode($hash) .
- "&site=" . urlencode($settings['community_api_sitename']) .
- "&sitecookie=" . urlencode($sitecookie);
-
- $req = curl_init($api_req);
- curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
- curl_setopt($req, CURLOPT_HTTPHEADER, array("Content-type: text/xml;charset=UTF-8"));
- $response = curl_exec($req);
- curl_close($req);
-
- if ($response == '') { $site['attempt_sso'] = false; return false; }
-
- $xml = simplexml_load_string($response);
- $usernode = $xml->user[0];
- if (gettype($usernode) != 'object') { $site['attempt_sso'] = false; return false; }
-
- $username = $usernode->attributes()->username;
- $username = (string)$username;
- $email = $usernode->attributes()->email;
- $email = (string)$email;
- $maintainer = false;
-
- if (array_key_exists('community_api_maintainer_group', $settings))
- {
- foreach ($usernode->xpath('./group') as $groupElt)
- {
- if ($groupElt->attributes()->name == $settings['community_api_maintainer_group'])
- {
- $maintainer = true;
- }
- }
- }
-
- if ($username != '' && $usernode->attributes()->type == 'active') {
- provisionUser($username, $email, $maintainer);
- return $username;
- };
-
- $site['attempt_sso'] = false;
- return false;
- }
- function userInEntity($user, $entity)
- {
- global $dbh;
- $q = $dbh->prepare("SELECT id, permission FROM user_entity WHERE user = ?");
- $q->execute(array($user));
- $row = $q->fetch();
-
- if (! $row['id']) { return false; }
- return $row['permission'];
- }
- function userEntities($userid)
- {
- global $dbh;
-
- $userid = intval($userid);
- $q = $dbh->prepare("
- SELECT city, entity_type.name as etype, user_entity.entity, user_entity.permission, entity.name
- FROM entity,user_entity,entity_type,location
- WHERE entity.etype = entity_type.id
- AND location.id = entity.location
- AND user_entity.entity = entity.id
- AND entity.active = 'yes'
- AND user = ?
- GROUP BY user_entity.entity");
-
- $q->execute(array($userid));
-
- $return = array();
- foreach ($q->fetchAll() as $row)
- {
- $r = array();
- $r['name'] = $row['name'];
- $r['city'] = $row['city'];
- $r['id'] = $row['entity'];
- $r['type'] = $row['etype'];
- $r['permission'] = $row['permission'];
- $r['weight'] = 10;
-
- $return[] = $r;
- }
-
- return $return;
- }
- function getCategoryTags()
- {
- global $dbh;
- $tagids = array();
- $results = array();
-
- $masterResultSet = $dbh->prepare("SELECT id, name, weight from tag WHERE is_category = 1");
- $masterResultSet->execute();
-
- foreach ($masterResultSet as $row) {
- $results[ $row['id'] ] = array(
- 'name' => $row['name'],
- 'weight' => $row['weight'],
- 'id' => $row['id']
- );
- }
-
- return array($results);
- }
- function getTagIDsByTags($tagList)
- {
- global $dbh;
-
- $cleanTags = array();
- foreach ($tagList as $tag) { $cleanTags[$tag] = $dbh->quote(strtolower($tag)); }
- $tagNameString = join(',', $cleanTags);
-
- $tagids = array();
- $masterResultSet = $dbh->prepare("SELECT id, name from tag WHERE lcname IN ($tagNameString)");
- $masterResultSet->execute();
-
- foreach ($masterResultSet as $row) { $tagids[] = $row[0]; }
-
- return $tagids;
- }
- function usernameExists($username)
- {
- global $settings;
- $api_req = $settings['community_api_private_url']. '?cmd=exists&username=' . urlencode($username);
-
- $req = curl_init($api_req);
- curl_setopt($req, CURLOPT_RETURNTRANSFER, true);
- curl_setopt($req, CURLOPT_HTTPHEADER, array("Content-type: text/xml;charset=UTF-8"));
- $response = curl_exec($req);
- curl_close($req);
-
- $xml = simplexml_load_string($response);
- $usernode = $xml->user[0];
- if (gettype($usernode) != 'object') { return false; }
-
- $username = $usernode->attributes()->username;
-
- if ($username != '' && $usernode->attributes()->type == 'active') { return intval($usernode->attributes()->id); };
- return false;
- }
- function userlistByEntity($entity)
- {
- global $dbh;
-
- if (! userInEntity($_SESSION['userid'], $entity))
- {
- return false;
- }
-
-
- $results = array();
- $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 = ?");
- $resultSet->execute($entity);
-
- foreach ($resultSet->fetchAll() as $row) {
- $results[] = array(
- 'callsign' => $row['callsign'],
- 'id' => $row['userid'],
- 'username' => $row['username'],
- 'permission' => $row['permission']
- );
- }
-
- return array($results);
- }
- function makeQueryUpdate($query)
- {
- $string = 'UPDATE ' . $query['table'] . ' SET ';
- $string .= join(', ', $query['update']);
- $string .= ' WHERE ' . join(' AND ', $query['where']);
-
- return $string;
- }
- function makeQuerySelect($query)
- {
- $string = 'SELECT ' . join(', ', $query['columns']);
- $string .= ' FROM ' . join(', ', $query['tables']);
- if (array_key_exists('left-join', $query)) { $string .= ' LEFT JOIN ' . join(', ', $query['left-join']); };
- if (array_key_exists('on', $query)) { $string .= ' ON ' . join(' AND ', $query['on']); };
-
- $string .= ' WHERE ' . join(' AND ', $query['where']);
- if (array_key_exists('group', $query)) { $string .= ' GROUP BY ' . join(', ', $query['group']); };
- if (array_key_exists('order', $query)) { $string .= ' ORDER BY ' . join(', ', $query['order']); };
- if (array_key_exists('limit', $query)) { $string .= ' LIMIT ' . $query['limit']; };
-
- return $string;
- }
- function archiveEvents()
- {
- global $dbh;
- $query['update'] = array('archived = "true"');
- $query['where'] = array("DATE(start) < NOW()", "archived = 'false'");
- $query['table'] = 'event';
-
- $q = $dbh->prepare( makeQueryUpdate($query) );
- $q->execute();
-
- return true;
- }
- function exportGrant($args)
- {
- global $dbh;
-
- archiveEvents();
- $grantId = $args[0];
- $startdate = $args[1];
- $enddate = $args[2];
- $query = array();
- $query['columns'] =
- array('event.id', 'event.name', 'event.shortDesc',
- 'event.fullDesc', 'startTime', 'event.attendance', 'event.targetAge',
- 'weekday(start) as weekday', 'start', 'end', 'event.email', 'event.phone',
- 'event.active', 'source', 'location.city',
- 'DATE_FORMAT(updated, "%Y-%m-%d %H:%i") as updated',
- 'latitude', 'longitude', 'municipality.name AS muni',
- 'region', 'country', 'groupKey', 'entity.name AS "entity.name"', 'event.created', 'event.attended', 'event_grant.approved', 'event.paid');
-
- $query['tables'] =
- array('event', 'location', 'event_grant', 'municipality', 'entity');
-
- $query['where'] =
- array('event.id = event_grant.event', 'event_grant.grantid = ?',
- 'event.location = location.id', "event.archived = 'true'",
- 'municipality.id = location.municipality', 'event.entity = entity.id');
- $query['order'] = array('event.start DESC');
- $query_args = array($grantId);
- // Both start date and end date should be set.
- if($startdate !== '' && $enddate !== '') {
- $query['where'][] = 'event.start BETWEEN ? AND ?';
- $query_args[] = $startdate;
- $query_args[] = $enddate;
- } else {
- return array(array());
- }
- $q = $dbh->prepare( makeQuerySelect($query) );
-
- $q->execute($query_args);
- $set = $q->fetchAll();
-
- /* Use the same data to make an update query to signify that the data has been submitted.. */
- $ids = array();
- foreach ($set as $row) { $ids[] = $row['id']; }
- $qs2 = "UPDATE event_grant SET submitted = 'true' where event IN (" . join(',', $ids) . ') AND grantId = ?';
- if($startdate !== '' && $enddate !== '') {
- $qs2 .= 'AND event.start BETWEEN ? AND ?';
- }
- $g = $dbh->prepare($qs2);
- $g->execute($query_args);
-
- return array($set);
- }
- function restrictQueryByDef(&$query, $def, $ignore = array() )
- {
- $markerDef = splitMarkerDefs($def);
- $query['def'] = $markerDef;
- if (! array_key_exists('args', $query)) { $query['args'] = array(); }
- if (! array_key_exists('tables', $query)) { $query['tables'] = array(); }
- #if (! array_key_exists('group', $query)) { $query['group'] = array(); }
-
- $what = $markerDef['section'];
- if ($what != 'events' && $what != 'entities') { return false; }
- // Removed deprecated &s in the restrict calls, weird effects may happen.
- if (array_key_exists('entity', $markerDef) && ! in_array('entity', $ignore)) { restrict('entity.id', $markerDef['entity'], $query); }
- if (array_key_exists('groupKey', $markerDef) && ! in_array('groupKey', $ignore)) { restrict('entity.groupKey', $markerDef['groupKey'], $query); }
- if (array_key_exists('city', $markerDef) && ! in_array('city', $ignore)) { restrict('location.city', $markerDef['city'], $query); }
- if (array_key_exists('municipality', $markerDef) && ! in_array('municipality', $ignore)) { restrict('location.municipality', $markerDef['municipality'], $query); }
- if (array_key_exists('region', $markerDef) && ! in_array('region', $ignore)) { restrict('location.region', $markerDef['region'], $query); }
- if (array_key_exists('type', $markerDef) && ! in_array('type', $ignore)) { restrict('entity.etype', $markerDef['type'], $query); }
- if (array_key_exists('tags', $markerDef) && verifyNumberList($markerDef['tags']) && ! in_array('tags', $ignore))
- {
- if ($what == 'events')
- {
- $query['tables'][] = 'event_tag';
- $query['where'][] = 'event_tag.event = event.id';
- restrict('event_tag.tag', $markerDef['tags'], $query); // Removed deprecated &, weird effects may happen.
- }
- else if ($what == 'entities')
- {
- $query['tables'][] = 'org_tag';
- $query['where'][] = 'org_tag.org = entity.id';
- restrict('org_tag.tag', $markerDef['tags'], $query); // Removed deprecated &, weird effects may happen.
- }
- }
- if (array_key_exists('withBanners', $markerDef) && ! in_array('type', $ignore)) {
- restrict('banner.approved', 'true', $query); // Removed deprecated &, weird effects may happen.
- if ($markerDef['withBanners'] === 'unique') { $query['group'][] = 'banner.hash'; }
- }
-
-
- return true;
- }
- function getMunicipality($latitude, $longitude, $city = null)
- {
- global $dbh;
-
- $q = $dbh->prepare("SELECT id, name from municipality where north > ? and south < ? and west < ? and east > ?");
- $q->execute(array($latitude, $latitude, $longitude, $longitude));
- $muniHits = $q->fetchAll();
-
- /* One hit? Return that one (no need to check the bbox) */
- if (count($muniHits) == 1)
- {
- return $muniHits[0]['id'];
- }
-
- /* No hits whatsoever? */
- if (count($muniHits) == 0)
- {
- $muniHit = null;
- if ($city != null)
- {
- /* Do we have a muni with the same name as this city? Use that */
- $qc = $dbh->prepare("SELECT id, name FROM municipality WHERE name = ?");
- $qc->execute(array($city));
- $muniRow = $qc->fetch();
-
- if ($muniRow['id'])
- {
- $muniHit = $muniRow['id'];
- }
- else
- {
- error_log("Fail ($city -- " . $muniRow['name'] . ")");
- exit;
- }
- }
-
- return $muniHit;
- }
-
- /* More than one hit? Check for name matching */
- foreach ($muniHits as $row)
- {
- if ($row['name'] == $city) { return $row['id']; }
- }
-
- /* More than one hit and no name match? Check bboxes */
- foreach ($muniHits as $row)
- {
- $isInPoly = inMunicipalityPolygon($row['id'], $latitude, $longitude);
- if ($isInPoly) { return $row['id']; }
- }
-
- return false;
- }
- function inMunicipalityPolygon($polyId, $latitude, $longitude)
- {
- global $dbh;
- $q = $dbh->prepare("SELECT lat, lng from municipality_borders WHERE id = ? ORDER BY point");
- $q->execute(array($polyId));
-
- $points = array();
-
- foreach ($q->fetchAll() as $row)
- {
- $points[] = $row['lat'] . " " . $row['lng'];
- }
-
- $location = new pointLocation();
- $isIn = $location->pointInPolygon($latitude . " " . $longitude, $points);
-
- if ($isIn == 'outside') { return false; }
- return true;
- }
- function resizeBanner($id, $width, $outputExtn, $directOutput = false)
- {
- global $site, $dbh;
- $outputExtn = strtolower($outputExtn);
-
- $q = $dbh->prepare("SELECT id, path FROM banner WHERE id = ?");
- $q->execute(array( $id ));
-
- $row = $q->fetch();
- $bannerPath = $row['path'];
- $id = $row['id']; /* Important - otherwise we're open to all sorts of SQL crapiness */
- if (! $id) { return false; }
- $eman = strrev($bannerPath);
- $lastpos = strlen($eman) - strpos($eman, '.');
- if ($lastpos == 0) { break; }
- $bannerExt = strtolower(substr($bannerPath, $lastpos ));
-
- $originalPath = $site['basePath'] . $bannerPath;
- list($origWidth, $origHeight) = getimagesize($originalPath);
-
- if ($width > $origWidth) { return false; }
- $ratio = $width / $origWidth;
- $height = round($origHeight * $ratio);
-
- switch ($bannerExt)
- {
- case 'png':
- $source = imagecreatefrompng($originalPath);
- break;
- case 'jpg':
- $source = imagecreatefromjpeg($originalPath);
- break;
- case 'gif':
- $source = imagecreatefromgif($originalPath);
- break;
- default:
- return false;
- }
-
- $targetPath = $site['basePath'] . 'banners/' . $id . '_' . $width . '.' . $outputExtn;
- $targetImage = imagecreatetruecolor($width, $height);
- imagecopyresampled($targetImage, $source, 0, 0, 0, 0, $width, $height, $origWidth, $origHeight);
-
- switch ($outputExtn)
- {
- case 'jpg':
- imagejpeg($targetImage, $targetPath);
- if ($directOutput) {
- header('Content-Type: image/jpeg');
- imagejpeg($targetImage, null);
- }
- break;
- case 'png':
- imagepng($targetImage, $targetPath);
- if ($directOutput) {
- header('Content-Type: image/png');
- imagepng($targetImage, null);
- }
- break;
- case 'gif':
- imagegif($targetImage, $targetPath);
- if ($directOutput) {
- header('Content-Type: image/gif');
- imagegif($targetImage, null);
- }
- break;
- default:
- return false;
- }
-
- chmod($targetPath, 0755);
- return true;
- }
- function restrict($column, $value, &$query)
- {
- global $dbh;
- if ($value != '')
- {
- if (strpos($value, ','))
- {
- $items = explode(',', $value);
- $safe_items = array();
- foreach ($items as $item) { $safe_items[] = $dbh->quote(urldecode($item)); }
-
- $query['where'][] = "$column IN (" . implode(',', $safe_items) . ')';
- }
- else
- {
- $query['where'][] = "$column = ?";
- $query['args'][] = urldecode($value);
- }
- }
- }
- function authenticateApiUser($user, $secret)
- {
- global $dbh;
-
- $query = array();
- $query['columns'] = array('id');
- $query['tables'] = array('api_user');
- $query['where'] = array('secret = sha1(concat(salt, ' . $dbh->quote($secret) . '))');
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute();
- $row = $q->fetch();
-
- if ($row['id'] == '') { return false; }
- $userid = $row['id'];
-
- $q = $dbh->prepare("UPDATE api_user set accessed = NOW() where id = ?");
- $q->execute( array($userid) );
- $query = array();
- $query['columns'] = array('api_permission');
- $query['tables'] = array('api_permission');
- $query['where'] = array('api_user_id = ?');
-
- $q = $dbh->prepare( makeQuerySelect($query) );
- $q->execute( array($userid ));
-
- $permlist = array();
-
- foreach ($q->fetchAll() as $perm)
- {
- $permlist[] = $perm['api_permission'];
- }
-
- return $permlist;
- }
- function getStatTest()
- {
- global $dbh;
- $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");
- $q->execute();
-
- $events = array();
- foreach ($q->fetchAll() as $row)
- {
- $events[ $row['municipality'] ] = $row['hits'];
- }
-
- return array($events);
- }
- function pathResizeBanner(&$node, $width) {
- /* Sätt bredden på bilden */
- if(array_key_exists('path', $node) && $width !== '') { /* Le hack. */
- $path = array_pop(explode('/', $node['path'])); // Plocka bort allt utom filnamnet.
- $parts = explode('.', $path);
- $parts[count($parts)-2] .= "_$width"; /* Vi vill lägga till bredden på näst sista elementet, för det sista är filsuffixet. */
- $node['path'] = 'banners/' . implode('.', $parts);
- }
- }
- ?>