2 /*==============================================================
\r
4 ==============================================================
\r
5 Copyright (c) 2006 by Dylan Cheon & Kelvin Wong
\r
6 Institute for Assistive Technology / University of Victoria
\r
7 http://www.canassist.ca/
\r
9 This program is free software. You can redistribute it and/or
\r
10 modify it under the terms of the GNU General Public License
\r
11 as published by the Free Software Foundation.
\r
12 ==============================================================
\r
17 * @desc This file defines all the functions that access the database.
\r
18 * @author Dylan Cheon
\r
19 * @copyright 2006, Institute for Assistive Technology, University of Victoria
\r
20 * @link http://www.canassist.ca/
\r
25 * @desc This function returns all the image rows for the course id
\r
26 * @param int $course_id course id
\r
27 * @return Array array which contains all the image rows (mysql source)
\r
29 function get_all_images($course_id){
\r
31 $table=get_table_name(IMAGE);
\r
32 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id;
\r
33 $result=mysql_query($query, $db);
\r
38 * @desc This function returns the image array which contains the image information
\r
39 * @param int $course_id course_id
\r
40 * @param int $status image status
\r
41 * @param int $start_page start page
\r
42 * @param int $display_limit how many images should be displayed in the page
\r
43 * @param boolean $my_pic if this value is true, it is set for my photo mode. Returns images for one user if enabled
\r
44 * @param String $login login name
\r
45 * @return Array the array which contains the data from database
\r
47 function get_image_array($state, $course_id, $image_status, $start_page, $display_limit, $login=''){
\r
49 $table=get_table_name(IMAGE);
\r
50 $start=($start_page-1)*$display_limit;
\r
51 $query; $i=0; $array;
\r
54 //$query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$image_status." AND login='{$login}' ORDER BY image_id DESC LIMIT ".$start.", ".$display_limit;
\r
55 // changes sorting to order from image_id
\r
56 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$image_status." AND login='{$login}' ORDER BY `order` ASC LIMIT ".$start.", ".$display_limit;
\r
59 //$query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$image_status." ORDER BY image_id DESC LIMIT ".$start.", ".$display_limit;
\r
60 // changes sorting to order from image_id
\r
61 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$image_status." ORDER BY `order` ASC LIMIT ".$start.", ".$display_limit;
\r
65 $result=mysql_query($query, $db);
\r
66 while ($row=mysql_fetch_array($result)){
\r
67 $array[$i]['title']=$row['title'];
\r
68 $array[$i]['description']=$row['description'];
\r
69 $array[$i]['location']=$row['location'];
\r
70 $array[$i]['date']=$row['date'];
\r
71 $array[$i]['view_image_name']=$row['view_image_name'];
\r
72 $array[$i]['course_id']=$row['course_id'];
\r
73 $array[$i]['login']=$row['login'];
\r
74 $array[$i]['image_id']=$row['image_id'];
\r
75 $array[$i]['thumb_image_name']=$row['thumb_image_name'];
\r
76 $array[$i]['alt']=$row['alt'];
\r
77 $array[$i]['status']=$row['status'];
\r
78 // gg added image order to array
\r
79 $array[$i]['order']=$row['order'];
\r
80 $array[$i]['link']=BASE_PATH.'view.php?image_id='.$row['image_id'];
\r
88 * @desc This function returns the comment array which contains the comment information
\r
89 * @param int $course_id course id
\r
90 * @param int $status comment status
\r
91 * @param int $image_id image id
\r
92 * @param boolean $index_admin if this is true, then it returns the comments for admin/instructor view page
\r
93 * @param boolean $admin_request if this is true, then it returns the comments for admin/instructor panel
\r
94 * @param int $display_limit number of comments to be returned
\r
95 * @param int $start start page
\r
96 * @param boolean $my_comment my comment mode is enabled or not. Returns comments for one user if enabled
\r
97 * @return Array the array which contains the comment data from the database
\r
99 function get_comment_array($state, $course_id, $comment_status, $image_id=NOT_SET, $display_limit=NOT_SET, $start=NOT_SET){
\r
101 $table=get_table_name(COMMENT);
\r
102 $start=($start-1)*$display_limit;
\r
103 $query; $i=0; $array;
\r
107 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id." ORDER BY date DESC";
\r
110 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$comment_status." ORDER BY date DESC LIMIT ".$start.", ".$display_limit;
\r
113 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$comment_status." AND login='{$_SESSION['login']}' ORDER BY date DESC LIMIT ".$start.", ".$display_limit;
\r
116 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND status=".$comment_status." AND image_id=".$image_id." ORDER BY date DESC";
\r
120 $result=mysql_query($query, $db);
\r
121 while ($row=mysql_fetch_array($result)){
\r
122 $array[$i]['date']=$row['date'];
\r
123 $array[$i]['course_id']=$row['course_id'];
\r
124 $array[$i]['login']=$row['login'];
\r
125 $array[$i]['image_id']=$row['image_id'];
\r
126 $array[$i]['comment_id']=$row['comment_id'];
\r
127 $array[$i]['comment']=$row['comment'];
\r
128 $array[$i]['status']=$row['status'];
\r
137 * @desc This function stores the given image input data to the database
\r
138 * @param int $course_id course id
\r
139 * @param String $login login name
\r
140 * @param String $title title string
\r
141 * @param String $description image description
\r
142 * @param String $view_image_name view image file name
\r
143 * @param String $location location string
\r
144 * @param String $thumb_image_name thumb image file name
\r
145 * @param String $alt alt string
\r
146 * @param int $status image status
\r
147 * @return boolean returns true if the given input data is stored in the database successfully
\r
149 function store_image_in_database($course_id, $login, $title, $description, $view_image_name, $location, $thumb_image_name, $alt, $status){
\r
151 $table=get_table_name(IMAGE);
\r
153 $query="INSERT INTO ".$table." SET course_id='{$course_id}', title='{$title}', description='{$description}', view_image_name='{$view_image_name}', location='{$location}', date=NOW(), login='{$login}', thumb_image_name='{$thumb_image_name}', alt='{$alt}', status='{$status}'";
\r
154 $result=mysql_query($query, $db);
\r
155 $count=mysql_affected_rows();
\r
156 if (($count==1) && (!mysql_error())){
\r
164 * @desc This function stores the given comment input data to the database
\r
165 * @param int $course_id course id
\r
166 * @param String $login login name
\r
167 * @param String $comment user comment
\r
168 * @param int $image_id image id
\r
169 * @param int $status comment status
\r
170 * @return boolean returns true if the given input data is stored in the database successfully
\r
172 function store_comment_in_database($course_id, $login, $comment, $image_id, $status){
\r
174 $table=get_table_name(COMMENT);
\r
176 $query="INSERT INTO ".$table." SET course_id='{$course_id}', comment='{$comment}', date=NOW(), login='{$login}', status='{$status}', image_id='{$image_id}'";
\r
177 $result=mysql_query($query, $db);
\r
178 $count=mysql_affected_rows();
\r
179 if ($count==1){ //should affect just one row
\r
187 * @desc This function updates the image data in the database
\r
188 * @param int $course_id course id
\r
189 * @param String $title title string
\r
190 * @param String $description description
\r
191 * @param String $view_image_name file name of the full size image
\r
192 * @param String $image_id image id
\r
193 * @param String $thumb_image_name file name of the thumbnail image
\r
194 * @param String $alt alt string
\r
195 * @param int $status image status
\r
196 * @return boolean true if update finished successfully
\r
198 function update_image_in_database($course_id, $title, $description, $view_image_name, $image_id, $thumb_image_name, $alt, $status){
\r
200 $table=get_table_name(IMAGE);
\r
201 $query="UPDATE ".$table." SET title='{$title}', description='{$description}', thumb_image_name='{$thumb_image_name}', view_image_name='{$view_image_name}', date=NOW(), alt='{$alt}', status='{$status}' WHERE course_id='{$course_id}' AND image_id='{$image_id}'";
\r
202 $result=mysql_query($query, $db);
\r
203 $count=mysql_affected_rows();
\r
204 if ($count==1){ //should affect just one row
\r
212 * @desc This function updates the comment data in the database
\r
213 * @param int $course_id course id
\r
214 * @param String $comment comment string
\r
215 * @param int $image_id image id
\r
216 * @param int $comment_id comment id
\r
217 * @param int $status comment status
\r
218 * @return boolean true if update finished successfully
\r
220 function update_comment_in_database($course_id, $comment, $image_id, $comment_id,$status){
\r
222 $table=get_table_name(COMMENT);
\r
223 $query="UPDATE ".$table." SET comment='{$comment}', status='{$status}', date=NOW() WHERE course_id='{$course_id}' AND image_id='{$image_id}' AND comment_id='{$comment_id}'";
\r
224 $result=mysql_query($query, $db);
\r
225 $count=mysql_affected_rows();
\r
226 if ($count==1){ //should affect just one row
\r
235 * @desc This function returns the complete data row from the database
\r
236 * @param int $choose choose to search either IMAGE and COMMENT data
\r
237 * @param int $image_id image id
\r
238 * @param int $course_id course id
\r
239 * @param int $comment_id comment id
\r
240 * @return Array the complete data row array
\r
242 function get_single_data($choose, $image_id, $course_id, $comment_id=NOT_SET){
\r
243 global $db; $query;
\r
244 $table=get_table_name($choose);
\r
247 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
250 $query="SELECT * FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id." AND comment_id=".$comment_id;
\r
253 $result=mysql_query($query, $db);
\r
254 $result=mysql_fetch_array($result);
\r
260 * @desc This function deletes the image. Images and blogs which have the same course id and image id should also be deleted
\r
261 * @param int $image_id image id
\r
262 * @param int $course_id course id
\r
263 * @return boolean returns true if deletion is successful
\r
265 function delete_image($image_id, $course_id){
\r
267 $image_table=get_table_name(IMAGE);
\r
268 $blog_table=get_table_name(COMMENT);
\r
269 $delete_image_query="DELETE FROM ".$image_table." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
270 $image_query="SELECT view_image_name, location, thumb_image_name FROM ".$image_table." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
271 $blog_query="DELETE FROM ".$blog_table." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
273 $image_array=mysql_query($image_query, $db);
\r
274 delete_image_files($image_array); //delete the physical image file
\r
275 mysql_query($blog_query, $db); //deletes comments
\r
276 mysql_query($delete_image_query, $db); //deletes the images from database
\r
277 $count=mysql_affected_rows();
\r
278 if ($count ==1){ //should be one image
\r
288 * @desc This function deletes the given blog comment
\r
289 * @param int $image_id image id
\r
290 * @param int $course_id course id
\r
291 * @param int $comment_id comment id
\r
292 * @return boolean returns true if deletion is completed.
\r
294 function delete_blog($image_id, $course_id, $comment_id){
\r
296 $table=get_table_name(COMMENT);
\r
297 $query="DELETE FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id." AND comment_id=".$comment_id;
\r
298 mysql_query($query, $db);
\r
299 $count=mysql_affected_rows();
\r
300 if ($count==1){ //should be only one comment
\r
310 * @desc This function returns the table name from the database based on a switch variable
\r
311 * @param int $choose choose either IMAGE, COMMENT or CONFIG
\r
312 * @return String table name as a string
\r
314 function get_table_name($choose){
\r
318 $table_name=TABLE_PREFIX.'pa_image';
\r
321 $table_name=TABLE_PREFIX.'pa_comment';
\r
324 $table_name=TABLE_PREFIX.'pa_config';
\r
327 return $table_name;
\r
333 * @desc This function returns the total number of course images
\r
334 * @param int $course_id course id
\r
335 * @param int $status image status
\r
336 * @param boolean $mypic mypic mode enabled or not. Returns number for one user if enabled
\r
337 * @return int the total number of images
\r
339 function get_total_image_number($state, $course_id, $status){
\r
341 $table=get_table_name(IMAGE);
\r
344 $query="SELECT image_id FROM ".$table." WHERE course_id=".$course_id." AND status=".$status." AND login='{$_SESSION['login']}'";
\r
347 $query="SELECT image_id FROM ".$table." WHERE course_id=".$course_id." AND status=".$status;
\r
351 $result=mysql_query($query, $db);
\r
352 $count=mysql_affected_rows();
\r
357 * @desc This function returns the total number of comments
\r
358 * @param int $course_id course id
\r
359 * @param int $status comment status
\r
360 * @param boolean $my_comment mycomment mode is enabled or not. Returns number for one user if enabled
\r
361 * @param int $image_id image id
\r
362 * @return int the total number of comments
\r
364 function get_total_comment_number($state, $course_id, $status, $image_id=NOT_SET){
\r
366 $table=get_table_name(COMMENT);
\r
369 $login=$_SESSION['login'];
\r
370 $query="SELECT comment_id FROM ".$table." WHERE course_id=".$course_id." AND status=".$status." AND login='{$login}'";
\r
373 $query="SELECT comment_id FROM ".$table." WHERE course_id=".$course_id." AND status=".$status;
\r
376 $query="SELECT comment_id FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id." AND status=".$status;
\r
379 $result=mysql_query($query, $db);
\r
380 $count=mysql_affected_rows();
\r
387 * @desc This function returns the last page number to be used for the page table
\r
388 * @param int $display_limit how many should be displayed in the page
\r
389 * @param int $total total number
\r
390 * @return int last page number
\r
392 function get_last_page($display_limit, $total){
\r
393 $total=doubleval($total);
\r
395 if ($total < $display_limit){
\r
396 $last_page=FIRST_PAGE;
\r
398 $last_page=ceil($total/$display_limit);
\r
399 $last_page=intval($last_page);
\r
406 * @desc This function returns the full name of the member. The return string syntax is => (First_name)? First_name.Last_name_initial : Anonymous
\r
407 * @param String $login login name string
\r
408 * @return String the full name of the member
\r
410 function get_member_name($login){
\r
412 $query="SELECT last_name, first_name FROM ".TABLE_PREFIX."members WHERE login='{$login}'";
\r
413 $result=mysql_query($query, $db);
\r
414 $count=mysql_affected_rows();
\r
415 if ($count == 1) { //in this case, the login is instructor or student
\r
416 $result=mysql_fetch_array($result);
\r
417 if (empty($result['first_name'])){
\r
418 $name=_AT('anonymous');
\r
419 } else if (empty($result['last_name'])){
\r
420 $name=$result['first_name'];
\r
422 $temp=substr($result['last_name'], 0, 1);
\r
423 $name=$result['first_name'].'.'.$temp;
\r
426 } else if ($count==0){ //found nobody, check if the user is administrator
\r
427 $query="SELECT login FROM ".TABLE_PREFIX."admins WHERE login='{$login}'";
\r
428 $result=mysql_query($query, $db);
\r
429 $count=mysql_affected_rows();
\r
430 if ($count==1){ //admin is detected
\r
431 return _AT('pa_tag_administrator');
\r
439 * @desc This pagination function returns an array which has the start and end information to be used
\r
440 * @param int $display_limit maximum number of images displayed in the page
\r
441 * @param int $page_limit maximum number of pages displayed in the page
\r
442 * @param int $course_id course id
\r
443 * @param int $current current page
\r
444 * @param int $last_page last_page number
\r
445 * @return Array array contains the start and end information for page table
\r
447 function get_page_array($display_limit, $page_limit, $current, $last_page){
\r
449 if ($last_page <= ($start+$page_limit-1)){ //initialize the end variable if the last page is less than start+display-1
\r
452 $end=$start+$page_limit-1;
\r
456 while ($process==true){
\r
457 if (($current >= $start) && ($current <= $end)){
\r
458 $array['start']=$start;
\r
459 $array['end']=$end;
\r
463 $end=$start+$page_limit-1;
\r
464 if ($end > $last_page){
\r
469 $array['last_page']=$last_page;
\r
470 $array['previous']=$current-1;
\r
471 $array['next']=$current+1;
\r
472 $array['current']=$current;
\r
478 * @desc This function checks if the image exists in the database
\r
479 * @param int $image_id image id
\r
480 * @param int $course_id course id
\r
481 * @return boolean it returns true if the image exists in database. Otherwise, it returns false
\r
483 function image_exist($image_id, $course_id){
\r
485 $table_name=get_table_name(IMAGE);
\r
486 $query="SELECT * FROM ".$table_name." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
487 $result=mysql_query($query, $db);
\r
488 $count=mysql_affected_rows();
\r
489 if ($count == 1){ // there should be only 1 image
\r
498 * @desc This function checks if the comment exists in the database
\r
499 * @param int $comment_id comment id
\r
500 * @param int $course_id course id
\r
501 * @return boolean returns true if the comment exists in database. Otherwise, it returns false
\r
503 function comment_exist($comment_id, $course_id){
\r
505 $table_name=get_table_name(COMMENT);
\r
506 $query="SELECT * FROM ".$table_name." WHERE course_id=".$course_id." AND comment_id=".$comment_id;
\r
507 $result=mysql_query($query, $db);
\r
508 $count=mysql_affected_rows();
\r
509 if ($count == 1){ //there should be only 1 comment
\r
518 * @desc This function returns an array which has a list of the courses
\r
519 * @return Array array which contains title and course_id
\r
521 function get_course_list(){
\r
523 $table=TABLE_PREFIX.'courses';
\r
524 $query="SELECT course_id, title FROM ".$table." ORDER BY created_date";
\r
525 $result=mysql_query($query, $db);
\r
527 while($row=mysql_fetch_array($result)){
\r
528 $array[$i]['title']=$row['title'];
\r
529 $array[$i]['id']=$row['course_id'];
\r
537 * @desc This function returns the course title
\r
538 * @param int $course_id course id
\r
539 * @return String course title
\r
541 function get_course_title($course_id){
\r
543 $table=TABLE_PREFIX.'courses';
\r
544 $query="SELECT title FROM ".$table." WHERE course_id=".$course_id;
\r
545 $result=mysql_query($query, $db);
\r
546 $result=mysql_fetch_array($result);
\r
547 return $result['title'];
\r
552 * @desc This function checks if the course exists or not
\r
553 * @param int $course_id course id
\r
554 * @return Boolean true if exist
\r
556 function course_exist($id){
\r
558 $table=TABLE_PREFIX.'courses';
\r
559 $query="SELECT course_id FROM ".$table." WHERE course_id='{$id}'";
\r
560 $result=mysql_query($query, $db);
\r
561 $count=mysql_affected_rows();
\r
562 if ($count==1){ // It should affect only one record
\r
571 * @desc This function checks whether the given input belongs to the user
\r
572 * @param int $choose IMAGE or COMMENT to choose
\r
573 * @param int $image_id image id
\r
574 * @param int $course_id course id
\r
575 * @param int $comment_id comment_id
\r
576 * @return Boolean true if the input belongs to the user
\r
578 function user_own($choose, $image_id, $course_id, $comment_id=NOT_SET){
\r
581 $table=get_table_name($choose);
\r
584 $query="SELECT login FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id;
\r
587 $query="SELECT login FROM ".$table." WHERE course_id=".$course_id." AND image_id=".$image_id." AND comment_id=".$comment_id;
\r
590 $result=mysql_query($query, $db);
\r
591 $result=mysql_fetch_array($result);
\r
592 $count=mysql_affected_rows();
\r
593 if (is_admin_for_course()){
\r
595 } else if ($count!=1) { //there should be only one owner
\r
597 $msg->addError('pa_func_user_own');
\r
598 redirect('index.php');
\r
600 if ($result['login']==$_SESSION['login']){
\r
609 * @desc This function changes the image status
\r
610 * @param int $image_id image id
\r
611 * @param int $course_id course id
\r
612 * @param int $status image status, APPROVED, DISAPPROVED or POSTED_NEW
\r
614 function modify_image_status($image_id, $course_id, $status){
\r
615 If (($status==APPROVED || $status==DISAPPROVED || $status==POSTED_NEW) && image_exist($image_id, $course_id)){
\r
617 $table=get_table_name(IMAGE);
\r
618 $query="UPDATE ".$table." SET status=".$status." WHERE image_id=".$image_id." AND course_id=".$course_id;
\r
619 $result=mysql_query($query, $db);
\r
625 * @desc This function changes the comment status
\r
626 * @param int $comment_id comment id
\r
627 * @param int $course_id course id
\r
628 * @param int $status comment status, APPROVED, DISAPPROVED or POSTED_NEW
\r
630 function modify_comment_status($comment_id, $course_id, $status){
\r
631 if (($status==APPROVED || $status==DISAPPROVED || $status==POSTED_NEW) && comment_exist($comment_id, $course_id)){
\r
633 $table=get_table_name(COMMENT);
\r
634 $query="UPDATE ".$table." SET status=".$status." WHERE course_id=".$course_id." AND comment_id=".$comment_id;
\r
635 $result=mysql_query($query, $db);
\r
641 * @desc This function returns the moderation status for the course. If unmoderated, the course adds user submissions immediately
\r
642 * @param int $course_id course_id
\r
643 * @return int moderation status for the course, ENABLED or DISABLED
\r
645 function get_config_mode($course_id){
\r
647 $table=get_table_name(CONFIG);
\r
648 $query="SELECT status FROM ".$table." WHERE course_id=".$course_id;
\r
649 $result=mysql_query($query, $db);
\r
650 $count=mysql_affected_rows();
\r
652 if ($count==1){ //should be one configuration for the course
\r
653 $result=mysql_fetch_array($result);
\r
654 return $result['status'];
\r
655 } else { //configuration does not exist, so make one
\r
656 $query="INSERT INTO ".$table." SET course_id=".$course_id.", status=".CONFIG_DISABLED.", date=NOW()";
\r
657 mysql_query($query, $db);
\r
658 return CONFIG_DISABLED;
\r
664 * @desc This function modifies the moderation status
\r
665 * @param int $course_id course id
\r
666 * @param int $status moderation status
\r
668 function modify_config_mode($course_id, $status){
\r
670 $table=get_table_name(CONFIG);
\r
671 $query="UPDATE ".$table." SET status=".$status." WHERE course_id=".$course_id;
\r
672 mysql_query($query, $db);
\r
677 * @desc This function returns the maximum file size for the course
\r
678 * @param int $course_id course id
\r
679 * @return int max_file_size
\r
681 function get_max_file_size($course_id){
\r
683 $query="SELECT max_file_size FROM ".TABLE_PREFIX."courses WHERE course_id=".$course_id;
\r
684 $result=mysql_query($query, $db);
\r
685 $count=mysql_affected_rows();
\r
687 $result=mysql_fetch_array($result);
\r
688 if ($result['max_file_size']>0){
\r
689 return $result['max_file_size'];
\r