HRMS

Write-Host -ForegroundColor Yellow "Get-HRMSData"

$username = 'LEA100ILM'
$password = 'brunswic'
$connectionString = "DSN=HRMS;Uid=$username;Pwd=$password;"
$sqlQuery = @"
select UID AS UID,
EMP_FIRST_NM AS FIRST_NAME,
EMP_MIDDLE_NM AS MIDDLE_NAME,
EMP_LAST_NM AS LAST_NAME,
EMP_NICK_NM AS NICKNAME,
EMP_BIRTH_DTE,
EMP_STATUS_CD,
CES.ESTD_DESC AS EMP_STATUS_DESC,
EMP_HRMS_ID,
EMP_hPhone_ad AS HOME_PHONE,
EMP_cPhone_ad AS CELL_PHONE,
EMP_oPhone_ad AS OFFICE_PHONE,
CP.POS_TITLE_CD AS CURRENT_TITLE_CD,
CP.POS_DESC AS CURRENT_POS_TITLE_DESC,
CP.POS_SITE_CD AS CURRENT_SITE_NUMBER,
CS.STE_SITE_NM AS CURRENT_SITE_NAME,
CA.EAS_PRIMARY_IND AS CURRENT_PRIMARY_IND,
CHAR(CA.EAS_END_DTE,USA) as ASSIGNMENT_END_DATE,
FP.POS_TITLE_CD AS FUTURE_TITLE_CD,
FP.POS_DESC AS FUTURE_POS_TITLE_DESC,
FP.POS_SITE_CD AS FUTURE_SITE_NUMBER,
FS.STE_SITE_NM AS FUTURE_SITE_NAME,
FA.EAS_PRIMARY_IND AS FUTURE_PRIMARY_IND,
CHAR(FA.EAS_START_DTE,USA) as ASSIGNMENT_START_DATE,
PSC_STATUS_CD as PENDING_STATUS_CD,
PES.ESTD_DESC AS PENDING_STATUS_DESC
from HRMSLEA.EMPLOYEE
LEFT OUTER JOIN HRMSLEA.EMPLOYEE_ASSIGNMENT CA ON EMP_SSN_TXT = CA.EAS_SSN_TXT
LEFT OUTER JOIN HRMSLEA.ASSIGNMENT_FUTURE FA ON EMP_SSN_TXT = FA.EAS_SSN_TXT
LEFT OUTER JOIN HRMSLEA.POSITION CP ON CA.EAS_POS_DOCID = CP.POS_DOCID
LEFT OUTER JOIN HRMSLEA.POSITION FP ON FA.EAS_POS_DOCID = FP.POS_DOCID
LEFT OUTER JOIN HRMSSHR.UID ON EMP_SSN_TXT = UID_SSN_TXT
LEFT OUTER JOIN HRMSSHR.SITE CS ON CP.POS_SITE_CD = CS.STE_SITE_CD
LEFT OUTER JOIN HRMSSHR.SITE FS ON FP.POS_SITE_CD = FS.STE_SITE_CD
LEFT OUTER JOIN HRMSSHR.EMPLOYMENT_STATUS_DOM CES ON CES.ESTD_ID = EMP_STATUS_CD
LEFT OUTER JOIN HRMSLEA.PENDING_STATUS_CHG ON EMP_SSN_TXT = PSC_SSN_TXT
LEFT OUTER JOIN HRMSSHR.EMPLOYMENT_STATUS_DOM PES ON PES.ESTD_ID = PSC_STATUS_CD
WHERE CA.EAS_PRIMARY_IND = 1 OR FA.EAS_PRIMARY_IND = 1
"@
$connection = New-Object System.Data.Odbc.OdbcConnection($connectionString)
$connection.open()
$runQuery = New-Object system.Data.Odbc.OdbcCommand($sqlQuery,$connection)
$dataAdapter = New-Object system.Data.Odbc.OdbcDataAdapter($runQuery)
$dataTable = New-Object system.Data.datatable
$null = $dataAdapter.fill($dataTable)
$connection.close()

$allData = foreach ( $i in ($dataTable.Where{[string]$_.UID} | group UID) )
{
    if ( $i.Count -eq 1 )
    {
        $i.Group
    }
    else
    {
        $i.Group.Where{$_.CURRENT_PRIMARY_IND -and $_.FUTURE_PRIMARY_IND}
    }
}

$allData | Export-Clixml $HRMSDataFile

$IAMData = foreach ( $i in $allData )
{
    if ([string]$i.CURRENT_SITE_NUMBER -and [string]$i.FUTURE_SITE_NUMBER)
    {
        if ([string]$i.ASSIGNMENT_END_DATE)
        {
            if ($todaysDate -lt [datetime]$i.ASSIGNMENT_END_DATE)
            {
                $schoolCode = $i.CURRENT_SITE_NUMBER.Trim()
                $title = $i.CURRENT_POS_TITLE_DESC.Trim()
                $titleCode = $i.CURRENT_TITLE_CD.Trim()
                $statusCode = $i.EMP_STATUS_CD.Trim()
                $status = $i.EMP_STATUS_DESC.Trim()
            }
            else
            {
                $schoolCode = $i.FUTURE_SITE_NUMBER.Trim()
                $title = $i.FUTURE_POS_TITLE_DESC.Trim()
                $titleCode = $i.FUTURE_TITLE_CD.Trim()
                $statusCode = if (![string]$i.PENDING_STATUS_CD){$i.EMP_STATUS_CD.Trim()} else{$i.PENDING_STATUS_CD.Trim()}
                $status = if (![string]$i.PENDING_STATUS_DESC){$i.EMP_STATUS_DESC.Trim()} else{$i.PENDING_STATUS_DESC.Trim()}
            }
        }
        else
        {
            $schoolCode = $i.FUTURE_SITE_NUMBER.Trim()
            $title = $i.FUTURE_POS_TITLE_DESC.Trim()
            $titleCode = $i.FUTURE_TITLE_CD.Trim()
            $statusCode = if (![string]$i.PENDING_STATUS_CD){$i.EMP_STATUS_CD.Trim()} else{$i.PENDING_STATUS_CD.Trim()}
            $status = if (![string]$i.PENDING_STATUS_DESC){$i.EMP_STATUS_DESC.Trim()} else{$i.PENDING_STATUS_DESC.Trim()}
        }
    }
    elseif ([string]$i.CURRENT_SITE_NUMBER)
    {
        $schoolCode = $i.CURRENT_SITE_NUMBER.Trim()
        $title = $i.CURRENT_POS_TITLE_DESC.Trim()
        $titleCode = $i.CURRENT_TITLE_CD.Trim()
        $statusCode = $i.EMP_STATUS_CD.Trim()
        $status = $i.EMP_STATUS_DESC.Trim()
    }
    elseif ([string]$i.FUTURE_SITE_NUMBER)
    {
        $schoolCode = $i.FUTURE_SITE_NUMBER.Trim()
        $title = $i.FUTURE_POS_TITLE_DESC.Trim()
        $titleCode = $i.FUTURE_TITLE_CD.Trim()
        $statusCode = if (![string]$i.PENDING_STATUS_CD){$i.EMP_STATUS_CD.Trim()} else{$i.PENDING_STATUS_CD.Trim()}
        $status = if (![string]$i.PENDING_STATUS_DESC){$i.EMP_STATUS_DESC.Trim()} else{$i.PENDING_STATUS_DESC.Trim()}
    }

    foreach ($p in 'HOME_PHONE','CELL_PHONE','OFFICE_PHONE')
    {
        if ([string]$i.$p -notmatch '^(|0+)$')
        {
            [string]$i.$p | sv $p
        }
        else
        {
            $null | sv $p
        }
    }

    $i | select `
        @{l='EmployeeID';e={$_.UID.Trim()}},
        @{l='GivenName';e={$_.FIRST_NAME.Trim()}},
        @{l='OtherName';e={$_.MIDDLE_NAME.Trim()}},
        @{l='Surname';e={$_.LAST_NAME.Trim()}},
        @{l='NickName';e={$_.NICKNAME.Trim()}},
        @{l='Birthdate';e={$_.EMP_BIRTH_DTE}},
        @{l='HomePhone';e={$HOME_PHONE}},
        @{l='MobilePhone';e={$CELL_PHONE}},
        @{l='OfficePhone';e={$OFFICE_PHONE}},
        @{l='SchoolCode';e={$schoolCode}},
        @{l='Title';e={$title}},
        @{l='TitleCode';e={$titleCode}},
        @{l='StatusCode';e={$statusCode}},
        @{l='Status';e={$status}},
        @{l='EndDate';e={[string]$_.ASSIGNMENT_END_DATE.Trim()}},
        @{l='StartDate';e={[string]$_.ASSIGNMENT_START_DATE.Trim()}}
        <#@{l='EmployeeID';e={$_.UID.Trim()}},
        @{l='GivenName';e={$_.FIRST_NAME.Trim()}},
        @{l='OtherName';e={$_.MIDDLE_NAME.Trim()}},
        @{l='Surname';e={$_.LAST_NAME.Trim()}},
        @{l='NickName';e={$_.NICKNAME.Trim()}},
        @{l='Birthdate';e={$_.EMP_BIRTH_DTE}},
        @{l='CurrentSchoolCode';e={$_.CURRENT_SITE_NUMBER.Trim()}},
        @{l='FutureSchoolCode';e={$_.FUTURE_SITE_NUMBER.Trim()}},
        @{l='CurrentTitle';e={$_.CURRENT_POS_TITLE_DESC.Trim()}},
        @{l='FutureTitle';e={$_.FUTURE_POS_TITLE_DESC.Trim()}},
        @{l='CurrentTitleCode';e={$_.CURRENT_TITLE_CD.Trim()}},
        @{l='FutureTitleCode';e={$_.FUTURE_TITLE_CD.Trim()}},
        @{l='StatusCode';e={$_.EMP_STATUS_CD.Trim()}},
        @{l='FutureStatusCode';e={$_.PENDING_STATUS_CD.Trim()}},
        @{l='Status';e={$_.EMP_STATUS_DESC.Trim()}},
        @{l='EndDate';e={[string]$_.ASSIGNMENT_END_DATE.Trim()}},
        @{l='StartDate';e={[string]$_.ASSIGNMENT_START_DATE.Trim()}}#>
}

$IAMData | ? {$_.EmployeeID -match '\d+'} | Export-Clixml $IAMDataFile
<#$dataTable.Where{$_.UID -match '\d+'} | Export-Clixml $HRMSDataFile

$IAMData = $dataTable | select `
    @{l='EmployeeID';e={$_.UID.Trim()}},
    @{l='GivenName';e={$_.FIRST_NAME.Trim()}},
    @{l='OtherName';e={$_.MIDDLE_NAME.Trim()}},
    @{l='Surname';e={$_.LAST_NAME.Trim()}},
    @{l='NickName';e={$_.NICKNAME.Trim()}},
    @{l='idautoPersonBirthdate';e={[datetime]$_.EMP_BIRTH_DTE}},
    @{l='idautoPersonSchoolCodes';e={$_.CURRENT_SITE_NUMBER.Trim()}},
    @{l='Title';e={$_.CURRENT_POS_TITLE_DESC.Trim()}},
    @{l='TitleCode';e={$_.CURRENT_TITLE_CD.Trim()}},
    @{l='StatusCode';e={$_.EMP_STATUS_CD.Trim()}},
    @{l='Status';e={$_.EMP_STATUS_DESC.Trim()}}

$IAMData.Where{$_.EmployeeID -match '\d+'} | Export-Clixml $IAMDataFile#>