#Begin Connexion to the Exchange CAS server

#Script run as scheduled task with  AD account credentials allowed to read secure string, secure string will be stored in LSA secrets.

#With this command you do not need to install the Exchange Management Shell on the server, change the fqdn Cas-server.ldap389.local


$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://Cas-server.ldap389.local/PowerShell/
Import-PSSession $s -allowclobber


#Retrieve secure string for Oracle database password, change $user=adm, for database user. Only the AD account running the scheduled can read this secure string.

$Pword = get-content cred.txt | convertto-securestring
$user = "adm"
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord


#Connexion to the Oracle DB with odp.net2 shipped with the ODAC package:

$Assemblyfile = "C:\oracle\odp.net\bin\2.x\Oracle.DataAccess.dll"
[Reflection.Assembly]::LoadFile($AssemblyFile) | out-null
$OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection

#Change host, Port, Service_Name values: Database connexion string
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=ldap389-oraclesrv)(Port=1532)))(CONNECT_DATA=(SERVICE_NAME=HR1)));User ID="+$credential.GetNetworkCredential().username+";Password="+$credential.GetNetworkCredential().password

$OracleConnection.Open()

#Date in format MM/DD/YYYY HH:MI:SS, checking MBX for users who will be starting in the company the next 3 days.
$date = Get-Date((get-date).addDays(+3)) -format "MM/dd/yyyy hh:mm:ss"


#Get users starting in 3 days in HR database, change table name (LDAP389FAMILY), and attributes: HRCODE is customAttribute13.

$CommandText  = "SELECT HRCODE,START_DATE FROM LDAP389FAMILY WHERE START_DATE BETWEEN SYSTEMDATE AND to_date('"+$date+"', 'MM/DD/YYYY HH24:MI:SS')"
 
$OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
$OracleCommand.CommandText = $CommandText
$OracleCommand.Connection = $OracleConnection
$OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
$OracleDataAdapter.SelectCommand = $OracleCommand
$DataSet = New-Object -TypeName System.Data.DataSet
$OracleDataAdapter.Fill($DataSet)
$OracleDataAdapter.Dispose()
$OracleCommand.Dispose()
$OracleConnection.Close()
$Oracleresults = $DataSet.Tables[0] 


foreach($Oracleresult in $Oracleresults)
	{$customAttr13 = $Oracleresult.HRCODE

	#Check if mailbox was created before start date: cool!
	$mbx = get-mailbox -filter "(CustomAttribute13 -eq '$customAttr13')"
	if($mbx){
	write-host '--------------------------------------------------'
	write-host 'MBX already created'
	write-host 'Exchange information'
	$mbx | select CustomAttribute13,WindowsEmailAddress,whencreated
	write-host 'HRCODE: '$customAttr13
	write-host 'Start date: '$Oracleresult.START_DATE
	}
	#Mailbox needs to be created ASAP!
	else{
	write-host '--------------------------------------------------'
	write-host 'Create MBX ASAP'
	write-host 'HRCODE: '$customAttr13
	write-host 'Start date: '$Oracleresult.START_DATE
	}

	}
	
#Disconnect from Exchange CAS server
Remove-PSSession $session.id