. C:\Scripts\write-log.ps1

$logfile = "C:\Scripts\logs\spotprices.log"

Write-Log -path $logFile -level Info -tag Script -msg "Script starting"

 

Import-Module SimplySql

$dbUsername = "usrname"

$dbPass = "password"

$database = "database"

$sqlHost = "sqlhost"

 

Write-Log -path $logFile -level Info -tag Script -msg "Opening MySQL Connection"

Open-MySqlConnection -Server $sqlHost -Database $database -UserName $dbUsername -Password $dbPass -ConnectionName "graph"

 

$curTimezone = (Get-Date -Format tz).Replace("+","-")

$start = $(Get-Date((Get-Date).AddDays(1)) -Format yyyy-MM-dd)

$end = $(Get-Date((Get-Date).AddDays(2)) -Format yyyy-MM-dd)

 

Write-Log -path $logFile -level Info -tag Script -msg "curTimezone: $curTimezone`tStart: $start`tEnd:`t$end"

 

 

$newUrl = "https://api.energidataservice.dk/dataset/Elspotprices?offset=0&start=$($start)T00:00&end=$($end)T00:00&filter=%7B%22PriceArea%22:[%22DK1%22]%7D&sort=HourUTC%20DESC&timezone=dk"

$response = $null

try{

    Write-Log -path $logFile -level Info -tag WebRequest -msg "Calling energidataservice.dk"

    $response = Invoke-RestMethod $newUrl -Method 'GET'

}catch {

    Write-Host "fucked up"

    Write-Host $Error

    Write-Log -path $logFile -level Error -tag WebRequest -msg "Failed with calling energidataservice.dk"

 

$count = 0

foreach ($i in $response.records){

    $count++

    # Er udelukkende fordi PS og grafana bruger den tidszone man i er ift unixtime stmap. Dvs

    # at den unixtimestmap der REELT st�r for en time, ikke er den. Alts� det er HourDK der passer og det

    # er s� lagt en time til unixtimestmap f�r det passer.. Tror jeg.. H�ber jeg.. Passer nu i grafana og set-.temp!

    # okay jeg fatter faktisk ikke helt hvorfor det passer... men det passer, WHO CARES!

    $ourTime = (Get-date($i.HourDK)).addhours($curTimezone)

    #$ourTime = (Get-date($i.HourDK))

    $spotHourUnix = Get-date($ourTime) -UFormat %s -Millisecond 0

    $unixtimestamp = $spotHourUnix

    $kwpricedkk = [math]::Round($i.SpotPriceDKK / 1000,2)

    $HourUTC = $i.HourUTC

    $HourDK = $i.HourDK

    $SpotPriceDKK = [math]::Round(($i.SpotPriceDKK),2)

 

    Write-Log -path $logFile -level Info -tag Data -msg "unixtimestamp: $unixtimestamp`tkwpricedkk: $kwpricedkk`tHourUTC: $HourUTC`tHourDK: $HourDK`tSpotPriceDKK: $SpotPriceDKK"

    $query = "INSERT INTO elspotprices (id, unixtimestamp, kwpricedkk, HourUTC, HOURDK, SpotPriceDKK) VALUES (NULL, '$unixtimestamp', '$kwpricedkk','$HourUTC','$HourDK','$SpotPriceDKK')"

    Write-Log -path $logFile -level Info -tag Data -msg "Running Query: $query"

    Invoke-SqlQuery $query -ConnectionName "graph"

Write-Log -path $logFile -level Info -tag Data -msg "Count: $count`tStart: $start`tEnd: $End"

Write-Log -path $logFile -level Info -tag Script -msg "Script ending"