<?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.microservices.dms.achievementLibrary.mapper.AchievementsMapper">

    <resultMap type="Achievements" id="AchievementsResult">
        <result property="id"    column="id"    />
        <result property="showAchievementName"    column="showAchievementName"    />
        <result property="achievementName"    column="achievement_name"    />
        <result property="field1"    column="field_1"    />
        <result property="field2"    column="field_2"    />
        <result property="field3"    column="field_3"    />
        <result property="achievementType"    column="achievement_type"    />
        <result property="source"    column="source"    />
        <result property="sourceId"    column="source_id"    />
        <result property="sourceLink"    column="source_link"    />
        <result property="tags"    column="tags"    />
        <result property="summary"    column="summary"    />
        <result property="publishingUnit"    column="publishing_unit"    />
        <result property="address"    column="address"    />
        <result property="isFeatured"    column="is_featured"    />
        <result property="contactPerson"    column="contact_person"    />
        <result property="contactNumber"    column="contact_number"    />
        <result property="ownerId"    column="owner_id"    />
        <result property="ownerName"    column="owner_name"    />
        <result property="status"    column="status"    />
        <result property="details"    column="details"    />
        <result property="reviewer"    column="reviewer"    />
        <result property="reviewDate"    column="review_date"    />
        <result property="reviewComments"    column="review_comments"    />
        <result property="images"    column="images"    />
        <result property="attachments"    column="attachments"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="attachmentCount"    column="attachment_count"    />
        <result property="isExpertAudit"    column="is_expert_audit"    />
        <result property="hotRank"    column="hot_rank"    />
        <result property="ext1"    column="ext1"    />
        <result property="ext2"    column="ext2"    />
        <result property="ext3"    column="ext3"    />
    </resultMap>

    <sql id="selectAchievementsVo">
        select id, IFNULL(show_ach_name,achievement_name) as "showAchievementName", achievement_name,hot_rank, field_1, field_2, field_3,
               attachment_count,is_expert_audit,achievement_type, source, source_id, source_link, tags, summary, publishing_unit, address,
               is_featured, contact_person, contact_number, owner_id, owner_name, status, details, reviewer, review_date, review_comments, images,
               attachments, create_by, create_time, update_by, update_time,ext1,ext2,ext3 from achievements
    </sql>

    <select id="selectAchievementsList" parameterType="Achievements" resultMap="AchievementsResult">
        <include refid="selectAchievementsVo"/>
        <where>
            <if test="showAchievementName != null  and showAchievementName != ''"> and show_ach_name like concat('%', #{showAchievementName}, '%')</if>
            <if test="achievementName != null  and achievementName != ''"> and achievement_name like concat('%', #{achievementName}, '%')</if>
            <if test="field1 != null  and field1 != ''"> and field_1 = #{field1}</if>
            <if test="field2 != null  and field2 != ''"> and field_2 = #{field2}</if>
            <if test="field3 != null  and field3 != ''"> and field_3 = #{field3}</if>
            <if test="achievementType != null  and achievementType != ''"> and achievement_type = #{achievementType}</if>
            <if test="source != null  and source != ''"> and source = #{source}</if>
            <if test="sourceId != null  and sourceId != ''"> and source_id = #{sourceId}</if>
            <if test="sourceLink != null  and sourceLink != ''"> and source_link = #{sourceLink}</if>
            <if test="tags != null  and tags != ''"> and tags = #{tags}</if>
            <if test="summary != null  and summary != ''"> and summary = #{summary}</if>
            <if test="publishingUnit != null  and publishingUnit != ''"> and publishing_unit = #{publishingUnit}</if>
            <if test="address != null  and address != ''"> and address = #{address}</if>
            <if test="isFeatured != null "> and is_featured = #{isFeatured}</if>
            <if test="contactPerson != null  and contactPerson != ''"> and contact_person = #{contactPerson}</if>
            <if test="contactNumber != null  and contactNumber != ''"> and contact_number = #{contactNumber}</if>
            <if test="ownerId != null  and ownerId != ''"> and owner_id = #{ownerId}</if>
            <if test="ownerName != null  and ownerName != ''"> and owner_name like concat('%', #{ownerName}, '%')</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="details != null  and details != ''"> and details = #{details}</if>
            <if test="reviewer != null  and reviewer != ''"> and reviewer = #{reviewer}</if>
            <if test="reviewDate != null "> and review_date = #{reviewDate}</if>
            <if test="reviewComments != null  and reviewComments != ''"> and review_comments = #{reviewComments}</if>
            <if test="images != null  and images != ''"> and images = #{images}</if>
            <if test="attachments != null  and attachments != ''"> and attachments = #{attachments}</if>
            <if test="hotRank != null  and hotRank != ''"> and hot_rank = #{hotRank}</if>
            <if test="createBy != null  and createBy != ''"> and create_by = #{createBy}</if>
        </where>
        order by create_time desc
    </select>

    <select id="selectAchievementsById" parameterType="Long" resultType="com.microservices.dms.achievementLibrary.domain.Achievements">
        select a.id,IFNULL(a.show_ach_name,a.achievement_name) as "showAchievementName",a.achievement_name,a.hot_rank,a.field_1,a.field_2,a.field_3,a.attachment_count,
               a.is_expert_audit,a.achievement_type,a.source,a.source_id,a.ext1,a.ext2,a.ext3
                ,u.login  as "userLogin",IFNULL(u.nickname,owner_name) as "userNickName",ue.gender as "gender",
               a.source_link,a.tags,a.summary,a.publishing_unit,a.address,a.is_featured,a.contact_person,a.contact_number,a.owner_id,a.owner_name, a.status,a.details,
               a.reviewer,a.review_date,a.review_comments, a.images,a.attachments,a.create_by,a.create_time,a.update_by,a.update_time
        from achievements a
                 left join users u on a.owner_name = u.login
                 left join user_extensions ue on u.id = ue.user_id
        where a.id = #{id}
    </select>

    <select id="getFileInfoByIdents" resultType="java.util.Map">
        select file_identifier as k ,file_origin_name as v from sys_file_info
        where file_identifier in
        <foreach collection="idents" close=")" index="idx" item="item" open="(" separator=",">
            #{item}
        </foreach>
    </select>

    <insert id="insertAchievements" parameterType="Achievements" useGeneratedKeys="true" keyProperty="id">
        insert into achievements
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="showAchievementName != null">show_ach_name,</if>
            <if test="achievementName != null">achievement_name,</if>
            <if test="field1 != null">field_1,</if>
            <if test="field2 != null">field_2,</if>
            <if test="field3 != null">field_3,</if>
            <if test="achievementType != null">achievement_type,</if>
            <if test="source != null">source,</if>
            <if test="sourceId != null">source_id,</if>
            <if test="sourceLink != null">source_link,</if>
            <if test="tags != null">tags,</if>
            <if test="summary != null">summary,</if>
            <if test="publishingUnit != null">publishing_unit,</if>
            <if test="address != null">address,</if>
            <if test="isFeatured != null">is_featured,</if>
            <if test="contactPerson != null">contact_person,</if>
            <if test="contactNumber != null">contact_number,</if>
            <if test="ownerId != null">owner_id,</if>
            <if test="ownerName != null">owner_name,</if>
            <if test="status != null">status,</if>
            <if test="details != null">details,</if>
            <if test="reviewer != null">reviewer,</if>
            <if test="reviewDate != null">review_date,</if>
            <if test="reviewComments != null">review_comments,</if>
            <if test="images != null">images,</if>
            <if test="attachments != null">attachments,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="attachmentCount != null">attachment_count,</if>
            <if test="isExpertAudit != null">is_expert_audit,</if>
            <if test="hotRank != null">hot_rank,</if>
            <if test="ext1 != null">ext1,</if>
            <if test="ext2 != null">ext2,</if>
            <if test="ext3 != null">ext3,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="showAchievementName != null">#{showAchievementName},</if>
            <if test="achievementName != null">#{achievementName},</if>
            <if test="field1 != null">#{field1},</if>
            <if test="field2 != null">#{field2},</if>
            <if test="field3 != null">#{field3},</if>
            <if test="achievementType != null">#{achievementType},</if>
            <if test="source != null">#{source},</if>
            <if test="sourceId != null">#{sourceId},</if>
            <if test="sourceLink != null">#{sourceLink},</if>
            <if test="tags != null">#{tags},</if>
            <if test="summary != null">#{summary},</if>
            <if test="publishingUnit != null">#{publishingUnit},</if>
            <if test="address != null">#{address},</if>
            <if test="isFeatured != null">#{isFeatured},</if>
            <if test="contactPerson != null">#{contactPerson},</if>
            <if test="contactNumber != null">#{contactNumber},</if>
            <if test="ownerId != null">#{ownerId},</if>
            <if test="ownerName != null">#{ownerName},</if>
            <if test="status != null">#{status},</if>
            <if test="details != null">#{details},</if>
            <if test="reviewer != null">#{reviewer},</if>
            <if test="reviewDate != null">#{reviewDate},</if>
            <if test="reviewComments != null">#{reviewComments},</if>
            <if test="images != null">#{images},</if>
            <if test="attachments != null">#{attachments},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="attachmentCount != null">#{attachmentCount},</if>
            <if test="isExpertAudit != null">#{isExpertAudit},</if>
            <if test="hotRank != null">#{hotRank},</if>
            <if test="ext1 != null">#{ext1},</if>
            <if test="ext2 != null">#{ext2},</if>
            <if test="ext3 != null">#{ext3},</if>
        </trim>
    </insert>

    <update id="updateAchievements" parameterType="Achievements">
        update achievements
        <trim prefix="SET" suffixOverrides=",">
            <if test="showAchievementName != null">show_ach_name = #{showAchievementName},</if>
            <if test="achievementName != null">achievement_name = #{achievementName},</if>
            <if test="field1 != null">field_1 = #{field1},</if>
            <if test="field2 != null">field_2 = #{field2},</if>
            <if test="field3 != null">field_3 = #{field3},</if>
            <if test="achievementType != null">achievement_type = #{achievementType},</if>
            <if test="source != null">source = #{source},</if>
            <if test="sourceId != null">source_id = #{sourceId},</if>
            <if test="sourceLink != null">source_link = #{sourceLink},</if>
            <if test="tags != null">tags = #{tags},</if>
            <if test="summary != null">summary = #{summary},</if>
            <if test="publishingUnit != null">publishing_unit = #{publishingUnit},</if>
            <if test="address != null">address = #{address},</if>
            <if test="isFeatured != null">is_featured = #{isFeatured},</if>
            <if test="contactPerson != null">contact_person = #{contactPerson},</if>
            <if test="contactNumber != null">contact_number = #{contactNumber},</if>
            <if test="ownerId != null">owner_id = #{ownerId},</if>
            <if test="ownerName != null">owner_name = #{ownerName},</if>
            <if test="status != null">status = #{status},</if>
            <if test="details != null">details = #{details},</if>
            <if test="reviewer != null">reviewer = #{reviewer},</if>
            <if test="reviewDate != null">review_date = #{reviewDate},</if>
            <if test="reviewComments != null">review_comments = #{reviewComments},</if>
            <if test="images != null">images = #{images},</if>
            <if test="attachments != null">attachments = #{attachments},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="attachmentCount != null">attachment_count = #{attachmentCount},</if>
            <if test="isExpertAudit != null">is_expert_audit = #{isExpertAudit},</if>
            <if test="hotRank != null">hot_rank = #{hotRank},</if>
            <if test="ext3 != null">ext3 = #{ext3},</if>
            <if test="ext2 != null">ext2 = #{ext2},</if>
            <if test="ext1 != null">ext1 = #{ext1},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteAchievementsById" parameterType="Long">
        delete from achievements where id = #{id}
    </delete>

    <delete id="deleteAchievementsByIds" parameterType="String">
        delete from achievements where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="getChoiceImport" resultType="com.microservices.dms.achievementLibrary.domain.Achievements">
        select id,IFNULL(show_ach_name,achievement_name) as "showAchievementName",achievement_name,images,field_1,field_2,field_3,summary,source,source_id
        from achievements
        where is_featured ='1'
        order by update_time desc
        limit 0,4
    </select>

    <select id="getTjBySources" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select sum(1) as "value",source as "key"
        from achievements
        where status='1'
        group by source
    </select>

    <select id="getTjByAreas" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select r.name as "name",count(1) as "value" from (
             select c.name,a.source, a.field_1,c.remark
             from achievements a
             left join project_categories c on a.field_1 = c.id
             where  a.source='1' and a.status='1' and  a.field_1 is not null and a.field_1 !=""
             union all
             select c.name, a.source, a.field_1, c.remark
             from achievements a
             left join categories c on a.field_1 = c.id
             where a.source in ('2','3','4') and a.status='1' and  a.field_1 is not null and a.field_1 !=""
        )r
        group by r.name
        order by count(1) desc
        limit 0,5
    </select>

    <select id="selectAchievementsByParam" parameterType="com.microservices.dms.achievementLibrary.domain.AchQueryVo" resultType="com.microservices.dms.achievementLibrary.domain.Achievements">
        <if test="source == ''  or source == null">
            select  a.id,IFNULL(a.show_ach_name,a.achievement_name) as "showAchievementName",a.achievement_name,a.summary,a.create_time,a.owner_name,a.tags,a.field_1,a.field_2,a.field_3,a.source,a.source_id,
                    u.login as "userLogin",
                   IFNULL(u.nickname,a.owner_name) as "userNickName",
                    ue.gender as "gender",
            (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = id)  as watcherSum,
            (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = id) as favoriteSum
            from achievements a
            left join users u on a.owner_name = u.login
            left join user_extensions ue on u.id = ue.user_id
            <where>
                and a.status='1'
                <if test="achievementName != null  and achievementName != ''"> and a.achievement_name like concat('%', #{achievementName}, '%')</if>
                <if test="showAchievementName != null  and showAchievementName != ''"> and a.show_ach_name like concat('%', #{showAchievementName}, '%')</if>
            </where>
            order by a.create_time desc
        </if>
        <if test='source=="1"'>
            select a.id,IFNULL(a.show_ach_name,a.achievement_name) as "showAchievementName", a.achievement_name, a.summary, a.create_time, a.owner_name, a.tags, a.field_1, a.field_2, a.field_3, a.source, a.source_id,
                   c.name as "field1Name",u.login  as "userLogin",
                    IFNULL(u.nickname,a.owner_name) as "userNickName",
                   ue.gender as "gender",
            (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = a.id)  as watcherSum,
            (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = a.id) as favoriteSum
            from achievements a
            left join users u on a.owner_name = u.login
            left join user_extensions ue on u.id = ue.user_id
            left join project_categories c on a.field_1 = c.id
            <where>
                and a.status='1'
                <if test="achievementName != null  and achievementName != ''"> and a.achievement_name like concat('%', #{achievementName}, '%')</if>
                <if test="showAchievementName != null  and showAchievementName != ''"> and a.show_ach_name like concat('%', #{showAchievementName}, '%')</if>
                <if test="source != null  and source != ''"> and a.source = #{source}</if>
                <if test='areaQuery != null  and areaQuery != "" '>
                    and (a.field_1 = #{areaQuery} or a.field_2 = #{areaQuery} or a.field_3 = #{areaQuery})
                </if>
            </where>
            order by create_time desc
        </if>
        <if test='source=="2" or source=="3" or source=="4" '>
            select a.id, IFNULL(a.show_ach_name,a.achievement_name)  as "showAchievementName", a.achievement_name, a.summary, a.create_time, a.owner_name, a.tags, a.field_1, a.field_2, a.field_3, a.source, a.source_id,
                   c.name as "field1Name",u.login  as "userLogin",
                    IFNULL(u.nickname,a.owner_name) as "userNickName",
                   ue.gender as "gender",
            (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = a.id)  as watcherSum,
            (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = a.id) as favoriteSum
            from achievements a
            left join users u on a.owner_name = u.login
            left join user_extensions ue on u.id = ue.user_id
            left join categories c on a.field_1 = c.id
            <where>
                and a.status='1'
                <if test="achievementName != null  and achievementName != ''"> and a.achievement_name like concat('%', #{achievementName}, '%')</if>
                <if test="showAchievementName != null  and showAchievementName != ''"> and a.show_ach_name like concat('%', #{showAchievementName}, '%')</if>
                <if test="source != null  and source != ''"> and a.source = #{source}</if>
                <if test='areaQuery != null  and areaQuery != "" '>
                    and (a.field_1 = #{areaQuery} or a.field_2 = #{areaQuery} or a.field_3 = #{areaQuery})
                </if>
            </where>
            order by a.create_time desc
        </if>
    </select>

    <select id="getAchievementClickSum" resultType="long">
        select count(1) from clickers
        where click_type='Achievements' and click_id = #{id}
    </select>

    <select id="getaWatcher" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(created_at, '%m-%d') as 'k', count(*) as 'v'
        from watchers w
        inner join achievements a on w.watchable_id = a.id
        where created_at &gt;= #{s}
          and created_at &lt;= #{e}
          and watchable_type = #{t}
        and a.status='1'
        group by DATE_FORMAT(created_at, '%m-%d')
        order by DATE_FORMAT(created_at, '%m-%d')
    </select>

    <select id="getFavorite" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(created_at, '%m-%d') as 'k', count(*) as 'v'
        from favorites f
        inner join achievements a on f.favorite_id = a.id
        where created_at &gt;= #{s}
          and created_at &lt;= #{e}
          and favorite_type = #{t}
          and a.status='1'
        group by DATE_FORMAT(created_at, '%m-%d')
        order by DATE_FORMAT(created_at, '%m-%d')
    </select>

    <select id="getHotAchievement" resultType="com.microservices.dms.achievementLibrary.domain.HotAchVo">
        select t.show_ach_name as "showAchievementName",t.achievement_name as name, t.w as watcher, t.f as faviter, (t.w + t.f) as total, t.id as `id`,t.owner_name
                as userName,t.gender,t.userNickName
        from (select a.achievement_name,
                     IFNULL(a.show_ach_name,a.achievement_name) as "show_ach_name",
                     a.id,a.owner_name,
                     IFNULL(u.nickname,a.owner_name) as "userNickName",
                     ue.gender as "gender",
                     (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = a.id)  as f,
                     (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = a.id) as w
              from achievements a
               left join users u on a.owner_name = u.login
               left join user_extensions ue on u.id = ue.user_id
              where a.status = '1'
              ) as t
        order by (f+w) desc
        limit 0,3
    </select>

    <select id="getAchievement" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(create_time, '%m-%d') as 'k', count(*) as 'v'
        from achievements
        where create_time &gt;= #{s}
          and create_time &lt;= #{e}
          and source = #{t} and status='1'
        group by DATE_FORMAT(create_time, '%m-%d')
        order by DATE_FORMAT(create_time, '%m-%d')
    </select>


    <select id="getExperts" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(create_time, '%m-%d') as 'k', count(*) as 'v'
        from expert_resource_library
        where create_time &gt;= #{s}
          and create_time &lt;= #{e} and status='1'
        group by DATE_FORMAT(create_time, '%m-%d')
        order by DATE_FORMAT(create_time, '%m-%d')
    </select>

    <select id="indexProjectStatistic" resultType="java.util.Map">
        select COUNT(*) as projectAchievementCount,
               IFNULL(SUM(attachment_count), 0) as attachmentCount,
               (select count(1) from clickers c inner join achievements a ON  a.id = c.click_id where click_type = 'Achievements' and a.source='1' and a.status='1') as clickCount,
               (select count(distinct a.source_id) from achievements a join project_resource_library prl on a.source_id = prl.id where a.source='1' and a.status='1') as projectCount
        from achievements
        where source = '1' and status='1'
    </select>

    <select id="indexTaskStatistic" resultType="java.util.Map">
        select (select count(distinct trl.task_id) from achievements a join task_resource_library trl on a.source_id = trl.id where a.source='2' and a.status=1) as taskCount,
               (select count(1) from clickers c inner join achievements a ON  a.id = c.click_id where click_type = 'Achievements'  and a.source='2' and a.status=1) as clickCount,
               COUNT(*) as expertAuditCount
        from achievements
        where source = '2'  and status=1
    </select>
    <select id="indexCompetitionStatistic" resultType="java.util.Map">
        select COUNT(*) as CompetitionCountAchievementCount,
               (select COUNT(distinct crl.open_competition_id) from achievements a join competition_resource_library crl
                   on a.source_id = crl.id where source = '3' and a.is_expert_audit = 1 and a.status='1') as expertAuditCount,
               (select count(1) from clickers c inner join achievements a ON  a.id = c.click_id where click_type = 'Achievements'  and a.source='3' and a.status='1') as clickCount,
               (select COUNT(distinct open_competition_id) from competition_resource_library crl inner join achievements a on a.source_id=crl.id where a.status='1') as  CompetitionCount
        from achievements
        where source = '3' and status='1'
    </select>

    <select id="indexSchoolEnterpriseStatistic" resultType="java.lang.Long">
        select COUNT(*)
        from achievements a
        where source = #{s}
        and a.status = '1'
        <if test="t != null and t != ''">
            and tags = #{t}
        </if>
    </select>

    <select id="getAreasByName" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        SELECT name as "key",id as "value"
        FROM categories
        <where>
            <if test="areaName != null and areaName != ''">
                name like concat('%', #{areaName}, '%')
            </if>
        </where>
    </select>

    <select id="getProjectAreasByName" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        SELECT name as "key",id as "value"
        FROM project_categories
        <where>
            <if test="areaName != null and areaName != ''">
                name like concat('%', #{areaName}, '%')
            </if>
        </where>
    </select>

    <select id="getExpertAreasByName" resultType="String">
        select distinct a.domainName from (
           select review_area_three as "domainName" from experts
           union all
           select review_area_two as "domainName" from experts
           union all
           select review_area_one as "domainName" from experts
        )a
        where a.domainName is not null and a.domainName !=""
    </select>

    <select id="getActDataStatisticById"
            resultType="com.microservices.dms.behaviorImage.domain.AchievementBehaviorSumVo">
        select a.clickSum, b.watchSum, c.searchSum, d.favoriteSum, e.downloadSum
        from (
                 (select count(1) as clickSum from clickers where click_type = 'Achievements' and click_id = #{id}) a,
                     (select count(1) as watchSum from watchers where watchable_type = 'Achievements' and watchable_id = #{id}) b,
                     (select count(1) as searchSum from searchers where search_type = 'Achievements' and search_id = #{id}) c,
                     (select count(1) as favoriteSum from favorites where favorite_type = 'Achievements' and favorite_id = #{id}) d,
                     (select count(1) as downloadSum from downloads where download_type = 'Achievements' and download_id = #{id}) e
                 )
    </select>

    <select id="get7AddClickById" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(created_at, '%m-%d') as 'k', count(*) as 'v'
        from clickers
        where created_at &gt;= #{s}
          and created_at &lt;= #{e}
          and click_type = #{t}
          and click_id = #{id}
        group by DATE_FORMAT(created_at, '%m-%d')
        order by DATE_FORMAT(created_at, '%m-%d')
    </select>

    <select id="getWatchFavoriteStatusById" resultType="com.microservices.dms.behaviorImage.domain.AchievementBehaviorSumVo">
        select  b.watchSum, d.favoriteSum,c.userDockingSum
        from (
             (select count(1) as watchSum from watchers where watchable_type = 'Achievements' and watchable_id = #{id} and user_id=#{userId}) b,
             (select count(1) as favoriteSum from favorites where favorite_type = 'Achievements' and favorite_id = #{id} and user_id=#{userId}) d,
                 (select count(1) as userDockingSum from clickers where click_type = 'userDocking' and click_id = #{id} and user_id=#{userId}) c
         )
    </select>

    <select id="countAchievementBySource" resultType="java.lang.Long">
        select count(*)
        from achievements
        <where>
            <if test="t != null and t != ''">and `source` = #{t}</if>
            <if test="s != null">and create_time &gt;= #{s}</if>
            <if test="e != null">and create_time &lt;= #{e}</if>
        </where>
    </select>

    <select id="getAchievementDataYearly" resultType="java.util.Map">
        select DATE_FORMAT(create_time, '%Y') as 'k', count(*) as 'v'
        from achievements
        group by DATE_FORMAT(create_time, '%Y')
        order by DATE_FORMAT(create_time, '%Y')
    </select>

    <select id="getAchievementType" resultType="java.util.Map">
        select achievement_type as 'k', count(*) as 'v'
        from achievements where achievement_type is not null
        group by achievement_type
    </select>

    <select id="getAchievementActData" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select * from (
        select '关注' as `key`, DATE_FORMAT(created_at, '%m-%d') as name ,COUNT(*) as value from watchers where watchable_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e} group by DATE_FORMAT(created_at, '%m-%d')
        union all
        select '浏览' as `key`, DATE_FORMAT(created_at, '%m-%d') as  name,COUNT(*) as value  from clickers where click_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d')
        union all
        select '下载' as `key`, DATE_FORMAT(created_at, '%m-%d') as name,COUNT(*) as value  from downloads where download_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d')
        union all
        select '收藏' as `key`, DATE_FORMAT(created_at, '%m-%d') as name,COUNT(*) as value  from favorites where favorite_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d'))t
        order by t.name
    </select>

    <select id="getAchievementDomain" resultType="java.util.Map">
        select r.name as "name",count(1) as "value" from (
             select
                 c.name,a.source, a.field_1,c.remark
             from achievements a
             left join project_categories c on a.field_1 = c.id
             where  a.source='1' and  a.field_1 is not null and a.field_1 !=""
             union all
             select
                 c.name, a.source, a.field_1, c.remark
             from achievements a
             left join categories c on a.field_1 = c.id
             where a.source in ('2','3','4')  and  a.field_1 is not null and a.field_1 !=""
        )r
        group by r.name
    </select>

    <select id="getAchievementAddYearly" resultType="java.util.Map">
        select DATE_FORMAT(create_time, '%Y') as 'k', count(*) as 'v'
        from achievements
        group by DATE_FORMAT(create_time, '%Y')
        order by DATE_FORMAT(create_time, '%Y')
    </select>
    <select id="getAchievementHotRank" resultType="java.util.Map">
        select IFNULL(show_ach_name,achievement_name) as name, create_time as reelaseTime, hot_rank from achievements
        order by hot_rank desc
    </select>

    <select id="getAllId" resultType="java.lang.Long">
        select id from achievements
    </select>

    <select id="getAreaStatistic" resultType="com.microservices.dms.achievementLibrary.domain.AreaStatisticVo">
        <if test="source == ''  or source == null">
            select
                z.name as "areaName",
                z.field_1 as "areaKey",
                z.remark as "remark",
                z.source as "source",
                sum(z.kyxm) as "kyxmSum",
                sum(z.ckrw) as "ckrwSum",
                sum(z.kfjs) as "kfjsSum",
                sum(z.xqcg) as "xqcgSum"
            from (
                select
                    r.name,
                    r.field_1,
                    r.remark,
                    r.sorceType as "source",
                    case when r.source = 1 then tmp else 0 end as "kyxm",
                    case when r.source = 2 then tmp else 0 end as "ckrw",
                    case when r.source = 3 then tmp else 0 end as "kfjs",
                    case when r.source = 4 then tmp else 0 end as "xqcg"
                from (
                    select
                        t.name,t.source,count(1) as "tmp",t.field_1,t.remark,t.sorceType
                     from (
                        select c.name,a.source, a.field_1,c.remark,"1" as sorceType
                        from achievements a
                        left join project_categories c on a.field_1 = c.id  where  a.source='1' and c.name is not null
                        union all
                        select c.name, a.source, a.field_1, c.remark,"2" as sorceType
                        from achievements a
                        left join categories c on a.field_1 = c.id where a.source in ('2','3','4') and c.name is not null
                    )t
                    where t.field_1 is not null and t.field_1 !=""
                    group by t.name, t.source, t.field_1, t.remark,t.sorceType
                ) r
            ) z
            <where>
                <if test="areaKey != null  and areaKey != ''">
                    and z.field_1 = #{areaKey}
                </if>
            </where>
            group by z.name, z.field_1, z.remark, z.source
        </if>
        <if test='source=="1"'>
            select
                z.name as "areaName",
                z.field_1 as "areaKey",
                z.remark as "remark",
                sum(z.kyxm) as "kyxmSum",
                sum(z.ckrw) as "ckrwSum",
                sum(z.kfjs) as "kfjsSum",
                sum(z.xqcg) as "xqcgSum",
                "1" as source
            from (
                select
                    r.name,
                    r.field_1,
                    r.remark,
                    case when r.source = 1 then tmp else 0 end as "kyxm",
                    case when r.source = 2 then tmp else 0 end as "ckrw",
                    case when r.source = 3 then tmp else 0 end as "kfjs",
                    case when r.source = 4 then tmp else 0 end as "xqcg"
                from (
                    select c.name,a.source, a.field_1,c.remark,count(1) as "tmp"
                    from achievements a
                    left join project_categories c on a.field_1 = c.id
                    where a.source='1' and a.field_1 is not null and a.field_1 !=""
                    group by c.name, a.source, a.field_1, c.remark
                ) r
            ) z
            <where>
                <if test="areaKey != null  and areaKey != ''">
                    and z.field_1 = #{areaKey}
                </if>
            </where>
            group by z.name, z.field_1, z.remark
        </if>
        <if test='source=="2" or source=="3" or source=="4" '>
            select
                z.name as "areaName",
                z.field_1 as "areaKey",
                z.remark as "remark",
                sum(z.kyxm) as "kyxmSum",
                sum(z.ckrw) as "ckrwSum",
                sum(z.kfjs) as "kfjsSum",
                sum(z.xqcg) as "xqcgSum",
                "2" as source
            from (
                select
                    r.name,
                    r.field_1,
                    r.remark,
                    case when r.source = 1 then tmp else 0 end as "kyxm",
                    case when r.source = 2 then tmp else 0 end as "ckrw",
                    case when r.source = 3 then tmp else 0 end as "kfjs",
                    case when r.source = 4 then tmp else 0 end as "xqcg"
                    from (
                        select c.name, a.source, a.field_1, c.remark,count(1) as tmp
                        from achievements a
                        left join categories c on a.field_1 = c.id
                        where a.source in ('2','3','4') and  a.field_1 is not null and a.field_1 !=""
                        group by c.name, a.source, a.field_1, c.remark
                ) r
            ) z
            <where>
                <if test="areaKey != null  and areaKey != ''">
                    and z.field_1 = #{areaKey}
                </if>
            </where>
            group by z.name, z.field_1, z.remark
        </if>
    </select>

    <select id="getAllSourceId" resultType="java.lang.Long">
        select id
        from project_resource_library
        where project_id = (select project_id from project_resource_library where id = #{sourceId})
    </select>

    <select id="getAllSourceIdCompetition" resultType="java.lang.Long">
        select id
        from competition_resource_library
        where open_competition_id = (select open_competition_id from competition_resource_library where id = #{sourceId})
    </select>
    <select id="getAllSourceIdTask" resultType="java.lang.Long">
        select id
        from task_resource_library
        where task_id = (select task_id from task_resource_library where id = #{sourceId})
    </select>
    <select id="getDistinctYear" resultType="String">
        select  distinct DATE_FORMAT(create_time, '%Y')
        from achievements
        where  source_id in
        <foreach item="fileId" collection="sourceId" open="(" separator="," close=")">
            #{fileId}
        </foreach>
        order by  DATE_FORMAT(create_time, '%Y') desc
    </select>

    <select id="getRelatedAch" resultType="com.microservices.dms.achievementLibrary.domain.AchRelatedVo">
        select id                                as "id",
               attachments                       as "attachments",
               summary                           as "summary",
               achievement_type                  as "achievementType",
               DATE_FORMAT(create_time, '%m-%d') as "achMonthDay",
               source                            as "source",
               source_id                         as "sourceId" ,
               achievement_name                  as "achRelateName",
                show_ach_name                    as "showAchievementName",
               #{paramYear}                      as resultYear
        from achievements
        where summary is not null and source_id in
        <foreach item="fileId" collection="sourceId" open="(" separator="," close=")">
            #{fileId}
        </foreach>
          and DATE_FORMAT(create_time, '%Y') = #{paramYear}
        order by DATE_FORMAT(create_time, '%m-%d')
    </select>

    <select id="getRelatedAchTaskOfExpert"
            resultType="com.microservices.dms.resourceLibrary.domain.TaskResourceLibrary">
        select *
        from  task_resource_library erl
                 join
             (select container_id
              from task_expert
              where container_type = 1
                and `status` in (1, 2) and expert_id = #{id}
              group by container_id
              having count(*) > 0) as tmp
             on erl.task_id = tmp.container_id
    </select>

    <select id="getRelatedAchCompetitionOfExpert"
            resultType="com.microservices.dms.resourceLibrary.domain.CompetitionResourceLibrary">
        select *
        from competition_resource_library erl
                 join
             (select container_id
              from task_expert
              where container_type = 2
                and `status` in (1, 2) and expert_id = #{id}
              group by container_id
              having count(*) > 0) as tmp
             on erl.open_competition_id = tmp.container_id
    </select>


    <select id="selectAchievementsByName" resultType="Long">
        select id
        from achievements
        <where>
            <if test="achName != null  and achName != ''"> and achievement_name like concat('%', #{achName}, '%')</if>
        </where>
    </select>

    <select id="getField1NameByParam" resultType="String">
        <if test='source != null and source == "1"'>
            select p.name from achievements a
            inner join project_categories p on a.field_1 = p.id
            where a.id=#{id}
        </if>
        <if test='source != null and source == "2"'>
            select p.name from achievements a
            inner join categories p on a.field_1 = p.id
            where a.id=#{id}
        </if>
        <if test='source != null and source == "3"'>
            select p.name from achievements a
            inner join categories p on a.field_1 = p.id
            where a.id=#{id}
        </if>
        <if test='source != null and source == "4"'>
            select p.name from achievements a
            inner join categories p on a.field_1 = p.id
            where a.id=#{id}
        </if>
    </select>

    <select id="getField1RemarkByParam" resultType="String">
        <if test='source != null and source == "1"'>
            select remark from project_categories  where id=#{areaKey}
        </if>
        <if test='source != null and source == "2"'>
            select remark from categories   where id =#{areaKey}
        </if>
    </select>

    <select id="getAchievementAddYearlyData" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select DATE_FORMAT(create_time, '%Y-%m') as 'k', count(*) as 'v'
        from achievements
        where create_time &gt;= #{s}
          and create_time &lt;= #{e}
        group by DATE_FORMAT(create_time, '%Y-%m')
        order by DATE_FORMAT(create_time, '%Y-%m')
    </select>

    <select id="getAchievementActDataW"
            resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select '关注' as `key`, DATE_FORMAT(created_at, '%m-%d') as name ,COUNT(*) as value from watchers where watchable_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e} group by DATE_FORMAT(created_at, '%m-%d')
    </select>
    <select id="getAchievementActDataC"
            resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select '浏览' as `key`, DATE_FORMAT(created_at, '%m-%d') as  name,COUNT(*) as value  from clickers where click_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d')

    </select>
    <select id="getAchievementActDataD"
            resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select '下载' as `key`, DATE_FORMAT(created_at, '%m-%d') as name,COUNT(*) as value  from downloads where download_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d')

    </select>

    <select id="getAchievementActDataF"
            resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select '收藏' as `key`, DATE_FORMAT(created_at, '%m-%d') as name,COUNT(*) as value  from favorites where favorite_type = 'Achievements' and created_at &gt;= #{s} and created_at &lt;= #{e}  group by DATE_FORMAT(created_at, '%m-%d')
    </select>

    <select id="getDockingUserInfo" resultType="java.util.Map">
       select * from (
       select  u.id,u.login,u.nickname,p.name as "lastname,",u.phone,u.company_name,u.mail,p.card as "idNumber",e.entity_type,DATE_FORMAT(c.created_at,'%Y-%m-%d %H:%i:%s') as created_at
        from users u
             inner join entities e on u.id = e.user_id
             inner join personalities p on e.id = p.entity_id
             inner join (select created_at, user_id from clickers where click_id = #{id} and click_type ='userDocking' ) c on u.id = c.user_id
        where u.authentication='1'
        union all
        select  u.id,u.login,u.nickname,u.lastname,u.phone,en.name as "company_name",u.mail,en.credit_code as "idNumber",e.entity_type,DATE_FORMAT(c.created_at,'%Y-%m-%d %H:%i:%s') as created_at
        from users  u
             inner join entities e on u.id = e.user_id
             inner join enterprises en on e.id = en.entity_id
             inner join (select created_at,user_id from clickers where click_id = #{id} and click_type ='userDocking') c on u.id = c.user_id
       where u.enterprise_certification='1') t
       order by t.created_at
    </select>



    <select id="selectAchievementsByArea" parameterType="com.microservices.dms.achievementLibrary.domain.AchQueryVo" resultType="com.microservices.dms.achievementLibrary.domain.Achievements">
        <if test='source=="2"'>
            select a.id, IFNULL(a.show_ach_name,a.achievement_name)  as "showAchievementName", a.achievement_name, a.summary, a.create_time, a.owner_name, a.tags, a.field_1, a.field_2, a.field_3, a.source, a.source_id,
            c.name as "field1Name",u.login  as "userLogin",
            IFNULL(u.nickname,a.owner_name) as "userNickName",
            ue.gender as "gender",c.remark as "filedDesc",
            (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = a.id)  as watcherSum,
            (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = a.id) as favoriteSum,
            (select IFNULL(count(*), 0) from clickers where click_type = 'Achievements' and click_id = a.id) as clickSum,
            (select IFNULL(count(*), 0) from clickers where click_type = 'userDocking' and click_id = a.id) as dockSum
            from achievements a
            left join users u on a.owner_name = u.login
            left join user_extensions ue on u.id = ue.user_id
            left join categories c on a.field_1 = c.id
            <where>
               and a.source !='1'
                <if test="achievementName != null  and achievementName != ''"> and a.achievement_name like concat('%', #{achievementName}, '%')</if>
                <if test="showAchievementName != null  and showAchievementName != ''"> and a.show_ach_name like concat('%', #{showAchievementName}, '%')</if>
                <if test='areaQuery != null  and areaQuery != "" '>
                    and (a.field_1 = #{areaQuery} or a.field_2 = #{areaQuery} or a.field_3 = #{areaQuery})
                </if>
            </where>
            order by a.create_time desc
        </if>
        <if test='source=="1"'>
            select a.id,IFNULL(a.show_ach_name,a.achievement_name) as "showAchievementName", a.achievement_name, a.summary, a.create_time, a.owner_name, a.tags, a.field_1, a.field_2, a.field_3, a.source, a.source_id,
            c.name as "field1Name",u.login  as "userLogin",
            IFNULL(u.nickname,a.owner_name) as "userNickName",
            ue.gender as "gender",c.remark as "filedDesc",
            (select IFNULL(count(*), 0) from favorites where favorite_type = 'Achievements' and favorite_id = a.id)  as watcherSum,
            (select IFNULL(count(*), 0) from watchers where watchable_type = 'Achievements' and watchable_id = a.id) as favoriteSum,
            (select IFNULL(count(*), 0) from clickers where click_type = 'Achievements' and click_id = a.id) as clickSum,
            (select IFNULL(count(*), 0) from clickers where click_type = 'userDocking' and click_id = a.id) as dockSum
            from achievements a
            left join users u on a.owner_name = u.login
            left join user_extensions ue on u.id = ue.user_id
            left join project_categories c on a.field_1 = c.id
            <where>
                <if test="achievementName != null  and achievementName != ''"> and a.achievement_name like concat('%', #{achievementName}, '%')</if>
                <if test="showAchievementName != null  and showAchievementName != ''"> and a.show_ach_name like concat('%', #{showAchievementName}, '%')</if>
                <if test="source != null  and source != ''"> and a.source = #{source}</if>
                <if test='areaQuery != null  and areaQuery != "" '>
                    and (a.field_1 = #{areaQuery} or a.field_2 = #{areaQuery} or a.field_3 = #{areaQuery})
                </if>
            </where>
            order by create_time desc
        </if>
    </select>

    <select id="getAllAreas" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select r.name as "name",GROUP_CONCAT( r.source SEPARATOR ',') as "result"
        from (
                 select name,CONCAT(id, '_', '2')as source from categories
                 union all
                 select name,CONCAT(id, '_', '1') as source from project_categories
             )r
        <where>
            <if test="areaName != null  and areaName != ''">and r.name = #{areaName}</if></where>
        GROUP BY r.name
    </select>

    <update id="updateRelationAchievements">
        <if test='source != null and source == "1"'>
            update project_resource_library set is_transferred_to_achievement='0' ,transfer_date=null where id = #{sourceId}
        </if>
        <if test='source != null and source == "2"'>
            update task_resource_library set is_transferred_to_results_library='0' ,transfer_to_results_date=null where id = #{sourceId}
        </if>
        <if test='source != null and source == "3"'>
            update competition_resource_library set is_transferred_to_result_library='0' ,transferred_to_result_library_time=null where id = #{sourceId}
        </if>
        <if test='source != null and source == "4"'>
            update school_enterprise_achievements set status=null ,review_comments=null,review_date=null,reviewer=null  where id = #{sourceId}
        </if>
    </update>

    <select id="getDockingUserInfoList" resultType="java.util.Map">
        select u.id,u.login,u.nickname,p.name as "lastname,",u.phone,u.company_name,u.mail,p.card as "idNumber",e.entity_type,DATE_FORMAT(ck.created_at,'%Y-%m-%d %H:%i:%s') as created_at
        from clickers ck
                 inner join users u on ck.user_id = u.id
                 left join entities e on u.id = e.user_id
                 left join personalities p on e.id = p.entity_id
        where click_type='userDocking' and click_id = #{id}
    </select>
</mapper>