EmployeeDataEx.psm1

function Get-EmployeeData {
<#
    .Synopsis
    Function to get Employee data from SQL database.
#>

[CmdletBinding(
    DefaultParameterSetName = 'filter'
)]
param (
    # Employee first name.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [SupportsWildcards()]
    [string]$GivenName, 

    # Employee last name.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [SupportsWildcards()]
    [string]$Surname,

    # Employee information.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [SupportsWildcards()]
    [string]$Description,

    # Employee title.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [SupportsWildcards()]
    [string]$Title,

    # Retrieves new-hire employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$BeforeStartDate,

    # Retrieves currently hired employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$BeforeEndDate,

    # Retrieves employees that were already hired.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$AfterStartDate,

    # Retrieves terminated employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$AfterEndDate,

    # Retrieves employees with Id greater than provided.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [int]$IdGreaterThan,

    # Retrieves employees with Id lower than provided.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [int]$IdLowerThan,

    # Filter required to get employees data. Requires T-SQL format.
    [Parameter(
        ParameterSetName = 'filter',
        Mandatory = $true,
        HelpMessage = 'Filter required to get employees data. Requires T-SQL format.'
    )]
    [string]$Filter
)

    $parameterMap = @{
        # Parameter = SQL Column Name
        GivenName = 'First Name'
        Surname = 'Last Name'
        Description = 'User Information Detailed'
        Title = 'Title'
        SamAccountName = 'Login'
        Id = 'Employee Id'
    }

    if (-not $Filter) {
        $mappedParameters = '^({0})$' -f ($parameterMap.Keys -join '|')
        $queryList = New-Object System.Collections.ArrayList
        switch -Regex ($PSBoundParameters.Keys) {
            $mappedParameters {
                $wildcard = [System.Management.Automation.WildcardPattern]$PSBoundParameters[$_]
                $wql = $wildcard.ToWql()
                $item = "[{0}] LIKE '{1}'" -f $parameterMap[$_], $wql
                $queryList.Add($item) | Out-Null
            }

            ^Before {
                $field = $_ -replace '^Before'
                $item = "[{0}] > '{1}'" -f $field, (Get-Date)
                $queryList.Add($item) | Out-Null
            }

            ^After {
                $field = $_ -replace '^After'
                $item = "[{0}] < '{1}'" -f $field, (Get-Date)
                $queryList.Add($item) | Out-Null
            }

            GreaterThan$ {
                $fieldMappedTo = $_ -replace 'GreaterThan$'
                $field = $parameterMap[$fieldMappedTo]
                $item = '[{0}] > {1}' -f $field, $PSBoundParameters[$_]
                $queryList.Add($item) | Out-Null
            }

            LowerThan$ {
                $fieldMappedTo = $_ -replace 'LowerThan$'
                $field = $parameterMap[$fieldMappedTo]
                $item = '[{0}] < {1}' -f $field, $PSBoundParameters[$_]
                $queryList.Add($item) | Out-Null
            }
        }
        $Filter = $queryList -join ' AND '
    }
    $selectList = foreach ($key in $parameterMap.Keys) {
        "[{0}] AS '{1}'" -f $parameterMap.$key, $key
    }
    $select = $selectList -join ', '

    $query = @"
    SELECT
        Department, StartDate, EndDate,
        $select    
    FROM
        Employees
    WHERE
        $Filter  
"@
    Write-Verbose $query
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection @(
        "Server=localhost;Database=HRDB;Integrated Security=True"
    )
    $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter @(
        $query,
        $sqlConnection
    )
    $dataSet = New-Object System.Data.DataSet
    $sqlAdapter.Fill($dataSet) | Out-Null
    $SqlConnection.Close()
    $dataSet.Tables[0]
}