개발 ON
  • [ ETC/정보처리산업기사 ]
    [정보처리산업기사 | 실기] 진단검사 프로그램 SQL문 수정
    2023년 08월 16일 23시 02분 06초에 업로드 된 글입니다.
    작성자: 이주여이

    환자 조회

    수정 전

    <section>
<h2>환자조회</h2>
<form>
<table border="1">
<tr>
<td>환자번호</td>
<td>환자성명</td>
<td>생년월일</td>
<td>성별</td>
<td>전화번호</td>
<td>지역</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
try {
	String sql = "select p_no, p_name, p_birth, p_gender, p_tel1, p_tel2, p_tel3, p_city from tbl_patient_202004";
	PreparedStatement pstmt = c.prepareStatement(sql);
	ResultSet rs = pstmt.executeQuery();
	while(rs.next()) {
		String birth = rs.getString(3).substring(0,4) + "년" + rs.getString(3).substring(4,6) + "월" + rs.getString(3).substring(6,8) + "일";
		String gender = rs.getString(4);
		if(gender.equals("M"))
			gender = "남자";
		else
			gender = "여자";
		String tel1 = rs.getString(5);
		String tel2 = rs.getString(6);
		String tel3 = rs.getString(7);
		String tel_ = tel1 + "-" + tel2 + "-" + tel3;
		String city = rs.getString(8);
		if(city.equals("10")) {
			city = "서울";
		} else if(city.equals("20")) {
			city = "경기";
		} else if(city.equals("30")) {
			city = "강원";
		} else if(city.equals("40")) {
			city = "대구";
		}
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=birth %></td>
<td><%=gender %></td>
<td><%=tel_ %></td>
<td><%=city %></td>
</tr>
<%
	}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

     

    수정 후

    <%@page import="java.util.Date"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section>
<h2>환자조회</h2>
<form>
<table border="1">
<tr>
<td>환자번호</td>
<td>환자성명</td>
<td>생년월일</td>
<td>성별</td>
<td>전화번호</td>
<td>지역</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
try {
	String sql = "select p_no 환자번호, p_name 환자성명, " +
					"substr(p_birth,1,4)||'년'||substr(p_birth,5,2)||'월'||substr(p_birth,7,2)||'일' 생년월일, " +
					"case p_gender when 'M' then '남' else '여' end 성별, " +
					"p_tel1||'-'||p_tel2||'-'||p_tel3 전화번호, " +
					"case p_city when '10' then '서울' when '20' then '경기' when '30' then '강원' when '40' then '대구' end 지역 " +
					"from tbl_patient_202004 p";
	PreparedStatement pstmt = c.prepareStatement(sql);
	ResultSet rs = pstmt.executeQuery();
	while(rs.next()) {
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=rs.getString(4) %></td>
<td><%=rs.getString(5) %></td>
<td><%=rs.getString(6) %></td>
</tr>
<%
	}
} catch(Exception e) {
	e.printStackTrace();
}
%>

</table>
</form>

</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

     

    검사결과조회

    수정 전

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section>
<h2>검사결과조회</h2>
<form>
<table border="1">
<tr>
<td>환자번호</td>
<td>환자명</td>
<td>검사명</td>
<td>검사시작일</td>
<td>검사상태</td>
<td>검사완료일</td>
<td>검사결과</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
try {
	String sql = "select p.p_no, p.p_name, l.t_name, r.t_sdate, r.t_status, r.t_ldate, r.t_result " +
			 		"from tbl_patient_202004 p, tbl_result_202004 r, tbl_lab_test_202004 l " +
			 		"where p.p_no = r.p_no and r.t_code = l.t_code " +
			 		"group by p.p_no, p.p_name, l.t_name, r.t_sdate, r.t_status, r.t_ldate, r.t_result " +
			 		"order by p.p_no" ;
PreparedStatement pstmt = c.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
	String startDate = rs.getString(4).substring(0,10);
	String lastDate = rs.getString(6).substring(0,10);
	String status = rs.getString(5);
	String result = rs.getString(7);
	if(status.equals("1")) {
		status = "검사중";
	} else {
		status = "검사완료";
	}
	if(result.equals("X")) {
		result = "미입력";
	} else if(result.equals("P")){
		result = "양성";
	} else {
		result = "음성";
	}
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=startDate %></td>
<td><%=status %></td>
<td><%=lastDate %></td>
<td><%=result %></td>
</tr>
<%
}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>

     

    수정 후

    <%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ include file="DB.jsp" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:include page="header.jsp"></jsp:include>

<section>
<h2>검사결과조회</h2>
<form>
<table border="1">
<tr>
<td>환자번호</td>
<td>환자명</td>
<td>검사명</td>
<td>검사시작일</td>
<td>검사상태</td>
<td>검사완료일</td>
<td>검사결과</td>
</tr>
<%
request.setCharacterEncoding("UTF-8");
try {
	String sql = "select p.p_no 환자번호, p.p_name 환자명, l.t_name 검사명, " +
					"to_char(r.t_sdate, 'yyyy-MM-dd') 검사시작일, " +
					"case r.t_status when '1' then '검사중' when '2' then '검사완료' end 검사상태, " +
					"to_char(r.t_ldate, 'yyyy-MM-dd') 검사완료일, " +
					"case t_result when 'X' then '미입력' when 'P' then '양성' when 'N' then '음성' end 검사결과 " +
					"from tbl_patient_202004 p, tbl_result_202004 r, tbl_lab_test_202004 l " +
					"where p.p_no = r.p_no and r.t_code = l.t_code " +
					"group by p.p_no, p.p_name, l.t_name, r.t_sdate, r.t_status, r.t_ldate, r.t_result " +
					"order by p.p_no"; 
PreparedStatement pstmt = c.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
%>
<tr>
<td><%=rs.getString(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=rs.getString(4) %></td>
<td><%=rs.getString(5) %></td>
<td><%=rs.getString(6) %></td>
<td><%=rs.getString(7) %></td>
</tr>
<%
}
} catch(Exception e) {
	e.printStackTrace();
}
%>
</table>
</form>
</section>

<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>
    저작자표시 비영리 동일조건

    'ETC > 정보처리산업기사' 카테고리의 다른 글

    [정보처리산업기사 | 실기] 점수 테이블  (4) 2023.09.01
    [정보처리산입기사 | 실기] UI 테스트  (0) 2023.08.23
    [정보처리산업기사 | 실기] 백신예약 프로그램  (0) 2023.08.16
    [정보처리산업기사 | 실기] war  (0) 2023.08.15
    [정보처리산업기사 | 실기] 지역구의원투표 프로그램  (0) 2023.08.14
      댓글

    티스토리툴바