<?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="com.cool.store.mapper.HyPartnerInterviewPlanMapper">
  <resultMap id="BaseResultMap" type="com.cool.store.entity.HyPartnerInterviewPlanDO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="partner_line_id" jdbcType="BIGINT" property="partnerLineId" />
    <result column="partner_id" jdbcType="VARCHAR" property="partnerId" />
    <result column="interview_date" jdbcType="DATE" property="interviewDate" />
    <result column="start_time" jdbcType="TIMESTAMP" property="startTime" />
    <result column="end_time" jdbcType="TIMESTAMP" property="endTime" />
    <result column="is_partner_interview" jdbcType="TINYINT" property="isPartnerInterview" />
    <result column="application_approved" jdbcType="TINYINT" property="applicationApproved" />
    <result column="actual_start_time" jdbcType="TIMESTAMP" property="actualStartTime" />
    <result column="actual_end_time" jdbcType="TIMESTAMP" property="actualEndTime" />
    <result column="room_id" jdbcType="VARCHAR" property="roomId" />
    <result column="feishu_calendar_id" jdbcType="VARCHAR" property="feishuCalendarId" />
    <result column="feishu_schedule_id" jdbcType="VARCHAR" property="feishuScheduleId" />
    <result column="room_password" jdbcType="VARCHAR" property="roomPassword" />
    <result column="interviewer" jdbcType="VARCHAR" property="interviewer" />
    <result column="room_status" jdbcType="TINYINT" property="roomStatus" />
    <result column="deleted" jdbcType="BIT" property="deleted" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>
  <sql id="Base_Column_List">
    id, partner_line_id, partner_id, interview_date, start_time, end_time, is_partner_interview, 
    actual_start_time, actual_end_time, room_id, room_password, interviewer, room_status, 
    deleted, create_time, update_time,feishu_calendar_id,feishu_schedule_id
  </sql>
  <insert id="insertSelective" keyColumn="id" keyProperty="record.id" useGeneratedKeys="true">
    insert into hy_partner_interview_plan
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="record.partnerLineId != null">
        partner_line_id,
      </if>
      <if test="record.partnerId != null">
        partner_id,
      </if>
      <if test="record.interviewDate != null">
        interview_date,
      </if>
      <if test="record.startTime != null">
        start_time,
      </if>
      <if test="record.endTime != null">
        end_time,
      </if>
      <if test="record.isPartnerInterview != null">
        is_partner_interview,
      </if>
      <if test="record.actualStartTime != null">
        actual_start_time,
      </if>
      <if test="record.actualEndTime != null">
        actual_end_time,
      </if>
      <if test="record.roomId != null">
        room_id,
      </if>
      <if test="record.roomPassword != null">
        room_password,
      </if>
      <if test="record.interviewer != null">
        interviewer,
      </if>
      <if test="record.roomStatus != null">
        room_status,
      </if>
      <if test="record.deleted != null">
        deleted,
      </if>
      <if test="record.createTime != null">
        create_time,
      </if>
      <if test="record.updateTime != null">
        update_time,
      </if>
      <if test="record.applicationApproved != null">
        application_approved,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="record.partnerLineId != null">
        #{record.partnerLineId},
      </if>
      <if test="record.partnerId != null">
        #{record.partnerId},
      </if>
      <if test="record.interviewDate != null">
        #{record.interviewDate},
      </if>
      <if test="record.startTime != null">
        #{record.startTime},
      </if>
      <if test="record.endTime != null">
        #{record.endTime},
      </if>
      <if test="record.isPartnerInterview != null">
        #{record.isPartnerInterview},
      </if>
      <if test="record.actualStartTime != null">
        #{record.actualStartTime},
      </if>
      <if test="record.actualEndTime != null">
        #{record.actualEndTime},
      </if>
      <if test="record.roomId != null">
        #{record.roomId},
      </if>
      <if test="record.roomPassword != null">
        #{record.roomPassword},
      </if>
      <if test="record.interviewer != null">
        #{record.interviewer},
      </if>
      <if test="record.roomStatus != null">
        #{record.roomStatus},
      </if>
      <if test="record.deleted != null">
        #{record.deleted},
      </if>
      <if test="record.createTime != null">
        #{record.createTime},
      </if>
      <if test="record.updateTime != null">
        #{record.updateTime},
      </if>
      <if test="record.applicationApproved != null">
        #{record.applicationApproved},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective">
    update hy_partner_interview_plan
    <set>
      <if test="record.partnerLineId != null">
        partner_line_id = #{record.partnerLineId},
      </if>
      <if test="record.partnerId != null">
        partner_id = #{record.partnerId},
      </if>
      <if test="record.interviewDate != null">
        interview_date = #{record.interviewDate},
      </if>
      <if test="record.startTime != null">
        start_time = #{record.startTime},
      </if>
      <if test="record.endTime != null">
        end_time = #{record.endTime},
      </if>
      <if test="record.isPartnerInterview != null">
        is_partner_interview = #{record.isPartnerInterview},
      </if>
      <if test="record.actualStartTime != null">
        actual_start_time = #{record.actualStartTime},
      </if>
      <if test="record.actualEndTime != null">
        actual_end_time = #{record.actualEndTime},
      </if>
      <if test="record.roomId != null">
        room_id = #{record.roomId},
      </if>
      <if test="record.roomPassword != null">
        room_password = #{record.roomPassword},
      </if>
      <if test="record.interviewer != null">
        interviewer = #{record.interviewer},
      </if>
      <if test="record.roomStatus != null">
        room_status = #{record.roomStatus},
      </if>
      <if test="record.deleted != null">
        deleted = #{record.deleted},
      </if>
      <if test="record.createTime != null">
        create_time = #{record.createTime},
      </if>
      <if test="record.updateTime != null">
        update_time = #{record.updateTime},
      </if>
      <if test="record.feishuCalendarId != null">
        feishu_calendar_id = #{record.feishuCalendarId},
      </if>
      <if test="record.feishuScheduleId != null">
        feishu_schedule_id = #{record.feishuScheduleId},
      </if>
      <if test="record.applicationApproved != null">
        application_approved = #{record.applicationApproved},
      </if>
    </set>
    where id = #{record.id}
  </update>
  <update id="batchDeleteInterviewPlans">
    update hy_partner_interview_plan
    set deleted = 1
    where id in
    <foreach collection="interviewPlanIds" item="id" open="(" separator="," close=")">
      #{id}
    </foreach>
  </update>
  <update id="updateInterviewRoomStatus">
    update hy_partner_interview_plan
    set room_status = #{roomStatus},update_time = now()
    <where>
      <if test="record.roomStatus !=null and record.roomStatus!=''">
        and room_status = #{record.roomStatus}
      </if>
      <if test="record.startTime !=null'">
        and start_time &gt;= #{record.startTime}
      </if>
      <if test="record.endTime !=null">
        and end_time &lt;= #{record.endTime}
      </if>
      <if test="record.deleted !=null and record.deleted!=''">
        and deleted = #{deleted}
      </if>
    </where>
  </update>

  <update id="openInterviewRoom">
    update hy_partner_interview_plan
    set room_status = #{roomStatus},update_time = now()
    <where>
      partner_line_id in
      <foreach collection="lineIds" item="item" index="index" open="(" separator="," close=")">
        #{item}
      </foreach>
    and deleted = 0
    </where>
  </update>

  <update id="closeInterviewRoom">
    update hy_partner_interview_plan
    set room_status = #{roomStatus},update_time = now()
    <where>
      <if test="record.roomStatus !=null and record.roomStatus!=''">
        and room_status = #{record.roomStatus}
      </if>
      <if test="record.startTime !=null">
        and end_time &gt;= #{record.startTime}
      </if>
      <if test="record.endTime !=null">
        and end_time &lt;= #{record.endTime}
      </if>
      <if test="record.deleted !=null and record.deleted!=''">
        and deleted = #{deleted}
      </if>
    </where>
  </update>

    <!-- 修改面试实际开始时间 -->
    <update id="updateActualStartTime">
      update hy_partner_interview_plan
        set actual_start_time = IF(actual_start_time is NULL, #{now}, actual_start_time)
        where id = #{id}
    </update>

    <select id="getCurrentDateInterviewCount" resultType="com.cool.store.dto.partner.AdvanceLineDTO">
      select interview_date as date,count(1) as count from hy_partner_interview_plan
      <where>
        <if test="userId!=null and userId!=''">
            and interviewer = #{userId}
        </if>
        <if test="currentDate!=null and currentDate!=''">
          and interview_date BETWEEN #{currentDate} and #{endDate}
        </if>
        and deleted = 0 and application_approved = 1
      </where>
      group by interview_date
    </select>


  <select id="getInterviewCount" resultType="com.cool.store.dto.partner.SpecialDateRangeInterviewCountDTO">
    select
      ifnull(sum(if(interview_date = #{currentDate} and (start_time>now() or (start_time <![CDATA[<]]> now() and room_status!=2)),1,0)),0) as currentDayInterviewCount,
      ifnull(sum(if(start_time>#{startTime} and end_time <![CDATA[<]]> #{endTime},1,0)),0) as lastSevenDayInterviewCount
    FROM hy_partner_interview_plan
    where interviewer = #{userId}
    and deleted = 0
    and application_approved = 1
  </select>

  <select id="getInterviewPlanList" resultMap="BaseResultMap">
    select * from hy_partner_interview_plan
    <where>
      <if test="userId!=null and userId!=''">
        and interviewer = #{userId}
      </if>
      <if test="currentDay!=null and currentDay!=''">
        and interview_date = #{currentDay}
      </if>
      and (start_time>now() or (start_time<![CDATA[<]]>now() and room_status!=2))
      and deleted = 0
      and application_approved = 1
      order by start_time
    </where>
  </select>

  <select id="getPartnerInterviewInfoList" resultType="com.cool.store.dto.partner.PartnerInterviewInfoDTO">
    select
      hpli.id as partnerLineId,
      hpli.partner_id as partnerId,
      hpli.deadline as deadline,
      hpli.workflow_status as status,
      a.id as interviewId,
      a.auth_code as authCode,
      a.approve_time as approveTime,
      a.process_info as processInfo,
      b.start_time as startTime,
      b.end_time as endTime,
      b.interviewer as interviewer,
      b.create_time as createTime,
      b.room_id as roomId,
      b.id as id,
      hpci.intention_contract_no as intentionContractNo
    from hy_partner_line_info hpli
    left join hy_partner_interview a on hpli.id = a.partner_line_id
    left join hy_partner_interview_plan b on a.interview_plan_id = b.id
    left join hy_partner_certification_info hpci on hpci.partner_interview_id =  a.id
    <where>
      and hpli.deleted = 0 and hpli.line_status!=3
      <if test="filter">
        and b.deleted = 0
      </if>
      <if test="workflowStage!=null and workflowStage!=''">
        and hpli.workflow_stage = #{workflowStage}
      </if>
      <if test="workflowStatus!=null and workflowStatus!=''">
        and hpli.workflow_status = #{workflowStatus}
      </if>
      <if test="userId!=null and userId!=''">
        and hpli.investment_manager = #{userId}
      </if>
    </where>

  </select>

  <select id="getInterviewList" resultType="com.cool.store.vo.interview.InterviewVO">
    select hpip.id   as interviewPlanId,
    hpip.partner_id  as partnerId,
    hpui.username    as partnerName,
    hpui.mobile      as partnerMobile,
    hpip.room_id     as roomId,
    hpip.start_time  as startTime,
    hpip.interviewer as interviewerId,
    eu.name          as interviewerName,
    eu.mobile        as interviewerMobile,
    hpip.room_status as roomStatus,
    hpip.end_time    as endTime
    from hy_partner_interview_plan hpip
    left join hy_partner_interview hpi on hpip.id = hpi.interview_plan_id
    left join hy_partner_line_info hpll on hpip.partner_line_id = hpll.id
    left join hy_partner_user_info hpui on hpui.partner_id = hpip.partner_id
    left join enterprise_user eu on hpip.interviewer = eu.user_id
    <where>
      hpip.deleted = 0 and hpi.deleted = 0 and hpi.status != 1
      <if test="record.partnerName !=null and record.partnerName!=''">
        and hpui.username like concat('%',#{record.partnerName},'%')
      </if>
      <if test="record.partnerMobile !=null and record.partnerMobile!=''">
        and hpui.mobile like concat('%',#{record.partnerMobile},'%')
      </if>
      <if test="record.roomId !=null and record.roomId!=''">
        and hpip.room_id like concat('%', #{record.roomId}, '%')
      </if>
      <if test="record.interviewerName !=null and record.interviewerName!=''">
        and eu.name like concat('%',#{record.interviewerName},'%')
      </if>
      <if test="record.interviewerMobile !=null and record.interviewerMobile!=''">
        and eu.mobile like concat('%',#{record.interviewerMobile},'%')
      </if>
      <if test="record.roomStatus !=null">
        and hpip.room_status = #{record.roomStatus}
      </if>
      <if test="record.startTime !=null and record.startTime != ''">
        and hpip.start_time &gt;= #{record.startTime}
      </if>
      <if test="record.endTime != null and record.endTime != ''">
        and hpip.end_time &lt;= #{record.endTime}
      </if>
    </where>

  </select>
  <select id="getInterviewInfo" resultType="com.cool.store.vo.interview.InterviewVO">
    select hpip.id          as interviewPlanId,
           hpi.id           as interviewId,
           hpll.id          as partnerLineId,
           hpci.qualify_verify_id as qualifyVerifyId,
           hpci.intention_contract_no as intentionContractNo,
           hpll.workflow_stage as workflowStage,
           hpll.line_status      as lineStatus,
           hpi.pass_time    as passTime,
           hpi.pass_reason  as passReason,
           hpi.recorder     as recorderId,
           hpi.record_time  as recordTime,
           hpi.summary      as summary,
           hpi.process_info as processInfo,
           hpi.auth_code    as authCode,
           hpi.expiry_date  as expiryDate,
           hpi.pass_pdf_url as passPdfUrl,
           hpi.pass_image_url as passImageUrl,
           hpui.username    as partnerName,
           hpui.mobile      as partnerMobile,
           hpip.room_id     as roomId,
           hpip.start_time  as startTime,
           hpui.username    as interviewerName,
           hpui.mobile      as interviewerMobile,
           hpip.room_status as roomStatus,
           hpip.end_time    as endTime,
           hpip.partner_id  as partnerId,
           hpip.interviewer as interviewerId,
           hpip.feishu_calendar_id as feishuCalendarId,
           hpip.feishu_schedule_id as feishuScheduleId,
           hpi.status as status,
           hpip.actual_start_time actualStartTime,
           hpip.actual_end_time actualEndTime
    from hy_partner_interview_plan hpip
           left join hy_partner_line_info hpll on hpip.partner_line_id = hpll.id
           left join hy_partner_user_info hpui on hpui.partner_id = hpip.partner_id
           left join hy_partner_interview hpi on hpip.id = hpi.interview_plan_id
           left join hy_partner_certification_info hpci on hpci.partner_interview_id = hpi.id
    where hpip.id = #{interviewPlanId} and hpip.deleted = 0 and hpi.deleted = 0
  </select>
  <select id="selectBySelective" resultType="com.cool.store.entity.HyPartnerInterviewPlanDO">
    select
    <include refid="Base_Column_List"/>
    from hy_partner_interview_plan
    <where>
      <if test="record.id !=null and record.id!=''">
        and id = #{record.id}
      </if>
      <if test="record.partnerLineId !=null and record.partnerLineId!=''">
        and partner_line_id = #{record.partnerLineId}
      </if>
      <if test="record.partnerId !=null and record.partnerId!=''">
        and partner_id = #{record.partnerId}
      </if>
      <if test="record.startTime !=null">
        and start_time = #{record.startTime}
      </if>
      <if test="record.endTime !=null">
        and end_time = #{record.endTime}
      </if>
      <if test="record.interviewer !=null and record.interviewer!=''">
        and interviewer = #{record.interviewer}
      </if>
      <if test="record.createTime !=null and record.createTime!=''">
        and create_time = #{record.createTime}
      </if>
      <if test="record.roomId !=null and record.roomId!=''">
        and room_id = #{record.roomId}
      </if>
      <if test="record.roomStatus !=null and record.roomStatus!=''">
        and room_status = #{record.roomStatus}
      </if>
      <if test="record.interviewDate !=null and record.interviewDate!=''">
        and interview_date = #{record.interviewDate}
      </if>
      <if test="record.deleted !=null">
          and deleted = #{record.deleted}
      </if>
    </where>

  </select>

  <select id="selectInterviewIdByLineId" resultType="java.lang.Long">
    select id from hy_partner_interview_plan where partner_line_id = #{lineId} and deleted = '0'
  </select>

  
  <select id="getHyPartnerInterviewPlanByLineIds" resultMap="BaseResultMap">
    select <include refid="Base_Column_List"></include>
    from hy_partner_interview_plan
    where deleted = 0
    <if test="lindIds!=null and lindIds.size>0">
      <foreach collection="lindIds" item="lindId" open="and partner_line_id in (" close=")" separator=",">
        #{lindId}
      </foreach>
    </if>
  </select>




  <!-- 查询用户基本信息 -->
  <select id="getEnterpriseUserBaseInfo" resultType="com.cool.store.vo.EnterpriseUserBaseInfoVO">
    select name, mobile
      from enterprise_user
      where user_id = #{userId}
  </select>

  <!-- 获取面试房间状态 -->
  <select id="getRoomStatus" resultType="java.lang.Integer">
    select room_status
      from hy_partner_interview_plan
      where id = #{interviewPlanId}
  </select>

  <!-- 获取面试开始时间 -->
  <select id="getInterviewStartTime" resultType="java.lang.String">
    select start_time
      from hy_partner_interview_plan
      where id = #{interviewPlanId}
  </select>

  <!-- 根据id获取面试计划信息 -->
  <select id="getInterviewPlanById" resultType="com.cool.store.entity.HyPartnerInterviewPlanDO">
    select <include refid="Base_Column_List"></include>
      from hy_partner_interview_plan
      where id = #{interviewPlanId}
  </select>
  <select id="getInterviewPlanByLineId" resultType="com.cool.store.entity.HyPartnerInterviewPlanDO">
    select <include refid="Base_Column_List"></include>
      from hy_partner_interview_plan
      where partner_line_id = #{lineId} and deleted = 0
  </select>
  <select id="selectRemindInterviewPlan" resultType="com.cool.store.dto.message.RemindInterviewMsgDTO">
    select hpip.partner_id as partnerId,
           hpip.interviewer as interviewerId,
           hpui.username as partnerName,
           hpui.mobile as partnerMobile,
           hpip.start_time as startTime
    from hy_partner_interview_plan hpip
             left join hy_partner_line_info hpll on hpip.partner_line_id = hpll.id
             left join hy_partner_interview hpi on hpip.id = hpi.interview_plan_id
            left join hy_partner_user_info hpui on hpui.partner_id = hpip.partner_id
             where hpip.deleted = 0
               and hpi.deleted = 0
               and hpll.deleted = 0
               and hpip.start_time &gt;= #{startTime}
                and hpip.start_time &lt;= #{endTime}
               and hpll.workflow_stage = #{workflowStage}
                and hpll.workflow_status = #{workflowStatus}


  </select>

  <update id="updateLineId">
    update hy_partner_interview_plan
    set partner_line_id = #{newLineId}  , partner_id = #{newPartnerId}
    where partner_line_id = #{oldLineId}
  </update>
</mapper>