Imports IntraSell_DLL
Imports MySql.Data.MySqlClient

Module ModuleArchiv
    Dim LAST_YEAR As Date = Today.AddDays(-31 * 4) 'Quartal sollte bleiben

    Public Sub SetArchivStichtag(stichtag As Date)
        LAST_YEAR = stichtag
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function PruefeArchivierung() As String
        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand("select count(Typ+Nummer) as Cnt from `buchVorgang` WHERE Datum < @Stichtag")
        cmd.Parameters.AddWithValue("Stichtag", LAST_YEAR)
        Dim anzahlArchivieren = FirstRow(cmd)
        Dim msg As String = "Es gibt " & anzahlArchivieren & " Vorgänge, "

        cmd = New MySql.Data.MySqlClient.MySqlCommand("select count(Id) as Cnt from `grartikel-LagerBewegung` WHERE Datum < @Stichtag")
        cmd.Parameters.AddWithValue("Stichtag", LAST_YEAR)
        anzahlArchivieren = FirstRow(cmd)
        msg += vbNewLine & "Es gibt " & anzahlArchivieren & " Lagerbewegungen, "

        cmd = New MySql.Data.MySqlClient.MySqlCommand("select count(KBID) as Cnt from `buchKassabuch` WHERE Datum < @Stichtag")
        cmd.Parameters.AddWithValue("Stichtag", LAST_YEAR)
        anzahlArchivieren = FirstRow(cmd)
        msg += vbNewLine & "Es gibt " & anzahlArchivieren & " Kassabucheinträge, die ins Archiv verschoben werden können."

        Try
            cmd = New MySql.Data.MySqlClient.MySqlCommand("select count(Typ+Nummer) as Cnt from `buchVorgang-Archiv`")
            cmd.Parameters.AddWithValue("Stichtag", LAST_YEAR)
            anzahlArchivieren = FirstRow(cmd)
            msg += vbNewLine & vbNewLine & "Es gibt " & anzahlArchivieren & " Vorgänge im Archiv. "
        Catch ex As Exception
        End Try
        Return msg
    End Function

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub ArchiviereAlteRechnungen()
        Dim connString = My.Settings.intrasell_daten_2_ConnectionString
        Dim conn = NewMySQLConn(connString)
        conn.Open()
        ErstelleTabellenFuerArchivierung(conn)

        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand("select distinct date(Datum) as StichtagD from `buchVorgang` WHERE Datum < @Stichtag order by date(Datum)", conn)
        cmd.Parameters.AddWithValue("Stichtag", LAST_YEAR)
        Dim DataTable = OpenRecordsetInMemory(cmd)
        For Each r In DataTable.Rows
            ArchivierenStichtag(r("StichtagD"), conn)
        Next
        conn.Close()
    End Sub

    Public Sub DeArchiviereAlteRechnungen()
        Dim connString = My.Settings.intrasell_daten_2_ConnectionString
        Dim conn = NewMySQLConn(connString)
        conn.Open()
        ErstelleTabellenFuerArchivierung(conn)

        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand("select distinct date(Datum) as StichtagD from `buchVorgang-Archiv`", conn)
        Dim DataTable = OpenRecordsetInMemory(cmd)
        For Each r In DataTable.Rows
            DeArchiviereAlteRechnungenStichtag(r("StichtagD"), conn)
        Next
    End Sub

    Public Sub DeArchiviereAlteRechnungenStichtag(Stichtag As Date, conn As MySql.Data.MySqlClient.MySqlConnection)
        WriteLog("DeArchiviereAlteRechnungenStichtag " & Stichtag)

        Dim tr As MySqlTransaction = Nothing
        Try
            CurrentDB.Close()
            CurrentDB.Open()
            tr = CurrentDB.BeginTransaction()

            Dim cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchVorgang` select * from `buchVorgang-Archiv`  WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchVorgang-Artikel` select * from `buchVorgang-Artikel-Archiv` WHERE (Nummer,Typ) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchvorgaengeeigenschaften` select * from `buchvorgaengeeigenschaften-Archiv` WHERE (Nummer,VorgangTyp) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn) With {
                .Transaction = tr
            }
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchvorgaengeeigenschaften-Archiv`WHERE (Nummer,VorgangTyp) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn) With {
                .Transaction = tr
            }
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchVorgang-Artikel-Archiv` WHERE (Nummer,Typ) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn) With {
                .Transaction = tr
            }
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchVorgang-Archiv` WHERE date(Datum) = date(@Stichtag)", conn) With {
                .Transaction = tr
            }
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `grartikel-LagerBewegung` select * from `grartikel-LagerBewegung-Archiv` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `grartikel-LagerBewegung-Archiv` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchKassabuch` select * from `buchKassabuch-Archiv` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchKassabuch-Archiv` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            tr.Commit()
        Catch ex As Exception
            tr.Rollback()
            Throw ex
        End Try
    End Sub

    Private Sub ArchivierenStichtag(Stichtag As Date, conn As MySql.Data.MySqlClient.MySqlConnection)
        WriteLog("ArchivierenStichtag " & Stichtag)
        Dim tr As MySqlTransaction = Nothing
        Try
            tr = conn.BeginTransaction()
            Dim cmd As MySql.Data.MySqlClient.MySqlCommand
            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchVorgang-Artikel-Archiv` select * from `buchVorgang-Artikel` WHERE (Nummer,Typ) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchVorgang-Archiv` select * from `buchVorgang` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchvorgaengeeigenschaften-Archiv` select * from `buchvorgaengeeigenschaften`  WHERE (Nummer,VorgangTyp) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchvorgaengeeigenschaften` WHERE (Nummer,VorgangTyp) in (select Nummer, Typ from buchVorgang where date(Datum) = date(@Stichtag))", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchVorgang-Artikel` WHERE (Nummer,Typ) in (select Nummer, Typ from buchVorgang where Datum < @Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchVorgang` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `grartikel-LagerBewegung-Archiv` select * from `grartikel-LagerBewegung` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `grartikel-LagerBewegung` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO `buchKassabuch-Archiv` select * from `buchKassabuch` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            cmd = New MySql.Data.MySqlClient.MySqlCommand("DELETE FROM `buchKassabuch` WHERE date(Datum) = date(@Stichtag)", conn)
            cmd.Parameters.AddWithValue("Stichtag", Stichtag)
            cmd.Transaction = tr
            RunSQL(cmd)

            tr.Commit()
        Catch ex As Exception
            tr.Rollback()
            Throw ex
        End Try
    End Sub

    Private Sub ErstelleTabellenFuerArchivierung(conn As MySql.Data.MySqlClient.MySqlConnection)
        RunSQLWithConnection("CREATE table IF NOT EXISTS `buchVorgang-Archiv` as SELECT * FROM buchVorgang limit 0", conn)
        RunSQLWithConnection("CREATE table IF NOT EXISTS `buchVorgang-Artikel-Archiv` as SELECT * FROM `buchVorgang-Artikel` limit 0", conn)
        RunSQLWithConnection("CREATE table IF NOT EXISTS `buchvorgaengeeigenschaften-Archiv` as SELECT * FROM `buchvorgaengeeigenschaften` limit 0", conn)
        RunSQLWithConnection("CREATE table IF NOT EXISTS `grartikel-LagerBewegung-Archiv` as SELECT * FROM `grartikel-LagerBewegung` limit 0", conn)
        RunSQLWithConnection("CREATE table IF NOT EXISTS `buchKassabuch-Archiv` as SELECT * FROM `buchKassabuch` limit 0", conn)

        Try
            RunSQLWithConnection("ALTER TABLE `buchvorgang-archiv` ADD INDEX Index_Archiv1 (`Datum`)", conn)
        Catch ex As Exception
        End Try
        Try
            RunSQLWithConnection("ALTER TABLE `buchVorgang-Artikel-Archiv` ADD INDEX Index_Archiv2 (Typ, Nummer)", conn)
        Catch ex As Exception
        End Try
        Try
            RunSQLWithConnection("ALTER TABLE `buchvorgaengeeigenschaften-Archiv` ADD INDEX Index_Archiv3 (VorgangTyp, Nummer)", conn)
        Catch ex As Exception

        End Try
        Try
            RunSQLWithConnection("ALTER TABLE `grartikel-LagerBewegung-Archiv` ADD INDEX Index_Archiv4 (`Datum`)", conn)
        Catch ex As Exception
        End Try
        Try
            RunSQLWithConnection("ALTER TABLE `buchKassabuch-Archiv` ADD INDEX Index_Archiv5 (`Datum`)", conn)
        Catch ex As Exception
        End Try
    End Sub

    Function RunSQLWithConnection(sql As String, conn As MySql.Data.MySqlClient.MySqlConnection)
        Dim cmd = New MySql.Data.MySqlClient.MySqlCommand(sql, conn)
        Return RunSQL(cmd)
    End Function
End Module
