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

    <select id="selectUser"  parameterType="Long" resultType="org.dromara.zjk.domain.vo.UserVo">
        SELECT ze.expert_id,su.user_id,su.dept_id
        FROM zjk_expert ze
                 INNER JOIN sys_user su on ze.user_id = su.user_id
        WHERE ze.expert_id = #{expertId}
    </select>


    <select id="getById" resultType="org.dromara.zjk.domain.vo.ItemVO">
        SELECT zp.product_name as productName,zrp.review_sn as reviewSn
        FROM zjk_product_summarize zps
                 inner JOIN zjk_product zp on zp.id=zps.product_id
                 inner JOIN zjk_review_phase zrp on zps.review_id = zrp.id

        where zrp.id = #{id}
        limit 1
    </select>
    <select id="getByProductIds" resultType="org.dromara.zjk.domain.vo.ZjkExpertItemVo">
        select *
        from zjk_expert_item
        where accept_status in (0,1) and  item_id in
        <foreach collection="ids" open="(" separator="," close=")" item="id">
            #{id}
        </foreach>
    </select>

    <select id="selectDataList"  resultType="java.lang.String">
        select dict_label
        from sys_dict_data
        where dict_type = 'perform_work_type' and dict_sort in
        <foreach collection="list" open="(" separator="," close=")" item="id">
            #{id}
        </foreach>
        GROUP BY 	dict_label
    </select>
    <select id="expertTop5" resultType="org.dromara.zjk.domain.vo.ZjkExpertItemVo">
        SELECT user_id as userId ,count(1) as productCount
        FROM "zjk_expert_item"
        WHERE accept_status = 1
        GROUP BY user_id
        ORDER BY productCount desc
        LIMIT 5
    </select>
    <select id="selectBatchByReviewIds" resultType="org.dromara.zjk.domain.vo.ZjkExpertItemVo">
        select *
        from zjk_expert_item
        where  is_sure = 1 and  review_id in
        <foreach collection="reviewIds" open="(" separator="," close=")" item="reviewId">
            #{reviewId}
        </foreach>
    </select>

    <select id="getExpertItemInfoByUserIdAndCode" resultType="org.dromara.zjk.domain.ZjkExpertItem">
        SELECT
        zei.*
        FROM
        zjk_expert_item zei
        LEFT JOIN
        zjk_review_phase zrp ON zrp."id" = zei.review_id
        WHERE
        zei.is_sure = 1
        AND zei.user_id = #{userId}
        AND zei.expert_id = #{expertId}
        and zrp.is_summarize = 0
        <if test="smsIsSuer != null">
            <choose>
                <when test="smsIsSuer == 1">
                    AND zei.confirm_code = #{confirmCode}
                </when>
                <otherwise>
                    AND zei.refuse_code = #{confirmCode}
                </otherwise>
            </choose>
        </if>

    </select>
    <select id="selectCancle" resultType="org.dromara.zjk.domain.vo.ZjkExpertItemVo">
        SELECT zei.id as id ,zrp.review_time, zei.*,zie.id as  item_extreation_id
        FROM zjk_review_phase zrp
        INNER JOIN zjk_expert_item zei on zrp.id = zei.review_id
        inner join zjk_item_extreation zie on zrp.item_extreation_id = zie.id
        WHERE zrp.effective =1  and
        zei.is_sure = 1 and zei.accept_status = 0
        and is_summarize=0
        and  EXTRACT(EPOCH FROM (zei.create_time - CURRENT_TIMESTAMP)) / 3600 &lt;= -#{hour}
        and EXTRACT(EPOCH FROM (review_time - CURRENT_TIMESTAMP)) / 3600 &lt;=#{hour}
    </select>

    <select id="getExpertItemByUserIdAndItemId" resultType="org.dromara.zjk.domain.ZjkExpertItem">
        SELECT
            zei.*
        FROM
            zjk_review_phase zrp
                LEFT JOIN zjk_expert_item zei on zei.review_id = zrp.id
        WHERE
            zrp.effective = 1
            AND zei.is_sure = 1
          AND zrp.item_id = #{itemId}
          AND zrp.review_sn = #{reviewSn}
          AND zei.user_id = #{userId}
    </select>


    <select id="selectExpertByCodeAndPhone" resultType="org.dromara.zjk.domain.ZjkExpert">
        SELECT e.*
        FROM zjk_expert_item i
                 JOIN zjk_expert e ON i.expert_id = e.expert_id
        WHERE (i.confirm_code = #{code} OR i.refuse_code = #{code})
          AND e.contact = #{phone}
          AND i.is_sure = 1
          AND e.delete_flag = 0
        ORDER BY i.create_time DESC
            LIMIT 1
    </select>
    <select id="getCheckZjAccetpt" resultType="org.dromara.zjk.domain.ZjkExpertItem">
        select id,accept_status,create_by from zjk_expert_item where review_id=#{id} and is_sure = 1
    </select>
    <select id="checkUserPhoneExist" resultType="java.lang.String">
        select phonenumber from sys_user where user_id=#{id}
    </select>


</mapper>
