# SD課題 チームC
###### tags: `SD課題`
:::spoiler employee テーブル
```sql=
CREATE TABLE `employee` (
`employee_id` int(5) NOT NULL AUTO_INCREMENT,
`employee_name` varchar(15) NOT NULL,
`employee_name_sub` varchar(30) NOT NULL,
`employee_birthday` date NOT NULL,
`employee_gender` char(1) NOT NULL,
`employee_phone1` char(11) NOT NULL,
`employee_phone2` char(11),
`employee_phone3` char(11),
`employee_address` varchar(100) NOT NULL,
`employee_zipcode` char(7) NOT NULL,
`employee_type` char(1) NOT NULL,
`company_join` date NOT NULL,
`company_leaving` date DEFAULt NULL,
PRIMARY KEY(`employee_id`)
) DEFAULT CHARSET=utf8;
```
:::
:::spoiler work_shift テーブル
```sql=
CREATE TABLE `working_shift` (
`working_shift_id` int(5) NOT NULL AUTO_INCREMENT,
`employee_id` int(5) NOT NULL,
`working_shift_join` datetime NOT NULL,
`working_shift_leaving` datetime,
PRIMARY KEY(`working_shift_id`),
FOREIGN KEY(`employee_id`)
REFERENCES employee (`employee_id`)
) DEFAULT CHARSET=utf8;
```
:::
:::spoiler sd_index.html
```htmlembedded=
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link
href="https://unpkg.com/tailwindcss@^2/dist/tailwind.min.css"
rel="stylesheet"
/>
<title>Document</title>
</head>
<body>
<main>
<div class="flex justify-center items-center gap-4 h-screen w-full">
<h1>従業員勤怠管理システム</h1>
<a href="./sd_shift_join.jsp"
><button class="bg-blue-400 py-2 px-3">出勤登録</button></a
>
<a href="./sd_shift_leaving.jsp"
><button class="bg-blue-400 py-2 px-3">退勤登録</button></a
>
<a href="./sd_employee_insert.html"
><button class="bg-blue-400 py-2 px-3">従業員登録</button></a
>
</div>
</main>
</body>
</html>
```
:::
:::spoiler sd_employee_insert.html
```htmlmixed=
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link
href="https://unpkg.com/tailwindcss@^2/dist/tailwind.min.css"
rel="stylesheet"
/>
<title>Document</title>
</head>
<body>
<main
class="
flex
justify-center
items-center
flex-col
gap-4
h-screen
w-full
bg-gray-200
"
>
<form action="./servlet/sd_employee_insert" method="POST">
<div class="flex justify-center items-start flex-col gap-4">
<h1>従業員登録</h1>
<div>
<label for="employee_name">従業員氏名</label>
<input
type="text"
id="employee_name"
name="EMPLOYEE_NAME"
value=""
placeholder="従業員氏名"
/>
</div>
<div>
<label for="employee_name_sub">従業員氏名（カナ）</label>
<input
type="text"
id="employee_name_sub"
name="EMPLOYEE_NAME_SUB"
value=""
placeholder="従業員氏名（カナ）"
/>
</div>
<div>
<label for="birthday_year">生年月日</label>
<input
type="number"
id="birthday_year"
name="BIRTHDAY_YEAR"
value=""
placeholder="（年）"
/>
</div>
<div>
<label for="birthday_month">生年月日</label>
<input
type="number"
id="birthday_month"
name="BIRTHDAY_MONTH"
value=""
placeholder="（月）"
/>
</div>
<div>
<label for="birthday_day">生年月日</label>
<input
type="number"
id="birthday_day"
name="BIRTHDAY_DAY"
value=""
placeholder="（日）"
/>
</div>
<div>
<label for="employee_gender">性別</label>
<select name="EMPLOYEE_GENDER" id="employee_gender">
<option disabled>性別を選択してください</option>
<option value="1">男</option>
<option value="2">女</option>
<option value="3">その他</option>
</select>
</div>
<div>
<label for="company_join_yaer">入社年月日</label>
<input
type="number"
id="company_join_yaer"
name="COMPANY_JOIN_YEAR"
value=""
placeholder="（年）"
/>
</div>
<div>
<label for="company_join_month">入社年月日</label>
<input
type="number"
id="company_join_month"
name="COMPANY_JOIN_MONTH"
value=""
placeholder="（月）"
/>
</div>
<div>
<label for="company_join_day">入社年月日</label>
<input
type="number"
id="company_join_day"
name="COMPANY_JOIN_DAY"
value=""
placeholder="（日）"
/>
</div>
<div>
<label for="employee_phone1">電話番号1</label>
<input
type="text"
id="employee_phone1"
name="EMPLOYEE_PHONE_1"
value=""
/>
</div>
<div>
<label for="employee_phone2">電話番号2</label>
<input
type="text"
id="employee_phone2"
name="EMPLOYEE_PHONE_2"
value=""
/>
</div>
<div>
<label for="employee_phone3">電話番号3</label>
<input
type="text"
id="employee_phone3"
name="EMPLOYEE_PHONE_3"
value=""
/>
</div>
<div>
<label for="employee_zipcode">郵便番号</label>
<input
type="number"
id="employee_zipcode"
name="EMPLOYEE_ZIPCODE"
value=""
placeholder="郵便番号"
/>
</div>
<div>
<label for="employee_address">住所</label>
<input
type="text"
id="employee_address"
name="EMPLOYEE_ADDRESS"
value=""
placeholder="住所"
/>
</div>
<div>
<label for="employee_type">従業員タイプ</label>
<select name="EMPLOYEE_TYPE" id="employee_type">
<option disabled>従業員タイプを選択してください</option>
<option value="1">店長</option>
<option value="2">正社員</option>
<option value="3">アルバイト</option>
</select>
</div>
<button class="bg-blue-500 text-white py-2 px-5 rounded-full">
送信
</button>
</div>
</form>
</main>
</body>
</html>
```
:::
:::spoiler sd_employee_insert.java
```java=
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class sd_employee_insert extends HttpServlet {
public void doPost (
HttpServletRequest req,
HttpServletResponse res )
throws ServletException , IOException {
final String URL = "jdbc:mysql://localhost/sd_kadai?useUnicode=true&characterEncoding=UTF-8";
final String USER = "miyasan";
final String PASSWORD = "0301";
final String DRIVER = "com.mysql.jdbc.Driver";
Connection con = null;
Statement stmt = null;
PrintWriter out;
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
out = res.getWriter();
String employee_name = req.getParameter("EMPLOYEE_NAME");
String employee_name_sub = req.getParameter("EMPLOYEE_NAME_SUB");
String birthday_year = req.getParameter("BIRTHDAY_YEAR");
String birthday_month = req.getParameter("BIRTHDAY_MONTH");
String birthday_day = req.getParameter("BIRTHDAY_DAY");
String employee_gender = req.getParameter("EMPLOYEE_GENDER");
String company_join_year = req.getParameter("COMPANY_JOIN_YEAR");
String company_join_month = req.getParameter("COMPANY_JOIN_MONTH");
String company_join_day = req.getParameter("COMPANY_JOIN_DAY");
String employee_phone1 = req.getParameter("EMPLOYEE_PHONE_1");
String employee_phone2 = req.getParameter("EMPLOYEE_PHONE_2");
String employee_phone3 = req.getParameter("EMPLOYEE_PHONE_3");
String employee_zipcode = req.getParameter("EMPLOYEE_ZIPCODE");
String employee_address = req.getParameter("EMPLOYEE_ADDRESS");
String employee_type = req.getParameter("EMPLOYEE_TYPE");
try {
Class.forName(DRIVER);
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
StringBuffer query = new StringBuffer();
query.append("select * from employee where employee_phone1 ='");
query.append(employee_phone1);
query.append("'");
ResultSet rs = stmt.executeQuery(query.toString());
StringBuffer sb = new StringBuffer();
sb.append("<html>");
sb.append("<head>");
sb.append("<title>従業員登録完了</title>");
sb.append("<link href='https://cdn.jsdelivr.net/npm/tailwindcss@2.1/dist/tailwind.min.css' rel='stylesheet' type='text/css' />");
sb.append("<link href='https://cdn.jsdelivr.net/npm/daisyui@0.26.3/dist/full.css' rel='stylesheet' type='text/css' />");
sb.append("</head>");
sb.append("<body>");
sb.append("<div class='h-screen w-full'>");
sb.append("<div class='text-2xl text-center bg-primary py-5 font-bold text-white'>従業員登録</div>");
sb.append("<div class='px-5'>");
if (rs.next()) {
sb.append("<div class='text-secondary text-lg text-center py-3 bg-white'>従業員登録失敗</div>");
sb.append("<div class='text-center'>" + employee_name + "はすでに登録済みです</div>");
} else {
query = new StringBuffer();
query.append("INSERT INTO employee");
query.append("(employee_name, employee_name_sub, employee_birthday, employee_gender, company_join, ");
query.append("employee_phone1, employee_phone2, employee_phone3, employee_zipcode, employee_address,employee_type)");
query.append("values('");
query.append(employee_name);
query.append("','");
query.append(employee_name_sub);
query.append("','");
query.append(birthday_year + "-" + birthday_month + "-" + birthday_day);
query.append("','");
query.append(employee_gender);
query.append("','");
query.append(company_join_year + "-" + company_join_month + "-" + company_join_day);
query.append("','");
query.append(employee_phone1);
query.append("','");
query.append(employee_phone2);
query.append("','");
query.append(employee_phone3);
query.append("','");
query.append(employee_zipcode);
query.append("','");
query.append(employee_address);
query.append("','");
query.append(employee_type);
query.append("')");
stmt.executeUpdate(query.toString());
sb.append("<div class='text-secondary text-lg text-center py-3 bg-white'>従業員登録完了</div>");
}
sb.append("<div class='flex justify-center pt-5'>");
sb.append("<a href='/JV27/sd_insert.html' class='btn btn-link'>登録に戻る</a>");
sb.append("<a href='/JV27/sd_index.html' class='btn btn-link'>ホームに戻る</a>");
sb.append("</div>");
sb.append("</div>");
sb.append("</div>");
sb.append("</body>");
sb.append("</html>");
out.println(sb.toString());
stmt.close();
con.close();
}
catch (SQLException ex) {
out.println(" ---- SQL Exception ----");
out.println(" ---- Error Message ----");
while (ex != null) {
out.println(ex.getMessage());
ex = ex.getNextException();
}
} catch (Exception ex) {
ex.printStackTrace(out);
}
}
}
```
:::
:::spoiler sd_shift_join.jsp
```htmlmixed=
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.HashMap" %>
<%@ page import = "java.util.ArrayList" %>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
Connection con = null;
Statement stmt = null;
StringBuffer SQL = null;
ResultSet rs = null;
String USER = "miyasan";
String PASSWORD = "0301";
String URL = "jdbc:mysql://localhost/sd_kadai";
String DRIVER = "com.mysql.jdbc.Driver";
StringBuffer ERMSG = null;
HashMap<String,String> map = null;
ArrayList<HashMap> list = null;
list = new ArrayList<HashMap>();
try{
Class.forName(DRIVER).newInstance();
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
SQL = new StringBuffer();
SQL.append("SELECT employee_id, employee_name FROM employee");
rs = stmt.executeQuery(SQL.toString());
while (rs.next()){
map = new HashMap<String,String>();
map.put("employee_id",rs.getString("employee_id"));
map.put("employee_name",rs.getString("employee_name"));
list.add(map);
}
}
catch(ClassNotFoundException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
catch(SQLException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
catch(Exception e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
finally{
try{
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(con != null){
con.close();
}
}
catch(SQLException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>出勤登録</title>
</head>
<body>
<form action="./servlet/sd_shift_join" method="POST">
<select name="EMPLOYEE_ID">
<option selected disabled>選択してください</option>
<% for (int i = 0; i < list.size(); i++) { %>
<option value="<%= list.get(i).get("employee_id") %>">
<%= list.get(i).get("employee_name") %>
</option>
<% } %>
</select>
<button>送信</button>
</form>
</body>
</html>
```
:::
:::spoiler sd_hift_join.java
```java=
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.Date;
import java.text.*;
public class sd_shift_join extends HttpServlet {
public void doPost (
HttpServletRequest req,
HttpServletResponse res )
throws ServletException , IOException {
final String URL = "jdbc:mysql://localhost/sd_kadai?useUnicode=true&characterEncoding=UTF-8";
final String USER = "miyasan";
final String PASSWORD = "0301";
final String DRIVER = "com.mysql.jdbc.Driver";
Connection con = null;
Statement stmt = null;
PrintWriter out;
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
out = res.getWriter();
StringBuffer query = new StringBuffer();
String employee_id = req.getParameter("EMPLOYEE_ID");
SimpleDateFormat date_time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String join_date_time = date_time.format(new Date());
try {;
Class.forName(DRIVER);
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
StringBuffer sb = new StringBuffer();
sb.append("<html>");
sb.append("<head>");
sb.append("<title>出勤登録</title>");
sb.append("<link href='https://cdn.jsdelivr.net/npm/tailwindcss@2.1/dist/tailwind.min.css' rel='stylesheet' type='text/css' />");
sb.append("<link href='https://cdn.jsdelivr.net/npm/daisyui@0.26.3/dist/full.css' rel='stylesheet' type='text/css' />");
sb.append("</head>");
sb.append("<body>");
sb.append("<div class='h-screen w-full'>");
sb.append("<div class='text-2xl text-center bg-primary py-5 font-bold text-white'>出勤登録</div>");
sb.append("<div class='px-5'>");
query = new StringBuffer();
query.append("INSERT INTO working_shift ");
query.append("(employee_id, working_shift_join) ");
query.append("VALUES('");
query.append(employee_id);
query.append("','");
query.append(join_date_time);
query.append("')");
stmt.executeUpdate(query.toString());
sb.append("<div class='text-secondary text-lg text-center py-3 bg-white'>出勤登録完了</div>");
sb.append("<div class='flex justify-center pt-5'>");
sb.append("<a href='/JV27/sd_index.html' class='btn btn-link'>ホームに戻る</a>");
sb.append("</div>");
sb.append("</div>");
sb.append("</div>");
sb.append("</body>");
sb.append("</html>");
out.println(sb.toString());
stmt.close();
con.close();
}
catch (SQLException ex) {
out.println(" ---- SQL Exception ----");
out.println(" ---- Error Message ----");
while (ex != null) {
out.println(ex.getMessage());
ex = ex.getNextException();
}
} catch (Exception ex) {
ex.printStackTrace(out);
}
}
}
```
:::
:::spoiler sd_shift_leaving.jsp
```htmlmixed=
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.HashMap" %>
<%@ page import = "java.util.ArrayList" %>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
Connection con = null;
Statement stmt = null;
StringBuffer SQL = null;
ResultSet rs = null;
String USER = "miyasan";
String PASSWORD = "0301";
String URL = "jdbc:mysql://localhost/sd_kadai";
String DRIVER = "com.mysql.jdbc.Driver";
StringBuffer ERMSG = null;
HashMap<String,String> map = null;
ArrayList<HashMap> list = null;
list = new ArrayList<HashMap>();
try{
Class.forName(DRIVER).newInstance();
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
SQL = new StringBuffer();
SQL.append("SELECT working_shift_id, employee_name ");
SQL.append("FROM working_shift, employee ");
SQL.append("WHERE working_shift.employee_id = employee.employee_id ");
SQL.append("AND working_shift_leaving IS NULL ");
System.out.println(SQL.toString());
rs = stmt.executeQuery(SQL.toString());
while (rs.next()){
map = new HashMap<String,String>();
map.put("working_shift_id",rs.getString("working_shift_id"));
map.put("employee_name",rs.getString("employee_name"));
list.add(map);
}
}
catch(ClassNotFoundException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
catch(SQLException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
catch(Exception e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
finally{
try{
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(con != null){
con.close();
}
}
catch(SQLException e){
ERMSG = new StringBuffer();
ERMSG.append(e.getMessage());
}
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>退勤登録</title>
</head>
<body>
<form action="./servlet/sd_shift_leaving" method="POST">
<select name="WORKING_SHIFT_ID">
<option selected disabled>選択してください</option>
<% for (int i = 0; i < list.size(); i++) { %>
<option value="<%= list.get(i).get("working_shift_id") %>">
<%= list.get(i).get("employee_name") %>
</option>
<% } %>
</select>
<button>送信</button>
</form>
</body>
</html>
```
:::
:::spoiler sd_shift_leaving.java
```java=
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.Date;
import java.text.*;
public class sd_shift_leaving extends HttpServlet {
public void doPost (
HttpServletRequest req,
HttpServletResponse res )
throws ServletException , IOException {
final String URL = "jdbc:mysql://localhost/sd_kadai?useUnicode=true&characterEncoding=UTF-8";
final String USER = "miyasan";
final String PASSWORD = "0301";
final String DRIVER = "com.mysql.jdbc.Driver";
Connection con = null;
Statement stmt = null;
PrintWriter out;
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=UTF-8");
out = res.getWriter();
StringBuffer query = new StringBuffer();
String working_shift_id = req.getParameter("WORKING_SHIFT_ID");
SimpleDateFormat date_time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String leaving_date_time = date_time.format(new Date());
try {;
Class.forName(DRIVER);
con = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = con.createStatement();
StringBuffer sb = new StringBuffer();
sb.append("<html>");
sb.append("<head>");
sb.append("<title>退勤登録</title>");
sb.append("<link href='https://cdn.jsdelivr.net/npm/tailwindcss@2.1/dist/tailwind.min.css' rel='stylesheet' type='text/css' />");
sb.append("<link href='https://cdn.jsdelivr.net/npm/daisyui@0.26.3/dist/full.css' rel='stylesheet' type='text/css' />");
sb.append("</head>");
sb.append("<body>");
sb.append("<div class='h-screen w-full'>");
sb.append("<div class='text-2xl text-center bg-primary py-5 font-bold text-white'>退勤登録</div>");
sb.append("<div class='px-5'>");
query = new StringBuffer();
query.append("UPDATE working_shift SET working_shift_leaving = '");
query.append(leaving_date_time);
query.append("' WHERE working_shift_id = '");
query.append(working_shift_id);
query.append("'");
stmt.executeUpdate(query.toString());
sb.append("<div class='text-secondary text-lg text-center py-3 bg-white'>退勤登録完了</div>");
sb.append("<div class='flex justify-center pt-5'>");
sb.append("<a href='/JV27/sd_index.html' class='btn btn-link'>ホームに戻る</a>");
sb.append("</div>");
sb.append("</div>");
sb.append("</div>");
sb.append("</body>");
sb.append("</html>");
out.println(sb.toString());
stmt.close();
con.close();
}
catch (SQLException ex) {
out.println(" ---- SQL Exception ----");
out.println(" ---- Error Message ----");
while (ex != null) {
out.println(ex.getMessage());
ex = ex.getNextException();
}
} catch (Exception ex) {
ex.printStackTrace(out);
}
}
}
```
:::
Sign in with Wallet
Connect another wallet