<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: 王建文  
    $Date: 2017-3-6 下午7:49:17  
    $Revision: 1.0  
    $Purpose: 
-->
<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 COUNT(*) FROM prj_project t where 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 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 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 t.project_status = 'APPROVED' and t.created_by =${@user_id}) approved_count,
       (SELECT COUNT(*) FROM prj_project t WHERE 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,
       (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)))) month_new_add

  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:field name="month_new_add" databaseType="NUMBER" datatype="java.lang.Long" physicalName="MONTH_NEW_ADD"/>
    </bm:fields>
</bm:model>