• OpenKM-dev - Download Document

  • He we will discuss about how to make customization and improvement to the OpenKM source code.
He we will discuss about how to make customization and improvement to the OpenKM source code.
Forum rules: Please, before asking something see the documentation wiki or use the search feature of the forum. And remember we don't have a crystal ball or mental readers, so if you post about an issue tell us which OpenKM are you using and also the browser and operating system version. For more info read How to Report Bugs Effectively.
 #42622  by ofstab
 
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:
Code: Select all
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
This seems to work fine, and the email does appear in the folder where it is supposed to be, and displays properly.

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,
 #42639  by jllort
 
Seems you are trying to create the information from database directly. It's not a good idea making from this way.

I suggest another way for trying it. Can you convert the mail to msg or eml format and then with SDK for .net ( take a look here https://docs.openkm.com/kcenter/view/sd ... mentSimple ) and upload the document. OpenKM might detect it's a mail object and then add into the system.
 #42661  by ofstab
 
Ah, right. No using createsimple from the SDK (1.1.1) does not work with .msg files. It loads them into the system as document objects, not as mail objects. I have code that scans the file and extracts the text as metadata which I can display as a custom property group, but it is not as slick as the system recognising that it is an email.
 #42674  by jllort
 
If you upload the msg file from the UI then is detected as mail ? please check this point.
 #42956  by ofstab
 
jllort wrote:If you upload the msg file from the UI then is detected as mail ? please check this point.
Sorry about the delay in replying. Been too busy to be coding recently. Yes, you are right that uploading a .msg file from the UI does detect it as an email and creates it as an email in OpenKM.

This is not the behaviour with createsimple, which loads it as a document object, not as a mail object.
 #42969  by jllort
 
That's because createSimple is only for creating documents and not for creating mails etc.. Internally the FileUploadServlet take in consideration several things ( is a zip, is a mail etc.. ) and then applies some specific logic. Basically internally is executing:
Code: Select all
if (FilenameUtils.getExtension(fileName).equalsIgnoreCase("eml")) {
	log.debug("import EML file '{}' into '{}'", fileName, path);
	Mail mail = OKMMail.getInstance().importEml(path, is);
	fuResponse.get().setPath(mail.getPath());
	sendResponse(out, action, fuResponse.get());
} else if (FilenameUtils.getExtension(fileName).equalsIgnoreCase("msg")) {
	log.debug("import MSG file '{}' into '{}'", fileName, path);
	Mail mail = OKMMail.getInstance().importMsg(path, is);
	fuResponse.get().setPath(mail.getPath());
	sendResponse(out, action, fuResponse.get());
}
The best option is modify source code for adding in the rest services these methods
 #42987  by ofstab
 
Ah, thanks for the clarification. I will look into that. I am guessing the answer is to add some createsimple code to com.openkm.api.OKMMAIL.java using the FileUploadServlet code as a base?
 #43001  by jllort
 
The problem is the SDK 1.1.1 is not supporting the MailService.java class ( available REST WS where into you have importEML and importMsg ).

Actually you have two options:
1- Accessing directly to the rest services ( because SDK community version is still not wrapping it ).
2- Modify create client source code for taking in consideration the case of eml, msg files ( piece of code from FileUploadServlet ). ( I do not like much this solution, because in this scenario you are not able to save an eml file as is ).

In future we will extend de community SDK, but at the present this is still not available.
 #43011  by ofstab
 
2- Modify create client source code for taking in consideration the case of eml, msg files ( piece of code from FileUploadServlet ). ( I do not like much this solution, because in this scenario you are not able to save an eml file as is ).
Having looked at the code, you are right. I tried to simply modify the createsimple document code to detect if the document had the extension .eml or .msg, just as the FileUploadServlet does. That got messy very quickly, not least because that code returns a Document object, which cannot happen when you convert the stream to a Mail object.
jllort wrote:1- Accessing directly to the rest services ( because SDK community version is still not wrapping it ).
This was the best option, and I have managed to get this working here: https://forum.openkm.com/viewtopic.php? ... 041#p43006
 #43014  by jllort
 
After the next OpenKM release we will prepare a major SDK upgrades. I think in this month or next the next OpenKM release will arrive.
 #47684  by ruth
 
I want to say Mac EML to MSG converter is the best tool as this seems to be the quick tool for me when I performed conversion of thousands of EML files to MSG format. Give a chance to this tool. I hope you will get successful results.

About Us

OpenKM is part of the management software. A management software is a program that facilitates the accomplishment of administrative tasks. OpenKM is a document management system that allows you to manage business content and workflow in a more efficient way. Document managers guarantee data protection by establishing information security for business content.