<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxThumbMapper">
    <resultMap id="BaseWcxThumbMap" type="cn.com.countrygarden.bi.main.model.wcx.WcxThumb">
        <result column="thumb_id" property="thumbId"/>
        <result column="thumb_number" property="thumbNumber"/>
        <result column="user_id" property="userId"/>
        <result column="innovation_id" property="innovationId"/>
        <result column="status" property="status"/>
        <result column="create_date" property="createDate"/>
    </resultMap>

    <insert id="insert">
        INSERT INTO wcx_thumb
        (
        thumb_id,
        thumb_number,
        user_id,
        innovation_id,
        status,
        create_date
        )
        VALUES
        (
        #{thumbId},
        #{thumbNumber},
        #{userId},
        #{innovationId},
        #{status},
        #{createDate}
        )
    </insert>

    <select id="selectPointedNumber" resultType="int">
        select count(thumb_id)
        from wcx_thumb
        where
        user_id = #{userId}
        and ( create_date > #{startDate} or create_date &lt; #{endDate})
    </select>

    <select id="selectOne" resultMap="BaseWcxThumbMap">
        select
        thumb_id,
        thumb_number,
        user_id,
        innovation_id,
        status,
        create_date
        from wcx_thumb
        <where>
            <if test="userId != null">
                user_id = #{userId}
            </if>
            <if test="innovationId != null">
                and innovation_id = #{innovationId}
            </if>
            <if test="userId == null and innovationId == null">
                1 = 2
            </if>
        </where>
    </select>

    <select id="selectThumbNumberByInnovationId" resultType="java.lang.Long">
        select sum(thumb_number) thumbNumber from wcx_thumb where innovation_id = #{innovationId} group by innovation_id
    </select>

    <select id="getLeadThumbNumberByInnId" resultType="int">
        SELECT count(u.user_id)
        FROM wcx_thumb t
        JOIN wcx_user u on u.user_id = t.user_id
        JOIN wcx_role r on r.role_type = '2' and u.role_id = r.role_id
        WHERE innovation_id = #{innovationId}
    </select>

    <select id="selectAllThumbNumber" resultType="java.lang.Long">
        SELECT IFNULL(sum(thumb_number), 0)
        FROM wcx_thumb
        WHERE  create_date <![CDATA[>=]]> #{startDate} and create_date <![CDATA[<=]]> #{endDate}
    </select>
    <!-- 排除 baseline !=7  -->
    <select id="selectAllThumbNumberExclude" resultType="java.lang.Long">
        SELECT IFNULL(sum(thumb_number), 0)
        FROM wcx_thumb left join wcx_innovation on wcx_thumb.innovation_id=wcx_innovation.innovation_id
        WHERE
            wcx_innovation.baseline !='7'
            and wcx_thumb.create_date <![CDATA[>=]]> #{startDate}
            and wcx_thumb.create_date <![CDATA[<=]]> #{endDate}
    </select>

    <select id="findBestPutPointThumbNum" resultType="int">
        SELECT count(t.user_id) thumbNum
        FROM wcx_thumb t
        WHERE t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
        GROUP BY t.user_id
        ORDER BY thumbNum DESC
        limit 1
    </select>

    <delete id="deleteByInnovationId">
        DELETE FROM wcx_thumb WHERE innovation_id = #{innovationId}
    </delete>

    <select id="findThumbedNum" resultType="int">
        SELECT sum(t.thumb_number)
        FROM wcx_thumb t
        WHERE user_id = #{userId} and t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
    </select>

    <select id="findThumbedCountNum" resultType="int">
         SELECT count(t.thumb_id)
        FROM wcx_thumb t
        WHERE user_id = #{userId} and t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
    </select>

    <select id="findThumbCountNumMinOfLeader" resultType="int">
        SELECT temp.countNum FROM (
        SELECT t.innovation_id, count(t.thumb_id) countNum
        FROM wcx_thumb t
        JOIN wcx_user u on u.user_id = t.user_id AND t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
        WHERE t.role_id IN (SELECT tr.role_id FROM wcx_role tr WHERE tr.role_type = '2')
        GROUP BY t.innovation_id
        ORDER BY countNum DESC
        limit #{limitCount}
        ) temp
        ORDER BY temp.countNum ASC
        limit 1
    </select>
    <!-- 排除掉 baseline 7 -->
    <select id="findThumbCountNumMinOfLeaderExclude" resultType="int">
        SELECT temp.countNum FROM (
        SELECT t.innovation_id, count(t.thumb_id) countNum
        FROM wcx_thumb t left join wcx_innovation wi on t.innovation_id=wi.innovation_id
        JOIN wcx_user u on u.user_id = t.user_id AND t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
        WHERE wi.innovation_id!='7' and t.role_id IN (SELECT tr.role_id FROM wcx_role tr WHERE tr.role_type = '2')
        GROUP BY t.innovation_id
        ORDER BY countNum DESC
        limit #{limitCount}
        ) temp
        ORDER BY temp.countNum ASC
        limit 1
    </select>

    <select id="findThumbNumMaxOfLeader" resultType="int">
        SELECT max(temp.thumbNum) FROM (
        SELECT t.user_id, sum(t.thumb_number) thumbNum
        FROM wcx_thumb t
        JOIN wcx_user u on u.user_id = t.user_id AND t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
        WHERE t.role_id IN (SELECT tr.role_id FROM wcx_role tr WHERE tr.role_type = '2')
        GROUP BY t.user_id
        ) temp
    </select>

    <select id="findInnCountBetweenDateThumb" resultType="int">
        SELECT
        COUNT(tt.innovation_id)
        FROM (
        SELECT t.innovation_id FROM wcx_thumb t WHERE
        t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
        and t.innovation_id not in <include refid="BasePrizeInnIds"/>
        GROUP BY t.innovation_id) tt
    </select>
    <select id="findInnCountBetweenDateThumbExclude" resultType="int">
        SELECT
        COUNT(tt.innovation_id)
        FROM (
        SELECT t.innovation_id FROM wcx_thumb t left join wcx_innovation w on t.innovation_id=w.innovation_id
        WHERE
             w.baseline!='7'
            and  t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
            and t.innovation_id not in <include refid="BasePrizeInnIds"/>
        GROUP BY t.innovation_id) tt
    </select>

    <!-- 获奖的创新 -->
    <sql id="BasePrizeInnIds">
        (SELECT pi.inn_id
        FROM wcx_prize p
        JOIN wcx_prize_item pi on p.prize_id = pi.prize_id
        WHERE p.prize_type in ('1', '2') AND p.status = '1' AND pi.status = '1')
    </sql>

    <select id="findThumbedNumByInnoId" resultType="int">
        SELECT sum(t.thumb_number)
        FROM wcx_thumb t
        WHERE innovation_id = #{innoId} and t.create_date <![CDATA[>=]]> #{startDate} and t.create_date <![CDATA[<=]]> #{endDate}
    </select>

    <select id="findBigLeadThumbNumber" resultType="int">
        SELECT count(t.user_id)
                FROM wcx_thumb t
          WHERE t.innovation_id = #{innovationId} and
        t.role_id in (SELECT r.role_id from wcx_role r where r.role_type = '2' and r.role_code = 'leader')
    </select>
</mapper>