public static function getAllCourses($constraints = array()) { $select['main'] = 'c.id'; $select['has_instances'] = ""; //Must be here, even if empty $select['num_lessons'] = "(select count( * ) from lessons_to_courses cl, lessons l where cl.courses_ID=c.id and l.archive=0 and l.id=cl.lessons_ID) as num_lessons"; $select['num_students'] = EfrontCourse::setCourseUserSelection($constraints); if (G_VERSIONTYPE == 'enterprise') { #cpp#ifdef ENTERPRISE $select['num_skills'] = "(select count( * ) from module_hcd_course_offers_skill s where courses_ID=c.id) as num_skills"; $select['location'] = "(select b.name from module_hcd_branch b, module_hcd_course_to_branch cb where cb.branches_ID=b.branch_ID and cb.courses_ID=c.id limit 1) as location"; } #cpp#endif $select = EfrontCourse::convertCourseConstraintsToRequiredFields($constraints, $select); list($where, $limit, $orderby) = EfrontCourse::convertCourseConstraintsToSqlParameters($constraints); //$result = eF_getTableData("courses c", $select, implode(" and ", $where), $orderby, false, $limit); //WITH THIS NEW QUERY, WE GET THE SLOW 'has_instances' PROPERTY AFTER FILTERING $tables = "courses c"; $from = array("courses.*", "t.*"); if (in_array('has_instances', array_keys($select))) { unset($select['has_instances']); $from[] = "(select count(id) from courses c1 where c1.instance_source=courses.id and c1.archive=0) as has_instances"; $from[] = "(select count(id) from courses c1 where c1.instance_source=courses.id and c1.archive=0 and c1.active=1 and c1.show_catalog=1) as has_instances_show_in_catalog"; } if (isset($constraints['branch_url']) && $_SESSION['s_current_branch']) { $tables .= ' LEFT OUTER JOIN module_hcd_course_to_branch cb on cb.courses_ID=c.id'; } $sql = prepareGetTableData($tables, implode(",", $select), implode(" and ", $where), $orderby, false, $limit); $result = eF_getTableData("courses, ({$sql}) t", implode(",", $from), "courses.id=t.id"); if (!isset($constraints['return_objects']) || $constraints['return_objects'] == true) { return EfrontCourse::convertDatabaseResultToCourseObjects($result); } else { return EfrontCourse::convertDatabaseResultToCourseArray($result); } }
/** * The same as self::getUserCoursesAggregatingResultsIncludingUnassigned, only it has an addition "where" condition * @param array $constraints * @return array * @since 3.6.2 */ public function getUserCoursesAggregatingResults($constraints = array()) { !empty($constraints) or $constraints = array('archive' => false, 'active' => true); $select['main'] = 'c.id'; $select['user_type'] = "(select user_type from users_to_courses uc1 where users_login='******'login'] . "' and uc1.courses_ID=c.id) as user_type"; $select['score'] = "(select max(score) \t from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID) as score"; $select['completed'] = "(select max(completed) from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID) as completed"; $select['to_timestamp'] = "(select max(to_timestamp) from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID) as to_timestamp"; $select['active_in_course'] = "(select max(from_timestamp) from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID) as active_in_course"; $select['has_course'] = "(select count(*) > 0 from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID) as has_course"; $select['num_lessons'] = "(select count( * ) from lessons_to_courses cl, lessons l where cl.courses_ID=c.id and l.archive=0 and l.id=cl.lessons_ID) as num_lessons"; $select['num_students'] = "(select count( * ) from users_to_courses uc, users u where uc.courses_ID=c.id and u.archive=0 and u.login=uc.users_LOGIN and u.user_type='student') as num_students"; if (G_VERSIONTYPE == 'enterprise') { #cpp#ifdef ENTERPRISE $select['num_skills'] = "(select count( * ) from module_hcd_course_offers_skill s where courses_ID=c.id) as num_skills"; $select['location'] = "(select b.name from module_hcd_branch b, module_hcd_course_to_branch cb where cb.branches_ID=b.branch_ID and cb.courses_ID=c.id limit 1) as location"; } #cpp#endif $select = EfrontCourse::convertCourseConstraintsToRequiredFields($constraints, $select); list($where, $limit, $orderby) = EfrontCourse::convertCourseConstraintsToSqlParameters($constraints); if (isset($constraints['active']) && $constraints['active']) { $activeSql = 'and c1.active=1'; } else { if (isset($constraints['active']) && !$constraints['active']) { $activeSql = 'and c1.active=0'; } else { $activeSql = ''; } } $where[] = "(select count(*) > 0 from users_to_courses uc1, courses c1 where uc1.users_login='******'login'] . "' and uc1.archive=0 {$activeSql} and c1.archive = 0 and (c1.instance_source=c.id or c1.id=c.id) and c1.id=uc1.courses_ID)=1"; //WITH THIS NEW QUERY, WE GET THE SLOW 'has_instances' PROPERTY AFTER FILTERING $sql = prepareGetTableData("courses c left outer join (select id from courses) r on c.id=r.id", implode(",", $select), implode(" and ", $where), $orderby, false, $limit); $result = eF_getTableData("courses, ({$sql}) t", "courses.*, (select count(id) from courses c1 where c1.instance_source=courses.id ) as has_instances, t.*", "courses.id=t.id"); //THIS WAS THE OLD QUERY, MUCH SLOWER //$result = eF_getTableData("courses c left outer join (select id from courses) r on c.id=r.id", $select, implode(" and ", $where), $orderby, false, $limit); if (!isset($constraints['return_objects']) || $constraints['return_objects'] == true) { return EfrontCourse::convertDatabaseResultToCourseObjects($result); } else { return EfrontCourse::convertDatabaseResultToCourseArray($result); } }
public function getGroupUserCourses($constraints = array()) { $select['main'] = 'c.*'; $select['has_instances'] = "(select count( * ) from courses l where instance_source=c.id) as has_instances"; $select['num_lessons'] = "(select count( * ) from lessons_to_courses cl, lessons l where cl.courses_ID=c.id and l.archive=0 and l.id=cl.lessons_ID) as num_lessons"; // num_students: assigned+completed $select['num_students'] = "(select count( * ) from users_to_courses uc, users u where uc.courses_ID=c.id and u.archive=0 and uc.archive=0 and u.login=uc.users_LOGIN and u.user_type='student') as num_students"; $select['num_assigned'] = "(select count( * ) from users_to_courses uc, users u, users_to_groups ug where uc.courses_ID=c.id and u.archive=0 and uc.archive=0 and u.login=uc.users_LOGIN and u.user_type='student' and uc.completed=0 and u.login=ug.users_LOGIN and ug.groups_ID = " . $this->group['id'] . ") as num_assigned"; $select['num_completed'] = "(select count( * ) from users_to_courses uc, users u, users_to_groups ug where uc.courses_ID=c.id and u.archive=0 and uc.archive=0 and u.login=uc.users_LOGIN and u.user_type='student' and uc.completed=1 and u.login=ug.users_LOGIN and ug.groups_ID = " . $this->group['id'] . ") as num_completed"; if (G_VERSIONTYPE == 'enterprise') { #cpp#ifdef ENTERPRISE $select['num_skills'] = "(select count( * ) from module_hcd_course_offers_skill s where courses_ID=c.id) as num_skills"; $select['location'] = "(select b.name from module_hcd_branch b, module_hcd_course_to_branch cb where cb.branches_ID=b.branch_ID and cb.courses_ID=c.id limit 1) as location"; } #cpp#endif $select = EfrontCourse::convertCourseConstraintsToRequiredFields($constraints, $select); list($where, $limit, $orderby) = EfrontCourse::convertCourseConstraintsToSqlParameters($constraints); $result = eF_getTableData("courses c", implode(",", $select), implode(" and ", $where), $orderby, false, $limit); if (!isset($constraints['return_objects']) || $constraints['return_objects'] == true) { return EfrontCourse::convertDatabaseResultToCourseObjects($result); } else { return $result; } }