<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.pcloud.book.group.dao.impl.BookGroupDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.BookGroup">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="group_qrcode_url" property="groupQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="group_qrcode_name" property="groupQrcodeName" jdbcType="VARCHAR"/>
        <result column="pro_label_id" property="proLabelId" jdbcType="BIGINT"/>
        <result column="dep_label_id" property="depLabelId" jdbcType="BIGINT"/>
        <result column="pur_label_id" property="purLabelId" jdbcType="BIGINT"/>
        <result column="join_title" property="joinTitle" jdbcType="VARCHAR"/>
        <result column="join_slogan" property="joinSlogan" jdbcType="VARCHAR"/>
        <result column="personal_qrcode_url" property="personalQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="scene_id" property="sceneId" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="BIT"/>
        <result column="is_show_book_name" property="isShowBookName" jdbcType="BOOLEAN"/>
        <result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
    </resultMap>

    <resultMap id="BookGroupDTO" type="com.pcloud.book.group.dto.BookGroupDTO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="group_qrcode_url" property="groupQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="group_qrcode_name" property="groupQrcodeName" jdbcType="VARCHAR"/>
        <result column="pro_label_id" property="proLabelId" jdbcType="BIGINT"/>
        <result column="dep_label_id" property="depLabelId" jdbcType="BIGINT"/>
        <result column="pur_label_id" property="purLabelId" jdbcType="BIGINT"/>
        <result column="join_title" property="joinTitle" jdbcType="VARCHAR"/>
        <result column="join_slogan" property="joinSlogan" jdbcType="VARCHAR"/>
        <result column="personal_qrcode_url" property="personalQrcodeUrl" jdbcType="VARCHAR"/>
        <result column="product_id" property="productId" jdbcType="BIGINT"/>
        <result column="scene_id" property="sceneId" jdbcType="BIGINT"/>
        <result column="create_user" property="createUser" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
        <result column="is_delete" property="isDelete" jdbcType="BIT"/>
        <result column="book_img" property="bookImg" jdbcType="VARCHAR"/>
        <result column="isbn" property="isbn" jdbcType="VARCHAR"/>
        <result column="BOOK_NAME" property="bookName" jdbcType="VARCHAR"/>
        <result column="book_clock_info_id" property="bookClockInfoId" jdbcType="BIGINT"/>
        <result column="is_show_book_name" property="isShowBookName" jdbcType="BOOLEAN"/>
        <result column="join_group_type" property="joinGroupType" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, book_id, channel_id, scene_id, group_qrcode_url, group_qrcode_name, pro_label_id, dep_label_id,
        pur_label_id, join_title, join_slogan, personal_qrcode_url, product_id, create_user,
        create_time,
        update_time, is_delete, is_show_book_name,join_group_type
    </sql>

    <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id = #{id,jdbcType=BIGINT}
    </select>

    <select id="getDTOById" resultMap="BookGroupDTO" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id = #{id,jdbcType=BIGINT}
    </select>

    <select id="getDTOByIds" resultMap="BookGroupDTO" parameterType="list">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="getDTOByBookId" resultMap="BookGroupDTO" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where is_delete = 0
        and book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        and create_user = #{adviserId,jdbcType=BIGINT}
        ORDER BY create_time ASC limit 1
    </select>

    <select id="getBookGroupCount" resultType="Integer" parameterType="Long">
        SELECT
            COUNT(DISTINCT G.id)
        FROM
            book_group G
        LEFT JOIN BOOK_ADVISER A ON G.BOOK_ID = A.BOOK_ID
        AND G.CHANNEL_ID = A.CHANNEL_ID
        AND G.CREATE_USER = A.ADVISER_ID
        LEFT JOIN BOOK B ON A.BOOK_ID = B.BOOK_ID
        WHERE
            G.create_user = #{adviserId, jdbcType=BIGINT}
        AND G.is_delete = 0
        AND (
            A.BOOK_ADVISER_ID IS NULL
            OR A.IS_DELETE = 0
        )
        AND (
            B.BOOK_ID IS NULL
            OR B.IS_DELETE = 0
        )
    </select>

    <delete id="deleteById" parameterType="java.lang.Long">
        update book_group set is_delete = 1
        where id = #{id,jdbcType=BIGINT} and is_delete = 0
    </delete>

    <update id="deleteByBookId" parameterType="map">
        update book_group set is_delete = 1
        where book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        <if test="adviserId != null">
            and create_user = #{adviserId,jdbcType=BIGINT}
        </if>
        and is_delete = 0
    </update>
    
    <update id="recoverByBookId" parameterType="map">
        update book_group set is_delete = 0
        where book_id = #{bookId,jdbcType=BIGINT}
        and channel_id = #{channelId,jdbcType=BIGINT}
        <if test="adviserId != null">
            and create_user = #{adviserId,jdbcType=BIGINT}
        </if>
        and is_delete = 1
    </update>
    
    <update id="linkBookGroup" parameterType="com.pcloud.book.group.entity.BookGroup">
        update book_group set book_id = #{bookId,jdbcType=BIGINT}
        where id = #{id,jdbcType=BIGINT} and is_delete = 0
    </update>

    <insert id="insert" parameterType="com.pcloud.book.group.entity.BookGroup" useGeneratedKeys="true"
            keyProperty="id">
        insert into book_group
        <trim prefix="(" suffix=")" suffixOverrides=",">
            book_id,
            channel_id,
            scene_id,
            group_qrcode_url,
            group_qrcode_name,
            pro_label_id,
            dep_label_id,
            pur_label_id,
            join_title,
            join_slogan,
            personal_qrcode_url,
            product_id,
            create_user,
            create_time,
            update_time,
            is_show_book_name,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{bookId,jdbcType=BIGINT},
            #{channelId,jdbcType=BIGINT},
            #{sceneId,jdbcType=BIGINT},
            #{groupQrcodeUrl,jdbcType=VARCHAR},
            #{groupQrcodeName,jdbcType=VARCHAR},
            #{proLabelId,jdbcType=BIGINT},
            #{depLabelId,jdbcType=BIGINT},
            #{purLabelId,jdbcType=BIGINT},
            #{joinTitle,jdbcType=VARCHAR},
            #{joinSlogan,jdbcType=VARCHAR},
            #{personalQrcodeUrl,jdbcType=VARCHAR},
            #{productId,jdbcType=BIGINT},
            #{createUser,jdbcType=BIGINT},
            NOW(),
            NOW(),
            #{isShowBookName,jdbcType=BOOLEAN},
        </trim>
    </insert>

    <update id="update" parameterType="com.pcloud.book.group.entity.BookGroup">
        update book_group
        <set>
            <if test="bookId != null">
                book_id = #{bookId,jdbcType=BIGINT},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId,jdbcType=BIGINT},
            </if>
            <if test="groupQrcodeUrl != null">
                group_qrcode_url = #{groupQrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="groupQrcodeName != null">
                group_qrcode_name = #{groupQrcodeName,jdbcType=VARCHAR},
            </if>
            <if test="proLabelId != null">
                pro_label_id = #{proLabelId,jdbcType=BIGINT},
            </if>
            <if test="depLabelId != null">
                dep_label_id = #{depLabelId,jdbcType=BIGINT},
            </if>
            <if test="purLabelId != null">
                pur_label_id = #{purLabelId,jdbcType=BIGINT},
            </if>
            <if test="joinTitle != null">
                join_title = #{joinTitle,jdbcType=VARCHAR},
            </if>
            <if test="joinSlogan != null">
                join_slogan = #{joinSlogan,jdbcType=VARCHAR},
            </if>
            <if test="personalQrcodeUrl != null">
                personal_qrcode_url = #{personalQrcodeUrl,jdbcType=VARCHAR},
            </if>
            <if test="productId != null">
                product_id = #{productId,jdbcType=BIGINT},
            </if>
            <if test="sceneId != null and sceneId == 0">
                scene_id = null,
            </if>
            <if test="sceneId != null and sceneId != 0">
                scene_id = #{sceneId},
            </if>
            <if test="joinGroupType != null">
                join_group_type = #{joinGroupType},
            </if>
            update_time = NOW(),
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="getQrcodeNameAndProId" resultType="QrcodeNameAndProIdDTO" parameterType="java.lang.Long">
        select
        group_qrcode_name groupQrcodeName,
        join_group_type joinGroupType,
        product_id productId
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>

    <select id="getRankType" resultType="Integer" parameterType="java.lang.Long">
        select
        rank_type
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>

    <update id="updateRankType" parameterType="map">
        update book_group
        <set>
            rank_type = #{rankType},
            update_time = NOW()
        </set>
        where id = #{bookGroupId,jdbcType=BIGINT}
    </update>

    <select id="getSpareQr" resultType="String" parameterType="java.lang.Long">
        select
        personal_qrcode_url
        from book_group
        where id = #{_parameter,jdbcType=BIGINT}
    </select>


    <!--获取社群码基本信息(包括书籍信息)-->
    <select id="getBookBaseInfoByIds" parameterType="list" resultMap="BookGroupDTO">
        SELECT
        bg.id,
        bg.book_id,
        b.book_name,
        bg.group_qrcode_name,
        b.COVER_IMG book_img,
        b.ISBN,
        b.BOOK_NAME
        FROM
        book_group bg
        LEFT JOIN book b ON bg.book_id = b.BOOK_ID
        where id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <select id="getBaseInfoBySceneId" resultMap="BookGroupDTO" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book_group
        where scene_id = #{_parameter}
    </select>

    <select id="isSpecialAgent" resultType="Boolean" parameterType="Long">
        select
        count(1)
        from book_special_agent
        where agent_id = #{_parameter}
    </select>

    <update id="updatePersonQrcode" parameterType="map">
        update book_group set personal_qrcode_url = #{newQrcodeUrl} where personal_qrcode_url = #{oldQrcodeUrl}
    </update>

    <!--获取社群码基本信息(包括书籍信息,及BookClockInfoId)-->
    <select id="getBookInfoByIdsWithBookClockInfoId" parameterType="map" resultMap="BookGroupDTO">
        SELECT
        bg.id,
        bg.book_id,
        bg.group_qrcode_name,
        b.COVER_IMG book_img,
        b.ISBN,
        b.BOOK_NAME,
        #{bookClockInfoId} book_clock_info_id
        FROM
        book_group bg
        LEFT JOIN book b ON bg.book_id = b.BOOK_ID
        where id in
        <foreach collection="bookGroupIdList" open="(" close=")" item="item" separator=",">
            ${item}
        </foreach>
    </select>

    <!--更新是否显示书名-->
    <update id="updateIsShowBookName" parameterType="map">
        update book_group set
        is_show_book_name=#{isShowBookName}
        where create_user=#{partyId}
    </update>

    <!--获取是否显示书名-->
    <select id="getIsShowBookName" parameterType="Long" resultType="Boolean">
        select IFNULL(is_show_book_name,0) from book_group
        where create_user=#{partyId} limit 1
    </select>

    <!--删除-->
    <update id="deleteByBookGroupId" parameterType="map">
        update book_group
        set is_delete = 1,
        update_time=now()
        where id = #{bookGroupId} and is_delete = 0
    </update>

    <select id="listPageBook4AdBrand" parameterType="map" resultType="com.pcloud.book.advertising.dto.Book4AdvertisingTagDTO">
        SELECT
            b.BOOK_ID bookId,
            b.ISBN isbn,
            b.BOOK_NAME bookName,
            g.create_time createTime
        FROM
            book_group g
        LEFT JOIN book b ON g.book_id = b.BOOK_ID
        WHERE
            g.is_delete = 0
            AND b.IS_DELETE = 0
            AND g.book_id != 0
        <if test="name != null">
            AND (
            b.BOOK_NAME LIKE CONCAT('%', #{name},'%')
            OR b.ISBN LIKE  CONCAT('%', #{name},'%')
            )
        </if>
        GROUP BY
            g.book_id
        ORDER BY
            g.create_time DESC, g.id DESC
    </select>
</mapper>