<?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="balance_amount_sum"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
       decode(${@deposit_positive_n},0,0,sum(t1.deposit - t1.deduction_amount - t1.refund_amount)) balance_amount_sum
  FROM (SELECT c.contract_id,
                c.contract_number,
               (SELECT m.bp_code
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_tenant) bp_code,
               (SELECT m.bp_name
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_tenant) bp_name,
               (SELECT m.bp_code
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_agent_level1) bp_agent_code,
               (SELECT m.bp_name
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_agent_level1) bp_agent_name,
               '客户保证金' deposit_distinguish,
               (select nvl(sum(nvl(o.write_off_due_amount,0)),0)
                  from csh_write_off o
                 where ccc.cashflow_id = o.cashflow_id
                   and o.journal_date <=to_date(${@write_off_date},'yyyy-mm-dd')) deposit,
               (SELECT nvl(SUM(nvl(o.write_off_due_amount,0)),0)
                     FROM csh_transaction t, csh_write_off o
                    WHERE o.cf_item = 52
                      AND o.cf_type = 5
                      AND t.transaction_id = o.csh_transaction_id
                      AND t.transaction_type = 'DEDUCTION'
                      AND o.contract_id = c.contract_id
                      AND o.times = ca.times
                      AND trunc(o.journal_date) <=to_date(${@write_off_date},'yyyy-mm-dd')) deduction_amount,
               (SELECT nvl(SUM(nvl(o.write_off_due_amount,0)),0)
                     FROM csh_write_off o
                    WHERE o.write_off_type = 'PAYMENT_DEBT'
                      AND o.cashflow_id = ca.cashflow_id
                      AND trunc(o.journal_date) <=to_date(${@write_off_date},'yyyy-mm-dd')
                      AND NOT EXISTS
                    (SELECT 1
                             FROM csh_transaction ct
                            WHERE ct.transaction_id = o.csh_transaction_id
                              AND ct.transaction_type = 'DEDUCTION')) refund_amount,
               c.contract_status,
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'CON500_CONTRACT_STATUS'
                   and v.code_value = c.contract_status) AS contract_status_n,
               c.lease_execution_date
          FROM con_contract          c,
               con_contract_cashflow ccc,
               con_contract_cashflow ca
         WHERE c.contract_id = ccc.contract_id
           AND ccc.cf_item = 51
           AND ccc.cf_type = 5
           AND ca.cf_item = 52
           AND ca.cf_type = 5
           AND ca.contract_id = ccc.contract_id
           AND c.data_class = 'NORMAL'
        UNION ALL
        SELECT c.contract_id,
                c.contract_number,
               (SELECT m.bp_code
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_tenant) bp_code,
               (SELECT m.bp_name
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_tenant) bp_name,
               (SELECT m.bp_code
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_agent_level1) bp_agent_code,
               (SELECT m.bp_name
                  FROM hls_bp_master m
                 WHERE m.bp_id = c.bp_id_agent_level1) bp_agent_name,
               '代理商代付客户保证金' deposit_distinguish,
               (select nvl(sum(nvl(o.write_off_due_amount,0)),0)
                  from csh_write_off o
                 where ccc.cashflow_id = o.cashflow_id
                   and o.journal_date <=to_date(${@write_off_date},'yyyy-mm-dd')) deposit,
               (SELECT nvl(SUM(nvl(o.write_off_due_amount,0)),0)
                     FROM csh_transaction t, csh_write_off o
                    WHERE o.cf_item = 509
                      AND o.cf_type = 5
                      AND t.transaction_id = o.csh_transaction_id
                      AND t.transaction_type = 'DEDUCTION'
                      AND o.contract_id = c.contract_id
                      AND o.times = ca.times
                      AND trunc(o.journal_date) <=to_date(${@write_off_date},'yyyy-mm-dd')) deduction_amount,
               (SELECT nvl(SUM(nvl(o.write_off_due_amount,0)),0)
                     FROM csh_write_off o
                    WHERE o.write_off_type = 'PAYMENT_DEBT'
                      AND o.cashflow_id = ca.cashflow_id
                      AND trunc(o.journal_date) <=to_date(${@write_off_date},'yyyy-mm-dd')
                      AND NOT EXISTS
                    (SELECT 1
                             FROM csh_transaction ct
                            WHERE ct.transaction_id = o.csh_transaction_id
                              AND ct.transaction_type = 'DEDUCTION'
                              )) refund_amount,
               c.contract_status,
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'CON500_CONTRACT_STATUS'
                   and v.code_value = c.contract_status) AS contract_status_n,
               c.lease_execution_date
          FROM con_contract          c,
               con_contract_cashflow ccc,
               con_contract_cashflow ca
         WHERE c.contract_id = ccc.contract_id
           AND ccc.cf_item = 508
           AND ccc.cf_type = 5
           and ca.cf_item = 509
           and ca.cf_type = 5
           and c.data_class = 'NORMAL'
           and ca.contract_id = ccc.contract_id) t1

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