[Spring] buyprod 상세보기

수정 삭제 구현 X

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"
						style="cursor: pointer;"
						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="card card-primary">
			<div class="card-header">
				<h3 class="card-title">상세 보기</h3>
			</div>
			<!-- /.card-header -->
			<!-- form start -->
			<form>
				<div class="card-body">
					<div class="form-group">
						<label for="exampleInputEmail1">입고 일자</label> <input name="buyDate" readonly="readonly" type="date" class="form-control" id="mdate">
					</div>
					<div class="form-group">
						<label for="exampleInputEmail1">상품코드</label> <input name="buyProd" readonly="readonly" type="text" class="form-control" id="mbp">
					</div>
					<div class="form-group">
						<label for="exampleInputEmail1">상품명</label> <input name="prodName" type="text" class="form-control" id="mname">
					</div>
					<div class="form-group">
						<label for="exampleInputEmail1">재고</label> <input name="buyQty" type="number" class="form-control" id="mqty">
					</div>
					<div class="form-group">
						<label for="exampleInputEmail1">가격</label> <input name="buyCost" type="number" class="form-control" id="mcost">
					</div>
				</div>
				<!-- /.card-body -->

				<div class="card-footer">
				<div class="row"  style="justify-content: space-between;">
					<button type="button" id="del" class="btn btn-danger">삭제</button>
					<button type="submit" id="edit" class="btn btn-primary">변경</button>
				</div>
				</div>
			</form>
		</div>
		<!-- /.card -->
	</div>
	<!-- /.card-body -->
</div>
<!-- /.card -->
<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(){
		// data-buy-date, data-buy-prod
		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').val(res.buyDateStr);
				$('#mbp').val(res.buyProd);
				$('#mname').val(res.prodName);
				$('#mqty').val(res.buyQty);
				$('#mcost').val(res.buyCost);
				$('#mamt').val(res.buyQty * res.buyCost);
			}
		});
	});
	
	$('#del').on('click',function(){
		location.href = "/buyprod/delete";
	});
	
	$('#edit').on('click', function(){
		location.href = "/buyprod/update";
	});
})
</script>

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 buyDateStr;
	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.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;
	}
	
	@PostMapping("/update")
	public void list(Model model, @RequestParam BuyprodVO 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>