<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qianming
    $Date: 2014-7-25 下午2:18:46
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT" needAccessControl="false" defaultOrderBy="t1.contract_id DESC">
    <bm:fields>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CON_CONTRACT.CONTRACT_ID"/>
        <bm:field name="data_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DATA_CLASS" prompt="CON_CONTRACT.DATA_CLASS"/>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NUMBER" prompt="CON_CONTRACT.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME" prompt="CON_CONTRACT.CONTRACT_NAME"/>
        <bm:field name="project_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="PROJECT_ID" prompt="CON_CONTRACT.PROJECT_ID"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="CON_CONTRACT.COMPANY_ID"/>
        <bm:field name="lease_channel" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="LEASE_CHANNEL" prompt="CON_CONTRACT.LEASE_CHANNEL"/>
        <bm:field name="bp_id_tenant" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID_TENANT" prompt="CON_CONTRACT.BP_ID_TENANT"/>
        <bm:field name="bp_id_tenant_n" expression="(select bp_name from hls_bp_master where bp_id = t1.bp_id_tenant)" forInsert="false" forUpdate="false"/>
        <bm:field name="contract_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_STATUS" prompt="CON_CONTRACT.CONTRACT_STATUS"/>
        <bm:field name="bankid" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANKID" prompt="CON_CONTRACT.EANKID"/>
        <bm:field name="ebank_id_code" expression="(select bank_code from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/>
        <bm:field name="ebank_id_n" expression="(select bank_full_name from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/>
        <bm:field name="ebank_id_c" expression="(select bank_short_name from csh_tenant_bank_v where bank_id = t1.bankid)" forInsert="false" forUpdate="false"/>
        <bm:field name="ebank_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_TYPE" prompt="CON_CONTRACT.EBANK_TYPE"/>
        <bm:field name="ebank_account_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_ACCOUNT_NAME" prompt="CON_CONTRACT.EBANK_ACCOUNT_NAME"/>
        <bm:field name="ebank_account_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="ebank_account_num" prompt="CON_CONTRACT.EBANK_ACCOUNT_NUM"/>
        <bm:field name="idtype_n" expression="(SELECT scv.code_value_name FROM sys_code_values_v scv WHERE scv.code = &apos;HLS211_ID_TYPE&apos; AND scv.code_value = t1.idtype)" forInsert="false" forUpdate="false"/>
        <bm:field name="idtype"/>
        <bm:field name="idcode"/>
        <bm:field name="mobile"/>
        <!-- <bm:field name="ebank_protocol_status" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="CON591_EBANK_PROTOCOL_STATUS"  lookupField="ebank_protocol_status_n" physicalName="EBANK_PROTOCOL_STATUS"/> -->
        <bm:field name="ebank_protocol_status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EBANK_PROTOCOL_STATUS"/>
        <bm:field name="ebank_protocol_status_n" expression="(select  sv.code_value_name from sys_codes s,sys_code_values_vl sv where s.code_id = sv.code_id and sv.code_value = t1.ebank_protocol_status and s.code =&apos;CON591_EBANK_PROTOCOL_STATUS&apos;)" forInsert="false" forUpdate="false"/>
        <bm:field name="protocol_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROTOCOL_NUMBER"/>
        <bm:field name="ebank_account_prop" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="deduction_flag"/>
        <bm:field name="expireddate"/>
        <bm:field name="bankacctname"/>
        <bm:field name="bankacctid"/>
        <bm:field name="acctype"/>
        <bm:field name="deduction_flag_n"/>
        <bm:field name="acctype_n" expression="(select  sv.code_value_name from sys_codes s,sys_code_values_vl sv where s.code_id = sv.code_id and sv.code_value = t1.acctype and s.code =&apos;EBANK_DEDUCT_TYPE&apos;)" forInsert="false" forUpdate="false"/>
        <bm:field name="bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="bank_account_id"/>
        <bm:field name="bank_account_code" expression="(select bank_account_code from csh_bank_account_v where bank_account_id = t1.bank_account_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="deduct_bank"/>
        <bm:field name="deduct_bank_n"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="app_bank_name"/>
        <bm:field name="app_bank_branch"/>
        <bm:field name="app_bank_account_name"/>
        <bm:field name="app_bank_account_num"/>
        <bm:field name="contract_status_desc"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="bp_id_agent_level1_n" expression="(select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = t1.bp_id_agent_level1)" forInsert="false" forUpdate="false"/>
    </bm:fields>
    <bm:primary-key>
        <bm:pk-field name="contract_id"/>
    </bm:primary-key>
    <bm:features>
        <f:standard-who/>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    </bm:features>
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[


                begin
                  -- Call the procedure
                  csh_bank_account_pkg.update_contract_bank_info(p_contract_id => ${@contract_id},
                                                                 p_bankid => ${@bankid},
                                                                 p_deduction_flag => ${@deduction_flag},
                                                                 p_acctype => ${@acctype},
                                                                 p_bankacctname => ${@bankacctname},
                                                                 p_bankacctid => ${@bankacctid},
                                                                 p_expireddate => ${@expireddate},
                                                                 p_mobile => ${@mobile},
                                                                 p_idcode => ${@idcode},
                                                                 p_bank_account_id => ${@bank_account_id},
                                                                 p_deduct_bank => ${@deduct_bank},
                                                                 p_app_bank_name         => ${@app_bank_name},
                                                                 p_app_bank_branch       => ${@app_bank_branch},
                                                                 p_app_bank_account_name => ${@app_bank_account_name},
                                                                 p_app_bank_account_num  => ${@app_bank_account_num},
                                                                 p_user_id =>${/session/@user_id});
                end;
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.contract_id,
                    t1.data_class,
                    t1.contract_number,
                    t1.contract_name,
                    t1.project_id,
                    t1.company_id,
                    t1.lease_channel,
                    t1.bp_id_tenant,
                    (SELECT bp_name FROM hls_bp_master WHERE bp_id = t1.bp_id_tenant
                    ) AS bp_id_tenant_n,
                    t1.contract_status,
                    (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON500_CONTRACT_STATUS' AND
                            v.code_value = t1.contract_status
                        ) contract_status_desc,
                    t1.bankid,
                    (SELECT bank_code FROM csh_tenant_bank_v WHERE bank_id = t1.bankid
                    ) AS ebank_id_code,
                    (SELECT bank_full_name FROM csh_tenant_bank_v WHERE bank_id = t1.bankid
                    ) AS ebank_id_n,
                    (SELECT bank_short_name FROM csh_tenant_bank_v WHERE bank_id = t1.bankid
                    ) AS ebank_id_c,
                    t1.ebank_type,
                    t1.ebank_account_name,
                    t1.ebank_account_num,
                    (SELECT
                        scv.code_value_name
                    FROM
                        sys_code_values_v scv
                    WHERE
                        scv.code       = 'HLS211_ID_TYPE' AND
                        scv.code_value = NVL(t1.idtype,'ID_CARD')
                    ) AS idtype_n,
                    NVL(t1.idtype,'ID_CARD') AS idtype,
                    NVL(t1.idcode,
                    (SELECT l.id_card_no FROM hls_bp_master_lv l WHERE l.bp_id = t1.bp_id_tenant
                    )) AS idcode,
                    NVL(t1.mobile,
                    (SELECT l.phone FROM hls_bp_master_lv l WHERE l.bp_id = t1.bp_id_tenant
                    )) AS mobile,
                    t1.ebank_protocol_status,
                    (SELECT
                        sv.code_value_name
                    FROM
                        sys_codes s,
                        sys_code_values_vl sv
                    WHERE
                        s.code_id     = sv.code_id AND
                        sv.code_value = t1.ebank_protocol_status AND
                        s.code        ='CON591_EBANK_PROTOCOL_STATUS'
                    ) AS ebank_protocol_status_n,
                    t1.protocol_number,
                    t1.ebank_account_prop,
                    t1.deduction_flag,
                    (SELECT V.CODE_VALUE_NAME
                     FROM SYS_CODE_VALUES_V V
                     WHERE V.CODE = 'YES_NO'
                     AND V.CODE_VALUE = t1.deduction_flag) as deduction_flag_n,
                    t1.expireddate,
                    t1.bankacctname,
                    t1.bankacctid,
                    t1.acctype,
                    (SELECT
                        sv.code_value_name
                    FROM
                        sys_codes s,
                        sys_code_values_vl sv
                    WHERE
                        s.code_id     = sv.code_id AND
                        sv.code_value = t1.acctype AND
                        --s.code        ='DEDUCTION_CARD_TYPE'
                        s.code        ='EBANK_DEDUCT_TYPE'
                    ) AS acctype_n,
                    t1.bank_account_id,
                    (select bank_account_code from csh_bank_account_v where bank_account_id = t1.bank_account_id) bank_account_code,
                    t1.deduct_bank,
                   (SELECT
                        scv.code_value_name
                    FROM
                        sys_code_values_v scv
                    WHERE
                        scv.code       = 'DEDUCT_BANK' AND
                        scv.code_value = t1.deduct_bank
                    ) AS deduct_bank_n,
                    t1.bp_id_agent_level1,
                    (select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n,
                    t1.app_bank_name,

                    app_bank_branch,
                    app_bank_account_name,
                    app_bank_account_num,
                    t1.lease_execution_date

                FROM
                    con_contract t1 #WHERE_CLAUSE#
                    #ORDER_BY_CLAUSE#
                --ORDER BY
                    --t1.contract_number DESC
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="t1.contract_id in ${:@contract_id}"/>
        <bm:query-field field="bp_id_tenant" queryOperator="="/>
        <bm:query-field field="ebank_protocol_status" queryOperator="="/>
        <bm:query-field field="bankid" queryOperator="="/>
        <bm:query-field field="ebank_id_n" queryExpression="(select bank_full_name from csh_bank_v where bank_id = t1.bankid) like &apos;%&apos;||${@ebank_id_n}||&apos;%&apos;"/>
        <bm:query-field field="protocol_number" queryOperator="="/>
        <!-- <bm:query-field name="flag" queryExpression="exists (select 1 from dual where (t1.ebank_account_name is not null and t1.ebank_account_num is not null and t1.ebank_type is not null and t1.ebank_id is not null and ${@flag} = &apos;Y&apos;) or ${@flag} = &apos;N&apos;)"/> -->
        <bm:query-field field="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field field="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like &apos;%&apos;||${@bankacctid}||&apos;%&apos;"/>
        <bm:query-field name="status" queryExpression="(t1.ebank_protocol_status in (&apos;0&apos;,&apos;3&apos;,&apos;4&apos;,&apos;5&apos;) or t1.ebank_protocol_status is null)"/>
        <bm:query-field name="pki_status" queryExpression="(t1.ebank_protocol_status not in (&apos;2&apos;,&apos;4&apos;))"/>
        <bm:query-field name="deduction_flag" queryExpression="nvl(t1.deduction_flag, &apos;N&apos;)=${@deduction_flag}"/>
        <bm:query-field field="acctype" queryOperator="="/>
        <bm:query-field name="deduct_bank" queryExpression="nvl(t1.deduct_bank,'N')=${@deduct_bank}"/>
        <bm:query-field field="bankacctname" queryExpression="t1.bankacctname like &apos;%&apos;||${@bankacctname}||&apos;%&apos;"/>
        <bm:query-field field="bankacctid" queryExpression="t1.bankacctid like &apos;%&apos;||${@bankacctid}||&apos;%&apos;"/>
        <bm:query-field name="expireddate_from" queryExpression="${@expireddate_from} &lt;= t1.expireddate"/>
        <bm:query-field name="expireddate_to" queryExpression="${@expireddate_to} &gt;=  t1.expireddate"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression=" t1.bp_id_agent_level1 =  ${@bp_id_agent_level1}"/>
    </bm:query-fields>
    <bm:data-filters>
        <!--<bm:data-filter enforceOperations="query" expression="t1.contract_status NOT IN (&apos;CHANGE&apos;,&apos;CANCEL&apos;,&apos;CLOSED&apos;)"/>-->
        <bm:data-filter enforceOperations="query" expression="t1.contract_status IN (&apos;SIGN&apos;,&apos;INCEPT&apos;,&apos;TERMINATE&apos;,&apos;ETING&apos;,&apos;ET&apos;,&apos;PENDING&apos;,&apos;ADING&apos;,&apos;AD&apos;,&apos;AGENT_TERMINATE&apos;,&apos;LOSSSHARE_TERMINATE&apos;,&apos;VIRTUAL_TERMINATE&apos;,&apos;REPUR&apos;,&apos;PAID&apos;)"/>
        <bm:data-filter enforceOperations="query" expression="t1.data_class = &apos;NORMAL&apos;"/>
        <bm:data-filter enforceOperations="query" expression="exists (select 1 from hls_bp_master where bp_id = t1.bp_id_tenant)"/>
    </bm:data-filters>
</bm:model>