Public Function gblnProcessData(ByRef pobjConn As ADODB.Connection) As Boolean
On Error GoTo PROC_ERR
Dim objRst As ADODB.Recordset
Dim strRACFID As String ' store RACFID as variable. Used to determine if RACFID is same previous RACFID
Dim strRole As String ' store concatenated string of role
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim z As Integer
Dim blnReturn As Boolean
blnReturn = False
Dim strSQL As String
Dim arrSQL(2) As String
Set objRst = New ADODB.Recordset
' RACF exception data
strSQL = ""
strSQL = strSQL & "SELECT I.RecordID, I.RACFID, I.FullName, I.Role, I.PermissionString, I.Access, I.MaxSequence, I.Hash, I.LastLoggedIn, 1 AS RACFException " & vbNewLine
strSQL = strSQL & " FROM Import AS I INNER JOIN RACFExceptions ON I.RACFID = RACFExceptions.RACF " & vbNewLine
strSQL = strSQL & " ORDER BY I.RACFID, I.Role "
arrSQL(0) = strSQL
' Matched data
strSQL = ""
strSQL = strSQL & "SELECT I.RecordID, I.RACFID, I.FullName, I.Role, I.PermissionString, I.Access, I.MaxSequence, I.Hash, I.LastLoggedIn, 0 AS RACFException " & vbNewLine
strSQL = strSQL & " FROM Import as I " & vbNewLine
strSQL = strSQL & " WHERE RACFID NOT IN(SELECT RACF FROM RACFExceptions) " & vbNewLine
strSQL = strSQL & " ORDER BY I.RACFID, I.Role "
arrSQL(1) = strSQL
strSQL = ""
strSQL = strSQL & "SELECT RACFID, MaxSequence " & vbNewLine
strSQL = strSQL & "FROM Import " & vbNewLine
strSQL = strSQL & "ORDER BY RACFID, Role "
' Set MaxSequence to identify last record with full permission string
objRst.Open strSQL, pobjConn, adOpenDynamic, adLockOptimistic
If objRst.BOF Or objRst.EOF Then
MsgBox "No data has been imported !", vbCritical, "NO DATA !"
Else
With objRst
.MoveFirst
Do Until .EOF
If (strRACFID <> "" And strRACFID <> !RACFID) Or .EOF Then
.MovePrevious
!MaxSequence = True
.MoveNext
End If
.MoveNext
If .EOF Then
.MovePrevious
!MaxSequence = True
Else
.MovePrevious
End If
strRACFID = !RACFID
.MoveNext
Loop
End With
objRst.Close
' reset udt's
If blnLoadProfiles Then
ReDim mudtOutputRowsMatched.OutputRow(0)
mudtOutputRowsMatched.Count = 0
ReDim mudtOutputRowsExceptions.OutputRow(0)
mudtOutputRowsExceptions.Count = 0
For z = 1 To UBound(arrSQL)
objRst.Open arrSQL(z - 1), pobjConn, adOpenDynamic, adLockOptimistic
With objRst
If Not .BOF And Not .EOF Then
.MoveFirst
Do Until .EOF
If strRACFID <> !RACFID Then
' New RACFID
strRACFID = Trim(!RACFID)
strRole = Trim(!Role)
!PermissionString = strRole
If !MaxSequence = True Then
!Hash = modMD5.MD5_string(strRole)
End If
Else
' RACFID same as above record
' Build role string, concatenate from previous record
strRole = strRole & "|" & Trim(!Role)
!PermissionString = strRole
If !MaxSequence = True Then
!Hash = modMD5.MD5_string(strRole)
End If
End If
.Update
' match users against profile table
For i = 0 To mudtProfiles.Count - 1
If !Hash = mudtProfiles.Profile(i).Hash Then
!Access = mudtProfiles.Profile(i).Access
If !RACFException = 0 Then
j = j + 1
mudtOutputRowsMatched.Count = mudtOutputRowsMatched.Count + 1
ReDim Preserve mudtOutputRowsMatched.OutputRow(j)
mudtOutputRowsMatched.OutputRow(j - 1).FullName = !FullName
mudtOutputRowsMatched.OutputRow(j - 1).Access = mudtProfiles.Profile(i).Access
mudtOutputRowsMatched.OutputRow(j - 1).RACFID = Left(strRACFID, 4)
If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
mudtOutputRowsMatched.OutputRow(j - 1).LastLoggedIn = !LastLoggedIn
End If
Else
k = k + 1
mudtOutputRowsExceptions.Count = mudtOutputRowsExceptions.Count + 1
ReDim Preserve mudtOutputRowsExceptions.OutputRow(k)
mudtOutputRowsExceptions.OutputRow(k - 1).FullName = !FullName
mudtOutputRowsExceptions.OutputRow(k - 1).Access = mudtProfiles.Profile(i).Access
mudtOutputRowsExceptions.OutputRow(k - 1).RACFID = Left(strRACFID, 4)
If Nz(!LastLoggedIn, vbNullString) <> vbNullString Then
mudtOutputRowsExceptions.OutputRow(k - 1).LastLoggedIn = !LastLoggedIn
End If
End If
Exit For
End If
Next i
.MoveNext
Loop
End If
End With
objRst.Close
Next z ' iterate thru both pieces of SQL
blnReturn = True
End If ' blnLoadProfiles
End If ' test if data has been loaded
PROC_EXIT:
On Error Resume Next
objRst.Close
Set objRst = Nothing
gblnProcessData = blnReturn
Exit Function
PROC_ERR:
MsgBox "Error occurred in modProcess.gblnProcessData() : " & Err.Number & vbNewLine & Err.Description
Resume PROC_EXIT
Resume
End Function