<?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
                t.*,
                ib.insurer_name insurerName,
                ib.branch_name insurerBrachName,
                c.check_yearmonth checkDate,
                IFNULL(a.name,p.name) planName,

                f.name,
                f.practitioner_type_id practitionerTypeId,

                (case t.config_level when 2 then p2.pName2 else p3.pName3 END) categoryName,
                (case t.config_level when 2 then p2.cName2 else p3.cName3 END) planCategoryName
        from (SELECT o.id orderId,
                        IFNULL(o.plan_id,b.plan_id) plan_id,
                        IFNULL(o.product_id,b.product_id) product_id,
                        IFNULL(o.config_level,b.config_level) config_level,
                        IFNULL(o.insurer_id,oc.insurer_id) insurer_id,
                        oc.insurer_branch_id,
                        oc.commission_check_id,

                        oc.policy_no policyNo,
                        o.order_price orderPrice,
                        oc.rate commissionRate,
                        oc.amount commissionAmount,
                        oc.commission_status commissionCheckStatus,
                        o.order_date orderDate,
                        oc.commission_no commissionNo,
                        oc.id commissionId,

                        date_format(oc.commission_year, '%Y-%m-%d') commissionYear,
                        oc.commission_status commissionStatus,
                        oc.commission_time commissionTime,
                        oc.commission_period commissionPeriod,
                        oc.commission_type commissionType,
                        oc.commission_item commissionItem,
                        oc.practitioner_id practitionerId,
                        o.ydBranchId,

                        o.cover_term_type coverageTerm,
                        CONCAT(o.payment_term, (case o.payment_term_unit when 'Y' then '年' when 'M' then '月' else '日' END)) payTerm
                        from ag_po_order_commission oc
                        left join
                        (SELECT id,  order_price, product_id,
                                plan_id, insurer_id, order_date,  config_level, cover_term, insurer_branch_id ydBranchId,
                                payment_term, payment_term_unit,
                                cover_term_type
                                from ag_po_order t
                                where t.status in (3,4) and t.policy_no is not null
                                and (t.plan_id not in (424,453,464) or t.plan_id is null)
                        ) o ON o.id = oc.order_id
                        left join ag_product_life_commission_base b on oc.commission_base_id = b.id
                ) t
        left join ag_acl_insurer_branch ib on t.insurer_branch_id = ib.id
        left join ag_po_order_commission_check c on t.commission_check_id = c.id
        left join ag_product_plan a on t.plan_id = a.id
        left join ag_product p on t.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 t.config_level = p2.config_level and t.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 t.config_level = p3.config_level and t.plan_id = p3.plan_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 = t.orderId
        where 1=1
        <if test="startDate != null ">
            and date_format(t.commissionYear, '%Y-%m') &gt;= #{startDate}
        </if>
        <if test="endDate != null ">
            and date_format(t.commissionYear, '%Y-%m') &lt;= #{endDate}
        </if>
        <if test="policyNo != null">
            and policyNo like concat(concat('%',#{policyNo,jdbcType=VARCHAR}),'%')
        </if>

        <if test="insurerId != null">
            and t.insurer_id = #{insurerId}
        </if>
        <if test="insurerBranchId != null">
            and t.insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
            and t.commissionCheckStatus = #{commissionCheckStatus}
        </if>
        <if test="commissionType != null">
            and t.commissionType = #{commissionType}
            <if test="commissionItem != null">
                and t.commissionItem = #{commissionItem}
            </if>
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
        </choose>
        order by t.orderDate desc
    </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 t.commissionCheckStatus = '1' THEN 1 ELSE 0 END),0) totalDaiLaiYong,
            IFNULL(sum(CASE WHEN t.commissionCheckStatus = '2' THEN 1 ELSE 0 END),0) totalYiBiDui,
            IFNULL(sum(CASE WHEN t.commissionCheckStatus = '3' THEN 1 ELSE 0 END),0) totalYiTuiBao,
            IFNULL(sum(t.orderPrice),0) totalOrderPrice,
            IFNULL(sum(t.commissionAmount),0) totalCommission
        from <include refid="queryLifecommissionList"/>
    </select>

    <sql id="queryLifecommissionList">

             (SELECT o.id orderId,
                    IFNULL(o.plan_id,b.plan_id) plan_id,
                    IFNULL(o.product_id,b.product_id) product_id,
                    IFNULL(o.config_level,b.config_level) config_level,
                    IFNULL(o.insurer_id,oc.insurer_id) insurer_id,
                    oc.insurer_branch_id,
                    oc.commission_check_id,

                    oc.policy_no policyNo,
                    o.order_price orderPrice,
                    oc.rate commissionRate,
                    oc.amount commissionAmount,
                    oc.commission_status commissionCheckStatus,
                    o.order_date orderDate,
                    oc.commission_no commissionNo,
                    oc.id commissionId,

                    date_format(oc.commission_year, '%Y-%m-%d') commissionYear,
                    oc.commission_status commissionStatus,
                    oc.commission_time commissionTime,
                    oc.commission_period commissionPeriod,
                    oc.commission_type commissionType,
                    oc.commission_item commissionItem,
                    oc.practitioner_id practitionerId,
                    o.ydBranchId,

                    o.cover_term_type coverageTerm,
                    CONCAT(o.payment_term, (case o.payment_term_unit when 'Y' then '年' when 'M' then '月' else '日' END)) payTerm
                    from ag_po_order_commission oc
                    left join
                        (SELECT id,  order_price, product_id,
                                plan_id, insurer_id, order_date,  config_level, cover_term, insurer_branch_id ydBranchId,
                                payment_term, payment_term_unit,
                                cover_term_type
                        from ag_po_order t
                        where t.status in (3,4) and t.policy_no is not null
                        and (t.plan_id not in (424,453,464) or t.plan_id is null)
                        ) o ON o.id = oc.order_id
                    left join ag_product_life_commission_base b on oc.commission_base_id = b.id
            ) t
        left join ag_acl_insurer_branch ib on t.insurer_branch_id = ib.id
        left join ag_po_order_commission_check c on t.commission_check_id = c.id
        left join ag_product_plan a on t.plan_id = a.id
        left join ag_product p on t.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 = t.orderId
        where 1=1
        <if test="startDate != null ">
            and date_format(t.commissionYear, '%Y-%m') &gt;= #{startDate}
        </if>
        <if test="endDate != null ">
            and date_format(t.commissionYear, '%Y-%m') &lt;= #{endDate}
        </if>
        <if test="policyNo != null">
            and policyNo like concat(concat('%',#{policyNo,jdbcType=VARCHAR}),'%')
        </if>

        <if test="insurerId != null">
            and t.insurer_id = #{insurerId}
        </if>
        <if test="insurerBranchId != null">
            and t.insurer_branch_id = #{insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
          and t.commissionCheckStatus = #{commissionCheckStatus}
        </if>
        <if test="commissionType != null">
            and t.commissionType = #{commissionType}
            <if test="commissionItem != null">
                and t.commissionItem = #{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>
            <if test="policyNo != null">
                and policy_no like concat(concat('%',#{policyNo,jdbcType=VARCHAR}),'%')
            </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>