3XDkDXds

1DROP FUNCTION IF EXISTS sessioncounttoday(devices text[], timebucket text);
2CREATE OR REPLACE FUNCTION sessioncounttoday(devices text[] timebucket text) 
3RETURNS TABLE
4(
5	tm timestamp,
6    count double precision
7) AS
8$$
9
10DECLARE device text;
11BEGIN
12
13	DROP TABLE IF EXISTS sessioncount_temp_today;
14	CREATE TEMP TABLE sessioncount_temp_today(time timestamp,count DOUBLE PRECISION);
15
16	FOREACH device IN ARRAY devices LOOP
17		IF device LIKE 'D3V1C3-%' THEN
18			device := (SELECT REPLACE(device, 'D3V1C3-',''));
19
20			INSERT INTO sessioncount_temp_today ("time","count") (SELECT timebucket AS "time", Count(1) AS "sessions" FROM sca_sessionregister WHERE "time" > CURRENT_DATE AND "SCAId" = device GROUP BY 1 ORDER BY 1 ASC);
21
22		ELSE
23
24			INSERT INTO sessioncount_temp_today ("time","count") (SELECT timebucket AS "time", Count(1) AS "sessions" FROM ocpp_sessionregister WHERE "time" > CURRENT_DATE AND "chargeBoxId" = device AND "connectorId" IN(SELECT DISTINCT("connectorId") FROM ocpp_evsestatus WHERE "connectorId" IS NOT NULL AND "chargeBoxId" = device) GROUP BY 1 ORDER BY 1 ASC);
25		END IF;
26	END LOOp;
27
28	RETURN QUERY SELECT * FROM sessioncount_temp_today;
29
30END$$ LANGUAGE plpgsql;