public function index() { // ______________________________ // | School Year | Level | Action | // |---------------------|--------| // | | | [add] | // |------------------------------| // | 2015/2016 | | details| // ------------------------------ // /* CONFIGURES DROP DOWN Menus (2 in this case: programs and genre) */ $connection = new database(); $table = new simple_table_ops(); $content = "<div class='third_left'><p>Here are the class hours. If you change when a class starts/ends, change it here, but<p>DO NOT change the ORDER!"; $sql = 'SELECT time_id, time_class FROM time ORDER BY time_id ASC'; $levels_result = $connection->query($sql); $drop_down = array('time_id' => array('time_class' => $levels_result)); $table->set_drop_down($drop_down); /********************************************************************/ /* CONFIGURES Form structure */ $top_form = array('action' => '?controller=time&action=add', 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'time_id'), 2 => array('submit' => 'add'))); $table->set_top_form($top_form); /********************************************************************/ /* CONFIGURES Main table contents (from MySQL) */ $columns = array('Time', 'Action'); $table->set_html_table_column_names($columns); $sql = "SELECT time.time_id, time_class\n FROM time\n ORDER BY time_id ASC\n "; $result = $connection->query($sql); if ($connection->get_row_num() == 0) { $content .= "<p>Currently, you have no time configured. Choose one and click [ADD]."; } else { $content .= "<p>Currently, you have " . $connection->get_row_num() . " time configured."; } $content .= "</div>"; $details_link = array(1 => array('details', '?controller=time&action=details&id=')); $table->set_details_link($details_link); $table->set_main_table($result); /********************************************************************/ $content .= "<div class='third_middle'><table>{$table->get_table()}</table></div>"; $output['content'] = $content; return $output; }
public function index() { // ______________________________ // | School Year | Level | Action | // |---------------------|--------| // | | | [add] | // |------------------------------| // | 2015/2016 | | details| // ------------------------------ // /* CONFIGURES DROP DOWN Menus (2 in this case: programs and genre) */ $connection = new database(); $table = new simple_table_ops(); $content = '<p>Here you can add courses to the school year ' . $_SESSION['current_school_year'] . '.<p>Each course is a level of school grade.'; $sql = 'SELECT level_id, level FROM levels ORDER BY level_id ASC'; $levels_result = $connection->query($sql); $drop_down = array('level_id' => array('level' => $levels_result)); $table->set_drop_down($drop_down); /********************************************************************/ /* CONFIGURES Form structure */ $top_form = array('action' => '?controller=courses&action=add', 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'level_id'), 2 => array('submit' => 'add'))); $table->set_top_form($top_form); /********************************************************************/ /* CONFIGURES Main table contents (from MySQL) */ $columns = array('Level', 'Action'); $table->set_html_table_column_names($columns); $sql = "SELECT courses.course_id, levels.level\n FROM courses\n JOIN levels ON courses.level_id = levels.level_id\n WHERE courses.school_year_id={$_SESSION['current_school_year_id']}\n "; $result = $connection->query($sql); if ($connection->get_row_num() == 0) { $content .= "<p>Currently, you have no courses configured. Choose one and click [ADD]."; } else { $content .= "<p>Currently, you have " . $connection->get_row_num() . " courses configured."; } $details_link = array(1 => array('details', '?controller=courses&action=details&id=')); $table->set_details_link($details_link); $table->set_main_table($result); /********************************************************************/ $content .= "<div class='submit_top_left'><table>{$table->get_table()}</table></div>"; $output['content'] = $content; return $output; }
public function delete() { // When deleting check if the selected school_year_id is present in courses table // if yes, do not delete, and issue a warning $content = ''; $delete_handler = new database(); $sql = "SELECT school_year_id FROM school_years"; $result = $delete_handler->query($sql); if ($delete_handler->get_row_num() == 1) { $content .= '<p>You cannot delete the last school year!'; $output['content'] = $content; return $output; } $sql = "SELECT school_year_id FROM courses WHERE school_year_id={$_GET['id']}"; echo $sql . '<br>'; $result = $delete_handler->query($sql); if ($delete_handler->get_row_num() !== 0) { $content .= '<p>You cannot delete this school year!<p>You need first to delete ALL the courses attached to this year.'; $content .= "<p>There are currently {$delete_handler->get_row_num()} courses."; $output['content'] = $content; return $output; } $table = new simple_table_ops(); $table->set_id_column('school_year_id'); $table->set_table_name('school_years'); $table->delete(); // Check that when deleting, $_SESSION['current_school_year_id'] should be updated // Forbid last school_year deletion if last in school_years $sql = "SELECT MAX(school_year_id), school_year FROM school_years"; $school_years_handle = new database(); $new_school_year_result = $school_years_handle->query($sql); $new_school_year_id = $new_school_year_result[0]['MAX(school_year_id)']; $new_school_year = $new_school_year_result[0]['school_year']; $_SESSION['current_school_year_id'] = $new_school_year_id; $_SESSION['current_school_year'] = $new_school_year; header("Location: http://" . WEBSITE_URL . "/index.php?controller={$_GET['controller']}&action=index"); }
public function details() { //TODO: details in timetable $connection = new database(); $table = new simple_table_ops(); $id = $_GET['id']; // timetable_id $content = "<div class='link_button'>\n <a href='?controller=teachers&action=export'>Export to EXCEL</a>\n <a href='?controller=curricula&action=index'>Curricula</a>\n </div>"; $content .= "<div class='third_left'>"; $content .= '<p>You can configure the timetable for the following course:<p>'; $sql = "SELECT curricula.curriculum_id, CONCAT (teachers.nom, ' ', teachers.prenom, ' | ', teachers.nom_khmer, ' ', teachers.prenom_khmer, ' | ', sexes.sex) as teacher, subjects.subject, levels.level\n FROM curricula\n JOIN courses ON curricula.course_id = courses.course_id\n JOIN subjects ON curricula.subject_id = subjects.subject_id\n JOIN teachers ON teachers.teacher_id = curricula.teacher_id\n JOIN sexes ON teachers.sex_id = sexes.sex_id\n JOIN levels ON courses.level_id = levels.level_id\n JOIN timetables ON timetables.curriculum_id = curricula.curriculum_id\n WHERE timetables.timetable_id = {$_GET['id']}"; $curricula_data = $connection->query($sql); if ($connection->get_row_num() == 0) { header("Location: http://" . WEBSITE_URL . "/index.php?controller=curricula&action=index"); } $curricula_data = $curricula_data[0]; $content .= 'Teacher: ' . $curricula_data['teacher'] . '<br>'; $content .= 'Subject: ' . $curricula_data['subject'] . '<br>'; $content .= 'Level: ' . $curricula_data['level'] . '<br>'; $columns = array('start_time_id, end_time_id, weekday_id, classroom_id, timetable_period_id'); $neat_columns = array('Start Time', 'End Time', 'Week Day', 'Classroom', 'Time Period', 'Update', 'Delete'); // create curriculum_id array $sql = "SELECT curriculum_id FROM timetables WHERE timetable_id = {$id}"; $curriculum_id_result = $connection->query($sql); $curriculum_id_array = $curriculum_id_result[0]; // time_id, weekday_id, curriculum_id, classroom_id, $sql = 'SELECT time_id as start_time_id, time_class as time1 FROM time ORDER BY time_id ASC'; $time1_result = $connection->query($sql); $sql = 'SELECT time_id as end_time_id, time_class as time2 FROM time ORDER BY time_id ASC'; $time2_result = $connection->query($sql); $sql = 'SELECT weekday_id, weekday FROM weekdays ORDER BY weekday_id'; $weekdays_result = $connection->query($sql); $sql = "SELECT timetable_period_id, CONCAT(nom, ', from ', date_from, ' to ', date_to) as timetable_period FROM timetable_periods ORDER BY date_from"; $timetable_periods_result = $connection->query($sql); $sql = 'SELECT classroom_id, classroom FROM classrooms ORDER BY classroom ASC'; $classrooms_result = $connection->query($sql); $drop_down = array('start_time_id' => array('start_time' => $time1_result), 'end_time_id' => array('end_time' => $time2_result), 'weekday_id' => array('weekday' => $weekdays_result), 'timetable_period_id' => array('timetable_period' => $timetable_periods_result), 'classroom_id' => array('classroom' => $classrooms_result)); /********************************************************************/ /* CONFIGURES Form structure */ $form = array('action' => '?controller=timetable&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'method' => 'post', 'action_links' => array(1 => array('delete', '?controller=timetable&action=delete&id=')), 'id' => 'top_form', 'elements' => array(1 => array('hidden' => $curriculum_id_array), 3 => array('drop_down' => 'start_time_id'), 4 => array('drop_down' => 'end_time_id'), 5 => array('drop_down' => 'weekday_id'), 6 => array('drop_down' => 'classroom_id'), 7 => array('drop_down' => 'timetable_period_id'), 10 => array('submit' => 'update'))); $table->set_top_form($form); $table->set_table_name('timetables'); $table->set_id_column('timetable_id'); $table->set_table_column_names($columns); $table->set_html_table_column_names($neat_columns); $table->set_values_form(); // set values found in database into form elements when building top_form $table->set_drop_down($drop_down); $table->set_form_array($form); $content .= "</div>"; $content .= " <div class='two_thirds_right'><table>" . $table->details() . '</table></div>'; $output['content'] = $content; return $output; }
public function update() { // update student based on $_post variables and $_get['id'] //UPDATE multiple tables: // UPDATE tables SET table1.col1=table2.col2 // WHERE condition; $sql = 'UPDATE students SET nom=?, prenom=?, nom_khmer=?, prenom_khmer=?, sex_id=?, matricule=?, dob=?, program_id=?, active_id=? WHERE student_id=?'; $nom = $_POST['nom']; $prenom = $_POST['prenom']; $nom_khmer = $_POST['nom_khmer']; $prenom_khmer = $_POST['prenom_khmer']; $sex_id = $_POST['sex_id']; $matricule = $_POST['matricule']; $dob = $_POST['dob']; $program_id = $_POST['program_id']; $active_id = $_POST['active_id']; $id = $_GET['id']; $data = array($nom, $prenom, $nom_khmer, $prenom_khmer, $sex_id, $matricule, $dob, $program_id, $active_id, $id); $connection = new database(); if ($connection->update($sql, $data)) { $content = "Affected rows: "; $content .= $connection->get_row_num(); } else { $content = "Could not update student!"; } header("Location: http://" . WEBSITE_URL . "/index.php?controller=students&action=index"); $output['content'] = $content; return $output; }
public function index() { $content = ""; /* CONFIGURES DROP DOWN Menus (2 in this case: programs and genre) */ $connection = new database(); $table = new simple_table_ops(); $sql = "SELECT courses.course_id, levels.level\n FROM courses\n JOIN levels ON courses.level_id = levels.level_id\n WHERE courses.school_year_id = {$_SESSION['current_school_year_id']}\n ORDER BY levels.level_id ASC\n\n "; $courses_result = $connection->query($sql); if ($connection->get_row_num() == 0) { // no courses were setup - redirect $content .= "<p> No courses were found for the year {$_SESSION['current_school_year']}.<p>Go to [COURSES] to add a new one."; $output['content'] = $content; return $output; } $drop_down = array('course_id' => array('course' => $courses_result)); $table->set_drop_down($drop_down); /* CONFIGURES top column in neat names and html formatted */ $neat_column_names = array('Select', 'Surname', 'Name', 'Surname Kh', 'Name Kh', 'Genre', 'Program', 'Age'); $table->set_html_table_column_names($neat_column_names); /********************************************************************/ /* CONFIGURES main left table */ // selects ALL students from students where student_id is not inside [COURSES] table // need: select students that are not found in [COURSES] of the current_year_id. /* $sql = "SELECT students.student_id, students.nom, students.prenom, students.nom_khmer, students.prenom_khmer, sexes.sex, programs.program, TIMESTAMPDIFF(YEAR,students.dob,NOW()) AS age FROM students LEFT JOIN courses ON courses.student_id=students.student_id JOIN sexes ON students.sex_id=sexes.sex_id JOIN programs ON students.program_id=programs.program_id WHERE courses.school_year_id Is Null AND students.active_id=1 ORDER BY age ASC, nom ASC, prenom ASC"; */ $sql = "SELECT students.student_id, students.nom, students.prenom, students.nom_khmer, students.prenom_khmer, sexes.sex, programs.program, TIMESTAMPDIFF(YEAR,students.dob,NOW()) AS age\n FROM students\n JOIN sexes ON students.sex_id=sexes.sex_id\n JOIN programs ON students.program_id=programs.program_id\n WHERE students.active_id=1 AND NOT students.student_id IN (\n SELECT students.student_id\n FROM students\n JOIN classes ON classes.student_id = students.student_id\n JOIN courses ON courses.course_id = classes.course_id\n JOIN school_years ON school_years.school_year_id = courses.school_year_id\n WHERE courses.school_year_id = {$_SESSION['current_school_year_id']})\n ORDER BY age ASC, students.prenom, students.nom"; // CHECK: if no result, warn that you need to add students to students table before making courses! // $content .= 'Before building courses, you need to add STUDENTS to the [STUDENTS] table!'; $result = $connection->query($sql); if ($connection->get_row_num() == 0) { // no records found - $output['menu2'] = "No free students were found. To add a new STUDENT, click <a href='?controller=students&action=index'>HERE</a>."; } $table->set_id_column('student_id'); $table->set_html_check_box($result); $content .= "<div class='submit_top_left'><table width='100%'><tr><td>" . $table->get_html_drop_down('course_id') . "</td></tr></table></div>"; $content .= "<div class='half_left'><br><br><table width='100%'>"; $content .= $table->get_html_table_column_names(); $content .= "<form action = '?controller=classes&action=move' method = 'post' id='top_form'>"; $content .= $table->get_html_check_box(); $content .= "<div class='submit_top_left2'><input type ='submit' value='Move selected STUDENTS to Course ->'></div>"; $content .= "</form></table></div>"; //TODO (secondary): // foreach (total of courses.levels_id) // select student from classes // assemble table with new tables from select and add row containing levels.level on top // alternate div class table_row1/table_row2 for each group of class // end foreach // $courses_sql = "SELECT classes.classe_id,\n levels.level,\n students.nom, students.prenom, students.nom_khmer, students.prenom_khmer,\n sexes.sex,\n programs.program\n\n FROM courses\n JOIN classes ON classes.course_id = courses.course_id\n JOIN students ON classes.student_id = students.student_id\n JOIN programs ON programs.program_id = students.program_id\n JOIN sexes ON sexes.sex_id = students.sex_id\n JOIN school_years ON school_years.school_year_id = courses.school_year_id\n JOIN levels ON levels.level_id = courses.level_id\n WHERE courses.school_year_id = " . $_SESSION['current_school_year_id'] . "\n ORDER BY school_years.school_year DESC, levels.level_id ASC, students.nom ASC, students.prenom ASC\n "; // TODO: priority high - generate tables per level_id $courses_table = new simple_table_ops(); $columns = array('Surname', 'Name', 'Surname Kh', 'Name Kh', 'Genre', 'Program', 'Action'); $courses_table->set_html_table_column_names($columns); $details_link = array(1 => array('details', '?controller=classes&action=details&id=')); $courses_table->set_details_link($details_link); $content .= "<div class='half_right'>"; $sql = "SELECT level_id FROM courses GROUP BY level_id ORDER BY level_id"; $levels_result = $connection->query($sql); foreach ($levels_result as $row) { $content .= "<table width='100%'>"; foreach ($row as $field => $value) { $courses_sql = "SELECT classes.classe_id,\n\n students.nom, students.prenom, students.nom_khmer, students.prenom_khmer,\n sexes.sex,\n programs.program\n\n FROM courses\n JOIN classes ON classes.course_id = courses.course_id\n JOIN students ON classes.student_id = students.student_id\n JOIN programs ON programs.program_id = students.program_id\n JOIN sexes ON sexes.sex_id = students.sex_id\n JOIN school_years ON school_years.school_year_id = courses.school_year_id\n JOIN levels ON levels.level_id = courses.level_id\n WHERE courses.school_year_id = " . $_SESSION['current_school_year_id'] . " AND courses.level_id={$value}\n ORDER BY school_years.school_year DESC, levels.level_id ASC, students.nom ASC, students.prenom ASC\n "; $courses_table->set_main_table($connection->query($courses_sql)); $content .= "<tr><td colspan='7'>Grade: " . $value . "</td></tr>"; $content .= $courses_table->get_html_table_column_names(); $content .= $courses_table->get_html_main_table(); $content .= '</td></tr>'; } $content .= "</table>"; } $content .= "</div>"; /********************************************************************/ $output['content'] = $content; return $output; }
public function update() { /* UPDATE multiple tables: // UPDATE tables SET table1.col1=table2.col2 // WHERE condition; // needs: // 1. column names - $this->cols // 2. table name - $this->table_name // 3. id - $_GET['id'] from action form example: $sql = " UPDATE {$this->table_name} SET nom=?, prenom=?, nom_khmer=?, prenom_khmer=?, sex_id=?, matricule=?, dob=?, program_id=? WHERE student_id=?"; /********************************************/ $security_handler = new security(); $security_handler->check_token(); $id = $_GET['id']; $i = 0; $cols = ''; $values = array(); foreach ($this->cols as $column) { array_push($values, $_POST[$column]); $cols .= $column . '=?,'; $i++; } array_push($values, $id); // add last value to the array, which corresponds to the record id number. $cols = substr($cols, 0, -1); $sql = "UPDATE " . $this->table_name . " SET " . $cols . " WHERE " . $this->id_column . "=?"; //echo "<p>sql: ".$sql."<br>"; //echo "<p>values: "; //var_dump ($values); $connection = new database(); if ($connection->update($sql, $values)) { $_SESSION['log'] .= new timestamp("Affected rows: " . $connection->get_row_num()); } else { $_SESSION['log'] .= new timestamp("Record was not updated in {$_GET['controller']}!"); } //die(); }
public function submit() { $output = array(); if (isset($_POST['username']) and isset($_POST['password'])) { $username = trim($_POST['username']); $password = trim($_POST['password']); // TODO: implement password hashing check // $sql = 'SELECT user_id, email, profile_id, password FROM users WHERE username=?'; $data = array($username); $connection = new database(); $result = $connection->fetchAll($sql, $data); $hash = $result[0]['password']; /* REQUIRES php >= 5.5.0 if (password_verify($password, $hash)) { echo "ok"; } else { echo "not ok"; } //die(); */ // for PHP <=5.5.0 //if ($connection->get_row_num()==1 ) { if (crypt($password, MY_SALT) == $hash) { /* GATEWAY: define here all session variables based on user: * User Variables: * 1. css_username * 2. css_user_id * 3. css_email * 4. css_profile_id * System variables: * 1. main_menu * 2. upright_menu * 3. last_login * 4. controllers->actions (array) * [controller][action][permission] where [profile_id]=[user_profile_id] * 5. current_school_year -> max school year. If NO school year is configured, insert current year and select it. */ $_SESSION['css_username'] = $username; $_SESSION['log'] .= new timestamp("user {$username} has logged in"); $_SESSION['css_user_id'] = $result[0]['user_id']; $_SESSION['css_email'] = $result[0]['email']; $_SESSION['css_profile_id'] = $result[0]['profile_id']; $_SESSION['user_ip'] = $_SERVER['REMOTE_ADDR']; // small security control // Requires PECL extension to work //$country = geoip_country_name_by_name($_SESSION['user_ip']); $sql = "INSERT INTO login_activity (user_id, profile_id, username, email, ip_address) VALUES ('" . $result[0]['user_id'] . "', '" . $result[0]['profile_id'] . "', '" . $username . "', '" . $result[0]['email'] . "', '" . $_SESSION['user_ip'] . "')"; $login_activity = $connection->query($sql); // TODO: acl structure // results comes as: // $acl_results = array ( // 0 => array ( // 'controller' => 'about', // 'c_action' => 'index', // 'active_id' => 1), // 1 => array (.... // Refactor to: // $acl_results_refactored = array( // 'about' => array('index' => 1), // // 'admin' => array('index' => 1, // 'log' => 1, // 'users_index' => 1)); // 1. extract all controllers from DB which corresponds to user // 2. foreach $controllers['controller'] add $c_action and corresponding permission $sql = "SELECT controllers.controller, controllers.c_action, acl.active_id\n FROM acl\n JOIN controllers ON controllers.controller_id = acl.controller_id\n WHERE acl.profile_id=?\n GROUP BY controllers.controller_id ASC\n "; $data = array($_SESSION['css_profile_id']); $acl_results = $connection->fetchAll($sql, $data); //var_dump($acl_results); $acl_map = array(); $i = 0; foreach ($acl_results as $row) { $acl_map[$row['controller'] . '.' . $row['c_action']] = $row['active_id']; // preferable way to add a single row to an existing array $i++; } //var_dump ($acl_map); $_SESSION['acl_map'] = $acl_map; $date = new DateTime(); $_SESSION['last_login'] = $date->format('U'); $sql = "SELECT school_year_id, school_year\n FROM school_years\n ORDER BY school_year DESC\n LIMIT 1"; $school_years_result = $connection->query($sql); if ($connection->get_row_num() == 0) { // no school year has been registered, INSERT INTO school_years the current school year $date = new DateTime(); $current_year = $date->format('Y'); $current_month = $date->format('m'); if ($current_month >= 9 and $current_month <= 12) { $current_school_year = $current_year . '/' . ($current_year + 1); } else { $current_school_year = $current_year - 1 . '/' . $current_year; } $current_school_year = strval($current_school_year); $insert_school_year_sql = "INSERT INTO school_years (school_year)\n VALUES ('" . $current_school_year . "')"; $connection->query($insert_school_year_sql); // Get last school_year_id and assign to $_SESSION['current.... $_SESSION['current_school_year_id'] = $connection->last_Inserted_id(); $_SESSION['current_school_year'] = $current_school_year; } else { $_SESSION['current_school_year_id'] = $school_years_result[0]['school_year_id']; $_SESSION['current_school_year'] = $school_years_result[0]['school_year']; } // TODO: load main_menu and upright_menu htmls in $_SESSION['main_menu etc // hits DB, retrieves htmls from profiles and menus tables etc // 1. tables: profiles, menus, htmls // 2. fields: menus(menu_id, name (main, upright etc), html_id (from htmls table, sort of html library), profile_id) // from other tables, the corresponding IDs // SQL should select all html from htmls table where profile in menus table is the same as current user profile_id // $sql = 'SELECT menus.name, htmls.html from htmls JOIN menus ORDER BY menu_id WHERE $_SESSION['css_profile_id'] = menus.profile_id'; // retrieve $menu_name from query // concatenate html records sequentially (query was ordered by menu_id, which is NOT Auto-incremented) // do while etc $html; // $output [$menu_name]=$html; // TODO: retrieve controller/action permissions from profile, permissions and ctrl_actions tables // Assign $_SESSION['controller']['action'] CRUD, so index.php can check permission for // current user to execute controller/action header('Location: http://' . WEBSITE_URL . '/index.php?controller=home&action=index'); } else { // username and password do not match // return error page with link to retry $output['page'] = 'views/login/index.php'; $header = 'CSS AEC-Foyer Lataste ADTJK System V1.0'; // $content ='no matches (or more than one, which means inconsistencies in the DB!)<br>'; $content = "Credentials do not match<br><br>Click <a href='?controller=login&action=login'>here</a> to retry<br><br>"; $footer = 'CSS AEC-Foyer Lataste ADTJK Copyright and stuff. Webmastermind: ivan.bragatto@gmail.com'; $output['header'] = $header; $output['content'] = $content; $output['footer'] = $footer; } } return $output; }
public function setschoolyear() { // set school year : comes from drop down $_POST if (isset($_POST['school_year_id'])) { if (is_numeric($_POST['school_year_id'])) { $school_year_handle = new database(); $sql = "SELECT school_year FROM school_years WHERE school_year_id=?"; $data = array($_POST['school_year_id']); $result = $school_year_handle->fetchAll($sql, $data); if ($school_year_handle->get_row_num() == 1) { $_SESSION['current_school_year_id'] = $_POST['school_year_id']; $_SESSION['current_school_year'] = $result[0]['school_year']; } else { } } } //echo $_SESSION['school_year'].'<br>'; //var_dump ($result); //die(); header("Location: http://" . WEBSITE_URL . "/index.php?controller={$_GET['controller']}&action=index"); }