<?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;
}
}
?>