<commit_before><?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

include_once "ilios_base_model.php";

/**
 * Data Access Object (DAO) providing read-only access to various sets of data in the
 * Ilios database.
 */
class Canned_Queries extends Ilios_Base_Model
{
    /**
     * Constructor
     */
    public function __construct ()
    {
        parent::__construct();
    }

    /**
     * Retrieves a list of course-titles and -ids for courses that are associated with a given learner group via
     * session-offerings and independent-learning-sessions.
     * Note: Archived courses are excluded.
     * @param int $groupId The learner group id.
     * @return array A nested array of associative arrays. Each item contains a course title (key: 'title') and a course
     *      identifier (key: 'course_id').
     */
    public function getCourseIdAndTitleForLearnerGroup ($groupId)
    {
        $rhett = array();
        $clean = array();
        $clean['group_id'] = (int) $groupId;

        $sql =<<< EOL
SELECT c.`course_id`, c.`title`
FROM `offering_x_group` oxg
JOIN `offering` o ON o.`offering_id` = oxg.`offering_id`
JOIN `session` s ON s.`session_id` = o.`session_id`
JOIN `course` c ON c.`course_id` = s.`course_id`
WHERE oxg.`group_id` = {$clean['group_id']}
AND o.`deleted` = 0
AND s.`deleted` = 0
AND c.`deleted` = 0
AND c.`archived` = 0
UNION
SELECT c.`course_id`, c.`title`
FROM `ilm_session_facet_x_group` isfxg
JOIN `ilm_session_facet` isf ON isf.`ilm_session_facet_id` = isfxg.`ilm_session_facet_id`
JOIN `session` s ON s.`ilm_session_facet_id` = isf.`ilm_session_facet_id`
JOIN `course` c ON c.`course_id` = s.`course_id`
WHERE isfxg.`group_id` = {$clean['group_id']}
AND s.`deleted` = 0
AND c.`deleted` = 0
AND c.`archived` = 0
EOL;
        $query = $this->db->query($sql);
        if (0 < $query->num_rows()) {
            foreach ($query->result_array() as $row) {
                $rhett[] = $row;
            }
        }
        $query->free_result();
        return $rhett;
    }

    /**
     * Retrieves a list of learner groups that are associated with independent learning sessions and offerings
     * in a given course.
     * @param int $courseId The course id.
     * @return array A nested array of associative arrays. Each item represents a learner group, containing the group's
     *      identifier (key: 'group_id') and the group's name (key: 'title').
     */
    public function getLearnerGroupIdAndTitleForCourse ($courseId)
    {
        $rhett = array();
        $clean = array();
        $clean['course_id'] = (int) $courseId;

        $sql =<<< EOL
SELECT g.`group_id`, g.`title`
FROM `session` s
JOIN `offering` o ON o.`session_id` = s.`session_id`
JOIN `offering_x_group` oxg ON oxg.`offering_id` = o.`offering_id`
JOIN `group` g ON g.`group_id` = oxg.`group_id`
WHERE
s.`deleted` = 0
AND o.`deleted` = 0
AND s.`course_id` = {$clean['course_id']}
UNION
SELECT g.`group_id`, g.`title`
FROM `session` s
JOIN `ilm_session_facet` isf ON isf.`ilm_session_facet_id` = s.`ilm_session_facet_id`
JOIN `ilm_session_facet_x_group` isfxg ON isfxg.`ilm_session_facet_id` = isf.`ilm_session_facet_id`
JOIN `group` g ON g.`group_id` = isfxg.`group_id`
WHERE s.`deleted` = 0
AND s.`course_id` = {$clean['course_id']}
EOL;

        $query = $this->db->query($sql);
        if (0 < $query->num_rows()) {
            foreach ($query->result_array() as $row) {
                $rhett[] = $row;
            }
        }
        $query->free_result();
        return $rhett;
    }

    /**
     * Similar to <code>getOfferingsForCalendar</code>, but look up additional details about
     * the entries
     *
     * Returns learning-session offerings with fields that are needed for the calendar feeds.
     * @param int $schoolId
     * @param int $userId
     * @param array $roles an array of user-role ids
     * @param int $year
     * @param bool $includeArchived
     * @param int $begin UNIX timestamp when to begin search
     * @param int $end UNIX timestamp when to end search
     * @return array
     */
    public function getOfferingsDetailsForCalendarFeed ($schoolId = null,
            $userId = null, $roles = array(), $year = null,
            $includeArchived = false,
            $begin = null, $end = null)
    {
        $offerings = $this->_getOfferingsForCalendarFeed($userId, $schoolId, $roles,
         $year, $includeArchived, $begin, $end);
        $offeringIDs = $courses = $sessions = array();
        foreach($offerings as $offering) {
          $offeringIDs[] = $offering['offering_id'];
          $courses[] = $offering['course_id'];
          $sessions[] = $offering['session_id'];
        }
        $offeringIDs = array_unique($offeringIDs);
        $courses = array_unique($courses);
        $sessions = array_unique($sessions);

        $instructors = $this->getOfferingsInstructors($offeringIDs);
        $courseObjectives = $this->getCoursesObjectives($courses);
        $courseMaterials = $this->getCoursesMaterials($courses);
        $sessionObjectives = $this->getSessionsObjectives($sessions);
        $sessionMaterials = $this->getSessionsMaterials($sessions);

        for($i=0;$i<count($offerings);$i++) {
          @$offerings[$i]['instructors'] =
           $instructors[$offerings[$i]['offering_id']];
          @$offerings[$i]['course_objectives'] =
           $courseObjectives[$offerings[$i]['course_id']];
          @$offerings[$i]['course_materials'] =
           $courseMaterials[$offerings[$i]['course_id']];
          @$offerings[$i]['session_objectives'] =
           $sessionObjectives[$offerings[$i]['session_id']];
          @$offerings[$i]['session_materials'] =
           $sessionMaterials[$offerings[$i]['session_id']];
        }
        return $offerings;
    }

    /**
     * @param array $offerings An array of offering ids.
     * @return array
     */
    public function getOfferingsInstructors (array $offerings)
    {
        $rhett = array();

        if (empty($offerings)) {<commit_msg>code cleanup in method getOfferingDetailsForCalendarFeed(): renamed variables, improved code docs, fixed indentation etc.<commit_after><?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

include_once "ilios_base_model.php";

/**
 * Data Access Object (DAO) providing read-only access to various sets of data in the
 * Ilios database.
 */
class Canned_Queries extends Ilios_Base_Model
{
    /**
     * Constructor
     */
    public function __construct ()
    {
        parent::__construct();
    }

    /**
     * Retrieves a list of course-titles and -ids for courses that are associated with a given learner group via
     * session-offerings and independent-learning-sessions.
     * Note: Archived courses are excluded.
     * @param int $groupId The learner group id.
     * @return array A nested array of associative arrays. Each item contains a course title (key: 'title') and a course
     *      identifier (key: 'course_id').
     */
    public function getCourseIdAndTitleForLearnerGroup ($groupId)
    {
        $rhett = array();
        $clean = array();
        $clean['group_id'] = (int) $groupId;

        $sql =<<< EOL
SELECT c.`course_id`, c.`title`
FROM `offering_x_group` oxg
JOIN `offering` o ON o.`offering_id` = oxg.`offering_id`
JOIN `session` s ON s.`session_id` = o.`session_id`
JOIN `course` c ON c.`course_id` = s.`course_id`
WHERE oxg.`group_id` = {$clean['group_id']}
AND o.`deleted` = 0
AND s.`deleted` = 0
AND c.`deleted` = 0
AND c.`archived` = 0
UNION
SELECT c.`course_id`, c.`title`
FROM `ilm_session_facet_x_group` isfxg
JOIN `ilm_session_facet` isf ON isf.`ilm_session_facet_id` = isfxg.`ilm_session_facet_id`
JOIN `session` s ON s.`ilm_session_facet_id` = isf.`ilm_session_facet_id`
JOIN `course` c ON c.`course_id` = s.`course_id`
WHERE isfxg.`group_id` = {$clean['group_id']}
AND s.`deleted` = 0
AND c.`deleted` = 0
AND c.`archived` = 0
EOL;
        $query = $this->db->query($sql);
        if (0 < $query->num_rows()) {
            foreach ($query->result_array() as $row) {
                $rhett[] = $row;
            }
        }
        $query->free_result();
        return $rhett;
    }

    /**
     * Retrieves a list of learner groups that are associated with independent learning sessions and offerings
     * in a given course.
     * @param int $courseId The course id.
     * @return array A nested array of associative arrays. Each item represents a learner group, containing the group's
     *      identifier (key: 'group_id') and the group's name (key: 'title').
     */
    public function getLearnerGroupIdAndTitleForCourse ($courseId)
    {
        $rhett = array();
        $clean = array();
        $clean['course_id'] = (int) $courseId;

        $sql =<<< EOL
SELECT g.`group_id`, g.`title`
FROM `session` s
JOIN `offering` o ON o.`session_id` = s.`session_id`
JOIN `offering_x_group` oxg ON oxg.`offering_id` = o.`offering_id`
JOIN `group` g ON g.`group_id` = oxg.`group_id`
WHERE
s.`deleted` = 0
AND o.`deleted` = 0
AND s.`course_id` = {$clean['course_id']}
UNION
SELECT g.`group_id`, g.`title`
FROM `session` s
JOIN `ilm_session_facet` isf ON isf.`ilm_session_facet_id` = s.`ilm_session_facet_id`
JOIN `ilm_session_facet_x_group` isfxg ON isfxg.`ilm_session_facet_id` = isf.`ilm_session_facet_id`
JOIN `group` g ON g.`group_id` = isfxg.`group_id`
WHERE s.`deleted` = 0
AND s.`course_id` = {$clean['course_id']}
EOL;

        $query = $this->db->query($sql);
        if (0 < $query->num_rows()) {
            foreach ($query->result_array() as $row) {
                $rhett[] = $row;
            }
        }
        $query->free_result();
        return $rhett;
    }

    /**
     * Similar to <code>getOfferingsForCalendar</code>, but look up additional details about the entries.
     * Returns learning-session offerings with fields that are needed for the calendar feeds.
     *
     * @param int $schoolId
     * @param int $userId
     * @param array $roles an array of user-role ids
     * @param int $year
     * @param bool $includeArchived
     * @param int $begin UNIX timestamp when to begin search
     * @param int $end UNIX timestamp when to end search
     * @return array
     */
    public function getOfferingsDetailsForCalendarFeed ($schoolId = null, $userId = null, $roles = array(), $year = null,
                                                        $includeArchived = false, $begin = null, $end = null)
    {
        // get the offerings
        $offerings = $this->_getOfferingsForCalendarFeed($userId, $schoolId, $roles, $year, $includeArchived, $begin, $end);

        // extract course/session/offering ids
        $offeringIds = array();
        $courseIds =  array();
        $sessionIds = array();

        foreach ($offerings as $offering) {
            $offeringIds[] = $offering['offering_id'];
            $courseIds[] = $offering['course_id'];
            $sessionIds[] = $offering['session_id'];
        }
        $offeringIds = array_unique($offeringIds);
        $courseIds = array_unique($courseIds);
        $sessionIds = array_unique($sessionIds);

        // retrieve associated instructors/objectives/learning materials
        $instructors = $this->getOfferingsInstructors($offeringIds);
        $courseObjectives = $this->getCoursesObjectives($courseIds);
        $courseMaterials = $this->getCoursesMaterials($courseIds);
        $sessionObjectives = $this->getSessionsObjectives($sessionIds);
        $sessionMaterials = $this->getSessionsMaterials($sessionIds);

        // attach the instructors/objectives/learning materials to the appropriate offerings
        for ($i = 0, $n = count($offerings); $i < $n; $i++) {
            @$offerings[$i]['instructors'] = $instructors[$offerings[$i]['offering_id']];
            @$offerings[$i]['course_objectives'] = $courseObjectives[$offerings[$i]['course_id']];
            @$offerings[$i]['course_materials'] = $courseMaterials[$offerings[$i]['course_id']];
            @$offerings[$i]['session_objectives'] = $sessionObjectives[$offerings[$i]['session_id']];
            @$offerings[$i]['session_materials'] = $sessionMaterials[$offerings[$i]['session_id']];
        }

        return $offerings;
    }

    /**
     * @param array $offerings An array of offering ids.
     * @return array
     */
    public function getOfferingsInstructors (array $offerings)
    {
        $rhett = array();

        if (empty($offerings)) {
{'fraction_non_alphanumeric': 0.13795321167296407, 'fraction_numerical': 0.002090200176863092, 'mean_word_length': 2.9846252402306215, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3421566', 'n_tokens_mistral': 4171, 'n_tokens_neox': 4057, 'n_words': 1012}