EmployeeData.psm1

#requires -version 3.0
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,

    # 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) {
        $queryList = New-Object System.Collections.ArrayList
        switch ($PSBoundParameters.Keys) {
            { $parameterMap.Keys -contains $_ } {
                $wildcard = [System.Management.Automation.WildcardPattern]$PSBoundParameters[$_]
                $wql = $wildcard.ToWql()
                $item = "[{0}] LIKE '{1}'" -f $parameterMap[$_], $wql
                $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 "Using query: $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]
}