$main = {
    Get-ExcelData
}
function Get-ExcelData {
    $date = Get-Date -format "yyyyMMdd_HHmm"
    $Infile = "C:\Users\John\Desktop\sample.xlsx"
    $Outfile = "C:\Users\John\Desktop\" + date + "results.txt"
    $sheets = Get-ExcelSheetInfo $Infile
    $ExcelData = @()
    Write-Host "--Reading" $Infile "into datatable"
    foreach ($worksheet in $sheets) {
        $ExcelData += Import-Excel -Path $Infile -WorksheetName $worksheet.name
    }
    $ExcelData | Select-Object -Property FacilityName, "Region-2`nCounty-3`nDistrict-4" | Out-Null
    $rowMax = $ExcelData.Length
    Write-Host "--Executing SQL query"
    $UBData = Get-UBData
    $UBData = $UBData.Tables[0].EXTERNAL_CODE
    for($i=0; $i -le $rowMax; $i++) {
        if ($ExcelData[$i]."Region-2`nCounty-3`nDistrict-4") {
            $RCD = $ExcelData[$i]."Region-2`nCounty-3`nDistrict-4"
            $RCDTS = $RCD.substring(0,2) + "-" + $RCD.substring(2,3) + "-" + $RCD.Substring(5,4) + '-' + $ExcelData[$i].Type + '-' + $ExcelData[$i].School
        }
        $ExcelData[$i] | Where-Object {$_ -ne $null} | Add-Member -MemberType NoteProperty "RCDTS" -Value $RCDTS
    }
    $resultCount = 0
    Write-Host "--Comparing data sets"
    for($i=0; $i -le $rowMax; $i++)  {
        $excludeTypes = @("1","2","6","9","D")
        if (($UBData -notcontains $ExcelData[$i].RCDTS) -and ($excludeTypes -notcontains $ExcelData[$i].Cat) -and ($ExcelData[$i].RCDTS -notlike '77-777*') -and ($ExcelData[$i].RCDTS -ne $null)) {
            Add-Content $Outfile $($ExcelData[$i].FacilityName + ", " + $ExcelData[$i].RCDTS)
            $resultCount++
        }
    }
    Write-Host "`r`nWrote" $resultCount "results to" $Outfile
}
function Get-UBData {
    $DataSet = $null
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=server;Initial Catalog=database;Integrated Security=True;ApplicationIntent=ReadOnly"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "SELECT ORG_CODE, ACCT_CODE, NAME, EXTERNAL_CODE, ADDRESS_L1, CITY, STATE, POSTAL_CODE, MAIN_PHONE, KEYWORD_1, KEYWORD_2 FROM  ACCT_MASTER WHERE (ORG_CODE = 10) AND (TYPE = '9S') AND (CLASS = 'O')"
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandTimeout = 0
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $DataSet.DataSetName = 'ROOT'
    $SqlAdapter.Fill($DataSet) | Out-Null
    $SqlConnection.Close()
    return $DataSet
}
& $main