#--------------Connect to SQL to Get List of Known Accounts by ObjectID---------------------

#Import the SQL Module & Query to Select Data All ObjectID
Import-Module "C:\Scheduled-Tasks\SQL_Module\SqlServer.psm1"
$Query= "SELECT ObjectID FROM [Azure_Data].[dbo].[Azure_User]" 
$SQL_ObjectIDs=Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query "$Query"

#Build Usable Array for SQL Data (System.Data.DataRow)
$SQL_Array=@()
Foreach($SQLItem in $SQL_ObjectIDs){
    $SQL_Array+=$SQLItem.Item(0);
}

#------------------------------Gather All User Data in Azure AD------------------------------
$AppID = 'b7bbcf98-f84e-xxxxx-66427c667105'
$TenantID = '8d151ea2-4bxxxx09b-e5e020c04470'
$Thumbprint = 'D5EA77AD4C9xxxxxD35032AF04E022C71F8'
Connect-AzureAD -TenantId $tenantid -ApplicationId $appid -CertificateThumbprint $Thumbprint
$Userdata=Get-AzureADUser | Select *


#Compare Values to see allow only updating current accounts known to SQL 
$ObjectID_LeftOver=Compare-Object -ReferenceObject $SQL_Array  -DifferenceObject $Userdata.ObjectID -IncludeEqual | ? {$_.SideIndicator -eq '=='} | Select -ExpandProperty inputobject

#--------------------------------------------------------------------------------------------

#Loop Through Matching ObjectID and Update SQL only for these acccounts
    Foreach($Items in $ObjectID_LeftOver){

    $MyValues=$Userdata | Where{$_.ObjectID -eq $Items}
    
    #Variables 
    $DisplayName = $MyValues.DisplayName
    $AccountEnabled = $MyValues.AccountEnabled
    $JobTitle = $MyValues.JobTitle
    $Mobile = $MyValues.Mobile
    $TelephoneNumber = $MyValues.TelephoneNumber
    $GivenName = $MyValues.GivenName
    $Surname = $MyValues.Surname 
    
    #Update Query for Azure Users Table 
    $Update_Query="UPDATE [dbo].[Azure_User]
      SET [DisplayName] = '$Displayname',
          [AccountEnabled] = '$AccountEnabled',         
          [JobTitle] = '$JobTitle',
          [Mobile] = '$Mobile',
          [TelephoneNumber] = '$TelephoneNumber',
          [Surname] = '$Surname',
          [GivenName] = '$GivenName' WHERE [ObjectID] = '$Items'"

    #Executes Command to Update Record 
    Invoke-Sqlcmd  -ConnectionString "Data Source=Media-Center\SQLExpress;Initial Catalog=Azure_Data; Integrated Security=True;" -Query $Update_Query
}