<?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.pcloud.book.giftcoupon.dao.impl.GiftCouponPackageDaoImpl">
    <resultMap type="com.pcloud.book.giftcoupon.entity.GiftCouponPackage" id="GiftCouplePackageMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="coverPic" column="cover_pic" jdbcType="VARCHAR"/>
        <result property="title" column="title" jdbcType="VARCHAR"/>
        <result property="validDateBegin" column="valid_date_begin" jdbcType="TIMESTAMP"/>
        <result property="validDateEnd" column="valid_date_end" jdbcType="TIMESTAMP"/>
        <result property="denomination" column="denomination" jdbcType="INTEGER"/>
        <result property="stock" column="stock" jdbcType="INTEGER"/>
        <result property="receiveLimit" column="receive_limit" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="couponType" column="coupon_type" jdbcType="INTEGER"/>
        <result property="instructions" column="instructions" jdbcType="VARCHAR"/>
        <result property="useType" column="use_type" jdbcType="INTEGER"/>
        <result property="exchangeAddress" column="exchange_address" jdbcType="VARCHAR"/>
        <result property="urlNumber" column="url_number" jdbcType="VARCHAR" />
        <result property="agentId" column="agent_id" jdbcType="BIGINT" />
    </resultMap>

    <sql id="Base_Column_List">
        id, cover_pic, title, valid_date_begin, valid_date_end, denomination, stock, receive_limit, create_time, update_time, is_delete,
        coupon_type,instructions,use_type,exchange_address, url_number, agent_id
    </sql>

    <!--查询单个-->
    <select id="getById" resultMap="GiftCouplePackageMap">
        select
         <include refid="Base_Column_List"/>
        from book.gift_coupon_package
        where id = #{id}
        and is_delete=0
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="GiftCouplePackageMap">
        select
          id, cover_pic, title, valid_date_begin, valid_date_end, denomination, stock, receive_limit, create_time, update_time, is_delete,
           coupon_type,instructions,use_type,exchange_address,url_number
        from book.gift_coupon_package
        limit #{offset}, #{limit}
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="GiftCouplePackageMap">
        select
          id, cover_pic, title, valid_date_begin, valid_date_end, denomination, stock, receive_limit, create_time, update_time, is_delete,
        coupon_type,instructions,use_type,exchange_address, url_number
        from book.gift_coupon_package
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="coverPic != null and coverPic != ''">
                and cover_pic = #{coverPic}
            </if>
            <if test="title != null and title != ''">
                and title = #{title}
            </if>
            <if test="validDateBegin != null">
                and valid_date_begin = #{validDateBegin}
            </if>
            <if test="validDateEnd != null">
                and valid_date_end = #{validDateEnd}
            </if>
            <if test="denomination != null">
                and denomination = #{denomination}
            </if>
            <if test="stock != null">
                and stock = #{stock}
            </if>
            <if test="receiveLimit != null">
                and receive_limit = #{receiveLimit}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
            <if test="updateTime != null">
                and update_time = #{updateTime}
            </if>
            <if test="isDelete != null">
                and is_delete = #{isDelete}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" parameterType="com.pcloud.book.giftcoupon.entity.GiftCouponPackage" keyProperty="id" useGeneratedKeys="true">
        insert into book.gift_coupon_package(cover_pic, title, valid_date_begin, valid_date_end, denomination, stock, receive_limit, create_time, update_time, is_delete,
           coupon_type,instructions,use_type,exchange_address,url_number, agent_id)
        values (#{coverPic}, #{title}, #{validDateBegin}, #{validDateEnd}, #{denomination}, #{stock}, #{receiveLimit}, now(), now(), 0,
        #{couponType},#{instructions},#{useType},#{exchangeAddress}, #{urlNumber}, #{agentId})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update book.gift_coupon_package
        <set>
            <if test="coverPic != null and coverPic != ''">
                cover_pic = #{coverPic},
            </if>
            <if test="title != null and title != ''">
                title = #{title},
            </if>
            <if test="validDateBegin != null">
                valid_date_begin = #{validDateBegin},
            </if>
            <if test="validDateEnd != null">
                valid_date_end = #{validDateEnd},
            </if>
            <if test="denomination != null">
                denomination = #{denomination},
            </if>
            <if test="stock != null">
                stock = #{stock},
            </if>
            <if test="receiveLimit != null">
                receive_limit = #{receiveLimit},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
            <if test="couponType != null">
                coupon_type = #{couponType},
            </if>
            <if test="instructions != null">
                instructions = #{instructions},
            </if>
            <if test="useType != null">
                use_type = #{useType},
            </if>
            <if test="exchangeAddress != null">
                exchange_address = #{exchangeAddress},
            </if>
            <if test="urlNumber != null">
                url_number = #{urlNumber},
            </if>
                update_time = now()
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        update book.gift_coupon_package set is_delete=1 where id = #{id}
    </delete>

    <select id="list4GiftPackage" parameterType="map" resultType="com.pcloud.book.giftcoupon.dto.GiftPackageDTO">
        select
        a.id id,
        a.title title,
        a.cover_pic coverPic,
        a.denomination denomination,
        a.receive_limit receiveLimit,
        a.stock,
        a.valid_date_begin validDateBegin,
        a.valid_date_end validDateEnd,
        if(NOW()<![CDATA[ < ]]> a.valid_date_begin,1,if(NOW() between a.valid_date_begin and a.valid_date_end,2,3)) state,
        a.coupon_type couponType,
        a.instructions instructions,
        a.use_type useType,
        a.exchange_address exchangeAddress,
        a.url_number urlNumber
        from
        gift_coupon_package a
        where a.is_delete=0
        and a.stock>0
        <if test="title !=null and title !=''">
            and a.title like concat('%',#{title},'%')
        </if>
        <if test="state !=null and state==1">
            and now() <![CDATA[ < ]]> a.valid_date_begin
        </if>
        <if test="state !=null and state==2">
            and now() between a.valid_date_begin and a.valid_date_end
        </if>
        <if test="state !=null and state==3">
            and now() > a.valid_date_end
        </if>
        <if test="state !=null and state==4">
            and now() <![CDATA[ <= ]]> a.valid_date_end
        </if>
        <if test="couponType !=null">
            and a.coupon_type=#{couponType}
        </if>
        and a.receive_limit>0
        <choose>
            <when test="agentId != null">
                and a.agent_id = #{agentId}
            </when>
            <otherwise>
                and a.agent_id = 0
            </otherwise>
        </choose>
        GROUP BY a.id
        order by a.update_time desc

    </select>

    <select id="getMapByIds" parameterType="list" resultMap="GiftCouplePackageMap">
        select <include refid="Base_Column_List"/>
        from gift_coupon_package
        where is_delete=0
        and id in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
          ${item}
        </foreach>
    </select>

    <select id="reduceStock" parameterType="map">
        update gift_coupon_package set stock=stock-1
        where id=#{id}
    </select>

    <select id="reduceStockBatch" parameterType="map">
        update gift_coupon_package set stock=stock-1
        where id in
        <foreach collection="packageIds" item="item" index="index" open="(" separator="," close=")">
            ${item}
        </foreach>
    </select>

    <select id="getPackageIdsBy" parameterType="map" resultType="Long">
        select distinct id
        from gift_coupon_package
        where is_delete=0
        and id in
        <foreach collection="packageIds" item="item" index="index" open="(" separator="," close=")">
            ${item}
        </foreach>
    </select>

    <select id="getNoStockCount" parameterType="map" resultType="Integer">
        select ifnull(count(1),0)
        from gift_coupon_package
        where is_delete=0
        and id in
        <foreach collection="packageIds" item="item" index="index" open="(" separator="," close=")">
            ${item}
        </foreach>
        and stock<![CDATA[ <= ]]>0
    </select>

    <select id="getUrlNumberByAddress" parameterType="map" resultType="string">
        SELECT
            url_number
        FROM
            gift_coupon_package
        WHERE
            exchange_address = #{exchangeAddress}
        AND coupon_type = 1
        AND use_type = 1
        AND url_number IS NOT NULL
        <choose>
            <when test="agentId != null">
                and agent_id = #{agentId}
            </when>
            <otherwise>
                and agent_id = 0
            </otherwise>
        </choose>
        LIMIT 1
    </select>

    <select id="urlNumberExist" parameterType="map" resultType="boolean">
        SELECT EXISTS (
        SELECT
        id
        FROM
        gift_coupon_package
        WHERE
        url_number = #{urlNumber}
        AND exchange_address != #{exchangeAddress}
        AND coupon_type = 1
        AND use_type = 1
        <if test="id>0">
            AND id != #{id}
        </if>
        <choose>
            <when test="agentId != null">
                and agent_id = #{agentId}
            </when>
            <otherwise>
                and agent_id = 0
            </otherwise>
        </choose>
        )
    </select>


    <select id="listGiftPackage4Analysis" parameterType="map" resultType="com.pcloud.book.giftcoupon.dto.GiftPackageDTO">
        select
        a.id id,
        a.title title,
        a.cover_pic coverPic,
        a.denomination denomination,
        a.receive_limit receiveLimit,
        a.stock,
        a.valid_date_begin validDateBegin,
        a.valid_date_end validDateEnd,
        a.coupon_type couponType,
        a.instructions instructions,
        a.use_type useType,
        a.exchange_address exchangeAddress,
        a.url_number urlNumber
        from
        gift_coupon_package a
        where a.is_delete=0
        and a.stock>0
        <if test="title !=null and title !=''">
            and  (a.title like concat('%',#{title},'%') or a.url_number like concat('%',#{title},'%'))
        </if>
        <if test="state !=null and state==1">
            and now() <![CDATA[ < ]]> a.valid_date_begin
        </if>
        <if test="state !=null and state==2">
            and now() between a.valid_date_begin and a.valid_date_end
        </if>
        <if test="state !=null and state==3">
            and now() > a.valid_date_end
        </if>
        <if test="state !=null and state==4">
            and now() <![CDATA[ <= ]]> a.valid_date_end
        </if>
        <if test="couponType !=null">
            and a.coupon_type=#{couponType}
        </if>
        <if test="linkOnly == 1">
            and a.coupon_type = 1
        </if>
        and a.receive_limit>0
        <choose>
            <when test="agentId != null">
                and a.agent_id = #{agentId}
            </when>
            <otherwise>
                and a.agent_id = 0
            </otherwise>
        </choose>
        order by a.update_time desc
    </select>


</mapper>