competency/classes/user_competency_plan.php

   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 }