<?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_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_TYPE "productType.typeId", PRODUCT_RENT_TYPE productRentType 
	</sql>

	<sql id="userGift">
		USER_ID userId,
		USER_PRODUCT_ID userProductId,
		USER_COLOR_ID userColorId, 
		USER_GIFT_ID userGiftId,
		USER_GIFT_COLOR userGiftColor,
		USER_LEVEL userLevel,
		USER_DISCOUNT userDiscount,
		USER_GIFT_COUNT userGiftCount,
		USER_STATUS userStatus,
		USER_CREATE_TIME userCreateTime 
	</sql>

	<sql id="color">
		COLOR_ID colorId,
		COLOR_PRODUCT_ID colorProductId,
		COLOR_NAME colorName,
		COLOR_DISCOUNT colorDiscount,
		COLOR_PRICE colorPrice,
		COLOR_69CODE color69Code,
		COLOR_IMG color_img,
		COLOR_STATUS colorStatus,
		COLOR_PRESENT colorPresent
	</sql>
	<!-- 产品类型 -->
	<sql id="allTypeField">
		TYPE_ID typeId,
		TYPE_NAME typeName,
		TYPE_DETILAS typeDetilas,
		TYPE_CREATE_DATE typeCreateDate,
		TYPE_UPDATE_DATE typeUpdateDate,
		TYPE_STATE typeState
	</sql>
	<!--产品简介-->
	<sql id="imageText">
		imagetext_id imagetextId,
		imagetext_produect_id imagetextProduectId,
		imagetext_context imagetextContext,
		imagetext_create_date imagetextCreateDate
	</sql>
	<!--根据产品类型id查询产品信息-->
	<select id="listProductColorByTypeId" resultType="ProductColor" parameterType="ProductColor">
		SELECT
		COLOR_ID colorId,
		COLOR_PRODUCT_ID colorProductId,
		COLOR_NAME colorName,
		COLOR_DISCOUNT colorDiscount,
		COLOR_PRICE colorPrice,
		COLOR_IMG colorImg,
		COLOR_STATUS colorStatus,
		COLOR_69CODE color69Code,
		COLOR_CRETAE_TIME colorCretaeTime,
		COLOR_UPDATE_TIME colorUpdateTime,
		PRODUCT_NAME productName,
		COLOR_PRESENT colorPresent
		FROM tb_iamberry_product_info PI
		LEFT JOIN tb_iamberry_product_color PC ON PC.color_product_id=PI.PRODUCT_ID

		<where>
			PC.COLOR_STATUS=1 AND  PC.color_is_channel = 2
			<if test="productType != null ">
				AND PI.PRODUCT_TYPE=#{productType}
			</if>
			<if test="colorProductId != null ">
				AND COLOR_PRODUCT_ID = #{colorProductId}
			</if>
		</where>
		ORDER BY PI.product_sort_num,PC.color_id
		<if test="page!=null and page.recordBegin>0 and page.pageSize>0 ">
			limit ${page.recordBegin},${page.pageSize}
		</if>
		<if test="page!=null and page.recordBegin==0 and page.pageSize>0 ">
			limit ${page.pageSize}
		</if>
	</select>

    <!--根据产品颜色id查询产品信息-->
    <select id="selectProductByColorId"  resultType="Product">
        SELECT
        PRODUCT_ID productId,
        PRODUCT_NAME productName,
        PRODUCT_ABBREVIATION productAbbreviation,
		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_TYPE productTypeId,
		PRODUCT_RENT_TYPE productRentType,
        PC.COLOR_NAME,
		PC.COLOR_PRESENT,
		PC.color_img,
		PC.color_all_num,
		PC.color_sold_num,
		PC.color_discount,
		PC.color_price
        FROM tb_iamberry_product_info PI
        LEFT JOIN tb_iamberry_product_color PC ON PC.color_product_id=PI.PRODUCT_ID
        WHERE PC.color_id=#{colorId} AND color_status=1
    </select>
	<!--查询正在使用的产品类型-->
	<select id="listProductType" resultType="ProductType" >
		SELECT
		<include refid="allTypeField"></include>
		FROM TB_IAMBERRY_PRODUCT_TYPE
		WHERE TYPE_STATE=1
	</select>

	<!-- 分页查询商品信息 -->
	<select id="selectPageProduct" parameterType="Product" resultType="Product">
		SELECT
		<include refid="product" />,
		COLOR_DISCOUNT productDiscount,
		COLOR_PRICE productPrice,
		COLOR_NAME productColor,
		COLOR_ID colorId
		FROM
		TB_IAMBERRY_PRODUCT_INFO
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR ON PRODUCT_ID = COLOR_PRODUCT_ID
		WHERE
		PRODUCT_STATUS = 1
		<if test="productName != null and productName != ''">
			AND PRODUCT_NAME like CONCAT('%',#{productName},'%')
		</if>
		<if test="productType.typeId != null and productType.typeId != 2">
			AND PRODUCT_TYPE = #{productType.typeId}
		</if>
		<if test="productType.typeId != null and productType.typeId == 2">
			AND PRODUCT_TYPE in (2,6,7,8,9,10)
		</if>
		<if test="productRentType != null and productRentType != ''">
			AND PRODUCT_RENT_TYPE = #{productRentType}
		</if>
		ORDER BY PRODUCT_CREATE_DATE DESC
		<if test="page!=null and page.recordBegin>0 and page.pageSize>0 ">
			LIMIT ${page.recordBegin},${page.pageSize}
		</if>
	</select>

	<!-- 根据条件查询产品集合 -->
	<select id="listSelectProduct" parameterType="Product" resultType="Product">
		SELECT
		<include refid="product" />,
		COLOR_DISCOUNT productDiscount,
		COLOR_PRICE productPrice,
		COLOR_NAME productColor,
		COLOR_ID colorId
		FROM
		TB_IAMBERRY_PRODUCT_INFO
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR ON PRODUCT_ID = COLOR_PRODUCT_ID
		<where>
			<if test="productStatus != null and productStatus != ''">
				PRODUCT_STATUS = #{productStatus}
			</if>
			<if test="productRentType != null and productRentType != ''">
				AND PRODUCT_RENT_TYPE = #{productRentType}
			</if>
		</where>
	</select>

	<select id="selectColorList" parameterType="Integer" resultType="ProductColor">
		SELECT
		<include refid="color" />
		FROM
		TB_IAMBERRY_PRODUCT_COLOR
		WHERE
		COLOR_PRODUCT_ID = #{colorProductId}
		and color_status = 1
		ORDER BY COLOR_DISCOUNT ASC
	</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 CONCAT('%',#{productName},'%')
		</if>
		<if test="productType.typeId != null and productType.typeId != 2">
			AND PRODUCT_TYPE = #{productType.typeId}
		</if>
		<if test="productType.typeId != null and productType.typeId == 2">
			AND PRODUCT_TYPE in (2,6,7,8,9,10)
		</if>
		<if test="productRentType != null and productRentType != ''">
			AND PRODUCT_RENT_TYPE = #{productRentType}
		</if>
	</select>

	<!-- 查询首页产品 -->
	<select id="selectIndexPageProduct" resultType="Product">
		SELECT 
			PRODUCT_ID productId, 
			PRODUCT_COLOR_DES productColorDes,
			PRODUCT_INTRODUCE_IMG productIntroduceImg,
			PRODUCT_NAME productName, 
			PRODUCT_TYPE "productType.typeId", 
			COLOR_DISCOUNT productDiscount,
			COLOR_PRICE productPrice, 
			COLOR_NAME productColor, 
			COLOR_ID colorId  
		FROM 
			TB_IAMBERRY_PRODUCT_INFO 
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR ON PRODUCT_ID = COLOR_PRODUCT_ID 
		WHERE 
			PRODUCT_STATUS = 1 
		AND 
			PRODUCT_RENT_TYPE = 1 
		LIMIT 
			0,10
	</select>

	<!-- 根据productId查询商品信息 -->
	<select id="getProductByProductId" parameterType="java.lang.Integer" resultType="Product">
		SELECT 
			PRODUCT_ID productId, 
			PRODUCT_NAME productName, 
			PRODUCT_INTRODUCE_IMG productIntroduceImg, 
			PRODUCT_PARAMETER_IMG productParameterImg, 
			PRODUCT_SALESNUM productSalesnum, 
			COLOR_69CODE productRemark, 
			PRODUCT_TYPE "productType.typeId", 
			PRODUCT_RENT_TYPE productRentType,
			PRODUCT_STATUS productStatus,
			COLOR_DISCOUNT productDiscount,
			COLOR_PRICE productPrice, 
			COLOR_NAME productColor, 
			COLOR_ID colorId,
			COLOR_PRESENT colorPresent
		FROM 
			TB_IAMBERRY_PRODUCT_INFO 
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR ON PRODUCT_ID = COLOR_PRODUCT_ID 
		WHERE 
			PRODUCT_ID = #{0} 
		AND 
			COLOR_ID = #{1} 
	</select>
	<!--根据产品id查询产品简介-->
	<select id="selectProductImagetext" parameterType="Integer" resultType="ProductImageText">
		SELECT
		<include refid="imageText"></include>
		FROM tb_iamberry_product_imagetext
		WHERE imagetext_produect_id=#{productId}
	</select>
	<!-- 根据商品名称查询商品信息 -->
	<select id="getProductByProductName" parameterType="java.lang.String" resultType="Product">
		SELECT
		<include refid="product"/>
		FROM
		TB_IAMBERRY_PRODUCT_INFO
		<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>

	<!-- 根据商品id查询礼品信息 -->
	<select id="selectUserGift" parameterType="UserGiftDto" resultType="UserGiftDto">
		SELECT
		<include refid="userGift"/>
		FROM
		TB_IAMBERRY_SALES_USER_GIFT
		WHERE
		USER_STATUS = 1
		<if test="userProductId != null and userProductId != ''">
			AND USER_PRODUCT_ID = #{userProductId}
		</if>
		<if test="userColorId != null and userColorId != ''">
			AND USER_COLOR_ID = #{userColorId}
		</if>
		<if test="userLevel != null and userLevel != ''">
			AND USER_LEVEL = #{userLevel}
		</if>
	</select>

	<!-- 查询租赁产品信息 -->
	<select id="getLeaseProduct" parameterType="java.lang.Integer" resultType="Product">
		SELECT
		<include refid="product" />
		FROM
		TB_IAMBERRY_PRODUCT_INFO
		WHERE
		PRODUCT_ID = #{productId}
		and
		PRODUCT_RENT_TYPE = 2
	</select>

	<!-- 获取优惠二维码赠送礼品信息 -->
	<select id="listQrcodeGift" parameterType="QrcodeGift" resultType="QrcodeGift">
		SELECT
		*
		FROM
		TB_IAMBERRY_QRCODE_GIFT
		<where>
			<if test="qrcodeStatus != null and qrcodeStatus != ''">
				QRCODE_STATUS = #{qrcodeStatus}
			</if>
			<if test="qrcodeId != null and qrcodeId != ''">
				AND QRCODE_ID = #{qrcodeId}
			</if>
			<if test="qrcodeColorId != null and qrcodeColorId != ''">
				AND QRCODE_COLOR_ID = #{qrcodeColorId}
			</if>
		</where>
		ORDER BY QRCODE_CREATE_TIME DESC
		<if test="page!=null and page.pageSize>0 ">
			LIMIT ${page.recordBegin},${page.pageSize}
		</if>
	</select>

	<!-- 获取优惠二维码赠送礼品信息数量 -->
	<select id="listQrcodeGiftCount" parameterType="QrcodeGift" resultType="Integer">
		SELECT
		count(QRCODE_ID)
		FROM
		TB_IAMBERRY_QRCODE_GIFT
		<where>
			<if test="qrcodeStatus != null and qrcodeStatus != ''">
				QRCODE_STATUS = #{qrcodeStatus}
			</if>
			<if test="qrcodeId != null and qrcodeId != ''">
				AND QRCODE_ID = #{qrcodeId}
			</if>
			<if test="qrcodeColorId != null and qrcodeColorId != ''">
				AND QRCODE_COLOR_ID = #{qrcodeColorId}
			</if>
		</where>
		ORDER BY QRCODE_CREATE_TIME DESC
	</select>

	<!-- 添加优惠二维码赠送礼品信息 -->
	<insert id="addQrcodeGift" parameterType="QrcodeGift" useGeneratedKeys="true" keyProperty="qrcodeId">
        insert into tb_iamberry_qrcode_gift
        (
            qrcode_color_id,qrcode_name,qrcode_color_name,qrcode_pre_gift_color,
            qrcode_sales_gift_color,qrcode_pre_discount,qrcode_sales_discount,
            qrcode_status,qrcode_desc,qrcode_create_time
        )
        VALUES
        (
            #{qrcodeColorId},#{qrcodeName},#{qrcodeColorName},#{qrcodePreGiftColor},
            #{qrcodeSalesGiftColor},#{qrcodePreDiscount},#{qrcodeSalesDiscount},
            #{qrcodeStatus},#{qrcodeDesc},NOW()
        )
    </insert>

	<!-- 修改优惠二维码赠品表信息 -->
	<update id="updateQrcodeGift" parameterType="QrcodeGift">
		update
		tb_iamberry_qrcode_gift
		<set>
			qrcode_color_id = #{qrcodeColorId},
			qrcode_name = #{qrcodeName},
			qrcode_color_name = #{qrcodeColorName},
			qrcode_pre_gift_color = #{qrcodePreGiftColor},
			qrcode_sales_gift_color = #{qrcodeSalesGiftColor},
			qrcode_pre_discount = #{qrcodePreDiscount},
			qrcode_sales_discount = #{qrcodeSalesDiscount},
			<if test="qrcodeStatus != null and qrcodeStatus != ''">
				qrcode_status = #{qrcodeStatus},
			</if>
			qrcode_desc = #{qrcodeDesc}
		</set>
		<where>
			qrcode_id = #{qrcodeId}
		</where>
	</update>

	<!-- 修改优惠二维码赠品表状态 -->
	<update id="updateGiftStatus" parameterType="QrcodeGift">
		update
		tb_iamberry_qrcode_gift
		<set>
			<if test="qrcodeStatus != null and qrcodeStatus != ''">
				qrcode_status = #{qrcodeStatus}
			</if>
		</set>
		<where>
			qrcode_id = #{qrcodeId}
		</where>
	</update>

	<!-- 查询是否是商户且是否存在二维码 -->
	<select id="getQrCodeByPlaceOpenId" parameterType="String" resultType="Integer">
        SELECT
          count(qrcode_id)
        from
          tb_iamberry_user_qrcode
        where
          qrcode_openid = (
            select
              place_id
            from
              tb_iamberry_place_info
            where
              place_openid = #{openId})
    </select>

	<select id="getProductColor" parameterType="ProductColor" resultType="ProductColor">
		SELECT
			color_id AS colorId,
			color_product_id AS colorProductId,
			color_name AS colorName,
			color_discount AS  colorDiscount,
			color_price AS  colorPrice,
			color_cretae_time AS  colorCretaeTime,
			color_update_time AS colorUpdateTime,
			color_69code AS  color69code
		FROM
			tb_iamberry_product_color
		<where>
			<if test="colorId != null and colorId != ''">
				color_id = #{colorId}
			</if>
			<if test="colorProductId != null and colorProductId != ''">
				AND color_product_id = #{colorProductId}
			</if>
		</where>
	</select>


	<!-- 分页查询商品信息 -->
	<select id="getProductPage" parameterType="Product" resultType="Product">
		SELECT
			<include refid="product"></include>
		FROM
			TB_IAMBERRY_PRODUCT_INFO
		WHERE
			PRODUCT_STATUS = 1
		AND  product_is_channel = 2
		AND  product_type = #{productType.typeId}
		ORDER BY product_sort_num
	</select>

</mapper>