<?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.AgmsDashboardMapper">


    <select id="statisticsPEP" resultType="com.yd.dal.entity.agms.dashboard.StatisticsPEPInfo">
        select if(b.branch_name is null,'暂无公司',b.branch_name) branchName,
        if(p.insurer_branch_id is null,0,p.insurer_branch_id) branchId,
        if(s.name is null ,'暂无团队',s.name) subordinateName,
        if(p.subordinate_system_id is null ,0,p.subordinate_system_id) subordinateId,
        sum(case when to_days(t.track_time)=to_days(now()) then t.track_score  else 0 end) scoreDay,
        sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d')) = YEARWEEK(now()) then t.track_score  else 0 end) scoreWeek,
        sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then t.track_score  else 0 end) scoreMonth,
        sum(case when QUARTER(t.track_time)=QUARTER(now()) then t.track_score  else 0 end) scoreQuarter,
        sum(case when YEAR(t.track_time)=YEAR(now()) then t.track_score  else 0 end) scoreYear,
        sum(case when t.track_score is not null then t.track_score else 0 end) scoreTotal
        from ag_acl_practitioner p
        left join ag_mkt_leads_assigneds a on p.id = a.assigned_practitioner_id and a.is_active = 1
        left join ag_mkt_leads_assigned_track t on t.leads_assigned_id = a.id
        left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id
        left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
        left join ag_acl_practitioner_setting ps on ps.practitioner_id = p.id
        where ps.practitioner_type_id = 28
        and p.is_active = 1
        and not EXISTS(
        select tt.leads_assigned_id
        from ag_mkt_leads_assigned_track tt
        where tt.md_drop_option_id = #{dropOptionId,jdbcType=BIGINT}
        and a.id = tt.leads_assigned_id)
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by p.insurer_branch_id
            </when>
            <when test="type.equalsIgnoreCase('T')">
                group by p.subordinate_system_id
            </when>
        </choose>
        order by scoreDay desc ,scoreWeek desc ,scoreMonth desc
    </select>

    <select id="statisticsPEPTeam" resultType="com.yd.dal.entity.agms.dashboard.StatisticsPEPInfo">
        <foreach collection="list" item="item" index="index"  separator=" union all " >
            <if test="item.teamMember != null">
                select if(b.branch_name is null,'暂无公司',b.branch_name) branchName,
                if(p.insurer_branch_id is null,0,p.insurer_branch_id) branchId,
                (select name from ag_acl_practitioner_subordinate_system where id=#{item.id}) as  subordinateName,
                #{item.id} as subordinateId,
                sum(case when to_days(t.track_time)=to_days(now()) then t.track_score  else 0 end) scoreDay,
                sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d')) = YEARWEEK(now()) then t.track_score  else 0 end) scoreWeek,
                sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then t.track_score  else 0 end) scoreMonth,
                sum(case when QUARTER(t.track_time)=QUARTER(now()) then t.track_score  else 0 end) scoreQuarter,
                sum(case when YEAR(t.track_time)=YEAR(now()) then t.track_score  else 0 end) scoreYear,
                sum(case when t.track_score is not null then t.track_score else 0 end) scoreTotal
                from ag_acl_practitioner p
                left join ag_mkt_leads_assigneds a on p.id = a.assigned_practitioner_id and a.is_active = 1
                left join ag_mkt_leads_assigned_track t on t.leads_assigned_id = a.id
                left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id
                left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
                left join ag_acl_practitioner_setting ps on ps.practitioner_id = p.id
                where ps.practitioner_type_id = 28
                and p.is_active = 1
                and not EXISTS(
                select tt.leads_assigned_id
                from ag_mkt_leads_assigned_track tt
                where tt.md_drop_option_id = #{dropOptionId,jdbcType=BIGINT}
                and a.id = tt.leads_assigned_id)

                and p.id in <foreach collection="item.teamMember" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
            </if>
        </foreach>
        order by scoreDay desc ,scoreWeek desc ,scoreMonth desc
    </select>

    <select id="statisticsOpportunity" resultType="com.yd.dal.entity.agms.dashboard.StatisticsOpportunityInfo">
        select if(b.branch_name is null,'暂无公司',b.branch_name) branchName,
        if(s.name is null ,'暂无团队',s.name) subordinateName,
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                sum(case when to_days(a.created_at)=to_days(now()) then 1 else 0 end) opportunityNum,
                sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYP else 0 end) predictFYP,
                sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYC else 0 end) predictFYC,
                sum(case when to_days(a.time_to_close)=to_days(now()) then a.pieces else 0 end) pieces
            </when>
            <when test="time.equalsIgnoreCase('W')">
                sum(case when YEARWEEK(date_format(a.created_at, '%Y-%m-%d'))=YEARWEEK(now()) then 1 else 0 end) opportunityNum,
                sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYP else 0 end) predictFYP,
                sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYC else 0 end) predictFYC,
                sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.pieces else 0 end) pieces
            </when>
            <when test="time.equalsIgnoreCase('M')">
                sum(case when date_format(a.created_at, '%Y-%m') = date_format(now(), '%Y-%m') then 1 else 0 end) opportunityNum,
                sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYP else 0 end) predictFYP,
                sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYC else 0 end) predictFYC,
                sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.pieces else 0 end) pieces
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                sum(case when QUARTER(a.created_at)=QUARTER(now()) then 1 else 0 end) opportunityNum,
                sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYP else 0 end) predictFYP,
                sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYC else 0 end) predictFYC,
                sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.pieces else 0 end) pieces
            </when>
            <when test="time.equalsIgnoreCase('Y')">
                sum(case when YEAR(a.created_at)=YEAR(now()) then 1 else 0 end) opportunityNum,
                sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYP else 0 end) predictFYP,
                sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYC else 0 end) predictFYC,
                sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.pieces else 0 end) pieces
            </when>
        </choose>
        from ag_acl_practitioner p
        left join ag_mkt_leads_assigneds a on p.id = a.assigned_practitioner_id and a.is_active = 1
        left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id
        left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
        left join ag_acl_practitioner_setting ps on ps.practitioner_id = p.id
        where
        ps.practitioner_type_id = 28
        and p.is_active = 1
        and not EXISTS(
        select tt.leads_assigned_id
        from ag_mkt_leads_assigned_track tt
        where tt.md_drop_option_id = #{dropOptionId,jdbcType=BIGINT}
        and a.id = tt.leads_assigned_id)
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by p.insurer_branch_id
            </when>
            <when test="type.equalsIgnoreCase('T')">
                and p.subordinate_system_id is not null
                group by p.subordinate_system_id
            </when>
        </choose>
        order by predictFYC desc ,predictFYP desc ,pieces desc ,opportunityNum desc
    </select>

    <select id="statisticsOpportunityTeam" resultType="com.yd.dal.entity.agms.dashboard.StatisticsOpportunityInfo">
        <foreach collection="list" item="item" index="index"  separator=" union all " >
            <if test="item.teamMember != null">
                select if(b.branch_name is null,'暂无公司',b.branch_name) branchName,
                (select name from ag_acl_practitioner_subordinate_system where id=#{item.id}) as subordinateName,
                <choose>
                    <when test="time.equalsIgnoreCase('D')">
                        sum(case when to_days(a.created_at)=to_days(now()) then 1 else 0 end) opportunityNum,
                        sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYP else 0 end) predictFYP,
                        sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYC else 0 end) predictFYC,
                        sum(case when to_days(a.time_to_close)=to_days(now()) then a.pieces else 0 end) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('W')">
                        sum(case when YEARWEEK(date_format(a.created_at, '%Y-%m-%d'))=YEARWEEK(now()) then 1 else 0 end) opportunityNum,
                        sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYP else 0 end) predictFYP,
                        sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYC else 0 end) predictFYC,
                        sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.pieces else 0 end) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('M')">
                        sum(case when date_format(a.created_at, '%Y-%m') = date_format(now(), '%Y-%m') then 1 else 0 end) opportunityNum,
                        sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYP else 0 end) predictFYP,
                        sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYC else 0 end) predictFYC,
                        sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.pieces else 0 end) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('Q')">
                        sum(case when QUARTER(a.created_at)=QUARTER(now()) then 1 else 0 end) opportunityNum,
                        sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYP else 0 end) predictFYP,
                        sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYC else 0 end) predictFYC,
                        sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.pieces else 0 end) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('Y')">
                        sum(case when YEAR(a.created_at)=YEAR(now()) then 1 else 0 end) opportunityNum,
                        sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYP else 0 end) predictFYP,
                        sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYC else 0 end) predictFYC,
                        sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.pieces else 0 end) pieces
                    </when>
                </choose>
                from ag_acl_practitioner p
                left join ag_mkt_leads_assigneds a on p.id = a.assigned_practitioner_id and a.is_active = 1
                left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id and b.is_active = 1
                left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id and s.is_active = 1
                left join ag_acl_practitioner_setting ps on ps.practitioner_id = p.id
                where
                ps.practitioner_type_id = 28
                and p.is_active = 1
                and not EXISTS(
                select tt.leads_assigned_id
                from ag_mkt_leads_assigned_track tt
                where tt.md_drop_option_id = #{dropOptionId,jdbcType=BIGINT}
                and a.id = tt.leads_assigned_id)

                and p.id in <foreach collection="item.teamMember" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
            </if>
        </foreach>
        order by predictFYC desc ,predictFYP desc ,pieces desc ,opportunityNum desc
    </select>

    <select id="getPractitionerNums" resultType="java.util.Map">
        select
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                if(p.insurer_branch_id is null,0,p.insurer_branch_id) id,
            </when>
            <when test="type.equalsIgnoreCase('T')">
                if(p.subordinate_system_id is null,0,p.subordinate_system_id) id,
            </when>
            <otherwise>
                0 id,
            </otherwise>
        </choose>
        count(p.id) practitionerNum
        from ag_acl_practitioner p
        left join ag_acl_practitioner_setting ps on ps.practitioner_id = p.id
        where ps.practitioner_type_id = 28
        and p.is_active = 1
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by p.insurer_branch_id
            </when>
            <when test="type.equalsIgnoreCase('T')">
                group by p.subordinate_system_id
            </when>
        </choose>
    </select>

    <select id="statisticsSalesYD" resultType="com.yd.api.agms.vo.dashboard.StatisticsSalesInfo">
        select
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                b.branch_name as typeName,
                b.id as typeId,
                '' as level,
            </when>
            <otherwise>
                s.name as typeName,
                s.id as typeId,
                (SELECT drop_option_code
                from ag_acl_practitioner_setting b
                LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
                where b.practitioner_id = p.id
                ) as level,
            </otherwise>
        </choose>
        if(GROUP_CONCAT(DISTINCT o.id) is null, 0,
        (select sum(t.order_price)  from ag_po_order t where FIND_IN_SET(t.id, GROUP_CONCAT(DISTINCT o.id SEPARATOR ',')))
        ) as fyp,
        concat(ifnull(sum(o.fyc_amount*lip.achievements_rate/100),0),'') as fyc,
        ifnull(count(DISTINCT o.id),0) as pieces
        from ag_acl_practitioner p
        INNER JOIN  ag_acl_life_insurance_practitioner lip ON lip.practitioner_id= p.id
        INNER JOIN  ag_po_order o ON o.policy_no = lip.policy_no
            left join ag_product prd on prd.id = o.product_id
            left join ag_product_category c1 on c1.id = prd.product_category_id
            left join ag_product_plan a on o.plan_id = a.id
            left join ag_product aa on aa.id = a.product_id
            left join ag_product_category c2 on c2.id = aa.product_category_id
        left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id
        left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
        where o.status = 3 and o.payment_status=3
        <choose>
            <when test="category.equalsIgnoreCase('life')">
                and (c1.id in (2,5,8) or c2.id in (2,5,8))
            </when>
            <when test="category.equalsIgnoreCase('pc')">
                and (c1.id not in (2,5,8) or c2.id not in (2,5,8))
            </when>
        </choose>
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                and TO_DAYS(o.underwriting_date) = TO_DAYS(now())
            </when>
            <when test="time.equalsIgnoreCase('W')">
                and YEARWEEK(DATE_FORMAT(o.underwriting_date,'%Y-%m-%d')) = YEARWEEK(NOW())
            </when>
            <when test="time.equalsIgnoreCase('M')">
                and DATE_FORMAT(o.underwriting_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                and QUARTER(o.underwriting_date) = QUARTER(NOW()) AND year(o.underwriting_date)=year(now())
            </when>
            <otherwise>
                and YEAR(o.underwriting_date) = YEAR(now())
            </otherwise>
        </choose>
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by p.insurer_branch_id
            </when>
            <otherwise>
                and p.subordinate_system_id is not null
                group by p.subordinate_system_id
            </otherwise>
        </choose>
        order by 4 desc
    </select>

    <select id="statisticsSalesTeam" resultType="com.yd.api.agms.vo.dashboard.StatisticsSalesInfo">
        <foreach collection="list" item="item" index="index"  separator=" union all " >
            <if test="item.teamMember != null">
                select
                    (select name from ag_acl_practitioner_subordinate_system where id=#{item.id}) as typeName,
                    #{item.id} as typeId,
                    (SELECT drop_option_code
                    from ag_acl_practitioner_subordinate_system s
                    LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = s.owner_practitioner_id
                    LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
                    where s.id=#{item.id}
                    ) as level,

                    if(GROUP_CONCAT(DISTINCT o.id) is null, 0,
                    (select sum(t.order_price)  from ag_po_order t where FIND_IN_SET(t.id, GROUP_CONCAT(DISTINCT o.id SEPARATOR ',')))
                    ) as fyp,
                    ifnull(sum(o.fyc_amount*lip.achievements_rate/100),0) as fyc,
                    ifnull(count(DISTINCT o.id),0) as pieces
                from ag_acl_practitioner p
                INNER JOIN  ag_acl_life_insurance_practitioner lip ON lip.practitioner_id= p.id
                INNER JOIN  ag_po_order o ON o.policy_no = lip.policy_no
                    left join ag_product prd on prd.id = o.product_id
                    left join ag_product_category c1 on c1.id = prd.product_category_id
                    left join ag_product_plan a on o.plan_id = a.id
                    left join ag_product aa on aa.id = a.product_id
                    left join ag_product_category c2 on c2.id = aa.product_category_id
                left join ag_acl_insurer_branch b on b.id = p.insurer_branch_id
                left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
                where o.status = 3 and o.payment_status=3
                and p.id in <foreach collection="item.teamMember" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
                <choose>
                    <when test="category.equalsIgnoreCase('life')">
                        and (c1.id in (2,5,8) or c2.id in (2,5,8))
                    </when>
                    <when test="category.equalsIgnoreCase('pc')">
                        and (c1.id not in (2,5,8) or c2.id not in (2,5,8))
                    </when>
                </choose>
                <choose>
                    <when test="time.equalsIgnoreCase('D')">
                        and TO_DAYS(o.underwriting_date) = TO_DAYS(now())
                    </when>
                    <when test="time.equalsIgnoreCase('W')">
                        and YEARWEEK(DATE_FORMAT(o.underwriting_date,'%Y-%m-%d')) = YEARWEEK(NOW())
                    </when>
                    <when test="time.equalsIgnoreCase('M')">
                        and DATE_FORMAT(o.underwriting_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
                    </when>
                    <when test="time.equalsIgnoreCase('Q')">
                        and QUARTER(o.underwriting_date) = QUARTER(NOW()) AND year(o.underwriting_date)=year(now())
                    </when>
                    <otherwise>
                        and YEAR(o.underwriting_date) = YEAR(now())
                    </otherwise>
                </choose>
            </if>
        </foreach>
        order by 4 desc
    </select>


    <select id="statisticsPractitioner" resultType="com.yd.api.agms.vo.dashboard.StatisticsPractitionerInfo">
        SELECT
            ib.branch_name AS typeName,
            ib.id AS typeId,
            ifnull( count( p.id ), 0 ) AS amount,
            ifnull( a.real_count, 0 ) AS realAmount,
            <include refid="statisticsPractitionerHiring"/> AS hiringAmount,
            sum(case when p.cert_list LIKE "%1%"  then 1 else 0 end) AS mdrt
        FROM
            ag_acl_practitioner p
        left join ag_acl_practitioner_hiring_approve_records hr on hr.hiring_basic_info_id=p.hiring_basic_info_id
        and hr.approve_step_id=5 and hr.approving_status=1
        LEFT JOIN (
            SELECT p.insurer_branch_id insurer_branch_id,
                    count( DISTINCT f.sale_practitioner_id ) AS real_count
            FROM
            ag_acl_customer_fortune f
            left JOIN  ag_po_order o ON f.order_id = o.id and o.STATUS = 3 AND o.payment_status = 3
            INNER JOIN ag_acl_practitioner p ON p.id = f.sale_practitioner_id
            WHERE o.STATUS = 3 AND o.payment_status = 3
            GROUP BY
            p.insurer_branch_id
        ) a ON a.insurer_branch_id = p.insurer_branch_id
        inner JOIN ag_acl_insurer_branch ib ON ib.id = p.insurer_branch_id and ib.is_active=1
        WHERE
        p.is_active = 1 AND p.insurer_branch_id IS NOT NULL
        GROUP BY p.insurer_branch_id
        ORDER BY amount DESC
    </select>

    <select id="statisticsPractitionerTeam" resultType="com.yd.api.agms.vo.dashboard.StatisticsPractitionerInfo">
        <foreach collection="list" item="item" index="index"  separator=" union all " >
            <if test="item.teamMember != null">
                SELECT
                    #{item.id} as typeId,
                    (select name from ag_acl_practitioner_subordinate_system where id=#{item.id}) as typeName,
                    ifnull( count( p.id ), 0 ) AS amount,

                    <include refid="realAmount"/> AS realAmount,
                    <include refid="statisticsPractitionerHiring"/> AS hiringAmount,
                    sum(case when p.cert_list LIKE "%1%"  then 1 else 0 end) AS mdrt
                FROM
                    ag_acl_practitioner p
                left join ag_acl_practitioner_hiring_approve_records hr on hr.hiring_basic_info_id=p.hiring_basic_info_id
                and hr.approve_step_id=5 and hr.approving_status=1
                WHERE 1=1
                and p.id in <foreach collection="item.teamMember" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
                and p.is_active = 1
            </if>
        </foreach>

    </select>

    <sql id="realAmount">
        (SELECT
             count( DISTINCT f.sale_practitioner_id )
         FROM
            ag_acl_customer_fortune f
         left JOIN  ag_po_order o ON f.order_id = o.id and o.STATUS = 3 AND o.payment_status = 3
         left JOIN ag_acl_practitioner p ON p.id = f.sale_practitioner_id
         WHERE 1=1
            and p.id in <foreach collection="item.teamMember" item="p_id" open="(" separator="," close=")">#{p_id,jdbcType=BIGINT}</foreach>
            and p.is_active = 1
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                and TO_DAYS(o.underwriting_date) = TO_DAYS(now())
            </when>
            <when test="time.equalsIgnoreCase('W')">
                and YEARWEEK(DATE_FORMAT(o.underwriting_date,'%Y-%m-%d')) = YEARWEEK(NOW())
            </when>
            <when test="time.equalsIgnoreCase('M')">
                and DATE_FORMAT(o.underwriting_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                and QUARTER(o.underwriting_date) = QUARTER(NOW()) AND year(o.underwriting_date)=year(now())
            </when>
            <otherwise>
                and YEAR(o.underwriting_date) = YEAR(now())
            </otherwise>
        </choose>
        )
    </sql>

    <sql id="statisticsPractitionerHiring">
        sum(case
            when
            <choose>
                <when test="time.equalsIgnoreCase('D')">
                    TO_DAYS(hr.created_at) = TO_DAYS(now())
                </when>
                <when test="time.equalsIgnoreCase('W')">
                    YEARWEEK(DATE_FORMAT(hr.created_at,'%Y-%m-%d')) = YEARWEEK(NOW())
                </when>
                <when test="time.equalsIgnoreCase('M')">
                    DATE_FORMAT(hr.created_at, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
                </when>
                <when test="time.equalsIgnoreCase('Q')">
                    QUARTER(hr.created_at) = QUARTER(NOW()) AND year(hr.created_at)=year(now())
                </when>
                <otherwise>
                    YEAR(hr.created_at) = YEAR(now())
                </otherwise>
            </choose>
            then 1 else 0 end)
    </sql>

    <select id="statisticsSalesEG" resultType="com.yd.api.agms.vo.dashboard.StatisticsSalesInfo">
        select
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                spc1.SPC_AREA_NAME as typeName,
            </when>
            <otherwise>
                spc4.SPC_DIV_NAME as typeName,
            </otherwise>
        </choose>
        ifnull(count(distinct policy.INS_MST_ID),0) as pieces,
        concat(ifnull(sum(policy.INS_MST_TOTAL_PREIUM),0),'') as fyp,
        concat(ifnull(sum(policy.INS001_FYCTOTAL),0),'') as fyc
        FROM
        ins001 policy INNER JOIN mon025 m on policy.INS_MST_ID = m.MON025_002 and m.MON025_109 IN (1,2,3,4,7) AND m.MON025_303 = 1 AND m.mon025_108 = 'S01'
        INNER JOIN (
        SELECT mon025_002,MAX(MON025_405) as MON025_405 FROM mon025 group by mon025_002 ORDER BY mon025_002
        ) b on m.mon025_002 = b.mon025_002 AND m.MON025_405 = b.MON025_405
        inner join spc004 spc4 on m.MON025_006 = spc4.SPC_DIV_ID -- 体系
        inner join spc002 spc2 on spc4.FK_BUS_ID = spc2.SPC_BUS_ID -- 营业部
        inner join spc001 spc1 on spc2.FK_AREA_ID = spc1.SPC_AREA_ID -- 分公司
        where
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                TO_DAYS(policy.INS_MST_ACCEPT_DATE) = TO_DAYS(now())
            </when>
            <when test="time.equalsIgnoreCase('W')">
                YEARWEEK(DATE_FORMAT(policy.INS_MST_ACCEPT_DATE,'%Y-%m-%d')) = YEARWEEK(NOW())
            </when>
            <when test="time.equalsIgnoreCase('M')">
                DATE_FORMAT(policy.INS_MST_ACCEPT_DATE, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                QUARTER(policy.INS_MST_ACCEPT_DATE) = QUARTER(NOW()) AND year(policy.INS_MST_ACCEPT_DATE)=year(now())
            </when>
            <otherwise>
                YEAR(policy.INS_MST_ACCEPT_DATE) = YEAR(now())
            </otherwise>
        </choose>
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by spc1.SPC_AREA_ID
            </when>
            <otherwise>
                group by m.MON025_006
            </otherwise>
        </choose>
        order by sum(policy.INS001_FYCTOTAL) desc
    </select>

    <select id="personalOffLineFYC" resultType="com.yd.dal.entity.practitioner.PersonalOffLineFYC">
        SELECT DISTINCT
        sales.SAL_MST_MOBILE mobileNo,
        ifnull(sum(policy.INS001_FYCTOTAL),0) FYC
        FROM sal001 sales LEFT JOIN ins003 p_sales on sales.SAL_MST_ID = p_sales.FK_SAL_MST_ID
        LEFT JOIN ins001 policy on policy.INS_MST_ID = p_sales.FK_INS_MST_ID
        WHERE policy.INS_MST_REPEAL != 1
        AND DATE_FORMAT(policy.INS_MST_ACCEPT_DATE, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
        AND sales.SAL_MST_MOBILE in
        <foreach collection="list" item="mobileNo" index="index" open="(" close=")" separator=",">
            #{mobileNo,jdbcType=VARCHAR}
        </foreach>
        group by sales.SAL_MST_MOBILE
    </select>

    <select id="queryTeamAllMap" resultType="MAP">
        select s.id,

               (select group_concat(p.id)
                from ag_acl_practitioner p
                         LEFT JOIN ag_acl_practitioner_subordinate_system ps ON p.s2_id = ps.id or p.s1_id = ps.id or p.s3_id = ps.id
                WHERE ps.id = s.id
                  and p.is_active=1
                ) as 'teamMember'
        from ag_acl_practitioner_subordinate_system s
        WHERE s.is_active=1

    </select>

</mapper>