PageRenderTime 7ms CodeModel.GetById 3ms app.highlight 6ms RepoModel.GetById 1ms 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

Large files files are truncated, but you can click here to view the full file

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

Large files files are truncated, but you can click here to view the full file