62 lines
2.5 KiB
PowerShell
62 lines
2.5 KiB
PowerShell
$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() |