Add-Type -Path (Get-Item "..\Bin\Elsinore.ScreenConnect.Core.dll").FullName
Add-Type -Path (Get-Item "..\Bin\Elsinore.ScreenConnect.Server.dll").FullName

$serializer = New-Object System.Xml.Serialization.XmlSerializer([Elsinore.ScreenConnect.Session])
$sessionSummaries = @()

foreach ($sessionFile in Get-ChildItem("Session"))
{
	$stream = [System.IO.File]::OpenRead($sessionFile.FullName)
	$session = $serializer.Deserialize($stream)
	$sessionSummary = New-Object Elsinore.ScreenConnect.SessionSummary
	$sessionSummary.RefreshFromSession($session)
	$sessionSummaries = $sessionSummaries + $sessionSummary
}

$sessionSummaries | Export-Csv -Path "report.csv"

Set-ExecutionPolicy Unrestricted

if (!(test-path -path "Archiv")) { new-item "Archiv" -type directory}
$towrite = $false
[array]$logItems = @()
foreach ($sessionFile in Get-ChildItem("Session"))
{
	$xml = [xml](get-content $sessionFile.FullName)
	[timespan]$gDiff = 0
	[timespan]$hDiff = 0
	$endedSession = "Never"
        $xml.session.Event | foreach {
                $tTime = $_.Time
		if ($_.EventType -eq "EndedSession") {
			$endedSession = [datetime]$_.Time
		}
                if ($_.ProcessType -eq "Host") {
			switch ($_.EventType) {
				"Connected" {
					$hostConnected = $tTime
				} 
				"Disconnected"   {
					$tDiff = [datetime]$tTime - [datetime]$hostConnected
					$hDiff = $hDiff + $tDiff
				}
			}
		} elseif ($_.ProcessType -eq "Guest") {
			switch ($_.EventType) {
				"Connected" {
					$guestConnected = $tTime
				} 
				"Disconnected"   {
					$tDiff = [datetime]$tTime - [datetime]$guestConnected
					$gDiff = $gDiff + $tDiff
				}
			}
		}
	}
	if (($endedSession -ne "Never") -and ($hDiff+$gDiff) -ne 0.0) {
		$ended = $endedSession.ToString("u") 
		$ended = $ended -replace ".$"
		[array]$logItem = $ended
	        $logItem += $xml.session.host
	        $logItem += $xml.session.InvitationType
	        $logItem += $xml.session.Tag
		$logItem += $([string]::Format("{0:d2}:{1:d2}:{2:d2}", $hDiff.hours, $hDiff.minutes, $hDiff.seconds)) 
		$logItem += $([string]::Format("{0:d2}:{1:d2}:{2:d2}", $gDiff.hours, $gDiff.minutes, $gDiff.seconds)) 
		$logItems += ,$logItem
		Move-Item $sessionFile.FullName "Archiv"
		$towrite = $true
	}
}
if ($logitems.GetUpperBound(0) -gt 0) {
	$logItems = $logItems | sort-object @{Expression={$_[0]}; Ascending=$false}
}




if (-not $towrite) {
	return
}


$wsettings = New-Object system.Xml.XmlWriterSettings  
$wsettings.Indent = $true  
$wsettings.OmitXmlDeclaration = $false  
$wsettings.NewLineOnAttributes = $true  

$writer = [system.xml.XmlWriter]::Create("sessions.xml", $wsettings)
$writer.WriteStartElement("Sessions")

foreach($logItem in $logItems) {
	$writer.WriteStartElement("Session")
	$writer.WriteElementString("ended", $logItem[0])
	$writer.WriteElementString("user", $logItem[1])
	$writer.WriteElementString("type", $logItem[2])
	$writer.WriteElementString("code", $logItem[3])
	$writer.WriteElementString("minuteshost", $logItem[4])
	$writer.WriteElementString("minutesguest", $logItem[5])
	$writer.WriteEndElement()
}


$rerror = $false
$rsettings = new-object System.Xml.XmlReaderSettings
$rsettings.CloseInput = $true
$rsettings.IgnoreWhitespace = $true
try {
	$reader = [System.Xml.XmlReader]::Create("archiv.xml", $settings)
}
catch {
	$rerror = $true
}

if ($rerror) {
	$writer.WriteEndElement()
} else {
	$reader.ReadStartElement("Sessions")

	while (!$reader.EOF) {
		$writer.WriteNode($reader, $false)
	}
	$reader.Close()
}


$writer.Flush()
$writer.Close()

copy-item "sessions.xml" "archiv.xml"

DROP VIEW SessionConnectionTime1;
DROP VIEW SessionConnectionTime2;
DROP VIEW SessionConnectionTime3;
DROP VIEW SessionConnectionTime4;
DROP VIEW SessionConnectionTime5;
DROP VIEW SessionConnectionTime6;

CREATE VIEW SessionConnectionTime1 AS
SELECT a.*, b.Time AS ConnectTime, c.Time AS DisconnectTime
FROM SessionConnection a
INNER JOIN SessionConnectionEvent b ON a.SessionID = b.SessionID AND a.ConnectionID = b.ConnectionID AND b.EventType = 10
INNER JOIN SessionConnectionEvent c ON a.SessionID = c.SessionID AND a.ConnectionID = c.ConnectionID AND c.EventType = 11;

CREATE VIEW SessionConnectionTime2 AS
SELECT
                a.SessionID,
                a.ConnectionID,
                a.ConnectTime,
                a.DisconnectTime,
                (SELECT MAX(DisconnectTime) FROM SessionConnectionTime1 b WHERE b.ProcessType <> a.ProcessType AND b.ConnectTime <= a.ConnectTime AND b.DisconnectTime > a.ConnectTime) MaxOtherDisconnectTime
FROM SessionConnectionTime1 a;

CREATE VIEW SessionConnectionTime3 AS
SELECT
                SessionID,
                ConnectionID,
                ConnectTime StartTime,
                (CASE WHEN DisconnectTime > MaxOtherDisconnectTime THEN MaxOtherDisconnectTime ELSE DisconnectTime END) EndTime
FROM SessionConnectionTime2
WHERE NOT MaxOtherDisconnectTime IS NULL;

CREATE VIEW SessionConnectionTime4 AS
SELECT
                SessionID,
                ConnectionID,
                MIN(StartTime) StartTime,
                EndTime
FROM SessionConnectionTime3
GROUP BY SessionID, EndTime;

drop table sct;
create table sct as select * from SessionConnectionTime4;

CREATE VIEW SessionConnectionTime5 AS
SELECT
                SessionID,
                ConnectionID,
                strftime('%s', EndTime) EndSeconds,
                strftime('%s', StartTime) StartSeconds
FROM 
                sct;

CREATE VIEW SessionConnectionTime6 AS
SELECT
                SessionID,
                SUM(EndSeconds - StartSeconds) SecondsConnected
FROM 
                SessionConnectionTime5
GROUP BY SessionID;