eta.db.query(sql, [req.session["userid"]], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
res.redirect("/apply?error=" + querystring.escape("We were unable to process your application due to an internal error."));
return;
}
let valueSql: string = "(?, ?, 1),".repeat(positions.length);
let sql: string = `
INSERT INTO ApplicantPosition
(id, position, count)
VALUES ${valueSql.substring(0, valueSql.length - 1)}
ON DUPLICATE KEY UPDATE
lastApplied = CURRENT_TIMESTAMP(),
count = count + 1`;
let params: string[] = [];
for (let i: number = 0; i < positions.length; i++) {
params.push(req.session["userid"]);
params.push(positions[i]);
}
eta.db.query(sql, params, (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
res.redirect("/apply?error=" + querystring.escape("We were unable to process your application due to an internal error."));
return;
}
res.redirect("/apply?success=true");
});
});
public render(req: express.Request, res: express.Response, callback: (env: { [key: string]: any }) => void): void {
let curDate: Date = new Date();
let sql: string = `
SELECT
UNIX_TIMESTAMP(date + interval 1 day - interval 1 second) * 1000 AS start,
UNIX_TIMESTAMP(date + interval 1 second) * 1000 AS end
FROM
DateClosed
WHERE
UNIX_TIMESTAMP(date) >= ? / 1000 AND
UNIX_TIMESTAMP(date) < ? / 1000`;
eta.db.query(sql, [req.query.from, req.query.to], (err: eta.DBError, closedRows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
let n: number = 0;
for (let i: number = 0; i < closedRows.length; i++) {
closedRows[i].id = n;
closedRows[i].url = "";
closedRows[i].class = "event-danger";
closedRows[i].title = "Closed";
}
sql = `
SELECT
UNIX_TIMESTAMP(DateExamJam.start) * 1000 AS start,
UNIX_TIMESTAMP(DateExamJam.end) * 1000 AS end,
Course.subject AS subject,
Course.number AS number
FROM
DateExamJam
LEFT JOIN Course ON
DateExamJam.course = Course.id
WHERE
UNIX_TIMESTAMP(start) >= ? / 1000 AND
UNIX_TIMESTAMP(end) < ? / 1000`;
eta.db.query(sql, [req.query.from, req.query.to], (err: eta.DBError, examJamRows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
for (let i: number = 0; i < examJamRows.length; i++) {
examJamRows[i].id = n;
examJamRows[i].url = "";
examJamRows[i].class = "event-info";
examJamRows[i].title = examJamRows[i].subject + " " + examJamRows[i].number + " Exam Jam @ " + eta.time.getMinuteTime(new Date(examJamRows[i].start)).toString();
closedRows.push(examJamRows[i]);
}
callback({
raw: JSON.stringify({
"success": 1,
"result": closedRows
})
});
});
});
}
eta.athlete.isDirector(req.session["userid"], (isAthleteDirector: boolean) => {
if (isAthleteDirector === null) {
callback({ errcode: eta.http.InternalError });
return;
}
if (!isAthleteDirector) {
res.redirect("/track/index");
return;
}
let isPastWeek: boolean = !!req.query.isPastWeek;
let pastWeekSql: string = `AND DATE(Visit.timeIn) >= (CURDATE() - INTERVAL 7 DAY)`;
let sql: string = `
SELECT
Person.id,
Person.firstName,
Person.lastName,
VisitCount.count AS visitCount,
VisitCount.duration AS visitDuration,
VisitCount.timesOut AS visitTimesOut,
VisitCount.lastTimeIn AS visitLastTimeIn
FROM
Athlete
LEFT JOIN Person ON
Athlete.id = Person.id
LEFT JOIN (
SELECT
Visit.student AS student,
COUNT(DISTINCT Visit.student, Visit.timeIn) AS count,
ROUND(SUM(TIME_TO_SEC(TIMEDIFF(Visit.timeOut, Visit.timeIn))) / 3600, 2) AS duration,
GROUP_CONCAT(IFNULL(Visit.timeOut, 'NULL') ORDER BY Visit.timeIn DESC SEPARATOR ', ') AS timesOut,
DATE(MAX(Visit.timeIn)) = CURDATE() AS lastTimeIn
FROM
Visit
WHERE
Visit.term = ?
${isPastWeek ? pastWeekSql : ""}
GROUP BY
Visit.student
) VisitCount ON
Athlete.id = VisitCount.student
WHERE
VisitCount.count > 0`;
eta.db.query(sql, [eta.term.getCurrent().id], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
for (let i: number = 0; i < rows.length; i++) {
let lastTimeOut: string = rows[i].visitTimesOut.split(", ")[0];
rows[i].isCurrent = (lastTimeOut === "NULL") && rows[i].visitLastTimeIn;
}
callback({
"students": rows,
"isPastWeek": isPastWeek
})
});
});
eta.wiziq.schedule(start, end, rooms[i], (err: Error, result?: any) => {
if (err) {
return eta.logger.warn("Couldn't schedule room " + rooms[i] + ": " + err.message);
}
let url: string = result.CommonAttendeeUrl;
eta.db.query(sql, [rooms[i], url], (err: eta.DBError, rows: any[]) => {
if (err) {
return eta.logger.dbError(err);
}
});
});
eta.section.getByID(req.query.id, (section: eta.Section) => {
if (!section) {
callback({ errcode: eta.http.NotFound });
return;
}
// the prof isn't the one logged in
if (section.professor != req.session["userid"]) {
callback({ errcode: eta.http.Forbidden });
return;
}
let isPastWeek: boolean = !!req.query.isPastWeek;
let pastWeekSql: string = `AND DATE(Visit.timeIn) >= (CURDATE() - INTERVAL 7 DAY)`;
let sql: string = `
SELECT
Person.id,
Person.firstName,
Person.lastName,
VisitCount.count AS visitCount,
VisitCount.duration AS visitDuration
FROM
StudentSection
LEFT JOIN Person ON
StudentSection.student = Person.id
LEFT JOIN (
SELECT
Visit.student AS student,
COUNT(DISTINCT Visit.student, Visit.timeIn) AS count,
ROUND(SUM(TIME_TO_SEC(TIMEDIFF(Visit.timeOut, Visit.timeIn))) / 3600, 2) AS duration
FROM
Visit
WHERE
Visit.section REGEXP ?
${isPastWeek ? pastWeekSql : ""}
GROUP BY Visit.student
) VisitCount ON
StudentSection.student = VisitCount.student
WHERE
StudentSection.section = ? AND
StudentSection.status = 'E'`;
eta.db.query(sql, [req.query.id, req.query.id], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
callback({
"section": section,
"students": rows,
"isPastWeek": isPastWeek
});
});
});
eta.student.get(req.session["userid"], (student: eta.Student) => {
if (!student) {
callback({ errcode: eta.http.InternalError });
return;
}
if (student.sections.length == 0
|| !eta.section.hasCurrentSections(student.sections, eta.term.getCurrent().id)) {
res.redirect("/track/index");
return;
}
let sql: string = `
SELECT
LOWER(TIME_FORMAT(Visit.timeIn, '%l:%i %p')) AS timeIn,
LOWER(TIME_FORMAT(Visit.timeOut, '%l:%i %p')) AS timeOut,
ROUND(TIME_TO_SEC(TIMEDIFF(Visit.timeOut, Visit.timeIn)) / 3600, 2) AS totalHours,
DATE_FORMAT(Visit.timeIn, '%c/%e/%Y') AS date,
GROUP_CONCAT(DISTINCT CONCAT(Course.subject, ' ', Course.number) ORDER BY Course.subject, Course.number SEPARATOR ', ') AS courses
FROM
Visit
RIGHT JOIN Section ON
Visit.section REGEXP Section.id
RIGHT JOIN Course ON
Section.course = Course.id
WHERE
Visit.term = ? AND
Visit.student = ?
GROUP BY Visit.student, Visit.timeIn
ORDER BY Visit.timeIn`;
eta.db.query(sql, [eta.term.getCurrent().id, req.session["userid"]], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
let totalHours: number = 0;
for (let i: number = 0; i < rows.length; i++) {
totalHours += rows[i].totalHours;
}
eta.person.getByID(req.session["userid"], (person: eta.Person) => {
if (!person) {
callback({ errcode: eta.http.InternalError });
return;
}
callback({
"visits": rows,
"name": person.firstName + " " + person.lastName,
"totalHours": totalHours.toFixed(2)
});
});
});
});
eta.db.query(sql, [req.body.start, req.body.end], (err: eta.DBError, examJamDays: any[]) => {
if (err) {
eta.logger.dbError(err);
return callback({ errcode: eta.http.InternalError });
}
sql = `
SELECT
1 AS isClosed,
date AS day
FROM
DateClosed
WHERE
date > DATE(?) AND
date < DATE(?)`;
eta.db.query(sql, [req.body.start, req.body.end], (err: eta.DBError, rawDays: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
let days: {
[date: string]: {
isExamJam: boolean;
isClosed: boolean;
}
} = {};
for (let i: number = 0; i < examJamDays.length; i++) {
rawDays.push(examJamDays[i]);
}
for (let i: number = 0; i < rawDays.length; i++) {
let day: string = eta.time.getStandardDate(rawDays[i].day);
if (!days[day]) {
days[day] = {
"isExamJam": false,
"isClosed": false
};
}
if (rawDays[i].isExamJam) {
days[day].isExamJam = true;
}
if (rawDays[i].isClosed) {
days[day].isClosed = true;
}
}
callback({
"raw": JSON.stringify(days)
});
});
});
eta.person.getByID(req.body.student, (person: eta.Person) => {
if (!person) {
callback({ errcode: eta.http.InternalError });
return;
}
let sectionSql: string = "AND Section.id = ?";
let params: string[] = [req.body.student, eta.term.getCurrent().id];
if (req.body.section) {
params.push(req.body.section);
}
let sql: string = `
SELECT
GROUP_CONCAT(DISTINCT CONCAT(Course.subject, ' ', Course.number) ORDER BY Course.subject, Course.number SEPARATOR ', ') AS "0",
DATE_FORMAT(Visit.timeIn, '%c/%e/%Y') AS "1",
LOWER(TIME_FORMAT(Visit.timeIn, '%l:%i %p')) AS "2",
IFNULL(
LOWER(TIME_FORMAT(Visit.timeOut, '%l:%i %p')),
'N/A'
) AS "3",
IFNULL(
ROUND(TIME_TO_SEC(TIMEDIFF(Visit.timeOut, Visit.timeIn)) / 3600, 2),
'0.00'
) AS "4"
FROM
Visit
RIGHT JOIN Section ON
Visit.section REGEXP Section.id
RIGHT JOIN Course ON
Section.course = Course.id
WHERE
Visit.student = ? AND
Visit.term = ?
${req.body.section ? sectionSql : ""}
GROUP BY Visit.timeIn`;
eta.db.query(sql, params, (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
callback({
"raw": JSON.stringify({
"firstName": person.firstName,
"lastName": person.lastName,
"visits": rows
})
});
});
});
public render(req: express.Request, res: express.Response, callback: (env: { [key: string]: any }) => void): void {
// check if a position was selected
let positions: string[] = [];
for (let param in req.body) {
if (param.startsWith("Position:")) {
positions.push(param.split(":")[1]);
}
}
if (positions.length == 0) {
res.redirect("/apply?error=" + querystring.escape("Please select positions before submitting your application."));
return;
}
let sql: string = `
INSERT IGNORE INTO Applicant
(id, notes, workStudy, phone, altEmail)
VALUES (?, '', 0, '', '')`;
eta.db.query(sql, [req.session["userid"]], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
res.redirect("/apply?error=" + querystring.escape("We were unable to process your application due to an internal error."));
return;
}
let valueSql: string = "(?, ?, 1),".repeat(positions.length);
let sql: string = `
INSERT INTO ApplicantPosition
(id, position, count)
VALUES ${valueSql.substring(0, valueSql.length - 1)}
ON DUPLICATE KEY UPDATE
lastApplied = CURRENT_TIMESTAMP(),
count = count + 1`;
let params: string[] = [];
for (let i: number = 0; i < positions.length; i++) {
params.push(req.session["userid"]);
params.push(positions[i]);
}
eta.db.query(sql, params, (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
res.redirect("/apply?error=" + querystring.escape("We were unable to process your application due to an internal error."));
return;
}
res.redirect("/apply?success=true");
});
});
}
public render(req: express.Request, res: express.Response, callback: (env: { [key: string]: any }) => void): void {
let sql: string = `
SELECT
Position.name,
GROUP_CONCAT(
DISTINCT CONCAT(
Course.subject, ' ', Course.number
)
ORDER BY
Course.subject ASC,
Course.number ASC
) AS courses
FROM
Position
LEFT JOIN Course ON
Course.tutor = Position.name
WHERE Position.open = 1
GROUP BY Position.name
ORDER BY Position.name ASC`;
eta.db.query(sql, [], (err: eta.DBError, rows: any[]) => {
if (err) {
eta.logger.dbError(err);
callback({ errcode: eta.http.InternalError });
return;
}
eta.person.getByID(req.session["userid"], (person: eta.Person) => {
if (!person) {
// this person doesn't exist
eta.logger.warn("Person " + req.session["userid"] + " does not exist.");
callback({ errcode: eta.http.InternalError });
return;
}
callback({
"isAvailable": rows.length != 0,
"firstName": person.firstName,
"lastName": person.lastName,
"error": req.query.error,
"success": req.query.success,
"positions": rows
});
});
});
}
