aonestar.public

Parameters

Name	Type	Mode
i_register_id	integer	IN

Definition

WITH pin AS (
        SELECT d.pin_code, d.valid_from, d.valid_until, d.extra_doors
        FROM intf_doorlock_logs d
        WHERE d.register_id = i_register_id
          AND d.credential_type = 'PIN'
          AND d.status = 'P'
        ORDER BY d.log_time DESC
        LIMIT 1
    ), qr AS (
        SELECT d.qr_payload, d.qr_url, d.valid_from, d.valid_until
        FROM intf_doorlock_logs d
        WHERE d.register_id = i_register_id
          AND d.credential_type = 'QR'
          AND d.status = 'P'
        ORDER BY d.log_time DESC
        LIMIT 1
    )
    SELECT NULLIF(jsonb_strip_nulls(
               jsonb_build_object(
                   'pin_code',    p.pin_code,
                   'valid_from',  COALESCE(p.valid_from,  q.valid_from),
                   'valid_until', COALESCE(p.valid_until, q.valid_until),
                   'qr_payload',  q.qr_payload,
                   'qr_url',      q.qr_url)
               || COALESCE(p.extra_doors, '{}'::jsonb)), '{}'::jsonb)
    FROM (SELECT 1) one
    LEFT JOIN pin p ON true
    LEFT JOIN qr  q ON true

fn_doorlock_credential

Description

Parameters

Definition