<?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.yd.dal.mapper.marketing.MktLeadsPoolMapper">
  <resultMap id="BaseResultMap" type="com.yd.dal.entity.marketing.MktLeadsPool">
    <!--@mbg.generated-->
    <!--@Table ag_mkt_leads_pool-->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="customer_id" jdbcType="BIGINT" property="customerId" />
    <result column="mkt_campaign" jdbcType="VARCHAR" property="mktCampaign" />
    <result column="mkt_task" jdbcType="VARCHAR" property="mktTask" />
    <result column="has_assigned" jdbcType="INTEGER" property="hasAssigned" />
    <result column="source_from" jdbcType="INTEGER" property="sourceFrom" />
    <result column="is_active" jdbcType="INTEGER" property="isActive" />
    <result column="added_at" jdbcType="TIMESTAMP" property="addedAt" />
    <result column="added_by" jdbcType="BIGINT" property="addedBy" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, customer_id, mkt_campaign, mkt_task, has_assigned, source_from,  is_active,
    added_at, added_by
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    <!--@mbg.generated-->
    select 
    <include refid="Base_Column_List" />
    from ag_mkt_leads_pool
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    <!--@mbg.generated-->
    delete from ag_mkt_leads_pool
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.marketing.MktLeadsPool" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ag_mkt_leads_pool (customer_id, mkt_campaign, mkt_task, 
      has_assigned, source_from,
      is_active, added_at, added_by
      )
    values (#{customerId,jdbcType=BIGINT}, #{mktCampaign,jdbcType=VARCHAR}, #{mktTask,jdbcType=VARCHAR}, 
      #{hasAssigned,jdbcType=INTEGER}, #{sourceFrom,jdbcType=INTEGER},
      #{isActive,jdbcType=INTEGER}, #{addedAt,jdbcType=TIMESTAMP}, #{addedBy,jdbcType=BIGINT}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.marketing.MktLeadsPool" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into ag_mkt_leads_pool
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="customerId != null">
        customer_id,
      </if>
      <if test="mktCampaign != null">
        mkt_campaign,
      </if>
      <if test="mktTask != null">
        mkt_task,
      </if>
      <if test="hasAssigned != null">
        has_assigned,
      </if>
      <if test="sourceFrom != null">
        source_from,
      </if>
      <if test="isActive != null">
        is_active,
      </if>
      <if test="addedAt != null">
        added_at,
      </if>
      <if test="addedBy != null">
        added_by,
      </if>
      <if test="adderType != null">
        adder_type,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="customerId != null">
        #{customerId,jdbcType=BIGINT},
      </if>
      <if test="mktCampaign != null">
        #{mktCampaign,jdbcType=VARCHAR},
      </if>
      <if test="mktTask != null">
        #{mktTask,jdbcType=VARCHAR},
      </if>
      <if test="hasAssigned != null">
        #{hasAssigned,jdbcType=INTEGER},
      </if>
      <if test="sourceFrom != null">
        #{sourceFrom,jdbcType=INTEGER},
      </if>
      <if test="isActive != null">
        #{isActive,jdbcType=INTEGER},
      </if>
      <if test="addedAt != null">
        #{addedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="addedBy != null">
        #{addedBy,jdbcType=BIGINT},
      </if>
       <if test="adderType != null">
        #{adderType,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.marketing.MktLeadsPool">
    <!--@mbg.generated-->
    update ag_mkt_leads_pool
    <set>
      <if test="customerId != null">
        customer_id = #{customerId,jdbcType=BIGINT},
      </if>
      <if test="mktCampaign != null">
        mkt_campaign = #{mktCampaign,jdbcType=VARCHAR},
      </if>
      <if test="mktTask != null">
        mkt_task = #{mktTask,jdbcType=VARCHAR},
      </if>
      <if test="hasAssigned != null">
        has_assigned = #{hasAssigned,jdbcType=INTEGER},
      </if>
      <if test="sourceFrom != null">
        source_from = #{sourceFrom,jdbcType=INTEGER},
      </if>
      <if test="isActive != null">
        is_active = #{isActive,jdbcType=INTEGER},
      </if>
      <if test="addedAt != null">
        added_at = #{addedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="addedBy != null">
        added_by = #{addedBy,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.marketing.MktLeadsPool">
    <!--@mbg.generated-->
    update ag_mkt_leads_pool
    set customer_id = #{customerId,jdbcType=BIGINT},
      mkt_campaign = #{mktCampaign,jdbcType=VARCHAR},
      mkt_task = #{mktTask,jdbcType=VARCHAR},
      has_assigned = #{hasAssigned,jdbcType=INTEGER},
      source_from = #{sourceFrom,jdbcType=INTEGER},
      is_active = #{isActive,jdbcType=INTEGER},
      added_at = #{addedAt,jdbcType=TIMESTAMP},
      added_by = #{addedBy,jdbcType=BIGINT}
    where id = #{id,jdbcType=BIGINT}
  </update>
  <select id="findByCustomerId" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_mkt_leads_pool
      where customer_id = #{customerId,jdbcType=BIGINT}
      and is_active=1
  </select>
  <select id="findALLByDate" resultType="com.yd.api.agms.vo.statistics.LeadsStatisticsInfo">
    select  c.id as leadsCustomerId,
            c.`name` as leadsName,
            c.login as leadsMobileNo,
            c.wechat_no as leadsWeChatId,
            c.age as leadsAge,
            c.address as leadsCity,
            c.blood_type_id as leadsBloodTypeId,
            c.zodiacTypeName as leadsZodiacType,
            c.bloodTypeName as leadsBloodType,
            c.zodiac_type_id as leadsZodiacTypeId,
            IF(c.gender = 1, '男', '女') as leadsGender,
            date_format(pool.added_at , '%Y-%m-%d %H:%i:%s') as leadsCreateTime,
            assign.id as assignId,
            assign.assigned_practitioner_id as practitionerId,
            assign.name as practitionerName,
            assign.login as practitionerMobileNo,
            pool.source_from as leadsSourceId,
            op.drop_option_name as leadsSource,
            date_format(assign.created_at, '%Y-%m-%d %H:%i:%s') as leadsAssignTime,
            expertAssign.expertId as smePractitionerId, -- 专家经纪人id
            expertAssign.expertName as sme,
            expertAssign.expertMobileNo as smeMobileNo,
            date_format(expertAssign.expertAssignTime , '%Y-%m-%d %H:%i:%s') as assignSMEDate, -- 专家派遣时间
            date_format(request.created_at , '%Y-%m-%d %H:%i:%s') as requestSMEDate, -- 专家请求时间
            assign.FYC as predictFYP, -- 预计FYP
            assign.FYP as predictFYC, -- 预计FYC
            assign.pieces as predictPieces, -- 预计件数
            date_format(assign.time_to_close , '%Y-%m-%d %H:%i:%s') as dealTime,  -- 预计成交时间
            assignStatus.leadsStatusId as leadsStatusId,
            assignStatus.leadsStatusName as leadsStatus
        from ag_mkt_leads_pool pool inner JOIN (select t.id,t.name,t.login,t.wechat_no,t.age,t.address,t.blood_type_id,t.zodiac_type_id,t.gender,op1.drop_option_name as bloodTypeName,op2.drop_option_name as zodiacTypeName
                                                from ag_acl_customer t left join  ag_md_drop_options op1 on t.blood_type_id = op1.id
                                                                        left join ag_md_drop_options op2 on t.zodiac_type_id = op2.id) c on pool.customer_id = c.id
                                    left join (select assign.id,assign.customer_id,assign.assigned_practitioner_id,assign.created_at,assign.FYC,assign.FYP,assign.pieces,assign.time_to_close,p.name,c.login
                                                from ag_mkt_leads_assigneds assign left join ag_acl_practitioner p on assign.assigned_practitioner_id = p.id
                                                                                    inner join ag_acl_customer c on p.customer_id = c.id) assign on assign.customer_id =  pool.customer_id
                                    left join  ag_mkt_leads_expert_request request on request.leads_assigned_id = assign.id
                                    left join  (select expertAssign.leads_expert_request_id as expertRequestId,p.id as expertId,p.name as expertName,c.login as expertMobileNo,expertAssign.created_at as expertAssignTime
                                                    from ag_mkt_leads_expert_assign expertAssign inner join ag_acl_practitioner p on expertAssign.expert_practitioner_id = p.id
                                                                                                    inner join ag_acl_customer c on p.customer_id = c.id) expertAssign on request.id = expertAssign.expertRequestId
                                    left  join (SELECT maxt.customer_id as leadsCustomerId,
                                                        maxt.practitioner_id as practitionerId,
                                                        tr.md_drop_option_id as leadsStatusId, -- 商机状态id
                                                        options.drop_option_name as leadsStatusName -- 商机状态名
                                                FROM(SELECT customer_id,practitioner_id,max(updated_at) as track_time FROM ag_mkt_leads_assigned_track tr GROUP BY tr.customer_id,tr.practitioner_id) maxt
                                                                INNER JOIN ag_mkt_leads_assigned_track tr on maxt.customer_id=tr.customer_id and tr.practitioner_id = maxt.practitioner_id AND maxt.track_time = tr.updated_at
                                                                inner join ag_md_drop_options options on tr.md_drop_option_id = options.id
                                                                order by tr.leads_assigned_id) assignStatus on assignStatus.leadsCustomerId = pool.customer_id and assign.assigned_practitioner_id = assignStatus.practitionerId
                                    left join  ag_md_drop_options op on pool.source_from = op.id
        where pool.is_active = 1
        <choose>
          <when test="leadsStatus == 1">
                and assign.id is null
          </when>
          <when test="leadsStatus == 2">
                and assign.id is not null
                <choose>
                  <when test="expertApplyStatus == 1">
                        and request.id is null
                  </when>
                  <when test="expertApplyStatus == 2">
                        and request.id is not null
                        and expertAssign.expertId is null
                  </when>
                  <when test="expertApplyStatus == 3">
                        and request.id is not null
                        and expertAssign.expertId is not null
                  </when>
                  <otherwise></otherwise>
                </choose>
          </when>
          <otherwise></otherwise>
        </choose>
    <if test="leadsStartTime != null">
      and pool.added_at &gt;= #{leadsStartTime,jdbcType=VARCHAR}
    </if>
    <if test="leadsEndTime != null">
      and pool.added_at &lt;= #{leadsEndTime,jdbcType=VARCHAR}
    </if>
    order by pool.added_at desc
  </select>

  <select id="findIsAssignLeads" resultType="com.yd.api.agms.vo.statistics.LeadsStatisticsInfo">
    SELECT
      assign.id as assignId, -- 派遣id
      assign.customer_id as leadsCustomerId, -- 商机id
      assign.assigned_practitioner_id as practitionerId, -- 经纪人id
      expertAssign.expert_practitioner_id as smePractitionerId, -- 专家经纪人id
      date_format(expertAssign.created_at , '%Y-%m-%d %H:%i:%s') as assignSMEDate, -- 专家派遣时间
      date_format(assign.created_at , '%Y-%m-%d %H:%i:%s') as leadsAssignTime, -- 商机派遣时间
      date_format(request.created_at , '%Y-%m-%d %H:%i:%s') as requestSMEDate, -- 专家请求时间
      assign.FYC as predictFYP, -- 预计FYP
      assign.FYP as predictFYC, -- 预计FYC
      assign.pieces as predictPieces, -- 预计件数
      date_format(assign.time_to_close , '%Y-%m-%d %H:%i:%s') as dealTime  -- 预计成交时间
    FROM ag_mkt_leads_assigneds assign left join  ag_mkt_leads_expert_request request on request.leads_assigned_id = assign.id
                                       left join  ag_mkt_leads_expert_assign expertAssign on request.id = expertAssign.leads_expert_request_id

  </select>
  <select id="findLeadsStatusMap" resultType="com.yd.dal.entity.leads.LeadsStatusInfo">
    SELECT maxt.customer_id as leadsCustomerId,
           tr.md_drop_option_id as leadsStatusId -- 商机状态id
    FROM(SELECT customer_id,max(updated_at) as track_time FROM ag_mkt_leads_assigned_track tr GROUP BY tr.customer_id) maxt
          INNER JOIN ag_mkt_leads_assigned_track tr on maxt.customer_id=tr.customer_id AND maxt.track_time = tr.updated_at order by tr.leads_assigned_id

  </select>
</mapper>