Overview
This document serves as a guide for all clients using the Eleos Document Poller, as a guide to reprocess documents that are Emailed and/or File Exported. This can be done by running a script in the database. Someone with permission to access and edit the database can run the script to reprocess the documents. The following script allows users to first identify and review documents within a specific time window (from and to) and then with a single change, sets those records for reprocessing. This script is specifically designed to work with documents which were initially processed following the Q42023 EleosCore enhancements deployment.
Script to Reprocess Documents
Before running the script to reprocess any missing documents, please see the following sections explaining the variables used in the script and step-by-step instructions on how to use it.
Variable Explanations
@ReviewRecords - Setting responsible for controlling if the records are output for review or reset to be processed again. When the value is 1, impacted records will be displayed. When the value is 0, the records will be updated within the database to be processed again by the document poller.
@ResendEmails - This value determines if emailed records should be reprocessed or not. If your document poller is not configured to email documents after processing, you will not need this setting. When the value is 1, emailed documents will reprocess. When the value is 0, the records will not be.
@ReExportFiles - This value determines if exported records should be reprocessed or not. If your document poller is not configured to export documents to a directory after processing, you will not need this setting. When the value is 1, emailed documents will reprocess. When the value is 0, the records will not be.
@UploadTo - This value represents the end of the reprocessing window. Documents within the database that were uploaded to Eleos before or at the time specified will be reprocessed. The time should be in a format of 'yyyy-mm-dd hh:mm:ss.0000000 +00:00'. If you are including an offset with the time, the '+00:00' would instead be preceeded by a '-'. Eastern Standard Time for instance would be ‘-05:00’.
@UploadFrom - This value represents the start of the reprocessing window. Documents within the database that were uploaded to Eleos after or at the time specified will be reprocessed. The time should be in a format of 'yyyy-mm-dd hh:mm:ss.0000000 +00:00'. If you are including an offset with the time, the '+00:00' would instead be preceeded by a '-'. Eastern Standard Time for instance would be ‘-05:00’.
Steps for Usage
- Ensure that @ReviewRecords is set to 1 on the initial execution. Before making updates to documents, you should review the results and make sure everything looks accurate beforehand.
- Update @UploadTo and @UploadFrom to create a date range of documents for review and reprocessing.
- Determine the type of processing you would like to perform. Do you need to resend emailed documents, documents exported to a directory, or both.
- Update the corresponding variable based on the processing you intend to perform. Set @ResentEmails to 1 for emailed documents. Set @ReExportFiles to 1 for exported documents. Processing methods not being used or reset should be maintained with a default of 0.
- Once the window has been defined, processing type has been determined and the selected records have been reviewed, update @ReviewRecords to 0 to disable review and execute the script again. This time, instead of outputting the records for review, the updates will be persisted into the database for reprocessing.
Query
/********************************************************************************************
CONFIGURE VARIABLES FOR REVIEW AND PROCESSING
********************************************************************************************/
;DECLARE /* Reprocessing Controls */
@ReviewRecords BIT = 1
/* Set @ResentEmails to 1 to resend emails. Set @ReExportFiles to 1 to rexport to directory. */
,@ResendEmails BIT = 0
,@ReExportFiles BIT = 0
/* To and From window require date, time and offset - Ex. Both Dates Below are the Same */
,@UploadTo DATETIMEOFFSET = '2023-11-10 15:17:58.0165000 -05:00'
,@UploadFrom DATETIMEOFFSET = '2023-11-10 20:17:58.0165000 +00:00'
/********************************************************************************************
WHEN @ReviewRecords IS 1, SELECT RESULTS BACK FOR REVIEW BEFORE REPROCESSING
********************************************************************************************/
;IF ( @ReviewRecords = 1 )
BEGIN
/* Select DocumentHistory records within the defined UploadTo and UploadFrom window */
/* Note the UploadDate times will be displayed in UTC time */
SELECT [DocumentId] AS 'Id'
,[UserName] AS 'ScannedBy'
,[NumberOfPages] AS 'NumberOfPages'
,[UploadedAt] AS 'UploadDate'
,[LoadNumber] AS 'LoadNumber'
,[BolNumber] AS 'BolNumber'
,[DocumentType] AS 'DocumentType'
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
ORDER BY [RetryDt] ASC
END
/********************************************************************************************
WHEN REVIEW IS DISABLED, UPDATE RECORDS TO BE REPROCESSED
********************************************************************************************/
ELSE BEGIN
/* Declare a table variable to store DocumentIds as primary key for reprocessing */
;DECLARE @DocumentsToReprocess TABLE (
[DocumentId] VARCHAR(30)
)
/* Insert DocumentIds into the table variable existing within the To/From window */
;INSERT INTO @DocumentsToReprocess(
[DocumentId]
)
SELECT [DocumentId] = [DocumentId]
FROM Eleos.DocumentHistory
WHERE [UploadedAt] <= @UploadTo
AND [UploadedAt] >= @UploadFrom
/********************************************************************************************
UPDATE DocumentFields TO RESET SENT EMAIL FLAG TO FALSE
********************************************************************************************/
;IF ( @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'EmailSent'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DocumentFields TO RESET EXPORTED FILE FLAG TO FALSE
********************************************************************************************/
;IF ( @ReExportFiles = 1 )
BEGIN
;UPDATE Eleos.DocumentFields
SET [FieldValue] = 'false'
WHERE [FieldChecksum] = Eleos.udf_Checksum('true')
AND [FieldName] = 'FileSaved'
AND [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
/********************************************************************************************
UPDATE DOCUMENT HISTORY TO PENDING WHERE REPROCESSING WAS ENABLED
********************************************************************************************/
;IF ( @ReExportFiles = 1
OR @ResendEmails = 1 )
BEGIN
;UPDATE Eleos.DocumentHistory
SET [Pending] = 1
,[Retries] = 0
,[RetryDt] = SYSDATETIMEOFFSET()
,[SentToEmail] = IIF ( @ResendEmails = 1, NULL, [SentToEmail] )
WHERE [DocumentId] IN ( SELECT [DocumentId]
FROM @DocumentsToReprocess )
END
END