Imports IntraSell_DLL
Imports IntraSell_Net_Base
Imports MySql.Data.MySqlClient

Module ModuleKunden

    Dim MessageZustellpreiseEinmalGezeigt As Boolean = False

    ''' <summary>
    ''' Suche wenn mindest 2 Zeichen 
    ''' </summary>
    ''' <param name="mobileNummer"></param>
    ''' <returns>List of IDNR</returns>
    Public Function KundeSuchen(mobileNummer As String) As List(Of Integer)

        If Len(mobileNummer) > 2 Then
            mobileNummer = CleanUpTelNrFuerSuche(mobileNummer)
            mobileNummer = "%" & mobileNummer & "%"
            'Adresse nach TelNr od. Email finden 
            Dim sql As String = "SELECT max(IDNR) as IDNR, name, vorname, adresse from ofAdressen " &
                                "WHERE Tel like @Txt or Mobil like @Txt or Email like @Txt or Vorname like @Txt or Name like @Txt or Firma Like @Txt or IDNR Like @Txt " &
                                "GROUP BY name, vorname, adresse"
            Dim cmd As New MySqlCommand(sql)
            cmd.Parameters.AddWithValue("Txt", mobileNummer)
            Dim res = OpenRecordsetInMemory(cmd)
            If res.Rows.Count >= 1 Then 'Found 
                Dim l As New List(Of Integer)
                For Each r In res.Rows
                    l.Add(r("IDNR"))
                Next
                Return l 'res.Rows(0)("IDNR")
            Else
                Return Nothing
            End If
        End If
        Return Nothing

    End Function

    Public Function CleanUpTelNrFuerSuche(ByVal mobileNummer As String) As String
        If mobileNummer.StartsWith("0043") Then
            mobileNummer = mobileNummer.Substring(4, mobileNummer.Length - 4)
        End If

        If mobileNummer.StartsWith("+43") Then
            mobileNummer = mobileNummer.Substring(3, mobileNummer.Length - 3)
        End If

        If mobileNummer.StartsWith("43") Then
            mobileNummer = mobileNummer.Substring(2, mobileNummer.Length - 2)
        End If

        While mobileNummer.StartsWith("0")
            mobileNummer = mobileNummer.Substring(1, mobileNummer.Length - 1)
        End While
        Return mobileNummer
    End Function

    Public Function KundeLand(idnr As Integer)
        Dim cmd As New MySqlCommand With {
            .CommandText = "select land from ofAdressen where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)
        Return FirstRow(cmd)
    End Function

    Public Function KundeNamen(idnr As Integer) As String
        Dim cmd As New MySqlCommand With {
            .CommandText = "select trim(concat(COALESCE(Firma,''), ' ', COALESCE(Name,''), ' ' , COALESCE(Vorname,''))) as Namen from ofAdressen  where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)

        Dim namen = FirstRow(cmd)
        If IsDBNull(namen) Then
            Return Nothing
        Else
            Return CStr(namen)
        End If
    End Function

    Public Function KundeAdresse(idnr As Integer)
        Dim cmd As New MySqlCommand With {
            .CommandText = "select Adresse from ofAdressen where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)
        Return FirstRow(cmd)
    End Function

    Public Function KundeEmail(idnr As Integer) As String
        Dim cmd As New MySqlCommand With {
            .CommandText = "select Email from ofAdressen where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)

        Dim email = FirstRow(cmd)
        If IsDBNull(email) Then
            Return Nothing
        Else
            Return CStr(email)
        End If
    End Function

    Public Function KundeUID(idnr As Integer) As String
        Dim cmd As New MySqlCommand With {
            .CommandText = "select concat(UID, ' / ', ifnull(date(UIDGeprueftAm), 'nicht geprueft')) from ofAdressen where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)

        Dim feld = FirstRow(cmd)
        If IsDBNull(feld) Then
            Return Nothing
        Else
            Return CStr(feld)
        End If
    End Function

    Public Function KundePreisliste(idnr As Integer)
        Dim cmd As New MySqlCommand With {
            .CommandText = "select Preisliste from `ofAdressen-Settings` where idnr=@idnr"
        }
        cmd.Parameters.AddWithValue("idnr", idnr)
        Return FirstRow(cmd)
    End Function


    ''' <summary>
    ''' Duplikate in der Tabelle ofAdressen entfernen
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub DuplicateEntfernen()
        WriteLog("DuplicateEntfernen START")
        Dim sql As String = "select min(idnr) firstIDNR, max(idnr) lastIDNR, vorname, name, adresse, plz, tel, mobil, email from ofadressen  group by vorname, name, adresse, plz, tel, mobil, email having count(*) > 1"
        Dim cmd As New MySqlCommand(sql)
        Dim rs = OpenRecordsetInMemory(cmd)
        For Each r As DataRow In rs.Rows

            Dim firstIDNR = r("FirstIDNR")
            Dim lastIDNR = r("LastIDNR")
            WriteLog("Entferne Duplikat IDNR " & lastIDNR & " ersetze mit " & firstIDNR)
            RunSQL("update buchVorgaenge set kundnr=" & firstIDNR & " where kundnr=" & lastIDNR)
            RunSQL("update buchVorgang set kundnr=" & firstIDNR & " where kundnr=" & lastIDNR)
            RunSQL("update buchVorgang set kundnr1=" & firstIDNR & " where kundnr1=" & lastIDNR)
            RunSQL("update buchVorgang set kundnr2=" & firstIDNR & " where kundnr2=" & lastIDNR)

            RunSQL("update `ofadressen-profil` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)
            RunSQL("update `ofadressen-seriendruck` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)
            'RunSQL("update `ofadressen-kundengruppen` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)

            'TODO - was ist wenn die firstIDNR bereits existiert ? 
            RunSQL("update `ofadressen-settings` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)
            RunSQL("update `ofadressen-weitere` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)

            RunSQL("update `ofadressenbanken` set kundennr=" & firstIDNR & " where kundennr=" & lastIDNR)
            RunSQL("update `ofanrufe` set adrnr=" & firstIDNR & " where adrnr=" & lastIDNR)
            RunSQL("update `ofkorespondenz` set idnr=" & firstIDNR & " where idnr=" & lastIDNR)
            'RunSQL("delete from ofadressen where idnr=" & lastIDNR)
        Next
        WriteLog("DuplicateEntfernen DONE")
    End Sub

    Public Function CalculateUmsatzKundeNetto(IDNR As Long, Optional DatumVon As Date = Nothing, Optional DatumBis As Date = Nothing) As Decimal
        If IsNull(IDNR) OrElse IDNR < 0 Then Return 0
        If DatumVon = Nothing Then DatumVon = Now.AddDays(-365 * 7)
        If DatumBis = Nothing Then DatumBis = Now
        Dim sql
        sql = "SELECT  Sum(Preis_Netto*stk) AS UmsatzKunde" & _
        " FROM buchVorgang LEFT JOIN `buchVorgang-Artikel` ON buchVorgang.Nummer = `buchVorgang-Artikel`.Nummer and buchVorgang.Typ = `buchVorgang-Artikel`.Typ " & _
        " WHERE buchVorgang.Typ = 'AR' and buchVorgang.KundNr = @IDNR and Datum >= @DatumVon and Datum <= @DatumBis"
        Dim cmd = New MySqlCommand(sql)
        cmd.Parameters.AddWithValue("IDNR", IDNR)
        cmd.Parameters.AddWithValue("DatumVon", DatumVon)
        cmd.Parameters.AddWithValue("DatumBis", DatumBis)
        Dim umsatz = FirstRow(cmd)
        If IsNumeric(umsatz) Then
            Return CDbl(umsatz)
        End If
        Return 0
    End Function

    Public Function AnzahlKundenVorgaenge(IDNR As Long, Typ As String, abgeschlossen As Boolean) As Integer
        If IsNull(IDNR) OrElse IDNR < 0 Then Return 0
        Dim sqlc As String = "SELECT count(Nummer) as cnt " & _
        " FROM buchVorgang v" & _
        " WHERE  v.KundNr = @IDNR and v.Typ=@Typ and abgeschlossen=@abgeschlossen"
        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand(sqlc)
        cmd.Parameters.AddWithValue("IDNR", IDNR)
        cmd.Parameters.AddWithValue("Typ", Typ)
        cmd.Parameters.AddWithValue("abgeschlossen", abgeschlossen)
        Dim umsatz = FirstRow(cmd)
        If IsNumeric(umsatz) Then
            Return umsatz
        End If
        Return 0
    End Function

    Public Function AnzahlKundenPreise(IDNR As Long) As Decimal
        If IsNull(IDNR) OrElse IDNR < 0 Then Return 0
        Dim sqlc As String = "SELECT count(IDNR) as cnt " & _
        " FROM `grartikel-vkpreisperselection` v" & _
        " WHERE  v.IDNR = @IDNR"
        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand(sqlc)
        cmd.Parameters.AddWithValue("IDNR", IDNR)
        Dim umsatz = FirstRow(cmd)
        If IsNumeric(umsatz) Then
            Return CDbl(umsatz)
        End If
        Return 0
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="KundenNr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNextIDNR(KundenNr As Integer) As Integer
        Dim nextIDNR = FirstRow("select nextIDNR from ofAdressen where IDNR=" & KundenNr)
        If nextIDNR Is Nothing OrElse IsDBNull(nextIDNR) Then
            Return -1
        Else
            Return nextIDNR
        End If
    End Function


    Sub StandardKundengruppeAnlegen()
        Dim sql = "INSERT  IGNORE  INTO  `ofadressen-kundengruppen` 
(`KGR`, `Gruppe`, `KundenNrKreisVon`, `KundenNrKreisBis`, `VorgangNrKreisVon`, `VorgangNrKreisBis`, `RechungenMitMWST`, `MandantNr`)
 VALUES ('1', 'Kunden', '1', '100000', '1', '100000', '1', '0') "
        RunSQL(sql)
    End Sub



    Function KundenLoeschen(idnr As Integer)
        Return RunSQL("delete from ofadressen where idnr=" & idnr)
    End Function

    '' Mitgliedstaaten der Europäischen Union (EU)	Ländercode
    '' Belgien	BE
    '' Bulgarien	BG
    '' Dänemark	DK
    '' Deutschland	DE
    '' Estland	EE
    '' Finnland	FI
    '' Frankreich	FR
    '' Griechenland	GR (Eurostat: EL)
    '' Irland	IE
    '' Italien	IT
    '' Kroatien	HR
    '' Lettland	LV
    '' Litauen	LT
    '' Luxem­burg	LU
    '' Malta	MT
    '' Nieder­lande	NL
    '' Öster­reich	AT
    '' Polen	PL
    '' Portugal	PT
    '' Rumänien	RO
    '' Schweden	SE
    '' Slowakei	SK
    '' Slowenien	SI
    '' Spanien	ES
    '' Tschechien	CZ
    '' Ungarn	HU
    '' Zypern	CY
    '' Europäische Union	EU
    '' Mitgliedstaaten der Europäischen Freihandelszone (EFTA)	Ländercode
    '' Island	IS
    '' Liechtenstein	LI
    '' Norwegen	NO
    '' Schweiz	CH
    '' Europäische Freihandelszone (European Free Trade Association)	EFTA
    '' EU-Beitrittskandidaten (offizieller Status)	Ländercode
    '' Albanien	AL
    '' Montenegro	ME
    '' Bosnien und Herzegowina	BA
    '' Moldau, Republik	MD
    '' Nordmazedonien	MK
    '' Serbien	RS
    '' Türkei	TR
    '' Ukraine	UA
    '' Weitere europäische Staaten	Ländercode
    '' Andorra	AD
    '' Belarus	BY
    '' Monaco	MC
    '' Russische Föderation	RU
    '' San Marino	SM
    '' Vatikanstadt	VA
    '' Vereinigtes Königreich	GB (Eurostat: UK)
    Public Function IstEULand(iso As String)
        Dim AlleEUISOs = "BE,BG,DK,DE,EE,FI,FR,GR,IE,IT,HR,LV,LT,LU,MT,NL,AT,PL,PT,RO,SE,SK,SI,ES,CZ,HU,CY,IS,LI,NO,CH,"
        Dim Kandidaten = "AL,ME,BA,MD,MK,RS,TR,UA,"
        Dim WeitereLänder = "AD,BY,MC,RU,SM,VA,GB"
        Return AlleEUISOs.IndexOf(iso) >= 0
    End Function

    'Umsatz je Kunde berechnen
    Public Sub BerechneKundenUmsatz(Optional abWann As Date = Nothing, Optional bisWann As Date = Nothing)

        If abWann = Nothing Then
            abWann = Today.AddYears(-7)
        End If
        If bisWann = Nothing Then
            bisWann = Today
        End If

        Dim scriptFilename = "SQL\20240101_vKundenumsatz.sql"
        Dim script = $"
DROP TABLE IF EXISTS vKundenUmsatzAktuellesJahr;

CREATE TABLE vKundenUmsatzAktuellesJahr AS
SELECT KundNr, SUM(summe) AS Umsatz, COUNT(Nummer) AS AnzahlRechnungen
       FROM  buchVorgang
       WHERE Typ = 'AR' and Datum >= @DatumAb and Datum < @DatumBis
       GROUP BY KundNr;
                                                              
ALTER TABLE `vKundenUmsatzAktuellesJahr`
	ADD INDEX `KundNr` (`KundNr`);"
        script = script.Replace("@DatumAb", "'" & Format(abWann, "yyyy-MM-dd") & "'")
        script = script.Replace("@DatumBis", "'" & Format(bisWann, "yyyy-MM-dd") & "'")

        IO.File.WriteAllText(scriptFilename, script)
        Dim db As New ClassDBUpgrade
        Dim result1 = ""
        db.ProcessSQLFile(scriptFilename, result:=result1)

    End Sub

End Module
