Imports MySql.Data.MySqlClient
Imports System.Net.Mail

Public Class mail_sending

    Dim connStr As String = "server=localhost;user=root;database=dvag_mailing;port=3306;password=;charset=utf8;"
    Dim connstr_dvag As String = "server=localhost;user=root;database=dvag;port=3306;password=;charset=utf8;"
    Dim conn As New MySqlConnection(connStr)
    Dim conn_dvag As New MySqlConnection(connstr_dvag)
    Dim sqlcmd As New MySqlCommand
    Dim dr
    Dim dw




    Private Sub bt_start_Click(sender As Object, e As EventArgs) Handles bt_start.Click


        System.Windows.Forms.Application.DoEvents()

        'ID einlesen
        Try
            conn.Open()
            sqlcmd = New MySqlCommand("SELECT id FROM data where send = 0 and mailing_ohne_freigabe = 1", conn)
            dr = sqlcmd.ExecuteScalar()
            lb_id.Text = dr
            conn.Close()
            rtb_log.Text = rtb_log.Text & DateTime.Now & ": ID " & lb_id.Text & " erfolgreich eingelesen" & vbNewLine
            rtb_log.ScrollToCaret()
        Catch ex As System.Exception
            MsgBox(ex.ToString)
        End Try



        If lb_id.Text = "" Then
            rtb_log.Text = rtb_log.Text & DateTime.Now & ": Keine Mailings zum verarbeiten gefunden" & vbNewLine
        Else
            'von einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `from` FROM data where ID = " & lb_id.Text & "", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_von.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Mail von erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try

            'an einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `to` FROM data where ID = " & lb_id.Text & "", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_an.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Mail an erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try


            'Betreff einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `subject` FROM data where ID = " & lb_id.Text & "", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_betreff.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Betreff erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try

            'text einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `text` FROM data where ID = " & lb_id.Text & "", conn)
                dr = sqlcmd.ExecuteScalar()
                rtb_mail_body.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Mail Body erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try



            'AK einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `gruppe` FROM `zugriff` where sender_mail = '" & tb_an.Text & "'", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_ak_gruppe.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Gruppe erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try

            'sender_mail einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `sender_mail` FROM `zugriff` where sender_mail = '" & tb_an.Text & "'", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_sender_mail.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Sender Mail Adresse erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try

            'sender_name einlesen
            Try
                conn.Open()
                sqlcmd = New MySqlCommand("SELECT `sender_name` FROM `zugriff` where sender_mail = '" & tb_an.Text & "'", conn)
                dr = sqlcmd.ExecuteScalar()
                tb_sender_name.Text = dr
                conn.Close()
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Sender Name erfolgreich eingelesen" & vbNewLine
                rtb_log.ScrollToCaret()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try


            If tb_ak_gruppe.Text = "" Then

                ' Mail an geschaeftsstelle








                'MsgBox("Keine Empfänger definiert!")
                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Keine Empfänger definiert!" & vbNewLine
                Exit Sub
            Else

                conn_dvag.open()
                conn.open()

                'Regionalforum
                If InStr(tb_ak_gruppe.Text, "Forum") Then
                    sqlcmd = New MySqlCommand("SELECT `email` FROM `tl_member` where xt_regioforum or xt_moreregio like '%" & tb_ak_gruppe.Text & "%' and DISABLE = ''", conn_dvag)

                    'Arbeitskreis
                ElseIf instr(tb_ak_gruppe.Text, "AK") Then
                    sqlcmd = New MySqlCommand("SELECT `email` FROM `tl_member` where xt_arbeitskre like '%" & tb_ak_gruppe.Text & "%' and DISABLE = ''", conn_dvag)

                    'Funktionaere
                ElseIf instr(tb_ak_gruppe.Text, "Funktionäre") Then
                    sqlcmd = New MySqlCommand("SELECT `email` FROM `funktionaere`", conn)

                    'Vorstand
                ElseIf instr(tb_ak_gruppe.Text, "Vorstand") Then
                    sqlcmd = New MySqlCommand("SELECT `email` FROM `vorstand`", conn)

                    'Mitglieder
                ElseIf instr(tb_ak_gruppe.Text, "Mitglieder") Then
                    sqlcmd = New MySqlCommand("select email from newsletter union select email from dvag.tl.member where disable = ''", conn)

                    'Test
                ElseIf instr(tb_ak_gruppe.Text, "TEST") Then
                    sqlcmd = New MySqlCommand("SELECT `email` FROM `test`", conn)


                Else
                    ' MsgBox("nichts gefunden")


                End If

                rtb_log.Text = rtb_log.Text & DateTime.Now & ": Empfänger SQL gesetzt" & vbNewLine
                'MsgBox(sqlcmd.ExecuteNonQuery)
                rtb_log.ScrollToCaret()
                Try



                    Dim DataAdapter As New MySqlDataAdapter
                    DataAdapter.SelectCommand = sqlcmd

                    Dim chemicalInventory As New DataTable()
                    DataAdapter.Fill(chemicalInventory)

                    'MsgBox(sqlcmd.ExecuteScalar)

                    For Each row As DataRow In chemicalInventory.Rows

                        dr = (row.Item("email"))
                        rtb_empfaengergruppe.Text = rtb_empfaengergruppe.Text & dr & vbNewLine
                    Next



                    Dim last = rtb_empfaengergruppe.Text.LastIndexOf("")
                    If last > 0 Then

                        rtb_empfaengergruppe.Text = rtb_empfaengergruppe.Text.Substring(0, last + 1)
                        Dim beforelast = rtb_empfaengergruppe.Text.LastIndexOf("")
                        rtb_empfaengergruppe.Text = rtb_empfaengergruppe.Text.Substring(0, beforelast + 0)

                    Else

                        'rtb_empfaengergruppe.Text = ""

                    End If



                    conn.Close()
                    rtb_log.Text = rtb_log.Text & DateTime.Now & ": Empfänger erfolgreich eingelesen" & vbNewLine
                    rtb_log.ScrollToCaret()
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try

            End If


            If rtb_empfaengergruppe.Text = "" Then rtb_log.Text = rtb_log.Text & DateTime.Now & ": Es wurde keine Mail versendet" & vbNewLine





        End If



        Dim tmp As New System.Text.StringBuilder
        For Each line As String In rtb_empfaengergruppe.Lines
            If line.Trim.Length > 0 Then tmp.AppendLine(line)
        Next
        rtb_empfaengergruppe.Text = tmp.ToString


        System.Windows.Forms.Application.DoEvents()
        'save_log()
        mail_versenden()
        'save_log()
        update_sql_gesendete_mails()
        'save_log()
        felder_leeren()
        'save_log()



    End Sub


    Public Sub mail_versenden()
        System.Windows.Forms.Application.DoEvents()
        If rtb_empfaengergruppe.Text = "" Then Exit Sub


        Dim Msg As New MailMessage
        Dim myCredentials As New System.Net.NetworkCredential
        myCredentials.UserName = "m.haase"
        myCredentials.Password = "Start2013!"
        Msg.IsBodyHtml = True
        Dim mySmtpsvr As New SmtpClient()
        mySmtpsvr.Host = "remote.it-professional.biz" 'bei web.de
        mySmtpsvr.Port = 27
        mySmtpsvr.EnableSsl = 0
        mySmtpsvr.UseDefaultCredentials = False
        mySmtpsvr.Credentials = myCredentials
        Try
            'Msg.From = New MailAddress(tb_sender_mail.Text)
            Msg.From = New MailAddress(tb_sender_mail.Text, tb_sender_name.Text)
            Dim empfaenger As String = rtb_empfaengergruppe.Text
            ' MsgBox(empfaenger)
            'MsgBox(empfaenger.Replace(vbLf, ", "))
            empfaenger = empfaenger.Replace(vbLf, ", ")
            'MsgBox(empfaenger)
            empfaenger = (empfaenger.Substring(0, empfaenger.Length - 2))
            'MsgBox(empfaenger)
            Msg.Bcc.Add(empfaenger)

            Msg.Subject = tb_betreff.Text
            Msg.Body = rtb_mail_body.Text


            Try


                For Each item In My.Computer.FileSystem.GetFiles("C:\DVAG\Mailing\" & lb_id.Text, FileIO.SearchOption.SearchTopLevelOnly, "*.*")
                    'MsgBox(item)
                    Dim myAttch = Msg.Attachments
                    myAttch.Add(New System.Net.Mail.Attachment(item))
                Next

            Catch ex As System.Exception
                MsgBox(Err.Number & ex.Message & ex.StackTrace.ToString)
            End Try





            mySmtpsvr.Send(Msg)
            rtb_log.Text = rtb_log.Text & DateTime.Now & ": E-Mail gesendet." & vbNewLine
            rtb_log.Text = rtb_log.Text & DateTime.Now & ": Auftrag für die ID " & lb_id.Text & " erfolgreich abgeschlossen." & vbNewLine
        Catch ex As Exception
            MsgBox(Err.Number & ex.Message & ex.StackTrace.ToString) 'Falls ein Fehler auftritt wird eine MsgBox angezeigt
            Exit Sub
        End Try


    End Sub



    Public Sub felder_leeren()
        System.Windows.Forms.Application.DoEvents()
        rtb_empfaengergruppe.Clear()
        tb_ak_gruppe.Clear()
        tb_sender_mail.Clear()
        tb_von.Clear()
        tb_an.Clear()
        tb_betreff.Clear()
        rtb_mail_body.Clear()
        lb_id.Text = ""
        rtb_log.Text = ""

        mail_auslesen.Show()
        If My.Settings.mail_auslesen = "1" Then mail_auslesen.Timer_mail_auslesen.Start()

        Me.Hide()

    End Sub

    Public Sub update_sql_gesendete_mails()
        System.Windows.Forms.Application.DoEvents()
        Try
            conn.Open()
            ' MsgBox("ok")
            sqlcmd = New MySqlCommand("update data set `send` = '1', `verarbeitet_am` = '" & DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") & "' where ID = '" & lb_id.Text & "'", conn)
            dr = sqlcmd.ExecuteScalar()

            conn.Close()
            rtb_log.Text = rtb_log.Text & DateTime.Now & ": Vorgang als abgeschlossen in der DB markiert" & vbNewLine
            rtb_log.ScrollToCaret()
        Catch ex As System.Exception
            MsgBox(ex.ToString)
        End Try
    End Sub


    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        System.Windows.Forms.Application.DoEvents()
        Dashboard.Show()
        Me.Hide()
    End Sub



    Public Sub save_log()
        System.Windows.Forms.Application.DoEvents()
        rtb_log.SaveFile("c:\DVAG\" & DateTime.Now = ("HH__dd_MM_yyyy") & "mail_log.txt", RichTextBoxStreamType.PlainText)
    End Sub


End Class