<?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.applet.dao.impl.AppletRecordServeDaoImpl">

    <resultMap id="BaseResultMap" type="com.pcloud.book.applet.entity.AppletRecordServe">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="wechat_user_id" property="wechatUserId" jdbcType="BIGINT"/>
        <result column="record_type" property="recordType" jdbcType="INTEGER"/>
        <result column="rights_setting_id" property="rightsSettingId" jdbcType="BIGINT"/>
        <result column="book_id" property="bookId" jdbcType="BIGINT"/>
        <result column="channel_id" property="channelId" jdbcType="BIGINT"/>
        <result column="adviser_id" property="adviserId" jdbcType="BIGINT"/>
        <result column="from_id" property="fromId" jdbcType="BIGINT"/>
        <result column="from_name" property="fromName" jdbcType="VARCHAR"/>
        <result column="link_url" property="linkUrl" jdbcType="VARCHAR"/>
        <result column="account_setting_id" property="accountSettingId" jdbcType="BIGINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result column="create_date" property="createDate" jdbcType="DATE"/>
        <result column="is_delete" property="isDelete" jdbcType="INTEGER"/>
        <result column="type_code" property="typeCode" jdbcType="VARCHAR"/>
        <result column="type_name" property="typeName" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, wechat_user_id, record_type, rights_setting_id, book_id, channel_id, adviser_id, from_id, from_name, link_url, account_setting_id, create_time, create_date, is_delete, type_code, type_name
    </sql>

    <select id="getById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM applet_record_serve
        WHERE id = #{id}
    </select>

    <select id="getList" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM applet_record_serve
    </select>

    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        INSERT INTO applet_record_serve(
            wechat_user_id, 
            record_type, 
            rights_setting_id, 
            book_id, 
            channel_id, 
            adviser_id, 
            from_id, 
            from_name, 
            link_url, 
            account_setting_id, 
            create_time, 
            create_date,
            type_code,
            type_name
        ) VALUES (
            #{wechatUserId, jdbcType=BIGINT}, 
            #{recordType, jdbcType=INTEGER}, 
            #{rightsSettingId, jdbcType=BIGINT}, 
            #{bookId, jdbcType=BIGINT}, 
            #{channelId, jdbcType=BIGINT}, 
            #{adviserId, jdbcType=BIGINT}, 
            #{fromId, jdbcType=BIGINT}, 
            #{fromName, jdbcType=VARCHAR}, 
            #{linkUrl, jdbcType=VARCHAR}, 
            #{accountSettingId, jdbcType=BIGINT}, 
           now(),
            CURRENT_DATE(),
            #{typeCode},
            #{typeName}
        )
    </insert>

    <insert id="batchInsert">
        INSERT INTO applet_record_serve (
        wechat_user_id,
        record_type,
        rights_setting_id,
        book_id,
        channel_id,
        adviser_id,
        from_id,
        from_name,
        link_url,
        account_setting_id,
        create_time,
        create_date,
        is_delete
        ) VALUES
        <foreach collection="list" item="item" separator=",">
            (
            #{item.wechatUserId, jdbcType=BIGINT},
            #{item.recordType, jdbcType=INTEGER},
            #{item.rightsSettingId, jdbcType=BIGINT},
            #{item.bookId, jdbcType=BIGINT},
            #{item.channelId, jdbcType=BIGINT},
            #{item.adviserId, jdbcType=BIGINT},
            #{item.fromId, jdbcType=BIGINT},
            #{item.fromName, jdbcType=VARCHAR},
            #{item.linkUrl, jdbcType=VARCHAR},
            #{item.accountSettingId, jdbcType=BIGINT},
            #{item.createTime, jdbcType=TIMESTAMP},
            #{item.createDate, jdbcType=DATE},
            #{item.isDelete, jdbcType=INTEGER}
            )
        </foreach>
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        UPDATE applet_record_serve
        <set>
                 wechat_user_id = #{wechatUserId},
                 record_type = #{recordType},
                 rights_setting_id = #{rightsSettingId},
                book_id = #{bookId},
                channel_id = #{channelId},
                adviser_id = #{adviserId},
                from_id = #{fromId},
                from_name = #{fromName},
                link_url = #{linkUrl},
                account_setting_id = #{accountSettingId},
                create_time = now(),
                create_date = CURRENT_DATE(),
                type_code = #{typeCode},
                type_name = #{typeName}
        </set>
        WHERE id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        DELETE FROM applet_record_serve where id = #{id}
    </delete>


    <select id="getByTypeAndServeId" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from
        applet_record_serve
        where
        wechat_user_id = #{wechatUserId}
        and
        record_type = #{recordType}
        and
        from_id = #{fromId}
        and
        is_delete = 0
        limit 1
    </select>


    <select id="listAppletRecord" parameterType="map" resultMap="com.pcloud.book.applet.dao.impl.AppletRecordDaoImpl.BaseResultMap4DTO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        `applet_record_serve`
        WHERE
        is_delete = 0
        <if test="wechatUserIds != null">
            and wechat_user_id IN
            <foreach collection="wechatUserIds" separator="," open="(" close=")" index="index" item="wechatUserId">
                ${wechatUserId}
            </foreach>
        </if>
        <if test="recordTypes != null">
            and record_type in
            <foreach collection="recordTypes" index="index" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        and
        create_date &gt; DATE_SUB(curdate(),INTERVAL 30 day)
        <if test="queryName != null">
            and from_name like concat("%",#{queryName},"%")
        </if>
        <if test="typeName != null">
            and type_name = #{typeName}
        </if>
        ORDER BY
        create_time DESC
    </select>

    <select id="getBookMap" parameterType="list"  resultType="com.pcloud.book.applet.dto.AppletUserBookcaseDTO">
        SELECT
        c.id id,
        c.book_id bookId,
        c.channel_id channelId,
        c.adviser_id adviserId,
        b.BOOK_NAME bookName,
        b.COVER_IMG coverImg,
        b.AUTHOR author,
        b.PUBLISH publish,
        a.SECOND_TEMPLET_ID secondTempletId,
        a.TEMPLET_ID templetId,
        a.third_TEMPLET_ID thirdTempletId,
        d.join_group_type joinGroupType,
        d.id bookGroupId,
        b.ISBN isbn,
        a.GRA_LABEL_ID gradeLabelId,
        a.SUB_LABEL_ID subjectLabelId,
        a.vol_label_id volLabelId,
        IF(d.join_group_type=1 OR d.related_book_group_id>0,1,0) hasGroup,
        r.id rightsSettingId,
        r.count rightsSettingCount
        FROM applet_record_serve c
        LEFT JOIN BOOK_ADVISER a ON c.book_id=a.BOOK_ID AND c.adviser_id=a.ADVISER_ID
        AND c.channel_id=a.CHANNEL_ID
        INNER JOIN BOOK b ON c.BOOK_ID = b.BOOK_ID AND b.IS_DELETE = 0
        LEFT JOIN book_group d ON d.BOOK_ID = a.BOOK_ID
        AND d.create_user = c.adviser_id
        AND d.channel_id = c.channel_id
        AND d.IS_DELETE = 0
        LEFT JOIN rights_setting r ON c.rights_setting_id = r.id AND d.join_group_type=4 and r.show_state = 1  and r.rights_setting_type = 2
        WHERE
        c.is_delete = 0
        and
        c.id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </select>

    <select id="getAppletRecordCatalogue" parameterType="map" resultType="com.pcloud.book.applet.dto.AppletRecordDTO">
    SELECT
	record_type recordType,
	type_code typeCode,
	type_name typeName
    FROM
	applet_record_serve
    WHERE
	wechat_user_id IN
	<foreach collection="wechatUserIds" item="item" index="index" close=")" open="(" separator=",">
        #{item}
    </foreach>
    and
    CASE
    WHEN record_type in (1,5,6) THEN
	type_name IS NOT NULL
    ELSE
	type_name IS  NULL
    END
    AND
	create_date &gt; DATE_SUB(curdate(),INTERVAL 30 day)
    GROUP BY
	record_type,
	type_name
    </select>

    <update id="batchUpdate" parameterType="list">
        update
        applet_record_serve
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="type_code =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.typeCode!=null">
                        when id=#{item.id} then #{item.typeCode}
                    </if>
                </foreach>
            </trim>
            <trim prefix="type_name =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.typeName!=null">
                        when id=#{item.id} then #{item.typeName}
                    </if>
                </foreach>
            </trim>
        </trim>
        <where>
            <foreach collection="list" separator="or" item="item">
                id = #{item.id}
            </foreach>
        </where>
    </update>

    <select id="getUserRecordListByType" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from
        applet_record_serve
        where
        wechat_user_id = #{wechatUserId}
        <if test="recordType != null">
            and
            record_type = #{recordType}
        </if>
        and
        is_delete = 0
        order by create_time desc
    </select>

    <select id="listAppletRecordLimit" parameterType="map" resultMap="com.pcloud.book.applet.dao.impl.AppletRecordDaoImpl.BaseResultMap4DTO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM
        `applet_record_serve`
        WHERE
        is_delete = 0
        <if test="wechatUserIds != null">
            and wechat_user_id IN
            <foreach collection="wechatUserIds" separator="," open="(" close=")" index="index" item="wechatUserId">
                ${wechatUserId}
            </foreach>
        </if>
        <if test="recordTypes != null">
            and record_type in
            <foreach collection="recordTypes" index="index" item="item" open="(" separator="," close=")">
                ${item}
            </foreach>
        </if>
        and
        create_date &gt; DATE_SUB(curdate(),INTERVAL 30 day)
        ORDER BY
        create_time DESC
        limit #{limit}
    </select>
</mapper>