<?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.iamberry.wechat.service.mapper.ProductInfoMapper">
	<sql id="product">
		PRODUCT_ID productId, PRODUCT_NAME productName, PRODUCT_ABBREVIATION productAbbreviation, PRODUCT_DISCOUNT productDiscount, 
		PRODUCT_PRICE productPrice, PRODUCT_SALESNUM productSalesnum, PRODUCT_ACCESSNUM productAccessnum, PRODUCT_STATUS productStatus, 
		PRODUCT_INTRODUCE_IMG productIntroduceImg, PRODUCT_PARAMETER_IMG productParameterImg, PRODUCT_CREATE_DATE productCreateDate, 
		PRODUCT_SORT_NUM productSortNum, PRODUCT_COLOR_DES productColorDes, PRODUCT_REMARK productRemark,PRODUCT_COLOR productColor 
	</sql>

	<resultMap id="productMap" type="Product">
	<id column="product_id" property="productId"/>
	<result column="product_name" property="productName"/>
	<result column="product_abbreviation" property="productAbbreviation"/>
	<result column="product_salesnum" property="productSalesnum"/>
	<result column="product_accessnum" property="productAccessnum"/>
	<result column="product_status" property="productStatus"/>
	<result column="product_parameter_img" property="productParameterImg"/>
	<result column="PRODUCT_CREATE_DATE" property="productCreateDate"/>
	<result column="PRODUCT_SORT_NUM" property="productSortNum"/>
	<result column="PRODUCT_COLOR_DES" property="productColorDes"/>
	<result column="PRODUCT_REMARK" property="productRemark"/>
	<result column="PRODUCT_CHOICENESS" property="productChoiceness"/>
	<result column="product_choiceness_im" property="productChoicenessIm"/>
	<result column="product_child_type" property="productChildType"/>
	<collection property="productColorList" column="product_id" ofType="colorMap" select="selectProductColor"/>
</resultMap>

	<resultMap id="colorMap" type="ProductColor">
		<id column="color_id" property="colorId"/>
		<result column="color_product_id" property="colorProductId"/>
		<result column="color_name" property="colorName"/>
		<result column="color_discount" property="colorDiscount"/>
		<result column="color_price" property="colorPrice"/>
		<result column="color_69code" property="color69Code"/>
		<result column="color_code" property="colorCode"/>
		<result column="color_product_pic" property="colorProductPic"/>
		<result column="color_status" property="colorStatus"/>
		<result column="color_desc" property="colorDesc"/>
		<result column="color_cretae_time" property="colorCretaeTime"/>
		<result column="color_update_time" property="colorUpdateTime"/>
		<collection property="salesUserGiftList" column="color_id" ofType="SalesUserGift" select="listGift"/>
	</resultMap>

	<!-- 分页查询商品信息 -->
	<select id="selectPageProduct" parameterType="PaperBean" resultMap="productMap">
		SELECT
		PRODUCT_ID,
		PRODUCT_NAME,
		PRODUCT_ABBREVIATION,
		PRODUCT_SALESNUM,
		PRODUCT_ACCESSNUM,
		PRODUCT_STATUS,
		PRODUCT_PARAMETER_IMG,
		PRODUCT_CREATE_DATE,
		PRODUCT_SORT_NUM,
		PRODUCT_COLOR_DES,
		PRODUCT_REMARK,
		PRODUCT_CHOICENESS,
		product_child_type,
		product_choiceness_im
		FROM 
			TB_IAMBERRY_PRODUCT_INFO
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR on TB_IAMBERRY_PRODUCT_INFO.product_id = TB_IAMBERRY_PRODUCT_COLOR.color_product_id
		LEFT JOIN tb_iamberry_product_child_type on tb_iamberry_product_child_type.child_type_id = TB_IAMBERRY_PRODUCT_INFO.product_child_type
		WHERE
			PRODUCT_STATUS = 1 
			<if test="str != null and str != ''">
				AND PRODUCT_NAME like #{str} 
			</if>
			<if test="otherInt != null and otherInt != ''">
				AND TB_IAMBERRY_PRODUCT_INFO.product_type = #{otherInt}
			</if>
			GROUP BY TB_IAMBERRY_PRODUCT_INFO.PRODUCT_ID
			ORDER BY product_child_type DESC
		LIMIT 
			#{minNum},#{maxNum}
	</select>

	<!-- 分页查询配件信息 -->
	<select id="selectPageProductFittings" parameterType="PaperBean" resultType="ProductFittings">
		SELECT
			t.fittings_id,
			t.product_color_id,
			t.product_type,
			t.product_child_type,
			t.fittings_color,
			t.fittings_name,
			t.fittings_discount,
			t.fittings_price,
			t.fittings_bar,
			t.fittings_pic,
			t.fittings_status,
			t.fittings_desc,
			t.fittings_cretae_time
		FROM
			tb_iamberry_product_fittings t
		WHERE
		fittings_status = 1
		<if test="otherInt != null and otherInt != ''">
			AND t.product_type = #{otherInt}
		</if>
		ORDER BY t.fittings_cretae_time DESC
		LIMIT
		#{minNum},#{maxNum}
	</select>


	<!-- 根据产品id获取所有颜色信息 -->
	<select id="selectProductColor" parameterType="Integer" resultMap="colorMap">
		SELECT
		c.*
		FROM
		tb_iamberry_product_color c
		<where>
			c.color_product_id = #{productId}
		</where>
	</select>

	<select id="listGift" resultType="SalesUserGift" parameterType="Integer">
		SELECT
		ug.user_id userId,
		ug.user_product_id userProductId,
		ug.user_color_id userColorId,
		ug.user_gift_id userGiftId,
		ug.user_gift_color userGiftColor,
		ug.user_gift_count userGiftCount,
		ug.user_status userStatus,
		ug.user_create_time userCreateTime,
		pi.product_name giftName,
		pi.product_color giftColorName,
		pi.product_child_type giftChildType,
		pi.Product_introduce_img giftProductPic
		FROM
		tb_iamberry_sales_user_gift ug
		LEFT JOIN tb_iamberry_product_color pc on pc.color_id = #{cartColorId}
		LEFT JOIN tb_iamberry_product_info pi on pi.product_id = pc.color_product_id
		WHERE
		ug.user_color_id = #{cartColorId}
	</select>
	
	<select id="selectProductCount" resultType="Integer" parameterType="Product">
		SELECT 
			COUNT(PRODUCT_ID) 
		FROM 
			TB_IAMBERRY_PRODUCT_INFO 
		WHERE 
			PRODUCT_STATUS = 1 
			<if test="productName != null and productName != ''">
				AND PRODUCT_NAME LIKE #{productName} 
			</if>
	</select>
	
	<!-- 查询首页产品 -->
	<select id="selectIndexPageProduct" resultType="Product">
		SELECT 
			PRODUCT_ID productId,
			PRODUCT_COLOR_DES productColorDes,
			color_discount productDiscount,
			color_product_pic productIntroduceImg,
			PRODUCT_NAME productName,
			color_name productColor,
			color_price productPrice
		FROM 
			TB_IAMBERRY_PRODUCT_INFO
			LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR
		on TB_IAMBERRY_PRODUCT_INFO.product_id = TB_IAMBERRY_PRODUCT_COLOR.color_product_id
		WHERE 
			PRODUCT_STATUS = 1
		LIMIT 
			0,10
	</select>
	
	<!-- 根据productId查询商品信息 -->
	<select id="getProductByProductId" parameterType="java.lang.Integer" resultMap="productMap">
		SELECT 
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_ID,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_NAME,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_ABBREVIATION,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_DISCOUNT,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_PRICE,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_SALESNUM,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_ACCESSNUM,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_STATUS,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_INTRODUCE_IMG,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_PARAMETER_IMG,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_CREATE_DATE,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_SORT_NUM,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_COLOR_DES,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_REMARK,
			TB_IAMBERRY_PRODUCT_INFO.PRODUCT_CHOICENESS,
			TB_IAMBERRY_PRODUCT_INFO.product_choiceness_im
		FROM 
			TB_IAMBERRY_PRODUCT_INFO
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR
		on TB_IAMBERRY_PRODUCT_INFO.product_id = TB_IAMBERRY_PRODUCT_COLOR.color_product_id
		WHERE 
			PRODUCT_ID = #{productId}
		  GROUP BY PRODUCT_ID
	</select>
	
	<!-- 根据商品名称查询商品信息 -->
	<select id="getProductByProductName" parameterType="java.lang.String" resultMap="productMap">
		SELECT
			PRODUCT_ID,
			PRODUCT_NAME,
			PRODUCT_ABBREVIATION,
			PRODUCT_DISCOUNT,
			PRODUCT_PRICE,
			PRODUCT_SALESNUM,
			PRODUCT_ACCESSNUM,
			PRODUCT_STATUS,
			PRODUCT_INTRODUCE_IMG,
			PRODUCT_PARAMETER_IMG,
			PRODUCT_CREATE_DATE,
			PRODUCT_SORT_NUM,
			PRODUCT_COLOR_DES,
			PRODUCT_REMARK,
			PRODUCT_CHOICENESS,
			product_choiceness_im
		FROM 
			TB_IAMBERRY_PRODUCT_INFO
			LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR
			on TB_IAMBERRY_PRODUCT_INFO.product_id = TB_IAMBERRY_PRODUCT_COLOR.color_product_id
		<where>
			<if test="_parameter != null">
				PRODUCT_NAME like #{productName} 
			</if>
		</where>
		ORDER BY PRODUCT_ID DESC
	</select>
	
	<update id="updateProductNumList" parameterType="java.util.List">
		<foreach collection="list" item="item" index="index" separator=";" open="" close="">
		UPDATE 
			TB_IAMBERRY_PRODUCT_INFO 
		SET 
			PRODUCT_SALESNUM = (PRODUCT_SALESNUM + #{item.itemNum,jdbcType=INTEGER}) 
		WHERE 
			PRODUCT_ID = #{item.itemProductId,jdbcType=INTEGER}
			</foreach>
	</update>

	<!-- 获取产品颜色信息 -->
	<select id="getProductColor" parameterType="ProductColor" resultType="ProductColor">
		SELECT
		  c.*,
		  ct.type_id colorProductType,
		  ct.child_type_id productChildType,
		  p.product_name colorProductName
		FROM
		  tb_iamberry_product_color c
		LEFT JOIN tb_iamberry_product_info p ON c.color_product_id = p.product_id
		LEFT JOIN tb_iamberry_product_child_type ct ON p.product_child_type = ct.child_type_id
		<where>
			<if test="color69Code != null and color69Code != ''">
				color_69code = #{color69Code}
			</if>
			<if test="colorId != null and colorId != ''">
				color_id = #{colorId}
			</if>
		</where>
	</select>

	<!-- 获取配件信息 -->
	<select id="getProductFittings" parameterType="ProductFittings" resultType="ProductFittings">
		SELECT
			t.fittings_id,
			t.product_color_id,
			t.product_type,
			t.product_child_type,
			t.fittings_color,
			t.fittings_name,
			t.fittings_discount,
			t.fittings_price,
			t.fittings_bar,
			t.fittings_pic,
			t.fittings_status,
			t.fittings_cretae_time,
			ct.child_type_id,
			ct.child_type_name
		FROM
			tb_iamberry_product_fittings t
		LEFT JOIN tb_iamberry_product_child_type ct ON ct.child_type_id = t.product_child_type
		<where>
			<if test="fittingsBar != null and fittingsBar != ''">
				fittings_bar = #{fittingsBar}
			</if>
			<if test="fittingsId != null and fittingsId != ''">
				fittings_id = #{fittingsId}
			</if>
		</where>
	</select>

	<!-- 查询所有产品类型-->
	<select id="selectProductType" resultMap="productTypeMap">
		select * from tb_iamberry_product_type where type_state = 1
	</select>
	<resultMap id="productTypeMap" type="ProductType">
		<id column="type_id" property="typeId"/>
		<result column="type_name" property="typeName"/>
		<result column="type_detilas" property="typeDetilas"/>
		<result column="type_create_date" property="typeCreateDate"/>
		<result column="type_update_date" property="typeUpdateDate"/>
		<result column="type_state" property="typeState"/>
		<collection property="childTypeList" column="type_id" ofType="ProductChildType" select="listChildType"/>
	</resultMap>
	<select id="listChildType" parameterType="Integer" resultType="ProductChildType">
		select * from tb_iamberry_product_child_type where type_id = #{typeId} and child_type_state = 1
	</select>
	<!--查询精选产品-->
	<select id="selectIndexChoicenessProduct" resultMap="productMap" parameterType="Integer">
			SELECT
			PRODUCT_ID,
			PRODUCT_NAME,
			PRODUCT_ABBREVIATION,
			PRODUCT_SALESNUM,
			PRODUCT_ACCESSNUM,
			PRODUCT_STATUS,
			PRODUCT_PARAMETER_IMG,
			PRODUCT_CREATE_DATE,
			PRODUCT_SORT_NUM,
			PRODUCT_COLOR_DES,
			PRODUCT_REMARK,
			PRODUCT_CHOICENESS,
			PRODUCT_CHOICENESS_IM
			FROM
			TB_IAMBERRY_PRODUCT_INFO

			LEFT JOIN tb_iamberry_product_child_type on tb_iamberry_product_child_type.child_type_id = TB_IAMBERRY_PRODUCT_INFO.product_child_type
			WHERE
			PRODUCT_STATUS = 1
			AND tb_iamberry_product_info.product_choiceness  = #{productChoiceness}
			ORDER BY PRODUCT_CREATE_DATE DESC
		</select>




	<!--根据配件类型id查询所有子类型-->
	<select id="selectProductFittingsType" resultType="ProductChildType" parameterType="Integer">
		select * from tb_iamberry_product_child_type
 		where type_id = #{typeId}
	</select>
	<!--新增搜索记录-->
	<insert id="insertSearchInfo" parameterType="SearchInfo">
		insert into tb_iamberry_search_info (user_open_id,search_details,search_num,search_create_date,search_update_date,search_type_state)
		VALUES (userOpenId,searchDetails,1,NOW(),NOW(),1)
	</insert>

	<update id="updateSearchInfo" parameterType="SearchInfo">
		UPDATE tb_iamberry_search_info
		SET search_num = search_num + 1,search_update_date = NOW()
		WHERE
		user_open_id = #{userOpenId} and search_details = #{searchDetails}
	</update>
	<!--查询是否已存在这条搜索记录-->
	<select id="getSearchInfo" resultType="SearchInfo" parameterType="SearchInfo">
		SELECT * from tb_iamberry_search_info
		<where>
			<if test="userOpenId != null and userOpenId != ''">
				userOpenId = #{userOpenId}
			</if>
			<if test="searchDetails != null and searchDetails != ''">
				search_details = #{searchDetails}
			</if>
			<if test="searchTypeState != null and searchTypeState != ''">
				search_type_state = #{searchTypeState}
			</if>
		</where>
	</select>
	<!--查询前五条搜索记录-->
	<select id="selectSearchInfo" resultType="SearchInfo" parameterType="SearchInfo">
		SELECT TOP 5 * from tb_iamberry_search_info
		<where>
			<if test="userOpenId != null and userOpenId != ''">
				userOpenId = #{userOpenId}
			</if>
			<if test="searchDetails != null and searchDetails != ''">
				search_details like '%#{searchDetails}%'
			</if>
			<if test="searchTypeState != null and searchTypeState != ''">
				search_type_state = #{searchTypeState}
			</if>
		</where>
		ORDER BY
		search_update_date DESC
		LIMIT 5
	</select>

	<select id="selectColorByPid" parameterType="Integer" resultType="ProductColor">
		select * from tb_iamberry_product_color where color_product_id = #{productId}
	</select>
</mapper>