Imports IntraSell_DLL
Imports IntraSell_Net_Base
Imports IntraSell_Net_Base.dsAdressen
Public Class Kundenliste
    Inherits AbstractForm
    Implements InterfaceExportableGrid

    Dim JahrBeginn As New Date(Now.Year, 1, 1)

    Private Sub Form_Verlassen() Handles MyBase.Verlassen
        Me.Close()
    End Sub

    Private Sub KundenListe_Load(sender As Object, e As System.EventArgs) Handles MyBase.Load
        Try

            'BindingNavigatorDeleteItem.Enabled = False

            'abstract form
            ds = DsAdressen
            ParentBindingNavigator.BindingSource = OfAdressenlisteBindingSource
            'end abstract form 


            KundengruppenTableAdapter.Connection = CurrentDB

            FillComboBox(NichtStatusComboBox, "select Status from `ofAdressen-Status` group by Status order by Status", "Status", "Status")
            Try
                NichtStatusComboBox.SelectedValue = "Gesperrt"
            Catch ex As Exception
            End Try

            FillComboBox(StatusComboBox, "select Status from `ofAdressen-Status` group by Status order by Status", "Status", "Status")
            FillComboBox(MandantComboBox, "SELECT IDNR, Firma FROM Mandant ORDER By Firma", "Firma", "IDNR")
            'TODO: Mandant in der Spalte als ComboBox umsetzen
            'FillComboBoxInDG(Me.DataGridViewComboBoxColumnMandant, "SELECT IDNR, Firma FROM Mandant ORDER By Firma", "Firma", "IDNR")
            GrlandTableAdapter.Fill(DsPLZ.grland)
            FillComboBox(PreislisteComboBox, SQL_PREISLISTEN, "PreislisteName")

            KundengruppenTableAdapter.Fill(DsAdressen.Kundengruppen)
            GrbranchenTableAdapter.Fill(DsBranchen.grbranchen)

            LandComboBox.Text = ""
            KundengruppeComboBox.Text = ""
            PreislisteComboBox.Text = ""
            BrancheComboBox.Text = ""
            LoadData()

            Dim fU = New FormUtility
            fU.MakeSplitterCustomizable(SplitContainer, "KUNDENLISTE_SPLITTER_SIZE")

            SichbtbareSpaltenConfig()
        Catch ex As Exception
            HandleAppError(ex, , Silent:=True)
        End Try
    End Sub

    Private Sub AddNewKunde(sender As Object, e As System.EventArgs) Handles MyBase.AddNew
        Main.NeuerKundeToolStripMenuItem_Click(Nothing, Nothing)
    End Sub

    Private Sub ParentBindingNavigatorSaveItem_Click(sender As Object, e As System.EventArgs) Handles MyBase.Save
        Try
            If Not HasRecht(Rechte.Kunden, True) Then
                Exit Sub
            End If

            Validate()
            OfAdressenlisteBindingSource.EndEdit()
            OfAdressenlisteTableAdapter.Update(DsAdressen)
            InvalidateCache()
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub

    Sub LoadData()
        OfAdressenlisteTableAdapter.Connection = CurrentDB
        DsAdressen.EnforceConstraints = False
        OfAdressenlisteTableAdapter.Fill(DsAdressen.ofAdressenliste)
        If DsAdressen.ofAdressenliste.Rows.Count < 1000 Then 'Sehr langsam
            DsAdressen.EnforceConstraints = True
        End If

    End Sub

    ' Sub ReLoadData()
    '     OfAdressenlisteTableAdapter.Connection = CurrentDB
    '     Me.DsAdressen.ofAdressenliste.Clear()
    '     Me.OfAdressenlisteTableAdapter.Fill(Me.DsAdressen.ofAdressenliste)
    ' End Sub

    'Filtern
    Private Sub FilterButton_Click(sender As Object, e As System.EventArgs) Handles FilterButton.Click
        Try
            Cursor = Cursors.WaitCursor
            LoadData()
            Dim filter As String = "1=1 "

            If txtIDNR.Text.Length > 0 AndAlso IsNumeric(txtIDNR.Text) Then
                filter += " and (IDNR = " & txtIDNR.Text & ")"
            End If

            If txtFirmaName.Text.Length > 0 Then
                filter += " and (Firma Like '%" & txtFirmaName.Text & "%' or Name Like '%" & txtFirmaName.Text & "%' or Vorname Like '%" & txtFirmaName.Text & "%')"
            End If

            If LandComboBox.Text.Length > 0 Then
                filter += " and Land Like '" & LandComboBox.Text & "%'"
            End If

            If PLZVonTextBox.Text.Length > 0 Then
                filter += " and PLZ >= '" & PLZVonTextBox.Text & "'"
            End If

            If PLZBisTextBox.Text.Length > 0 Then
                filter += " and PLZ <= '" & PLZBisTextBox.Text & "'"
            End If

            If OrtTextBox.Text.Length > 0 Then
                filter += " and Ort Like '%" & OrtTextBox.Text & "%'"
            End If

            If KundengruppeComboBox.Text.Length > 0 Then
                filter += " and Kundengruppe Like '" & KundengruppeComboBox.Text & "%'"
            End If

            If PreislisteComboBox.Text.Length > 0 Then
                filter += " and Preisliste Like '" & PreislisteComboBox.Text & "%'"
            End If

            If BrancheComboBox.Text.Length > 0 Then
                filter += " and Branche Like '" & BrancheComboBox.Text & "%'"
            End If

            If StatusComboBox.Text.Length > 0 Then
                filter += " and Status Like '" & StatusComboBox.Text & "%'"
            End If

            If NichtStatusComboBox.Text.Length > 0 Then
                filter += " and (Status is null or Status Not Like '" & NichtStatusComboBox.Text & "%')"
            End If


            If TextBoxEmail.Text.Length > 0 Then
                filter += " and ( Mobil Like '%" & TextBoxEmail.Text & "%' or Tel Like '%" & TextBoxEmail.Text & "%' or Email Like '%" & TextBoxEmail.Text & "%')"
            End If

            If MandantComboBox.Text.Length > 0 Then
                filter = filter & " and Mandantnr = '" & MandantComboBox.SelectedValue & "'"
            End If

            If TextBoxUmsatzAb.Text.Length > 0 Then
                filter += " and Umsatz >=  " & TextBoxUmsatzAb.Text
            End If

            If filter IsNot Nothing Then
                OfAdressenlisteBindingSource.Filter = filter
            Else
                OfAdressenlisteBindingSource.Filter = Nothing
            End If

        Catch ex As Exception
            HandleAppError(ex)
        Finally
            Cursor = Cursors.Default
        End Try
    End Sub

    'KundenDetail öffnen 
    Private Sub OfAdressenlisteDataGridView_RowHeaderMouseDoubleClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles OfAdressenlisteDataGridView.RowHeaderMouseDoubleClick
        OeffneKundenMaske(OfAdressenlisteDataGridView.SelectedRows(0).Cells(0).Value, True)
    End Sub

    Private Sub BindingSource_CurrentChanged(sender As Object, _
                             e As System.EventArgs) _
                             Handles OfAdressenlisteBindingSource.CurrentChanged
        Try
            If OfAdressenlisteDataGridView.SelectedRows.Count > 0 Then
                Dim IDNR As Object = OfAdressenlisteDataGridView.SelectedRows(0).Cells(0).Value
                If IsNumeric(IDNR) Then KundenIDNR = IDNR
            End If
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub


#Region "Anrufen"


    Private Sub NeuerAnrufToolStripMenuItem_Click(sender As Object, e As System.EventArgs) Handles NeuerAnrufToolStripMenuItem.Click
        Try
            If OfAdressenlisteDataGridView.SelectedRows.Count <= 0 Then MsgBox("Bitte eine Zeile auswählen!") : Exit Sub
            Dim IDNR As Integer = OfAdressenlisteDataGridView.SelectedRows(0).Cells(0).Value
            Dim a As New Anruf With {
                .MdiParent = MdiParent
            }
            a.Show()
            'Anruf.FilterBy("AdrNR=" & )
            a.StarteAnruf(IDNR)
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub

    Private Sub AnrufprotokollToolStripMenuItem_Click(sender As Object, e As System.EventArgs) Handles AnrufprotokollToolStripMenuItem.Click
        Try
            If OfAdressenlisteDataGridView.SelectedRows.Count <= 0 Then MsgBox("Bitte eine Zeile auswählen!") : Exit Sub
            Dim IDNR As Integer = OfAdressenlisteDataGridView.SelectedRows(0).Cells(0).Value
            OeffneAnrufliste(IDNR)
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub
#End Region

    Private Sub txtIDNR_Validated(sender As Object, e As EventArgs) Handles txtIDNR.Validated
        Try
            If IsNumeric(txtIDNR) Then
                FilterButton_Click(Nothing, Nothing)
            End If
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub

    Private Sub txtFirmaName_TextChanged(sender As Object, e As System.EventArgs) Handles txtFirmaName.TextChanged
        If OfAdressenlisteDataGridView.RowCount < 1000 Then
            FilterButton_Click(Nothing, Nothing)
        End If
    End Sub


    Private Sub txtFirmaName_Validated(sender As Object, e As EventArgs) Handles txtFirmaName.Validated
        If txtFirmaName.Text.Length = 0 Then
            Exit Sub
        End If
        FilterButton_Click(Nothing, Nothing)
    End Sub

    Private Sub PLZVonTextBox_TextChanged(sender As Object, e As System.EventArgs) Handles PLZVonTextBox.TextChanged
        If OfAdressenlisteDataGridView.RowCount < 1000 Then
            PLZBisTextBox.Text = PLZVonTextBox.Text
        End If
    End Sub

    Private Sub SeriendruckToolStripMenuItem_Click(sender As Object, e As System.EventArgs) Handles SeriendruckToolStripMenuItem.Click
        SeriendruckvorlageBearbeitenToolStripMenuItem_Click(Nothing, Nothing)

        'OfAdressenlisteBindingSource.MoveFirst()
        'While True
        '    Dim adress As dsAdressen.ofAdressenlisteRow = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow)
        '    'Dim IDNR As Integer = OfAdressenlisteDataGridView.SelectedRows(0).Cells(0).Value
        '    OfAdressenlisteBindingSource.MoveNext()
        '    If adress.IDNR = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow).IDNR Then Exit While
        'End While

    End Sub

    Public Sub Export(sender As Object) Implements InterfaceExportableGrid.Export
        Exportieren(Nothing)
    End Sub

    Private Sub ExportierenToolStripMenuItem_Click(sender As Object, e As System.EventArgs) Handles ExportierenToolStripMenuItem.Click
        Exportieren(GetAppPath() & "\tmp\Seriendruck.csv")
    End Sub

    Private Sub SeriendruckvorlageBearbeitenToolStripMenuItem_Click(sender As Object, e As System.EventArgs) Handles SeriendruckvorlageBearbeitenToolStripMenuItem.Click
        Dim vorlage As String = GetAppPath() & "\Vorlagen\Vorlage_Mailing.dot"
        EditInWord(vorlage)
    End Sub

    Sub Exportieren(filename As String)
        Try
            Cursor = Cursors.WaitCursor
            Dim e As New FunctionsDataGrid
            Dim a As New ofAdressenlisteDataTable
            Dim row As dsAdressen.ofAdressenlisteRow

            OfAdressenlisteBindingSource.MoveFirst()
            While True
                row = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow)
                a.ImportRow(row)
                OfAdressenlisteBindingSource.MoveNext()
                If row.IDNR = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow).IDNR Then Exit While
            End While

            e.Table = a
            e.ExportDataGridContentToCSVFile(filename)

        Catch ex As Exception
            HandleAppError(ex)
        Finally
            Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub TextBoxEmail_TextChanged(sender As Object, e As EventArgs) Handles TextBoxEmail.TextChanged
        If OfAdressenlisteDataGridView.RowCount < 1000 Then
            FilterButton_Click(Nothing, Nothing)
        End If
    End Sub

    ''' <summary>
    ''' Send SMS / Email to the list 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub SMSEmailToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SMSEmailToolStripMenuItem.Click
        Try
            Dim a As New ofAdressenlisteDataTable
            Dim row As dsAdressen.ofAdressenlisteRow

            OfAdressenlisteBindingSource.MoveFirst()
            While True
                row = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow)
                a.ImportRow(row)
                OfAdressenlisteBindingSource.MoveNext()
                If row.IDNR = CType(CType(OfAdressenlisteBindingSource.Current, DataRowView).Row, dsAdressen.ofAdressenlisteRow).IDNR Then Exit While
            End While

            WerbeNachricht.Kundenliste = a
            WerbeNachricht.MdiParent = MdiParent
            WerbeNachricht.Show()

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

    Private Sub DuplikateEntfernenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DuplikateEntfernenToolStripMenuItem.Click
        Try
            DuplicateEntfernen()
        Catch ex As Exception
            HandleAppError(ex)
        End Try
    End Sub

    '''  Private Sub OfAdressenlisteDataGridView_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles OfAdressenlisteDataGridView.DataError
    '''
    '''  End Sub
    ''' 
    Private Sub UmsatzBerechnenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UmsatzBerechnenToolStripMenuItem.Click
        Try
            KundenUmsatzBerechnen.ShowDialog()
            'Cursor = Cursors.WaitCursor
            'ModuleKunden.BerechneKundenUmsatz()
            'MsgBox("Umsatz wurde erfolgreich berechnet!")
            FilterButton_Click(Nothing, Nothing)
        Catch ex As Exception
            HandleAppError(ex)
        Finally
            Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub txtIDNR_KeyDown(sender As Object, e As KeyEventArgs) Handles txtIDNR.KeyDown

    End Sub

    Private Sub NichtAusgedrucktCheckBox_KeyDown(sender As Object, e As KeyEventArgs) Handles _
    StatusComboBox.KeyDown, _
    LandComboBox.KeyDown, _
    PLZVonTextBox.KeyDown, _
    PLZBisTextBox.KeyDown, _
    MandantComboBox.KeyDown, _
    txtIDNR.KeyDown, _
    txtFirmaName.KeyDown, _
    LandComboBox.KeyDown, _
    KundengruppeComboBox.KeyDown, _
    PreislisteComboBox.KeyDown, _
    BrancheComboBox.KeyDown, _
    NichtStatusComboBox.KeyDown, _
    TextBoxEmail.KeyDown
        If e.KeyCode = Keys.Enter Then
            FilterButton_Click(Nothing, Nothing)
        End If
    End Sub

    Private Sub OfAdressenlisteDataGridView_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles OfAdressenlisteDataGridView.CellFormatting
        Try
            If e.ColumnIndex = 1 Then
                Dim r As dsAdressen.ofAdressenlisteRow = OfAdressenlisteDataGridView.Rows(e.RowIndex).DataBoundItem.row
                If Not r.IsStatusNull Then
                    Dim status = r.Status
                    If IsDBNull(status) Then Exit Sub
                    If status.ToLower = "kunde" Then
                        e.CellStyle.BackColor = Color.Green
                    ElseIf status.ToLower = "anonym" Then
                        e.CellStyle.BackColor = Color.GreenYellow
                    ElseIf status.ToLower = "gesperrt" Then
                        e.CellStyle.BackColor = Color.Gray
                    End If
                End If
            End If
        Catch ex As Exception
            ' HandleAppError(ex, Silent:=True)
        End Try
    End Sub

    Private Sub SichbtbareSpaltenConfig()
        Try

            ColumnIDNR.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_IDNR_SICHTBAR, True)
            ColumnAdresse.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_ADRESSE_SICHTBAR, True)
            ColumnBranche.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_BRANCHE_SICHTBAR, False)
            ColumnEmail.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_EMAIL_SICHTBAR, True)
            ColumnFirma.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_FIRMA_SICHTBAR, True)
            ColumnName.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_NAME_SICHTBAR, True)
            ColumnVorname.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_VORNAME_SICHTBAR, True)
            ColumnKundengruppe.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_KUNDENGRUPPE_SICHTBAR, False)
            ColumnLand.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_LAND_SICHTBAR, False)
            ColumnMandant.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_MANDANT_SICHTBAR, False)
            ColumnMobil.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_MOBIL_SICHTBAR, False)
            ColumnOrt.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_ORT_SICHTBAR, True)
            ColumnPLZ.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_PLZ_SICHTBAR, True)
            ColumnPreisliste.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_PREISLISTE_SICHTBAR, False)
            ColumnStatus.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_STATUS_SICHTBAR, False)
            ColumnTel.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_TEL_SICHTBAR, True)
            ColumnUmsatz.Visible = VarValue_Default(Variablen.KUNDENLISTE_SPALTE_UMSATZ_SICHTBAR, True)

        Catch ex As Exception
            HandleAppError(ex, Silent:=True)
        End Try
    End Sub

    Private Sub BtnSpalten_Click(sender As Object, e As EventArgs) Handles BtnSpalten.Click
        Dim v As New Vars
        v.Show()
        v.Filter("KUNDENLISTE_SPALTE")
        AddHandler v.FormClosed, AddressOf Vars_FormClosed
    End Sub

    Private Sub Vars_FormClosed(sender As Object, e As FormClosedEventArgs)
        SichbtbareSpaltenConfig()
    End Sub
End Class