<?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 * from(
                SELECT
                    h.extra_nam,
                    --合同编号
                    t.contract_number,
                    --应付未付租金总额
                    (CASE
                     WHEN to_char(t.lease_start_date, 'yyyy-mm') <> ${@period_name}
                         THEN
                             (SELECT SUM(nvl(ccc.due_amount, 0))
                              FROM con_contract_cashflow ccc
                              WHERE ccc.contract_id = t.contract_id
                                    AND ccc.cf_item IN (1, 200, 250)
                                    AND ccc.cf_status = 'RELEASE') -
                             nvl((SELECT SUM(cwo.write_off_due_amount)
                                  FROM csh_write_off cwo, csh_transaction ct
                                  WHERE cwo.contract_id = t.contract_id
                                        AND cwo.csh_transaction_id = ct.transaction_id
                                        AND cwo.cf_item IN (1, 200, 250)
                                        AND nvl(cwo.reversed_flag, 'N') = 'N'
                                        AND trunc(ct.transaction_date) <=
                                            trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
                                        and exists(select 1
                                                   from con_contract_cashflow cc
                                                   where cc.CASHFLOW_ID = cwo.CASHFLOW_ID
                                                         and cc.CF_STATUS = 'RELEASE')),
                                 0)
                     ELSE
                         0
                     END)                                                                unpayment_amount,
                    --应付未付首付款总额 --首付款关联不到就取核销日期而不是收款日期
                    contract_item_query_pkg.calc_down_payment_balance(p_contract_id=>t.contract_id,
                                                                      p_calc_date  =>to_date(${@last_month_date},
                                                                                             'yyyy-mm-dd'),
                                                                      p_calc_flag  =>'START',
                                                                      p_user_id    =>-1) undown_payment,
                    --应付未付留购金总额
                    (CASE
                     WHEN to_char(t.lease_start_date, 'yyyy-mm') <> ${@period_name}
                         THEN
                             (SELECT SUM(nvl(ccc.due_amount, 0))
                              FROM con_contract_cashflow ccc
                              WHERE ccc.contract_id = t.contract_id
                                    AND ccc.cf_item = 8
                                    AND ccc.cf_status = 'RELEASE') -
                             nvl((SELECT SUM(cwo.write_off_due_amount)
                                  FROM csh_write_off cwo, csh_transaction ct
                                  WHERE cwo.contract_id = t.contract_id
                                        AND cwo.cf_item = 8
                                        AND cwo.csh_transaction_id = ct.transaction_id
                                        AND nvl(cwo.reversed_flag, 'N') = 'N'
                                        AND trunc(ct.transaction_date) <=
                                            trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                                 0)
                     ELSE
                         0
                     END)                                                                unpurchase_amount,
                    --逾期租金
                    (CASE
                     WHEN to_char(t.lease_start_date, 'yyyy-mm') <> ${@period_name} AND
                          (nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                                FROM con_contract_cashflow ccc
                                WHERE ccc.contract_id = t.contract_id
                                      AND ccc.cf_item IN (1, 200, 250)
                                      AND trunc(ccc.due_date) <=
                                          trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
                                      AND ccc.cf_status = 'RELEASE'),
                               0) -
                           nvl((SELECT SUM(cwo.write_off_due_amount)
                                FROM csh_write_off cwo, csh_transaction ct
                                WHERE cwo.contract_id = t.contract_id
                                      AND cwo.cf_item IN (1, 200, 250)
                                      AND cwo.csh_transaction_id = ct.transaction_id
                                      AND nvl(cwo.reversed_flag, 'N') = 'N'
                                      AND trunc(ct.transaction_date) <=
                                          trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                               0)) > 0
                         THEN
                             (nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                                   FROM con_contract_cashflow ccc
                                   WHERE ccc.contract_id = t.contract_id
                                         AND ccc.cf_item IN (1, 200, 250)
                                         AND trunc(ccc.due_date) <=
                                             trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
                                         AND ccc.cf_status = 'RELEASE'),
                                  0) - nvl((SELECT SUM(cwo.write_off_due_amount)
                                            FROM csh_write_off cwo, csh_transaction ct
                                            WHERE cwo.contract_id = t.contract_id
                                                  AND cwo.cf_item IN (1, 200, 250)
                                                  AND cwo.csh_transaction_id = ct.transaction_id
                                                  AND nvl(cwo.reversed_flag, 'N') = 'N'
                                                  AND trunc(ct.transaction_date) <=
                                                      trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                                           0))
                     ELSE
                         0
                     END)                                                                overdue_amount,
                    --逾期违约金
                    (CASE
                     WHEN to_char(t.lease_start_date, 'yyyy-mm') <> ${@period_name}
                         THEN
                             (contract_item_query_pkg.calc_penalty_by_contract(p_contract_id => t.contract_id,
                                                                               p_calc_date   => to_date(
                                                                                       ${@start_calc_date},
                                                                                       'yyyy-mm-dd'),
                                                                               p_user_id     => 1) -
                              nvl((SELECT SUM(cwo.write_off_due_amount)
                                   FROM csh_write_off cwo, csh_transaction ct
                                   WHERE cwo.contract_id = t.contract_id
                                         AND nvl(cwo.reversed_flag, 'N') = 'N'
                                         AND cwo.cf_item = 9
                                         AND cwo.csh_transaction_id = ct.transaction_id
                                         AND trunc(ct.transaction_date) <=
                                             trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
                                         And Exists(Select 1
                                                    From con_contract_cashflow cc
                                                    Where cc.cashflow_id = cwo.cashflow_id
                                                          And cc.cf_status = 'RELEASE'
                                                          And cc.cf_direction <> 'NONCASH')),
                                  0))
                     ELSE
                         0
                     END)                                                                overdue_penalty,
                    --当月新增租金
                    (CASE
                     WHEN trunc(t.lease_start_date) BETWEEN
                     trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd')) AND
                     TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                         THEN
                             (SELECT SUM(ccc.due_amount)
                              FROM con_contract_cashflow ccc
                              WHERE ccc.cf_item IN (1, 200, 250)
                                    AND ccc.cf_status = 'RELEASE'
                                    AND ccc.contract_id = t.contract_id)
                     ELSE
                         0
                     END)                                                                monthly_amount,
                    --当月新增首付款
                    (CASE
                     WHEN trunc(t.lease_start_date) BETWEEN
                     trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd')) AND
                     TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                         THEN
                             (SELECT SUM(ccc.due_amount)
                              FROM con_contract_cashflow ccc
                              WHERE ccc.cf_item = 2
                                    AND ccc.cf_status = 'RELEASE'
                                    AND ccc.contract_id = t.contract_id)
                     ELSE
                         0
                     END)                                                                monthly_downpayment,
                    --当月新增留购款
                    (CASE
                     WHEN trunc(t.lease_start_date) BETWEEN
                     trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd')) AND
                     TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                         THEN
                             (SELECT SUM(ccc.due_amount)
                              FROM con_contract_cashflow ccc
                              WHERE ccc.cf_item = 8
                                    AND ccc.cf_status = 'RELEASE'
                                    AND ccc.contract_id = t.contract_id)
                     ELSE
                         0
                     END)                                                                monthly_purchase_amount,
                    --当月回租金
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                         WHERE cwo.contract_id = t.contract_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND cwo.cf_item IN (1, 200, 250)
                               AND cwo.csh_transaction_id = ct.transaction_id
                               AND trunc(ct.transaction_date) <=
                                   TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                               AND trunc(ct.transaction_date) >=
                                   TRUNC(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
                        0)                                                               write_off_amount,
                    --当月回收首付款

                    contract_item_query_pkg.calc_down_payment_write(p_contract_id=> t.contract_id,
                                                                    p_calc_date   => to_date(${@start_calc_date},
                                                                                             'yyyy-mm-dd'),
                                                                    p_user_id    => -1)  write_off_downpayment,

                    --回收留购金
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                         WHERE cwo.contract_id = t.contract_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND cwo.cf_item = 8
                               AND cwo.csh_transaction_id = ct.transaction_id
                               AND trunc(ct.transaction_date) <=
                                   TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                               AND trunc(ct.transaction_date) >=
                                   trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
                        0)                                                               write_off_purchase_amount,
                    --回收违约金
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                         WHERE cwo.contract_id = t.contract_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND cwo.cf_item = 9
                               AND cwo.csh_transaction_id = ct.transaction_id
                               AND trunc(ct.transaction_date) <=
                                   TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                               AND trunc(ct.transaction_date) >=
                                   trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
                        0)                                                               write_off_penalty,
                    --期末数据
                    (SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                     WHERE ccc.contract_id = t.contract_id
                           AND ccc.cf_item IN (1, 200, 250)
                           AND ccc.cf_status = 'RELEASE') -
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                         WHERE cwo.contract_id = t.contract_id
                               AND cwo.cf_item IN (1, 200, 250)
                               AND cwo.csh_transaction_id = ct.transaction_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND trunc(ct.transaction_date) <=
                                   TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                               and exists(select 1
                                          from con_contract_cashflow cc
                                          where cc.CASHFLOW_ID = cwo.CASHFLOW_ID
                                                and cc.CF_STATUS = 'RELEASE')
                        ),
                        0)                                                               rest_unpayment_amount,
                    --保证金后
                    contract_item_query_pkg.calc_rest_rent_by_dudt(p_contract_id => t.contract_id,
                                                                   p_calc_date => last_day(
                                                                           to_date(${@start_calc_date}, 'yyyy-mm-dd')),
                                                                   p_user_id => -1)      rest_unpayment_amount_af,

                    contract_item_query_pkg.calc_down_payment_balance(p_contract_id=>t.contract_id,
                                                                      p_calc_date  =>last_day(
                                                                              to_date(${@start_calc_date},
                                                                                      'yyyy-mm-dd')),
                                                                      p_calc_flag  =>'END',
                                                                      p_user_id    =>-1) rest_undown_payment,
                    --应付未付留购金总额
                    (SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                     WHERE ccc.contract_id = t.contract_id
                           AND ccc.cf_item = 8
                           AND ccc.cf_status = 'RELEASE') -
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                         WHERE cwo.contract_id = t.contract_id
                               AND cwo.cf_item = 8
                               AND cwo.csh_transaction_id = ct.transaction_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND trunc(ct.transaction_date) <=
                                   TRUNC(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))),
                        0)                                                               rest_unpurchase_amount,
                    --逾期租金
                    (CASE
                     WHEN (nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                                FROM con_contract_cashflow ccc
                                WHERE ccc.contract_id = t.contract_id
                                      AND ccc.cf_item IN (1, 200, 250)
                                      AND trunc(ccc.due_date) <=
                                          trunc(comparedate(
                                                        p_date1 => last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')),
                                                        p_date2 =>sysdate,
                                                        p_type =>'SMALL'))
                                      AND ccc.cf_status = 'RELEASE'),
                               0) -
                           nvl((SELECT SUM(cwo.write_off_due_amount)
                                FROM csh_write_off cwo, csh_transaction ct
                                WHERE cwo.contract_id = t.contract_id
                                      AND cwo.cf_item IN (1, 200, 250)
                                      AND cwo.csh_transaction_id = ct.transaction_id
                                      AND nvl(cwo.reversed_flag, 'N') = 'N'
                                      AND trunc(ct.transaction_date) <=
                                          trunc(comparedate(
                                                        p_date1 => last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')),
                                                        p_date2 =>sysdate,
                                                        p_type =>'SMALL'))),
                               0)) > 0
                         THEN
                             (nvl((SELECT SUM(nvl(ccc.due_amount, 0))
                                   FROM con_contract_cashflow ccc
                                   WHERE ccc.contract_id = t.contract_id
                                         AND ccc.cf_item IN (1, 200, 250)
                                         AND trunc(ccc.due_date) <=
                                             trunc(comparedate(p_date1 => last_day(
                                                     to_date(${@start_calc_date}, 'yyyy-mm-dd')),
                                                               p_date2 =>sysdate,
                                                               p_type =>'SMALL'))
                                         AND ccc.cf_status = 'RELEASE'),
                                  0) - nvl((SELECT SUM(cwo.write_off_due_amount)
                                            FROM csh_write_off cwo, csh_transaction ct
                                            WHERE cwo.contract_id = t.contract_id
                                                  AND cwo.cf_item IN (1, 200, 250)
                                                  AND cwo.csh_transaction_id = ct.transaction_id
                                                  AND nvl(cwo.reversed_flag, 'N') = 'N'
                                                  AND trunc(ct.transaction_date) <=
                                                      trunc(comparedate(p_date1 => last_day(
                                                              to_date(${@start_calc_date}, 'yyyy-mm-dd')),
                                                                        p_date2 =>sysdate,
                                                                        p_type =>'SMALL'))),
                                           0))
                     ELSE
                         0
                     END)                                                                rest_overdue_amount,
                    contract_item_query_pkg.calc_overdue_amount_by_dudt(p_contract_id => t.contract_id,
                                                                        p_calc_date =>comparedate(p_date1 => last_day(
                                                                                to_date(${@start_calc_date},
                                                                                        'yyyy-mm-dd')),
                                                                                                  p_date2 =>sysdate,
                                                                                                  p_type =>'SMALL'),
                                                                        p_user_id => -1) rest_overdue_amount_af,
                    --逾期违约金
                    (contract_item_query_pkg.calc_penalty_by_contract(p_contract_id => t.contract_id,
                                                                      p_calc_date   =>last_day(to_date(
                                                                                                       ${@start_calc_date},
                                                                                                       'yyyy-mm-dd')) +
                                                                                      1,
                                                                      p_user_id     => 1) -
                     nvl((SELECT SUM(cwo.write_off_due_amount)
                          FROM csh_write_off cwo, csh_transaction ct
                          WHERE cwo.contract_id = t.contract_id
                                AND nvl(cwo.reversed_flag, 'N') = 'N'
                                AND cwo.cf_item = 9
                                AND cwo.csh_transaction_id = ct.transaction_id
                                AND trunc(ct.transaction_date) <=
                                    trunc(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                                And Exists(Select 1
                                           From con_contract_cashflow cc
                                           Where cc.cashflow_id = cwo.cashflow_id
                                                 And cc.cf_status = 'RELEASE'
                                                 And cc.cf_direction <> 'NONCASH')),
                         0))                                                             rest_overdue_penalty,
                    --次月应收租金
                    nvl((SELECT SUM(nvl(due_amount, 0))
                         FROM con_contract_cashflow ccc
                         WHERE ccc.contract_id = t.contract_id
                               AND to_char(ccc.due_date, 'yyyy-mm') =
                                   to_char(add_months(to_date(${@start_calc_date}, 'yyyy-mm-dd'), 1), 'yyyy-mm')
                               AND ccc.cf_item IN (1, 200, 250)
                               AND ccc.cf_status = 'RELEASE'),
                        0)                                                               next_due_amount,
                    t.lease_start_date,
                    (SELECT c.bp_name
                     FROM hls_bp_master c
                     WHERE c.bp_id = t.bp_id_tenant)                                     tenant_name,

                    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,
                    --逾期期数
                    contract_item_query_pkg.calc_business_overdue_times(p_contract_id =>t.contract_id,
                                                                        p_calc_date => comparedate(p_date1 => last_day(
                                                                                to_date(${@start_calc_date},
                                                                                        'yyyy-mm-dd')),
                                                                                                   p_date2 =>sysdate,
                                                                                                   p_type =>'SMALL'),
                                                                        p_user_id => -1) overdue_times_be,
                    --保证金后逾期期数
                    contract_item_query_pkg.calc_overdue_times_by_dudt(p_contract_id =>t.contract_id,
                                                                       p_calc_date =>comparedate(p_date1 => last_day(
                                                                               to_date(${@start_calc_date},
                                                                                       'yyyy-mm-dd')),
                                                                                                 p_date2 =>sysdate,
                                                                                                 p_type =>'SMALL'),
                                                                       p_user_id => -1)  overdue_times_af,
                    (select v.CODE_VALUE_NAME
                     from SYS_CODE_VALUES_V v
                     where v.CODE = 'CON500_CONTRACT_STATUS'
                           and v.CODE_VALUE = t.CONTRACT_STATUS
                           and v.CODE_ENABLED_FLAG = 'Y'
                           and v.CODE_VALUE_ENABLED_FLAG = 'Y')                          contract_status_n,
                    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,
                              (select sh.large_balance from prj_quotation sh where sh.DOCUMENT_ID = t.project_id) large_balance,
                              (select v.code_value_name
                                from sys_code_values_v v
                               where v.code = 'SECONDARY_LEASE'
                                 and v.code_value = (select sh.large_balance from prj_quotation sh where sh.DOCUMENT_ID = t.project_id)) large_balance_n,
                                 t.branch_code,
                               (Select v.code_value_name
                              From sys_code_values_v v
                             Where v.code = 'BRANCH_CODE_SZMP'
                               And v.code_enabled_flag = 'Y'
                               And v.code_value_enabled_flag = 'Y'
                               And v.code_value = t.branch_code) branch_code_n
                FROM con_contract t, hls_bp_master h
                WHERE t.data_class = 'NORMAL'
                      AND t.bp_id_agent_level1 = h.bp_id
                      AND (t.contract_status IN ('INCEPT',
                                                 'TERMINATING',
                                                 'ETING',
                                                 'REPURING') or
                           (to_char(t.et_date, 'yyyy-mm') >= ${@period_name} and t.contract_status in ('TERMINATE',
                                                                                                       'ET')))
                      and trunc(t.lease_start_date) <= trunc(last_day(to_date(${@start_calc_date}, 'yyyy-mm-dd')))
                ) t
                #WHERE_CLAUSE#
                ]]>
            </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-field name="contract_number" queryExpression="t.contract_number=${@contract_number}"/>
                <bm:query-field name="rest_unpayment_amount_from" queryExpression="t.rest_unpayment_amount &gt;=${@rest_unpayment_amount_from}"/>
                <bm:query-field name="rest_unpayment_amount_to" queryExpression="t.rest_unpayment_amount &lt;=${@rest_unpayment_amount_to}"/>
    </bm:query-fields>
</bm:model>
