---
# System prepended metadata

title: SD課題 チームC
tags: [SD課題]

---

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

```
:::