<?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.csf.service.dao.FortuneMapper">

    <resultMap id="BaseResultMap" type="com.yd.csf.service.model.Fortune">
            <id property="id" column="id" />
            <result property="fortuneBizId" column="fortune_biz_id" />
            <result property="expectedFortuneBizId" column="expected_fortune_biz_id" />
            <result property="payableNo" column="payable_no" />
            <result property="reconciliationYearMonth" column="reconciliation_year_month" />
            <result property="fortuneBizType" column="fortune_biz_type" />
            <result property="isPart" column="is_part" />
            <result property="batchBizId" column="batch_biz_id" />
            <result property="commissionBizId" column="commission_biz_id" />
            <result property="commissionExpectedBizId" column="commission_expected_biz_id" />
            <result property="policyNo" column="policy_no" />
            <result property="policyCurrency" column="policy_currency" />
            <result property="fortunePeriod" column="fortune_period" />
            <result property="fortuneTotalPeriod" column="fortune_total_period" />
            <result property="brokerBizId" column="broker_biz_id" />
            <result property="teamBizId" column="team_biz_id" />
            <result property="gradeCommissionRate" column="grade_commission_rate" />
            <result property="shareRate" column="share_rate" />
            <result property="fortuneName" column="fortune_name" />
            <result property="fortuneType" column="fortune_type" />
            <result property="amount" column="amount" />
            <result property="currency" column="currency" />
            <result property="exchangeRate" column="exchange_rate" />
            <result property="hkdAmount" column="hkd_amount" />
            <result property="currentPaymentAmount" column="current_payment_amount" />
            <result property="currentPaymentHkdAmount" column="current_payment_hkd_amount" />
            <result property="currentPaymentRatio" column="current_payment_ratio" />
            <result property="status" column="status" />
            <result property="payoutDate" column="payout_date" />
            <result property="actualPayoutDate" column="actual_payout_date" />
            <result property="reconciliationOperator" column="reconciliation_operator" />
            <result property="isTax" column="is_tax" />
            <result property="taxAmount" column="tax_amount" />
            <result property="netAmount" column="net_amount" />
            <result property="salaryBizId" column="salary_biz_id" />
            <result property="baseRuleBizId" column="base_rule_biz_id" />
            <result property="settlementBizId" column="settlement_biz_id" />
            <result property="calculationFormula" column="calculation_formula" />
            <result property="remark" column="remark" />
            <result property="isDeleted" column="is_deleted" />
            <result property="creatorId" column="creator_id" />
            <result property="updaterId" column="updater_id" />
            <result property="createTime" column="create_time" />
            <result property="updateTime" column="update_time" />
    </resultMap>

    <sql id="Base_Column_List">
        id,fortune_biz_id,expected_fortune_biz_id,payable_no,reconciliation_year_month,fortune_biz_type,batch_biz_id,
        commission_biz_id,commission_expected_biz_id,policy_no,policy_currency,fortune_period,fortune_total_period,broker_biz_id,
        team_biz_id,grade_commission_rate,share_rate,fortune_name,fortune_type,is_part,
        amount,currency,exchange_rate,hkd_amount,current_payment_amount,current_payment_hkd_amount,current_payment_ratio,status,payout_date,actual_payout_date,is_tax,
        tax_amount,net_amount,salary_biz_id,base_rule_biz_id,settlement_biz_id,reconciliation_operator,
        calculation_formula,remark,is_deleted,creator_id,updater_id,
        create_time,update_time
    </sql>

    <select id="getFortuneStatistics" resultType="com.yd.csf.service.dto.FortuneStatisticsVO">
        SELECT
            f.policy_no,
            IFNULL(SUM(CASE WHEN f.is_part = '0' THEN f.hkd_amount ELSE 0 END),0) AS amount,
            SUM(CASE WHEN f.status = '2' THEN f.current_payment_hkd_amount ELSE 0 END) AS sentAmount,
            IFNULL(
                (select sum(unpaid_amount)
                from expected_fortune where status = '0' and policy_no = f.policy_no
                ),0) AS pendingOutAmount,
            IFNULL(SUM(CASE WHEN f.status in ('6','1') THEN f.current_payment_hkd_amount ELSE 0 END),0) AS availableOutAmount,
            COUNT(DISTINCT f.policy_no) AS totalPolicyCount
        FROM fortune f
        WHERE 1 = 1
        <if test="fortuneIdList != null and fortuneIdList.size() > 0">
            AND f.id IN
            <foreach collection="fortuneIdList" item="fortuneId" open="(" close=")" separator=",">
                #{fortuneId}
            </foreach>
        </if>
        GROUP BY f.policy_no
    </select>

</mapper>
