<?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 v.monthly,
                        Sum(NVL(purchase_amount, 0)) total_purchase_amount,
                        Sum(NVL(down_payment, 0)) total_down_payment,
                        Sum(NVL(lease_amount, 0)) total_lease_amount,
                        (Sum(NVL(purchase_amount, 0)) + Sum(NVL(down_payment, 0)) +
                         Sum(NVL(lease_amount, 0))) total_amount,
                        Sum(NVL(outflow_amount, 0)) total_outflow_amount
                    From (Select to_char(t.calendar_date, 'yyyy/mm') monthly,
                              t.calendar_date,
                                 decode(to_char(t.calendar_date, 'd'),
                                        1,
                                        '日',
                                        2,
                                        '月',
                                        3,
                                        '火',
                                        4,
                                        '水',
                                        5,
                                        '木',
                                        6,
                                        '金',
                                        7,
                                        '土') named_day,
                                 (Select Sum(ccc.due_amount - nvl(ccc.received_amount, 0))
                                  From con_contract_cashflow ccc
                                  Where ccc.cf_item = 8
                                        And ccc.due_date = t.calendar_date
                                        And ccc.cf_status = 'RELEASE'
                                        And Exists
                                        (Select 1
                                         From con_contract ct
                                         Where ct.contract_status In
                                               ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                               And ct.contract_id = ccc.contract_id
                                               And ct.data_class = 'NORMAL')) purchase_amount, --留购金
                                 (Select Sum(due_amount - nvl(received_amount, 0))
                                  From con_contract_cashflow ccc
                                  Where ccc.cf_item = 2
                                        And ccc.due_date = t.calendar_date
                                        And ccc.cf_status = 'RELEASE'
                                        And Exists
                                        (Select 1
                                         From con_contract ct
                                         Where ct.contract_status In
                                               ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                               And ct.contract_id = ccc.contract_id
                                               And ct.data_class = 'NORMAL')) down_payment, --首付款
                                 (Select Sum(due_amount)
                                  From con_contract_cashflow ccc
                                  Where ccc.cf_item In (1, 200, 250, 11)
                                        And ccc.due_date = t.calendar_date
                                        And ccc.cf_status = 'RELEASE'
                                        And Exists
                                        (Select 1
                                         From con_contract ct
                                         Where ct.contract_status In
                                               ('INCEPT', 'PENDING', 'ETING', 'REPURING')
                                               And ct.contract_id = ccc.contract_id
                                               And ct.data_class = 'NORMAL')) lease_amount, --租金
                                 (Case
                                  When to_char(Sysdate, 'dd') <= 20 And
                                       to_char(t.calendar_date, 'yyyymmdd') =
                                       to_char(Sysdate, 'yyyymm') || 20 Then
                                      --当月的二十号统计数据
                                      NVL((Select Sum(nvl(c.lease_item_amount, 0))
                                           From con_contract c
                                           Where c.contract_status <> 'CANCEL'
                                                 And c.data_class = 'NORMAL'
                                                 And to_char(add_months(c.lease_start_date, 1),
                                                             'yyyymm') = to_char(Sysdate, 'yyyymm')
                                                 And nvl(c.payment_deduction, 'NO_DEDUCTION') =
                                                     'NO_DEDUCTION'
                                                 And nvl(c.secondary_lease, 'NO') = 'NO'),
                                          0) +
                                      NVL((Select Sum(nvl(c.lease_item_amount, 0) -
                                                      nvl(c.down_payment, 0))
                                           From con_contract c
                                           Where c.contract_status <> 'CANCEL'
                                                 And c.data_class = 'NORMAL'
                                                 And to_char(add_months(c.lease_start_date, 1),
                                                             'yyyymm') = to_char(Sysdate, 'yyyymm')
                                                 And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
                                                     'NO_DEDUCTION'
                                                 And nvl(c.secondary_lease, 'NO') = 'NO'),
                                          0)
                                  When to_char(Sysdate, 'dd') > 20 And
                                       to_char(add_months(t.calendar_date, -1), 'yyyymmdd') =
                                       to_char(Sysdate, 'yyyymm') || 20 Then
                                      NVL((Select Sum(nvl(c.lease_item_amount, 0))
                                           From con_contract c
                                           Where c.contract_status <> 'CANCEL'
                                                 And c.data_class = 'NORMAL'
                                                 And to_char(c.lease_start_date, 'yyyymm') =
                                                     to_char(Sysdate, 'yyyymm')
                                                 And nvl(c.payment_deduction, 'NO_DEDUCTION') =
                                                     'NO_DEDUCTION'
                                                 And nvl(c.secondary_lease, 'NO') = 'NO'),
                                          0) + NVL((Select Sum(nvl(c.lease_item_amount, 0) -
                                                               nvl(c.down_payment, 0))
                                                    From con_contract c
                                                    Where c.contract_status <> 'CANCEL'
                                                          And c.data_class = 'NORMAL'
                                                          And to_char(c.lease_start_date, 'yyyymm') =
                                                              to_char(Sysdate, 'yyyymm')
                                                          And nvl(c.payment_deduction, 'NO_DEDUCTION') <>
                                                              'NO_DEDUCTION'
                                                          And nvl(c.secondary_lease, 'NO') = 'NO'),
                                                   0)
                                  Else
                                      0
                                  End) outflow_amount
                          From (Select s.calendar_date
                                From fnd_calendar_details s
                                Where trunc(s.calendar_date) >= trunc(Sysdate)
                                      And trunc(s.calendar_date) <= Any
                                          (Select trunc(due_date)
                                           From con_contract_cashflow ccc
                                           Where ccc.cf_status = 'RELEASE'
                                                 And Exists
                                                 (Select 1
                                                  From con_contract t
                                                  Where t.contract_status In
                                                        ('INCEPT',
                                                         'PENDING',
                                                         'ETING',
                                                         'REPURING')
                                                        And t.contract_id = ccc.contract_id
                                                        And t.data_class = 'NORMAL'))
                                Order By s.calendar_date) t) v
                    Group By v.monthly
                    Order By monthly

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