31. One Identity SAP

21 февраля 2020 г.

15:30

#If Not SCRIPTDEBUGGER Then

References VI.Projector.Dll

References VI.Projector.Database.Dll

References VI.Projector.SAP.JobComponent.Dll

References VI.Projector.SAP.Dll

References VI.Projector.SAP.Data.Dll

References JobComponent.dll

References VI.Projector.Connector.Base.dll

References VI.Projector.JobComponent.dll

'References VI.Projector.SAP.JobComponent.Dll

Imports VI.Projector.Database

Imports System.Collections.Generic

Imports VI.Projector.SAP.JobComponent

Imports VI.Projector.SAP.ProjectorSAPConnector

Imports VI.Projector.Database.Serialization

Imports VI.Projector.Connector.Base

Imports VI.Projector.SAP

Imports VI.Projector

Imports System.Reflection

Imports VI.Projector.Connection

Imports VI.Projector.Projection

Imports VI.Projector.JobComponent

Imports System.Collections.Concurrent

#End If

Public Sub Z_SAP_Sync_Table_HRP1002()

Dim sapTblName As String = "HRP1002"

Dim wherecond As String = " MANDT = sy-mandt AND PLVAR = '01' AND ( OTYPE = 'S' OR OTYPE = 'O' ) AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_HRT1002()

Dim sapTblName As String = "HRT1002"

Dim wherecond As String = " MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_HRP1001()

Dim sapTblName As String = "HRP1001"

Dim wherecond As String = " MANDT = sy-mandt AND PLVAR = '01' AND ( ( ( RELAT = '002' OR RELAT = '012' ) AND SCLAS IN ( 'O', 'S' ) AND OTYPE IN ( 'O', 'S' ) ) OR SCLAS = 'Q' ) AND RSIGN = 'A' AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_HRP1000()

Dim sapTblName As String = "HRP1000"

Dim wherecond As String = "MANDT = sy-mandt AND PLVAR = '01' AND ( OTYPE = 'S' OR OTYPE = 'O' ) AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_HRPAD31()

Dim sapTblName As String = "HRPAD31"

Dim wherecond As String = "MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_PA0290()

Dim sapTblName As String = "PA0290"

Dim wherecond As String = "MANDT = sy-mandt AND SUBTY = '802' AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_T001()

Dim sapTblName As String = "T001"

Dim wherecond As String = "MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_T005U()

Dim sapTblName As String = "T005U"

Dim wherecond As String = "MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_T500P()

Dim sapTblName As String = "T500P"

Dim wherecond As String = "MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_T77TP()

Dim sapTblName As String = "T77TP"

Dim wherecond As String = "MANDT = sy-mandt "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_T7RU9A()

Dim sapTblName As String = "T7RU9A"

Dim wherecond As String = "MANDT = sy-mandt AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_PA0035()

Dim sapTblName As String = "PA0035"

Dim wherecond As String = "MANDT = sy-mandt AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_PA0000()

Dim sapTblName As String = "PA0000"

Dim wherecond As String = "MANDT = sy-mandt AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_PA0001()

Dim sapTblName As String = "PA0001"

Dim wherecond As String = "MANDT = sy-mandt AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_PA0002()

Dim sapTblName As String = "PA0002"

Dim wherecond As String = "MANDT = sy-mandt AND ENDDA >= sy-datum "

Z_SAP_Sync_Table(sapTblName, wherecond)

End Sub

Public Sub Z_SAP_Sync_Table_ALL_TABLES()

Z_SAP_Sync_Table_HRP1000()

Z_SAP_Sync_Table_HRP1001()

Z_SAP_Sync_Table_HRP1002()

Z_SAP_Sync_Table_HRPAD31()

Z_SAP_Sync_Table_HRT1002()

Z_SAP_Sync_Table_PA0035()

Z_SAP_Sync_Table_PA0290()

Z_SAP_Sync_Table_T001()

Z_SAP_Sync_Table_T005U()

Z_SAP_Sync_Table_T005U()

Z_SAP_Sync_Table_T500P()

Z_SAP_Sync_Table_T77TP()

'временно отключено

' Z_SAP_Sync_Table_T7RU9A()

Z_SAP_Sync_Table_PA0000()

Z_SAP_Sync_Table_PA0001()

Z_SAP_Sync_Table_PA0002()

End Sub

Public Function Z_SAP_Sync_Table(sapTblName As String, wherecond As String) As String

logger.trace(sapTblName & ": ENTER")

Dim batchLogSize As Integer = 2500

Dim syncName As String = Connection.GetConfigParm("TargetSystem\UNS\ITResource\SAPHR\DPRShellName")

Dim parPref As String = "TargetSystem\UNS\ITResource\SAPHR\InfotypeHandling\

Dim maxReadAttemptNumStr As String = Z_Get_One_Of_Conf_Param_Value(parPref & sapTblName & "\ReReadAttempts", parPref & "Default\ReReadAttempts", "2" )

Dim maxReadAttemptNum As Integer = CInt(maxReadAttemptNumStr) + 1

Dim maxCreationNum As Integer = CInt(Z_Get_One_Of_Conf_Param_Value(parPref & sapTblName & "\MaxCreated", parPref & "Default\MaxCreated", "-1" ))

Dim maxDeletionNum As Integer = CInt(Z_Get_One_Of_Conf_Param_Value(parPref & sapTblName & "\MaxRemoved", parPref & "Default\MaxRemoved", "-1" ))

Dim treatMaxNumbersAsPercentage As Boolean = "1" = Z_Get_One_Of_Conf_Param_Value(parPref & sapTblName & "\TreatMaxNumbersAsPercentage", parPref & "Default\TreatMaxNumbersAsPercentage", "0" )

Dim readAttemptNum As Integer = 0

While readAttemptNum \< maxReadAttemptNum

logger.trace(sapTblName & ": starting read iteration #" & readAttemptNum)

Dim f As ISqlFormatter = Connection.SqlFormatter

Dim idmTableName As String = "CCC" & sapTblName

Dim allSapAttrsList As New List(Of String)

Dim dateSapAttrsList As New List(Of String)

Dim columnDefs As IColDbObject = Connection.CreateCol("DialogColumn")

columnDefs.Prototype("ColumnName").IsDisplayItem = True

columnDefs.Prototype("DataType").IsDisplayItem = True

columnDefs.Prototype.WhereClause = "uid_dialogtable in (select uid_dialogtable from dialogtable where " & f.Comparison("TableName", idmTableName, ValType.String) & ") and columnname like 'CCC%' "

columnDefs.Load()

For Each nextColumn As IColElem In columnDefs

Dim colName As String = nextColumn.GetValue("ColumnName").String

Dim sapColName = colName.Replace("CCC_", "")

If colName = "CCC_IsDeleted" Then

Continue For

End If

If nextColumn.GetValue("DataType").String = CStr(ValType.Date) Then

dateSapAttrsList.Add(sapColName)

End If

allSapAttrsList.Add(sapColName)

Next

Dim UID_DPRShell As String = Connection.GetSingleProperty("DPRShell", "UID_DPRShell", f.Comparison("DisplayName", syncName, ValType.String))

Dim expr_AB As New DatabaseReader(Session, "DPRShell", f.UidComparison("UID_DPRShell", UID_DPRShell))

expr_AB.SkipInactiveSchemaTypes = True

Using projectorShell = CType(expr_AB.Read(Nothing), IProjectorShell)

Dim projectorSAPConnector As New VI.Projector.SAP.ProjectorSAPConnector()

Dim ov As New OverwriteVariables("")

Dim projectionOption As New ProjectionOption(ProjectionDirection.ToTheLeft)

projectionOption.VariableSet = projectorShell.DefaultVariableSet

Dim Fu As Func(Of String, String) = Function(s As String ) As String

Return ov.GetVariableValue(s)

End Function

projectionOption.VariableOverruler = Fu

Dim systemConnection As VI.Projector.Connection.ISystemConnection = Nothing

'logger.trace("found connections: " & projectorShell.Connections.Count)

For Each sc As VI.Projector.Connection.ISystemConnection In projectorShell.Connections

'If Not sc.Schema.System.IsMainSystem Then

'If True Then

' logger.trace("sc.Schema.System.Identity.Id:")

' logger.trace(sc.Schema.System.Identity.Id)

If Not sc.Schema.System.Identity.Id.StartsWith("FTP#", StringComparison.OrdinalIgnoreCase) Then

systemConnection = sc

Exit For

End If

Next

If systemConnection Is Nothing Then

Throw New exception(sapTblName & ": cant find main connnection")

End If

projectorSAPConnector.Connect(systemConnection.ConnectionParameter.Replace(projectionOption.VariableSetEffective, False))

If Not projectorSAPConnector.IsConnected Then

logger.warn(sapTblName & ": not connected :(")

Throw New Exception("cant connect to SAP system")

End If

logger.trace(sapTblName & ": connected!!!")

Dim m_SAPConnection As SAPConnection = CType(projectorSAPConnector.GetType().GetField("m_SAPConnection", BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(projectorSAPConnector) ,SAPConnection )

Dim tbl As New SAPTable(m_SAPConnection)

logger.trace(sapTblName & ": processing sql: " & wherecond)

Dim whereconds As String() = { wherecond }

Dim dynMethod As MethodInfo

For Each dm As MethodInfo In tbl.GetType().GetMethods(System.Reflection.BindingFlags.NonPublic Or System.Reflection.BindingFlags.Instance)

If dm.Name = "SAPQuery" AndAlso dm.GetParameters.Count = 3 Then

dynMethod = dm

Exit For

End If

Next

Dim ans As BigDataTable = CType(dynMethod.Invoke(tbl, { sapTblName, whereconds, Nothing }), BigDataTable)

logger.trace(sapTblName & ": obtained rows from " & sapTblName & ": " & ans.Rows.Count)

Dim cycleNum As Integer = 0

Dim handledIds As New List(Of String)

logger.Trace(sapTblName & ": fill map with sap data")

Dim hashToSapData As New Dictionary(Of String, BigDataRow)

For Each nextRow As BigDataRow In ans.Rows

Dim hash As String = ""

For Each attrName As String In allSapAttrsList

If dateSapAttrsList.Contains(attrName) Then

'Dim strval As String = nextRow(attrName).tostring

'hash = hash & If ( strval="0000-00-00", "00000000", CType(nextRow(attrName), Date).tostring("yyyyMMdd") ) & "_"

hash = hash & Z_SAP_Get_Date_Sap_Attr_For_Hash(nextRow, attrName) & "_"

Else

hash = hash & CStr(nextRow(attrName)) & "_"

End If

Next

If hashToSapData.ContainsKey(hash) Then

logger.warn(sapTblName & ": sap has duplicate data: " & hash)

Else

hashToSapData.Add(hash, nextRow)

End If

Next

logger.Trace(sapTblName & ": end fill map with sap data")

logger.Trace(sapTblName & ": fill map with idm data")

Dim hashToIdmData As New Dictionary(Of String, String)

Dim existingObjects As IColDbObject = Nothing

existingObjects = Connection.CreateCol("CCC" & sapTblName)

Z_Add_All_Columns_To_Output_Result("CCC" & sapTblName, existingObjects)

existingObjects.Prototype.WhereClause = "CCC_IsDeleted = 0"

existingObjects.Load()

logger.trace(sapTblName & ": not deleted records from idm: " & existingObjects.Count)

For Each nextRow As IColElem In existingObjects

Dim hash As String = ""

For Each attrName As String In allSapAttrsList

If dateSapAttrsList.Contains(attrName) Then

Dim dv As Date = nextRow.getValue("CCC_" & attrName).Date

hash = hash & If (dv = Nothing OrElse dv.CompareTo(FIRST_VALID_DATE)\<0, "00000000", dv.tostring("yyyyMMdd")) & "_"

Else

hash = hash & nextRow.getValue("CCC_" & attrName).String & "_"

End If

Next

If hashToIdmData.ContainsKey(hash) Then

logger.warn(sapTblName & ": IDM has duplicate data: " & hash)

Else

hashToIdmData.Add(hash, nextRow.GetValue("UID_" & idmTableName).String)

End If

Next

logger.Trace(sapTblName & ": end fill map with idm data")

Dim newHashesToCreate = hashToSapData.Keys.Except(hashToIdmData.Keys).ToList

logger.trace(sapTblName & ": new objects (planned): " & newHashesToCreate.Count)

Dim toRemoveObjectHashes = hashToIdmData.Keys.Except(hashToSapData.Keys ).ToList

logger.trace(sapTblName & ": objects to remove: " & toRemoveObjectHashes.Count)

Dim creationexceeded As Boolean = False

If maxCreationNum > -1 Then

If treatMaxNumbersAsPercentage Then

Dim lim As Double = (existingObjects.Count * maxCreationNum) / 100

logger.Trace(sapTblName & ": maxCreationNum = " & lim)

creationexceeded = newHashesToCreate.Count > lim

Else

creationexceeded = newHashesToCreate.Count > maxCreationNum

End If

End If

Dim deletionexceeded As Boolean = False

If maxDeletionNum > -1 Then

If treatMaxNumbersAsPercentage Then

deletionexceeded = toRemoveObjectHashes.Count > (existingObjects.Count * maxDeletionNum) / 100

Else

deletionexceeded = toRemoveObjectHashes.Count > maxDeletionNum

End If

End If

If creationexceeded OrElse deletionexceeded Then

readAttemptNum = readAttemptNum + 1

If readAttemptNum >= maxReadAttemptNum Then

logger.warn(sapTblName & ": last read iteration; throwing exception...")

Z_Send_Plain_Text_Mail(Connection.GetConfigParm("Common\MailNotification\ErrorAuditor"), "Idm. HR sync error - Threshold exceeded for " & sapTblName, "Threshold exceeded for " & sapTblName & ": objects to create: " & newHashesToCreate.Count.ToString & "; objects to remove: " & toRemoveObjectHashes.Count.ToString,False)

Throw New Exception("Problem with obtaining data from " & sapTblName & "; objects to create: " & newHashesToCreate.Count.ToString & "; objects to remove: " & toRemoveObjectHashes.Count.ToString)

Else

Z_Send_Plain_Text_Mail(Connection.GetConfigParm("Common\MailNotification\ErrorAuditor"), "Idm. HR sync warning - Threshold exceeded for " & sapTblName, "Threshold exceeded for " & sapTblName & ": objects to create: " & newHashesToCreate.Count.ToString & "; objects to remove: " & toRemoveObjectHashes.Count.ToString,False)

logger.warn(sapTblName & ": going to next read iteration!!!!")

End If

Continue While

End If

Dim createdObjectCount As Integer = 0

Connection.BeginTransaction

Try

Dim doLog As Boolean = False

Dim ccn As Integer = 0

For Each hash As String In hashToSapData.Keys

doLog = cycleNum Mod batchLogSize = 0

If doLog Then

logger.trace(sapTblName & ":cyclNum start #" & cycleNum.ToString)

End If

ccn = cycleNum

cycleNum = cycleNum + 1

If Not hashToIdmData.ContainsKey(hash) Then

logger.trace(sapTblName & ":next hash to create: " & hash)

Dim dbrow As ISingleDbObject = Connection.CreateSingle(idmTableName)

createdObjectCount = createdObjectCount + 1

Dim nextRow As BigDataRow = hashToSapData(hash)

For Each attrName As String In allSapAttrsList

If dateSapAttrsList.Contains(attrName) Then

VID_PutValueSafe(dbrow, "CCC_" & attrName, Z_SAP_Get_Date_Sap_Attr(nextRow, attrName))

Else

VID_PutValueSafe(dbrow, "CCC_" & attrName, nextRow(attrName))

End If

Next

If doLog Then

logger.trace(sapTblName & ": cycle #" & ccn.tostring() & " before save")

End If

dbrow.Save()

If doLog Then

logger.trace(sapTblName & ": cycle #" & ccn.tostring() & " after save")

End If

End If

If doLog Then

logger.trace(sapTblName & ": cyclNum finish #" & ccn.ToString)

End If

Next

logger.trace(sapTblName & ": new objects (fact): " & createdObjectCount)

For Each hash As String In toRemoveObjectHashes

logger.trace(sapTblName & ": next hash to remove: " & hash)

Dim dbrow As ISingleDbObject = Connection.CreateSingle("CCC" & sapTblName, hashToIdmData(hash))

'dbrow.Delete()

VID_PutValueSafe(dbrow, "CCC_IsDeleted", 1)

dbrow.Save()

Next

Connection.CommitTransaction

Catch ex As Exception

Connection.RollbackTransaction()

Throw ex

End Try

logger.Trace(sapTblName & ": finish update")

Return "ok"

End Using

End While

Throw New Exception("Problem with obtaining data from " & sapTblName)

End Function

Dim FIRST_VALID_DATE As Date = Date.ParseExact("01/01/1900", "dd/MM/yyyy",System.Globalization.DateTimeFormatInfo.InvariantInfo)

Private Function Z_SAP_Get_Date_Sap_Attr_For_Hash(nextRow As BigDataRow, attrName As String) As String

If Convert.ToString(nextRow(attrName))\<>"0000-00-00" AndAlso Not String.IsNullOrEmpty(Convert.ToString(nextRow(attrName))) Then

Dim tmpDate As Date = CType(nextRow(attrName), Date)

If Not (tmpDate = Nothing) AndAlso tmpDate.CompareTo(FIRST_VALID_DATE) >= 0 Then

Return tmpDate.tostring("yyyyMMdd")

End If

End If

If attrName = "BEGDA" Then

Return "19000101"

Else

Return "00000000"

End If

End Function

Private Function Z_SAP_Get_Date_Sap_Attr(nextRow As BigDataRow, attrName As String) As Date

If Convert.ToString(nextRow(attrName))\<>"0000-00-00" AndAlso Not String.IsNullOrEmpty(Convert.ToString(nextRow(attrName))) Then

Dim tmpDate As Date = CType(nextRow(attrName), Date)

If Not (tmpDate = Nothing) AndAlso tmpDate.CompareTo(FIRST_VALID_DATE) >= 0 Then

Return tmpDate

End If

End If

If attrName = "BEGDA" Then

Return FIRST_VALID_DATE

Else

Return Nothing

End If

End Function