<?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.ScheduleTrackMapper">
    <resultMap id="ScheduleTrackResultMap" type="com.yd.dal.entity.practitioner.ScheduleTrack">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="notice" jdbcType="VARCHAR" property="notice"/>
        <result column="task_type" jdbcType="INTEGER" property="taskType"/>
        <result column="refer_leads_id" jdbcType="BIGINT" property="referLeadsId"/>
        <result column="refer_potential_id" jdbcType="BIGINT" property="referPotentialId"/>
        <result column="task_important_tag" jdbcType="INTEGER" property="taskImportantTag"/>
        <result column="task_routine_at_week7" jdbcType="INTEGER" property="taskRoutineAtweek7"/>
        <result column="task_routine_at_week6" jdbcType="INTEGER" property="taskRoutineAtweek6"/>
        <result column="task_routine_at_week5" jdbcType="INTEGER" property="taskRoutineAtweek5"/>
        <result column="task_routine_at_week4" jdbcType="INTEGER" property="taskRoutineAtweek4"/>
        <result column="task_routine_at_week3" jdbcType="INTEGER" property="taskRoutineAtweek3"/>
        <result column="task_routine_at_week2" jdbcType="INTEGER" property="taskRoutineAtweek2"/>
        <result column="task_routine_at_week1" jdbcType="INTEGER" property="taskRoutineAtweek1"/>
        <result column="task_time_from" jdbcType="VARCHAR" property="taskTimeFrom"/>
        <result column="task_time_end" jdbcType="VARCHAR" property="taskTimeEnd"/>
        <result column="is_active" jdbcType="INTEGER" property="isActive"/>
        <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="track_time" jdbcType="TIMESTAMP" property="trackTime"/>
        <result column="created_at" jdbcType="TIMESTAMP" property="createdAt"/>
        <result column="created_by" jdbcType="BIGINT" property="createdBy"/>
        <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="creator_type" jdbcType="INTEGER" property="creatorType"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
    </resultMap>
    <select id="queryScheduleTrackList" resultMap="ScheduleTrackResultMap">
        SELECT t.id, notice, task_type, refer_leads_id, refer_potential_id,
            task_important_tag, task_routine_at_week7,task_routine_at_week6, task_routine_at_week5,
            task_routine_at_week4, task_routine_at_week3, task_routine_at_week2, task_routine_at_week1,
            DATE_FORMAT(task_time_from,'%H:%i') task_time_from, DATE_FORMAT(task_time_end,'%H:%i') task_time_end,
            practitioner_id, customer_id, md_drop_option_id, track_score, track_time,
            c.name, c.mobile_no
        FROM ag_mkt_schedule_task_tracking t
          LEFT JOIN ag_acl_customer c on t.customer_id=c.id
        WHERE practitioner_id = #{practitionerId,jdbcType=BIGINT}
        AND DATEDIFF(#{trackDate,jdbcType=VARCHAR},track_time) &lt; 365
        ORDER BY task_time_from
    </select>

    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.yd.dal.entity.practitioner.ScheduleTrack" useGeneratedKeys="true">
        insert into ag_mkt_schedule_task_tracking
            (notice, task_type, refer_leads_id, refer_potential_id,
                  task_important_tag, task_routine_at_week7,task_routine_at_week6, task_routine_at_week5,
                  task_routine_at_week4, task_routine_at_week3, task_routine_at_week2, task_routine_at_week1,
                  task_time_from, task_time_end, is_active, practitioner_id, customer_id, md_drop_option_id, track_score, track_time,
                  created_at, created_by, updated_at, updated_by, updator_type,creator_type )
        values (#{notice,jdbcType=VARCHAR},
                #{taskType,jdbcType=INTEGER},
                #{referLeadsId,jdbcType=BIGINT},
                #{referPotentialId,jdbcType=BIGINT},
                #{taskImportantTag,jdbcType=INTEGER},
                #{taskRoutineAtweek7,jdbcType=INTEGER},
                #{taskRoutineAtweek6,jdbcType=INTEGER},
                #{taskRoutineAtweek5,jdbcType=INTEGER},
                #{taskRoutineAtweek4,jdbcType=INTEGER},
                #{taskRoutineAtweek3,jdbcType=INTEGER},
                #{taskRoutineAtweek2,jdbcType=INTEGER},
                #{taskRoutineAtweek1,jdbcType=INTEGER},
                #{taskTimeFrom,jdbcType=VARCHAR},
                #{taskTimeEnd,jdbcType=VARCHAR},
                #{isActive,jdbcType=INTEGER},
                #{practitionerId,jdbcType=BIGINT},
                #{customerId,jdbcType=BIGINT},
                #{mdDropOptionId,jdbcType=BIGINT},
                #{trackScore,jdbcType=INTEGER},
                #{trackTime,jdbcType=TIMESTAMP},
                #{createdAt,jdbcType=TIMESTAMP},
                #{createdBy,jdbcType=BIGINT},
                #{updatedAt,jdbcType=TIMESTAMP},
                #{updatedBy,jdbcType=BIGINT},
                #{updatorType,jdbcType=INTEGER},
                #{creatorType,jdbcType=INTEGER}
                )
    </insert>

    <select id="checkTimePeriodConflict" resultType="int">
        SELECT count(1)
        FROM ag_mkt_schedule_task_tracking
        WHERE 1=1
            AND to_days(track_time) = to_days(#{trackTime,jdbcType=TIMESTAMP})
            AND practitioner_id = #{practitionerId,jdbcType=BIGINT}
            AND (task_time_from &lt; STR_TO_DATE(#{taskTimeEnd,jdbcType=VARCHAR}, '%H:%i:%s')
	            AND task_time_end &gt; STR_TO_DATE(#{taskTimeFrom,jdbcType=VARCHAR}, '%H:%i:%s')
                )
    </select>

    <resultMap id="PersonalPEPScore" type="com.yd.dal.entity.practitioner.PersonalPEPScore">
        <result column="practitionerId" jdbcType="BIGINT" property="practitionerId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="mobile_no" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="dayScore" jdbcType="BIGINT" property="dayScore"/>
        <result column="weekScore" jdbcType="BIGINT" property="weekScore"/>
        <result column="monthScore" jdbcType="BIGINT" property="monthScore"/>
        <result column="FYC" jdbcType="DECIMAL" property="FYC"/>
    </resultMap>
    <select id="querySaleRecuitScoreFYC" resultMap="PersonalPEPScore">
        SELECT practitionerId, name, dayScore, weekScore, monthScore, COALESCE(fyc,0) FYC, mobile_no
        FROM
        (select p.id practitionerId,
                p.name,
                p.mobile_no,
                p.customer_id,
                sum(case when to_days(t.track_time) = to_days(now()) then COALESCE(t.track_score,0) else 0 end) dayScore,
                sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1) then COALESCE(t.track_score,0) else 0 end) weekScore,
                sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then COALESCE(t.track_score,0) else 0 end) monthScore
           from ag_acl_practitioner p
            left join (select practitioner_id, track_time, track_score
                        from ag_mkt_schedule_task_tracking
                        where md_drop_option_id not in
                                (SELECT o.id FROM ag_md_drop_options o LEFT JOIN ag_md_drop_master m ON m.id = o.drop_master_id
                                            WHERE m.scenario_code = 'pep_schedule_task_others')
                        and date_format(track_time,'%Y-%m')=date_format(now(),'%Y-%m')
                        and to_days(track_time) &lt;= to_days(now())
                      ) t on p.id = t.practitioner_id
          where 1=1
            and p.is_active=1
            and p.id in
            <foreach collection="list" item="practitionerId" index="index" open="(" close=")" separator=",">
                #{practitionerId,jdbcType=BIGINT}
            </foreach>
          group by p.id
        ) s
        LEFT JOIN
        (select t.customer_id, sum(t.fyc_amount) fyc
                   from ag_acl_customer_fortune t left join ag_po_order o on t.order_id = o.id
                   where o.`status` =3
                   and  o.payment_status =3
                   and o.fyc_amount>0
                   and t.drop_option_code ='S01'
                   and DATE_FORMAT(o.created_at, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )
                   group by t.customer_id
        ) fyc
        ON s.customer_id = fyc.customer_id
        ORDER BY dayScore desc, weekScore desc, monthScore desc
    </select>

    <select id="queryTeamAllPractitionerId" resultType="Long">
        SELECT id from ag_acl_practitioner
            where subordinate_system_id =
            (select subordinate_system_id from ag_acl_practitioner where id = #{practitionerId,jdbcType=BIGINT})
    </select>

    <resultMap id="PersonalSchedule" type="com.yd.dal.entity.practitioner.PersonalSchedule">
        <result column="practitioner_id" jdbcType="BIGINT" property="practitionerId"/>
        <collection property="scheduleList" ofType="com.yd.dal.entity.practitioner.ScheduleTrack">
            <id column="id" jdbcType="BIGINT" property="id" />
            <result column="notice" jdbcType="VARCHAR" property="notice"/>
            <result column="task_type" jdbcType="INTEGER" property="taskType"/>
            <result column="refer_leads_id" jdbcType="BIGINT" property="referLeadsId"/>
            <result column="refer_potential_id" jdbcType="BIGINT" property="referPotentialId"/>
            <result column="task_important_tag" jdbcType="INTEGER" property="taskImportantTag"/>
            <result column="task_routine_at_week7" jdbcType="INTEGER" property="taskRoutineAtweek7"/>
            <result column="task_routine_at_week6" jdbcType="INTEGER" property="taskRoutineAtweek6"/>
            <result column="task_routine_at_week5" jdbcType="INTEGER" property="taskRoutineAtweek5"/>
            <result column="task_routine_at_week4" jdbcType="INTEGER" property="taskRoutineAtweek4"/>
            <result column="task_routine_at_week3" jdbcType="INTEGER" property="taskRoutineAtweek3"/>
            <result column="task_routine_at_week2" jdbcType="INTEGER" property="taskRoutineAtweek2"/>
            <result column="task_routine_at_week1" jdbcType="INTEGER" property="taskRoutineAtweek1"/>
            <result column="task_time_from" jdbcType="VARCHAR" property="taskTimeFrom"/>
            <result column="task_time_end" jdbcType="VARCHAR" property="taskTimeEnd"/>
            <result column="is_active" jdbcType="INTEGER" property="isActive"/>
            <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="track_time" jdbcType="TIMESTAMP" property="trackTime"/>
            <result column="created_at" jdbcType="TIMESTAMP" property="createdAt"/>
            <result column="created_by" jdbcType="BIGINT" property="createdBy"/>
            <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="creator_type" jdbcType="INTEGER" property="creatorType"/>
        </collection>
    </resultMap>
    <select id="queryPersonalScheduleList" resultMap="PersonalSchedule">
        SELECT id, notice, task_type, refer_leads_id, refer_potential_id,
            task_important_tag, task_routine_at_week7,task_routine_at_week6, task_routine_at_week5,
            task_routine_at_week4, task_routine_at_week3, task_routine_at_week2, task_routine_at_week1,
            task_time_from, task_time_end, is_active, practitioner_id, customer_id, md_drop_option_id, track_score, track_time,
            created_at, created_by, updated_at, updated_by, updator_type, creator_type
        FROM
            ag_mkt_schedule_task_tracking t
        WHERE 1=1
        AND DATEDIFF(#{trackDate,jdbcType=TIMESTAMP},track_time) &lt; 365
        AND md_drop_option_id in
                (SELECT o.id FROM ag_md_drop_options o LEFT JOIN ag_md_drop_master m ON m.id = o.drop_master_id
                        WHERE m.scenario_code = 'pep_schedule_task_others'
                )
        AND practitioner_id in
        <foreach collection="list" item="practitionerId" index="index" open="(" close=")" separator=",">
            #{practitionerId,jdbcType=BIGINT}
        </foreach>
    </select>

    <select id="queryPersonalScheduleListByTime" resultMap="ScheduleTrackResultMap">
        select id, notice, task_type, refer_leads_id, refer_potential_id,
            task_important_tag, task_routine_at_week7,task_routine_at_week6, task_routine_at_week5,
            task_routine_at_week4, task_routine_at_week3, task_routine_at_week2, task_routine_at_week1,
            task_time_from, task_time_end, is_active, practitioner_id, customer_id, md_drop_option_id, track_score, track_time,
            created_at, created_by, updated_at, updated_by, updator_type, creator_type
        from ag_mkt_schedule_task_tracking t
        where t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
          and to_days(t.track_time) &lt;= to_days(now())
        <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>
        union
        select id, notice, task_type, refer_leads_id, refer_potential_id,
            task_important_tag, task_routine_at_week7,task_routine_at_week6, task_routine_at_week5,
            task_routine_at_week4, task_routine_at_week3, task_routine_at_week2, task_routine_at_week1,
            task_time_from, task_time_end, is_active, practitioner_id, customer_id, md_drop_option_id, track_score, track_time,
            created_at, created_by, updated_at, updated_by, updator_type, creator_type
            from ag_mkt_schedule_task_tracking t
        where t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
            and DATEDIFF(#{trackDate,jdbcType=TIMESTAMP},track_time) &lt; 365
            and md_drop_option_id in
            (SELECT o.id FROM ag_md_drop_options o LEFT JOIN ag_md_drop_master m ON m.id = o.drop_master_id
                WHERE m.scenario_code = 'pep_schedule_task_others'
            )
    </select>

    <delete id="deleteScheduleTrackById">
        delete from ag_mkt_schedule_task_tracking where id=#{scheduleTrackId,jdbcType=BIGINT}
    </delete>

    <select id="queryNameByPractitionerId" resultType="String">
        SELECT p.name from ag_acl_practitioner p
            where p.id = #{practitionerId,jdbcType=BIGINT}
    </select>

    <select id="checkIsExist" resultType="int">
        SELECT count(1)
        FROM ag_mkt_schedule_task_tracking
        WHERE 1=1
            AND to_days(track_time) = to_days(#{trackTime,jdbcType=TIMESTAMP})
            AND practitioner_id = #{practitionerId,jdbcType=BIGINT}
            AND task_time_from = STR_TO_DATE(#{taskTimeFrom,jdbcType=VARCHAR}, '%H:%i:%s')
	        AND task_time_end = STR_TO_DATE(#{taskTimeEnd,jdbcType=VARCHAR}, '%H:%i:%s')
	        AND md_drop_option_id = #{mdDropOptionId,jdbcType=BIGINT}
    </select>

</mapper>