11주차 실습. 인하대 수강신청 홈페이지 구현하기

변현섭·2023년 11월 6일

데이터베이스설계

목록 보기

17/22

1) 인하대 수강신청 홈페이지 카피하기

① 메모장을 열고, 아래의 내용을 입력한다.

CREATE SCHEMA IF NOT EXISTS WEEK11_INHA_DB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE WEEK11_INHA_DB;

CREATE TABLE Building
( ID INT NOT NULL AUTO_INCREMENT,
    Bid             INT              NOT NULL,
  Bname           VARCHAR(20)      NOT NULL,
PRIMARY KEY   (ID));

CREATE TABLE Room
( ID INT NOT NULL AUTO_INCREMENT,
  Rid             VARCHAR(10)       NOT NULL,
  Rname           VARCHAR(100)       NOT NULL,
  capacity        INT,
  Bid			  INT               NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Bid) REFERENCES Building(ID));

CREATE TABLE Department
( ID INT NOT NULL AUTO_INCREMENT,
  Did             VARCHAR(10)       NOT NULL,
  Dname           VARCHAR(100)       NOT NULL,
  Demail		  VARCHAR(45),
  Dphonenum		  VARCHAR(10)       NOT NULL,
  Bid			  INT               NOT NULL,
  Rid			  INT       NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Bid) REFERENCES Building(ID),
FOREIGN KEY (Rid) REFERENCES Room(ID));

CREATE TABLE Class
( ID INT NOT NULL AUTO_INCREMENT,
  Cid             VARCHAR(10)       NOT NULL,
  Name           VARCHAR(45)       NOT NULL,
  Professor       VARCHAR(100)       NOT NULL,
  Number_of_participant	  INT,
  Bid             INT               NOT NULL,
  Did			  INT       NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Bid) REFERENCES Building(ID),
FOREIGN KEY (Did) REFERENCES Department(ID));

CREATE TABLE Student
( ID             INT    	        NOT NULL        AUTO_INCREMENT,
  Sname           VARCHAR(20)       NOT NULL,
  Semail          VARCHAR(45),
  Sphonenum		  VARCHAR(11),
  StudentId		  INT               NOT NULL,
  password		  VARCHAR(20)       NOT NULL,
  Did			  INT               NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (Did) REFERENCES Department(ID));

CREATE TABLE class_student
(
  Student_Id INT NOT NULL,
  Class_Id INT NOT NULL,
  FOREIGN KEY (Student_Id) REFERENCES Student(ID),
  FOREIGN KEY (Class_Id) REFERENCES Class(ID)
);

INSERT INTO Building 
VALUES      (null, '5','Building 5'), (null, '10','High Tech Center');

INSERT INTO Room 
VALUES  (null, 'h-416','Office of Information Technology','10','2'),
            (null, 'h-232','Major Theory Classroom','80','2'),
            (null, 'h-424','Major Practicum Classroom','75','2');
            
INSERT INTO Department 
VALUES      (null, 'ICE','Information and Communication Engineering','','0328607431','2','2');

INSERT INTO Class 
VALUES     (null, 'ICE4016001','Database','Wonik Choi','74','2','1'),
		   (null, 'ICE3001003','Signals and Systems','Wonik Choi2','54','2','1'),
           (null, 'ICE3014001','Operating System','Wonik Choi3','47','2','1'),
           (null, 'ICE4010001','Telecommunications','Wonik Choi4','62','2','1E'),
           (null, 'ICE2004002','Data Structures','Wonik Choi','0','2','1');

INSERT INTO Student(ID, Sname, Semail, Sphonenum, StudentId, password, Did) 
VALUES      (null, 'Lee','jojo@naver.com','01011112222','12000000','1234', '1'),
            (null, 'Kim','yubi@naver.com','01022223333','12000001','1234', '1'),
            (null, 'Park','son@naver.com','01033334444','12000002','1234', '1');

INSERT INTO class_student 
VALUES      (1, 1);

② working 디렉토리 안에 week11 디렉토리를 생성한다.

③ 메모장을 week11.sql이라는 이름으로 week11 디렉토리 안에 저장한다.

④ cmd에서 week11 디렉토리로 이동한 후 아래의 명령을 입력한다.

npm init
npm install express mysql2 body-parser nodemon morgan dotenv express-session
npm install @babel/node @babel/core @babel/preset-env
npm link hbs
npm install livereload connect-livereload
npm install cookie-parser express-session

⑤ mysql을 실행하고 아래의 명령을 입력한다.

source ./week11.sql
use week11
show tables; // 데이터베이스가 잘 생성되었는지 확인한다.

2) VSCode

① VSCode에서 week11을 열고 아래와 같이 파일을 생성한다.

inhaLogo.png에는 아래의 파일을 넣으면 된다.
package.json의 scripts 부분을 아래와 같이 수정한다.

"scripts": {
  "test": "echo \"Error: no test specified\" && exit 1",
  "start" : "nodemon --exec babel-node index.js"
},

② sql.js에 아래의 내용을 입력한다.

import mysql from "mysql2";

const pool = mysql.createPool(
  process.env.JAWSDB_URL ?? {
    host: 'localhost',
    user: 'root',
    database: 'WEEK11_INHA_DB',
    password: '{비밀번호}',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
  }
);

// async / await 사용
const promisePool = pool.promise();

// selec query
export const selectSql = {
  getUsers: async () => {
    const [rows] = await promisePool.query(`select * from student`);
    return rows;
  },
  //TODO
}

export const createSql = {
  addClass: async (data) => {
    const uid = await promisePool.query(`select Id from Student where StudentId=${data.sId}`);
    console.log(uid);
    const results = await promisePool.query (
      `insert into class_student values (${uid[0][0].Id}, ${data.cId});`
    )
    return results[0];
  }
}

③ home.css에 아래의 내용을 입력한다.

body {
    height: 100vh;
    display: flex;
    padding: 0px;
    flex-direction: column;
  }
  
  .content {
    display: flex;
    flex-direction: row;
    padding: 0;
  }
  
  .header {
    display: flex;
    margin-top: 16px;
    margin-left: 16px;
    flex-direction: row;
    margin-bottom: 16px;
    align-items: end;
  }
  
  .header-logo {
    display: flex;
    align-items: center;
  }
  
  .header-info {
    margin-left: 102px;
    height: 44px;
    border: 2px solid blue;
    padding: 2px 8px;
    display: flex;
    align-items: center;
  }
  
  .header-info_title {
    font-size: 22px;
  }
  
  .header-info_title_strong {
    color: blue;
  }
  
  img {
    max-width: 112px;
  }
  
  .logo-text {
    font-size: 24px;
    color: #0770bb;
    margin-left: 4px;
    margin-bottom: 5px;
  }
  
  .side {
    display: flex;
    flex-direction: column;
    min-width: 250px;
  }
  
  .side-frame {
    display: flex;
    justify-content: center;
    align-items: center;
    flex-direction: column;
  }
  
  .loginform-input {
    display: flex;
    flex-direction: column;
  }
  
  form {
    display: flex;
    padding: 8px;
    flex-direction: row;
    background-color: #f5f5f5;
  }
  
  .label-text {
    display: inline-block;
    font-size: 13px;
    width: 60px;
    line-height: 18px;
  }
  
  .loginform-input_forget {
    font-size: 13px;
  }
  
  input {
    width: 86px;
    height: 24px;
  }
  
  .loginform-btnDiv {
    margin-left: 8px;
  }
  
  .loginform-btnDiv_btn {
    width: 65px;
    height: 52px;
    background-color: #690;
    font-size: 13px;
    border: none;
  }
  
  .side-menu_list {
    margin-top: 8px;
    border-bottom: #e5e5e5 solid 1px;
    padding: 8px;
    display: flex;
    align-items: center;
    justify-content: space-between;
    font-size: 16px;
  }
  
  .side-menu_list:hover {
    background-color: blue;
    color: white;
  }
  
  .main {
    margin-left: 16px;
    display: flex;
    flex-direction: column;
    width: 100%;
  }
  
  .main-top_table {
    margin-top: 8px;
  }
  
  .main-top_title {
    color: #666;
  }
  
  table {
    display: table;
    width: 100%;
  }
  tr,
  td {
    display: flex;
    align-items: center;
    justify-content: center;
    border: #97bbda solid 1px !important;
    height: 30px;
  }
  
  .main-top_table_head_first {
    width: 40%;
    color: white;
  }
  
  .main-top_table_head_second {
    width: 60%;
    color: white;
  }
  
  .main-top_table_body_first {
    font-size: 13px;
    padding-left: 4px;
    width: 40%;
    color: #666;
    display: flex;
    justify-content: start;
  }
  
  .main-top_table_body_second {
    font-size: 13px;
    padding-left: 4px;
    width: 60%;
    color: #666;
    display: flex;
    justify-content: start;
  }
  
  .main-bottom {
    margin-top: 16px;
  }
  
  .main-bottom_title {
    color: #127c97;
    font-size: 20px;
    display: flex;
    margin-bottom: 20px;
  }
  
  .main-bottom_table_head_first {
    width: 15%;
    color: white;
  }
  
  .main-bottom_table_head_second {
    width: 15%;
    color: white;
  }
  
  .main-bottom_table_head_third {
    width: 40%;
    color: white;
  }
  
  .main-bottom_table_head_fourth {
    width: 15%;
    color: white;
  }
  
  .main-bottom_table_head_fifth {
    width: 15%;
    color: white;
  }
  
  .main-bottom_table_body_first,
  .main-bottom_table_body_second,
  .main-bottom_table_body_fourth,
  .main-bottom_table_body_fifth {
    font-size: 13px;
    padding-left: 4px;
    width: 15%;
    color: #666;
    display: flex;
    justify-content: center;
  }
  
  .main-bottom_table_body_third {
    font-size: 13px;
    padding-left: 4px;
    width: 40%;
    color: #666;
    display: flex;
    justify-content: start;
  }

④ init.css에 아래의 내용을 입력한다.

html,
body,
div,
span,
applet,
object,
iframe,
h1,
h2,
h3,
h4,
h5,
h6,
p,
blockquote,
pre,
a,
abbr,
acronym,
address,
big,
cite,
code,
del,
dfn,
em,
img,
ins,
kbd,
q,
s,
samp,
small,
strike,
strong,
sub,
sup,
tt,
var,
b,
u,
i,
center,
dl,
dt,
dd,
ol,
ul,
li,
fieldset,
form,
label,
legend,
table,
caption,
tbody,
tfoot,
thead,
tr,
th,
td,
article,
aside,
canvas,
details,
embed,
figure,
figcaption,
footer,
header,
hgroup,
menu,
nav,
output,
ruby,
section,
summary,
time,
mark,
audio,
video {
  margin: 0;
  padding: 0;
  border: 0;
  font-size: 100%;
  font: inherit;
  vertical-align: baseline;
}
/* HTML5 display-role reset for older browsers */
article,
aside,
details,
figcaption,
figure,
footer,
header,
hgroup,
menu,
nav,
section {
  display: block;
}
body {
  line-height: 1;
}
ol,
ul {
  list-style: none;
}
blockquote,
q {
  quotes: none;
}
blockquote:before,
blockquote:after,
q:before,
q:after {
  content: "";
  content: none;
}
table {
  border-collapse: collapse;
  border-spacing: 0;
}

⑤ main.css에 아래의 내용을 입력한다.

body {
  height: 100vh;
  display: flex;
  padding: 0px;
  flex-direction: column;
}

.content {
  display: flex;
  flex-direction: row;
  padding: 0;
}

.header {
  display: flex;
  margin-top: 16px;
  margin-left: 16px;
  flex-direction: row;
  margin-bottom: 16px;
  align-items: end;
}

.header-logo {
  display: flex;
  align-items: center;
}

.header-info {
  margin-left: 102px;
  height: 44px;
  border: 2px solid blue;
  padding: 2px 8px;
  display: flex;
  align-items: center;
}

.header-info_title {
  font-size: 22px;
}

.header-info_title_strong {
  color: blue;
}

img {
  max-width: 112px;
}

.logo-text {
  font-size: 24px;
  color: #0770bb;
  margin-left: 4px;
  margin-bottom: 5px;
}

.side {
  display: flex;
  flex-direction: column;
  max-width: 250px;
  border-right: #e5e5e5 solid 1px;
}

.side-frame {
  display: flex;
  justify-content: center;
  align-items: center;
  flex-direction: column;
}

.side-frame_id {
  display: flex;
  width: 234px;
  height: 60px;
  padding: 8px;
  flex-direction: row;
  background-color: #f5f5f5;
  align-items: center;
  justify-content: space-between;
}

.userinfo {
  margin-left: 16px;
}

.logout {
  height: 52px;
}

.side-menu_list {
  margin-top: 8px;
  border-bottom: #e5e5e5 solid 1px;
  padding: 8px;
  display: flex;
  align-items: center;
  justify-content: space-between;
  font-size: 16px;
}

.side-menu_list:hover {
  background-color: blue;
  color: white;
}

.main {
  margin-left: 16px;
  display: flex;
  flex-direction: column;
  width: 100%;
}

.main-top_table {
  margin-top: 8px;
}

.main-top_title {
  color: #666;
}

table {
  display: table;
  width: 100%;
}
tr,
td {
  display: flex;
  align-items: center;
  justify-content: center;
  border: #97bbda solid 1px !important;
  height: 30px;
}

.main-top_table_head_first {
  width: 40%;
  color: white;
}

.main-top_table_head_second {
  width: 60%;
  color: white;
}

.main-top_table_body_first {
  font-size: 13px;
  padding-left: 4px;
  width: 40%;
  color: #666;
  display: flex;
  justify-content: start;
}

.main-top_table_body_second {
  font-size: 13px;
  padding-left: 4px;
  width: 60%;
  color: #666;
  display: flex;
  justify-content: start;
}

.main-bottom {
  margin-top: 16px;
}

.main-bottom_title {
  color: #127c97;
  font-size: 20px;
  display: flex;
  margin-bottom: 20px;
}

.main-bottom_table_head_first {
  width: 15%;
  color: white;
}

.main-bottom_table_head_second {
  width: 15%;
  color: white;
}

.main-bottom_table_head_third {
  width: 40%;
  color: white;
}

.main-bottom_table_head_fourth {
  width: 15%;
  color: white;
}

.main-bottom_table_head_fifth {
  width: 15%;
  color: white;
}

.main-bottom_table_body_first,
.main-bottom_table_body_second,
.main-bottom_table_body_fourth,
.main-bottom_table_body_fifth {
  font-size: 13px;
  padding-left: 4px;
  width: 15%;
  color: #666;
  display: flex;
  justify-content: center;
}

.main-bottom_table_body_third {
  font-size: 13px;
  padding-left: 4px;
  width: 40%;
  color: #666;
  display: flex;
  justify-content: start;
}

⑥ select.css에 아래의 내용을 입력한다.

body {
  height: 100vh;
  display: flex;
  padding: 0px;
  flex-direction: column;
}

.content {
  display: flex;
  flex-direction: row;
  padding: 0;
}

.header {
  display: flex;
  margin-top: 16px;
  margin-left: 16px;
  flex-direction: row;
  margin-bottom: 16px;
  align-items: end;
}

.header-logo {
  display: flex;
  align-items: center;
}

.header-info {
  margin-left: 102px;
  height: 44px;
  border: 2px solid blue;
  padding: 2px 8px;
  display: flex;
  align-items: center;
}

.header-info_title {
  font-size: 22px;
}

.header-info_title_strong {
  color: blue;
}

img {
  max-width: 112px;
}

.logo-text {
  font-size: 24px;
  color: #0770bb;
  margin-left: 4px;
  margin-bottom: 5px;
}

.side {
  display: flex;
  flex-direction: column;
  max-width: 250px;
  border-right: #e5e5e5 solid 1px;
}

.side-frame {
  display: flex;
  justify-content: center;
  align-items: center;
  flex-direction: column;
}

.side-frame_id {
  display: flex;
  width: 234px;
  height: 60px;
  padding: 8px;
  flex-direction: row;
  background-color: #f5f5f5;
  align-items: center;
  justify-content: space-between;
}

.userinfo {
  margin-left: 16px;
}

.logout {
  height: 52px;
}

.side-menu_list {
  margin-top: 8px;
  border-bottom: #e5e5e5 solid 1px;
  padding: 8px;
  display: flex;
  align-items: center;
  justify-content: space-between;
  font-size: 16px;
}

.side-menu_list:hover {
  background-color: blue;
  color: white;
}

.main {
  margin-left: 16px;
  display: flex;
  flex-direction: column;
  width: 100%;
}

.main-top_table {
  margin-top: 8px;
}

.main-top_title {
  color: #666;
}

table {
  display: table;
  width: 100%;
}
tr,
td {
  /* display: flex; */
  text-align: center;
  vertical-align:middle;
  align-items: center;
  justify-content: center;
  border: #97bbda solid 1px !important;
  height: 30px;
}

.main-top_table_head_first {
  width: 40%;
  color: white;
}

.main-top_table_head_second {
  width: 60%;
  color: white;
}

.main-top_table_body_first {
  font-size: 13px;
  padding-left: 4px;
  width: 40%;
  color: #666;
  display: flex;
  justify-content: start;
}

.main-top_table_body_second {
  font-size: 13px;
  padding-left: 4px;
  width: 60%;
  color: #666;
  display: flex;
  justify-content: start;
}

.info {
  font-size: 30px !important;
  font-weight: bold !important;
  padding: 20px 10px 20px 0px !important;
}

b {
  font-weight: bold !important;
}

⑦ login.js에 아래의 내용을 입력한다.

import cookieParser from "cookie-parser";
import express from "express";
import expressSession from 'express-session';
import { selectSql } from "../database/sql";

const router = express.Router();

router.use(cookieParser());
router.use(expressSession({
    secret: 'dilab',
    resave: true,
    saveUninitialized: true,
}))

router.get('/', (req, res) => {
    if (req.cookies.user) {
        res.render('main', { 
            'user': req.cookies.user,
        });
    } else {
        res.render('login');
    }
});

router.get('/logout', (req, res) => {
    if (req.cookies.user) {
        res.clearCookie('user')
        res.redirect("/");
    } else {
        res.redirect("/");
    }
})

router.post('/', async (req, res) => {
    const vars = req.body;
    const users = await selectSql.getUsers();
    var whoAmI = 1;
    let checkLogin = false;

    users.map((user) => {
        if (vars.id == user.StudentId && vars.password === user.password) {
            checkLogin = true;
            whoAmI = user.StudentId;
        }
        console.log("check login: ", checkLogin);
    })

    if (checkLogin) {
        res.cookie('user', whoAmI, {
            expires: new Date(Date.now() + 3600000), // ms 단위 (3600000: 1시간 유효)
            httpOnly: true
        })
        res.redirect('/');
    } else {
        res.redirect('/');
    }
})

module.exports = router;

⑧ logout.js에 아래의 내용을 입력한다.

import cookieParser from "cookie-parser";
import express from "express";
import expressSession from 'express-session';
const router = express.Router();

router.use(cookieParser());
router.use(expressSession({
    secret: 'dilab',
    resave: true,
    saveUninitialized: true,
}));

router.get('/logout', (req, res) => {
    if (req.cookies.user) {
        res.clearCookie('user');
        res.redirect("/");
    } else {
        res.redirect("/");
    }
})

module.exports = router;

⑨ select.js에 아래의 내용을 입력한다.

import express from "express";
// TODO
// sql import

const router = express.Router();

router.get('/', async function (req, res) {
    // TODO
    
    if (req.cookies.user) {
        res.render('select', { user: req.cookies.user });
    } else {
        res.render('/')
    }

});

router.post('/', async(req, res) => {
    // TODO
    const data = {
        cId: req.body.applyBtn,
        sId: req.cookies.user,
    };
});

module.exports = router;

⑩ layout.hbs에 아래의 내용을 입력한다.

<html>
  <head>
    <title>{{title}}</title>
    <link rel="stylesheet" href="init.css" type="text/css" />
  </head>
  <body>
    {{{body}}}
  </body>
</html>

⑪ login.hbs에 아래의 내용을 입력한다.

<head>
  <link rel="stylesheet" type="text/css" href="home.css" />
  <link
    href="https://fonts.googleapis.com/icon?family=Material+Icons"
    rel="stylesheet"
  />
  <link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Material+Symbols+Outlined:opsz,wght,FILL,GRAD@48,400,0,0" />
</head>
<div class="header">
  <div class="header-logo">
    <div class="logo"><img
        src="./img/inhaLogo.png"
        alt="INHA UNIVERSITY"
      /></div>
    <span class="logo-text">Enrolment</span>
  </div>

  <div class="header-info">
    <span class="header-info_title">Information on transitional measures related to 2021 curriculum reform (<span
        class="header-info_title_strong"
      >required for student numbers prior to 2020</span>) -> View details</span>
  </div>
</div>

<div class="content">
<div class="side">
  <div class="side-frame">
    <form id="loginform" method="post" action="/">
      <div class="loginform-input">
        <div class="loginform-input_id">
          <label for="id" class="label-text">Id</label>
          <input
            class="loingform-input_id_input"
            name="id"
            type="text"
            required
          />
        </div>
        <div style="margin-top:8px;" class="loginform-input_pwd">
          <label for="password" class="label-text">Password</label>
          <input
            class="loginform-input_pwd_input"
            name="password"
            type="password"
            required
          />
        </div>
        <div style="margin-top:8px">
          <span class="loginform-input_forget">Find ID/Password</span>
        </div>
      </div>
      <div class="loginform-btnDiv">
        <button class="loginform-btnDiv_btn" type="submit">Login</button>
      </div>
    </form>
  </div>
  <div class="side-menu">
      <div class="side-menu_list">
        <span>Course information</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Lecture diagnosis</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Enrolment</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Grade confirmation</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Seasonal semester</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Subject inquiry</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Reference Link</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>privacy policy</span>
        <span class="material-icons">add</span>
      </div>

    </div>
  </div>
  <div class="main">
    <div class="main-top">
      <span class="main-top_title">Main course registration schedule for the 2nd semester of 2023</span>
      <div class="main-top_table">
        <table>
          <tr style="background-color: #0770bb;">
            <td class="main-top_table_head_first">Date</td>
            <td class="main-top_table_head_second">detail</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 7. 22 (Fri) 10:00 ~ 7. 26 (Tue) 16:00</td>
            <td class="main-top_table_body_second">Priority course registration date</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 10 (Wed) 10:00 ~ 16:00</td>
            <td class="main-top_table_body_second">Major, required course registration (possible to take courses in engineering and software convergence colleges, no retakes, limited number of students per grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 11 (Thursday) 10:00 ~ 16:00</td>
            <td class="main-top_table_body_second">Major, required course registration (can take courses at colleges and universities except engineering and software convergence colleges; restrictions are the same as above)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 17 (Wed) 10:00 ~ 13:00</td>
            <td class="main-top_table_body_second">General course registration (re-enrollment is not allowed, number of students limited by grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 17 (Wed) 14:00 ~ 16:00</td>
            <td class="main-top_table_body_second">General course registration (re-enrollment possible, no limit on number of students by grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">August 29, 2023 (Monday)</td>
            <td class="main-top_table_body_second">2023-2nd semester begins</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 9. 2 (Fri) 08:30 ~ 19:00</td>
            <td class="main-top_table_body_second">Course registration change date</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 9. 26 (Mon) 09:00 ~ 9. 28 (Wed) 24:00</td>
            <td class="main-top_table_body_second">Course withdrawal period</td>
          </tr>
        </table>
      </div>
    </div>
    <div class="main-bottom">
      <span class="main-bottom_title">Notice regarding course registration</span>
      <div class="main-bottom_table">
        <table>
          <tr style="background-color: #0770bb;">
            <td class="main-bottom_table_head_first">number</td>
            <td class="main-bottom_table_head_second">attach</td>
            <td class="main-bottom_table_head_third">title</td>
            <td class="main-bottom_table_head_fourth">Registration date</td>
            <td class="main-bottom_table_head_fifth">Writer</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3150</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Notice on allowing pop-ups when registering for courses</td>
            <td class="main-bottom_table_body_fourth">2020-01-20</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3061</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Information on restrictions on taking classes between professors’ children</td>
            <td class="main-bottom_table_body_fourth">2019-07-22</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">2926</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">Notice on changes to additional 3 credits conditions for course registration (applies from 2018-1st semester course registration)</td>
            <td class="main-bottom_table_body_fourth">2018-07-25</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3318</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[For undergraduate students] Notice on implementation of 2021 statutory compulsory education (online education)</td>
            <td class="main-bottom_table_body_fourth">2021-01-13</td>
            <td class="main-bottom_table_body_fifth">Gender Equality Counseling Center</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3023</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">
Information on course credits (21 credits) for new students in the College of Engineering and College of Natural Sciences (applicable from freshmen in 2019</td>
            <td class="main-bottom_table_body_fourth">2019-02-19</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3586</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">
[Frontier College] Information on transitional measures following the reorganization of the liberal arts curriculum for the 2021 school year (basic liberal arts/core liberal arts)</td>
            <td class="main-bottom_table_body_fourth">2022-07-26</td>
            <td class="main-bottom_table_body_fifth">Frontier Undergraduate University Administration</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3616</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Information on 2nd canceled courses for the 2nd semester of the 2022 academic year</td>
            <td class="main-bottom_table_body_fourth">2022-09-05</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3615</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Future Automotive Business Group] Notice on changes to Object-Oriented Programming (FVE3010-001) classroom</td>
            <td class="main-bottom_table_body_fourth">2022-08-31</td>
            <td class="main-bottom_table_body_fifth">Future Automobile Business Division</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3614</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Department of Business Administration] BUS3204-005 Human Resource Management Timetable Change Notice</td>
            <td class="main-bottom_table_body_fourth">2022-08-26</td>
            <td class="main-bottom_table_body_fifth">Business Administration</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3613</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[English Education Department] Notice on classroom changes for the 2nd semester of 2022</td>
            <td class="main-bottom_table_body_fourth">2022-08-26</td>
            <td class="main-bottom_table_body_fifth">English Education Department</td>
          </tr>
    </div>
  </div>
</div>

⑫ main.hbs에 아래의 내용을 입력한다.

<head>
  <link rel="stylesheet" type="text/css" href="main.css" />
  <link
    href="https://fonts.googleapis.com/icon?family=Material+Icons"
    rel="stylesheet"
  />
  <link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Material+Symbols+Outlined:opsz,wght,FILL,GRAD@48,400,0,0" />
  <script>
    const logout = () => {window.location.href="http://localhost:3000/logout"};
    const move = () => {window.location.href="http://localhost:3000/sugang"};
  </script>
</head>
<div class="header">
  <div class="header-logo">
    <div class="logo"><img
        src="./img/inhaLogo.png"
        alt="INHA UNIVERSITY"
      /></div>
    <span class="logo-text">Enrolment</span>
  </div>

  <div class="header-info">
    <span class="header-info_title">Information on transitional measures related to 2021 curriculum reform (<span
        class="header-info_title_strong"
      >required for student numbers prior to 2020</span>) -> View details</span>
  </div>
</div>

<div class="content">
  <div class="side">
    <div class="side-frame">
      <div class="side-frame_id">
        <div class="userinfo">{{user}}</div>
        <button class="logout" onclick="logout()">Logout</button>
      </div>
    </div>
  <div class="side-menu">
      <div class="side-menu_list">
        <span>Course information</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Lecture diagnosis</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list" onclick="move()">
        <span>Enrolment</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Grade confirmation</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Seasonal semester</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Subject inquiry</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Reference Link</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>privacy policy</span>
        <span class="material-icons">add</span>
      </div>

    </div>
  </div>
  <div class="main">
    <div class="main-top">
      <span class="main-top_title">Main course registration schedule for the 2nd semester of 2023</span>
      <div class="main-top_table">
        <table>
          <tr style="background-color: #0770bb;">
            <td class="main-top_table_head_first">Date</td>
            <td class="main-top_table_head_second">detail</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 7. 22 (Fri) 10:00 ~ 7. 26 (Tue) 16:00</td>
            <td class="main-top_table_body_second">Priority course registration date</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 10 (Wed) 10:00 ~ 16:00</td>
            <td class="main-top_table_body_second">Major, required course registration (possible to take courses in engineering and software convergence colleges, no retakes, limited number of students per grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 11 (Thursday) 10:00 ~ 16:00</td>
            <td class="main-top_table_body_second">Major, required course registration (can take courses at colleges and universities except engineering and software convergence colleges; restrictions are the same as above)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 17 (Wed) 10:00 ~ 13:00</td>
            <td class="main-top_table_body_second">General course registration (re-enrollment is not allowed, number of students limited by grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 8. 17 (Wed) 14:00 ~ 16:00</td>
            <td class="main-top_table_body_second">General course registration (re-enrollment possible, no limit on number of students by grade)</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">August 29, 2023 (Monday)</td>
            <td class="main-top_table_body_second">2023-2nd semester begins</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 9. 2 (Fri) 08:30 ~ 19:00</td>
            <td class="main-top_table_body_second">Course registration change date</td>
          </tr>
          <tr>
            <td class="main-top_table_body_first">2023. 9. 26 (Mon) 09:00 ~ 9. 28 (Wed) 24:00</td>
            <td class="main-top_table_body_second">Course withdrawal period</td>
          </tr>
        </table>
      </div>
    </div>
    <div class="main-bottom">
      <span class="main-bottom_title">Notice regarding course registration</span>
      <div class="main-bottom_table">
        <table>
          <tr style="background-color: #0770bb;">
            <td class="main-bottom_table_head_first">number</td>
            <td class="main-bottom_table_head_second">attach</td>
            <td class="main-bottom_table_head_third">title</td>
            <td class="main-bottom_table_head_fourth">Registration date</td>
            <td class="main-bottom_table_head_fifth">Writer</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3150</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Notice on allowing pop-ups when registering for courses</td>
            <td class="main-bottom_table_body_fourth">2020-01-20</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3061</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Information on restrictions on taking classes between professors’ children</td>
            <td class="main-bottom_table_body_fourth">2019-07-22</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">2926</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">Notice on changes to additional 3 credits conditions for course registration (applies from 2018-1st semester course registration)</td>
            <td class="main-bottom_table_body_fourth">2018-07-25</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3318</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[For undergraduate students] Notice on implementation of 2021 statutory compulsory education (online education)</td>
            <td class="main-bottom_table_body_fourth">2021-01-13</td>
            <td class="main-bottom_table_body_fifth">Gender Equality Counseling Center</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3023</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">
Information on course credits (21 credits) for new students in the College of Engineering and College of Natural Sciences (applicable from freshmen in 2019</td>
            <td class="main-bottom_table_body_fourth">2019-02-19</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3586</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">
[Frontier College] Information on transitional measures following the reorganization of the liberal arts curriculum for the 2021 school year (basic liberal arts/core liberal arts)</td>
            <td class="main-bottom_table_body_fourth">2022-07-26</td>
            <td class="main-bottom_table_body_fifth">Frontier Undergraduate University Administration</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3616</td>
            <td class="main-bottom_table_body_second"><span class="material-symbols-outlined">
description
</span></td>
            <td class="main-bottom_table_body_third">[Undergraduate] Information on 2nd canceled courses for the 2nd semester of the 2022 academic year</td>
            <td class="main-bottom_table_body_fourth">2022-09-05</td>
            <td class="main-bottom_table_body_fifth">Administrator</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3615</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Future Automotive Business Group] Notice on changes to Object-Oriented Programming (FVE3010-001) classroom</td>
            <td class="main-bottom_table_body_fourth">2022-08-31</td>
            <td class="main-bottom_table_body_fifth">Future Automobile Business Division</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3614</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[Department of Business Administration] BUS3204-005 Human Resource Management Timetable Change Notice</td>
            <td class="main-bottom_table_body_fourth">2022-08-26</td>
            <td class="main-bottom_table_body_fifth">Business Administration</td>
          </tr>
          <tr>
            <td class="main-bottom_table_body_first">3613</td>
            <td class="main-bottom_table_body_second"></td>
            <td class="main-bottom_table_body_third">[English Education Department] Notice on classroom changes for the 2nd semester of 2022</td>
            <td class="main-bottom_table_body_fourth">2022-08-26</td>
            <td class="main-bottom_table_body_fifth">English Education Department</td>
          </tr>
    </div>
  </div>
</div>

⑬ select.hbs에 아래의 내용을 입력한다.

<head>
  <link rel="stylesheet" type="text/css" href="select.css" />
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet" />
  <script>
    const logout = () => { window.location.href = "http://localhost:3000/logout" };
    const move = () => { window.location.href = "http://localhost:3000/sugang" };
  </script>
</head>
<div class="header">
  <div class="header-logo">
    <div class="logo"><img src="./img/inhaLogo.png" alt="INHA UNIVERSITY" /></div>
    <span class="logo-text">Enrolment</span>
  </div>

  <div class="header-info">
    <span class="header-info_title">Information on transitional measures related to 2021 curriculum reform (<span
        class="header-info_title_strong">required for student numbers prior to 2020</span>) -> View details</span>
  </div>
</div>

<div class="content">
  <div class="side">
    <div class="side-frame">
      <div class="side-frame_id">
        <div class="userinfo">{{user}}</div>
        <button class="logout" onclick="logout()">Logout</button>
      </div>
    </div>
    <div class="side-menu">
      <div class="side-menu_list">
        <span>Course information</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Lecture diagnosis</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list" onclick="move()">
        <span>Enrolment</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Grade confirmation</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Seasonal semester</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Subject inquiry</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>Reference Link</span>
        <span class="material-icons">add</span>
      </div>
      <div class="side-menu_list">
        <span>privacy policy</span>
        <span class="material-icons">add</span>
      </div>

    </div>
  </div>
  <div class="main">
    <div class="main-top">
      <span class="main-top_title">Course registration for the 2nd semester of 2023</span>
      <div class="main-top_table">
        <h1 class="info">{{title}}</h1>
        <table>
          <tr>
            <td><b>Id</b></td>
            <td><b>Course</b></td>
            <td><b>Professor</b></td>
            <td><b>Opening Departments</b></td>
            <td><b>Maximum participants</b></td>
          </tr>
          {{#each classes}}
          <form method="post">
            <tr>
              <td>{{ID}}</td>
              <td>{{Course}}</td>
              <td>{{Professor}}</td>
              <td>{{Opening_departments}}</td>
              <td>{{Number_of_participant}}</td>
            </tr>
          </form>
          {{/each}}
        </table>

        <h1 class="info">{{title2}}</h1>
        <table>
          <tr>
            <td><b>Id</b></td>
            <td><b>Course</b></td>
            <td><b>Professor</b></td>
            <td><b>Opening Departments</b></td>
            <td><b>Maximum participants</b></td>
            <td><b>Remaining participants</b></td>
          </tr>
          {{#each allClass}}
          <form method="post">
            <tr>
              <td>{{ID}}</td>
              <td>{{Course}}</td>
              <td>{{Professor}}</td>
              <td>{{Opening_departments}}</td>
              <td>{{Number_of_participant}}</td>
              <td id="empty">{{Remaining_participants}}</td>
              <td style="border: none; margin-left: 10px;">
                <button style="margin-left: 10px;" name="applyBtn" type="submit" value="{{ID}}" formaction="/sugang">
                  Apply
                </button>
              </td>
            </tr>
          </form>
          {{/each}}
        </table>
      </div>
    </div>
  </div>
</div>

⑭ index.js에 아래의 내용을 입력한다.

import express from "express";
import logger from "morgan";
import path from "path";
import liveReload from 'livereload';
import connectLiveReload from 'connect-livereload';

import loginRouter from "./routes/login";
import logoutRouter from './routes/logout';
import selectRouter from "./routes/select";

const PORT = 3000;

const liveReloadServer = liveReload.createServer(); 
liveReloadServer.server.once("connection", () => {
  setTimeout(() => {
    liveReloadServer.refresh('/');
  }, 100)
});

const app = express();

app.use(connectLiveReload());

app.use(express.urlencoded({ extended: false }));
app.use(express.json());

app.set("views", path.join(__dirname, "views"));
app.set("view engine", "hbs");
app.use(express.static(path.join(__dirname, 'public')));
// css 경로 설정

app.use(logger("dev"));

app.use("/", loginRouter);
app.use("/logout", logoutRouter);
app.use("/sugang", selectRouter);

app.listen(PORT, () => {
  console.log(`Example app listening at http://localhost:${PORT}`);
});

⑮ babel.config.json에 아래의 내용을 입력한다.

{ 
    "presets": [
        "@babel/preset-env"
    ]
}

Terminal을 열고, npm run start를 입력하자. localhost:8080에 접속하면 아래와 같이 인하대학교 수강신청 홈페이지를 카피한 웹 페이지가 렌더링될 것이다.

2) 로그인 기능 확인하기

① cmd에서 Students 테이블에 대한 정보를 확인해보자.

StudentId 필드와 password 필드가 로그인에 사용되는 ID/PW이다.

② 1번 학생으로 로그인한 후 Enrolment 버튼을 클릭하면, 수강신청 페이지로 이동한다.

로그인하기 전에는 Enrolment를 클릭해도, 수강신청 페이지로 이동되지 않는다.

③ 아래는 수강신청 페이지의 모습이다.

④ 로그아웃을 버튼을 누르면, 처음 화면으로 돌아온다.

2. 수강신청 기능 구현하기

1) 데이터 표시하기

① 수강신청 페이지에 데이터를 표시하기 위해 sql.js의 selectSql을 아래와 같이 수정한다.

getMyClasses: 로그인한 학생이 수강신청한 강의를 가져오는 메서드
getClasses: 개설된 강의들을 모두 가져와서 수용 가능 인원과 여석을 알려주는 메서드

export const selectSql = {
  getUsers: async () => {
    const [rows] = await promisePool.query(`select * from student`);
    return rows;
  },
  getMyClasses: async (userId) => {
    const [rows] = await promisePool.query(`select c.ID, c.Name as Course, c.Professor, d.Dname as Opening_departments,
     c.Number_of_participant as Number_of_participant from class c, department d, class_student cs, student s
      where c.Did=d.ID and cs.Student_id=s.ID and s.StudentId=${userId} and cs.Class_id=c.ID`);
    return rows;
  },
  getClasses: async () => {
    const [rows] = await promisePool.query(`SELECT c.ID as ID, c.Name as Course, c.Professor, 
        d.Dname as Opening_departments, c.Number_of_participant,
        (c.Number_of_participant - COALESCE(cs.student_count, 0)) AS Remaining_participants
        FROM class c
        JOIN department d ON c.Did = d.Id
        LEFT JOIN (
            SELECT Class_id, COUNT(cs.Student_id) AS student_count
            FROM class_student cs
            GROUP BY Class_id
        ) cs ON c.ID = cs.Class_id`);
    return rows;
  }
}

② select.js를 아래와 같이 수정한다.

select.hbs에 title, classes, title2, allClass를 전달한다.

router.get('/', async function (req, res) {
    // TODO
    
    if (req.cookies.user) {
        const allClass = await selectSql.getClasses();
        const classes = await selectSql.getMyClasses(req.cookies.user);

        res.render('select', { 
            user: req.cookies.user, 
            title: "Course Completion List",
            classes,
            title2: "Course List (Registration)",
            allClass
        });
    } else {
        res.render('/')
    }

});

다시 1번 학생으로 로그인해 결과를 확인해보자.

실제로 1번 학생이 Database 강의를 수강 신청했는지 cmd에서 확인해보자.

수강 신청 결과가 잘 나타나고 있음을 확인해볼 수 있다.

2) 수강신청 처리하기

수강신청 기능을 구현할 때에는 아래의 조건을 모두 만족해야 한다.

Apply 버튼을 클릭했을 때, DB와 웹 페이지에 수강 신청 내용이 반영되어야 한다.
여석이 없는 경우, 수강 신청이 진행되지 않아야 한다.
이미 수강 신청한 강의에 대해서는 수강 신청이 진행되지 않아야 한다.

① sql.js를 아래와 같이 수정한다.

여석이 부족할 경우, full을 반환한다.
이미 수강 중인 강의인 경우, enrolled를 반환한다.
여석도 존재하고, 수강 중인 강의도 아닌 경우, DB에 수강 신청 내용이 반영된다.

export const createSql = {
    addClass: async (data) => {
      const uid = await promisePool.query(`SELECT Id FROM Student WHERE StudentId = ${data.sId}`);
      console.log(uid);
      const [studentClasses] = await promisePool.query(`
        SELECT Class_id
        FROM class_student
        WHERE Student_id = ${uid[0][0].Id}
      `);
  
      const classAvailability = await promisePool.query(`
        SELECT (c.Number_of_participant - IFNULL(cs.StudentCount, 0)) AS RemainingSeats
        FROM class c
        LEFT JOIN (
          SELECT Class_id, COUNT(Student_id) AS StudentCount
          FROM class_student
          GROUP BY Class_id
        ) cs ON c.ID = cs.Class_id
        WHERE c.ID = ${data.cId}
      `);
  
      const isAlreadyEnrolled = studentClasses.some((classes) => classes.Class_id == data.cId);
      console.log("isAlreadyEnrolled: ", isAlreadyEnrolled);
  
      if (!isAlreadyEnrolled && classAvailability[0][0].RemainingSeats > 0) {
        const results = await promisePool.query(`
          INSERT INTO class_student (Student_id, Class_id) 
          VALUES (${uid[0][0].Id}, ${data.cId})
        `);
        return results[0];
      } else if (isAlreadyEnrolled) {
        return "enrolled";
      } else {
        return "full";
      }
    }
  };

② select.js를 아래와 같이 수정한다.

full을 전달 받은 경우, 여석이 부족함을 알리는 알림창을 띄운다.
enrolled를 전달 받은 경우, 이미 수강 중임을 알리는 알림창을 띄운다.
정상적으로 수강 신청이 완료된 경우, 현재 페이지를 다시 렌더링한다.

import express from "express";
import { selectSql } from '../database/sql';
import { createSql } from '../database/sql';
...

router.post('/', async(req, res) => {
    // TODO
    const data = {
        cId: req.body.applyBtn,
        sId: req.cookies.user,
    };

    const result = await createSql.addClass(data);
    if (result === "full") {
        return res.send(`<script>
                            alert('여석 부족으로 인해 수강신청에 실패했습니다.');
                            location.href='/sugang';
                        </script>`);
    } else if (result === "enrolled") {
        return res.send(`<script>
                            alert('이미 수강 중인 강의는 신청할 수 없습니다.');
                            location.href='/sugang';
                        </script>`);
    }
    res.redirect('/sugang');
});

module.exports = router;

3) 수강 신청 기능 확인하기

이제 실행결과를 확인해보자. 먼저 1번 학생으로 로그인하여 수강신청 내역을 확인한다. 현재는 1번 강의인 Database만 수강 중이다.

2번 강의인 Signals and Systems에 Apply해보자.

DB와 웹 페이지 모두에 수강 신청한 내역이 잘 적용되었다. 또한, 1번 학생이 Signals and Systems에 성공적으로 Apply함으로써, Remaining participants가 하나 줄어든 것도 확인할 수 있다. 이번에는 2번 학생에 대해 테스트해보자. 2번 학생은 현재 아무 강의도 신청하지 않은 상태이다.

이번에도 Signals and Systems에 Apply 해보자.

DB와 웹 페이지 모두에 결과가 잘 나타나고 있는 것을 확인할 수 있다. 또한, Remaining participants도 잘 계산되었다.

여석이 없는 강의에 대해서는 수강 신청이 진행되어서는 안되며, 사용자에게 여석이 부족함을 알려주어야 한다. 2번 학생으로 여석이 없는 Data Structures에 Apply해보자.

여석이 부족하여 수강신청에 실패하였음을 알려주는 알림 메시지가 잘 표시되었다. 확인 버튼을 클릭하여 /sugang 페이지로 돌아오면, 수강 신청 내역이 변동사항 없이 그대로인 것을 확인할 수 있다. 즉, 수강 신청이 진행되지 않은 것이다.

이번에는 이미 수강 중인 강의에 Apply하는 경우를 테스트해보기로 하자.

이번에도 웹 페이지와 DB에 대한 변동 사항은 없었으며(수강 신청은 진행되지 않았으며), 이미 수강 중인 강의를 다시 신청할 수 없다는 메시지가 잘 표시되었다.

변현섭

Java Spring, Android Kotlin, Node.js, ML/DL 개발을 공부하는 인하대학교 정보통신공학과 학생입니다.

11주차 실습. 인하대 수강신청 홈페이지 구현하기

데이터베이스설계

1) 인하대 수강신청 홈페이지 카피하기

2) VSCode

2) 로그인 기능 확인하기

2. 수강신청 기능 구현하기

1) 데이터 표시하기

2) 수강신청 처리하기

3) 수강 신청 기능 확인하기

11주차 이론. Indexing Structure

12주차 이론. Transaction Processing

0개의 댓글

11주차 실습. 인하대 수강신청 홈페이지 구현하기

데이터베이스설계

1. Cookie를 활용한 Web Login 구현하기

1) 인하대 수강신청 홈페이지 카피하기

2) VSCode

2) 로그인 기능 확인하기

2. 수강신청 기능 구현하기

1) 데이터 표시하기

2) 수강신청 처리하기

3) 수강 신청 기능 확인하기

11주차 이론. Indexing Structure

12주차 이론. Transaction Processing

0개의 댓글