<?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.resourceLibrary.mapper.ExpertResourceLibraryMapper">

    <resultMap type="ExpertResourceLibrary" id="ExpertResourceLibraryResult">
        <result property="id" column="id"/>
        <result property="expertId" column="expert_id"/>
        <result property="userId" column="user_id"/>
        <result property="expertName" column="expert_name"/>
        <result property="highestDegree" column="highest_degree"/>
        <result property="graduatedFrom" column="graduated_from"/>
        <result property="idNumber" column="id_number"/>
        <result property="major" column="major"/>
        <result property="workplace" column="workplace"/>
        <result property="phone" column="phone"/>
        <result property="workplaceType" column="workplace_type"/>
        <result property="expertEmail" column="expert_email"/>
        <result property="expertSummary" column="expert_summary"/>
        <result property="expertDetail" column="expert_detail"/>
        <result property="professionalTitle" column="professional_title"/>
        <result property="expertType" column="expert_type"/>
        <result property="expertDomain" column="expert_domain"/>
        <result property="titleRank" column="title_rank"/>
        <result property="status" column="status"/>
        <result property="reviewAreaOne" column="review_area_one"/>
        <result property="reviewAreaTwo" column="review_area_two"/>
        <result property="reviewAreaThree" column="review_area_three"/>
        <result property="isDelete" column="is_delete"/>
        <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="sortNo" column="sort_no"/>
        <result property="gender" column="gender"/>
    </resultMap>

    <sql id="selectExpertResourceLibraryVo">
        select id,
               user_id,
               expert_id,
               gender,
               sort_no,
               expert_name,
               highest_degree,
               graduated_from,
               id_number,
               major,
               workplace,
               phone,
               workplace_type,
               expert_email,
               expert_summary,
               expert_detail,
               professional_title,
               expert_type,
               expert_domain,
               title_rank,
               status,
               review_area_one,
               review_area_two,
               review_area_three,
               is_delete,
               create_by,
               create_time,
               update_by,
               update_time,images, attachments,gender,sort_no,status
        from expert_resource_library
    </sql>

    <select id="selectExpertResourceLibraryList" parameterType="ExpertResourceLibrary"
            resultType="com.microservices.dms.resourceLibrary.domain.ExpertResourceLibrary">
        select * from ( select erl.id, e.id as expert_id,e.user_id, e.expert_name, e.highest_degree, e.graduated_from,
        u.nickname as "userNickName", IFNULL(erl.gender, ue.gender) as "gender",
        e.id_number,images, attachments,
        e.major, e.workplace, e.phone,
        CONCAT_WS(',',resume, title_certificate,academic_achievements,honors) AS fullFileIds,
        e.workplace_type, e.expert_email, erl.expert_summary, erl.expert_detail, e.professional_title, e.expert_type,
        expert_domain,u.authentication as expertAuth,
        e.title_rank, erl.status, e.review_area_one, e.review_area_two, e.review_area_three, e.is_delete, erl.create_by,
        erl.create_time,
        (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 1 and `status` in (1,2)) as taskAuditCount,
        (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 2 and `status` in (1,2)) as competitionAuditCount,
        erl.update_by, erl.update_time,erl.sort_no
        from experts e
        inner join users u on e.user_id = u.id
        inner join user_extensions ue on u.id = ue.user_id
        left join expert_resource_library erl on e.id = erl.expert_id
        <where>
            e.is_delete='0' and e.status=1
            <if test="userId != null ">and e.user_id = #{userId}</if>
            <if test="gender != null ">and ue.gender = #{gender}</if>
            <if test="expertName != null  and expertName != ''">and e.expert_name like concat('%', #{expertName}, '%')
            </if>
            <if test="highestDegree != null  and highestDegree != ''">and highest_degree = #{highestDegree}</if>
            <if test="graduatedFrom != null  and graduatedFrom != ''">and graduated_from = #{graduatedFrom}</if>
            <if test="idNumber != null  and idNumber != ''">and id_number = #{idNumber}</if>
            <if test="major != null  and major != ''">and major = #{major}</if>
            <if test="workplace != null  and workplace != ''">and workplace = #{workplace}</if>
            <if test="phone != null  and phone != ''">and phone = #{phone}</if>
            <if test="workplaceType != null  and workplaceType != ''">and workplace_type = #{workplaceType}</if>
            <if test="expertEmail != null  and expertEmail != ''">and expert_email = #{expertEmail}</if>
            <if test="expertSummary != null  and expertSummary != ''">and expert_summary = #{expertSummary}</if>
            <if test="expertDetail != null  and expertDetail != ''">and expert_detail = #{expertDetail}</if>
            <if test="professionalTitle != null  and professionalTitle != ''">and professional_title =
                #{professionalTitle}
            </if>
            <if test="expertType != null  and expertType != ''">and expert_type = #{expertType}</if>
            <if test="expertDomain != null  and expertDomain != ''">and expert_domain = #{expertDomain}</if>
            <if test="titleRank != null  and titleRank != ''">and title_rank = #{titleRank}</if>
            <if test="status != null ">and status = #{status}</if>
            <if test="reviewAreaOne != null  and reviewAreaOne != ''">and review_area_one = #{reviewAreaOne}</if>
            <if test="reviewAreaTwo != null  and reviewAreaTwo != ''">and review_area_two = #{reviewAreaTwo}</if>
            <if test="reviewAreaThree != null  and reviewAreaThree != ''">and review_area_three = #{reviewAreaThree}
            </if>
            <if test="isDelete != null ">and is_delete = #{isDelete}</if>
        </where>
        ) tmp where tmp.taskAuditCount > 0 OR tmp.competitionAuditCount > 0 order by tmp.status
    </select>

    <select id="selectExpertResourceLibraryList2" parameterType="ExpertResourceLibrary"
            resultMap="ExpertResourceLibraryResult">
        select erl.id, e.id as expert_id,e.user_id, e.expert_name, e.highest_degree, e.graduated_from, e.id_number,
        e.major, e.workplace, e.phone,u.nickname as "userNickName", IFNULL(erl.gender, ue.gender) as "gender",
        e.workplace_type, e.expert_email, erl.expert_summary, erl.expert_detail, e.professional_title, e.expert_type,
        expert_domain,images, attachments,
        e.title_rank, erl.status, e.review_area_one, e.review_area_two, e.review_area_three, e.is_delete, erl.create_by,
        erl.create_time,
        (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 1 and `status` in (1,2)) as taskAuditCount,
        (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 2 and `status` in (1,2)) as competitionAuditCount,
        (select count(*) as watchSum from watchers where watchable_type = 'Experts' and watchable_id = erl.id) as watcherSum,
        (select count(*) as favoriteSum from favorites where favorite_type = 'Experts' and favorite_id = erl.id) as favoriteSum,
        erl.update_by, erl.update_time,erl.sort_no
        from expert_resource_library erl
        left join experts e on e.id = erl.expert_id
        left join users u on e.user_id = u.id
        left join user_extensions ue on u.id = ue.user_id
        <where>
            and erl.status ='1'
            <if test="userId != null ">and user_id = #{userId}</if>
            <if test="expertName != null  and expertName != ''">and erl.expert_name like concat('%', #{expertName}, '%')
            </if>
            <if test="highestDegree != null  and highestDegree != ''">and erl.highest_degree = #{highestDegree}</if>
            <if test="graduatedFrom != null  and graduatedFrom != ''">and erl.graduated_from = #{graduatedFrom}</if>
            <if test="idNumber != null  and idNumber != ''">and erl.id_number = #{idNumber}</if>
            <if test="major != null  and major != ''">and erl.major = #{major}</if>
            <if test="workplace != null  and workplace != ''">and erl.workplace = #{workplace}</if>
            <if test="phone != null  and phone != ''">and erl.phone = #{phone}</if>
            <if test="workplaceType != null  and workplaceType != ''">and erl.workplace_type = #{workplaceType}</if>
            <if test="expertEmail != null  and expertEmail != ''">and erl.expert_email = #{expertEmail}</if>
            <if test="expertSummary != null  and expertSummary != ''">and expert_summary = #{expertSummary}</if>
            <if test="expertDetail != null  and expertDetail != ''">and expert_detail = #{expertDetail}</if>
            <if test="professionalTitle != null  and professionalTitle != ''">and erl.professional_title =
                #{professionalTitle}
            </if>
            <if test="expertType != null  and expertType != ''">and erl.expert_type = #{expertType}</if>
            <if test="expertDomain != null  and expertDomain != ''">and expert_domain = #{expertDomain}</if>
            <if test="titleRank != null  and titleRank != ''">and erl.title_rank = #{titleRank}</if>
            <if test="status != null ">and erl.status = #{status}</if>
            <if test="areaQuery != null  and areaQuery != ''">
                and (erl.review_area_one = #{areaQuery} or erl.review_area_two = #{areaQuery} or erl.review_area_three = #{areaQuery})
            </if>
            <if test="isDelete != null ">and erl.is_delete = #{isDelete}</if>
        </where>
        order by erl.sort_no
    </select>

    <select id="selectExpertResourceLibraryById" parameterType="Long" resultMap="ExpertResourceLibraryResult">
        <include refid="selectExpertResourceLibraryVo"/>
        where id = #{id}
    </select>

    <select id="auditExpertCount" resultType="java.lang.Long">
        select COUNT(*)
        from (select (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 1 and `status` in (1,2)) as taskAuditCount,
                     (select COUNT(distinct container_id)
                      from task_expert
                      where expert_id = e.id
                        and container_type = 2 and `status` in (1,2))                                                       as competitionAuditCount
              from experts e where is_delete = '0' and status = 1) tmp
        where tmp.taskAuditCount > 0
           OR tmp.competitionAuditCount > 0
    </select>

    <select id="competitionAuditCount" resultType="java.lang.Long">
        select IFNULL(SUM(competitionAuditCount), 0)
        from (select COUNT(distinct container_id) as competitionAuditCount
              from task_expert te
                       join experts e on e.id = te.expert_id and te.container_type = 2 and te.`status` in (1, 2)) tmp
    </select>
    <select id="taskAuditCount" resultType="java.lang.Long">
        select IFNULL(SUM(taskAuditCount), 0)
        from (select COUNT(distinct container_id) as taskAuditCount
              from task_expert te
                       join experts e on e.id = te.expert_id and te.container_type = 1 and te.`status` in (1, 2)) tmp
    </select>
    <select id="auditCompetitionAuditExpertCount" resultType="java.lang.Long">
        select count(*)
        from expert_resource_library erl
                 join
             (select expert_id from task_expert where container_type = 2 and `status` in (1,2) group by expert_id having count(*) > 0) as tmp
             on erl.expert_id = tmp.expert_id
    </select>
    <select id="expertResourceCount" resultType="java.lang.Long">
        select count(*)
        from expert_resource_library
    </select>
    <select id="auditTaskAuditExpertCount" resultType="java.lang.Long">
        select count(*)
        from expert_resource_library erl
                 join
             (select expert_id from task_expert where container_type = 1 and `status` in (1,2) group by expert_id having count(*) > 0) as tmp
             on erl.expert_id = tmp.expert_id
    </select>

    <insert id="insertExpertResourceLibrary" parameterType="ExpertResourceLibrary" useGeneratedKeys="true"
            keyProperty="id">
        insert into expert_resource_library
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="expertId != null">expert_id,</if>
            <if test="expertName != null and expertName != ''">expert_name,</if>
            <if test="highestDegree != null and highestDegree != ''">highest_degree,</if>
            <if test="graduatedFrom != null">graduated_from,</if>
            <if test="idNumber != null">id_number,</if>
            <if test="major != null">major,</if>
            <if test="workplace != null and workplace != ''">workplace,</if>
            <if test="phone != null and phone != ''">phone,</if>
            <if test="workplaceType != null and workplaceType != ''">workplace_type,</if>
            <if test="expertEmail != null">expert_email,</if>
            <if test="expertSummary != null">expert_summary,</if>
            <if test="expertDetail != null">expert_detail,</if>
            <if test="professionalTitle != null and professionalTitle != ''">professional_title,</if>
            <if test="expertType != null and expertType != ''">expert_type,</if>
            <if test="expertDomain != null and expertDomain != ''">expert_domain,</if>
            <if test="titleRank != null and titleRank != ''">title_rank,</if>
            <if test="status != null">status,</if>
            <if test="reviewAreaOne != null and reviewAreaOne != ''">review_area_one,</if>
            <if test="reviewAreaTwo != null">review_area_two,</if>
            <if test="reviewAreaThree != null">review_area_three,</if>
            <if test="isDelete != null">is_delete,</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="sortNo != null">sort_no,</if>
            <if test="gender != null">gender,
            </if>
            <if test="images != null">images,</if>
            <if test="attachments != null">attachments,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="expertId != null">#{expertId},</if>
            <if test="expertName != null and expertName != ''">#{expertName},</if>
            <if test="highestDegree != null and highestDegree != ''">#{highestDegree},</if>
            <if test="graduatedFrom != null">#{graduatedFrom},</if>
            <if test="idNumber != null">#{idNumber},</if>
            <if test="major != null">#{major},</if>
            <if test="workplace != null and workplace != ''">#{workplace},</if>
            <if test="phone != null and phone != ''">#{phone},</if>
            <if test="workplaceType != null and workplaceType != ''">#{workplaceType},</if>
            <if test="expertEmail != null">#{expertEmail},</if>
            <if test="expertSummary != null">#{expertSummary},</if>
            <if test="expertDetail != null">#{expertDetail},</if>
            <if test="professionalTitle != null and professionalTitle != ''">#{professionalTitle},</if>
            <if test="expertType != null and expertType != ''">#{expertType},</if>
            <if test="expertDomain != null and expertDomain != ''">#{expertDomain},</if>
            <if test="titleRank != null and titleRank != ''">#{titleRank},</if>
            <if test="status != null">#{status},</if>
            <if test="reviewAreaOne != null and reviewAreaOne != ''">#{reviewAreaOne},</if>
            <if test="reviewAreaTwo != null">#{reviewAreaTwo},</if>
            <if test="reviewAreaThree != null">#{reviewAreaThree},</if>
            <if test="isDelete != null">#{isDelete},</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="sortNo != null">#{sortNo},</if>
            <if test="gender != null">#{gender},</if>
            <if test="images != null">#{images},</if>
            <if test="attachments != null">#{attachments},</if>
        </trim>
    </insert>

    <update id="updateExpertResourceLibrary" parameterType="ExpertResourceLibrary">
        update expert_resource_library
        <trim prefix="SET" suffixOverrides=",">
            <if test="userId != null">user_id = #{userId},</if>
            <if test="expertId != null">expert_id = #{expertId},</if>
            <if test="expertName != null and expertName != ''">expert_name = #{expertName},</if>
            <if test="highestDegree != null and highestDegree != ''">highest_degree = #{highestDegree},</if>
            <if test="graduatedFrom != null">graduated_from = #{graduatedFrom},</if>
            <if test="idNumber != null">id_number = #{idNumber},</if>
            <if test="major != null">major = #{major},</if>
            <if test="workplace != null and workplace != ''">workplace = #{workplace},</if>
            <if test="phone != null and phone != ''">phone = #{phone},</if>
            <if test="workplaceType != null and workplaceType != ''">workplace_type = #{workplaceType},</if>
            <if test="expertEmail != null">expert_email = #{expertEmail},</if>
            <if test="expertSummary != null">expert_summary = #{expertSummary},</if>
            <if test="expertDetail != null">expert_detail = #{expertDetail},</if>
            <if test="professionalTitle != null and professionalTitle != ''">professional_title =
                #{professionalTitle},
            </if>
            <if test="expertType != null and expertType != ''">expert_type = #{expertType},</if>
            <if test="expertDomain != null and expertDomain != ''">expert_domain = #{expertDomain},</if>
            <if test="titleRank != null and titleRank != ''">title_rank = #{titleRank},</if>
            <if test="status != null">status = #{status},</if>
            <if test="reviewAreaOne != null and reviewAreaOne != ''">review_area_one = #{reviewAreaOne},</if>
            <if test="reviewAreaTwo != null">review_area_two = #{reviewAreaTwo},</if>
            <if test="reviewAreaThree != null">review_area_three = #{reviewAreaThree},</if>
            <if test="isDelete != null">is_delete = #{isDelete},</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="sortNo != null">sort_no = #{sortNo},</if>
            <if test="gender != null">gender = #{gender},
            <if test="images != null">images = #{images},</if>
            <if test="attachments != null">attachments = #{attachments},</if></if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteExpertResourceLibraryById" parameterType="Long">
        delete
        from expert_resource_library
        where id = #{id}
    </delete>

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


    <select id="getTitleRankStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select count(1) as "value", title_rank as "name"
        from experts
        where is_delete = '0'
          and status = 1
        group by title_rank
    </select>

    <select id="getExpertTypeStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select count(1) as "value", expert_type as "name"
        from experts
        where is_delete = '0'
          and status = 1
        group by expert_type
    </select>

    <select id="getHighestDegreeStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select count(1) as "value", highest_degree as "name"
        from experts
        where is_delete = '0'
          and status = 1
        group by highest_degree
    </select>

    <select id="getWorkplaceTypeStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select count(1) as "value", workplace_type as "name"
        from experts
        where is_delete = '0'
          and status = 1
        group by workplace_type
    </select>

    <select id="getAuthenticationStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select b.expertTotal as "total", d.expertAuth as "value"
        from (
                 (select count(1) as "expertTotal" from experts where is_delete = '0' and status = 1) b,
             (select count(1) as "expertAuth"
              from experts e
                       inner join users u on e.user_id = u.id
              where e.is_delete = '0'
                and e.status = 1
                and (u.authentication = true or u.enterprise_certification='1')) d
                 )
    </select>

    <select id="getExpertTotal" resultType="long">
        select count(1) as "value"
        from experts
        where is_delete = '0'
          and status = 1
    </select>

    <select id="getExpertTotalByYear" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select DATE_FORMAT(created_on, '%Y') as 'name', count(*) as 'value'
        from experts
        where is_delete = '0'
          and status = 1
        group by DATE_FORMAT(created_on, '%Y')
        order by DATE_FORMAT(created_on, '%Y')
    </select>

    <select id="getReviewAreasStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select a.domain_value as "name", count(a.domain_value) as "value"
        from (select review_area_one as domain_value
              from experts
              where is_delete = '0'
                and status = 1
                and review_area_one is not null
                and review_area_one != ''
              union all
              select review_area_two as domain_value
              from experts
              where is_delete = '0'
                and status = 1
                and review_area_two is not null
                and review_area_two != ''
              union all
              select review_area_three as domain_value
              from experts
              where is_delete = '0'
                and status = 1
                and review_area_three is not null
                and review_area_three != '') a
        group by a.domain_value
        order by count(a.domain_value) desc
        limit 0,9
    </select>

    <select id="getExpertAduit" resultType="com.microservices.dms.achievementLibrary.domain.ExpertTotallVo">
        select tmp.expert_name                                  as "name",
               tmp.taskAuditCount                               as "taskAuditSum",
               tmp.competitionAuditCount                        as "competitionAuditSum",
               (tmp.taskAuditCount + tmp.competitionAuditCount) as "total"
        from (select e.expert_name,e.created_on,
                     (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 1  and `status` in (1,2)) as taskAuditCount,
                     (select COUNT(distinct container_id) from task_expert where expert_id = e.id and container_type = 2 and `status` in (1,2))    as competitionAuditCount
              from experts e
                       left join users u on e.user_id = u.id
              where e.is_delete = '0'
                and e.status = 1) tmp
         order by (tmp.taskAuditCount + tmp.competitionAuditCount) desc,tmp.created_on desc
        limit 0,12
    </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 = 'Experts' and click_id = #{id}) a,
                     (select count(1) as watchSum from watchers where watchable_type = 'Experts' and watchable_id = #{id}) b,
                     (select count(1) as searchSum from searchers where search_type = 'Experts' and search_id = #{id}) c,
                     (select count(1) as favoriteSum from favorites where favorite_type = 'Experts' and favorite_id = #{id}) d,
                     (select count(1) as downloadSum from downloads where download_type = 'Experts' and download_id = #{id}) e
                 )
    </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 = 'Experts' and watchable_id = #{id} and user_id=#{userId}) b,
                     (select count(1) as favoriteSum from favorites where favorite_type = 'Experts' 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="getMemoAduit" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select a.auditSum            as "value",
               b.allSum              as "total",
               a.auditSum / b.allSum as "result",
               c.currentYearSum      as "value2"
        from (
                 (select count(1) as "auditSum"
                  from memos
                  where destroy_status is null and hidden = 0 and published_at is not null) a,
                         (select count(1) as "allSum" from memos) b,
                     (select count(1) as "currentYearSum"
                      from memos
                      where destroy_status is null
                        and hidden = 0
                        and published_at is not null
                        and DATE_FORMAT(published_at, '%Y') = YEAR(NOW())) c
                                                                                                                               )
    </select>

    <select id="getIsOriginalStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select is_original as "key",count(1) as "value" from memos where destroy_status is null and hidden=0 and published_at is not null
        group by is_original
    </select>

    <select id="getMemoTotalByYear" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select DATE_FORMAT(published_at, '%Y') as "key", COUNT(1) as "value"
        from memos
        where destroy_status is null
          and hidden = 0
          and published_at is not null
        group by DATE_FORMAT(published_at, '%Y')
        order by DATE_FORMAT(published_at, '%Y')
    </select>

    <select id="getForumSectionStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select a.ancestry as "key", fs.title as "name", a.tmp as "value"
        from (
             select   IFNULL(f.ancestry, m.forum_section_id) as"ancestry",count(1)  as "tmp" from memos m
            inner join forum_sections f on m.forum_section_id = f.id
             where m.destroy_status is null
               and m.hidden = 0
               and m.published_at is not null
             group by IFNULL(f.ancestry, m.forum_section_id)
             ) a
        left join forum_sections fs on fs.id = a.ancestry
        ORDER BY a.ancestry DESC
    </select>

    <select id="getAddMemoStatistic" resultType="com.microservices.dms.achievementLibrary.domain.KeyValueVo">
        select a.currentMonth as "name", a.tag_id as "key", sum(a.countTag) as "value"
        from (select DATE_FORMAT(published_at, '%Y-%m') as "currentMonth", tag_id, count(1) as "countTag"
        from memos
        where destroy_status is null and hidden=0 and published_at is not null
        and  published_at  &gt;= #{s} and published_at &lt;=  #{e}
        group by DATE_FORMAT(published_at, '%Y-%m'), tag_id
        ) a
        group by a.currentMonth, a.tag_id
        order by a.tag_id, a.currentMonth asc
    </select>


    <select id="getTop5Memos"  resultType="com.microservices.dms.achievementLibrary.domain.MemoTotalVo">
        select id as "id",subject as "subject",published_at as "publishedAt",viewed_count as "viewedCount",praises_count as "praisesCount",replies_count as "repliesCount",(viewed_count+praises_count+replies_count) as "behavioreSum"
        from memos where destroy_status is null and hidden=0 and published_at is not null and DATE_FORMAT(published_at, '%Y') = YEAR (NOW())
        order by (viewed_count+praises_count+replies_count) desc
        limit 0,5
    </select>

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

    <select id="getExpertInfoById" resultType="com.microservices.dms.resourceLibrary.domain.vo.ExpertAttachmentVo">
        select honors honorsAttachments, title_certificate as titleCertificateAttachments,
               resume resumeAttachments, academic_achievements academicAchievementsAttachments
        from experts
        where id = #{expertId}
    </select>
    <select id="getAttachmentInfo" resultType="java.util.Map">
        select file_name,id,unique_file_name from busi_attachments where id = #{id} limit 1
    </select>


    <select id="getAttachments" resultType="com.microservices.dms.resourceLibrary.domain.vo.KeyValVo">
        select id as v, file_name as k from busi_attachments where FIND_IN_SET (id,#{fullFileIds})
    </select>
</mapper>