Imports Microsoft.VisualBasic
Imports System
Imports System.Diagnostics
Imports System.IO
Imports System.Linq
Imports System.Collections.Generic
Imports System.Data
Imports System.Threading
Imports System.Collections.Concurrent
Imports JF.PlmJobManager
Imports JF.PlmJobManager.JobServer
Imports JF.Tools
Imports Infragistics.Documents.Excel
Imports JobManagerDataModelUI
Imports JF.InfoSys
Imports PerCederberg.Grammatica.Runtime
Imports System.Text

' # Doc: Example Usage of external .net .dll
'References Infragistics4.Documents.Excel.v25.1.dll


'xReferences System.Data.DataSetExtensions.dll

'' Script to get persistent names for Profile via TaskScheduler: 
''  Date         Person        ChangeInfo
''  01.08.2023   S.Gueth       created
''  27.07.2025.1 S.Gueth       updated

'' #Doc: Parameter usage:
'' Parameter                             Description   
'' /PauseOnEnd=true/false                enables / disables  pause on end 		
'' /JobMgrSiteID=SiteID1                 defines the JobMgr Site ID, where to read the JobManager Dataset From
'' /ExportRoot_DP=C:\Export\Root         defines the root export path
'' /ExcelFile_DPNE=C:\path\to\excel.xls  defines the path to the excel mapping file
'' /ForceUpdateAllObjects=true           defines if all objects in JobMgr DB should be updated (default: only Objects where ObjectAttr01 is empty will be updated)
'' /NumberRange_StartValue=E1R100052681  defines the start value for TeamCenter ItemId Number Range (example: E1R100052681)
'' /NumberRange_DefaultRevision=A        defines the default revision for the number range (example: a | -)
'' /NumberRange_Length=400               defines the start length for TeamCenter ItemId Number Range (example: 400)

Namespace JobMgrScript

	Public Class NXtoC_SetPersistentNamesAndMapping_cls

		Private Property ExportFilePath_DPNE_ObjectAttr As String = "ObjectAttr01"
		Private Property TCItemId_new_ObjectAttr As String = "ObjectAttr02"
		Private Property TCItemRev_new_ObjectAttr As String = "ObjectAttr03"
		Private Property TCObjectName_new_ObjectAttr As String = "ObjectAttr04"
		Private Property TCItemType_new_ObjectAttr As String = "ObjectAttr05"
		Private Property TCCliName_new_ObjectAttr As String = "ObjectAttr06"
		Private Property MappSource_ObjectAttr As String = "ObjectAttr07"

		''' <remarks> 
		''' #New: 3.2000;02.04.2025;S.Gueth
		''' </remarks>
		Public Function ScriptMain(ByVal JS_ScriptingInterface As ScriptingInterface.Methodes) As JF_Return_cls

			' Required class for returning script results
			Dim ScriptResult_r As New JF_Return_cls
			Dim WaitTimeIn_MSec As Integer = 200
			'Dim SiteId As String = Nothing
			Dim StatusToSet As String = Nothing
			Dim ProcessGroupNumber As String = Nothing
			Dim PauseOnEnd As Boolean = False
			Dim ScriptParameter As String = JS_ScriptingInterface.ScriptDef.cmdPara

			'-- #Step: open the ScriptRun form  
			Dim ScriptOutput_frm As New JobServer.JobServer_ScriptRun_frm(JS_ScriptingInterface.ScriptDef)
			'ScriptOutput_frm.Show()

			Dim InfoSysHeader = ScriptOutput_frm.InfoSysHeader
			Dim InfoSysProtocol = ScriptOutput_frm.InfoSysProtocol

			InfoSysHeader = JS_ScriptingInterface.CurActiveInfoSysGet
			InfoSysProtocol = JS_ScriptingInterface.CurActiveInfoSysGet


			'-- for printing Informations to the header textbox 
			InfoSysHeader.PrintObject("Script file", JS_ScriptingInterface.ScriptDef.Script_DPNE)
			InfoSysHeader.PrintObject("Script description", JF.Tools.JF_ToolsFile.ParsePath_NE(JS_ScriptingInterface.ScriptDef.NameDescription))

			Dim dataSourceObj As MyAppl_DataSource_cls = JS_ScriptingInterface.NewDataSourceObjGenerate("DataSource.GetPersistentNamesAndMapping")
			Dim jobMgrDBCommand As Common.DbCommand = Nothing

			Dim cadNamesDbCommand As Common.DbCommand = Nothing

			If System.Diagnostics.Debugger.IsAttached Then
				Stop
			End If


			Try
				'SiteId = JS_ScriptingInterface.ParameterValueGet("SiteId")
				PauseOnEnd = If(JS_ScriptingInterface.ParameterValueGet("PauseOnEnd") = "true", True, False)
				ScriptResult_r.ResetToDefaultValues()
				Dim JobMgrSiteID As String = JS_ScriptingInterface.ParameterValueGet("JobMgrSiteID")
				Dim ExportRoot_DP As String = JS_ScriptingInterface.ParameterValueGet("ExportRoot_DP")
				Dim ExcelFile_DPNE As String = JS_ScriptingInterface.ParameterValueGet("ExcelFile_DPNE")
				Dim ForceUpdateAllObjects As String = JS_ScriptingInterface.ParameterValueGet("ForceUpdateAllObjects")
				Dim NumberRange_StartValue As String = JS_ScriptingInterface.ParameterValueGet("NumberRange_StartValue")
				Dim NumberRange_DefaultRevision As String = JS_ScriptingInterface.ParameterValueGet("NumberRange_DefaultRevision")
				Dim NumberRange_Length_String As String = JS_ScriptingInterface.ParameterValueGet("NumberRange_Length")
				Dim NumberRange_Length As Integer = -1
				If Not String.IsNullOrEmpty(NumberRange_Length_String) Then
					NumberRange_Length = CType(NumberRange_Length_String, Integer)
				End If


				' parse number range number and prefix from string
				Dim NumberRange_StartNumber As Integer = -1
				Dim NumberRange_Prefix As String = ""
				Dim NumberRange_DigitsLength As Integer = 0

				If Not String.IsNullOrEmpty(NumberRange_StartValue) Then
					Dim numberPart As String = ""

					' iterate through the string from back to front and collect digits
					For i As Integer = NumberRange_StartValue.Length - 1 To 0 Step -1
						If Char.IsDigit(NumberRange_StartValue(i)) Then
							numberPart = NumberRange_StartValue(i) & numberPart
						Else
							' as soon as no more digit ? the rest is the prefix
							NumberRange_Prefix = NumberRange_StartValue.Substring(0, i + 1)
							Exit For
						End If
					Next

					NumberRange_DigitsLength = numberPart.Length
					NumberRange_StartNumber = Integer.Parse(numberPart)
				End If

				Dim Text_str As String =
					"Script started with parameters..:" + vbCrLf +
					"[" + JS_ScriptingInterface.ParameterStr + "]" + vbCrLf +
					vbCrLf +
					"JobMgrSiteID=" + JobMgrSiteID + vbCrLf +
					"ExportRoot_DP=" + ExportRoot_DP + vbCrLf +
					"ExcelFile_DPNE=" + ExcelFile_DPNE + vbCrLf +
					"ForceUpdateAllObjects=" + ForceUpdateAllObjects + vbCrLf +
					"NumberRange_StartValue=" + NumberRange_StartValue + vbCrLf +
					"NumberRange_StartNumber=" + NumberRange_StartNumber.ToString() + vbCrLf +
					"NumberRange_Length=" + NumberRange_Length.ToString() + vbCrLf +
					"NumberRange_DefaultRevision=" + NumberRange_DefaultRevision + vbCrLf +
					vbCrLf +
					"..\10-JobLogArchiv..............:" + vbCrLf +
					"[" + JS_ScriptingInterface.ServerSettings.Dir_DataRoot_DP_10JobLogArchiv + "]"

				InfoSysHeader.Print(Text_str)

				Dim updateAllObjectsForce As Boolean = False
				If Not String.IsNullOrEmpty(ForceUpdateAllObjects) AndAlso ForceUpdateAllObjects.ToLower.Equals("true") Then
					updateAllObjectsForce = True
				End If

				Dim numberRangeList As New ConcurrentBag(Of String)

				InfoSysProtocol.StatusBarProgressBarStart()

				InfoSysProtocol.Print("")
				InfoSysProtocol.PrintInfoLine("Reading Excel mapping file ...")
				Dim mappingsDictItemId As New ConcurrentDictionary(Of String, Mapping_cls)
				Dim mappingsDictItemRev As New ConcurrentDictionary(Of String, Mapping_cls)

				If Not String.IsNullOrEmpty(ExcelFile_DPNE) Then
					mappingsDictItemRev = Me.ExcelFileRead(ExcelFile_DPNE, InfoSysProtocol, mappingsDictItemId)
				End If

				If mappingsDictItemRev Is Nothing OrElse mappingsDictItemRev.Count <= 0 Then

					If String.IsNullOrEmpty(NumberRange_StartValue) Then
						' if there was an error, exit script
						ScriptResult_r.ResultCode = 99
						ScriptResult_r.ResultMsg = "ERR: on reading excel mapping file, or no NumberRange_StartValue or ExcelFile_DPNE parameter defined"
						jobMgrDBCommand = Nothing
						GoTo EndOfScript
					End If

				End If

				InfoSysProtocol.Print("")
				InfoSysProtocol.PrintInfoLine("Get new Names from mapping and Update JobMgr data ")

				InfoSysProtocol.Print("")
				InfoSysProtocol.Print("Get JobManager dataset objects from site " + JobMgrSiteID + "...")
				InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get JobManager objects from site " + JobMgrSiteID + " ")
				Dim JobMgrDS_tbl As DataTable = Nothing

				' get JobMgr Objects objects
				'	Dim CommandText = "SELECT Id, IrName, ObjectType, ObjectName, ObjectPuid, ObjectCliName, ObjectFs_NE FROM DS_with_Irs_View WHERE ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'DS' and (DSObjectAttr01 = '' OR DSObjectAttr01 is null)"
				'Dim CommandText As String = ""
				'CommandText += "SELECT Id, IrName, ObjectType, ObjectName, ObjectPuid, ObjectCliName, ObjectFs_NE "
				'CommandText += "FROM DS_with_Irs_View "
				'CommandText += "WHERE "
				'CommandText += "ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'DS' "
				'CommandText += "AND (DSObjectAttr02 like 'JobMgrGen%' OR DSObjectAttr07 IN('NumberRange','JobMgrGen') OR DSObjectAttr06 like '%altrep/E1R1%')"


				' for testing, select only 10 objects
				Dim CommandText As String = "SELECT Id, IrName, ObjectType, ObjectName, ObjectPuid, ObjectCliName, ObjectFs_NE "
				CommandText += "FROM DS_with_Irs_View "
				CommandText += "WHERE "
				CommandText += "ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'DS' "
				CommandText += "AND DSObjectAttr02 like 'JobMgrGen%'"



				If updateAllObjectsForce Then
					CommandText = "SELECT Id, IrName, ObjectType, ObjectName, ObjectPuid, ObjectCliName, ObjectFs_NE FROM DS_with_Irs_View WHERE ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'DS' "
				End If

				jobMgrDBCommand = dataSourceObj.CreateCommandNew(CommandType.Text, CommandText)
				' fill data table
				If JF.Tools.BooleanTrueFalseToggle(dataSourceObj.cmdFillDataTable(jobMgrDBCommand, JobMgrDS_tbl)) Then

					' exit only when NumberRange_StartValue is empty
					If String.IsNullOrEmpty(NumberRange_StartValue) Then
						' if there was an error, exit script
						ScriptResult_r.ResultCode = 100
						ScriptResult_r.ResultMsg = "ERR: on getting dataset data from JobMgr Objects_tbl"
						jobMgrDBCommand = Nothing
						GoTo EndOfScript
					End If


				End If
				jobMgrDBCommand = Nothing

				Dim jobMgrObjectsTotal As Integer = JobMgrDS_tbl.Rows.Count
				Dim objectsProcessed As Integer = 0

				InfoSysProtocol.Print("JobManager Datasets found in DB: " + jobMgrObjectsTotal.ToString)

				Dim resultSetParallelOptions As New System.Threading.Tasks.ParallelOptions()
				resultSetParallelOptions.MaxDegreeOfParallelism = 1
				Dim resultDBSourceDict As New System.Collections.Concurrent.ConcurrentDictionary(Of Integer, MyAppl_DataSource_cls)
				Dim resultDBCommandDict As New System.Collections.Concurrent.ConcurrentDictionary(Of Integer, Common.DbCommand)

				InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get new Names from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
				InfoSysProtocol.StatusBarProgressSetValue(0)

				objectsProcessed = 0
				Dim numberRangeIndexCurr As Integer = 0
				Dim numberRangeSyncLock As New Object

				System.Threading.Tasks.Parallel.ForEach(JobMgrDS_tbl.AsEnumerable(),
					resultSetParallelOptions,
					Sub(JobMgrDSDataRow As DataRow)

						System.Threading.Interlocked.Increment(objectsProcessed)

						JS_ScriptingInterface.ScriptPauseCheck()
						If JS_ScriptingInterface.ScriptStopButtonWasClicked() Then
							Exit Sub
						End If

						Dim threadID = System.Threading.Thread.CurrentThread.ManagedThreadId
						Dim resultDBSourceObj As MyAppl_DataSource_cls = Nothing
						If resultDBSourceDict.ContainsKey(threadID) Then
							resultDBSourceObj = resultDBSourceDict.Item(threadID)
						Else
							resultDBSourceObj = JS_ScriptingInterface.NewDataSourceObjGenerate("DataSource_ResultsToDB_" + threadID.ToString)
							resultDBSourceObj.DbConnOpen = True
							resultDBSourceDict.GetOrAdd(threadID, resultDBSourceObj)
						End If

						Dim resultDBCommand As Common.DbCommand = Nothing
						If resultDBCommandDict.ContainsKey(threadID) Then
							resultDBCommand = resultDBCommandDict.Item(threadID)
						Else
							resultDBCommand = resultDBSourceObj.CreateCommandNew(CommandType.Text, "")
							resultDBCommandDict.GetOrAdd(threadID, resultDBCommand)
						End If

						Dim ds_puid As String = JobMgrDSDataRow.Item("ObjectPuid").ToString
						Dim ds_objectName As String = JobMgrDSDataRow.Item("ObjectName").ToString
						Dim jobMgrDB_Id As Guid = CType(JobMgrDSDataRow.Item("Id"), Guid)
						Dim ds_type As String = JobMgrDSDataRow.Item("ObjectType").ToString
						Dim itemRevName As String = JobMgrDSDataRow.Item("IrName").ToString
						Dim objectFs_NE As String = JobMgrDSDataRow.Item("objectFs_NE").ToString
						Dim ds_objectCliName As String = JobMgrDSDataRow.Item("ObjectCliName").ToString
						Dim objectCliName_obj = JobServer.ObjectCliName_cls.Parse(ds_objectCliName)

						' get corresponding mapping object
						Dim mappingObj As Mapping_cls = Nothing
						If mappingsDictItemRev.ContainsKey(itemRevName) Then
							mappingObj = mappingsDictItemRev.Item(itemRevName)
						ElseIf mappingsDictItemId.ContainsKey(objectCliName_obj.Item) Then
							mappingObj = mappingsDictItemId.Item(objectCliName_obj.Item)
							mappingObj.ItemRev = objectCliName_obj.Rev
						Else

							If NumberRange_StartNumber > -1 AndAlso (numberRangeIndexCurr <= NumberRange_Length Or NumberRange_Length = -1) Then

								SyncLock numberRangeSyncLock

									Dim revCurr As String = objectCliName_obj.Rev
									If Not String.IsNullOrEmpty(NumberRange_DefaultRevision) Then
										revCurr = NumberRange_DefaultRevision
									End If

									' check again if mappingsDictItemId contains current item (could have happend in another thread)
									If mappingsDictItemId.ContainsKey(objectCliName_obj.Item) Then
										mappingObj = mappingsDictItemId.Item(objectCliName_obj.Item)
										mappingObj.ItemRev = revCurr
									Else
										' generate new item ID
										Dim itemId_New As String = NumberRange_Prefix + (NumberRange_StartNumber + numberRangeIndexCurr).ToString("D" + NumberRange_DigitsLength.ToString())
										' increment number range index
										System.Threading.Interlocked.Increment(numberRangeIndexCurr)

										InfoSysProtocol.PrintObject("mapping by NumberRange!", objectCliName_obj.DefaultObjectCliName + " -> ItemID: " + itemId_New)
										mappingObj = New Mapping_cls(itemId_New, revCurr, ds_type)

										' add mapping object to dictionaries
										mappingsDictItemRev.GetOrAdd(itemRevName, mappingObj)
										mappingsDictItemId.GetOrAdd(objectCliName_obj.Item, mappingObj)
										numberRangeList.Add(itemId_New)
									End If

								End SyncLock

							Else

								InfoSysProtocol.PrintObject("no mapping found -> generate TC ID!", objectCliName_obj.DefaultObjectCliName)
								mappingObj = New Mapping_cls("JobMgrGen_" + objectCliName_obj.Item, objectCliName_obj.Rev, ds_type)

							End If


						End If

						Dim objNamesuffix As String = ""
						If Not ds_type.ToUpper.Equals("UGMASTER") Then
							objNamesuffix = "_" + JF.Tools.FileNameRemoveForbiddenChars(ds_objectName)
						End If

						' get export path and name
						Dim ExportPath_NE As String = JF.Tools.FileNameRemoveForbiddenChars(ds_type) + "_" + JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item) + "_" + JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Rev) + objNamesuffix + JF.Tools.ParsePath_E(objectFs_NE)

						Dim ExportPath_DP As String = IO.Path.Combine(ExportRoot_DP, (If(objectCliName_obj.Item.Length > 4, JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item.Substring(0, 5)), JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item))).Replace(".", "_"))
						ExportPath_DP = IO.Path.Combine(ExportPath_DP, JF.Tools.FileNameRemoveForbiddenChars(ds_puid).Replace(".", "_"))

						Dim ExportPath_DPNE As String = IO.Path.Combine(ExportPath_DP, ExportPath_NE)

						' get new object name
						Dim objectNameNew As String = ds_objectName.Replace(objectCliName_obj.Item, mappingObj.ItemId)

						Select Case ds_type.ToUpper
							Case "UGMASTER"
								objectNameNew = mappingObj.ItemId + "-" + mappingObj.ItemRev
								'If objectNameNew.EndsWith(objectCliName_obj.Rev) Then
								'	objectNameNew = Left(objectNameNew, Len(objectNameNew) - Len(objectCliName_obj.Rev)) & mappingObj.ItemRev
								'End If
						End Select

						objectNameNew = objectNameNew.Replace("/", "-")

						If ds_type.ToUpper.Equals("UGALTREP") Then
							objectNameNew = ds_objectName
						End If

						Dim cliNameNew = JobServer.ObjectCliName_cls.ObjectCliName_gen(mappingObj.ItemId, mappingObj.ItemRev, objectNameNew, ds_type)

						' set MappSource
						Dim mappSourceCurr As String = "Excel"
						If mappingObj.ItemId.StartsWith("JobMgrGen_") Then
							mappSourceCurr = "JobMgrGen"
						End If
						If numberRangeList.Contains(mappingObj.ItemId) Then
							mappSourceCurr = "NumberRange"
						End If

						' update JobManager dataset

						' generate basic SQL with variables
						Dim cmdText As New Text.StringBuilder()
						cmdText.AppendFormat("UPDATE Objects_tbl {0}", vbCrLf)
						cmdText.AppendFormat("SET {0}", vbCrLf)
						If Not String.IsNullOrEmpty(ExportRoot_DP) Then
							cmdText.AppendFormat("{0} = @ExportPath, {1}", ExportFilePath_DPNE_ObjectAttr, vbCrLf)
						End If
						cmdText.AppendFormat("{0} = @ItemId, {1}", TCItemId_new_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("{0} = @ItemRev, {1}", TCItemRev_new_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("{0} = @ObjectName, {1}", TCObjectName_new_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("{0} = @ItemType, {1}", TCItemType_new_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("{0} = @CliName {1},", TCCliName_new_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("{0} = @MappSource {1}", MappSource_ObjectAttr, vbCrLf)
						cmdText.AppendFormat("WHERE Id = @JobMgrDB_Id")

						' add parameters and values
						resultDBCommand.Parameters.Clear()

						If Not String.IsNullOrEmpty(ExportRoot_DP) Then
							resultDBCommand.ParameterAddNew("@ExportPath", ExportPath_DPNE, DbType.String)
						End If
						resultDBCommand.ParameterAddNew("@ItemId", mappingObj.ItemId, DbType.String)
						resultDBCommand.ParameterAddNew("@ItemRev", mappingObj.ItemRev, DbType.String)
						resultDBCommand.ParameterAddNew("@ObjectName", objectNameNew, DbType.String)
						resultDBCommand.ParameterAddNew("@ItemType", mappingObj.ItemType, DbType.String)
						resultDBCommand.ParameterAddNew("@CliName", cliNameNew, DbType.String)
						resultDBCommand.ParameterAddNew("@MappSource", mappSourceCurr, DbType.String)
						resultDBCommand.ParameterAddNew("@JobMgrDB_Id", jobMgrDB_Id, DbType.Guid)

						resultDBCommand.CommandText = cmdText.ToString
						' execute sql
						Dim result As JF_Return_cls = resultDBSourceObj.cmdExecuteNonQuery(resultDBCommand)

						If result.ErrUp Then
							InfoSysProtocol.Print("Error updating dataset [" + jobMgrDB_Id.ToString + "]" + vbCrLf + resultDBCommand.CommandText)
							If result.ExUp Then
								InfoSysProtocol.PrintEx(result.Ex)
							End If
						End If

						Try
							If objectsProcessed Mod 100 = 0 Then
								InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get new Names from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
								InfoSysProtocol.StatusBarProgressSetValue(CType(System.Math.Round(objectsProcessed * 100 / jobMgrObjectsTotal, 0), Integer))
							End If
						Catch ex As Exception
						End Try


					End Sub)

				jobMgrDBCommand = Nothing

				Try
					InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get new Names from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
					InfoSysProtocol.StatusBarProgressSetValue(CType(System.Math.Round(objectsProcessed * 100 / jobMgrObjectsTotal, 0), Integer))
				Catch ex As Exception
				End Try



				'' ################# START Creating Dummy Names ############################################################################

				'InfoSysProtocol.Print("")
				'InfoSysProtocol.PrintInfoLine("Get Dummy Names from mapping and Update JobMgr data ")

				'InfoSysProtocol.Print("")
				'InfoSysProtocol.Print("Get JobManager itemRev objects from site " + JobMgrSiteID + "...")
				'InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get JobManager objects from site " + JobMgrSiteID + " ")
				'JobMgrDS_tbl = Nothing

				'' get JobMgr IR objects
				'CommandText = "SELECT Id, ObjectName, ObjectPuid, ObjectCliName FROM Objects_tbl WHERE ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'IR' and (ObjectAttr01 = '' OR ObjectAttr01 is null)"
				'If updateAllObjectsForce Then
				'	CommandText = "SELECT Id, ObjectName, ObjectPuid, ObjectCliName FROM Objects_tbl WHERE ObjectFromSiteID =  '" + JobMgrSiteID + "' AND ObjectClass = 'IR' "
				'End If

				'jobMgrDBCommand = dataSourceObj.CreateCommandNew(CommandType.Text, CommandText)
				'' fill data table
				'If JF.Tools.BooleanTrueFalseToggle(dataSourceObj.cmdFillDataTable(jobMgrDBCommand, JobMgrDS_tbl)) Then
				'	' if there was an error, exit script
				'	ScriptResult_r.ResultCode = 100
				'	ScriptResult_r.ResultMsg = "ERR: on getting dataset data from JobMgr Objects_tbl"
				'	jobMgrDBCommand = Nothing
				'	GoTo EndOfScript
				'End If
				'jobMgrDBCommand = Nothing

				'jobMgrObjectsTotal = JobMgrDS_tbl.Rows.Count
				'objectsProcessed = 0

				'InfoSysProtocol.Print("JobManager ItemRevs found in DB: " + jobMgrObjectsTotal.ToString)

				'resultSetParallelOptions = New System.Threading.Tasks.ParallelOptions()
				'resultSetParallelOptions.MaxDegreeOfParallelism = 3
				'resultDBSourceDict = New System.Collections.Concurrent.ConcurrentDictionary(Of Integer, MyAppl_DataSource_cls)
				'resultDBCommandDict = New System.Collections.Concurrent.ConcurrentDictionary(Of Integer, Common.DbCommand)

				'InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get Dummy Names from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
				'InfoSysProtocol.StatusBarProgressSetValue(0)

				'System.Threading.Tasks.Parallel.ForEach(JobMgrDS_tbl.AsEnumerable(),
				'	resultSetParallelOptions,
				'	Sub(JobMgrDSDataRow As DataRow)

				'		System.Threading.Interlocked.Increment(objectsProcessed)

				'		JS_ScriptingInterface.ScriptPauseCheck()
				'		If JS_ScriptingInterface.ScriptStopButtonWasClicked() Then
				'			Exit Sub
				'		End If

				'		Dim threadID = System.Threading.Thread.CurrentThread.ManagedThreadId
				'		Dim resultDBSourceObj As MyAppl_DataSource_cls = Nothing
				'		If resultDBSourceDict.ContainsKey(threadID) Then
				'			resultDBSourceObj = resultDBSourceDict.Item(threadID)
				'		Else
				'			resultDBSourceObj = JS_ScriptingInterface.NewDataSourceObjGenerate("DataSource_ResultsToDB_" + threadID.ToString)
				'			resultDBSourceObj.DbConnOpen = True
				'			resultDBSourceDict.GetOrAdd(threadID, resultDBSourceObj)
				'		End If

				'		Dim resultDBCommand As Common.DbCommand = Nothing
				'		If resultDBCommandDict.ContainsKey(threadID) Then
				'			resultDBCommand = resultDBCommandDict.Item(threadID)
				'		Else
				'			resultDBCommand = resultDBSourceObj.CreateCommandNew(CommandType.Text, "")
				'			resultDBCommandDict.GetOrAdd(threadID, resultDBCommand)
				'		End If

				'		Dim ir_puid As String = JobMgrDSDataRow.Item("ObjectPuid").ToString
				'		Dim itemRevName As String = JobMgrDSDataRow.Item("ObjectName").ToString
				'		Dim jobMgrDB_Id As Guid = CType(JobMgrDSDataRow.Item("Id"), Guid)
				'		Dim ir_objectCliName As String = JobMgrDSDataRow.Item("ObjectCliName").ToString
				'		Dim objectCliName_obj = JobServer.ObjectCliName_cls.Parse(ir_objectCliName)

				'		' get corresponding mapping object
				'		Dim mappingObj As Mapping_cls = Nothing
				'		If mappingsDictItemRev.ContainsKey(itemRevName) Then
				'			mappingObj = mappingsDictItemRev.Item(itemRevName)
				'		ElseIf mappingsDictItemId.ContainsKey(objectCliName_obj.Item) Then
				'			mappingObj = mappingsDictItemId.Item(objectCliName_obj.Item)
				'			mappingObj.ItemRev = objectCliName_obj.Rev
				'		Else
				'			InfoSysProtocol.PrintObject("Skipped -> no mapping found!", objectCliName_obj.DefaultObjectCliName)
				'			Return
				'		End If


				'		' get export path and name
				'		Dim ExportPath_NE As String = "Dummy_" + JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item) + "_" + JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Rev) + ".prt"

				'		Dim ExportPath_DP As String = IO.Path.Combine(ExportRoot_DP, "____DummyFiles")
				'		ExportPath_DP = IO.Path.Combine(ExportPath_DP, (If(objectCliName_obj.Item.Length > 4, JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item.Substring(0, 5)), JF.Tools.FileNameRemoveForbiddenChars(objectCliName_obj.Item))).Replace(".", "_"))
				'		ExportPath_DP = IO.Path.Combine(ExportPath_DP, JF.Tools.FileNameRemoveForbiddenChars(ir_puid).Replace(".", "_"))

				'		Dim ExportPath_DPNE As String = IO.Path.Combine(ExportPath_DP, ExportPath_NE)

				'		' get new object name
				'		Dim objectNameNew As String = ""

				'		Dim cliNameNew = JobServer.ObjectCliName_cls.ObjectCliName_gen(mappingObj.ItemId, mappingObj.ItemRev)

				'		' update JobManager objects

				'		' generate basic SQL with variables
				'		Dim cmdText As New Text.StringBuilder()
				'		cmdText.AppendFormat("UPDATE Objects_tbl {0}", vbCrLf)
				'		cmdText.AppendFormat("SET {0}", vbCrLf)
				'		'cmdText.AppendFormat("{0} = @ExportPath, {1}", ExportFilePath_DPNE_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("{0} = @ItemId, {1}", TCItemId_new_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("{0} = @ItemRev, {1}", TCItemRev_new_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("{0} = @ObjectName, {1}", TCObjectName_new_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("{0} = @ItemType, {1}", TCItemType_new_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("{0} = @CliName {1}", TCCliName_new_ObjectAttr, vbCrLf)
				'		cmdText.AppendFormat("WHERE Id = @JobMgrDB_Id")

				'		' add parameters and values
				'		resultDBCommand.Parameters.Clear()
				'		'resultDBCommand.ParameterAddNew("@ExportPath", ExportPath_DPNE, DbType.String)
				'		resultDBCommand.ParameterAddNew("@ItemId", mappingObj.ItemId, DbType.String)
				'		resultDBCommand.ParameterAddNew("@ItemRev", mappingObj.ItemRev, DbType.String)
				'		resultDBCommand.ParameterAddNew("@ObjectName", objectNameNew, DbType.String)
				'		resultDBCommand.ParameterAddNew("@ItemType", mappingObj.ItemType, DbType.String)
				'		resultDBCommand.ParameterAddNew("@CliName", cliNameNew, DbType.String)
				'		resultDBCommand.ParameterAddNew("@JobMgrDB_Id", jobMgrDB_Id, DbType.Guid)

				'		resultDBCommand.CommandText = cmdText.ToString
				'		' execute sql
				'		Dim result As JF_Return_cls = resultDBSourceObj.cmdExecuteNonQuery(resultDBCommand)

				'		If result.ErrUp Then
				'			InfoSysProtocol.Print("Error updating itemRev [" + jobMgrDB_Id.ToString + "]" + vbCrLf + resultDBCommand.CommandText)
				'			If result.ExUp Then
				'				InfoSysProtocol.PrintEx(result.Ex)
				'			End If
				'		End If

				'		Try
				'			If objectsProcessed Mod 100 = 0 Then
				'				InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get Dummy Names from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
				'				InfoSysProtocol.StatusBarProgressSetValue(CType(System.Math.Round(objectsProcessed * 100 / jobMgrObjectsTotal, 0), Integer))
				'			End If
				'		Catch ex As Exception
				'		End Try


				'	End Sub)

				'jobMgrDBCommand = Nothing

				'Try
				'	InfoSysProtocol.StatusBarSetCurrActionStatusInfo_Panel3(" get new Dummy from mapping and Update JobMgr data " + objectsProcessed.ToString + "/" + jobMgrObjectsTotal.ToString)
				'	InfoSysProtocol.StatusBarProgressSetValue(CType(System.Math.Round(objectsProcessed * 100 / jobMgrObjectsTotal, 0), Integer))
				'Catch ex As Exception
				'End Try











				InfoSysProtocol.Print("")
				InfoSysProtocol.Print("Script ended successfully")
				InfoSysProtocol.Print("")

			Catch ex As Exception
				ScriptResult_r.Ex = ex
				ScriptResult_r.ExMsg = "Exception in script:" + JS_ScriptingInterface.ScriptDef.NameDescription
				InfoSysProtocol.PrintEx(ex, "MultiSiteCheck.ScriptMain")
			Finally
				' close db connection
				If cadNamesDbCommand IsNot Nothing AndAlso
					cadNamesDbCommand.Connection IsNot Nothing AndAlso
					cadNamesDbCommand.Connection.State <> ConnectionState.Closed Then
					cadNamesDbCommand.Connection.Close()
				End If
				' close db connection
				If jobMgrDBCommand IsNot Nothing AndAlso
					jobMgrDBCommand.Connection IsNot Nothing AndAlso
					jobMgrDBCommand.Connection.State <> ConnectionState.Closed Then
					jobMgrDBCommand.Connection.Close()
				End If
			End Try

EndOfScript:

			'-- stop the progress bar
			InfoSysProtocol.StatusBarProzessBarStop()
			'-- setting the progress bar to idel			
			InfoSysHeader.Print("#Step - 3 Progressbar set idle")
			Call InfoSysProtocol.StatusBarProgressBarSetIdle()

			Call InfoSysProtocol.PrintInfoLine(ScriptResult_r.ResultMsg)

			If PauseOnEnd Then
				Dim TextStr As String = "#Step - 4 Script will end after clicking the button"
				InfoSysHeader.Print(TextStr)
				InfoSysProtocol.Print_and_StatusBarSetCurrActionStatusInfo_Panel3(TextStr)
				ScriptOutput_frm.Pause("click me and script will end")

				'-- closing the ScriptRun form 
				ScriptOutput_frm.Close()
				ScriptOutput_frm.Dispose()
			End If


			Return ScriptResult_r
		End Function

		''' <summary>
		''' method to read the mapping file 
		''' </summary>
		''' <remarks> 
		''' <br/>#New: 3.2000;31.03.2025;S.Gueth
		''' </remarks>
		Private Function ExcelFileRead(excel_DPNE_p As String,
			curInfoSys_p As JF_InfoSys,
			ByRef mappingsDictItemId_p As ConcurrentDictionary(Of String, Mapping_cls)) As ConcurrentDictionary(Of String, Mapping_cls)

			Dim res As ConcurrentDictionary(Of String, Mapping_cls) = Nothing

			Try
				curInfoSys_p.PrintObject("Loading Excel", excel_DPNE_p)
				Dim excelWorkbook = Workbook.Load(excel_DPNE_p)
				curInfoSys_p.PrintObject("Excel loaded successfully", excel_DPNE_p)

				Dim excelWorksheet = excelWorkbook.Worksheets(0)

				res = New ConcurrentDictionary(Of String, Mapping_cls)

				Dim isFirstRow As Boolean = True
				For Each ExcelRow As WorksheetRow In excelWorksheet.Rows

					If ExcelRow.Cells(0).Value IsNot Nothing AndAlso
						ExcelRow.Cells(1).Value IsNot Nothing AndAlso
						ExcelRow.Cells(2).Value IsNot Nothing AndAlso
						ExcelRow.Cells(3).Value IsNot Nothing AndAlso
						ExcelRow.Cells(4).Value IsNot Nothing AndAlso
						ExcelRow.Cells(5).Value IsNot Nothing Then

						Dim ItemId_SMA As String = ExcelRow.Cells(0).Value.ToString()
						Dim ItemRev_SMA As String = ItemId_SMA + "/" + ExcelRow.Cells(1).Value.ToString()
						Dim ItemId_SPO As String = ExcelRow.Cells(2).Value.ToString()
						Dim ItemRev_SPO As String = ExcelRow.Cells(3).Value.ToString()
						Dim ItemType_SPO As String = ExcelRow.Cells(5).Value.ToString()

						If Not res.ContainsKey(ItemRev_SMA) Then
							Dim mappingObj = New Mapping_cls(ItemId_SPO,
								ItemRev_SPO,
								ItemType_SPO)
							res.GetOrAdd(ItemRev_SMA, mappingObj)
						End If

						If Not mappingsDictItemId_p.ContainsKey(ItemId_SMA) Then
							Dim mappingObj = New Mapping_cls(ItemId_SPO,
								"",
								ItemType_SPO)
							mappingsDictItemId_p.GetOrAdd(ItemId_SMA, mappingObj)
						End If

					End If
				Next
			Catch ex As Exception
				curInfoSys_p.PrintEx(ex, "EX in Method [ExcelFileRead]")
			End Try

			Return res

		End Function

		''' <summary>
		''' check if oracle bin db is in path
		''' </summary>
		''' <remarks>
		''' #New: 3.1503;08.04.2021;S.Gueth
		''' </remarks>
		Private Sub EnsureOracleBinIsInPath(oracleBinDP_p As String)

			Dim pathEnvVar As String = Environment.GetEnvironmentVariable("PATH")
			If Not pathEnvVar.Contains(oracleBinDP_p) Then
				Environment.SetEnvironmentVariable("PATH", oracleBinDP_p + ";" + pathEnvVar)
			End If

		End Sub

		''' <remarks> 
		''' <br/>#New: 3.2000;31.03.2025;S.Gueth
		''' </remarks>
		Class Mapping_cls

			Sub New(itemId_p As String,
				itemRev_p As String,
				itemType_p As String)
				Me.ItemId = itemId_p
				Me.ItemRev = itemRev_p
				Me.ItemType = itemType_p
			End Sub

			Public Property ItemId As String = ""
			Public Property ItemRev As String = ""
			Public Property ItemType As String = ""
		End Class

		Class VersionCurr
			Public Property VersionId As String = "2026-02-09.V01"
		End Class

	End Class



End Namespace



