<?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.lifecommission.LifeCommissionMapper">
    <!--  查询来佣比对列表  -->
    <select id="queryComeCommissionList" parameterType="com.yd.api.commission.vo.lifecommission.QueryComeCommissionListRequestVO"
            resultType="com.yd.api.commission.vo.lifecommission.ComeCommissionVO">
        select
            o.id orderId,
            c.check_yearmonth checkDate,
            IFNULL(i.name_abbre,i.name) insurerName,
            IFNULL(a.name,p.name) planName,
            o.policy_no policyNo,
            o.order_price orderPrice,
            o.commissionRate,
            o.commissionAmount,
            o.commissionCheckStatus,
            o.order_date orderDate,
            o.commission_no commissionNo,
            o.commissionId,
            o.amount ,
            date_format(o.commissionYear, '%Y-%m-%d') commissionYear,
            o.commissionCheckStatus commissionStatus,
            o.commission_time commissionTime,
            o.commission_period commissionPeriod,
            o.commission_type commissionType,
            o.commission_item commissionItem,

            (case when o.cover_term in ('1','2','3') then o.coverageTerm  else e.coverageTerm END) coverageTerm,
            IFNULL(o.payTerm,e.payTerm) payTerm,
            f.name,
            f.practitioner_type_id practitionerTypeId,
            (case o.config_level when 2 then p2.pName2 else p3.pName3 END) categoryName,
            (case o.config_level when 2 then p2.cName2 else p3.cName3 END) planCategoryName
        from (SELECT o.*,
                    policyNo, commission_no, amount, commission_time, rate, commission_check_id, commissionId,
                    commission_year commissionYear,
                    commission_status commissionCheckStatus,
                    amount commissionAmount,
                    rate commissionRate,
                    commission_category,
                    commission_type,
                    commission_item,
                    commission_period
                from
                    (SELECT id, policy_no, order_price, product_id, commission_rate, commission_amount, commission_check_status,
                        plan_id, insurer_id, order_date, commission_check_at, config_level, cover_term,
                        CONCAT(payment_term, (case payment_term_unit when 'Y' then '年' when 'M' then '月' when 'D' then '日' else '年' END)) payTerm,
                        cover_term_type coverageTerm
                        from ag_po_order t
                        where t.status in (3,4) and policy_no is not null
                        and (t.plan_id not in (424,453,464) or t.plan_id is null)
                    ) o
                LEFT JOIN
                    (SELECT id commissionId, policy_no policyNo, commission_no, commission_year, amount, commission_status, commission_time, commission_check_id,
                        t.commission_type,  t.commission_item,
                        commission_period, rate, order_id, commission_category
                        from ag_po_order_commission t
                    ) orderCommission
                ON o.id = orderCommission.order_id
                WHERE 1=1
                    <if test="startDate != null ">
                        and date_format(commission_year, '%Y-%m') &gt;= #{startDate}
                    </if>
                    <if test="endDate != null ">
                        and date_format(commission_year, '%Y-%m') &lt;= #{endDate}
                    </if>
             ) o
        left join ag_po_order_commission_check c on o.commission_check_id = c.id
        left join ag_product_plan a on o.plan_id = a.id
        left join ag_product p on o.product_id = p.id
        left join (select plcl.config_level, plcl.product_id, plc.name cName2, pc.name pName2
                        from ag_plan_category_list plcl
                        left join ag_plan_category plc on plcl.plan_category_id = plc.id
                        left join ag_product_category pc on plc.product_category_id = pc.id
                    where plcl.config_level = 2
                    ) p2
                    on o.config_level = p2.config_level and o.product_id = p2.product_id
        left join (select plcl.config_level, plcl.plan_id, plc.name cName3, pc.name pName3
                        from ag_plan_category_list plcl
                        left join ag_plan_category plc on plcl.plan_category_id = plc.id
                        left join ag_product_category pc on plc.product_category_id = pc.id
                        where plcl.config_level = 3
                    ) p3
                    on o.config_level = p3.config_level and o.plan_id = p3.plan_id
        left join ag_acl_insurer i on o.insurer_id = i.id
        left join (SELECT order_id, name, practitioner_type_id, f.customer_id
                        from (
                            select *
                            from (select order_id, customer_id from ag_acl_customer_fortune order by referral_amount desc) f
                            group by f.order_id
                            ) f
                        left join ag_acl_practitioner practitioner on f.customer_id = practitioner.customer_id
                        left join ag_acl_practitioner_setting setting on setting.practitioner_id = practitioner.id
                ) f on f.order_id = o.id
        left join (SELECT
                    order_id,
                    MAX(CASE WHEN factor_code = 'f_coverage_term' THEN element_text END) coverageTerm,
                    MAX(CASE WHEN factor_code = 'f_pay_term' THEN element_text END) payTerm
                    FROM ag_po_order_life_product_elements t
                    GROUP BY order_id
                    ) e on e.order_id = o.id
        where 1=1
        <if test="insurerId != null">
            and o.insurer_id = #{insurerId}
        </if>
        <if test="insurerBranchId != null">
            and p.insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
            and o.commissionCheckStatus = #{commissionCheckStatus}
        </if>
        <if test="commissionType != null">
            and o.commission_type = #{commissionType}
            <if test="commissionItem != null">
                and o.commission_item = #{commissionItem}
            </if>
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
        </choose>
    </select>


    <insert id="insertOrderCommissionCheck" parameterType="com.yd.dal.entity.commission.OrderCommissonCheck"
            useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into ag_po_order_commission_check (check_yearmonth,created_at,created_by,updated_at,updated_by)
        VALUES
            (#{checkYearmonth},#{createdAt},#{createdBy},#{updatedAt},#{updatedBy})
    </insert>


    <update id="setOrderCommissionCheckId">
        update ag_po_order
        <set>
            <if test="paramMap.checkId != null">
                commission_check_id = #{paramMap.checkId,jdbcType=BIGINT},
            </if>
            <if test="paramMap.status != null">
                commission_check_status = #{paramMap.status,jdbcType=VARCHAR},
            </if>
            <if test="paramMap.checkAt != null">
                commission_check_at = #{paramMap.checkAt,jdbcType=TIMESTAMP},
            </if>
            <if test="paramMap.checkBy != null">
                commission_check_by = #{paramMap.checkBy,jdbcType=VARCHAR},
            </if>
        </set>
        where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

    <select id="findByCheckYearmonth" parameterType="String" resultType="com.yd.dal.entity.commission.OrderCommissonCheck">
        select * from  ag_po_order_commission_check
        where check_yearmonth = #{orderCommissionCheckBatch,jdbcType=VARCHAR}
        order by id asc
    </select>

    <select id="getSummaries" resultType="java.util.Map">
        select
            count(1) totalSingular,
            IFNULL(sum(CASE WHEN o.commissionCheckStatus = '1' THEN 1 ELSE 0 END),0) totalDaiLaiYong,
            IFNULL(sum(CASE WHEN o.commissionCheckStatus = '2' THEN 1 ELSE 0 END),0) totalYiBiDui,
            IFNULL(sum(CASE WHEN o.commissionCheckStatus = '3' THEN 1 ELSE 0 END),0) totalYiTuiBao,
            IFNULL(sum(o.order_price),0) totalOrderPrice,
            IFNULL(sum(o.commissionAmount),0) totalCommission
        from <include refid="queryLifecommissionList"/>
    </select>

    <sql id="queryLifecommissionList">

             (SELECT o.*,
                    policyNo, commission_no, amount, commission_time, rate, commission_check_id,
                    commission_year commissionYear,
                    commission_status commissionCheckStatus,
                    amount commissionAmount,
                    rate commissionRate,
                    commission_category,
                    commission_type,
                    commission_item,
                    commission_period
                from
                (SELECT id, policy_no, order_price, commission_rate, commission_amount, commission_check_status, product_id,
                    plan_id, insurer_id, order_date, commission_check_at, config_level,
                    CONCAT(payment_term, (case payment_term_unit when 'Y' then '年' when 'M' then '月' else '日' END)) payTerm,
                    CONCAT(cover_term, cover_term_type) coverageTerm
                    from ag_po_order t
                    where t.status in (3,4) and policy_no is not null
                    and (t.plan_id not in (424,453,464) or t.plan_id is null)
                ) o
                LEFT JOIN
                (SELECT policy_no policyNo, commission_no, commission_year, amount, commission_status, commission_time, rate, commission_check_id,
                        t.commission_type,t.commission_item,
                        commission_period, order_id, commission_category
                        from ag_po_order_commission t
                    ) orderCommission
                ON o.id = orderCommission.order_id
                WHERE 1=1
                    <if test="startDate != null ">
                        and date_format(commission_year, '%Y-%m') &gt;= #{startDate}
                    </if>
                    <if test="endDate != null ">
                        and date_format(commission_year, '%Y-%m') &lt;= #{endDate}
                    </if>
            ) o
        left join ag_po_order_commission_check c on o.commission_check_id = c.id
        left join ag_acl_insurer i on o.insurer_id = i.id
        left join ag_product_plan a on o.plan_id = a.id
        left join ag_product p on o.product_id = p.id
        left join (SELECT order_id, name, practitioner_type_id, f.customer_id
                    from (
                        select *
                        from (select order_id, customer_id from ag_acl_customer_fortune order by referral_amount desc) f
                        group by f.order_id
                        ) f
                    left join ag_acl_practitioner practitioner on f.customer_id = practitioner.customer_id
                    left join ag_acl_practitioner_setting setting on setting.practitioner_id = practitioner.id
            ) f on f.order_id = o.id
        where 1=1
        <if test="insurerId != null">
            and o.insurer_id = #{insurerId}
        </if>
        <if test="insurerBranchId != null">
            and p.insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
            and o.commissionCheckStatus = #{commissionCheckStatus}
        </if>
        <if test="commissionType != null">
            and o.commission_type = #{commissionType}
            <if test="commissionItem != null">
                and o.commission_item = #{commissionItem}
            </if>
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
        </choose>
    </sql>

    <select id="querycheckBatchList" resultType="java.lang.String">
        select distinct check_yearmonth from  ag_po_order_commission_check
        order by check_yearmonth desc
    </select>

    <select id="queryLifePractitionerCount" resultType="java.lang.Integer">
        select
        count(distinct f.customer_id)
        from <include refid="queryLifecommissionList"/> and f.practitioner_type_id = 28

    </select>

    <select id="querySpecialCommissionList" parameterType="com.yd.api.commission.vo.lifecommission.QueryComeCommissionListRequestVO"
            resultType="com.yd.api.commission.vo.lifecommission.ComeCommissionVO">
        select
        null as orderId,
        c.check_yearmonth checkDate,
        IFNULL(i.name_abbre,i.name) insurerName,
        IFNULL(a.name,p.name) planName,
        o.policy_no policyNo,
        o.premium orderPrice,


        o.commission_status commissionCheckStatus,
        o.amount commissionAmount,
        o.rate commissionRate,

        o.created_at orderDate,
        o.commission_no commissionNo,
        o.commissionId,

        date_format(o.commission_year, '%Y-%m-%d') commissionYear,

        o.commission_time commissionTime,
        o.commission_period commissionPeriod,
        o.commission_type commissionType,
        o.commission_item commissionItem,

        null as coverageTerm,
        null as payTerm,
        (case o.config_level when 2 then p2.pName2 else p3.pName3 END) categoryName,
        (case o.config_level when 2 then p2.cName2 else p3.cName3 END) planCategoryName
        from
        (SELECT t.id commissionId, policy_no, commission_no, commission_year, amount, commission_status, commission_time, commission_check_id,premium,t.insurer_id,
            t.commission_type,t.commission_item,
            commission_period, rate, order_id, commission_category, t.created_at, b.product_id, b.plan_id, b.config_level
            from ag_po_order_commission t left join ag_product_life_commission_base b on t.commission_base_id = b.id
            WHERE 1=1
            <if test="startDate != null ">
                and date_format(commission_year, '%Y-%m') &gt;= #{startDate}
            </if>
            <if test="endDate != null ">
                and date_format(commission_year, '%Y-%m') &lt;= #{endDate}
            </if>
        ) o
        left join ag_po_order_commission_check c on o.commission_check_id = c.id
        left join ag_product_plan a on o.plan_id = a.id
        left join ag_product p on o.product_id = p.id
        left join (select plcl.config_level, plcl.product_id, plc.name cName2, pc.name pName2
                        from ag_plan_category_list plcl
                        left join ag_plan_category plc on plcl.plan_category_id = plc.id
                        left join ag_product_category pc on plc.product_category_id = pc.id
                        where plcl.config_level = 2
                    ) p2
        on o.config_level = p2.config_level and o.product_id = p2.product_id
        left join (select plcl.config_level, plcl.plan_id, plc.name cName3, pc.name pName3
                        from ag_plan_category_list plcl
                        left join ag_plan_category plc on plcl.plan_category_id = plc.id
                        left join ag_product_category pc on plc.product_category_id = pc.id
                        where plcl.config_level = 3
                    ) p3
        on o.config_level = p3.config_level and o.plan_id = p3.plan_id
        left join ag_acl_insurer i on o.insurer_id = i.id
        where 1=1
        <if test="insurerId != null">
            and o.insurer_id = #{insurerId}
        </if>
        <if test="insurerBranchId != null">
            and p.insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="commissionType != null">
            and o.commission_type = #{commissionType}
            <if test="commissionItem != null">
                and o.commission_item = #{commissionItem}
            </if>
        </if>
        <if test="commissionCheckStatus != null">
            and o.commission_status = #{commissionCheckStatus}
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
        </choose>
    </select>
</mapper>