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

    <select id="page" resultType="com.yd.csf.feign.response.expectedfortune.ApiExpectedFortunePageResponse">
        select * from expected_fortune ef
        <where>
            <if test="request.broker != null and request.broker != ''">
                and ef.broker like concat('%', #{request.broker}, '%')
            </if>

            <if test="request.fortunePeriod != null">
                and ef.fortune_period = #{request.fortunePeriod}
            </if>

            and ef.policy_no = #{request.policyNo}
            and ef.is_deleted = 0
        </where>
    </select>

    <select id="listByPolicyBizId" resultType="com.yd.csf.service.model.ExpectedFortune">
        select *
        from expected_fortune ef
        where ef.policy_no = (select policy_no from policy where policy_biz_id = #{policyBizId})
    </select>

    <select id="getStatistics" resultType="com.yd.csf.service.vo.ExpectedFortuneStatisticsVO">
        select
            ifnull(sum(ef.hkd_amount),0) as totalExpectedAmount,
            ifnull(sum(ef.paid_amount),0) as totalPaidAmount,
            ifnull(sum(ef.unpaid_amount),0) as totalUnpaidAmount,
            ifnull(e.totalPremiumAmount,0) as totalPremiumAmount,
            count(distinct ef.policy_no) as totalPolicyCount
        from expected_fortune ef
        LEFT JOIN (
            SELECT p.policy_no, SUM(p.payment_premium*p.payment_term) as totalPremiumAmount
            FROM policy p
            GROUP BY policy_no
        ) e ON ef.policy_no = e.policy_no
        <where>
            <if test="expectedFortuneIds != null and expectedFortuneIds.size > 0">
                ef.id in
                <foreach collection="expectedFortuneIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>
