Product: PowerShell Universal
Version: 3.7.9
@adam I seem to be having an issue with DataGrids. Hopefully, I am missing something easy. Anyone’s assistance would be greatly appreciated.
$Context.Page missing after first page.
Zero is fine, but 1 never appears
I am passing an array of hashtables. Each hashtable is a row. I have tried passing a array of hashtables, generic list of hashtables, PSCustomObject, calculated properties, with and without the [Array] cast, forcing and array with a subexpression @(), preventing unrolling with ,. I always get the first page of data but I then click next page and I do not see the second page of data.
On the first page, the page indicator looks like this screenshot:
function Out-UDSQLDataGrid {
param(
[Parameter(Mandatory)]
$Context,
[Parameter()]
[string]
$Table = 'InactiveUser',
[Parameter(Mandatory)]
$Connection,
[Parameter()]
$AccessReviewID
)
end {
$simpleFilter = @()
Show-UDToast ('C {0} ' -f (@($Context | ForEach-Object { $_ | Select-Object * | Out-String }) -ne '' -join ', ')) -Duration 10000
if ($null -ne $Context.Filter.Items -and $Context.Filter.Items.Count -gt 0) {
$linkOperator = $Context.Filter.linkOperator #The link operator is 'AND' or 'OR'. It will always be one or the other for all properties
foreach ($item in $Context.Filter.Items) {
$simpleFilter += [PSCustomObject]@{
Property = $item.columnField
Value = $item.Value
Operator = $item.operatorValue
}
}
}
if ($null -ne $simpleFilter -and $simpleFilter.Count -gt 0) {
$count = 1
foreach ($filter in $simpleFilter) {
if ($count -gt 1) {
$Where += " $($linkOperator) "
}
else {
$Where += " WHERE "
}
switch ($filter.Operator) {
"contains" { $Where += " $($filter.Property) LIKE '%$($filter.Value)%' " }
"equals" { $Where += " $($filter.Property) = '$($filter.Value)' " }
"startsWith" { $Where += " $($filter.Property) LIKE '$($filter.Value)%' " }
"endsWith" { $Where += " $($filter.Property) LIKE '%$($filter.Value)' " }
"isAnyOf" {
$count = 1
foreach ($val in $filter.Value) {
if ($count -gt 1) {
$list += ", '$val'"
}
else {
$list += "'$val'"
}
$count += 1
}
$Where += " $($filter.Property) IN ($($list)) "
}
"isempty" { $Where += " TRIM ($($filter.Property)) IS NULL " }
"isnotempty" { $Where += " TRIM ($($filter.Property)) IS NOT NULL " }
"notequals" { $Where += " $($filter.Property) != '$($filter.Value)' " }
"notcontains" { $Where += " $($filter.Property) NOT LIKE '%$($filter.Value)%' " }
}
$count += 1
}
}
else {
$Where = $null
}
$sort = $Context.Sort.'0'
if ($sort) {
$sqlSort = "ORDER BY $($sort.field) $($sort.Sort) "
}
else {
$sqlSort = "ORDER BY UserDisplayName asc"
}
$sqlPage = "OFFSET $($Context.Page * $Context.PageSize) ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY"
$Query = @"
SELECT Users.accountEnabled, InactiveUser.UserId, InactiveUser.UserDisplayName, InactiveUser.LastSignIn, InactiveUser.RemoveFromApp
FROM InactiveUser
JOIN Review ON InactiveUser.AccessReviewID = Review.AccessReviewID
JOIN Users ON Users.Id = InactiveUser.UserID
"@
if ($null -ne $Where) {
$Where = '{0} {1}.AccessReviewID = {2}' -f $Where, $Table, $AccessReviewId
}
else {
$Where = 'WHERE {0}.AccessReviewID = {1}' -f $Table, $AccessReviewId
}
$Query = '{0} {1} {2} {3}' -f $Query, $Where, $sqlSort, $sqlPage
$totalCount = (Read-GacAFdb -Connection $Connection -Query "SELECT COUNT(*) As Count FROM $Table $Where").Count
$Rows = [System.Collections.Generic.List[hashtable]]::new()
$RowList = Read-GacAFdb -Connection $Connection -Query $Query
foreach ($Item in $RowList) {
$ThisRow = @{
'Action' = if ($Item.RemoveFromApp.value) { 'Remove' } else { 'Keep' }
'UserDisplayName' = $Item.UserDisplayName.value
'accountEnabled' = $Item.accountEnabled.value
'LastSignIn' = $Item.LastSignIn.value
'UserID' = $Item.UserID.value
}
$Rows.Add($ThisRow)
}
@{
rows = [Array]$Rows
rowCount = $TotalCount
}
}
}
# calling page
New-UDPage -Name 'Application Access Review' -Url '/accessreviewsgrid' -Content {
# BELOW Verifies that you belong on this page #
$SESSION:ID = ($ClaimsPrincipal.Claims | Where-Object { $_.Type -eq 'http://schemas.microsoft.com/identity/claims/objectidentifier' }).value
if (-not $SESSION:ID) {
Show-UDToast -Message 'Session ID not found. Please sign in with your Azure AD credentials. Please use incognito mode to sign in if the problem persists' -Persistent -BackgroundColor Red -MessageColor white
break
}
$uri = [uri]$Headers.Referer
$queryParams = [System.Web.HttpUtility]::ParseQueryString($uri.Query)
$AccessReviewId = $queryParams['AccessReviewId']
if (-not $AccessReviewId) {
Show-UDToast -Message 'To access this dashboard, please use the link emailed to you' -BackgroundColor Red -MessageColor White -Duration 100000
break
}
$Connection = Connect-GacAFdb
$Connection.open()
$ReviewQuery = @"
SELECT Groups.GroupDisplayName, Application.AppDisplayName
FROM Review
JOIN Groups ON Review.GroupId = Groups.GroupId
JOIN Application ON Review.AppId = Application.AppId
WHERE Review.AccessReviewID = '$AccessReviewId'
AND Review.ReviewerID = '$SESSION:ID';
"@
$checksplat = @{
Query = $ReviewQuery
Connection = $Connection
ErrorAction = 'Stop'
}
try {
$ReviewTableRow = $null
if ($ReviewTableRow = Read-GacAFdb @checksplat) {
New-UDTypography -Align right -Text ('Access review of application {0} for the members of the group {1}' -f $ReviewTableRow.AppDisplayName, $ReviewTableRow.GroupDisplayName) -Variant h5 -FontWeight bold
}
}
catch {
Write-Host ('[ FAILED READ DB IN ACCESSREVIEWS ] [ SELECT CASE WHEN EXISTS (SELECT 1 FROM Review WHERE AccessReviewID = "{0}" AND ReviewerID = "{1}") THEN "TRUE" ELSE "FALSE" END; ] ERROR [ {2} ]' -f $AccessReviewId, $SESSION:ID, ($Error[0] | Select-Object * | Out-String))
Show-UDToast ('Failed to read from Database. Stopping. Please notify administrator of AccessReviews page.') -Duration 100000
break
}
if (-not $ReviewTableRow) {
Show-UDToast -Message 'This Access Review is not assigned to you' -BackgroundColor Blue -MessageColor White -Duration 100000
break
}
# ABOVE Verifies that you belong on this page #
New-UDDataGrid -LoadRows {
Out-UDSqlDataGrid -Context $EventData -Connection $Connection -AccessReviewID $AccessReviewId
} -Columns @(
@{ field = "Action"; }
@{ field = "userdisplayname"; }
@{ field = "accountEnabled"; }
@{ field = "LastSignIn"; }
@{ field = "UserID"; }
) -RowsPerPageOptions @(10, 25, 50, 100) -PageSize 10 -Pagination
$Connection.close()
}
Once I click next: OFFSET is empty
Thank you.
it would have kind of worked for me.
