<?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 t.contract_id,
       t.contract_number,
        (SELECT  h.extra_nam
          FROM hls_bp_master h
          WHERE h.bp_id=t.bp_id_agent_level1) extra_nam,
        (SELECT  h.bp_name
          FROM hls_bp_master h
          WHERE h.bp_id=t.bp_id_tenant) tenant_name,
       --租赁开始日
       t.lease_start_date,
       --机型
       ccli.modelcd,
       --机号
       ccli.machine_number,
       --回数
       decode(ccc.cf_item,8,'留购价格',ccc.times) times,
       --支付预定日
       ccc.due_date,
       --应收款金额
       (ccc.due_amount -
       nvl((SELECT SUM(cwo.write_off_due_amount)
              FROM csh_transaction t, csh_write_off cwo
             WHERE cwo.cashflow_id = ccc.cashflow_id
               AND t.transaction_id = cwo.csh_transaction_id
               AND nvl(cwo.reversed_flag, 'N') = 'N'
               AND trunc(t.transaction_date) <
                   trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
            0)) net_amount,
       --当月已收款金额
       nvl((SELECT SUM(cwo.write_off_due_amount)
             FROM csh_transaction t, csh_write_off cwo
            WHERE cwo.cashflow_id = ccc.cashflow_id
              AND t.transaction_id = cwo.csh_transaction_id
              AND nvl(cwo.reversed_flag, 'N') = 'N'
              AND to_char(t.transaction_date, 'yyyy-mm') = ${@period_name}),
           0) write_off_amount,
       --剩余金额
       (ccc.due_amount -
       nvl((SELECT SUM(cwo.write_off_due_amount)
              FROM csh_transaction t, csh_write_off cwo
             WHERE cwo.cashflow_id = ccc.cashflow_id
               AND t.transaction_id = cwo.csh_transaction_id
               AND nvl(cwo.reversed_flag, 'N') = 'N'
               AND trunc(t.transaction_date) <=
                   trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
            0)) unrecived_amount,
       --逾期天数
       (CASE
         WHEN (ccc.due_amount -
              nvl((SELECT SUM(cwo.write_off_due_amount)
                     FROM csh_transaction t, csh_write_off cwo
                    WHERE cwo.cashflow_id = ccc.cashflow_id
                      AND t.transaction_id = cwo.csh_transaction_id
                      AND nvl(cwo.reversed_flag, 'N') = 'N'
                      AND trunc(t.transaction_date) <=
                          trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                   0)) > 0 THEN
          floor(to_date(${@last_month_date}, 'yyyy-mm-dd') - ccc.due_date)
         ELSE
          0
       END) overdue_days,
       NVL((select sum((nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
             nvl(tt.returned_amount, 0)))
         from csh_transaction tt
         where tt.transaction_type = 'DEPOSIT'
           AND tt.deposit_trans_type = 'rent_deposit'
           AND NVL(tt.reversed_flag,'N')='N'
          and tt.ref_contract_id = T.contract_id),0) deposit_amount
  FROM con_contract_cashflow   ccc,
       con_contract            t,
       con_contract_lease_item ccli
 WHERE ccc.contract_id = t.contract_id
   AND t.contract_id = ccli.contract_id
   AND ccc.cf_item IN (1, 8, 200, 250)
   AND ccc.cf_status = 'RELEASE'
   AND t.data_class = 'NORMAL'
   AND trunc(ccc.due_date) <=
       trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
   AND ((trunc(t.lease_start_date) <=
       trunc(to_date(${@last_month_date}, 'yyyy-mm-dd')) AND
       t.contract_status = 'INCEPT') OR
       (to_char(t.et_date, 'yyyy-mm') >= ${@period_name} AND
       t.contract_status = 'TERMINATE'))
   AND ccc.due_amount >
       nvl((SELECT SUM(cwo.write_off_due_amount)
             FROM csh_transaction t, csh_write_off cwo
            WHERE cwo.cashflow_id = ccc.cashflow_id
              AND t.transaction_id = cwo.csh_transaction_id
              AND nvl(cwo.reversed_flag, 'N') = 'N'
              AND trunc(t.transaction_date) <
                  trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
           0)
                ]]>
            </bm:query-sql>
        </bm:operation>

    </bm:operations>
    <bm:query-fields>
<!--        <bm:query-field name="period_name" queryExpression="t1.period_day=${@period_name}"/>-->
<!--        <bm:query-field name="journal_date_to" queryExpression="to_char(t.journal_date,&apos;yyyy-mm-dd&apos;) &lt;= ${@journal_date_to}"/>-->
<!--        <bm:query-field name="bp_id_agent_level1" queryExpression="t.bp_id_agent_level1=${@bp_id_agent_level1}"/>-->
    </bm:query-fields>
</bm:model>
