<?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
                    cc.bp_id_tenant_n name,
                    SUBSTR(t.item_frame_number, LENGTH(t.item_frame_number) - 5, LENGTH(t.item_frame_number)) standno,
                    hbm.phone usertel,
                    0 noticeuser,
                    DECODE(hbm.gender, 'MALE', 1, 'FEMALE', 2) usersex,
                    10380 companyid,
                    t.license_number idnum,
                    t.engine,
                    t.displacement,
                    acg.contactor stroelinkman,
                    acg.phone stroetel,
                    TO_CHAR(acg.install_date, 'yyyy-mm-dd') fixtime,
                    NULL instruction,
                    cc.lease_term
                    ||
                    (SELECT
                        hb.province_id_n
                    FROM
                        hls_bp_master_lv hb,
                        con_contract_bp cb
                    WHERE
                        hb.bp_id        = cb.bp_id AND
                        cb.bp_category  = 'AGENT' AND
                        cb.enabled_flag = 'Y' AND
                        cb.contract_id  = cc.contract_id
                    )
                    || t.model_id_n
                    || t.series_id_n remark,
                    10001 operator,
                    hbm.living_address homeAddress,
                    hbm.work_unit_address workAddress,
                    hbm.address_on_resident_booklit originAddress,
                    NULL groupName,
                    NULL groupVal,
                    NULL groupEmail,
                    NULL managerPhone,
                    NULL managerName,
                    1 deviceNumber,
                    TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') TIMESTAMP,
                    sys_login_pkg.md5(sys_login_pkg.md5(${@token_key})
                    ||TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')) SIGN
                FROM
                    con_contract_lv cc,
                    con_contract_lease_item_lv t,
                    hls_bp_master hbm,
                    ast_car_gps acg
                WHERE
                    cc.contract_id  = t.contract_id AND
                    cc.bp_id_tenant = hbm.bp_id AND
                    cc.contract_id  = acg.contract_id AND
                    cc.contract_id  =${@contract_id}
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
