<?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.ResourcePageItemDaoImpl">

    <resultMap type="com.pcloud.book.group.entity.ResourcePageItem" id="ResourcePageItemMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="resourcePageId" column="resource_page_id" jdbcType="INTEGER"/>
        <result property="resourcePageColumnId" column="resource_page_column_id" jdbcType="INTEGER"/>
        <result property="serveId" column="serve_id" jdbcType="INTEGER"/>
        <result property="serveType" column="serve_type" jdbcType="VARCHAR"/>
        <result property="linkUrl" column="link_url" jdbcType="VARCHAR"/>
        <result property="typeCode" column="type_code" jdbcType="VARCHAR"/>
        <result property="seqNum" column="seq_num" jdbcType="INTEGER"/>
        <result property="isShow" column="is_show" jdbcType="BOOLEAN"/>
        <result property="guideContent" column="guide_content" jdbcType="VARCHAR"/>
        <result property="logoUrl" column="logo_url" jdbcType="VARCHAR"/>
        <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="qrcodeSource" column="qrcode_source" jdbcType="INTEGER"/>
        <result property="qrcodeShowType" column="qrcode_show_type" jdbcType="INTEGER"/>
        <result property="accountSettingId" column="account_setting_id" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="Base_Column_List">
         id, resource_page_id, resource_page_column_id, serve_id, serve_type, link_url, type_code, seq_num, is_show,
           guide_content, logo_url, create_time, qrcode_source, qrcode_show_type, account_setting_id
    </sql>

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

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into book.resource_page_item(resource_page_id, resource_page_column_id, serve_id, serve_type, link_url, type_code,
        seq_num, is_show, guide_content, logo_url, create_time, update_time, create_user, qrcode_source,
        qrcode_show_type, account_setting_id)
        values (#{resourcePageId}, #{resourcePageColumnId}, #{serveId}, #{serveType}, #{linkUrl}, #{typeCode},
         #{seqNum}, #{isShow}, #{guideContent}, #{logoUrl}, now(), now(), #{createUser}, #{qrcodeSource},
         #{qrcodeShowType}, #{accountSettingId})
    </insert>

    <insert id="batchInsert" keyProperty="id" useGeneratedKeys="true">
        insert into book.resource_page_item(resource_page_id, resource_page_column_id, serve_id, serve_type,
        link_url, type_code, seq_num, is_show, guide_content, logo_url,
        create_time, update_time, create_user, qrcode_source, qrcode_show_type, account_setting_id)
        values
        <foreach collection="list" separator="," index="index" item="item">
            (#{item.resourcePageId}, #{item.resourcePageColumnId}, #{item.serveId}, #{item.serveType},
            #{item.linkUrl}, #{item.typeCode}, #{item.seqNum}, #{item.isShow}, #{item.guideContent}, #{item.logoUrl},
            now(), now(), #{item.createUser}, #{item.qrcodeSource}, #{item.qrcodeShowType}, #{item.accountSettingId})
        </foreach>
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update book.resource_page_item
        <set>
            <if test="resourcePageId != null">
                resource_page_id = #{resourcePageId},
            </if>
            <if test="resourcePageColumnId != null">
                resource_page_column_id = #{resourcePageColumnId},
            </if>
            <if test="serveId != null">
                serve_id = #{serveId},
            </if>
            <if test="serveType != null and serveType != ''">
                serve_type = #{serveType},
            </if>
            <if test="linkUrl != null and linkUrl != ''">
                link_url = #{linkUrl},
            </if>
                update_time = now(),
        </set>
        where id = #{id}
    </update>

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


    <delete id="deleteByPageColumnId">
        delete from book.resource_page_item where resource_page_column_id = #{resourcePageColumnId}
    </delete>

    <select id="getResourcePageItemByColumnId" resultType="com.pcloud.book.group.vo.ResourcePageItemVO" parameterType="map">
        SELECT
            serve_id serveId,
            serve_type serveType,
            link_url linkUrl,
            type_code typeCode,
            logo_url logoUrl,
            guide_content guideContent,
            create_user createUser
        FROM
            resource_page_item
        WHERE
            resource_page_column_id = #{resourcePageColumnId}
        ORDER BY seq_num asc, id asc
    </select>

    <select id="getResourcePageItemByPageId" resultType="com.pcloud.book.group.vo.ResourcePageItemVO" parameterType="map">
         SELECT
            id,
            serve_id serveId,
            serve_type serveType,
            link_url linkUrl,
            resource_page_column_id resourcePageColumnId,
            type_code typeCode,
            logo_url logoUrl,
            guide_content guideContent,
            qrcode_source qrcodeSource,
            create_user createUser,
            seq_num seqNum,
            qrcode_show_type qrcodeShowType,
            account_setting_id accountSettingId
        FROM
            resource_page_item
        WHERE
            resource_page_id = #{resourcePageId}
        <if test="isShow != null">
            and is_show = #{isShow}
        </if>
        ORDER BY seq_num asc, id asc
    </select>

    <select id="getResourceCounts" parameterType="long" resultType="integer">
        SELECT  COUNT(1)
        FROM
            resource_page_item
        WHERE
            serve_type IN ("APP", "PRODUCT")
        AND resource_page_id = #{resourcePageId}
    </select>

    <select id="getServeListByBookGroupId" parameterType="long" resultType="com.pcloud.book.group.entity.BookGroupServe">
       SELECT
            i.serve_id serveId,
            i.serve_type serveType,
            i.link_url serveUrl,
            p.book_group_id bookGroupId,
            p.create_user createUser,
            i.type_code typeCode
        FROM
            resource_page_item i
        LEFT JOIN resource_page p ON i.resource_page_id = p.id
        LEFT JOIN resource_page_column c ON i.resource_page_column_id = c.id
        WHERE
            p.book_group_id = #{bookGroupId}
        AND i.serve_type IN ('APP', 'PRODUCT')
        ORDER BY
            c.column_seq ASC, i.seq_num ASC, i.id ASC
    </select>

    <delete id="deleteByPageId" parameterType="long">
        delete from book.resource_page_item where resource_page_id = #{resourcePageId}
    </delete>
    <select id="getServeListByBookGroupIds" parameterType="list" resultType="com.pcloud.book.group.entity.BookGroupServe">
        SELECT
        i.serve_id serveId,
        i.serve_type serveType,
        i.link_url serveUrl,
        p.book_group_id bookGroupId,
        p.create_user createUser,
        i.type_code typeCode
        FROM
        resource_page_item i
        LEFT JOIN resource_page p ON i.resource_page_id = p.id
        LEFT JOIN resource_page_column c ON i.resource_page_column_id = c.id
        WHERE
        p.book_group_id IN
        <foreach collection="list" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
        AND i.serve_type IN ('APP', 'PRODUCT')
        ORDER BY
        c.column_seq ASC, i.seq_num ASC, i.id ASC
    </select>

    <select id="getServeListBySceneIds" parameterType="list" resultType="com.pcloud.book.group.entity.BookGroupServe">
        SELECT
            i.serve_id serveId,
            i.serve_type serveType,
            i.link_url serveUrl,
            p.create_user createUser,
            i.type_code typeCode,
            p.scene_id sceneId
        FROM
            resource_page_item i
        LEFT JOIN resource_page p ON i.resource_page_id = p.id
        LEFT JOIN resource_page_column c ON i.resource_page_column_id = c.id
        LEFT JOIN resource_page_navigation n ON c.navigation_id = n.id
        WHERE
            p.scene_id IN
        <foreach collection="list" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
        AND i.serve_type IN ('APP', 'PRODUCT')
        ORDER BY
        n.navigation_seq ASC, c.column_seq ASC, i.seq_num ASC, i.id ASC
    </select>

    <!--只配置了1个应用作品，且不开启小睿-->
    <select id="checkOneServeItemByPageId" resultType="com.pcloud.book.group.vo.ResourcePageItemVO" parameterType="map">
        SELECT
            i.serve_id serveId,
            i.serve_type serveType,
            i.link_url linkUrl,
            i.type_code typeCode,
            i.create_user createUser
        FROM
            resource_page p
        LEFT JOIN resource_page_item i ON i.resource_page_id = p.id
        WHERE
            resource_page_id = #{resourcePageId}
        AND p.open_rays = 0
        HAVING
            COUNT(i.id) = 1
        AND i.serve_type IN ("APP", "PRODUCT")
    </select>

    <delete id="deleteByServeId" parameterType="long">
        delete from book.resource_page_item where serve_id = #{resourceId} and serve_type="PRODUCT"
    </delete>

    <select id="getAppServeByPage" resultType="com.pcloud.book.group.dto.BookServeDTO" parameterType="map">
           SELECT
            i.serve_id serveId,
            i.serve_type serveType,
            i.link_url url,
            i.type_code fromType
        FROM
            resource_page_item i
        WHERE
            i.serve_type IN ("APP", "PRODUCT")
        AND i.resource_page_id = #{resourcePageId}
    </select>

    <select id="getServeUrl4Update" parameterType="list" resultType="com.pcloud.book.group.dto.ResourcePageUrlDTO">
        SELECT
            p.scene_id sceneId,
            i.link_url linkUrl,
            i.id
        FROM
        resource_page_item i
        LEFT JOIN resource_page p ON p.id = i.resource_page_id
        WHERE
            p.scene_id IN
            <foreach collection="list" item="item" close=")" open="(" separator="," index="index">
                #{item}
            </foreach>
        AND i.serve_type IN ("APP", "PRODUCT")
        AND i.link_url NOT LIKE CONCAT("%", p.scene_id, "%")
    </select>

    <update id="batchUpdateServeUrlById" parameterType="list">
        update book.resource_page_item set link_url =
        <foreach collection="list" open="CASE" separator=" " close="END" item="item">
            WHEN id = #{item.id} THEN #{item.linkUrl}
        </foreach>
        where id IN
        <foreach collection="list" item="item" close=")" separator="," open="(">
            ${item.id}
        </foreach>
    </update>

    <select id="mapQrServeCount" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
            p.book_group_id bookQrcodeId,
            p.scene_id sceneId,
            ifnull(count(i.id),0) serveCount
        FROM
            resource_page p
        LEFT JOIN resource_page_item i ON i.resource_page_id = p.id
        WHERE
            p.scene_id in
        <foreach collection="sceneIds" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
        group by p.scene_id
    </select>

    <select id="getHasResourceCode4Erp" parameterType="map" resultType="Long">
        SELECT
        p.scene_id
        FROM
        resource_page p
        LEFT JOIN resource_page_item i ON i.resource_page_id = p.id
        WHERE
        p.scene_id in
        <foreach collection="sceneIds" index="index" separator="," open="(" close=")" item="item">
            #{item}
        </foreach>
        group by p.scene_id having count(i.id)>0
    </select>

    <select id="getAppIdsByPageId" parameterType="long" resultType="long">
        SELECT DISTINCT
            serve_id
        FROM
            resource_page_item
        WHERE
            resource_page_id = #{resourcePageId}
        AND serve_type = "APP"
    </select>

    <select id="getBookAllServeIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookServeDTO">
        SELECT
            i.serve_id serveId,
            i.serve_type serveType,
            i.link_url url,
            i.type_code fromType
        FROM
            resource_page_item i
        LEFT JOIN resource_page p ON i.resource_page_id = p.id
        WHERE
            i.serve_type IN ("APP", "PRODUCT")
        AND p.book_id = #{bookId}
        AND p.channel_id = #{channelId}
        AND p.create_user = #{adviserId}
        GROUP BY
            i.serve_type,
            i.serve_id
    </select>

    <select id="getPopupQrcodeItem" resultType="com.pcloud.book.group.vo.ResourcePageItemVO" parameterType="map">
        SELECT
        id,
        serve_id serveId,
        serve_type serveType,
        link_url linkUrl,
        resource_page_column_id resourcePageColumnId,
        type_code typeCode,
        logo_url logoUrl,
        guide_content guideContent,
        qrcode_source qrcodeSource,
        create_user createUser,
        seq_num seqNum,
        qrcode_show_type qrcodeShowType,
        account_setting_id accountSettingId
        FROM
        resource_page_item
        WHERE
        resource_page_id = #{resourcePageId}
        and qrcode_show_type = 2
        and serve_type = 'OFFICIAL_ACCOUNT'
        limit 1
    </select>


    <select id="getConfiguredRaysAppTypeCode" resultType="string" parameterType="long">
        SELECT DISTINCT
            type_code
        FROM
            resource_page_item
        WHERE
            resource_page_column_id = #{resourcePageColumnId}
    </select>

    <select id="getServeListBySceneId4QrUpdate" parameterType="long" resultType="com.pcloud.book.group.vo.ResourcePageItemVO">
        SELECT
        i.serve_id serveId,
        i.serve_type serveType,
        i.link_url linkUrl,
        p.create_user createUser,
        i.type_code typeCode,
        p.scene_id sceneId
        FROM
        resource_page_item i
        LEFT JOIN resource_page p ON i.resource_page_id = p.id
        LEFT JOIN resource_page_column c ON i.resource_page_column_id = c.id
        LEFT JOIN resource_page_navigation n ON c.navigation_id = n.id
        WHERE
        p.scene_id = #{sceneId}
        AND i.serve_type IN ('APP', 'PRODUCT','MEMBER_ACTIVITY')
        GROUP BY i.serve_id
        ORDER BY
        n.navigation_seq ASC, c.column_seq ASC, i.id ASC
    </select>

</mapper>