$nums = Get-Content C:\users\ewpmni\desktop\cardnumbers.txt
$ptos = Import-Csv C:\users\ewpmni\desktop\ptos.txt -Delimiter "`t" -Encoding UTF8

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

$result = foreach($num in $nums) {
    Write-Progress -Activity 'SQL' -Status "$num - $i of $($nums.Count)" -PercentComplete (100 / $nums.Count * $i)
    $i++

    $sqlCommand.CommandText = @"
    select top 5 r.enumeratorwording as EquipmentType, t.FileName as FileName, t.cardengravedid as CardEngravedID, t.messagereceptiondate as MessageReceptionDate, t.serviceproviderid as ServiceProviderID, t.contractorid as ContractorID, t.staffid as StaffID, t.deviceid as DeviceID from trk.objtransaction t with(readuncommitted)
    inner join eqm.objequipment e with(readuncommitted) on (t.deviceid = e.deviceid)
    inner join cmn.refconfiguration r with(readuncommitted) on (r.enumeratorkey = e.equipmenttypeid and r.enumeratordomain = 'EquipmentType' and r.languagecode = 'en-GB')

    where t.CardEngravedID = $num

    order by t.TransactionID desc
"@

    $reader = $sqlCommand.ExecuteReader()

    if($reader.HasRows) {
        while($reader.read()) {
            $sid = $reader['ServiceProviderID']
            $cid = $reader['ContractorID']

            [pscustomobject]@{ 
                CardEngravedID = $reader['CardEngravedID']
                EquipmentType = $reader['EquipmentType']
                FileName = $reader['FileName']
                MessageReceptionDate = $reader['MessageReceptionDate']
                ServiceProviderID = $reader['ServiceProviderID']
                ContractorID = $reader['ContractorID']
                StaffID = $reader['StaffID']
                DeviceID = $reader['DeviceID']
                Contractor = ($ptos | where { $_.ReferenceNumber -eq $cid }).LongName
                ServiceProvider = ($ptos | where { $_.ReferenceNumber -eq $sid }).LongName
            }
        }
    } else {
        [pscustomobject]@{
            CardEngravedID = $num
            EquipmentType = ''
            FileName = ''
            MessageReceptionDate = ''
            ServiceProviderID = ''
            ContractorID = ''
            StaffID = ''
            DeviceID = ''
            Contractor = ''
            ServiceProvider = ''
        }
    }

    $reader.Close()
}

$sqlConnection.Close()