amf.public

Parameters

Name	Type	Mode
_truck_id	uuid	IN
_loader_serial	text	IN
_loader_id	uuid	IN
_day_id	integer	IN
_truck_volume	numeric	IN
_loader_volume	numeric	IN
_loading_time	integer	IN
_load_time_coef	numeric	IN
_max_distance	integer	IN

Definition

WITH distance_data AS (
    WITH loader_centroids AS (
        WITH motionless_data AS (
        SELECT
                id,
                type,
                centroid AS motionless_centroid,
                start_date,
                duration
            FROM sms_truck_move AS move_data
            WHERE truck_id = _truck_id
                AND day_id = _day_id
                AND type = 'motionless'
                AND duration >= (_truck_volume / _loader_volume) * _loading_time * _load_time_coef
    )

SELECT
            *,
            (
                SELECT
                    ST_Centroid(ST_UNION(coord)) AS centroid
                FROM sms_teltonika_gps_data
                WHERE serial_number = _loader_serial AND time > (motionless_data.start_date - INTERVAL '10 second') AND time < (motionless_data.start_date + (motionless_data.duration + 10) * INTERVAL '1 second')
            ) AS centroid
        FROM motionless_data
    )

SELECT
        id,
     CASE
            WHEN centroid IS NULL THEN NULL
        ELSE ST_DistanceSphere(motionless_centroid, centroid)
     END AS distance
    FROM loader_centroids
)

UPDATE sms_truck_move 
SET type = 'loading', loader_id = _loader_id
FROM distance_data
WHERE sms_truck_move.id = distance_data.id AND distance_data.distance <= _max_distance

sms_update_truck_loading_data(IN _truck_id uuid, IN _loader_serial text, IN _loader_id uuid, IN _day_id integer, IN _truck_volume numeric, IN _loader_volume numeric, IN _loading_time integer, IN _load_time_coef numeric, IN _max_distance integer)

Parameters

Definition