<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zc
    $Date: 2020年11月9日 10点35分
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id"/>
        <bm:field name="bp_name"/>
        <bm:field name="id_type"/>
        <bm:field name="id_no"/>
        <bm:field name="mobile"/>
        <bm:field name="signer_category"/>
        <bm:field name="esign_type"/>
        <bm:field name="legal_person"/>
        <bm:field name="id_card_no_leg"/>
       </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select distinct t.* from (select
                   cs.contract_id,
                   (select c.contract_number from con_contract c where c.contract_id = cs.contract_id) contract_number,
                   cs.bp_id,
                   cs.bp_name,
                   cs.id_type,
                   cs.id_no,
                   cs.mobile,
                   hs.signer_category,
                   hs.indentity_type,
                    nvl(hs.auto_sign, 'N') auto_sign,
                    cs.esign_type,
                   (select nvl(hm.bp_name_leg,hm.legal_person)
                    from con_contract_bp cb, hls_bp_master hm
                   where cb.bp_id = hm.bp_id
                     and cb.contract_id = cs.contract_id
                     and hm.social_code = cs.id_no
                     and cb.bp_class = 'ORG'
                     and rownum=1) legal_person,
                   (select hm.id_card_no_leg
                    from con_contract_bp cb, hls_bp_master hm
                   where cb.bp_id = hm.bp_id
                     and cb.contract_id = cs.contract_id
                     and hm.social_code = cs.id_no
                     and cb.bp_class = 'ORG'
                     and rownum=1) id_card_no_leg
              from con_contract_ele_signer cs, hls_doc_file_templet_signer hs,con_contract_content cc,con_clause_templet ct
             where cs.templet_signer_id = hs.templet_signer_id
               and ct.doc_template_id = hs.templet_id
               and ct.templet_id = cc.templet_id
               and cs.contract_id = cc.contract_id
               and cc.clause_usage = 'AGENT_CONTRACT'
               and not exists (select 1
                      from con_contract_bp cb
                     where cb.contract_id = cc.contract_id
                       and hs.signer_category = (cb.bp_category || '_SP')
                       and cb.sp_sign_flag = 'N'
                       and cb.id_no_sp = cs.id_no)) t
                #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="t.contract_id= ${@contract_id}"/>
        <bm:query-field name="signer_category" queryExpression="t.signer_category= ${@signer_category}"/>
        <bm:query-field name="esign_type" queryExpression="t.esign_type= ${@esign_type}"/>
        <bm:query-field name="id_no" queryExpression="t.id_no= ${@id_no}"/>
    </bm:query-fields>
</bm:model>
