<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_code"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_code"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="deposit_distinguish"/>
        <bm:field name="deposit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="deduction_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="refund_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="balance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="deposit_positive_n"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
       select    t1.*     from(SELECT t.*,
       (t.deposit - t.deduction_amount - t.refund_amount) balance_amount,
       decode((t.deposit - t.deduction_amount - t.refund_amount),0,0,1) deposit_positive_n
  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,
               c.et_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'
           and c.contract_status not in ('CANCEL','NEW')
        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,
               c.et_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 c.contract_status not in ('CANCEL','NEW')
           and ca.contract_id = ccc.contract_id) t)t1

                #WHERE_CLAUSE#
                 #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field field="deposit_distinguish" queryOperator="="/>
        <bm:query-field field="deposit_positive_n" queryOperator="="/>
        <bm:query-field field="contract_status_n" queryOperator="=" />
    </bm:query-fields>
</bm:model>
