<?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="org.dromara.zjk.mapper.ZjkExpertChartMapper">

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkExpertChart">
        <id property="id" column="ID" jdbcType="VARCHAR"/>
        <result property="expertId" column="expert_id" jdbcType="BIGINT"/>
        <result property="userId" column="user_id" jdbcType="BIGINT"/>
        <result property="scorePerformWork" column="score_perform_work" jdbcType="OTHER"/>
        <result property="scorePaper" column="score_paper" jdbcType="OTHER"/>
        <result property="scorePatent" column="score_patent" jdbcType="OTHER"/>
        <result property="scoreWorkYear" column="score_work_year" jdbcType="OTHER"/>
        <result property="scoreAwards" column="score_awards" jdbcType="OTHER"/>
        <result property="scoreEvaluate" column="score_evaluate" jdbcType="OTHER"/>
        <result property="scoreJoinTime" column="score_join_time" jdbcType="OTHER"/>
        <result property="scoreCredit" column="score_credit" jdbcType="OTHER"/>
        <result property="createDept" column="create_dept" jdbcType="VARCHAR"/>
        <result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
        <result property="createBy" column="CREATE_BY" jdbcType="BIGINT"/>
        <result property="createTime" column="CREATE_TIME" jdbcType="DATE"/>
        <result property="updateBy" column="UPDATE_BY" jdbcType="BIGINT"/>
        <result property="updateTime" column="UPDATE_TIME" jdbcType="DATE"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,expert_id,user_id,
        score_perform_work,score_paper,score_patent,
        score_work_year,score_awards,score_evaluate,
        score_join_time,score_credit,create_dept,
        tenant_id,CREATE_BY,CREATE_TIME,
        UPDATE_BY,UPDATE_TIME
    </sql>
    <delete id="removeAll">
        delete
        from zjk_expert_chart
    </delete>
    <select id="getHandleData" resultType="org.dromara.zjk.domain.ZjkExpertChart">
        select current_timestamp as           createTime,
               cc.expert_id,
               cc.user_id,
               cc.tenant_id,
               coalesce(ee.score_evaluate, 0) score_evaluate,
               case
                   when aa.paperNum > 0 and aa.paperNum <![CDATA[ <= ]]> 1 then 3
                   when aa.paperNum >= 2 and aa.paperNum <![CDATA[ <= ]]> 5 then 4
                   when aa.paperNum > 5 then 5
                   else 0 end    as           score_paper,
               case
                   when bb.patentNum > 0 and bb.patentNum <![CDATA[ <= ]]> 1 then 3
                   when bb.patentNum >= 2 and bb.patentNum <![CDATA[ <= ]]> 5 then 4
                   when bb.patentNum > 5 then 5
                   else 0 end    as           score_patent,
               case
                   when ff.awardsNum = 1 then 1
                   when ff.awardsNum >= 2 and ff.awardsNum <![CDATA[ <= ]]> 3 then 2
                   when ff.awardsNum >= 4 and ff.awardsNum <![CDATA[ <= ]]> 5 then 3
                   when ff.awardsNum >= 6 and ff.awardsNum <![CDATA[ <= ]]> 8 then 4
                   when ff.awardsNum > 8 then 5
                   else 0 end    as           score_awards,
               case
                   when dd.joinNum = 1 then 1
                   when dd.joinNum >= 2 and dd.joinNum <![CDATA[ <= ]]> 4 then 2
                   when dd.joinNum >= 5 and dd.joinNum <![CDATA[ <= ]]> 8 then 3
                   when dd.joinNum >= 9 and dd.joinNum <![CDATA[ <= ]]> 10 then 4
                   when dd.joinNum > 10 then 5
                   else 0 end    as           score_join_time,
               case
                   when cc.num = 1 then 3
                   when cc.num = 2 then 4
                   when cc.num = 3 then 5
                   else 0 end    as           score_perform_work,
               case
                   when cc.wyear <![CDATA[ < ]]> 5 then 2
                   when 5 <![CDATA[ <= ]]> cc.wyear and cc.wyear <![CDATA[ < ]]> 10 then 3
                   when 11 <![CDATA[ <= ]]> cc.wyear and cc.wyear <![CDATA[ < ]]> 20 then 4
                   when cc.wyear > 20 then 5
                   else 0 end    as           score_work_year,
               case
                   when ce.credit_name = '优秀' then 5
                   when ce.credit_name = '良好' then 4
                   when ce.credit_name = '一般' then 3
                   when ce.credit_name = '较差' then 2
                   when ce.credit_name = '差' then 1
                   else 0 end    as           score_credit
        from (SELECT ze.expert_id,
                     ze.user_id,
                     ze.tenant_id,
                     array_length(string_to_array(ze.perform_work_id, ','), 1) AS num,
                     coalesce(ze.work_professial_year, 0)                      as wyear
              from zjk_expert ze
              where ze.delete_flag = 0
                and ze.status = 2) cc
                 left join (select expert_id, coalesce(count(*), 0) paperNum
                            from zjk_expert_paper
                            where del_flag = 0
                            group by expert_id) aa
                           on cc.expert_id = aa.expert_id
                 left join (select expert_id, coalesce(count(*), 0) patentNum
                            from zjk_expert_patent
                            where del_flag = 0
                            group by expert_id) bb on cc.expert_id = bb.expert_id
                 left join (select expert_id, coalesce(count(*), 0) joinNum
                            from zjk_expert_item
                            where accept_status = 1
                              and leave_status = 0
                              and is_sure = 1
                            group by expert_id) dd on cc.expert_id = dd.expert_id
                 left join (select expert_id, coalesce(count(*), 0) awardsNum
                            from zjk_expert_awards
                            where del_flag = 0
                            group by expert_id) ff on cc.expert_id = ff.expert_id
                 left join (select round((sum(bus_level_score) + sum(work_attitude_score) + sum(right_approval_score)) /
                                         3 / count(1)) score_evaluate,
                                   evaluate_user
                            from zjk_evaluate
                            group by evaluate_user) ee on cc.expert_id = ee.evaluate_user
                 left join (select zcu.expert_id, lv.credit_name
                            from zjk_credit_user zcu
                                     left join zjk_credit_lv lv on zcu.lv_id = lv.id) ce on cc.expert_id = ce.expert_id

    </select>
    <select id="getSameSchoolExperts" resultType="org.dromara.zjk.domain.dto.SimpleExpertDto">
        select expert_id, expert_name
        from zjk_expert
        where status = 2
          and graduate_school = (select graduate_school from zjk_expert where expert_id = #{userId})
          and expert_id != #{userId}
    </select>
    <select id="getSameUnitExperts" resultType="org.dromara.zjk.domain.dto.SimpleExpertDto">
        select expert_id, expert_name
        from zjk_expert
        where status = 2
          and work_unit = (select work_unit from zjk_expert where expert_id = #{userId})
          and expert_id != #{userId}

    </select>
    <select id="getSameProExperts" resultType="org.dromara.zjk.domain.dto.SimpleExpertDto">
        select distinct bb.expert_id, bb.expert_name
        from (select expert_id, unnest(string_to_array(perform_work_id, ',')) wid
              from zjk_expert
              where expert_id = #{userId}) aa
                 left join

             (select expert_id, expert_name, unnest(string_to_array(perform_work_id, ',')) wid from zjk_expert) bb
             on bb.wid = aa.wid
        where bb.expert_id != #{userId}
    </select>
    <select id="getExpertMajor" resultType="java.lang.String">
        select string_agg(major, ',')
        from zjk_expert_major
        where expert_id = #{expertId}
        group by expert_id

    </select>
    <select id="getProSkills" resultType="org.dromara.zjk.domain.dto.ProGetDto">
        select professional as name, create_time as getTime
        from zjk_expert_professional
        where expert_id = #{expertId}
    </select>
    <select id="getCreditTitle" resultType="java.lang.String">
        select lv.credit_name
        from zjk_credit_user zcu
                 left join zjk_credit_lv lv on zcu.lv_id = lv.id
        where zcu.expert_id =#{expertId} limit 1
    </select>
</mapper>
