$csv = Import-Csv -Delimiter ';' -Path "\\client\c$\Users\m.nielsen\Documents\Thales\SV1612090061\data.csv"

$group = $csv | group CardEngravedID

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'Server=rejk-p-sql003\sql01;Database=AfcCookedIFSv2;Trusted_Connection=True;'
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()

$missingATSN = New-Object System.Collections.Generic.List`[int`]
$foundATSN = New-Object System.Collections.Generic.List`[int`]
$missingTransactions = New-Object System.Collections.Generic.List`[object`]
$foundTransactions = New-Object System.Collections.Generic.List`[object`]

$i = 0
foreach($entry in $group) {
    $i++

    Write-Progress -Activity 'Looking' -Status "$i / $($group.Count)" -PercentComplete ($i * 100 / $group.Count)

    if($entry.Group[0].KnownTransactionID -lt 0) { continue }

    Write-Verbose "New entry:$($entry.Name)"

    [int]$maxATSN = 0
    [int]$minATSN = [int32]::MaxValue
    
    $missingATSN.Clear()
    $foundATSN.Clear()

    foreach($item in $entry.Group) {
        Write-Verbose ". New item - MissingATSN:$($item.MissingATSN) KnownTransactionID:$($item.KnownTransactionID)"

        if([int]$item.MissingATSN -gt $maxATSN) {
            $maxATSN = [int]$item.MissingATSN
        }

        if([int]$item.MissingATSN -lt $minATSN) {
            $minATSN = [int]$item.MissingATSN
        }

        $missingATSN.Add([int]$item.MissingATSN)
    }


    ## 
    Write-verbose "Missing ATSN:$(($missingATSN | sort) -join ', ')"
    Write-Verbose "Finding CardEngravedID:$($entry.Name) ATSN BETWEEN $minATSN AND $maxATSN in ObjTransaction"

    $sqlCommand.CommandText = @"
select t.TransactionID, t.ApplicationTransactionSequenceNumber as ATSN, t.TransactionMediaLastDeviceID as LastDeviceID, t.DeviceID, t.MsgReportDate from trk.objtransaction t with(readuncommitted) 
where t.CardEngravedID = $($entry.Name) 
    and t.ApplicationTransactionSequenceNumber between $minATSN and $maxATSN
order by t.ApplicationTransactionSequenceNumber 
"@

    $foundCount = 0

    try { 
        $reader = $sqlCommand.ExecuteReader()

        if($reader.HasRows) {
            while($reader.Read()) {
                
                $msgReportDate = $reader['MsgReportDate']
                $transactionID = $reader['TransactionID']
                $ATSN = $reader['ATSN']
                $lastDeviceID = $reader['LastDeviceID']
                $deviceID = $reader['DeviceID']

                if($missingATSN -contains $ATSN) {
                    Write-Verbose ". Found transaction - T.ID:$transactionID MRD:$msgReportDate ATSN:$ATSN DeviceID:$deviceID LastDeviceID:$lastDeviceID - Unexpected, supposed to be missing?"
                    $foundCount++
                    if(-not ($missingATSN.Remove([int]$ATSN))) {
                        Write-Warning "Couldn't remove ATSN:$ATSN from MissingATSN:$($missingATSN -join ', ')"
                    }

                    $foundATSN.Add([int]$ATSN)
                    $foundTransactions.Add(
                        [pscustomobject]@{ 
                            TransactionID = $transactionID
                            CardEngravedID = $entry.Name
                            ATSN = $ATSN
                            MsgReportDate = $msgReportDate
                            DeviceID = $deviceID
                            LastDeviceID = $lastDeviceID

                            Exception = $false
                            ExceptionText = $null
                            TransactionExceptionID = $null
                        }
                    )
                }
                else {
                    Write-Verbose ". Ignoring $ATSN"
                }
            }
        }
    }
    finally {
        $reader.Close()
    }


    Write-Verbose "Finding CardEngravedID:$($entry.Name) ATSN BETWEEN $minATSN AND $maxATSN in ObjTransactionException"

    $sqlCommand.CommandText = @"
select te.TransactionID, te.TransactionExceptionID, te.ApplicationTransactionSequenceNumber as ATSN, te.TransactionMediaLastDeviceID as LastDeviceID, te.DeviceID, te.MsgReportDate, te.ExceptionText 
from dcp.ObjTransactionException te with(readuncommitted) 
where te.CardEngravedID = $($entry.Name) 
  and te.ApplicationTransactionSequenceNumber between $minATSN and $maxATSN
order by te.ApplicationTransactionSequenceNumber
"@

    $exceptionCount = 0

    try { 
        $reader = $sqlCommand.ExecuteReader()

        if($reader.HasRows) {
            while($reader.Read()) {
                
                $transactionID = $reader['TransactionID']
                $transactionExceptionID = $reader['TransactionExceptionID']
                $ATSN = $reader['ATSN']
                $lastDeviceID = $reader['LastDeviceID']
                $deviceID = $reader['DeviceID']
                $msgReportDate = $reader['MsgReportDate']
                $exceptionText = $reader['ExceptionText']

                if($missingATSN -contains $ATSN) {
                    Write-Verbose "Found exception - T.ID:$transactionID TEID:$transactionExceptionID MRD:$msgReportDate ATSN:$ATSN DeviceID:$deviceID LastDeviceID:$lastDeviceID - ExceptionText:$exceptionText"
                    $exceptionCount++
                    if(-not ($missingATSN.Remove([int]$ATSN))) {
                        Write-Warning "Couldn't remove ATNS:$ATSN from List:$($missingATSN -join ', ')"
                    }

                    $foundATSN.Add([int]$ATSN)
                    $foundTransactions.Add(
                        [pscustomobject]@{ 
                            TransactionID = $transactionID
                            CardEngravedID = $entry.Name
                            ATSN = $ATSN
                            MsgReportDate = $msgReportDate
                            DeviceID = $deviceID
                            LastDeviceID = $lastDeviceID

                            Exception = $true
                            ExceptionText = $exceptionText
                            TransactionExceptionID = $transactionExceptionID
                        }
                    )
                }
            }
        }
    }
    finally {
        $reader.Close()
    }


    Write-Verbose "Found $($foundCount + $exceptionCount) transactions for CardEngravedID:$($entry.Name) ATSN BETWEEN $minATSN AND $maxATSN` - FoundATSN:$($foundATSN -join ', ')"
    
    if($missingATSN.Count -gt 0) {
        Write-Warning "CardEngravedID:$($entry.Name) StartATSN:$minATSN EndATSN:$maxATSN Missing ATSN:$($missingATSN -join ', ')"

        foreach($ATSN in $missingATSN) {
            $missingTransactions.Add(
                [pscustomobject]@{
                    CardEngravedID = $entry.Name
                    ATSN = $ATSN
                }
            )
        }

    }
}