To download a PDF of this document, please see the attachment.

TABLE OF CONTENTS

• Introduction
• Feature Options
• Configuring Appsettings For Connecting To SharePoint And User Permissions
  • Enabling User Permissions to a Locked Down System Directory:
  • Configuring a SharePoint Site URL:
• Modifying Eleos.usp_GetMediaItems
  • Procedure Update Location
  • Where Clause Explanation
• File Path - @FilePath
  • More Advanced Example
• File Search - @FileSearch
  • Additional File Search example
  • File Search Explained
• Direct Data - @DirectData
  • Populating The Temporary Data Table
  • Building And Inserting Blob Data Into Media Items
• SharePoint File Retrieval
  • Setting Up SharePoint Site Access
    • Register an App
      • Azure Active Directory
      • SharePoint App Registration
    • Request Read Permissions for the Application
    • Updating AppSettings.json with configured app information
  • SharePoint File Path - @SharePointFilePath
  • SharePoint File Search - @SharePointFileSearch
• Application Results

Overview 

Introduction

The Media Attachments process is a feature of our Eleos Integration that places the attachments process in the client’s hands. PDF Documents, Training Videos, and Location Maps are just a few of the many possible uses that can be explored with this feature. Using this document, we will explain the process of how files placed in a system directory, file share, or SharePoint site can be loaded dynamically into the application for the driver’s review and benefit. Note utilizing a SharePoint site for media attachments requires additional setup to ensure connections and permissions are properly established. Image data uploaded into your TMS can also be pulled and uploaded as blob data for review within the app. If this feature can reach your selected media, then it can dynamically place the items within the driver’s application directly on their corresponding load.

Feature Options

There are three (3) options in how you grab media from a system directory or file share and two (2) similar options for how you grab media from a SharePoint site for the platform to display. Additional details will be provided in later sections of the documentation. All modifications and customizations are completed within your TMS database within our stored procedure “ELEOS.usp_GetMediaItems”.

Configuring Appsettings For Connecting To SharePoint And User Permissions

*Note: If you are not using a SharePoint site for pulling your media items, or the location of your media items on a system does not require a specific user to access the attachment files / directory, or you are only using the Direct Data build method, then “EnableImpersonateUser” and the SharePoint “Enabled”  settings should be set to false, and the rest of this section can be skipped.

Figure 1: Settings disabled if no system user permissions are needed for directory access and media items are not pulled from SharePoint.

Enabling User Permissions to a Locked Down System Directory:

The Attachments Feature has built-in functionality designed for user impersonation, allowing it to retrieve files from system directories that are locked down to specific users. If the Media that you specify for attachments requires a specific user to access, you will need to update settings with the users’ credentials to allow the web services to retrieve them.

Figure 2: MediaItems section with example user credentials for impersonation and access to a locked down system directory.

1. Locate the section “MediaItems” as shown in the image above. This is where we will integrate the user that will be impersonatedby the integration when attempting to access the files specified.
   1. Impersonation is the ability of a thread to execute in a security context that is different from the context of the process that owns the thread, which in this case means as a different user.
2. EnableImpersonateUser – Update this to be “true” as seen above. This will signal for the integration to use the credentials provided.
3. Domain – The Domain of the user that the integration is going to impersonate. The information should be entered within the allotted quotes. Ex. “MYDOMAIN”.
4. Username – The username of the user that the integration is going to impersonate. The information should be entered within the allotted quotes as show in the image above “Test User”.
5. Password – The password of the user that the integration is going to impersonate. The information should be entered within the allotted quotes as shown in the image above “testuser123”.

The provided information, if accurate, is intended to allow the integration to login to the provided user, allowing the integration the required permissions to access your local directory or file share.

Configuring a SharePoint Site URL:

The Attachments Feature also allows functionality to retrieve files from a SharePoint site. Connecting to the desired SharePoint site requires providing the sites base URL along with the sites Client Id and Client Secret to authenticate to the main site and subsites, these will need to be updated in settings allowing the SharePoint service to establish a connection and retrieve the items. To configure or obtain this Site and Client Information from SharePoint see the next section below for Establishing and Configuring your SharePoint Site for Connection.

Figure 3: SharePoint section of MediaItems with example SharePoint site and client configuration values.

1. Located the subsection of “MediaItems” called “SharePoint” as shown in the image above. This is where we will provide the SharePoint site and client info we want to connect to.
2. Enabled – This should be updated to “true” to signal to the integration to try authentication with the proceeding site and client info.
3. SiteURL – This is the base URL of your SharePoint site. This should be set as the root site that files will be accessed through to specified subsites (Subsites are configured in the mentioned procedure discussed in the following document section). The SiteURL should be entered with double quotes and should include the “https://” prefix followed by the site name as shown in the image above “https://myorg.sharepoint.com/sites/mysite”.
4. ClientId – This is the id generated when the SharePoint site was established and is used for authenticating with the application. Obtaining this for your SharePoint site may require advanced user privileges. The ClientId should be entered with double quotes and is typically in the form of a GUID (Global Unique Identification Number) much like the example GUID shown in the image above “91D6783E-C825-4B27-84BB-1D0D66794E2F”.
5. ClientSecret – This is the secret generated when the SharePoint site was established and is used for security when authenticating with the application. Obtaining this for your SharePoint site may require advanced user privileges. The ClientSecret should be entered with double quotes and is also typically in the form of a GUID as well much like the example GUID shown in the image above “A86FF39A-FCD5-464E-B50E-CDEB4F5D6387”.

Modifying Eleos.usp_GetMediaItems

Procedure Update Location

Figure 4: Edits should be done prior to this section within the procedure.

Where Clause Explanation

• L.[lgh_driver1] = @p_UserName This ensures that we are only processing load attachments for a specific driver when invoking the procedure.
• AND L.[lgh_outstatus] IN (‘PLN’, ‘DSP’, ‘STD’) This section ensures that we are only creating attachments for loads that are not completed yet. Without this, attachments for all historical loads that have ever been completed by the invoking driver would be created.

Figure 5: Important section in the WHERE clause to avoid excess attachment creation.

File Path - @FilePath

Here we have an example of our first build method @FilePath. This build method specifies a full file path and name for a single media item to attach.

Figure 6: File Path Example.

• [BuildMethod] is set as @FilePath to specify the type of method.
• [LoadId] is set as the leg number to know which load the attachment should be on.
• [FilePath] is set as a string pointing to a local file path and name
• [Title] is set as a string that indicates what the media item will be called within the application.
  

*Note: [FilePath] could also be set to a network share directory for this method such as \\SomeNetworkShare\DirectoryToSearch\StaticImage.png.

Figure 7: StaticImage.png would reside in the local file system as shown for our Figure 3 Build Method.

More Advanced Example

Here we have an additional example of @FilePath to show how we can further customize the FROM and WHERE clause to change the specific company ID this image is seen on. 

Figure 8: Modifying the FROM and WHERE clause.

In Figure 6, a SQL JOIN on dbo.Stops was added in order to restrict our generic file attachment to only show for specific loads containing stops matching our company criteria.

AND S.[cmp_id] = ‘{Company ID to Show Image For}’ would be updated with the specific company ID that the file should be shown for.

File Search - @FileSearch

Here we have an example of our second build method @FileSearch. This build method specifies a directory path and a search pattern for multiple media items to attach, skipping the specific filename as noted in the File Path build method.

Figure 9: File Search Example.

• [BuildMethod] is set as @FileSearch specifying the type of method.
• [LoadId] is set as the leg number to know which load the attachments should be on.
• [FilePath] is set as a string pointing to the Network Share or File Path excluding the filename.
• [FileSearch] which is set to include all files that have the matching search patter in their name.

*Note: [FilePath] must end with a backslash character and can optionally be set to a local directory for this method such as C:\MyDirectory\ instead of a Network Share location.

Figure 10: File Search Network Share Example - Suppose our order number is 12345 we would pull the first text document.

Additional File Search example

Here we have an additional example of @FileSearch to show how we can customize our search pattern to look for different information tied to the load.

Figure 11: Using Stop Information to Idenify Attachment Documents.

In Figure 9, a JOIN on dbo.Stops was added in order to incorporate the Stop Company ID into our search pattern, now looking for a filename that contains the load specific stop company.

File Search Explained

• ‘*’ is a wildcard or anything character, which indicates all, or anything is acceptable.
• ’*’ + CONVERT(VARCHAR, L.[OrderNumber]) +’*.*’ in figure 7 is saying that anything can exist before the ordernumber, and anything can exist after the order number. At the end, the ‘.* ‘ uses a wildcard to indicate that any extension is applicable.
  • Essentially, if the order number exists within the filename somewhere, the file will be identified.
• In Figure 9, the additional file search example, we have a similar pattern. ‘*’ + CONVERT(VARCHAR, S.[cmp_id]) + ‘*.*’. In this case, we are looking for a filename that contains anything, the Stops Company ID, and then anything again, followed by any extension.

Direct Data - @DirectData

The third build method @DirectData grabs the media item directly from the database. This method pulls the data based on the search criteria within the first section which is our insert into #BlobData.

Populating The Temporary Data Table

Figure 12: Populate the Blob Table with Attachment Data.

Building And Inserting Blob Data Into Media Items

Figure 13: Inserting Data from the Blob Data temporary table into the returned Media Items table.

Using the information within our temporary table, we then insert those items into our MediaItems table which returns the results to the integration for processing. Here, if modifications are made to the WHERE clause, the updates will only effect what is pulled from the temporary table. Not from the database.

• [BuildMethod] is set as @DirectData specifying the type of method used.
• [LoadId], [OriginalFilename], [Title], and [Contents] all are set using blob data from a temporary table indicated by the B. alias.
• [Contents] is pulled directly from the database as a byte array which is converted within the integration and uploaded to the platform for application use.

Web Path - @WebPath

The fourth build method grabs the media item directly from a web URL. This method pulls the data based on the search criteria within the first section which is our insert into #BlobData.

Populating the Temporary Data Table

Figure 12: Populate the Media Items table with Attachment Data. 

Figure 12 demonstrates how we pull the data from a sample URL. In this instance, we are pulling files by setting [FilePath] = 'https://www.google.com/search?q=' and [Title] = Google Search - + FORMAT([S.LoadID] + ‘.pdf’ In this case, we use the file path specified to pull the image, and the title as the title of the image to display. 

This can be setup differently within your system, but the important takeaway to understand with this build method is that the data is first pulled using the full URL and can be assigned to specific drivers or loads using the WHERE clause at the bottom. 

SharePoint File Retrieval

Retrieval of one or more files from SharePoint is also supported.  Similarly to standard file system methods, a direct file path to a file or a folder directory with a file search pattern may be specified.

Setting Up SharePoint Site Access

The integration uses a client secret token-based authentication model to connect securely to a given SharePoint site.  As such, there is some setup required before SharePoint integration can function:

1. Register an App in Azure Active Directory or directly in the SharePoint site
2. Request SharePoint site Read permissions for the app and approve them in SharePoint
3. Update appsettings.json with app information

Register an App

Token-based authentication is only possible when an app is registered beforehand and given permission to perform its operations, in this case Read only.  This allows Microsoft to authenticate the integration and allow it to connect to the SharePoint site on your behalf without requiring user interaction.

There are two ways that an app can be registered based on your organizations preferences: via Azure Active Directory or directly in the SharePoint site.

*Note: The user registering an app and setting up its permissions must be an administrator of the SharePoint site regardless of where the app is registered.

Azure Active Directory

1. Navigate to your organization's Azure Portal and select the "Azure Active Directory" service.
   
2. Click "App registrations" under the "Manage" section of the left pane.
   
   
   
3. Click "New Registration" at the top of the App registrations page
   
   
4. Enter a name for your application and ensure that "Accounts in this organizational directory only" is selected.  You do not need to select a platform or enter a Redirect URI since we'll be using application-based authentication instead of user interaction.  Click "Register" at the bottom of the page.
   
   
   
5. After Registering the application, the new application's Overview page should open automatically.  Write down the "Application (client) ID" and "Directory (tenant) ID" listed here, then click "Add a certificate or secret" next to "Client credentials".
   
   
   
6. Click "New client secret", enter a secret description and select an expiration date (limited by Microsoft to a two year maximum), then click "Add"
   
   
7. Once Add is clicked, a secret will be generated and displayed under Client Secrets.  Copy the secret's value and write it down for later.  Azure will only allow you to view and copy the secret's value directly after creating it, so make sure to copy and/or write it down for later reference before leaving this screen.
   
   

SharePoint App Registration

If you do not have an Azure Active Directory subscription or would simply prefer to register an app directly in SharePoint, this section will instruct you how to do so.  Skip directly to "Request Read Permissions for the Application" if an app was already registered in Azure Active Directory.

Before following these steps, make sure to note your SharePoint site's URL.  It should include everything in the URL up until the route segment following "sites" (e.g. "https://myorg.sharepoint.com/sites/Documents").

The site URL will be referred to as <siteUrl> below.

1. Manually navigate to the SharePoint site's app registration page:

   <siteUrl>/_layouts/15/AppRegNew.aspx

   This can only be navigated to by entering the URL manually into your browser.
   
   

2. On this page, you should see several text entry fields.  You'll need to do all of the following.
   1. Click "Generate" next to "Client Id" to automatically generate a client ID (or manually enter one of your own if desired).  Write this value down.
   2. Click "Generate" next to "Client Secret" to automatically generate a client secret (or manually enter one of your own if desired).  Write this value down.
   3. Enter a Title for your SharePoint app
   4. Enter a valid App Domain and Redirect URI.  Our application will not actually use these, but SharePoint app registration requires these fields to be valid in order to register the app.  "www.google.com" and "https://www.google.com" can be used respectively.
   5. Once all fields are entered, click "Create" at the bottom of the page.
      
      
3. Once created, a confirmation page displaying all of the fields entered above will open automatically.  Write down any information not gathered in previous steps.
   
   

Request Read Permissions for the Application

Once an app has been registered via Azure Active Directory or in the SharePoint site itself, it must request Read permissions from the SharePoint site that it will access. 

1. Manually navigate to the SharePoint site's app permissions request page:
   
   

   <siteUrl>/_layouts/15/AppInv.aspx

   This can only be navigated to by entering the URL manually into a browser window.

   
   
2. Several text entry fields should be displayed on this page.  You will need to take the following steps:
   1. Enter the app's Client ID into the "App Id" field and click "Lookup".  The app information that you entered when registering the app should automatically populate into the appropriate fields.
      1. If you registered the app in Azure Active Directory and did not enter an App Domain and Redirect URI, you will need to do so on this screen.  The URL's that you enter will not be used, so "www.google.com" as the App Domain and "https://www.google.com" as the redirect URI will satisfy the requirements.
   2. Request Read permission in the Site Collection scope for the app.  Doing so requires entering the request in XML format in a particular way.  Copy the below XML and paste it into the "Permission Request XML" box:
      

      <AppPermissionRequests AllowAppOnlyPolicy="true">
          <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="Read" />
      </AppPermissionRequests>

      
      

   3. Click the "Create" button
      
      
      
3. Once "Create" is clicked, a confirmation page should open asking to have those permissions granted by an administrator.  Assuming you are the site's administrator, you should see a "Trust It" button.  Click it.
   
   
   Note, if you are not the site's administrator, you will instead see a prompt on this page telling you that an administrator must approve the request.

Updating AppSettings.json with configured app information

Once the app has been registered and its permissions have been granted, all that is left to do is to put the client app’s information into Eleos Core’s appsetting.json file. See the above section titled, “Configuring Appsettings For Connecting To SharePoint and User Permissions” then look underneath the subsection titled “Configuring a SharePoint Site URL”. This is the section that your SiteUrl, ClientId, and ClientSecret will need to be updated.

SharePoint File Path - @SharePointFilePath

Here we have an example of the fourth build method @SharePointFilePath that utilizes a SharePoint site-relative file path and name for a single media item to attach. Note advanced FROM and WHERE clause logic mentioned above in above File Path build method can also be applied to this build method.

Figure 14 SharePoint File Path example.

•  [BuildMethod] is set as @SharePointFilePath to specify the type of method. 
• [LoadId] is set as the leg number to know which load the attachment should be on.
• [FilePath] is set as a string pointing to a site-relative file path and name. Note this site-relative path does not include the base site URL as this base piece of the URL will be appended onto the beginning of the path depending on what is configured in appsettings.
• [Title] is set as a string that indicates what the media item will be called within the application.

Figure 15 StaticImage.png file on SharePoint site that is grabbed from Figure 14 example.

SharePoint File Search - @SharePointFileSearch

Here we have an example of the fifth build method @SharePointFileSearch that utilizes a SharePoint site-relative file path and a search pattern for multiple media items to attach, skipping the specific filename as noted in the SharePoint File Path build method. Note advanced FROM and WHERE clause logic and customized search patterns mentioned above in above File Search build method can also be applied to this build method.

Figure 16 SharePoint File Search example.

•  [BuildMethod] is set as @SharePointFileSearch to specify the type of method. 
• [LoadId] is set as the leg number to know which load the attachment should be on.
• [FilePath] is set as a string pointing to a site-relative file path excluding any filename. Note this site-relative path does not include the base site URL as this base piece of the URL will be appended onto the beginning of the path depending on what is configured in appsettings.
• [FileSearch] is set to include all files that have the matching search pattern in their name.

Figure 17 SharePoint site folder with multiple files that shows grabbing out of specified folder and search pattern from Figure 16 example.

Application Results

System Directory or File Share Results:

The results of either of the first (3) distinct build methods will show the media item attachments on the specific load, corresponding to the [LoadId] that was determined on the attachment within the procedure.

Figure 12: Load Attachments Application View

*Note: Some sections of the screenshots have been blacked out to provide animosity within our testing application.

SharePoint Results:

The results of either of the two (2) distinct build methods when utilizing a SharePoint site will yield the same visual results as mentioned above The Media Item attachments on the specific load, correspond to the [LoadId] that was determined on the attachment within the procedure.

Figure 19 Load Attachments Application View (zoomed in on the Attachments section) item examples pulled from SharePoint.

1. “TifExample3” and “TifExample2” were tif files pulled from a configured SharePoint site from a folder called ExampleFolder using the SharePoint File Search method.
2. “Test Tif” was a tif file pulled from a configured SharePoint site from the root Documents folder specifically by name using the SharePoint File Path method.

*Note: Our conversion logic converted the above examples from TIF files to PDF for Eleos to properly display them.