<?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(due_amount - nvl(received_amount, 0))
                  FROM con_contract_cashflow
                 WHERE cf_item = 8
                   AND due_date = t.calendar_date
                   AND EXISTS
                 (SELECT 1
                          FROM con_contract ct
                         WHERE ct.contract_status IN
                               ('INCEPT', 'PENDING','ETING', 'REPURING')
                           AND ct.contract_id = contract_id)) purchase_amount, --留购金
               (SELECT SUM(due_amount - nvl(received_amount, 0))
                  FROM con_contract_cashflow
                 WHERE cf_item = 2
                   AND due_date = t.calendar_date
                   AND EXISTS
                 (SELECT 1
                          FROM con_contract ct
                         WHERE ct.contract_status IN
                                 ('INCEPT', 'PENDING','ETING', 'REPURING')
                           AND ct.contract_id = contract_id)) down_payment, --首付款
               (SELECT SUM(due_amount)
                  FROM con_contract_cashflow ccc
                 WHERE cf_item = 1
                   AND due_date = t.calendar_date
                   AND EXISTS
                 (SELECT 1
                          FROM con_contract ct
                         WHERE ct.contract_status IN
                              ('INCEPT', 'PENDING','ETING', 'REPURING')
                           AND ct.contract_id = contract_id)) 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'),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'),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'),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'),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>
