Overview
This is the continuation part of the PayloadVault project, if you have not checked our article on part-1 and part-2 then click the link below to check that out. In this article, we will focus on below tasks.
- We will update the landing page with search elements and populate the entries from the database to the UI.
Check out the full project :
Prerequisite
- Basics of JDBC adapter.
- Basics of SQL queries.
- Basics of DSP page with HTML, CSS, and javascript.
Create a JDBC adapter to extract transactions
- Create a JDBC adapter named ‘getTransactions‘ in the ‘HG_PayloadVault.v1.UI.adapter‘ location using the appropriate JDBC connection and a dynamic SQL template.
- Place the following query into the SQL statement section of the adapter, which will extract transactions based on the date, group them, and provide the count of each group.
SELECT INTERFACE_ID, SOURCE, TARGET, MAX(INTERFACE_NAME) AS INTERFACE_NAME, TRACKING_ID, DOCUMENT_TYPE, DOCUMENT_NAME, MAX(INSERTED_DATE) AS INSERTED_DATE, COUNT(*) AS DataCount FROM PAYLOADVAULT WHERE INSERTED_DATE > '${inDate}' GROUP BY INTERFACE_ID, SOURCE, TARGET, DOCUMENT_TYPE, TRACKING_ID,DOCUMENT_NAME,INTERFACE_NAME;
- Since we are using dynamic SQL, we need to define the expected output for this SQL, Refer to the screenshot below to define the output accordingly.
- Now refer to the screenshot below and set the query time out and result field.
Create a JDBC adapter to search transactions
- Copy ‘getTransactions‘ and paste it into the same location and rename it to ‘searchTransactions‘.
- Update the SQL query of the JDBC adapter to “${sqlStatement}” as we will dynamically build the SQL statement based on user input from the UI.
Create a Java service to build the where clause
- Create a java service in ‘HG_PayloadVault.v1.UI.subService‘ location with the name ‘createSQLStatement‘.
- This java service will create the where clause for the search operation based on the input received from UI.
- In the Java service input section, create the below string variables.
- In the output section of the java service create a string variable with the name ‘sqlStatement‘.
- Insert the following Java code into the Java service to assign column names with values based on their availability and return them in the output.
// pipeline IDataCursor pipelineCursor = pipeline.getCursor(); String source = IDataUtil.getString( pipelineCursor, "source" ); String target = IDataUtil.getString( pipelineCursor, "target" ); String interfaceId = IDataUtil.getString( pipelineCursor, "interfaceId" ); String interfaceName = IDataUtil.getString( pipelineCursor, "interfaceName" ); String identifierType = IDataUtil.getString( pipelineCursor, "identifierType" ); String identifierInput = IDataUtil.getString( pipelineCursor, "identifierInput" ); String transactionBefore = IDataUtil.getString( pipelineCursor, "transactionBefore" ); String transactionAfter = IDataUtil.getString( pipelineCursor, "transactionAfter" ); pipelineCursor.destroy(); Boolean variableExist=false; String tempSqlStatement=""; if(source != null && !source.trim().isEmpty()) { tempSqlStatement=tempSqlStatement.concat("SOURCE= \"" + source + "\""+" "); variableExist=true; } if(target != null && !target.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TARGET= \"" + target + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TARGET= \"" + target + "\""+ ""); variableExist=true; } } if(interfaceId != null && !interfaceId.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INTERFACE_ID= \"" + interfaceId + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INTERFACE_ID= \"" + interfaceId + "\""+" "); variableExist=true; } } if(interfaceName != null && !interfaceName.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INTERFACE_NAME= \"" + interfaceName + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INTERFACE_NAME= \"" + interfaceName + "\""+" "); variableExist=true; } } if("trackingId".equals(identifierType)) { if(identifierInput != null && !identifierInput.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TRACKING_ID= \"" + identifierInput + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TRACKING_ID= \"" + identifierInput + "\""+" "); variableExist=true; } } } if("transactionId".equals(identifierType)){ if(identifierInput != null && !identifierInput.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TRANSACTION_ID= \"" + identifierInput + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TRANSACTION_ID= \"" + identifierInput + "\""+" "); variableExist=true; } } } if(transactionBefore != null && !transactionBefore.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INSERTED_DATE < \"" + transactionBefore + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INSERTED_DATE < \"" + transactionBefore + "\""+ ""); variableExist=true; } } if(transactionAfter != null && !transactionAfter.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INSERTED_DATE > \"" + transactionAfter + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INSERTED_DATE > \"" + transactionAfter + "\""+ ""); variableExist=true; } } // pipeline IDataCursor pipelineCursor_1 = pipeline.getCursor(); IDataUtil.put( pipelineCursor_1, "sqlStatement", tempSqlStatement ); pipelineCursor_1.destroy();
- If you have pasted the above code correctly in the java service then your java service should look like below.
package HG_PayloadVault.v1.UI.subService; import com.wm.data.*; import com.wm.util.Values; import com.wm.app.b2b.server.Service; import com.wm.app.b2b.server.ServiceException; public final class createSQLStatement_SVC { /** * The primary method for the Java service * * @param pipeline * The IData pipeline * @throws ServiceException */ public static final void createSQLStatement(IData pipeline) throws ServiceException { // pipeline IDataCursor pipelineCursor = pipeline.getCursor(); String source = IDataUtil.getString( pipelineCursor, "source" ); String target = IDataUtil.getString( pipelineCursor, "target" ); String interfaceId = IDataUtil.getString( pipelineCursor, "interfaceId" ); String interfaceName = IDataUtil.getString( pipelineCursor, "interfaceName" ); String identifierType = IDataUtil.getString( pipelineCursor, "identifierType" ); String identifierInput = IDataUtil.getString( pipelineCursor, "identifierInput" ); String transactionBefore = IDataUtil.getString( pipelineCursor, "transactionBefore" ); String transactionAfter = IDataUtil.getString( pipelineCursor, "transactionAfter" ); pipelineCursor.destroy(); Boolean variableExist=false; String tempSqlStatement=""; if(source != null && !source.trim().isEmpty()) { tempSqlStatement=tempSqlStatement.concat("SOURCE= \"" + source + "\""+" "); variableExist=true; } if(target != null && !target.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TARGET= \"" + target + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TARGET= \"" + target + "\""+ ""); variableExist=true; } } if(interfaceId != null && !interfaceId.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INTERFACE_ID= \"" + interfaceId + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INTERFACE_ID= \"" + interfaceId + "\""+" "); variableExist=true; } } if(interfaceName != null && !interfaceName.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INTERFACE_NAME= \"" + interfaceName + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INTERFACE_NAME= \"" + interfaceName + "\""+" "); variableExist=true; } } if("trackingId".equals(identifierType)) { if(identifierInput != null && !identifierInput.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TRACKING_ID= \"" + identifierInput + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TRACKING_ID= \"" + identifierInput + "\""+" "); variableExist=true; } } } if("transactionId".equals(identifierType)){ if(identifierInput != null && !identifierInput.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"TRANSACTION_ID= \"" + identifierInput + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("TRANSACTION_ID= \"" + identifierInput + "\""+" "); variableExist=true; } } } if(transactionBefore != null && !transactionBefore.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INSERTED_DATE < \"" + transactionBefore + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INSERTED_DATE < \"" + transactionBefore + "\""+ ""); variableExist=true; } } if(transactionAfter != null && !transactionAfter.trim().isEmpty()) { if(variableExist) { tempSqlStatement=tempSqlStatement.concat("AND"+" "+"INSERTED_DATE > \"" + transactionAfter + "\""+" "); } else{ tempSqlStatement=tempSqlStatement.concat("INSERTED_DATE > \"" + transactionAfter + "\""+ ""); variableExist=true; } } // pipeline IDataCursor pipelineCursor_1 = pipeline.getCursor(); IDataUtil.put( pipelineCursor_1, "sqlStatement", tempSqlStatement ); pipelineCursor_1.destroy(); } // --- <<IS-BEGIN-SHARED-SOURCE-AREA>> --- // --- <<IS-END-SHARED-SOURCE-AREA>> --- final static createSQLStatement_SVC _instance = new createSQLStatement_SVC(); static createSQLStatement_SVC _newInstance() { return new createSQLStatement_SVC(); } static createSQLStatement_SVC _cast(Object o) { return (createSQLStatement_SVC)o; } }
Create a flow service to load transactions
- Create a flow service in ‘HG_PayloadVault.v1.UI‘ with ‘loadTransaction‘ name.
- In the input of the flow service create the below string variables which will help us if we are searching for any transaction.
- source
- target
- interfaceId
- interfaceName
- identifierType
- identifierInput
- transactionBefore
- transactionAfter
- Copy the output document of the ‘getTransactions‘ adapter and place it into this service’s output document.
- Invoke the map service and create two string variables in the pipeline output. Then perform the following mapping.
- Create a string variable named ‘days‘-> hard code it with ‘%PayloadVault.days%‘, and check the “Perform global variable substitution” option.
- Create a string variable named ‘dateFormat‘-> hard code it with ‘%PayloadVault.datePattern%‘, and check the “Perform global variable substitution” option.
Please note: If you are directly following this article, we recommend checking out our previous article in this series, as there are dependencies. We have created the global variables needed for this project there.
- Invoke the Branch step and in the switch property put ‘transactionBefore‘.
- Invoke ‘pub.date:dateTimeFormat‘ inside the Branch step, and in the label, use the regular expression ‘/.+/‘ following the mapping below.
- transactionBefore -> inString.
- dateFormat ->newPattern.
- currentPattern -> hard code it with ‘yyyy-MM-dd’.
- value -> transactionBefore.
- Invoke ‘pub.date:dateTimeFormat‘ inside the Branch step, and in the label, use the regular expression ‘/.+/‘ following the mapping below.
- Invoke the Branch step and in the switch property put ‘transactionAfter‘.
- Invoke ‘pub.date:dateTimeFormat‘ inside the Branch step, and in the label, use the regular expression ‘/.+/‘ following the mapping below.
- transactionAfter -> inString.
- dateFormat ->newPattern.
- currentPattern -> hard code it with ‘yyyy-MM-dd’.
- value -> transactionAfter.
- Invoke ‘pub.date:dateTimeFormat‘ inside the Branch step, and in the label, use the regular expression ‘/.+/‘ following the mapping below.
- Invoke the ‘HG_PayloadVault.v1.UI.subService:createSQLStatement‘ service and follow the mapping below. If a search request has come from the UI, this service will generate the WHERE clause.
- source -> source.
- target -> target.
- interfaceId ->interfaceId.
- interfaceName ->interfaceName.
- identifierType ->identifierType.
- identifierInput ->identifierInput.
- transactionBefore ->transactionBefore.
- transactionAfter ->transactionAfter.
- Invoke the Branch step and set the switch property to ‘sqlStatement‘.
- Invoke the Sequence step inside the Branch step and in the level put ‘/.+/’ regex, which will check if ‘sqlStatement‘ variable has some data in it.
- Invoke the Map step and use the ‘pub.string:concat‘ service as a transformer. Follow the mapping instructions below.
- inString1 -> Hard code it with ‘SELECT INTERFACE_ID, SOURCE, TARGET, MAX(INTERFACE_NAME) AS INTERFACE_NAME, TRACKING_ID, DOCUMENT_TYPE, DOCUMENT_NAME, MAX(INSERTED_DATE) AS INSERTED_DATE, COUNT(*) AS DataCount
FROM PAYLOADVAULT WHERE ‘. - sqlStatement -> inString2.
- value ->sqlStatement.
- inString1 -> Hard code it with ‘SELECT INTERFACE_ID, SOURCE, TARGET, MAX(INTERFACE_NAME) AS INTERFACE_NAME, TRACKING_ID, DOCUMENT_TYPE, DOCUMENT_NAME, MAX(INSERTED_DATE) AS INSERTED_DATE, COUNT(*) AS DataCount
- Invoke the Map step and use the ‘pub.string:concat‘ service as a transformer. Follow the mapping instructions below.
- sqlStatement ->inString1.
- inString2 -> Hard code it with ‘GROUP BY INTERFACE_ID, SOURCE, TARGET, TRACKING_ID, DOCUMENT_TYPE, DOCUMENT_NAME’.
- value ->sqlStatement.
- Invoke ‘HG_PayloadVault.v1.UI.adapter:searchTransactions’ service which will get us the search result.
- sqlStatement ->getTransactionsInput/sqlStatement.
- Invoke the Map step and use the ‘pub.string:concat‘ service as a transformer. Follow the mapping instructions below.
- Invoke the Sequence step inside the Branch step and in the level put ‘$default’.
- Invoke ‘pub.date:getCurrentDateString‘ to get the current date time string and follow the below mapping.
- pattern -> dateFormat.
- value -> currentDate.
- Invoke ‘pub.datetime:increment‘ and follow the below mapping.
- startDate -> currentDate.
- startDatePattern ->dateFormat.
- endDatePattern ->dateFormat.
- addDays -> days.
- endDate->endDate.
- Invoke ‘HG_PayloadVault.v1.UI.adapter:getTransactions‘ adapter ser to extract transactions based on the date specified and follow the below mapping.
- endDate ->getTransactionsInput/inDate.
- getTransactionsOutput ->getTransactionsOutput.
- Invoke ‘pub.date:getCurrentDateString‘ to get the current date time string and follow the below mapping.
- Invoke the Sequence step inside the Branch step and in the level put ‘/.+/’ regex, which will check if ‘sqlStatement‘ variable has some data in it.
- Invoke the Branch step to check if any data is retrieved. if not, we will throw an exception that will be visible in the UI.
- Invoke the Exit step inside the branch and follow the below setup.
- Label -> $null.
- Signal -> FILURE.
- Exit from -> $flow.
- Failure message ->’ Sorry, no transactions were found with the given search criteria.’.
- Invoke the Exit step inside the branch and follow the below setup.
- Invoke the Map step and drop all variables except the ‘getTransactionsOutput‘ document.
Load transaction into UI
- Create the below folders inside the package’s pub directory in the file system where we will be keeping CSS and javascript code.
- css.
- js.
- Inside the css folder create a file with the name stylesheet.css.
- Inside the js folder create a file with the name script.js.
index.dsp file update
Insert the following HTML script into the index.dsp page. This script will invoke the ‘HG_PayloadVault.v1.UI:loadTransaction’ backend service to search transactions and load them into the UI. The HTML also includes pagination, loading 10 transactions per page if the total number of transactions exceeds 10.
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Payload Vault</title> <link rel="stylesheet" href="css\stylesheet.css"> </head> <body> <header> <img src="image\harmonigate.png" alt="Harmonigate.com"> <h1>Payload Vault</h1> </header> <table> <form id="searchForm"> <tr> <td><input type="text" id="source" name="source" placeholder="Enter Source"></td> <td><input type="text" id="target" name="target" placeholder="Enter Target"></td> <td> <input type="text" id="interfaceId" name="interfaceId" placeholder="Enter InterfaceId"></td> </tr> <tr> <td><input type="text" id="interfaceName" name="interfaceName" placeholder="Enter InterfaceName"></td> <td> <select id="identifierType" name="identifierType" onchange="updateIdentifierField()"> <option value="trackingId" name="trackingId">Tracking ID</option> <option value="transactionId" name="transactionId">Transaction ID</option> </select> </td> <td> <input type="text" id="identifierInput" name="identifierInput" placeholder="Enter Tracking ID"> </td> </tr> <tr> <td> <label>Before Date</label> <input type="date" id="beforedate" name="transactionBefore"> </td> <td> <label>After Date</label> <input type="date" id="afterDate" name="transactionAfter"> </td> <td> <button type="submit" class="full-width">Submit</button> <button type="button" class="full-width" onclick="clearFormAndSubmit()" style="background-color: rgb(246, 107, 32);">Reset</button> </td> </tr> </form> </table> <div class="table-container"> <table id="myTable"> <thead> <tr> <th>Interface ID</th> <th>Source</th> <th>Target</th> <th>Interface Name</th> <th>Document Type</th> <th>Document Name</th> <th>Inserted Date</th> <th>Tracking ID</th> <th>Payload Count</th> </tr> </thead> %invoke HG_PayloadVault.v1.UI:loadTransaction% <tbody id="table-body"> %loop getTransactionsOutput/results% <tr> <td>%value INTERFACE_ID%</td> <td>%value SOURCE%</td> <td>%value TARGET%</td> <td>%value INTERFACE_NAME%</td> <td>%value DOCUMENT_TYPE%</td> <td>%value DOCUMENT_NAME%</td> <td>%value INSERTED_DATE%</td> <td>%value TRACKING_ID%</td> <td><a href="" class="capture-row-data">%value DataCount%</a></td> </tr> %endloop% <!-- Add more rows as needed --> </tbody> %onerror% <HR> <P> <FONT COLOR="#FF0000">%value errorMessage%</FONT> </P> %endinvoke% </table> <div class="pagination"> <button id="prevBtn" onclick="prevPage()">Previous</button> <button id="nextBtn" onclick="nextPage()">Next</button> </div> </div> </body> <script src="js\script.js"></script> </html>
stylesheet.css file update
Insert the following script into the stylesheet.css file to improve the UI’s look and feel.
body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f2f2f2; } header { background-color: #3e7eb0; color: white; padding: 20px; display: flex; justify-content: center; align-items: center; } header img { max-width: 160px; height: 40px; margin-right: auto; /* Pushes the logo to the right */ } h1 { margin: 0; text-align: center; flex-grow: 1; /* Allows the title to take remaining space */ } .table-container { width: 100%; overflow-x: auto; margin: 20px auto; /* Adjust margin as needed */ } table { width: 100%; max-width: 100%; border-collapse: collapse; border-radius: 8px; table-layout: fixed; /* Ensures table respects width constraints */ background-color: #f9f9f9; } th, td { padding: 15px; border: 1px solid #ddd; word-break: break-word; /* Ensures content breaks within cells */ max-width: 300px; /* Example: Limit maximum width of each cell */ white-space: nowrap; /* Prevents wrapping and forces content to stay on one line */ overflow: hidden; text-overflow: ellipsis; /* Truncate text with ellipsis if it exceeds cell width */ } th { background-color: #4CAF50; color: white; } tbody tr:hover { background-color: #f5f5f5; } .pagination { display: flex; justify-content: center; list-style: none; padding: 0; } .pagination li { margin: 0 5px; } .pagination button { padding: 10px 20px; margin: 5px; border: none; background-color: #007bff; color: white; cursor: pointer; font-size: 16px; border-radius: 5px; transition: background-color 0.3s ease; } .pagination button:hover { background-color: #0056b3; } .pagination button:disabled { background-color: #ddd; color: #aaa; cursor: not-allowed; } button.full-width { width: auto%; padding: 15px; background-color: #28a745; color: white; border: none; border-radius: 5px; font-size: 16px; font-weight: bold; cursor: pointer; margin-top: 0px; transition: background-color 0.3s ease, transform 0.3s ease; box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1); } button.full-width:hover { background-color: #218838; transform: translateY(-2px); } button.full-width:active { background-color: #1e7e34; transform: translateY(1px); }
script.js file update
Insert the following script into the script.js file. This script includes the code for pagination handling and form-clearing logic. Additionally, it contains the logic to pass data to the ‘viewTransaction.dsp’ page when the count anchor is clicked, which will load all the transactions for that entry as we grouped the transactions earlier. From ‘viewTransaction.dsp’, there will be a view button to load the actual payload, which we will explore in the next article.
function updateIdentifierField() { var identifierType = document.getElementById('identifierType').value; var identifierInput = document.getElementById('identifierInput'); if (identifierType === 'trackingId') { identifierInput.placeholder = 'Enter Tracking ID'; } else if (identifierType === 'transactionId') { identifierInput.placeholder = 'Enter Transaction ID'; } } document.addEventListener('DOMContentLoaded', function () { const tableBody = document.getElementById('table-body'); const rowsPerPage = 10; let currentPage = 1; const totalRows = tableBody.rows.length; function showPage(page) { const startIndex = (page - 1) * rowsPerPage; const endIndex = startIndex + rowsPerPage; for (let i = 0; i < totalRows; i++) { if (i >= startIndex && i < endIndex) { tableBody.rows[i].style.display = 'table-row'; } else { tableBody.rows[i].style.display = 'none'; } } // Enable/disable previous and next buttons document.getElementById('prevBtn').disabled = page === 1; document.getElementById('nextBtn').disabled = endIndex >= totalRows; } window.prevPage = function () { if (currentPage > 1) { currentPage--; showPage(currentPage); } } window.nextPage = function () { if (totalRows > currentPage * rowsPerPage) { currentPage++; showPage(currentPage); } } // Initial page load showPage(currentPage); }); function clearFormAndSubmit() { const form = document.getElementById('searchForm'); form.reset(); form.submit(); } document.addEventListener('DOMContentLoaded', function () { // Get all anchor tags with class 'capture-row-data' var editLinks = document.querySelectorAll('.capture-row-data'); // Attach click event listeners to each anchor tag editLinks.forEach(function (link) { link.addEventListener('click', function (event) { event.preventDefault(); // Prevent the default behavior of the anchor tag // Traverse up the DOM to find the parent 'tr' element (table row) var row = event.target.closest('tr'); var url = "viewTransaction.dsp?INTERFACE_ID=" + row.cells[0].innerText + "&SOURCE=" + row.cells[1].innerText + "&TARGET=" + row.cells[2].innerText + "&INTERFACE_NAME=" + row.cells[3].innerText + "&DOCUMENT_TYPE=" + row.cells[4].innerText + "&DOCUMENT_NAME=" + row.cells[5].innerText + "&INSERTED_DATE=" + row.cells[6].innerText + "&TRACKING_ID="+row.cells[7].innerText; console.log(url); window.open(url, "_blank"); }); }); });
Testing :
- Load transaction:
- Error Message:
In the next article, we will focus on loading the actual payload into the UI. This will be the final article for this project. Stay tuned.