[Spring] buyprod - 모달창으로 상세 보기

pom.xml

ajax로 json을 처리하기 위해 먼저 pom.xml에 설치가 필요하다
(설치하지 않을 시 json처리를 받는 것이 없어 415 에러가 발생하게 된다)

		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.13.3</version>
		</dependency>

JSP

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"%>
<script type="text/javascript" src="/resources/js/jquery.min.js"></script>
<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 class="click" data-toggle="modal" data-target="#modalProd" data-buy-prod="${data.buyProd}" data-buy-date="<fmt:formatDate value='${data.buyDate}' pattern='yyyy-MM-dd'/>">
					<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>
<div class="modal fade" id="modalProd">
  <div class="modal-dialog modal-lg">
    <div class="modal-content">
      <div class="modal-header">
        <h4 class="modal-title">상세 보기</h4>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
      <div class="card">
              <div class="card-body p-0">
                <table class="table table-striped">
                  <thead>
                    <tr>
						<th>매입 날짜</th>
						<th>매입 번호</th>
						<th>상품명</th>
						<th>재고 수량</th>
						<th>가격</th>
						<th>총합</th>
                    </tr>
                  </thead>
                  <tbody>
					<tr>
						<td id="mdate"></td>
						<td id="mbp"></td>
						<td id="mname"></td>
						<td id="mqty"></td>
						<td id="mcost"></td>
						<td id="mamt"></td>
					</tr>
                  </tbody>
                </table>
              </div>
              <!-- /.card-body -->
            </div>
            <!-- /.card -->
      </div>
      <div class="modal-footer justify-content-between">
        <button type="button" class="btn btn-default" data-dismiss="modal">취소</button>
        <button type="button" class="btn btn-primary">확인</button>
      </div>
    </div>
    <!-- /.modal-content -->
  </div>
  <!-- /.modal-dialog -->
</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 !== '');
    let yearu = parts[2]
    if(parts[2]==0){
    	yearu = 2020
    } 
    document.querySelector('#year').value = yearu;
   	document.querySelector('#month').value = parts[3];
};

$(function(){
$('.click').on('click', function(){
	let keyd = $(this).data("buyDate");
	let keyb = $(this).data("buyProd");
	const obj = {"buyProd" : keyb, "buyDate" : keyd};
	console.log(obj);
	$.ajax({
		url : "/buyprod/detailAjax",
		type : "POST",
		data: JSON.stringify(obj),
		dataType : "json",
        contentType : 'application/json; charset=utf-8',
		success : function(res){
			console.table(res);
			$('#mdate').html(res.buyDateStr);
			$('#mbp').html(res.buyProd);
			$('#mname').html(res.prodName);
			$('#mqty').html(res.buyQty);
			$('#mcost').html(res.buyCost);
			$('#mamt').html(res.buyQty * res.buyCost);
		}
	});
});
})
</script>

JAVA

BuyprodController.java

package kr.or.ddit.controller;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
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.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

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";
	}
	
	/*
	   요청URI : /buyprod/detailAjax
	   요청파라미터 : JSON{buyDate:2020-02-11,buyProd:P101000001}
	   요청방식 : post
	   
	   client(JSON String) ->  ->    텍스트       -> -> (RequestBody)server
	   client(JSON Object) <-  <-    텍스트       <- ResponseBody <- (JSON String)server
	   */
	@ResponseBody
	@PostMapping("/detailAjax")
	public BuyprodVO detailAjax(@RequestBody BuyprodVO vo) {
		vo = this.service.detailAjax(vo);
		log.info("vo "+vo);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String buyDateStr = sdf.format(vo.getBuyDate());
		vo.setBuyDateStr(buyDateStr);
		log.info(buyDateStr);
		return vo;
	}
}

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);

	public BuyprodVO detailAjax(BuyprodVO vo);

}

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);
	}

	@Override
	public BuyprodVO detailAjax(BuyprodVO vo) {
		return this.dao.detailAjax(vo) ;
	}

}

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);
	}

	public BuyprodVO detailAjax(BuyprodVO vo) {
		return this.sst.selectOne("buyprod.detailAjax", vo);
	}
}

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>
	
	<select id="detailAjax" parameterType="buyprodVo" resultType="buyprodVo">
		select *
		from buyprod a, prod b
		where a.buy_prod = #{buyProd}
		and to_char(a.buy_date, 'yyyy-mm-dd') = to_char(#{buyDate},'yyyy-mm-dd')
		and a.buy_prod=b.prod_id
	</select>
</mapper>