<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from (
                select c.contract_id,
                       c.contract_number,
       (select h.bp_code from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_code,
       (select h.bp_name from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_name,
       (select h.bp_code
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_code,
       (select h.extra_nam
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
       (Select d.description
          From hls_division d
         Where d.division = c.division) As division,
       (Select ch.lease_channel
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel_desc,
       c.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = c.business_type) AS business_type_n,
       (select ccli.pattern
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') pattern,
       (select ccli.machine_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') machine_number,
       (select ccli.equipment_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') equipment_number,
       c.et_date,
       '合格证' send_object,
       cce.cert_send_date01 send_date,
       cce.cert_tracknum01 send_num,
       cce.cert_recipient01 recipients,
       null invoice_number,
       null vat_invoice_code,
       null total_amount,
       null cf_item_n,
       null invoice_date,
       null ele_invoice_hd_id,
       null ele_invoice_status
  from con_contract c, con_contract_et_document cce
 where c.contract_id = cce.contract_id

UNION ALL

select c.contract_id,
       c.contract_number,
       (select h.bp_code from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_code,
       (select h.bp_name from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_name,
       (select h.bp_code
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_code,
       (select h.extra_nam
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
       (Select d.description
          From hls_division d
         Where d.division = c.division) As division,
       (Select ch.lease_channel
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel_desc,
       c.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = c.business_type) AS business_type_n,
       (select ccli.pattern
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') pattern,
       (select ccli.machine_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') machine_number,
       (select ccli.equipment_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') equipment_number,
       c.et_date,
       '所有权转移证明' send_object,
       cce.trans_send_date01 send_date,
       cce.trans_tracknum01 send_num,
       cce.trans_recipient01 recipients,
       null invoice_number,
       null vat_invoice_code,
       null total_amount,
       null cf_item_n,
       null invoice_date,
       null ele_invoice_hd_id,
       null ele_invoice_status
  from con_contract c, con_contract_et_document cce
 where c.contract_id = cce.contract_id

UNION ALL

select c.contract_id,
       c.contract_number,
       (select h.bp_code from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_code,
       (select h.bp_name from hls_bp_master h where h.bp_id = c.bp_id_tenant) bp_name,
       (select h.bp_code
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_code,
       (select h.extra_nam
          from hls_bp_master h
         where h.bp_id = c.bp_id_agent_level1) bp_agent_name,
       (Select d.description
          From hls_division d
         Where d.division = c.division) As division,
       (Select ch.lease_channel
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (select cc.lease_channel
                  from con_contract cc
                 where cc.contract_id = c.contract_id)) As lease_channel_desc,
       c.business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type = c.business_type) AS business_type_n,
       (select ccli.pattern
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') pattern,
       (select ccli.machine_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') machine_number,
       (select ccli.equipment_number
          from con_contract_lease_item ccli
         where ccli.contract_id = c.contract_id
           AND ccli.equipment_type = 'MAIN') equipment_number,
       c.et_date,
       '发票' send_object,
       aih.express_date send_date,
       aih.express_num send_num,
       aih.express_to recipients,
       (nvl(aih.invoice_number,
        (Select he.yfphm
           From acr_ele_invoice_hd he
          Where he.invoice_hd_id = aih.invoice_hd_id))) invoice_number,
       aih.vat_invoice_code vat_invoice_code,
       aih.total_amount total_amount,
       (select to_char(wm_concat(distinct(ci.description)))
                  from acr_invoice_ln        ail,
                       con_contract_cashflow ccc,
                       hls_cashflow_item     ci
                 where ail.invoice_hd_id = aih.invoice_hd_id
                   and ccc.cashflow_id = ail.cashflow_id
                   and ci.cf_item = ccc.cf_item) cf_item_n,
       TO_CHAR(aih.invoice_date, 'yyyy-mm-dd') AS invoice_date,
       (select aeih.ele_invoice_hd_id
                  from acr_ele_invoice_hd aeih
                 where aeih.invoice_hd_id = aih.invoice_hd_id) ele_invoice_hd_id,
       nvl((select nvl(aeih.status, 'NEW')
                  from acr_ele_invoice_hd aeih
                 where aeih.invoice_hd_id = aih.invoice_hd_id),'NEW') ele_invoice_status
  from con_contract c, acr_invoice_hd aih
 where c.contract_id = aih.contract_id
  and aih.invoice_kind in ('0','2')
  and aih.reversed_flag ='N'
                    ) t1 #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_code"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_agent_code"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="cf_item_n"/>
        <bm:field name="division"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_desc"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="equipment_number"/>
        <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="send_object"/>
        <bm:field name="send_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="send_num"/>
        <bm:field name="recipients"/>
        <bm:field name="invoice_number"/>
        <bm:field name="vat_invoice_code"/>
        <bm:field name="total_amount"/>
        <bm:field name="invoice_date"/>
        <bm:field name="ele_invoice_hd_id"/>
        <bm:field name="ele_invoice_status"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field field="lease_channel" queryOperator="="/>
        <bm:query-field field="send_object" queryOperator="="/>
        <bm:query-field field="business_type" queryOperator="="/>
        <bm:query-field field="send_num" queryOperator="like"/>
        <bm:query-field field="pattern" queryOperator="like"/>
        <bm:query-field field="machine_number" queryOperator="like"/>
        <bm:query-field name="et_date_from" queryExpression="to_char(et_date,&apos;yyyy-mm-dd&apos;)&gt;=${@et_date_from}"/>
        <bm:query-field name="et_date_to" queryExpression="to_char(et_date,&apos;yyyy-mm-dd&apos;)&lt;=${@et_date_to}"/>
        <bm:query-field name="send_date_from" queryExpression="to_char(send_date,&apos;yyyy-mm-dd&apos;)&gt;=${@send_date_from}"/>
        <bm:query-field name="send_date_to" queryExpression="to_char(send_date,&apos;yyyy-mm-dd&apos;)&lt;=${@send_date_to}"/>
    </bm:query-fields>
    <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:model>
