<?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,
        ifnull(sum(case when to_days(t.track_time)=to_days(now()) then t.track_score  else 0 end), 0) scoreDay,
        ifnull(sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d')) = YEARWEEK(now()) then t.track_score else 0 end), 0) scoreWeek,
        ifnull(sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then t.track_score else 0 end), 0) scoreMonth,
        ifnull(sum(case when QUARTER(t.track_time)=QUARTER(now()) and YEAR(t.track_time)=YEAR(now()) then t.track_score else 0 end), 0) scoreQuarter,
        ifnull(sum(case when YEAR(t.track_time)=YEAR(now()) then t.track_score else 0 end), 0) scoreYear,
        ifnull(sum(case when t.track_score is not null then t.track_score else 0 end), 0) scoreTotal
        from ag_acl_practitioner p
        left join ag_mkt_schedule_task_tracking t on t.practitioner_id = p.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 t.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')
        <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,
                ifnull(sum(case when to_days(t.track_time)=to_days(now()) then t.track_score else 0 end), 0) scoreDay,
                ifnull(sum(case when YEARWEEK(date_format(t.track_time,'%Y-%m-%d')) = YEARWEEK(now()) then t.track_score else 0 end), 0) scoreWeek,
                ifnull(sum(case when date_format(t.track_time,'%Y-%m')=date_format(now(),'%Y-%m') then t.track_score else 0 end), 0) scoreMonth,
                ifnull(sum(case when QUARTER(t.track_time)=QUARTER(now()) and YEAR(t.track_time)=YEAR(now()) then t.track_score else 0 end), 0) scoreQuarter,
                ifnull(sum(case when YEAR(t.track_time)=YEAR(now()) then t.track_score else 0 end), 0) scoreYear,
                ifnull(sum(case when t.track_score is not null then t.track_score else 0 end), 0) scoreTotal
                from ag_acl_practitioner p
                left join ag_mkt_schedule_task_tracking t on t.practitioner_id = p.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 t.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 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')">
                and p.insurer_branch_id is not null
                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>
        <!-- 上海营业部 -->
        union all
        <include refid="shangHaiDept" />
        order by predictFYC desc ,predictFYP desc ,pieces desc ,opportunityNum desc
    </select>

    <sql id="shangHaiDept">
        select '上海营业部' as branchName,
                '' as  subordinateName,
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                ifnull(sum(case when to_days(a.created_at)=to_days(now()) then 1 else 0 end), 0) opportunityNum,
                ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYP else 0 end), 0) predictFYP,
                ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYC else 0 end), 0) predictFYC,
                ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.pieces else 0 end), 0) pieces
            </when>
            <when test="time.equalsIgnoreCase('W')">
                ifnull(sum(case when YEARWEEK(date_format(a.created_at, '%Y-%m-%d'))=YEARWEEK(now()) then 1 else 0 end), 0) opportunityNum,
                ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYP else 0 end), 0) predictFYP,
                ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYC else 0 end), 0) predictFYC,
                ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.pieces else 0 end), 0) pieces
            </when>
            <when test="time.equalsIgnoreCase('M')">
                ifnull(sum(case when date_format(a.created_at, '%Y-%m') = date_format(now(), '%Y-%m') then 1 else 0 end), 0) opportunityNum,
                ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYP else 0 end), 0) predictFYP,
                ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYC else 0 end), 0) predictFYC,
                ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.pieces else 0 end), 0) pieces
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                ifnull(sum(case when QUARTER(a.created_at)=QUARTER(now()) then 1 else 0 end), 0) opportunityNum,
                ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYP else 0 end), 0) predictFYP,
                ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYC else 0 end), 0) predictFYC,
                ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.pieces else 0 end), 0) pieces
            </when>
            <when test="time.equalsIgnoreCase('Y')">
                ifnull(sum(case when YEAR(a.created_at)=YEAR(now()) then 1 else 0 end), 0) opportunityNum,
                ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYP else 0 end), 0) predictFYP,
                ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYC else 0 end), 0) predictFYC,
                ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.pieces else 0 end), 0) 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 p.dept_id = (SELECT id from ag_acl_insurer_branch_dept where insurer_id=888 and name='上海营业部')
        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
            )
    </sql>

    <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')">
                        ifnull(sum(case when to_days(a.created_at)=to_days(now()) then 1 else 0 end), 0) opportunityNum,
                        ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYP else 0 end), 0) predictFYP,
                        ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.FYC else 0 end), 0) predictFYC,
                        ifnull(sum(case when to_days(a.time_to_close)=to_days(now()) then a.pieces else 0 end), 0) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('W')">
                        ifnull(sum(case when YEARWEEK(date_format(a.created_at, '%Y-%m-%d'))=YEARWEEK(now()) then 1 else 0 end), 0) opportunityNum,
                        ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYP else 0 end), 0) predictFYP,
                        ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.FYC else 0 end), 0) predictFYC,
                        ifnull(sum(case when YEARWEEK(date_format(a.time_to_close, '%Y-%m-%d')) = YEARWEEK(now()) then a.pieces else 0 end), 0) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('M')">
                        ifnull(sum(case when date_format(a.created_at, '%Y-%m') = date_format(now(), '%Y-%m') then 1 else 0 end), 0) opportunityNum,
                        ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYP else 0 end), 0) predictFYP,
                        ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.FYC else 0 end), 0) predictFYC,
                        ifnull(sum(case when date_format(a.time_to_close, '%Y-%m') = date_format(now(), '%Y-%m') then a.pieces else 0 end), 0) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('Q')">
                        ifnull(sum(case when QUARTER(a.created_at)=QUARTER(now()) then 1 else 0 end), 0) opportunityNum,
                        ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYP else 0 end), 0) predictFYP,
                        ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.FYC else 0 end), 0) predictFYC,
                        ifnull(sum(case when QUARTER(a.time_to_close) = QUARTER(now()) then a.pieces else 0 end), 0) pieces
                    </when>
                    <when test="time.equalsIgnoreCase('Y')">
                        ifnull(sum(case when YEAR(a.created_at)=YEAR(now()) then 1 else 0 end), 0) opportunityNum,
                        ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYP else 0 end), 0) predictFYP,
                        ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.FYC else 0 end), 0) predictFYC,
                        ifnull(sum(case when YEAR(a.time_to_close) = YEAR(now()) then a.pieces else 0 end), 0) 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>

        and p.insurer_branch_id is not null
        group by p.insurer_branch_id

        union all
        <include refid="statisticsSalesYDShangHaiDept" />
        order by 4 desc
    </select>

    <sql id="statisticsSalesYDShangHaiDept">
        select
                '上海营业部' as typeName,
                b.id as typeId,
                '' 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,
        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
        and p.is_active = 1
        and p.dept_id = (SELECT id from ag_acl_insurer_branch_dept where insurer_id=888 and name='上海营业部')
        <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>
    </sql>

    <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,
            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">
        SELECT
            p.subordinate_system_id as typeId,
            (select name from ag_acl_practitioner_subordinate_system where id=p.subordinate_system_id) as typeName,
            ifnull(count( p.id ), 0 ) AS amount,

            <include refid="realAmount"/> AS realAmount,
            sum(case when p.cert_list LIKE "%1%"  then 1 else 0 end) AS mdrt
        FROM ag_acl_practitioner p
        WHERE 1=1
        and p.is_active = 1
        and p.subordinate_system_id is not null
        GROUP BY p.subordinate_system_id
        ORDER BY amount DESC
    </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 p2 ON p2.id = f.sale_practitioner_id
         WHERE 1=1
            and p2.subordinate_system_id = p.subordinate_system_id
            and p2.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>

    <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>