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

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkPoint">
        <id property="id" column="ID" jdbcType="SMALLINT"/>
        <result property="points" column="points" jdbcType="NUMERIC"/>
        <result property="ruleId" column="rule_id" jdbcType="BIGINT"/>
        <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="TIMESTAMP"/>
        <result property="updateBy" column="UPDATE_BY" jdbcType="BIGINT"/>
        <result property="updateTime" column="UPDATE_TIME" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,points,rule_id,
        create_dept,tenant_id,CREATE_BY,
        CREATE_TIME,UPDATE_BY,UPDATE_TIME
    </sql>
    <update id="clearUnPresentData">
        update zjk_point set points=0 where evaluate_id in
        <foreach collection="evaIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>
    <select id="queryPointList" resultType="org.dromara.zjk.domain.vo.PointShowVO">
        select ze.approval_time as inTime,
        ze.work_unit,
        ze.duty as expertPost,
        ze.contact,
        ze.expert_name,
        zpu.expert_id,
        RANK() OVER (ORDER BY point_count DESC) AS rank,
        zpu.point_count as points
        from zjk_point_user zpu
        inner join zjk_expert ze on zpu.expert_id = ze.expert_id
        where
            ze.delete_flag = 0
            <if test="bo.expertName != null and bo.expertName != ''">
                and ze.expert_name like '%' || #{bo.expertName} || '%'
            </if>
            <if test="bo.workUnit != null and bo.workUnit != ''">
                and ze.work_unit like '%' || #{bo.workUnit} || '%'
            </if>
            <if test="bo.startDate != null">
                and ze.approval_time >= #{bo.startDate}
            </if>
            <if test="bo.endDate != null">
                and ze.approval_time <![CDATA[ <= ]]> #{bo.endDate}
            </if>

        ORDER BY point_count DESC

    </select>
    <select id="queryPointDetail" resultType="org.dromara.zjk.domain.vo.ZjkPointVO">
        select zp.*,zpr.point_reason from zjk_point zp left join zjk_point_rule zpr on zp.rule_id=zpr."ID"
        where zp.expert_id=#{bo.expertId}
        <if test="bo.pointReason != null and bo.pointReason != ''">
            and zpr.point_reason like '%' || #{bo.pointReason} || '%'
        </if>
        <if test="bo.endDate != null">
            and zp.CREATE_TIME <![CDATA[ <= ]]> #{bo.endDate}
        </if>
        <if test="bo.startDate != null">
            and zp.CREATE_TIME >= #{bo.startDate}
        </if>
    </select>
    <select id="getPointByDate" resultType="java.math.BigDecimal">
        SELECT sum(points)
        FROM "zjk_point"
        where to_char("CREATE_TIME", 'yyyy-MM-dd') = #{date}
          and expert_id = #{expertId}
    </select>
    <select id="getCheckPointList" resultType="org.dromara.zjk.domain.vo.PointShowVO">
        select zpu.id,ze.approval_time as inTime,
        ze.work_unit,
        ze.duty as expertPost,
        ze.contact,
        ze.expert_name,
        zpu.expert_id,
        zpu.check_status,
        zpu.check_time,
        RANK() OVER (ORDER BY point_count DESC) AS rank,
        zpu.point_count as points
        from zjk_point_user zpu
        left join zjk_expert ze on zpu.expert_id = ze.expert_id
        <where>
            ze.expert_id is not null and zpu.check_status != 0
            <if test="bo.expertName != null and bo.expertName != ''">
                and ze.expert_name like '%' || #{bo.expertName} || '%'
            </if>
            <if test="bo.workUnit != null and bo.workUnit != ''">
                and ze.work_unit like '%' || #{bo.workUnit} || '%'
            </if>
            <if test="bo.startDate != null">
                and ze.approval_time >= #{bo.startDate}
            </if>
            <if test="bo.endDate != null">
                and ze.approval_time <![CDATA[ <= ]]> #{bo.endDate}
            </if>
        </where>
        ORDER BY zpu.point_count DESC
    </select>
    <select id="getCheckWaitPointList" resultType="org.dromara.zjk.domain.vo.PointShowVO">
        select DISTINCT hh.id,
                        hh.inTime,
                        hh.work_unit,
                        hh.expertPost,
                        hh.contact,
                        hh.expert_name,
                        hh.expert_id,
                        hh.check_time,
                        hh.rank,
                        hh.points,
                        hh.check_status
        from (select *
              from (select zpu.id,
                           ze.approval_time                     as inTime,
                           ze.work_unit,
                           ze.duty                            as expertPost,
                           ze.contact,
                           ze.expert_name,
                            zpu.check_status,
                           zpu.expert_id,
                           zpu.check_time,
                           RANK() OVER (ORDER BY points DESC) AS rank,
                           zpu.point_count                    as points
                    from zjk_point_user zpu
                             left join zjk_expert ze on zpu.expert_id = ze.expert_id) aa
              WHERE aa.rank <![CDATA[ <= ]]> 10
                and aa.check_time <![CDATA[ <= ]]> #{bo.weekCheckDate}
              UNION all
              select *
              from (select zpu.id,
                           ze.approval_time                          as inTime,
                           ze.work_unit,
                           ze.duty                                 as expertPost,
                           ze.contact,
                           ze.expert_name,
                           zpu.check_status,
                           zpu.expert_id,
                           zpu.check_time,
                           RANK() OVER (ORDER BY point_count DESC) AS rank,
                           zpu.point_count                         as points
                    from zjk_point_user zpu
                             left join zjk_expert ze on zpu.expert_id = ze.expert_id
                    where zpu.change_time <![CDATA[ < ]]> #{bo.monthCheckDate}
                      and zpu.point_count <![CDATA[ < ]]> 10
                     ) bb) hh
        ORDER BY hh.points DESC
    </select>
    <select id="getPointsByClearData" resultType="org.dromara.zjk.domain.ZjkPoint">
SELECT * FROM zjk_point  where evaluate_id in
        <foreach collection="evaIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>
