<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
             SELECT h.bp_code,
       h.bp_name bp_id_tenant_n,
       --合同编号
       t.contract_number,
       t.contract_id,
       to_char(nvl(((SELECT SUM(nvl(ccc.principal, 0))
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 1) +
                   (SELECT nvl(ccc.due_amount, 0)
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 2)),
                   0),
               'FM999,999,999,999,990.00') total_principal,
       to_char(nvl(((SELECT SUM(nvl(ccc.interest, 0))
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 1) +
                   (SELECT nvl(ccc.due_amount, 0)
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 8)),
                   0),
               'FM999,999,999,999,990.00') total_interest,
       '0.00' total_other,
       to_char(nvl(((SELECT SUM(nvl(ccc.principal, 0))
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 1) +
                   (SELECT nvl(ccc.due_amount, 0)
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 2) +
                   (SELECT SUM(nvl(ccc.interest, 0))
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 1) +
                   (SELECT nvl(ccc.due_amount, 0)
                       FROM con_contract_cashflow ccc
                      WHERE ccc.contract_id = t.contract_id
                        AND ccc.cf_item = 8)),
                   0),
               'FM999,999,999,999,990.00') total_amount,
       --合同开始日
       t.lease_start_date,
       (SELECT ccc.due_date
          FROM con_contract_cashflow ccc
         WHERE ccc.cf_item=1 and ccc.contract_id=t.contract_id and  ccc.times=(
            select max(times) from con_contract_cashflow cc where cc.contract_id=t.contract_id
         )) lease_end_date,
       --当期利息
       to_char(nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          ${@due_date_from} AND ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_interest,
       --当次累计利息
       to_char(nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          to_char(t.lease_start_date, 'yyyy-mm-dd') AND
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_add_interest,

       --设备本金
       to_char(nvl((SELECT SUM(nvl(ccc.principal, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          ${@due_date_from} AND ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_principal,
       --累计本金
       to_char(nvl((SELECT SUM(nvl(ccc.principal, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          to_char(t.lease_start_date, 'yyyy-mm-dd') AND
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_add_principal,
       --当次违约金
       to_char(nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 9
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          ${@due_date_from} AND ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_penalty,
       --累计违约金
       to_char(nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 9
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          to_char(t.lease_start_date, 'yyyy-mm-dd') AND
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_add_penalty,
       --当期累计
       to_char(nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item IN (1, 9)
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          ${@due_date_from} AND ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_total,
       --累计之和
       to_char(nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item IN (1, 9)
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') BETWEEN
                          to_char(t.lease_start_date, 'yyyy-mm-dd') AND
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') this_add_total,

       --未执行利息
       '0.00' unexecute_interest,
       --未来应收利息
       to_char(nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') >
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') future_interest,
       --未执行本金
       '0.00' unexecute_principal,
       --未来本金
       to_char(nvl((SELECT SUM(nvl(ccc.principal, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') >
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') future_principal,
       '0.00' unexecute_total,
       --未来应收
       '0.00' this_due_amount,
       to_char(nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id = t.contract_id
                      AND cf_item = 1
                      AND to_char(ccc.due_date, 'yyyy-mm-dd') >
                          ${@due_date_to}),
                   0),
               'FM999,999,999,999,990.00') future_due_amount
  FROM con_contract t, hls_bp_master h
 WHERE t.data_class = 'NORMAL'
    and t.contract_status='INCEPT'
    AND h.bp_id = t.bp_id_tenant

                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
