RecordOrganizer/app/DD-Record-Organizer/frmSAP_FuBa.vb

Imports ERPConnect
Imports System.Data.SqlClient

Public Class frmSAP_FuBa
    Dim m_CountTo As Integer = 0
    Private Sub TBPMO_SAP_FUBABindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TBPMO_SAP_FUBABindingNavigatorSaveItem.Click
        Try
            Me.TBPMO_SAP_FUBABindingSource.EndEdit()
            If DD_ECMAdmin.TBPMO_SAP_FUBA.GetChanges Is Nothing = False Then
                Me.CHANGED_WHOTextBox.Text = USER_USERNAME
                Me.TBPMO_SAP_FUBABindingSource.EndEdit()
                Me.TBPMO_SAP_FUBATableAdapter.Update(DD_ECMAdmin.TBPMO_SAP_FUBA)
                lblSave.Visible = True
            Else
                lblSave.Visible = False
            End If
        Catch ex As Exception
            MsgBox("Error in Save Fuba:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try



    End Sub

    Private Sub frmSAP_FuBa_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: Diese Codezeile lädt Daten in die Tabelle "DD_ECMAdmin.TBPMO_SAP_FUBA". Sie können sie bei Bedarf verschieben oder entfernen.
        Refresh_Fubas()
    End Sub
    Sub Refresh_Fubas()
        Try
            Me.TBPMO_SAP_FUBATableAdapter.Connection.ConnectionString = MYDB_ECM.CurrentConnectionString
            Me.TBPMO_SAP_FUBATableAdapter.Fill(Me.DD_ECMAdmin.TBPMO_SAP_FUBA)
            If MethodTextBox.Text <> "" Then
                If MethodTextBox.Text = "TABLE" Then
                    rbTable.Checked = True
                Else
                    rBFuba.Checked = True
                End If
            End If
            If rbTable.Checked Then
                MethodTextBox.Text = "TABLE"
                lblMethodname.Text = "SAP Table-/View-Name"
            Else
                lblMethodname.Text = "FuBa Name"
                MethodTextBox.Text = "FUBA"
            End If
        Catch ex As Exception
            MsgBox("Error in Load Fubas:" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub btnERPConnect_Click(sender As Object, e As EventArgs) Handles btnERPConnect.Click
        Me.Cursor = Cursors.WaitCursor
        If ClassSAP.ConnectionInit(HostTextBox.Text, SystemNumberTextBox.Text, UserNameTextBox.Text, PasswordTextBox.Text, ClientTextBox.Text, LanguageTextBox.Text) = True Then
            lblConnectionopen.Text = "SAP Connection successfull"
            lblConnectionopen.BackColor = Color.Yellow
            lblConnectionopen.Visible = True
        Else
            lblConnectionopen.Text = "SAP Connection Error"
            lblConnectionopen.BackColor = Color.Red
            lblConnectionopen.Visible = True
        End If
        Me.Cursor = Cursors.Default
        'Try
        '    Me.Cursor = Cursors.WaitCursor
        '    LOGGER.Debug("")
        '    LOGGER.Debug("SAP Connect started:")
        '    ERPConnect.LIC.SetLic("W86DWC992C")
        '    con.Host = HostTextBox.Text
        '    con.SystemNumber = SystemNumberTextBox.Text
        '    con.UserName = UserNameTextBox.Text
        '    con.Password = PasswordTextBox.Text
        '    con.Client = ClientTextBox.Text
        '    con.Language = LanguageTextBox.Text
        '    LOGGER.Debug("HOST: " & HostTextBox.Text & " |SystemNumber: " & SystemNumberTextBox.Text & " |UserName: " & UserNameTextBox.Text & " |Password: " & PasswordTextBox.Text & " |Client: " & ClientTextBox.Text & " |Language: " & LanguageTextBox.Text)
        '    con.Open(False)
        '    LOGGER.Debug("SAP-Connection erfolgreich aufgebaut!")
        '    LOGGER.Debug("")
        '    lblConnectionopen.Visible = True
        '    Me.Cursor = Cursors.Default
        'Catch ex As Exception
        '    MsgBox("Unexpected Error in SAP Connect: " & ex.Message, MsgBoxStyle.Critical)
        '    lblConnectionopen.Visible = False
        '    Me.Cursor = Cursors.Default
        '    lblResult.Visible = False
        '    GridControl2.DataSource = Nothing
        '    lblSave.Visible = False
        '    Me.Cursor = Cursors.Default
        '    Exit Sub
        'End Try
    End Sub

    Private Sub TBPMO_SAP_FUBABindingSource_AddingNew(sender As Object, e As System.ComponentModel.AddingNewEventArgs) Handles TBPMO_SAP_FUBABindingSource.AddingNew
        DD_ECMAdmin.TBPMO_SAP_FUBA.ADDED_WHOColumn.DefaultValue = USER_USERNAME
    End Sub

    Private Sub TBPMO_SAP_FUBABindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles TBPMO_SAP_FUBABindingSource.CurrentChanged
        lblSave.Visible = False
        GridControl2.DataSource = Nothing
        GridView2.Columns.Clear()
        lblConnectionopen.Visible = False
        If MethodTextBox.Text <> "" Then
            If MethodTextBox.Text = "TABLE" Then
                rbTable.Checked = True
            Else
                rBFuba.Checked = True
            End If
        End If
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnInsertData.Click
        If TempTableNameTextBox.Text <> "" Then
            Me.Cursor = Cursors.WaitCursor
            If ClassSAP.ConnectionInit(HostTextBox.Text, SystemNumberTextBox.Text, UserNameTextBox.Text, PasswordTextBox.Text, ClientTextBox.Text, LanguageTextBox.Text) = True Then
                lblConnectionopen.Visible = True
            Else
                lblConnectionopen.Visible = False
                Me.Cursor = Cursors.Default
                lblResult.Visible = False
                GridControl2.DataSource = Nothing
                GridView2.Columns.Clear()
                lblSave.Visible = False
                Exit Sub
            End If
            Dim rowlimit As Integer = 0
            If txtLimitRows.Text <> "" Then
                rowlimit = txtLimitRows.Text
            End If
            Dim oSAPResulttable As DataTable
            If rbTable.Checked = True Then
                Dim result = ClassSAP.Return_Datatable_for_Table(TableNameTextBox.Text, WHERE_CLAUSETextBox.Text, True, rowlimit)
                If Not IsNothing(result) Then
                    oSAPResulttable = result
                End If
            End If
            If rBFuba.Checked = True Then
                Dim result = ClassSAP.FuBa_Returndatatable_Seperated(TableNameTextBox.Text, FuBa_SplitCharacterTextBox.Text, rowlimit)
                If Not IsNothing(result) Then
                    oSAPResulttable = result
                End If
            End If
            If oSAPResulttable Is Nothing Then
                ClassSAP.SAP_CONNECTION.Close()
                lblResult.Visible = False
                Me.Cursor = Cursors.Default
                lblSave.Visible = False
                GridControl2.DataSource = Nothing
                GridView2.Columns.Clear()
                LOGGER.Warn("SAPResulttable is nothing")
                Exit Sub
            Else
                GridControl2.DataSource = Nothing
                GridView2.Columns.Clear()
                lblResult.Text = ""
            End If
            '######
            Dim i = 0
            Dim oTablename = "[TBPMO_SAP_" & TempTableNameTextBox.Text & "]"
            MYDB_ECM.ExecuteNonQuery("DROP TABLE " & oTablename)

            Dim createtablesql = "CREATE TABLE " & oTablename & " ( "
            For Each col As System.Data.DataColumn In oSAPResulttable.Columns
                If i = 0 Then
                    createtablesql += "[" & col.ColumnName & "] [varchar](250)"
                Else
                    createtablesql += ", [" & col.ColumnName & "] [varchar](250)"
                End If
                i += 1
            Next
            createtablesql += ", [ADDED_WHEN] DATETIME NOT NULL DEFAULT GETDATE()"
            createtablesql += ")"
            ''Die Tabelle anlegen
            LOGGER.Debug("Create Table: " & createtablesql)
            If MYDB_ECM.ExecuteNonQuery(createtablesql) = False Then
                MsgBox("Error in creating TempTable: Check log", MsgBoxStyle.Critical)
                Exit Sub
            End If
            LOGGER.Debug("table has been created")

            Dim alter = String.Format("ALTER TABLE {0} ADD GUID INTEGER NOT NULL IDENTITY(1,1)", oTablename)
            MYDB_ECM.ExecuteNonQuery(alter)
            alter = String.Format("ALTER TABLE {0} ADD ADDED_WHEN DATETIME NOT NULL DEFAULT GETDATE()", oTablename)
            MYDB_ECM.ExecuteNonQuery(alter)
            Try
                'Dim OwnerCS As String
                'OwnerCS = ClassDatabase.GetConnectionString(1)
                Dim SQLconnect As New SqlClient.SqlConnection
                SQLconnect.ConnectionString = MYDB_ECM.CurrentConnectionString
                SQLconnect.Open()
                ' Note that the column positions in the source DataTable 
                ' match the column positions in the destination table, 
                ' so there is no need to map columns.
                Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconnect)
                    bulkCopy.DestinationTableName = oTablename
                    Try
                        bulkCopy.WriteToServer(oSAPResulttable)
                    Catch ex As Exception
                        MsgBox("Unexpected Error in BulkCopy SQL: " & ex.Message, MsgBoxStyle.Critical)
                    End Try
                End Using
                SQLconnect.Close()
                Me.Cursor = Cursors.Default
                LOGGER.Debug("Create table from SAP was successfull " & oSAPResulttable.Rows.Count & " Rows were inserted to table '" & oTablename & "'!")
                MsgBox("Create table was successfull! " & vbNewLine & oSAPResulttable.Rows.Count & " Rows were inserted to table '" & oTablename & "'!", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("Unexpected Error in SQL bulkCopy: " & ex.Message, MsgBoxStyle.Critical)
                Me.Cursor = Cursors.Default
            End Try
            '######
        End If
    End Sub

    Private Sub rbTable_CheckedChanged(sender As Object, e As EventArgs) Handles rbTable.CheckedChanged
        If rbTable.Checked Then
            MethodTextBox.Text = "TABLE"
            lblMethodname.Text = "Table Name"
        Else
            lblMethodname.Text = "FuBa Name"
            MethodTextBox.Text = "FUBA"
        End If
    End Sub

    Private Sub MethodTextBox_TextChanged(sender As Object, e As EventArgs) Handles MethodTextBox.TextChanged
        If MethodTextBox.Text = "TABLE" Then
            rbTable.Checked = True
        End If
    End Sub

    Private Sub btnGetResult_Click(sender As Object, e As EventArgs) Handles btnGetResult.Click

        Dim sw As New Stopwatch
        sw.Start()
        m_CountTo = 100

        Me.Cursor = Cursors.WaitCursor
        If ClassSAP.ConnectionInit(HostTextBox.Text, SystemNumberTextBox.Text, UserNameTextBox.Text, PasswordTextBox.Text, ClientTextBox.Text, LanguageTextBox.Text) = True Then
            lblConnectionopen.Visible = True
        Else
            lblConnectionopen.Visible = False
            Me.Cursor = Cursors.Default
            lblResult.Visible = False
            GridControl2.DataSource = Nothing
            GridView2.Columns.Clear()
            lblSave.Visible = False
            sw.Stop()
            Exit Sub
        End If
       
        Dim SAPResulttable As DataTable = New DataTable
        Dim rowlimit As Integer = 0
        If txtLimitRows.Text <> "" Then
            rowlimit = txtLimitRows.Text
        End If
        If rbTable.Checked = True Then

            Dim result = ClassSAP.Return_Datatable_for_Table(TableNameTextBox.Text, WHERE_CLAUSETextBox.Text, True, rowlimit)
            If Not IsNothing(result) Then
                SAPResulttable = result
            End If
        ElseIf rBFuba.Checked = True Then 'FUBA Abruf
            Dim result = ClassSAP.FuBa_Returndatatable_Seperated(TableNameTextBox.Text, FuBa_SplitCharacterTextBox.Text, rowlimit)
            If Not IsNothing(result) Then
                SAPResulttable = result
            End If
        End If
        If SAPResulttable Is Nothing Then
            ClassSAP.SAP_CONNECTION.Close()
            lblResult.Visible = False
            Me.Cursor = Cursors.Default
            lblSave.Visible = False
            GridControl2.DataSource = Nothing
            GridView2.Columns.Clear()
            sw.Stop()
            LOGGER.Warn("SAPResulttable is nothing")
            Exit Sub
        End If


        'Jetzt dem Grid die Datasource zuordnen

        Try
            If SAPResulttable.Rows.Count > 0 Then
                GridControl2.DataSource = SAPResulttable
            Else
                Try
                    GridControl2.DataSource = Nothing
                    GridView2.Columns.Clear()
                Catch ex As Exception

                End Try
            End If
            ClassSAP.SAP_CONNECTION.Close()
            Dim elapsed As Double
            elapsed = sw.Elapsed.TotalSeconds
            sw.Stop()
            If rowlimit <> 0 And SAPResulttable.Rows.Count >= rowlimit Then
                lblResult.Text = "Result (limited to " & rowlimit.ToString & " rows) - Duration: " & Format(elapsed, "0.00") & " seconds - " & Now.ToShortTimeString
            Else
                lblResult.Text = "Result (unlimited) >> " & SAPResulttable.Rows.Count & " rows retrieved - Duration: " & Format(elapsed, "0.00") & " seconds - " & Now.ToShortTimeString
            End If
            lblResult.Visible = True
            Me.Cursor = Cursors.Default
            lblSave.Visible = False
           
        Catch ex As Exception
            ClassSAP.SAP_CONNECTION.Close()
            lblResult.Visible = False
            Me.Cursor = Cursors.Default
            lblSave.Visible = False
            GridControl2.DataSource = Nothing
            GridView2.Columns.Clear()
            LOGGER.Warn("Unexpected Error in GridControl2 >> DataTable 2: " & ex.Message)
            MsgBox("Error in GridControl2 >> DataTable: " & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

    Private Sub rBFuba_CheckedChanged(sender As Object, e As EventArgs) Handles rBFuba.CheckedChanged
        If rBFuba.Checked = True Then
            Me.FuBa_SplitCharacterTextBox.Visible = True
            lblSplitchr.Visible = True
        Else
            Me.FuBa_SplitCharacterTextBox.Visible = False
            lblSplitchr.Visible = False
        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Refresh_Fubas()
    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Cursor = Cursors.WaitCursor
        lblresultFunction.Visible = False
        If ClassSAP.ConnectionInit(testHOST.Text, testSYSNR.Text, testUSERName.Text, testPW.Text, testClient.Text, testLanguage.Text) = True Then
            lblSuccess.Visible = True
        Else
            lblSuccess.Visible = False
            Me.Cursor = Cursors.Default
            Exit Sub
        End If
        Dim result = ClassSAP.INSERT_ARCHIVE_OBJECT(testArchiveID.Text, testDOCID.Text, testAROBJECT.Text, testAROBJECT.Text, testSAP_OBJECT.Text, testDOCTYPE.Text, testTablename.Text)
        lblresultFunction.Visible = True
        If result <> "" Then
            lblresultFunction.Text = result.ToString
        Else
            lblresultFunction.Text = "Function executed successfully!"
        End If
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Cursor = Cursors.WaitCursor
        lblResultBAPI.Visible = False
        If ClassSAP.ConnectionInit(testHOST.Text, testSYSNR.Text, testUSERName.Text, testPW.Text, testClient.Text, testLanguage.Text) = True Then
            lblSuccess.Visible = True
        Else
            lblSuccess.Visible = False
            Me.Cursor = Cursors.Default
            Exit Sub
        End If
        Dim result = ClassSAP.LINK_ARC_OBJECT(txtTableSendList.Text, txtBARCODESendlist.Text, txtCONTREPSendList.Text, txtDOC_IDSendList.Text, txtARDATESendList.Text, txtDoctypeSendList.Text)
        lblResultBAPI.Visible = True
        If result <> "" Then
            lblResultBAPI.Text = result.ToString
        Else
            lblResultBAPI.Text = "Function executed successfully!"
        End If
        Me.Cursor = Cursors.Default
    End Sub


End Class