<?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.agms.AgmsPractitionerMapper">

    <select id="practitionerFileSharingList" resultType="com.yd.api.agms.vo.practitioner.PractitionerFileSharing">
        SELECT  s.id id,
                s.md_drop_option_id mdDropOptionId,
                s.file_content fileContent,
                s.is_active isActive,
                date_format(s.created_at, '%Y-%m-%d %H:%i:%s')createdAt,
                s.created_by createdBy,
                uc.name createdName,
                date_format(s.updated_at, '%Y-%m-%d %H:%i:%s') updatedAt,
                s.updated_by updatedBy,
                uu.name updatedName,
                s.title title,
                s.digest digest,
                s.author author,
                s.cover_url coverUrl,
                count(ost.id) totalNum
        <if test="practitionerId != null">
            ,(SELECT count(aost.id)
                    FROM ag_acl_customer_object_share_track aost
                WHERE aost.share_id IN (
                    SELECT aos.id
                        FROM ag_acl_customer_object_share aos
                    WHERE aos.item_id = s.id
                        AND aos.item_type = 1
                        AND aos.practitioner_id = #{practitionerId,jdbcType=BIGINT}
                )) num
        </if>
        FROM ag_acl_practitioner_file_sharing s
                LEFT JOIN  ag_md_drop_options o on o.id = s.md_drop_option_id
                LEFT JOIN  ag_acl_user uc on uc.id = s.created_by
                LEFT JOIN  ag_acl_user uu on uu.id = s.created_by
                LEFT JOIN ag_acl_customer_object_share os
                    LEFT JOIN ag_acl_customer_object_share_track ost ON ost.share_id = os.id
                    ON os.item_id = s.id
            <where>
                <if test="mdDropOptionIds != null">
                    <foreach collection="mdDropOptionIds" item="mdDropOptionId" index="index" open="(" close=")" separator="or">
                        find_in_set (#{mdDropOptionId,jdbcType=BIGINT},s.md_drop_option_id)
                    </foreach>
                </if>
                <if test="id != null">
                    AND s.id = #{id,jdbcType=BIGINT}
                </if>
                <if test="shareCode != null">
                    AND os.item_type = 1
                    AND os.share_code = #{shareCode,jdbcType=VARCHAR}
                </if>
                <if test="isActive != null">
                    AND s.is_active = #{isActive,jdbcType=INTEGER}
                </if>
            </where>
        GROUP BY s.id
        ORDER BY s.id DESC
    </select>
    <resultMap id="BaseResultMap" type="com.yd.api.agms.vo.practitioner.SharePractitionerInfo">
        <result column="customerId" jdbcType="BIGINT" property="customerId" />
        <result column="practitionerId" jdbcType="BIGINT" property="practitionerId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="mobileNo" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="insurerBranchName" jdbcType="VARCHAR" property="insurerBranchName" />
        <result column="headImagePath" jdbcType="VARCHAR" property="headImagePath" />
        <result column="qrCodePath" jdbcType="VARCHAR" property="qrCodePath" />
    </resultMap>
    <select id="findSharePractitioner" resultMap="BaseResultMap">
        SELECT p.customer_id customerId,
               p.id practitionerId,
               p.name name,
               p.mobile_no mobileNo,
               b.branch_name insurerBranchName,
               uh.file_path headImagePath,
               uw.file_path qrCodePath
        FROM ag_acl_customer_object_share os
                 LEFT JOIN ag_acl_practitioner p ON p.id = os.practitioner_id
                 LEFT JOIN ag_acl_insurer_branch b ON b.id = p.insurer_branch_id
                 LEFT JOIN ag_acl_file_upload uh ON uh.target_type = 1 AND uh.target_id = p.id AND uh.is_active = 1 AND uh.target_use_for = 1
                 LEFT JOIN ag_acl_file_upload uw ON uw.target_type = 1 AND uw.target_id = p.id AND uw.is_active = 1 AND uw.target_use_for = 9
        WHERE os.share_code = #{shareCode,jdbcType=VARCHAR}
    </select>

    <select id="practitionerSchedule" resultType="com.yd.api.agms.vo.practitioner.PractitionerScheduleInfo">
        SELECT
               p.name practitionerName,
               (CASE (t.task_type) WHEN 1 THEN '商机' WHEN 2 THEN '增员'  ELSE '其他' END) taskType,
               op.drop_option_name dropOptionName,
               t.track_time trackTime,
               t.task_time_from taskTimeFrom,
               task_time_end taskTimeEnd,
               t.notice ,
               t.created_at createdAt
        FROM ag_mkt_schedule_task_tracking t, ag_acl_practitioner p, ag_md_drop_options op
        WHERE t.practitioner_id = p.id AND op.id = t.md_drop_option_id
        <if test="trackTime != null">
            AND date_format(t.track_time, '%Y-%m-%d') &gt;= #{trackTime,jdbcType=VARCHAR} AND date_format(t.track_time, '%Y-%m-%d') &lt;= #{trackTime,jdbcType=VARCHAR}
        </if>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        <if test="taskType != null">
            AND t.task_type = #{taskType,jdbcType=BIGINT}
        </if>
        <if test="dropOptionName != null">
            AND op.drop_option_name like concat(concat('%',#{dropOptionName,jdbcType=VARCHAR}),'%')
        </if>
        AND t.is_active = 1
    </select>

    <select id="practitionerScheduleExport" resultType="map">
        SELECT
                p.name '1',
                date_format(t.track_time, '%Y-%m-%d') '2',
               (CASE (t.task_type) WHEN 1 THEN '商机' WHEN 2 THEN '增员'  ELSE '其他' END) '3',
                op.drop_option_name '4',
                t.task_time_from '5',
                task_time_end '6',
                REPLACE(t.notice,',',';') '7',
                date_format(t.created_at, '%Y-%m-%d %H:%i:%s') '8'
        FROM ag_mkt_schedule_task_tracking t, ag_acl_practitioner p, ag_md_drop_options op
        WHERE t.practitioner_id = p.id AND op.id = t.md_drop_option_id
        <if test="trackTime != null">
            AND date_format(t.track_time, '%Y-%m-%d') &gt;= #{trackTime,jdbcType=VARCHAR} AND date_format(t.track_time, '%Y-%m-%d') &lt;= #{trackTime,jdbcType=VARCHAR}
        </if>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        <if test="taskType != null">
            AND t.task_type = #{taskType,jdbcType=BIGINT}
        </if>
        <if test="dropOptionName != null">
            AND op.drop_option_name like concat(concat('%',#{dropOptionName,jdbcType=VARCHAR}),'%')
        </if>
        AND t.is_active = 1
    </select>

    <select id="practitionerLeadsGoals" resultType="com.yd.api.agms.vo.practitioner.PractitionerLeadsGoalsInfo">
        SELECT
            p.name practitionerName,
            (CASE (t.statistic_time_unit) WHEN 1 THEN '年度目标' WHEN 3 THEN '月度目标'  ELSE '其他' END) type,
            if(t.statistic_time_unit = 3, concat(year(t.created_at),'-',t.seq_time), t.seq_time) seqTime,
            t.premium ,
            t.commission ,
            t.pieces ,
            t.piece_average_premium pieceAveragePremium,
            t.created_at createdAt
        FROM ag_mkt_leads_goals t,ag_acl_practitioner p
        WHERE t.practitioner_id = p.id
        <choose>
            <when test="type != null and date != null">
                AND t.statistic_time_unit = #{type,jdbcType=BIGINT}
                <if test="type == 1">
                    AND year(t.created_at) = year(str_to_date(#{date,jdbcType=VARCHAR}, '%Y-%m'))
                </if>
                <if test="type == 3">
                    AND year(t.created_at) = left(#{date,jdbcType=VARCHAR}, 4)+0
                    AND t.seq_time = right(#{date,jdbcType=VARCHAR}, 2)+0
                </if>
            </when>
            <when test="type != null and date == null">
                AND t.statistic_time_unit = #{type,jdbcType=BIGINT}
            </when>
            <otherwise>
                <if test="date != null">
                    AND t.statistic_time_unit = 3
                    AND year(t.created_at) = left(#{date,jdbcType=VARCHAR}, 4)+0
                    AND t.seq_time = right(#{date,jdbcType=VARCHAR}, 2)+0
                </if>
            </otherwise>
        </choose>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        AND t.is_active = 1
    </select>

    <select id="practitionerLeadsGoalsExport" resultType="map">
        SELECT
            p.name '1',
            (CASE (t.statistic_time_unit) WHEN 1 THEN '年度目标' WHEN 3 THEN '月度目标'  ELSE '其他' END) '2',
            if(t.statistic_time_unit = 3, concat(year(t.created_at),'-',t.seq_time), t.seq_time) '3',
            t.premium '4',
            t.commission '5',
            t.pieces '6',
            t.piece_average_premium '7',
            date_format(t.created_at, '%Y-%m-%d %H:%i:%s') '8'
        FROM ag_mkt_leads_goals t,ag_acl_practitioner p
        WHERE t.practitioner_id = p.id
        <choose>
            <when test="type != null and date != null">
                AND t.statistic_time_unit = #{type,jdbcType=BIGINT}
                <if test="type == 1">
                    AND year(t.created_at) = year(str_to_date(#{date,jdbcType=VARCHAR}, '%Y-%m'))
                </if>
                <if test="type == 3">
                    AND year(t.created_at) = left(#{date,jdbcType=VARCHAR}, 4)+0
                    AND t.seq_time = right(#{date,jdbcType=VARCHAR}, 2)+0
                </if>
            </when>
            <when test="type != null and date == null">
                AND t.statistic_time_unit = #{type,jdbcType=BIGINT}
            </when>
            <otherwise>
                <if test="date != null">
                    AND t.statistic_time_unit = 3
                    AND year(t.created_at) = left(#{date,jdbcType=VARCHAR}, 4)+0
                    AND t.seq_time = right(#{date,jdbcType=VARCHAR}, 2)+0
                </if>
            </otherwise>
        </choose>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        AND t.is_active = 1
    </select>

    <select id="practitionerActivity" resultType="com.yd.api.agms.vo.practitioner.PractitionerActivityInfo">
        SELECT
            p.name practitionerName,
            (CASE
                WHEN (o.drop_master_id = 14) THEN '增员'
                WHEN (o.drop_master_id = 16) THEN '销售'
                WHEN (o.id = 226) THEN '辅导'
                WHEN (o.id = 227) THEN '培训'
                WHEN (o.id = 229) THEN '会议'
                ELSE '其他'
                END) type,
            SUM(track_score) score,
            YEARWEEK(DATE_FORMAT(t.track_time, '%Y-%m-%d')) timePeriod
        FROM ag_mkt_schedule_task_tracking t, ag_acl_practitioner p, ag_md_drop_options o
        WHERE t.practitioner_id = p.id  AND o.id = t.md_drop_option_id
        AND (o.drop_master_id IN(14,16) OR o.id IN(226,227,229))
        <if test="startDate != null">
            AND date_format(t.track_time, '%Y-%m-%d') &gt;= #{startDate,jdbcType=VARCHAR}
        </if>
        <if test="endDate != null">
            AND date_format(t.track_time, '%Y-%m-%d') &lt;= #{endDate,jdbcType=VARCHAR}
        </if>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        AND t.is_active = 1
        GROUP BY p.name, o.drop_master_id, YEARWEEK(DATE_FORMAT(t.track_time, '%Y-%m-%d'))
    </select>

    <select id="practitionerActivityExport" resultType="map">
        SELECT
            p.name '1',
            (CASE
                WHEN (o.drop_master_id = 14) THEN '增员'
                WHEN (o.drop_master_id = 16) THEN '销售'
                WHEN (o.id = 226) THEN '辅导'
                WHEN (o.id = 227) THEN '培训'
                WHEN (o.id = 229) THEN '会议'
                ELSE '其他'
                END) '2',
            SUM(track_score) '3',
            YEARWEEK(DATE_FORMAT(t.track_time, '%Y-%m-%d')) '4'
        FROM ag_mkt_schedule_task_tracking t, ag_acl_practitioner p, ag_md_drop_options o
        WHERE t.practitioner_id = p.id  AND o.id = t.md_drop_option_id
        AND (o.drop_master_id IN(14,16) OR o.id IN(226,227,229))
        <if test="startDate != null">
            AND t.track_time &gt;= DATE #{startDate,jdbcType=VARCHAR}
        </if>
        <if test="endDate != null">
            AND t.track_time &lt;= DATE #{endDate,jdbcType=VARCHAR}
        </if>
        <if test="practitionerId != null">
            AND t.practitioner_id = #{practitionerId,jdbcType=BIGINT}
        </if>
        AND t.is_active = 1
        GROUP BY p.name, o.drop_master_id, YEARWEEK(DATE_FORMAT(t.track_time, '%Y-%m-%d'))
    </select>

</mapper>