function get_list($query)
{
global $db;
try {
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
}
}
function update_signup_dates($gradeList, $modeList, $startList, $endList)
{
global $db;
$db->beginTransaction();
try {
for ($i = 0; $i < count($gradeList); $i++) {
update_signup_date_for_grade($gradeList[$i], $modeList[$i], $startList[$i], $endList[$i]);
}
$db->commit();
} catch (PDOException $e) {
$db->rollback();
display_db_exception($e);
exit;
}
}
function course_interest_download()
{
global $db;
$query = "select u.usr_id, u.usr_last_name, u.usr_first_name, x.course_id, c.course_name\n from elect_student_course_xref x, elect_course c, user u\n where x.course_id = c.course_id\n and x.usr_id = u.usr_id";
try {
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
/**
* Created by PhpStorm.
* User: macbook
* Date: 3/9/16
* Time: 9:20 AM
*/
function get_log_messages($app_cde)
{
$query = "select log_id, log_lvl_cde, log_msg, log_src, log_pdo_file, log_pdo_line, log_dt, user.usr_id, concat (usr_last_name, ', ' ,usr_first_name) as name\n from log\n left join user on log.usr_id = user.usr_id\n where app_cde = :app_cde\n order by log_id desc\n limit 200";
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(":app_cde", $app_cde);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function update_teacher_sessions($presId, $usrId, $sesId, $currentUserId)
{
$query = 'call insert_update_delete_user_pres(:presId, :usrId, :sesId, :currentUserId);';
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(":usrId", $usrId, PDO::PARAM_INT);
$statement->bindValue(":presId", $presId, PDO::PARAM_INT || PDO::PARAM_NULL);
$statement->bindValue(":sesId", $sesId, PDO::PARAM_INT);
$statement->bindValue(":currentUserId", $currentUserId, PDO::PARAM_INT);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_session_by_room($rm_id, $ses_id)
{
$query = 'select p.ses_id, pres_title, organization, get_full_name_presenters_comma_list (p.pres_id) full_presenters, ses_start
from presentation p, session_times s
where p.ses_id = s.ses_id
and rm_id = :rm_id
and p.ses_id = :ses_id';
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(':rm_id', $rm_id);
$statement->bindValue(':ses_id', $ses_id);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
public static function deletePresentationsByUser($usr_id, $pres_id)
{
$query = 'delete from user_presentation_xref
where pres_id = :pres_id
and usr_id = :usr_id';
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(":pres_id", $pres_id);
$statement->bindValue(":usr_id", $usr_id);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function clear_signups()
{
global $db;
$query = 'delete from pres_user_xref';
try {
$statement = $db->prepare($query);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function delete_admin($usr_id, $usr_role_cde)
{
$query = 'delete from role_application_user_xref
where usr_role_cde = :usr_role_cde
and app_cde = :app_cde
and usr_id = :usr_id';
global $db;
global $app_cde;
try {
$statement = $db->prepare($query);
$statement->bindValue(":usr_role_cde", $usr_role_cde);
$statement->bindValue(":usr_id", $usr_id);
$statement->bindValue(":app_cde", $app_cde);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_sessions_by_user($usr_id)
{
$query = 'select session_times.ses_id ses_times, my_ses.ses_id ses_id, pres_room, pres_max_capacity, pres_enrolled_count, pres_id, mentor_position, mentor_company, mentor_field, mentor_last_name, mentor_first_name, ses_name, ses_start, ses_end, session_times.sort_order
from session_times
left join (
select ses_id, pres_room, mentor_position, mentor_field, mentor_company, mentor_last_name, mentor_first_name, pres_max_capacity, pres_enrolled_count, presentation.pres_id
from presentation
inner join mentor on mentor.mentor_id = presentation.mentor_id
inner join pres_user_xref on presentation.pres_id = pres_user_xref.pres_id
where usr_id = :usr_id) my_ses on session_times.ses_id = my_ses.ses_id
order by session_times.sort_order';
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(':usr_id', $usr_id);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_presenter_teammate_ids($pres_id)
{
$query = "select GROUP_CONCAT( concat ('\\'', usr_id, '\\'') SEPARATOR ', ') as ids\n from user_presentation_xref\n where pres_id = :pres_id\n and presenting = 1\n and usr_id != :usr_id\n group by pres_id";
global $db;
global $user;
try {
$statement = $db->prepare($query);
$statement->bindValue(':pres_id', $pres_id, PDO::PARAM_INT);
$statement->bindValue(':usr_id', $user->usr_id, PDO::PARAM_INT);
$statement->execute();
$result = $statement->fetch()['ids'];
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_presentation_list($mentor_id, $ses_id)
{
$query = 'SELECT mentor.mentor_id, mentor_last_name, mentor_first_name, mentor_position, mentor_company,
pres_room, pres_host_teacher, pres_max_capacity, ses_start, ses_end, ses_name, pres_enrolled_count,
presentation.pres_id
from mentor
inner join presentation on presentation.mentor_id = mentor.mentor_id
inner join session_times on session_times.ses_id = presentation.ses_id
where mentor.active = 1';
if ($mentor_id != 'All') {
$query .= " and mentor.mentor_id = :mentor_id";
}
if ($ses_id != 'All') {
$query .= " and presentation.ses_id= :ses_id";
}
$query .= " order by mentor_last_name, mentor_first_name, session_times.sort_order";
global $db;
try {
$statement = $db->prepare($query);
if ($mentor_id != 'All') {
$statement->bindValue(':mentor_id', $mentor_id);
}
if ($ses_id != 'All') {
$statement->bindValue(':ses_id', $ses_id);
}
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function update_pres_null_rm($rm_id)
{
global $db;
global $app_cde;
$update_table = $app_cde == 'TPOR' ? 'test' : 'presentation';
$query = 'update ' . $update_table . ' set
rm_id = null
where rm_id = :rm_id';
try {
$statement = $db->prepare($query);
$statement->bindValue('rm_id', $rm_id);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function delete_mentor($mentor_id)
{
global $db;
$query = 'select pres_id from presentation where mentor_id = :mentor_id';
try {
$db->beginTransaction();
$statement = $db->prepare($query);
$statement->bindValue(':mentor_id', $mentor_id);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
foreach ($result as $id) {
$query = 'delete from pres_user_xref where pres_id = :pres_id';
$statement = $db->prepare($query);
$statement->bindValue(':pres_id', $id['pres_id']);
$statement->execute();
$statement->closeCursor();
}
$query = 'delete from presentation where mentor_id = :mentor_id';
$statement = $db->prepare($query);
$statement->bindValue(':mentor_id', $mentor_id);
$statement->execute();
$statement->closeCursor();
$query = 'delete from mentor where mentor_id = :mentor_id';
$statement = $db->prepare($query);
$statement->bindValue(':mentor_id', $mentor_id);
$statement->execute();
$statement->closeCursor();
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
display_db_exception($e);
exit;
}
}
function clear_all_courses()
{
global $db;
$db->beginTransaction();
try {
$query = "delete from elect_course";
//Lol time to break the app
$statement = $db->prepare($query);
$statement->execute();
$statement->closeCursor();
$db->commit();
} catch (PDOException $e) {
$db->rollback();
display_db_exception($e);
exit;
}
}
function update_session_times($start1, $end1, $start2, $end2)
{
global $db;
$db->beginTransaction();
try {
update_session_times_for_session(1, $start1, $end1);
update_session_times_for_session(2, $start2, $end2);
$db->commit();
} catch (PDOException $e) {
$db->rollback();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_active_tests_teachers()
{
global $db;
$query = 'SELECT distinct t.test_name, td.test_time_desc, usr_last_name, usr_first_name
FROM user, test_updt_xref tx, test_time td, test t
WHERE t.test_id = tx.test_id AND td.test_time_id = tx.test_time_id
AND tx.usr_id = user.usr_id
ORDER BY test_name, test_time_desc, usr_last_name, usr_first_name';
try {
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
}
}
function convert_times_arr($inputTimesArr)
{
global $db;
$query = "insert into elect_user_free_xref (usr_id, time_id)\n values (13, 9)";
try {
$statement = $db->prepare($query);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
$usr_id = $result['usr_id'];
return $usr_id;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_students_in_ses($pres_id)
{
$query = 'SELECT usr_last_name, usr_first_name, usr_grade_lvl, academy_cde
from pres_user_xref x, user u
where x.usr_id = u.usr_id
and x.pres_id = :pres_id
and usr_grade_lvl != 13
order by usr_last_name, usr_first_name';
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(':pres_id', $pres_id);
$statement->execute();
$result = $statement->fetchAll();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function edit_course($course_id, $new_course_name, $new_course_desc)
{
global $db;
echo $course_id;
echo $new_course_desc;
echo $new_course_name;
$query = "UPDATE elect_course\n SET course_name = :new_course_name, course_desc = :new_course_desc\n WHERE course_id = :course_id";
try {
$statement = $db->prepare($query);
$statement->bindValue(':new_course_name', $new_course_name);
$statement->bindValue(':new_course_desc', $new_course_desc);
$statement->bindValue(':course_id', $course_id);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function delete_field($field_id)
{
global $db;
$query = "DELETE FROM field\n WHERE field_id = :field_id";
try {
$statement = $db->prepare($query);
$statement->bindValue(":field_id", $field_id);
$statement->execute();
$statement->closeCursor();
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
function get_csv_list($query, $grade = null)
{
global $db;
try {
$statement = $db->prepare($query);
if ($grade != 0) {
$statement->bindValue(':usr_grade_lvl', $grade);
}
$statement->execute();
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
private static function getUserByColumn($whereColumn, $whereCriteria)
{
$query = 'SELECT usr_id, usr_first_name, usr_last_name, usr_display_name,
usr_grade_lvl, usr_bca_id, user_email, usr_type_cde,
usr_class_year, academy_cde, ps_id, usr_active
FROM user
WHERE user.' . $whereColumn . ' = :' . $whereColumn;
global $db;
try {
$statement = $db->prepare($query);
$statement->bindValue(':' . $whereColumn, $whereCriteria);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
$u = new User($result["usr_id"], $result["usr_first_name"], $result["usr_last_name"], $result["usr_display_name"], $result["usr_grade_lvl"], $result["usr_bca_id"], $result["user_email"], $result["usr_type_cde"], $result["usr_class_year"], $result["academy_cde"], $result["ps_id"], $result["usr_active"]);
$u->loadRoles();
return $u;
} catch (PDOException $e) {
display_db_exception($e);
exit;
}
}
public function addPresForUser($usr_id)
{
// begin transaction
global $db;
$db->beginTransaction();
try {
// Check if the user currently has a presentation for this session.
// If so, remove it.
// The loop is a bit of overkill, but in case of an erroneous situation of the user having multiple
// presentations for the same session, this will remove those extra cases.
//
// Include a check to make sure that presenters don't overwrite their presentations through this method.
$presentations = get_presentations_by_user_by_session($usr_id, $this->ses_id);
foreach ($presentations as $presentation) {
Presentation::deletePresentationsByUser($usr_id, $presentation['pres_id']);
}
// Inserts the new session for the user.
$this->insert_presentation_for_user($usr_id);
// commit transaction
$db->commit();
} catch (PDOException $e) {
// roll back transaction
$db->rollback();
// log any errors to file
log_pdo_exception($e, $usr_id, "Adding Presentation:" . $this, "addPresForUser");
display_db_exception($e);
// display_error("Error saving data.");
exit;
}
}
function get_course($course_id)
{
global $db;
$query = 'SELECT course_id, course_name, course_nbr, course_short_name,
course_desc, teacher_id, course_type_cde, room
from course
where course_id = :course_id
order by course_nbr';
try {
$statement = $db->prepare($query);
$statement->bindValue(':course_id', $course_id);
$statement->execute();
$result = $statement->fetch();
$statement->closeCursor();
return $result;
} catch (PDOException $e) {
display_db_exception($e);
}
}