<?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 to_char(cw.times) times,
                 to_char(cw.due_date, 'yyyy-mm-dd') due_date,
                 to_char(cw.contract_id) contract_id,
                 to_char(cw.due_amount,'FM999,999,999,990.00')due_amount,
                 --to_char(nvl(cw.vat_due_amount, 0),'FM999,999,999,990.00') vat_due_amount,
                 to_char(cw.cf_item) cf_item,
                 --租金增值税
                 to_char(NVL(cw.vat_due_amount,0),'FM999,999,999,990.00')  vat_due_amount,
                 to_char(NVL(cw.principal,0),'FM999,999,999,990.00')  principal,
                 to_char(NVL(cw.vat_principal,0),'FM999,999,999,990.00') vat_principal,


                 --上下端利息
                 to_char(nvl((SELECT cu.interest
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 2),
                     0),'FM999,999,999,990.00') up_interest, --上端
                 to_char(nvl((SELECT cu.interest
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 1),
                     0),'FM999,999,999,990.00') down_interest, --下端
                 to_char((SELECT nvl(SUM(cu.interest), 0)
                    FROM con_unearned_finance_income cu
                   WHERE cu.cashflow_id = cw.cashflow_id
                     AND cu.contract_id = cw.contract_id),'FM999,999,999,990.00') total_interest,
                 --上下段税利息
                 to_char(nvl((SELECT cu.vat_interest
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 2),
                     0),'FM999,999,999,990.00') up_vat_interest, --上端
                 to_char(nvl((SELECT cu.vat_interest
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 1),
                     0),'FM999,999,999,990.00') down_vat_interest, --下端
                 to_char((SELECT nvl(SUM(cu.vat_interest), 0)
                    FROM con_unearned_finance_income cu
                   WHERE cu.cashflow_id = cw.cashflow_id
                     AND cu.contract_id = cw.contract_id),'FM999,999,999,990.00') total_vat_interest,
                 --不含税利息
                 to_char(
                 nvl((SELECT cu.unearned_finance_income
                       FROM con_unearned_finance_income cu

                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 2),
                     0),'FM999,999,999,990.00') up_net_interest,
                 to_char(nvl((SELECT cu.unearned_finance_income
                       FROM con_unearned_finance_income cu

                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 1),
                     0),'FM999,999,999,990.00') down_net_interest,
                 to_char((nvl((SELECT cu.unearned_finance_income
                        FROM con_unearned_finance_income cu
                       WHERE cu.cashflow_id = cw.cashflow_id
                         AND cu.contract_id = cw.contract_id
                         AND cu.seq_in_period = 2),
                      0) + nvl((SELECT cu.unearned_finance_income
                                  FROM con_unearned_finance_income cu
                                 WHERE cu.cashflow_id = cw.cashflow_id
                                   AND cu.contract_id = cw.contract_id
                                   AND cu.seq_in_period = 1),
                                0)

                 ),'FM999,999,999,990.00') total_net_interest,
                 --本期天数
                 nvl((SELECT cu.weight_factor
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 2),
                     0) up_weight_factor,
                 nvl((SELECT cu.weight_factor
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 1),
                     0) down_weight_factor,
                 nvl((SELECT cu.total_weight
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id
                        AND cu.seq_in_period = 1),
                     0) total_weight_factor

            FROM con_contract ct, con_contract_cashflow cw
           WHERE ct.contract_id = cw.contract_id
             AND cf_item <> 0
             AND ct.contract_id = ${/parameter/@contract_id}
          UNION ALL

          SELECT '' times,
                 '合计' due_date,
                 '' contract_id,
                 to_char(nvl(SUM(cw.due_amount), 0),'FM999,999,999,990.00') due_amount,
               --  to_char(SUM(nvl(cw.vat_due_amount, 0)),'FM999,999,999,990.00') vat_due_amount,
                 '' cf_item,
                   --租金增值税
                 to_char(SUM(NVL(cw.vat_due_amount,0)),'FM999,999,999,990.00')  vat_due_amount,
                 to_char(SUM(NVL(cw.principal,0)),'FM999,999,999,990.00')  principal,
                 to_char(SUM(NVL(cw.vat_principal,0)),'FM999,999,999,990.00') vat_principal,

                 --上下端利息
                 to_char(SUM(nvl((SELECT cu.interest
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 2),
                         0)),'FM999,999,999,990.00') up_interest, --上端
                 to_char(SUM(nvl((SELECT cu.interest
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 1),
                         0)),'FM999,999,999,990.00') down_interest, --下端
                 to_char(SUM((SELECT nvl(SUM(cu.interest), 0)
                       FROM con_unearned_finance_income cu
                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id)),'FM999,999,999,990.00') total_interest,
                 --上下段税利息
                 to_char(SUM(nvl((SELECT cu.vat_interest
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 2),
                         0)),'FM999,999,999,990.00') up_vat_interest, --上端
                 to_char(SUM(nvl((SELECT cu.vat_interest
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 1),
                         0)),'FM999,999,999,990.00') down_vat_interest, --下端
                 to_char(SUM((SELECT nvl(SUM(cu.vat_interest), 0)
                       FROM con_unearned_finance_income cu

                      WHERE cu.cashflow_id = cw.cashflow_id
                        AND cu.contract_id = cw.contract_id)),'FM999,999,999,990.00') total_vat_interest,
                 --不含税利息
                 to_char(SUM(nvl((SELECT cu.unearned_finance_income
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 2),
                         0)),'FM999,999,999,990.00') up_net_interest,
                 to_char(SUM(nvl((SELECT cu.unearned_finance_income
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 1),
                         0)),'FM999,999,999,990.00') down_net_interest,
                 to_char(SUM((nvl((SELECT cu.unearned_finance_income
                            FROM con_unearned_finance_income cu
                           WHERE cu.cashflow_id = cw.cashflow_id
                             AND cu.contract_id = cw.contract_id
                             AND cu.seq_in_period = 2),
                          0) + nvl((SELECT cu.unearned_finance_income
                                      FROM con_unearned_finance_income cu
                                     WHERE cu.cashflow_id = cw.cashflow_id
                                       AND cu.contract_id = cw.contract_id
                                       AND cu.seq_in_period = 1),
                                    0)

                     )),'FM999,999,999,990.00') total_net_interest,
                 --本期天数
                 SUM(nvl((SELECT cu.weight_factor
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 2),
                         0)) up_weight_factor,
                 SUM(nvl((SELECT cu.weight_factor
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 1),
                         0)) down_weight_factor,
                 SUM(nvl((SELECT cu.total_weight
                           FROM con_unearned_finance_income cu
                          WHERE cu.cashflow_id = cw.cashflow_id
                            AND cu.contract_id = cw.contract_id
                            AND cu.seq_in_period = 1),
                         0)) total_weight_factor

            FROM con_contract ct, con_contract_cashflow cw
           WHERE ct.contract_id = cw.contract_id
             AND cf_item <> 0 AND ct.contract_id = ${/parameter/@contract_id}

          ) t
   ORDER BY to_number(times),cf_item


                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
<!--        <bm:query-field name="contract_id" queryExpression="t.contract_id=${@contract_id}"/>-->
    </bm:query-fields>
</bm:model>
