<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: shenhaozhe  
    $Date: 2017-9-22 上午11:18:52  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select *
  from (
  --项目上会
  select t1.approval_id,
               pp1.project_id,
               pp1.project_number,
               pp1.project_name,
               pp1.project_status,
               pp1.hn_industry_classification,
               pp1.company_id,
               pp1.bp_name,
               pp1.employee_id,
               pp1.employee_id_n,
               pp1.lease_organization_n,
               pp1.employee_id_of_manager,
               pp1.employee_id_of_manager_n,
               t1.status,
               t1.approval_result,
               v.workflow_desc work_flow_type,
               v.workflow_code,
               v.workflow_desc,
               v.document_info,
               v.instance_id,
               'PRJ_PROJECT_APPROVAL' table_name
          from prj_project_approval       t1,
               prj_project_lv             pp1,
               zj_wfl_workflow_instance_v v
         where t1.project_id = pp1.project_id
           and pp1.wfl_instance_id_2 = v.instance_id
           and pp1.project_status = 'APPROVING'
           and v.instance_status='1'
           and t1.status = 'VOTING'
        union all
        --项目补充
        select t2.approval_id,
               ppl.project_id,
               ppl.project_number,
               ppl.project_name,
               ppl.project_status,
               ppl.hn_industry_classification,
               ppl.company_id,
               ppl.bp_name,
               ppl.employee_id,
               ppl.employee_id_n,
               ppl.lease_organization_n,
               ppl.employee_id_of_manager,
               ppl.employee_id_of_manager_n,
               t2.status,
               t2.approval_result,
               v.workflow_desc work_flow_type,
               v.workflow_code,
               v.workflow_desc,
               v.document_info,
               v.instance_id,
               'HN_DOC_APPROVAL' table_name
          from hn_doc_approval            t2,
               hn_prj_project_side        pp2,
               prj_project_lv             ppl,
               zj_wfl_workflow_instance_v v
         where t2.document_id = pp2.prj_side_id
           and t2.document_type = 'HN_PRJ_PROJECT_SIDE'
           and pp2.wfl_instance_id = v.instance_id
           and pp2.project_id = ppl.project_id
           and pp2.wfl_status <> 'APPROVED'
           and v.instance_status='1'
           and t2.status = 'VOTING'
        
        union all
        --合同文本变更
        select t1.approval_id,
               ppl.project_id,
               ppl.project_number,
               ppl.project_name,
               ppl.project_status,
               ppl.hn_industry_classification,
               ppl.company_id,
               ppl.bp_name,
               ppl.employee_id,
               ppl.employee_id_n,
               ppl.lease_organization_n,
               ppl.employee_id_of_manager,
               ppl.employee_id_of_manager_n,
               t1.status,
               t1.approval_result,
               v.workflow_desc work_flow_type,
               v.workflow_code,
               v.workflow_desc,
               v.document_info,
               v.instance_id,
               'PRJ_PROJECT_APPROVAL' table_name
          from prj_project_approval       t1,
               prj_project_lv             ppl,
               hn_atm_complement          t,
               zj_wfl_workflow_instance_v v
         where t1.project_id = ppl.project_id
           and ppl.data_class = 'VIRTUAL_CON'
           and ppl.project_id = t.document_id
           and t.status = 'APPROVING'
           and t.wfl_instance_id = v.instance_id
           and ppl.project_status = 'APPROVED'
           and v.instance_status='1'
           and t1.status = 'VOTING'
        union all
        --付款先决条件变更
        select t2.approval_id,
               ppl.project_id,
               ppl.project_number,
               ppl.project_name,
               ppl.project_status,
               ppl.hn_industry_classification,
               ppl.company_id,
               ppl.bp_name,
               ppl.employee_id,
               ppl.employee_id_n,
               ppl.lease_organization_n,
               ppl.employee_id_of_manager,
               ppl.employee_id_of_manager_n,
               t2.status,
               t2.approval_result,
               v.workflow_desc work_flow_type,
               v.workflow_code,
               v.workflow_desc,
               v.document_info,
               v.instance_id,
               'HN_DOC_APPROVAL' table_name
          from hn_doc_approval            t2,
               hn_payment_cond_change_hd  pp3,
               prj_project_lv             ppl,
               zj_wfl_workflow_instance_v v
         where t2.document_id = pp3.header_id
           and t2.document_type = 'COND_CHANGE'
           and pp3.wfl_instance_id = v.instance_id
           and pp3.project_id = ppl.project_id
           and pp3.req_status <> 'APPROVED'
           and v.instance_status='1'
           and t2.status = 'VOTING'
           union all
           
           --项目变更
        select t2.approval_id,
               ppl.project_id,
               ppl.project_number,
               ppl.project_name,
               ppl.project_status,
               ppl.hn_industry_classification,
               ppl.company_id,
               ppl.bp_name,
               ppl.employee_id,
               ppl.employee_id_n,
               ppl.lease_organization_n,
               ppl.employee_id_of_manager,
               ppl.employee_id_of_manager_n,
               t2.status,
               t2.approval_result,
               v.workflow_desc work_flow_type,
               v.workflow_code,
               v.workflow_desc,
               v.document_info,
               v.instance_id,
               'HN_DOC_APPROVAL' table_name
          from hn_doc_approval            t2,
               prj_project_change_req  pp3,
               prj_project_lv             ppl,
               zj_wfl_workflow_instance_v v
         where t2.document_id = pp3.change_req_id
           and pp3.wfl_instance_id = v.instance_id
           and pp3.project_id = ppl.project_id
           and pp3.req_status <> 'APPROVED'
           and v.instance_status='1'
           and t2.status = 'VOTING') t1

             #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="approval_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="approval_id"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="project_id"/>
        <bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="project_number" prompt="项目编号"/>
        <bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="project_name" prompt="项目名称"/>
        <bm:field name="project_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="project_status" prompt="项目状态"/>
        <bm:field name="hn_industry_classification" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="hn_industry_classification"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="company_id"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_name" prompt="承租人名称"/>
        <bm:field name="employee_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="employee_id"/>
        <bm:field name="employee_id_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="employee_id_n" prompt="项目经理"/>
        <bm:field name="lease_organization_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="lease_organization_n" prompt="部门名称"/>
        <bm:field name="employee_id_of_manager" databaseType="NUMBER" datatype="java.lang.Long" physicalName="employee_id_of_manager"/>
        <bm:field name="employee_id_of_manager_n" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="employee_id_of_manager_n" prompt="部门负责人"/>
        <bm:field name="work_flow_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="work_flow_type" prompt="上会类型"/>
        <bm:field name="workflow_code"/>
        <bm:field name="workflow_desc"/>
        <bm:field name="document_info"/>
        <bm:field name="instance_id"/>
        <bm:field name="table_name"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="project_number" queryOperator="="/>
        <bm:query-field field="project_name" queryOperator="="/>
        <bm:query-field field="employee_id_n" queryOperator="="/>
        <bm:query-field field="bp_name" queryOperator="="/>
        <bm:query-field field="lease_organization_n" queryOperator="="/>
        <bm:query-field field="work_flow_type" queryOperator="="/>
    </bm:query-fields>
</bm:model>
