355 lines
15 KiB
VB.net
355 lines
15 KiB
VB.net
Imports System.IO
|
|
Imports DigitalData.Modules.Base
|
|
Imports DigitalData.Modules.Database
|
|
Imports DigitalData.Modules.Logging
|
|
Imports DigitalData.Modules.Messaging.Mail
|
|
Imports EmailProfiler.Common.ClassCurrent
|
|
Imports Limilabs.Mail
|
|
Imports Limilabs.Mail.Headers
|
|
|
|
Public Class clsWorker
|
|
Private ReadOnly _Logger As Logger
|
|
Private ReadOnly _Fetcher As MailFetcher
|
|
Private ReadOnly _Database As MSSQLServer
|
|
Private ReadOnly _UseWindream As Boolean = False
|
|
Private ReadOnly ClassWindreamAllgemein As clsWindream_allgemein
|
|
Private ReadOnly ClassWindreamIndex As clsWindream_Index
|
|
Private ReadOnly _ClassWorkMail As clsWorkEmail
|
|
Private ReadOnly Encryption As clsEncryption
|
|
Private ReadOnly _ProfileId As Integer = 0D
|
|
Private ReadOnly EmailLimitationSender As String = ""
|
|
Private ReadOnly EmailLimitationEnabled As Boolean = False
|
|
|
|
Private Const SUCCESS_IMAP_FOLDER = "Verarbeitet"
|
|
|
|
Private ReadOnly LocalEmlFile As String = ""
|
|
|
|
Sub New(pLogConfig As LogConfig, pConnectionString As String, pWindreamConnectionString As String, pPollProfileId As Integer, pConfigData As Config, Optional pLocalEML As String = "")
|
|
_Logger = pLogConfig.GetLogger
|
|
'ClassEmailImap = New clsEmailIMAP(pLogConfig)
|
|
_Fetcher = New MailFetcher(pLogConfig)
|
|
_Database = New MSSQLServer(pLogConfig, pConnectionString)
|
|
|
|
_UseWindream = pConfigData.UseWindream
|
|
EmailLimitationSender = pConfigData.EmailSenderLimitation
|
|
If EmailLimitationSender.Contains("@") Then
|
|
_Logger.Info("Email Sender Limitation active for address: [{0}]", EmailLimitationSender)
|
|
EmailLimitationEnabled = True
|
|
End If
|
|
|
|
If _UseWindream Then
|
|
ClassWindreamAllgemein = New clsWindream_allgemein(pLogConfig)
|
|
ClassWindreamIndex = New clsWindream_Index(pLogConfig)
|
|
End If
|
|
|
|
_ClassWorkMail = New clsWorkEmail(pLogConfig, pConnectionString, pWindreamConnectionString, pConfigData)
|
|
|
|
Encryption = New clsEncryption("!35452didalog=", pLogConfig)
|
|
_ProfileId = pPollProfileId
|
|
LocalEmlFile = pLocalEML
|
|
End Sub
|
|
|
|
Private Sub DeleteTempFiles()
|
|
For Each _file In TEMP_FILES
|
|
_Logger.Debug("Trying to delete temp file: [{0}]", _file)
|
|
|
|
If File.Exists(_file) Then
|
|
Try
|
|
File.Delete(_file)
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
_Logger.Warn("Could not delete the tempfile from TEMP_FILES: [{0}]", _file)
|
|
End Try
|
|
End If
|
|
Next
|
|
TEMP_FILES.Clear()
|
|
End Sub
|
|
|
|
Private Function LoadEmailAccounts() As DataTable
|
|
Return _Database.GetDatatable("SELECT * FROM TBDD_EMAIL_ACCOUNT WHERE ACTIVE = 1")
|
|
End Function
|
|
|
|
Private Function LoadPollingProfiles(pProfileId As Integer) As DataTable
|
|
Dim oSQL = "SELECT * FROM TBEMLP_POLL_PROFILES WHERE ACTIVE = 1"
|
|
|
|
If _ProfileId = 0 Then
|
|
oSQL &= " ORDER BY SEQUENCE"
|
|
Else
|
|
oSQL &= $" AND GUID = {pProfileId}"
|
|
End If
|
|
|
|
Return _Database.GetDatatable(oSQL)
|
|
End Function
|
|
|
|
Public Sub Start_WorkingProfiles(Optional LocalEmail As Boolean = False)
|
|
Try
|
|
DeleteTempFiles()
|
|
IS_LOCAL_TEST = LocalEmail
|
|
If _Database.DBInitialized = False Then
|
|
_Logger.Warn("Database is not initialized. Exiting.")
|
|
Exit Sub
|
|
End If
|
|
|
|
_Logger.Debug("now windream_init... ")
|
|
If _UseWindream Then
|
|
If ClassWindreamAllgemein.Init = False Then
|
|
_Logger.Info("windream could not be initialized!!")
|
|
Exit Sub
|
|
Else
|
|
_Logger.Debug("windream_initialized!")
|
|
End If
|
|
End If
|
|
|
|
Dim EmailAccountTable As DataTable = LoadEmailAccounts()
|
|
Dim PollingProfileTable = LoadPollingProfiles(_ProfileId)
|
|
|
|
If IsNothing(PollingProfileTable) Then
|
|
_Logger.Warn("Error while fetching Polling Profiles. Exiting.")
|
|
Exit Sub
|
|
End If
|
|
|
|
If PollingProfileTable.Rows.Count = 0 Then
|
|
_Logger.Warn("No active Polling Profiles found. Exiting.")
|
|
Exit Sub
|
|
End If
|
|
|
|
_Logger.Debug("Count of active profiles: " & PollingProfileTable.Rows.Count.ToString)
|
|
|
|
For Each oProfile As DataRow In PollingProfileTable.Rows
|
|
|
|
Dim oValidationSql = oProfile.ItemEx("VALIDATION_SQL", "")
|
|
|
|
CURRENT_PROFILE_GUID = oProfile.Item("GUID")
|
|
DT_POLL_PROCESS = Nothing
|
|
Dim sql = String.Format("SELECT * FROM TBEMLP_POLL_PROCESS WHERE PROFILE_ID = {0} AND ACTIVE = 1", CURRENT_PROFILE_GUID)
|
|
DT_POLL_PROCESS = _Database.GetDatatable(sql)
|
|
If Not IsNothing(DT_POLL_PROCESS) Then
|
|
If DT_POLL_PROCESS.Rows.Count = 0 Then
|
|
_Logger.Info("No processes configured for this Email-Profile - " & sql)
|
|
Continue For
|
|
Else
|
|
DT_STEPS = Nothing
|
|
DT_STEPS = _Database.GetDatatable($"SELECT T.* FROM TBEMLP_POLL_STEPS T,TBEMLP_POLL_PROCESS T1 WHERE T.PROCESS_ID = T1.GUID AND T1.PROFILE_ID = {CURRENT_PROFILE_GUID} AND T1.ACTIVE = 1")
|
|
|
|
End If
|
|
Else
|
|
_Logger.Warn("DT_POLL_PROCESS is nothing")
|
|
Continue For
|
|
End If
|
|
|
|
CURRENT_EMAIL_GUID = 0
|
|
|
|
CURRENT_POLL_TYPE = oProfile.Item("POLL_TYPE")
|
|
_Logger.Debug(String.Format("Working on profile: ({0}-{1}-{2}) ", oProfile.Item("GUID"), oProfile.Item("PROFILE_NAME"), CURRENT_POLL_TYPE))
|
|
CURRENT_EMAIL_GUID = oProfile.Item("EMAIL_CONF_ID")
|
|
|
|
Dim FilteredRows As List(Of DataRow) = EmailAccountTable.
|
|
Select($"GUID = {CURRENT_EMAIL_GUID}").
|
|
ToList()
|
|
_Logger.Debug("FilteredRows: " & FilteredRows.Count)
|
|
|
|
If FilteredRows.Count = 1 Then
|
|
Dim oRow = FilteredRows(0)
|
|
Dim oMailFrom = oRow("EMAIL_FROM")
|
|
Dim oMailServer = oRow("EMAIL_SMTP")
|
|
Dim oMailUser = oRow("EMAIL_USER")
|
|
Dim oMailPassword = oRow("EMAIL_PW")
|
|
Dim oMailPortIn = oRow("PORT_IN")
|
|
Dim oMailboxName = "Inbox"
|
|
Dim oMailArchiveFolder = oRow("ARCHIVE_FOLDER")
|
|
Dim oMailAuthType = oRow("AUTH_TYPE")
|
|
|
|
_Logger.Debug("Mail Server: {0}", oMailServer)
|
|
_Logger.Debug("Mail From: {0}", oMailFrom)
|
|
|
|
Dim PWPlain = Encryption.DecryptData(oMailPassword)
|
|
If Not IsNothing(PWPlain) Then
|
|
If PWPlain <> "" Then
|
|
oMailPassword = PWPlain
|
|
Else
|
|
_Logger.Warn("PWPlain is string.empty - Could not decrypt passwort")
|
|
End If
|
|
Else
|
|
_Logger.Warn("PWPlain is nothing - Could not decrypt passwort")
|
|
End If
|
|
|
|
CURRENT_WORKMAIL_UID_LIST.Clear()
|
|
|
|
If oMailServer <> "" Then
|
|
Dim oPollResult As Boolean = False
|
|
If LocalEmail = True Then
|
|
oPollResult = True
|
|
Else
|
|
Select Case CURRENT_POLL_TYPE
|
|
Case "IMAP"
|
|
' We are using 'Archive Folder' as an additional field to save the Tenant-ID for O365-OAuth2
|
|
oPollResult = FetchMessages(oMailServer, oMailPortIn, oMailUser, oMailPassword, oMailAuthType, oMailArchiveFolder)
|
|
|
|
Case Else
|
|
_Logger.Error("Poll Type [{0}] is not supported!", CURRENT_POLL_TYPE)
|
|
oPollResult = False
|
|
End Select
|
|
End If
|
|
|
|
If CURRENT_WORKMAIL_UID_LIST.Count() > 0 Or LocalEmail = True Then
|
|
If LocalEmail Then
|
|
_Logger.Info("Working with local Mail")
|
|
Dim oEmail As IMail = New MailBuilder().CreateFromEmlFile(LocalEmlFile)
|
|
|
|
Dim oUID = String.Concat(Now.Month.ToString, Now.Day, Now.Hour, Now.Minute, Now.Second)
|
|
_ClassWorkMail.WorkEmailMessage(oEmail, oUID, oValidationSql)
|
|
Else
|
|
Try
|
|
_Logger.Info(String.Format("Pulled: [{0}] E-Mails", CURRENT_WORKMAIL_UID_LIST.Count()))
|
|
|
|
For Each oMailId In CURRENT_WORKMAIL_UID_LIST
|
|
Dim oEmail As IMail = _Fetcher.FetchMail(oMailId)
|
|
|
|
If Not IsNothing(oEmail) Then
|
|
If EmailLimitationEnabled Then
|
|
Dim oEmailFrom As String = ""
|
|
For Each m As MailBox In oEmail.From
|
|
oEmailFrom = m.Address
|
|
Next
|
|
If oEmailFrom <> EmailLimitationSender Then
|
|
_Logger.Debug($"Skipping email {oEmailFrom} ...Subject [{oEmail.Subject}]")
|
|
Continue For
|
|
End If
|
|
End If
|
|
' Hier wird die einzelne EMail jetzt verarbeitet
|
|
If _ClassWorkMail.WorkEmailMessage(oEmail, oMailId, oValidationSql) = True Then
|
|
If LocalEmlFile = "" Then
|
|
DeleteOrMoveEmailFile(oMailId)
|
|
End If
|
|
End If
|
|
|
|
Else
|
|
_Logger.Info("### oEmail was nothing ###")
|
|
End If
|
|
Next
|
|
|
|
If CURRENT_POLL_TYPE = "IMAP" And CURRENT_WORKMAIL_UID_LIST.Count > 0 Then
|
|
_Fetcher.Disconnect()
|
|
End If
|
|
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
_Logger.Warn($"Unexpected Error working CURRENT_WORKMAIL_UID_LIST: {ex.Message} ")
|
|
End Try
|
|
|
|
End If
|
|
Else
|
|
_Logger.Debug(String.Format("No emails for profile!"))
|
|
End If
|
|
|
|
Else
|
|
_Logger.Warn("For the Email-Profile ID " & CURRENT_EMAIL_GUID & " no record could be found!")
|
|
End If
|
|
|
|
_Database.ExecuteNonQuery("UPDATE TBEMLP_POLL_PROFILES SET LAST_TICK = GETDATE() WHERE GUID = " & oProfile.Item("GUID").ToString)
|
|
Else
|
|
_Logger.Warn("For the Email-Profile ID " & CURRENT_EMAIL_GUID & " no record could be found! Check wether Email-Profile is active!")
|
|
End If
|
|
Next
|
|
|
|
DeleteTempFiles()
|
|
|
|
_Database.ExecuteNonQuery("UPDATE TBEMLP_CONFIG SET LAST_TICK = GETDATE() WHERE GUID = 1")
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
End Try
|
|
End Sub
|
|
|
|
Public Function FetchMessages(pServer As String, pPort As Integer, pUsername As String, pPassword As String, pAuthType As String, pArchiveFolder As String) As Boolean
|
|
Try
|
|
Dim oSession As MailSession.SessionInfo
|
|
|
|
If pAuthType = MailSession.AUTH_OAUTH2 Then
|
|
Dim oClientId As String = pServer
|
|
Dim oClientSecret As String = pPassword
|
|
Dim oTenantId As String = pArchiveFolder
|
|
oSession = _Fetcher.ConnectToO365(pUsername, oClientId, oTenantId, oClientSecret)
|
|
Else
|
|
oSession = _Fetcher.Connect(pServer, pPort, pUsername, pPassword, pAuthType, New MailSession.MailSessionOptions() With {
|
|
.EnableTls1_1 = True,
|
|
.EnableTls1_2 = True
|
|
})
|
|
End If
|
|
|
|
|
|
If oSession.Connected = False AndAlso oSession.Error IsNot Nothing Then
|
|
_Logger.Warn("Connection to Mail Server failed!")
|
|
_Logger.Error(oSession.Error)
|
|
Return False
|
|
|
|
ElseIf oSession.Connected = False Then
|
|
_Logger.Warn("Connection to Mail Server failed!")
|
|
Return False
|
|
End If
|
|
|
|
CURRENT_ImapObject = _Fetcher.Client
|
|
Dim oMailIds As List(Of Long) = _Fetcher.ListAllMails()
|
|
|
|
If oMailIds Is Nothing Then
|
|
_Logger.Warn("List of UIDs was Nothing. Exiting.")
|
|
Return False
|
|
End If
|
|
|
|
If oMailIds.Count = 0 Then
|
|
_Logger.Debug("No Emails found.")
|
|
Return True
|
|
End If
|
|
|
|
CURRENT_WORKMAIL_UID_LIST = oMailIds
|
|
|
|
Return True
|
|
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
Return False
|
|
|
|
End Try
|
|
End Function
|
|
Private Sub DeleteOrMoveEmailFile(pMailId As Integer)
|
|
Try
|
|
If MESSAGE_ERROR = True Then
|
|
_Logger.Warn("Did not delete or move Message with UID [{0}] as there was an MessageError!", pMailId)
|
|
Return
|
|
End If
|
|
|
|
If IsNothing(_Fetcher.Client) Then
|
|
_Logger.Warn("Did not delete or move Message with UID [{0}] as ImapClient is null", pMailId)
|
|
Return
|
|
End If
|
|
|
|
If DeleteMail = True Then
|
|
_Fetcher.Client.DeleteMessageByUID(pMailId)
|
|
_Logger.Info("Email with Id [{0}] was deleted.", pMailId)
|
|
Else
|
|
If TestImapFolderExists(SUCCESS_IMAP_FOLDER) Then
|
|
If _Fetcher.Client.MoveByUID(pMailId, SUCCESS_IMAP_FOLDER) IsNot Nothing Then
|
|
_Logger.Info("Email with UID [{0}] was moved", pMailId)
|
|
End If
|
|
Else
|
|
_Logger.Warn("IMAP Folder [{0}] does not exist. Emails could not be moved!", SUCCESS_IMAP_FOLDER)
|
|
End If
|
|
End If
|
|
Catch ex As Exception
|
|
_Logger.Error(ex)
|
|
End Try
|
|
End Sub
|
|
|
|
Private Function TestImapFolderExists(pFolderName As String) As Boolean
|
|
Try
|
|
Return _Fetcher.Client.
|
|
GetFolders().
|
|
Where(Function(f) f.Name = pFolderName).
|
|
Any()
|
|
Catch ex As Exception
|
|
_Logger.Warn("Could not get IMAP folders. Returning False.")
|
|
_Logger.Error(ex)
|
|
Return False
|
|
End Try
|
|
End Function
|
|
End Class
|