[Summary view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Class for user_competency_plan persistence.
  19   *
  20   * @package    core_competency
  21   * @copyright  2015 Serge Gauthier
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  namespace core_competency;
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  use lang_string;
  28  use context_user;
  29  
  30  /**
  31   * Class for loading/storing user_competency_plan from the DB.
  32   *
  33   * @copyright  2015 Serge Gauthier
  34   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  35   */
  36  class user_competency_plan extends persistent {
  37  
  38      /** Table name for user_competency_plan persistency */
  39      const TABLE = 'competency_usercompplan';
  40  
  41      /**
  42       * Return the definition of the properties of this model.
  43       *
  44       * @return array
  45       */
  46      protected static function define_properties() {
  47          return array(
  48              'userid' => array(
  49                  'type' => PARAM_INT,
  50              ),
  51              'competencyid' => array(
  52                  'type' => PARAM_INT,
  53              ),
  54              'proficiency' => array(
  55                  'type' => PARAM_BOOL,
  56                  'default' => null,
  57                  'null' => NULL_ALLOWED,
  58              ),
  59              'grade' => array(
  60                  'type' => PARAM_INT,
  61                  'default' => null,
  62                  'null' => NULL_ALLOWED,
  63              ),
  64              'planid' => array(
  65                  'type' => PARAM_INT,
  66              ),
  67              'sortorder' => array(
  68                  'type' => PARAM_INT,
  69                  'default' => 0,
  70              ),
  71          );
  72      }
  73  
  74      /**
  75       * Return the competency Object.
  76       *
  77       * @return competency Competency Object
  78       */
  79      public function get_competency() {
  80          return new competency($this->get('competencyid'));
  81      }
  82  
  83      /**
  84       * Get the context.
  85       *
  86       * @return context The context.
  87       */
  88      public function get_context() {
  89          return context_user::instance($this->get('userid'));
  90      }
  91  
  92      /**
  93       * Validate the user ID.
  94       *
  95       * @param int $value The value.
  96       * @return true|lang_string
  97       */
  98      protected function validate_userid($value) {
  99          global $DB;
 100  
 101          if (!$DB->record_exists('user', array('id' => $value))) {
 102              return new lang_string('invaliduserid', 'error');
 103          }
 104  
 105          return true;
 106      }
 107  
 108      /**
 109       * Validate the competency ID.
 110       *
 111       * @param int $value The value.
 112       * @return true|lang_string
 113       */
 114      protected function validate_competencyid($value) {
 115          if (!competency::record_exists($value)) {
 116              return new lang_string('errornocompetency', 'core_competency', $value);
 117          }
 118  
 119          return true;
 120      }
 121  
 122      /**
 123       * Validate the grade.
 124       *
 125       * @param int $value The value.
 126       * @return true|lang_string
 127       */
 128      protected function validate_grade($value) {
 129          if ($value !== null) {
 130              if ($value <= 0) {
 131                  return new lang_string('invalidgrade', 'core_competency');
 132              }
 133  
 134              // TODO MDL-52243 Use a core method to validate the grade_scale item.
 135              // Check if grade exist in the scale item values.
 136              $competency = $this->get_competency();
 137              if (!array_key_exists($value - 1, $competency->get_scale()->scale_items)) {
 138                  return new lang_string('invalidgrade', 'core_competency');
 139              }
 140          }
 141  
 142          return true;
 143      }
 144  
 145      /**
 146       * Validate the plan ID.
 147       *
 148       * @param int $value The value.
 149       * @return true|lang_string
 150       */
 151      protected function validate_planid($value) {
 152          if (!plan::record_exists($value) ) {
 153              return new lang_string('invalidplan', 'core_competency');
 154          }
 155  
 156          return true;
 157      }
 158  
 159      /**
 160       * Create a new user_competency_plan object.
 161       *
 162       * Note, this is intended to be used to create a blank relation, for instance when
 163       * the record was not found in the database. This does not save the model.
 164       *
 165       * @param int $userid The user ID.
 166       * @param int $competencyid The competency ID.
 167       * @param int $planid The plan ID.
 168       * @return \core_competency\user_competency_plan
 169       */
 170      public static function create_relation($userid, $competencyid, $planid) {
 171          $relation = new user_competency_plan(0, (object) array('userid' => $userid, 'competencyid' => $competencyid,
 172                  'planid' => $planid));
 173          return $relation;
 174      }
 175  
 176      /**
 177       * List the competencies in this plan.
 178       *
 179       * @param int $planid The plan ID
 180       * @param int $userid The user ID
 181       * @return competency[]
 182       */
 183      public static function list_competencies($planid, $userid) {
 184          global $DB;
 185  
 186          $sql = 'SELECT c.*
 187                    FROM {' . competency::TABLE . '} c
 188                    JOIN {' . self::TABLE . '} ucp
 189                      ON ucp.competencyid = c.id
 190                     AND ucp.userid = :userid
 191                   WHERE ucp.planid = :planid
 192                ORDER BY ucp.sortorder ASC,
 193                         ucp.id ASC';
 194          $params = array('userid' => $userid, 'planid' => $planid);
 195  
 196          $results = $DB->get_recordset_sql($sql, $params);
 197          $instances = array();
 198          foreach ($results as $key => $result) {
 199               $instances[$key] = new competency(0, $result);
 200          }
 201          $results->close();
 202  
 203          return $instances;
 204      }
 205  
 206      /**
 207       * Fetch a competency by plan ID.
 208       *
 209       * @param  int $id The plan ID.
 210       * @return competency
 211       */
 212      public static function get_competency_by_planid($planid, $competencyid) {
 213          global $DB;
 214  
 215          $sql = "SELECT c.*
 216                    FROM {" . self::TABLE . "} ucp
 217                    JOIN {" . competency::TABLE . "} c
 218                      ON c.id = ucp.competencyid
 219                   WHERE ucp.planid = ?
 220                     AND ucp.competencyid = ?";
 221          $record = $DB->get_record_sql($sql, array($planid, $competencyid));
 222  
 223          if (!$record) {
 224              throw new \coding_exception('The competency does not belong to this plan: ' . $competencyid . ', ' . $planid);
 225          }
 226  
 227          return new competency(0, $record);
 228      }
 229  
 230      /**
 231       * Get multiple user_competency_plan for a user.
 232       *
 233       * @param int $userid The user ID.
 234       * @param int $planid The plan ID.
 235       * @param array  $competenciesorids Limit search to those competencies, or competency IDs.
 236       * @return \core_competency\user_competency_plan[]
 237       */
 238      public static function get_multiple($userid, $planid, array $competenciesorids = null) {
 239          global $DB;
 240  
 241          $params = array();
 242          $params['userid'] = $userid;
 243          $params['planid'] = $planid;
 244          $sql = '1 = 1';
 245  
 246          if (!empty($competenciesorids)) {
 247              $test = reset($competenciesorids);
 248              if (is_number($test)) {
 249                  $ids = $competenciesorids;
 250              } else {
 251                  $ids = array();
 252                  foreach ($competenciesorids as $comp) {
 253                      $ids[] = $comp->get('id');
 254                  }
 255              }
 256  
 257              list($insql, $inparams) = $DB->get_in_or_equal($ids, SQL_PARAMS_NAMED);
 258              $params += $inparams;
 259              $sql = "competencyid $insql";
 260          }
 261  
 262          // Order by ID to prevent random ordering.
 263          return static::get_records_select("userid = :userid AND planid = :planid AND $sql", $params, 'id ASC');
 264      }
 265  
 266      /**
 267       * Checks if a competency has user competency plan records.
 268       *
 269       * @param  int $competencyid The competency ID
 270       * @return boolean
 271       */
 272      public static function has_records_for_competency($competencyid) {
 273          return self::record_exists_select('competencyid = ?', array($competencyid));
 274      }
 275  
 276      /**
 277       * Checks if any of the competencies of a framework has a user competency plan record.
 278       *
 279       * @param  int $frameworkid The competency framework ID.
 280       * @return boolean
 281       */
 282      public static function has_records_for_framework($frameworkid) {
 283          global $DB;
 284  
 285          $sql = "SELECT 'x'
 286                    FROM {" . self::TABLE . "} ucp
 287                    JOIN {" . competency::TABLE . "} c
 288                      ON ucp.competencyid = c.id
 289                   WHERE c.competencyframeworkid = ?";
 290          $params = array($frameworkid);
 291  
 292          return $DB->record_exists_sql($sql, $params);
 293      }
 294  
 295      /**
 296       * Check if user competency plan has records for competencies.
 297       *
 298       * @param array $competencyids The competences IDs
 299       * @return boolean
 300       */
 301      public static function has_records_for_competencies($competencyids) {
 302          global $DB;
 303          list($insql, $params) = $DB->get_in_or_equal($competencyids, SQL_PARAMS_NAMED);
 304          return self::record_exists_select("competencyid $insql", $params);
 305      }
 306  
 307      /**
 308       * Count the number of records matching a specific template, optionally filtered by proficient values.
 309       *
 310       * @param int $templateid
 311       * @param mixed $proficiency - If true - filter by proficiency, if false filter by not proficient, if null - do not filter.
 312       * @return int
 313       */
 314      public static function count_records_for_template($templateid, $proficiency=null) {
 315          global $DB;
 316  
 317          $params = array('templateid' => $templateid);
 318          $sql = 'SELECT ' . " COUNT('x') " .
 319                   'FROM {' . self::TABLE . '} ucp
 320                    JOIN {' . plan::TABLE . '} p
 321                      ON ucp.planid = p.id
 322                   WHERE p.templateid = :templateid';
 323          if ($proficiency === true) {
 324              $sql .= ' AND ucp.proficiency = :proficiency';
 325              $params['proficiency'] = true;
 326          } else if ($proficiency === false) {
 327              $sql .= ' AND (ucp.proficiency = :proficiency OR ucp.proficiency IS NULL)';
 328              $params['proficiency'] = false;
 329          }
 330  
 331          return $DB->count_records_sql($sql, $params);
 332      }
 333  
 334      /**
 335       * Get the list of competencies that were completed the least times (in completed plans) from a template.
 336       *
 337       * @param int $templateid
 338       * @param int $skip The number of competencies to skip
 339       * @param int $limit The max number of competencies to return
 340       * @return competency[]
 341       */
 342      public static function get_least_proficient_competencies_for_template($templateid, $skip = 0, $limit = 0) {
 343          global $DB;
 344  
 345          $fields = competency::get_sql_fields('c', 'c_');
 346          $params = array('templateid' => $templateid, 'notproficient' => false);
 347          $sql = 'SELECT ' . $fields . '
 348                    FROM (SELECT ucp.competencyid, COUNT(ucp.competencyid) AS timesnotproficient
 349                            FROM {' . self::TABLE . '} ucp
 350                            JOIN {' . plan::TABLE . '} p
 351                                 ON p.id = ucp.planid
 352                           WHERE p.templateid = :templateid
 353                                 AND (ucp.proficiency = :notproficient OR ucp.proficiency IS NULL)
 354                        GROUP BY ucp.competencyid
 355                       ) p
 356                    JOIN {' . competency::TABLE . '} c
 357                      ON c.id = p.competencyid
 358                ORDER BY p.timesnotproficient DESC, c.id ASC';
 359  
 360          $results = $DB->get_records_sql($sql, $params, $skip, $limit);
 361  
 362          $comps = array();
 363          foreach ($results as $r) {
 364              $c = competency::extract_record($r, 'c_');
 365              $comps[] = new competency(0, $c);
 366          }
 367          return $comps;
 368      }
 369  }