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

    <resultMap id="BaseResultMap" type="com.yd.csf.service.model.CommissionExpected">
            <id property="id" column="id" />
            <result property="commissionExpectedBizId" column="commission_expected_biz_id" />
            <result property="receivableNo" column="receivable_no" />
            <result property="commissionBizType" column="commission_biz_type" />
            <result property="policyNo" column="policy_no" />
            <result property="premium" column="premium" />
            <result property="policyCurrency" column="policy_currency" />
            <result property="insuranceCompanyBizId" column="insurance_company_biz_id" />
            <result property="productLaunchBizId" column="product_launch_biz_id" />
            <result property="reconciliationCompany" column="reconciliation_company" />
            <result property="reconciliationCompanyCode" column="reconciliation_company_code" />
            <result property="reconciliationCompanyBizId" column="reconciliation_company_biz_id" />
            <result property="commissionPeriod" column="commission_period" />
            <result property="totalPeriod" column="total_period" />
            <result property="commissionName" column="commission_name" />
            <result property="commissionType" column="commission_type" />
            <result property="commissionRatio" column="commission_ratio" />
            <result property="amount" column="amount" />
            <result property="currency" column="currency" />
            <result property="commissionDate" column="commission_date" />
            <result property="actualCommissionDate" column="actual_commission_date" />
            <result property="status" column="status" />
            <result property="statusDesc" column="status_desc" />
            <result property="defaultExchangeRate" column="default_exchange_rate" />
            <result property="expectedAmount" column="expected_amount" />
            <result property="paidAmount" column="paid_amount" />
            <result property="paidRatio" column="paid_ratio" />
            <result property="pendingRatio" column="pending_ratio" />
            <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,commission_expected_biz_id,receivable_no,commission_biz_type,
        policy_no,premium,policy_currency,insurance_company_biz_id,product_launch_biz_id,
        reconciliation_company,reconciliation_company_code,reconciliation_company_biz_id,commission_period,
        total_period,commission_name,commission_type,commission_ratio,amount,currency,default_exchange_rate,expected_amount,
        commission_date,status,status_desc,paid_amount,paid_ratio,pending_ratio,remark,is_deleted,creator_id,
        updater_id,create_time,update_time
    </sql>

    <select id="getExpectedStatistics" resultType="com.yd.csf.service.vo.CommissionExpectedStatisticsVO">
        SELECT
            IFNULL(SUM(expected_amount),0) AS totalAmount,
            IFNULL(SUM(paid_amount),0) AS totalPaidAmount,
            COUNT(DISTINCT policy_no) AS totalPolicyCount,
            GROUP_CONCAT(DISTINCT policy_no) AS policyNos
        FROM
            commission_expected ce
        <where>
            <if test="expectedIds != null and expectedIds.size > 0">
                id IN
                <foreach collection="expectedIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>

    <select id="receivableReportPage" resultType="com.yd.csf.service.vo.ReceivableReportVO">
        select
            null as receivableNo,
            ce.policy_no as policyNo,
            (select pp.name from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolder,
            (select pp.name_en from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolderEn,
            (select pi.name from policy_insurant pi where pi.policy_biz_id = p.policy_biz_id limit 1) as insured,
            (select pi.name_en from policy_insurant pi where pi.policy_biz_id = p.policy_biz_id limit 1) as insuredEn,
            ce.manual_remark as manualRemark,
            MAX(ce.reconciliation_company) as reconciliationCompany,
            MAX(ce.reconciliation_company_biz_id) as reconciliationCompanyBizId,
            ce.commission_period as commissionPeriod,
            MAX(ce.total_period) as totalPeriod,
            MAX(ce.commission_date) as commissionDate,
            ifnull(sum(ce.commission_ratio), 0) as commissionRatio,
            ifnull(sum(ce.amount), 0) as amount,
            MAX(ce.currency) as currency,
            ifnull(sum(ce.expected_amount), 0) as hkdAmount,
            ifnull(sum(ce.paid_amount), 0) as paidAmount,
            ifnull(sum(ce.expected_amount) - sum(ce.paid_amount), 0) as unpaidAmount,
            case when ifnull(sum(ce.expected_amount), 0) > 0
                then round(ifnull(sum(ce.paid_amount), 0) / ifnull(sum(ce.expected_amount), 0) * 100, 2)
                else 0
            end as paidRatio,
            case when ifnull(sum(ce.expected_amount), 0) > 0
                then round((ifnull(sum(ce.expected_amount), 0) - ifnull(sum(ce.paid_amount), 0)) / ifnull(sum(ce.expected_amount), 0) * 100, 2)
                else 0
            end as unpaidRatio,
            MAX(ce.default_exchange_rate) as exchangeRate,
            MAX(p.insurance_company) as insuranceCompany,
            MAX(p.product_name) as productName,
            ifnull(avg(ce.premium), 0) as premium,
            MAX(p.currency) as policyCurrency
        from commission_expected ce
        left join policy p on ce.policy_no = p.policy_no
        <where>
            ce.commission_biz_type = 'R'
            <if test="expectedIds != null and expectedIds.size > 0">
                and ce.id in
                <foreach collection="expectedIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            and ce.is_deleted = 0
        </where>
        group by ce.policy_no, ce.commission_period
        union all
        select
            ce.receivable_no as receivableNo,
            ce.policy_no as policyNo,
            (select pp.name from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolder,
            (select pp.name_en from policy_policyholder pp where pp.policy_biz_id = p.policy_biz_id limit 1) as policyHolderEn,
            (select pi.name from policy_insurant pi where pi.policy_biz_id = p.policy_biz_id limit 1) as insured,
            (select pi.name_en from policy_insurant pi where pi.policy_biz_id = p.policy_biz_id limit 1) as insuredEn,
            ce.manual_remark as manualRemark,
            ce.reconciliation_company as reconciliationCompany,
            ce.reconciliation_company_biz_id as reconciliationCompanyBizId,
            ce.commission_period as commissionPeriod,
            ce.total_period as totalPeriod,
            ce.commission_date as commissionDate,
            null as commissionRatio,
            ifnull(ce.amount, 0) as amount,
            ce.currency as currency,
            ifnull(ce.expected_amount, 0) as hkdAmount,
            ifnull(ce.paid_amount, 0) as paidAmount,
            ifnull(ce.expected_amount - ce.paid_amount, 0) as unpaidAmount,
            case when ifnull(ce.expected_amount, 0) > 0
                then round(ifnull(ce.paid_amount, 0) / ifnull(ce.expected_amount, 0) * 100, 2)
                else 0
            end as paidRatio,
            case when ifnull(ce.expected_amount, 0) > 0
                then round((ifnull(ce.expected_amount, 0) - ifnull(ce.paid_amount, 0)) / ifnull(ce.expected_amount, 0) * 100, 2)
                else 0
            end as unpaidRatio,
            ce.default_exchange_rate as exchangeRate,
            p.insurance_company as insuranceCompany,
            p.product_name as productName,
            ifnull(ce.premium, 0) as premium,
            null as policyCurrency
        from commission_expected ce
        left join policy p on ce.policy_no = p.policy_no
        <where>
            ce.commission_biz_type = 'U'
            <if test="expectedIds != null and expectedIds.size > 0">
                and ce.id in
                <foreach collection="expectedIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            and ce.is_deleted = 0
        </where>
    </select>
</mapper>
