289 lines
10 KiB
JavaScript
289 lines
10 KiB
JavaScript
const { models } = require("../../sequelize");
|
||
const { Sequelize, Op } = require("sequelize");
|
||
const moment = require("moment");
|
||
|
||
function checkParams(query) {
|
||
//проверка параметров на формат и их данные
|
||
let errors = {};
|
||
let queryInfo = {}; //Все значения из запроса превратим в нужный формат для использования
|
||
|
||
if (query.date) {
|
||
//проверка дат
|
||
queryInfo.dates = query.date.split(",").sort();
|
||
if (queryInfo.dates.length > 2)
|
||
errors.date = "You need to use 2 or less dates in query";
|
||
else if (
|
||
!(
|
||
moment(queryInfo.dates[0], "YYYY-MM-DD", true).isValid() &&
|
||
moment(
|
||
queryInfo.dates[1] || queryInfo.dates[0],
|
||
"YYYY-MM-DD",
|
||
true
|
||
).isValid()
|
||
)
|
||
) {
|
||
errors.date = "Wrong date format";
|
||
}
|
||
}
|
||
|
||
if (query.status) {
|
||
//проверка статуса
|
||
if (!(query.status === "0" || query.status === "1"))
|
||
errors.status = "Status can only be 0 or 1";
|
||
else queryInfo.status = parseInt(query.status);
|
||
}
|
||
|
||
if (query.teacherIds) {
|
||
//проверка id учителей
|
||
let teacherIds = query.teacherIds.split(",");
|
||
queryInfo.teacherIds = [];
|
||
for (teacherId of teacherIds) {
|
||
let id = +teacherId;
|
||
if (
|
||
isNaN(id) ||
|
||
id % 1 > 0 ||
|
||
id < 1 ||
|
||
queryInfo.teacherIds.includes(id)
|
||
)
|
||
errors.teacherIds = "teacherIds can only be unique integer more than 0";
|
||
else queryInfo.teacherIds.push(id);
|
||
}
|
||
}
|
||
|
||
if (query.studentsCount) {
|
||
//проверка количества учеников
|
||
let studentsCounts = query.studentsCount.split(",").sort();
|
||
queryInfo.studentsCount = [];
|
||
if (studentsCounts.length > 2)
|
||
errors.studentsCount =
|
||
"You need to use 2 or less studentsCounts in query";
|
||
else
|
||
for (studentsCount of studentsCounts) {
|
||
let count = +studentsCount;
|
||
if (isNaN(count) || count % 1 > 0 || count < 0)
|
||
errors.studentsCount =
|
||
"studentsCounts can only be integer greater than or equal to 0";
|
||
else queryInfo.studentsCount.push(count);
|
||
}
|
||
}
|
||
|
||
if (query.page) {
|
||
//проверка номера страницы
|
||
let page = +query.page;
|
||
if (isNaN(page) || page % 1 > 0 || page < 1)
|
||
errors.page = "Page can only be integer more than 0";
|
||
else queryInfo.page = page;
|
||
}
|
||
|
||
if (query.lessonsPerPage) {
|
||
//проверка количества учеников
|
||
let lessonsPerPage = +query.lessonsPerPage;
|
||
if (isNaN(lessonsPerPage) || lessonsPerPage % 1 > 0 || lessonsPerPage < 1)
|
||
errors.lessonsPerPage = "lessonsPerPage can only be integer more than 0";
|
||
else queryInfo.lessonsPerPage = lessonsPerPage;
|
||
}
|
||
|
||
return { queryInfo, errors };
|
||
}
|
||
|
||
function getWhereParametres(queryInfo) {
|
||
//Преобразуем параметры в условия запроса
|
||
let where = {};
|
||
let and = [];
|
||
|
||
if (queryInfo.dates) {
|
||
//Добавляем условие на проверку даты занятия
|
||
if (queryInfo.dates.length == 2)
|
||
where.date = { [Op.between]: [queryInfo.dates[0], queryInfo.dates[1]] };
|
||
else where.date = { [Op.eq]: queryInfo.dates[0] };
|
||
}
|
||
|
||
if (queryInfo.status !== undefined)
|
||
//Добавляем условие на проверку статуса занятия
|
||
where.status = { [Op.eq]: queryInfo.status };
|
||
|
||
if (queryInfo.studentsCount) {
|
||
//Добавляем условие на проверку количества учеников записанных на занятие
|
||
if (queryInfo.studentsCount.length == 2)
|
||
and.push({
|
||
id: Sequelize.literal(`
|
||
(SELECT CAST(COUNT(lesson_students.student_id)AS INT) AS studentsCount
|
||
FROM public.lessons AS lessons_student
|
||
LEFT OUTER JOIN public.lesson_students AS lesson_students ON lessons_student.id = lesson_students.lesson_id
|
||
WHERE lessons_student.id = lessons.id
|
||
GROUP BY lessons_student.id) BETWEEN ${queryInfo.studentsCount[0]} AND ${queryInfo.studentsCount[1]}`),
|
||
});
|
||
else
|
||
and.push({
|
||
id: Sequelize.literal(`
|
||
(SELECT CAST(COUNT(lesson_students.student_id)AS INT) AS studentsCount
|
||
FROM public.lessons AS lessons_student
|
||
LEFT OUTER JOIN public.lesson_students AS lesson_students ON lessons_student.id = lesson_students.lesson_id
|
||
WHERE lessons_student.id = lessons.id
|
||
GROUP BY lessons_student.id) = ${queryInfo.studentsCount[0]}`),
|
||
});
|
||
}
|
||
|
||
if (queryInfo.teacherIds)
|
||
//Добавляем условие на проверку наличия учителей на занятии
|
||
where.id = {
|
||
[Op.any]: Sequelize.literal(`
|
||
(SELECT lessons_in.id
|
||
FROM public.lessons AS lessons_in
|
||
LEFT OUTER JOIN public.lesson_teachers AS lesson_teachers ON lessons_in.id = lesson_teachers.lesson_id
|
||
WHERE lesson_teachers.teacher_id = ANY (SELECT unnest(ARRAY[${queryInfo.teacherIds}]))
|
||
GROUP BY lessons_in.id)
|
||
`),
|
||
};
|
||
|
||
return { ...where, [Op.and]: and };
|
||
}
|
||
|
||
async function getAll(req, res) {
|
||
let query = checkParams(req.query);
|
||
if (Object.keys(query.errors).length > 0) {
|
||
return res.status(400).json({ isSuccess: false, errors: query.errors });
|
||
}
|
||
|
||
const lessons = await models.lessons.findAll({
|
||
offset: (query.queryInfo.lessonsPerPage || 5) * ((query.queryInfo.page || 1) - 1) || 0,
|
||
limit: query.queryInfo.lessonsPerPage || 5,
|
||
attributes: {
|
||
include: [
|
||
//добавляем счетчик учеников посетивших занятие
|
||
[
|
||
Sequelize.literal(`(SELECT COALESCE((SELECT CAST(COUNT(lesson_students.visit) AS INT) FROM lesson_students WHERE lessons.id = lesson_students.lesson_id AND lesson_students.visit GROUP BY lessons.id),0))`),
|
||
"visitCount",
|
||
],
|
||
],
|
||
},
|
||
include: [
|
||
{
|
||
model: models.students,
|
||
as: "students",
|
||
attributes: {
|
||
//Переносим аттрибут посещенного занятия из таблицы lesson_students
|
||
include: [
|
||
[
|
||
Sequelize.literal(
|
||
`(SELECT lesson_students.visit FROM lesson_students WHERE lesson_students.student_id = students.id and lesson_students.lesson_id = lessons.id)`
|
||
),
|
||
"visit",
|
||
],
|
||
],
|
||
},
|
||
through: {
|
||
model: models.lesson_students,
|
||
attributes: [],
|
||
},
|
||
},
|
||
{
|
||
model: models.teachers,
|
||
as: "teachers",
|
||
through: {
|
||
model: models.lesson_teachers,
|
||
attributes: [],
|
||
},
|
||
},
|
||
],
|
||
where: getWhereParametres(query.queryInfo),
|
||
order: [["date", "ASC"]],
|
||
});
|
||
|
||
res.status(200).json({ isSuccess: true, lessons });
|
||
}
|
||
|
||
async function checkBody(body) { //проверка параметров на формат и их данные
|
||
let errors = {};
|
||
|
||
if (Array.isArray(body.teacherIds)) { //проверка id учителей на нужный формат и значения
|
||
for (let id of body.teacherIds)
|
||
if (!Number.isInteger(id) || id < 1)
|
||
errors.teacherIds = "teacherId must be integer more than 0";
|
||
if (errors.teacherIds == undefined)
|
||
await models.teachers
|
||
.findAll({
|
||
where: {
|
||
id: {
|
||
[Op.in]: body.teacherIds,
|
||
},
|
||
},
|
||
})
|
||
.then(function (teachers) {
|
||
if (teachers.length != body.teacherIds.length)
|
||
errors.teacherIds = "You must use unique teacherIds";
|
||
})
|
||
.catch(function (err) {
|
||
errors.teacherIds = "Some teacherIds cant be found";
|
||
});
|
||
} else errors.teacherIds = "Wrong teacherIds format";
|
||
|
||
if (!(typeof body.title === "string" || body.title instanceof String)) //проверка названия на нужный формат и значения
|
||
errors.title = "Wrong title format";
|
||
|
||
if (Array.isArray(body.days)) { //проверка дней на нужный формат и значения
|
||
let days = [];
|
||
for (let day of body.days)
|
||
if (!Number.isInteger(day) || day < 0 || day > 6 || days.includes(day))
|
||
errors.days =
|
||
"day must be unique integer more than or equal 0 and less than or equal 6";
|
||
else days.push(day);
|
||
} else errors.days = "Wrong days format";
|
||
|
||
if (!moment(body.firstDate, "YYYY-MM-DD", true).isValid()) //проверка первой даты на нужный формат и значения
|
||
errors.firstDate = "Wrong firstDate format";
|
||
|
||
if (body.lessonsCount != undefined && body.lastDate != undefined) //проверка на отсутствие обоих параметров
|
||
errors.parametres = "You cant use lessonsCount and lastDate at the same time";
|
||
if (body.lessonsCount == undefined && body.lastDate == undefined) //проверка на присутствие обоих параметров
|
||
errors.parametres = "You have missed lessonsCount or lastDate";
|
||
|
||
if (body.lastDate && !moment(body.lastDate, "YYYY-MM-DD", true).isValid()) //проверка последней даты на нужный формат и значения
|
||
errors.lastDate = "Wrong lastDate format";
|
||
|
||
if (body.lessonsCount && (!Number.isInteger(body.lessonsCount) || body.lessonsCount < 1 || body.lessonsCount > 300))
|
||
errors.lessonsCount = "lessonsCount must be integer more than 0 and less than or equal 300"; //проверка количества занятий на нужный формат и значения
|
||
|
||
return errors;
|
||
}
|
||
|
||
async function create(req, res) {
|
||
let errors = await checkBody(req.body);
|
||
if (Object.keys(errors).length > 0) {
|
||
res.status(400).json({ isSuccess: false, errors });
|
||
} else {
|
||
let date = moment(req.body.firstDate);
|
||
let col = 0;
|
||
|
||
let data = []; //создаем массив занятий который необходимо записать в базу данных
|
||
while (col < (req.body.lessonsCount ? req.body.lessonsCount : 300) && date <= (req.body.lastDate? moment(req.body.lastDate) : moment(req.body.firstDate).add(1, "year"))) {
|
||
if (req.body.days.includes(date.day())) {
|
||
data.push({
|
||
title: req.body.title,
|
||
date: date.format("YYYY-MM-DD"),
|
||
});
|
||
col++;
|
||
}
|
||
date.add(1, "day");
|
||
}
|
||
let lessons = await models.lessons.bulkCreate(data);
|
||
|
||
let assotiations = [];
|
||
for await (let lesson of lessons)
|
||
for (let teacherId of req.body.teacherIds) {
|
||
assotiations.push({
|
||
lesson_id: lesson.dataValues.id,
|
||
teacher_id: teacherId,
|
||
});
|
||
}
|
||
await models.lesson_teachers.bulkCreate(assotiations);
|
||
|
||
res.status(201).json({isSuccess: true, lessonIds: lessons.map((lesson) => lesson.dataValues.id)});
|
||
}
|
||
}
|
||
|
||
module.exports = {
|
||
getAll,
|
||
create,
|
||
};
|