SQLBitsPS.psm1

#Region './Public/Get-SQLBitsEmptySession.ps1' 0
function Get-SQLBitsEmptySession {
    <#
    .SYNOPSIS
    A helper function to get the number of empty sessions at SQLBits
    
    .DESCRIPTION
    Just a helper function to get the number of empty sessions at SQLBits
    
    .PARAMETER output
    The type of output required. Valid values are Raw(default), Grouped or Total
    
    .EXAMPLE
    Get-SQLBitsEmptySessionCount

         Day StartTime EndTime EmptySessions
         --- --------- ------- -------------
        Thursday 10:10 11:00 1
        Thursday 11:30 11:50 3
        Thursday 12:00 12:50 1
        Thursday 13:40 14:00 0
        Thursday 14:10 15:00 0

    Returns the number of empty sessions at SQLBits per time slot as a psobject

    .EXAMPLE

    Get-SQLBitsEmptySessionCount -output Grouped

    Name Count

    Thursday 4
    Friday 3
    Saturday 3

    Returns the number of empty sessions at SQLBits per day

    .EXAMPLE

    Get-SQLBitsEmptySessionCount -output Total

    10

    Returns the total number of empty sessions at SQLBits
    
    .NOTES
    Rob Sewell 2023
    #>

    [cmdletbinding()]
    Param(
        [Parameter()]
        [ValidateSet('Raw', 'Grouped', 'Total')]
        [string]
        $output = 'Raw'
    )
    $Schedule = Get-SQLBitsSchedule -output object
    $plenarysessions = 'Registration', 'Quick Break', 'Closing Keynote and Prize Giving', 'End - TearDown', 'Coffee Break', 'Lunch', 'Free Time', 'Prize Giving', 'Party', 'Pub Quiz', 'Keynote by The Community', 'End - TearDown'
    $KeyNotes = 'Keynote by The Community', 'Opening Keynote'

    $rawOutput = foreach ($time in $Schedule | Where-Object { $_.'All Rooms'.Trim() -notin $plenarysessions -and $_.Auditorium.Trim() -notin $KeyNotes }) {
        $SessionCount = ($time.psobject.Properties.Where{ $_.Name -ne 'All Rooms' }.Value -eq '
'
 ).Count
        $Message = "{0} {1} has {2} empty sessions" -f $time.Day, $time.StartTime, $SessionCount
        Write-PSFMessage $message -Level Verbose
        
        [pscustomobject]@{
            Day           = $time.Day
            StartTime     = $time.StartTime
            EndTime       = $time.EndTime
            EmptySessions = $SessionCount
        }
    }

    switch ($output) {
        'Raw' {
            $rawOutput
        }
        'Grouped' {
            $Summary = @{Name='EmptySessions';Expression={($_.Group | Measure-Object -Property EmptySessions -Sum).Sum}}
            $rawOutput | Group-Object Day | Select-Object Name, $Summary
        }
        'Total' {
            ($rawOutput | Measure-Object -Property EmptySessions -Sum).Sum
        }
        Default {
            $RawOutput
        }
    }
}
#EndRegion './Public/Get-SQLBitsEmptySession.ps1' 89
#Region './Public/Get-SQLBitsSchedule.ps1' 0

function Get-SQLBitsSchedule {
    <#
    .SYNOPSIS
        Gets the SQLBits Schedule from the Sessionize API

    .DESCRIPTION
        Gets the SQLBits Schedule from the Sessionize API and outputs to json, excel, psobject, html or csv

    .PARAMETER output
        The type of output required. Valid values are json, excel, psobject, html or csv

    .PARAMETER search
        A wild card search best used to find a speaker

    .PARAMETER fileDirectory
        The directory to save the output file to - defaults to Env:Temp

    .PARAMETER Show
        Whether to open the output file after it has been created

    .EXAMPLE
        Get-SQLBitsSchedule -output Excel -Show

        Gets the SQLBits Schedule from the Sessionize API and outputs to excel, opens the file and saves it to the default temp directory

    .EXAMPLE
        Get-SQLBitsSchedule -output Raw

        Gets the SQLBits Schedule from the Sessionize API and outputs as json on the screen

    .EXAMPLE
        Get-SQLBitsSchedule -output csv -Show

        Gets the SQLBits Schedule from the Sessionize API and outputs to csv, opens the file and saves it to the default temp directory

    .EXAMPLE
        Get-SQLBitsSchedule -output object

        Gets the SQLBits Schedule from the Sessionize API and outputs as a psobject on the screen

    .EXAMPLE
        Get-SQLBitsSchedule -output html -Show

        Gets the SQLBits Schedule from the Sessionize API and outputs to html, opens the file and saves it to the default temp directory

    .EXAMPLE
        Get-SQLBitsSchedule -search Buck -output object | ft

            Day StartTime EndTime Room speakers Session
            --- --------- ------- ---- -------- -------
        Thursday 14:10 15:00 MR 1B Buck Woody Arc, Arc and Arc: De-mystifying Azure Arc for Data…
        Friday 12:00 12:50 Expo Room 2 Buck Woody The Microsoft Intelligent Data Platform…

        Gets the SQLBits Schedule from the Sessionize API searches fro Buck and outputs an object

    .NOTES
        Author: Rob Sewell
        December 2022
#>

    [CmdletBinding()]
    param (
        [Parameter()]
        [ValidateSet('raw', 'excel', 'object', 'csv', 'html')]
        $output = 'excel',
        [string]
        $search,
        [string]
        $fileDirectory = $env:TEMP,
        [switch]
        $show
    )

    $BaseUri = 'https://sessionize.com/api/v2/u1qovn3p/view'
    $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'

    #TODO Add other options
    $filter = 'Schedule'
    switch ($filter) {
        'All' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Schedule' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Sessions' {
            $uri = '{0}/sessions' -f $BaseUri
        }
        'speakers' {
            $uri = '{0}/speakers' -f $BaseUri
        }
        Default {
            $uri = '{0}/All' -f $BaseUri
        }
    }

    $Data = Invoke-RestMethod -Uri $uri

    if (-not $Data) {
        Write-Warning 'No data returned from Sessionize API'
        return
    }
    $rooms = ($data.rooms | Sort-Object name)
    if (-not $rooms) {
        Write-Warning 'No rooms returned from Sessionize API'
        return
    }
    $speakers = $data.speakers
    if (-not $speakers) {
        Write-Warning 'No speakers returned from Sessionize API'
        return
    }
    # Thank you Shane - https://nocolumnname.blog/2020/10/29/pivot-in-powershell/
    $props = @(
        @{ Name = 'Day' ; Expression = { $Psitem.Group[0].startsAt.DayOfWeek } }
        @{ Name = 'Date' ; Expression = { $Psitem.Group[0].startsAt.tolongdatestring() } }
        @{ Name = 'StartTime' ; Expression = { $Psitem.Group[0].startsAt.ToShortTimeString() } }
        @{ Name = 'EndTime' ; Expression = { $Psitem.Group[0].EndsAt.ToShortTimeString() } }
        foreach ($room in $rooms) {
            $rn = $room.Name
            @{
                Name       = $rn
                Expression = {
                    '{0}
{1}'
  -f @(
                        ($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).title,
                        (($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).speakers.ForEach{ $speakers | Where-Object id -EQ $_ }.FullName -join ' ')
                    )

                }.GetNewClosure()
            }
        }
    )
    if ($IsCoreCLR) {
        $rawsessions = $Data.sessions 
    } else {
        $rawsessions = $Data.sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers

    }
    $sessions = $rawsessions | Group-Object -Property StartsAt | Select-Object $props

    # if we have a search filter, filter the sessions
    if ($search) {
        $Results = @{Name = 'Results'; Expression = {
                $_.psobject.properties.Value -like "*$search*" 
            }
        }
        $RoomSearch = @{Name = 'Room'; Expression = {
            ($_.psobject.properties | Where-Object { $_.Value -like "*$search*" } ).Name
            }
        }
        $speakerSearch = @{Name = 'speakers'; Expression = { ($_.Results -Split "`n")[1] } }
        $Session = @{Name = 'Session'; Expression = { ($_.Results -Split "`n")[0] } }
        $sessions = $sessions | Select-Object -Property *, $RoomSearch, $Results | Where-Object { $null -ne $_.Results } | Select-Object -Property Day, StartTime, EndTime, Room, $speakerSearch, $Session
    }
    

    switch ($output) {
        'Raw' {
            $Data
        }
        'object' {
            $sessions
        }
        'Excel' {
            if (Get-Module -Name ImportExcel -ErrorAction SilentlyContinue -ListAvailable) {
                if ($filter -eq 'Schedule') {

                    $FilePath = '{0}\SQLBitsSchedule{1}_{2}.xlsx' -f $fileDirectory, $filter, $Date

                    $sessions | Group-Object Day | ForEach-Object {

                        $worksheetName = $_.Name
                        $excel = $_.Group | Export-Excel -Path $FilePath -WorksheetName $worksheetName -AutoSize  -FreezePane 2, 5 -PassThru
                        1..15 | ForEach-Object {
                            Set-ExcelRow -ExcelPackage $excel -WorksheetName $worksheetName -Row $_ -Height 30 -WrapText
                        }

                        $rulesparam = @{
                            Address   = $excel.Workbook.Worksheets[$WorkSheetName].Dimension.Address
                            WorkSheet = $excel.Workbook.Worksheets[$WorkSheetName]
                        }

                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Coffee Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Quick Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Keynote",$E1)))' -BackgroundColor BlueViolet -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Lunch",$E1)))' -BackgroundColor Chocolate  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Prize",$E1)))' -BackgroundColor PowderBlue  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Free Time",$E1)))' -BackgroundColor GoldenRod  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Registration",$E1)))' -BackgroundColor DarkOrange  -ForegroundColor White -StopIfTrue
                        Close-ExcelPackage $excel
                    }
                    if ($Show) {
                        Invoke-Item $filepath
                    } else {
                        Write-Output "Excel file saved to $FilePath"
                    }
                }
            } else {
                Write-Warning 'You need to install ImportExcel to use this option but here is a CSV instead'
                $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date
                $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation
                if ($Show) {
                    Invoke-Item $filepath
                } else {
                    Write-Output "Csv file saved to $FilePath"
                }
            }

        }
        'CSv' {
            $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date
            $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation
            if ($Show) {
                Invoke-Item $filepath
            } else {
                Write-Output "Csv file saved to $FilePath"
            }
        }
        'html' {
            $FilePath = '{0}\SQLBits_{1}_{2}.html' -f $fileDirectory, $filter, $Date
            $sessions | ConvertTo-Html | Out-File $FilePath
            if ($Show) {
                Invoke-Item $filepath
            } else {
                Write-Output "Html file saved to $FilePath"
            }
        }
        Default {
        }
    }
}
#EndRegion './Public/Get-SQLBitsSchedule.ps1' 233
#Region './Public/Get-SqlBitsSession.ps1' 0

function Get-SQLBitsSession {
<#
.SYNOPSIS
Gets the sessions from the Sessionize API.

.DESCRIPTION
Gets all the sessions from the Sessionize API and outputs to json, excel, psobject, html or csv

.PARAMETER search
searches all properties for the search term

.PARAMETER all
returns all the sessions

.EXAMPLE
Get-SQLBitsSession -search 'Power Bi'

Searches for the term 'Power Bi' in all the sessions

.EXAMPLE
Get-SQLBitsSession -search 'Community Corner'

Searches for all the sessions in the Community Corner

.EXAMPLE
Get-SQLBitsSession -search 'Rie Merritt'

Searches for all the sessions by Rie Merritt

.NOTES
Rob Sewell
#>

    [CmdletBinding()]
    param (
        [string]
        $search,
        [switch]
        $all
    )

    $BaseUri = 'https://sessionize.com/api/v2/u1qovn3p/view'
    $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'

    #TODO Add other options
    $filter = 'Sessions'
    switch ($filter) {
        'All' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Schedule' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Sessions' {
            $uri = '{0}/sessions' -f $BaseUri
        }
        'speakers' {
            $uri = '{0}/speakers' -f $BaseUri
        }
        Default {
            $uri = '{0}/All' -f $BaseUri
        }
    }

    $Data = Invoke-RestMethod -Uri $uri

    if (-not $Data) {
        Write-Warning 'No data returned from Sessionize API'
        return
    }
    $sessions = ($data.sessions | Sort-Object id)


    if ($IsCoreCLR) {
        $rawsessions = $sessions
    } else {
        $rawsessions = $sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers

    }
    $Speakers = @{Name='Speakers'; Expression = {$PsItem.speakers.name -join ', '}}
    $PrimaryTheme = @{Name='PrimaryTheme'; Expression = {($PsItem.categories | Where Name -eq 'Primary Theme').categoryItems.name}}
    $SessionLength = @{Name='SessionLength'; Expression = {($PsItem.categories | Where id -eq 34075).categoryItems.name -replace ' sessoin', ''}}

    if($all){
        $rawsessions | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
    }else{
        if ($search) {

            $Results = @{Name = 'Results'; Expression = {
                    $_.psobject.properties.Value -like "*$search*"
                }
            }
        $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select-Object -Property *, $Results | Where-Object { $null -ne $_.Results }  | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
        } else {
            $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
        }
    }

}

#EndRegion './Public/Get-SqlBitsSession.ps1' 101
#Region './Public/Get-SQLBitsSpeakers.ps1' 0
function Get-SQLBitsSpeakers {
    <#
    .SYNOPSIS
    Returns the SQLBits Speakers from the Sessionize API

    .DESCRIPTION
    This function returns the SQLBits Speakers from the Sessionize API

    .PARAMETER search
    Filters the results by the search term

    .PARAMETER remote
    A switch to filter the results to only remote speakers

    .PARAMETER full
    Returns the full object as output

    .EXAMPLE
    Get-SQLBitsSpeakers

    Returns all the SQLBits Speakers from the Sessionize API

    .EXAMPLE

    Get-SQLBitsSpeakers -search 'Rob'

    Returns all the SQLBits Speakers from the Sessionize API that contain Rob in their name

    .EXAMPLE

    Get-SQLBitsSpeakers -remote

    Returns all the SQLBits Speakers from the Sessionize API that are remote

    .EXAMPLE

    Get-SQLBitsSpeakers -full

    Returns all the SQLBits Speakers from the Sessionize API as a full object

    .NOTES
    Rob Sewell - January 2023
    #>

    [CmdletBinding()]
[Diagnostics.CodeAnalysis.SuppressMessageAttribute(
    <#Category#>'PSUseSingularNouns',<#CheckId#>$null,
    Justification = 'because my beard is glorious'
)]
    param (
        [Parameter(Mandatory = $false)]
        [string]$search,
        [switch]$remote,
        [switch]$full
    )
    begin {
        $BaseUri = 'https://sessionize.com/api/v2/u1qovn3p/view'
        $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'
        $uri = '{0}/speakers' -f $BaseUri
        $sessionuri = '{0}/sessions' -f $BaseUri
        $AllSessions = Invoke-RestMethod -Uri $sessionuri

        $Isremote = @{Name='IsRemote';Expression={($_.categories | Where-Object {$_.id -eq '44351';}).categoryItems.name}}

        $CompanyName = @{Name='CompanyName';Expression={($_.questionAnswers | Where-Object {$_.id -eq 43369}).Answer}}
        $LinkedIn = @{Name='LinkedIn';Expression={($_.links | Where-Object {$_.linktype -eq 'LinkedIn'}).url}}
        $Sessionize = @{Name='Sessionize';Expression={($_.links | Where-Object {$_.linktype -eq 'Sessionize'}).url}}
        $Blog = @{Name='Blog';Expression={($_.links | Where-Object {$_.linktype -eq 'Blog'}).url}}
        $Facebook = @{Name='Facebook';Expression={($_.links | Where-Object {$_.linktype -eq 'Facebook'}).url}}
        $Twitter = @{Name='Twitter';Expression={($_.links | Where-Object {$_.linktype -eq 'Twitter'}).url}}
        $CompanyWebsite = @{Name='Company Website';Expression={($_.links | Where-Object {$_.linktype -eq 'Company Website'}).url}}
        $Other = @{Name='Other';Expression={($_.links | Where-Object {$_.linktype -eq 'Other'}) | ForEach-Object { $_ }}}
        $SessionNames = @{
            Name='SessionDetails';Expression={$_.sessions | ForEach-Object {
                $id = $_.id
                $Session = $AllSessions.Sessions|Where-Object{$_.id -eq $id}
                [PSCustomObject]@{
                    Name = $_.name
                    Room = $Session.Room
                    Starts = $Session.startsAt
                    Ends = $Session.endsAt
                }
            }
        }
    }
        $Data = Invoke-RestMethod -Uri $uri
        $Data = $Data|Select-Object *,$CompanyName,$Isremote,$LinkedIn,$Sessionize,$Blog,$Facebook,$Twitter,$CompanyWebsite,$Other,$sessionNames
        if (-not $Data) {
            Write-Warning 'No data returned from Sessionize API'
            return
        }

    }
    process {
        if($search) {
            $Data = $Data | Where-Object { $_.fullName -like "*$search*" }
        }
        if($remote) {
            $Data = $Data | Where-Object { $_.IsRemote -eq 'Remote' }
        }
        if($full) {
            $Data | Select-Object -ExcludeProperty id,isTopSpeaker,questionAnswers,categories,links
        }
        else {
            $Data | Select-Object fullName, companyName,tagLine, $SessionNames
        }
    }
    end {
    }
}
#EndRegion './Public/Get-SQLBitsSpeakers.ps1' 110
#Region './Public/Get-SqlBitsTDSession.ps1' 0

function Get-SQLBitsTDSession {
<#
.SYNOPSIS
Gets the TD sessions from the Sessionize API.

.DESCRIPTION
Gets all the TD sessions from the Sessionize API and outputs to json, excel, psobject, html or csv

.PARAMETER search
searches all properties for the search term

.PARAMETER all
returns all the sessions

.EXAMPLE
Get-SQLBitsTD Session -search 'Power Bi'

Searches for the term 'Power Bi' in all the sessions


.EXAMPLE
Get-SQLBitsTDSession -search 'Rie Merritt'

Searches for all the sessions by Rie Merritt

.NOTES
Rob Sewell
#>

    [CmdletBinding()]
    param (
        [string]
        $search,
        [switch]
        $all
    )

    $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view/'
    $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'

    #TODO Add other options
    $filter = 'Sessions'
    switch ($filter) {
        'All' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Schedule' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Sessions' {
            $uri = '{0}/sessions' -f $BaseUri
        }
        'speakers' {
            $uri = '{0}/speakers' -f $BaseUri
        }
        Default {
            $uri = '{0}/All' -f $BaseUri
        }
    }

    $Data = Invoke-RestMethod -Uri $uri

    if (-not $Data) {
        Write-Warning 'No data returned from Sessionize API'
        return
    }
    $sessions = ($data.sessions | Sort-Object id)


    if ($IsCoreCLR) {
        $rawsessions = $sessions
    } else {
        $rawsessions = $sessions | select -Property id, title, @{Name = 'startsAt'; expression = { [datetime]$_.startsAt } } , @{Name = 'endsAt'; expression = { [datetime]$_.endsAt } }, roomID, speakers

    }
    $Speakers = @{Name='Speakers'; Expression = {$PsItem.speakers.name -join ', '}}
    $PrimaryTheme = @{Name='PrimaryTheme'; Expression = {($PsItem.categories | Where Name -eq 'Primary Theme').categoryItems.name}}
    $SessionLength = @{Name='SessionLength'; Expression = {($PsItem.categories | Where id -eq 34075).categoryItems.name -replace ' sessoin', ''}}

    if($all){
        $rawsessions | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
    }else{
        if ($search) {

            $Results = @{Name = 'Results'; Expression = {
                    $_.psobject.properties.Value -like "*$search*"
                }
            }
        $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select-Object -Property *, $Results | Where-Object { $null -ne $_.Results }  | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
        } else {
            $rawsessions | Where-Object {$PSItem.isServiceSession -eq $false -and $PsItem.isPlenumSession -eq $false } | Select title,description,startsAt,EndsAt,$Speakers,$PrimaryTheme,$SessionLength,room
        }
    }

}

#EndRegion './Public/Get-SqlBitsTDSession.ps1' 97
#Region './Public/Get-SQLBitsTDSpeakers.ps1' 0
function Get-SQLBitsTDSpeakers {
    <#
    .SYNOPSIS
    Returns the SQLBits TD Speakers from the Sessionize API

    .DESCRIPTION
    This function returns the SQLBits TD Speakers from the Sessionize API

    .PARAMETER search
    Filters the results by the search term

    .PARAMETER remote
    A switch to filter the results to only remote speakers

    .PARAMETER full
    Returns the full object as output

    .EXAMPLE
    Get-SQLBitsTDSpeakers

    Returns all the SQLBits Speakers from the Sessionize API

    .EXAMPLE

    Get-SQLBitsTDSpeakers -search 'Rob'

    Returns all the SQLBits Speakers from the Sessionize API that contain Rob in their name

    .EXAMPLE

    Get-SQLBitsTDSpeakers -remote

    Returns all the SQLBits Speakers from the Sessionize API that are remote

    .EXAMPLE

    Get-SQLBitsTDSpeakers -full

    Returns all the SQLBits Speakers from the Sessionize API as a full object

    .NOTES
    Rob Sewell - January 2023
    #>

    [CmdletBinding()]
[Diagnostics.CodeAnalysis.SuppressMessageAttribute(
    <#Category#>'PSUseSingularNouns',<#CheckId#>$null,
    Justification = 'because my beard is glorious'
)]
    param (
        [Parameter(Mandatory = $false)]
        [string]$search,
        [switch]$remote,
        [switch]$full
    )
    begin {
        $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view'
        $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'
        $uri = '{0}/speakers' -f $BaseUri
        $sessionuri = '{0}/sessions' -f $BaseUri
        $AllSessions = Invoke-RestMethod -Uri $sessionuri

        $Isremote = @{Name='IsRemote';Expression={($_.categories | Where-Object {$_.id -eq '44351';}).categoryItems.name}}

        $CompanyName = @{Name='CompanyName';Expression={($_.questionAnswers | Where-Object {$_.id -eq 43369}).Answer}}
        $LinkedIn = @{Name='LinkedIn';Expression={($_.links | Where-Object {$_.linktype -eq 'LinkedIn'}).url}}
        $Sessionize = @{Name='Sessionize';Expression={($_.links | Where-Object {$_.linktype -eq 'Sessionize'}).url}}
        $Blog = @{Name='Blog';Expression={($_.links | Where-Object {$_.linktype -eq 'Blog'}).url}}
        $Facebook = @{Name='Facebook';Expression={($_.links | Where-Object {$_.linktype -eq 'Facebook'}).url}}
        $Twitter = @{Name='Twitter';Expression={($_.links | Where-Object {$_.linktype -eq 'Twitter'}).url}}
        $CompanyWebsite = @{Name='Company Website';Expression={($_.links | Where-Object {$_.linktype -eq 'Company Website'}).url}}
        $Other = @{Name='Other';Expression={($_.links | Where-Object {$_.linktype -eq 'Other'}) | ForEach-Object { $_ }}}
        $SessionNames = @{
            Name='SessionNames';Expression={$_.sessions | ForEach-Object {
                $id = $_.id
                [PSCustomObject]@{
                    Name = $_.name
                    Room = ($AllSessions.Sessions|Where-Object{$_.id -eq $id}).Room
                }
            }
        }
    }
        $Data = Invoke-RestMethod -Uri $uri
        $Data = $Data|Select-Object *,$CompanyName,$Isremote,$LinkedIn,$Sessionize,$Blog,$Facebook,$Twitter,$CompanyWebsite,$Other,$sessionNames
        if (-not $Data) {
            Write-Warning 'No data returned from Sessionize API'
            return
        }

    }
    process {
        if($search) {
            $Data = $Data | Where-Object { $_.fullName -like "*$search*" }
        }
        if($remote) {
            $Data = $Data | Where-Object { $_.IsRemote -eq 'Remote' }
        }
        if($full) {
            $Data | Select-Object -ExcludeProperty id,isTopSpeaker,questionAnswers,categories,links
        }
        else {
            $Data | Select-Object fullName, companyName,tagLine, $SessionNames
        }
    }
    end {
    }
}
#EndRegion './Public/Get-SQLBitsTDSpeakers.ps1' 107
#Region './Public/Get-SQLBitsTrainingDay.ps1' 0

function Get-SQLBitsTrainingDay {
    <#
.SYNOPSIS
Gets the SQLBits Training Day Schedule from the Sessionize API

.DESCRIPTION
Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to json, excel, psobject, html or csv

.PARAMETER Output
The type of output required. Valid values are json, excel, psobject, html or csv

.PARAMETER fileDirectory
The directory to save the output file to - defaults to Env:Temp

.PARAMETER Show
Whether to open the output file after it has been created

.EXAMPLE
Get-SQLBitsTrainingDay -Output Excel -Show

Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to excel, opens the file and saves it to the default temp directory

.EXAMPLE
Get-SQLBitsTrainingDay -Output Raw

Gets the SQLBits Training Day Schedule from the Sessionize API and outputs as json on the screen

.EXAMPLE
Get-SQLBitsTrainingDay -Output csv -Show

Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to csv, opens the file and saves it to the default temp directory

.EXAMPLE
Get-SQLBitsTrainingDay -Output object

Gets the SQLBits Training Day Schedule from the Sessionize API and outputs as a psobject on the screen

.EXAMPLE
Get-SQLBitsTrainingDay -Output html -Show

Gets the SQLBits Training Day Schedule from the Sessionize API and outputs to html, opens the file and saves it to the default temp directory

.NOTES
Author: Rob Sewell
December 2022
#>

    [CmdletBinding()]
    param (
        [Parameter()]
        [ValidateSet('raw', 'excel', 'object', 'csv', 'html')]
        $Output = 'excel',
        [string]
        $fileDirectory = $env:TEMP,
        [switch]
        $Show
    )

    $BaseUri = 'https://sessionize.com/api/v2/sydgl43f/view'
    $Date = Get-Date -Format 'yyyy-MM-dd-HH-mm-ss'

    #TODO Add other options
    $filter = 'Schedule'
    switch ($filter) {
       # 'All' {
       # $uri = '{0}/All' -f $BaseUri
       # }
        'Schedule' {
            $uri = '{0}/All' -f $BaseUri
        }
        'Sessions' {
            $uri = '{0}/sessions' -f $BaseUri
        }
        'Speakers' {
            $uri = '{0}/speakers' -f $BaseUri
        }
        Default {
            $uri = '{0}/All' -f $BaseUri
        }
    }

    $Data = Invoke-RestMethod -Uri $uri
    if(-not $Data){
        Write-Warning 'No data returned from Sessionize API'
        return
    }
    $rooms = ($data.rooms | Sort-Object name)
    if(-not $rooms){
        Write-Warning 'No rooms returned from Sessionize API'
        return
    }
    $Speakers = $data.speakers
    if(-not $Speakers){
        Write-Warning 'No Speakers returned from Sessionize API'
        return
    }
    # Thank you Shane - https://nocolumnname.blog/2020/10/29/pivot-in-powershell/
    $props = @(
        @{ Name = 'Day' ; Expression = { $Psitem.Group[0].startsAt.DayOfWeek } }
        @{ Name = 'Date' ; Expression = { $Psitem.Group[0].startsAt.tolongdatestring() } }
        @{ Name = 'StartTime' ; Expression = { $Psitem.Group[0].startsAt.ToShortTimeString() } }
        @{ Name = 'EndTime' ; Expression = { $Psitem.Group[0].EndsAt.ToShortTimeString() } }
        foreach ($room in $rooms) {
            $rn = $room.Name
            @{
                Name       = $rn
                Expression = {
                    '{0}
{1}'
  -f @(
                        ($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).title,
                        (($Psitem.Group | Where-Object { $PSItem.roomID -eq $room.id }).Speakers.ForEach{ $Speakers | Where-Object id -EQ $_ }.FullName -join ' ')
                    )

                }.GetNewClosure()
            }
        }
    )

    if($IsCoreCLR){
        $rawsessions = $Data.sessions 
    } else {
        $rawsessions = $Data.sessions | Select -Property id, title,@{Name = 'startsAt';expression = {[datetime]$_.startsAt}} , @{Name = 'endsAt';expression = {[datetime]$_.endsAt}}, roomID, speakers

    }
    $sessions = $rawsessions | Group-Object -Property StartsAt | Select-Object $props

    switch ($output) {
        'Raw' {
            $Data
        }
        'object' {
            $sessions
        }
        'Excel' {
            if (Get-Module -Name ImportExcel -ErrorAction SilentlyContinue -ListAvailable) {
                if ($filter -eq 'Schedule') {

                    $FilePath = '{0}\SQLBitsSchedule{1}_{2}.xlsx' -f $fileDirectory, $filter, $Date

                    $sessions | Group-Object Day | ForEach-Object {

                        $worksheetName = $_.Name
                        $excel = $_.Group | Export-Excel -Path $FilePath -WorksheetName $worksheetName -AutoSize  -FreezePane 2, 5 -PassThru
                        1..15 | ForEach-Object {
                            Set-ExcelRow -ExcelPackage $excel -WorksheetName $worksheetName -Row $_ -Height 30 -WrapText
                        }

                        $rulesparam = @{
                            Address   = $excel.Workbook.Worksheets[$WorkSheetName].Dimension.Address
                            WorkSheet = $excel.Workbook.Worksheets[$WorkSheetName]
                        }

                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Coffee Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Quick Break",$E1)))' -BackgroundColor GoldenRod -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Keynote",$E1)))' -BackgroundColor BlueViolet -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Lunch",$E1)))' -BackgroundColor Chocolate  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Prize",$E1)))' -BackgroundColor PowderBlue  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Free Time",$E1)))' -BackgroundColor GoldenRod  -ForegroundColor White -StopIfTrue
                        Add-ConditionalFormatting @rulesparam -RuleType 'Expression'  -ConditionValue 'NOT(ISERROR(FIND("Registration",$E1)))' -BackgroundColor DarkOrange  -ForegroundColor White -StopIfTrue
                        Close-ExcelPackage $excel
                    }
                    if ($Show) {
                        Invoke-Item $filepath
                    }else {
                        Write-Output "Excel file saved to $FilePath"
                    }
                }
            } else {
                Write-Warning 'You need to install ImportExcel to use this option but here is a CSV instead'
                $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date
                $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation
                if ($Show) {
                    Invoke-Item $filepath
                }else {
                    Write-Output "Csv file saved to $FilePath"
                }
            }

        }
        'CSv' {
            $FilePath = '{0}\SQLBits_{1}_{2}.csv' -f $fileDirectory, $filter, $Date
            $sessions | Sort-Object Day, StartsAt | Export-Csv -Path $FilePath -NoTypeInformation
            if ($Show) {
                Invoke-Item $filepath
            }else {
                Write-Output "Csv file saved to $FilePath"
            }
        }
        'html' {
            $FilePath = '{0}\SQLBits_{1}_{2}.html' -f $fileDirectory, $filter, $Date
            $sessions | ConvertTo-Html | out-file $FilePath
                if ($Show) {
                    Invoke-Item $filepath
                }else {
                    Write-Output "Html file saved to $FilePath"
                }
        }
        Default {

        }
    }
}
#EndRegion './Public/Get-SQLBitsTrainingDay.ps1' 203