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 acl_index()
{
// [controllers] -> [acl] <- [profiles]
// on the left: show ALL [controllers] table contents with a check box to select
// on the right: [acl] table with details link
$connection = new database();
$table = new simple_table_ops();
$content = "<div class='link_button'>\n <a href='?controller=admin&action=users_index'>Users</a>\n <a href='?controller=admin&action=controllers_index'>Controllers</a>\n <a href='?controller=admin&action=profiles_index'>Profiles</a>\n </div>";
/* CONFIGURES DROP DOWN Menus (2 in this case: programs and genre) */
$sql = 'SELECT profile_id, profile FROM profiles';
$profiles_result = $connection->query($sql);
$sql = 'SELECT active_id, active FROM actives';
$actives_result = $connection->query($sql);
$drop_down = array('profile_id' => array('profile' => $profiles_result), 'active_id' => array('active' => $actives_result));
$table->set_drop_down($drop_down);
/********************************************************************/
/* CONFIGURES Form structure */
// add form: presents only profile_id, active_id and submit button. controller_id comes from $_POST['checkbox_array'] configured
// by simple_table_ops->set_html_checkbox
$sql = "SELECT controller_id, controller, c_action FROM controllers ORDER BY controller ASC, c_action ASC";
$table->set_id_column('controller_id');
$table->set_html_check_box($connection->query($sql));
/* CONFIGURES Main table contents (from MySQL) */
$table->set_id_column('controller_id');
/********************************************************************/
$content .= "<div class='third_left'>";
$content .= "<div class='submit_top_acl'><table>";
$content .= "<form action = '?controller=admin&action=acl_add' method = 'post' id='top_form'>";
$content .= "<td>Profile: </td><td>" . $table->get_html_drop_down('profile_id') . "</td>";
$content .= "<td>Access?: </td><td>" . $table->get_html_drop_down('active_id') . "</td>";
$content .= "<td><input type ='submit' value='Move ->'></td>";
$content .= "</table></div>";
$columns = array('Select', 'Controller', 'Action');
$table->set_html_table_column_names($columns);
$content .= "<table width='100%'>" . $table->get_html_table_column_names();
$content .= $table->get_html_check_box();
$content .= "</form></table>";
$content .= "</div>";
// MIDDLE_SECTION START
$content .= "<div class='column_margin'></div>";
// MIDDLE_SETION END
// START OF RIGHT TABLE - [acl] and its appendices *******************************************************************/
$content .= "<div class='two_thirds_right'>";
$columns = array('Profile', 'Controller', 'Action', 'Access', 'Details');
$table->set_html_table_column_names($columns);
$sql = "SELECT acl.acl_id, profiles.profile, controllers.controller, controllers.c_action, actives.active\n FROM acl\n JOIN controllers ON controllers.controller_id = acl.controller_id\n JOIN profiles ON profiles.profile_id = acl.profile_id\n JOIN actives ON actives.active_id = acl.active_id\n ORDER BY profiles.profile ASC, controllers.controller ASC, controllers.c_action ASC\n ";
$details_link = array(1 => array('edit', '?controller=admin&action=acl_details&id='));
$table->set_details_link($details_link);
$table->set_main_table($connection->query($sql));
/********************************************************************/
$content .= "<table>" . $table->get_html_table_column_names() . $table->get_html_main_table() . '</table>';
/******************************** END OF RIGHT TABLE **********************************************/
$content .= "</div>";
/* CONFIGURE profiles TABLE */
$output['content'] = $content;
return $output;
}
