/** * Get the list of competencies that were completed the least times in a course. * * @param int $courseid * @param int $skip The number of competencies to skip * @param int $limit The max number of competencies to return * @return competency[] */ public static function get_least_proficient_competencies_for_course($courseid, $skip = 0, $limit = 0) { global $DB; $fields = competency::get_sql_fields('c', 'c_'); $params = array('courseid' => $courseid); $sql = 'SELECT ' . $fields . ' FROM (SELECT cc.competencyid, SUM(COALESCE(ucc.proficiency, 0)) AS timesproficient FROM {' . course_competency::TABLE . '} cc LEFT JOIN {' . self::TABLE . '} ucc ON ucc.competencyid = cc.competencyid AND ucc.courseid = cc.courseid WHERE cc.courseid = :courseid GROUP BY cc.competencyid ) p JOIN {' . competency::TABLE . '} c ON c.id = p.competencyid ORDER BY p.timesproficient ASC, c.id DESC'; $results = $DB->get_records_sql($sql, $params, $skip, $limit); $a = $DB->get_records_sql('SELECT * from {' . self::TABLE . '}'); $comps = array(); foreach ($results as $r) { $c = competency::extract_record($r, 'c_'); $comps[] = new competency(0, $c); } return $comps; }
/** * List the user competencies to review. * * The method returns values in this format: * * array( * 'competencies' => array( * (stdClass)( * 'usercompetency' => (user_competency), * 'competency' => (competency), * 'user' => (user) * ) * ), * 'count' => (int) * ) * * @param int $skip The number of records to skip. * @param int $limit The number of results to return. * @param int $userid The user we're getting the competencies to review for. * @return array Containing the keys 'count', and 'competencies'. The 'competencies' key contains an object * which contains 'competency', 'usercompetency' and 'user'. */ public static function list_user_competencies_to_review($skip = 0, $limit = 50, $userid = null) { global $DB, $USER; static::require_enabled(); if ($userid === null) { $userid = $USER->id; } $capability = 'moodle/competency:usercompetencyreview'; $ucfields = user_competency::get_sql_fields('uc', 'uc_'); $compfields = competency::get_sql_fields('c', 'c_'); $usercols = array('id') + get_user_fieldnames(); $userfields = array(); foreach ($usercols as $field) { $userfields[] = "u." . $field . " AS usr_" . $field; } $userfields = implode(',', $userfields); $select = "SELECT {$ucfields}, {$compfields}, {$userfields}"; $countselect = "SELECT COUNT('x')"; $sql = " FROM {" . user_competency::TABLE . "} uc\n JOIN {" . competency::TABLE . "} c\n ON c.id = uc.competencyid\n JOIN {user} u\n ON u.id = uc.userid\n WHERE (uc.status = :waitingforreview\n OR (uc.status = :inreview AND uc.reviewerid = :reviewerid))"; $ordersql = " ORDER BY c.shortname ASC"; $params = array('inreview' => user_competency::STATUS_IN_REVIEW, 'reviewerid' => $userid, 'waitingforreview' => user_competency::STATUS_WAITING_FOR_REVIEW); $countsql = $countselect . $sql; // Primary check to avoid the hard work of getting the users in which the user has permission. $count = $DB->count_records_sql($countselect . $sql, $params); if ($count < 1) { return array('count' => 0, 'competencies' => array()); } // TODO MDL-52243 Use core function. list($insql, $inparams) = self::filter_users_with_capability_on_user_context_sql($capability, $userid, SQL_PARAMS_NAMED); $params += $inparams; $countsql = $countselect . $sql . " AND uc.userid {$insql}"; $getsql = $select . $sql . " AND uc.userid {$insql} " . $ordersql; // Extracting the results. $competencies = array(); $records = $DB->get_recordset_sql($getsql, $params, $skip, $limit); foreach ($records as $record) { $objects = (object) array('usercompetency' => new user_competency(0, user_competency::extract_record($record, 'uc_')), 'competency' => new competency(0, competency::extract_record($record, 'c_')), 'user' => persistent::extract_record($record, 'usr_')); $competencies[] = $objects; } $records->close(); return array('count' => $DB->count_records_sql($countsql, $params), 'competencies' => $competencies); }
/** * Get the list of competencies that were completed the least times (in completed plans) from a template. * * @param int $templateid * @param int $skip The number of competencies to skip * @param int $limit The max number of competencies to return * @return competency[] */ public static function get_least_proficient_competencies_for_template($templateid, $skip = 0, $limit = 0) { global $DB; $fields = competency::get_sql_fields('c', 'c_'); $params = array('templateid' => $templateid, 'notproficient' => false); $sql = 'SELECT ' . $fields . ' FROM (SELECT ucp.competencyid, COUNT(ucp.competencyid) AS timesnotproficient FROM {' . self::TABLE . '} ucp JOIN {' . plan::TABLE . '} p ON p.id = ucp.planid WHERE p.templateid = :templateid AND (ucp.proficiency = :notproficient OR ucp.proficiency IS NULL) GROUP BY ucp.competencyid ) p JOIN {' . competency::TABLE . '} c ON c.id = p.competencyid ORDER BY p.timesnotproficient DESC, c.id ASC'; $results = $DB->get_records_sql($sql, $params, $skip, $limit); $comps = array(); foreach ($results as $r) { $c = competency::extract_record($r, 'c_'); $comps[] = new competency(0, $c); } return $comps; }
/** * Get the competencies related to a competency. * * @param int $competencyid The competency ID. * @return competency[] */ public static function get_related_competencies($competencyid) { global $DB; $fields = competency::get_sql_fields('c', 'c_'); $sql = "(SELECT {$fields}, " . $DB->sql_concat('rc.relatedcompetencyid', "'_'", 'rc.competencyid') . " AS rid\n FROM {" . self::TABLE . "} rc\n JOIN {" . competency::TABLE . "} c\n ON c.id = rc.relatedcompetencyid\n WHERE rc.competencyid = :cid)\n UNION ALL\n (SELECT {$fields}, " . $DB->sql_concat('rc.competencyid', "'_'", 'rc.relatedcompetencyid') . " AS rid\n FROM {" . self::TABLE . "} rc\n JOIN {" . competency::TABLE . "} c\n ON c.id = rc.competencyid\n WHERE rc.relatedcompetencyid = :cid2)\n ORDER BY c_path ASC, c_sortorder ASC"; $competencies = array(); $records = $DB->get_recordset_sql($sql, array('cid' => $competencyid, 'cid2' => $competencyid)); foreach ($records as $record) { unset($record->rid); $competencies[$record->c_id] = new competency(null, competency::extract_record($record, 'c_')); } $records->close(); return $competencies; }