<?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.frequency.mallgoods.mapper.MallGoodsMapper">

    <resultMap type="com.ruoyi.frequency.mallgoods.entity.MallGoods" id="MallGoodsResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="name"    column="name"    />
        <result property="coverImage"    column="cover_image"    />
        <result property="goodsTypeId"    column="goods_type_id"    />
        <result property="describes"    column="describes"    />
        <result property="rotationImages"    column="rotation_images"    />
        <result property="sales"    column="sales"    />
        <result property="specType"    column="spec_type"    />
        <result property="star"    column="star"    />
        <result property="price"    column="price"    />
        <result property="detail"    column="detail"    />
        <result property="goodsUrl"    column="goods_url"    />
        <result property="urlType"    column="url_type"    />
        <result property="limitNum"    column="limit_num"    />
        <result property="typeName"    column="typeName"    />
    </resultMap>



    <sql id="selectMallGoodsVo">
        select
            t.id ,
            t.create_by ,
            t.create_time ,
            t.update_by ,
            t.update_time ,
            t.del_flag ,
            t.name ,
            t.cover_image ,
            t.goods_type_id ,
            t.describes ,
            t.rotation_images ,
            t.sales ,
            t.spec_type ,
            t.star ,
            t.price ,
            t.detail ,
            t.goods_url ,
            t.url_type,
            t.limit_num,
            gt.name AS typeName
        from mall_goods AS t
                 LEFT JOIN mall_goods_type AS gt ON t.goods_type_id = gt.id

    </sql>

    <select id="selectMallGoodsList" parameterType="com.ruoyi.frequency.mallgoods.entity.MallGoods" resultMap="MallGoodsResult">
        <include refid="selectMallGoodsVo"/>
        <where>AND t.del_flag != 2
            <if test="name != null  and name != ''">and t.name like concat('%', #{name}, '%')</if>
            <if test="coverImage != null  and coverImage != ''">and t.cover_image = #{coverImage}</if>
            <if test="goodsTypeId != null ">and t.goods_type_id = #{goodsTypeId}</if>
            <if test="describes != null  and describes != ''">and t.describes = #{describes}</if>
            <if test="rotationImages != null  and rotationImages != ''">and t.rotation_images = #{rotationImages}</if>
            <if test="sales != null ">and t.sales = #{sales}</if>
            <if test="specType != null ">and t.spec_type = #{specType}</if>
            <if test="star != null ">and t.star = #{star}</if>
            <if test="price != null  and price != ''">and t.price = #{price}</if>
            <if test="detail != null  and detail != ''">and t.detail = #{detail}</if>
            <if test="goodsUrl != null  and goodsUrl != ''">and t.goods_url = #{goodsUrl}</if>
            <if test="urlType != null ">and t.url_type = #{urlType}</if>
            <if test="delFlag != null and delFlag != ''">and t.del_flag = #{delFlag}</if>
            <if test="beginTime != null ">
                and t.create_time >= #{beginTime}
            </if>
            <if test="endTime != null ">
                and t.create_time &lt; #{endTime}
            </if>
        </where>
        order by t.create_time desc
    </select>

    <select id="selectMallGoodsById" parameterType="Long" resultMap="MallGoodsResult">
        <include refid="selectMallGoodsVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertMallGoods" parameterType="com.ruoyi.frequency.mallgoods.entity.MallGoods">
        insert into mall_goods
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="name != null">name,</if>
            <if test="coverImage != null">cover_image,</if>
            <if test="goodsTypeId != null">goods_type_id,</if>
            <if test="describes != null">describes,</if>
            <if test="rotationImages != null">rotation_images,</if>
            <if test="sales != null">sales,</if>
            <if test="speType != null">spec_type,</if>
            <if test="star != null">star,</if>
            <if test="price != null">price,</if>
            <if test="detail != null">detail,</if>
            <if test="goodsUrl != null">goods_url,</if>
            <if test="urlType != null">url_type,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="name != null">#{name},</if>
            <if test="coverImage != null">#{coverImage},</if>
            <if test="goodsTypeId != null">#{goodsTypeId},</if>
            <if test="describes != null">#{describes},</if>
            <if test="rotationImages != null">#{rotationImages},</if>
            <if test="sales != null">#{sales},</if>
            <if test="speType != null">#{speType},</if>
            <if test="star != null">#{star},</if>
            <if test="price != null">#{price},</if>
            <if test="detail != null">#{detail},</if>
            <if test="goodsUrl != null">#{goodsUrl},</if>
            <if test="urlType != null">#{urlType},</if>
        </trim>
    </insert>

    <update id="updateMallGoods" parameterType="com.ruoyi.frequency.mallgoods.entity.MallGoods">
        update mall_goods
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="name != null">name = #{name},</if>
            <if test="coverImage != null">cover_image = #{coverImage},</if>
            <if test="goodsTypeId != null">goods_type_id = #{goodsTypeId},</if>
            <if test="describes != null">describes = #{describes},</if>
            <if test="rotationImages != null">rotation_images = #{rotationImages},</if>
            <if test="sales != null">sales = #{sales},</if>
            <if test="specType != null">spec_type = #{specType},</if>
            <if test="star != null">star = #{star},</if>
            <if test="price != null">price = #{price},</if>
            <if test="detail != null">detail = #{detail},</if>
            <if test="goodsUrl != null">goods_url = #{goodsUrl},</if>
            <if test="urlType != null">url_type = #{urlType},</if>
        </trim>
        where id = #{id}
    </update>

    <select id="goodsPage" resultType="com.ruoyi.controller.resp.GoodsPageResp">
        SELECT
        t.id,
        t.`name`,
        t.`describes` `describe`,
        t.cover_image,
        t.price,
        t.goods_url,
        t.url_type,
        t.goods_type_id,
        t.sales,
        t2.`name` goodsTypeName
        FROM
        mall_goods AS t
        LEFT JOIN mall_goods_type t2 ON t2.id = t.goods_type_id
        WHERE
        t.del_flag = 1
        <if test="req.goodsTypeId != null and req.goodsTypeId != ''">
            and t.goods_type_id = #{req.goodsTypeId}
        </if>
        <if test="req.keyword != null and req.keyword != ''">
            and t.`name` like concat('%',#{req.keyword},'%')
        </if>
        <choose>
            <when test="req.sortType != null and req.sortType != ''">
                <if test="req.sortType == 1">
                    ORDER BY t.create_time DESC, t.id
                </if>
                <if test="req.sortType == 2">
                    <choose>
                        <when test="req.isAsc != null and req.isAsc != ''">
                            <if test="req.isAsc == 1">
                                ORDER BY t.sales , t.id
                            </if>
                            <if test="req.isAsc == 2">
                                ORDER BY t.sales DESC, t.id
                            </if>
                        </when>
                        <otherwise>
                            ORDER BY t.sales DESC, t.id
                        </otherwise>
                    </choose>
                </if>
                <if test="req.sortType == 3">
                    <choose>
                        <when test="req.isAsc != null and req.isAsc != ''">
                            <if test="req.isAsc == 1">
                                ORDER BY (
                                REPLACE (t.price, '积分', '') + 0
                                ), t.id
                            </if>
                            <if test="req.isAsc == 2">
                                ORDER BY (
                                REPLACE (t.price, '积分', '') + 0
                                ) DESC, t.id
                            </if>
                        </when>
                        <otherwise>
                            ORDER BY (
                            REPLACE (t.price, '积分', '') + 0
                            ) DESC, t.id
                        </otherwise>
                    </choose>
                </if>
                <if test="req.sortType == 4">
                    <choose>
                        <when test="req.isAsc != null and req.isAsc != ''">
                            <if test="req.isAsc == 1">
                                ORDER BY t.listing_time, t.id
                            </if>
                            <if test="req.isAsc == 2">
                                ORDER BY t.listing_time DESC, t.id
                            </if>
                        </when>
                        <otherwise>
                            ORDER BY t.listing_time DESC, t.id
                        </otherwise>
                    </choose>
                </if>
            </when>
            <otherwise>
                ORDER BY t.create_time DESC, t.id
            </otherwise>
        </choose>
    </select>

    <select id="goodsDetail" resultType="com.ruoyi.controller.resp.GoodsDetailResp">
        SELECT
            t.id,
            t.`name`,
            t.`describes` `describe`,
            t.rotation_images,
            t.cover_image,
            t.price,
            t.goods_url,
            t.url_type,
            t.goods_type_id,
            t.detail,
            t2.`name` goodsTypeName
        FROM
            mall_goods AS t
                LEFT JOIN mall_goods_type t2 ON t2.id = t.goods_type_id
        WHERE
            t.id = #{goodsId}
    </select>


    <update id="subSales" parameterType="java.util.List">
        <foreach item="item" collection="list"  separator=";" >
            update mall_goods
            <trim prefix="SET" suffixOverrides=",">
                sales = sales - #{item.sales}
            </trim>
            where id = #{item.id}
            AND sales >= #{item.sales}
        </foreach>
    </update>

    <update id="addSales" parameterType="java.util.List">
        <foreach item="item" collection="list"  separator=";" >
            update mall_goods
            <trim prefix="SET" suffixOverrides=",">
                sales = sales + #{item.sales}
            </trim>
            where id = #{item.id}
        </foreach>
    </update>
</mapper>
