<?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 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)
        <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>
        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>
        concat(ifnull(sum(o.order_price),0),'') as fyp,
        concat(ifnull(sum(o.commission_amount),0),'') as fyc,
        ifnull(count(o.id),0) as pieces
        from ag_acl_practitioner p
        inner join  ag_po_order o on o.broker_customer_id = p.customer_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 o.product_category_id in (2,5,8)
            </when>
            <when test="category.equalsIgnoreCase('pc')">
                and o.product_category_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')">
                and p.insurer_branch_id is not null
                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="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,
                    <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 o.broker_customer_id ) AS real_count
                    FROM ag_po_order o
                    INNER JOIN ag_acl_practitioner p ON o.broker_customer_id = p.customer_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
            </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,
                    <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.subordinate_system_id subordinate_system_id,
                           count( DISTINCT o.broker_customer_id ) AS real_count
                    FROM
                    ag_po_order o
                    INNER JOIN ag_acl_practitioner p ON o.broker_customer_id = p.customer_id
                    WHERE o.STATUS = 3 AND o.payment_status = 3
                    GROUP BY p.subordinate_system_id
                ) a ON a.subordinate_system_id = p.subordinate_system_id
                inner join ag_acl_practitioner_subordinate_system s on s.id = p.subordinate_system_id and s.is_active=1
                WHERE p.is_active = 1
                  AND p.subordinate_system_id IS NOT NULL
                GROUP BY p.subordinate_system_id
                union all <include refid="statisticsPractitionerS2"/>
                union all <include refid="statisticsPractitionerS3"/>
                ORDER BY amount DESC
            </otherwise>
        </choose>
    </select>

    <sql id="statisticsPractitionerS2">
        SELECT
            s.id as typeId,
            s.name as typeName,
            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.s2_id s2_id,
                       count( DISTINCT o.broker_customer_id ) AS real_count
                FROM
                    ag_po_order o
                INNER JOIN ag_acl_practitioner p ON o.broker_customer_id = p.customer_id
                WHERE o.STATUS = 3 AND o.payment_status = 3
                GROUP BY p.subordinate_system_id
            ) a ON a.s2_id = p.s2_id
        inner join ag_acl_practitioner_subordinate_system s on s.id = p.s2_id and s.is_active=1
        WHERE p.is_active = 1
          AND p.s2_id IS NOT NULL
        GROUP BY p.s2_id
    </sql>

    <sql id="statisticsPractitionerS3">
        SELECT
            s.id as typeId,
            s.name as typeName,
            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.s3_id s3_id,
                       count( DISTINCT o.broker_customer_id ) AS real_count
                FROM
                    ag_po_order o
                        INNER JOIN ag_acl_practitioner p ON o.broker_customer_id = p.customer_id
                WHERE o.STATUS = 3 AND o.payment_status = 3
                GROUP BY p.subordinate_system_id
            ) a ON a.s3_id = p.s3_id
        inner join ag_acl_practitioner_subordinate_system s on s.id = p.s3_id and s.is_active=1
        WHERE p.is_active = 1
          AND p.s3_id IS NOT NULL
        GROUP BY p.s3_id
    </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>

</mapper>