<?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="lingtao.net.dao.ProductMapper">

	<select id="getProductList" parameterType="Product" resultType="Product">
		SELECT 
			* 
		FROM
		  	product 
		<where>
			<if test="proTypeLabel != null and proTypeLabel !=''">
				and pro_type_label like "%${proTypeLabel}%"
			</if>
		</where>
	</select>
	
	<update id="updatePriceById" parameterType="Product">
		UPDATE 
		  	product 
		<set>
			updater = #{updater},
			update_date = now(),
			<if test="count != null">
				count = #{count,jdbcType=INTEGER},
			</if>
			<if test="price != null">
				price = #{price,jdbcType=DOUBLE},
			</if>
			<if test="priceMultiple != null">
				price_multiple = #{priceMultiple},
			</if>
			<if test="weight != null">
				weight = #{weight},
			</if>
		</set>
		WHERE 
		  	pro_id = #{proId} 
	</update>

	<!-- 获取所有的产品种类 -->
	<select id="findAllPro" resultType="Product">
		SELECT DISTINCT 
			pro_type_label,
		  	pro_type_value 
		FROM
		  	sys_dict_product 
		WHERE pro_type_label IS NOT NULL 
	</select>
	
	<!-- 根据产品种类，获取品种 -->
	<select id="getKindsByPro" parameterType="String" resultType="Product">
		SELECT  
		    kind_value,
		    kind_label 
		  FROM
		    sys_dict_product 
		  WHERE 
		  	pro_type_value = #{proTypeValue}
		  ORDER BY kind_value ASC
	</select>
	
	<!-- 价格计算 -->
	<select id="getThanPrice" parameterType="Product" resultType="Product">
		SELECT 
		 	COUNT, price, price_multiple, discount_price discountPrice,kind_label,weight
		FROM
		  	product 
		  <where>
		  	<if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
         	<if test="kindValue != null and kindValue != ''">
               	and kind_value = #{kindValue} 
           	</if>
           	<if test="kind1Value != null and kind1Value != ''">
           		and kind1_value = #{kind1Value}
           	</if>
           	<if test="kind2Value != null and kind2Value != ''">
           		and kind2_value = #{kind2Value}
           	</if>
            <if test="length != null and length != ''">
                and length = #{length}
            </if>
            <if test="width != null and width != ''">
                and width = #{width}
            </if>
            <if test="count != null and count != ''">
                and count >= #{count}
                ORDER BY count
            </if>
		  </where>
		LIMIT 4
	</select>
	<!-- 慕斯垫价格计算 -->
	<select id="getMsdPrice" parameterType="Product" resultType="Product">
		SELECT 
		 	COUNT, price, price_multiple, discount_price discountPrice,kind_label,weight
		FROM
		  	product 
		  <where>
		  	<if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
           	<if test="(kindValue != null and kindValue != '') or (kind1Value != null and kind1Value != '') or (kind2Value != null and kind2Value != '')">
               	and (kind_value = #{kindValue} or kind1_value = #{kindValue} or  kind2_value = #{kindValue})
           	</if>
            <if test="count != null and count != ''">
                and count >= #{count}
            </if>
		  </where>
		LIMIT 4
	</select>
	
	<!--优惠券大于5位计算  1万以内（数据库中是单价）=====（单价*位数）-->
    <select id="couponThanPrice" parameterType="Product" resultType="Product">
        SELECT
	        COUNT,
	        (price * #{num})  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="count != null and count != ''">
                and count >= #{count}
            </if>
        </where>
    </select>
    
    <!--优惠券5位以上数量1万以上价格======（单价*位数） * 1万的倍数-->
    <select id="couponThousandThanPrice" parameterType="Product" resultType="Product">
        SELECT
	        #{count} count,
	        (price * #{num}) * #{count}/10000  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="count != null and count != ''">
                and count = 10000
            </if>
        </where>
        LIMIT 4
    </select>
    
    <!-- 腰封/吊旗标识，比优惠券贵10元/位   1万以内====（优惠券单价+10） * 位数-->
    <select id="hangingFlagsThanPrice" parameterType="Product" resultType="Product">
        SELECT
	        COUNT,
	        ((price+ #{p}) * #{num})  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="count != null and count != ''">
                and count >= #{count}
            </if>
        </where>
        LIMIT 4
    </select>
    <!-- 腰封/吊旗标识，比优惠券贵10元/位，数量1万以上====（（单价+10）*位数） * 1万的倍数-->
    <select id="hangingFlagsThousandThanPrice" parameterType="Product" resultType="Product">
        SELECT
	        #{count} count,
	        ((price+ #{p}) * #{num}) * #{count}/10000  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="count != null and count != ''">
                and count = 10000
            </if>
        </where>
        LIMIT 4
    </select>
    
	<!--吊旗/宣传单6-9位数以上价格计算（同 couponThanPrices）-->
    <select id="couponThanPrices" parameterType="Product" resultType="Product">
        SELECT
	        COUNT,
	        ((price + #{p}) * #{num})  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="kind2Value != null and kind2Value != ''">
                and kind2_value = #{kind2Value}
            </if>
            <if test="count != null and count != ''">
                and count >= #{count}
            </if>
        </where>
        LIMIT 4
    </select>
    
    <!--吊旗/宣传单5位数以上价格计算（同 couponThanPrice）-->
    <select id="diaoqiThanPrice" parameterType="Product" resultType="Product">
        SELECT
	        COUNT,
	        (price * #{num})  price,
	        price_multiple
        FROM
        	product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="kind2Value != null and kind2Value != ''">
                and kind2_value = #{kind2Value}
            </if>
            <if test="count != null and count != ''">
                and count >= #{count}
            </if>
        </where>
        LIMIT 4
    </select>
    
    <!--便签本联单/稿纸超过1千数量的价格-数据库是单价，不用除1000-->
    <select id="notePaperPrice" parameterType="Product" resultType="Product">
    	SELECT 
    		(1 * #{count}) count,
		  	a.price price,
		  	a.price_multiple price_multiple
		FROM
		  	(SELECT 
		    	price,
		    	price_multiple
		  	FROM
		  		product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
            <if test="kind2Value != null and kind2Value != ''">
                and kind2_value = #{kind2Value}
            </if>
            <if test="count != null and count != ''">
                and count = 1000) a 
            </if>
        </where>
    </select>
    
    <!--优惠券、吊旗、腰封;便签本彩胶纸/红头文件;扇子;超过1万数量的价格;扇子超过1万数量的重量-->
    <select id="thanThousandPrice" parameterType="Product" resultType="Product">
    	SELECT 
    		(1 * #{count}) count,
		  	(a.price * #{count}) price,
		  	price_multiple,
		  	(a.weight * #{count}) weight
		FROM
		  	(SELECT 
		    	(price / 10000) price,
		    	price_multiple,
		    	(weight / 10000) weight
		  	FROM
		  		product
        <where>
            <if test="proTypeValue != null and proTypeValue != ''">
                and pro_type_value = #{proTypeValue}
            </if>
            <if test="kindValue != null and kindValue != ''">
                and kind_value = #{kindValue}
            </if>
             <if test="kind1Value != null and kind1Value != ''">
                and kind1_value = #{kind1Value}
            </if>
            <if test="kind2Value != null and kind2Value != ''">
                and kind2_value = #{kind2Value}
            </if>
            <if test="count != null and count != ''">
                and count = 10000) a 
            </if>
        </where>
    </select>
    
    <!-- 根据关键字搜索菜单 -->
    <select id="searchPro" resultType="sysDictSearchPro">
    	SELECT 
		  	pro_type_label,url
		FROM
		 	sys_dict_search_pro
		WHERE 
			like_pro_type_label LIKE '%${likeProTypeLabel}%' 
		AND 
			status  = 1	
    </select>
    
    <!-- 价格导入 -->
    <insert id="insertSelective" parameterType="Product">
    	insert into product (length,width,count,price,kind_value,kind_label,pro_type_value,pro_type_label)
    	values (#{length},#{width},#{count},#{price},#{kindValue},#{kindLabel},#{proTypeValue},#{proTypeLabel})
    </insert>
</mapper>