FxhmSBtr

1DROP FUNCTION IF EXISTS sessioncountdynamic(devices text[], durationtype text);
2CREATE OR REPLACE FUNCTION sessioncountdynamic(devices text[], durationtype text) 
3RETURNS TABLE
4(
5	tm timestamp,
6    sessions double precision
7) AS
8$$
9
10DECLARE device text;
11BEGIN
12	DROP TABLE IF EXISTS sessioncount_temp_dynamic;
13	CREATE TEMP TABLE sessioncount_temp_dynamic(time timestamp,sessions DOUBLE PRECISION);
14
15	FOREACH device IN ARRAY devices LOOP
16		IF device LIKE 'D3V1C3-%' THEN
17			device := (SELECT REPLACE(device, 'D3V1C3-',''));
18			IF durationtype = 'week' THEN
19				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM sca_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '7 DAYS' ) AND "SCAId" = device GROUP BY 1 ORDER BY 1 ASC);
20			ELSEIF durationtype = 'month' THEN
21				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM sca_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '30 DAYS' ) AND "SCAId" = device GROUP BY 1 ORDER BY 1 ASC);
22			ELSEIF durationtype = 'year' THEN
23				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM sca_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '365 DAYS' ) AND "SCAId" = device GROUP BY 1 ORDER BY 1 ASC);
24			ELSE
25				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT current_timestamp - interval '1 day'  AS "time", Count(1) AS "sessions" FROM sca_sessionregister WHERE "SCAId" = device GROUP BY 1 ORDER BY 1 ASC);
26			END IF;
27
28		ELSE
29			IF durationtype = 'week' THEN
30				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM ocpp_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '7 DAYS') 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);
31			ELSEIF durationtype = 'month' THEN
32				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM ocpp_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '30 DAYS') 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);
33			ELSEIF durationtype = 'year' THEN
34				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT "time", Count(1) AS "sessions" FROM ocpp_sessionregister WHERE "time" > (CURRENT_DATE - INTERVAL '365 DAYS') 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);
35			ELSE
36				INSERT INTO sessioncount_temp_dynamic ("time","sessions") (SELECT current_timestamp - interval '1 day' AS "time", Count(1) AS "sessions" FROM ocpp_sessionregister WHERE "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);
37			END IF;
38
39			
40		END IF;
41	END LOOp;
42
43	RETURN QUERY SELECT * FROM sessioncount_temp_dynamic;
44
45END$$ LANGUAGE plpgsql;