<?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.AgmsFortuneMapper">

    <select id="findFortuneStatisticalByCustomers" parameterType="list"  resultType="com.yd.dal.entity.agms.fortune.CustomerFortuneStatisticalInfo">
        select
            s.customer_id         customerId,
            s.accumulated_fortune accumulatedFortune,
            s.cancelled_fortune   cancelledFortune,
            s.drawn_fortune       drawnFortune,
            (SELECT sum(w.withdraw_amount) from ag_acl_customer_fortune_withdraw w where w.customer_id = s.customer_id and w.is_paid = 0 and lottery_draws_id is NULL) processing,
            (SELECT sum(w.withdraw_amount) from ag_acl_customer_fortune_withdraw w where w.customer_id = s.customer_id and w.is_paid = 1 and YEAR(w.paid_date ) = YEAR(NOW( )) and w.lottery_draws_id is null) yearDrawnFortune,
            (SELECT sum(f.referral_amount) from ag_acl_customer_fortune f LEFT JOIN ag_po_order o on o.id = f.order_id where o.`status` = 3 and  f.customer_id = s.customer_id and  f.withdrawable_date > now() and f.withdrawed_id is null) uncashableFortune,
            c.name customerName,
            c.mobile_no mobileNo,
            c.id_no customerIdNo,
            c.id_type_id customerIdTypeId,
            c.birth_date customerBirthdate,
            c.gender customerGender
        from ag_acl_customer_fortune_statistic s
            left join ag_acl_customer c on c.id=s.customer_id
        where s.customer_id in
            <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
    </select>
    <select id="commissionPayoutStatusQuery"
            parameterType="com.yd.dal.entity.agms.fortune.CommissionPayoutStatusQueryInfo"
            resultType="com.yd.dal.entity.agms.fortune.CommissionPayoutStatus">
        select f.id fortuneId,
        date_format(f.commission_payout_at, '%Y-%m') commissionPayoutYearmonth,
        i.name insurerName,
        if(o.config_level = 2,p.name,pp.name) productName,
        o.policy_no policyNo,
        if(o.payment_term IS NOT NULL ,CONCAT(o.payment_term,(case o.payment_term_unit when 'Y' then '年' when 'M' then '月' else '日' END ) ),(SELECT e.element_text from ag_po_order_life_product_elements e where o.id = e.order_id and e.factor_code like 'f_pay_term' LIMIT 1)) paymentTermUnit,
        if(o.cover_term IS NOT NULL ,o.cover_term_type ,(SELECT e.element_text from ag_po_order_life_product_elements e where o.id = e.order_id and e.factor_code like 'f_coverage_term' LIMIT 1)) coverTermType,
        o.order_price orderPrice,
        pra.name practitionerName,
        dept.name insurerBranchDeptName,
        sub.name subordinateSystemName,
        f.practitioner_level practitionerLevel,
        f.commission_amount commissionAmount,
        f.commission_type commissionType,
        f.referral_rate referralRate,
        f.referral_amount referralAmount,
        op.drop_option_name commissionPayoutStatus,
        pb.payout_yearmonth monthPeriod
        from ag_acl_customer_fortune f
        LEFT JOIN ag_po_order o
        LEFT JOIN ag_acl_insurer i ON i.id = o.insurer_id
        LEFT JOIN ag_product p ON p.id = o.product_id
        LEFT JOIN ag_product_plan pp ON pp.id = o.plan_id
        ON o.id = f.order_id
        LEFT JOIN ag_acl_practitioner pra
        LEFT JOIN ag_acl_insurer_branch_dept dept ON pra.dept_id = dept.id
        LEFT JOIN ag_acl_practitioner_subordinate_system sub ON sub.id = pra.subordinate_system_id
        ON pra.customer_id = f.customer_id
        LEFT JOIN ag_acl_customer_fortune_payout_batch pb ON pb.id = f.payout_batch_id
        LEFT JOIN ag_md_drop_options op
        LEFT JOIN ag_md_drop_master mas ON mas.id = op.drop_master_id
        ON op.drop_option_code = f.commission_payout_status
        where o.commission_check_status = 2
        AND  mas.drop_code like 'Commission_Payout_Status'
        <if test="item.insurerId != null">
            AND i.id = #{item.insurerId,jdbcType=BIGINT}
        </if>
        <if test="item.insurerBranchId != null">
            and  pra.insurer_branch_id = #{item.insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="item.insurerBranchDeptId != null">
            and  dept.id = #{item.insurerBranchDeptId,jdbcType=BIGINT}
        </if>
        <if test="item.subordinateSystemId != null">
            and sub.id = #{item.subordinateSystemId,jdbcType=BIGINT}
        </if>
        <if test="item.practitionerId != null">
            and pra.id = #{item.practitionerId,jdbcType=BIGINT}
        </if>
        <if test="item.commissionPayoutStatus != null">
            and f.commission_payout_status = #{item.commissionPayoutStatus,jdbcType=BIGINT}
        </if>
        <if test="item.commissionPayoutYearmonth != null">
            and date_format(f.commission_payout_at, '%Y-%m') = #{item.commissionPayoutYearmonth,jdbcType=VARCHAR}
        </if>
        <if test="item.payoutYearmonth != null">
            and pb.payout_yearmonth = #{item.payoutYearmonth,jdbcType=VARCHAR}
        </if>
    </select>

    <select id="fortunePayToOrder"  resultType="com.yd.dal.entity.agms.fortune.FortunePayToOrderInfo">
        select i.name                                  insurerName,
               o.policy_no                             policyNo,
               o.order_price                           orderPrice,
               f.commission_type                       commissionType,
               f.referral_rate                         referralRate,
               f.referral_amount                       referralAmount,
               if(o.config_level = 2, p.name, pp.name) productName,
               pb.payout_yearmonth                     payoutYearmonth,
               pra.name                                practitionerName,
               f.commission_amount                     commissionAmount,
               f.commission_rate                       commissionRate,
               op.drop_option_name                     commissionPayoutStatus,
               pra.mobile_no                           mobileNo,
               f.practitioner_level                    practitionerLevel,
               dept.name                               insurerBranchDeptName,
               sub.name                                subordinateSystemName,
               ib.branch_name                          insurerBranchName
        from ag_acl_customer_fortune f
                     left join ag_po_order o
                     left join ag_product_plan pp on pp.id = o.plan_id
                     left join ag_product p on p.id = o.product_id
                     left join ag_acl_insurer i on o.insurer_id = i.id
                on o.id = f.order_id
                     left join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
                     LEFT JOIN ag_acl_practitioner pra
                     LEFT JOIN ag_acl_insurer_branch ib on ib.id = pra.insurer_branch_id
                     LEFT JOIN ag_acl_insurer_branch_dept dept ON pra.dept_id = dept.id
                     LEFT JOIN ag_acl_practitioner_subordinate_system sub ON sub.id = pra.subordinate_system_id
                ON pra.customer_id = f.customer_id
                     LEFT JOIN ag_md_drop_options op
                     LEFT JOIN ag_md_drop_master mas ON mas.id = op.drop_master_id
                ON op.drop_option_code = f.commission_payout_status
                where (o.status = 3 or o.status = 4)
                  AND mas.drop_code like 'Commission_Payout_Status'
                  and f.order_price != 0
                  and f.fortune_payed_id in
        <foreach collection="array" item="item" open="(" separator="," close=")">
            #{item,jdbcType=BIGINT}
        </foreach>
    </select>

    <resultMap id="WithdrawLabelInfo" type="com.yd.dal.entity.agms.fortune.WithdrawLabelInfo">
        <id column="payId" jdbcType="BIGINT" property="payId" />
        <result column="payoutYearmonth" jdbcType="VARCHAR" property="payoutYearmonth" />
        <result column="practitionerName" jdbcType="VARCHAR" property="practitionerName" />
        <result column="referralAmount" jdbcType="DECIMAL" property="referralAmount" />
        <result column="mobileNo" jdbcType="VARCHAR" property="mobileNo" typeHandler="com.yd.util.deshandler.DESTypeHandler"/>
        <result column="practitionerLevel" jdbcType="VARCHAR" property="practitionerLevel" />
        <result column="insurerBranchName" jdbcType="VARCHAR" property="insurerBranchName" />
        <result column="insurerBranchDeptName" jdbcType="VARCHAR" property="insurerBranchDeptName" />
        <result column="subordinateSystemName" jdbcType="VARCHAR" property="subordinateSystemName" />
        <result column="commissionPayoutStatus" jdbcType="VARCHAR" property="commissionPayoutStatus" />
        <result column="payDate" jdbcType="VARCHAR" property="payDate" />
    </resultMap>
    <select id="transformForWithdrawLabel"
            parameterType="com.yd.dal.entity.agms.fortune.WithdrawQueryInfo"
            resultMap="WithdrawLabelInfo">
        select pay.id                       payId,
               pb.payout_yearmonth          payoutYearmonth,
               pra.name                     practitionerName,
               pay.withdraw_amount          referralAmount,
               pra.mobile_no                mobileNo,
               opt.drop_option_name         practitionerLevel,
               ib.branch_name               insurerBranchName,
               dept.name                    insurerBranchDeptName,
               sub.name                     subordinateSystemName,
               if(pay.pay_status = 0, 2, 4) commissionPayoutStatus,
               date_format(pay.pay_date, '%Y-%m-%d %H:%i:%s') payDate
        from ag_acl_customer_fortune_pay pay
                     left join ag_acl_customer_fortune f
                     left join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
                on f.fortune_payed_id = pay.id,
             ag_acl_practitioner pra
                     LEFT JOIN ag_acl_insurer_branch ib on ib.id = pra.insurer_branch_id
                     LEFT JOIN ag_acl_insurer_branch_dept dept ON pra.dept_id = dept.id
                     LEFT JOIN ag_acl_practitioner_subordinate_system sub ON sub.id = pra.subordinate_system_id
                     left join ag_acl_practitioner_setting setting
                     left join ag_md_drop_options opt on opt.id = setting.practitioner_level
                     on pra.id = setting.practitioner_id
        where pay.customer_id = pra.customer_id
          and pra.is_active = 1
          and setting.practitioner_type_id = 28
          and pay.withdraw_amount != 0
        <if test="item.payoutYearmonth != null">
            and pb.payout_yearmonth = #{item.payoutYearmonth,jdbcType=VARCHAR}
        </if>
        <if test="item.insurerBranchId != null">
            and  pra.insurer_branch_id = #{item.insurerBranchId,jdbcType=BIGINT}
        </if>
        <if test="item.insurerBranchDeptId != null">
            and  dept.id = #{item.insurerBranchDeptId,jdbcType=BIGINT}
        </if>
        <if test="item.subordinateSystemId != null">
            and sub.id = #{item.subordinateSystemId,jdbcType=BIGINT}
        </if>
        <if test="item.practitionerId != null">
            and pra.id = #{item.practitionerId,jdbcType=BIGINT}
        </if>
        <if test="item.commissionPayoutStatus != null">
            and f.commission_payout_status = #{item.commissionPayoutStatus,jdbcType=BIGINT}
        </if>
        group by pay.id
    </select>

</mapper>