<?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="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')">
                group by p.subordinate_system_id
            </when>
        </choose>
        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,
            </when>
            <otherwise>
                s.name as typeName,
                s.id as typeId,
            </otherwise>
        </choose>
        concat(ifnull(sum(cf.order_price),0),'') as fyp,
        concat(ifnull(sum(cf.fyc_amount),0),'') as fyc,
        ifnull(count(cf.order_id),0) as pieces
        from ag_acl_practitioner p inner join (select t.* from ag_acl_customer_fortune t where t.drop_option_code = "S01" group by t.order_id ) cf on p.customer_id = cf.customer_id
        inner join  ag_po_order o on cf.order_id = o.id and o.status = 3
        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
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                p.insurer_branch_id is not null
            </when>
            <otherwise>
                p.subordinate_system_id is not null
            </otherwise>
        </choose>
        <choose>
            <when test="time.equalsIgnoreCase('D')">
                and TO_DAYS(o.order_date) = TO_DAYS(now())
            </when>
            <when test="time.equalsIgnoreCase('W')">
                and YEARWEEK(DATE_FORMAT(o.order_date,'%Y-%m-%d')) = YEARWEEK(NOW())
            </when>
            <when test="time.equalsIgnoreCase('M')">
                and DATE_FORMAT(o.order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
            </when>
            <when test="time.equalsIgnoreCase('Q')">
                and QUARTER(o.order_date) = QUARTER(NOW()) AND year(o.order_date)=year(now())
            </when>
            <otherwise>
                and YEAR(o.order_date) = YEAR(now())
            </otherwise>
        </choose>
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                group by p.insurer_branch_id
            </when>
            <otherwise>
                group by p.subordinate_system_id
            </otherwise>
        </choose>
        order by sum(cf.fyc_amount) desc
    </select>


    <select id="statisticsPractitioner" resultType="com.yd.api.agms.vo.dashboard.StatisticsPractitionerInfo">
        <choose>
            <when test="type.equalsIgnoreCase('B')">
                select
                ib.branch_name as typeName,
                ib.id as typeId,
                ifnull(count(p.id),0) as amount,
                ifnull(a.real_count,0) as realAmount,
                ifnull(b.mdrt_count,0) as mdrt
                from ag_acl_practitioner p left join
                (
                select p.insurer_branch_id insurer_branch_id,count(distinct f.customer_id) as real_count
                from ag_acl_customer_fortune f inner join ag_po_order o on f.order_id = o.id and o.status = 3 and f.drop_option_code = "S01"
                inner join ag_acl_practitioner p on f.customer_id = p.customer_id
                group by p.insurer_branch_id
                )a on a.insurer_branch_id = p.insurer_branch_id
                left join
                (
                select t.insurer_branch_id,count(t.id) as mdrt_count from ag_acl_practitioner t where t.cert_list is not null and t.cert_list like "%1%" group by t.insurer_branch_id
                )b on b.insurer_branch_id = p.insurer_branch_id
                left join ag_acl_insurer_branch ib on ib.id = p.insurer_branch_id
                where p.is_active = 1
                and p.insurer_branch_id is not null
                group by p.insurer_branch_id order by amount desc ;
            </when>
            <otherwise>
                select
                s.id as typeId,
                s.name as typeName,
                ifnull(count(p.id),0) as amount,
                ifnull(a.real_count,0) as realAmount,
                ifnull(b.mdrt_count,0) as mdrt
                from ag_acl_practitioner p left join
                (
                select p.subordinate_system_id subordinate_system_id,count(distinct f.customer_id) as real_count
                from ag_acl_customer_fortune f inner join ag_po_order o on f.order_id = o.id and o.status = 3 and f.drop_option_code = "S01"
                inner join ag_acl_practitioner p on f.customer_id = p.customer_id
                group by p.subordinate_system_id
                )a on a.subordinate_system_id = p.subordinate_system_id
                left join
                (
                select t.subordinate_system_id,count(t.id) as mdrt_count from ag_acl_practitioner t where t.cert_list is not null and t.cert_list like "%1%" group by t.subordinate_system_id
                )b on b.subordinate_system_id = p.subordinate_system_id
                left join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id
                where p.is_active = 1
                and p.subordinate_system_id is not null
                group by p.subordinate_system_id order by amount desc ;
            </otherwise>
        </choose>
    </select>
    <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>

</mapper>