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

Table of Contents

• Overview 
  • Introduction
  • Feature Options
    • System Directory or File Share Options
    • SharePoint 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_Custom Template
• 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
  • Creating and Populating the Temporary Data Table
  • Building and Inserting Blob Data into Media Items
• Web Path - @WebPath
  • Populating the Temporary Data Table
• 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
  • System Directory or File Share Results
  • SharePoint 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 four (4) 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 utilizing our stored procedure ELEOS.usp_GetMediaItems_Custom_Template if SQL contracts are implemented. If SQL Contracts are not yet implemented and ELEOS.usp_GetMediaItems_Custom_Template does not exist, then changes should be made in ELEOS.usp_GetMediaItems instead.

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 “LogErrorsForMissingFiles”, “EnableImpersonateUser”, and the SharePoint “Enabled” settings should all be set to false, the “FilterClientServiceErrors” setting can remain it’s default value of true, and the rest of the settings can be skipped and left as blank strings as shown below:

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 impersonated by 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 “TestUser".
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 “testpassword12345”.
6. LogErrorsForMissingFiles – This is an additional setting that should be left as “false” and only set to “true” when more in depth troubleshooting is required for diagnosing issues with missing media items

The information provided, 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.

For additional information regarding impersonation, please see our document, Eleos - How ASR Secured TMWSuite. 

Configuring a SharePoint Site URL

The Attachments Feature also allows the 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 section below for Setting Up SharePoint Site Access.

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”.
6. FilterClientServiceErrors – This is an additional setting that should be left as “true” and only set to “false” when more in depth troubleshooting is required for diagnosing issues with missing media items.

Modifying Eleos.usp_GetMediaItems_Custom Template

Instructions to make modifications can be found within ELEOS.usp_GetMediaItems_Custom_Template itself:

More specific guidance on each method is included in their respective sections below:

Procedure Update Location

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

For all attachment build methods both in the ELEOS.usp_GetMediaItems procedure or utilizing the ELEOS.usp_GetMediaItems_Custom_Template procedure, the build method examples will have to be uncommented and have their WHERE clause updated to remove the @True = @False condition that is used to disable each build method example by default as shown in the example screenshot below:

Figure 5: Example build method before being uncommented and having the WHERE clause currently set to disable the build method.

Figure 6: Example build method after being uncommented and having the WHERE clause updated removing the @True = @False condition to be enabled.

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 these WHERE clause conditions, attachments for all drivers and all their loads both current and historical would have load attachments created for them.


Figure 7: 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 8: File Path Example.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

• [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.
  

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

Figure 9: 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 10: Modifying the FROM and WHERE clause.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

In the above screenshot ‘{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 11: File Search Example.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

• [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 pattern in their name. See “File Search Explained” for more information on how search patterns can be defined.

[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 12: 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 13: Using Stop Information to Identify Attachment Documents.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

In the above screenshot the Company ID is used in our search pattern, now looking for a filename that contains the company id associated for the stop.

File Search Explained

• ‘*’ is a wildcard or anything character, which indicates all, or anything is acceptable.
• ’*’ + FORMAT(S.[CompanyId], 'F0') +’*.*’ in the screenshot above is saying that anything can exist before the company id, and anything can exist after the company id. At the end, the ‘.* ‘ uses a wildcard to indicate that any file extension is applicable.
  • Essentially, if the string of the searched value (company id, order number, etc.) exists within the filename somewhere, the file will be identified.

Note: Search patterns and formatting can be used to dynamically generate a full path to your searched file in the @FileSearch and @SharePointFileSearch build methods if the resulting values match a specific filename.

Direct Data - @DirectData

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

Creating and Populating the Temporary Data Table

Figure 14: Populate the Blob Table with Attachment Data.

The screenshot above demonstrates how we pull the data from a sample blob data table. In this instance, we are pulling files where the B.[blob_table] = 'company' AND S.[CompanyId] = B.[blob_key] or B.[blob_table] = 'commodity' AND S.[CommodityId] = B.[blob_key]. In this case, we essentially pull blob data where the company id matches the data key, or where the commodity code matches the blob key after first determining the specific table to use.

This can be set up differently within your system, but the important takeaway to understand with this build method is that the data is first pulled from your blob data table based on specific criteria determined within this INSERT statement.

Building and Inserting Blob Data into Media Items

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

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

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 affect 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.

Lastly after all the information is pulled from the blob data temporary table it is dropped.

Web Path - @WebPath

The fourth build method @WebPath grabs the media item directly from a web URL. This method pulls the data based on the search criteria that utilizes a URL.

Populating the Temporary Data Table

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

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

The screenshot above 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], ‘F0’) + ‘.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 set up 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 further filtering using a WHERE clause can be added for restricting loads attachments from the web are shown to.

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 ReadAll permissions for the app
3. Update appsettings.json with app information

Register an App

Token-based authentication is only possible when an app is defined that has the required permissions to perform Read operations.  This allows SharePoint to verify that you are who you say you are without requiring an explicit user login.

There are two ways that an app can be registered based on your organization’s 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 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 where 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.

Figure 17: SharePoint File Path example.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

• [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 18: 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.

Figure 19: SharePoint File Path example.

Note: In the above example, if SQL contracts implemented, then use the temp table #MediaItems within the ELEOS.usp_GetMediaItems_Custom_Template instead of @MediaItems.

• [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. See “File Search Explained” for more information on how search patterns can be defined.

Figure 20: 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 the first 3 distinct build methods (@FilePath, @FileSearch, and @DirectData) will show the media item attachments on the specific test load, corresponding to the [LoadId] that was determined on the attachment within the procedure.

Figure 21: Load Attachments Application View.

Note: Some sections of the screenshots such as the stop information and special instructions have been removed if they contained sensitive information.

SharePoint Results

The results of either of the other 3 distinct build methods (@WebPath, @SharePointFilePath, and @SharePointFileSearch) will yield the same visual results as shown in the screenshot above. The media item attachments on the specific test load correspond to the [LoadId] that was determined on the attachment within the procedure.

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

1. “Example3” and “Example2” were files pulled from a configured SharePoint site from a folder called ExampleFolder using the SharePoint File Search method and a file search pattern.
2. “Example” was a file pulled from a configured SharePoint site from the root Documents folder specifically by name using the SharePoint File Path method.
3. “23” was a file pulled from a website specifically by name using the Web Path method.