maya.public

Parameters

Name	Type	Mode
_idmayauser	integer	IN
_idclient	integer	IN
_begindate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
idunit	integer	OUT
alias	character varying	OUT
vehicletype	character varying	OUT
unitplate	character varying	OUT
unitmodel	character varying	OUT
clientcode	character varying	OUT
aliasshipper	character varying	OUT
producttype	character varying	OUT
latitude	numeric	OUT
longitude	numeric	OUT
posts	timestamp with time zone	OUT
deviceserial	character varying	OUT
devicemodel	character varying	OUT
location	character varying	OUT
city	character varying	OUT
state	character varying	OUT
georeference	character varying	OUT
pdate	timestamp with time zone	OUT
speed	real	OUT
odometer	numeric	OUT
status	character varying	OUT
idevent	bigint	OUT
receivedate	timestamp with time zone	OUT
reason	character varying	OUT
ignitionon	boolean	OUT
direction	real	OUT
satellites	integer	OUT
hdop	real	OUT
unitstatus	character varying	OUT
switches	character varying	OUT
temp1	real	OUT
temp2	real	OUT
temp3	real	OUT

Definition

DECLARE
begin_threshold timestamptz;
end_threshold timestamptz;
maxposdelay interval;
BEGIN
/*Optimization to take advantage of event table partitioning */
maxposdelay := '1 day'::interval;
begin_threshold := _begindate - maxposdelay;
end_threshold := _enddate + maxposdelay;

RETURN QUERY(SELECT u.idunit,u.alias,vt.id::varchar,up.platenum,coalesce(v.model,tr.model),c.clientcode,u.alias_shipper,(pr.idproduct::varchar||' '||pr.alias)::varchar,e.latitude,e.longitude,e.senddate,d.serialnumber,(dm.idmodeldevice::varchar||' '||dm.name::varchar)::varchar,((e.location->'ciudad')::varchar||' '||coalesce(e.location->'localidad','')::varchar||' '||(e.location->'estado')::varchar)::varchar,(e.location->'ciudad')::varchar,(e.location->'estado')::varchar,nullif((e.location->'localidad')::varchar,'null')::varchar,e.senddate,e.speed,e.odometer,d.status::varchar,e.idevent,e.receivedate,(er.id::varchar||' '||er.description::varchar)::varchar,e.ignition_on,e.direction,(e.otros->'SATELLITES')::int,(e.otros->'HDOP')::real,u.status::varchar,(otros->'switches')::varchar,(otros->'TEMP_SENSOR_1')::real,(otros->'TEMP_SENSOR_2')::real,(otros->'TEMP_SENSOR_1')::real FROM event e JOIN device d ON d.iddevice = e.iddevice JOIN devicemodel dm ON dm.idmodeldevice = d.idmodeldevice JOIN technicalaccount ta ON ta.idtechnicalaccount = d.idtechnicalaccount JOIN product pr ON pr.idproduct = ta.idproduct JOIN unit u ON u.idunit = d.idunit JOIN client c ON c.idclient = u.idclient JOIN mvw_mayauser_device mud ON mud.iddevice = d.iddevice LEFT JOIN vehicle v ON v.idunit = u.idunit LEFT JOIN trailer tr ON tr.idunit = u.idunit LEFT JOIN catalogs.vehicle_vehicletype vt ON vt.id = v.vehicletype LEFT JOIN catalogs.trailer_trailertype tt ON tt.id = tr.trailertype LEFT JOIN unitplate up ON u.idunit = up.idunit LEFT JOIN catalogs.device_securitystatus dss ON dss.id = e.securitystatus LEFT JOIN catalogs.event_reason er ON er.id = e.reason WHERE e.senddate BETWEEN begin_threshold AND end_threshold AND e.creationdate BETWEEN _begindate AND _enddate AND mud.idmayauser = _idmayauser);

END;

ot_ro_pos_customerds(integer, integer, timestamp with time zone, timestamp with time zone)

Parameters

Definition