Agent Callback
Ref: https://sqlperformance.com/2013/09/t-sql-queries/random-collisions
CREATE TABLE dbo.RandomNumbers ( RowID INT, VALUE INT, --UNIQUE, PRIMARY KEY (RowID, VALUE) ); ;WITH x AS ( SELECT TOP (1000000) s1.[object_id] FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ORDER BY s1.[object_id] ) INSERT dbo.RandomNumbers(RowID, VALUE) SELECT r = ROW_NUMBER() OVER (ORDER BY [object_id]), n = ROW_NUMBER() OVER (ORDER BY NEWID()) FROM x ORDER BY r;
Remove any non 6 digit numbers.
DELETE FROM [dbo].[RandomNumbers] Where Len(Value) <> 6
This query proves that there are no duplicate records
SELECT VALUE , COUNT(*) FROM RandomNumbers GROUP BY VALUE HAVING COUNT(*) > 1
Insert into Callback Table
DECLARE @holding TABLE(CallbackId INT); DELETE TOP (1) dbo.RandomNumbers OUTPUT deleted.Value INTO @holding; INSERT dbo.Callback(CallbackId) SELECT CallbackId FROM @holding;
Create Agent Table
USE [AgentCallback] GO /****** Object: Table [dbo].[Callback] Script Date: 15/05/2021 21:27:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Callback]( [Id] [INT] IDENTITY(1,1) NOT NULL, [CallbackId] [INT] NULL, [AgentPeripheralNumber] [nvarchar](32) NULL, [SkillgroupId] [INT] NULL, [OverflowToSkillgroup] [CHAR](1) NULL, [Attempts] [INT] NULL, [MaxAttempts] [INT] NULL, [Enabled] [CHAR](1) NULL, [Created_DateTime] [datetime] NULL, [Expiry_DateTime] [datetime] NULL, CONSTRAINT [PK_Callback] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Callback] ADD CONSTRAINT [DF_Callback_Attempts] DEFAULT ((0)) FOR [Attempts] GO ALTER TABLE [dbo].[Callback] ADD CONSTRAINT [DF_Callback_MaxAttempts] DEFAULT ((3)) FOR [MaxAttempts] GO ALTER TABLE [dbo].[Callback] ADD CONSTRAINT [DF_Callback_Enabled] DEFAULT ('Y') FOR [Enabled] GO ALTER TABLE [dbo].[Callback] ADD CONSTRAINT [DF_Callback_Created_DateTime] DEFAULT (getdate()) FOR [Created_DateTime] GO
Insert into Callback Table
DECLARE @holding TABLE(CallbackId INT); DELETE TOP (1) dbo.RandomNumbers OUTPUT deleted.Value INTO @holding; INSERT dbo.Callback(CallbackId,AgentPeripheralNumber,Created_DateTime, Expiry_DateTime) SELECT CallbackId, '12345',GetDate(),dateadd(DAY,7,getDate()) FROM @holding; SELECT * FROM Callback WHERE AgentPeripheralNumber = '12345' AND Expiry_DateTime > GetDate() AND Enabled = 'Y' ORDER BY Created_DateTime DESC
Store Procedure
USE [AgentCallback] GO /****** Object: StoredProcedure [dbo].[CreateCallBackId] Script Date: 14/05/2021 16:38:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[CreateCallBackId] -- Add the parameters for the stored procedure here @AgentPeripheralNumber nvarchar(32) = NULL, @Expiry_DateTime datetime = NULL AS BEGIN SET NOCOUNT ON; SET @Expiry_DateTime = isNull(@Expiry_DateTime ,dateadd(DAY,7,getDate())) DECLARE @holding TABLE(CallbackId INT); DELETE TOP (1) dbo.RandomNumbers OUTPUT deleted.Value INTO @holding; INSERT dbo.Callback(CallbackId,AgentPeripheralNumber,Created_DateTime, Expiry_DateTime) SELECT CallbackId, @AgentPeripheralNumber,GetDate(),@Expiry_DateTime FROM @holding; SELECT * FROM Callback WHERE AgentPeripheralNumber = @AgentPeripheralNumber AND Expiry_DateTime > GetDate() AND Enabled = 'Y' ORDER BY Created_DateTime DESC END
USE [AgentCallback] GO DECLARE @return_value INT EXEC @return_value = [dbo].[CreateCallBackId] @AgentPeripheralNumber = N'5551001', @Expiry_DateTime = N'2021-05-14 20:00:00' SELECT 'Return Value' = @return_value GO
Create User
USE [AgentCallback] GO /****** Object: User [callbackuser] Script Date: 15/05/2021 21:45:10 ******/ CREATE USER [callbackuser] FOR LOGIN [callbackuser] WITH DEFAULT_SCHEMA=[dbo] GO
PHP Store Procedure
References Used
Gagdet
Sample URL for getCallbacks
http://w2016-dev/callback/getCallbacks.php?agentId=5551001
Sample URL for createCallback
Note: the only Required variable is agentId - otherwise a default of 24 hours is set for expiry and '-' values are stored for customer name and description.
http://w2016-dev/callback/createCallback.php?agentId=5551000&customerName=Mr+Boo&Description=Blah&expireIn=72
JS Code for createCallback
// Author: Gerry O'Rourke // Agent Callback Gadget // Date 02/02/2022 Version 1.0 var finesse = finesse || {}; finesse.gadget = finesse.gadget || {}; finesse.container = finesse.container || {}; _clientLogger = finesse.cslogger.ClientLogger || {}; // for logging /** @namespace */ finesse.modules = finesse.modules || {}; finesse.modules.AgentCallbackGadget = (function ($) { var _user; var _host = 'w2016-dev'; var _baseURL = 'http://' + _host ; var _getCallbackPath = '/callback/getCallbacks.php'; var _createCallbackPath = '/callback/createCallback.php'; var _callbackText = 'To speak directly to this agent, please call 01 5551234 and enter the 6 digit PIN:'; var _version = '1.0'; /** * Populates the fields in the gadget with data */ _updateUI = function () { _clientLogger.log ("_updateUI(): --------"); gadgets.window.adjustHeight(); }, /* This function uses the gadgets.io.makeRequest - to do the REST GET, POST or PUT requests and returns success / error * Reference: https://developers.google.com/gadgets/docs/reference#makerequest * * @param {type} url * @param {type} options * @param {type} handlers * @returns {undefined} */ makeRequest = function (url, options, handlers) { var params, uuid; _clientLogger.log("makeRequest()"); // Protect against null dereferencing of options & handlers allowing its (nonexistant) keys to be read as undefined params = {}; options = options || {}; handlers.success = _util.validateHandler(handlers.success); handlers.error = _util.validateHandler(handlers.error); // Request Headers params[gadgets.io.RequestParameters.HEADERS] = {}; // HTTP method is a passthrough to gadgets.io.makeRequest params[gadgets.io.RequestParameters.METHOD] = options.method; if (options.method === "GET") { // Disable caching for GETs if (url.indexOf("?") > -1) { url += "&"; } else { url += "?"; } url += "nocache=" + _util.currentTimeMillis(); uuid = _util.generateUUID(); params[gadgets.io.RequestParameters.HEADERS].requestId = uuid; params[gadgets.io.RequestParameters.GET_FULL_HEADERS] = "true"; } else { // Generate a requestID and add it to the headers uuid = _util.generateUUID(); params[gadgets.io.RequestParameters.HEADERS].requestId = uuid; params[gadgets.io.RequestParameters.GET_FULL_HEADERS] = "true"; } // Add content type & body if content body is provided if (options.content) { // Content Type params[gadgets.io.RequestParameters.HEADERS]["Content-Type"] = options.contentType; // Content params[gadgets.io.RequestParameters.POST_DATA] = options.content; } // Call the gadgets.io.makereqest function with the encoded url _clientLogger.log("makeRequest(): Making a REST API request to: " + url); gadgets.io.makeRequest(encodeURI(url), handleResponse(handlers), params); }, /** * Handler for the response of the REST API request. This function determines if * the success or error handler should be called based on HTTP status code. * * @param {Object} handlers * An object containing the success and error handlers. * * {Function} handlers.success(response) * A callback function to be invoked for a successful request. * {Function} handlers.error(response) * A callback function to be invoked for an unsuccessful request. */ handleResponse = function(handlers) { return function (response) { _clientLogger.log("handleResponse(): The response status code is: " + response.rc); // Send the response to the success handler if the http status // code is 200 - 299. Send the response to the error handler // otherwise. if (response.rc >= 200 && response.rc < 300 && handlers.success) { _clientLogger.log("handleResponse(): Got a successful response."); handlers.success(response); } else if (handlers.error) { _clientLogger.log("handleResponse(): Got a failure response."); handlers.error(response); } else { _clientLogger.log("handleResponse(): Missing the success and/or error handler."); } }; }, /** * Handler for getCallbacks when successful. */ _getCallbacksSuccess = function(response) { _clientLogger.log ("_getCallbacksSuccess(): --------"); var _getCallbacksSuccessJSON = JSON.parse(response.text); _clientLogger.log ("_getCallbacksSuccess(): _getCallbacksSuccessJSON: " + JSON.stringify(_getCallbacksSuccessJSON)); // Commenting this out to remove authToken from Console Logs callbackTableValues = _getCallbacksSuccessJSON; $("#CallbackTable tr").remove(); //Clear table rows before refreshing data _clientLogger.log ("_getCallbacksSuccess(): Clear table before regenerating") function generateTableHead(table, data) { let thead = table.createTHead(); let row = thead.insertRow(); for (let key of data) { let th = document.createElement("th"); let text = document.createTextNode(key); th.appendChild(text); row.appendChild(th); } } function generateTable(table, data) { for (let element of data) { let row = table.insertRow(); for (key in element) { let cell = row.insertCell(); let text = document.createTextNode(element[key]); cell.appendChild(text); } } } let table = document.querySelector("#CallbackTable"); let data = Object.keys(callbackTableValues[0]); _clientLogger.log ("_getCallbacksSuccess(): Printing Table") generateTableHead(table, data); generateTable(table, callbackTableValues); gadgets.window.adjustHeight(); }, /** * Handler for getCallback Errors */ _getCallbacksError = function(response) { _clientLogger.log ("_getCallbacksError(): in method"); var statusCode = response.rc; var errorMessage = 'Network or Configuration Error'; //Default Error // Checking if we got any HTTP Headers in response (maybe not if there was a Timeout etc.) if (typeof response.headers != "undefined") { var myContentType = response.headers["content-type"]; console.log("Content-Type: " + myContentType); //Checking if Content is JSON and so use the error value returned by the server if (myContentType == "application/json; charset=UTF-8") { var responseJSON = JSON.parse(response.text); var errorMessage = responseJSON.error; }else{ _clientLogger.log ("Content-Type is NOT \'application/json; charset=UTF-8\'"); errorMessage = 'Server Error'; } } _clientLogger.log ("_getCallbacksError() - WebServerStatusCode: " + statusCode); _clientLogger.log ("_getCallbacksError() - errorMessage: " + errorMessage); $('#errorMsgHeader').text("Failed to Retrieve Callbacks!"); $('#errorMsgCode').text(statusCode); $('#errorMsgMessage').text(errorMessage); $('#errorMsg').show(); gadgets.window.adjustHeight(); }, /** * Handler for getCallbacks when successful. */ _createCallbackSuccess = function(response) { _clientLogger.log ("_createCallbacksSuccess(): --------"); var _getCallbacksSuccessJSON = JSON.parse(response.text); _clientLogger.log ("_createCallbacksSuccess(): _getCallbacksSuccessJSON: " + JSON.stringify(_getCallbacksSuccessJSON)); // Commenting this out to remove authToken from Console Logs callbackTableValues = _getCallbacksSuccessJSON; $("#CallbackTable tr").remove(); //Clear table rows before refreshing data _clientLogger.log ("_createCallbacksSuccess(): Clear table before regenerating") function generateTableHead(table, data) { let thead = table.createTHead(); let row = thead.insertRow(); for (let key of data) { let th = document.createElement("th"); let text = document.createTextNode(key); th.appendChild(text); row.appendChild(th); } } function generateTable(table, data) { for (let element of data) { let row = table.insertRow(); for (key in element) { let cell = row.insertCell(); let text = document.createTextNode(element[key]); cell.appendChild(text); } } } let table = document.querySelector("#CallbackTable"); let data = Object.keys(callbackTableValues[0]); _clientLogger.log ("_createCallbacksSuccess(): Printing Table") generateTableHead(table, data); generateTable(table, callbackTableValues); //Clear Name and Description Fields after successful Callback Creation. document.getElementById('name').value = ""; document.getElementById('description').value = ""; gadgets.window.adjustHeight(); }, /** * Handler for getCallback Errors */ _createCallbackError = function(response) { _clientLogger.log ("_createCallbacksError(): in method"); var statusCode = response.rc; var errorMessage = 'Network or Configuration Error'; //Default Error // Checking if we got any HTTP Headers in response (maybe not if there was a Timeout etc.) if (typeof response.headers != "undefined") { var myContentType = response.headers["content-type"]; console.log("Content-Type: " + myContentType); //Checking if Content is JSON and so use the error value returned by the server if (myContentType == "application/json; charset=UTF-8") { var responseJSON = JSON.parse(response.text); var errorMessage = responseJSON.error; }else{ _clientLogger.log ("Content-Type is NOT \'application/json; charset=UTF-8\'"); errorMessage = 'Server Error'; } } _clientLogger.log ("_createCallbacksError() - WebServerStatusCode: " + statusCode); _clientLogger.log ("_createCallbacksError() - errorMessage: " + errorMessage); $('#errorMsgHeader').text("Failed to Retrieve Callbacks!"); $('#errorMsgCode').text(statusCode); $('#errorMsgMessage').text(errorMessage); $('#errorMsg').show(); gadgets.window.adjustHeight(); }, /** * Handler for the onLoad of a User object. This occurs when the User object is initially read * from the Finesse server. Any once only initialization should be done within this function. */ _handleUserLoad = function (userevent) { _clientLogger.log("_handleUserLoad(): --------"); _myAgentId = _user.getId(); _clientLogger.log("_handleUserLoad(): Agent ID: " + _myAgentId) ; _getCallbacks(); }, _getCallbacks = function() { _clientLogger.log ("_getCallbacks(): --------"); var url = _baseURL + _getCallbackPath + '?agentId=' + _myAgentId; _clientLogger.log ("_getCallbacks(): URL: " + url); makeRequest(url, { method: 'GET', }, { success: _getCallbacksSuccess, error: _getCallbacksError, }); }; _createCallback = function() { _clientLogger.log ("_createCallback(): --------"); const RadioCallbackExpiryButtons = document.querySelectorAll('input[name="RadioCallbackExpiry"]'); for (const RadioCallbackExpiryButton of RadioCallbackExpiryButtons) { if (RadioCallbackExpiryButton.checked) { ExpiryHours = RadioCallbackExpiryButton.value; _clientLogger.log ("_createCallback(): ExpiryHours: " + ExpiryHours + " hours"); break; } } var name = document.getElementById('name').value; if (name===""){name = "-"}; var description = document.getElementById('description').value; if (description===""){description = "-"}; _clientLogger.log ("_createCallback(): Name: " + name); var url = _baseURL + _createCallbackPath + '?agentId=' + _myAgentId + '&customerName=' + name + '&description=' + description + '&expireIn=' + ExpiryHours; _clientLogger.log ("_createCallback(): URL: " + url); makeRequest(url, { method: 'GET', }, { success: _createCallbackSuccess, error: _createCallbackError, }); }; /** @scope finesse.modules.AgentCallbackGadget */ return { /** * Get Callbacks * This is used on login and also if the refresh button is clicked. */ refreshCallbacks : function () { // Clear Name and Description Fields document.getElementById('name').value = ""; document.getElementById('description').value = ""; _getCallbacks(); }, /** * Create Callback * This is used on login and also if the refresh button is clicked. */ createCallback : function () { _createCallback(); }, /** * Create Callback * This is used on login and also if the refresh button is clicked. */ copyToClipboard : function () { _clientLogger.log ('copyToClipboard: --------'); //var copyText = document.getElementById('name'); var myTable = document.getElementById('CallbackTable'); var copyCallbackId = myTable.rows[1].cells[0].innerHTML; // Select the First Cell on the second row (exclude the table header) var copyExpiryTime = myTable.rows[1].cells[4].innerHTML; // Select the First Cell on the second row (exclude the table header) _clientLogger.log ('copyToClipboard: CallbackId: ' + copyCallbackId); _clientLogger.log ('copyToClipboard: ExpiryTime: ' + copyExpiryTime); /* Copy the text inside the text field */ navigator.clipboard.writeText(_callbackText + ' ' + copyCallbackId + '. This callback will expire on ' + copyExpiryTime); /* Alert the copied text */ alert('Copied Callback ID: ' + copyCallbackId + ' to the clipboard'); }, /** * Performs all initialization for this gadget */ init : function () { var _clientLogger = finesse.cslogger.ClientLogger; // declare _clientLogger // Initiate the ClientLogs. The gadget id will be logged as a part of the message _clientLogger.init(gadgets.Hub, "AgentCallbackGadget", finesse.gadget.Config); _clientLogger.log ("init(): Initializing..."); var cfg = finesse.gadget.Config; _util = finesse.utilities.Utilities; // Initiate the ClientServices and load the user object. ClientServices are // initialized with a reference to the current configuration. finesse.clientservices.ClientServices.init(cfg, false); gadgets.window.adjustHeight(); _user = new finesse.restservices.User({ id: finesse.gadget.Config.id, onLoad : _handleUserLoad, }); _clientLogger.log ("init(): Host: " + _host); _clientLogger.log ("init(): Version: " + _version); // Initiate the ContainerServices and add a handler for when the tab is visible // to adjust the height of this gadget in case the tab was not visible // when the html was rendered (adjustHeight only works when tab is visible) // *** Without this the gadget is not correct size on load without refresh the page! *** containerServices = finesse.containerservices.ContainerServices.init(); containerServices.addHandler(finesse.containerservices.ContainerServices.Topics.ACTIVE_TAB, function() { _clientLogger.log("Gadget is now visible"); // log to Finesse logger gadgets.window.adjustHeight(); // Automatically adjust the height of the gadget to show the html }); containerServices.makeActiveTabReq(); _clientLogger.log ("init(): Initialization finished."); } }; }(jQuery));
XML Code for createCallback
<?xml version="1.0" encoding="UTF-8"?> <Module> <ModulePrefs title="Agent Callback Gadget" description="Agent Callback Gadget" scrolling="auto"> <Require feature="settitle"/> <Require feature="dynamic-height"/> <Require feature="pubsub-2"/> <Require feature="setprefs"/> <Require feature="loadingindicator"> <Param name="manual-dismiss">false</Param> <Param name="loading-timeout">10</Param> </Require> </ModulePrefs> <!--Uncomment the below 3 lines if NOT hosting on Finesse server. --> <UserPref name="scheme" display_name="scheme" default_value=""/> <UserPref name="host" display_name="host" default_value=""/> <UserPref name="hostPort" display_name="hostPort" default_value=""/> <Content type="html"> <![CDATA[ <!DOCTYPE html> <head> <!-- Styling --> <link rel="stylesheet" href="./bootstrap-4.6.1-dist/css/bootstrap.min.css" type="text/css" /> <link rel="stylesheet" href="./AgentCallback.css" type="text/css" /> <!-- jQuery - uncomment if NOT hosting on Finesse server --> <script type="text/javascript" src="__UP_scheme__://__UP_host__:__UP_hostPort__/desktop/assets/js/jquery.min.js"></script> <!-- Finesse Library - uncomment if NOT hosting on Finesse server --> <script type="text/javascript" src="__UP_scheme__://__UP_host__:__UP_hostPort__/desktop/assets/js/finesse.js"></script> <!-- Bootstrap - Must appear After jQuery JS--> <script type="text/javascript" src="./bootstrap-4.6.1-dist/js/bootstrap.min.js"></script> <!-- Gadget Business Logic --> <script type="text/javascript" src="./AgentCallback.js"></script> </head> <body class="claro"> <div class="container"> <div id="errorMsg" class="alert alert-warning alert-dismissible fade show" role="alert" style="display: none"> <div><strong><span id="errorMsgHeader"></span></strong></div> <div><strong>Error Code: </strong><span id="errorMsgCode"></span></div> <div><strong>Error Message: </strong><span id="errorMsgMessage"></span></div> <button type="button" class="close" data-hide="alert" aria-label="Close" onclick="$('#errorMsg').hide();gadgets.window.adjustHeight(800);"> <span aria-hidden="true">×</span> </button> </div> <div class="alert alert-primary" role="alert"> <strong>Agent Callback Number:</strong> 01 555 1234 </div> <form> <div class="row"> <div class="col"> <label for="name"><strong>Name</strong></label> </div> <div class="col"> <label for="description"><strong>Description</strong></label> </div> </div> <div class="row"> <div class="col"> <input type="text" class="form-control" id="name" placeholder="Enter the customer's name" maxlength="40"> </div> <div class="col"> <input type="text" class="form-control" id="description" placeholder="Enter a description" maxlength="40"> </div> </div> <div> </div> <div class="custom-control custom-radio custom-control-inline"> <input class="form-check-input" type="radio" name="RadioCallbackExpiry" id="RadioButton1" value="1" checked> <label class="form-check-label" for="RadioButton1"> 1 hour </label> </div> <div class="custom-control custom-radio custom-control-inline"> <input class="form-check-input" type="radio" name="RadioCallbackExpiry" id="RadioButton2" value="24"> <label class="form-check-label" for="RadioButton2"> 1 day </label> </div> <div class="custom-control custom-radio custom-control-inline"> <input class="form-check-input" type="radio" name="RadioCallbackExpiry" id="RadioButton3" value="168"> <label class="form-check-label" for="RadioButton3"> 1 week </label> </div> <div class="custom-control custom-radio custom-control-inline" style="width: 200px;"> <button type="button" class="btn btn-outline-success" id="createCallback" onClick="finesse.modules.AgentCallbackGadget.createCallback()">Create</button> <button type="button" class="btn btn-outline-secondary" id="refreshCallbacks" onClick="finesse.modules.AgentCallbackGadget.refreshCallbacks()">Refresh</button> <button type="button" class="btn btn-outline-info" id="copyToClipboard" onClick="finesse.modules.AgentCallbackGadget.copyToClipboard()">Copy</button> </div> </form> <hr> <div class="table-responsive" style="height: 500px;"> <table class="table" id="CallbackTable"> </table> </div> </div> </body> <script type="text/javascript"> gadgets.HubSettings.onConnect = function () {finesse.modules.AgentCallbackGadget.init();};</script> ]]></Content> </Module>