Hi there,
In my post history you might be able to see that i have been trying different approaches to getting OpenKM working better with emails sourced from outlook/exchange.
My latest attempt has been to bypass the REST API altogether and to just covert an interop.outlook.mailitem into a custom object which can be loaded directly into the MySQL okmdb database. The code for this, in vb.net, is as follows:
I am, however, having a problem getting the email to load outwith OpenKM, depending on the browser I am using. Firefox = No. Chrome & Safari = Yes. I coded the above by reverse engineering the okmdb. I was hoping that someone might be able to direct me to the relevant part of the openkm-dev code that handles the display of these emails, and even better the part that serves them up to be downloaded. I presume that a new .eml file is built from scratch from the data stored in the okmdb, and I would like to review that code, but just cannot find it.
Many thanks,
In my post history you might be able to see that i have been trying different approaches to getting OpenKM working better with emails sourced from outlook/exchange.
My latest attempt has been to bypass the REST API altogether and to just covert an interop.outlook.mailitem into a custom object which can be loaded directly into the MySQL okmdb database. The code for this, in vb.net, is as follows:
Code: Select all
This seems to work fine, and the email does appear in the folder where it is supposed to be, and displays properly.Imports System.Windows.Forms
Imports com.openkm.sdk4csharp
Imports Microsoft.Office.Interop
Imports MySql.Data.MySqlClient
Public Class Email
Private Property CACHE_root_folder As bean.Folder
Private Property CACHE_user_folder As bean.Folder
Private internal_name As String
Public ReadOnly Property name As String
Get
Return internal_name
End Get
End Property
Private Property internal_uuid As String
Public ReadOnly Property uuid As String
Get
Return internal_uuid
End Get
End Property
Public Property author As String
Public ReadOnly Property context As String
Get
Return ultimate_parent(parent_uuid)
End Get
End Property
Public Property created As DateTime
Public Property parent_uuid As String
Public Property content As String
Public Property from As email_contact
Public Property mimetype As String
Public Property received As DateTime
Public Property sent As DateTime
Public Property size As Integer
Public Property subject As String
Public Property [to] As List(Of email_contact)
Public Property [bcc] As List(Of email_contact)
Public Property [cc] As List(Of email_contact)
'Public Property reply As String ' no idea what this does.
Public Property path As String
Public Sub New()
End Sub
Public Sub New(outlook_email As Microsoft.Office.Interop.Outlook.MailItem, folder_to_use As Fast_Folder)
internal_name = Guid.NewGuid().ToString + "-" + outlook_email.Subject
internal_uuid = Guid.NewGuid().ToString
author = Authenticator.load_sql_settings.username
created = System.DateTime.Now
parent_uuid = folder_to_use.uuid
Select Case outlook_email.BodyFormat
Case Microsoft.Office.Interop.Outlook.OlBodyFormat.olFormatHTML
content = outlook_email.HTMLBody
mimetype = "text/html"
Case Microsoft.Office.Interop.Outlook.OlBodyFormat.olFormatPlain
content = CleanText(outlook_email.Body)
mimetype = "text/plain"
End Select
from = Get_Sender_Details(outlook_email)
received = outlook_email.ReceivedTime
sent = outlook_email.SentOn
size = outlook_email.Size
subject = outlook_email.Subject
[to] = Get_All_Recipent_Details(outlook_email)
'TODO bcc and cc as per [to]
path = folder_to_use.path & "/" & name
End Sub
Private Function ultimate_parent(destination_folder_uuid As String) As String
CACHE_root_folder = Authenticator.webservice_logon(Authenticator.load_sql_settings).getRootFolder()
CACHE_user_folder = Authenticator.webservice_logon(Authenticator.load_sql_settings).getPersonalFolder()
' We are then going to build a list of UUIDs, in reverse order.
Dim folder_genealogy As New List(Of String)
' We will need to track the current folder we are processing - which starts with the destination folder.
Dim current_folder_uuid As String = destination_folder_uuid
' We will be grabbing the parent uuid for the current folder.
Dim parent_folder_uuid As String
' And once we reach the bottom of the tree, we need to raise a flag.
Dim rock_bottom As Boolean = False
' The first entry in the chain is the destination.
folder_genealogy.Add(destination_folder_uuid)
While rock_bottom = False
parent_folder_uuid = returns_parent_uuid(current_folder_uuid)
' if no parent uuid is returned then we have hit rock bottom.
If parent_folder_uuid = "" Then
rock_bottom = True
Else
' We add the parent UUID, and we then update the current folder with the parent uuid.
folder_genealogy.Add(parent_folder_uuid)
current_folder_uuid = parent_folder_uuid
End If
End While
' Reverse the order of the chain, so that root is first and destination last.
folder_genealogy.Reverse()
' Once we have our chain, we can start to do things with it.
' find out what the root is
Select Case folder_genealogy(0)
Case CACHE_root_folder.uuid
Return "okm_root"
Case CACHE_user_folder.uuid
Return "okm_personal"
Case Else
Dim error_message As New Magenta.Message("Rock bottom not found to be user's base or public base.", "Folder Open Problem", "error")
Return Nothing
End Select
End Function
Private Function returns_parent_uuid(child_folder_uuid As String) As String
If child_folder_uuid = CACHE_root_folder.uuid Or child_folder_uuid = CACHE_user_folder.uuid Then
Return ""
Else
Using internalsubMySqlConnection As New MySqlConnection(Authenticator.openkm_direct_sql)
Using internalsubMySqlCommand As New MySqlCommand("SELECT `NBS_PARENT` FROM `okmdb`.`okm_node_base` WHERE `NBS_UUID` = @child_folder_uuid", internalsubMySqlConnection)
Try
'Open the connection
internalsubMySqlConnection.Open()
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@child_folder_uuid", child_folder_uuid)
'Send the command and process the reply
Using internalsubDataReader As MySqlDataReader = internalsubMySqlCommand.ExecuteReader()
If internalsubDataReader.HasRows Then
While internalsubDataReader.Read()
Return internalsubDataReader("NBS_PARENT").ToString()
End While
End If
End Using
Catch somesortofSQLerror As MySqlException
MessageBox.Show("Cannot connect To database: " & somesortofSQLerror.Message)
If internalsubMySqlConnection.State = ConnectionState.Open Then internalsubMySqlConnection.Close()
End Try
End Using 'internalsubMySqlCommand
End Using 'internalsubMySqlConnection
Return ""
End If
End Function
Private Function CleanText(ByVal Source As String) As String
Dim result As String = Source
' Replace repeating spaces with a single space
result = Replace(result, " +", " ")
' Remove any trailing spaces and tabs from the end of each line
result = Replace(result, "[ \t]+\r\n", vbCrLf)
' Remove any leading whitespace characters
result = Replace(result, "^[\s]+", String.Empty)
' Remove any trailing whitespace characters
result = Replace(result, "[\s]+$", String.Empty)
' Remove extra line breaks
result = Replace(result, "(\r\n)+", vbCrLf + vbCrLf)
Return result
End Function
Private Function Get_Sender_Details(inputMessage As Outlook.MailItem) As email_contact
Dim name_to_return As String = ""
Dim email_address_to_return As String = ""
Dim senderOfInterest As Outlook.AddressEntry, sendersExchangeRecords As Outlook.ExchangeUser
Try ' because weird email has moved error is creeping up here from time to time.
senderOfInterest = inputMessage.Sender
If senderOfInterest IsNot Nothing Then
If senderOfInterest.AddressEntryUserType = 0 Then 'Note 0 replaces olExchangeUserAddressEntry from https://msdn.microsoft.com/EN-US/library/office/ff868214.aspx
sendersExchangeRecords = senderOfInterest.GetExchangeUser
name_to_return = sendersExchangeRecords.Name
email_address_to_return = sendersExchangeRecords.PrimarySmtpAddress
Else
name_to_return = get_name_from_email_address(inputMessage.SenderEmailAddress)
email_address_to_return = inputMessage.SenderEmailAddress
End If
Else
name_to_return = "Nobody"
email_address_to_return = "nobody@nowhere.tld"
End If
Catch ex As System.ArgumentNullException
Dim message As New Magenta.Message("Cannot extract Sender's SMTP address:" & ex.Message, "Email Saving Error", "error")
Catch another_error As System.Runtime.InteropServices.COMException
Dim message As New Magenta.Message("Cannot extract Sender's SMTP address:" & another_error.Message, "Email Saving Error", "error")
End Try
If email_address_to_return = "" Or email_address_to_return = Nothing Then email_address_to_return = "nobody@nowhere.tld"
Return New email_contact(name_to_return, email_address_to_return)
senderOfInterest = Nothing
sendersExchangeRecords = Nothing
End Function
Private Function Get_All_Recipent_Details(inputMessage As Outlook.MailItem) As List(Of email_contact)
Dim recipient_Of_Interest As Outlook.Recipient
Dim recipients_Property_Accessor As Outlook.PropertyAccessor
Dim list_of_email_addresses_we_are_interested_in As New List(Of email_contact)
Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
Try
If inputMessage.Recipients.Count > 0 Then
For count As Integer = 1 To inputMessage.Recipients.Count
recipient_Of_Interest = inputMessage.Recipients(count)
recipients_Property_Accessor = recipient_Of_Interest.PropertyAccessor
Dim position_of_ampersand As Integer = InStr(recipient_Of_Interest.Name, "@")
Select Case position_of_ampersand
Case 0
list_of_email_addresses_we_are_interested_in.Add(New email_contact(recipient_Of_Interest.Name, recipients_Property_Accessor.GetProperty(PR_SMTP_ADDRESS)))
Case Else
list_of_email_addresses_we_are_interested_in.Add(New email_contact(get_name_from_email_address(recipient_Of_Interest.Name), recipients_Property_Accessor.GetProperty(PR_SMTP_ADDRESS)))
End Select
Next
Else
list_of_email_addresses_we_are_interested_in.Add(New email_contact("nobody", "nobody@nowhere.tld"))
End If
Catch ex As System.Runtime.InteropServices.COMException
Dim message As New Magenta.Message("Cannot extract Sender's SMTP address:" & ex.Message, "Email Saving Error", "error")
End Try
Return list_of_email_addresses_we_are_interested_in
End Function
Private Function get_name_from_email_address(email_address As String) As String
Dim position_of_ampersand As Integer = InStr(email_address, "@")
Return Strings.Left(email_address, (position_of_ampersand - 1))
End Function
Public Function write_email_to_sql() As Boolean
Using internalsubMySqlConnection As New MySqlConnection(Magenta.Authenticator.openkm_direct_sql)
Try
'I am going to try to only open one connection, which means wrapping the two using ...commands in one try catch loop.
'This is really about making sure we do not have an unhandled exception, as the using...end using takes care of closing the db connection if it does fail.
internalsubMySqlConnection.Open()
'write to activity log
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_activity` (`ACT_ACTION`, `ACT_DATE`, `ACT_ITEM`, `ACT_PARAMS`, `ACT_PATH`, `ACT_USER`) VALUES (@action, @date, @item, @params, @path, @user);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@action", "CREATE_MAIL")
internalsubMySqlCommand.Parameters.AddWithValue("@date", Me.created)
internalsubMySqlCommand.Parameters.AddWithValue("@item", Me.uuid)
internalsubMySqlCommand.Parameters.AddWithValue("@params", Nothing)
internalsubMySqlCommand.Parameters.AddWithValue("@path", Me.path)
internalsubMySqlCommand.Parameters.AddWithValue("@user", Me.author)
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
'write to nodebase
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_base` (`NBS_UUID`, `NBS_AUTHOR`, `NBS_CONTEXT`, `NBS_CREATED`, `NBS_NAME`, `NBS_PARENT`, `NBS_PATH`, `NDC_SCRIPT_CODE`, `NDC_SCRIPTING`) VALUES (@uuid, @author, @context, @created, @name, @parent, @path, @script_code, @scripting);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@uuid", Me.uuid)
internalsubMySqlCommand.Parameters.AddWithValue("@author", Me.author)
internalsubMySqlCommand.Parameters.AddWithValue("@context", Me.context)
internalsubMySqlCommand.Parameters.AddWithValue("@created", Me.created)
internalsubMySqlCommand.Parameters.AddWithValue("@name", Me.name)
internalsubMySqlCommand.Parameters.AddWithValue("@parent", Me.parent_uuid)
internalsubMySqlCommand.Parameters.AddWithValue("@path", Nothing)
internalsubMySqlCommand.Parameters.AddWithValue("@script_code", Nothing)
internalsubMySqlCommand.Parameters.AddWithValue("@scripting", "F")
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
'write to mail
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_mail` (`NML_CONTENT`, `NML_FROM`, `NML_MIME_TYPE`, `NML_RECEIVED_DATE`, `NML_SENT_DATE`, `NML_SIZE`, `NML_SUBJECT`, `NBS_UUID`) VALUES (@content, @from, @mime_type, @received_date, @sent_date, @size, @subject, @uuid);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@content", Me.content)
internalsubMySqlCommand.Parameters.AddWithValue("@from", Me.from.description)
internalsubMySqlCommand.Parameters.AddWithValue("@mime_type", Me.mimetype)
internalsubMySqlCommand.Parameters.AddWithValue("@received_date", Me.received)
internalsubMySqlCommand.Parameters.AddWithValue("@sent_date", Me.sent)
internalsubMySqlCommand.Parameters.AddWithValue("@size", Me.size)
internalsubMySqlCommand.Parameters.AddWithValue("@subject", Me.subject)
internalsubMySqlCommand.Parameters.AddWithValue("@uuid", Me.uuid)
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
'write to to
For Each recipient_to_write As email_contact In Me.[to]
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_mail_to` (`NML_TO`, `NMT_NODE`) VALUES (@to, @node);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@to", recipient_to_write.description)
internalsubMySqlCommand.Parameters.AddWithValue("@node", Me.uuid)
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
Next
'write to role permission
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_role_permission` (`NRP_NODE`, `NRP_PERMISSION`, `NRP_ROLE`) VALUES (@node, @permission, @role);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@node", Me.uuid)
internalsubMySqlCommand.Parameters.AddWithValue("@permission", 15)
internalsubMySqlCommand.Parameters.AddWithValue("@role", "ROLE_USER")
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
'write to user permission
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_user_permission` (`NUP_PERMISSION`, `NUP_USER`, `NUP_NODE`) VALUES (@permission, @user, @node);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@permission", 15)
internalsubMySqlCommand.Parameters.AddWithValue("@user", Me.author)
internalsubMySqlCommand.Parameters.AddWithValue("@node", Me.uuid)
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
Using internalsubMySqlCommand As New MySqlCommand("INSERT INTO `okm_node_user_permission` (`NUP_PERMISSION`, `NUP_USER`, `NUP_NODE`) VALUES (@permission, @user, @node);", internalsubMySqlConnection)
internalsubMySqlCommand.Prepare()
internalsubMySqlCommand.Parameters.AddWithValue("@permission", 15)
internalsubMySqlCommand.Parameters.AddWithValue("@user", "okmAdmin")
internalsubMySqlCommand.Parameters.AddWithValue("@node", Me.uuid)
internalsubMySqlCommand.ExecuteNonQuery()
End Using 'internalsubMySqlCommand
Return True
Catch somesortofSQLerror As MySqlException
Dim message As New Magenta.Message("Cannot connect to database: " & somesortofSQLerror.Message, "Database Connection Error", "error")
If internalsubMySqlConnection.State = ConnectionState.Open Then internalsubMySqlConnection.Close()
End Try
End Using 'internalsubMySqlConnection
Return False
End Function
End Class
Public Class email_contact
Private Property internal_name As String
Public Property name As String
Get
If internal_name Is Nothing Or internal_name = "" Then
Return "null"
Else
Return internal_name
End If
End Get
Set(value As String)
internal_name = value
End Set
End Property
Private Property internal_address As String
Public Property address As String
Get
If internal_address Is Nothing Or internal_address = "" Then
Return "null"
Else
Return internal_address
End If
End Get
Set(value As String)
internal_address = value
End Set
End Property
Public ReadOnly Property description As String
Get
Return name + " <" + address + ">"
End Get
End Property
Public Sub New()
End Sub
Public Sub New(input_name As String, input_address As String)
name = input_name
address = input_address
End Sub
End Class
I am, however, having a problem getting the email to load outwith OpenKM, depending on the browser I am using. Firefox = No. Chrome & Safari = Yes. I coded the above by reverse engineering the okmdb. I was hoping that someone might be able to direct me to the relevant part of the openkm-dev code that handles the display of these emails, and even better the part that serves them up to be downloaded. I presume that a new .eml file is built from scratch from the data stored in the okmdb, and I would like to review that code, but just cannot find it.
Many thanks,