<?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.expertPaper.mapper.ZjkExpertPaperMapper">

    <select id="selectByExpertId" resultType="org.dromara.zjk.expert.expertPaper.domain.ZjkExpertPaper">
        SELECT
        zep.*,
        so.original_name,
        so.file_suffix,
        so.url
        FROM
        zjk_expert_paper zep
        LEFT JOIN sys_oss so ON so.oss_id = zep.paper_path
        <where>
            <if test="expertId != null">
                and zep.expert_id=#{expertId}
            </if>
        </where>

    </select>
    <select id="selectVoListJoinExpertId"
            resultType="org.dromara.zjk.expert.expertPaper.domain.vo.ZjkExpertPaperVo">
        SELECT
            *,
            ze.expert_name
        FROM
            (
                SELECT

                    zep.expert_id,
                    2 AS TYPE,
                    so.*,
                    zep.create_time,
                    zep.periodical_number as number,
                    zep.paper_name as invention
                FROM zjk_expert_paper zep

                         INNER JOIN sys_oss so ON so.oss_id = zep.paper_path
                UNION ALL
                SELECT
                    zep.expert_id,
                    1 AS TYPE,
                    so.*,
                    zep.create_time,
        zep.patent_number as number,
        zep.name_invention as invention
                FROM zjk_expert_patent zep

                         INNER JOIN sys_oss so ON so.oss_id = zep.patent_path

            )
                A INNER JOIN zjk_expert ze ON ze.expert_ID = a.expert_id
        <where>
            <if test="bo.fileName != null and bo.fileName != ''">
                and a.original_name like '%' || #{bo.fileName} || '%'
            </if>
        </where>
    </select>
    <select id="paperList" resultType="org.dromara.zjk.expert.expertPaper.domain.vo.ZjkExpertPaperVo">
        SELECT
            zpr.* ,so.*,zep.*
        FROM
            zjk_product_reviewsn zpr
                INNER JOIN zjk_expert_paper zep ON zep.product_id = zpr.ID
                INNER JOIN sys_oss so on so.oss_id = zep.paper_path
        WHERE
            zpr.product_id = #{productId}
          AND zpr.review_sn = #{reviewSn}
    </select>

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