<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="deposit_balance_amount_sum"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select sum(t1.deposit_received_amount - t1.deposit_deduction_amount - t1.deposit_refund_amount) deposit_balance_amount_sum
                from(
                select (SELECT m.bp_name
                      FROM hls_bp_master m
                     WHERE m.bp_id = ct.bp_id) AS agent_name,
                   (SELECT m.bp_code
                      FROM hls_bp_master m
                     WHERE m.bp_id = ct.bp_id) AS agent_code,
                     '保证金池' deposit_distinguish,
                     ct.transaction_date,
                     nvl((SELECT SUM(nvl(o.write_off_due_amount,0))
                     FROM csh_write_off o
                    WHERE o.csh_transaction_id = ct.transaction_id
                      AND o.write_off_type = 'RECEIPT_CREDIT'
                      AND trunc(o.write_off_date) <=to_date(${@write_off_date},'yyyy-mm-dd')),
                   0) deposit_deduction_amount,
                   nvl((SELECT SUM(nvl(o.write_off_due_amount,0))
                     FROM csh_write_off o
                    WHERE o.csh_transaction_id = ct.transaction_id
                      AND o.write_off_type = 'CSH_RETURN'
                      AND trunc(o.write_off_date) <=to_date(${@write_off_date},'yyyy-mm-dd')),
                   0) deposit_refund_amount,
                   nvl((select c.transaction_amount
                   from csh_transaction c
                   where c.transaction_id = ct.transaction_id
                    AND c.transaction_category = 'BUSINESS'
                   AND c.deposit_flag = 'Y'
                   AND c.write_off_flag != 'FULL'
                   AND c.returned_flag != 'FULL'
                   AND c.posted_flag = 'Y'
                   AND c.transaction_date <=to_date(${@write_off_date},'yyyy-mm-dd')),0) deposit_received_amount
                    from csh_transaction ct
                    WHERE ct.company_id = 1
                   AND ct.transaction_category = 'BUSINESS'
                   AND ct.deposit_flag = 'Y'
                   AND ct.write_off_flag != 'FULL'
                   AND ct.returned_flag != 'FULL'
                   AND ct.posted_flag = 'Y') t1

                #WHERE_CLAUSE#
                 #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="agent_code" queryExpression="t1.agent_code like &apos;%&apos; || upper(${@agent_code}) || &apos;%&apos;"/>
        <bm:query-field name="agent_name" queryExpression="t1.agent_name like &apos;%&apos; || ${@agent_name} || &apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
