Imports guinet

Public Class dropdownclass

    Public myguixt As guixt
    Private myDtSet1 As DataSet

    Public Function initDropDowns(filename As String) As String

        Dim excelfile As String = filename
        Dim MyConnection As OleDb.OleDbConnection
        Dim errorMessage As String = ""
        Dim sw As New Stopwatch

        If myguixt Is Nothing Then
            myguixt = New guixt
        End If

        Try
            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Read data from Excel File (one time)
            If myDtSet1 Is Nothing Then

                Dim MyCommand As OleDb.OleDbDataAdapter
                Dim sqlcondition As String = ""
                myDtSet1 = New DataSet

                MyConnection = New OleDb.OleDbConnection _
                        ("provider=Microsoft.Jet.OLEDB.4.0;Data source='" _
                         & excelfile _
                         & "';Extended Properties=Excel 8.0")

                Dim columNames As String() = {"salesorganization", _
                                              "distributionchannel", "division", _
                                              "salesgroup", "salesoffice"}

                ' Read the data into a datatable object within the dataset
                For Each column In columNames

                    sqlcondition = "select * from [" & column & "$] "

                    MyCommand = New OleDb.OleDbDataAdapter _
                        (sqlcondition, MyConnection)

                    MyCommand.Fill(myDtSet1, column)
                Next

                MyConnection.Close()

            End If

            ' Start stopwatch to measure the runtime
            sw.Start()

            ' Chosen values in Dropdown (user selection)
            Dim u_vkorg As String = myguixt.GetVariable("va01_vkorg").Trim
            Dim u_vtweg As String = myguixt.GetVariable("va01_vtweg").Trim
            Dim u_spart As String = myguixt.GetVariable("va01_spart").Trim
            Dim u_vkbur As String = myguixt.GetVariable("va01_vkbur").Trim
            Dim u_vkgrp As String = myguixt.GetVariable("va01_vkgrp").Trim


            Dim isInitital As Boolean = True
            If u_vkorg <> "" Or u_vtweg <> "" _
                Or u_spart <> "" Or u_vkbur <> "" _
                Or u_vkgrp <> "" Then

                isInitital = False

            End If


            ' Data from excel file in datatables
            Dim salesorganization As DataTable = myDtSet1.Tables("salesorganization")
            Dim distributionchannel As DataTable = myDtSet1.Tables("distributionchannel")
            Dim division As DataTable = myDtSet1.Tables("division")
            Dim salesgroup As DataTable = myDtSet1.Tables("salesgroup")
            Dim salesoffice As DataTable = myDtSet1.Tables("salesoffice")


            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Build a where condition for the select statement
            Dim cond As String = ""

            If u_vkorg.Trim <> "" Then
                ' User selected a value for sales office
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If

            If u_vtweg.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If
                cond &= "DChl = " & "'" & u_vtweg & "'"
            End If

            If u_spart.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If
                cond &= "Dv = " & "'" & u_spart & "'"

            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Excecute the select statement 
            Dim salesoffice_filtered As DataRow() = salesoffice.Select(cond)
            '''''''''''''''''''''''''
            '''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            cond = ""
            If u_vkorg.Trim <> "" Then
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If
            Dim distributionchannel_filtered As DataRow() = distributionchannel.Select(cond)

            cond = ""
            If u_vkorg.Trim <> "" Then
                cond = "Sorg = " & "'" & u_vkorg & "'"
            End If

            If u_vtweg.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If

                cond &= "DChl = " & "'" & u_vtweg & "'"
            End If

            Dim division_filtered As DataRow() = division.Select(cond)
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            cond = ""
            If u_vkbur.Trim <> "" Then
                cond = "Soff = " & "'" & u_vkbur & "'"
            End If

            If u_vkgrp.Trim <> "" Then
                If cond <> "" Then
                    cond &= " AND "
                End If

                cond &= "SGrp = " & "'" & u_vkgrp & "'"
            End If


            Dim salesgroup_filtered As DataRow() = salesgroup.Select(cond)
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            ' Read a joined table to build the new dropdown values

            Dim query_vkorg

            If isInitital Then

                query_vkorg = _
               From sorg In salesorganization
               Select New With _
             { _
               Key .vkorg = sorg.Field(Of String)("Sorg"), _
               .vkorg_t = sorg.Field(Of String)("Name")
             }

            Else

                query_vkorg = _
               From sorg In salesorganization
               Join soff In salesoffice_filtered
               On sorg.Field(Of String)("Sorg") Equals soff.Field(Of String)("Sorg")
               Join grp In salesgroup_filtered
               On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkorg = sorg.Field(Of String)("Sorg"), _
               .vkorg_t = sorg.Field(Of String)("Name")
             }


            End If

            ' Buld a dictionary for the dropdown values
            Dim d_vkorg As New Dictionary(Of String, String)
            For Each v In query_vkorg
                d_vkorg.Item(v.vkorg) = v.vkorg & " " & v.vkorg_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vkbur

            If isInitital Then
                query_vkbur = _
             From soff In salesoffice_filtered
             Select New With _
           { _
             Key .vkbur = soff.Field(Of String)("Soff"), _
             .vkbur_t = soff.Field(Of String)("Description")
           }
            Else
                query_vkbur = _
               From sorg In salesorganization
               Join soff In salesoffice_filtered
               On sorg.Field(Of String)("Sorg") Equals soff.Field(Of String)("Sorg")
               Join grp In salesgroup_filtered
               On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkbur = soff.Field(Of String)("Soff"), _
               .vkbur_t = soff.Field(Of String)("Description")
             }
            End If


            Dim d_vkbur As New Dictionary(Of String, String)
            For Each v In query_vkbur
                d_vkbur.Item(v.vkbur) = v.vkbur & " " & v.vkbur_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vtweg
            If isInitital Then
                query_vtweg = _
              From dist In distributionchannel_filtered
              Select New With _
            { _
              Key .vtweg = dist.Field(Of String)("DChl"), _
              .vtweg_t = dist.Field(Of String)("Name")
            }

            Else
                query_vtweg = _
              From dist In distributionchannel_filtered
              Join soff In salesoffice_filtered
              On dist.Field(Of String)("DChl") Equals soff.Field(Of String)("DChl")
              Join grp In salesgroup_filtered
              On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
              Select New With _
            { _
              Key .vtweg = dist.Field(Of String)("DChl"), _
              .vtweg_t = dist.Field(Of String)("Name")
            }

            End If

            Dim d_vtweg As New Dictionary(Of String, String)
            For Each v In query_vtweg
                d_vtweg.Item(v.vtweg) = v.vtweg & " " & v.vtweg_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_spart
            If isInitital Then
                query_spart = _
              From div In division_filtered
              Select New With _
            { _
              Key .spart = div.Field(Of String)("Dv"), _
              .spart_t = div.Field(Of String)("Name")
            }
            Else
                query_spart = _
              From div In division_filtered
              Join soff In salesoffice_filtered
              On div.Field(Of String)("Dv") Equals soff.Field(Of String)("Dv")
              Join grp In salesgroup_filtered
              On soff.Field(Of String)("Soff") Equals grp.Field(Of String)("Soff")
              Select New With _
            { _
              Key .spart = div.Field(Of String)("Dv"), _
              .spart_t = div.Field(Of String)("Name")
            }
            End If


            Dim d_spart As New Dictionary(Of String, String)
            For Each v In query_spart
                d_spart.Item(v.spart) = v.spart & " " & v.spart_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim query_vkgrp
            If isInitital Then
                query_vkgrp = _
               From grp In salesgroup_filtered
               Select New With _
             { _
               Key .vkgrp = grp.Field(Of String)("SGrp"), _
               .vkgrp_t = grp.Field(Of String)("Description")
             }
            Else
                query_vkgrp = _
               From grp In salesgroup_filtered
               Join soff In salesoffice_filtered
               On grp.Field(Of String)("Soff") Equals soff.Field(Of String)("Soff")
               Select New With _
             { _
               Key .vkgrp = grp.Field(Of String)("SGrp"), _
               .vkgrp_t = grp.Field(Of String)("Description")
             }
            End If


            Dim d_vkgrp As New Dictionary(Of String, String)
            For Each v In query_vkgrp
                d_vkgrp.Item(v.vkgrp) = v.vkgrp & " " & v.vkgrp_t
            Next
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            'Create dropdowns -> fill GuiXT variables'
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkorgDropdown = ""
            If d_vkorg.Keys.Count > 1 Then
                vkorgDropdown = "= Please choose sales organization" & vbNewLine
            End If

            For Each v In d_vkorg.Keys
                vkorgDropdown &= v & "=" & d_vkorg.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkorg", vkorgDropdown)

            ' Select the value, of only one is available
            If d_vkorg.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkorg", d_vkorg.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vtwegDropdown = ""
            If d_vtweg.Keys.Count > 1 Then
                vtwegDropdown = "= Please choose distribution channel" & vbNewLine
            End If

            For Each v In d_vtweg.Keys
                vtwegDropdown &= v & "=" & d_vtweg.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vtweg", vtwegDropdown)

            If d_vtweg.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vtweg", d_vtweg.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim spartDropdown = ""
            If d_spart.Keys.Count > 1 Then
                spartDropdown = "= Please choose division" & vbNewLine
            End If

            For Each v In d_spart.Keys
                spartDropdown &= v & "=" & d_spart.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_spart", spartDropdown)

            If d_spart.Keys.Count = 1 Then
                myguixt.SetVariable("va01_spart", d_spart.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''



            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkburDropdown = ""
            If d_vkbur.Keys.Count > 1 Then
                vkburDropdown = "= Please choose sales office" & vbNewLine
            End If

            For Each v In d_vkbur.Keys
                vkburDropdown &= v & "=" & d_vkbur.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkbur", vkburDropdown)

            If d_vkbur.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkbur", d_vkbur.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''


            ''''''''''''''''''''''''''''''''''''''''''''''''
            Dim vkgrpDropdown = ""
            If d_vkgrp.Keys.Count > 1 Then
                vkgrpDropdown = "= Please choose sales group" & vbNewLine
            End If

            For Each v In d_vkgrp.Keys
                vkgrpDropdown &= v & "=" & d_vkgrp.Item(v) & vbNewLine
            Next
            myguixt.SetText("va_codes_vkgrp", vkgrpDropdown)

            If d_vkgrp.Keys.Count = 1 Then
                myguixt.SetVariable("va01_vkgrp", d_vkgrp.Keys(0))
            End If
            ''''''''''''''''''''''''''''''''''''''''''''''''

            ' Measured runtime
            sw.Stop()
            myguixt.SetVariable("build_dropdowns_runtime", sw.ElapsedMilliseconds.ToString)

        Catch ex As Exception
            Dim st As New StackTrace(True)
            st = New StackTrace(ex, True)
            errorMessage = ex.Message & " " & "Method: " _
                & System.Reflection.MethodInfo.GetCurrentMethod().Name() _
                & ", Line " & st.GetFrame(0).GetFileLineNumber().ToString

        End Try

        ' "" if no error occured
        Return errorMessage


    End Function


End Class