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);
}
}
function eF_countTableData($table, $fields = "*", $where = "", $order = "", $group = "", $limit = "")
{
$thisQuery = microtime(true);
$sql = prepareGetTableData($table, $fields, $where, $order, $group, $limit);
$result = $GLOBALS['db']->GetAll("select count(*) as count from ({$sql}) count_query");
logProcess($thisQuery, $sql);
if ($result == false) {
return array();
} else {
return $result;
}
}
