/** * 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; }