<?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
        o.id orderId,
        f.id fortuneId,
        c.check_yearmonth 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
        Left Join ag_acl_practitioner_setting setting on setting.practitioner_id = pra.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
        left join ag_po_order_commission_check c on o.commission_check_id = c.id
        where o.commission_check_status = 2
        AND  mas.drop_code like 'Commission_Payout_Status'
        and setting.practitioner_type_id = 28  -->
    SELECT o.id orderId,t.id fortuneId,c.check_yearmonth 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,
<!--        t.practitioner_level practitionerLevel, -->
       op1.drop_option_name practitionerLevel,
       t.commission_amount commissionAmount,
       t.commission_type commissionType,
       t.referral_rate referralRate,
       t.referral_amount referralAmount,
       t.predict_month_period predictMonthPeriod,
       op.drop_option_name commissionPayoutStatus,
       pb.payout_yearmonth monthPeriod
	FROM ag_acl_customer_fortune t 
        LEFT JOIN ag_po_order o ON o.id = t.order_id 
        LEFT JOIN ag_product p ON p.id = o.product_id
        LEFT JOIN ag_product_plan pp ON pp.id = o.plan_id
        LEFT JOIN ag_po_order_commission oc ON oc.order_id = t.order_id  AND oc.id = t.commission_id
        LEFT JOIN ag_po_order_commission_check c on oc.commission_check_id = c.id
        LEFT JOIN ag_acl_insurer i ON i.id = o.insurer_id
        LEFT JOIN ag_acl_practitioner pra ON pra.customer_id = t.customer_id <!-- ON pra.id = oc.practitioner_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 ON setting.practitioner_id = pra.id
        LEFT JOIN ag_acl_customer_fortune_payout_batch pb ON pb.id = t.payout_batch_id
        LEFT JOIN ag_md_drop_options op ON op.drop_option_code = t.status
        LEFT JOIN ag_md_drop_master mas ON mas.id = op.drop_master_id  
        LEFT JOIN ag_md_drop_options op1 ON op1.id = setting.practitioner_level
        LEFT JOIN ag_md_drop_master mas1 ON mas1.id = op1.drop_master_id
        WHERE t.settlement_type = '1'
        AND mas.drop_code = 'Fortune_Payout_Status'
        AND mas1.scenario_code = 'practitioner_level'
        AND oc.commission_status = '2'
        and setting.practitioner_type_id = 28
        <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.subsystemIdList != null and item.subsystemIdList.size() > 0">
            and sub.id in
            <foreach collection="item.subsystemIdList" index="index" item="subordinateSystemId" open="(" separator="," close=")">
                #{subordinateSystemId}
            </foreach>
        </if>
        <if test="item.practitionerId != null">
            and pra.id = #{item.practitionerId,jdbcType=BIGINT}
        </if>
        <if test="item.commissionPayoutStatus != null">
            and t.status = #{item.commissionPayoutStatus,jdbcType=BIGINT}
            <if test="'1'.equals(item.commissionPayoutStatus)">
                and oc.commission_check_id is not null
            </if>
        </if>
        <if test="item.commissionPayoutYearmonth != null">
            and c.check_yearmonth = #{item.commissionPayoutYearmonth,jdbcType=VARCHAR}
        </if>
        <if test="item.payoutYearmonth != null">
            and DATE_FORMAT(t.predict_month_period, '%Y-%m') = #{item.payoutYearmonth,jdbcType=VARCHAR}
        </if>
      UNION ALL                
    SELECT '' orderId,t.id fortuneId,'' commissionPayoutYearmonth,'' insurerName,'' productName,
	   '' policyNo,
	   '' paymentTermUnit,
	   '' coverTermType,
	   '0.00' orderPrice,
	    pra.name practitionerName,
	    dept.name insurerBranchDeptName,
	    sub.name subordinateSystemName,
<!-- 	    t.practitioner_level practitionerLevel, -->
        op1.drop_option_name practitionerLevel,
	    t.commission_amount commissionAmount,
	    t.commission_type commissionType,
	    t.referral_rate referralRate,
	    t.referral_amount referralAmount,
        t.predict_month_period predictMonthPeriod,
	    op.drop_option_name commissionPayoutStatus,
	    pb.payout_yearmonth monthPeriod
    FROM ag_acl_customer_fortune t 
        LEFT JOIN ag_acl_practitioner pra ON pra.id = t.practitioner_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 ON setting.practitioner_id = pra.id
        LEFT JOIN ag_acl_customer_fortune_payout_batch pb ON pb.id = t.payout_batch_id
        LEFT JOIN ag_md_drop_options op ON op.drop_option_code = t.status
        LEFT JOIN ag_md_drop_master mas ON mas.id = op.drop_master_id  
        LEFT JOIN ag_md_drop_options op1 ON op1.id = setting.practitioner_level
        LEFT JOIN ag_md_drop_master mas1 ON mas1.id = op1.drop_master_id   
        WHERE t.settlement_type = '2'
        AND mas.drop_code = 'Fortune_Payout_Status'
        AND mas1.scenario_code = 'practitioner_level'
        and setting.practitioner_type_id = 28
        <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.subsystemIdList != null and item.subsystemIdList.size() > 0">
            and sub.id in
            <foreach collection="item.subsystemIdList" index="index" item="subordinateSystemId" open="(" separator="," close=")">
                #{subordinateSystemId}
            </foreach>
        </if>
        <if test="item.practitionerId != null">
            and pra.id = #{item.practitionerId,jdbcType=BIGINT}
        </if>
        <if test="item.commissionPayoutStatus != null">
            and t.status = #{item.commissionPayoutStatus,jdbcType=BIGINT}
        </if>
<!--         <if test="item.commissionPayoutYearmonth != null"> -->
<!--             and c.check_yearmonth = #{item.commissionPayoutYearmonth,jdbcType=VARCHAR} -->
<!--         </if> -->
        <if test="item.payoutYearmonth != null">
            and DATE_FORMAT(t.predict_month_period, '%Y-%m') = #{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,
               f.fyc_amount                            fycAmount
        from ag_acl_customer_fortune f
                     left join ag_po_order o on o.id = f.order_id
                     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
                     left join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
                     LEFT JOIN ag_acl_practitioner pra  ON pra.customer_id = f.customer_id
                     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_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 or o.status is null)
                  AND mas.drop_code like 'Commission_Payout_Status'
                  AND (f.order_price != 0 or f.order_price is null)
                  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="withdrawId" jdbcType="BIGINT" property="withdrawId" />
        <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" />
        <result column="practitionerId" jdbcType="BIGINT" property="practitionerId" />
        <result column="taxAmount" jdbcType="DECIMAL" property="taxAmount" />
        <result column="afterTaxAmount" jdbcType="DECIMAL" property="afterTaxAmount" />
        <result column="insurerBranchId" jdbcType="BIGINT" property="insurerBranchId" />

        <result column="insuredName" jdbcType="VARCHAR" property="insuredName" />
        <result column="insurerName" jdbcType="VARCHAR" property="insurerName" />
        <result column="productName" jdbcType="VARCHAR" property="productName" />
        <result column="planName" jdbcType="VARCHAR" property="planName" />
        <result column="effectiveStartDate" jdbcType="VARCHAR" property="effectiveStartDate" />
        <result column="policyNo" jdbcType="VARCHAR" property="policyNo" />
        <result column="orderPrice" jdbcType="DECIMAL" property="orderPrice" />
        <result column="fycAmount" jdbcType="DECIMAL" property="fycAmount" />
        <result column="gradeCommissionRate" jdbcType="DECIMAL" property="gradeCommissionRate" />
        <result column="commissionRate" jdbcType="DECIMAL" property="commissionRate" />
        <result column="fycRate" jdbcType="DECIMAL" property="fycRate" />
        <result column="shareRate" jdbcType="DECIMAL" property="shareRate" />
        <result column="dropOptionCode" jdbcType="DECIMAL" property="dropOptionCode" />
        <result column="isActive" jdbcType="INTEGER" property="isActive"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
        <result column="commissionType" jdbcType="VARCHAR" property="commissionType"/>
        <result column="calculationFormula" jdbcType="VARCHAR" property="calculationFormula"/>
    </resultMap>
    <select id="transformForWithdrawLabel"
            parameterType="com.yd.dal.entity.agms.fortune.WithdrawQueryInfo"
            resultMap="WithdrawLabelInfo">
        select distinct pay.id                       payId,
        pay.withdraw_id              withdrawId,
        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,
        pra.ID practitionerId,
        if(salary.taxout_amount is null,0,salary.taxout_amount) taxAmount,
        if(salary.net_amount is null,0,salary.net_amount) afterTaxAmount,
        ib.id insurerBranchId,
        salary.is_active isActive
        from ag_acl_customer_fortune_pay pay
        inner join ag_acl_customer_fortune f on f.fortune_payed_id = pay.id
<!--         inner JOIN ag_po_order o on o.id = f.order_id -->
        inner join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
        inner JOIN ag_acl_practitioner pra on pay.customer_id = pra.customer_id
        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
        inner join ag_acl_practitioner_setting setting on pra.id = setting.practitioner_id
        left join ag_md_drop_options opt on opt.id = setting.practitioner_level
        left join ag_acl_life_practitioner_salary salary on salary.practitioner_id=pra.id and salary.`year_month`=REPLACE (pb.payout_yearmonth, '-', '')

        where
           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.subsystemIdList != null and item.subsystemIdList.size() > 0">
            and sub.id in
            <foreach collection="item.subsystemIdList" index="index" item="subordinateSystemId" open="(" separator="," close=")">
                #{subordinateSystemId}
            </foreach>
        </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>
        order by pb.payout_yearmonth
    </select>


    <select id="salaryPolicyNoDetails"
            parameterType="com.yd.dal.entity.agms.fortune.WithdrawQueryInfo"
            resultMap="WithdrawLabelInfo">
        select
            (select off.name from ag_acl_policyholder off where off.order_id=o.id and off.type=2) insuredName,
            i.name insurerName,
            p.name productName,
            pp.name planName,
            o.effective_start_date effectiveStartDate,
            o.policy_no policyNo,
            f.order_price orderPrice,
            f.fyc_amount fycAmount,
            f.grade_commission_rate gradeCommissionRate,
            (select s.name from ag_acl_practitioner_subordinate_system s where s.id = pra.subordinate_system_id) subordinateSystemName,
            pra.name practitionerName,
            if(f.commission_rate is null,0,f.commission_rate) commissionRate,
            if(f.fyc_rate is null,0,f.fyc_rate) fycRate,
            if(f.share_rate is null,100,f.share_rate) shareRate,
            f.drop_option_code dropOptionCode,
            f.remark remark,
            f.commission_type commissionType,
            f.referral_amount  referralAmount,
            f.calculation_formula calculationFormula,
            f.withdrawed_id   withdrawId
        from ag_acl_customer_fortune f
                 left join ag_po_order o on o.id = f.order_id
                 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
                 left join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
                 left join ag_acl_practitioner pra  on pra.customer_id = f.customer_id
                 left join ag_acl_life_insurance_practitioner lifepra on pra.id=lifepra.practitioner_id and lifepra.order_id=o.id
                 inner join ag_acl_practitioner_setting setting on pra.id = setting.practitioner_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 or o.status is null)
          AND mas.drop_code like 'Commission_Payout_Status'
          AND (f.order_price != 0 or f.order_price is null) and setting.practitioner_type_id = 28
          and f.commission_payout_status = 4
        <if test="item.payoutYearmonth != null">
            and pb.payout_yearmonth = #{item.payoutYearmonth,jdbcType=VARCHAR}
        </if>
        <if test="item.practitionerId != null">
            and pra.id = #{item.practitionerId,jdbcType=BIGINT}
        </if>

    </select>

</mapper>
