# SD課題
###### tags: `SD課題`
:::spoiler cd_out.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 f_inout_id, f_employee_name");
SQL.append(" FROM t_inout, t_employee");
SQL.append(" WHERE t_inout.f_employee_id = t_employee.f_employee_id");
SQL.append(" AND f_inout_out IS NULL");
System.out.println(SQL.toString());
rs = stmt.executeQuery(SQL.toString());
while (rs.next()){
map = new HashMap<String,String>();
map.put("f_inout_id",rs.getString("f_inout_id"));
map.put("f_employee_name",rs.getString("f_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 method="post" action="./servlet/sd_outtime">
<select name="INOUT_ID">
<option disabled selected>選択してください</option>
<% for (int i = 0; i < list.size(); i++) { %>
<option value="<%= list.get(i).get("f_inout_id") %>">
<%= list.get(i).get("f_employee_name") %>
</option>
<% } %>
</select>
<button>送信</button>
</form>
</body>
</html>
```
:::
:::spoiler sd_in.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 * from t_employee");
System.out.println(SQL.toString());
rs = stmt.executeQuery(SQL.toString());
while (rs.next()){
map = new HashMap<String,String>();
map.put("f_employee_id",rs.getString("f_employee_id"));
map.put("f_employee_name",rs.getString("f_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 method="post" action="./servlet/sd_intime">
<select name="EMPLOYEE_ID">
<option disabled selected>選択してください</option>
<% for (int i = 0; i < list.size(); i++) { %>
<option value="<%= list.get(i).get("f_employee_id") %>">
<%= list.get(i).get("f_employee_name") %>
</option>
<% } %>
</select>
<button>送信</button>
</form>
</body>
</html>
```
:::
:::spoiler sd_insert.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>
<form action="./servlet/sd_insert" method="post">
<div
class="
flex
justify-center
items-start
flex-col
gap-4
h-screen
w-full
bg-gray-200
px-80
"
>
<h1>従業員登録</h1>
<div>
<label for="employee_name">従業員氏名</label>
<input
type="text"
id="employee_name"
name="EMPLOYEE_NAME"
placeholder="従業員氏名"
/>
</div>
<div>
<label for="employee_name_sub">従業員氏名（カナ）</label>
<input
type="text"
id="employee_name_sub"
name="EMPLOYEE_NAME_SUB"
placeholder="従業員氏名（カナ）"
/>
</div>
<div>
<label for="birthday_year">生年月日</label>
<input
type="number"
id="birthday_year"
name="BIRTHDAY_YEAR"
placeholder="（年）"
/>
</div>
<div>
<label for="birthday_month">生年月日</label>
<input
type="number"
id="birthday_month"
name="BIRTHDAY_MONTH"
placeholder="（月）"
/>
</div>
<div>
<label for="birthday_day">生年月日</label>
<input
type="number"
id="birthday_day"
name="BIRTHDAY_DAY"
placeholder="（日）"
/>
</div>
<div>
<label for="employee_sex">性別</label>
<select name="SEX" id="employee_sex">
<option value="" disabled>性別を選択してください</option>
<option value="1">男</option>
<option value="2">女</option>
<option value="3">その他</option>
</select>
</div>
<div>
<label for="employee_in_ymd">入社年月日</label>
<input type="date" id="employee_in_ymd" name="EMPLOYEE_IN_YMD" />
</div>
<div>
<label for="phone_number1">電話番号1</label>
<input type="text" id="phone_number1" name="PHONE_1" />
</div>
<div>
<label for="phone_number2">電話番号2</label>
<input type="text" id="phone_number2" name="PHONE_2" />
</div>
<div>
<label for="phone_number3">電話番号3</label>
<input type="text" id="phone_number3" name="PHONE_3" />
</div>
<div>
<label for="zipcode">郵便番号</label>
<input
type="number"
id="zipcode"
name="ZIPCODE"
placeholder="郵便番号"
/>
</div>
<div>
<label for="address">住所</label>
<input type="text" id="address" name="ADDRESS" placeholder="住所" />
</div>
<div>
<label for="employee_type">従業員タイプ</label>
<select name="EMPLOYEE_TYPE" id="employee_type">
<option value="" 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_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_in.jsp"
><button class="bg-blue-400 py-2 px-3">出勤登録</button></a
>
<a href="sd_out.jsp"
><button class="bg-blue-400 py-2 px-3">退勤登録</button></a
>
<a href="sd_insert.html"
><button class="bg-blue-400 py-2 px-3">従業員登録</button></a
>
</div>
</main>
</body>
</html>
```
:::
:::spoiler sd_insert.java
```java=
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class sd_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_sex = req.getParameter("SEX");
String employee_in_ymd = req.getParameter("EMPLOYEE_IN_YMD");
String phone_number = req.getParameter("PHONE_1");
String phone_number2 = req.getParameter("PHONE_2");
String phone_number3 = req.getParameter("PHONE_3");
String zipcode = req.getParameter("ZIPCODE");
String address = req.getParameter("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 t_employee where f_phone_number ='");
query.append(phone_number);
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 t_employee");
query.append("(f_employee_name, f_employee_name_sub, f_employee_birthday, f_employee_sex, f_in_ymd, f_phone_number, f_phone_number2, f_phone_number3, f_employee_address, f_employee_zipcode, f_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_sex);
query.append("','");
query.append(employee_in_ymd);
query.append("','");
query.append(phone_number);
query.append("','");
query.append(phone_number2);
query.append("','");
query.append(phone_number3);
query.append("','");
query.append(address);
query.append("','");
query.append(zipcode);
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_intime.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_intime 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 time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String in_time = 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 t_inout");
query.append("(f_employee_id, f_inout_in)");
query.append("values('");
query.append(employee_id);
query.append("','");
query.append(in_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_outtime.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_outtime 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 inout_id = req.getParameter("INOUT_ID");
SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String out_time = 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 t_inout set f_inout_out = '");
query.append(out_time);
query.append("' where f_inout_id = '");
query.append(inout_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