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

    <select id="page"
            resultType="com.yd.csf.feign.response.premiumreconciliation.ApiPremiumReconciliationPageResponse">
        SELECT
            MAX(p.insurance_company) AS insuranceCompany,
            pr.policy_no,
            MAX(pf.status) AS policyFollowStatus,
            MAX(pr.reconciliation_status) AS reconciliation_status,
            MAX(p.status) AS policyStatus,
            MAX(pr.remaining_unpaid_amount) AS remaining_unpaid_amount,
            MAX(pr.remaining_unpaid_currency) AS remaining_unpaid_currency,
            COALESCE(SUM(prm.payment_amount), 0) AS paymentAmount,
            MAX(prm.payment_currency) AS paymentCurrency,
            GROUP_CONCAT(DISTINCT prm.payment_method SEPARATOR ';') AS paymentMethod,
            MAX(pr.recognized_amount) AS recognized_amount,
            MAX(pr.recognized_currency) AS recognized_currency,
            GROUP_CONCAT(DISTINCT prm.payer SEPARATOR ';') AS payer,
            GROUP_CONCAT(DISTINCT prm.paying_bank SEPARATOR ';') AS paying_bank,
            MAX(p.policy_holder) AS policy_holder,
            MAX(p.insured) AS insured,
            GROUP_CONCAT(DISTINCT pb.broker_name SEPARATOR ';') AS brokerName,
            MAX(pr.create_time) AS create_time,
            MAX(pr.update_time) AS update_time,
            MAX(p.reconciliation_company) AS reconciliation_company
        FROM premium_reconciliation pr
        LEFT JOIN policy p ON p.policy_no = pr.policy_no AND p.is_deleted = 0
        LEFT JOIN policy_follow pf ON pf.policy_no = pr.policy_no AND pf.is_deleted = 0
        LEFT JOIN premium_remittance prm ON prm.premium_reconciliation_biz_id = pr.premium_reconciliation_biz_id AND prm.is_deleted = 0
        LEFT JOIN policy_broker pb ON pb.policy_no = pr.policy_no AND pb.is_deleted = 0
        WHERE pr.is_deleted = 0
        GROUP BY pr.premium_reconciliation_biz_id, pr.policy_no
        ORDER BY MAX(pr.create_time) DESC
    </select>
</mapper>