owl_SQLite.bat

set be_db_path="C:\Documents and Settings\All Users\Application Data\2SE"
set tool_path=C:\tools\OWL

copy /y %be_db_path%\be.db %tool_path%\
cd /d C:\tools\OWL 
powershell.exe C:\tools\OWL\owl_SQLite.ps1

rem php %tool_path%\owl_SQLite.phpset be_db_path="C:\Documents and Settings\All Users\Application Data\2SE"
set tool_path=C:\tools\OWL

copy /y %be_db_path%\be.db %tool_path%\
cd /d C:\tools\OWL 
powershell.exe C:\tools\OWL\owl_SQLite.ps1

rem php %tool_path%\owl_SQLite.php

owl_SQLite.ps1

#設定ファイル読み込み
$setting_ini = Get-Content "owl_setting.ini"
$url = $setting_ini[1]
$tpath = $setting_ini[3]
$dpath = $setting_ini[5]
$bit = $setting_ini[7]
$email = $setting_ini[9]
$apikey = $setting_ini[11]

#SQLite用DLL
$dll = $tpath + "\" + $bit + "\System.Data.SQLite.dll"

#SQLiteデータベースファイル
$ds = $dpath + "\be.db"

#前回最終日時保存ファイル
$ini = Get-Content "owl.ini"
$year = $ini[0]
$month = ("0" + $ini[1]).Substring(("0" + $ini[1]).Length - 2,2)
$day = ("0" + $ini[2]).Substring(("0" + $ini[2]).Length - 2,2)
$hour = ("0" + $ini[3]).Substring(("0" + $ini[3]).Length - 2,2)
$min = ("0" + $ini[4]).Substring(("0" + $ini[4]).Length - 2,2)

$date_now = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

$sql = "select * from energy_history where dt > '" + $year + "-" + $month + "-" + $day + " " + $hour + ":" + $min + ":59' and dt < '" + $date_now + "'"

#System.Data.SQLite http://system.data.sqlite.org
[System.Reflection.Assembly]::LoadFile($dll)
$sqlite = New-Object System.Data.SQLite.SQLiteConnection
$sqlite.ConnectionString = "Data Source = " + $ds
$sqlite.Open()
$sqlcmd = New-Object System.Data.SQLite.SQLiteCommand
$sqlcmd.Connection = $sqlite
$sqlcmd.CommandText = $sql
echo $sql
$result = $sqlcmd.ExecuteReader()
echo $url
While($result.Read()){
    $addr = $result[0]
    $year = $result[1]
    $month = $result[2]
    $day = $result[3]
    $hour = $result[4]
    $min = $result[5]
    $ch1_amps_avg = $result[6]
    $ch1_kw_avg = $result[7]
    $ghg = $result[8]
    $cost = $result[9]
    $ch1_amps_min = $result[10]
    $ch1_amps_max = $result[11]
    $ch1_kw_min = $result[12]
    $ch1_kw_max = $result[13]
    $dt = ([Datetime]$result[14]).ToString("yyyy-MM-dd HH:mm:ss")
    $timestamp = [string]$year + [string]('0' + $month).Substring(('0' + $month).Length - 2,2) + [string]('0' + $day).Substring(('0' + $day).Length - 2,2) + [string]('0' + $hour).Substring(('0' + $hour).Length - 2,2) + [string]('0' + $min).Substring(('0' + $min).Length - 2,2)

    echo $dt
   
    $postdata = "addr=" + $addr + "&year=" + $year + "&month=" + $month + "&day=" + $day + "&hour=" + $hour + "&min=" + $min + "&ch1_amps_avg=" + $ch1_amps_avg + "&ch1_kw_avg=" + $ch1_kw_avg + "&ghg=" + $ghg + "&cost=" + $cost + "&ch1_amps_min=" + $ch1_amps_min + "&ch1_amps_max=" + $ch1_amps_max + "&ch1_kw_min=" + $ch1_kw_min + "&ch1_kw_max=" + $ch1_kw_max + "&dt=" + $dt + "&timestamp=" + $timestamp + "&email=" + $email + "&apikey=" + $apikey
    echo $postdata

    #SSL Validation Check
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem) {
            return true;
        }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy


    $r =  Invoke-WebRequest -uri $url -Method Post -Body $postdata -TimeoutSec 30
    echo $r.InnerText


    #Start-Sleep -s 1

}

$sqlite.Close()

#最終読み込み日時保存
$year | Out-File "owl.ini"
("0" + $month).Substring(("0" + $month).Length - 2,2) | Add-Content "owl.ini"
("0" + $day).Substring(("0" + $day).Length - 2,2) | Add-Content "owl.ini"
("0" + $hour).Substring(("0" + $hour).Length - 2,2) | Add-Content "owl.ini"
("0" + $min).Substring(("0" + $min).Length - 2,2) | Add-Content "owl.ini"