[Spring] 상품 월 별 상세보기

JSP

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<div class="col-md-12 card">
	<div class="card-header">
		<h3>월별 매입 현황</h3>
		<form class="row" style="justify-content: space-between;">
			<select id="year" name="year" style="width: 100px"
				class="form-control">
				<c:forEach var="yr" begin="2020" end="2024">
					<option value="${yr}"
						<c:if test="${param.year==yr or (param.year=='' and yr==2020)}">selected</c:if>>${yr}</option>
				</c:forEach>
			</select>
			<div class="card-tools">
				<div class="input-group input-group-sm" style="width: 150px;">
					<input type="text" name="amt" class="form-control float-right"
						placeholder="검색할 매입금액의 합계"
						value="<c:if test="${param.amt!=null}">${param.amt}</c:if>">
					<div class="input-group-append">
						<button type="submit" class="btn btn-default">
							<i class="fas fa-search"></i>
						</button>
					</div>
				</div>
			</div>
		</form>
	</div>
<div class="card-body">
	<!-- items : 배열, Collection(list, Set), Map(hashTable, hashMap, sortedMap) -->
	<table class="table table-bordered table-hover">
		<thead>
			<tr>
				<th style="width: 10%">월</th>
				<th style="width: 45%">매입 재고 수량</th>
				<th style="width: 45%">매입 금액 합계</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="data" items="${data.content}">
 				<tr onclick="javascript:location.href='/buyprod/detail/${param.year}/${data.buyDateMonth}'"> 
<%--				<tr onclick="javascript:location.href='/buyprod/detail?year=${data.year}&month=${data.buyDateMonth}'">--%>
					<td>${data.buyDateMonth}월</td>
					<td><fmt:formatNumber value="${data.buyQty}" type="number"
							pattern="#,###" /> 개</td>
					<td><fmt:formatNumber value="${data.amt}" type="number"
							pattern="#,###" /> 원</td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
	<div class="card-footer clearfix">
		<!-- startPage가 5보다 클 때에만 [이전] 활성화 -->
		${data.pagingArea}
	</div>
</div>
</div>

detail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<div class="col-md-12 card">
	<div class="card-header">
		<h3>월별 매입 현황</h3>
		<div class="row" style="justify-content: space-between;">
		<div class="row" style="width:50%;">
			<select id="year" name="year" style="width: 100px;  margin-right:20px;"
				class="form-control">
				<c:forEach var="yr" begin="2020" end="2024">
					<option value="${yr}">${yr}</option>
				</c:forEach>
			</select>
			<select name="month" id="month" class="form-control" style="width:80px;">
				<c:forEach var="mn" begin="1" end="12">
					<option value="${mn}">${mn}월</option>
				</c:forEach>
			</select>
		</div>
		<form>
			<div class="card-tools">
				<div class="input-group input-group-sm" style="width: 150px;">
					<input type="text" name="amt" class="form-control float-right"
						placeholder="검색할 매입금액의 합계"
						value="<c:if test="${param.amt!=null}">${param.amt}</c:if>">
					<div class="input-group-append">
						<button type="submit" class="btn btn-default">
							<i class="fas fa-search"></i>
						</button>
					</div>
				</div>
			</div>
		</form>
		</div>
	</div>
<div class="card-body">
	<!-- items : 배열, Collection(list, Set), Map(hashTable, hashMap, sortedMap) -->
	<table class="table table-bordered table-hover">
		<thead>
			<tr>
				<th>순번</th>
				<th>매입 날짜</th>
				<th>매입 번호</th>
				<th>상품명</th>
				<th>재고 수량</th>
				<th>가격</th>
				<th>총합</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="data" items="${data.content}">
				<tr>
					<td>${data.rnum}</td>
					<td><fmt:formatDate value="${data.buyDate}" pattern="yyyy-MM-dd"/></td>
					<td>${data.buyProd}</td>
					<td>${data.prodName}</td>
					<td><fmt:formatNumber value="${data.buyQty}" type="number" pattern="#,###" /></td>
					<td><fmt:formatNumber value="${data.buyCost}" type="number" pattern="#,###" /></td>
					<td><fmt:formatNumber value="${data.buyCost*data.buyQty}" type="number" pattern="#,###" /></td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
	<div class="card-footer clearfix">
		<!-- startPage가 5보다 클 때에만 [이전] 활성화 -->
		${data.pagingArea}
	</div>
</div>
</div>
<script>
let yeari = document.querySelector('#year');
let monthi = document.querySelector('#month');
const url = window.location.href;
getYearAndMonth(url);
yeari.addEventListener("change", (event) => {
	let year = yeari.value;
	let month = monthi.value;
	location.href = "/buyprod/detail/"+year+"/"+month;
})
monthi.addEventListener("change", (event) => {
	let year = yeari.value;
	let month = monthi.value;
	location.href = "/buyprod/detail/"+year+"/"+month;
})
function getYearAndMonth(url) {
    const urlObj = new URL(url);
    const parts = urlObj.pathname.split('/').filter(part => part !== '');
    console.log(parts[2]);
    console.log(parts[3]);
    let yearu = parts[2]
    if(parts[2]==0){
    	yearu = 2020
    } 
    document.querySelector('#year').value = yearu;
   	document.querySelector('#month').value = parts[3];

};
</script>

 

이전 방법

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<div class="col-md-12 card">
	<div class="card-header">
		<h3>월별 매입 현황</h3>
		<form class="row" style="justify-content: space-between;">
		<div class="row" style="width:50%;">
			<select id="year" name="year" style="width: 100px;  margin-right:20px;"
				class="form-control">
				<c:forEach var="yr" begin="2020" end="2024">
					<option value="${yr}"
						<c:if test="${param.year==yr or (param.year=='' and yr==2020)}">selected</c:if>>${yr}</option>
				</c:forEach>
			</select>
			<select name="month" class="form-control" style="width:80px;">
				<c:forEach var="mn" begin="1" end="12">
					<option value="${mn}"
						<c:if test="${param.month==mn}">selected</c:if>>${mn}</option>
				</c:forEach>
			</select>
		</div>
			<div class="card-tools">
				<div class="input-group input-group-sm" style="width: 150px;">
					<input type="text" name="amt" class="form-control float-right"
						placeholder="검색할 매입금액의 합계"
						value="<c:if test="${param.amt!=null}">${param.amt}</c:if>">
					<div class="input-group-append">
						<button type="submit" class="btn btn-default">
							<i class="fas fa-search"></i>
						</button>
					</div>
				</div>
			</div>
		</form>
	</div>
<div class="card-body">
	<!-- items : 배열, Collection(list, Set), Map(hashTable, hashMap, sortedMap) -->
	<table class="table table-bordered">
		<thead>
			<tr>
				<th>순번</th>
				<th>매입 날짜</th>
				<th>매입 번호</th>
				<th>상품명</th>
				<th>재고 수량</th>
				<th>가격</th>
				<th>총합</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="data" items="${data.content}">
				<tr>
					<td>${data.rnum}</td>
					<td><fmt:formatDate value="${data.buyDate}" pattern="yyyy-MM-dd"/></td>
					<td>${data.buyProd}</td>
					<td>${data.prodName}</td>
					<td><fmt:formatNumber value="${data.buyQty}" type="number" pattern="#,###" /></td>
					<td><fmt:formatNumber value="${data.buyCost}" type="number" pattern="#,###" /></td>
					<td><fmt:formatNumber value="${data.buyCost*data.buyQty}" type="number" pattern="#,###" /></td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
	<div class="card-footer clearfix">
		<!-- startPage가 5보다 클 때에만 [이전] 활성화 -->
		${data.pagingArea}
	</div>
</div>
</div>

JAVA

BuyprodVO.java

package kr.or.ddit.vo;

import java.util.Date;

import lombok.Data;

@Data
public class BuyprodVO {
	private int rnum;
	private Date buyDate;
	private String buyProd;
	private int buyQty;
	private int buyCost;
	private String prodName;

	private int year;
	private int buyDateMonth;
	private int amt;
}

BuyprodController.java

package kr.or.ddit.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import kr.or.ddit.service.BuyprodService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.BuyprodVO;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Controller
@RequestMapping("/buyprod")
public class BuyprodController {
	@Autowired
	BuyprodService service;
	
	@GetMapping("/list")
	public String list(Model model, @RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
		if(map.get("year")==null || map.get("year")=="") map.put("year", 2020);
		if(map.get("amt")==null || map.get("amt")=="") map.put("amt",0);
		int size = 3;
		map.put("size", size);
		int total = this.service.getTotal(map);
		log.info("total "+total);
		map.put("currentPage", currentPage);
		log.info("map >>"+map);
		List<BuyprodVO> list = this.service.list(map);
		map.remove("size");
		map.remove("currentPage");
		model.addAttribute("data", new ArticlePage<BuyprodVO>(total, currentPage, size, list, map));
		return "buyprod/list";
	}
	
	@GetMapping("/detail/{year}/{buyDateMonth}")
	public String detail(Model model, @RequestParam(value="amt" ,required = false,defaultValue="0") int amt, @PathVariable("year") int year, @PathVariable("buyDateMonth") int month, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
//	@GetMapping("/detail")
//	public String detail(Model model,@RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
		int size = 10;
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("year", year);
		map.put("month", month);
		map.put("amt", amt);
//		if(map.get("year").equals("0")) {
		if((int)map.get("year")==0) {
			map.put("year", 2020);
		}
		log.info("map >>"+map);
		int total = this.service.getDetailTotal(map);
		log.info("total "+total);
		map.put("size", size);
		map.put("currentPage", currentPage);
		log.info("map >>"+map);
		List<BuyprodVO> list = this.service.detail(map);
		map.remove("size");
		map.remove("currentPage");
		model.addAttribute("data", new ArticlePage<BuyprodVO>(total, currentPage, size, list, map));
		return "buyprod/detail";
	}
}

이전 방법

package kr.or.ddit.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import kr.or.ddit.service.BuyprodService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.BuyprodVO;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Controller
@RequestMapping("/buyprod")
public class BuyprodController {
	@Autowired
	BuyprodService service;
	
	@GetMapping("/list")
	public String list(Model model, @RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
		if(map.get("year")==null || map.get("year")=="") map.put("year", 2020);
		if(map.get("amt")==null || map.get("amt")=="") map.put("amt",0);
		int size = 3;
		map.put("size", size);
		int total = this.service.getTotal(map);
		log.info("total "+total);
		map.put("currentPage", currentPage);
		log.info("map >>"+map);
		List<BuyprodVO> list = this.service.list(map);
		map.remove("size");
		map.remove("currentPage");
		model.addAttribute("data", new ArticlePage<BuyprodVO>(total, currentPage, size, list, map));
		return "buyprod/list";
	}
	
//	@GetMapping("/detail/{year}/{buyDateMonth}")
//	public String detail(Model model, @PathVariable("year") int year, @PathVariable("buyDateMonth") int month, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
	@GetMapping("/detail")
	public String detail(Model model,@RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
		int size = 10;
//		Map<String, Object> map = new HashMap<String, Object>();
//		map.put("year", year);
//		map.put("month", month);
		if(map.get("year").equals("0")) {
			map.put("year", 2020);
		} else {
			map.put("year", Integer.parseInt((String)map.get("year")));
		}
		log.info("map >>"+map);
		int total = this.service.getDetailTotal(map);
		log.info("total "+total);
		map.put("size", size);
		map.put("currentPage", currentPage);
		log.info("map >>"+map);
		List<BuyprodVO> list = this.service.detail(map);
		map.remove("size");
		map.remove("currentPage");
		model.addAttribute("data", new ArticlePage<BuyprodVO>(total, currentPage, size, list, map));
		return "buyprod/detail";
	}
}

BuyprodService.java

package kr.or.ddit.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.vo.BuyprodVO;

public interface BuyprodService {

	public List<BuyprodVO> list(Map<String, Object> map);

	public int getTotal(Map<String, Object> map);

	public int getDetailTotal(Map<String, Object> map);

	public List<BuyprodVO> detail(Map<String, Object> map);

}

BuyprodServiceImpl.java

package kr.or.ddit.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import kr.or.ddit.dao.BuyprodDao;
import kr.or.ddit.service.BuyprodService;
import kr.or.ddit.vo.BuyprodVO;

@Service
public class BuyprodServiceImpl implements BuyprodService{

	@Autowired
	BuyprodDao dao;
	
	@Override
	public List<BuyprodVO> list(Map<String, Object> map) {
		return this.dao.list(map);
	}

	@Override
	public int getTotal(Map<String, Object> map) {
		return this.dao.getTotal(map);
	}

	@Override
	public int getDetailTotal(Map<String, Object> map) {
		return this.dao.getDetailTotal(map);
	}

	@Override
	public List<BuyprodVO> detail(Map<String, Object> map) {
		return this.dao.detail(map);
	}

}

BuyprodDao.java

package kr.or.ddit.dao;

import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import kr.or.ddit.vo.BuyprodVO;

@Repository
public class BuyprodDao {
	@Autowired
	SqlSessionTemplate sst;

	public List<BuyprodVO> list(Map<String, Object> map) {
		return this.sst.selectList("buyprod.list", map);
	}

	public int getTotal(Map<String, Object> map) {
		return this.sst.selectOne("buyprod.getTotal", map);
	}

	public List<BuyprodVO> detail(Map<String, Object> map) {
		return this.sst.selectList("buyprod.detail", map);
	}

	public int getDetailTotal(Map<String, Object> map) {
		return this.sst.selectOne("buyprod.getDetailTotal", map);
	}
}

MyBatis

buyprod_SQL.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="buyprod">

	<select id="list" resultType="buyprodVo" parameterType="hashMap">
	<![CDATA[
	with t as(
		SELECT 
		    ROW_NUMBER() OVER (ORDER BY TO_NUMBER(REPLACE(B.BUY_DATE, 12, 0)) ASC) AS RNUM,
		    B.BUY_DATE AS buy_date_month,
		    NVL(A.BUY_QTY, 0) AS BUY_QTY,
		    NVL(A.AMT, 0) AS AMT
		FROM
		    (
		        SELECT 
		            EXTRACT(MONTH FROM BUY_DATE) AS BUY_DATE,
		            SUM(BUY_QTY) AS BUY_QTY,
		            SUM(BUY_QTY * BUY_COST) AS AMT
		        FROM 
		            BUYPROD
		        WHERE 
		            EXTRACT(YEAR FROM BUY_DATE) = #{year}
		        GROUP BY 
		            EXTRACT(MONTH FROM BUY_DATE)
		        HAVING 
		            SUM(BUY_QTY * BUY_COST) >= #{amt}
		    ) A 
		RIGHT OUTER JOIN
		    (
		        SELECT LEVEL AS BUY_DATE 
		        FROM DUAL
		        CONNECT BY LEVEL <= 12
		    ) B 
		ON 
		    A.BUY_DATE = B.BUY_DATE
	)
	select t.*
	from t
	WHERE  T.RNUM BETWEEN (#{currentPage}*#{size}) - (#{size}-1) AND (#{currentPage}*#{size})	
	]]>
	</select>
	
	<select id="getTotal" parameterType="hashMap" resultType="int">
		<![CDATA[
		SELECT count(*)
	FROM
	(
	    SELECT EXTRACT(MONTH FROM BUY_DATE) BUY_DATE
	         , SUM(BUY_QTY) BUY_QTY
	         , SUM(BUY_QTY * BUY_COST) AMT
	    FROM   BUYPROD
	    WHERE  EXTRACT(YEAR FROM BUY_DATE) = TO_CHAR(#{year})
	    GROUP BY EXTRACT(MONTH FROM BUY_DATE)
	    HAVING SUM(BUY_QTY * BUY_COST) >= #{amt}
	) A RIGHT OUTER JOIN
	(
	    SELECT LEVEL BUY_DATE FROM DUAL
	    CONNECT BY LEVEL <= 12
	) B
	ON(A.BUY_DATE = B.BUY_DATE)
		]]>
	</select>
	
	<select id="detail" parameterType="hashMap" resultType="buyprodVo">
	<![CDATA[
	with t as(
		select ROW_NUMBER() OVER (ORDER BY BUY_DATE ASC) AS RNUM,
			buy_date, buy_prod, prod_name, buy_qty, buy_cost
		from buyprod a, prod b
		where extract(year from buy_date) = #{year}
		and extract(month from buy_date) = #{month}
		and a.buy_prod=b.prod_id
		and buy_qty*buy_cost >= #{amt}
	)
	select t.*
	from t
	WHERE  T.RNUM BETWEEN (#{currentPage}*#{size}) - (#{size}-1) AND (#{currentPage}*#{size})	
	]]>
	</select>
	
	<select id="getDetailTotal" parameterType="hashMap" resultType="int">
		select count(*)
		from buyprod a, prod b
		where extract(year from buy_date) = #{year}
		and extract(month from buy_date) = #{month}
		and buy_qty*buy_cost >= #{amt}
		and a.buy_prod=b.prod_id
	</select>
</mapper>