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

    <select id="selectCheckInfoList" resultType="org.dromara.zjk.domain.vo.CheckSupportVo">

        select ze.expert_name,
               zc.money_type    as proLevel,
               ze.contact,
               ze.id_card as idcard,
               zrp.review_sn,
               ze.expert_id,
               zei.item_id      as product_id
        from zjk_expert_item zei
                 left join zjk_expert ze on zei.expert_id = ze.expert_id
                 left join zjk_expert_money_config zc on ze.position_title_level = zc.money_id
                 left join zjk_review_phase zrp on zrp.id = zei.review_id
        where zrp.effective = 1
          and zei.accept_status = '1'
          and zei.leave_status = '0'
        --and  CURRENT_TIMESTAMP > zrp.review_time
        <if test="bo.productId != null and bo.productId != ''">
            and zei.item_id = #{bo.productId}
        </if>
        <if test="bo.reviewSn != null and bo.reviewSn != ''">
            and zrp.review_sn = #{bo.reviewSn}
        </if>
    </select>
    <select id="getInfoListToExpert" resultType="org.dromara.zjk.domain.vo.CheckSupportVo">
        select ze.expert_name,
        zc.money_type    as proLevel,
        ze.contact,
        ze.id_card as idcard,
        zrp.review_sn,
        ze.expert_id,
        zei.item_id      as product_id
        from zjk_expert_item zei
        left join zjk_expert ze on zei.expert_id = ze.expert_id
        left join zjk_expert_money_config zc on ze.position_title_level = zc.money_id
        left join zjk_review_phase zrp on zrp.id = zei.review_id
        where zrp.effective = 1
        and zei.accept_status = '1'
        and zei.leave_status = '0'
        --and  CURRENT_TIMESTAMP > zrp.review_time
        <if test="bo.productId != null and bo.productId != ''">
            and zei.item_id = #{bo.productId}
        </if>
        <if test="bo.reviewSn != null and bo.reviewSn != ''">
            and zrp.review_sn = #{bo.reviewSn}
        </if>
    </select>
    <select id="getInvFileNameById" resultType="java.lang.String">
        SELECT original_name FROM sys_oss where oss_id= #{invFileId}
    </select>
    <select id="getCheckNo" resultType="java.lang.String">
        SELECT 'DZ' || TO_CHAR(NOW(), 'YYYYMMDD') || '-' || (
            SELECT COUNT(1) + 1
            FROM zjk_inout_check)
        </select>
</mapper>
