include/classes/DAO/CoursesDAO.class.php

   1 <?php
   2 /************************************************************************/
   3 /* AContent                                                             */
   4 /************************************************************************/
   5 /* Copyright (c) 2010                                                   */
   6 /* Inclusive Design Institute                                           */
   7 /*                                                                      */
   8 /* This program is free software. You can redistribute it and/or        */
   9 /* modify it under the terms of the GNU General Public License          */
  10 /* as published by the Free Software Foundation.                        */
  11 /************************************************************************/
  12
  13 /**
  14  * DAO for "courses" table
  15  * @access      public
  16  * @author      Cindy Qi Li
  17  * @package     DAO
  18  */
  19
  20 if (!defined('TR_INCLUDE_PATH')) exit;
  21
  22 require_once(TR_INCLUDE_PATH. 'classes/DAO/DAO.class.php');
  23
  24 class CoursesDAO extends DAO {
  25
  26         /**
  27          * Create new course
  28          * @access  public
  29          * @param
  30          * @return  user id, if successful
  31          *          false and add error into global var $msg, if unsuccessful
  32          * @author  Cindy Qi Li
  33          */
  34         public function Create($user_id, $content_packaging, $access, $title, $description, $course_dir_name,
  35                                $max_quota, $max_file_size, $copyright,
  36                                $primary_language, $icon, $side_menu)
  37         {
  38                 global $addslashes;
  39
  40                 $title = $addslashes(trim($title));
  41                 $decsription = $addslashes(trim($description));
  42                 $copyright = $addslashes(trim($copyright));
  43
  44                 if ($this->isFieldsValid($user_id, $title))
  45                 {
  46                         /* insert into the db */
  47                         $sql = "INSERT INTO ".TABLE_PREFIX."courses
  48                                       (user_id,
  49                                        content_packaging,
  50                                        access,
  51                                        title,
  52                                        description,
  53                                        course_dir_name,
  54                                        max_quota,
  55                                        max_file_size,
  56                                        copyright,
  57                                        primary_language,
  58                                        icon,
  59                                        side_menu,
  60                                        created_date
  61                                        )
  62                                VALUES (".$user_id.",
  63                                        '".$content_packaging."',
  64                                        '".$access."',
  65                                        '".$title."',
  66                                        '".$decsription."',
  67                                        '".$course_dir_name."',
  68                                        '".$max_quota."',
  69                                        '".$max_file_size."',
  70                                        '".$copyright."',
  71                                        '".$primary_language."',
  72                                        '".$icon."',
  73                                        '".$side_menu."',
  74                                        now())";
  75
  76                         if (!$this->execute($sql))
  77                         {
  78                                 $msg->addError('DB_NOT_UPDATED');
  79                                 return false;
  80                         }
  81                         else
  82                         {
  83                                 $course_id = mysql_insert_id();
  84                                 // create the user and course relationship
  85                                 $sql = "INSERT INTO ".TABLE_PREFIX."user_courses (user_id, course_id, role, last_cid)
  86                                         VALUES (".$user_id.", ".$course_id.", ".TR_USERROLE_AUTHOR.", 0)";
  87                                 $this->execute($sql);
  88
  89                                 // create the course content directory
  90                                 $path = TR_CONTENT_DIR . $course_id . '/';
  91                                 @mkdir($path, 0700);
  92
  93                                 return $course_id;
  94                         }
  95                 }
  96                 else
  97                 {
  98                         return false;
  99                 }
 100         }
 101
 102         /**
 103          * Update an existing course record
 104          * @access  public
 105          * @param   courseID: course ID
 106          *          fieldName: the name of the table field to update
 107          *          fieldValue: the value to update
 108          * @return  true if successful
 109          *          error message array if failed; false if update db failed
 110          * @author  Cindy Qi Li
 111          */
 112         public function UpdateField($courseID, $fieldName, $fieldValue)
 113         {
 114                 global $addslashes, $msg;
 115
 116                 // check if ther courseID is provided
 117                 if (intval($courseID) == 0)
 118                 {
 119                         $missing_fields[] = _AT('course_id');
 120                 }
 121
 122                 if ($missing_fields)
 123                 {
 124                         $missing_fields = implode(', ', $missing_fields);
 125                         $msg->addError(array('EMPTY_FIELDS', $missing_fields));
 126                 }
 127
 128                 if ($msg->containsErrors())
 129                         return false;
 130
 131                 // check if the course title is provided
 132                 if ($fieldName == 'title' && !$this->isFieldsValid($courseID, $fieldValue))
 133                         return false;
 134
 135                 $sql = "UPDATE ".TABLE_PREFIX."courses
 136                            SET ".$fieldName."='".$addslashes($fieldValue)."',
 137                                modified_date = now()
 138                          WHERE course_id = ".$courseID;
 139
 140                 return $this->execute($sql);
 141         }
 142
 143         /**
 144          * Delete course
 145          * @access  public
 146          * @param   course ID
 147          * @return  true, if successful
 148          *          false and add error into global var $msg, if unsuccessful
 149          * @author  Cindy Qi Li
 150          */
 151         public function Delete($courseID)
 152         {
 153                 require_once(TR_INCLUDE_PATH.'classes/FileUtility.class.php');
 154                 require_once(TR_INCLUDE_PATH.'classes/DAO/ContentDAO.class.php');
 155                 require_once(TR_INCLUDE_PATH.'classes/DAO/ForumsCoursesDAO.class.php');
 156                 $contentDAO = new ContentDAO();
 157                 $forumsCoursesDAO = new ForumsCoursesDAO();
 158
 159                 unset($_SESSION['s_cid']);
 160
 161                 // delete course content dir
 162                 $content_dir = TR_CONTENT_DIR.$courseID.'/';
 163                 FileUtility::clr_dir($content_dir);
 164
 165                 // delete tests and tests related data
 166                 $sql = "DELETE FROM ".TABLE_PREFIX."content_tests_assoc
 167                          WHERE content_id in (SELECT content_id FROM ".TABLE_PREFIX."content WHERE course_id = ".$courseID.")";
 168                 $this->execute($sql);
 169
 170                 $sql = "DELETE FROM ".TABLE_PREFIX."tests_questions_categories WHERE course_id = ".$courseID;
 171                 $this->execute($sql);
 172
 173                 $sql = "DELETE FROM ".TABLE_PREFIX."tests_questions_assoc
 174                          WHERE test_id in (SELECT test_id FROM ".TABLE_PREFIX."tests WHERE course_id = ".$courseID.")";
 175                 $this->execute($sql);
 176
 177                 $sql = "DELETE FROM ".TABLE_PREFIX."tests_questions WHERE course_id = ".$courseID;
 178                 $this->execute($sql);
 179
 180                 $sql = "DELETE FROM ".TABLE_PREFIX."tests WHERE course_id = ".$courseID;
 181                 $this->execute($sql);
 182
 183                 // delete forums that are associated with this course
 184                 $forumsCoursesDAO->DeleteByCourseID($courseID);
 185
 186                 // loop thru content to delete using ContentDAO->Delete(), which deletes a4a objects as well
 187                 $content_rows = $contentDAO->getContentByCourseID($courseID);
 188                 if (is_array($content_rows)) {
 189                         foreach ($content_rows as $content) {
 190                                 $contentDAO->Delete($content['content_id']);
 191                         }
 192                 }
 193                 $sql = "DELETE FROM ".TABLE_PREFIX."content WHERE course_id = ".$courseID;
 194                 $this->execute($sql);
 195
 196                 // delete user <-> course association
 197                 $sql = "DELETE FROM ".TABLE_PREFIX."user_courses WHERE course_id = ".$courseID;
 198                 $this->execute($sql);
 199
 200                 // delete the course
 201                 $sql = "DELETE FROM ".TABLE_PREFIX."courses WHERE course_id = ".$courseID;
 202                 return $this->execute($sql);
 203         }
 204
 205         /**
 206          * Update courses.modified_date to the current timestamp
 207          * @access  public
 208          * @param   id: course_id or content_id
 209          *          id_type: "course_id" or "content_id", by default is "course_id"
 210          * @return  true if successful, otherwise, return false
 211          * @author  Cindy Qi Li
 212          */
 213         public function updateModifiedDate($id, $id_type = "course_id")
 214         {
 215                 if ($id_type != "course_id" && $id_type != "content_id") return false;
 216
 217                 if ($id_type == "course_id") {
 218                         $sql = "UPDATE ".TABLE_PREFIX."courses SET modified_date=now() WHERE course_id=".$id;
 219                 } else if ($id_type == "content_id") {
 220                         $sql = "UPDATE ".TABLE_PREFIX."courses SET modified_date=now()
 221                                  WHERE course_id=(SELECT course_id FROM ".TABLE_PREFIX."content WHERE content_id=".$id.")";
 222                 }
 223                 return $this->execute($sql);
 224         }
 225
 226         /**
 227          * Return course information by given course id
 228          * @access  public
 229          * @param   course id
 230          * @return  course row
 231          * @author  Cindy Qi Li
 232          */
 233         public function get($courseID)
 234         {
 235                 $sql = 'SELECT * FROM '.TABLE_PREFIX.'courses WHERE course_id='.$courseID;
 236                 if ($rows = $this->execute($sql))
 237                 {
 238                         return $rows[0];
 239                 }
 240                 else return false;
 241         }
 242
 243         /**
 244          * Return courses in the order of time stamp
 245          * @access  public
 246          * @param   none
 247          * @return  course row
 248          * @author  Cindy Qi Li
 249          */
 250         public function getByMostRecent()
 251         {
 252                 $sql = "SELECT * FROM ".TABLE_PREFIX."courses
 253                          WHERE access='public'
 254                          ORDER BY modified_date DESC, created_date DESC";
 255                 return $this->execute($sql);
 256         }
 257
 258         /**
 259          * Return course information by given category id
 260          * @access  public
 261          * @param   category id
 262          * @return  course row
 263          * @author  Cindy Qi Li
 264          */
 265         public function getByCategory($categoryID)
 266         {
 267                 $sql = "SELECT * FROM ".TABLE_PREFIX."courses
 268                          WHERE category_id=".$categoryID."
 269                            AND access='public'
 270                          ORDER BY title";
 271                 return $rows = $this->execute($sql);
 272         }
 273
 274         /**
 275          * Return the array of (category_id, num_of_courses)
 276          * @access  public
 277          * @param   none
 278          * @return  the array of (category_id, num_of_courses)
 279          * @author  Cindy Qi Li
 280          */
 281         public function getCategoriesAndNumOfCourses()
 282         {
 283                 $sql = "SELECT category_id, count(*) num_of_courses
 284                           FROM ".TABLE_PREFIX."courses
 285                          WHERE access = 'public'
 286                          GROUP BY category_id";
 287                 return $this->execute($sql);
 288         }
 289
 290         /**
 291          * Return course information by given course id
 292          * @access  public
 293          * @param   keywords: for keywords to include, use '+' in front.
 294          *                    for keywords to exclude, use '-' in front.
 295          *                    for example '+a -b' means find all courses with keyword 'a', without 'b'
 296          *          catid: category id
 297          *          start: start receiving from this record number, 0 if not specified
 298          *          maxResults: Number of results desired. If 0, returns all
 299          * @return  course row if successful, otherwise, return false
 300          * @author  Cindy Qi Li
 301          */
 302         public function getSearchResult($keywords, $catid='', $start=0, $maxResults=0)
 303         {
 304                 require_once(TR_INCLUDE_PATH.'classes/Utility.class.php');
 305                 // full-text search
 306 //              $sql = "SELECT DISTINCT course_id, title, description, created_date
 307 //                        FROM (SELECT cs.course_id as course_id, cs.title as title, cs.description as description
 308 //                                     MATCH(cs.title, cs.description) AGAINST ('".$keywords."') as score1,
 309 //                                     MATCH(ct.keywords, ct.title, ct.text) AGAINST ('".$keywords."') score2
 310 //                                FROM ".TABLE_PREFIX."courses cs, ".TABLE_PREFIX."content ct
 311 //                               WHERE cs.access='public'
 312 //                                 AND cs.course_id = ct.course_id
 313 //                                 AND (MATCH(cs.title, cs.description) AGAINST ('".$keywords."' in boolean mode)
 314 //                                  OR MATCH(ct.keywords, ct.title, ct.text) AGAINST ('".$keywords."' in boolean mode))
 315 //                               ORDER BY score1+score2 desc) a";
 316
 317                 // if the keywords is not given, return false
 318                 $keywords = trim($keywords);
 319 //              if ($keywords == '') return false;
 320
 321                 $all_keywords = Utility::removeEmptyItemsFromArray(explode(' ', $keywords));
 322
 323 //              if (!is_array($all_keywords) || count($all_keywords) == 0) return false;
 324
 325                 list($sql_where, $sql_order) = $this->getSearchSqlParams($all_keywords);
 326
 327                 if ($sql_where <> '') $sql_where = ' AND '. $sql_where;
 328                 if (trim($catid) <> '') $sql_where .= ' AND category_id='.intval($catid);
 329
 330                 // sql search
 331                 $sql = "SELECT DISTINCT cs.course_id, cs.title, cs.description, cs.created_date
 332                           FROM ".TABLE_PREFIX."courses cs, ".TABLE_PREFIX."content ct, ".TABLE_PREFIX."users u
 333                          WHERE cs.access='public'
 334                            AND cs.course_id = ct.course_id
 335                            AND cs.user_id = u.user_id";
 336                 if ($sql_where <> '') $sql .= $sql_where;
 337                 if ($sql_order <> '') $sql .= " ORDER BY ".$sql_order." DESC ";
 338
 339                 if ($maxResults > 0) $sql .= " LIMIT ".$start.", ".$maxResults;
 340
 341                 return $this->execute($sql);
 342         }
 343
 344         /**
 345          * Validate fields preparing for insert and update
 346          * @access  private
 347          * @param   $courseID, $title
 348          * @return  true    if update successfully
 349          *          false   if update unsuccessful
 350          * @author  Cindy Qi Li
 351          */
 352         private function isFieldsValid($courseID, $title)
 353         {
 354                 global $msg;
 355
 356                 $missing_fields = array();
 357
 358                 if (intval($courseID) == 0)
 359                 {
 360                         $missing_fields[] = _AT('course_id');
 361                 }
 362                 if ($title == '')
 363                 {
 364                         $missing_fields[] = _AT('title');
 365                 }
 366
 367                 if ($missing_fields)
 368                 {
 369                         $missing_fields = implode(', ', $missing_fields);
 370                         $msg->addError(array('EMPTY_FIELDS', $missing_fields));
 371                 }
 372
 373                 if (!$msg->containsErrors())
 374                         return true;
 375                 else
 376                         return false;
 377         }
 378
 379         /**
 380          * Based on the pass-in keywords array, return the WHERE and ORDER BY part in the search SQL
 381          * @access  private
 382          * @param   $all_keywords   the array of all the keywords including "OR"
 383          * @return  the array of (sql_where, sql_order) if successful
 384          *          otherwise, return empty array
 385          * @author  Cindy Qi Li
 386          */
 387         private function getSearchSqlParams($all_keywords)
 388         {
 389                 if (!is_array($all_keywords) || count($all_keywords) == 0) return array();
 390
 391                 $sql_search_template = "(cs.title like '%{KEYWORD}%' ".
 392                                 "OR cs.description like '%{KEYWORD}%' ".
 393                                 "OR ct.keywords like '%{KEYWORD}%' ".
 394                                 "OR ct.title like '%{KEYWORD}%' ".
 395                                 "OR ct.text like '%{KEYWORD}%' ".
 396                                 "OR u.first_name like '%{KEYWORD}%' ".
 397                                 "OR u.last_name like '%{KEYWORD}%')";
 398                 $sql_order_template = " 15* ((LENGTH(cs.title) - LENGTH(REPLACE(lower(cs.title),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 399                                       " 12* ((LENGTH(cs.description) - LENGTH(REPLACE(lower(cs.description),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 400                                       " 10* ((LENGTH(u.first_name) - LENGTH(REPLACE(lower(u.first_name),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 401                                       " 10* ((LENGTH(u.last_name) - LENGTH(REPLACE(lower(u.last_name),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 402                                       " 8* ((LENGTH(ct.keywords) - LENGTH(REPLACE(lower(ct.keywords),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 403                                       " 4* ((LENGTH(ct.title) - LENGTH(REPLACE(lower(ct.title),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) + ".
 404                                       " 1* ((LENGTH(ct.text) - LENGTH(REPLACE(lower(ct.text),lower('{KEYWORD}'), ''))) / LENGTH(lower('{KEYWORD}'))) ";
 405
 406                 // get all OR conditions
 407                 $found_first_or_item = false;
 408                 foreach ($all_keywords as $i => $keyword)
 409                 {
 410                         if ($keyword == 'OR')
 411                         {
 412                                 // if the first keyword is "OR" without the leading keyword,
 413                                 // OR, the last keyword is "OR" without the following keyword,
 414                                 // remove this "OR"
 415                                 if ((!isset($all_keywords[$i-1]) && !$found_first_or_item) ||
 416                                     !isset($all_keywords[$i+1]))
 417                                 {
 418                                         unset($all_keywords[$i]);
 419                                         continue;
 420                                 }
 421
 422                                 // The first "OR" joins the 2 keywords around it,
 423                                 // the following "OR" only needs to join the keyword followed.
 424                                 // Removed the keywords that have been pushed into OR sql from
 425                                 // the keywords array.
 426                                 if (!$found_first_or_item)
 427                                 {
 428                                         $found_first_or_item = true;
 429                                         $sql_where_or .= str_replace('{KEYWORD}', $all_keywords[$i-1], $sql_search_template) .
 430                                                          ' OR '.
 431                                                          str_replace('{KEYWORD}', $all_keywords[$i+1], $sql_search_template);
 432                                         $sql_order_or .= str_replace('{KEYWORD}', $all_keywords[$i-1], $sql_order_template) .
 433                                                       ' + '.
 434                                                       str_replace('{KEYWORD}', $all_keywords[$i+1], $sql_order_template);
 435                                         unset($all_keywords[$i-1]);  // the keyword before "OR"
 436                                         unset($all_keywords[$i]);    // "OR"
 437                                         unset($all_keywords[$i+1]);  // the keyword after "OR"
 438                                 }
 439                                 else
 440                                 {
 441                                         $sql_where_or .= ' OR '.str_replace('{KEYWORD}', $all_keywords[$i+1], $sql_search_template);
 442                                         $sql_order_or .= ' + '.str_replace('{KEYWORD}', $all_keywords[$i+1], $sql_order_template);
 443                                         unset($all_keywords[$i]);   // "OR"
 444                                         unset($all_keywords[$i+1]); // the keyword after "OR"
 445                                 }
 446                         }
 447                 }
 448
 449                 // the left-over in $all_keywords array is "AND" condition
 450                 if (count($all_keywords) > 0)
 451                 {
 452                         foreach ($all_keywords as $keyword)
 453                         {
 454                                 $sql_where .= str_replace('{KEYWORD}', $keyword, $sql_search_template). ' AND ';
 455                                 $sql_order .= str_replace('{KEYWORD}', $keyword, $sql_order_template). ' + ';
 456                         }
 457                 }
 458                 if ($sql_where_or == '') $sql_where = substr($sql_where, 0, -5);
 459                 else $sql_where .= "(".$sql_where_or.")";
 460
 461                 if ($sql_order_or == '') $sql_order = substr($sql_order, 0, -3);
 462                 else $sql_order .= $sql_order_or;
 463
 464                 return array($sql_where, $sql_order);
 465         }
 466 }
 467 ?>