<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hc  
    $Date: 2015-10-10 下午01:37:01  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="PRJ_CHANCE_V" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    project_status,   --项目状况
                    rownum AS row_num,--序号
                    contract_number,  --合同号
                    bp_name,          --承租人名称
                    bp_business,      --承租人简介
                    --lease_items,
                    business_desc, -- 租赁方式
                    finance_amount,--租赁本金（万元）
                    lease_term,    --期限（月）
                    irr,           --内部收益率
                    irr_n,
                    guarantor_bp,  --保证人
                    --mortgage_names,
                    notes,             --租金共几期，已回收几期，逾期几期
                    inception_of_lease,--投放时间
                    org_name,          --责任部门
                    sponsor,           --主办人
                    assist,            --协办人
                    deposit,           --保证金
                    deposit_ratio,     --保证金比例
                    int_rate,
                    deposit_ratio_n,
                    int_rate_n,
                    commission_charge_n,
                    commission_charge
                FROM
                    (SELECT
                        '已投放项目' project_status,
                        cc.contract_number,
                        bgfl_report_project_pkg.get_con_bp_name(cc.contract_id) bp_name,        ---承租人
                        bgfl_report_project_pkg.get_con_bp_business(cc.contract_id) bp_business, --承租人简介
                        --bgfl_report_project_pkg.get_con_lease_items(cc.contract_id) lease_items,
                        hbt.description business_desc,                                             --租赁方式
                        cc.finance_amount / 10000 finance_amount,                                  --租赁本金（万元）
                        cc.lease_term * 12 lease_term,                                             --期限（月）
                        cc.irr,       
                        round(cc.irr,6)*100||'%' irr_n,                                                             --内部收益率
                        bgfl_report_project_pkg.get_con_guarantor_bp(cc.contract_id) guarantor_bp, --保证人
                        --bgfl_report_project_pkg.get_con_mortgage(cc.contract_id) mortgage_names, --抵质押物
                        '租金共'
                        || cc.lease_times
                        || '期,已回收'
                        ||
                        (SELECT
                            COUNT(1)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.write_off_flag <> 'NOT' AND
                            c.cf_item =1 and
                            c.contract_id     = cc.contract_id
                        )
                        || '期，逾期'
                        ||
                        (SELECT
                            COUNT(1)
                        FROM
                            con_contract_cashflow cash
                        WHERE
                            cash.contract_id  = cc.contract_id AND
                            cc.overdue_status = 'Y'
                        )
                        || '期' notes,             --租金共几期，已回收几期，逾期几期
                        cc.inception_of_lease,    --投放时间
                        hlo.description org_name, --责任部门
                        ee.name sponsor,          --主办人
                        (SELECT
                            ees.name
                        FROM
                            exp_employees ees
                        WHERE
                            ees.employee_id = pp.assisting_pm_a
                        ) assist,         --协办人
                        cc.deposit,       --保证金
                        cc.deposit_ratio, --保证金比例
                        cc.int_rate,      --租赁利率
                        (ROUND(cc.deposit_ratio,6)*100)
                        ||'%' deposit_ratio_n,
                        (ROUND(cc.int_rate,6)*100)
                        ||'%' int_rate_n,
                        (ROUND(to_number(a.lease_charge_ratio),6)*100)
                        ||'%' commission_charge_n,
                        a.lease_charge commission_charge --手续费/年(含税）
                    FROM
                        con_contract cc,
                        prj_project pp,
                        hls_business_type hbt,
                        hls_lease_organization hlo,
                        exp_employees ee,
                        hls_fin_calculator_hd a,
                        prj_quotation q
                    WHERE
                        cc.project_id          = pp.project_id AND
                        cc.contract_status     = 'INCEPT' AND
                        hbt.business_type      = cc.business_type AND
                        hlo.lease_organization = pp.lease_organization AND
                        ee.employee_id         = pp.employee_id AND
                        a.calc_session_id      = q.calc_session_id AND
                        q.document_category    = 'PROJECT' AND
                        q.document_id          = pp.project_id AND
                        cc.data_class          = 'NORMAL'
                    UNION ALL
                    SELECT
                        '已立项项目' project_status,
                        pp.project_number,
                        bgfl_report_project_pkg.get_prj_bp_name(pp.project_id) bp_name,        ---承租人
                        bgfl_report_project_pkg.get_prj_bp_business(pp.project_id) bp_business, --承租人简介
                        --bgfl_report_project_pkg.get_prj_lease_items(pp.project_id) lease_items,
                        hbt.description business_desc,                                            --租赁方式
                        a.finance_amount / 10000 finance_amount,                                  --租赁本金（万元）
                        a.lease_term * 12 lease_term,                                             --期限（月）
                        to_number(a.irr),   
                        round(to_number(a.irr),6)*100||'%' irr_n,                                                      --内部收益率
                        bgfl_report_project_pkg.get_prj_guarantor_bp(pp.project_id) guarantor_bp, --保证人
                        --bgfl_report_project_pkg.get_prj_mortgage(pp.project_id) mortgage_names, --抵质押物
                        TO_CHAR('') notes,                           --租金共几期，已回收几期，逾期几期
                        to_date(a.inception_of_lease, 'yyyy-mm-dd'), --投放时间
                        hlo.description org_name,                    --责任部门
                        ee.name sponsor,                             --主办人
                        (SELECT
                            ees.name
                        FROM
                            exp_employees ees
                        WHERE
                            ees.employee_id = pp.assisting_pm_a
                        ) assist,                   --协办人
                        to_number(a.deposit),       --保证金
                        to_number(a.deposit_ratio), --保证金比例
                        to_number(a.int_rate),      --租赁利率
                        (ROUND(to_number(a.deposit_ratio),6)*100)
                        ||'%' deposit_ratio_n,
                        (ROUND(to_number(a.int_rate),6)*100)
                        ||'%' int_rate_n,
                        (ROUND(to_number(a.lease_charge_ratio),6)*100)
                        ||'%' commission_charge_n,
                        a.lease_charge --手续费/年(含税）
                    FROM
                        prj_project pp,
                        hls_business_type hbt,
                        hls_lease_organization hlo,
                        exp_employees ee,
                        hls_fin_calculator_hd a,
                        prj_quotation q
                    WHERE
                        pp.project_status      = 'NEW' AND
                        hbt.business_type      = pp.business_type AND
                        hlo.lease_organization = pp.lease_organization AND
                        ee.employee_id         = pp.employee_id AND
                        a.calc_session_id      = q.calc_session_id AND
                        q.document_category    = 'PROJECT' AND
                        q.document_id          = pp.project_id
                    UNION ALL
                    --未创建合同
                    SELECT
                        '在审项目' project_status,
                        pp.project_number,
                        bgfl_report_project_pkg.get_prj_bp_name(pp.project_id) bp_name,        ---承租人
                        bgfl_report_project_pkg.get_prj_bp_business(pp.project_id) bp_business, --承租人简介
                        --bgfl_report_project_pkg.get_prj_lease_items(pp.project_id) lease_items,
                        hbt.description business_desc,                                            --租赁方式
                        a.finance_amount / 10000 finance_amount,                                  --租赁本金（万元）
                        a.lease_term * 12 lease_term,                                             --期限（月）
                        to_number(a.irr),   
                        round(to_number(a.irr),6)*100||'%' irr_n,                                                      --内部收益率
                        bgfl_report_project_pkg.get_prj_guarantor_bp(pp.project_id) guarantor_bp, --保证人
                        --bgfl_report_project_pkg.get_prj_mortgage(pp.project_id) mortgage_names, --抵质押物
                        TO_CHAR('') notes,                           --租金共几期，已回收几期，逾期几期
                        to_date(a.inception_of_lease, 'yyyy-mm-dd'), --投放时间
                        hlo.description org_name,                    --责任部门
                        ee.name sponsor,                             --主办人
                        (SELECT
                            ees.name
                        FROM
                            exp_employees ees
                        WHERE
                            ees.employee_id = pp.assisting_pm_a
                        ) assist,                   --协办人
                        to_number(a.deposit),       --保证金
                        to_number(a.deposit_ratio), --保证金比例
                        to_number(a.int_rate),      --租赁利率
                        (ROUND(to_number(a.deposit_ratio),6)*100)
                        ||'%' deposit_ratio_n,
                        (ROUND(to_number(a.int_rate),6)*100)
                        ||'%' int_rate_n,
                        (ROUND(to_number(a.lease_charge_ratio),6)*100)
                        ||'%' commission_charge_n,
                        a.lease_charge --手续费/年(含税）
                    FROM
                        prj_project pp,
                        hls_business_type hbt,
                        hls_lease_organization hlo,
                        exp_employees ee,
                        hls_fin_calculator_hd a,
                        prj_quotation q
                    WHERE
                        pp.project_status     != 'NEW' AND
                        hbt.business_type      = pp.business_type AND
                        hlo.lease_organization = pp.lease_organization AND
                        ee.employee_id         = pp.employee_id AND
                        a.calc_session_id      = q.calc_session_id AND
                        q.document_category    = 'PROJECT' AND
                        q.document_id          = pp.project_id AND
                        NOT EXISTS
                        (SELECT * FROM con_contract cc WHERE cc.project_id = pp.project_id
                        )
                    UNION ALL
                    --已创建合同
                    SELECT
                        '在审项目' project_status,
                        cc.contract_number,
                        bgfl_report_project_pkg.get_con_bp_name(cc.contract_id) bp_name,        ---承租人
                        bgfl_report_project_pkg.get_con_bp_business(cc.contract_id) bp_business, --承租人简介
                        --bgfl_report_project_pkg.get_con_lease_items(cc.contract_id) lease_items,
                        hbt.description business_desc,                                             --租赁方式
                        cc.finance_amount / 10000 finance_amount,                                  --租赁本金（万元）
                        cc.lease_term * 12 lease_term,                                             --期限（月）
                        cc.irr,  
                        round(cc.irr,6)*100||'%' irr_n,                                                                   --内部收益率
                        bgfl_report_project_pkg.get_con_guarantor_bp(cc.contract_id) guarantor_bp, --保证人
                        --bgfl_report_project_pkg.get_con_mortgage(cc.contract_id) mortgage_names, --抵质押物
                        '租金共'
                        || cc.lease_times
                        || '期,已回收'
                        ||
                        (SELECT
                            COUNT(1)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.write_off_flag <> 'NOT' AND
                            c.cf_item =1 and
                            c.contract_id     = cc.contract_id
                        )
                        || '期，逾期'
                        ||
                        (SELECT
                            COUNT(1)
                        FROM
                            con_contract_cashflow cash
                        WHERE
                            cash.contract_id  = cc.contract_id AND
                            cc.overdue_status = 'Y'
                        )
                        || '期' notes,             --租金共几期，已回收几期，逾期几期
                        cc.inception_of_lease,    --投放时间
                        hlo.description org_name, --责任部门
                        ee.name sponsor,          --主办人
                        (SELECT
                            ees.name
                        FROM
                            exp_employees ees
                        WHERE
                            ees.employee_id = pp.assisting_pm_a
                        ) assist,              --协办人
                        cc.deposit,            --保证金
                        cc.deposit_ratio*100 , --保证金比例
                        cc.int_rate,           --租赁利率
                        (ROUND(cc.deposit_ratio,6)*100)
                        ||'%' deposit_ratio_n,
                        (ROUND(cc.int_rate,6)*100)
                        ||'%' int_rate_n,
                        (ROUND(a.lease_charge_ratio,6)*100)
                        ||'%' commission_charge_n,
                        a.lease_charge commission_charge --手续费/年(含税）
                    FROM
                        con_contract cc,
                        prj_project pp,
                        hls_business_type hbt,
                        hls_lease_organization hlo,
                        exp_employees ee,
                        hls_fin_calculator_hd a,
                        prj_quotation q
                    WHERE
                        cc.project_id          = pp.project_id AND
                        cc.contract_status    != 'INCEPT' AND
                        cc.data_class          = 'NORMAL' AND
                        pp.project_status     <> 'NEW' AND
                        hbt.business_type      = cc.business_type AND
                        hlo.lease_organization = pp.lease_organization AND
                        ee.employee_id         = pp.employee_id AND
                        a.calc_session_id      = q.calc_session_id AND
                        q.document_category    = 'PROJECT' AND
                        q.document_id          = pp.project_id
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="row_num" expression="rownum"/>
        <bm:field name="project_status"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_business"/>
        <bm:field name="deposit_ratio_n"/>
        <bm:field name="int_rate_n"/>
        <bm:field name="irr_n"/>
        <bm:field name="commission_charge_n"/>
        <bm:field name="business_desc"/>
        <bm:field name="finance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_term"/>
        <bm:field name="irr" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="guarantor_bp"/>
        <bm:field name="notes"/>
        <bm:field name="inception_of_lease" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="org_name"/>
        <bm:field name="sponsor"/>
        <bm:field name="assist"/>
        <bm:field name="deposit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="deposit_ratio" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="int_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="commission_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="project_status" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
