2 /***********************************************************************/
4 /***********************************************************************/
5 /* Copyright (c) 2002-2010 */
6 /* Inclusive Design Institute */
9 /* This program is free software. You can redistribute it and/or */
10 /* modify it under the terms of the GNU General Public License */
11 /* as published by the Free Software Foundation. */
12 /***********************************************************************/
17 * Note: Using intval for photo id, if the system is large enough, int might run out of bound.
23 function PhotoAlbum($id=0){
24 $this->id = intval($id);
29 * @param string filename
30 * @param string description of the photo
31 * @param int author of this photo
32 * @return the photo id that's in the database.
34 function addPhoto($name, $comment, $member_id){
35 global $db, $addslashes;
36 $name = $addslashes($name);
37 $comment = $addslashes($comment);
38 $member_id = intval($member_id);
39 $album_id = $this->id;
42 $sql = 'SELECT MAX(ordering) AS ordering FROM '.TABLE_PREFIX."pa_photos WHERE album_id=$album_id";
43 $result = mysql_query($sql, $db);
45 $row = mysql_fetch_assoc($result);
46 $ordering = intval($row['ordering']) + 1;
51 $sql = "INSERT INTO ".TABLE_PREFIX."pa_photos (name, description, member_id, album_id, ordering, created_date, last_updated) VALUES ('$name', '$comment', $member_id, $album_id, $ordering, NOW(), NOW())";
52 $result = mysql_query($sql, $db);
54 //update album last_updated
56 $photo_id = mysql_insert_id();
57 $this->updateAlbumTimestamp();
64 function getPhotoInfo($id){
65 global $db, $addslashes;
69 $sql = "SELECT * FROM ".TABLE_PREFIX."pa_photos WHERE id=$id";
70 $result = mysql_query($sql, $db);
72 $row = mysql_fetch_assoc($result);
80 * Edit the info of the photo.
82 * @param string the caption of the photo
83 * @param string alternative text of the image.
85 function editPhoto($id, $description, $alt_text){
86 global $db, $addslashes;
88 $description = $addslashes($description);
89 $alt_text = $addslashes($alt_text);
91 $sql = "UPDATE ".TABLE_PREFIX."pa_photos SET description='$description', alt_text='$alt_text', last_updated=NOW() WHERE id=$id";
92 $result = mysql_query($sql);
94 //update album last_updated
96 $this->updateAlbumTimestamp();
103 * Edit the order of the photo.
104 * @param int photo id
105 * @param int the ordering of this photo within this album
107 function editPhotoOrder($id, $ordering){
108 global $db, $addslashes;
110 $ordering = intval($ordering);
112 $sql = "UPDATE ".TABLE_PREFIX."pa_photos SET ordering=$ordering, last_updated=NOW() WHERE id=$id";
113 $result = mysql_query($sql);
115 //update album last_updated
117 $this->updateAlbumTimestamp();
125 * @param int photo id
127 function deletePhoto($id){
131 $sql = 'SELECT a.id AS aid, p.name AS name, p.ordering AS ordering, a.created_date AS album_date, p.created_date AS photo_date FROM '.TABLE_PREFIX.'pa_photos p, '.TABLE_PREFIX."pa_albums a WHERE a.id=p.album_id AND p.id=$id";
132 $result = mysql_query($sql, $db);
134 $row = mysql_fetch_assoc($result);
136 //if the aid don't match each other, there must be something wrong.
137 if($row['aid']!=$this->id){
140 $albumpath = AT_PA_CONTENT_DIR.getAlbumFilePath($row['aid'], $row['album_date']);
141 $filepath = $albumpath.DIRECTORY_SEPARATOR.getPhotoFilePath($id, $row['name'], $row['photo_date']); //orig
142 $filepath_tn = $albumpath.'_tn'.DIRECTORY_SEPARATOR.getPhotoFilePath($id, $row['name'], $row['photo_date']); //thumbnail
143 if (is_file($filepath) && is_file($filepath_tn)){
145 unlink($filepath_tn);
148 //delete photo comments
149 $sql = 'DELETE FROM '.TABLE_PREFIX."pa_photo_comments WHERE photo_id=$id";
150 mysql_query($sql, $db);
153 $sql = 'UPDATE '.TABLE_PREFIX.'pa_photos SET `ordering`=`ordering`-1 WHERE album_id='.$row['aid'].' AND `ordering` > '.$row['ordering'];
154 mysql_query($sql, $db);
156 //delete the photo from db
157 $sql = "DELETE FROM ".TABLE_PREFIX."pa_photos WHERE id=$id";
158 mysql_query($sql, $db);
160 //update album last_updated
162 $this->updateAlbumTimestamp();
170 * @param string name of the album
171 * @param string location of where this album took place
172 * @param string descriptive text of this album
173 * @param int check include/constants.inc.php
174 * @param int permission, 0 for private, 1 for shared
175 * @param int album author
176 * @param int OPTIONAL, Photo cover for this album
177 * @return int album_id, FALSE if failed.
179 function createAlbum($name, $location, $description, $type, $permission, $member_id, $photo_id=0){
180 global $addslashes, $db;
183 $name = $addslashes($name);
184 $locatoin = $addslashes($location);
185 $description = $addslashes($description);
186 $type = intval($type);
187 $type = ($type<=0)?AT_PA_TYPE_MY_ALBUM:$type;
188 $permission = intval($permission);
189 $member_id = intval($member_id);
190 $photo_id = intval($photo_id);
192 $sql = "INSERT INTO ".TABLE_PREFIX."pa_albums (name, location, description, type_id, member_id, permission, photo_id, created_date, last_updated) VALUES ('$name', '$location', '$description', $type, $member_id, $permission, $photo_id, NOW(), NOW())";
193 $result = mysql_query($sql, $db);
194 $aid = mysql_insert_id();
196 //if course album, add a record.
197 if ($type==AT_PA_TYPE_COURSE_ALBUM){
198 $sql = "INSERT INTO ".TABLE_PREFIX."pa_course_album (course_id, album_id) VALUES ($_SESSION[course_id], $aid)";
199 $result = mysql_query($sql, $db);
208 * Updating album cover.
209 * @param int photo id (the album cover)
210 * @precondition user has the ability to edit the album.
212 function editAlbumCover($pid){
219 //pid and aid cannot be empty
220 if ($pid<=0 || $aid<=0){
224 $sql = "UPDATE ".TABLE_PREFIX."pa_albums SET photo_id=$pid, last_updated=NOW() WHERE id=$aid";
225 $result = mysql_query($sql, $db);
231 * @param string name of the album
232 * @param string location of where this album took place
233 * @param string descriptive text of this album
234 * @param int check include/constants.inc.php
235 * @param int permission, 0 for private, 1 for shared
237 function editAlbum($name, $location, $description, $type, $permission){
238 global $db, $addslashes;
240 $name = $addslashes($name);
241 $location = $addslashes($location);
242 $description = $addslashes($description);
243 $type = ($type==AT_PA_TYPE_COURSE_ALBUM)?AT_PA_TYPE_COURSE_ALBUM:AT_PA_TYPE_MY_ALBUM;
244 $permission = ($permission==AT_PA_SHARED_ALBUM)?AT_PA_SHARED_ALBUM:AT_PA_PRIVATE_ALBUM;
245 $info = $this->getAlbuminfo();
247 //if type has been changed, run the query to update the course_album table
248 if ($info['type_id'] != $type){
249 //if course album, add a record.
250 if ($type==AT_PA_TYPE_COURSE_ALBUM){
251 $sql = "INSERT INTO ".TABLE_PREFIX."pa_course_album (course_id, album_id) VALUES ($_SESSION[course_id], $id)";
252 $result = mysql_query($sql, $db);
254 $sql = 'DELETE FROM '.TABLE_PREFIX."pa_course_album WHERE course_id=$_SESSION[course_id] AND album_id=$id";
255 $result = mysql_query($sql, $db);
259 $sql = 'UPDATE '.TABLE_PREFIX."pa_albums SET name='$name', location='$location', description='$description', type_id=$type, permission=$permission, last_updated=NOW() WHERE id=$id";
260 $result = mysql_query($sql, $db);
265 * Delete an album and all associations
267 function deleteAlbum(){
268 //TODO Error checking on each step, if anyone fails, should report it to user
273 $sql = 'SELECT created_date FROM '.TABLE_PREFIX."pa_albums WHERE id=$id";
274 $result = mysql_query($sql, $db);
276 $row = mysql_fetch_assoc($result);
278 $filepath = AT_PA_CONTENT_DIR . getAlbumFilePath($id, $row['created_date']); //orig
279 $filepath_tn = $filepath.'_tn'; //thumbnails
281 if (is_dir($filepath) && is_dir($filepath_tn)){
283 clr_dir($filepath_tn);
286 //delete all photo comments
287 $sql = 'DELETE c.* FROM '.TABLE_PREFIX.'pa_photo_comments c LEFT JOIN '.TABLE_PREFIX."pa_photos p ON c.photo_id=p.id WHERE p.album_id=$id";
288 mysql_query($sql, $db);
290 //delete all photos within this album
291 $sql = "DELETE FROM ".TABLE_PREFIX."pa_photos WHERE album_id=$id";
292 mysql_query($sql, $db);
294 //delete all album comments
295 $sql = 'DELETE FROM '.TABLE_PREFIX."pa_album_comments WHERE album_id=$id";
296 mysql_query($sql, $db);
299 $sql = "DELETE FROM ".TABLE_PREFIX."pa_albums WHERE id=$id";
300 mysql_query($sql, $db);
304 * Update album last_updated column to the current timestamp.
308 private function updateAlbumTimestamp(){
311 //quit if album id is not set.
314 $sql = 'UPDATE '.TABLE_PREFIX.'pa_albums SET last_updated=NOW() WHERE id='.$this->id;
315 mysql_query($sql, $db);
321 function getAlbumPhotos($offset=-1){
324 $offset = intval($offset);
327 $sql = "SELECT photos.* FROM " .TABLE_PREFIX."pa_photos photos LEFT JOIN ".TABLE_PREFIX."pa_albums albums ON albums.id=photos.album_id WHERE albums.id=$id ORDER BY ordering";
329 $sql .= " LIMIT $offset ,".AT_PA_PHOTOS_PER_PAGE;
332 $result = mysql_query($sql, $db);
334 while ($row = mysql_fetch_assoc($result)){
342 * Get album information
343 * @param int album id
344 * @return the album row, false on error
346 function getAlbumInfo(){
349 $sql = "SELECT * FROM ".TABLE_PREFIX."pa_albums WHERE id=$id";
350 $result = mysql_query($sql, $db);
352 $row = mysql_fetch_assoc($result);
359 * Get a list of album by the given type (profile/my albums/class albums)
362 function getAlbums($member_id, $type_id=-1, $offset=-1){
364 $type_id = intval($type_id);
365 $member_id = intval($member_id);
366 $offset = intval($offset);
369 $sql = "SELECT * FROM ".TABLE_PREFIX."pa_albums WHERE member_id=$member_id";
370 if($type_id==AT_PA_TYPE_COURSE_ALBUM){
371 //if inside the course scope, get this course's albums only
372 //if in my_start_page, get all enrolled course
373 $course_sql = ($_SESSION['course_id']==0)?'':'AND ca.course_id='.$_SESSION['course_id'];
375 $sql = 'SELECT albums.* FROM '.TABLE_PREFIX.'pa_albums albums,
376 (SELECT ca.* FROM '.TABLE_PREFIX.'course_enrollment enrollments
377 RIGHT JOIN '.TABLE_PREFIX."pa_course_album ca
378 ON enrollments.course_id=ca.course_id
379 WHERE member_id=$member_id $course_id
381 WHERE albums.id=allowed_albums.album_id";
383 elseif($type_id > 0){
384 $sql .= " AND type_id=$type_id";
387 $sql .= " LIMIT $offset ," . AT_PA_ALBUMS_PER_PAGE;
389 $result = mysql_query($sql, $db);
391 while($row = mysql_fetch_assoc($result)){
392 $rows[$row['id']] = $row;
399 * Get all albums, used by Admin only.
401 function getAllAlbums($offset=-1){
403 $offset = intval($offset);
405 $sql = 'SELECT * FROM '.TABLE_PREFIX.'pa_albums';
408 $sql .= " LIMIT $offset ," . AT_PA_ADMIN_ALBUMS_PER_PAGE;
411 $result = mysql_query($sql, $db);
413 while($row = mysql_fetch_assoc($result)){
414 $rows[$row['id']] = $row;
422 * Get all private/shared albums (ignore album type)
423 * @param boolean True to get all shared album; false to get all private album, default: true
424 * @param int Resultset's limit
426 function getSharedAlbums($isShared=true, $offset=-1){
428 $offset = intval($offset);
429 $permission = ($isShared)? 1 : 0;
431 $sql = 'SELECT * FROM '.TABLE_PREFIX."pa_albums WHERE permission=$permission";
433 $sql .= " LIMIT $offset ," . AT_PA_ALBUMS_PER_PAGE;
435 $result = mysql_query($sql, $db);
437 while ($row = mysql_fetch_assoc($result)){
438 $rows[$row['id']] = $row;
445 * Get album type names
446 * @param int album types, check constants.inc.php
447 * @return the string representation of this album type
449 function getAlbumTypeName($type){
451 case AT_PA_TYPE_MY_ALBUM:
452 return _AT('pa_my_albums');
453 case AT_PA_TYPE_COURSE_ALBUM:
454 return _AT('pa_course_albums');
455 case AT_PA_TYPE_PERSONAL:
456 return _AT('pa_profile_album');
463 * Get the owner of this album
464 * @param int album_id
465 * @param int member_id
466 * @return True if the given user has the privilege to delete/edit.
468 function checkAlbumPriv($member_id){
470 $album_id = $this->id;
471 $member_id = intval($member_id);
474 if (admin_authenticate(AT_ADMIN_PRIV_PHOTO_ALBUM, true)){
478 $sql = "SELECT member_id FROM ".TABLE_PREFIX."pa_albums WHERE id=$album_id";
479 $result = mysql_query($sql, $db);
481 $row = mysql_fetch_assoc($result);
482 return ($row['member_id']==$member_id);
488 * Get the owner of this photo
489 * @param int photo_id
490 * @param int member_id
491 * @return True if the given user has the privilege to delete/edit.
493 function checkPhotoPriv($photo_id, $member_id){
495 $photo_id = intval($photo_id);
496 $member_id = intval($member_id);
498 $sql = "SELECT member_id FROM ".TABLE_PREFIX."pa_photos WHERE id=$photo_id";
499 $result = mysql_query($sql, $db);
501 $row = mysql_fetch_assoc($result);
502 return ($row['member_id']==$member_id);
509 * Get the owner of the comment
511 function checkCommentPriv($comment_id, $member_id, $isPhoto){
513 $comment_id = intval($comment_id);
514 $member_id = intval($member_id);
517 $sql = "SELECT member_id FROM ".TABLE_PREFIX."pa_photo_comments WHERE id=$comment_id";
519 $sql = "SELECT member_id FROM ".TABLE_PREFIX."pa_album_comments WHERE id=$comment_id";
521 $result = mysql_query($sql, $db);
523 $row = mysql_fetch_assoc($result);
524 return ($row['member_id']==$member_id);
531 * @param int id (can be photo_id, or album_id)
532 * @param string comment
534 * @param boolean true if it is photo_id, false otherwise
536 function addComment($id, $comment, $member_id, $isPhoto){
537 global $addslashes, $db;
540 $member_id = intval($member_id);
541 $comment = $addslashes($comment);
544 $sql = 'INSERT INTO '.TABLE_PREFIX."pa_album_comments (album_id, comment, member_id, created_date) VALUES ($id, '$comment', $member_id, NOW())";
546 $sql = 'INSERT INTO '.TABLE_PREFIX."pa_photo_comments (photo_id, comment, member_id, created_date) VALUES ($id, '$comment', $member_id, NOW())";
548 $result = mysql_query($sql, $db);
554 * @param int comment id
555 * @param string comment
556 * @param boolean true if it is photo_id, false otherwise
557 * @precondition this->member_id has the privilige to edit comment.
559 function editComment($id, $comment, $isPhoto){
560 global $addslashes, $db;
563 $comment = $addslashes($comment);
564 if($id<1 || $comment==''){
569 $sql = 'UPDATE '.TABLE_PREFIX."pa_album_comments SET comment='$comment' WHERE id=$id";
571 $sql = 'UPDATE '.TABLE_PREFIX."pa_photo_comments SET comment='$comment' WHERE id=$id";
573 $result = mysql_query($sql, $db);
580 * @param int id (can be photo_id, or album_id)
581 * @param boolean true of it is photo_id, false otherwise.
583 function getComments($id, $isPhoto){
589 $sql = 'SELECT * FROM '.TABLE_PREFIX."pa_photo_comments WHERE photo_id=$id";
591 $sql = 'SELECT * FROM '.TABLE_PREFIX."pa_album_comments WHERE album_id=$id";
593 $sql .= ' ORDER BY created_date';
595 $result = mysql_query($sql, $db);
597 while ($row = mysql_fetch_assoc($result)){
605 * Delete photo comment
607 function deleteComment($id, $isPhoto){
612 $sql = "DELETE FROM ".TABLE_PREFIX."pa_photo_comments WHERE id=$id";
614 $sql = "DELETE FROM ".TABLE_PREFIX."pa_album_comments WHERE id=$id";
616 mysql_query($sql, $db);
620 * Search and return list of albums, and list of photos
621 * Note: Speed and ranks are of priority here.
622 * @param Array The unescaped array of search phrases.
623 * @return [Array, Array] First array is albums, second array are matched photos
625 function search($words){
626 global $db, $addslashes;
629 $visible_photos = array();
630 $visible_albums = array();
633 if (!is_array($words) || empty($words)){
638 foreach($words as $k=>$v){
639 $v = $addslashes(trim($v));
640 $query .= "(description LIKE '%$v%' OR name LIKE '%$v%' OR alt_text LIKE '%$v%') OR "; //for sql
641 $pattern .= $v.'|'; //regex for albums
643 $pattern = str_replace (array('>', '<', '/', '\\'), "", $pattern);
644 $pattern = substr($pattern, 0, -1);
646 //TODO: Optimize SQL, UNION is slow, but I think this is the fastest I can get, prove me wrong.
648 /** Get all visible albums */
649 $sql = 'SELECT albums.* FROM '.TABLE_PREFIX.'pa_albums albums,
650 (SELECT ca.* FROM '.TABLE_PREFIX.'course_enrollment enrollments
651 RIGHT JOIN '.TABLE_PREFIX."pa_course_album ca
652 ON enrollments.course_id=ca.course_id
653 WHERE member_id=$_SESSION[member_id]
655 WHERE albums.id=allowed_albums.album_id
657 SELECT * FROM AT_pa_albums WHERE member_id=$_SESSION[member_id] OR permission=1";
658 $result = mysql_query($sql, $db);
662 while($row = mysql_fetch_assoc($result)){
663 $visible_albums[$row['id']] = $row;
665 $visible_albums_ids = implode(', ', array_keys($visible_albums));
667 /** Get all photos from these albums */
668 $sql = 'SELECT * FROM '.TABLE_PREFIX."pa_photos WHERE album_id IN ($visible_albums_ids)";
669 $query = ' AND ' . substr($query, 0, -3);
670 $sql = $sql . $query . ' LIMIT ' . AT_PA_PHOTO_SEARCH_LIMIT;
671 $result = mysql_query($sql, $db);
675 while($row = mysql_fetch_assoc($result)){
676 $visible_photos[$row['id']] = $row;
681 if (!empty($visible_photos)){
682 $album_photos = array(); //keep track of the # of photos inside an album, should match a 'count(*) group by'
683 foreach($visible_photos as $photo_id=>$photo){
686 if (preg_match("/$pattern/i", $photo['name'])){
687 $visible_photos[$photo_id]['point'] += 1;
690 if (preg_match("/$pattern/i", $photo['alt_text'])){
691 $visible_photos[$photo_id]['point'] += 1;
694 if (preg_match("/$pattern/i", $photo['description'])){
695 $visible_photos[$photo_id]['point'] += 2;
698 //total photo points within an album
700 $album_photos[$photo['album_id']] += 1;
706 foreach($visible_albums as $album_id=>$album){
707 if (preg_match("/$pattern/i", $album['name'])){
708 $visible_albums[$album_id]['point'] += 3;
710 if (preg_match("/$pattern/i", $album['location'])){
711 $visible_albums[$album_id]['point'] += 1;
713 if (preg_match("/$pattern/i", $album['description'])){
714 $visible_albums[$album_id]['point'] += 1;
716 //every photo has a certain value to the album, and is calculated as follow
717 //[# of matched photo in an album] / [total number of matched photos] *4
718 //4 is the total matched photo score (ie. all album's photo score should add up to 4)
719 if (isset($album_photos[$album_id])){
720 $visible_albums[$album_id]['point'] += $album_photos[$album_id]/sizeof($visible_photos) * 4;
722 //If no point in the album, most likely it's irrelevant and not of interest, take it out
723 if (!isset($visible_albums[$album_id]['point'])){
724 unset($visible_albums[$album_id]);
728 /** sort and return */
729 usort($visible_photos, array('PhotoAlbum', 'search_cmp'));
730 usort($visible_albums, array('PhotoAlbum', 'search_cmp'));
731 // debug($visible_photos, 'visible_photos');
732 // debug($visible_albums, 'visible albums');
734 return array($visible_albums, $visible_photos);
738 * Compare functino for usort, used by search (descending)
740 function search_cmp($k1, $k2){
741 if(!isset($k1['point'])){
744 if(!isset($k2['point'])){
748 if ($k1['point'] == $k2['point']) return 0;
749 if ($k1['point'] > $k2['point']) return -1;