<?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.ruoyi.mall.mapper.MallGoodsProductMapper">

    <resultMap type="MallGoodsProduct" id="MallGoodsProductResult">
        <result property="id"    column="id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="isDefault"    column="is_default"    />
        <result property="productName"    column="product_name"    />
        <result property="price"    column="price"    />
        <result property="priceN"    column="price_n"    />
        <result property="priceM"    column="price_m"    />
        <result property="purchasePrice"    column="purchase_price"    />
        <result property="stock"    column="stock"    />
        <result property="upAt"    column="up_at"    />
        <result property="downAt"    column="down_at"    />
        <result property="numSale"    column="num_sale"    />
        <result property="status"    column="status"    />
        <result property="createAt"    column="create_at"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="num"    column="num"    />
        <result property="priceVip"    column="price_vip"    />
        <result property="copyId"    column="copy_id"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.GoodsProductVO" id="GoodsProductVOResult">
        <result property="id"    column="id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="isDefault"    column="is_default"    />
        <result property="productName"    column="product_name"    />
        <result property="price"    column="price"    />
        <result property="priceN"    column="price_n"    />
        <result property="stock"    column="stock"    />
        <result property="numSale"    column="num_sale"    />
        <result property="isDefault"    column="is_default"    />
        <result property="num"    column="num"    />
        <result property="priceVip"    column="price_vip"    />
    </resultMap>

    <sql id="selectMallGoodsProductVo">
        select id, goods_id, is_default, product_name, price, price_n, price_m, purchase_price, stock, up_at, down_at, num_sale, status, create_at, del_flag, num, price_vip, copy_id from mall_goods_product
    </sql>

    <select id="selectMallGoodsProductList" parameterType="MallGoodsProduct" resultMap="MallGoodsProductResult">
        <include refid="selectMallGoodsProductVo"/>
        <where>
            <if test="productName != null  and productName != ''"> and product_name like concat('%', #{productName}, '%')</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
        </where>
    </select>

    <select id="selectMallGoodsProductById" parameterType="Long" resultMap="MallGoodsProductResult">
        <include refid="selectMallGoodsProductVo"/>
        where id = #{id}
    </select>
    <select id="selectMallGoodProByParams" parameterType="MallGoodsProduct" resultMap="GoodsProductVOResult">
        select id, is_default, goods_id, product_name, price, price_n, stock, num_sale, num, price_vip
        from mall_goods_product
        <where>
            <if test="productName != null  and productName != ''"> and product_name = #{productName}</if>
            <if test="goodsId != null  and goodsId != ''"> and goods_id = #{goodsId}</if>
        </where>
        order by num asc
    </select>

    <select id="selectMallGoodsProductByGoodsId" parameterType="Long" resultMap="MallGoodsProductResult">
        <include refid="selectMallGoodsProductVo"/>
        where goods_id = #{id}
        and del_flag = '0'
    </select>
    <select id="selectMallGoodsProductByGoodsIdVO" parameterType="Long" resultMap="GoodsProductVOResult">
        select id,is_default, goods_id, product_name, price, price_n, stock, num_sale, num, price_vip
        from mall_goods_product
        where goods_id = #{aLong}
        and del_flag = '0'
        order by num asc
    </select>


    <insert id="insertMallGoodsProduct" parameterType="MallGoodsProduct" useGeneratedKeys="true" keyProperty="id">
        insert into mall_goods_product
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="goodsId != null">goods_id,</if>
            <if test="isDefault != null">is_default,</if>
            <if test="productName != null">product_name,</if>
            <if test="price != null">price,</if>
            <if test="priceN != null">price_n,</if>
            <if test="priceM != null">price_m,</if>
            <if test="purchasePrice != null">purchase_price,</if>
            <if test="stock != null">stock,</if>
            <if test="upAt != null">up_at,</if>
            <if test="downAt != null">down_at,</if>
            <if test="numSale != null">num_sale,</if>
            <if test="status != null">status,</if>
            <if test="createAt != null">create_at,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="num != null">num,</if>
            <if test="priceVip != null">price_vip,</if>
            <if test="copyId != null">copy_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="goodsId != null">#{goodsId},</if>
            <if test="isDefault != null">#{isDefault},</if>
            <if test="productName != null">#{productName},</if>
            <if test="price != null">#{price},</if>
            <if test="priceN != null">#{priceN},</if>
            <if test="priceM != null">#{priceM},</if>
            <if test="purchasePrice != null">#{purchasePrice},</if>
            <if test="stock != null">#{stock},</if>
            <if test="upAt != null">#{upAt},</if>
            <if test="downAt != null">#{downAt},</if>
            <if test="numSale != null">#{numSale},</if>
            <if test="status != null">#{status},</if>
            <if test="createAt != null">#{createAt},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="num != null">#{num},</if>
            <if test="priceVip != null">#{priceVip},</if>
            <if test="copyId != null">#{copyId},</if>
         </trim>
    </insert>

    <update id="updateMallGoodsProduct" parameterType="MallGoodsProduct">
        update mall_goods_product
        <trim prefix="SET" suffixOverrides=",">
            <if test="goodsId != null">goods_id = #{goodsId},</if>
            <if test="isDefault != null">is_default = #{isDefault},</if>
            <if test="productName != null">product_name = #{productName},</if>
            <if test="price != null">price = #{price},</if>
            <if test="priceN != null">price_n = #{priceN},</if>
            <if test="priceM != null">price_m = #{priceM},</if>
            <if test="purchasePrice != null">purchase_price = #{purchasePrice},</if>
            <if test="stock != null">stock = #{stock},</if>
            <if test="upAt != null">up_at = #{upAt},</if>
            <if test="downAt != null">down_at = #{downAt},</if>
            <if test="numSale != null">num_sale = #{numSale},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createAt != null">create_at = #{createAt},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="num != null">num = #{num},</if>
            <if test="priceVip != null">price_vip = #{priceVip},</if>
            <if test="copyId != null">copy_id = #{copyId},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="removeByGoodsId" parameterType="Long">
        update mall_goods_product
        set del_flag = '1'
        where goods_id = #{goodsId}
    </update>
    <update id="updateMallGoodsProductByParams">
        update mall_goods_product
        set stock = stock + #{total}, num_sale = num_sale - #{total}
        where id = #{productId}
    </update>

    <update id="updateMallGoodsProductByParamsRefund">
        update mall_goods_product
        set stock = stock - #{total}, num_sale = num_sale + #{total}
        where id = #{productId}
    </update>

    <delete id="deleteMallGoodsProductById" parameterType="Long">
        delete from mall_goods_product where id = #{id}
    </delete>

    <delete id="deleteMallGoodsProductByIds" parameterType="String">
        delete from mall_goods_product where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <insert id="bathInsertListMallGoodsProduct">
        insert into mall_goods_product(goods_id, is_default, product_name, price, price_vip, purchase_price, num, status, copy_id, create_at, del_flag) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.goodsId},'0',#{item.productName},#{item.price},#{item.priceVip},#{item.purchasePrice},#{item.num}, 0, #{item.copyId}, sysdate(), '0')
        </foreach>
    </insert>

    <update id="bathUpdateListMallGoodsProduct">
        <foreach collection="list" item="item" index="index" separator=";">
            update mall_goods_product
            <set>
                product_name = #{item.productName},
                price = #{item.price},
                price_vip = #{item.priceVip},
                num = #{item.num},
            </set>
            where id = #{item.id}
        </foreach>
    </update>

    <delete id="bathDeleteListMallGoodsProduct">
        <foreach collection="list" item="item" index="index" separator=";">
            delete from mall_goods_product
            where id = #{item.id}
        </foreach>
    </delete>

    <update id="setNotDefault">
        update mall_goods_product
        <set>
            is_default = '0',
        </set>
        where goods_id = #{goodsId}
    </update>
    <update id="setDefault">
        update mall_goods_product
        <set>
            is_default = '1',
        </set>
        where goods_id = #{goodsId}
        order by price asc
        limit 1
    </update>

    <insert id="bathCopyInsertListMallGoodsProduct">
        insert into mall_goods_product(goods_id, is_default, product_name, price, price_vip, num, status, copy_id, create_at, del_flag) values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.goodsId},#{item.isDefault},#{item.productName},#{item.price} + 4,#{item.priceVip},#{item.num}, 0, #{item.copyId}, sysdate(), '0')
        </foreach>
    </insert>

    <delete id="bathCopyDeleteListMallGoodsProduct">
        <foreach collection="list" item="item" index="index" separator=";">
            delete from mall_goods_product
            where goods_id = #{item.id}
        </foreach>
    </delete>

    <select id="selectMallGoodsProductListCopyIdIsNull" resultMap="MallGoodsProductResult">
        SELECT
            mgpc.id,

            (SELECT mgp.id FROM mall_goods_goods mgg
                                    LEFT JOIN mall_goods_product mgp ON mgg.id = mgp.goods_id
             WHERE mgg.id = mggc.copy_id
               AND mgp.product_name = mgpc.product_name
                limit 1
            ) copy_id,

            (SELECT mgp.price + 4 FROM mall_goods_goods mgg
            LEFT JOIN mall_goods_product mgp ON mgg.id = mgp.goods_id
            WHERE mgg.id = mggc.copy_id
            AND mgp.product_name = mgpc.product_name
            limit 1
            ) price,

						(SELECT mgp.num FROM mall_goods_goods mgg
            LEFT JOIN mall_goods_product mgp ON mgg.id = mgp.goods_id
            WHERE mgg.id = mggc.copy_id
            AND mgp.product_name = mgpc.product_name
            limit 1
            ) num,

						(SELECT mgp.price_vip FROM mall_goods_goods mgg
            LEFT JOIN mall_goods_product mgp ON mgg.id = mgp.goods_id
            WHERE mgg.id = mggc.copy_id
            AND mgp.product_name = mgpc.product_name
            limit 1
            ) price_vip

        FROM mall_goods_product mgpc
            LEFT JOIN mall_goods_goods mggc on mgpc.goods_id = mggc.id
        WHERE mggc.is_copy = 1
          AND (mgpc.copy_id = 0 or mgpc.copy_id is null)
          AND mggc.del_flag ='0'
          AND (SELECT mgp.id FROM mall_goods_goods mgg
            LEFT JOIN mall_goods_product mgp ON mgg.id = mgp.goods_id
            WHERE mgg.id = mggc.copy_id
          AND mgp.product_name = mgpc.product_name
            limit 1
            ) is not null
        ORDER BY mgpc.goods_id DESC, mgpc.id ASC
    </select>

    <update id="bathCopyUpdateListMallGoodsProduct">
        <foreach collection="list" item="item" separator=";">
            update mall_goods_product
            <trim prefix="SET" suffixOverrides=",">
                <if test="item.price != null and item.price != ''">price = #{item.price},</if>
                <if test="item.copyId != null and item.copyId != ''">copy_id = #{item.copyId},</if>
            </trim>
            where id = #{item.id}
        </foreach>
    </update>
</mapper>
