con_contract_overdue.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hty
    $Date: 2018/5/17 22:42
    $Revision: 1.0 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate)
                  ) overdue_amount,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 1
                  ) overdue_amount_1,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 2
                  ) overdue_amount_2,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 3
                  ) overdue_amount_3,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 4
                  ) overdue_amount_4,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 5
                  ) overdue_amount_5,
                  (
                    SELECT NVL(
                               SUM(
                                   (
                                     nvl(cc.due_amount, 0) - nvl(cc.received_amount, 0)
                                   )
                               ),
                               0
                           ) AS overdue_amount
                    FROM
                      con_contract_cashflow cc
                    WHERE
                      cc.overdue_status = 'Y'
                      AND cc.due_date = trunc(sysdate) - 6
                  ) overdue_amount_6
                FROM
                  dual
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="overdue_amount" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdueAmount" prompt="员工号"/>
        <bm:field name="overdue_amount_1" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_1" prompt="员工号"/>
        <bm:field name="overdue_amount_2" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_2" prompt="员工号"/>
        <bm:field name="overdue_amount_3" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_3" prompt="员工号"/>
        <bm:field name="overdue_amount_4" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_4" prompt="员工号"/>
        <bm:field name="overdue_amount_5" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_5" prompt="员工号"/>
        <bm:field name="overdue_amount_6" databaseType="NUMBER" datatype="java.lang.Double" forDisplay="true" forQuery="true" physicalName="overdue_amount_6" prompt="员工号"/>

    </bm:fields>
</bm:model>