<?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.yami.shop.dao.CategoryMapper">

  <resultMap id="categoryProdMap" type="com.yami.shop.bean.model.Category">
    <id column="category_id" jdbcType="BIGINT" property="categoryId" />
    <result column="category_name" jdbcType="VARCHAR" property="categoryName" />
    <collection property="products" column="prod_id" ofType="com.yami.shop.bean.model.Product">
		<!--修改-->
        <!--<result column="prod_id" jdbcType="BIGINT" property="prodId" />-->
	    <!--<result column="prod_name" jdbcType="VARCHAR" property="prodName" />-->
	    <!--<result column="price" jdbcType="DECIMAL" property="price" />-->
	    <!--<result column="brief" jdbcType="VARCHAR" property="brief" />-->
	    <!--<result column="imgs" jdbcType="VARCHAR" property="imgs" />-->
	    <!--<result column="prod_category_id" jdbcType="BIGINT" property="categoryId" />-->
	    <!--<result column="buys" jdbcType="INTEGER" property="buys" />-->
	    <!--<result column="stocks" jdbcType="INTEGER" property="stocks" />-->
	    <!--<result column="has_prop" jdbcType="INTEGER" property="hasProp" />-->
	    <!--<result column="spec_list_str" jdbcType="VARCHAR" property="specListStr" />-->

		<id property="prodId" column="prod_id"/>
		<result property="prodName" column="prod_name"/>
		<result property="shopId" column="shop_id"/>
		<result property="oriPrice" column="ori_price"/>
		<result property="price" column="price"/>
		<result property="brief" column="brief"/>
		<result property="content" column="content"/>
		<result property="imgs" column="imgs"/>
		<result property="status" column="status"/>
		<result property="categoryId" column="category_id"/>
		<result property="soldNum" column="sold_num"/>
		<result property="totalStocks" column="total_stocks"/>
		<result property="createTime" column="create_time"/>
		<result property="updateTime" column="update_time"/>

	    <!--<collection property="skuList" column="sku_id" ofType="com.yami.shop.model.model.Sku">-->
	        <!--<result column="sku_id" jdbcType="BIGINT" property="skuId" />-->
		    <!--<result column="sPrice" jdbcType="DECIMAL" property="price" />-->
		    <!--<result column="sStocks" jdbcType="INTEGER" property="stocks" />-->
		    <!--<result column="sName" jdbcType="VARCHAR" property="name" />-->
	    <!--</collection>-->
    </collection>
  </resultMap>


  <select id="listByParentId" resultType="com.yami.shop.bean.model.Category">
  	select category_id,icon,category_name,`seq`,`status`,pic from tz_category where parent_id = #{parentId} and shop_id = 0  and `status` = 1 order by seq
  </select>

	<select id="listThreeByParentId" resultType="com.yami.shop.bean.model.Category">
		SELECT * FROM tz_category WHERE  parent_id IN(SELECT category_id
		FROM tz_category
		WHERE parent_id = #{parentId}   AND `status` = 1 ORDER BY seq)
		and `status` = 1 order by seq
  </select>

	<select id="listCategory" parameterType="com.yami.shop.bean.dto.ListCategoryDTO" resultType="com.yami.shop.bean.vo.ListCategoryVO">
		SELECT
		a.category_id AS categoryID,
		a.parent_id AS parentId,
		a.category_name AS categoryName,
		a.icon AS icon,
		<if test="listCategoryDTO.parentId != 0">
			( SELECT COUNT(*) FROM tz_prod WHERE tz_prod.shop_category_id = a.category_id ) AS resource,
		</if>
		a.STATUS AS STATUS,
		a.grade as grade,
		a.shop_id AS shopId,
		b.shop_name AS shopName,
		a.seq AS seq
		FROM
		tz_category a JOIN tz_shop_detail b ON a.shop_id = b.shop_id
		WHERE a.parent_id = #{listCategoryDTO.parentId}
		<if test="listCategoryDTO.categoryName != '' and listCategoryDTO.categoryName != null">
			and a.category_name LIKE CONCAT( '%', #{listCategoryDTO.categoryName}, '%')
		</if>
		<if test="listCategoryDTO.shopId != 0">
			AND a.shop_id = #{listCategoryDTO.shopId}
		</if>
		ORDER BY a.seq DESC
	</select>
	<select id="listCategoryChildren" parameterType="com.yami.shop.bean.dto.ListCategoryDTO" resultType="com.yami.shop.bean.vo.ListCategoryVO">
		SELECT
		a.category_id AS categoryID,
		a.parent_id AS parentId,
		a.category_name AS categoryName,
		a.icon AS icon,
		<if test="listCategoryDTO.parentId != 0">
			( SELECT COUNT(*) FROM tz_prod WHERE tz_prod.shop_category_id = a.category_id ) AS resource,
		</if>
		a.STATUS AS STATUS,
		a.grade as grade,
		a.shop_id AS shopId,
		b.shop_name AS shopName,
		a.seq AS seq
		FROM
		tz_category a JOIN tz_shop_detail b ON a.shop_id = b.shop_id
		WHERE a.parent_id = #{listCategoryDTO.parentId}
		<if test="listCategoryDTO.categoryName != '' and listCategoryDTO.categoryName != null">
			and a.category_name LIKE CONCAT( '%', #{listCategoryDTO.categoryName}, '%')
		</if>
		<if test="listCategoryDTO.shopId != 0">
			AND a.shop_id = #{listCategoryDTO.shopId}
		</if>
		ORDER BY a.seq DESC
	</select>


</mapper>
