<?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.book.dao.impl.BookQrcodeWxworkDaoImpl">

    <resultMap type="com.pcloud.book.book.entity.BookQrcodeWxwork" id="BookQrcodeWxworkMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="bookId" column="book_id" jdbcType="INTEGER"/>
        <result property="channelId" column="channel_id" jdbcType="INTEGER"/>
        <result property="adviserId" column="adviser_id" jdbcType="INTEGER"/>
        <result property="bookQrcodeType" column="book_qrcode_type" jdbcType="INTEGER"/>
        <result property="bookQrcodeId" column="book_qrcode_id" jdbcType="INTEGER"/>
        <result property="wxworkQrcodeId" column="wxwork_qrcode_id" jdbcType="INTEGER"/>
        <result property="wxworkQrcodeType" column="wxwork_qrcode_type" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="guide" column="guide" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
       id, book_id, channel_id, adviser_id, book_qrcode_type, book_qrcode_id, wxwork_qrcode_id, wxwork_qrcode_type, create_time, guide
    </sql>
    <!--查询单个-->
    <select id="getById" resultMap="BookQrcodeWxworkMap">
        select
          <include refid="Base_Column_List"/>
        from book.book_qrcode_wxwork
        where id = #{id}
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into book.book_qrcode_wxwork(book_id, channel_id, adviser_id, book_qrcode_type, book_qrcode_id, wxwork_qrcode_id,
         wxwork_qrcode_type, create_time, guide)
        values (#{bookId}, #{channelId}, #{adviserId}, #{bookQrcodeType}, #{bookQrcodeId}, #{wxworkQrcodeId},
         #{wxworkQrcodeType}, now(), #{guide})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update book.book_qrcode_wxwork
        <set>
            <if test="bookId != null">
                book_id = #{bookId},
            </if>
            <if test="channelId != null">
                channel_id = #{channelId},
            </if>
            <if test="adviserId != null">
                adviser_id = #{adviserId},
            </if>
            <if test="bookQrcodeType != null">
                book_qrcode_type = #{bookQrcodeType},
            </if>
            <if test="bookQrcodeId != null">
                book_qrcode_id = #{bookQrcodeId},
            </if>
            <if test="wxworkQrcodeId != null">
                wxwork_qrcode_id = #{wxworkQrcodeId},
            </if>
            <if test="wxworkQrcodeType != null">
                wxwork_qrcode_type = #{wxworkQrcodeType},
            </if>
        </set>
        where id = #{id}
    </update>

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

    <delete id="deleteByQrcodeIdAndType" parameterType="map">
        delete from book.book_qrcode_wxwork
        where book_qrcode_type = #{bookQrcodeType}
        and  book_qrcode_id = #{bookQrcodeId}
        and wxwork_qrcode_type = #{wxworkQrcodeType}
    </delete>
    <select id="getByQrcodeIdAndType" resultMap="BookQrcodeWxworkMap" parameterType="map">
        select
        <include refid="Base_Column_List"/>
        from book.book_qrcode_wxwork
        where book_qrcode_type = #{bookQrcodeType}
        and  book_qrcode_id = #{bookQrcodeId}
        and wxwork_qrcode_type = #{wxworkQrcodeType}
    </select>

    <select id="getWxworkQrcodeByBook" parameterType="map" resultMap="BookQrcodeWxworkMap">
        select
        <include refid="Base_Column_List"/>
        from book.book_qrcode_wxwork
        where book_id = #{bookId}
        and channel_id = #{channelId}
        and adviser_id = #{adviserId}
        <if test="wxworkQrcodeType != null">
            and wxwork_qrcode_type = #{wxworkQrcodeType}
        </if>
        GROUP BY
        wxwork_qrcode_type, wxwork_qrcode_id
        order by wxwork_qrcode_type asc, create_time desc
    </select>

    <select id="getWxworkQrcodeIdsExceptBook" resultType="long" parameterType="map">
        SELECT DISTINCT
            wxwork_qrcode_id
        FROM
            book_qrcode_wxwork
        WHERE
            wxwork_qrcode_type = #{wxworkQrcodeType}
        AND book_id != #{bookId}
    </select>

    <select id="mapWxWorkServeCount4SceneIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
            book_qrcode_id bookQrcodeId, COUNT(id) serveCount, book_id bookId, adviser_id adviserId, channel_id channelId, COUNT(DISTINCT book_qrcode_id) qrcodeCount
        FROM
            book_qrcode_wxwork
        WHERE
            book_qrcode_type = #{bookQrcodeType}
            AND book_qrcode_id IN
            <foreach collection="sceneIds" item="item" separator="," open="(" close=")">
                ${item}
            </foreach>
        GROUP BY book_qrcode_id
    </select>

    <select id="mapWxWorkServeCount4BookIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookGroupServeCountDTO">
        SELECT
            CONCAT(book_id,'_',channel_id,'_',adviser_id) bookChannelAdviserId, book_qrcode_id bookQrcodeId,COUNT(id) serveCount,
            book_id bookId, adviser_id adviserId, channel_id channelId, COUNT(DISTINCT book_qrcode_id) qrcodeCount,
            GROUP_CONCAT(DISTINCT book_qrcode_id) sceneIdStr
        FROM
            book_qrcode_wxwork
        WHERE
        book_qrcode_type = #{bookQrcodeType}
        AND book_qrcode_id IN (${strSceneIds})
        <if test="adviserIds!=null">
            AND adviser_id IN
            <foreach collection="adviserIds" item="item" separator="," open="(" close=")">
                ${item}
            </foreach>
        </if>
        <if test="bookIds!=null">
            AND book_id IN
            <foreach collection="bookIds" item="item" separator="," open="(" close=")">
                ${item}
            </foreach>
        </if>
        <if test="channelIds!=null">
            AND channel_id IN
            <foreach collection="channelIds" item="item" separator="," open="(" close=")">
                ${item}
            </foreach>
        </if>
        GROUP BY book_id, channel_id, adviser_id
    </select>

    <select id="getHasWxWorkCode" parameterType="map" resultType="long">
        SELECT
        book_qrcode_id
        FROM
        book_qrcode_wxwork
        WHERE
        book_qrcode_type = 1
        AND book_qrcode_id IN
        <foreach collection="sceneIds" item="item" separator="," open="(" close=")">
            ${item}
        </foreach>
    </select>

    <select id="getByQrcodeId" parameterType="map" resultMap="BookQrcodeWxworkMap">
        select
        <include refid="Base_Column_List"/>
        from book.book_qrcode_wxwork
        where book_qrcode_type = #{bookQrcodeType}
        and  book_qrcode_id = #{bookQrcodeId}
    </select>

    <select id="listGroupOnBook" parameterType="long" resultType="com.pcloud.book.group.dto.ResourceOnBookDTO">
        SELECT
            wxwork_qrcode_id serveId,
            book_qrcode_id sceneId,
            book_id bookId,
            adviser_id adviserId,
            channel_id channelId
        FROM
            book_qrcode_wxwork
        WHERE
            wxwork_qrcode_id = #{groupId}
          AND book_qrcode_type = 1
          AND wxwork_qrcode_type = 2
        GROUP BY
            book_qrcode_id
    </select>

</mapper>
