public function details()
{
// details combines seeing with editing
$id = $_GET['id'];
$columns = array('nom, prenom, nom_khmer, prenom_khmer, sex_id, active_id');
$neat_columns = array('Last Name', 'First Name', 'Last Name Khmer', 'First Name Khmer', 'Genre', 'Active', 'Update', 'Delete');
$form = array('action' => '?controller=teachers&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('delete', '?controller=teachers&action=delete&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'nom'), 2 => array('text' => 'prenom'), 3 => array('text' => 'nom_khmer'), 4 => array('text' => 'prenom_khmer'), 5 => array('drop_down' => 'sex_id'), 6 => array('drop_down' => 'active_id'), 7 => array('submit' => 'update')));
$connection = new database();
$table = new simple_table_ops();
$sql = 'SELECT sex_id, sex FROM sexes';
$sex_result = $connection->query($sql);
$sql2 = 'SELECT active_id, active FROM actives';
$active_result = $connection->query($sql2);
$drop_down = array('sex_id' => array('sex' => $sex_result), 'active_id' => array('active' => $active_result));
$table->set_table_name('teachers');
$table->set_id_column('teacher_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 = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function details()
{
// TODO: details on timetable2 controller
$id = $_GET['id'];
$columns = array('nom, date_from, date_to');
$neat_columns = array('Timetable Name', 'Start Date', 'End Date', 'Action', 'Delete');
$form = array('action' => '?controller=timetable2&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('delete', '?controller=timetable2&action=delete&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'nom'), 2 => array('date' => 'date_from'), 3 => array('date' => 'date_to'), 4 => array('submit' => 'update')));
$table = new simple_table_ops();
$table->set_id_column('timetable_period_id');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_form_array($form);
$table->set_table_name('timetable_periods');
$table->set_values_form();
$content = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function details()
{
// details combines seeing with editing
$id = $_GET['id'];
$columns = array('weekday');
$neat_columns = array('Week Day', 'Action');
$form = array('action' => '?controller=weekdays&action=update&id=' . $id, 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'weekday'), 2 => array('submit' => 'update')));
$table = new simple_table_ops();
$table->set_id_column('weekday_id');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_form_array($form);
$table->set_table_name('weekdays');
$table->set_values_form();
$content = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function index()
{
$connection = new database();
$table = new simple_table_ops();
$content = '<p>Add or Delete a LEVEL. It corresponds to a CLASS in French. <p>BE CAREFUL! When deleting, be sure you have just created the level, and has not used it in [COURSES] table!';
/* CONFIGURES Form structure */
$top_form = array('action' => '?controller=levels&action=add', 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'level'), 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 level_id, level
FROM levels
ORDER BY LENGTH (level), level
';
$details_link = array(1 => array('delete', '?controller=levels&action=delete&id='));
$table->set_details_link($details_link);
$table->set_main_table($connection->query($sql));
/********************************************************************/
$content .= '<table>' . $table->get_table() . '</table>';
$output['content'] = $content;
return $output;
}
public function index()
{
$connection = new database();
$table = new simple_table_ops();
$content = '<p>Add or Delete a School Year. <p>BE CAREFUL! When deleting, be sure you have just created the school year, and has not used it in [COURSES] table!';
/* CONFIGURES Form structure */
$top_form = array('action' => '?controller=school_years&action=add', 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'school_year'), 2 => array('submit' => 'add')));
$table->set_top_form($top_form);
/********************************************************************/
/* CONFIGURES Main table contents (from MySQL) */
$columns = array('School Year', 'Action');
$table->set_html_table_column_names($columns);
$sql = 'SELECT school_year_id, school_year
FROM school_years
ORDER BY school_year DESC
';
$details_link = array(1 => array('delete', '?controller=school_years&action=delete&id='));
$table->set_details_link($details_link);
$table->set_main_table($connection->query($sql));
/********************************************************************/
$content .= '<table>' . $table->get_table() . '</table>';
$output['content'] = $content;
return $output;
}
public function details()
{
// details combines seeing with editing
$id = $_GET['id'];
$columns = array('level_id');
$neat_columns = array('Level', 'Action');
$form = array('action' => '?controller=courses&action=update&id=' . $id, 'div' => "class='solitary_input'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'level_id'), 2 => array('submit' => 'update')));
$connection = new database();
$table = new simple_table_ops();
$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_id_column('course_id');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_form_array($form);
$table->set_table_name('courses');
$table->set_values_form();
// set values found in database into form elements when building top_form
$table->set_drop_down($drop_down);
$content = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function show()
{
// TODO: when coming back from update, show previous timetable
$connection = new database();
$table = new simple_table_ops();
if (isset($_POST['timetable_period_id'])) {
//if (isset($_GET['timetable_period_id'])) {
//$timetable_period_id = $_GET['timetable_period_id'];
$timetable_period_id = $_POST['timetable_period_id'];
$post_values = array('timetable_period_id' => $timetable_period_id);
$table->set_values_form_manually($post_values);
}
if (isset($_GET['timetable_period_id'])) {
$timetable_period_id = $_GET['timetable_period_id'];
//$timetable_period_id = $_POST['timetable_period_id'];
$post_values = array('timetable_period_id' => $timetable_period_id);
$table->set_values_form_manually($post_values);
}
$this->timetable_period_id = $timetable_period_id;
$content = "<div class='link_button'>\n <a href='?controller=teachers&action=index'>Teacher</a>\n <a href='?controller=curricula&action=index'>Curricula</a>\n </div>";
/* CONFIGURES DROP DOWN Menu */
$sql = "SELECT timetable_period_id, CONCAT (nom, ' - ', date_from, ' to ', date_to) as period\n FROM timetable_periods\n WHERE school_year_id = {$_SESSION['current_school_year_id']}\n ";
$timetable_periods_result = $connection->query($sql);
$drop_down = array('timetable_period_id' => array('period' => $timetable_periods_result));
$table->set_drop_down($drop_down);
/********************************************************************/
/* CONFIGURES Form structure */
$top_form = array('action' => '?controller=timetable&action=show&submit=yes', 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'timetable_period_id'), 3 => array('submit' => 'Show Timetable')));
$table->set_top_form($top_form);
/********************************************************************/
$columns = array('Timetable', 'Action');
$table->set_html_table_column_names($columns);
$content .= "<table width='auto'>" . $table->get_html_table_column_names() . $table->get_form() . '</table>';
if (isset($_GET['submit'])) {
if ($_GET['submit'] == 'yes') {
$this->set_timetable_html();
$content .= $this->get_timetable_html();
}
}
$output['content'] = $content;
return $output;
}
public function details2()
{
// details combines seeing with editing
$id = $_GET['id'];
$columns = array('nom, prenom, nom_khmer, prenom_khmer, matricule, dob, program_id, sex_id, active_id');
$neat_columns = array('Last Name', 'First Name', 'Last Name Khmer', 'First Name Khmer', 'Matricule', 'Date of Birth', 'Program', 'Genre', 'Active', 'Update', 'Delete');
$form = array('action' => '?controller=students&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('delete', '?controller=students&action=delete&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'nom'), 2 => array('text' => 'prenom'), 3 => array('text' => 'nom_khmer'), 4 => array('text' => 'prenom_khmer'), 5 => array('text' => 'matricule'), 6 => array('text' => 'dob'), 7 => array('drop_down' => 'program_id'), 8 => array('drop_down' => 'sex_id'), 9 => array('drop_down' => 'active_id'), 10 => array('submit' => 'update')));
$connection = new database();
$table = new simple_table_ops();
$sql = 'SELECT sex_id, sex FROM sexes';
$sex_result = $connection->query($sql);
$sql2 = 'SELECT active_id, active FROM actives';
$active_result = $connection->query($sql2);
$sql3 = 'SELECT program_id, program FROM programs';
$programs_result = $connection->query($sql3);
$drop_down = array('sex_id' => array('sex' => $sex_result), 'active_id' => array('active' => $active_result), 'program_id' => array('program' => $programs_result));
$table->set_table_name('students');
$table->set_id_column('student_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 = '<table>';
$content .= $table->details();
$content .= '</table>';
//require_once 'models/studentsModel.php'; // needless to add require
$studentsModel_handler = new studentsModel();
$studentsModel_handler->set_student_id($id);
$content .= $studentsModel_handler->get_timetable_html();
$content .= $studentsModel_handler->get_attendance() . "<br>";
$content .= $studentsModel_handler->get_results() . "<br>";
$output['content'] = $content;
return $output;
}
public function details()
{
// details combines seeing with editing
$id = $_GET['id'];
$connection = new database();
$table = new simple_table_ops();
/* Drop down menus */
$sql = "SELECT courses.course_id, levels.level AS 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";
$levels_result = $connection->query($sql);
$sql_teachers = "SELECT teachers.teacher_id, CONCAT (teachers.nom, ' ', teachers.prenom, ' | ', teachers.nom_khmer, ' ', teachers.prenom_khmer, ' | ', sexes.sex) as teacher\n FROM teachers\n JOIN sexes ON teachers.sex_id = sexes.sex_id\n WHERE teachers.active_id=1";
$teachers_result = $connection->query($sql_teachers);
$sql = "SELECT subject_id, subject FROM subjects ORDER BY subject";
$subjects_result = $connection->query($sql);
$drop_down = array('course_id' => array('level' => $levels_result), 'teacher_id' => array('teacher' => $teachers_result), 'subject_id' => array('subject' => $subjects_result));
/* end of drop down menus definition */
$columns = array('course_id, teacher_id, subject_id');
$neat_columns = array('Course', 'Teacher', 'Subject', 'Update', 'Action');
$form = array('action' => '?controller=curricula&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('delete', '?controller=curricula&action=delete&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'course_id'), 2 => array('drop_down' => 'teacher_id'), 3 => array('drop_down' => 'subject_id'), 4 => array('submit' => 'update')));
$sql = "SELECT curricula.course_id as course_id, curricula.teacher_id as teacher_id, curricula.subject_id as subject_id\n FROM curricula\n JOIN teachers ON curricula.teacher_id = teachers.teacher_id\n JOIN subjects ON curricula.subject_id = subjects.subject_id\n JOIN courses ON curricula.course_id = courses.course_id\n WHERE curricula.curriculum_id={$_GET['id']}\n ";
$courses_teachers_result = $connection->query($sql);
$courses_teachers_result = $courses_teachers_result[0];
$table->set_table_name('curricula');
$table->set_id_column('curriculum_id');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_values_form_manually($courses_teachers_result);
// set values found in database into form elements when building top_for
$table->set_drop_down($drop_down);
$table->set_form_array($form);
$content = "<div class='link_button'>\n <a href='?controller=curricula&action=export'>Export to EXCEL</a>\n <a href='?controller=timetable&action=show'>Timetable</a>\n <a href='?controller=teachers&action=index'>Teachers</a>\n </div>";
$content .= '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function details()
{
// details combines seeing with editing
$id = $_GET['id'];
$connection = new database();
$table = new simple_table_ops();
/* Drop down menu */
$sql = "SELECT courses.course_id, levels.level AS 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";
$levels_result = $connection->query($sql);
$drop_down = array('course_id' => array('level' => $levels_result));
/* end of drop down menus definition */
$columns = array('course_id');
// only columns to be updated
$neat_columns = array('Student', 'Genre', 'Program', 'Course', 'Update', 'Remove');
$form = array('action' => '?controller=classes&action=update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('remove', '?controller=classes&action=remove&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('label' => 'student'), 3 => array('label' => 'sex'), 4 => array('label' => 'program'), 6 => array('drop_down' => 'course_id'), 7 => array('submit' => 'update')));
// select row based on id value
$sql = "SELECT classes.course_id as course_id, CONCAT (students.nom, ' ', students.prenom, ', ', students.nom_khmer, ' ', students.prenom_khmer) as student, sexes.sex as sex, programs.program as program\n FROM classes\n JOIN courses ON classes.course_id = courses.course_id\n JOIN students ON classes.student_id = students.student_id\n JOIN sexes ON students.sex_id = sexes.sex_id\n JOIN programs ON students.program_id = programs.program_id\n WHERE classes.classe_id={$_GET['id']}\n ";
$result = $connection->query($sql);
$result = $result[0];
$table->set_table_name('classes');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_values_form_manually($result);
// set values found in database into form elements when building top_for
$table->set_drop_down($drop_down);
$table->set_form_array($form);
$content = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function profiles_details()
{
// details combines seeing with editing
$id = $_GET['id'];
$columns = array('profile');
$neat_columns = array('Profile', 'Update', 'Delete');
$form = array('action' => '?controller=admin&action=profiles_update&id=' . $id, 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'action_links' => array(1 => array('delete', '?controller=admin&action=profiles_delete&id=')), 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('text' => 'profile'), 2 => array('submit' => 'update')));
$table = new simple_table_ops();
$table->set_id_column('profile_id');
$table->set_table_column_names($columns);
$table->set_html_table_column_names($neat_columns);
$table->set_form_array($form);
$table->set_table_name('profiles');
$table->set_values_form();
$content = '<table>';
$content .= $table->details();
$content .= '</table>';
$output['content'] = $content;
return $output;
}
public function index()
{
$content = '<div class="third_left">';
$content .= file_get_contents('views/help/help1.php');
$content .= file_get_contents('views/messages/messages.php');
$content .= '</div>';
/************ left/middle ************/
$content .= "<div class='third_middle'>";
// drop down with school_years and submit button => set $_SESSION['current_year_id'] and $_SESSION['current_year'] variables here
$connection = new database();
$table = new simple_table_ops();
$sql = 'SELECT school_year_id, school_year FROM school_years ORDER BY school_year DESC';
$school_year_result = $connection->query($sql);
if ($connection->get_row_num() == 0) {
// no school year were found, please add one
$content .= "<p> No school year was found! <p>Please, add a school year in [CONFIGURATION] if you are the administrator.";
}
$drop_down = array('school_year_id' => array('school_year' => $school_year_result));
$form_values = array('school_year_id' => $_SESSION['current_school_year_id']);
$table->set_values_form_manually($form_values);
// set drop_down menu with current_school_year_id
$table->set_drop_down($drop_down);
/* CONFIGURES top column in neat names and html formatted */
$neat_column_names = array('School Year', 'Action');
$table->set_html_table_column_names($neat_column_names);
/* CONFIGURES Form structure */
$top_form = array('action' => '?controller=home&action=setschoolyear', 'div' => "class='solitary_input'", 'div_button' => "class='form_button'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'school_year_id'), 11 => array('submit' => 'Set School Year')));
$table->set_top_form($top_form);
/********************************************************************/
$content .= "<table id=pan>";
$content .= "<tr><td>";
$content .= "<table width='226px'>";
$content .= $table->get_html_table_column_names();
$content .= $table->get_form() . "</table>";
$content .= "<p>Select the School Year to use throughout the application. <p>You can see it in the upper right (in orange). Any operation (class, curriculum, timetable etc creation) are automatically attached to this School Year.";
$content .= "</td></tr></table>";
/* CONFIGURES Main table contents */
$columns = array('Available School Years');
$table->set_html_table_column_names($columns);
$sql = 'SELECT school_year_id, school_year
FROM school_years
ORDER BY school_year DESC
';
$table->set_main_table($connection->query($sql));
/********************************************************************/
$content .= "<table id=pan>";
$content .= "<tr><td>";
$content .= "<table width='113px'>";
$content .= $table->get_html_table_column_names();
$content .= $table->get_html_main_table();
$content .= '</table>';
$content .= "</td><td>List of School Years already in the database.<p>To add a new school year, go to <a href='?controller=school_years&action=index'>Configuration->School Years</a></td></tr></table>";
$content .= "</div>";
/************ middle/right ************/
$content .= "<div class='third_right'>";
/* CONFIGURES Main table contents (from MySQL) */
/* LIST OF ALL PROGRAMS */
$columns = array('Programs');
$table->set_html_table_column_names($columns);
$sql = 'SELECT program_id, program
FROM programs
ORDER BY program ASC
';
$table->set_main_table($connection->query($sql));
$content .= "<table id=pan><tr><td>";
$content .= "<table width='113px'>";
$content .= $table->get_html_table_column_names();
$content .= $table->get_html_main_table();
$content .= '</table>';
$content .= "</td><td>List of programs in the database.<p>If a program is changed or created, go to <a href='?controller=program&action=index'>Configuration->Programs</a></td></tr></table>";
/********************************************************************/
$columns = array('Program', 'Students');
$table->set_html_table_column_names($columns);
$sql = "SELECT students.program_id, programs.program, COUNT(students.student_id)\n FROM students\n JOIN programs ON programs.program_id = students.program_id\n GROUP BY program_id\n ORDER BY programs.program";
$table->set_main_table($connection->query($sql));
$content .= "<table id=pan><tr><td>";
$content .= "<table width='226px'>" . $table->get_html_table_column_names() . $table->get_html_main_table() . '</table>';
$sql = "SELECT COUNT(DISTINCT program_id), COUNT(student_id)\n FROM students\n ";
$total_students = $connection->query($sql);
$content .= "<p>Total programs: " . $total_students[0]['COUNT(DISTINCT program_id)'];
$content .= "<br>Total students: " . $total_students[0]['COUNT(student_id)'];
$content .= "</td></tr></table>";
$content .= "</div>";
$output['content'] = $content;
$output['footer'] = 'Welcome to CSS AEC-Foyer Lataste System';
return $output;
}
public function index()
{
$content = "<div class='link_button'>\n <a href='?controller=attendances&action=export'>Export to EXCEL</a>\n\n </div>";
// attendances index:
// Receives curriculum_id -
/* CONFIGURES DROP DOWN Menus (2 in this case: programs and genre) */
$connection = new database();
$table = new simple_table_ops();
$sql = "SELECT month_id, month FROM months ORDER BY month_id";
$months_result = $connection->query($sql);
$sql = "SELECT curricula.curriculum_id,\n CONCAT (teachers.nom, ' ', teachers.prenom, ' ', teachers.nom_khmer, ' ', teachers.prenom_khmer, ' ',\n subjects.subject, ' ', levels.level) as curricula\n FROM curricula\n JOIN teachers ON teachers.teacher_id = curricula.teacher_id\n JOIN subjects ON subjects.subject_id = curricula.subject_id\n JOIN courses ON courses.course_id = curricula.course_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 levels.level_id ASC, teachers.nom ASC";
$curricula_result = $connection->query($sql);
$drop_down = array('curriculum_id' => array('curricula' => $curricula_result), 'month_id' => array('month' => $months_result));
// TODO: set values from $_POST if $_POST isset in order to preset drop down values
if (isset($_POST['curriculum_id']) and isset($_POST['month_id'])) {
$curriculum_id = $_POST['curriculum_id'];
$month_id = $_POST['month_id'];
$post_values = array('curriculum_id' => $curriculum_id, 'month_id' => $month_id);
$table->set_values_form_manually($post_values);
}
$table->set_drop_down($drop_down);
/********************************************************************/
/* CONFIGURES Form structure */
$top_form = array('action' => '?controller=attendances&action=index&submit=yes', 'div' => "class='solitary_input'", 'div_button' => "class='submit_button1'", 'method' => 'post', 'id' => 'top_form', 'elements' => array(1 => array('drop_down' => 'curriculum_id'), 2 => array('drop_down' => 'month_id'), 3 => array('submit' => 'Show Attendance Table')));
$table->set_top_form($top_form);
/********************************************************************/
$columns = array('Curricula', 'Month', 'Action');
$table->set_html_table_column_names($columns);
$content .= "<table width='auto'>" . $table->get_html_table_column_names() . $table->get_form() . '</table>';
if (isset($_GET['submit'])) {
if ($_GET['submit'] == 'yes') {
//$second_table = new simple_table_ops();
// TODO:REFACTOR
// show table
// 1. Get records from [attendance] table corresponding to
// $_POST['course_id'] groups the records in classes->attendances.
// From classes we get the students list and creates the attendance list with a 'text' input in array
// COUNT DAYS IN MONTH - separate in first/second school year
$year_first_semester = substr($_SESSION['current_school_year'], 0, 4);
$year_second_semester = substr($_SESSION['current_school_year'], -4, 4);
if ($month_id < 9) {
// user wants attendance list of first semester
$total_days = cal_days_in_month(CAL_GREGORIAN, $month_id, $year_first_semester);
} else {
// user wants attendance list of second semester
$total_days = cal_days_in_month(CAL_GREGORIAN, $month_id, $year_second_semester);
}
$input_form = '';
// retrieves info (if any) from [attendances] table
// using:
// 1. curriculum_id
// 2. month_id
//
$days_month = '';
for ($i = 1; $i <= 31; $i++) {
$days_month .= "attendances.day" . $i . ",";
}
$days_month = substr($days_month, 0, -1);
$sql = "SELECT classes.student_id, {$days_month}\n FROM attendances\n JOIN classes ON classes.classe_id = attendances.classe_id\n JOIN curricula ON curricula.curriculum_id = attendances.curriculum_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND attendances.month_id = {$month_id}";
$sda = $connection->query($sql);
// Now, should extract students (in the form of classes.classe_id) found in classes absent in [attendances]
// This is necessary if a student starts studying in the middle of the school year
$missing_students_sql = "SELECT classes.classe_id\n FROM classes\n JOIN courses ON courses.course_id = classes.course_id\n JOIN curricula ON curricula.course_id = courses.course_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND NOT classes.student_id IN\n (SELECT classes.student_id\n FROM attendances\n JOIN classes ON classes.classe_id = attendances.classe_id\n JOIN curricula ON curricula.curriculum_id = attendances.curriculum_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND attendances.month_id = {$month_id})";
$missing_classes_id = $connection->query($missing_students_sql);
// then, insert missing students into [attendances] table, and present it to user
$sql = "INSERT INTO attendances (classe_id, curriculum_id, month_id) VALUES (?, ?, ?)";
foreach ($missing_classes_id as $row) {
$classe_id = $row['classe_id'];
echo $classe_id . ", ";
$data[] = array($classe_id, $curriculum_id, $month_id);
// $connection->insert($sql, $data);
}
$connection->insert($sql, $data);
// this selects all students found in [attendances], which should be now up-to-date
$sql = "SELECT attendances.attendance_id, CONCAT(students.nom, ' ', students.prenom) as nom_prenom, programs.program, sexes.sex, {$days_month}\n FROM curricula\n JOIN courses ON curricula.course_id = courses.course_id\n JOIN classes ON courses.course_id = classes.course_id\n JOIN students ON classes.student_id = students.student_id\n JOIN attendances ON attendances.classe_id = classes.classe_id\n JOIN sexes ON sexes.sex_id = students.sex_id\n JOIN programs ON programs.program_id = students.program_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND attendances.month_id = {$month_id}\n ORDER BY students.nom ASC, students.prenom ASC";
$sql = "SELECT attendances.attendance_id, CONCAT(students.nom, ' ', students.prenom) as nom_prenom, programs.program, sexes.sex, {$days_month}\n FROM curricula\n JOIN courses ON curricula.course_id = courses.course_id\n JOIN classes ON courses.course_id = classes.course_id\n JOIN students ON classes.student_id = students.student_id\n JOIN attendances ON attendances.classe_id = classes.classe_id\n JOIN sexes ON sexes.sex_id = students.sex_id\n JOIN programs ON programs.program_id = students.program_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND attendances.month_id = {$month_id}\n ORDER BY students.nom ASC, students.prenom ASC";
$sql = "SELECT attendances.attendance_id, CONCAT(students.nom, ' ', students.prenom) as nom_prenom, programs.program, sexes.sex, attendances.day1,attendances.day2,attendances.day3,attendances.day4,attendances.day5,attendances.day6,attendances.day7,attendances.day8,attendances.day9,attendances.day10,attendances.day11,attendances.day12,attendances.day13,attendances.day14,attendances.day15,attendances.day16,attendances.day17,attendances.day18,attendances.day19,attendances.day20,attendances.day21,attendances.day22,attendances.day23,attendances.day24,attendances.day25,attendances.day26,attendances.day27,attendances.day28,attendances.day29,attendances.day30,attendances.day31\n FROM attendances\n JOIN curricula ON curricula.curriculum_id = attendances.curriculum_id\n JOIN classes ON classes.classe_id = attendances.classe_id\n JOIN students ON classes.student_id = students.student_id\n JOIN sexes ON sexes.sex_id = students.sex_id\n JOIN programs ON programs.program_id = students.program_id\n WHERE curricula.curriculum_id = {$curriculum_id} AND attendances.month_id = {$month_id}\n ORDER BY students.nom ASC, students.prenom ASC";
$students_id = $connection->query($sql);
/* CONFIGURES DROP DOWN Menu ****************************************/
// Bug: drop down menu on ALL input elements is PLAIN UGLY. TODO: better insert info on large form
$sql = 'SELECT attendance_check_id, attendance_abb FROM attendance_checks';
$attendances_result = $connection->query($sql);
/********************************************************************/
$input_form .= "<tr><td><center>Student</center></td><td>Program</td><td>Genre</td>";
for ($i = 1; $i <= $total_days; $i++) {
$input_form .= "<td><center>{$i}</center></td>";
}
$input_form .= "</tr>";
foreach ($students_id as $student_id) {
$input_form .= "<tr><td>" . $student_id['nom_prenom'] . "</td><td><center>" . $student_id['program'] . "</center></td><td>" . $student_id['sex'] . "</td>";
for ($i = 1; $i <= $total_days; $i++) {
$name_day = "attendance[" . $student_id['attendance_id'] . "][" . $i . "]";
$current_day = 'day' . $i;
$value = $student_id[$current_day];
// drop down menu on EACH cell is just too ugly - leave as text, and do a validation later in 'save' method
$input_form .= "<td><input type = 'text' name = '{$name_day}' value = '{$value}' size='1'></td>";
}
$input_form .= "</tr>";
}
$input_form .= "<input type='submit' value='save'>";
$content .= "<table><form method='post' action='?controller=attendances&action=save&id={$curriculum_id}&total_days={$total_days}' id='top_form'>";
$content .= $input_form;
$content .= "</form></table>";
}
}
$output['content'] = $content;
return $output;
}
