maya.public

Parameters

Name	Type	Mode
_idmayauser	integer	IN
active	boolean	IN
includefilter	boolean	IN
alerttype	integer	IN
alertid	bigint	IN
alertoffset	integer	IN

Definition

DECLARE
start_date timestamptz;
end_date timestamptz;
res json;
BEGIN
/* Get user alerts */
/*When was the most recent alert? */
SELECT al.alertdate INTO end_date FROM mvw_mayauser_device mud join alert_last al ON al.iddevice = mud.iddevice WHERE mud.idmayauser =_idmayauser ORDER BY al.alertdate desc limit 1;
start_date := end_date - '12 hours'::interval;
/* Build the json to return */
 WITH Q(unit,device,alertconf,alerts) as (SELECT row_to_json(u)::text as unit,row_to_json(d)::text as device,row_to_json(ac)::text as alertconf,array_agg(a.*) as alerts
 FROM
 mayaUser mu
 JOIN mvw_mayauser_device mud ON mud.idmayauser = mu.idmayauser
 JOIN device d ON d.iddevice = mud.iddevice
 JOIN unit u ON u.idunit = d.idunit
 JOIN alertconf ac ON ac.idclient = mu.idclient
 JOIN alert a ON (a.idalertconf = ac.idalertconf AND a.iddevice = d.iddevice)
    WHERE
  mu.idmayauser = _idmayauser
  AND a.alertdate BETWEEN start_date and end_date
 GROUP BY 1,2,3
) select row_to_json(q) INTO res from q;
RETURN res;
END;

ot_ro_getalertswithcondition(integer, boolean, boolean, integer, bigint, integer)

Parameters

Definition