<?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.commission_rate commissionRate,
            o.commission_amount commissionAmount,
            o.commission_check_status commissionCheckStatus,
            o.order_date orderDate,
            IFNULL(o.coverageTerm,e.coverageTerm) coverageTerm,
            IFNULL(o.payTerm,e.payTerm) payTerm,
            f.name,
            f.practitioner_type_id practitionerTypeId,
            IFNULL(p.categoryName,a.categoryName2) categoryName,
            IFNULL(p.planCategoryName,a.planCategoryName2) planCategoryName
        from (SELECT id, policy_no, order_price, commission_rate, commission_amount, commission_check_status, commission_check_id, product_id,
                        plan_id, insurer_id, order_date, commission_check_at,
                        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 ag_po_order_commission_check c on o.commission_check_id = c.id
        left join (select p.id, p.name, pc.name categoryName, ppc.name planCategoryName
                        from ag_product p left join ag_product_category pc on p.product_category_id = pc.id
                                          left join ag_plan_category_list pcl on p.id = pcl.product_id
                                          LEFT JOIN ag_plan_category ppc on ppc.id=pcl.plan_category_id
                    ) p on o.product_id = p.id
        left join (select pl.id, pl.name, pc.name planCategoryName2, proc.name categoryName2
                        from ag_product_plan pl
                        left join ag_plan_category_list pcl on pl.id = pcl.plan_id
                        left join ag_plan_category pc on pc.id = pcl.plan_category_id
                        left join ag_product pro on pro.id = pl.product_id
                        left join ag_product_category proc on proc.id = pro.product_category_id
                    ) a on o.plan_id = a.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="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
            and o.commission_check_status = #{commissionCheckStatus}
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
            <when test="compareDate != null ">
                and date_format(o.order_date, '%Y-%m') &lt; #{compareDate}
            </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.commission_check_status = '1' THEN 1 ELSE 0 END),0) totalDaiLaiYong,
            IFNULL(sum(CASE WHEN o.commission_check_status = '2' THEN 1 ELSE 0 END),0) totalYiBiDui,
            IFNULL(sum(CASE WHEN o.commission_check_status = '3' THEN 1 ELSE 0 END),0) totalYiTuiBao,
            IFNULL(sum(o.order_price),0) totalOrderPrice,
            IFNULL(sum(o.commission_amount),0) totalCommission
        from (SELECT order_price, commission_amount, commission_check_status, commission_check_id, order_date,
                     insurer_id,id
                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 ag_po_order_commission_check c on o.commission_check_id = c.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="practitionerTypeId != null">
            and f.practitioner_type_id = #{practitionerTypeId}
        </if>
        <if test="commissionCheckStatus != null">
            and o.commission_check_status = #{commissionCheckStatus}
        </if>
        <choose>
            <when test="historyDate != null ">
                and c.check_yearmonth = #{historyDate}
            </when>
            <when test="compareDate != null ">
                and date_format(o.order_date, '%Y-%m') &lt; #{compareDate}
            </when>
        </choose>
    </select>

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


</mapper>