<?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.ZjkReviewPhaseMapper">
    <update id="updateByIds">
        update zjk_review_phase set is_summarize = 1
        where id in
        <foreach collection="ids" open="(" separator="," close=")" item="id">
            #{id}
        </foreach>
    </update>

    <select id="reviewProductList" resultType="org.dromara.zjk.domain.vo.ZjkReviewProductVo">
        SELECT
        zp.product_name,
        zp.product_sn,
        zp.product_unit,
        zp.unit_by,
        zp.unit_concat,
        zvp.review_sn,
        zvp.review_time,
        zp.id AS product_id
        FROM zjk_expert_item zei
        INNER JOIN zjk_review_phase zvp ON zei.review_id = zvp.id
        INNER JOIN zjk_product zp ON zp.id = zei.item_id
        WHERE 1 = 1
        AND effective = 1

        <if test="bo.reviewSn != null and bo.reviewSn != ''">
            AND zvp.review_sn = #{bo.reviewSn}
        </if>

        <if test="bo.search != null and bo.search != ''">
            AND (zp.product_name LIKE CONCAT('%', #{bo.search}, '%')
            OR zp.product_sn LIKE CONCAT('%', #{bo.search}, '%'))
        </if>

        <if test="bo.startTime != null">
            AND zvp.review_time &gt;= #{bo.startTime}
        </if>
        <if test="bo.endTime != null">
            AND zvp.review_time &lt;= #{bo.endTime}
        </if>

        GROUP BY
        zp.product_name,
        zp.product_sn,
        zp.product_unit,
        zp.unit_by,
        zp.unit_concat,
        zvp.review_sn,
        zvp.review_time,
        zp.id
    </select>

    <select id="getByProductId" resultType="java.lang.Long">
        SELECT zrp.id
        FROM zjk_expert_item zei
                 INNER JOIN zjk_review_phase zrp on zei.review_id = zrp.id
        where zei.item_id =  #{productId}
        GROUP BY zrp.id
    </select>
    <select id="reviewExport" resultType="org.dromara.zjk.domain.vo.ZjkReviewProductVo">
        SELECT Max(zp.product_name) as product_name,
        Max(zp.product_sn) as product_sn,
        Max(zp.product_unit) as product_unit,
        Max(zp.unit_by) as unit_by,
        Max(zp.unit_concat) as unit_concat,
        Max(zvp.review_sn) as review_sn,
        Max(zvp.review_time) as review_time,
        Max(zp.id) as product_id
        from  zjk_expert_item zei
        inner JOIN zjk_review_phase zvp on zei.review_id = zvp.id
        inner JOIN zjk_product zp on zp.id = zei.item_id
        WHERE 1=1
        <if test="bo.search != null and bo.search!= '' ">
            and (zp.product_name like '%' || #{bo.search} ||'%' or zp.product_sn like '%' || #{bo.search} ||'%' )
        </if>
        <if test="bo.reviewSn != null and bo.reviewSn != '' ">
            and zvp.review_sn = #{bo.reviewSn}
        </if>
        <if test="bo.startTime != null ">
            and zvp.review_time >= #{bo.startTime}
        </if>
        <if test="bo.endTime != null ">
            and zvp.review_time &lt;= #{bo.endTime}
        </if>
        GROUP BY zei.item_id

    </select>
    <select id="getByProductIds" resultType="org.dromara.zjk.domain.vo.ZjkReviewProductVo">
        select *,item_id productId
        from zjk_review_phase
        where effective = 1 and item_id in
        <foreach collection="productIds" open="(" separator="," close=")" item="productId">
        #{productId}
        </foreach>
    </select>

    <select id="getReviewProductListByExpert" resultType="org.dromara.zjk.domain.vo.ZjkReviewProductVo">
        SELECT
            MAX( zp.product_name ) AS product_name,
            MAX( zp.product_sn ) AS product_sn,
            MAX( zp.product_unit ) AS product_unit,
            MAX( zp.unit_by ) AS unit_by,
            MAX( zp.unit_concat ) AS unit_concat,
            MAX( zvp.review_sn ) AS review_sn,
            MAX( zvp.review_time ) AS review_time,
            MAX( zp.ID ) AS product_id
        FROM
            zjk_expert_item zei
                INNER JOIN zjk_review_phase zvp ON zei.review_id = zvp.
                ID INNER JOIN zjk_product zp ON zp.ID = zei.item_id
        WHERE
            1 = 1
          AND effective = 1
          AND zei.expert_id = #{bo.expertId}
        GROUP BY
            zei.item_id
    </select>

    <select id="selectVoListByLimit" resultType="org.dromara.zjk.domain.ZjkReviewPhase">
        select *  from zjk_review_phase where parent_id = #{id} order by update_time desc LIMIT 0,1
    </select>

    <select id="getReviewProductCountByExpertId" resultType="java.lang.Long">
        SELECT
            COUNT( 1 )
        FROM
            zjk_expert_item zei
                INNER JOIN zjk_review_phase zvp ON zei.review_id = zvp.ID
        WHERE
            1 = 1
          AND accept_status = 1
          AND effective = 1
          AND zei.expert_id = #{expertId}
    </select>

    <select id="selectVoListByZjkItemExpert" resultType="org.dromara.zjk.domain.vo.ZjkReviewPhaseVo">
        SELECT re.ID as id,
               re.revision as revision,
               re.review_sn as reviewSn,
               re.review_time as reviewTime,
               re.review_place as reviewPlace,
               re.planned_intake as plannedIntake,
               re.extraction_mode as extractionMode,
               re.extractio_restriction as extractioRestriction,
               re.avoidance_expert as avoidanceExpert,
               re.avoidance_unit as avoidanceUnit,
               re.item_id as itemId,
               re.item_extreation_id as itemExtreationId,
               re.designated_number as designatedNumber,
               re.area as area,
               re.effective as effective,
               re.cause_repum as causeRepum,
               re.parent_id as parentId,
               re.extract_area as extractArea,
               re.tenant_id as tenantId,
               re.create_dept as createDept,
               re.create_by as createBy,
               re.create_time as createTime,
               re.update_by as updateBy,
               re.update_time as updateTime
        FROM
            zjk_review_phase re left join zjk_item_extreation item on re.item_extreation_id = item.id
        <where>
            re.item_id = #{itemid} AND re.review_sn = #{reviewSn} and  item.is_sure = #{rown}
            <if test="supplementCause != null">
                and re.effective = 1
            </if>
        </where>

    </select>

    <select id="selectVoListByZjkItemExpertBylast" resultType="org.dromara.zjk.domain.vo.ZjkReviewPhaseVo">
        SELECT re.ID as id,
        re.revision as revision,
        re.review_sn as reviewSn,
        re.review_time as reviewTime,
        re.review_place as reviewPlace,
        re.planned_intake as plannedIntake,
        re.extraction_mode as extractionMode,
        re.extractio_restriction as extractioRestriction,
        re.avoidance_expert as avoidanceExpert,
        re.avoidance_unit as avoidanceUnit,
        re.item_id as itemId,
        re.item_extreation_id as itemExtreationId,
        re.designated_number as designatedNumber,
        re.area as area,
        re.effective as effective,
        re.cause_repum as causeRepum,
        re.parent_id as parentId,
        re.extract_area as extractArea,
        re.tenant_id as tenantId,
        re.create_dept as createDept,
        re.create_by as createBy,
        re.create_time as createTime,
        re.update_by as updateBy,
        re.update_time as updateTime
        FROM
        zjk_review_phase re left join zjk_item_extreation item on re.item_extreation_id = item.id
        <where>
            re.item_id = #{itemid} AND re.review_sn = #{reviewSn} and  item.is_sure = #{rown}
        </where>
        order by re.create_time desc
        limit 1
    </select>

    <select id="selectUpdateZjkItemExpert"  resultType="org.dromara.zjk.domain.vo.ZjkReviewPhaseVo">
        SELECT ID as id,
               revision as revision,
               review_sn as reviewSn,
               review_time as reviewTime,
               review_place as reviewPlace,
               planned_intake as plannedIntake,
               extraction_mode as extractionMode,
               extractio_restriction as extractioRestriction,
               avoidance_expert as avoidanceExpert,
               avoidance_unit as avoidanceUnit,
               item_id as itemId,
               item_extreation_id as itemExtreationId,
               designated_number as designatedNumber,
               area as area,
               effective as effective,
               cause_repum as causeRepum,
               parent_id as parentId,
               extract_area as extractArea,
               tenant_id as tenantId,
               create_dept as createDept,
               create_by as createBy,
               create_time as createTime,
               update_by as updateBy,
               update_time as updateTime
        FROM
            zjk_review_phase
        WHERE
            item_id = #{itemid} AND review_sn = #{reviewSn}
    </select>

    <select id="selectListByItem" resultType="java.lang.Long">
        select ex."id"
        from zjk_item_extreation ex
        LEFT JOIN zjk_review_phase re ON ex.id = re.item_extreation_id
        where re."id" = #{reviewId}
    </select>
    <select id="selectVoByExtreationId" resultType="org.dromara.zjk.domain.vo.ZjkReviewPhaseVo">
        select zrp.*
        from zjk_review_phase zrp
        where zrp.item_extreation_id = #{extractionId}
        order by zrp.CREATE_TIME desc
    </select>
</mapper>
