<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
               select *
   from (select t1.contract_id,
       t1.contract_number,
       t1.bp_id_tenant,
       (select hm.bp_name
          from hls_bp_master hm
         where hm.bp_id = t1.bp_id_tenant) bp_id_tenant_n,
       (select hm.id_card_no
          from hls_bp_master hm
         where hm.bp_id = t1.bp_id_tenant) id_card_no_tenant,
       (select hm.bp_class
          from hls_bp_master hm
         where hm.bp_id = t1.bp_id_tenant) bp_id_tenant_type,
       t1.bp_id_agent_level1,
       t1.sign_type,
       (select hm.bp_name
          from hls_bp_master hm
         where hm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n,
       (select hm.id_card_no_leg
          from hls_bp_master hm
         where hm.bp_id = t1.bp_id_agent_level1) id_card_no_agent,
       decode(t1.division,70,(SELECT i.machine_number_70
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN'),(SELECT i.machine_number
          FROM con_contract_lease_item i
         WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN')) machine_number, --机号
       t1.contract_status,
       (select v.code_value_name
          from sys_code_values_v v
         where v.code = decode(nvl(t1.sign_type,'PAPER_SIGN'),
                               'PAPER_SIGN',
                               'CON_PAPER_SIGN_STATUS',
                               'ELE_SIGN',
                               'CON_ELE_SIGN_STATUS')
           and v.code_value = t1.contract_status
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y') AS contract_status_n,
       (select ci.modelcd
          from con_contract_lease_item ci
         where ci.contract_id = t1.contract_id
           and ci.equipment_type = 'MAIN') modelcd, --机型
       to_char(t1.lease_start_date, 'yyyy-mm-dd') lease_start_date, --合同开始日期
       (select to_char(ccc.due_date,'yyyy-MM-dd') from con_contract_cashflow ccc
       where ccc.contract_id = t1.contract_id
       and ((ccc.cf_item = 1 and ccc.cf_type in (1,200,250)) or (ccc.cf_item = 11 and ccc.cf_type =11)
                    or (ccc.cf_item = 8 and ccc.cf_type = 8) or (ccc.cf_item = 9 and ccc.cf_type = 9))
                    and ccc.cf_direction = 'INFLOW'
                    and ccc.cf_status = 'RELEASE'
       and ccc.times = (select max(cc.times)  from con_contract_cashflow cc where cc.contract_id = ccc.contract_id
       and ((cc.cf_item = 1 and cc.cf_type in (1,200,250)) or (cc.cf_item = 11 and cc.cf_type =11)
                    or (cc.cf_item = 8 and cc.cf_type = 8) or (cc.cf_item = 9 and cc.cf_type = 9))
                    and cc.cf_direction = 'INFLOW'
                    and cc.cf_status = 'RELEASE')
       and rownum =1) lease_end_date, --合同结束日期
       (select to_char(cc.due_date, 'dd')
          from con_contract_cashflow cc
         where cc.contract_id = t1.contract_id
           and cc.cf_item = 1
           and cc.cf_direction = 'INFLOW'
           and cc.cf_status = 'RELEASE'
           and rownum = 1) due_day, --还款日
       (NVL(t1.down_payment, 0) + NVL(t1.residual_value, 0) +
       NVL(t1.total_rental, 0)) total_sign, --总金额
       t1.bank_branch_num, --还款银行账号
       t1.bank_account_name, --还款账户名称
       (select ba.bank_full_name
          from hls_bp_master_bank_account ba
         where ba.bank_account_id = t1.bank_account_id) bank_full_name, --还款银行名称
       t1.lease_times, --期数
       (select to_char(sum(cc.due_amount), 'FM9,999,999,999,990.00')
          from con_contract_cashflow cc
         where cc.contract_id = t1.contract_id
           and ((cc.cf_type = 1 and cc.cf_item in (1,200,250)) or (cc.cf_item = 11 and cc.cf_type =11)
                    or (cc.cf_item = 8 and cc.cf_type = 8) or (cc.cf_item = 9 and cc.cf_type = 9))
                    and cc.cf_direction = 'INFLOW'
                    and cc.cf_status = 'RELEASE') due_amount_all, --应还总额
       (SELECT cs.sign_url
                  from con_contract_ele_signer cs
                 where cs.contract_id = t1.contract_id
                   and ((${@account_type} = 'AG' and
                       (cs.id_no =
                       (select hm.social_code
                             from hls_bp_master hm, con_contract_bp cb
                            where hm.bp_id = cb.bp_id
                              and cb.contract_id = t1.contract_id
                              and cb.id_card_no_leg = ${@id_no}) or
                       cs.id_no =
                       (select hm.social_code
                             from hls_bp_master hm, hls_bp_master_signer hs
                            where hm.bp_id = hs.bp_id
                              and hm.bp_id = t1.bp_id_tenant
                              and hs.id_card = ${@id_no}))) or
                       (${@account_type} != 'AG' and cs.id_no = ${@id_no}))
                   and rownum = 1) sign_url
  from con_contract t1
 where t1.data_class = 'NORMAL'
   and nvl(t1.sign_contract_status,'SIGN') = decode(t1.contract_status,'NEW','SIGN',nvl(t1.sign_contract_status,'SIGN'))
   and t1.contract_status in
       (select v.code_value
          from sys_code_values_v v
         where v.code = decode(nvl(t1.sign_type,'PAPER_SIGN'),
                               'PAPER_SIGN',
                               'CON_PAPER_SIGN_STATUS',
                               'ELE_SIGN',
                               'CON_ELE_SIGN_STATUS')
           and v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y')
   and ((${@account_type} = 'PE' and exists
        (select 1
            from con_contract_bp ccb, hls_bp_master hb
           where ccb.bp_id = hb.bp_id
             and ccb.contract_id = t1.contract_id
             and (ccb.bp_category in ('TENANT', 'GUARANTOR') and
                 (ccb.id_card_no = ${@id_no} or (ccb.id_no_sp = ${@id_no} and ccb.sp_sign_flag = 'Y'))))) or
       (${@account_type} = 'AG' and exists
        (select 1
            from hls_bp_master_signer hs
           where hs.hls_bp_signer_id = t1.signer
             and hs.id_card = ${@id_no})
             and exists (select 1 from hls_bp_master hbm
             where hbm.bp_id = t1.bp_id_tenant
             and hbm.social_code = ${@organization_id})
            and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)) or
               (${@account_type} = 'AG' and exists
                (select 1
                    from hls_bp_master_signer hs,con_contract_change_req cr
                   where hs.hls_bp_signer_id = cr.signer
                     and cr.change_req_id = t1.contract_id
                     and hs.id_card = ${@id_no})
                 and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)
             ) or
       (${@account_type} = 'AG' and exists
        (select 1
            from con_contract_bp cb,hls_bp_master hbm
           where cb.contract_id = t1.contract_id
             and cb.bp_category = 'GUARANTOR'
             and cb.bp_class = 'ORG'
             and cb.bp_id = hbm.bp_id
             and hbm.social_code = ${@organization_id}
             and cb.id_card_no_leg = ${@id_no})) or
       (${@account_type} = 'LP' and exists
        (select 1
            from con_contract_bp cb, hls_bp_master hb
           where cb.bp_id = hb.bp_id
			 and cb.contract_id = t1.contract_id
             and hb.social_code = ${@id_no})))
) v #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="sign_type"/>
        <bm:field name="bp_id_tenant_type"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="machine_number"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="modelcd"/>
        <bm:field name="lease_start_date"/>
        <bm:field name="lease_end_date"/>
        <bm:field name="due_day"/>
        <bm:field name="total_sign"/>
        <bm:field name="bank_full_name"/>
        <bm:field name="bank_account_name"/>
        <bm:field name="bank_branch_num"/>
        <bm:field name="lease_times"/>
        <bm:field name="due_amount_all"/>
        <bm:field name="sign_url"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like '%'||${@contract_number}||'%' "/>
        <bm:query-field name="contract_status" queryExpression="v.contract_status_n = ${@contract_status}"/>
    </bm:query-fields>
</bm:model>