Imports System.IO
Imports System.Text
Imports IntraSell_DLL
Imports IntraSell_Net_Base
Imports IntraSell_Net_Starter
Imports MySql.Data.MySqlClient

Public Class FunctionsPLZ
    Dim LandComboBox As ComboBox, PLZComboBox As ComboBox, OrtComboBox As ComboBox, AdresseTextBox As ComboBox
    Dim landComboboxloading
    Dim lastLand


    Sub Init(LandComboBox As ComboBox, PLZComboBox As ComboBox, OrtComboBox As ComboBox, AdresseTextBox As ComboBox)
        Me.LandComboBox = LandComboBox
        Me.PLZComboBox = PLZComboBox
        Me.OrtComboBox = OrtComboBox
        Me.AdresseTextBox = AdresseTextBox

        PLZComboBox.DropDownWidth = PLZComboBox.Width * 3
        OrtComboBox.DropDownWidth = OrtComboBox.Width * 2

        'Disable Auto Complete - problem mit Tabellen, AC drop down wird nicht komplett gezeigt
        'PLZComboBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        'OrtComboBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend

        PLZComboBox.AutoCompleteMode = AutoCompleteMode.None
        OrtComboBox.AutoCompleteMode = AutoCompleteMode.None

        AddHandler LandComboBox.SelectedValueChanged, AddressOf LandComboBox_SelectedValueChanged
        AddHandler PLZComboBox.Validating, AddressOf PLZComboBox_Validating
        AddHandler PLZComboBox.KeyUp, AddressOf PLZComboBox_KeyUp
        AddHandler OrtComboBox.Validating, AddressOf OrtComboBox_Validating
        AddHandler AdresseTextBox.DropDownClosed, AddressOf AdresseTextBox_SelectedValueChanged
        AddHandler AdresseTextBox.SelectedIndexChanged, AddressOf AdresseTextBox_SelectedIndexChanged
        AddHandler AdresseTextBox.KeyUp, AddressOf AdresseTextBox_KeyUp

        AddHandler ModuleGlobal.CommandRequestEvent, AddressOf CommandRequestEvent_StmmdatenGeaendertCommand
    End Sub

    Private Sub CommandRequestEvent_StmmdatenGeaendertCommand(cmd As IntraSellCommand)
        If TypeOf (cmd) Is IntraSellStammdatenGeaendertCommand Then
            FillPLZOrt(LandComboBox.SelectedValue)
        End If
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks></remarks>
    Sub FillLand()
        Try
            landComboboxloading = True
            FillComboBox(LandComboBox, "select idnr, name from grLand where name <> '' order by Name", "name", "idnr")
            landComboboxloading = False
            LandComboBox.SelectedValue = FirstRow("select idnr from grLand where ISO2='AT'")
        Catch ex As Exception
            HandleAppError(ex, Silent:=True)
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub LandComboBox_SelectedValueChanged(sender As Object, e As EventArgs)
        If landComboboxloading Then Exit Sub
        If IsNumeric(LandComboBox.SelectedValue) Then
            If lastLand <> LandComboBox.SelectedValue Then
                FillPLZOrt(LandComboBox.SelectedValue)
                lastLand = LandComboBox.SelectedValue
            End If
        End If
    End Sub

    Shared Sub FillPLZ(PLZComboBox As ComboBox, Land As Integer)
        FillComboBox(PLZComboBox, "select distinct PLZ from grPLZ where Land=" & Land & " and PLZ <> '' group by PLZ order by PLZ", "PLZ", "PLZ")
    End Sub

    Shared Sub FillOrt(OrtComboBox As ComboBox, Land As Integer)
        FillComboBox(OrtComboBox, "select distinct Ort from grPLZ where Land=" & Land & " and PLZ <> '' group by Ort order by Ort", "Ort", "Ort")
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="Land"></param>
    ''' <remarks></remarks>
    Sub FillPLZOrt(Land As Integer)
        Try
            Dim oldPLZ = PLZComboBox.Text
            'FillComboBox(LandComboBox, "select idnr, name from grLand where name <> '' order by Name", "name", "idnr")
            FillPLZ(PLZComboBox, Land)
            FillOrt(OrtComboBox, Land)
            Dim strSQL As String = "select distinct strasse from grStrassen where Zustellung <> 0 order by Strasse"
            If oldPLZ <> "" Then
                strSQL = "select distinct strasse from grStrassen where PLZ= '" & oldPLZ & "' and Zustellung <> 0 order by Strasse"
            End If
            FillComboBox(AdresseTextBox, strSQL, "strasse", "strasse")
        Catch ex As Exception
            HandleAppError(ex, Silent:=True)
        End Try
    End Sub

    Private Sub PLZComboBox_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs)
        Debug.Print("PLZComboBox_Validating" + PLZComboBox.Text)
        Try
            If OrtComboBox.Text = "" Then
                'aus PLZ holen 
                Dim Land = LandComboBox.SelectedValue
                Dim Ort = FirstRow("select Ort from grPLZ where Land=" & Land & " and PLZ = '" & PLZComboBox.Text & "'")
                If Ort IsNot Nothing Then
                    OrtComboBox.Text = Ort
                End If
            End If
        Catch ex As Exception
            HandleAppError(ex, Silent:=True)
        End Try
    End Sub

    Private Sub OrtComboBox_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs)
        Try
            Dim oldLand = LandComboBox.Text
            Dim oldStrasse = AdresseTextBox.Text
            Dim oldPLZ = PLZComboBox.Text
            Dim oldOrt = OrtComboBox.Text

            If oldPLZ = "" Or oldOrt = "" Then
                Exit Sub
            End If
            'Enter new PLZ + Ort if not Existing 
            Dim existValue = IntraSellKunden.GetPLZ(LandComboBox.SelectedValue, OrtComboBox.Text, oldPLZ)
            If existValue IsNot Nothing Then
                'Me.PLZComboBox.SelectedValue = existValue
                Exit Sub
            End If

            IntraSellKunden.GetPLZMitPrompt(LandComboBox.SelectedValue, oldOrt, oldPLZ, PromptNeuePLZ:=True)
            existValue = IntraSellKunden.GetPLZ(LandComboBox.SelectedValue, OrtComboBox.Text, oldPLZ)
            'Refresh Combo and reset Text
            If existValue IsNot Nothing Then
                FillPLZOrt(LandComboBox.SelectedValue)
                If oldLand IsNot Nothing Then
                    LandComboBox.Text = oldLand
                End If
                PLZComboBox.Text = oldPLZ
                If existValue IsNot Nothing Then
                    PLZComboBox.Text = oldPLZ
                End If
                If oldOrt IsNot Nothing Then
                    OrtComboBox.Text = oldOrt
                End If
                If oldStrasse IsNot Nothing Then
                    AdresseTextBox.Text = oldStrasse
                End If
            End If

        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub

    Private Sub AdresseTextBox_SelectedValueChanged(sender As Object, e As EventArgs)
        'Neue Strasse ausgewählt 
        Try
            Dim strasse = AdresseTextBox.Text
            If strasse = "" Then
                Exit Sub
            End If
            Dim sql As String = "select s.PLZ, p.Ort from grStrassen s, grPLZ p where s.PLZ=p.PLZ and Strasse = @Strasse"
            Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(sql)
            cmd.Parameters.AddWithValue("Strasse", strasse)
            Dim res = OpenRecordsetInMemory(cmd)
            If res.Rows.Count > 0 Then
                If PLZComboBox.Text = "" Then
                    PLZComboBox.Text = res.Rows(0)("PLZ")
                End If
                If OrtComboBox.Text = "" Then
                    OrtComboBox.Text = res.Rows(0)("Ort")
                End If
            End If
        Catch ex As Exception
            HandleAppError(ex, "Vorgng.Bezeichnung_AfterUpdate", Silent:=True)
        End Try
    End Sub

    Private Sub AdresseTextBox_SelectedIndexChanged(sender As Object, e As EventArgs)
        If AdresseTextBox.SelectedIndex > 0 Then
            AdresseTextBox_SelectedValueChanged(Nothing, Nothing)
        End If
    End Sub

    Private Sub AdresseTextBox_KeyUp(sender As Object, e As KeyEventArgs)
        Dim strasse = AdresseTextBox.Text
        If AdresseTextBox.Text.Length > 5 And strasse <> "Syste.Data.DataRowView" Then
            AdresseTextBox_SelectedValueChanged(Nothing, Nothing)
        End If
    End Sub

    Private Sub PLZComboBox_KeyUp(sender As Object, e As KeyEventArgs) 'Handles ComboBox1.KeyUp
        If e.KeyCode = Keys.Enter Then
            OrtComboBox.Focus()
        End If
    End Sub

    ''' <summary>
    ''' Strassen von Statistik Austria Herunterladen
    ''' </summary>
    ''' <param name="mPLZ"></param>
    ''' <param name="cnt"></param>
    ''' <returns></returns>
    Public Shared Function DownloadPLZ(mPLZ As String, ByRef cnt As Integer) As Boolean
        'ClassUpdate.DownloadFile("http://www.statistik.at/verzeichnis/strassenliste/gemplzstr_1.csv", "gemplzstr_1.csv")
        'Wien http://www.statistik.at/verzeichnis/strassenliste/gemplzstr_9.csv
        Dim File = "gemplzstr_9.csv"

        If mPLZ.StartsWith("7") Then File = "gemplzstr_1.csv"
        If mPLZ.StartsWith("9") Then File = "gemplzstr_2.csv"
        If mPLZ.StartsWith("2") Then File = "gemplzstr_3.csv"
        If mPLZ.StartsWith("3") Then File = "gemplzstr_3.csv"
        If mPLZ.StartsWith("4") Then File = "gemplzstr_4.csv"
        If mPLZ.StartsWith("5") Then File = "gemplzstr_5.csv"
        If mPLZ.StartsWith("6") Then File = "gemplzstr_8.csv"
        If mPLZ.StartsWith("8") Then File = "gemplzstr_6.csv"
        If mPLZ.StartsWith("9") Then File = "gemplzstr_2.csv"
        If mPLZ.StartsWith("1") Then File = "gemplzstr_9.csv"

        If Not IO.File.Exists(File) Then
            ClassUpdate.DownloadFile("http://www.statistik.at/verzeichnis/strassenliste/" & File, File)
        End If

        If IO.File.Exists(File) Then

            Using reader As New StreamReader(File, Encoding.UTF8)
                'Dim reader = IO.File.OpenText(file)
                Dim line As String = Nothing
                Dim lines As Integer = 0

                While (reader.Peek() <> -1)
                    lines += 1
                    line = reader.ReadLine()
                    Dim s = line.Split(";")
                    If s.Length > 6 Then
                        'Dim Gemeinde As String = s(1)
                        Dim Ort = s(3)
                        Dim Strasse = s(5)
                        Dim PLZ = s(7)
                        If PLZ = mPLZ Then
                            Try
                                Dim cmd = New MySqlCommand("Insert into grStrassen(Ort, PLZ, Strasse, Zustellung) values (@Ort, @PLZ, @Strasse, 1) On duplicate Key Update Ort=@Ort, Strasse=@Strasse")
                                cmd.Parameters.AddWithValue("Ort", Ort)
                                cmd.Parameters.AddWithValue("PLZ", PLZ)
                                cmd.Parameters.AddWithValue("Strasse", Strasse)
                                RunSQL(cmd)
                                Dim Land As Nullable(Of Integer) = 43
                                IntraSellKunden.GetPLZCreateIfNeeded(Land, Ort, PLZ)
                                cnt += 1
                            Catch ex As Exception
                                'ignore duplicate key 
                                Debug.Print(ex.Message)
                                HandleAppError(ex, Silent:=True)
                            End Try

                        End If
                    End If
                End While
            End Using

            ModuleGlobal.RequestCommandDirekt(New IntraSellStammdatenGeaendertCommand())
        Else
            Return False
        End If
        Return True
    End Function

End Class
