<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Think
    $Date: 2017-1-16 上午10:53:16  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                
                select 
      (SELECT
                    COUNT(*) 
                FROM
                    prj_project t
                WHERE
                    TO_CHAR(creation_date,'yyyy')=TO_CHAR(sysdate,'yyyy') and 
                    
                    exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = t.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate)))) apply_count,
      (SELECT
                    COUNT(*) 
                FROM
                    prj_project t
                WHERE
                    TO_CHAR(creation_date,'yyyy')=TO_CHAR(sysdate,'yyyy') and t.project_status='NEW' and 
                    exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = t.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate))) ) new_count,
       (SELECT
                    COUNT(*) 
                FROM
                    prj_project t
                WHERE
                    TO_CHAR(creation_date,'yyyy')=TO_CHAR(sysdate,'yyyy') and t.project_status='APPROVING' and 
                    exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = t.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate))) ) approving_count,
        (SELECT
                    COUNT(*) 
                FROM
                    prj_project t
                WHERE
                    TO_CHAR(creation_date,'yyyy')=TO_CHAR(sysdate,'yyyy') and t.project_status='CONTRACT_CREATED' and
                    exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = t.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate))) ) approved_count,
         (SELECT
                    COUNT(*) 
                FROM
                    prj_project t
                WHERE
                    TO_CHAR(creation_date,'yyyy')=TO_CHAR(sysdate,'yyyy') and t.project_status='REJECT' and 
                    exists (select 1 from aut_trx_user_authorize a1,aut_owner_user_authorize a2 where a1.trx_category = 'PROJECT' 
            and a1.trx_id = t.project_id
            and trunc(sysdate) between a1.start_date and nvl(a1.end_date,trunc(sysdate)) 
            and a1.user_id = a2.owner_user_id 
            and a1.trx_category = a2.trx_category  
            and a2.authorized_user_id =${@user_id} 
            and trunc(sysdate) between a2.start_date and nvl(a2.end_date,trunc(sysdate))) ) reject_count
                      
                    from dual
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="apply_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="apply_count"/>
        <bm:field name="new_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="new_count"/>
        <bm:field name="approving_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="approving_count"/>
        <bm:field name="approved_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="approved_count"/>
        <bm:field name="reject_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="reject_count"/>
    </bm:fields>
</bm:model>
