<%@ 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>
<link rel="stylesheet" href="css/css.css">
</head>
<body>
<%@include file="header.jsp" %>
<section>
시작화면입니다
</section>
<%@ include file = "footer.jsp" %>
</body>
</html>
<%@ 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>
<link rel="stylesheet" href="css/css.css">
<script>
window.onload = function() {
const submitBtn = document.querySelector("#submits");
let odnum = document.querySelector("input[name=odnum]");
let odwhere = document.querySelector("select[name=odwhere]");
let oddate = document.querySelector("input[name=oddate]");
let odcode = document.querySelector("select[name=odcode]");
let odmany = document.querySelector("input[name=odmany]");
submitBtn.addEventListener("click", f);
function f(event) {
event.preventDefault();
if(odnum.value.trim() =="") {
alert("주문번호가 입력되지 않았습니다!");
odnum.focus();
return;
}
if(odwhere.value != "S001" && odwhere.value !="S002") {
alert("주문점포가 선택되지 않았습니다!")
odwhere.focus();
return;
}
if(oddate.value.trim() =="") {
alert("주문일자가 입력되지 않았습니다!")
oddate.focus();
return;
}
if(odcode.value != "AA01"&&odcode.value != "AA02"&&odcode.value != "AA03") {
alert("제품코드가 입력되지 않았습니다!");
odcode.focus();
return;
}
if(odmany.value.trim() =="") {
alert("주문수량이 입력되지 않았습니다!");
odmany.focus();
return;
}
form.submit();
alert("주문등록이 정상적으로 등록 되었습니다!");
}
}
</script>
</head>
<body>
<%@include file="header.jsp" %>
<section id="se1">
<h1>주문등록</h1>
<form name="form" action="orderregist" method="GET">
<table>
<tr>
<td>주문번호</td>
<td><input type="text" name="odnum"></td>
</tr>
<tr>
<td>주문점포</td>
<td>
<select name="odwhere">
<option>점포선택</option>
<option>S001</option>
<option>S002</option>
</select>
</td>
</tr>
<tr>
<td>주문일자</td>
<td><input type="text" name="oddate"></td>
</tr>
<tr>
<td>제품코드</td>
<td>
<select name="odcode">
<option>코드선택</option>
<option>AA01</option>
<option>AA02</option>
<option>AA03</option>
</select>
</td>
</tr>
<tr>
<td>주문수량</td>
<td><input type="text" name="odmany"></td>
</tr>
<tr>
<td colspan="2">
<input id="submits" type="submit" value="주문등록">
<input type="reset" value="다시쓰기">
</td>
<td></td>
</tr>
</table>
</form>
</section>
<jsp:include page="footer.jsp"></jsp:include>
</body>
</html>
<%@page import="vo.RegistSelectVo"%>
<%@page import="java.util.ArrayList"%>
<%@page import="dao.RegistSelectDao"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="vo.DBcon"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="css/css.css">
<title>Insert title </title>
</head>
<body>
<%@include file="header.jsp" %>
<section>
<h1>주문목록</h1>
<table>
<tr>
<th>할인점코드</th>
<th>점포명</th>
<th>주문번호</th>
<th>주문일자</th>
<th>제품코드</th>
<th>제품명</th>
<th>주문수량</th>
<th>단가</th>
<th>소비자가격</th>
<th>할인가격</th>
</tr>
<c:forEach var="sel" items="${list }">
<c:set var="num"> ${sel.shopno }</c:set>
<c:set var="sname" value="${sel.shopname}"></c:set>
<c:set var="pcd" value="${sel.pcode}"></c:set>
<c:set var="pname">${sel.pname}</c:set>
<tr>
<td>${num }</td>
<td>${sname}</td>
<td>${sel.orderno }</td>
<td>${sel.orderDate }</td>
<td><c:out value="${pcd }"/></td>
<td><c:out value="${pname }"/></td>
<td><fmt:formatNumber value="${sel.getAmount()}"/></td>
<td><fmt:formatNumber value="${sel.cost }"/></td>
<td><fmt:formatNumber value="${sel.custmoney }"/></td>
<td><fmt:formatNumber value="${sel.custmoney }"/></td>
</tr>
</c:forEach>
</table>
</section>
<%@ include file = "footer.jsp" %>
</body>
</html>
<%@page import="vo.RegistSelectVo"%>
<%@page import="java.util.ArrayList"%>
<%@page import="dao.RegistSelectDao"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@page import="vo.DBcon"%>
<%@ 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>
<link rel="stylesheet" href="css/css.css">
</head>
<body>
<%
Connection conn = DBcon.getConnection();
String query = "SELECT o.SHOPNO, s.SHOPNAME, o.ORDERNO, ";
query+= "o.ORDERDATE, p.PCODE, p.PNAME, o.AMOUNT, p.COST, (p.COST* o.AMOUNT) as custmoney, ";
query+= "((p.COST* o.AMOUNT) - (p.COST* o.AMOUNT* (s.DISCOUNT/100))) AS discountmoney FROM tbl_order_202101 o, tbl_product_202101 p, tbl_shop_202101 s ";
query+= "WHERE o.PCODE = p.PCODE AND o.SHOPNO = s.SHOPNO";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
%>
<%@include file="header.jsp" %>
<section>
<h1>주문목록</h1>
<table>
<tr>
<th>할인점코드</th>
<th>점포명</th>
<th>주문번호</th>
<th>주문일자</th>
<th>제품코드</th>
<th>제품명</th>
<th>주문수량</th>
<th>단가</th>
<th>소비자가격</th>
<th>할인가격</th>
</tr>
<%
while(rs.next()) {
//substring 활용
String s =rs.getString("ORDERNO");
String result = s.substring(0,4) +"-" + s.substring(4,8);
//StringBuilder활용
StringBuilder sb2 = new StringBuilder(rs.getString("ORDERDATE"));
sb2.insert(4,"-");
sb2.insert(7,"-");
String dateResult = sb2.toString();
int ss = rs.getInt("COST");
String cost = String.format("%,d", ss);
int ss1 = rs.getInt("custmoney");
String custmoney = String.format("%,d",ss1);
int ss2 = rs.getInt("discountmoney");
String discountmoney = String.format("%,d", ss2);
%>
<tr>
<td><%=rs.getString("SHOPNO") %></td>
<td><%=rs.getString("SHOPNAME") %></td>
<td><%=result %></td>
<td><%=dateResult %></td>
<td><%=rs.getString("PCODE") %></td>
<td><%=rs.getString("PNAME") %></td>
<td><%=rs.getInt("AMOUNT") %></td>
<td><%=cost %></td>
<td><%=custmoney%></td>
<td><%=discountmoney %></td>
</tr>
<%
}
%>
</table>
</section>
<%@ include file = "footer.jsp" %>
</body>
</html>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="vo.DBcon"%>
<%@page import="java.sql.Connection"%>
<%@ 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>
<link rel="stylesheet" href="css/css.css">
</head>
<%
Connection conn = DBcon.getConnection();
String query ="SELECT PCODE, PNAME, COST, (cost*0.9) AS ten, (COST*0.85) AS fifteen ";
query += "FROM tbl_product_202101";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
%>
<body>
<%@include file="header.jsp" %>
<section>
<h1>제품코드조회</h1>
<table>
<tr>
<th>제품코드</th>
<th>제품명</th>
<th>단가</th>
<th>할인율(10%)</th>
<th>할인율(15%)</th>
</tr>
<%
while(rs.next()) {
String costResult = String.format("%,d", rs.getInt("COST"));
String ten = String.format("%,d", rs.getInt("ten"));
String fifteen = String.format("%,d", rs.getInt("fifteen"));
%>
<tr>
<td><%=rs.getString("PCODE") %></td>
<td><%=rs.getString("PNAME") %></td>
<td><%=costResult %></td>
<td><%=ten %></td>
<td><%=fifteen %></td>
</tr>
<%
}
%>
</table>
</section>
<%@ include file = "footer.jsp" %>
</body>
</html>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Connection"%>
<%@page import="vo.DBcon"%>
<%@ 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>
<link rel="stylesheet" href="css/css.css">
</head>
<body>
<%
Connection conn = DBcon.getConnection();
String query ="SELECT o.SHOPNO, p.PCODE, p.PNAME, sum(o.AMOUNT) AS total_amount ";
query+= "FROM tbl_order_202101 o, tbl_product_202101 p ";
query+= "WHERE p.PCODE = o.PCODE ";
query += "GROUP BY o.SHOPNO, p.PCODE, p.PNAME";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
%>
<%@include file="header.jsp" %>
<section>
<h1>점포별주문현황</h1>
<table>
<tr>
<th>할인점코드</th>
<th>제품코드</th>
<th>제품명</th>
<th>주문총수량</th>
</tr>
<%
while(rs.next()) {
%>
<jsp:useBean id="js" class="vo.JumboSelect"></jsp:useBean>
<jsp:setProperty property="shopno" name="js" value='<%=rs.getString("SHOPNO") %>'/>
<jsp:setProperty property="pcode" name="js" value='<%=rs.getString("PCODE") %>'/>
<jsp:setProperty property="pname" name="js" value='<%=rs.getString("PNAME") %>'/>
<jsp:setProperty property="amount" name="js" value='<%=rs.getInt("total_amount") %>'/>
<tr>
<td><jsp:getProperty property="shopno" name="js"/></td>
<td><jsp:getProperty property="pcode" name="js"/></td>
<td><jsp:getProperty property="pname" name="js"/></td>
<td><jsp:getProperty property="amount" name="js"/></td>
</tr>
<%--
<tr>
<td><%=rs.getString("SHOPNO") %></td>
<td><%=rs.getString("PCODE") %></td>
<td><%=rs.getString("PNAME") %></td>
<td><%=rs.getInt("AMOUNT") %></td>
</tr>
--%>
<%
}
%>
</table>
</section>
<%@ include file = "footer.jsp" %>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<header>
<h1><a href="index.jsp">할인점 주문프로그램 ver 1.0</a></h1>
<nav>
<ul>
<li><a href="regist.jsp">주문등록</a></li>
<li><a href="orderSelect">주문목록조회</a></li>
<li><a href="jumpoOrder.jsp">점포별주문현황</a></li>
<li><a href="codeSelect.jsp">제품코드조회</a></li>
<li><a href="index.jsp">홈으로</a></li>
</ul>
</nav>
</header>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<footer>
<p>저작권 영역</p>
</footer>
package servletStore;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.RegistSelectDao;
import vo.RegistSelectVo;
@WebServlet("/orderSelect")
public class orderSelect extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
RegistSelectDao rsd = new RegistSelectDao();
ArrayList<RegistSelectVo> ar = rsd.registSelect();
for(RegistSelectVo a : ar) {
String date =a.getOrderDate();
String result = date.substring(0, 4) +"-" +date.substring(4, 8);
a.setOrderDate(result);
}
request.setAttribute("list", ar);
request.getRequestDispatcher("registSelect.jsp").forward(request, response);
}
}
package servletStore;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.RegistDao;
import vo.DBcon;
@WebServlet("/orderregist")
public class orderregist extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String odnum = request.getParameter("odnum");
String odwhere = request.getParameter("odwhere");
String oddate = request.getParameter("oddate");
String odcode = request.getParameter("odcode");
String odmany = request.getParameter("odmany");
try {
RegistDao rd = new RegistDao(odnum, odwhere, oddate, odcode, odmany);
rd.regist();
response.sendRedirect("index.jsp");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import vo.DBcon;
import vo.RegistSelectVo;
public class RegistSelectDao {
//예전에 자바에서 할 떄는 service페이지가 계속 켜져 있어서 전역변수에
//값이 계속 저장돼서 안되는거고 요거는 계속 새로실행시키고 해서 ㄱㅊ은듯
public ArrayList<RegistSelectVo> ar = new ArrayList<>();
public ArrayList<RegistSelectVo> registSelect() {
try {
Connection conn = DBcon.getConnection();
String query = "SELECT * FROM orderlist";
PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
String shopno = rs.getString("SHOPNO");
String shopname = rs.getString("SHOPNAME");
String orderno = rs.getString("ORDERNO");
String orderDate= rs.getString("ORDERDATE");
String pcode = rs.getString("PCODE");
String pname = rs.getString("PNAME");
int amount = rs.getInt("AMOUNT");
int cost = rs.getInt("COST");
int custmoney = rs.getInt("custmoney");
int discountmoney = rs.getInt("discountmoney");
RegistSelectVo rsv = new RegistSelectVo();
rsv.setShopno(shopno);
rsv.setShopname(shopname);
rsv.setOrderno(orderno);
rsv.setOrderDate(orderDate);
rsv.setPcode(pcode);
rsv.setPname(pname);
rsv.setAmount(amount);
rsv.setCost(cost);
rsv.setCustmoney(custmoney);
rsv.setDiscountmoney(discountmoney);
ar.add(rsv);
}
} catch (Exception e) {
e.printStackTrace();
}
return ar;
}
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import vo.DBcon;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class RegistDao {
private String odnum;
private String odwhere;
private String oddate;
private String odcode;
private String odmany;
public void regist() {
try {
Connection conn = DBcon.getConnection();
String query = "INSERT INTO tbl_order_202101 VALUES (?,?,?,?,?)";
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, odnum);
stmt.setString(2, odwhere);
stmt.setString(3, oddate);
stmt.setString(4, odcode);
stmt.setString(5, odmany);
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
