<?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 to_char(v.calendar_date, 'yyyy/mm/dd') calendar_date,
                        v.named_day,
                        v.purchase_amount,
                        v.down_payment,
                        v.lease_amount,
                           nvl(v.purchase_amount, 0) + nvl(down_payment, 0) +
                           nvl(lease_amount, 0) total_amount,
                        outflow_amount
                    From (Select 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(ccc.due_amount - nvl(ccc.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(ccc.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') ||
                                    decode(to_char(to_date(to_char(Sysdate, 'yyyymm') || 20,
                                                           'yyyymmdd'),
                                                   'd'),
                                           1,
                                           18,
                                           7,
                                           19,
                                           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(t.calendar_date, 'yyyymmdd') =
                                    decode(to_char(to_date((to_char(add_months(Sysdate, 1),
                                                                    'yyyymm') || 20),
                                                           'yyyymmdd'),
                                                   'd'),
                                           1,
                                           to_char(add_months(Sysdate, 1), 'yyyymm') || 18,
                                           7,
                                           to_char(add_months(Sysdate, 1), 'yyyymm') || 19,
                                           to_char(add_months(Sysdate, 1), '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
                                   Null
                               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

                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            	begin
            		contract_item_query_pkg.send_excel_mail(p_file_name => ${@file_name},
                                                            p_file_path => ${@file_path} ,
                                                            p_file_size => ${@file_size},
                                                            p_user_id   => -1
                                                            );
            	end;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>