Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Linq
Imports System.Data
Imports System.Collections.Generic
Imports JF.PlmJobManager
Imports JF.PlmJobManager.JobServer
Imports JF.Tools

'' Script For Index reorg for JobManager db via TaskSheduler: 
''  Date         Person        ChangeInfo
''  26.06.2022   S.Gueth       created
''  29.06.2022   J.Fes         Output of messages optimised

'' #Doc: Parameter usage:
'' Parameter                           Description   
'' /PauseOnEnd=true/false              enables / disables  pause on end 		

Namespace JobMgrScript


    ' ApplWhatsNew: 3.1602;29.08.2022;S.Gueth;TaskScheduler;UpdDb=No;New:
    '++ \nNew Standard TaskSheduler Script avilabel: 'S:JobServerReoergIndices.vb'
    '++ \nThis TaskSheduler script will reorganice the JobManager database indices, 
    '++ \nand can be automated executed via JobServer TaskSheduler.


    ' ApplWhatsNew: 3.2000;30.07.2024;J.Fes;Install and Setup,Scripts/Params.;UpdDb=Yes,Ver.Info;Doc:
    '++ \nJobMgr Database Maintain via 
    '++ \n -> Extra 
    '++ \n  |-> 01 - Setup - Server Settings 
    '++ \n   |-> Setup database 
    '++ \n    |-> Database Maintain 
    '++ \n     |-> 22 Optimice "Index" (Reorg. Indices via SQLCmd)
    '++ \n
    '++ \nSupports now for JobMgr.DB Tables Update Statistic methodes
    '++ \nThis will help to optimice JobMgr SQL performance behavior
    '++ \nEnhancement implemented at sql script JobMgr_61_DataBaseOptimice_ReorgIndices.sql

    ''' <summary>
    ''' Main Script
    ''' </summary>
    ''' <remarks> 
    ''' #New: 3.1602;26.08.2022;S.Gueth
    ''' #LUp: 3.1602;29.08.2022;J.Fes
    ''' </remarks>
    Public Class JobServerReorgIndices_cls

        Private InfoSysHeader As JF.InfoSys.JF_InfoSys = Nothing
        Private InfoSysProtocol As JF.InfoSys.JF_InfoSys = Nothing
        Private sqlProcessIsRunning As Boolean = False

        '-- Required object for returning script results
        Dim ScriptResult_r As New JF_Return_cls

        Public Function ScriptMain(ByVal JS_ScriptingInterface As JobServer.ScriptingInterface.Methodes) As JF_Return_cls

            Dim StatusToSet As String = Nothing
            Dim ProcessGroupNumber As String = Nothing
            Dim PauseOnEnd As Boolean = False
            Dim ScriptParameter As String = JS_ScriptingInterface.ScriptDef.cmdPara
            Dim SQLCMD_EXE_NE As String = "SQLCMD.EXE"
            Dim SQLCMD_EXE_DPNE As String = ""
            Dim SQLScriptsDPNE_ToExecute As New List(Of String)

            PauseOnEnd = If(JS_ScriptingInterface.ParameterValueGet("PauseOnEnd") = "true", True, False)

            Dim ScriptOutput_frm As JobServer.JobServer_ScriptRun_frm = Nothing
            If PauseOnEnd Then
                '-- #Step: open the ScriptRun form  
                ScriptOutput_frm = New JobServer.JobServer_ScriptRun_frm(JS_ScriptingInterface.ScriptDef)
                ScriptOutput_frm.Show()
                InfoSysHeader = ScriptOutput_frm.InfoSysHeader
                InfoSysProtocol = ScriptOutput_frm.InfoSysProtocol
            Else
                InfoSysHeader = JS_ScriptingInterface.CurActiveInfoSysGet
                InfoSysProtocol = JS_ScriptingInterface.CurActiveInfoSysGet
            End If

            '-- for printing Informations to the header textbox 
            '
            '-- #ApplDokuIntern: 3.2000;23.07.2024;J.Fes;#Doc:
            Call InfoSysProtocol.PrintInfoListClear("ReOrg JobManager Indices")
            Call InfoSysHeader.PrintObject("Script file", JS_ScriptingInterface.ScriptDef.Script_DPNE)
            ' Call InfoSysHeader.PrintObject("Script description", JS_ScriptingInterface.ScriptDef.NameDescription)
            Call InfoSysHeader.PrintObject("Script description", JS_ScriptingInterface.ScriptDef.TaskSchedulerDef.Description)
            Call InfoSysHeader.PrintObject("using Paramter", ScriptParameter)

            Try

                Dim sw_Obj As New JF.Tools.JF_Stopwatch_cls("ReOrg JobManager Indices")
                sw_Obj.Start()

                Me.ScriptResult_r.ResetToDefaultValues()
                Call InfoSysProtocol.PrintObject("Database Maintain:", "Optimice Database (ReOrg. Indices)")

                InfoSysProtocol.StatusBarProgressBarStart()

                ' get sql scripts to run reorg indices
                Call JS_ScriptingInterface.MyApplSettingsObjGet.DataBaseMaintain_22_OptimiceDB_ReOrgIndices(MsgBoxResult.Yes, SQLScriptsDPNE_ToExecute)
                'search in Path at SQLCMD
                SQLCMD_EXE_DPNE = JF.Tools.JF_ToolsFile.FileFindInPath(SQLCMD_EXE_NE)

                If String.IsNullOrEmpty(SQLCMD_EXE_DPNE) Then
                    Call InfoSysProtocol.PrintObject("Cannot continue", "ERR:fnf Required Program file 'SQLCMD.EXE' not found")
                    Call InfoSysProtocol.PrintObject("searched in path", JF.Tools.GetEnvironmentVariable("%path%"))
                    Exit Try
                Else
                    Call InfoSysProtocol.PrintObject("Using SQLCMD.exe", SQLCMD_EXE_DPNE)
                End If

                For Each SQLScript_DPNE As String In SQLScriptsDPNE_ToExecute

                    Try
                        Me.sqlProcessIsRunning = False

                        'define process info object
                        Dim procStartInfo As New System.Diagnostics.ProcessStartInfo()
                        procStartInfo.FileName = SQLCMD_EXE_DPNE
                        procStartInfo.Arguments = JS_ScriptingInterface.MyApplSettingsObjGet.ServerSettings.MSSQL_CMDSQL_DBConnectionString + " -i """ + SQLScript_DPNE + """ -w 256"
                        procStartInfo.RedirectStandardError = True
                        procStartInfo.RedirectStandardOutput = True
                        procStartInfo.UseShellExecute = False
                        procStartInfo.CreateNoWindow = True
                        '   procStartInfo.StandardOutputEncoding = System.Text.Encoding.GetEncoding(65001)

                        'define process object, enable events to print the output
                        Dim sqlProcess As New System.Diagnostics.Process
                        sqlProcess.StartInfo = procStartInfo
                        sqlProcess.EnableRaisingEvents = True

                        ' add handler when process is finished
                        AddHandler sqlProcess.Exited,
                                    Sub(source As Object, ev As EventArgs)

                                        Try
                                            sqlProcess.Close()
                                            If sqlProcess IsNot Nothing Then
                                                sqlProcess.Dispose()
                                            End If
                                        Catch ex As Exception
                                            ScriptResult_r.Ex = ex
                                            ScriptResult_r.ExMsg = "Exeption on SQLCMD.exe process exited!"
                                            InfoSysProtocol.PrintEx(ex, "JobServer_ReorgIndexes.SQLProcessExited")
                                        Finally
                                            Me.sqlProcessIsRunning = False
                                        End Try

                                    End Sub

                        ' add handler when process has std output
                        AddHandler sqlProcess.OutputDataReceived, AddressOf Me.ProcessOutputDataRecieved

                        ' add handler when process has errutput
                        AddHandler sqlProcess.ErrorDataReceived, AddressOf Me.ProcessErrorDataRecieved

                        'execute SQL cmd Process
                        Me.sqlProcessIsRunning = True
                        sqlProcess.Start()
                        sqlProcess.BeginErrorReadLine()
                        sqlProcess.BeginOutputReadLine()

                        ' wait until process finished
                        While sqlProcessIsRunning
                            Threading.Thread.Sleep(5)
                        End While

                    Catch ex As Exception

                    End Try

                Next

                '-- optimiceDB_ReOrgIndices_Task.Dispose()
                sw_Obj.Stop()
                Call InfoSysProtocol.PrintObject("Optimice Database duration", sw_Obj.TimeElapsed_str)
                'Me.ScriptResult_r.ResultMsgAdd(InfoSysProtocol.PrintTextLineLast_r)
                'Me.ScriptResult_r.ResultMsgAdd("Optimice Database duration:" + sw_Obj.TimeElapsed_str)

            Catch ex As Exception
                ScriptResult_r.Ex = ex
                ScriptResult_r.ExMsg = "Exeption in script:" + JS_ScriptingInterface.ScriptDef.NameDescription
                Call InfoSysProtocol.PrintEx(ex, "JobServer_ReorgIndexes.ScriptMain")
                Me.ScriptResult_r.ResultMsgAdd(ScriptResult_r.ExMsg)
                Me.ScriptResult_r.ResultMsgAdd("Exception.Message:" + ex.Message)
            End Try

            Me.ScriptResult_r.ResultMsgAdd("Process result see JobServerInfoSys.log:")
            Me.ScriptResult_r.ResultMsgAdd(Me.InfoSysProtocol.SyslogFileCurrent_DPNE)

            InfoSysProtocol.StatusBarProgressBarSetIdle()

            If PauseOnEnd Then
                ScriptOutput_frm.Pause()
                '-- #Step: closing the ScriptRun form 
                ScriptOutput_frm.Close()
                ScriptOutput_frm.Dispose()
            Else
                Dim WaitTimeIn_MSec As Integer = 2500
                InfoSysProtocol.Print_and_StatusBarSetCurrActionStatusInfo_Panel3("wait:" + (WaitTimeIn_MSec / 1000).ToString("#.#") + " sec")
                System.Threading.Thread.Sleep(WaitTimeIn_MSec)
            End If

            Return ScriptResult_r
        End Function

        ''' <summary>
        ''' will be called when process reports std data
        ''' </summary>
        ''' <remarks> 
        ''' #New: 3.1602;26.08.2022;S.Gueth
        ''' </remarks>
        Private Sub ProcessOutputDataRecieved(sender As Object, e As System.Diagnostics.DataReceivedEventArgs)
            If Not String.IsNullOrEmpty(e.Data) Then
                Me.InfoSysProtocol.Print(e.Data)
                'Me.ScriptResult_r.ResultMsgAdd("OK: " + e.Data)
            End If
        End Sub

        ''' <summary>
        ''' will be called when process reports error data
        ''' </summary>
        ''' <remarks> 
        ''' #New: 3.1602;26.08.2022;S.Gueth
        ''' </remarks>
        Private Sub ProcessErrorDataRecieved(sender As Object, e As System.Diagnostics.DataReceivedEventArgs)
            If Not String.IsNullOrEmpty(e.Data) Then
                Me.InfoSysProtocol.Print(e.Data)
                'Me.ScriptResult_r.ResultMsgAdd("ERR: " + e.Data)
            End If
        End Sub

    End Class
End Namespace

