쌍용교육 -JSP수업 71일차 - ch06_mvcPageMVC(16)

OrderDAO 내용추가

//사용자 - 전체 주문 개수/검색 주문 개수
	public int getOrderCountByMem_num(String keyfield, String keyword, int mem_num) throws Exception{
		Connection conn = null;
	      PreparedStatement pstmt = null;
	      String sql =null;
	      ResultSet rs = null;
	      String sub_sql = "";
	      int count = 0;
	      try {
	         //커넥션풀로부터 커넥션 할당
	         conn = DBUtil.getConnection();
	         
	         if(keyword != null && !"".equals(keyword)) {
	        	 //검색글 개수
	        	 if(keyfield.equals("1")) sub_sql += "AND item_name LIKE '%' || ? || '%'"; 
	        	 else if(keyfield.equals("2")) sub_sql += "AND order_num=?";
	         }
	         //SQL문 작성
	         sql="SELECT COUNT(*) FROM zorder JOIN (SELECT order_num,LISTAGG(item_name,',') "
	         		+"WITHIN GROUP (ORDER BY item_name) item_name FROM zorder_detail GROUP BY order_num) "
	         		+ "USING (order_num) WHERE mem_num=?"+ sub_sql;
	         	     
	         //PreparedStatment 객체 생성
	         pstmt = conn.prepareStatement(sql);
	         //?에 데이터 바인딩
	         pstmt.setInt(1, mem_num);
	         if(keyword != null && !"".equals(keyword)) {
	        	 pstmt.setString(2,keyword);
	         }
	         //SQL문 실행
	         rs = pstmt.executeQuery();
	         if(rs.next()) {
	        	 count = rs.getInt(1);
	         }
	      }catch(Exception e) {
	         throw new Exception(e);
	      }finally {
	         DBUtil.executeClose(null, pstmt, conn);
	      }
	      
	      return count;
	}
	//사용자 - 전체 주문 목록/검색 주문 목록
	public List<OrderVO> getListOrderByMem_num(int start, int end, String keyfield,String keyword,int mem_num) 
										throws Exception{
		Connection conn = null;
	      PreparedStatement pstmt = null;
	      String sql =null;
	      String sub_sql = "";
	      List<OrderVO> list= null;
	      ResultSet rs = null;
	      int cnt = 0;
	      try {
		         //커넥션풀로부터 커넥션 할당
		         conn = DBUtil.getConnection();
		         
		         if(keyword != null && !"".equals(keyword)) {
		        	 //검색글 목록
		        	 if(keyfield.equals("1")) sub_sql += "AND item_name LIKE '%' || ? || '%'"; 
		        	 else if(keyfield.equals("2")) sub_sql += "AND order_num=?";
		         }
		         //SQL문 작성
		         sql= "SELECT * FROM (SELECT a.*, rownum rnum FROM("
	         		+ "SELECT * FROM zorder JOIN (SELECT order_num,LISTAGG(item_name,',') "
	         		+ "WITHIN GROUP (ORDER BY item_name) item_name FROM zorder_detail GROUP BY order_num) "
	         		+ "USING (order_num) WHERE mem_num=?"+ sub_sql
	         		+" ORDER BY order_num DESC)a) WHERE rnum>=? AND rnum<=?";
		         	     
		         //PreparedStatment 객체 생성
		         pstmt = conn.prepareStatement(sql);
		         //?에 데이터 바인딩
		         pstmt.setInt(1, mem_num);
		         if(keyword != null && !"".equals(keyword)) {
		        	 pstmt.setString(++cnt,keyword);
		         }
		         pstmt.setInt(++cnt, start);
		         pstmt.setInt(++cnt, end);
		         //SQL문 실행
		         rs = pstmt.executeQuery();
		         list = new ArrayList<OrderVO>();
		         while(rs.next()) {
		        	 OrderVO order = new OrderVO();
		        	 order.setOrder_num(rs.getInt("order_num"));
		        	 order.setOrder_total(rs.getInt("oreder_total"));
		        	 order.setItem_name(rs.getString("item_name"));
		        	 order.setStatus(rs.getInt("status"));
		        	 order.setReg_date(rs.getDate("reg_date"));
		        	 
		        	 list.add(order);
		         }
		      }catch(Exception e) {
		         throw new Exception(e);
		      }finally {
		         DBUtil.executeClose(null, pstmt, conn);
		      }
		return list;
	}

kr.member.action/ MyPageAction 내용추가 (구매상품정보 추가)

package kr.member.action;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.board.dao.BoardDAO;
import kr.board.vo.BoardVO;
import kr.controller.Action;
import kr.member.dao.MemberDAO;
import kr.member.vo.MemberVO;
import kr.order.dao.OrderDAO;
import kr.order.vo.OrderVO;
  
public class MyPageAction implements Action{

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		HttpSession session = request.getSession();
		Integer user_num = (Integer)session.getAttribute("user_num");
		if(user_num == null) {//로그인이 되지 않은 경우
			return "redirect:/member/loginForm.do";
		}
		//로그인이 된 경우
		//회원정보
		MemberDAO dao = MemberDAO.getInstance();
		MemberVO member = dao.getMember(user_num);
		
		//관심 게시물 정보
		BoardDAO boardDAO = BoardDAO.getInstance();
		List<BoardVO> boardList = boardDAO.getListBoardFav(1, 5, user_num);
		
		
		//구매상품 정보
		OrderDAO orderDAO = OrderDAO.getInstance();
		List<OrderVO> orderList =orderDAO.getListOrderByMem_num(1, 5, null, null, user_num);
		
		request.setAttribute("member", member);
		request.setAttribute("boardList", boardList);
		request.setAttribute("orderList", orderList);
		//JSP 경로 반환
		return "/WEB-INF/views/member/myPage.jsp";
	}

}

views/member/mypage.jsp 내용추가 (맨아래쪽)

<h3>상품구매목록<input type="button" value="더보기" onclick="location.href='${pageContext.request.contextPath}/order/orderList.do'"></h3>
			<table>
				<tr>
					<th>주문번호</th>
					<th>상품명</th>
					<th>주문날짜</th>
					<th>배송상태</th>
				</tr>
				<c:forEach var="order" items="${orderList}">
				<tr>
					<td>${order.order_num}</td>
					<td><a href="${pageContext.request.contextPath}/order/orderDetail.do?order_num=${order.order_num}">${order.item_name}</a></td>
					<td>${order.reg_date}</td>
					<td>
					<c:if test="${order.status==1}">배송대기</c:if>
					<c:if test="${order.status==2}">배송준비중</c:if>
					<c:if test="${order.status==3}">배송중</c:if>
					<c:if test="${order.status==4}">배송완료</c:if>
					<c:if test="${order.status==5}">주문취소</c:if>
					</td>
					
				</tr>
				</c:forEach>
			</table>

OrderDAO 내용추가

//개별 상품 목록
	public List<OrderDetailVO> getListOrderDetail(int order_num)throws Exception{
		Connection conn = null;
	    PreparedStatement pstmt = null;
	    String sql =null;
	    ResultSet rs = null;
	    List<OrderDetailVO> list = null;
	    try {
	       //커넥션풀로부터 커넥션 할당
	       conn = DBUtil.getConnection();
	       
	       //SQL문 작성
	       sql="SELECT * FROM zorder_detail WHERE order_num=? "
	       		+ "ORDER BY item_num DESC";
	       
	       //PreparedStatment 객체 생성
	       pstmt = conn.prepareStatement(sql);
	       
	       //?에 데이터 바인딩
	       pstmt.setInt(1, order_num);
	       
	       rs = pstmt.executeQuery();
	       list = new ArrayList<OrderDetailVO>();
	       while(rs.next()) {
	    	   OrderDetailVO detail = new OrderDetailVO();
	    	   detail.setItem_num(rs.getInt("item_num"));
	    	   detail.setItem_name(rs.getString("item_name"));
	    	   detail.setItem_price(rs.getInt("item_price"));
	    	   detail.setItem_total(rs.getInt("item_total"));
	    	   detail.setOrder_quantity(rs.getInt("order_quantity"));
	    	   detail.setOrder_num(rs.getInt("order_num"));
	    	   
	    	   list.add(detail);
	       }
	    }catch(Exception e) {
	       throw new Exception(e);
	    }finally {
	       DBUtil.executeClose(rs, pstmt, conn);
	    }
		
		return list;
	}
	//주문 삭제(삭제시 재고를 원상 복귀시키 않음, 주문 취소일 때 재고 수량 원상 복귀)
	//관리자/사용자 - 주문 상세
	public OrderVO getOrder(int order_num)throws Exception{
		Connection conn = null;
	    PreparedStatement pstmt = null;
	    String sql =null;
	    ResultSet rs = null;
	    OrderVO order = null;
	    
	    try {
	       //커넥션풀로부터 커넥션 할당
	       conn = DBUtil.getConnection();
	       
	       //SQL문 작성
	       sql="SELECT * FROM zorder WHERE order_num=?";
	       
	       //PreparedStatment 객체 생성
	       pstmt = conn.prepareStatement(sql);
	       
	       //?에 데이터 바인딩
	       pstmt.setInt(1, order_num);
	       
	       rs=pstmt.executeQuery();
	       
	       if(rs.next()) {
	    	   order = new OrderVO();
	    	   order.setOrder_num(rs.getInt("order_num"));
	    	   order.setOrder_total(rs.getInt("order_total"));
	    	   order.setPayment(rs.getInt("payment"));
	    	   order.setStatus(rs.getInt("status"));
	    	   order.setReceive_name(rs.getString("receive_name"));
	    	   order.setReceive_post(rs.getString("receive_post"));
	    	   order.setReceive_address1(rs.getString("receive_address1"));
	    	   order.setReceive_address2(rs.getString("receive_address2"));
	    	   order.setReceive_phone(rs.getString("receive_phone"));
	    	   order.setNotice(rs.getString("notice"));
	    	   order.setReg_date(rs.getDate("reg_date"));
	    	   order.setMem_num(rs.getInt("mem_num"));
	       }
	    }catch(Exception e) {
	       throw new Exception(e);
	    }finally {
	       DBUtil.executeClose(rs, pstmt, conn);
	    }
		return order;
		
	}

UserOrderDetailAction.java

package kr.order.action;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import kr.controller.Action;
import kr.order.dao.OrderDAO;
import kr.order.vo.OrderDetailVO;
import kr.order.vo.OrderVO;

public class UserOrderDetailAction implements Action {

	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		HttpSession session = request.getSession();
		Integer user_num = (Integer)session.getAttribute("user_num");
		if(user_num == null) { //로그인이 되지 않은 경우
			return "redirect:/member/loginForm.do";
		}
		//로그인 된 경우
		int order_num = Integer.parseInt(request.getParameter("order_num"));
		OrderDAO dao = OrderDAO.getInstance();
		//주문정보 반환
		OrderVO order = dao.getOrder(order_num);
		if(order.getMem_num()!=user_num) {
			//구매자 회원번호와 로그인한 회원번호가 불일치할 경우
			return "/WEB-INF/views/common/notice.jsp";
		}
		//주문한 개별상품정보 반환
		List<OrderDetailVO> detailList = dao.getListOrderDetail(order_num);
		
		request.setAttribute("order", order);
		request.setAttribute("detailList", detailList);
		return "/WEB-INF/views/order/user_detail.jsp";
	}

}

views/order/user_detail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>구매상세</title>
<link rel="stylesheet"
	href="${pageContext.request.contextPath}/css/style.css" type="text/css">
</head>
<body>
	<div class="page-main">
		<jsp:include page="/WEB-INF/views/common/header.jsp" />
		<div class="content-main">
			<h2>구매상세</h2>
			<table>
				<tr>
					<th>상품명</th>
					<th>수량</th>
					<th>상품가격</th>
					<th>합계</th>
				</tr>
				<c:forEach var="detail" items="${detailList}">
					<tr>
						<td>${detail.item_name}</td>
						<td class="align-center">
							<fmt:formatNumber value="${detail.order_quantity}" />
						</td>
						<td class="align-center">
						<fmt:formatNumber value="${detail.item_price}" />원</td>
						<td class="align-center">
						<fmt:formatNumber value="${detail.item_total}" />원 <br> 
						</td>
					</tr>
				</c:forEach>
				<tr>
					<td colspan="3" class="align-right"><b>총구매금액</b></td>
					<td class="align-center">
					<fmt:formatNumber value="${order.order_total}" />원
					</td>
				</tr>
			</table>
	
				<ul>
					<li>
						<span>받는 사람</span>${order.receive_name}
						
					</li>
					<li>
						<span>우편번호</span>${order.receive_post}
					</li>
					<li>
						<span>주소</span>${order.receive_address1} ${order.receive_address2}
					</li>
					<li>
						<span>전화번호</span>${order.receive_phone} 
					<li>
						<span>남기실 말씀</span>${order.notice} 
					</li>
					<li>
						<span>결제수단</span>
						<c:if test="${order.payment==1}">통장입금</c:if>
						<c:if test="${order.payment==2}">카드결제</c:if>
					</li>
					<li>
						<span>배송상태</span>
						<c:if test="${order.status==1}">배송대기</c:if>
						<c:if test="${order.status==2}">배송준비중</c:if>
						<c:if test="${order.status==3}">배송중</c:if>
						<c:if test="${order.status==4}">배송완료</c:if>
						<c:if test="${order.status==5}">주문취소</c:if>
						
					</li>
					<li class="align-center">
						<c:if test="${order.status==1}">
						<input type="button" value="배송지정보수정" onclick="location.href='orderModifyForm.do?order_num=${order.order_num}'">
						<input type="button" value="주문취소" id="order_cancel">
						<script>
							const order_cancel = document.getElementById('order_cancel');
							order_cancel.onclick=function(){
								let choice = confirm('주문을 취소하시겠습니까?');
								if(choice){
									location.replace('orderCancel.do?order_num=${order.order_num}');//히스토리 삭제
								}
							};
						</script>
						</c:if>
						<input type="button" value="주문목록" onclick="location.href='orderList.do'">
						<input type="button" value="MY페이지" onclick="location.href='${pageContext.request.contextPath}/member/myPage.do'">
					</li>
				</ul>
		</div>
	</div>
	
</body>
</html>