<?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.MktLeadsAssignedTrackMapper">
  <resultMap id="BaseResultMap" type="com.yd.dal.entity.marketing.MktLeadsAssignedTrack">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="order_id" jdbcType="BIGINT" property="orderId" />
    <result column="order_no" jdbcType="VARCHAR" property="orderNo" />
    <result column="sales_notice" jdbcType="VARCHAR" property="salesNotice" />
    <result column="is_active" jdbcType="INTEGER" property="isActive" />
    <result column="created_at" jdbcType="TIMESTAMP" property="createdAt" />
    <result column="created_by" jdbcType="BIGINT" property="createdBy" />
    <result column="creator_type" jdbcType="INTEGER" property="creatorType" />
    <result column="updated_at" jdbcType="TIMESTAMP" property="updatedAt" />
    <result column="updated_by" jdbcType="BIGINT" property="updatedBy" />
    <result column="updator_type" jdbcType="INTEGER" property="updatorType" />
    <result column="practitioner_id" jdbcType="BIGINT" property="practitionerId" />
    <result column="customer_id" jdbcType="BIGINT" property="customerId" />
    <result column="md_drop_option_id" jdbcType="BIGINT" property="mdDropOptionId" />
    <result column="track_score" jdbcType="INTEGER" property="trackScore" />
    <result column="leads_assigned_id" jdbcType="BIGINT" property="leadsAssignedId" />
    <result column="track_time" jdbcType="TIMESTAMP" property="trackTime" />
    <result column="informed_oss_path" jdbcType="VARCHAR" property="informedOssPath" />
    <result column="informed_id" jdbcType="BIGINT" property="informedId" />
    <result column="informed_status" jdbcType="VARCHAR" property="informedStatus" />
    <result column="sfp_main_id" jdbcType="BIGINT" property="sfpMainId" />
    <result column="task_time_from" jdbcType="VARCHAR" property="taskTimeFrom" />
    <result column="task_time_end" jdbcType="VARCHAR" property="taskTimeEnd" />

  </resultMap>
  <sql id="Base_Column_List">
    id, order_id, order_no, sales_notice, is_active, created_at, created_by, creator_type,
    updated_at, updated_by, updator_type, practitioner_id, customer_id, md_drop_option_id,
    track_score, leads_assigned_id,track_time,informed_oss_path,informed_id,informed_status,
    sfp_main_id,task_time_from,task_time_end
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from ag_mkt_leads_assigned_track
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
    delete from ag_mkt_leads_assigned_track
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.marketing.MktLeadsAssignedTrack" useGeneratedKeys="true">
    insert into ag_mkt_leads_assigned_track (order_id, order_no, sales_notice, 
      is_active, created_at, created_by, 
      creator_type, updated_at, updated_by, 
      updator_type, practitioner_id, customer_id, 
      md_drop_option_id, track_score, leads_assigned_id,track_time
      )
    values (#{orderId,jdbcType=BIGINT}, #{orderNo,jdbcType=VARCHAR}, #{salesNotice,jdbcType=VARCHAR}, 
      #{isActive,jdbcType=INTEGER}, #{createdAt,jdbcType=TIMESTAMP}, #{createdBy,jdbcType=BIGINT}, 
      #{creatorType,jdbcType=INTEGER}, #{updatedAt,jdbcType=TIMESTAMP}, #{updatedBy,jdbcType=BIGINT}, 
      #{updatorType,jdbcType=INTEGER}, #{practitionerId,jdbcType=BIGINT}, #{customerId,jdbcType=BIGINT}, 
      #{mdDropOptionId,jdbcType=BIGINT}, #{trackScore,jdbcType=INTEGER}, #{leadsAssignedId,jdbcType=BIGINT},
      #{trackTime,jdbcType=TIMESTAMP}
      )
  </insert>
  <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.marketing.MktLeadsAssignedTrack" useGeneratedKeys="true">
    insert into ag_mkt_leads_assigned_track
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="orderId != null">
        order_id,
      </if>
      <if test="orderNo != null">
        order_no,
      </if>
      <if test="salesNotice != null">
        sales_notice,
      </if>
      <if test="isActive != null">
        is_active,
      </if>
      <if test="createdAt != null">
        created_at,
      </if>
      <if test="createdBy != null">
        created_by,
      </if>
      <if test="creatorType != null">
        creator_type,
      </if>
      <if test="updatedAt != null">
        updated_at,
      </if>
      <if test="updatedBy != null">
        updated_by,
      </if>
      <if test="updatorType != null">
        updator_type,
      </if>
      <if test="practitionerId != null">
        practitioner_id,
      </if>
      <if test="customerId != null">
        customer_id,
      </if>
      <if test="mdDropOptionId != null">
        md_drop_option_id,
      </if>
      <if test="trackScore != null">
        track_score,
      </if>
      <if test="leadsAssignedId != null">
        leads_assigned_id,
      </if>
      <if test="trackTime != null">
        track_time,
      </if>
      <if test="scheduleTaskTrackingId != null">
        schedule_task_tracking_id,
      </if>
      <if test="informedId != null">
        informed_id,
      </if>
      <if test="informedOssPath != null">
        informed_oss_path,
      </if>
      <if test="informedStatus != null">
        informed_status,
      </if>
      <if test="sfpMainId != null">
        sfp_main_id,
      </if>
      <if test="taskTimeFrom != null">
        task_time_from,
      </if>
      <if test="taskTimeEnd != null">
        task_time_end,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="orderId != null">
        #{orderId,jdbcType=BIGINT},
      </if>
      <if test="orderNo != null">
        #{orderNo,jdbcType=VARCHAR},
      </if>
      <if test="salesNotice != null">
        #{salesNotice,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        #{createdBy,jdbcType=BIGINT},
      </if>
      <if test="creatorType != null">
        #{creatorType,jdbcType=INTEGER},
      </if>
      <if test="updatedAt != null">
        #{updatedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="updatedBy != null">
        #{updatedBy,jdbcType=BIGINT},
      </if>
      <if test="updatorType != null">
        #{updatorType,jdbcType=INTEGER},
      </if>
      <if test="practitionerId != null">
        #{practitionerId,jdbcType=BIGINT},
      </if>
      <if test="customerId != null">
        #{customerId,jdbcType=BIGINT},
      </if>
      <if test="mdDropOptionId != null">
        #{mdDropOptionId,jdbcType=BIGINT},
      </if>
      <if test="trackScore != null">
        #{trackScore,jdbcType=INTEGER},
      </if>
      <if test="leadsAssignedId != null">
        #{leadsAssignedId,jdbcType=BIGINT},
      </if>
      <if test="trackTime != null">
        #{trackTime,jdbcType=TIMESTAMP},
      </if>
      <if test="scheduleTaskTrackingId != null">
        #{scheduleTaskTrackingId,jdbcType=BIGINT},
      </if>
      <if test="informedId != null">
        #{informedId,jdbcType=BIGINT},
      </if>
      <if test="informedOssPath != null">
        #{informedOssPath,jdbcType=VARCHAR},
      </if>
      <if test="informedStatus != null">
        #{informedStatus,jdbcType=VARCHAR},
      </if>
      <if test="sfpMainId != null">
        #{sfpMainId,jdbcType=BIGINT},
      </if>
      <if test="taskTimeFrom != null">
        #{taskTimeFrom,jdbcType=VARCHAR},
      </if>
      <if test="taskTimeEnd != null">
        #{taskTimeEnd,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.yd.dal.entity.marketing.MktLeadsAssignedTrack">
    update ag_mkt_leads_assigned_track
    <set>
      <if test="orderId != null">
        order_id = #{orderId,jdbcType=BIGINT},
      </if>
      <if test="orderNo != null">
        order_no = #{orderNo,jdbcType=VARCHAR},
      </if>
      <if test="salesNotice != null">
        sales_notice = #{salesNotice,jdbcType=VARCHAR},
      </if>
      <if test="isActive != null">
        is_active = #{isActive,jdbcType=INTEGER},
      </if>
      <if test="createdAt != null">
        created_at = #{createdAt,jdbcType=TIMESTAMP},
      </if>
      <if test="createdBy != null">
        created_by = #{createdBy,jdbcType=BIGINT},
      </if>
      <if test="creatorType != null">
        creator_type = #{creatorType,jdbcType=INTEGER},
      </if>
      <if test="updatedAt != null">
        updated_at = #{updatedAt,jdbcType=TIMESTAMP},
      </if>
      <if test="updatedBy != null">
        updated_by = #{updatedBy,jdbcType=BIGINT},
      </if>
      <if test="updatorType != null">
        updator_type = #{updatorType,jdbcType=INTEGER},
      </if>
      <if test="practitionerId != null">
        practitioner_id = #{practitionerId,jdbcType=BIGINT},
      </if>
      <if test="customerId != null">
        customer_id = #{customerId,jdbcType=BIGINT},
      </if>
      <if test="mdDropOptionId != null">
        md_drop_option_id = #{mdDropOptionId,jdbcType=BIGINT},
      </if>
      <if test="trackScore != null">
        track_score = #{trackScore,jdbcType=INTEGER},
      </if>
      <if test="leadsAssignedId != null">
        leads_assigned_id = #{leadsAssignedId,jdbcType=BIGINT},
      </if>
      <if test="trackTime != null">
        track_time = #{trackTime,jdbcType=TIMESTAMP},
      </if>
      <if test="informedStatus != null">
        informed_status = #{informedStatus,jdbcType=VARCHAR},
      </if>
      <if test="informedOssPath != null">
        informed_oss_path = #{informedOssPath,jdbcType=VARCHAR},
      </if>
      <if test="informedId != null">
        leads_assigned_id = #{informedId,jdbcType=BIGINT},
      </if>
      <if test="sfpMainId != null">
        sfp_main_id = #{sfpMainId,jdbcType=BIGINT},
      </if>
      <if test="taskTimeFrom != null">
        task_time_from = #{taskTimeFrom,jdbcType=VARCHAR},
      </if>
      <if test="taskTimeEnd != null">
        task_time_end = #{taskTimeEnd,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.yd.dal.entity.marketing.MktLeadsAssignedTrack">
    update ag_mkt_leads_assigned_track
    set order_id = #{orderId,jdbcType=BIGINT},
      order_no = #{orderNo,jdbcType=VARCHAR},
      sales_notice = #{salesNotice,jdbcType=VARCHAR},
      is_active = #{isActive,jdbcType=INTEGER},
      created_at = #{createdAt,jdbcType=TIMESTAMP},
      created_by = #{createdBy,jdbcType=BIGINT},
      creator_type = #{creatorType,jdbcType=INTEGER},
      updated_at = #{updatedAt,jdbcType=TIMESTAMP},
      updated_by = #{updatedBy,jdbcType=BIGINT},
      updator_type = #{updatorType,jdbcType=INTEGER},
      practitioner_id = #{practitionerId,jdbcType=BIGINT},
      customer_id = #{customerId,jdbcType=BIGINT},
      md_drop_option_id = #{mdDropOptionId,jdbcType=BIGINT},
      track_score = #{trackScore,jdbcType=INTEGER},
      leads_assigned_id = #{leadsAssignedId,jdbcType=BIGINT},
      track_time = #{trackTime,jdbcType=TIMESTAMP},
    where id = #{id,jdbcType=BIGINT}
  </update>
  <select id="selectByPrimaryKeySelective" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from ag_mkt_leads_assigned_track
    <where>
      <if test="id != null">
         id = #{id}
      </if>
      <if test="orderId != null">
        and order_id = #{orderId,jdbcType=BIGINT}
      </if>
      <if test="orderNo != null">
        and order_no = #{orderNo,jdbcType=VARCHAR}
      </if>
      <if test="salesNotice != null">
        and sales_notice = #{salesNotice,jdbcType=VARCHAR}
      </if>
      <if test="isActive != null">
        and is_active = #{isActive,jdbcType=INTEGER}
      </if>
      <if test="createdAt != null">
        and created_at = #{createdAt,jdbcType=TIMESTAMP}
      </if>
      <if test="createdBy != null">
        and created_by = #{createdBy,jdbcType=BIGINT}
      </if>
      <if test="creatorType != null">
        and creator_type = #{creatorType,jdbcType=INTEGER}
      </if>
      <if test="updatedAt != null">
        and updated_at = #{updatedAt,jdbcType=TIMESTAMP}
      </if>
      <if test="updatedBy != null">
        and updated_by = #{updatedBy,jdbcType=BIGINT}
      </if>
      <if test="updatorType != null">
        and updator_type = #{updatorType,jdbcType=INTEGER}
      </if>
      <if test="practitionerId != null">
        and practitioner_id = #{practitionerId,jdbcType=BIGINT}
      </if>
      <if test="customerId != null">
        and customer_id = #{customerId,jdbcType=BIGINT}
      </if>
      <if test="mdDropOptionId != null">
        and md_drop_option_id = #{mdDropOptionId,jdbcType=BIGINT}
      </if>
      <if test="trackScore != null">
        and track_score = #{trackScore,jdbcType=INTEGER}
      </if>
      <if test="leadsAssignedId != null">
        and leads_assigned_id = #{leadsAssignedId,jdbcType=BIGINT}
      </if>
      <if test="trackTime != null">
        and track_time = #{trackTime,jdbcType=TIMESTAMP}
      </if>
      <if test="sfpMainId != null">
        and sfp_main_id = #{sfpMainId,jdbcType=BIGINT}
      </if>
    </where>
  </select>
    <select id="findByTrackTimeForNew" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_mkt_leads_assigned_track
      <where>
        to_days(track_time) = to_days(now())
        <if test="id != null">
          and id = #{id}
        </if>
        <if test="orderId != null">
          and order_id = #{orderId,jdbcType=BIGINT}
        </if>
        <if test="orderNo != null">
          and order_no = #{orderNo,jdbcType=VARCHAR}
        </if>
        <if test="salesNotice != null">
          and sales_notice = #{salesNotice,jdbcType=VARCHAR}
        </if>
        <if test="isActive != null">
          and is_active = #{isActive,jdbcType=INTEGER}
        </if>
        <if test="practitionerId != null">
          and practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        <if test="customerId != null">
          and customer_id = #{customerId,jdbcType=BIGINT}
        </if>
        <if test="mdDropOptionId != null">
          and md_drop_option_id = #{mdDropOptionId,jdbcType=BIGINT}
        </if>
        <if test="trackScore != null">
          and track_score = #{trackScore,jdbcType=INTEGER}
        </if>
        <if test="leadsAssignedId != null">
          and leads_assigned_id = #{leadsAssignedId,jdbcType=BIGINT}
        </if>
      </where>
    </select>
  <select id="totalStatisticsForTeam" resultType="hashmap">
    select sum(case when to_days(track_time) = to_days(now()) then track_score end) scoreToday,
          sum(case when YEARWEEK(date_format(track_time,'%Y-%m-%d')) = YEARWEEK(now()) then track_score end) scoreWeek,
          sum(case when date_format(track_time,'%Y-%m')=date_format(now(),'%Y-%m') then track_score end) scoreMonth
    from ag_mkt_leads_assigned_track
    where is_active = 1
    and practitioner_id in
        <foreach collection="list" item="practitionerId" index="index" open="(" close=")" separator=",">
          #{practitionerId,jdbcType=BIGINT}
        </foreach>
  </select>
    <select id="findByListNotesForCustomerId" resultMap="BaseResultMap">
      select
      <include refid="Base_Column_List" />
      from ag_mkt_leads_assigned_track
      where customer_id = #{customerId,jdbcType=BIGINT}
      order by id desc
      limit 0,1
    </select>
  <resultMap id="SalesScoreDetailMap" type="com.yd.api.practitioner.vo.opportunity.SalesScoreDetail">
    <id column="dropOptionsId" jdbcType="BIGINT" property="dropOptionsId" />
    <result column="dropOptionsName" jdbcType="BIGINT" property="dropOptionsName" />
    <result column="number" jdbcType="BIGINT" property="number" />
    <result column="score" jdbcType="BIGINT" property="score" />
   </resultMap>
  <select id="getSalesScoreDetailByPractitionerId" resultMap="SalesScoreDetailMap">
        select o.id dropOptionsId,
            o.drop_option_name dropOptionsName,
            count(t.id) number ,
            o.drop_option_score score
        from ag_mkt_leads_assigned_track t
        left join ag_md_drop_options o on o.id = t.md_drop_option_id
        where t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        <if test="time != null">
            <choose>
                <when test="time == 'd'.toString() || time == 'D'.toString()">
                    and to_days(t.track_time) = to_days(now())
                </when>
                <when test="time == 'w'.toString() || time == 'W'.toString()">
                    and YEARWEEK(date_format(t.track_time, '%Y-%m-%d')) = YEARWEEK(now())
                </when>
                <when test="time == 'm'.toString() || time == 'M'.toString()">
                    and date_format(t.track_time, '%Y-%m') = date_format(now(), '%Y-%m')
                </when>
            </choose>
        </if>
        group by t.md_drop_option_id,o.drop_option_order
        ORDER BY o.drop_option_order
  </select>

  <delete id="deleteScheduleTrackById" parameterType="java.lang.Long">
    delete from ag_mkt_leads_assigned_track
    where schedule_task_tracking_id = #{scheduleTrackId,jdbcType=BIGINT}
  </delete>
  <update id="updateInformedOssPathByInformedId">
    update ag_mkt_leads_assigned_track
    set informed_oss_path = #{informedOssPath,jdbcType=VARCHAR}
    where informed_id = #{informedId,jdbcType=BIGINT} and informed_status = '1'
  </update>
  <update id="updateCustomerIdByLeadsAssignedId">
    update ag_mkt_leads_assigned_track
    set customer_id = #{customerId,jdbcType=BIGINT}
    where leads_assigned_id = #{leadsAssignedId,jdbcType=BIGINT}
  </update>

  <update id="updateAppointmentRecord" parameterType="com.yd.dal.entity.marketing.AgSfpAppointmentRecord">
    update ag_sfp_appointment_record
    set status = 1,is_complete = 1,updated_at = now()
    where sfp_main_id = #{sfpMainId,jdbcType=BIGINT}
  </update>

</mapper>
