TypeScript eta-lib.db類代碼示例

 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
             });
         });
     });
 }