Location: PHPKode > scripts > phpFlickrSynch > phpflickrsynch/_classes/class_TableFlickrPortfolio.php
<?php
	/**
	  * Access layer to the "flickr_portfolio" table, which contains the description of each photo album
	  */
	class TableFlickrPortfolio {
	
		/**
		  * Check if the Flickr photo album has already been registered in the "flickr_portfolio" table
		  * @param $flickrid FlickrID of the photo album to check
		  * @return the internal ID of the existing photo_album ($por_id) ; 0 if not existing album found
		  */
		public function albumAlreadyExists($flickrid) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT por_id FROM flickr_portfolio WHERE flickr_id = ?');
			$st->bindParam(1, $flickrid, PDO::PARAM_STR, 50);	
			$res = Helpers::fetchOneRow($st);	
			return ($res != false && isset($res['por_id'])) ? $res['por_id'] : 0;
		}

		/**
		  * Check if a photo album with ID $por_id already exists in the "flickr_portfolio" table
		  * @param $por_id Portfolio ID
		  * @return The flickr_id of the portfolio ; null if it doesn't exist
		  */
		public function checkAlbum($por_id) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT flickr_id FROM flickr_portfolio WHERE por_id = ?');
			$st->bindParam(1, $por_id, PDO::PARAM_INT);	
			$res = Helpers::fetchOneRow($st);
			return ($res != false && isset($res['flickr_id'])) ? $res['flickr_id'] : 0;
		}

		/**
		  * Get all the details of a photo album
		  * @param $por_id Photo album ID
		  * @return An array of all the properties of the album
		  */
		public function getDetails($por_id) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT por_title title, por_desc description, por_cent_lat cent_lat, por_cent_lon cent_lon, por_sw_lat sw_lat, por_sw_lon sw_lon, por_ne_lat ne_lat, por_ne_lon ne_lon, grp_id groupid FROM flickr_portfolio WHERE por_id = ?');
			$st->bindParam(1, $por_id, PDO::PARAM_INT);	
			return Helpers::fetchOneRow($st);
		}

		/**
		  * Insert a new record in the "flickr_portfolio" (mapping between Flickr photo album and own album number)
		  * @param $flickrid FlickrID of the photo album to register
		  * @return the internal ID of the newly created photo_album ($por_id) ; 0 if the insertion failed		  
		  */
		function insertAlbum($flickrid, $title, $desc) {
			global $FLDB;
			$st = $FLDB->prepare('INSERT INTO flickr_portfolio (flickr_id, por_creationdate, por_lastupdate, por_title, por_desc) VALUES (?, NOW(), NOW(), ?, ?)');
			$st->bindParam(1, $flickrid, PDO::PARAM_STR);	
			$st->bindParam(2, $title, PDO::PARAM_STR, 100);
			$st->bindParam(3, $desc, PDO::PARAM_STR, 4000); // TODO Column TEXT - anything special ?
			return Helpers::executeInsertStatement($FLDB, $st);	
		}

		/**
		  * Update the core data of a photo album
		  * @param $flickrid The FlickrID of the photo album
		  * @param $title The title of the photo album
		  * @param $desc The description of the photo album
		  */
		public function updateAlbum($por_id, $title, $desc) {
			global $FLDB;
			$st = $FLDB->prepare('UPDATE flickr_portfolio SET por_lastupdate=NOW(), por_title=?, por_desc=? WHERE por_id=?');
			$st->bindParam(1, $title, PDO::PARAM_STR, 100);
			$st->bindParam(2, $desc, PDO::PARAM_STR, 4000); // TODO Column TEXT - anything special ?
			$st->bindParam(3, $por_id, PDO::PARAM_INT);
			return Helpers::executeStatement($st);
		}

		public function updateGroup($flickrid, $grp_id) {
			global $FLDB;
			$st1 = $FLDB->prepare('UPDATE flickr_portfolio SET grp_id = ? WHERE flickr_id = ?');
			$st1->bindParam(1, $grp_id);	
			$st1->bindParam(2, $flickrid);
			return Helpers::executeStatement($st1);
		}

		/**
		  * Compute geo coordinates for the photo album based on the coordintates of all the photos in this album
		  * @param $por_id Photo album ID
		  */
		public function updateGeoData($por_id) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT por_id, AVG(flickr_lat) lat, AVG(flickr_lon) lon, MAX(flickr_lat) max_lat, MAX(flickr_lon) max_lon, MIN(flickr_lat) min_lat, MIN(flickr_lon) min_lon FROM flickr_photo WHERE por_id=? GROUP BY por_id');
			$st->bindParam(1, $por_id, PDO::PARAM_INT);
			$res = Helpers::fetchOneRow($st);
				
			if ($res) {
				$lat = $res['lat'];
				$lon = $res['lon'];
				$min_lat = $res['min_lat'];
				$min_lon = $res['min_lon'];
				$max_lat = $res['max_lat'];
				$max_lon = $res['max_lon'];								
								
				$st1 = $FLDB->prepare('UPDATE flickr_portfolio SET por_lastupdate=NOW(), por_cent_lat = ?, por_cent_lon = ?, por_sw_lat = ?, por_sw_lon = ?, por_ne_lat = ?, por_ne_lon = ? WHERE por_id=?');
				$st1->bindParam(1, $lat);	
				$st1->bindParam(2, $lon);
				$st1->bindParam(3, $min_lat);	
				$st1->bindParam(4, $min_lon);
				$st1->bindParam(5, $max_lat);	
				$st1->bindParam(6, $max_lon);									
				$st1->bindParam(7, $por_id, PDO::PARAM_INT);	
				Helpers::executeStatement($st1);
			}
		}	

		/**
		  * Retrieve the list of all registered Flickr album in "flickr_portfolio" table
		  * @return a map of (flickr_id => por_id) associations
		  */
		public function getRegisteredAlbums() {
			global $FLDB;
			$st = $FLDB->prepare('SELECT flickr_id, por_id, grp_id FROM flickr_portfolio');
			$tab = Helpers::fetchAll($st);
			$res = array();
			if ($tab) {
				foreach ($tab as $key => $val) {
					$flickr = $val['flickr_id'];
					$res[$flickr] = array(
						'porId' => $val['por_id'],
						'grpId' => $val['grp_id']
					);
				}	
			}
			return $res;		
		}		
	
		/**
		  * Delete a photo album and all photos belonging to it
		  * @param $por_id Portfolio ID
		  */
		function deleteAlbum($por_id) {
			global $FLDB;
			$FLDB->query('DELETE FROM flickr_photo WHERE por_id = ' . $por_id);
			$FLDB->query('DELETE FROM flickr_portfolio WHERE por_id = ' . $por_id);
		}






		/**
		  * Retrieve the list of photo albums with geo data along with useful data to be shown on a map
		  * @param $groupID Group ID - only returns albums tagged with this group ID ; default: 0 means all		  
		  * @return An array with key = Photo Album ID ; value = an array with "lat" (latitude), "lon" (longitude), "title" (photo album title), "datetime" (creation date)
		  */
		public function getAllPhotoAlbumsWithGeoData($groupId = 0) {
			global $FLDB;
			$sql = 'SELECT por_id, por_cent_lat, por_cent_lon, por_title, por_creationdate FROM flickr_portfolio WHERE por_cent_lat IS NOT NULL';
			if ($groupId > 0) {
				$sql .= ' AND grp_id = ?';
			}
			
			$st = $FLDB->prepare($sql);
			if ($groupId > 0) {
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			}
			
			$tab = Helpers::fetchAll($st);
			$res = array();
			if ($tab) {
				foreach ($tab as $key => $val) {
					$por_id = $val['por_id'];
					$res[$por_id] = array(
						'lat' => $val['por_cent_lat'], 
						'lon' => $val['por_cent_lon'], 
						'title' => $val['por_title'],
						'datetime' => $val['por_creationdate']
					);
				}	
			}
			return $res;	
		}

		/**
		  * Returns the last photo albums registered
		  * @param $nb Number of photo albums to return ; 5 by default
		  * @param $nb Number of photo per photo album to return ; 3 by default
		  * @param $size Size of photos to be returned ; default is 'Thumbnail'
		  * @param $groupID Group ID - only returns albums tagged with this group ID ; default: 0 means all		  
		  * @return An associative array of photo albums
		  */		
		public function getLastAlbums($nbAlbums = 5, $nbPhotosPerAlbum = 3, $size = 'Thumbnail', $groupId = 0) {
			global $FLDB;
			
			$sql = 'SELECT a.por_id, por_title, por_desc, por_creationdate, COUNT(*) nb FROM flickr_portfolio a, flickr_photo b WHERE a.por_id = b.por_id ';
			if ($groupId > 0) {
				$sql .= ' AND grp_id = ?';
			}
			$sql .= 'GROUP BY a.por_id, por_title, por_creationdate ORDER BY por_id DESC LIMIT ' . $nbAlbums;
			
			$st = $FLDB->prepare($sql);
			if ($groupId > 0) {
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			}			
			return $this->_buildPhotoAlbumResult($st, $nbPhotosPerAlbum, $size);
		}

		/**
		  * Returns the list of photo albums for a given date
		  * @param $year Year
		  * @param $month Month
		  * @param $day Day
		  * @param $nbPhotosPerAlbum Number of photo per photo album to return ; 3 by default
		  * @param $size Size of photos to be returned ; default is 'Thumbnail'
		  * @param $groupID Group ID - only returns albums tagged with this group ID ; default: 0 means all		  		  
		  * @return An associative array "porID => details"		  
		  */
		public function getAlbums($year = NULL, $month = NULL, $day = NULL, $nbPhotosPerAlbum = 3, $size = 'Thumbnail', $groupId = 0) {
			global $FLDB;
			$sql = 'SELECT a.por_id, por_title, por_desc, por_creationdate, COUNT(*) nb FROM flickr_portfolio a, flickr_photo b ';
			$sql .= "WHERE a.por_id = b.por_id "; 
			
			if ($year != NULL) {
				$sql .= " AND YEAR(por_creationdate) = ? ";
				if ($month != NULL) {
					$sql .= " AND MONTH(por_creationdate) = ? ";
					if ($day != NULL) {
						$sql .= "AND DAY(por_creationdate) = ? ";
					}
				}
			}
			
			if ($groupId > 0) {
				$sql .= ' AND grp_id = ? ';
			}			
			$sql .= "GROUP BY a.por_id, por_title, por_creationdate";

			$st = $FLDB->prepare($sql);
			
			$idx = 1;
			if ($year != NULL) {
				$st->bindParam($idx++, $year, PDO::PARAM_INT);
				if ($month != NULL) {
					$st->bindParam($idx++, $month, PDO::PARAM_INT);
					if ($day != NULL) {
						$st->bindParam($idx++, $day, PDO::PARAM_INT);
					}
				}
			}

			if ($groupId > 0) {
				$st->bindParam($idx, $groupId, PDO::PARAM_INT);
			}
			return $this->_buildPhotoAlbumResult($st, $nbPhotosPerAlbum, $size);
		}




		
		public function search($searchterm, $nbPhotosPerAlbum = 3, $size = 'Thumbnail', $groupId = 0) {
			global $FLDB;
			$searchterm = str_replace("'", ' ', $searchterm);
			
			$sql = 'SELECT a.por_id, por_title, por_desc, por_creationdate, COUNT(*) nb FROM flickr_portfolio a, flickr_photo b ';
			$sql .= 'WHERE a.por_id = b.por_id AND MATCH (por_title, por_desc) AGAINST (?  IN BOOLEAN MODE) ';			
			if ($groupId > 0) { $sql .= 'AND grp_id = ? ';}
			$sql .= 'GROUP BY a.por_id, por_title, por_creationdate';
			
			$st = $FLDB->prepare($sql);
			$st->bindParam(1, Helpers::getSearchString($searchterm), PDO::PARAM_STR);
			if ($groupId > 0) {
				$st->bindParam(2, $groupId, PDO::PARAM_INT);
			}
			return $this->_buildPhotoAlbumResult($st, $nbPhotosPerAlbum, $size);
		
		}

		
		private function _buildPhotoAlbumResult($st, $nbPhotosPerAlbum = 3, $size = 'Thumbnail') {
			$tab = Helpers::fetchAll($st);
			$res = array();
			if ($tab) {
				$nb = 0;
				$DB = new TableFlickrPhoto();
				foreach ($tab as $key => $val) {	
					$imgs = array();
					if ($nbPhotosPerAlbum > 0) {
						$imgs = $DB->getPhotos($val['por_id'], $nbPhotosPerAlbum, $size);	
					}							
					$res[$nb++] = array(
						'id' => $val['por_id'],
						'title' => $val['por_title'],
						'desc' => $val['por_desc'],
						'datetime' => $val['por_creationdate'],
						'nb' => $val['nb'],
						'photos' => $imgs
					);	
				}							
			}
			return $res;
		}
		
		/**
		  * Returns the total number of photo albums
		  * @param $groupID Group ID - only returns albums tagged with this group ID ; default: 0 means all		  		  
		  */
		public function getNumberOfAlbums($groupId = 0) {
			global $FLDB;
			$sql = 'SELECT COUNT(*) nb FROM flickr_portfolio';
			if ($groupId > 0) {
				$sql .= ' WHERE grp_id = ? ';
			}
			
			$st = $FLDB->prepare($sql);
			if ($groupId > 0) {
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			}				
			
			$res = Helpers::fetchOneRow($st);		
			return ($res != false && isset($res['nb'])) ? $res['nb'] : 0;
		}				
	}

?>
Return current item: phpFlickrSynch