<?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.group.dao.impl.ResourcePageDaoImpl">

    <resultMap type="com.pcloud.book.group.entity.ResourcePage" id="ResourcePageMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="bookGroupId" column="book_group_id" jdbcType="INTEGER"/>
        <result property="style" column="style" jdbcType="INTEGER"/>
        <result property="showBook" column="show_book" jdbcType="INTEGER"/>
        <result property="openRays" column="open_rays" jdbcType="INTEGER"/>
        <result property="openFood" column="open_food" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="createUser" column="create_user" jdbcType="INTEGER"/>
        <result property="qrcodeUrl" column="qrcode_url" jdbcType="VARCHAR"/>
        <result property="sceneId" column="scene_id" jdbcType="INTEGER"/>
        <result property="officialSceneId" column="official_scene_id" jdbcType="INTEGER"/>
        <result property="bookCover" column="book_cover" jdbcType="VARCHAR"/>
        <result property="bookId" column="book_id" jdbcType="INTEGER"/>
        <result property="channelId" column="channel_id" jdbcType="INTEGER"/>
        <result property="backgroundImg" column="background_img" jdbcType="VARCHAR"/>
        <result property="navigationColor" column="navigation_color" jdbcType="INTEGER"/>
        <result property="navigationFormat" column="navigation_format" jdbcType="INTEGER"/>
        <result property="fontColor" column="font_color" jdbcType="VARCHAR"/>
        <result property="watermark" column="watermark" jdbcType="VARCHAR"/>
        <result property="watermarkStyle" column="watermark_style" jdbcType="TINYINT"/>
    </resultMap>

    <sql id="Base_Column_List">
         id, book_group_id, style, show_book, open_rays, open_food, create_time, update_time, create_user, qrcode_url, scene_id, official_scene_id,
          book_cover, book_id, channel_id, background_img, navigation_color, navigation_format, font_color,watermark, watermark_style
    </sql>

    <!--查询单个-->
    <select id="getById" resultMap="ResourcePageMap">
        select
         <include refid="Base_Column_List"/>
        from book.resource_page
        where id = #{id}
    </select>


    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into book.resource_page(book_group_id, style, show_book, open_rays, open_food, create_time, update_time, create_user,
         qrcode_url, scene_id, book_cover, book_id, channel_id, background_img, font_color, watermark, watermark_style)
        values (#{bookGroupId}, #{style}, #{showBook}, #{openRays}, IFNULL(#{openFood},0), now(), now(), #{createUser}, #{qrcodeUrl}, #{sceneId}, #{bookCover},
        #{bookId}, #{channelId}, #{backgroundImg}, #{fontColor}, #{watermark}, #{watermarkStyle})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update book.resource_page
        <set>
            <if test="style != null">
                style = #{style},
            </if>
            <if test="showBook != null">
                show_book = #{showBook},
            </if>
            <if test="openRays != null">
                open_rays = #{openRays},
            </if>
            <if test="openFood != null">
                open_food = #{openFood},
            </if>
            <if test="officialSceneId != null">
                official_scene_id = #{officialSceneId},
            </if>
            <if test="qrcodeUrl != null">
                qrcode_url = #{qrcodeUrl},
            </if>
            <if test="bookGroupId != null">
                book_group_id = #{bookGroupId},
            </if>
            <if test="bookId != null">
                book_id = #{bookId},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId},
            </if>
                book_cover = #{bookCover},
                update_time = NOW(),
                background_img = #{backgroundImg},
                font_color = #{fontColor},
                 watermark =  #{watermark},
                watermark_style = #{watermarkStyle}
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete from book.resource_page where id = #{id}
    </delete>

    <select id="getByBookGroupId" parameterType="long" resultMap="ResourcePageMap">
         select
         <include refid="Base_Column_List"/>
        from book.resource_page
        where book_group_id = #{bookGroupId}
        limit 1
    </select>

    <select id="getByBookId" parameterType="map" resultMap="ResourcePageMap">
        SELECT
        p.id,
        p.book_group_id,
        p.style,
        p.show_book,
        p.book_cover,
        p.open_rays,
        p.open_food,
        p.scene_id,
        p.background_img,
        p.navigation_color,
        p.navigation_format,
        p.font_color
        FROM
        resource_page p
        LEFT JOIN resource_page_item i ON p.id = i.resource_page_id
        WHERE
        p.book_id = #{bookId}
        <if test="channelId != null">
            AND p.channel_id = #{channelId}
        </if>
        <if test="adviserId != null">
            AND p.create_user = #{adviserId}
        </if>
        GROUP BY
        p.id
        ORDER BY
        p.open_rays DESC,
        COUNT(i.id) DESC,
        p.update_time DESC
        LIMIT 1
    </select>

    <update id="updatePageUpdateTime" parameterType="long">
         update book.resource_page
          SET update_time = NOW()
          where id = #{id}
    </update>

    <select id="getBySceneId" parameterType="long" resultMap="ResourcePageMap">
        select
        <include refid="Base_Column_List"/>
        from book.resource_page
        where scene_id = #{sceneId}
        limit 1
    </select>

    <select id="getPageByOfficialScene" parameterType="long" resultMap="ResourcePageMap">
        select
        id, book_group_id, style, show_book, open_rays, open_food, qrcode_url, scene_id, official_scene_id, book_cover
        from book.resource_page
        where official_scene_id = #{officialSceneId}
        limit 1
    </select>

    <select id="getOpenRaysSceneIds" resultType="long" parameterType="list">
        SELECT
            scene_id
        FROM
            resource_page
        WHERE
            open_rays = 1
        AND scene_id IN
        <foreach collection="list" item="item" open="(" close=")" separator="," index="index">
            #{item}
        </foreach>
    </select>

    <update id="batchUpdateSceneIdByBookGroup" parameterType="list">
        update book.resource_page set scene_id =
        <foreach collection="list" open="CASE" separator=" " close="END" item="item">
            WHEN book_group_id = #{item.bookGroupId} THEN #{item.sceneId}
        </foreach>
        where book_group_id IN
        <foreach collection="list" item="item" close=")" separator="," open="(">
            ${item.bookGroupId}
        </foreach>
    </update>

    <select id="getPageByColumnId" resultMap="ResourcePageMap" parameterType="long">
    SELECT
        p.id,
        p.book_group_id,
        p.style,
        p.show_book,
        p.open_rays,
        p.open_food,
        p.qrcode_url,
        p.scene_id,
        p.book_cover,
        p.official_scene_id
    FROM
        resource_page_column c
    LEFT JOIN resource_page p ON c.resource_page_id = p.id
    WHERE
        c.id = #{resourcePageColumnId}
    </select>

    <select id="getSceneIdsByResourceId" resultType="Long" parameterType="long">
   SELECT DISTINCT
	b.scene_id
    FROM
	resource_page_item a
    INNER JOIN resource_page b ON a.resource_page_id = b.id
    WHERE
	a.serve_type = "PRODUCT"
    AND a.serve_id = #{resourceId}
    </select>

    <update id="updateBookBySceneIds" parameterType="map">
        UPDATE resource_page
        SET book_id = #{bookId},
        channel_id = #{channelId}
        WHERE
        scene_id IN
        <foreach collection="sceneIds" close=")" open="(" separator="," index="index" item="item">
            #{item}
        </foreach>
    </update>

    <update id="setOtherPageRaysClose" parameterType="map">
        UPDATE resource_page
        SET open_rays = 0
        WHERE
            book_id = #{bookId}
        AND channel_id = #{channelId}
        AND create_user = #{createUser}
        <if test="sceneId != null">
            AND scene_id != #{sceneId}
        </if>
        <if test="bookGroupId != null">
            AND book_group_id != #{bookGroupId}
        </if>
    </update>

    <select id="isOpenRaysBook" resultType="boolean" parameterType="map">
        SELECT
            COUNT(1)
        FROM
            resource_page
        WHERE
            book_id = #{bookId}
        AND create_user = #{adviserId}
        AND channel_id = #{channelId}
        AND open_rays = 1
    </select>
    <select id="mapRaysCodeServeCount" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
        CONCAT(bg.book_id,'_',bg.channel_id,'_',bg.create_user) bookChannelAdviserId,ifnull(COUNT(DISTINCT bk.id),0) serveCount,
        bg.book_id bookId,bg.create_user adviserId,bg.channel_id channelId
        FROM
        resource_page bg
        LEFT JOIN resource_page_item bk ON bg.id=bk.resource_page_id
        WHERE
        bg.book_id IN
        <foreach collection="bookIds" item="bookId" separator="," open="(" close=")">
            ${bookId}
        </foreach>
        AND bg.channel_id IN
        <foreach collection="channelIds" item="channelId" separator="," open="(" close=")">
            ${channelId}
        </foreach>
        AND bg.create_user IN
        <foreach collection="adviserIds" item="adviserId" separator="," open="(" close=")">
            ${adviserId}
        </foreach>
        GROUP BY bg.book_id,bg.create_user,bg.channel_id
    </select>


    <select id="getAllBook4OpenFood" parameterType="map" resultType="com.pcloud.book.group.vo.ResourcePageVO">
		SELECT id, book_id bookId, scene_id sceneId
		FROM resource_page rp
		WHERE rp.open_food = 1
		AND book_id > 0
		GROUP BY book_id
		ORDER BY id
	</select>

    <update id="updateBookIdBySceneId" parameterType="map">
        UPDATE resource_page
        SET book_id = #{bookId},
         update_time = NOW()
        WHERE
        scene_id = #{sceneId}
    </update>

    <select id="updateNavigationFormat" parameterType="map">
        UPDATE resource_page
        <set>
            <if test="navigationColor != null">
                navigation_color = #{navigationColor},
            </if>
            <if test="navigationFormat != null">
                navigation_format = #{navigationFormat},
            </if>
            update_time = NOW(),
        </set>
        where id = #{id}
    </select>

</mapper>
