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

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkEvaluate">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="evaluateUser" column="evaluate_user" jdbcType="BIGINT"/>
        <result property="expertPost" column="expert_post" jdbcType="VARCHAR"/>
        <result property="itemId" column="item_id" jdbcType="VARCHAR"/>
        <result property="itemStage" column="item_stage" jdbcType="BIGINT"/>
        <result property="busLevelScore" column="bus_level_score" jdbcType="NUMERIC"/>
        <result property="workAttitudeScore" column="work_attitude_score" jdbcType="NUMERIC"/>
        <result property="rightApprovalScore" column="right_approval_score" jdbcType="NUMERIC"/>
        <result property="expertComment" column="expert_comment" jdbcType="VARCHAR"/>
        <result property="evaluateFile" column="evaluate_file" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="OTHER"/>
        <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"/>
        <result property="createDept" column="create_dept" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,evaluate_user,expert_post,
        item_id,item_stage,bus_level_score,
        work_attitude_score,right_approval_score,expert_comment,
        evaluate_file,status,tenant_id,
        create_by,create_time,update_by,
        update_time,create_dept
    </sql>
    <select id="updateOtherInfoToUnPresent" resultType="java.lang.String">
        update zjk_evaluate
        set bus_level_score=0,
            work_attitude_score=0,
            right_approval_score=0,
            is_present=0
        where is_present = 1
          and source_role != 2
          and item_id = #{itemId}
          and item_stage = #{itemStage}
        and  evaluate_user = #{evaluateUser}
            RETURN id
    </select>
    <select id="getListToZj" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        select * from (
        select ze.id,case when ze.status = 1 then '已评价' else '未评价' end as status,
        COALESCE(ze.status,0)  as flag,
        zee.expert_name as evaluateUser,
        zee.work_unit,
        zee.duty as expertPost,
        zp.product_name,
        ze.create_time,
        zee.expert_id,
        aa.item_Id as itemId,
        aa.review_sn as itemStage,
        sdd.dict_label,
        aa.expert_id as userId,
        ze.expert_comment
        from (select zrp.item_id, zrp.review_sn, zei.expert_id
        from zjk_review_phase zrp
        left join zjk_expert_item zei on zrp.id = zei.review_id and zrp.effective=1
        <where>
            zei.accept_status = '1'
            and zei.leave_status = '0'
            and zrp.review_time <![CDATA[ <]]> CURRENT_TIMESTAMP
            <if test="userIds != null and userIds.size() > 0">
                AND zei.user_id not IN
                <foreach collection="userIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="itemIds != null and itemIds.size() > 0">
                AND zrp.id IN
                <foreach collection="itemIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        group by zrp.item_id, zrp.review_sn, zei.expert_id) aa
        left join zjk_evaluate ze on aa.expert_id = ze.evaluate_user and aa.item_id=ze.item_id and
        ze.item_stage=aa.review_sn and
        ze.create_by=#{bo.userId}
        left join zjk_expert zee on aa.expert_id = zee.expert_id
        left join zjk_product zp on aa.item_id = zp.id
        left join sys_dict_data sdd on sdd.dict_value=aa.review_sn and sdd.dict_type='item_stage'
        )dd
        <where>
            dd.userId is not null and dd.expert_id is not null
            <if test="bo.evaluateUser != null and bo.evaluateUser != ''">
                and dd.evaluateUser like '%' || #{bo.evaluateUser} || '%'
            </if>
            <if test="bo.productName != null and bo.productName != ''">
                and dd.product_name like '%' || #{bo.productName} || '%'
            </if>
            <if test="bo.status != null and bo.status != ''">
                and dd.flag = #{bo.status}
            </if>
            <if test="bo.expertComment != null and bo.expertComment != ''">
                and dd.expert_comment like '%' || #{bo.expertComment} || '%'
            </if>
        </where>
        order by dd.flag desc,dd.create_time desc

    </select>
    <select id="getInfoById" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        select ze.*,
               case when ze.status = 1 then '已评价' else '未评价' end as status,
               zee.expert_name                                         as evaluateUser,
               zee.work_unit,
               zee.duty                                                as expertPost,
               zp.product_name                                         as itemId
        from zjk_evaluate ze
                 left join zjk_expert zee on ze.evaluate_user = zee.expert_id
                 left join zjk_product zp on ze.item_id = zp.id
        where ze.id = #{id}
    </select>
    <select id="getMyJoinItems" resultType="org.dromara.zjk.domain.dto.SelExpertRuleDto">
        select zrp.id, zrp.item_id, zrp.review_sn
        from zjk_review_phase zrp
                 left join zjk_expert_item zei on zrp.id = zei.review_id
        where zei.accept_status = '1'
          and zei.leave_status = '0'
          and zrp.review_time <![CDATA[ <]]> CURRENT_TIMESTAMP
          and zei.user_id = #{userId}
        group by zrp.id, zrp.item_id, zrp.review_sn
    </select>
    <select id="getHadEvaluatedUsers" resultType="java.lang.String">
        select distinct evaluate_user
        from zjk_evaluate
        where item_id = #{itemId}
          and item_stage = #{itemStage}
          and status = 1
    </select>
    <select id="getListToOrg" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        select dd.* from (
        select ze.id,
        zp.user_id puid,
        ze.status sortnum,
        case when ze.status = 1 then '已评价' else '未评价' end as status,
        COALESCE(ze.status,0)  as flag,
        zee.expert_name as evaluateUser,
        zee.expert_id,
        zee.work_unit ,
        zee.duty as expertPost,
        zp.product_name ,
        ze.create_time,
        ze.expert_comment,
        aa.item_Id as itemId,
        aa.review_sn as itemStage,
        sdd.dict_label as itemStageName,
        aa.expert_id as userId
        from (select zrp.id, zrp.item_id, zrp.review_sn, zei.expert_id
        from zjk_review_phase zrp
        left join zjk_expert_item zei on zrp.id = zei.review_id and zrp.effective=1
        where zei.accept_status = '1'
        -- 为了展示注释掉条件
        and zei.leave_status = '0'
        and zrp.review_time  <![CDATA[ <]]> CURRENT_TIMESTAMP
        group by zrp.id, zrp.item_id, zrp.review_sn, zei.expert_id) aa
        left join zjk_evaluate ze on aa.expert_id = ze.evaluate_user and aa.item_id=ze.item_id and
        ze.item_stage=aa.review_sn and
        ze.create_by=#{bo.userId}
        left join zjk_expert zee on aa.expert_id = zee.expert_id
        left join zjk_product zp on aa.item_id = zp.id
        left join sys_dict_data sdd on sdd.dict_value=aa.review_sn and sdd.dict_type='item_stage'
        ) dd
        <where>
            dd.userId is not null and dd.expert_id is not null
            <if test="bo.isXmAdmin != null and bo.isXmAdmin != ''">
                and dd.puid=#{bo.userId}
            </if>
            <if test="bo.evaluateUser != null and bo.evaluateUser != ''">
                and dd.evaluateUser like '%' || #{bo.evaluateUser} || '%'
            </if>
            <if test="bo.productName != null and bo.productName != ''">
                and dd.product_name like '%' || #{bo.productName} || '%'
            </if>
            <if test="bo.status != null and bo.status != ''">
                <if test="bo.status == 0">
                    and dd.flag is null
                </if>
                <if test="bo.status != 0">
                    and dd.flag = #{bo.status}
                </if>
            </if>
            <if test="bo.expertComment != null and bo.expertComment != ''">
                and dd.expert_comment like '%' || #{bo.expertComment} || '%'
            </if>
        </where>
        order by dd.flag desc ,dd.create_time desc
    </select>

    <select id="testsysrole" resultType="java.lang.String">
        select role_id
        from sys_role
    </select>

    <select id="getEvaluateListByExpert" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        SELECT ze."id",
               ze.create_time,
               ze.expert_comment,
               ze.right_approval_score,
               case when ze.source_role = 1 then '专家互评' else '机构评价' end as sourceRoleStr,
               zp.product_name
        FROM zjk_evaluate ze
                 LEFT JOIN zjk_product zp on zp."id" = ze.item_id
        WHERE ze.evaluate_user = #{bo.evaluateUser}
    </select>
    <select id="getListToXm" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        select * from (
        select ze.id,case when ze.status = 1 then '已评价' else '未评价' end as status,
        COALESCE(ze.status,0)  as flag,
        zee.expert_name as evaluateUser,
        zee.work_unit,
        zee.duty as expertPost,
        zp.product_name,
        ze.create_time,
        zee.expert_id,
        aa.item_Id as itemId,
        aa.review_sn as itemStage,
        sdd.dict_label,
        aa.expert_id as userId,
        ze.expert_comment
        from (select zrp.item_id, zrp.review_sn, zei.expert_id
        from zjk_review_phase zrp
        left join zjk_expert_item zei on zrp.id = zei.review_id and zrp.effective = 1
        <where>
            zei.accept_status = '1'
            and zei.leave_status = '0'
            and zrp.review_time <![CDATA[ <]]> CURRENT_TIMESTAMP
            <if test="userIds != null and userIds.size() > 0">
                AND zei.user_id not IN
                <foreach collection="userIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="itemIds != null and itemIds.size() > 0">
                AND zrp.id IN
                <foreach collection="itemIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
        group by zrp.item_id, zrp.review_sn, zei.expert_id) aa
        left join zjk_evaluate ze on aa.expert_id = ze.evaluate_user and aa.item_id=ze.item_id and
        ze.item_stage=aa.review_sn and
        ze.create_by=#{bo.userId}
        left join zjk_expert zee on aa.expert_id = zee.expert_id
        left join zjk_product zp on aa.item_id = zp.id and zp.user_id=#{bo.userId}
        left join sys_dict_data sdd on sdd.dict_value=aa.review_sn and sdd.dict_type='item_stage'
        )dd
        <where>
            dd.userId is not null and dd.expert_id is not null
            <if test="bo.evaluateUser != null and bo.evaluateUser != ''">
                and dd.evaluateUser like '%' || #{bo.evaluateUser} || '%'
            </if>
            <if test="bo.productName != null and bo.productName != ''">
                and dd.product_name like '%' || #{bo.productName} || '%'
            </if>
            <if test="bo.status != null and bo.status != ''">
                and dd.flag = #{bo.status}
            </if>
            <if test="bo.expertComment != null and bo.expertComment != ''">
                and dd.expert_comment like '%' || #{bo.expertComment} || '%'
            </if>
        </where>
        order by dd.flag desc,dd.create_time desc
    </select>
    <select id="getStsNum" resultType="org.dromara.zjk.domain.vo.StsEvaluateNumVO">
        select (select count(1) from zjk_evaluate)                       nums,
               (select count(1) from zjk_evaluate where source_role = 1) eachNums,
               (select count(1)
                from zjk_evaluate
                where bus_level_score = 5
                  and work_attitude_score = 5
                  and right_approval_score = 5)                          greatNums,
               (select count(distinct evaluate_user) from zjk_evaluate)  expertNums
    </select>
    <select id="getStsEvaluateNums" resultType="org.dromara.zjk.domain.dto.LeaveStsDto">
        select year(create_time) yr, month(create_time) mt, count(1) cs
        from zjk_evaluate
        where create_time between #{ago} and #{now}
        group by year(create_time), month(create_time)

    </select>
    <select id="getStsEvaluatePerNums" resultType="org.dromara.zjk.domain.dto.LeaveStsDto">
        select dd.mt, count(1) cs
        from (select year(create_time) yr, month(create_time) mt
              from zjk_evaluate
              where create_time between #{ago} and #{now}
              group by year(create_time), month(create_time), evaluate_user) dd
        group by dd.yr, dd.mt
    </select>
    <select id="getStsFiveStar" resultType="org.dromara.zjk.domain.vo.StsFiveVO">
        select (select count(1) from zjk_evaluate where bus_level_score != 5) otherNums,
               (select count(1) from zjk_evaluate where bus_level_score = 5)  fiveNums
        union all
        select (select count(1) from zjk_evaluate where work_attitude_score != 5) otherNums,
               (select count(1) from zjk_evaluate where work_attitude_score = 5)  fiveNums
        union all
        select (select count(1) from zjk_evaluate where right_approval_score != 5) otherNums,
               (select count(1) from zjk_evaluate where right_approval_score = 5)  fiveNums
    </select>
    <select id="getStsList" resultType="org.dromara.zjk.domain.vo.StsListVO">
        select ze.expert_name,ze.work_unit,ze.duty,ze.approval_time,aa.create_by, aa.score_sum, round(aa.score_sum / cs
        / 3,2)
        score_avg, bb.joinProNums,aa.closeTime,
        case when score_avg >= 4.5 then '优秀' when score_avg >= 3 and score_avg <![CDATA[ <]]> 4.5 then '合格' else
        '不合格'
        end as evaRank
        from (select create_by,max(create_time) closeTime, sum(bus_level_score + work_attitude_score +
        right_approval_score) score_sum, count(1) cs
        from zjk_evaluate
        group by create_by) aa
        LEFT JOIN (select m.create_by, count(1) joinProNums
        FROM (select create_by, count(1) from zjk_evaluate group by item_id, item_stage, create_by) m
        group by m.create_by) bb on aa.create_by = bb.create_by
        left join zjk_expert ze on aa.create_by=ze.user_id
        where
        ze.expert_id is not null
        <if test="bo.evaluateUser != null and bo.evaluateUser != ''">
            and ze.expert_name like '%' || #{bo.evaluateUser} || '%'
        </if>
        <if test="bo.workUnit != null and bo.workUnit != ''">
            and ze.work_unit like '%' || #{bo.workUnit} || '%'
        </if>
        <if test="bo.startDate != null">
            and aa.closeTime >= #{bo.startDate}
        </if>
        <if test="bo.endDate != null">
            and aa.closeTime <![CDATA[ <= ]]> #{bo.endDate}
        </if>
        <if test="bo.sortWay == 1 ">
            order by score_avg desc
        </if>
    </select>
    <select id="getStsEvaluateList" resultType="org.dromara.zjk.domain.vo.StsListVO">
        select ze.expert_name,ze.work_unit,ze.duty,ze.approval_time,aa.create_by, aa.score_sum, round(aa.score_sum / cs
        / 3,2)
        score_avg, bb.joinProNums,aa.closeTime,
        case when score_avg >= 4.5 then '优秀' when score_avg >= 3 and score_avg <![CDATA[ <]]> 4.5 then '合格' else
        '不合格'
        end as evaRank
        from (select create_by,max(create_time) closeTime, sum(bus_level_score + work_attitude_score +
        right_approval_score) score_sum, count(1) cs
        from zjk_evaluate
        group by create_by) aa
        LEFT JOIN (select m.create_by, count(1) joinProNums
        FROM (select create_by, count(1) from zjk_evaluate group by item_id, item_stage, create_by) m
        group by m.create_by) bb on aa.create_by = bb.create_by
        left join zjk_expert ze on aa.create_by=ze.user_id
        where
        ze.expert_id is not null
        <if test="bo.evaluateUser != null and bo.evaluateUser != ''">
            and ze.expert_name like '%' || #{bo.evaluateUser} || '%'
        </if>
        <if test="bo.workUnit != null and bo.workUnit != ''">
            and ze.work_unit like '%' || #{bo.workUnit} || '%'
        </if>
        <if test="bo.startDate != null">
            and aa.closeTime >= #{bo.startDate}
        </if>
        <if test="bo.endDate != null">
            and aa.closeTime <![CDATA[ <= ]]> #{bo.endDate}
        </if>
        <if test="bo.sortWay == 1 ">
            order by score_avg desc
        </if>
    </select>
    <select id="checkIsPresentStatus" resultType="java.lang.Integer">
        select is_present
        from zjk_evaluate
        where evaluate_user = #{userId}
          and source_role = 2
          and item_id = #{itemId}
          and item_stage = #{itemStage}
        limit 1
    </select>
</mapper>
