UserOrderCancelAction
package kr.order.action;
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.OrderVO;
public class UserOrderCancelAction 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 db_order = dao.getOrder(order_num);
if(db_order.getMem_num()!=user_num) {
//타인의 주문정보른 취소할 수 없음
return "/WEB-INF/views/common/notice.jsp";
}
if(db_order.getStatus()>1) {
//배송준비중 이상으로 관리자가 변경한 상품을 주문자가 취소할 수 없음
request.setAttribute("notice_msg", "배송상태가 변경되어 주문자가 주문을 취소할 수 없음");
request.setAttribute("notice_url", request.getContextPath()+"/order/orderDetail.do?order_num="+order_num);
return "/WEB-INF/views/common/alert_view.jsp";
}
//주문 취소
dao.updateOrderCancel(order_num);
request.setAttribute("notice_msg", "주문취소가 완료되었습니다.");
request.setAttribute("notice_url", request.getContextPath()+"/order/orderDetail.do?order_num="+order_num);
return "/WEB-INF/views/common/alert_view.jsp";
}
}
UserOrderListAction
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.OrderVO;
import kr.util.PagingUtil;
public class UserOrderListAction 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";
}
String pageNum = request.getParameter("pageNum");
if(pageNum == null) pageNum = "1";
String keyfield = request.getParameter("keyfield");
String keyword = request.getParameter("keyword");
OrderDAO dao = OrderDAO.getInstance();
int count = dao.getOrderCountByMem_num(keyfield, keyword, user_num);
//페이지 처리
PagingUtil page = new PagingUtil(keyfield,keyword,Integer.parseInt(pageNum),count,20,10,"orderList.do");
List<OrderVO> list = null;
if(count>0) {
list = dao.getListOrderByMem_num(page.getStartRow(), page.getEndRow(), keyfield, keyword, user_num);
}
request.setAttribute("count", count);
request.setAttribute("list", list);
request.setAttribute("page",page.getPage());
return "/WEB-INF/views/order/user_list.jsp";
}
}
user_list.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">
<script type="text/javascript">
window.onload = function(){
const myForm = document.getElementById('search_form');
//이벤트 연결
myForm.onsubmit = function(){
const keyword = document.getElementById('keyword');
if(keyword.value.trim()==''){
alert('검색어를 입력하세요');
keyword.value='';
keyword.focus();
return false;
}
};
}
</script>
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>구매 목록</h2>
<form id="search_form" action="orderList.do" method="get">
<ul class="search">
<li>
<select name="keyfield">
<%-- c:if와 selected를 쓰는건 검색을 계속 유지 시키기 위해서 --%>
<option value="1" <c:if test="${param.keyfield ==1}">selected</c:if>>상품명</option>
<option value="2" <c:if test="${param.keyfield ==2}">selected</c:if>>주문번호</option>
</select>
</li>
<li>
<input type="search" size="16" name="keyword" id="keyword" value="${param.keyword}">
</li>
<li>
<input type="submit" value="검색">
</li>
</ul>
</form>
<div class="list-space align-right">
<input type="button" value="목록" onclick="location.href='orderList.do'">
<input type="button" value="My페이지" onclick="location.href='${pageContext.request.contextPath}/member/myPage.do'">
</div>
<c:if test="${count==0}">
<div class="result-display">
표시할 게시물이 없습니다.
</div>
</c:if>
<c:if test="${count>0}">
<table>
<tr>
<th>주문번호</th>
<th>상품명</th>
<th>총구매금액</th>
<th>주문날짜</th>
<th>상태</th>
</tr>
<c:forEach var="order" items="${list}">
<tr>
<td>${order.order_num}</td>
<td><a href="orderDetail.do?order_num=${order.order_num}">${order.item_name}</a></td>
<td><fmt:formatNumber value="${order.order_total}"/>원</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>
<div class="align-center">${page}</div>
</c:if>
</div>
</div>
</body>
</html>
header.jsp 추가
<li>
<a href="${pageContext.request.contextPath}/order/adminList.do">주문관리</a>
</li>
OrderDAO 추가
//관리자 - 전체 주문 개수/검색 주문 개수
public int getOrderCount(String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
ResultSet rs = null;
String sub_sql = "";
int count = 0;
int cnt = 0;
try {
//커넥션풀로부터 커넥션 할당
conn = DBUtil.getConnection();
if(keyword != null && !"".equals(keyword)) {
//검색글 개수
if(keyfield.equals("1")) sub_sql += "WHERE order_num=?";
else if(keyfield.equals("2")) sub_sql += "WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE item_name LIKE '%' || ? || '%'";
}
//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) JOIN zmember USING(mem_num) "+ sub_sql;
//PreparedStatment 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
if(keyword != null && !"".equals(keyword)) {
pstmt.setString(++cnt,keyword);
}
//SQL문 실행
rs = pstmt.executeQuery();
if(rs.next()) {
count = rs.getInt(1);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return count;
}
//관리자 - 전체 주문 목록/검색 주문 목록
public List<OrderVO> getListOrder(int start, int end, String keyfield, String keyword) throws Exception{
Connection conn = null;
PreparedStatement pstmt = null;
String sql =null;
ResultSet rs = null;
List<OrderVO> list = null;
String sub_sql = "";
int cnt = 0;
try {
//커넥션풀로부터 커넥션 할당
conn = DBUtil.getConnection();
if(keyword != null && !"".equals(keyword)) {
//검색글 개수
if(keyfield.equals("1")) sub_sql += "WHERE order_num=?";
else if(keyfield.equals("2")) sub_sql += "WHERE id LIKE '%' || ? || '%'";
else if(keyfield.equals("3")) sub_sql += "WHERE item_name LIKE '%' || ? || '%'";
}
//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) JOIN zmember USING(mem_num) "+ sub_sql
+" ORDER BY order_num DESC)a) WHERE rnum>=? AND rnum<=?";
//PreparedStatment 객체 생성
pstmt = conn.prepareStatement(sql);
//?에 데이터 바인딩
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.setItem_name(rs.getString("item_name"));
order.setOrder_total(rs.getInt("order_total"));
order.setStatus(rs.getInt("status"));
order.setReg_date(rs.getDate("reg_date"));
order.setId(rs.getString("id"));
list.add(order);
}
}catch(Exception e) {
throw new Exception(e);
}finally {
DBUtil.executeClose(rs, pstmt, conn);
}
return list;
}
AdminListAction
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.OrderVO;
import kr.util.PagingUtil;
public class AdminListAction 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";
}
Integer user_auth = (Integer)session.getAttribute("user_auth");
if(user_auth !=9) { //관리자가 아닌경우
return "/WEB-INF/views/common/notice.jsp";
}
String pageNum = request.getParameter("pageNum");
if(pageNum == null) pageNum = "1";
String keyfield = request.getParameter("keyfield");
String keyword = request.getParameter("keyword");
OrderDAO dao = OrderDAO.getInstance();
int count = dao.getOrderCount(keyfield, keyword);
//페이지 처리
PagingUtil page = new PagingUtil(keyfield,keyword,Integer.parseInt(pageNum),count,20,10,"adminList.do");
List<OrderVO> list = null;
if(count>0) {
list = dao.getListOrder(page.getStartRow(), page.getEndRow(), keyfield, keyword);
}
request.setAttribute("count", count);
request.setAttribute("list", list);
request.setAttribute("page",page.getPage());
return "/WEB-INF/views/order/admin_list.jsp";
}
}
admin_list.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">
<script type="text/javascript">
window.onload = function(){
const myForm = document.getElementById('search_form');
//이벤트 연결
myForm.onsubmit = function(){
const keyword = document.getElementById('keyword');
if(keyword.value.trim()==''){
alert('검색어를 입력하세요');
keyword.value='';
keyword.focus();
return false;
}
};
}
</script>
</head>
<body>
<div class="page-main">
<jsp:include page="/WEB-INF/views/common/header.jsp"/>
<div class="content-main">
<h2>구매 목록(관리자전용)</h2>
<form id="search_form" action="adminList.do" method="get">
<ul class="search">
<li>
<select name="keyfield">
<%-- c:if와 selected를 쓰는건 검색을 계속 유지 시키기 위해서 --%>
<option value="1" <c:if test="${param.keyfield ==1}">selected</c:if>>주문번호</option>
<option value="2" <c:if test="${param.keyfield ==2}">selected</c:if>>ID</option>
<option value="2" <c:if test="${param.keyfield ==3}">selected</c:if>>상품명</option>
</select>
</li>
<li>
<input type="search" size="16" name="keyword" id="keyword" value="${param.keyword}">
</li>
<li>
<input type="submit" value="검색">
</li>
</ul>
</form>
<div class="list-space align-right">
<input type="button" value="목록" onclick="location.href='orderList.do'">
<input type="button" value="홈으로" onclick="location.href='${pageContext.request.contextPath}/main/main.do'">
</div>
<c:if test="${count==0}">
<div class="result-display">
표시할 게시물이 없습니다.
</div>
</c:if>
<c:if test="${count>0}">
<table>
<tr>
<th>주문번호</th>
<th>상품명</th>
<th>총구매금액</th>
<th>주문날짜</th>
<th>상태</th>
</tr>
<c:forEach var="order" items="${list}">
<tr>
<td>${order.order_num}</td>
<td>${order.id}</td>
<td><a href="adminDetail.do?order_num=${order.order_num}">${order.item_name}</a></td>
<td><fmt:formatNumber value="${order.order_total}"/>원</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>
<div class="align-center">${page}</div>
</c:if>
</div>
</div>
</body>
</html>
