amf.public

Parameters

Name	Type	Mode
_serial_number	text	IN
_day_id	integer	IN
_tenant_id	uuid	IN
_site_id	uuid	IN

Definition

WITH
    gps AS (
        SELECT
            id,
            time,
            (
                SELECT sd.id
                FROM sms_shift_day AS sd
                LEFT JOIN sms_shift AS s ON s.id = sd.shift_id
                LEFT JOIN tenants AS t on t.id = _tenant_id
                WHERE time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone >= TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.start_hour, ':', sd.start_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone < TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.end_hour, ':', sd.end_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND SUBSTRING(REVERSE(sd.day_mask::BIT(7)::VARCHAR), EXTRACT(isodow from time)::INT, 1) = '1'
                AND
                (
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
                    ) OR
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to IS NULL
                    )
                )
                AND s.tenant_id = _tenant_id
                AND sd.tenant_id = _tenant_id
                AND s.site_id = _site_id
                LIMIT 1
            ) AS shift_day_id,
            (
                SELECT sd.name
                FROM sms_shift_day AS sd
                LEFT JOIN sms_shift AS s ON s.id = sd.shift_id
                LEFT JOIN tenants AS t on t.id = _tenant_id
                WHERE time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone >= TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.start_hour, ':', sd.start_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND time::timestamp AT TIME ZONE (SELECT current_setting('TIMEZONE')) AT TIME ZONE t.time_zone < TO_TIMESTAMP(CONCAT(TO_DATE(_day_id::TEXT, 'YYYYMMDD'), ' ', sd.end_hour, ':', sd.end_minute, ':', 0), 'YYYY-MM-DD HH24:MI:SS')
                AND SUBSTRING(REVERSE(sd.day_mask::BIT(7)::VARCHAR), EXTRACT(isodow from time)::INT, 1) = '1'
                AND
                (
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to > TO_DATE(_day_id::TEXT, 'YYYYMMDD')
                    ) OR
                    (
                        s.valid_from <= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND
                        s.valid_to IS NULL
                    )
                )
                AND s.tenant_id = _tenant_id
                AND sd.tenant_id = _tenant_id
                AND s.site_id = _site_id
                LIMIT 1
            ) AS shift_day_name,
            ST_DistanceSphere(coord, LEAD(coord, 1) OVER (ORDER BY time)) AS delta_distance,
            EXTRACT(EPOCH FROM (LEAD(time, 1) OVER (ORDER BY time) - time)) AS delta_time,
            LEAD(alt, 1) OVER (ORDER BY time) - alt AS delta_alt
        FROM sms_teltonika_gps_data
        WHERE time >= TO_DATE(_day_id::TEXT, 'YYYYMMDD') AND time < TO_DATE(_day_id::TEXT, 'YYYYMMDD') + 1 AND
        tenant_id = _tenant_id AND serial_number = _serial_number
        ORDER BY time
    )

INSERT INTO sms_tgd_computed_info(id, tenant_id, delta_time, delta_alt, delta_distance, computed_speed, day_id, shift_day_id, shift_day_name)

SELECT
        gps.id AS id,
        _tenant_id AS tenant_id,
        CASE
            WHEN gps.delta_time IS NULL THEN 0.00
            ELSE ROUND((gps.delta_time)::numeric, 2)
        END AS delta_time,
        gps.delta_alt,
        CASE
            WHEN gps.delta_distance IS NULL THEN 0.00
            ELSE ROUND((gps.delta_distance)::numeric, 2)
        END AS delta_distance,
        CASE
            WHEN gps.delta_distance IS NULL THEN 0.00
            ELSE ROUND((3.6 * (gps.delta_distance / gps.delta_time))::numeric, 2)
        END AS computed_speed,
        _day_id AS day_id,
        shift_day_id,
        shift_day_name
    FROM gps

sms_insert_computed_info(IN _serial_number text, IN _day_id integer, IN _tenant_id uuid, IN _site_id uuid)

Parameters

Definition