From 7c21bc6c6d7c43c2014d69043777ce833cf62c4c Mon Sep 17 00:00:00 2001
From: Dennis Eriksen <d@ennis.no>
Date: Tue, 4 Oct 2022 07:27:38 +0200
Subject: adding grid tariff from elvia

---
 elvia_gridtariff2pgsql.py | 94 +++++++++++++++++++++++++++++++++++++++++++++++
 env.sample                |  2 +
 schema.sql                | 26 +++++++++++++
 3 files changed, 122 insertions(+)
 create mode 100755 elvia_gridtariff2pgsql.py

diff --git a/elvia_gridtariff2pgsql.py b/elvia_gridtariff2pgsql.py
new file mode 100755
index 0000000..b72f7de
--- /dev/null
+++ b/elvia_gridtariff2pgsql.py
@@ -0,0 +1,94 @@
+#!/bin/python3
+
+import os
+import sys
+import json
+import psycopg2
+import requests
+
+from datetime import datetime
+from datetime import timedelta
+from tzlocal import get_localzone
+
+
+apiKey = os.environ['elvia_grid_api_key']
+apiUrl = "https://elvia.azure-api.net/grid-tariff/digin/api/1/tariffquery/meteringpointsgridtariffs"
+meteringPointId = os.environ['meteringPointId']
+
+pg_db = os.environ['pg_db']
+pg_host = os.environ['pg_host']
+pg_table = "elvia_gridtariff"
+
+
+startTime = datetime.now(get_localzone()) - timedelta(days = 2)
+startTime = startTime.strftime('%Y-%m-%d')
+
+endTime = datetime.now(get_localzone()) + timedelta(days = 1)
+endTime = endTime.strftime('%Y-%m-%d')
+
+
+### Get the data
+try:
+    url = apiUrl
+    # Request headers
+    hdr = {
+        'Cache-Control': 'no-cache',
+        'X-API-Key': apiKey,
+        'Content-Type': 'application/json'
+    }
+
+    # Request body
+    body = {
+        'starttime': startTime,
+        'endtime': endTime,
+        'meteringPointIds': [ meteringPointId ]
+    }
+
+    response = requests.post(url, headers=hdr, json=body)
+    if response.status_code != 200:
+        print(response.status_code)
+        print("Oh shit")
+        response.raise_for_status()
+
+except Exception as e:
+    print("oh lol")
+    sys.exit(e)
+
+data = response.json()
+
+print("Got "+ str(len(data['gridTariffCollections'][0]['gridTariff']['tariffPrice']['hours'])) +" items from between "+ startTime + " and " + endTime)
+
+
+### insert data into database
+
+values = []
+for item in data['gridTariffCollections'][0]['gridTariff']['tariffPrice']['hours']:
+    values.append((meteringPointId, item['startTime'], item['expiredAt'], item['shortName'], item['isPublicHoliday'], item['energyPrice']['total'], item['energyPrice']['totalExVat']))
+
+# connect to database
+conn = psycopg2.connect(database=pg_db, host=pg_host)
+cur = conn.cursor()
+
+# count rows before we start
+cur.execute("SELECT COUNT(*) FROM " + pg_table)
+before = cur.fetchone()
+
+# insert data
+try:
+    cur.executemany("INSERT INTO " + pg_table + " VALUES(%s,%s,%s,%s,%s,%s,%s) ON CONFLICT (meteringPointId,startTime,endTime) DO NOTHING", values)
+    conn.commit()
+except Exception as e:
+    conn.rollback()
+    raise e
+
+# count rows after we finnish
+cur.execute("SELECT COUNT(*) FROM " + pg_table)
+after = cur.fetchone()
+
+# count *new* rows
+newRows = after[0] - before[0]
+
+# close connection
+conn.close()
+
+print("Successfully inserted " + str(newRows) + " records into the database")
diff --git a/env.sample b/env.sample
index b54a9c7..949e69d 100644
--- a/env.sample
+++ b/env.sample
@@ -1,4 +1,6 @@
+export metringPointId=XXX
 export elvia_token=XXX
+export elvia_grid_api_key=XXX
 
 export pg_db=XXX
 export pg_host=XXX
diff --git a/schema.sql b/schema.sql
index 89d8e77..e1ac14c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -37,6 +37,24 @@ CREATE TABLE public.elvia (
 
 ALTER TABLE public.elvia OWNER TO dennis;
 
+--
+-- Name: elvia_gridtariff; Type: TABLE; Schema: public; Owner: dennis
+--
+
+CREATE TABLE public.elvia_gridtariff (
+    meteringpointid bigint NOT NULL,
+    starttime timestamp with time zone NOT NULL,
+    endtime timestamp with time zone NOT NULL,
+    shorttime character varying(9),
+    ispublicholiday boolean DEFAULT false NOT NULL,
+    price numeric,
+    priceexvat numeric,
+    inserted timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+ALTER TABLE public.elvia_gridtariff OWNER TO dennis;
+
 --
 -- Name: entsoe; Type: TABLE; Schema: public; Owner: dennis
 --
@@ -129,6 +147,14 @@ ALTER TABLE ONLY public.elvia
     ADD CONSTRAINT el_pkey PRIMARY KEY (starttime, endtime);
 
 
+--
+-- Name: elvia_gridtariff elvia_gridtariff_pkey; Type: CONSTRAINT; Schema: public; Owner: dennis
+--
+
+ALTER TABLE ONLY public.elvia_gridtariff
+    ADD CONSTRAINT elvia_gridtariff_pkey PRIMARY KEY (meteringpointid, starttime, endtime);
+
+
 --
 -- Name: entsoe entsoe_pkey; Type: CONSTRAINT; Schema: public; Owner: dennis
 --
-- 
cgit v1.2.3