<?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.HyInspectionMapper">
  <resultMap id="BaseResultMap" type="com.cool.store.entity.HyInspectionDO">
    <constructor>
      <idArg column="id" javaType="java.lang.Long" />
      <arg column="interview_plan_id" javaType="java.lang.Long" />
      <arg column="operator_user_id" javaType="java.lang.String" />
      <arg column="type" javaType="java.lang.Integer" />
      <arg column="status" javaType="java.lang.Integer" />
      <arg column="files" javaType="java.lang.String" />
      <arg column="description" javaType="java.lang.String" />
      <arg column="inspection_time" javaType="java.lang.String" />
      <arg column="creator" javaType="java.lang.String" />
      <arg column="updator" javaType="java.lang.String" />
      <arg column="remark" javaType="java.lang.String" />
      <arg column="deleted" javaType="java.lang.Boolean" />
      <arg column="create_time" javaType="java.lang.String" />
      <arg column="update_time" javaType="java.lang.String" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    id, interview_plan_id, operator_user_id, type, status, files, description, inspection_time, 
    creator, updator, remark, deleted, create_time, update_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from hy_inspection
    where id = #{id}
    and deleted = 0
  </select>

  <!-- 使用 interviewPlanId 查询面试稽核信息 -->
  <select id="selectByInterviewPlanId" resultType="com.cool.store.entity.HyInspectionDO">
    select
    <include refid="Base_Column_List" />
    from hy_inspection
    where interview_plan_id = #{interviewPlanId}
    and deleted = 0
  </select>

    <select id="interviewInspectionGetList" resultType="com.cool.store.vo.interview.InterviewInspectionVO">
      SELECT
      eu.`name` AS interviewerName,
      eu.mobile AS interviewerMobile,
      hpui.username AS intervieweeName,
      hpui.mobile AS intervieweeMobile,
      trim(BOTH '/' FROM trim(hoai.area_path))  AS wantShopAreaName,
      hi.create_time AS interviewPassTime,
      hi.`status` AS `status`,
      euj.`name` AS auditorName,
      euj.mobile AS auditorMobile,
      hi.inspection_time AS inspectionTime,
      hi.id AS id,
      hi.interview_plan_id AS interviewPlanId
      FROM
      hy_inspection hi
      LEFT JOIN enterprise_user euj ON hi.operator_user_id = euj.user_id
      LEFT JOIN hy_partner_interview_plan hpip ON hi.interview_plan_id = hpip.id
      AND hi.deleted = 0
      AND hpip.deleted = 0
      LEFT JOIN hy_partner_interview hpi ON hpi.interview_plan_id = hpip.id
      AND hpi.deleted = 0
      LEFT JOIN enterprise_user eu ON hpi.interviewer = eu.user_id
      LEFT JOIN hy_partner_user_info hpui ON hpip.partner_id = hpui.partner_id
      LEFT JOIN hy_inspection_setting_mapping hism ON hpui.want_shop_area = hism.open_area_mapping_id
      AND hism.deleted = 0
      LEFT JOIN hy_inspection_setting his ON hism.inspection_setting_id = his.id
      AND his.deleted = 0
      AND eu.deleted = 0
      LEFT JOIN hy_open_area_info hoai ON hpui.want_shop_area = hoai.id
      AND hoai.deleted = 0
      <where>
        <if test="userId !=null and userId !=''">
         and ((his.inspection_user_id = #{userId} and  hi.`status`=0 ) OR hi.operator_user_id = #{userId})
        </if>
        <if test="interviewerName !=null and interviewerName !=''">
         and eu.`name` like  concat('%',#{interviewerName},'%')
        </if>
        <if test="interviewerMobile !=null and interviewerMobile !=''">
         and eu.mobile like  concat('%',#{interviewerMobile},'%')
        </if>
        <if test="intervieweeName !=null and intervieweeName !=''">
         and hpui.username like  concat('%',#{intervieweeName},'%')
        </if>
        <if test="intervieweeMobile !=null and intervieweeMobile !=''">
         and hpui.mobile like  concat('%',#{intervieweeMobile},'%')
        </if>
        <if test="status !=null">
         and hi.`status`=#{status}
        </if>
        <if test="codeList !=null and codeList.size>0">
         and  hpui.want_shop_area in
              <foreach collection="codeList" separator="," open="(" close=")" item="code" >#{code}</foreach>
        </if>
        <if test="startTime != null and startTime != ''">
          and hpi.pass_time &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
          and hpi.pass_time &lt;= #{endTime}
        </if>
      </where>
        order by hi.id  desc
    </select>
  <select id="interviewInspectionGetDetail" resultType="com.cool.store.vo.interview.InterviewInspectionInfo">
    SELECT  b.`name` as  auditorName, b.mobile  as  auditorMobile,inspection_time as  inspectionTime,`status` ,description,files as filesStr
    FROM hy_inspection  a left  join    enterprise_user  b on a.operator_user_id=b.user_id and a.deleted=0  and b.deleted=0
    where a.id=#{id}
  </select>
    <select id="interviewInspectionResultGetList" resultType="com.cool.store.vo.interview.InterviewInspectionResultVO">
      SELECT
      hi.id,hi.interview_plan_id as interviewPlanId,
      hpui.username AS intervieweeName,
      hpui.mobile AS intervieweeMobile,
      trim(BOTH '/' FROM trim(hoai.area_path))  AS wantShopAreaName,
      hi.create_time AS interviewPassTime,
      hi.`status` AS `status`,
      euj.`name` AS auditorName,
      euj.mobile AS auditorMobile,
      hi.inspection_time AS inspectionTime,
      hi.id AS id,
      hi.interview_plan_id AS interviewPlanId
      FROM
      hy_inspection hi
      LEFT JOIN enterprise_user euj ON hi.operator_user_id = euj.user_id
      LEFT JOIN hy_partner_interview_plan hpip ON hi.interview_plan_id = hpip.id
      AND hi.deleted = 0
      AND hpip.deleted = 0 and `status`!=0
      LEFT JOIN hy_partner_interview hpi ON hpi.interview_plan_id = hpip.id
      AND hpi.deleted = 0
      LEFT JOIN hy_partner_user_info hpui ON hpip.partner_id = hpui.partner_id
      LEFT JOIN hy_open_area_info hoai ON hpui.want_shop_area = hoai.id
      AND hoai.deleted = 0
      <where>
        <if test="userId !=null and userId !=''">
          and hpi.interviewer = #{userId}
        </if>
        <if test="intervieweeName !=null and intervieweeName !=''">
          and hpui.username like  concat('%',#{intervieweeName},'%')
        </if>
        <if test="intervieweeMobile !=null and intervieweeMobile !=''">
          and hpui.mobile like  concat('%',#{intervieweeMobile},'%')
        </if>
        <if test="status !=null">
          and hi.`status`=#{status}
        </if>
        <if test="codeList !=null">
          and  hpui.want_shop_area in <foreach collection="codeList" separator="," open="(" close=")" item="code" >#{code}</foreach>
        </if>
        <if test="startTime != null and startTime != ''">
          and hpi.pass_time &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
          and hpi.pass_time &lt;= #{endTime}
        </if>
      </where>
      order by hi.id  desc
    </select>
  <select id="interviewInspectionGetProvinceList" resultType="com.cool.store.vo.interview.InterviewInspectionVO">
    SELECT
    eu.`name` AS interviewerName,
    eu.mobile AS interviewerMobile,
    hpui.username AS intervieweeName,
    hpui.mobile AS intervieweeMobile,
    trim(BOTH '/' FROM trim(hoai.area_path))  AS wantShopAreaName,
    hi.create_time AS interviewPassTime,
    hi.`status` AS `status`,
    euj.`name` AS auditorName,
    euj.mobile AS auditorMobile,
    hi.inspection_time AS inspectionTime,
    hi.id AS id,
    hi.interview_plan_id AS interviewPlanId
    FROM
    hy_inspection hi
    LEFT JOIN enterprise_user euj ON hi.operator_user_id = euj.user_id
    LEFT JOIN hy_partner_interview_plan hpip ON hi.interview_plan_id = hpip.id
    AND hi.deleted = 0
    AND hpip.deleted = 0
    LEFT JOIN hy_partner_interview hpi ON hpi.interview_plan_id = hpip.id
    AND hpi.deleted = 0
    LEFT JOIN enterprise_user eu ON hpi.interviewer = eu.user_id
    LEFT JOIN hy_partner_user_info hpui ON hpip.partner_id = hpui.partner_id
    LEFT JOIN hy_inspection_setting_mapping hism ON hpui.want_shop_area = hism.open_area_mapping_id
    AND hism.deleted = 0
    LEFT JOIN hy_inspection_setting his ON hism.inspection_setting_id = his.id
    AND his.deleted = 0
    AND eu.deleted = 0
    LEFT JOIN hy_open_area_info hoai ON hpui.want_shop_area = hoai.id
    AND hoai.deleted = 0
    left join  ( SELECT  c.* FROM `hy_open_area_info` a  left join  hy_open_area_info b    on a.`id`=b.`parent_id`
    left join  hy_open_area_info c    on b.`id`=c.`parent_id`
    WHERE a.`id`=#{wantShopArea}  )   mdd  on  hpui.want_shop_area=mdd.`id`
    <where>
      <if test="true">
        and mdd.id is not null
      </if>
      <if test="userId !=null and userId !=''">
        and ((his.inspection_user_id = #{userId} and  hi.`status`=0 ) OR hi.operator_user_id = #{userId})
      </if>
      <if test="interviewerName !=null and interviewerName !=''">
        and eu.`name` like  concat('%',#{interviewerName},'%')
      </if>
      <if test="interviewerMobile !=null and interviewerMobile !=''">
        and eu.mobile like  concat('%',#{interviewerMobile},'%')
      </if>
      <if test="intervieweeName !=null and intervieweeName !=''">
        and hpui.username like  concat('%',#{intervieweeName},'%')
      </if>
      <if test="intervieweeMobile !=null and intervieweeMobile !=''">
        and hpui.mobile like  concat('%',{intervieweeMobile},'%')
      </if>
      <if test="status !=null">
        and hi.`status`=#{status}
      </if>
      <if test="startTime != null and startTime != ''">
        and hpi.pass_time &gt;= #{startTime}
      </if>
      <if test="endTime != null and endTime != ''">
        and hpi.pass_time &lt;= #{endTime}
      </if>
    </where>
    order by hi.id  desc
  </select>
  <select id="interviewInspectionResultGetProvinceList" resultType="com.cool.store.vo.interview.InterviewInspectionResultVO">
    SELECT
    hi.id,hi.interview_plan_id as interviewPlanId,
    hpui.username AS intervieweeName,
    hpui.mobile AS intervieweeMobile,
    trim(BOTH '/' FROM trim(hoai.area_path))  AS wantShopAreaName,
    hi.create_time AS interviewPassTime,
    hi.`status` AS `status`,
    euj.`name` AS auditorName,
    euj.mobile AS auditorMobile,
    hi.inspection_time AS inspectionTime,
    hi.id AS id,
    hi.interview_plan_id AS interviewPlanId
    FROM
    hy_inspection hi
    LEFT JOIN enterprise_user euj ON hi.operator_user_id = euj.user_id
    LEFT JOIN hy_partner_interview_plan hpip ON hi.interview_plan_id = hpip.id
    AND hi.deleted = 0
    AND hpip.deleted = 0 and `status`!=0
    LEFT JOIN hy_partner_interview hpi ON hpi.interview_plan_id = hpip.id
    AND hpi.deleted = 0
    LEFT JOIN hy_partner_user_info hpui ON hpip.partner_id = hpui.partner_id
    LEFT JOIN hy_open_area_info hoai ON hpui.want_shop_area = hoai.id
    AND hoai.deleted = 0
    left join  ( SELECT  c.* FROM `hy_open_area_info` a  left join  hy_open_area_info b    on a.`id`=b.`parent_id`
    left join  hy_open_area_info c    on b.`id`=c.`parent_id`
    WHERE a.`id`=#{wantShopArea}  )   mdd  on  hpui.want_shop_area=mdd.`id`
    <where>
      <if test="true">
        and mdd.id is not null
      </if>
      <if test="userId !=null and userId !=''">
        and hpi.interviewer = #{userId}
      </if>
      <if test="intervieweeName !=null and intervieweeName !=''">
        and hpui.username like  concat('%',#{intervieweeName},'%')
      </if>
      <if test="intervieweeMobile !=null and intervieweeMobile !=''">
        and hpui.mobile like  concat('%',#{intervieweeMobile},'%')
      </if>
      <if test="status !=null">
        and hi.`status`=#{status}
      </if>
      <if test="startTime != null and startTime != ''">
        and hpi.pass_time &gt;= #{startTime}
      </if>
      <if test="endTime != null and endTime != ''">
        and hpi.pass_time &lt;= #{endTime}
      </if>
    </where>
    order by hi.id  desc
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from hy_inspection
    where id = #{id}
  </delete>
  <insert id="insert" parameterType="com.cool.store.entity.HyInspectionDO">
    insert into hy_inspection (id, interview_plan_id, operator_user_id, 
      type, status, files, 
      description, inspection_time, creator, 
      updator, remark, deleted, 
      create_time, update_time)
    values (#{id}, #{interviewPlanId}, #{operatorUserId}, 
      #{type}, #{status}, #{files}, 
      #{description}, #{inspectionTime}, #{creator}, 
      #{updator}, #{remark}, #{deleted}, 
      #{createTime}, #{updateTime})
  </insert>
  <insert id="insertSelective" parameterType="com.cool.store.entity.HyInspectionDO">
    insert into hy_inspection
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="interviewPlanId != null">
        interview_plan_id,
      </if>
      <if test="operatorUserId != null">
        operator_user_id,
      </if>
      <if test="type != null">
        type,
      </if>
      <if test="status != null">
        status,
      </if>
      <if test="files != null">
        files,
      </if>
      <if test="description != null">
        description,
      </if>
      <if test="inspectionTime != null">
        inspection_time,
      </if>
      <if test="creator != null">
        creator,
      </if>
      <if test="updator != null">
        updator,
      </if>
      <if test="remark != null">
        remark,
      </if>
      <if test="deleted != null">
        deleted,
      </if>
      <if test="createTime != null">
        create_time,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id},
      </if>
      <if test="interviewPlanId != null">
        #{interviewPlanId},
      </if>
      <if test="operatorUserId != null">
        #{operatorUserId},
      </if>
      <if test="type != null">
        #{type},
      </if>
      <if test="status != null">
        #{status},
      </if>
      <if test="files != null">
        #{files},
      </if>
      <if test="description != null">
        #{description},
      </if>
      <if test="inspectionTime != null">
        #{inspectionTime},
      </if>
      <if test="creator != null">
        #{creator},
      </if>
      <if test="updator != null">
        #{updator},
      </if>
      <if test="remark != null">
        #{remark},
      </if>
      <if test="deleted != null">
        #{deleted},
      </if>
      <if test="createTime != null">
        #{createTime},
      </if>
      <if test="updateTime != null">
        #{updateTime},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cool.store.entity.HyInspectionDO">
    update hy_inspection
    <set>
      <if test="interviewPlanId != null">
        interview_plan_id = #{interviewPlanId},
      </if>
      <if test="operatorUserId != null">
        operator_user_id = #{operatorUserId},
      </if>
      <if test="type != null">
        type = #{type},
      </if>
      <if test="status != null">
        status = #{status},
      </if>
      <if test="files != null">
        files = #{files},
      </if>
      <if test="description != null">
        description = #{description},
      </if>
      <if test="inspectionTime != null">
        inspection_time = #{inspectionTime},
      </if>
      <if test="creator != null">
        creator = #{creator},
      </if>
      <if test="updator != null">
        updator = #{updator},
      </if>
      <if test="remark != null">
        remark = #{remark},
      </if>
      <if test="deleted != null">
        deleted = #{deleted},
      </if>
      <if test="createTime != null">
        create_time = #{createTime},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime},
      </if>
    </set>
    where id = #{id}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cool.store.entity.HyInspectionDO">
    update hy_inspection
    set interview_plan_id = #{interviewPlanId},
      operator_user_id = #{operatorUserId},
      type = #{type},
      status = #{status},
      files = #{files},
      description = #{description},
      inspection_time = #{inspectionTime},
      creator = #{creator},
      updator = #{updator},
      remark = #{remark},
      deleted = #{deleted},
      create_time = #{createTime},
      update_time = #{updateTime}
    where id = #{id}
  </update>
</mapper>