Viewing file:  database.inc.php (25.94 KB) -rwxrwxrwx
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?
function pdoSet($allowed, &$values, $source = array()) {
$set = '';
$values = array();
if (!$source) $source = &$_POST;
foreach ($allowed as $field) {
if (isset($source[$field])) {
$set.="`".str_replace("`","``",$field)."`". "=:$field, ";
$values[$field] = $source[$field];
}
}
return substr($set, 0, -2);
}
class Database
{
public static $PDO = null;
public function Database()
{
$dsn = "mysql:host=".DATABASE_HOST.";dbname=".DATABASE_NAME.";charset=utf8";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"
];
self::$PDO = new PDO($dsn, DATABASE_USER, DATABASE_PASS, $opt);
}
public static function GetPDO()
{
if (self::$PDO == null)
new Database();
return self::$PDO;
}
public static function ClearTable($table)
{
$sql = "TRUNCATE TABLE {$table}";
$stm = Database::GetPDO()->prepare($sql);
$stm->execute();
}
public static function AddRow($row, $table)
{
$pdo = Database::GetPDO();
$keys = array_keys($row);
$values = array_values($row);
foreach ($values as &$val)
$val = addslashes($val);
$valuesString = "'". implode("','", $values)."'";
$keysString = implode(', ', $keys);
$sql = "INSERT INTO {$table} ({$keysString}) VALUES({$valuesString})";
//echo $sql;
$stm = $pdo->prepare($sql);
$stm->execute();
}
public static function GetRow($table, $field_name, $field_value)
{
$pdo = Database::GetPDO();
$sql = "SELECT * FROM {$table} WHERE {$field_name} = :value";
$stm = $pdo->prepare($sql);
$stm->bindValue(':value', $field_value);
$stm->execute();
//var_dump($sql);
return $stm->fetch();
}
public static function GetId($table, $field, $value, $field_id)
{
$row = self::GetRow($table, $field, $value);
if (!empty($row))
return $row[$field_id];
return false;
}
public static function AddActivity($subgroup_name, $day_name, $hour_name, $room_name, $teacher_name, $subject_name, $activity_name)
{
Schedule::AddDay($day_name);
Schedule::AddRoom($room_name);
Schedule::AddHour($hour_name);
Schedule::AddTeacher($teacher_name);
Schedule::AddSubgroup($subgroup_name);
Schedule::AddActivityTag($activity_name);
Schedule::AddSubject($subject_name);
$row['activity_day_id'] = Schedule::GetDay($day_name);
$row['activity_hour_id'] = Schedule::GetHour($hour_name);
$row['activity_room_id'] = Schedule::GetRoom($room_name);
$row['activity_tag_id'] = Schedule::GetActivityTag($activity_name);
$row['activity_subject_id'] = Schedule::GetSubject($subject_name);
$row['activity_subgroup_id'] = Schedule::GetSubgroup($subgroup_name);
$row['activity_teacher_id'] = Schedule::GetTeacher($teacher_name);
self::AddRow($row, 'activities');
}
public static function AddIfNotExists($table, $field, $value)
{
if (empty($value))
$value = "";
if (Database::GetRow($table, $field, $value) === false)
Database::AddRow(array($field => $value), $table);
}
public static function GetScheduleForSubgroup($subgroup_id)
{
$pdo = Database::GetPDO();
$sql = "SELECT day_name, hour_name, subject_name, teacher_name, room_name, activity_tag_name FROM activities, rooms, days, hours, subjects, teachers, activities_tags WHERE activity_subgroup_id = :value AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_subject_id = subject_id AND activity_teacher_id = teacher_id AND activity_room_id = room_id AND activities_tags.activity_tag_id = activities.activity_tag_id";
$stm = $pdo->prepare($sql);
$stm->bindValue(':value', $subgroup_id);
$stm->execute();
return $stm->fetchAll();
}
}
class Schedule
{
public static function EraseData()
{
Database::ClearTable('days');
Database::ClearTable('rooms');
Database::ClearTable('hours');
Database::ClearTable('teachers');
Database::ClearTable('subgroups');
Database::ClearTable('activities_tags');
Database::ClearTable('subjects');
Database::ClearTable('activities');
}
public static function UpdateGroupsInfo()
{
Database::ClearTable('groups');
$pdo = Database::GetPDO();
$sql = "SELECT subgroup_name FROM subgroups";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
$groups = array();
foreach ($rows as $row) {
preg_match("/(.*)\[([12]?)\]/", $row['subgroup_name'], $output_array);
$groups[$output_array[1]]++;
}
foreach ($groups as $key => $value) {
Database::AddRow(array('group_name' => $key, 'group_subgroup_count' => $value), 'groups');
}
}
public static function AddDay($value)
{
Database::AddIfNotExists('days', 'day_name', $value);
}
public static function GetDay($value)
{
return Database::GetId('days', 'day_name', $value, 'day_id');
}
public static function AddRoom($value)
{
Database::AddIfNotExists('rooms', 'room_name', $value);
}
public static function GetRoom($value)
{
return Database::GetId('rooms', 'room_name', $value, 'room_id');
}
public static function AddHour($value)
{
Database::AddIfNotExists('hours', 'hour_name', $value);
}
public static function GetHour($value)
{
return Database::GetId('hours', 'hour_name', $value, 'hour_id');
}
public static function AddTeacher($value)
{
Database::AddIfNotExists('teachers', 'teacher_name', $value);
}
public static function GetTeacher($value)
{
return Database::GetId('teachers', 'teacher_name', $value, 'teacher_id');
}
public static function AddSubgroup($value)
{
Database::AddIfNotExists('subgroups', 'subgroup_name', $value);
}
public static function GetSubgroup($value)
{
return Database::GetId('subgroups', 'subgroup_name', $value, 'subgroup_id');
}
public static function AddActivityTag($value)
{
Database::AddIfNotExists('activities_tags', 'activity_tag_name', $value);
}
public static function GetActivityTag($value)
{
return Database::GetId('activities_tags', 'activity_tag_name', $value, 'activity_tag_id');
}
public static function AddSubject($value)
{
Database::AddIfNotExists('subjects', 'subject_name', $value);
}
public static function GetSubject($value)
{
return Database::GetId('subjects', 'subject_name', $value, 'subject_id');
}
public static function GetScheduleForSubgroup($name)
{
$subgroupName = "ПУА-2[1]";
$subgroupId = self::GetSubgroup($subgroupName);
$res = Database::GetScheduleForSubgroup($subgroupId);
return $res;
}
public static function GetSubGroups($day, $hour, $room)
{
$pdo = Database::GetPDO();
$day = addslashes($day);
$sql = "SELECT day_name, hour_name, subject_name, teacher_name, room_name, activity_tag_name, subgroup_name FROM activities, rooms, days, hours, subjects, teachers, activities_tags, subgroups WHERE day_name = '{$day}' AND hour_name = '{$hour}' AND room_name = '{$room}' AND activity_subgroup_id = subgroup_id AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_subject_id = subject_id AND activity_teacher_id = teacher_id AND activity_room_id = room_id AND activities_tags.activity_tag_id = activities.activity_tag_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
return $rows;
}
public static function GetRooms()
{
$pdo = Database::GetPDO();
$sql = "SELECT * FROM rooms";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
return $rows;
}
public static function GetTeachers($day, $hour, $room)
{
$pdo = Database::GetPDO();
$day = addslashes($day);
$sql = "SELECT * FROM teachers";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
return $rows;
}
public function GetRoomIdByName($room)
{
$pdo = Database::GetPDO();
$room = addslashes($room);
$sql = "SELECT room_id FROM rooms WHERE room_name = '{$room}'";
$stm = $pdo->prepare($sql);
$stm->execute();
$row = $stm->fetch();
return $row['room_id'];
}
public function GetDayIdByName($s)
{
$pdo = Database::GetPDO();
$s = addslashes($s);
$sql = "SELECT day_id FROM days WHERE day_name = '{$s}'";
$stm = $pdo->prepare($sql);
$stm->execute();
$row = $stm->fetch();
return $row['day_id'];
}
public function GetHourIdByName($s)
{
$pdo = Database::GetPDO();
$s = addslashes($s);
$sql = "SELECT hour_id FROM hours WHERE hour_name = '{$s}'";
$stm = $pdo->prepare($sql);
$stm->execute();
$row = $stm->fetch();
return $row['hour_id'];
}
public function ChangeRoom($day, $hour, $old_room, $new_room)
{
$pdo = Database::GetPDO();
$day = addslashes($day);
$sql = "SELECT activity_id FROM activities, rooms, days, hours WHERE day_name = '{$day}' AND hour_name = '{$hour}' AND room_name = '$old_room' AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_room_id = room_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
$ids = array();
foreach ($rows as $value)
array_push($ids, $value['activity_id']);
$room_id = self::GetRoomIdByName($new_room);
foreach ($ids as $activity_id) {
$sql = "UPDATE activities SET activity_room_id = '{$room_id}' WHERE activity_id = '{$activity_id}'";
$stm = $pdo->prepare($sql);
$stm->execute();
}
return null;
}
public static function GetAllRooms()
{
$pdo = Database::GetPDO();
$sql = "SELECT room_name FROM rooms";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
$res = array();
foreach ($rows as $row)
array_push($res, $row['room_name']);
return $res;
}
public static function GetFreeRooms($day, $hour)
{
$pdo = Database::GetPDO();
$day = addslashes($day);
$sql = "SELECT room_name FROM activities, rooms, days, hours WHERE day_name = '{$day}' AND hour_name = '{$hour}' AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_room_id = room_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$allRooms = self::GetAllRooms();
//var_dump($allRooms);
$busyRooms = $stm->fetchAll();
$res = array();
foreach ($busyRooms as $row)
array_push($res, $row['room_name']);
$busyRooms = array_unique($res);
// var_dump($busyRooms);
$rooms = array_diff($allRooms, $busyRooms);
return $rooms;
}
public static function GetGroupsList()
{
$pdo = Database::GetPDO();
$sql = "SELECT group_name, group_kurs, faculty_abbr,faculty_name FROM groups, faculties WHERE faculty_id = group_faculty_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
return $rows;
}
public static function GetFaculties()
{
$pdo = Database::GetPDO();
$sql = "SELECT * FROM faculties";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
return $rows;
}
public static function GetFreeMatrix($day, $hour, $room)
{
$pdo = Database::GetPDO();
$day = addslashes($day);
$sql = "SELECT activity_subgroup_id, activity_teacher_id FROM activities, rooms, days, hours WHERE day_name = '{$day}' AND hour_name = '{$hour}' AND room_name = '$room' AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_room_id = room_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
$ids = array();
foreach ($rows as $value)
array_push($ids, $value['activity_subgroup_id']);
$teacher_id = $rows[0]['activity_teacher_id'];
$ids = array_unique($ids);
//////
$pdo = Database::GetPDO();
$idsStr = '(' . implode(',', $ids) . ')';
$sql = "SELECT day_name, hour_name, count(activity_subgroup_id) as count FROM activities, days, hours WHERE activity_subgroup_id IN {$idsStr} AND activity_day_id = day_id AND activity_hour_id = hour_id GROUP BY day_name, hour_name";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
$daynames1 = array("Понеділок 1", "Вівторок 1", "Середа 1", "Четвер 1", "П'ятниця 1");
$daynames2 = array("Понеділок 2", "Вівторок 2", "Середа 2", "Четвер 2", "П'ятниця 2");
$timenames = array("8:30-9:50", "10:00-11:20", "11:40-13:00", "13:30-14:50", "15:00-16:20", "16:30-17:50");
$daynames = array_merge($daynames1, $daynames2);
$matrix = array();
foreach ($daynames as $day)
foreach ($timenames as $time)
$matrix[$day][$time] = 0;
foreach ($rows as $row)
$matrix[$row['day_name']][$row['hour_name']] = $row['count'];
$sql = "SELECT day_name, hour_name, count(activity_subgroup_id) as count FROM activities, days, hours WHERE activity_day_id = day_id AND activity_hour_id = hour_id AND activity_teacher_id = '{$teacher_id}' GROUP BY day_name, hour_name";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
foreach ($rows as $row)
if ($matrix[$row['day_name']][$row['hour_name']] == 0)
$matrix[$row['day_name']][$row['hour_name']] = '-1';
return $matrix;
}
public static function MovePair($from_day, $from_hour, $from_room, $to_day, $to_hour, $to_room)
{
$fromDayId = self::GetDayIdByName($from_day);
$toDayId = self::GetDayIdByName($to_day);
$fromHourId = self::GetHourIdByName($from_hour);
$toHourId = self::GetHourIdByName($to_hour);
$fromRoomId = self::GetRoomIdByName($from_room);
$toRoomId = self::GetRoomIdByName($to_room);
$pdo = Database::GetPDO();
$sql = "UPDATE activities SET activity_day_id = '{$toDayId}', activity_hour_id = '{$toHourId}', activity_room_id = '{$toRoomId}' WHERE activity_day_id = '{$fromDayId}' AND activity_hour_id = '{$fromHourId}' AND activity_room_id = '{$fromRoomId}'";
$stm = $pdo->prepare($sql);
$stm->execute();
}
public static function GetSubgroupsCount($group)
{
$pdo = Database::GetPDO();
$sql = "SELECT group_subgroup_count FROM groups WHERE group_name = '{$group}'";
$stm = $pdo->prepare($sql);
$stm->execute();
$row = $stm->fetch();
return $row['group_subgroup_count'];
}
public static function GetGroupName($subgroupName)
{
preg_match("/(.*)\[([12]?)\]/", $subgroupName, $output_array);
return $output_array[1];
}
public static function GetSubgroupNumber($subgroupName)
{
preg_match("/(.*)\[([12]?)\]/", $subgroupName, $output_array);
return $output_array[2];
}
public static function GetScheduleForGroup($groupName)
{
$pdo = Database::GetPDO();
$sql = "SELECT day_name, hour_name, subject_name, teacher_name, room_name, activity_tag_name, subgroup_name, activity_id, subgroup_has_variativ FROM activities, rooms, days, hours, subjects, teachers, activities_tags, subgroups WHERE (subgroup_name LIKE '{$groupName}' OR subgroup_name LIKE '{$groupName}[1]' OR subgroup_name LIKE '{$groupName}[2]') AND activity_subgroup_id = subgroup_id AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_subject_id = subject_id AND activity_teacher_id = teacher_id AND activity_room_id = room_id AND activities_tags.activity_tag_id = activities.activity_tag_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
//var_dump($rows);
$subgroupsCount = self::GetSubgroupsCount($groupName);
foreach ($rows as &$row) {
$sg = self::GetSubGroups($row['day_name'], $row['hour_name'], $row['room_name']);
$row['flow'] = $sg;
}
$schedule['group'] = $groupName;
$schedule['subgroupCount'] = $subgroupsCount;
$schedule[1] = array(array());
$schedule[2] = array(array());
foreach ($rows as &$row) {
$subjects = array();
$teachers = array();
$subgroups = array();
foreach ($row['flow'] as $flowRow) {
array_push($subjects, $flowRow['subject_name']);
array_push($teachers, $flowRow['teacher_name']);
array_push($subgroups, $flowRow['subgroup_name']);
}
$subjects = array_unique($subjects);
$teachers = array_unique($teachers);
$subgroups = array_unique($subgroups);
$row['flow_teachers'] = implode(", ", $teachers);
$sgMatrix = array();
foreach ($subgroups as $subgroup) {
$grName = self::GetGroupName($subgroup);
$grNum = self::GetSubgroupNumber($subgroup);
if (!is_array($sgMatrix[$grName]))
$sgMatrix[$grName] = array();
array_push($sgMatrix[$grName], $grNum);
}
$flowElems = array();
foreach ($sgMatrix as $key => $value) {
if (self::GetSubgroupsCount($key) > count($value))
array_push($flowElems, "{$key}[{$value[0]}]");
else
array_push($flowElems, "{$key}");
}
$row['flow_subgroups'] = implode(", ", $flowElems);
$row['flow_subgroups_count'] = count($subgroups);
$row['flow_subjects'] = implode(" / ", $subjects);
$day_name = $row['day_name'];
$hour_name = $row['hour_name'];
$room_name = $row['room_name'];
$subgroupNumber = self::GetSubgroupNumber($row['subgroup_name']);
if (!is_array($schedule[$subgroupNumber][$day_name][$hour_name]))
$schedule[$subgroupNumber][$day_name][$hour_name] = array();
array_push($schedule[$subgroupNumber][$day_name][$hour_name], $row);
if ($subgroupsCount == 1) {
if (!is_array($schedule[2][$day_name][$hour_name]))
$schedule[2][$day_name][$hour_name] = array();
array_push($schedule[2][$day_name][$hour_name], $row);
}
}
return $schedule;
}
public static function GetScheduleForTeacher($teacherName)
{
$pdo = Database::GetPDO();
$sql = "SELECT day_name, hour_name, subject_name, teacher_name, room_name, activity_tag_name, subgroup_name, activity_id, subgroup_has_variativ FROM activities, rooms, days, hours, subjects, teachers, activities_tags, subgroups WHERE (teacher_name LIKE '{$teacherName}') AND activity_subgroup_id = subgroup_id AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_subject_id = subject_id AND activity_teacher_id = teacher_id AND activity_room_id = room_id AND activities_tags.activity_tag_id = activities.activity_tag_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
foreach ($rows as &$row) {
$sg = self::GetSubGroups($row['day_name'], $row['hour_name'], $row['room_name']);
$row['flow'] = $sg;
}
$schedule['teacher'] = $teacherName;
$schedule[1] = array(array());
foreach ($rows as &$row) {
$subjects = array();
$teachers = array();
$subgroups = array();
foreach ($row['flow'] as $flowRow) {
array_push($subjects, $flowRow['subject_name']);
array_push($teachers, $flowRow['teacher_name']);
array_push($subgroups, $flowRow['subgroup_name']);
}
$subjects = array_unique($subjects);
$teachers = array_unique($teachers);
$subgroups = array_unique($subgroups);
$row['flow_teachers'] = implode(", ", $teachers);
$sgMatrix = array();
foreach ($subgroups as $subgroup) {
$grName = self::GetGroupName($subgroup);
$grNum = self::GetSubgroupNumber($subgroup);
if (!is_array($sgMatrix[$grName]))
$sgMatrix[$grName] = array();
array_push($sgMatrix[$grName], $grNum);
}
$flowElems = array();
foreach ($sgMatrix as $key => $value) {
if (self::GetSubgroupsCount($key) > count($value))
array_push($flowElems, "{$key}[{$value[0]}]");
else
array_push($flowElems, "{$key}");
}
$row['flow_subgroups'] = implode(", ", $flowElems);
$row['flow_subgroups_count'] = count($subgroups);
$row['flow_subjects'] = implode(" / ", $subjects);
$day_name = $row['day_name'];
$hour_name = $row['hour_name'];
$room_name = $row['room_name'];
$subgroupNumber = self::GetSubgroupNumber($row['subgroup_name']);
if (!is_array($schedule[$subgroupNumber][$day_name][$hour_name]))
$schedule[$subgroupNumber][$day_name][$hour_name] = array();
array_push($schedule[$subgroupNumber][$day_name][$hour_name], $row);
if (!is_array($schedule[2][$day_name][$hour_name]))
$schedule[2][$day_name][$hour_name] = array();
array_push($schedule[2][$day_name][$hour_name], $row);
}
return $schedule;
}
public static function GetScheduleForRoom($roomName)
{
$pdo = Database::GetPDO();
$sql = "SELECT day_name, hour_name, subject_name, teacher_name, room_name, activity_tag_name, subgroup_name, activity_id, subgroup_has_variativ FROM activities, rooms, days, hours, subjects, teachers, activities_tags, subgroups WHERE (room_name LIKE '{$roomName}') AND activity_subgroup_id = subgroup_id AND activity_day_id = day_id AND activity_hour_id = hour_id AND activity_subject_id = subject_id AND activity_teacher_id = teacher_id AND activity_room_id = room_id AND activities_tags.activity_tag_id = activities.activity_tag_id";
$stm = $pdo->prepare($sql);
$stm->execute();
$rows = $stm->fetchAll();
foreach ($rows as &$row) {
$sg = self::GetSubGroups($row['day_name'], $row['hour_name'], $row['room_name']);
$row['flow'] = $sg;
}
$schedule['room'] = $roomName;
$schedule[1] = array(array());
foreach ($rows as &$row) {
$subjects = array();
$teachers = array();
$subgroups = array();
foreach ($row['flow'] as $flowRow) {
array_push($subjects, $flowRow['subject_name']);
array_push($teachers, $flowRow['teacher_name']);
array_push($subgroups, $flowRow['subgroup_name']);
}
$subjects = array_unique($subjects);
$teachers = array_unique($teachers);
$subgroups = array_unique($subgroups);
$row['flow_teachers'] = implode(", ", $teachers);
$sgMatrix = array();
foreach ($subgroups as $subgroup) {
$grName = self::GetGroupName($subgroup);
$grNum = self::GetSubgroupNumber($subgroup);
if (!is_array($sgMatrix[$grName]))
$sgMatrix[$grName] = array();
array_push($sgMatrix[$grName], $grNum);
}
$flowElems = array();
foreach ($sgMatrix as $key => $value) {
if (self::GetSubgroupsCount($key) > count($value))
array_push($flowElems, "{$key}[{$value[0]}]");
else
array_push($flowElems, "{$key}");
}
$row['flow_subgroups'] = implode(", ", $flowElems);
$row['flow_subgroups_count'] = count($subgroups);
$row['flow_subjects'] = implode(" / ", $subjects);
$day_name = $row['day_name'];
$hour_name = $row['hour_name'];
$room_name = $row['room_name'];
$subgroupNumber = self::GetSubgroupNumber($row['subgroup_name']);
if (!is_array($schedule[$subgroupNumber][$day_name][$hour_name]))
$schedule[$subgroupNumber][$day_name][$hour_name] = array();
array_push($schedule[$subgroupNumber][$day_name][$hour_name], $row);
if (!is_array($schedule[2][$day_name][$hour_name]))
$schedule[2][$day_name][$hour_name] = array();
array_push($schedule[2][$day_name][$hour_name], $row);
}
return $schedule;
}
}
?>
|