<?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 distinct 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,
       IFNULL(pra.name,cha.name) practitionerName,
       dept.name insurerBranchDeptName,
       sub.name subordinateSystemName,
       op1.drop_option_name practitionerLevel,
       t.commission_amount commissionAmount,
       CONCAT(t.drop_option_code,'-',t.commission_type) commissionType,
       t.referral_rate referralRate,
       t.referral_amount referralAmount,
       t.predict_month_period predictMonthPeriod,
       op.drop_option_name commissionPayoutStatus,
       t.remark,
       pb.payout_yearmonth monthPeriod,
       t.channel_type channelType
	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_acl_insurer i ON i.id = o.insurer_id
        LEFT JOIN ag_acl_practitioner pra ON pra.customer_id = t.customer_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
<!--         LEFT JOIN ag_acl_channel_new cha ON cha.customer_id = t.customer_id -->
        LEFT JOIN ag_acl_channel_new cha ON cha.id = t.channel_id
        LEFT JOIN (select distinct poc.order_id,poc.commission_period,c.* FROM ag_po_order_commission poc,ag_po_order_commission_check c
        WHERE poc.commission_check_id=c.id AND poc.commission_status='2' AND poc.commission_type='1'
	        <if test="item.commissionPayoutYearmonth == null">
	           GROUP BY poc.order_id,poc.commission_period
	        </if>
        ) c ON  c.order_id=t.order_id AND c.commission_period=t.commission_period
        WHERE ((t.settlement_type='1' and setting.practitioner_type_id=28  and mas1.scenario_code='practitioner_level' ) or t.settlement_type='3')
        and mas.drop_code = 'Fortune_Payout_Status'
        and EXISTS(select 1 from ag_po_order_commission poc
                    where poc.commission_status='2' and poc.commission_type='1'
                      and poc.order_id=t.order_id and poc.commission_period=t.commission_period)
       <if test="item.policyNo != null">
           and o.policy_no like concat(concat('%',#{item.policyNo,jdbcType=VARCHAR}),'%')
       </if>
       <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.channelId != null">
           and cha.id = #{item.channelId,jdbcType=BIGINT}
       </if>
       <if test="item.channelType != null">
           	and t.channel_type = #{item.channelType,jdbcType=VARCHAR}
       </if>
       <if test="item.insurerBranchDeptId != null">
           and dept.id = #{item.insurerBranchDeptId,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.commissionTypeDropOptionCodeList != null and item.commissionTypeDropOptionCodeList.size() > 0">
           and t.drop_option_code in
           <foreach collection="item.commissionTypeDropOptionCodeList" index="index" item="dropOptionCode" open="(" separator="," close=")">
               #{dropOptionCode}
           </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}
           	 or (t.drop_option_code in(select distinct t.commission_type FROM ag_settlement_rule_base t where t.calculation_method='3' and t.is_active=1) 
           	 	and QUARTER(DATE_SUB(STR_TO_DATE(CONCAT(c.check_yearmonth,'-01'), "%Y-%m-%d"),INTERVAL 1 MONTH))=QUARTER(DATE_SUB(STR_TO_DATE(CONCAT(#{item.commissionPayoutYearmonth,jdbcType=VARCHAR},'-01'), "%Y-%m-%d"),INTERVAL 1 MONTH))))
       </if>
       <if test="item.predictMonthPeriod != null">
           and DATE_FORMAT(t.predict_month_period, '%Y-%m') = #{item.predictMonthPeriod,jdbcType=VARCHAR}
       </if>
       <if test="item.payoutYearmonth != null">
           and pb.payout_yearmonth = #{item.payoutYearmonth,jdbcType=VARCHAR}
       </if>
     <if test="item.policyNo == null">
          UNION ALL
        SELECT '' orderId,t.id fortuneId,'' commissionPayoutYearmonth,'' insurerName,'' productName,
           '' policyNo,
           '' paymentTermUnit,
           '' coverTermType,
           '0.00' orderPrice,
       		IFNULL(pra.name,cha.name) practitionerName,
            dept.name insurerBranchDeptName,
            sub.name subordinateSystemName,
            op1.drop_option_name practitionerLevel,
            t.commission_amount commissionAmount,
            CONCAT(t.drop_option_code,'-',t.commission_type) commissionType,
            t.referral_rate referralRate,
            t.referral_amount referralAmount,
            t.predict_month_period predictMonthPeriod,
            op.drop_option_name commissionPayoutStatus,
            t.remark,
            pb.payout_yearmonth monthPeriod,
      		t.channel_type channelType
        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
<!--         	LEFT JOIN ag_acl_channel_new cha ON cha.customer_id = t.customer_id -->
        	LEFT JOIN ag_acl_channel_new cha ON cha.id = t.channel_id
            WHERE ((t.settlement_type='2' and mas1.scenario_code='practitioner_level' and setting.practitioner_type_id=28) or t.settlement_type='3')
            AND mas.drop_code = 'Fortune_Payout_Status'
            <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.channelId != null">
				and cha.id = #{item.channelId,jdbcType=BIGINT}
	       	</if>
	      	<if test="item.channelType != null">
	           	and t.channel_type = #{item.channelType,jdbcType=VARCHAR}
	        </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.commissionTypeDropOptionCodeList != null and item.commissionTypeDropOptionCodeList.size() > 0">
                and t.drop_option_code in
                <foreach collection="item.commissionTypeDropOptionCodeList" index="index" item="dropOptionCode" open="(" separator="," close=")">
                    #{dropOptionCode}
                </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.predictMonthPeriod != null">
                and DATE_FORMAT(t.predict_month_period, '%Y-%m') = #{item.predictMonthPeriod,jdbcType=VARCHAR}
            </if>
            <if test="item.payoutYearmonth != null">
                and pb.payout_yearmonth = #{item.payoutYearmonth,jdbcType=VARCHAR}
            </if>
     </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,
               CONCAT(f.drop_option_code,'-',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,
               cha.name								   channelName,
               cha.contact_phone					   contactPhone
        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
        			 LEFT JOIN ag_acl_channel_new cha ON cha.id = f.channel_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"/>
        <result column="calculationRate" jdbcType="DECIMAL" property="calculationRate" />
        <result column="calculationAmount" jdbcType="DECIMAL" property="calculationAmount" />
        <result column="channelType" jdbcType="VARCHAR" property="channelType" />


    </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,
        ifnull(pra.name,cha.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,
        f.channel_type channelType
        from ag_acl_customer_fortune_pay pay
        inner join ag_acl_customer_fortune f on f.fortune_payed_id = pay.id
        inner join ag_acl_customer_fortune_payout_batch pb on pb.id = f.payout_batch_id
        left 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
        left 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, '-', '')
        left join ag_acl_channel_new cha ON cha.id = f.channel_id
        where
           (setting.practitioner_type_id=28 or f.settlement_type='3')
      	<if test="item.channelId != null">
      		and cha.id = #{item.channelId,jdbcType=BIGINT}
     	</if>
      	<if test="item.channelType != null">
      		and f.channel_type = #{item.channelType,jdbcType=VARCHAR}
     	</if>
        <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.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,
            if(o.effective_start_date is not null,left(o.effective_start_date,10),o.effective_start_date) effectiveStartDate,
            o.policy_no policyNo,
            if(f.order_price is null,0,f.order_price) orderPrice,
            if(f.fyc_amount is null,0,f.fyc_amount) fycAmount,
            if(f.grade_commission_rate is null,0,f.grade_commission_rate) gradeCommissionRate,
            (select s.name from ag_acl_practitioner_subordinate_system s where s.id = pra.subordinate_system_id) subordinateSystemName,
            if(f.sale_practitioner_id is not null,(	select name from ag_acl_practitioner a where a.id=sale_practitioner_id limit 1),(select name from ag_acl_practitioner a where a.customer_id = f.customer_id limit 1)) practitionerName,
            /**if(lifepra.commission_rate is null,0,lifepra.commission_rate) commissionRate,*/
           if(mc.code_code is null,if((select t.commission_rate from ag_acl_life_insurance_practitioner t where t.practitioner_id= f.sale_practitioner_id and t.policy_no=o.policy_no) is null,
                                             0,
                                      (select t.commission_rate from ag_acl_life_insurance_practitioner t where t.practitioner_id= f.sale_practitioner_id and t.policy_no=o.policy_no)),
                                                         if((select t.achievements_rate from ag_acl_life_insurance_practitioner t where
                                                             t.practitioner_id= f.sale_practitioner_id and t.policy_no=o.policy_no) is null,
                                                                                                                                                 0,
                                                            (select t.achievements_rate from ag_acl_life_insurance_practitioner t where
                                                              t.practitioner_id= f.sale_practitioner_id and t.policy_no=o.policy_no))) 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,
            if(f.referral_amount is null,0,f.referral_amount)  referralAmount,
            f.calculation_formula calculationFormula,
            f.withdrawed_id   withdrawId,
            if(f.calculation_rate is null,0,f.calculation_rate) calculationRate,
            if(f.calculation_amount is null,0,f.calculation_amount) calculationAmount


        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
            left join ag_md_code mc on mc.code_code = f.drop_option_code and mc.code_type='ACHIEVEMENTS_CODE'
        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
        <if test="item.commissionPayoutStatus != null and item.commissionPayoutStatus == 4  ">
            and f.commission_payout_status = 4
        </if>
        <if test="item.commissionPayoutStatus != null and item.commissionPayoutStatus == 2  ">
            and f.commission_payout_status = 2
        </if>
        <if test="item.commissionPayoutStatus == null ">
            and f.commission_payout_status = 4
        </if>
        <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>

    <sql id="sheet2Data1" >
        select
            ifnull((SELECT t.commission_time FROM ag_po_order_commission t WHERE /*t.id= f.commission_id*/
                                                t.commission_status='2' and t.commission_type='1'
                                            and t.order_id=f.order_id and t.commission_period=f.commission_period limit 1),
                if(f.payout_batch_id is not null,(SELECT t.payout_yearmonth FROM ag_acl_customer_fortune_payout_batch t WHERE t.id= f.payout_batch_id), '')
                ) '0' /*对账月*/,
            (select name from ag_acl_policyholder where order_id = f.order_id and type=2) '1',
            o.policy_no '2',
            i.name '3' /*保司*/,
            o.order_price '4' ,
            if(p.name is not null, p.name, (select tp.name from ag_product_plan tp where tp.id = o.plan_id)) '5' /*产品*/,
            
            o.underwriting_date '6',
            CONCAT(o.payment_term, (case o.payment_term_unit when 'Y' then '年' when 'M' then '月' else '年' END)) '7',
            if(o.fyc_rate,o.fyc_rate,'') '8' /*'FYC%'*/,
            if(f.order_id is not null, o.is_achievement_statistic, '100') '9' /*'业绩折标率%'*/,
            if(l.commission_rate is not null,l.commission_rate,'') '10' /*'分佣%'*/,
            if(l.achievements_rate is not null,l.achievements_rate,'') '11' /*'业绩%'*/,
            if(f.commission_period!=1,f.fyc_rate,'') '12' /*'RYC%'*/,            
            o.fyc_amount '13'/*保单FYC*/,
            round(o.fyc_amount*l.commission_rate/100*IF(f.grade_commission_rate IS NOT NULL,f.grade_commission_rate,'100')/100,2) '14' /*佣金FYC*/,
            round(o.fyc_amount*l.achievements_rate/100*IF(f.grade_commission_rate IS NOT NULL,f.grade_commission_rate,'100')/100,2) '15' /*业绩FYC*/,
            if(f.commission_period!=1,f.fyc_amount,'') '16' /*保单RYC*/,
            if(f.commission_period!=1,f.referral_amount,'') '17' /*佣金RYC*/,

            (select name from ag_acl_channel where id = f.channel_id) '18' /*渠道*/,
            ifnull(coalesce(l.s1_id,l.s2_id,l.s3_id),(SELECT a.subordinate_system_id from ag_acl_practitioner a where a.id = f.sale_practitioner_id)) as '19' /*FYC体系*/,
            ifnull(l.practitioner_name,(select t.name from ag_acl_practitioner t where t.id = f.sale_practitioner_id)) '20' /*销售经纪人*/,
            ifnull(l.practitioner_level,
                    (SELECT drop_option_code from ag_acl_practitioner a
                    LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
                    LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
                    where a.id = f.sale_practitioner_id
                    )) '21' /*经纪人职级*/,
            if(f.grade_commission_rate is not null,f.grade_commission_rate,'100') '22' /*职级率*/,
            sum(case when drop_option_code ='C01' then f.referral_amount else null end) '23' /*首年销售佣金*/,
            sum(case when drop_option_code ='C02' then f.referral_amount else null end) '24' /*续年销售佣金*/,

            (SELECT group_concat(t.practitioner_id) from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id
            WHERE drop_option_code ='R03' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id AND a.id=l.mentor_id 
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '25' /*续期辅导人*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id
            WHERE drop_option_code ='R03' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id AND a.id=l.mentor_id 
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '26' /*续期辅导奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id
            WHERE drop_option_code ='R03' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id AND a.id=l.mentor_id 
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '27' /*续期辅导奖*/,

            sum(case when drop_option_code ='C04' then f.calculation_rate else null end) '28' /*'经纪人月销售加码奖金%'*/,
            sum(case when drop_option_code ='C04' then f.referral_amount else null end) '29' /*经纪人月销售加码奖金*/,

            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C05' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.introducer_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '30' /*推荐人*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C05' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.introducer_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '31' /*个人推荐率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C05' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.introducer_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '32' /*个人推荐奖￥*/,

            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_team_introducer_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '33' /*S1体系推荐率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_team_introducer_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '34' /*S1体系推荐奖金￥*/,

            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C06' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '35' /*一代辅导人*/,
            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C06' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '36' /*一代辅导人职级*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C06' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '37' /*'一代辅导人职级岗位津贴%'*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C06' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '38' /*一代辅导人岗位津贴*/,

            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C07' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.in_mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '39' /*二代辅导人*/,
            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C07' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.in_mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '40' /*二代辅导人职级*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C07' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.in_mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '41' /*'二代辅导人职级岗位津贴%'*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C06' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.in_mentor_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '42' /*二代辅导人岗位津贴*/,

            l.s1_id '43' /*所属S1分部*/,
            (case when l.s1_team_leader_id is not null then
            (SELECT group_concat(calculation_rate)
                from ag_acl_customer_fortune t
                LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
                where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s1_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) else null end) '44' /*S1团队达标奖金率*/,
            (case when l.s1_team_leader_id is not null then
            (SELECT group_concat(referral_amount)
                from ag_acl_customer_fortune t
                LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
                where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s1_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) else null end) '45' /*'S1团队达标奖金￥（季度）'*/,
            (case when l.s1_team_leader_id is not null then
            (SELECT group_concat(calculation_rate)
                from ag_acl_customer_fortune t
                LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
                where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s1_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) else null end) '46' /*S1团队长辅导津贴比率*/,
            (case when l.s1_team_leader_id is not null then
                (SELECT group_concat(referral_amount)
                from ag_acl_customer_fortune t
                LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
                where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s1_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) else null end) '47' /*S1团队长辅导津贴*/,
           (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='R04' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s1_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) as '48' /*S1续期绩效奖金*/,

            l.s2_id '49' /*所属S2体系*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s2_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '50' /*S2团队达标奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s2_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '51' /*'S2团队达标奖金￥（季度）'*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s2_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '52' /*S2团队长辅导津贴比率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s2_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '53' /*S2团队长辅导津贴*/,
           (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='R04' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s2_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) as '54' /*S2续期绩效奖金*/,

            l.s3_id '55' /*所属S3纵队*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s3_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '56' /*S3团队达标奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C08' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s3_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '57' /*'S3团队达标奖金￥（季度）'*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s3_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '58' /*S3团队长辅导津贴比率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where t.drop_option_code ='C10' and order_id=f.order_id and t.sale_practitioner_id=l.practitioner_id and a.id=l.s3_team_leader_id
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
                ) '59' /*S3团队长辅导津贴*/,
            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_raise_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '60' /*S1育成人*/,
            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_raise_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '61' /*S1育成人职级*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_raise_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '62' /*S1育成奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_raise_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '63' /*S1育成金额*/,

            sum(case when drop_option_code ='C14' then f.calculation_rate else null end) '64' /*C14直接销售加码佣金率*/,
            sum(case when drop_option_code ='C14' then f.referral_amount else null end) '65' /*C14直接销售加码佣金*/,

            if(o.settlement_rule_type=1,'简单基本法','标准基本法') '66' /*基本法属性*/,
            sum(case when drop_option_code ='S01' then f.referral_amount else null end) '67' /*S01-首年度销售佣金*/,
         	(SELECT GROUP_CONCAT(referral_amount)
            FROM ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id
            WHERE drop_option_code ='S03' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id 
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '68' /*S03-首年度辅导奖金*/,

            null as '69' /*R01-首年度直接销售佣金（旧）*/,
            sum(case when drop_option_code ='R02' then f.referral_amount else null end) '70' /*R02-续年度直接销售拥金(旧)*/,
<!--             (SELECT GROUP_CONCAT(CONCAT_WS('-', t.practitioner_id, referral_amount)) -->
<!--             FROM ag_acl_customer_fortune t  -->
<!--             LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id -->
<!--             WHERE drop_option_code ='R03' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id AND a.id=l.mentor_id -->
<!-- 	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach> -->
<!--             ) '71' /*R03-续年度辅导佣金(旧)*/, -->
<!--             (SELECT group_concat(CONCAT_WS('-', t.practitioner_id, referral_amount)) -->
<!--             from ag_acl_customer_fortune t -->
<!--             LEFT JOIN ag_acl_practitioner a ON a.customer_id = t.customer_id -->
<!--             WHERE drop_option_code ='R04' AND order_id=f.order_id AND sale_practitioner_id = l.practitioner_id -->
<!-- 	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach> -->
<!--             ) '72' /*R04-续年度绩效奖金(旧)*/, -->
            null as '71' /*R03-续年度辅导佣金(旧)*/,
            null as '72' /*R04-续年度绩效奖金(旧)*/,
            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s1_team_introducer_id and t.segment_vlaue='S1'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '73' /*S1推荐人*/,
            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_team_introducer_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '74' /*S2推荐人*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_team_introducer_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '75' /*S2体系推荐率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_team_introducer_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '76' /*S2体系推荐奖金￥*/,
            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_team_introducer_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '77' /*S3推荐人*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_team_introducer_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '78' /*S3体系推荐率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C15' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_team_introducer_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '79' /*S3体系推荐奖金￥*/,

            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_raise_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '80' /*S2育成人*/,
            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_raise_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '81' /*S2育成人职级*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_raise_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '82' /*S2育成奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s2_raise_id and t.segment_vlaue='S2'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '83' /*S2育成金额*/,

            (SELECT group_concat(a.name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_raise_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '84' /*S3育成人*/,
            (SELECT group_concat(op1.drop_option_name)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            LEFT JOIN ag_acl_practitioner_setting b on b.practitioner_id = a.id
            LEFT JOIN ag_md_drop_options op1 ON op1.id = b.practitioner_level
            where t.drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_raise_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '85' /*S3育成人职级*/,
            (SELECT group_concat(calculation_rate)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_raise_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '86' /*S3育成奖金率*/,
            (SELECT group_concat(referral_amount)
            from ag_acl_customer_fortune t
            LEFT JOIN ag_acl_practitioner a on a.customer_id = t.customer_id
            where drop_option_code ='C09' and order_id=f.order_id and sale_practitioner_id = l.practitioner_id and a.id=l.s3_raise_id and t.segment_vlaue='S3'
	            and t.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
            ) '87' /*S3育成金额*/,
            sum(case when drop_option_code ='C95' then f.referral_amount else null end) as '88' /*其它税前加扣款1*/,
            sum(case when drop_option_code ='C96' then f.referral_amount else null end) as '89' /*其它税前加扣款2*/,
            sum(case when drop_option_code ='C97' then f.referral_amount else null end) as '90' /*其它税前加扣款3*/,
            sum(case when drop_option_code ='C98' then f.referral_amount else null end) as '91' /*其它税前加扣款4*/,
            sum(case when drop_option_code ='C99' then f.referral_amount else null end) as '92' /*其它*/,
            f.order_id,
            l.customer_id
        FROM ag_acl_customer_fortune f
        LEFT JOIN ag_acl_life_insurance_practitioner l on l.order_id = f.order_id and l.practitioner_id = f.sale_practitioner_id
        LEFT JOIN ag_acl_practitioner pra on pra.customer_id = f.customer_id
        LEFT JOIN ag_acl_practitioner_setting ps on ps.practitioner_id = pra.id
        LEFT JOIN ag_po_order o on o.id = f.order_id
        LEFT JOIN ag_acl_insurer i on i.id = o.insurer_id
        LEFT JOIN ag_product p on p.id = o.product_id
        WHERE
            f.id in <foreach collection="list1" item="item" open="(" separator="," close=")">#{item,jdbcType=BIGINT}</foreach>
        GROUP BY f.order_id,f.sale_practitioner_id
    </sql>

    <select id="querySheet2Data" resultType="java.util.LinkedHashMap">
        <include refid="sheet2Data1"/>
        <if test="list2.size() > 0">
        UNION ALL
        select
            (SELECT t.commission_time FROM ag_po_order_commission t WHERE /*t.id= f.commission_id*/
                            t.commission_status='2' and t.commission_type='1'
                            and t.order_id=f.order_id and t.commission_period=f.commission_period limit 1 ) '0' /*对账月*/,
            (select name from ag_acl_policyholder where order_id = f.order_id and type=2) '1',
            o.policy_no '2',
            i.name '3' /*保司*/,
            o.order_price '4' ,
            if(p.name is not null, p.name, (select tp.name from ag_product_plan tp where tp.id = o.plan_id)) '5' /*产品*/,
            o.underwriting_date '6',
            CONCAT(o.payment_term, (case o.payment_term_unit when 'Y' then '年' when 'M' then '月' else '年' END)) '7',

            if(o.fyc_rate,o.fyc_rate,'') '8' /*'FYC%'*/,
            if(f.order_id is not null, o.is_achievement_statistic, '100') '9' /*'业绩折标率%'*/,
            if(l.commission_rate is not null,l.commission_rate,'') '10' /*'分佣%'*/,
            if(l.achievements_rate is not null,l.achievements_rate,'') '11' /*'业绩%'*/,
            if(f.commission_period!=1,f.fyc_rate,'') '12' /*'RYC%'*/,
            o.fyc_amount '13'/*保单FYC*/,
            round(o.fyc_amount*l.commission_rate/100*IF(f.grade_commission_rate IS NOT NULL,f.grade_commission_rate,'100')/100,2) '14' /*佣金FYC*/,
            round(o.fyc_amount*l.achievements_rate/100*IF(f.grade_commission_rate IS NOT NULL,f.grade_commission_rate,'100')/100,2) '15' /*业绩FYC*/,
            if(f.commission_period!=1,f.fyc_amount,'') '16' /*保单RYC*/,
            if(f.commission_period!=1,f.referral_amount,'') '17' /*佣金RYC*/,

            (select name from ag_acl_channel where id = f.channel_id) '18' /*渠道*/,
            coalesce(l.s1_id,l.s2_id,l.s3_id) as '19' /*FYC体系*/,
            l.practitioner_name '20' /*销售经纪人*/,
            l.practitioner_level '21' /*经纪人职级*/,
            if(f.grade_commission_rate is not null,f.grade_commission_rate,'100') '22' /*职级率*/,
            
            null as '23' /*首年销售佣金*/,
            null as '24' /*续年销售佣金*/,

            null as '25' /*续期辅导人*/,
            null as '26' /*续期辅导奖金率*/,
            null as '27' /*续期辅导奖*/,

            null as '28' /*'经纪人月销售加码奖金%'*/,
            null as '29' /*经纪人月销售加码奖金*/,

            null as '30' /*推荐人*/,
            null as '31' /*个人推荐率*/,
            null as '32' /*个人推荐奖￥*/,

            null as '33' /*体系推荐率*/,
            null as '34' /*体系推荐奖金￥*/,

            null as '35' /*一代辅导人*/,
            null as '36' /*一代辅导人职级*/,
            null as '37' /*'一代辅导人职级岗位津贴%'*/,
            null as '38' /*一代辅导人岗位津贴*/,

            null as '39' /*二代辅导人*/,
            null as '40' /*二代辅导人职级*/,
            null as '41' /*'二代辅导人职级岗位津贴%'*/,
            null as '42' /*二代辅导人岗位津贴*/,

            (case when op1.drop_option_code='S1' then pra.subordinate_system_id else null end) as '43' /*所属S1分部*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S1' then f.calculation_rate else null end) '44' /*S1团队达标奖金率*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S1' then f.referral_amount else null end) '45' /*'S1团队达标奖金￥（季度）'*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S1' then f.calculation_rate else null end) '46' /*S1团队长辅导津贴比率*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S1' then f.referral_amount else null end) '47' /*S1团队长辅导津贴*/,
            if(f.commission_period!=1,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S1' then f.referral_amount else null end),
            null) as '48' /*S1续期绩效奖金*/,

            (case when op1.drop_option_code='S2' then pra.subordinate_system_id else null end) as '49' /*所属S2体系*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S2' then f.calculation_rate else null end) '50' /*S2团队达标奖金率*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S2' then f.referral_amount else null end) '51' /*'S2团队达标奖金￥（季度）'*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S2' then f.calculation_rate else null end) '52' /*S2团队长辅导津贴比率*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S2' then f.referral_amount else null end) '53' /*S2团队长辅导津贴*/,
            if(f.commission_period!=1,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S2' then f.referral_amount else null end),
            null) as '54' /*S2续期绩效奖金*/,

            (case when op1.drop_option_code='S3' then pra.subordinate_system_id else null end) as '55' /*所属S3纵队*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S3' then f.calculation_rate else null end) '56' /*S3团队达标奖金率*/,
            (case when f.drop_option_code ='C08' and op1.drop_option_code='S3' then f.referral_amount else null end) '57' /*'S3团队达标奖金￥（季度）'*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S3' then f.calculation_rate else null end) '58' /*S3团队长辅导津贴比率*/,
            (case when f.drop_option_code ='C10' and op1.drop_option_code='S3' then f.referral_amount else null end) '59' /*S3团队长辅导津贴*/,

            null as '60' /*育成人*/,
            null as '61' /*育成人职级*/,
            null as '62' /*育成奖金率*/,
            null as '63' /*育成金额*/,

            null as '64' /*C14直接销售加码佣金率*/,
            null as '65' /*C14直接销售加码佣金*/,

            if(o.settlement_rule_type=1,'简单基本法','标准基本法') '66' /*基本法属性*/,
            null as '67' /*S01-首年度销售佣金*/,
            null as '68' /*S03-首年度辅导奖金*/,

            null as '69' /*R01-首年度直接销售佣金（旧）*/,
            null as '70' /*R02-续年度直接销售拥金(旧)*/,
            null as '71' /*R03-续年度辅导佣金(旧)*/,
            null as '72' /*R04-续年度绩效奖金(旧)*/,

            null as '73' /*S1推荐人*/,
            null as '74' /*S2推荐人*/,
            null as '75' /*S2体系推荐率*/,
            null as '76' /*S2体系推荐奖金￥*/,
            null as '77' /*S3推荐人*/,
            null as '78' /*S3体系推荐率*/,
            null as '79' /*S3体系推荐奖金￥*/,
            null as '80' /*S2育成人*/,
            null as '81' /*S2育成人职级*/,
            null as '82' /*S2育成奖金率*/,
            null as '83' /*S2育成金额*/,
            null as '84' /*S3育成人*/,
            null as '85' /*S3育成人职级*/,
            null as '86' /*S3育成奖金率*/,
            null as '87' /*S3育成金额*/,
            null as '88' /*其它税前加扣款1*/,
            null as '89' /*其它税前加扣款2*/,
            null as '90' /*其它税前加扣款3*/,
            null as '91' /*其它税前加扣款4*/,
            null as '92' /*其它*/,

            f.order_id,
            f.customer_id

        FROM ag_acl_customer_fortune f
        INNER JOIN ag_acl_life_insurance_practitioner l on l.order_id = f.order_id and l.practitioner_id = f.sale_practitioner_id
        LEFT JOIN ag_acl_practitioner pra on pra.customer_id = f.customer_id
        LEFT JOIN ag_acl_practitioner_setting ps on ps.practitioner_id = pra.id
        LEFT JOIN ag_md_drop_options op1 ON op1.id = ps.practitioner_level
        LEFT JOIN ag_po_order o on o.id = f.order_id
        LEFT JOIN ag_acl_insurer i on i.id = o.insurer_id
        LEFT JOIN ag_product p on p.id = o.product_id
        WHERE
        f.id in <foreach collection="list2" item="item" open="(" separator="," close=")"> #{item,jdbcType=BIGINT} </foreach>
        </if>
        ORDER BY '2'
    </select>

    <select id="querySalePractitionerFortune1" parameterType="list"  resultType="Long">
        SELECT f.id
        FROM ag_acl_customer_fortune f
        LEFT JOIN ag_acl_practitioner pra on pra.customer_id = f.customer_id
        WHERE 1=1
        and f.id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and f.drop_option_code in ('C08', 'C10')
        and pra.id in
            (SELECT l.s1_team_leader_id
                from ag_acl_life_insurance_practitioner l
                where l.order_id = f.order_id
            union all
            SELECT l.s2_team_leader_id
                from ag_acl_life_insurance_practitioner l
                where l.order_id = f.order_id
            union all
            SELECT l.s3_team_leader_id
                from ag_acl_life_insurance_practitioner l
                where l.order_id = f.order_id
            )
    </select>

    <select id="querySalePractitionerFortune" parameterType="list"  resultType="Long">
        SELECT f.id
        FROM ag_acl_customer_fortune f
        LEFT JOIN ag_acl_practitioner pra on pra.customer_id = f.customer_id
        WHERE 1=1
        and f.id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and f.drop_option_code in ('C08', 'C10')
    </select>
</mapper>
