102일차 판매자 전용 페이지 구현 실습

✶ 온라인 쇼핑몰 유형의 사이트 - 판매자 전용 페이지 구현 실습

• 카테고리 부분

 

*99일차(Day99)에서 했던 부분에 카테고리 부분의 기능을 활성화시켜 병합하는 실습을 진행하였다.

• 카테고리에서 꼬리 물기로 이어지는 n단계 세부 카테고리를 원하는 형태로 뽑아내기 위한 SQL문을 작성
• 검색과 리스트 셀렉트문에 병합시켰다.

 

*데이터베이스를 학습할 때(Dayy33 ~ Day47) 했던 부분들이기 때문에 따로 정리를 하지는 않겠다.

 

< Mapper >

<?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="com.jm.myweb.product.service.ProductMapper">
  	
  	<!-- 상품 등록 메서드 -->
  	<insert id="regist" parameterType="ProductVO">
  		insert into PRODUCT(prod_endDate, prod_category, prod_writer, prod_name, prod_price,
  			prod_count, prod_discount, prod_purchase_yn, prod_content, prod_comment)
  		values(#{prod_endDate}, #{prod_category}, #{prod_writer}, #{prod_name}, #{prod_price},
  			#{prod_count}, #{prod_discount}, #{prod_purchase_yn},
  			#{prod_content}, #{prod_comment})
  	</insert>

  	<!-- 리스트 조회 메서드(특정 id에 대한 정보만) -->
  	<select id="getList" resultType="ProductVO">
  		<!-- 
  		select * from product
  		where prod_writer = #{user_id}
  		and prod_name like concat('%', #{cri.searchName}, '%')
  		and prod_content like concat('%', #{cri.searchContent}, '%')
  		order by prod_id desc limit #{cri.pageStart}, #{cri.amount}
  		 -->
		select p.*, c.category_nav
		from (
			select * from product
			where prod_writer = #{user_id}
			and prod_name like concat('%', #{cri.searchName}, '%')
			and prod_content like concat('%', #{cri.searchContent}, '%')
	  		order by prod_id desc limit #{cri.pageStart}, #{cri.amount}
		) p
		left outer join (
			SELECT
					CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY,
					CASE A1.category_parent_LV
						WHEN 0 THEN A1.category_detail_NM
						WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
						WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
					END as CATEGORY_NAV
			FROM PRODUCT_CATEGORY A1
			LEFT OUTER JOIN PRODUCT_CATEGORY A2
			ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
			LEFT OUTER JOIN PRODUCT_CATEGORY A3
			ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV	
		) c
		on p.prod_category = c.category_key
  	</select>
  	
  	<!-- 전체 게시물 수 반환 메서드 -->
  	<select id="getTotal" resultType="Integer">
  		select count(*) from product
  		where prod_writer = #{user_id}
  		and prod_name like concat('%', #{cri.searchName}, '%')
  		and prod_content like concat('%', #{cri.searchContent}, '%')
  	</select>
  	
  	<!-- 카테고리 대분류 -->
  	<select id="getCategory" resultType="CategoryVO">
  		select * from product_category
  		where category_lv = 1
  	</select>

	<!-- 카테고리 중분류 & 소분류 - group_id, parent_lv, detail_parent_lv에 매칭 -->
	<select id="getCategoryChild" resultType="CategoryVO" parameterType="CategoryVO">
		select * from product_category
		where group_id = #{group_id}
		and category_parent_lv = #{category_lv}
		and category_detail_parent_lv = #{category_detail_lv}
	</select>
	

	<!-- 
	<insert id="regist" parameterType="ProductVO">
		insert into PRODUCT(prod_enddate,
		prod_writer,
		prod_name,
		prod_price,
		prod_count,
		prod_discount,
		prod_purchase_yn,
		prod_content,
		prod_comment )
		values( #{prod_enddate},
		#{prod_writer},
		#{prod_name},
		#{prod_price},
		#{prod_count},
		#{prod_discount},
		#{prod_purchase_yn},
		#{prod_content},
		#{prod_comment} )
	</insert>

	<select id="getList" resultType="ProductVO">
		select * from PRODUCT
		where prod_writer = #{user_id}

		<if test="cri.startDate != null and cri.startDate != '' ">
        <![CDATA[ and date_format(prod_enddate, '%Y-%m-%d') >= date_format( #{cri.startDate}, '%Y-%m-%d')]]>
		</if>
		<if test="cri.endDate != null and cri.endDate != '' ">
      <![CDATA[ and date_format(prod_enddate, '%Y-%m-%d') <= date_format( #{cri.endDate}, '%Y-%m-%d')   ]]>
		</if>
		<if test="cri.searchName != null and cri.searchName != '' ">
			and prod_name like concat('%', #{cri.searchName}, '%')
		</if>
		<if test="cri.searchContent != null and cri.searchContent != '' ">
			and prod_content like concat('%', #{cri.searchContent}, '%')
		</if>
		order by
		<if test="cri.searchPrice == 'asc' ">
			prod_price asc,
		</if>
		<if test="cri.searchPrice == 'desc' ">
			prod_price desc,
		</if>
		prod_id desc
		limit #{cri.pageStart}, #{cri.amount}
	</select>

	<select id="getTotal" resultType="int">
		select count(*) as total from PRODUCT
		where prod_writer = #{user_id}

		<if test="cri.startDate != null and cri.startDate != '' ">
        <![CDATA[ and date_format(prod_enddate, '%Y-%m-%d') >= date_format( #{cri.startDate}, '%Y-%m-%d')]]>
		</if>
		<if test="cri.endDate != null and cri.endDate != '' ">
      <![CDATA[ and date_format(prod_enddate, '%Y-%m-%d') <= date_format( #{cri.endDate}, '%Y-%m-%d')   ]]>
		</if>
		<if test="cri.searchName != null and cri.searchName != '' ">
			and prod_name like concat('%', #{cri.searchName}, '%')
		</if>
		<if test="cri.searchContent != null and cri.searchContent != '' ">
			and prod_content like concat('%', #{cri.searchContent}, '%')
		</if>
	</select>
  	 -->
  	
  	
  </mapper>