<?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,
                    t.is_buyout,
                    (Select v1.code_value_name
                     From sys_code_values_v v1
                     Where v1.code = 'YES_OR_NO'
                           And v1.code_enabled_flag = 'Y'
                           And v1.code_value_enabled_flag = 'Y'
                           and v1.CODE_VALUE = t.is_buyout)                is_buyout_n
                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>
