Option Compare Text
Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Collections.Generic
Imports System.Data

Imports JF.PlmJobManager.JobServer
Imports JF.Tools
Imports JF.PlmJobManager


' # Doc: Example Usage of external .net .dll
'xReferences CustomDll_Example.dll

' Information for developer:
' All Lines with mark "#" should be edit by the developer
' # Vers: 02.02.2018 S.Gueth
' # ---------------------------------------------------------------------------------------
' # Documentation:
' # Description  : script to get work floww error qout data for PLMPerf Analyse via PLMConnector database (BSH)
' # Parameters   : 
' # 
' # History:
' # Date         Person      ChangeInfo
' # 02.02.18     S.Gueth     created
' # ---------------------------------------------------------------------------------------

Namespace JS_ScriptingTemplate

    Public Class WorkFlowErrorQuote


        ''' <summary>
        ''' Returns Description and Version Info for ths script
        ''' </summary>
        Public Shared ReadOnly Property ScriptDescVers As String
            Get
                Return "Workflow error quote get for PLMPerf  V02.02.2018 S.Gueth"
            End Get
        End Property

        Private ScriptOutput_frm As JobServer_ScriptRun_frm


        ''' <summary>
        ''' #Please replace summary content
        ''' </summary>
        ''' <param name="JS_ScriptingInterface"></param>
        ''' <returns></returns>
        ''' <remarks> 
        ''' #New: #Date# / Name
        ''' </remarks>
        Public Function ScriptMain(ByVal JS_ScriptingInterface As ScriptingInterface.Methodes) As JF_Return_cls

            '-- #Doc: ScriptResult_r Required Object for returning script results
            Dim ScriptResult_r As New JF_Return_cls
            Dim RunResult_Obj As New JF_Return_cls

            Dim TextStr As String = ""

            '-- #Doc: using "standard form" for printing script informations 
            Me.ScriptOutput_frm = New JobServer_ScriptRun_frm(JS_ScriptingInterface.ScriptDef)
            Me.ScriptOutput_frm.Show()
            Me.ScriptOutput_frm.InfoSysProtocol.PrintObject("Script Descr + Ver", ScriptDescVers)
            Me.ScriptOutput_frm.InfoSysProtocol.StatusBarSetCurrActionInfo_Panel1(ScriptDescVers)

            Dim I As Integer = 0

            '-- #Doc: printing Informations to the header textbox 
            Call Me.ScriptOutput_frm.InfoSysHeader.PrintInfoLine("#Step - 1 Script started!")

            '-- #Doc: handling Exeptions via Try Catch
            Try

                '-- starting the progress bar
                Me.ScriptOutput_frm.InfoSysProtocol.StatusBarProgressBarStart()
                Me.ScriptOutput_frm.InfoSysHeader.Print("#Step - 2 Script is in process")

                ' add ora client to path
                JF.Tools.PathInsertPathPart("\\mchswitbmon01\addPLM\JobManagerV3\03-BinPublic\OraClient\64Bit", False)

                ' define WorkflowPerfData path
                Dim WorkflowPerfData_DP = "\\mchswitbmon01\PLMPerfData\WorkFlows\WorkFlows_Total"

                ' define start and end date (last day)
                Dim EndDate As String = Date.Now.ToString("dd.MM.yyyy")
                Dim StartDate As String = Date.Now.AddDays(-1).ToString("dd.MM.yyyy")



                ' -- Start: define Data for TC DB ------------------------------------------------------

                Dim TcDbProviderFactory As System.Data.Common.DbProviderFactory
                Dim TcDbConnection As System.Data.Common.DbConnection
                TcDbProviderFactory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.OracleClient")
                TcDbConnection = TcDbProviderFactory.CreateConnection
                TcDbConnection.ConnectionString = "Data Source=(DESCRIPTION =(ADDRESS_LIST =(LOAD_BALANCE = OFF)(FAILOVER = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = cdcslxs3050.bsh.corp.bshg.com)(PORT = 1585))(ADDRESS = (PROTOCOL = TCP)(HOST = cdcslxs3000.bsh.corp.bshg.com)(PORT = 1585)))(CONNECT_DATA =(SERVICE_NAME = TC1P.MCH.BSHG.COM)(FAILOVER_MODE =(TYPE = SESSION)(METHOD = BASIC)(RETRIES = 10)(DELAY = 5))));User Id=tcinfouser;Password=info10_DB;"

                Dim totalWorkflows_SQL As String = " SELECT (tasktempl.ptemplate_name) as WorkflowTemplateName, count (*) as Count " +
                    " FROM infodba.PPOM_APPLICATION_OBJECT paobject ," +
                    "      infodba.PEPMJOB job ," +
                    "      infodba.PEPMTASKTEMPLATE tasktempl" +
                    " WHERE paobject.pcreation_date >= TO_DATE('#StartDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND" +
                    "       paobject.pcreation_date <= TO_DATE('#EndDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND" +
                    "       job.rprocess_templateu = tasktempl.puid AND" +
                    "          tasktempl.ptemplate_name  LIKE  UPPER( 'BSH%' ) AND" +
                    "       paobject.puid = job.puid" +
                    " GROUP BY tasktempl.ptemplate_name"

                ' -- End: define Data for TC DB ------------------------------------------------------


                ' get total workflows count
                Dim totalWorkflows As Integer = SQLExecute(TcDbConnection, StartDate, EndDate, totalWorkflows_SQL, "tc1p")



                ' -- Start: define Data for PL Connector ------------------------------------------------------

                Dim PLMConDbProviderFactory As System.Data.Common.DbProviderFactory
                Dim PLMConDbConnection As System.Data.Common.DbConnection
                PLMConDbProviderFactory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.OracleClient")
                PLMConDbConnection = PLMConDbProviderFactory.CreateConnection
                PLMConDbConnection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rb0orarac13.bsh.corp.bshg.com)(PORT=38000))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PLCONECP_rb0orarac13.bsh.corp.bshg.com)));User Id=tc_mon;Password=3IIXI9BPXP$41;"

                Dim errorWorkflows_SQL As String = " SELECT SUM(anzahl) as Count, WORKFLOW_TEMPLATE as WorkflowTemplateName FROM" +
                                                   " ((SELECT COUNT(*) AS anzahl, PWD.DATA_VALUE As WORKFLOW_TEMPLATE" +
                                                   " FROM P_PLMC_PROBLEM_WORKFLOW PW, P_PLMC_PROBLEM_WORKFLOW_DATA PWD" +
                                                   " WHERE PW.ID = PWD.DATA_PROBLEM And PWD.DATA_KEY Like '%workflow'" +
                                                   " AND PW.CREATEDON BETWEEN TO_DATE('#StartDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND TO_DATE('#EndDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS')" +
                                                   " GROUP BY PWD.DATA_VALUE" +
                                                   " )" +
                                                   " UNION" +
                                                   " (SELECT COUNT(*) AS anzahl, PWDA.DATA_VALUE AS WORKFLOW_TEMPLATE" +
                                                   " FROM P_PLMC_PROBLEM_WORKFLOW_ARCH PWA, P_PLMC_PROBLEM_WORKFLOW_DATA_A PWDA" +
                                                   " WHERE PWA.ID = PWDA.DATA_PROBLEM AND PWDA.DATA_KEY LIKE '%workflow'" +
                                                   " And PWA.CREATEDON BETWEEN TO_DATE('#StartDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS') AND TO_DATE('#EndDate# 00:00:00', 'DD.MM.YYYY HH24:MI:SS')" +
                                                   " GROUP BY PWDA.DATA_VALUE" +
                                                   " ))" +
                                                   " GROUP BY WORKFLOW_TEMPLATE" +
                                                   " ORDER BY SUM(anzahl) DESC"

                ' -- End: define Data for PL Connector ------------------------------------------------------


                ' get error workflows count
                Dim errorWorkflows As Integer = SQLExecute(PLMConDbConnection, StartDate, EndDate, errorWorkflows_SQL, "PLCONECP")


                ' generate result csv file

                Dim csvFile_NE As String = Date.Now.ToString("yyyy-MM-dd") + ".csv"
                Dim csvFile_DPNE As String = IO.Path.Combine(WorkflowPerfData_DP, csvFile_NE)

                Dim fileHeader As String = "TimeSpanStartDate;TimeSpanStartDTime;TimeSpanEndDate;TimeSpanEndTime;WorkflowTotal;WorkflowErrors;WorkflowErrorQuote"

                ' check if file allready exists
                If Not IO.File.Exists(csvFile_DPNE) Then
                    ' write header
                    Try
                        IO.File.AppendAllLines(csvFile_DPNE, New String() {fileHeader})
                    Catch ex As Exception
                        ScriptResult_r.Ex = ex
                        ScriptResult_r.ExUp = True
                        ScriptResult_r.ExMsg = "error on generation result file"
                        Me.ScriptOutput_frm.InfoSysProtocol.PrintEx(ex, ScriptResult_r.ExMsg)
                        I = 20
                        GoTo EndOfScript
                    End Try
                End If

                ' write data to result csv file
                Dim quote As Double = Math.Round(errorWorkflows / totalWorkflows, 4)
                Dim dataLine = String.Format("{0};00:00:00;{1};00:00:00;{2};{3};{4}", StartDate, EndDate, totalWorkflows.ToString, errorWorkflows.ToString, quote.ToString.Replace(".", ","))

                Me.ScriptOutput_frm.InfoSysProtocol.Print("")
                Me.ScriptOutput_frm.InfoSysProtocol.Print("WF Errors: " + errorWorkflows.ToString)
                Me.ScriptOutput_frm.InfoSysProtocol.Print("WF Total: " + totalWorkflows.ToString)
                Me.ScriptOutput_frm.InfoSysProtocol.Print("WF Quote: " + quote.ToString())

                Try
                    IO.File.AppendAllLines(csvFile_DPNE, New String() {dataLine})
                Catch ex As Exception
                    ScriptResult_r.Ex = ex
                    ScriptResult_r.ExUp = True
                    ScriptResult_r.ExMsg = "error on writing data line to csv file"
                    Me.ScriptOutput_frm.InfoSysProtocol.PrintEx(ex, ScriptResult_r.ExMsg)
                    I = 30
                    GoTo EndOfScript
                End Try


            Catch ex As Exception
                ScriptResult_r.Ex = ex
                ScriptResult_r.ExUp = True
                ScriptResult_r.ExMsg = "CustomScript"
                Me.ScriptOutput_frm.InfoSysProtocol.PrintEx(ex, ScriptResult_r.ExMsg)
                I = 10
                GoTo EndOfScript
            End Try

            '-- #Doc: returning results 
            I = 0
            If I = 0 Then
                ScriptResult_r.ResultCode = 0
                ScriptResult_r.ResultMsg = "OK.: Schript ends with no errors (message generated with script)"
            Else
                ScriptResult_r.ResultCode = 10
                ScriptResult_r.ResultMsg = "ERR: Schript ends with ERRORS! (message generated with script)"
            End If

            ScriptResult_r.ResultMsg += vbCrLf + JS_ScriptingInterface.ScriptDef.Script_DPNE

            '-- setting the progress bar to idel			
            Me.ScriptOutput_frm.InfoSysHeader.Print("#Step - 3 Progressbar set idle")
            System.Threading.Thread.Sleep(1000)
            Call ScriptOutput_frm.InfoSysProtocol.StatusBarProgressBarSetIdle()

EndOfScript:

            Call ScriptOutput_frm.InfoSysProtocol.PrintInfoLine(ScriptResult_r.ResultMsg)

            Me.ScriptOutput_frm.InfoSysHeader.Print(TextStr)
            Me.ScriptOutput_frm.InfoSysProtocol.Print_and_StatusBarSetCurrActionStatusInfo_Panel3(TextStr)

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

            Return ScriptResult_r
        End Function

        ''' <summary>
        ''' function to get count of all workflows for a given timespan
        ''' </summary>
        ''' <remarks> 
        ''' #New: 3.1451;02.02.2018;S.Gueth
        ''' </remarks>
        Private Function SQLExecute(ByRef DBConnection_obj As Common.DbConnection,
            ByVal StartDate_p As String,
            ByVal EndDate_p As String,
            ByVal SQLTemplate_p As String,
            ByVal SiteID_p As String) As Integer

            Dim SQLText = SQLTemplate_p.Replace("#StartDate#", StartDate_p)
            SQLText = SQLText.Replace("#EndDate#", EndDate_p)

            Dim cmd = DBConnection_obj.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = SQLText

            Dim workFlowCountTotal As Integer = 0

            Try
                DBConnection_obj.Open()

                Using reader = cmd.ExecuteReader()

                    While reader.Read()

                        Dim count As Integer = 0
                        If Integer.TryParse(reader.Item("Count").ToString, count) Then
                            workFlowCountTotal += count
                        End If

                        Me.ScriptOutput_frm.InfoSysProtocol.Print(reader.Item("WorkflowTemplateName").ToString + ": " + count.ToString)

                    End While

                End Using

            Catch ex As Exception

                Me.ScriptOutput_frm.InfoSysProtocol.Print("Exception on executing SQL in Database:  " + SiteID_p)
                Me.ScriptOutput_frm.InfoSysProtocol.Print("Exeption: " + ex.ToString)
                Me.ScriptOutput_frm.InfoSysProtocol.Print("Ex Msg: " + ex.Message)
                Me.ScriptOutput_frm.InfoSysProtocol.Print("SQL: " + SQLText)

            Finally
                DBConnection_obj.Close()
            End Try

            Me.ScriptOutput_frm.InfoSysProtocol.Print("")
            Me.ScriptOutput_frm.InfoSysProtocol.Print(SiteID_p + " workFlowCountTotal: " + workFlowCountTotal.ToString)
            Return workFlowCountTotal

        End Function

    End Class
End Namespace
