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

    <resultMap id="BaseResultMap" type="com.pcloud.book.group.entity.WeixinQrcode">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="weixin_group_id" property="weixinGroupId" jdbcType="VARCHAR"/>
        <result column="robot_id" property="robotId" jdbcType="VARCHAR"/>
        <result column="robot_wx_id" property="robotWxId" jdbcType="VARCHAR"/>
        <result column="qrcode_url" property="qrcodeUrl" jdbcType="BIGINT"/>
        <result column="use_state" property="useState" jdbcType="TINYINT"/>
        <result column="update_qr_time" property="updateQrTime" jdbcType="TIMESTAMP"/>
        <result column="generation" property="generation" jdbcType="INTEGER"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, weixin_group_id, robot_id, robot_wx_id, qrcode_url, use_state, update_qr_time, generation
    </sql>

    <!--根据Id查询详情-->
    <select id="getById" parameterType="Long" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from weixin_qrcode
        where id = #{id}
    </select>

    <insert id="insert" parameterType = "weixinQrcode" useGeneratedKeys="true"
            keyProperty="id">
        INSERT INTO
        weixin_qrcode(qrcode_url, weixin_group_id, robot_id, robot_wx_id, update_state, create_user, create_time, update_qr_time, generation)
        VALUES (#{qrcodeUrl,jdbcType=VARCHAR},#{weixinGroupId,jdbcType=VARCHAR}, #{robotId,jdbcType=BIGINT},#{robotWxId,jdbcType=VARCHAR},#{updateState},#{createUser}, NOW(),now(), #{generation})
    </insert>

    <select id="getOneWechatGroup" resultType = "WeixinQrcodeDTO" parameterType="map" >
        SELECT id, qrcode_url qrcodeUrl, weixin_group_id weixinGroupId, robot_id robotId, robot_wx_id robotWxId
        FROM weixin_qrcode
        WHERE use_state = 0  AND update_state in (0,1,3) and weixin_group_id != ''
        <if test="generation!=null">
            and generation=#{generation}
        </if>
        <if test="generation==null">
            and generation in (1,2,4)
        </if>
        LIMIT 1
    </select>

    <select id="countWechatGroup" resultType = "long" >
        SELECT count(*)
        FROM weixin_qrcode
        WHERE use_state = 0  AND update_state in (0,1,3) and weixin_group_id != ''
    </select>


    <update id="setIsUseById" parameterType="Long">
        update
        weixin_qrcode
        <set>
            use_state = 1
        </set>
        WHERE ID = #{id} and use_state = 0
    </update>

    <select id="getQrStatistics" resultType="QrStatisticsVO">
        SELECT sum(if(use_state = 0, 1, 0)) notUseCount,
        sum(if(use_state = 1, 1, 0)) inUseCount
        FROM weixin_qrcode
        WHERE
        update_state != 2 AND use_state in (0,1,3)
    </select>

    <select id="getQrStatisticsByVersion" resultType="QrStatisticsVO">
        SELECT sum(use_state = 0) notUseCount, sum(use_state in(1,2)) inUseCount, generation version
        FROM weixin_qrcode
        GROUP BY generation;
    </select>

    <update id="updateStatusToError" >
        update
        weixin_qrcode
        set
        update_state = 3
        where
        update_state = 1
    </update>

    <select id="getInitialQr" resultType="UpdateQrDTO" parameterType="map">
        select
            qrcode_url url, ID, robot_wx_id robotWxId, generation
        from
            weixin_qrcode
        where
            use_state in (0,1)
        AND
            update_state in (0,4)
        order by update_qr_time , id asc
        limit #{currentPage}, #{numPerPage}
    </select>

    <update id="setOnUpdate" parameterType="list">
        update weixin_qrcode set update_state = 1 WHERE ID in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach >
    </update>

    <update id="updateQrUrl" parameterType="updateQrDTO">
        update
        weixin_qrcode
        <set>
            update_state = #{updateState},
            update_qr_time = now(),
            <if test="url!=null">
                qrcode_url = #{url}
            </if>
        </set>
        WHERE ID = #{id}
    </update>

    <select id="getQrOnHandleCount" resultType="Integer" parameterType="map">
        select
        count(1)
        from
        weixin_qrcode
        where
        update_state = 3
        AND
        use_state in (0,1)
    </select>

    <select id="getQrOnHandle" resultType="UpdateQrDTO" parameterType="map">
        select
        qrcode_url url, ID, robot_wx_id robotWxId, generation
        from
        weixin_qrcode
        where
        use_state in (0,1) AND update_state!=2
        <if test="ids==null">
            AND update_state = 3
        </if>
        <if test="ids!=null">
            AND id in
            <foreach collection = "ids" index="index" item = "item" open = "("  separator= "," close = ")">
                ${item}
            </foreach >
        </if>
        order by update_qr_time , id asc
        limit #{currentPage}, #{numPerPage}
    </select>


    <update id="updateWeixinGroupId" parameterType="map">
        update
        weixin_qrcode
        <set>
            weixin_group_id = #{weixinGroupId},
            update_time = now(),
            update_state = 0
        </set>
        WHERE id = #{weixinQrcodeId}
    </update>

    <select id="listNoIdQrcode" resultType="SyncWeixinGroupIdRequestDTO" parameterType="map" >
        select
        qrcode_url url, ID, robot_wx_id robotWxId
        from
        weixin_qrcode
        where
        update_state != 2
        AND weixin_group_id = ''
        <if test="ids == null">
            AND use_state in (1, 2)
        </if>
        <if test="ids != null">
            AND id in
            <foreach collection = "ids" index="index" item = "item" open = "("  separator= "," close = ")">
                ${item}
            </foreach >
        </if>
    </select>

    <resultMap id="WeixinQrcodeCountMap" type="WeixinQrcodeCountDTO" >
        <result column="robot_id" property="robotId" jdbcType="BIGINT" />
        <result column="notUseCount" property="notUseCount" jdbcType="INTEGER" />
        <result column="useCount" property="useCount" jdbcType="INTEGER" />
        <result column="totalCount" property="totalCount" jdbcType="INTEGER" />
    </resultMap>
    <select id="listWeixinQrcodeCountByRobot" resultMap="WeixinQrcodeCountMap" parameterType="map" >
        select
            robot_id,
            count(1) totalCount,
            sum(if(use_state = 0, 1, 0)) notUseCount,
            sum(if(use_state = 1, 1, 0)) useCount
        from
            weixin_qrcode where robot_id in
        <foreach collection = "list" index="index" item = "item" open = "("  separator= "," close = ")">
            ${item}
        </foreach>
        group by robot_id
    </select>

    <update id="updateUseState" parameterType="map">
        update
        weixin_qrcode
        <set>
            use_state = #{useState},
            update_time = now()
        </set>
        WHERE id = #{id}
    </update>

    <select id="getQrcodeCount" resultType="integer">
        SELECT count(1)
        FROM weixin_qrcode
    </select>

    <select id="getQrcodeCountByVersion" resultType="QrStatisticsVO">
        SELECT count(1) allCount, generation version
        FROM weixin_qrcode
        group by generation;
    </select>

    <select id="checkExist" resultType="Boolean" parameterType="String">
        select
            count(1)
        from
            weixin_qrcode
        where
            weixin_group_id = #{_parameter}
    </select>

    <update id="updateState" parameterType="map">
        update
        weixin_qrcode
        <set>
            update_state = #{updateState}
        </set>
        WHERE ID = #{weixinQrcodeId}
    </update>

    <select id="getByGroupId" parameterType="String" resultMap="BaseResultMap">
        select <include refid="Base_Column_List"/>
        from weixin_qrcode where
        weixin_group_id = #{wechatGroupId}
        limit 1
    </select>

    <select id="listAvailableGroupByPage" parameterType="map" resultType="bookWxQrcodeDTO">
        SELECT
            weixin_group_id AS weixinGroupId,
            robot_wx_id AS robotWxId
        FROM
            `weixin_qrcode`
        WHERE
            use_state = 1
        GROUP BY weixin_group_id
        LIMIT #{currentPage}, #{numPerPage}
    </select>

    <select id="listOver10AvailableGroupByPage" parameterType="map" resultType="bookWxQrcodeDTO">
        SELECT
            q.weixin_group_id AS weixinGroupId,
            q.robot_wx_id AS robotWxId
        FROM
            `weixin_qrcode` q
        LEFT JOIN book_group_qrcode bq ON q.weixin_group_id = bq.weixin_group_id
        WHERE
            q.use_state = 1
        AND bq.user_number <![CDATA[ > ]]>  10
        GROUP BY
            q.weixin_group_id
        LIMIT #{currentPage}, #{numPerPage}
    </select>

    <select id="listByWxGroupIds" parameterType="map" resultType="com.pcloud.book.group.dto.BookWxQrcodeDTO">
        SELECT
            weixin_group_id weixinGroupId,
            robot_wx_id robotWxId,
            a.generation,
            b.wechat_group_ip wechatGroupIp,
            a.update_state updateState
        FROM
        weixin_qrcode a left join weixin_qrcode_generation b
        on a.generation = b.generation
        WHERE
        weixin_group_id IN
        <foreach collection="wxGroupIds" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>

    <select id="getVirtualIp" parameterType="int" resultType="string">
        SELECT virtual_machine_ip from weixin_qrcode_generation
        where generation = #{_parameter}
    </select>

    <!--根据状态获取群数量-->
    <select id="countByState" parameterType="map" resultType="Integer">
        select count(1) from weixin_qrcode t where t.robot_wx_id in
        <foreach collection="altIds" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
        and use_state=#{state}
    </select>

    <update id="updateGenerationMachineIp" parameterType="map">
        update weixin_qrcode_generation set virtual_machine_ip = #{machineIp} where generation = #{generation}
    </update>

    <select id="listUpdateQrByGeneration" parameterType="map" resultType="com.pcloud.book.group.dto.UpdateQrDTO">
        SELECT id, qrcode_url url, update_qr_time FROM weixin_qrcode
        WHERE generation =#{generation} AND update_state IN(0,1,3)
        ORDER BY update_qr_time ASC
        LIMIT #{num}
    </select>

    <select id="getCanUseCount" parameterType="int" resultType="integer">
        SELECT ifnull(COUNT(1), 0) FROM weixin_qrcode WHERE update_state !=2 AND use_state = 0
        and generation = #{_parameter}
    </select>
</mapper>