<?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.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'))),
              0)
         ELSE
          0
       END) unpayment_amount,
       --应付未付首付款总额 --首付款关联不到就取核销日期而不是收款日期
       (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 = 2
              AND ccc.cf_status = 'RELEASE') -
          contract_item_query_pkg.calc_down_payment_write(p_contract_id => t.contract_id,
                                                          p_calc_date   => to_date(${@last_month_date},
                                                                                   'yyyy-mm-dd'),
                                                          p_user_id     => -1)

         ELSE
          0
       END) 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'))),
               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_calc_date_2 => last_day(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')))),
           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,
       --应付未付首付款总额
       (SELECT SUM(nvl(ccc.due_amount, 0))
          FROM con_contract_cashflow ccc
         WHERE ccc.contract_id = t.contract_id
           AND ccc.cf_item = 2
           AND ccc.cf_status = 'RELEASE') -
       contract_item_query_pkg.calc_down_payment_write(p_contract_id => t.contract_id,
                                                       p_calc_date   => last_day(to_date(${@start_calc_date},'yyyy-mm-dd')),
                                                       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(last_day(to_date(${@start_calc_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(last_day(to_date(${@start_calc_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(last_day(to_date(${@start_calc_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(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')))),
                         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 => last_day(to_date(${@start_calc_date},'yyyy-mm-dd')),
                                                       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')))),
            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,
       --逾期期数
       nvl2((SELECT MIN(ccc.due_date)
              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'
               AND trunc(ccc.due_date) <=
                   TRUNC(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')))
               AND ccc.due_amount >
                   nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_write_off cwo, csh_transaction ct
                        WHERE cwo.cashflow_id = ccc.cashflow_id
                          AND nvl(cwo.reversed_flag, 'N') = 'N'
                          AND cwo.csh_transaction_id = ct.transaction_id
                          AND trunc(ct.transaction_date) <=
                              TRUNC(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')))),
                       0)),
            ceil(months_between(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')),
                                (SELECT MIN(ccc.due_date)
                                   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'
                                    AND trunc(ccc.due_date) <=
                                        TRUNC(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')))
                                    AND ccc.due_amount >
                                        nvl((SELECT SUM(cwo.write_off_due_amount)
                                              FROM csh_write_off   cwo,
                                                   csh_transaction ct
                                             WHERE cwo.cashflow_id =
                                                   ccc.cashflow_id
                                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                                               AND cwo.csh_transaction_id =
                                                   ct.transaction_id
                                               AND trunc(ct.transaction_date) <=
                                                   TRUNC(last_day(to_date(${@start_calc_date},'yyyy-mm-dd')))),
                                            0)))),
            0) overdue_times_be,
          --保证金后逾期期数
          contract_item_query_pkg.calc_overdue_times_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)overdue_times_af
  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',
                             'PENDING',
                             'TERMINATING',
                             'ETING',
                             'REPURING')or (to_char(t.et_date,'yyyy-mm')>=${@period_name} and t.contract_status='TERMINATE' ))



                ]]>
            </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>
