select_contract_status_info.lwm

<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hty
    $Date: 2018/5/18 14:54
    $Revision: 1.0 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="contract_status_value" databaseType="VARCHAR2" datatype="java.lang.String"/>

        <bm:field name="contract_count" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="contract_count_percent" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="overdue_count" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="contract_sum_count" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="current_month_append" databaseType="NUMBER" datatype="java.lang.Double"/>

    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select
                  c.contract_status                                as contract_status,
                  (select sc.code_value_name
                   from sys_code_values_v sc
                   where sc.code = 'CON500_CONTRACT_STATUS'
                         and sc.code_value = c.contract_status
                  )                                                as contract_status_value,
                  count(c.contract_id)                             as contract_count,
                  count(c.contract_id) / (select count(contract_id)
                                          from con_contract) * 100 as contract_count_percent,
                  sum(case c.overdue_status
                      when 'Y'
                        then 1
                      else 0 end)                                  as overdue_count,
                  (select count(contract_id)
                   from con_contract)                              as contract_sum_count,
                  (select count(contract_id)
                   from con_contract
                   where con_contract.creation_date > (sysdate - to_char(sysdate, 'dd') + 1)
                  )                                                as current_month_append
                from con_contract c

                group by c.contract_status
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>