<?
error_reporting(E_ALL);
ini_set("display_errors", 1);
session_start();
$db = new SQLite3('school.db');
/*
$db->exec("
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE IF NOT EXISTS lessons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER,
topic TEXT,
date TEXT,
time_start TEXT,
time_end TEXT,
repeat_type TEXT
);
CREATE TABLE IF NOT EXISTS homework (
id INTEGER PRIMARY KEY AUTOINCREMENT,
lesson_id INTEGER,
created_at TEXT
);
CREATE TABLE IF NOT EXISTS homework_files (
id INTEGER PRIMARY KEY AUTOINCREMENT,
homework_id INTEGER,
file_name TEXT,
type TEXT
);
");
CREATE TABLE IF NOT EXISTS notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT,
role TEXT,
student_id INTEGER,
homework_id INTEGER,
message TEXT,
is_read INTEGER DEFAULT 0,
created_at TEXT
);
CREATE TABLE IF NOT EXISTS access_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
role TEXT,
student_id INTEGER,
lesson_id INTEGER,
ip TEXT,
user_agent TEXT,
created_at TEXT
);
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
login TEXT UNIQUE,
password_hash TEXT,
role TEXT
);
$password = password_hash('forany', PASSWORD_DEFAULT);
INSERT INTO users (login,password_hash,role)
VALUES ('teacher',$password,'teacher'); password_hash('forany', PASSWORD_DEFAULT)
INSERT INTO users (login,password_hash,role)
VALUES ('teacher','forany','teacher');
*/
/* ================== РОЛЬ ================== */
$role = 'guest';
if (isset($_REQUEST['student_id']) || isset($_SESSION['student_id'])) {
$role = 'student';
}
if (!empty($_COOKIE['teacher_auth']) && $_COOKIE['teacher_auth'] === md5('SECRET_TEACHER_KEY')) {
$role = 'teacher';
}
$action = $_REQUEST['action'] ?? '';
/* ================== ЗАПРЕТ ДЛЯ УЧЕНИКА ================== */
$teacher_only = [
'save_lesson','add_homework','save_homework_comment'
];//'upload_homework_file', 'delete_homework_file'
if (in_array($action,$teacher_only) && $role!=='teacher') {
echo json_encode(['status'=>'error','message'=>'Доступ запрещён']);
exit;
}
###############################
if ($action=='search_students') {
$q = $_GET['q'] ?? '';
$res = $db->query("SELECT id,name FROM students WHERE name LIKE '%$q%'");
$out = [];
while($row=$res->fetchArray()) {
$out[] = ['id'=>$row['id'],'text'=>$row['name']];
}
echo json_encode($out);
exit;
}
/* ================== УРОКИ ================== */
if ($action == 'save_lesson') {
$student_id = $_POST['student_id'] ?? null;
$topic = $_POST['topic'] ?? '';
$date = $_POST['date'] ?? '';
$time_start = $_POST['start'] ?? '';
$time_end = $_POST['end'] ?? '';
$repeat = $_POST['repeat'] ?? 'none';
$lesson_id = $_POST['id'] ?? null;
// Если студент новый (текст), добавляем в таблицу students
if (!is_numeric($student_id)) {
$stmt = $db->prepare("INSERT INTO students (name) VALUES (:name)");
$stmt->bindValue(':name', $student_id, SQLITE3_TEXT);
$stmt->execute();
$student_id = $db->lastInsertRowID(); // получаем ID нового студента
}
if ($lesson_id) {
// обновление
$stmt = $db->prepare("UPDATE lessons SET student_id=:student_id, topic=:topic, date=:date, time_start=:start, time_end=:end, repeat_type=:repeat WHERE id=:id");
$stmt->bindValue(':id', $lesson_id, SQLITE3_INTEGER);
} else {
// создание
$stmt = $db->prepare("INSERT INTO lessons (student_id, topic, date, time_start, time_end, repeat_type) VALUES (:student_id, :topic, :date, :start, :end, :repeat)");
}
$stmt->bindValue(':student_id', $student_id, SQLITE3_INTEGER);
$stmt->bindValue(':topic', $topic, SQLITE3_TEXT);
$stmt->bindValue(':date', $date, SQLITE3_TEXT);
$stmt->bindValue(':start', $time_start, SQLITE3_TEXT);
$stmt->bindValue(':end', $time_end, SQLITE3_TEXT);
$stmt->bindValue(':repeat', $repeat, SQLITE3_TEXT);
$stmt->execute();
echo json_encode(['status'=>'ok']);
exit;
}
if ($action == 'get_lessons') {
$where='';
if ($role==='student') {
$sid=(int)$_GET['student_id'];
$where="WHERE lessons.student_id=$sid";
}
$res = $db->query("SELECT lessons.id, lessons.topic, lessons.date, students.name AS student_name
FROM lessons
LEFT JOIN students ON lessons.student_id = students.id
$where ORDER BY lessons.id DESC"); // новые сверху
$lessons = [];
while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
$lessons[] = [
'id' => $row['id'],
'topic' => $row['topic'],
'date' => $row['date'],
'student_name' => $row['student_name'],
'link_l' => 'https://'.$_SERVER['HTTP_HOST'].'/?lesson='.$row['id'].'-'.md5($row['id'].'lesson+')
];
}
echo json_encode($lessons);
exit;
}
if($action == 'get_lesson'){
$id = $_GET['id'] ?? 0;
$res = $db->query("SELECT * FROM lessons WHERE id=$id");
$lesson = $res->fetchArray(SQLITE3_ASSOC);
if(!$lesson){
echo json_encode(['status'=>'error','message'=>'Урок не найден']);
exit;
}
$lesson['link_l'] = 'https://'.$_SERVER['HTTP_HOST'].'/?lesson='.$id.'-'.md5($id.'lesson+');
// Получаем имя студента
$student_res = $db->query("SELECT name FROM students WHERE id=".$lesson['student_id']);
$student_row = $student_res->fetchArray(SQLITE3_ASSOC);
$lesson['student_name'] = $student_row['name'] ?? '';
echo json_encode($lesson);
exit;
}
/* ================== УВЕДОМЛЕНИЯ ================== */
if ($action==='get_notifications' ) {//&& $role==='teacher'
if ($role==='teacher') {
$res=$db->query("SELECT notifications.id,notifications.type,notifications.student_id,notifications.homework_id,notifications.message,notifications.is_read,students.name
FROM notifications
LEFT JOIN students ON notifications.student_id = students.id
WHERE role='teacher' ORDER BY notifications.id DESC LIMIT 25");//WHERE (role=='student' AND is_read=1) OR role!='teacher'
} else {
$sid=(int)$_REQUEST['student_id'];
$res=$db->query("SELECT * FROM notifications WHERE role='student' AND student_id=$sid ORDER BY id DESC LIMIT 10");//Тут учителя разные надо будет выбирать
}
//$res=$db->query("SELECT * FROM notifications ORDER BY id DESC LIMIT 15");
$out=[];
while($r=$res->fetchArray(SQLITE3_ASSOC)) $out[]=$r;
echo json_encode($out);
exit;
}
if ($action==='read_notification' ) {//&& $role==='teacher'
$id=(int)$_POST['id'];
$db->exec("UPDATE notifications SET is_read=1 WHERE id=$id");
echo json_encode(['status'=>'ok']);
exit;
}
function addNotification($db, $type, $role, $student_id, $homework_id, $message) {
$stmt = $db->prepare("
INSERT INTO notifications (type, role, student_id, homework_id, message, created_at)
VALUES (:type, :role, :sid, :lid, :msg, :dt)
");
$stmt->bindValue(':type', $type, SQLITE3_TEXT);
$stmt->bindValue(':role', $role, SQLITE3_TEXT);
$stmt->bindValue(':sid', $student_id, SQLITE3_INTEGER);
$stmt->bindValue(':lid', $homework_id, SQLITE3_INTEGER);
$stmt->bindValue(':msg', $message, SQLITE3_TEXT);
$stmt->bindValue(':dt', date('Y-m-d H:i:s'), SQLITE3_TEXT);
$stmt->execute();
}
if($action == 'add_homework'){
$lesson_id = $_POST['lesson_id'] ?? 0;
if(!$lesson_id){
echo json_encode(['status'=>'error','message'=>'Не указан урок']);
exit;
}
$stmt = $db->prepare("INSERT INTO homework (lesson_id, created_at) VALUES (:lesson_id, :created_at)");
$stmt->bindValue(':lesson_id', $lesson_id, SQLITE3_INTEGER);
$stmt->bindValue(':created_at', date('Y-m-d H:i:s'), SQLITE3_TEXT);
$stmt->execute();
$homework_id = $db->lastInsertRowID();
//УВЕДОМЛЯТЬ УЧЕНИКА О НОВОМ ЗАДАНИИ
$res = $db->query("SELECT lessons.id as lesson_id, lessons.topic, lessons.date, students.name AS student_name, students.id as student_id
FROM students
LEFT JOIN homework ON homework.lesson_id = lessons.id
LEFT JOIN lessons ON lessons.student_id = students.id
WHERE homework.id=$homework_id ORDER BY lessons.id DESC"); // новые сверху
$user=$res->fetchArray(SQLITE3_ASSOC);
if ($user) {
addNotification(
$db,
'new_homework',
'student',
$user['student_id'],
$lesson_id,
'Добавлено новое домашнее задание'
);
}
echo json_encode(['status'=>'ok']);
exit;
}
if($action == 'get_homework'){
$lesson_id = $_GET['lesson_id'] ?? 0;
$res = $db->query("SELECT * FROM homework WHERE lesson_id=$lesson_id ORDER BY id DESC");
$homeworks = [];
while($row = $res->fetchArray(SQLITE3_ASSOC)){
// получаем файлы
$files_res = $db->query("SELECT id, file_name, type FROM homework_files WHERE homework_id=".$row['id']);
$task_files = [];
$answer_files = [];
while($f = $files_res->fetchArray(SQLITE3_ASSOC)){
if($f['type'] == 'task') $task_files[] = ['id'=>$f['id'], 'file_name'=>$f['file_name']];
else $answer_files[] = ['id'=>$f['id'], 'file_name'=>$f['file_name']];
}
$homeworks[] = [
'id' => $row['id'],
'created_at' => $row['created_at'],
'comment_th' => $row['comment_th'],
'task_files' => $task_files,
'answer_files' => $answer_files
];
}
echo json_encode($homeworks);
exit;
}
/*
if($action == 'upload_homework_file'){
$homework_id = $_POST['homework_id'] ?? 0;
$type = $_POST['type'] ?? 'task';
foreach($_FILES['files']['name'] as $key => $name){
$tmp = $_FILES['files']['tmp_name'][$key];
$path = 'uploads/'.$name;
move_uploaded_file($tmp, $path);
$stmt = $db->prepare("INSERT INTO homework_files (homework_id, file_name, type) VALUES (:hid,:fname,:type)");
$stmt->bindValue(':hid', $homework_id, SQLITE3_INTEGER);
$stmt->bindValue(':fname', $name, SQLITE3_TEXT);
$stmt->bindValue(':type', $type, SQLITE3_TEXT);
$stmt->execute();
}
echo json_encode(['status'=>'ok']);
exit;
}*/
if($action == 'upload_homework_file'){
$homework_id = $_POST['homework_id'] ?? 0;
$type = $_POST['type'] ?? 'task';
$upload_dir = 'uploads/';
if($type == 'task' && $role!=='teacher'){
echo json_encode(['status'=>'role error']);
exit;
}
/* 🔐 ЖЁСТКАЯ ПРОВЕРКА РОЛЕЙ */
foreach($_FILES['files']['name'] as $key=>$name){
$tmp = $_FILES['files']['tmp_name'][$key];
//$tmp = $_FILES['files']['tname'][$key];
$ext = pathinfo($name, PATHINFO_EXTENSION);
/* расширения */
// $allowed = ['pdf','doc','docx','jpg','png','zip'];
// if (!in_array($ext,$allowed)) continue;
//$safe_name = uniqid().'_'.preg_replace('/[^a-zA-Z0-9._-]/','_', $name);
$safe_name = $name;
$path = $upload_dir.$safe_name;
if(!is_dir($upload_dir)) mkdir($upload_dir, 0777, true);
if(move_uploaded_file($tmp, $path)){
$stmt = $db->prepare("INSERT INTO homework_files (homework_id,file_name,type) VALUES (:hid,:fname,:type)");
$stmt->bindValue(':hid',$homework_id,SQLITE3_INTEGER);
$stmt->bindValue(':fname',$safe_name,SQLITE3_TEXT);
$stmt->bindValue(':type',$type,SQLITE3_TEXT);
$stmt->execute();
}
}
/*if ($role === 'student' && $type === 'answer') {
addNotification(
$db,
'student_upload_answer',
'teacher',
$_REQUEST['student_id'] ?? null,
$homework_id,
'Ученик загрузил файл ответа'
);
}*/
if ($role==='teacher' && $type==='task') {
$res = $db->query("SELECT lessons.id as lesson_id, lessons.topic, lessons.date, students.name AS student_name, students.id as student_id
FROM students
LEFT JOIN homework ON homework.lesson_id = lessons.id
LEFT JOIN lessons ON lessons.student_id = students.id
WHERE homework.id=$homework_id ORDER BY lessons.id DESC"); // новые сверху
$user=$res->fetchArray(SQLITE3_ASSOC);
$stmt=$db->prepare("
INSERT INTO notifications (type,role,student_id,homework_id,message,created_at)
VALUES ('teache_upload_answer','student',:sid,:lid,'Учитель загрузил задание',:dt)
");
$stmt->bindValue(':sid',$user['student_id']);
$stmt->bindValue(':lid',$homework_id);
$stmt->bindValue(':dt',date('Y-m-d H:i:s'));
$stmt->execute();
}
if ($role==='student' && $type==='answer') {
$stmt=$db->prepare("
INSERT INTO notifications (type,role,student_id,homework_id,message,created_at)
VALUES ('student_upload_answer','teacher',:sid,:lid,'Ученик загрузил ответ',:dt)
");
$stmt->bindValue(':sid',$_SESSION['student_id']);//$_REQUEST['student_id']
$stmt->bindValue(':lid',$homework_id);
$stmt->bindValue(':dt',date('Y-m-d H:i:s'));
$stmt->execute();
}
// echo '<pre>',print_r($GLOBALS,1);
// exit;
echo json_encode(['status'=>'ok']);
exit;
}
if($action == 'delete_homework_file'){
$file_id = $_POST['file_id'] ?? 0;
$res = $db->query("SELECT file_name FROM homework_files WHERE id=$file_id");
$row = $res->fetchArray(SQLITE3_ASSOC);
if($row){
$path = 'uploads/'.$row['file_name'];
if(file_exists($path)) unlink($path);
$db->exec("DELETE FROM homework_files WHERE id=$file_id");
}
echo json_encode(['status'=>'ok']);
exit;
}
if($action == 'save_homework_comment'){
$homework_id = $_POST['homework_id'] ?? 0;
$comment = $_POST['comment_th'] ?? '';
$stmt = $db->prepare("UPDATE homework SET comment_th=:comment WHERE id=:hid");
$stmt->bindValue(':comment', $comment, SQLITE3_TEXT);
$stmt->bindValue(':hid', $homework_id, SQLITE3_INTEGER);
$stmt->execute();
//УВЕДОМЛЯТЬ УЧЕНИКА О НОВОМ save_homework_comment
if ($comment!='') {
$res = $db->query("SELECT lessons.id as lesson_id, lessons.topic, lessons.date, students.name AS student_name, students.id as student_id
FROM students
LEFT JOIN homework ON homework.lesson_id = lessons.id
LEFT JOIN lessons ON lessons.student_id = students.id
WHERE homework.id=$homework_id ORDER BY lessons.id DESC"); // новые сверху
$user=$res->fetchArray(SQLITE3_ASSOC);
if($user){
addNotification(
$db,
'save_homework_comment',
'student',
$user['student_id'],
$user['lesson_id'],
'Добавлен сообщение к заданию'
);
}
}
echo json_encode(['status'=>'ok']);
exit;
}
if ($action==='login') {
//session_start();
$login=$_POST['login'];
$pass=$_POST['password'];
$stmt=$db->prepare("SELECT * FROM users WHERE login=:l");
$stmt->bindValue(':l',$login);
$res=$stmt->execute();
$user=$res->fetchArray(SQLITE3_ASSOC);
if ($user && password_verify($pass,$user['password_hash'])) {
$_SESSION['user_id']=$user['id'];
$_SESSION['role']=$user['role'];
echo json_encode(['status'=>'ok']);
} else {
echo json_encode(['status'=>'error']);
}
exit;
}
//echo '<pre>'.print_r($GLOBALS,1);exit;