con_contract_query.lwm 8.59 KB
Newer Older
congzhao's avatar
congzhao committed
1 2 3 4 5
<?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[
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
select *
  from (select t1.contract_id,
               t1.contract_number,
               t1.contract_status,
               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,
               t1.ec_sign_type,
               decode(t1.ec_sign_type,
                      'INITIATE',
                      '待签约',
                      'SIGNING',
                      '待签约') AS ec_sign_type_n,
               (select hm.id_card_no
                  from hls_bp_master hm
                 where hm.bp_id = t1.bp_id_tenant) id_card_no_tenant,
               t1.bp_id_agent_level1,
               (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,
30 31
               decode(t1.data_class,
                      'NORMAL',
32
                      '合同签约',
33
                      'CHANGE_REQ',
34 35 36 37 38
                      '提前结清') contract_status_n,
               (select ci.brand_id
                  from con_contract_lease_item ci
                 where ci.contract_id = t1.contract_id
                   and ci.equipment_type = 'MAIN') brand_id,
39 40
               decode(t1.division,70,(select a.brand_value          --update by xsh35973 20220323
                   from CON_CONTRACT_LEASE_ITEM a
gzj34291's avatar
gzj34291 committed
41 42 43 44 45
                   where a.contract_id = t1.contract_id),
                    92,
                      (select a.brand_value --update by 34291
                         from CON_CONTRACT_LEASE_ITEM a
                        where a.contract_id = t1.contract_id),NVL((SELECT a.description
46 47 48 49 50 51
                     FROM hls_car_brands_vl a
                    WHERE a.brand_id =
                          (select ci.brand_id
                             from con_contract_lease_item ci
                            where ci.contract_id = t1.contract_id
                              and ci.equipment_type = 'MAIN')),
52
                   '日立')) brand_id_n,
53 54 55 56
               (select ci.modelcd
                  from con_contract_lease_item ci
                 where ci.contract_id = t1.contract_id
                   and ci.equipment_type = 'MAIN') modelcd, --机型
gzj34291's avatar
gzj34291 committed
57
              decode(t1.division,70,(SELECT i.machine_number_70
58 59
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
gzj34291's avatar
gzj34291 committed
60
                   AND i.equipment_type = 'MAIN'),92,(SELECT i.machine_number_70
61 62
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
gzj34291's avatar
gzj34291 committed
63
                   AND i.equipment_type = 'MAIN'),(SELECT i.machine_number
64 65
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
66
                   AND i.equipment_type = 'MAIN')) machine_number, --机号
67
               t1.lease_times, --期数
68
               t1.lease_start_date, --起租日
69 70
               (NVL(t1.down_payment, 0) + NVL(t1.residual_value, 0) +
               NVL(t1.total_rental, 0)) total_sign,
71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
               (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,
89 90 91 92 93 94 95
               (SELECT nvl(cs.ec_sign_status, 'N')
                  from con_contract_ele_signer cs
                 where cs.contract_id = t1.contract_id
                   and cs.id_no = ${@id_no}
                   and cs.id_type = ${@id_type}
                   and rownum = 1) userSignStatus
          from con_contract t1
96
         where t1.data_class in ('NORMAL','CHANGE_REQ')
97
           and t1.ec_sign_type in ('INITIATE', 'SIGNING')
98
           and t1.sign_type = 'ELE_SIGN'
99
           and exists (select 1 from con_contract_ele_flow cf where cf.contract_id = t1.contract_id)
100
           and exists (select 1 from con_contract_ele_signer csr where csr.contract_id = t1.contract_id and csr.sign_url is not null and csr.id_no in (${@id_no},${@organization_id}))
101 102
           and ((${@account_type} = 'PE' and exists
                (select 1
103 104
                    from con_contract_bp ccb, con_contract_ele_signer cs
                   where ccb.bp_id = cs.bp_id
105
                     and ccb.contract_id = t1.contract_id
106 107 108
                     and cs.esign_type = decode(t1.data_class,'NORMAL','CE','CHANGE_REQ','ET')
                     and ((ccb.bp_category in ('TENANT', 'GUARANTOR') and t1.data_class = 'NORMAL')
                         or ccb.bp_category = 'TENANT' and t1.data_class = 'CHANGE_REQ')
109
                     and (ccb.id_card_no = ${@id_no} or
110
                         (ccb.id_no_sp = ${@id_no} and ccb.sp_sign_flag = 'Y')))) or
111 112 113 114
               (${@account_type} = 'AG' and exists
                (select 1
                    from hls_bp_master_signer hs
                   where hs.hls_bp_signer_id = t1.signer
115
                     and hs.id_card = ${@id_no})
116 117 118
             and exists (select 1 from hls_bp_master hbm
             where hbm.bp_id = t1.bp_id_tenant
             and hbm.social_code = ${@organization_id})
119 120 121 122 123 124 125 126 127
                 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})
128 129 130
                 and exists (select 1 from hls_bp_master hbm
                 where hbm.bp_id = t1.bp_id_tenant
                 and hbm.social_code = ${@organization_id})
131 132
                 and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
133 134 135 136 137 138 139 140 141 142
                  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}
143 144 145 146 147 148 149
                     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
150
                #WHERE_CLAUSE#
congzhao's avatar
congzhao committed
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
            ]]></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="bp_id_agent_level1_n"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="ec_sign_type"/>
        <bm:field name="ec_sign_type_n"/>
        <bm:field name="brand_id_n"/>
        <bm:field name="modelcd"/>
        <bm:field name="machine_number"/>
        <bm:field name="lease_times"/>
167
        <bm:field name="lease_start_date"/>
congzhao's avatar
congzhao committed
168 169
        <bm:field name="total_sign"/>
        <bm:field name="sign_url"/>
170
        <bm:field name="userSignStatus"/>
congzhao's avatar
congzhao committed
171 172
    </bm:fields>
    <bm:query-fields>
173
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
congzhao's avatar
congzhao committed
174 175
    </bm:query-fields>
</bm:model>