<?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.expert.expertPatent.mapper.ZjkExpertPatentMapper">

    <select id="selectByExpertId" resultType="org.dromara.zjk.expert.expertPatent.domain.ZjkExpertPatent">
        SELECT
        zep.*,
        so.original_name,
        so.file_suffix,
        so.url
        FROM
        zjk_expert_patent zep
        LEFT JOIN sys_oss so ON so.oss_id = zep.patent_path
        <where>
            <if test="expertId != null">
                and zep.expert_id=#{expertId}
            </if>
        </where>
    </select>
    <select id="selectVoListJoinExpertId"
            resultType="org.dromara.zjk.expert.expertPatent.domain.vo.ZjkExpertPatentVo">
        select zep.*,oss.*
        from zjk_expert_patent zep
        inner join zjk_expert ze on zep.expert_id = ze.id
        inner join sys_oss so on so.oss_id = zep.paper_path
        where zep.del_flag = 0
        <if test="bo.nameInvention !=null and bo.nameInvention != '' ">
            and zep.name_invention = #{bo.nameInvention}
        </if>
        <if test="bo.patentNumber !=null and bo.patentNumber != '' ">
            and zep.patent_number = #{bo.patentNumber}
        </if>
    </select>
    <select id="patentList" resultType="org.dromara.zjk.expert.expertPatent.domain.vo.ZjkExpertPatentVo">
        SELECT
            zpr.* ,so.*,zep.*
        FROM
            zjk_product_reviewsn zpr
                INNER JOIN zjk_expert_patent zep ON zep.product_id = zpr.ID
                INNER JOIN sys_oss so on so.oss_id = zep.patent_path
        WHERE
            zpr.product_id = #{productId}
          AND zpr.review_sn = #{reviewSn}
    </select>

    <select id="getExpertPatentCount" resultType="java.lang.Long">
        SELECT
            count(1)
        FROM
            zjk_expert_patent zep
                INNER JOIN zjk_expert ze ON ze.expert_id = zep.expert_id
        WHERE ze.status = 2
    </select>
</mapper>
