/**
* Prepares the sql request to select courses in database.
*
* @return string $sql
*/
function prepare_get_filtred_course_list()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$sqlFilter = array();
// Prepare filter deal with KEY WORDS classification call
if (isset($_SESSION['admin_course_search'])) {
$sqlFilter[] = "( co.`intitule` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_search'])) . "%'" . "\n" . " OR co.`administrativeNumber` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_search'])) . "%'" . "\n" . ")";
}
// Deal with ADVANCED SEARCH parmaters call
if (isset($_SESSION['admin_course_intitule']) && !empty($_SESSION['admin_course_intitule'])) {
$sqlFilter[] = "(co.`intitule` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_intitule'])) . "%')";
}
if (isset($_SESSION['admin_course_code']) && !empty($_SESSION['admin_course_code'])) {
$sqlFilter[] = "(co.`administrativeNumber` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_code'])) . "%')";
}
if (isset($_SESSION['admin_course_language'])) {
$sqlFilter[] = "(co.`language` = '" . claro_sql_escape($_SESSION['admin_course_language']) . "')";
}
if (isset($_SESSION['admin_course_visibility'])) {
if ($_SESSION['admin_course_visibility'] == 'invisible') {
$sqlFilter[] = "co.`visibility`='INVISIBLE'";
} elseif ($_SESSION['admin_course_visibility'] == 'visible') {
$sqlFilter[] = "co.`visibility`='VISIBLE'";
}
}
if (isset($_SESSION['admin_course_access'])) {
if ($_SESSION['admin_course_access'] == 'public') {
$sqlFilter[] = "co.`access`='public'";
} elseif ($_SESSION['admin_course_access'] == 'private') {
$sqlFilter[] = "co.`access`='private'";
} elseif ($_SESSION['admin_course_access'] == 'platform') {
$sqlFilter[] = "co.`access`='platform'";
}
}
if (isset($_SESSION['admin_course_subscription'])) {
if ($_SESSION['admin_course_subscription'] == 'allowed') {
$sqlFilter[] = "co.`registration`='OPEN'";
} elseif ($_SESSION['admin_course_subscription'] == 'denied') {
$sqlFilter[] = "co.`registration`='CLOSE'";
} elseif ($_SESSION['admin_course_subscription'] == 'key') {
$sqlFilter[] = "co.`registration`='OPEN' AND CHAR_LENGTH(co.`registrationKey`) != 0";
}
}
// Create the WHERE clauses
$sqlFilter = sizeof($sqlFilter) ? "WHERE " . implode(" AND ", $sqlFilter) : "";
// Build the complete SQL request
$sql = "SELECT co.`cours_id` AS `id`, " . "\n" . "co.`administrativeNumber` AS `officialCode`, " . "\n" . "co.`intitule` AS `intitule`, " . "\n" . "co.`code` AS `sysCode`, " . "\n" . "co.`sourceCourseId` AS `sourceCourseId`, " . "\n" . "co.`isSourceCourse` AS `isSourceCourse`, " . "\n" . "co.`visibility` AS `visibility`, " . "\n" . "co.`access` AS `access`, " . "\n" . "co.`registration` AS `registration`, " . "\n" . "co.`registrationKey` AS `registrationKey`, " . "\n" . "co.`directory` AS `repository`, " . "\n" . "co.`status` AS `status` " . "\n" . "FROM `" . $tbl_mdb_names['course'] . "` AS co " . "\n" . $sqlFilter;
return $sql;
}
/**
*
* @todo: the name would be review befor move to a lib
* @todo: eject usage in function of $_SESSION
*
* @return sql statements
*/
function get_sql_filtered_user_list()
{
if (isset($_SESSION['admin_user_action'])) {
switch ($_SESSION['admin_user_action']) {
case 'plateformadmin':
$filterOnStatus = 'plateformadmin';
break;
case 'createcourse':
$filterOnStatus = 'createcourse';
break;
case 'followcourse':
$filterOnStatus = 'followcourse';
break;
case 'all':
$filterOnStatus = '';
break;
default:
trigger_error('admin_user_action value unknow : ' . var_export($_SESSION['admin_user_action'], 1), E_USER_NOTICE);
$filterOnStatus = 'followcourse';
}
} else {
$filterOnStatus = '';
}
$tbl_mdb_names = claro_sql_get_main_tbl();
$sql = "SELECT U.user_id AS user_id,\n U.nom AS name,\n U.prenom AS firstname,\n U.authSource AS authSource,\n U.email AS email,\n U.officialCode AS officialCode,\n U.phoneNumber AS phoneNumber,\n U.pictureUri AS pictureUri,\n U.creatorId AS creator_id,\n U.isCourseCreator ,\n U.isPlatformAdmin AS isPlatformAdmin\n FROM `" . $tbl_mdb_names['user'] . "` AS U\n WHERE 1=1 ";
//deal with admin user search only
if ($filterOnStatus == 'plateformadmin') {
$sql .= " AND U.isPlatformAdmin = 1";
}
//deal with KEY WORDS classification call
if (isset($_SESSION['admin_user_search'])) {
$sql .= " AND (U.nom LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_search'])) . "%'\n OR U.prenom LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_search'])) . "%' ";
$sql .= " OR U.email LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_search'])) . "%'";
$sql .= " OR U.username LIKE '" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_search'])) . "%'";
$sql .= " OR U.officialCode = '" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_search'])) . "')";
}
//deal with ADVANCED SEARCH parameters call
if (isset($_SESSION['admin_user_firstName']) && !empty($_SESSION['admin_user_firstname'])) {
$sql .= " AND (U.prenom LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_firstName'])) . "%') ";
}
if (isset($_SESSION['admin_user_lastName']) && !empty($_SESSION['admin_user_lastName'])) {
$sql .= " AND (U.nom LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_lastName'])) . "%') ";
}
if (isset($_SESSION['admin_user_userName']) && !empty($_SESSION['admin_user_userName'])) {
$sql .= " AND (U.username LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_userName'])) . "%') ";
}
if (isset($_SESSION['admin_user_officialCode']) && !empty($_SESSION['admin_user_officialCode'])) {
$sql .= " AND (U.officialCode LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_officialCode'])) . "%') ";
}
if (isset($_SESSION['admin_user_mail']) && !empty($_SESSION['admin_user_mail'])) {
$sql .= " AND (U.email LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_user_mail'])) . "%') ";
}
if ($filterOnStatus == 'createcourse') {
$sql .= " AND (U.isCourseCreator=1)";
} elseif ($filterOnStatus == 'followcourse') {
$sql .= " AND (U.isCourseCreator=0)";
}
return $sql;
}
