<?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, 'yyyymm') = TO_CHAR(sysdate, 'yyyymm') 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, 'yyyymm') = TO_CHAR(sysdate, 'yyyymm') 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, 'yyyymm') = TO_CHAR(sysdate, 'yyyymm') 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, 'yyyymm') = TO_CHAR(sysdate, 'yyyymm') 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, 'yyyymm') = TO_CHAR(sysdate, 'yyyymm') 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>
