<?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.LineInfoMapper">
  <resultMap id="BaseResultMap" type="com.cool.store.entity.LineInfoDO">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="partner_id" jdbcType="VARCHAR" property="partnerId" />
    <result column="region_id" jdbcType="BIGINT" property="regionId" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="sex" jdbcType="VARCHAR" property="sex" />
    <result column="want_shop_area_id" jdbcType="BIGINT" property="wantShopAreaId" />
    <result column="live_address" jdbcType="VARCHAR" property="liveAddress" />
    <result column="workflow_stage" jdbcType="TINYINT" property="workflowStage" />
    <result column="workflow_sub_stage" jdbcType="TINYINT" property="workflowSubStage" />
    <result column="workflow_sub_stage_status" jdbcType="TINYINT" property="workflowSubStageStatus" />
    <result column="select_site_num" jdbcType="INTEGER" property="selectSiteNum" />
    <result column="prepare_shop_num" jdbcType="INTEGER" property="prepareShopNum" />
    <result column="open_shop_num" jdbcType="INTEGER" property="openShopNum" />
    <result column="line_source" jdbcType="BIGINT" property="lineSource" />
    <result column="investment_manager" jdbcType="VARCHAR" property="investmentManager" />
    <result column="development_manager" jdbcType="VARCHAR" property="developmentManager" />
    <result column="first_interviewer" jdbcType="VARCHAR" property="firstInterviewer" />
    <result column="second_interviewer" jdbcType="VARCHAR" property="secondInterviewer" />
    <result column="user_portrait" jdbcType="VARCHAR" property="userPortrait" />
    <result column="join_status" jdbcType="BIT" property="joinStatus" />
    <result column="line_status" jdbcType="TINYINT" property="lineStatus" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="create_user_id" jdbcType="VARCHAR" property="createUserId" />
    <result column="update_user_id" jdbcType="VARCHAR" property="updateUserId" />
    <result column="deleted" jdbcType="BIT" property="deleted" />
    <result column="partner_num" jdbcType="VARCHAR" property="partnerNum" />
    <result column="big_region_id" jdbcType="BIGINT" property="bigRegionId" />
  </resultMap>
    <insert id="insertOrUpdate" parameterType="com.cool.store.entity.LineInfoDO" useGeneratedKeys="true" keyProperty="id">
      INSERT INTO xfsg_line_info
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="param.id != null and param.id != ''">
          id,
        </if>
        <if test="param.partnerId != null and param.partnerId != ''">
          partner_id,
        </if>
        <if test="param.regionId != null">
          region_id,
        </if>
        <if test="param.bigRegionId != null">
          big_region_id,
        </if>
        <if test="param.mobile != null and param.mobile != ''">
          mobile,
        </if>
        <if test="param.username != null and param.username != ''">
          username,
        </if>
        <if test="param.sex != null and param.sex != ''">
          sex,
        </if>
        <if test="param.wantShopAreaId != null">
          want_shop_area_id,
        </if>
        <if test="param.liveAddress != null and param.liveAddress != ''">
          live_address,
        </if>
        <if test="param.workflowStage != null">
          workflow_stage,
        </if>
        <if test="param.workflowSubStage != null">
          workflow_sub_stage,
        </if>
        <if test="param.workflowSubStageStatus != null">
          workflow_sub_stage_status,
        </if>
        <if test="param.selectSiteNum != null">
          select_site_num,
        </if>
        <if test="param.prepareShopNum != null">
          prepare_shop_num,
        </if>
        <if test="param.openShopNum != null">
          open_shop_num,
        </if>
        <if test="param.lineSource != null">
          line_source,
        </if>
        <if test="param.investmentManager != null and param.investmentManager != ''">
          investment_manager,
        </if>
        <if test="param.developmentManager != null and param.developmentManager != ''">
          development_manager,
        </if>
        <if test="param.firstInterviewer != null and param.firstInterviewer != ''">
          first_interviewer,
        </if>
        <if test="param.secondInterviewer != null and param.secondInterviewer != ''">
          second_interviewer,
        </if>
        <if test="param.userPortrait != null and param.userPortrait != ''">
          user_portrait,
        </if>
        <if test="param.joinStatus != null">
          join_status,
        </if>
        <if test="param.lineStatus != null">
          line_status,
        </if>
        <if test="param.createTime != null">
          create_time,
        </if>
        <if test="param.updateTime != null">
          update_time,
        </if>
        <if test="param.createUserId != null and param.createUserId != ''">
          create_user_id,
        </if>
        <if test="param.updateUserId != null and param.updateUserId != ''">
          update_user_id,
        </if>
        <if test="param.deleted != null">
          deleted,
        </if>
        <if test="param.partnerNum != null">
          partner_num,
        </if>
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="param.id != null and param.id != ''">
          #{param.id},
        </if>
        <if test="param.partnerId != null and param.partnerId != ''">
          #{param.partnerId},
        </if>
        <if test="param.regionId != null">
          #{param.regionId},
        </if>
        <if test="param.bigRegionId != null">
          #{param.bigRegionId},
        </if>
        <if test="param.mobile != null and param.mobile != ''">
          #{param.mobile},
        </if>
        <if test="param.username != null and param.username != ''">
          #{param.username},
        </if>
        <if test="param.sex != null and param.sex != ''">
          #{param.sex},
        </if>
        <if test="param.wantShopAreaId != null">
          #{param.wantShopAreaId},
        </if>
        <if test="param.liveAddress != null and param.liveAddress != ''">
          #{param.liveAddress},
        </if>
        <if test="param.workflowStage != null">
          #{param.workflowStage},
        </if>
        <if test="param.workflowSubStage != null">
          #{param.workflowSubStage},
        </if>
        <if test="param.workflowSubStageStatus != null">
          #{param.workflowSubStageStatus},
        </if>
        <if test="param.selectSiteNum != null">
          #{param.selectSiteNum},
        </if>
        <if test="param.prepareShopNum != null">
          #{param.prepareShopNum},
        </if>
        <if test="param.openShopNum != null">
          #{param.openShopNum},
        </if>
        <if test="param.lineSource != null">
          #{param.lineSource},
        </if>
        <if test="param.investmentManager != null and param.investmentManager != ''">
          #{param.investmentManager},
        </if>
        <if test="param.developmentManager != null and param.developmentManager != ''">
          #{param.developmentManager},
        </if>
        <if test="param.firstInterviewer != null and param.firstInterviewer != ''">
          #{param.firstInterviewer},
        </if>
        <if test="param.secondInterviewer != null and param.secondInterviewer != ''">
          #{param.secondInterviewer},
        </if>
        <if test="param.userPortrait != null and param.userPortrait != ''">
          #{param.userPortrait},
        </if>
        <if test="param.joinStatus != null">
          #{param.joinStatus},
        </if>
        <if test="param.lineStatus != null">
          #{param.lineStatus},
        </if>
        <if test="param.createTime != null">
          #{param.createTime},
        </if>
        <if test="param.updateTime != null">
          #{param.updateTime},
        </if>
        <if test="param.createUserId != null and param.createUserId != ''">
          #{param.createUserId},
        </if>
        <if test="param.updateUserId != null and param.updateUserId != ''">
          #{param.updateUserId},
        </if>
        <if test="param.deleted != null">
          #{param.deleted},
        </if>
        <if test="param.partnerNum != null">
          #{param.partnerNum},
        </if>
      </trim>
      ON DUPLICATE KEY UPDATE
      <trim suffixOverrides=",">
        <if test="param.partnerId != null and param.partnerId != ''">
          partner_id = #{param.partnerId},
        </if>
        <if test="param.regionId != null">
          region_id = #{param.regionId},
        </if>
        <if test="param.bigRegionId != null">
          big_region_id = #{param.bigRegionId},
        </if>
        <if test="param.mobile != null and param.mobile != ''">
          mobile = #{param.mobile},
        </if>
        <if test="param.username != null and param.username != ''">
          username = #{param.username},
        </if>
        <if test="param.sex != null and param.sex != ''">
          sex = #{param.sex},
        </if>
        <if test="param.wantShopAreaId != null">
          want_shop_area_id = #{param.wantShopAreaId},
        </if>
        <if test="param.liveAddress != null and param.liveAddress != ''">
          live_address = #{param.liveAddress},
        </if>
        <if test="param.workflowStage != null">
          workflow_stage = #{param.workflowStage},
        </if>
        <if test="param.workflowSubStage != null">
          workflow_sub_stage = #{param.workflowSubStage},
        </if>
        <if test="param.workflowSubStageStatus != null">
          workflow_sub_stage_status = #{param.workflowSubStageStatus},
        </if>
        <if test="param.selectSiteNum != null">
          select_site_num = #{param.selectSiteNum},
        </if>
        <if test="param.prepareShopNum != null">
          prepare_shop_num = #{param.prepareShopNum},
        </if>
        <if test="param.openShopNum != null">
          open_shop_num = #{param.openShopNum},
        </if>
        <if test="param.lineSource != null">
          line_source = #{param.lineSource},
        </if>
        <if test="param.investmentManager != null and param.investmentManager != ''">
          investment_manager = #{param.investmentManager},
        </if>
        <if test="param.developmentManager != null and param.developmentManager != ''">
          development_manager = #{param.developmentManager},
        </if>
        <if test="param.firstInterviewer != null and param.firstInterviewer != ''">
          first_interviewer = #{param.firstInterviewer},
        </if>
        <if test="param.secondInterviewer != null and param.secondInterviewer != ''">
          second_interviewer = #{param.secondInterviewer},
        </if>
        <if test="param.userPortrait != null and param.userPortrait != ''">
          user_portrait = #{param.userPortrait},
        </if>
        <if test="param.joinStatus != null">
          join_status = #{param.joinStatus},
        </if>
        <if test="param.lineStatus != null">
          line_status = #{param.lineStatus},
        </if>
        <if test="param.createTime != null">
          create_time = #{param.createTime},
        </if>
        <if test="param.updateTime != null">
          update_time = #{param.updateTime},
        </if>
        <if test="param.createUserId != null and param.createUserId != ''">
          create_user_id = #{param.createUserId},
        </if>
        <if test="param.updateUserId != null and param.updateUserId != ''">
          update_user_id = #{param.updateUserId},
        </if>
        <if test="param.deleted != null">
          deleted = #{param.deleted},
        </if>
        <if test="param.partnerNum != null">
          partner_num = #{param.partnerNum}
        </if>
      </trim>
    </insert>
    <update id="toExperiencing">
      UPDATE
      xfsg_line_info
    SET workflow_sub_stage_status = #{code}
    WHERE
    deleted = 0
    AND workflow_sub_stage_status = 85
    AND id in
    <foreach collection="lineIds" item="lineId" open="(" close=")" separator=",">
        #{lineId}
    </foreach>


    </update>

    <select id="getByPartnerId" resultMap="BaseResultMap">
    select * from xfsg_line_info where partner_id = #{partnerId} and deleted = 0
  </select>
  <select id="getByLineId" resultType="com.cool.store.entity.LineInfoDO">
     select * from xfsg_line_info where id = #{lineId} and deleted = 0
  </select>

  <select id="listByInvestmentManager" resultMap="BaseResultMap">
    select * from xfsg_line_info
    where  deleted = 0 and line_status = 1
    <if test="investmentManagerUserId != null and investmentManagerUserId != ''">
      and investment_manager = #{investmentManagerUserId}
    </if>
    <if test="codes !=null and codes.size>0">
      <foreach collection="codes" item="code" open="and workflow_sub_stage_status in (" close=")" separator=",">
        #{code}
      </foreach>
    </if>

  </select>


  <select id="listByInterview" resultMap="BaseResultMap">
    select * from xfsg_line_info
    where  deleted = 0 and line_status = 1
    <if test="codes !=null and codes.size>0">
      <foreach collection="codes" item="code" open="and workflow_sub_stage_status in (" close=")" separator=",">
        #{code}
      </foreach>
    </if>
    <if test="pendingInterviewStatusList !=null and pendingInterviewStatusList.size>0">
      <foreach collection="pendingInterviewStatusList" item="status" open="and (( workflow_sub_stage_status in (" close=")" separator=",">
        #{status}
      </foreach>
      <if test="interviewType != null and interviewType == 1">
        and first_interviewer = #{interviewId}
      </if>
      <if test="interviewType != null and interviewType == 2">
        and second_interviewer = #{interviewId}
      </if>
      )
    </if>
    <if test="notPassingTheInterview != null ">
      or (workflow_sub_stage_status = #{notPassingTheInterview}
      and investment_manager = #{interviewId}
      ))
    </if>


  </select>


  <select id="pendingCount" resultType="com.cool.store.dto.PendingCountDTO">
    SELECT
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status = 5, 1, 0 )  ) AS intendPendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status = 20, 1, 0 ) ) AS interviewPendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status IN ( 30, 40 ), 1, 0 ) ) AS firstInterviewPendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status = 50, 1, 0 ) ) AS payStagePendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status IN ( 63, 70, 80 ), 1, 0 ) ) AS signingPendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status IN ( 85, 90 ), 1, 0 ) ) AS storeExperiencePendingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage_status IN ( 105, 115 ), 1, 0 ) ) AS secondInterviewPendingCount
    FROM
    xfsg_line_info where deleted = 0 and line_status = 1
  </select>


  <select id="investmentData" resultType="com.cool.store.dto.InvestmentCountDTO">
    SELECT
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 1, 1, 0 )  ) AS intendCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 5, 1, 0 ) ) AS interviewCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 10, 1, 0 ) ) AS firstInterviewCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 15, 1, 0 ) ) AS payStageCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 20, 1, 0 ) ) AS signingCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 25, 1, 0 ) ) AS storeExperienceCount,
    sum( IF ( investment_manager = #{userId} AND workflow_sub_stage = 30 and workflow_stage =1, 1, 0 ) ) AS secondInterviewCount
    FROM
    xfsg_line_info where deleted = 0 and line_status = 1 and investment_manager = #{userId}
  </select>

  <select id="lineList" resultMap="BaseResultMap">
    select * from xfsg_line_info a
    <if test="wantShopAreaName!=null">
      left join xfsg_open_area_info b on a.want_shop_area_id = b.id
    </if>
    where  a.deleted = 0 and a.line_status = 1 and a.join_status = 0
    <if test="userId != null and userId != ''">
      and a.investment_manager = #{userId}
    </if>
    <if test="request.workflowSubStage != null">
      and a.workflow_sub_stage = #{request.workflowSubStage}
    </if>
    <if test="wantShopAreaName != null">
      and b.area_path like concat('%',#{wantShopAreaName},'%')
    </if>
    <if test="request.userName != null and request.username!=''">
      and a.username = #{request.userName}
    </if>
    <if test="request.keyword != null and request.keyword!=''">
      and (a.username like concat('%',#{request.keyword},'%') or a.mobile like concat('%',#{request.keyword},'%'))
    </if>
    <if test="request.mobile != null and request.mobile!=''">
      and a.mobile = #{request.mobile}
    </if>
    <if test="request.lineSource != null">
      and a.line_source =  #{request.lineSource}
    </if>
    <if test="request.createTimeStart!=null and request.createTimeEnd!=null">
      and a.create_time between #{request.createTimeStart} and #{request.createTimeEnd}
    </if>
    <if test="request.investmentManagerUserId != null and request.investmentManagerUserId != ''">
      and a.investment_manager = #{request.investmentManagerUserId}
    </if>
    <if test="wantShopAreaIds !=null and wantShopAreaIds.size>0">
      <foreach collection="wantShopAreaIds" item="wantShopAreaId" open="and a.want_shop_area_id in (" close=")" separator=",">
        #{wantShopAreaId}
      </foreach>
    </if>
  </select>


  <select id="partnerList" resultMap="BaseResultMap">
    select * from xfsg_line_info a
    <if test="wantShopAreaName != null">
      left join xfsg_open_area_info b on a.want_shop_area_id = b.id
    </if>
    where  a.deleted = 0 and a.line_status = 1 and a.join_status in (1,2)
    <if test="userId != null and userId != ''">
      and a.investment_manager = #{userId}
    </if>
    <if test="request.joinStatus != null">
      and a.join_status = #{request.joinStatus}
    </if>
    <if test="request.keyword != null and request.keyword!=''">
      and (a.username like concat('%',#{request.keyword},'%') or a.mobile like concat('%',#{request.keyword},'%'))
    </if>
    <if test="wantShopAreaName != null">
      and b.area_path like concat('%',#{wantShopAreaName},'%')
    </if>
    <if test="request.queryUserId!=null and request.queryUserId!=''">
      <if test="request.queryType != null and request.queryType == 1 ">
        and a.investment_manager = #{request.queryUserId}
      </if>
      <if test="request.queryType != null and request.queryType == 2 ">
        and a.development_manager = #{request.queryUserId}
      </if>
    </if>
    <if test="wantShopAreaIds !=null and wantShopAreaIds.size>0">
      <foreach collection="wantShopAreaIds" item="wantShopAreaId" open="and a.want_shop_area_id in (" close=")" separator=",">
        #{wantShopAreaId}
      </foreach>
    </if>
  </select>



  <select id="publicLineList" resultMap="BaseResultMap">
    select * from xfsg_line_info
    where  deleted = 0 and line_status = 0
    <if test="publicFlag">
      and investment_manager is not null
    </if>
    <if test="publicFlag==false">
      and investment_manager is null
    </if>
    <if test="request.userName != null and request.username!=''">
      and username like concat('%',#{request.userName},'%')
    </if>
    <if test="request.mobile != null and request.mobile!=''">
      and mobile like concat('%', #{request.mobile},'%')
    </if>
    <if test="request.keyword != null and request.keyword!=''">
      and (username like concat('%',#{request.keyword},'%') or mobile like concat('%',  #{request.keyword},'%'))
    </if>
    <if test=" request.lineSource != null and request.lineSource.size>0">
      and  line_source in
      <foreach collection="request.lineSource" item="lineSource" open="(" separator="," close=")">
        #{lineSource}
      </foreach>
    </if>
    <if test="request.createTimeStart!=null and request.createTimeEnd!=null">
      and create_time between #{request.createTimeStart} and #{request.createTimeEnd}
    </if>
    <if test="request.lastDevelopmentManagerUserId != null and request.lastDevelopmentManagerUserId != ''">
      and development_manager = #{request.lastDevelopmentManagerUserId}
    </if>
    <if test="request.lastInvestmentManagerUserId != null and request.lastInvestmentManagerUserId != ''">
      and investment_manager = #{request.lastInvestmentManagerUserId}
    </if>
  </select>
  
  <update id="batchUpdateInterviewWorkflowStage">
    update xfsg_line_info set workflow_sub_stage = #{workflowSubStage}, workflow_sub_stage_status = #{workflowSubStageStatus} where id in
    <foreach collection="lineIds" item="lineId" open="(" separator="," close=")">
      #{lineId}
    </foreach>
    and workflow_sub_stage_status = 15 and workflow_sub_stage = 5
  </update>
  
  <select id="getLinePageByDevelopmentManager" resultMap="BaseResultMap">
    select * from xfsg_line_info where deleted = 0 and development_manager = #{request.developmentManager} and join_status in (1, 2)
    <if test="request.keyword != null and request.keyword!=''">
      and (username like #{request.keyword} or mobile like #{request.keyword})
    </if>
  </select>
  <select id="getLines" resultType="com.cool.store.dto.openPreparation.PlanLineDTO">
    select  xli.id as lineId, xli.mobile as mobile, xli.username as username,xli.investment_manager as investmentManagerId ,eu.name as name
    from xfsg_line_info xli
    join enterprise_user_${enterpriseId}  eu on xli.investment_manager = eu.user_id
    where  deleted = 0
    <if test=" lineIdList != null and lineIdList.size>0">
      and  xli.id in
      <foreach collection="lineIdList" item="lineId" open="(" separator="," close=")">
        #{lineId}
      </foreach>
    </if>

  </select>
  <select id="getByLineIds" resultType="com.cool.store.entity.LineInfoDO">
      select *
      from xfsg_line_info
      where deleted = 0
      <if test="lineIds !=null and lineIds.size()>0">
          and id in
        <foreach collection="lineIds" item="lineId" open="(" separator="," close=")" >
          #{lineId}
        </foreach>
      </if>
  </select>

  <select id="getLineMobile" resultMap="BaseResultMap">
    select
      id, mobile
    from
      xfsg_line_info
    where
      deleted = 0 and id in
      <foreach collection="lineIds" item="lineId" open="(" separator="," close=")" >
        #{lineId}
      </foreach>
  </select>


</mapper>